Lookup ข้อมูล

[snasui]

ผมเข้าใจว่าการระบุว่าน้อยกว่าปีปัจจุบันและมากกว่าปีปัจจุบันได้นั้นต้องมีการกำหนดว่าน้อยกว่าแต่ไม่เกินปีใด และหากมากกว่าต้องไม่เกินปีใด

สมมุติว่าเงื่อนไขคือ สามารถกรอกค่าปีน้อยกว่าปีปัจจุบันคือ 2010 ได้ แต่ต้องไม่น้อยกว่าปีปัจจุับันเกินกว่า 10 ปีคือไม่น้อยไปกว่าปี 2000
และหากมากกว่าปีปัจจุบันก็ไม่ควรเกิน 1 ปี คือไม่เกินปี 2011 ก็สามารถกำหนดดังนี้ครับ

Code: Select all

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

[joo]

ขอบคุณครับ ทดลองแล้วใช้ได้ดีครับ ตอนแรกผมก็ลองบวกลบมาแนวเดียวกับที่แนะนำทำไมถึงไม่ได้ก็ไม่รู้ สงสัยเส้นผมมันจะบังภูเขาไว้จริงๆ ครับ

[joo]

อาจารย์ครับ...เวลาคีย์ข้อมูลยอดสะสมแล้วกดบันทึกจะเจอ bug ที่บรรทัดนี้ครับ
If Year(Range("D12,D13")) < Year(Date) - 10 Or Year(Range("D12,D13")) > Year(Date) + 1 Then
ถ้าหากเราไม่คีย์ข้อมูลวันลาสะสมลงในฐานข้อมูลเราจะสรุปข้อมูลยอดสะสมและคงเหลือของแต่ละบุคคลในแต่ละปีอย่างไรครับโดยมีเงื่อนไขตามซีท"ข้อมูลบุคคล"

[snasui]

การเขียนเงื่อนไขเขียนได้หลายแบบครับ ตัวอย่างตามด้านล่างครับ

Code: Select all

If (Year(Range("D12")) < Year(Date) - 10 Or Year(Range("D13")) < Year(Date) - 10) Or _
    (Year(Range("D12")) > Year(Date) + 1 Or Year(Range("D13")) > Year(Date) + 1) Then
   MsgBox "..."
       Exit Sub
    End If

ส่วนวันลาสะสม วันลาคงเหลือ เราหาได้ทุกเมื่อด้วยสูตรง่าย ๆ เช่น Sumif หรือ Sumproduct เพื่อหาวันลาแบบหลายเงื่อนไข หรือจะทำวันลาสะสมแยกไว้อีก 1 ตารางแยกรายปี รายพนักงาน ก็น่าจะทำได้ เมื่อสรุปเป็นรายงานก็ค่อยนำมาคำนวณร่วมกัน ลองประยุกต์ดูครับ หากติดปัญหาสามารถถามมาได้เรื่อย ๆ ครับ

[joo]

อาจารย์ครับ...สงสัยผมจะอธิบายไม่ละเอียดคือว่า ที่ซีท "Main" ถ้า D9 = ลา คีย์ข้อมูลวันที่เสร็จกดบันทึกระบบสามารถเก็บข้อมูลได้ปกติ แต่ถ้าหาก D9 = ยกมา D12,D13 เป็นค่า " - " แบบนี้ตามที่กำหนดไว้ เวลากดบันทึกระบบจะไม่ยอมจะเกิด bugครับ

[snasui]

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

Code: Select all

    If Sheets("Main").Range("D9") = "ลา" Then
        If (Year(Range("D12")) < Year(Date) - 10 Or Year(Range("D13")) < Year(Date) - 10) Or _
             (Year(Range("D12")) > Year(Date) + 1 Or Year(Range("D13")) > Year(Date) + 1) Then
                MsgBox "..."
                Exit Sub
        End If
    End If

[joo]

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

[snasui]

ผมปรับเป็นสูตร Vlookup เพื่อดึงข้อมูลมาให้ตามต้องการ ดูตัวอย่างในไฟล์แนบครับ

หากยังไม่ตรงตามต้องการหรือมีเงื่อนไขอื่นใดเพิ่มเติมแจ้งเพิ่มเ้ข้ามาได้ครับ

การสร้างสูตรเพื่ออ้างอิงค่าจากตาราง PivotTable ให้คีย์ตำแหน่งเซลล์เข้าไปเองแทนการคลิกเลือกมาจากตาราง PivotTable ครับ

[joo]

ค่าที่ลิงค์มายังไม่ค่อยตรงครับ
ที่ Sheet3 D19,H19 เวลาเปลี่ยนปี พ.ศ.แล้วค่าที่แสดงไม่ยอมเปลี่ยนแปลงครับ
ที่ Sheet 5 ตรง H13:H19 ค่าที่ลิงค์มาไม่ตรงครับ ผมลองปรับสูตรใหม่แต่ถ้ามีการเพิ่มข้อมูลของแต่ละบุคคลเข้ามาค่าที่ลิงมาจะไม่ตรงครับ
หรือว่าผมต้องแยกซีทสำหรับเก็บวันลาสะสมในแต่ละปีของบุคคลไว้อีกต่างหากแบบ Sheet6 ดีครับ

[snasui]

ในชีท 3 ลิ้งมาจากสรุปข้อมูลบุคคล แต่สิ่งที่ต้องการคือให้ดูเงื่อนไขปีด้วย ดังนั้น ในชีท สรุปข้อมูลบุคคลจะต้องมีปีให้อ้างอิง ซึ่งสามารถทำเป็น Database ลงไปข้างล่างเหมือนกับ Database อื่น ๆ ครับ ไม่เช่นนั้่นจะไม่สามารถดึงค่าที่อ้างอิงกับปีได้ และการทำ Database จะต้องเรียงลงด้านล่างเสมอครับ ไม่ใช่เรียงตาม Sheet6 ครับ

[joo]

อาจารย์ครับ...ที่Sheet3 ตรง D19 ถ้าเราลิงค์มาจาก Pivotable หรือจาก ซีท บันทึกการลาได้ไหมครับ ผมลองลิงค์มาจาก Sheet2 ค่าที่แสดงเปลี่ยนตามปีแต่ไม่ยอมเปลี่ยนตามรหัสบุคคลครับ
ที่ Sheet5 ข้อมูลไม่ข้อมูลไม่ UpDate ตามการเพิ่มของข้อมูลครับ

[snasui]

ลองดูในไฟล์แนบซึ่งผมตอบไว้ในชีทที่เขียนถามมาแล้วครับ

สูตรสำหรับการดึงข้อมูลตามเงื่อนไขในตัวอย่างจะเป็นสูตร Array การกดแป้นให้รับสูตรจะต้องกดแป้น Ctrl+Shift ค้างไว้แ้ล้วตามด้วย Enter ครับ สำหรับการปรับปรุงเปลี่ยนแปลงสูตรพวกนี้ก็ต้องกดแป้นลักษณะเดียวกับตอนสร้างครับ

[joo]

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

[snasui]

ทำตารางวันลาสะสมเป็นฐานข้อมูลไว้ต่างหากครับ เช่นตามตารางด้านล่าง แล้วค่อยดึงไปใช้กับข้อมูลอื่น ๆ

ปีล่าง ๆ ก็ไม่จำเป็นต้องดึงข้อมูลจากปีด้านบนเหมือนที่ต้องการจะทำ เพราะมันทำให้สับสนได้ครับ ในฐานข้อมูลสำหรับวันลาสะสมเก็บข้อมูลเป็น Value ไม่ต้องเป็นสูตร ข้อมูลคงเหลือยกมาปีถัดไปก็คือข้อมูลคงเหลือปีนี้ สามารถ Copy ไปวางได้เลยครับ

รหัสบุคคล ปี ลาพักร้อนสะสม
26401 2553 20
26402 2553 15
26403 2553 12
26404 2553 18
26405 2553 15
26406 2553 5
26407 2553 0
26408 2553 0
26409 2553 0
26410 2553 0
26411 2553 0
26401 2554 23
26402 2554 21
26403 2554 17
26404 2554 26
26405 2554 21
26406 2554 13
26407 2554 0
26408 2554 0
26409 2554 0
26410 2554 0

[snasui]

ผมทำตัวอย่างสูตรสำหรับการดึงมาใช้ในรูปแบบที่คุณต้องการตามด้านล่างครับ

ที่ Sheet5

1. เซลล์ I17 คีย์

=LOOKUP(2,1/(($H17-1<=H$6:H16)*(C$6:C16=$C17)),K$6:K16)

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

2. เซลล์ D48 คีย์

=LOOKUP(2,1/(($B48-1<=B$37:B47)*($C48=C$37:C47)),$G$37:$G$47)

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

[joo]

ขอบคุณครับ ได้ทดลองแล้วใช้งานได้ตามที่ต้องการครับ

[joo]

อาจารย์ครับผมทำ Validation แบบสัมพันธ์กันที่ซีท Main ตรง ที่D5 เดิมทีมีข้อมูลโชว์อยู่แต่ถ้าคลิกเปลี่ยนหน่วยงานใหม่ต้องการให้ D6,D7เป็นค่าว่างเลยทำได้ไหมครับ
ที่D7 เงื่อนไขให้ใส่รหัสอย่างเดียวก็ได้และถ้าคลิกเลือกข้อมูลในรายการแล้วให้แสดงเฉพาะรหัสอย่างเดียวทำอย่างไรครับ
มีข้อสงสัยครับทำไมผมค้นหาข้อมูลด้วยชื่อแต่รหัสจึงไม่ยอมแสดงครับ

[snasui]

การทำ Validation ที่ D5 ไม่สามารถทำอย่างที่ต้องการได้ครับ นั่นคือถ้า D6,D7 มีข้อมูลอยู่ก็มีอยู่อย่างนั้นจนกว่าจะไปเลือกเปลี่ยนเอง ถ้าจะให้เปลี่ยนเมื่อคลิกเลือก D5 ก็ต้องใช้ VBA ครับ ซึ่งก็ไม่ยากครับ สามารถเขียน Code ที่ชีท Main ตามด้านล่าง

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$D$5" Then
    Call ChangeCellValue
End If
Application.EnableEvents = True
End Sub

และเขียนที่ Module1 ตามด้านล่าง

Code: Select all

Sub ChangeCellValue()
With Sheets("Main")
    .Range("D6") = ""
    .Range("D7") = ""
End With
End Sub

ส่วน Vlookup ที่ L11 สูตรไม่ถูกต้องครับ ที่ถูกต้องคือ

=IF(L10="","",VLOOKUP($L10,Personal!$J$5:$L$24,1,1))

[joo]

ที่ D5 ทดลองแล้วใช้งานได้ดีครับ
เงื่อนไขตรงที่D7 ใส่รหัสอย่างเดียวก็ได้และถ้าคลิกเลือกข้อมูลแล้วให้แสดงเฉพาะรหัสอย่างเดียวตรงนนี้ทำได้ไหมครับ
ส่วนVlookup ที่ L11 สูตรไม่ถูกต้องจริงๆครับ ที่ต้องการคือค้นหาชื่อแล้วให้แสดงรหัส
ผมลองเปลี่ยนสูตรที่ L11 ของซีท Main แบบนี้ครับ =IF(L10="","",VLOOKUP($L10,Personal!$H$5:$L$24,1,1))ค่าที่ได้คือ #N/A

[snasui]

Vlookup ค้นหาจากขวาไปซ้ายไม่ได้ครับ ค้นหาจากซ้ายไปขวาได้เท่านั้น ดูคำอธิบาย Vlookup ได้ที่นี่ครับ http://snasui.blogspot.com/2009/12/vlookup.html

สำหรับกรณีนี้สามารถใช้ Index ร่วมกับ Match เพื่อดึงข้อมูลมาแสดง จะได้สูตรเป็น

=IF(L10="","",INDEX(Personal!$H$5:$H$24,MATCH(L10,Personal!$J$5:$J$24,0)))

จะเป็นการค้นหาด้วยชื่อแล้วให้รหัสมาแสดง

ส่วนตรง D7 ต้องการใส่รหัสอย่างเดียวผมคิดว่าคุณ Joo สามารถทำได้เองอยู่แล้ว เพราะจำได้ว่าคุณ Joo เคยถามเกียวกับให้นำรหัสและชื่อมาแสดงด้วยกัน ผมได้แนะนำให้นำรหัสและชื่อมาเชื่อมกันก่อน จากนั้นนำเข้ามาใน Validation ได้ นั่นแสดงว่าทราบวิธีการแล้ว เพราะใช้วิธีการเดียวกันครับ

ตอนนี้รหัสและชื่ออยู่ที่คอลัมน์ I ในชีท Personal หากต้องการนำรหัสอย่างเดียวมาแสดงก็เพียงแต่เปลี่ยนจากคอลัมน์ I ในชีท Pesonal มาเป็น H มาแสดงใน Validation เท่านั้น