Lookup ข้อมูล

[joo]

ซีท StockDay เป็น database ของวันลาสะสมครับ ลืมไปว่าข้อมูลใน database ไม่ควรมีสูตร
ตอนแรกทำสูตรไม่รู้จะอ้างอิงเงื่อนไขอย่างไรจึงลองใช้คอลัมภ์ D ในซีท Personal มาอ้างอิงครับครับ
เพราะแต่ละรหัสบุคคลจะมีสิทธิสะสมวันลาไม่เหมือนกัน เช่น
รหัส 10101 มีสิทธิสะสมได้แต่ไม่เกิน 30
รหัส 20101 สะสมไม่ได้
หรือว่าในตาราง StockDay ยังไม่ต้องสนใจเงื่อนไขนี้เก็บข้อมูลวันลาสะสมไปเรื่อยๆ เวลาเอาไปใช้จึงค่อยใส่เงื่อนไขเข้าไปน่าจะดี

[snasui]

แต่ละรหัสบุคคลจะมีสิทธิสะสมวันลาไม่เหมือนกัน เช่น
รหัส 10101 มีสิทธิสะสมได้แต่ไม่เกิน 30
รหัส 20101 สะสมไม่ได้
หรือว่าในตาราง StockDay ยังไม่ต้องสนใจเงื่อนไขนี้เก็บข้อมูลวันลาสะสมไปเรื่อยๆ เวลาเอาไปใช้จึงค่อยใส่เงื่อนไขเข้าไปน่าจะดี

ต้องพิจารณาเรื่องความสะดวกของการนำไปใช้ครับ ถ้าหากว่าใส่เงื่อนไขต่าง ๆ ไว้ครบ ตอนนำไปใช้ก็จะง่ายลงครับ

ผมทำตัวอย่างการปรับชีท StockDay และการทำไปใช้ที่ชีท Report1 มาให้ตามไฟล์แนบ

[joo]

ขอบคุณครับ...แล้วค่าต่างๆ ในซีท StockDay มาได้อย่างไรครับ

[snasui]

คำถามที่อาจจะมีได้คือแล้วข้อมูลในชีท StockDay มาได้อย่างไร คำตอบคือ Copy มาวางเฉพาะ Values หรือหากไม่อยาก Copy ด้วย Manual ก็สามารถเขียน VBA ให้นำมาวางก็ย่อมได้ครับ

สำหรับไฟล์นี้ผม Copy เป็น Value มาให้ครับ

[joo]

อาจารย์ครับ...ไฟล์ที่ Copy มาเป็น Value ของซีท StockDay นี้ เอามาจากซีทไหนครับ ถ้าหากเอามาจากซีท Report1
ข้อมูลไม่น่าจะตรงครับ สมมุติว่าเราลบข้อมูลในซีท RecordData ออกให้หมด แล้วเริ่มบันทึกข้อมูลใหม่ ข้อมูลที่ซีท Report1 เซลล์ D6 ลิงค์ข้อมูลมาได้อย่างไรครับ

[snasui]

จริง ๆ คุณ Joo ต้องทราบเองครับว่าข้อมูลที่ถูกต้องนั้นเอามาจากไหนที่จะยกมาจากปีก่อนเพื่อให้เป็นปีปัจจุบัน ซึ่งผมทำตัวอย่างไว้ให้แล้วว่าจะวางข้อมูลอย่างไร

ที่ผม Copy ไปเป็นยอดยกไปของปี 52 และยกมาของปี 53 คือที่ระบายสีชมพูไว้ตามด้านล่างครับ

Example.png

ส่วนสีเขียวด้านล่างคือที่ Copy ไปเป็นยอดยกไปของปี 53 และเป็นยอดยกมาของปี 54 ซึ่งที่เหลือก็จะใช้ช่องนี้ไปทำแต่เป็นช่วงเซลล์ของปีต่อ ๆ ไป

Example1.png

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

[joo]

อุ้ย... สงสัยผมจะอธิบายไม่ละเอียด ผมลองทำข้อมูลมาใหม่นะครับโดยกรอกข้อมูลเฉพาะปีงบประมาณ 2553 อย่างเดียวก่อน ที่ซีท StockDay ถ้าไม่ Copy ข้อมูลจาก ซีท
Personal คอลัมภ์ T มาใส่ไว้ ข้อมูลที่ซีท Report1 ก็จะคำนวณไม่ถูกต้องครับ

[snasui]

ผมอ่านแล้วไม่เข้าใจนะครับ ไม่ทราบว่าปัญหาคืออะไรครับ

เมื่อต้องการทำ Report1 ข้อมูลต้นแหล่งที่จะอ้างอิงมาซึ่งอยู่ในชีท StockDay ก็ต้องเตรียมไว้ก่อน

ข้อมูลจากชีท StockDay จะต้อง Update ก่อนที่จะนำไปใช้เสมอ ส่วนจะนำข้อมูลมาจากไหนก็แล้วแต่ว่าข้อมูลที่ถูกต้องอยู่ทีไหนก็เอามาจากที่นั่นครับ

[joo]

ผมอ่านแล้วไม่เข้าใจนะครับ ไม่ทราบว่าปัญหาคืออะไรครับ

ปัญหาคือ การเตรียมข้อมูลในซีท StockDay ครับ ที่ซีท StockDay ถ้าตอนแรกคีย์ข้อมูลที่ยกยอดมาจากปีงบประมาณ 2552 ใส่ให้ครบทุกรหัสบุคคลไว้ก่อน เมื่อเราทำการคีย์ข้อมูลที่ซีท Main แล้วมีการบันทึกข้อมูลไปเก็บไว้ที่ซีท RecordData ก็ให้ข้อมูลในซีท StockDay อัพเดทตามทุกครั้งครับ พอมีแนวทางเป็นไปได้ไหมครับ

[snasui]

เมื่อเราทำการคีย์ข้อมูลที่ซีท Main แล้วมีการบันทึกข้อมูลไปเก็บไว้ที่ซีท RecordData ก็ให้ข้อมูลในซีท StockDay อัพเดทตามทุกครั้งครับ พอมีแนวทางเป็นไปได้ไหมครับ

ก็ต้องดูเรื่องงวดการบันทึกข้อมูลว่าเป็นงวดเดียวกันหรือไม่ครับ

ถ้าตามที่ผมเข้าใจชีท Main เป็นการบันทึกข้อมูลทุกครั้งที่มีการลาเพื่อบันทึกลงฐานข้อมูลที่ชีท RecordData ยอดคงเหลือวันลาในแต่ละครั้งที่บันทึกไม่ใช่ยอดที่จะยกไปในปีต่อไป ต้องรอจนกว่าจะถึงสิ้นปี เมื่อขึ้นวันที่ 1 ของปีใหม่ จึงค่อยบันทึกยอด StockDay เช่นนี้แล้วก็ย่อมไม่สามารถที่จะ Update ชีท StockDay ตามการ Update ชีท RecordData ครับ

[joo]

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

[snasui]

ผมปรับ Code เป็นตัวอย่างมาให้ตามด้านล่างครับ

Code: Select all

Sub PasteData()
Dim rSource As Range
Dim rTarget As Range
Dim Lc As Integer, Ac As Integer, Af As Integer
Lc = Sheets("StockDay").Range("A65536").End(xlUp).Value + 1
With Worksheets("Report1")
    Ac = Application.CountIf(.Range("B:B"), Lc)
    Af = Application.Match(Lc, .Range("B:B"), 0)
End With
    Application.ScreenUpdating = False
    Set rSource = Sheets("Report1").Range( _
        "B" & Af & ":C" & Ac + Af - 1 & ",G" & Af & ":G" & Ac + Af - 1)
    Set rTarget = Sheets("StockDay").Range("A65536").End(xlUp).Offset(1, 0)
    rSource.Copy
    rTarget.PasteSpecial xlPasteValues
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    MsgBox "Record Complete"
End Sub

[godman]

ขอบคุณครับ ผมพบทองแล้ว ระบบนี้ดีมากเพราะผมอยากทำแบบ pivot อยู่แล้วอยากได้แบบอัตโนมัตแต่ไม่ได้ขอความรู้สักที จึงตรงกับความต้องการของผมพอดี ขอบคุณครับสำหรับเจ้าของกระทู้ด้วย

[joo]

ขอบคุณครับ....ได้ทดลองแล้วสามารถใช้งานได้ดีครับ
แต่ว่าถ้าเกิดเรากด UpdateStock หลายๆ ครั้งจนไม่มีข้อมูลที่จะให้ Copy มันจะ Error ที่บรรทัดนี้ครับ
Af = Application.Match(Lc, .Range("B:B"), 0) ผมจึงปรับโค๊ดใหม่แบบนี้ครับ

Code: Select all

Sub PasteData()
Dim rSource As Range
Dim rTarget As Range
Dim Lc As Integer, Ac As Integer, Af As Integer
On Error Resume Next
Lc = Sheets("StockDay").Range("A65536").End(xlUp).Value + 1
With Worksheets("Report1")
    Ac = Application.CountIf(.Range("B:B"), Lc)
    Af = Application.Match(Lc, .Range("B:B"), 0)
End With
   Application.ScreenUpdating = False
    Set rSource = Sheets("Report1").Range( _
        "B" & Af & ":C" & Ac + Af - 1 & ",G" & Af & ":G" & Ac + Af - 1)
    Set rTarget = Sheets("StockDay").Range("A65536").End(xlUp).Offset(1, 0)
    rSource.Copy
    rTarget.PasteSpecial xlPasteValues
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
           MsgBox "Record Complete"
    If rSource.Copy = False Then
          MsgBox "No Data Record "
     End If
      
End Sub

ก็พอที่จะหยุด Error ได้ ไม่แน่ใจว่าทำถูกวิธีหรือเปล่าเพราะว่า ข้อความยังแจ้งเตือนว่า "Record Complete" ตลอดทั้งๆ ที่ไม่มีข้อมูลจะให้ Copy
และที่ซีท StockDay เราสามารถอ้างอิงที่คอลัมภ์ A ได้ไหมครับ ทำไมต้องอ้างอิงที่คอลัมภ์ E ผมลองให้อ้างอิงที่คอลัมภ์ A โดยใส่ข้อมูลตั้งแต่ A2:A10 เป็น 2553 ทำการ UpdateStockDay ปรากฎว่าค่าที่ได้ในซีท StockDay จะออกมาไม่ตรงกับข้อมูลในซีท Report1 ของคอลัมภ์ G ครับ

[snasui]

ลองดูตัวอย่างการปรับ Code ตามด้านล่างครับ

Code: Select all

Sub PasteData()
Dim rSource As Range
Dim rTarget As Range
Dim Lc As Integer, Ac As Integer
Application.ScreenUpdating = False
Lc = Sheets("StockDay").Range("A65536").End(xlUp).Value + 1
With Worksheets("Report1")
    Ac = Application.CountIf(.Range("B:B"), Lc)
    Af = Application.Match(Lc, .Range("B:B"), 0)
End With
If Ac = 0 Then
    MsgBox "No data for paste."
    Exit Sub
End If
    Set rSource = Sheets("Report1").Range( _
        "B" & Af & ":C" & Ac + Af - 1 & ",G" & Af & ":G" & Ac + Af - 1)
    Set rTarget = Sheets("StockDay").Range("A65536").End(xlUp).Offset(1, 0)
    rSource.Copy
    rTarget.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Record Complete"
End Sub

[joo]

ขอบคุณครับอาจารย์...ทดลองแล้วสามารถทำงานได้ดีครับ
แล้วคำถามข้อนี้ครับ

และที่ซีท StockDay เราสามารถอ้างอิงที่คอลัมภ์ A ได้ไหมครับ ทำไมต้องอ้างอิงที่คอลัมภ์ E ผมลองให้อ้างอิงที่คอลัมภ์ A โดยใส่ข้อมูลตั้งแต่ A2:A10 เป็น 2553 ทำการ UpdateStockDay ปรากฎว่าค่าที่ได้ในซีท StockDay จะออกมาไม่ตรงกับข้อมูลในซีท Report1 ของคอลัมภ์ G ครับ

[snasui]

คุณ Joo ดูตรงไหนครับถึงพบว่าอ้างคอลัมน์ E หากเป็นสูตรที่ชีท Report1 ตรงคอลัมน์ D คำตอบคือ สามารถทำได้ครับ

โดยที่ D6 เขียนสูตรเป็น

=LOOKUP(9.9999999999999E+307,CHOOSE({1,2},0,INDEX(StockDay!$C$2:$C$34,MATCH(1,IF(StockDay!$A$2:$A$34=B6-1,IF(StockDay!$B$2:$B$34=C6,1)),0))))

Ctrl+Shift+Enter > Copy ลงด้านล่าง

[joo]

คุณ Joo ดูตรงไหนครับถึงพบว่าอ้างคอลัมน์ E

ที่ D6 สูตรนี้ครับ
=LOOKUP(9.9999999999999E+307,CHOOSE({1,2},0,INDEX(StockDay!$C$2:$C$34,MATCH(1,IF(StockDay!$E$2:$E$34=Report1!B6,IF(StockDay!$B$2:$B$34=Report1!C6,1)),0))))
แต่ว่าได้ลองสูตรที่อาจารย์แนะนำใหม่ก็สามารถใช้งานได้ดีครับ
- ถ้า User กดปุ่ม "updateStock" ก่อนหมดปีงบประมาณ ทำให้ข้อมูลของบุคคลที่คีย์เข้าไปใหม่ในปีงบประมาณนั้นไม่สามารถที่จะ UpdateStock ได้
จะต้องมาลบข้อมูลของปีนั้นในซีท StockDay ออกก่อนและทำการ UpdateStock ใหม่ ข้อมูลในซีท StockDay จึงจะ Update ตาม พอจะมีวิธีแก้ไขไหมครับ

[snasui]

ช่วยหาเงื่อนไขมาให้ด้วยครับว่าจะดักด้วยเงื่อนไขใดที่จะไม่ให้วางข้อมูลซ้ำจากกรณีดังกล่าว

[joo]

อาจารย์ครับ...คิดหาเงื่อนไขที่เหมาะสมยังไม่ได้เลยครับ
1. ถ้าใช้ปีงบประมาณมาเป็นตัวกำหนด คือ ทุกวันที่ 1 ต.ค.ของทุกปีซึ่งเป็นวันเริ่มต้นปีงบประมาณใหม่จึงค่อยกดปุ่ม updateStock ได้
ตัวอย่างเช่น ปีงบประมาณ 2554 มีการบันทึกข้อมูลระหว่างปีไปเรื่อยๆ พอถึงวันที่ 1 ต.ค.54 ก็ให้สามารถกดปุ่ม updateStock เพื่อนำข้อมูลไปเก็บใน
ซีท StockDayได้ แล้วถ้าเกิดมีการแก้ไขข้อมูลการลา เช่น บันทึกวันที่ลาผิดไปต้องมาแก้ไขวันลาใหม่ก็จะไม่สามารถ update ข้อมูลได้
2. ใช้ปีงบประมาณเหมือนกัน โดยให้เลือกปีงบประมาณก่อนทุกครั้งที่จะทำการ update ข้อมูล น่าจะดีกว่าไหมครับ