Excel Forum

joo wrote:ขอคุณครับอาจารย์ ที่ซีท 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 ลงด้านล่าง

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

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

ที่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 ให้ใหม่เพื่อเป็นแนวทางต่อไปครับ

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

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

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

ขออภัยที่แนบไฟล์ที่ยังไม่ได้ 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))

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

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

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

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

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

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

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

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

ชีท 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 เซลล์ ดูในไฟล์แนบประกอบครับ

ขอบคุณครับอาจารย์เยี่ยมเลยครับสามารถดัก 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),"")

เขียน 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 อยู่เท่าไร หากไม่ใช่แล้วให้แสดงค่าว่าง

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

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

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

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

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

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

ลองทดสอบดูแล้วครับก็พอเตือนได้อยู่ครับแต่โอกาสที่จะคีย์ปีเป็น 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