Lookup ข้อมูล

[snasui]

ขอคุณครับอาจารย์ ที่ซีท Main เงื่อนไขไม่ตรงคือ เมื่อเลือก"ยกมา"ที่ D10,D12D13 จะแสดง "-" จริงๆ ต้องแสดงเฉพาะ D12,D13 เท่านั้น ตรงนี้ผมแก้ไขปรับใหม่แล้วใช้ได้ดีครับ ทีนี้ถ้าหากว่า D10 หรือ D12,D13 แสดงค่า "-" แล้วต้องการไม่ให้คีย์ข้อมูลได้ต้องเพิ่มโค้ดล็อคเซลล์ยังไงครับ

ที่เขียนไว้ในชีท Main ตามด้านล่าง แสดงว่าสับขาหลอกผมหรือไรครับ
ที่D10 ถ้า D9 เท่ากับ "ยกมา" ให้สามาถคีย์ข้อมูลได้
แต่ถ้าเท่ากับ "ลา" ก็ให้ใส่เครื่องหมาย " - " ลงไป
ที่D12 ถ้า D9 เท่ากับ "ลา" ให้สามาถคีย์ข้อมูลได้
แต่ถ้าเท่ากับ "ยกมา" ก็ให้ใส่เครื่องหมาย " - " ลงไป
ที่D13 ถ้า D9 เท่ากับ "ลา" ให้สามาถคีย์ข้อมูลได้
แต่ถ้าเท่ากับ "ยกมา" ก็ให้ใส่เครื่องหมาย " - " ลงไป

กรณีค่าใน D10, D12, D13 เป็น - แล้วไม่ต้องการให้คีย์ข้อมูลได้ ที่ผมเขียน Validation ไปนั้นก็ไม่สามารถคีย์ได้ครับ

ที่ Sheet3 ผมลองดูแล้วเงื่อนไขไม่ตรงครับ เงื่อนไขที่D6 เวลาเราเลือกรายการก็จะเห็นทั้งรหัสและชื่อก็จะสามารถคลิกเลือกรายการได้ตรงนีทำได้ แต่ถ้าไม่คลิกเลือกรายการที่แสดงเพียงแค่ป้อนรหัสเพียงอย่างเดียวก็ให้ระบบค้นหาให้ตรงนี้ยังไม่ได้ครับ

อ่านไม่ละเอียดไปหน่อย สำหรับสูตรใหม่แล้วเพื่อให้สามารถ Search เพียงบางส่วนของข้อมูล ที่ชีทบันทึกการลา เซลล์ L5 คีย์

=IF(AND(ISNUMBER(SEARCH(B5,Sheet3!$D$6)),A5=Sheet3!$D$8),LOOKUP(9.99999999999999E+307, บันทึกการลา!L$4:L4)+1,"")

Enter > Copy ลงด้านล่าง

[joo]

โค้ดที่อาจารย์ทำไว้ล็อคได้ครับแต่เงื่อนไขไม่ตรงครับ คือว่าเมื่อ D9 เท่ากับ "ยกมา" ที่ D10 ต้องเป็นค่าว่าง D12,D13 ต้องเป็น "-"และล็อคไว้ไม่สามารถคีย์ข้อมูลได้ เมื่อ D9 เท่ากับ "ลา" ที่ D10 ต้องเป็นค่า "-" ล็อคไว้คีย์ข้อมูลไม่ได้และที่ D12,D13 จะเป็นค่าว่างสามารถคีย์ข้อมูลได้
ผมปรับโค้ดใหม่ให้เงื่อนไขมันตรงแต่ว่า เมื่อ D9 เท่ากับ"ลา" ที่ D10 แสดง "-" และที่ D12,D13 เป็นค่าว่าง ไม่สามารถคีย์ข้อมูลได้(ตามเงื่อนไขต้องคีย์ข้อมูลได้)อาจารย์ช่วยดูโค้ดให้หน่อยครับ
ที่ Sheet3 ลองดูแล้วถ้าแถวไหนเป็นแถบดำก็สามารถคีย์เฉพาะรหัสได้ถ้าพิมพ์รหัสอื่นที่ไม่ขึ้นแถบดำระบบก็จะไม่ค้นหาได้ตามเงื่อนไขคือที่ D6 คือถ้าจำรหัสได้ก็คีย์รหัสได้เลยไม่ต้องไปกดเลือกในรายการถ้าจำไม่ได้ก็ค่อยไปเลือกดูในรายการ

[snasui]

ผมปรับให้ตรงกับที่ต้องการนั่นแหละครับ ตามด้านล่าง

ที่D10 ถ้า D9 เท่ากับ "ยกมา" ให้สามาถคีย์ข้อมูลได้
แต่ถ้าเท่ากับ "ลา" ก็ให้ใส่เครื่องหมาย " - " ลงไป
ที่D12 ถ้า D9 เท่ากับ "ลา" ให้สามาถคีย์ข้อมูลได้
แต่ถ้าเท่ากับ "ยกมา" ก็ให้ใส่เครื่องหมาย " - " ลงไป
ที่D13 ถ้า D9 เท่ากับ "ลา" ให้สามาถคีย์ข้อมูลได้
แต่ถ้าเท่ากับ "ยกมา" ก็ให้ใส่เครื่องหมาย " - " ลงไป

แต่เมื่อเปลี่ยนเงื่อนไขใหม่เป็นด้านล่างและปรับ Code มาใหม่เองแล้วจากที่บอกมา

ตอบกลับพร้อมอ้างอิง
โค้ดที่อาจารย์ ทำไว้ล็อคได้ครับแต่เงื่อนไขไม่ตรงครับ คือว่าเมื่อ D9 เท่ากับ "ยกมา" ที่ D10 ต้องเป็นค่าว่าง D12,D13 ต้องเป็น "-"และล็อคไว้ไม่สามารถคีย์ข้อมูลได้ เมื่อ D9 เท่ากับ "ลา" ที่ D10 ต้องเป็นค่า "-" ล็อคไว้คีย์ข้อมูลไม่ได้และที่ D12,D13 จะเป็นค่าว่างสามารถคีย์ข้อมูลได้
ผม ปรับโค้ดใหม่ให้เงื่อนไขมันตรงแต่ว่า เมื่อ D9 เท่ากับ"ลา" ที่ D10 แสดง "-" และที่ D12,D13 เป็นค่าว่าง ไม่สามารถคีย์ข้อมูลได้(ตามเงื่อนไขต้องคีย์ข้อมูลได้)อาจารย์ช่วยดูโค้ดให้ หน่อยครับ

ลองตามนี้ครับ

ที่ชีท Main
1. คลุม D12:D13
2. เข้าเมนู Data > Validation
3. ที่แถบ Setting ตรง Formula เป็นสูตรเป็นตามด้านล่างครับ

=$D$9="ลา"

4. OK

ที่ Sheet3 ลองดูแล้วถ้าแถวไหนเป็นแถบดำก็สามารถคีย์เฉพาะรหัสได้ถ้าพิมพ์รหัสอื่นที่ ไม่ขึ้นแถบดำระบบก็จะไม่ค้นหาได้ตามเงื่อนไขคือที่ D6 คือถ้าจำรหัสได้ก็คีย์รหัสได้เลยไม่ต้องไปกดเลือกในรายการถ้าจำไม่ได้ก็ค่อย ไปเลือกดูในรายการ

ไม่เข้าใจตรงแถบดำครับ แถบดำมาจากไหนครับ

ตอนนี้ผมปรับให้สามารถเลือกรหัสบุคคลมาได้ หรือคีย์เฉพาะรหัสก็ได้ โดยการปรับจะอยู่ที่ Validation คือไปปลดการเตือนออก ไม่ให้เตือนว่ามีกาีรคีย์ผิดพลาด แต่ถ้าค่าที่คีย์หรือที่เลือกไม่มีในชีทข้อมูลบุคคลก็จะไม่มีรายการมาแสดง นอกจากนี้ได้ปรับสูตรใน D10, D12 ให้ใหม่เพื่อเป็นแนวทางต่อไปครับ

[joo]

ตองขอโทษอาจารย์ด้วยครับที่อธิบายเงื่อนไขไม่ละเอียดตรงซีม Main ทดลองแล้วใช้ได้ครับ
แต่ตรง Sheet3 ข้อมูลยังเหมือนเดิม ทดลองแล้วยังไม่ได้เหมือนเดิมครับ

[snasui]

ช่วยอธิบายเงื่อนไขโดยละเอียดมาให้อีกรอบ และช่วยระบุปัญหามาด้วยครับว่าคีย์ค่าใดแล้วต้องการคำตอบเป็นอย่างไร และปัจจุบันได้คำตอบเป็นอย่างไร จะได้ทดสอบได้ตรงประเด็นครับ

[joo]

อาจารย์ครับสงสัยผมจะอธิบายเงื่อนไขไม่ละเอียด ผมได้ปรับเงื่อนไขใหม่ใน Sheet3
เงื่อนไข
1. ที่ D6เวลาเลือกรายการจะเห็นทั้งรหัสและชื่อสามารถใชเม้าคลิกเลือกรายการระบบก็จะค้นหาให้ได้เลย ตรงนี้ได้แล้ว
2.ที่ D6 ถ้าเราป้อนเพียงรหัสอย่างเดียว เช่น ขณะที่ D6 ว่างเปล่าหรือมีรหัสอื่นแสดงอยู่เราก็คีย์รหัส เช่น 26401 ลงไปในเซลล์ D6 แล้ว enter ระบบก็จะค้นหาให้อัตโนมัติ ตรงนี้ยังไม่ได้ครับ
ปัจจุบันเวลาคีย์รหัสอย่างเดียว เช่น 26401 ระบบมันก็จะแจ้งเตือนว่าค่าที่ป้อนไม่ถูกต้องครับ

[snasui]

ขออภัยที่แนบไฟล์ที่ยังไม่ได้ Update ในความเห็นก่อนนี้ก็เลยทำให้คุณ joo ยังพบว่าไม่สามารถคีย์ได้ครับ

ตอนนี้ผมปรับไฟล์ที่ส่งมาล่าสุดให้อีกรอบ การทำให้คีย์ได้และเลือกได้ให้ทำดังนี้ครับ

1. ที่ Sheet3 คลิก D6
2. เข้าเมนู Data > Validation
3. ที่แถบ Error Alert > ปลดเครื่องหมายตรง Show error alert after invalid entered > OK

นอกจากนี้ยังปรับสูตรที่ D10 เป็น

=IF(D6="","",VLOOKUP(LEFT($D6,5)+0,ข้อมูลบุคคล!$B$5:$E$20,3,1))

และที่ D12 เป็น

=IF(D6="","",VLOOKUP(LEFT($D6,5)+0,ข้อมูลบุคคล!$B$5:$E$20,4,1))

ดูไฟล์แนบประกอบครับ

[joo]

ขอบคุณครับอาจารย์ที่ช่วยแนะนำมาตลอด ตอนนี้ทำได้แล้วครับออกแบบปรับแต่งฟอร์มใช้ในหน่วยงานเล็กๆ ได้สบาย
จะลองเอาไปใช้ในองค์กรที่มีเจ้าหน้าที่ประมาณพันกว่าคนไม่รู้จะเหมาะสมหรือเปล่า....?

[snasui]

คิดว่าสามารถใช้ได้ ไม่น่าจะมีปัญหาครับ

[joo]

เจอปัญหาใหม่ครับที่ซีท Main และซีท "บันทึกการลา" ถ้าเราคีย์ข้อมูลที่เคยเก็บไว้แล้วในฐานข้อมูลซ้ำเข้าไปอีกการคำนวณค่าต่างๆที่ได้จะผิดไปจากความเป็นจริงมีวิธีไหนครับที่จะคอยดักเหตุการณ์นี้ไม่ให้บันทึกข้อมูลซ้ำเข้าไปอีก

[snasui]

ผมจะบอกเป็นแนวทางไปก่อนนะครับ แล้วลองปรับ Code ตามแนวทางนั้น ติดอะไรค่อยส่ง Code มาช่วยกันดูครับ

กรณีที่ไม่ต้องการให้มีค่าซ้ำ เพื่อให้ง่ายให้เขียนสูตรเพื่อนับว่ามีค่านั้นอยู่แล้วหรือไม่ไว้ในเซลล์ใด ๆ แล้วใช้เซลล์นี้อ้างอิงไปยังใน Code เช่นถ้านับแล้วพบว่ามี 1 ค่า ก็สามารถบันทึกได้ ถ้าพบว่ามีมากกว่า 1 ค่าก็ไม่ให้บันทึกครับ โดย 1 ค่าที่ว่านี้อาจจะเข้าหลายเงื่อนไข เช่น ปี, เดือน, ชื่อ, ประเภทการลา

[joo]

ผมลองหลายสูตรแล้วยังไม่ได้ตามเงื่อนไขที่อาจารย์แนะนำไว้เลยครับ สูตรที่ผมทดลองอยู่ที่ซีท"บันทึกการลา" N5:P7 แนะนำด้วยครับ

[snasui]

ลองดูตัวอย่างตามไฟล์แนบครับ

ชีท Main

1. เซลล์ E23 ตรวจสอบค่าว่าซ้ำหรือไม่คีย์

=SUM(IF(C21=บันทึกการลา!A5:A155,IF(E21=บันทึกการลา!C5:C155,IF(G21=บันทึกการลา!E5:E155,IF(H21=บันทึกการลา!F5:F155,IF(L21=บันทึกการลา!J5:J155,1))))))=1

Ctrl+Shift+Enter

2. เซลล์ E24 ตรวจสอบว่าเมื่อกรอกวันลาแล้วกรอกวันเริ่มต้นและวันสิ้นสุดหรือไม่ คีย์

=AND(D9="ลา",D12<>"",D13<>"")

Enter

จากนั้นปรับ Code VBA เพื่อตรวจสอบค่าจาก 2 เซลล์ ดูในไฟล์แนบประกอบครับ

[joo]

ขอบคุณครับอาจารย์เยี่ยมเลยครับสามารถดัก User ที่ชอบลืมได้ดีครับ
ที่ D12,D13 สามารถบังคับให้ User ใส่วันที่ตามรูปแบบนี้ 20/12/2010 ซ้อนกับ Varidation เดิมได้อย่างไรครับหรือต้องไปเขียนดักไว้ที่โค้ด VBA ครับ ?
มีข้อสงสัยครับที่ซีท "บันทึกการลา" ระหว่างสองสูตรนี้มันแตกต่างกันอย่างไรครับ พอดีเจอตอนทดลองหาวิธีนับข้อมูลครับ ค่าที่ได้มันเท่ากันเลยครับ
=IF(B5=Sheet4!$C$2,LOOKUP(9.99999999999999E+307,บันทึกการลา!M$4:M4)+1,"")
=IF(AND(B5=Sheet4!$C$2,Sheet4!$D$6<>""),COUNTIF(B$5:B5,B5),"")

[snasui]

เขียน Note บอก User ไว้ครับ บวกกับเขียน VBA ดักไว้อีกทีก็ได้ ถ้าจะทำก็ลองปรับ Code มาเองก่อนครับ แล้วเอาที่ปรับแล้วมาดูกัน

สำหรับสูตรที่ถามมาความหมายคนละเรื่องกันสิ้นเชิงครับ

=IF(B5=Sheet4!$C$2,LOOKUP(9.99999999999999E+307,บันทึกการลา!M$4:M4)+1,"")

ความหมายคือถ้า B5=Sheet4!$C$2 แล้วให้หาค่าตัวเลขสุดท้ายในช่วง บันทึกการลา!M$4:M4 แล้วบวกด้วย 1 หากไม่ใช่แล้วให้แสดงค่าว่าง

ส่วนสูตร

=IF(AND(B5=Sheet4!$C$2,Sheet4!$D$6<>""),COUNTIF(B$5:B5,B5),"")

ความหมายคือ ถ้า B5=Sheet4!$C$2 และ Sheet4!$D$6<>"" แล้ว ให้นับว่ามี B5 อยู่ในช่วง B$5:B5 อยู่เท่าไร หากไม่ใช่แล้วให้แสดงค่าว่าง

[joo]

เขียน Note บอกไว้ก็ Ok อยู่ครับแต่ป้องกัน User Error ก็น่าจะดีครับ ต้องปรับโค้ด VBA อย่างไรครับ

[snasui]

ผมเพิ่ม Calendar ให้ ดู Code ที่ชีท Main และที่ Module1 ตามไฟล์แนบครับ

[joo]

ขอบคุณครับ ทดลองดูแล้วก็ OK ครับ
แต่ถ้าหากว่า User ถนัดการคีย์ข้อมูลแล้วเผลอคีย์ข้อมูลเป็นแบบปี พ.ศ.อีก เราดักโค้ดบังคับให้ป้อนในรูปแบบที่เรากำหนดได้อย่างไรครับ

[snasui]

ผมบอกเป็นแนวทาง เพื่อเป็นทางเลือกแล้วกันครับ ส่วน Code ก็ลองเขียนตามแนวคิดนี้ดูครับ

การให้ User คีย์เอง จะสามารถคีย์ค่าใด ๆ ก็ได้ สามารถคีย์ปีแค่ 2 หลักก็ได้ ถ้าคีย์ 2 หลักโอกาสเลขปีจะเป็น 19XX ก็จะเกิดขึ้น ถ้าคีย์ปีเป็น พ.ศ. โอกาสเลขปีจะเป็น 25XX ก็จะเกิดขึ้นครับ

หรือหากปีที่กรอกจะต้องเท่ากันกับปีปัจจุบันเสมอ ก็สามารถดักค่าด้วยฟังก์ชั่น Year ใน VBA ได้ เช่น

Code: Select all

If Year(Range("D12")) <> Year(Date) or Year(Range("D13")) <> Year(Date) then
Msgbox "......" <-- กรอกคำบรรยายที่ต้องการ
Exit Sub

[joo]

ลองทดสอบดูแล้วครับก็พอเตือนได้อยู่ครับแต่โอกาสที่จะคีย์ปีเป็น 2 หลักก็จะทำให้ค่าที่แสดงผิดพลาด สำหรับสูตรที่อาจารย์แนะนำถ้าต้องการให้ใส่ค่าปีได้ตั้งแต่ปีที่น้อยกว่าถึงปีที่มากกว่าได้ต้องปรับโค้ดอย่างไรครับ
ผมทดลองทำได้แค่ปีที่น้อยกว่าถึงปีปัจจุบันแต่ถ้าใส่ปีที่มากกว่ามันไม่ได้ครับ เช่น คีย์ แบบนี้ 01/12/2009,/12/2010 สามารถคีย์ข้อมูลได้ ส่วนถ้าคียเป็น พ.ศ. 01/12/2553 ระบบแจ้งเตือนได้ ถ้าให้สามารถคีย์ค่าปีให้มากกว่าได้ เช่น 05/01/2011 ระบบจะเตือนไม่ยอมครับ โค้ดที่ผมปรับใหม่ครับตามนี้ครับ
If Year(Range("D12")) <= Year(Date) Then
Else
Year (Range("D12")) <> Year(Date)
MsgBox "กรอกวันที่แบบปี ค.ศ. เท่านั้น"
End If