Lookup ข้อมูล

[snasui]

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

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

และถ้าต้องให้ผู้ใช้แก้ไขเองต้องเขียนระบบให้รองรับ เช่นการโพสต์บัญชี การปรับปรุงบัญชี แต่ต้องผ่านการอนุมัติการบันทึกบัญชีของผู้มีอำนาจสำหรับเรื่องนั้น ๆ

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

[joo]

ทดลองทำโค๊ดมาหลายวันแล้วยังไม่ได้เลยครับช่วยแนะนำด้วยครับ

Code: Select all

Sub PasteData()
Dim rSource As Range
Dim rTarget As Range
Dim Lc As Integer, Ac As Integer
Dim FisYear As String
FisYear = InputBox("Please enter Year", "Year for UpdateStock")
    If FisYear = "" Then
        Exit Sub
    End If
    Range("A11:C60").Select
    Selection.ClearContents
Application.ScreenUpdating = True
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
    MsgBox "Record Complete"
    Application.ScreenUpdating = True
End Sub

[snasui]

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

[joo]

เงื่อนไขที่คิดไว้คือเมื่อต้องการที่จะ Update ข้อมูลในซีท StockDay ก็ให้เลือกว่าจะ Update เป็นของปีงบประมาณไหนหรือว่าจะเลือกทั้งหมด
หากในซีท StockDayมีข้อมูลของปีที่เลือกแล้วให้แจ้งเตือนว่ามีข้อมูลอยู่แล้วต้องการวางทับหรือไม่ ประมาณนี้ครับ

[snasui]

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

Code: Select all

Sub EditOldStockDay()
Dim rs As Integer, i As Integer
Dim j As Integer, k As Integer
On Error Resume Next
rs = InputBox("Enter year for copy.")
i = Application.Match(rs, Worksheets("Report1") _
    .Range("B:B"), 0)
j = Application.CountIf(Worksheets("Report1") _
    .Range("B:B"), rs)
k = Application.Match(rs, Worksheets("StockDay") _
    .Range("A:A"), 0)
If j = 0 Then
    MsgBox "Year not found."
    Exit Sub
End If
Worksheets("Report1").Cells(i, 2).Resize(j).Copy
Worksheets("StockDay").Cells(k, 1).PasteSpecial xlPasteValues
Worksheets("Report1").Cells(i, 3).Resize(j).Copy
Worksheets("StockDay").Cells(k, 2).PasteSpecial xlPasteValues
Worksheets("Report1").Cells(i, 7).Resize(j).Copy
Worksheets("StockDay").Cells(k, 3).PasteSpecial xlPasteValues
Application.CutCopyMode = False
MsgBox "Finish."
End Sub

[joo]

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

[joo]

ใช้งานไประยะหนึ่งพบปัญหาเพิ่มครับ คือว่าเมื่อมีบุคคลย้ายเพิ่มเข้ามาในหน่วยงานทำให้ผลการคำนวณที่ซีท Report1 ,Report3 แสดงค่าผิดพลาดครับ
ต้องปรับแก้ไขสูตรอย่างไรดีครับ

[snasui]

จากเงื่อนไขในไฟล์แนบผมลอง Filter ดูพบว่าให้คำตอบที่ถูกต้องแล้วครับ

[joo]

อาจารย์ครับยังไม่ตรงกับเงื่อนไขที่ต้องการครับ

เมื่อมีบุคคลย้ายเพิ่มเข้ามาในหน่วยงานทำให้ผลการคำนวณที่ซีท Report1 ,Report3 แสดงค่าผิดพลาดครับ

[snasui]

จะทราบได้อย่างไรว่ามีบุคคลใหม่เข้ามาครับ

ช่วยแนบไฟล์ในสภาพที่แสดงค่าไม่ถูกต้องมาครับ เพื่อน ๆ จะได้ช่วยกันหาสาเหตุได้ครับ

[bank9597]

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

[joo]

จะทราบได้อย่างไรว่ามีบุคคลใหม่เข้ามาครับ

ดูได้จากซีท Personal ครับ รหัส 50203 ได้เพิ่มเข้ามาในระบบและรหัสนี้ก็ได้มีการลาเกิดขึ้นทำให้ซีทสรุป Report1 และ Report3 เกิด Error ครับ

[snasui]

ชีท Report1 ดึงข้อมูลมาจากชีท Stockday และพบว่าชีท Stockday ไม่มีรหัส 50203 จึงมีค่าเป็น 0

และเมื่อคลิกปุ่ม UpdateStock ที่ชีท Stockday จะเป็นการเพิ่มข้อมูลปีงบประมาณ 2555 เข้ามาที่ A32:D36 ในชีท Stockday โดยรหัส 50203 จะอยู่ที่เซลล์ B36

ในชีท Report1 เซลล์ D31 เป็นการดึงยอดยกมาจากงบประมาณปี 2554 (2555-1) จึงแสดงรหัส 50203 ด้วยยอดสะสมยกมาเป็น 0 ซึ่งถูกต้องแล้ว เพราะว่ารหัส 50203 ไม่มียอดยกไปในปีงบประมาณ 2554

[joo]

ผมขออธิบายรายละเอียดใหม่นะครับ...คือแบบนี้ครับในช่วงปีงบประมาณ 2554 บุคคลรหัส 50203 ยังไม่ได้ย้ายเข้ามาทำงานในแผนกนี้ แต่พอปีงบประมาณ 2555 บุคคลรหัส 50203 ได้ย้ายเข้ามาทำงานที่แผนกนี้และมีวันลาพักร้อนสะสมมาด้วยรายละเอียดดังซีท Personal เซลล์ T5 มียอดสะสมมา 8 วัน และเมื่อบุคคลรหัสนี้ได้ทำการลาไป 2 วัน ยอดที่แสดงในซีท Report1 และ Report3 ที่เซลล์ D31 ก็ควรจะแสดงยอดสะสมยกมาเป็น 8 วัน แต่ระบบที่ใส่สูตรเดิมไว้กลับคำนวณผิดพลาด ซึ่งถ้ามีบุคคลเพิ่มเข้ามาในซีท Personal อีกสัก 2-3 คน ข้อมูลของบุคคลที่เพิ่มเข้ามาก็จะคำนวณผิดพลาดหมดตรงนี้ครับคือปัญหาจะต้องปรับแก้ไขสูตรอย่างไรดีครับ

[snasui]

คือแบบนี้ครับในช่วงปีงบประมาณ 2554 บุคคลรหัส 50203 ยังไม่ได้ย้ายเข้ามาทำงานในแผนกนี้ แต่พอปีงบประมาณ 2555 บุคคลรหัส 50203 ได้ย้ายเข้ามาทำงานที่แผนกนี้และมีวันลาพักร้อนสะสมมาด้วยรายละเอียดดังซีท Personal เซลล์ T5 มียอดสะสมมา 8 วัน และเมื่อบุคคลรหัสนี้ได้ทำการลาไป 2 วัน ยอดที่แสดงในซีท Report1 และ Report3 ที่เซลล์ D31 ก็ควรจะแสดงยอดสะสมยกมาเป็น 8 วัน

เมื่อย้ายมากลางปีก็ต้องไปเพิ่มเป็นยอดยกไปในปี 2554 ครับซึ่งจะแสดงเป็นยอดยกมาในปี 2555 ครับ เพราะสูตรที่เขียนเป็นการดึงยอดยกไปของปีเก่าหรือยอดยกมาในปีนี้ เช่นตามด้านล่าง

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

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

[joo]

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

ตรงจุดนี้มีวิธีการเพิ่มอย่างไรครับ
แลัวที่ซีท Report3 เซลล์ D31 ซึ่งมีสูตรที่อ้างอิงมาจากคอลัมภ์ด้านบนซึ่งข้อมูลของบุคคลรหัส 50203 ก็ไม่มีจึงเกิดการ Error #N/A มีวิธีปรับแก้ไขสูตรอย่าไรครับ

[snasui]

สูตรด้านบนเขียนโดยอ้างอิงถึงชีทใดแสดงว่านำค่าของชืทนั้นมาแสดง การเพิ่มค่าใด ๆ ก็ต้องเพิ่มในชีทที่ดึงค่ามาครับ แก้ตรงจุดนี้ให้สำเร็จก่อนแล้วค่อยมาดูกันต่อที่ชีท Report3 ครับ

[joo]

ผมใช้วิธีแบบ Manual โดยคีย์ข้อมูลของรหัส 50203 เข้าไปในซีท StockDay โดยคีย์ต่อท้ายข้อมูลเดิมครับ ทำให้ค่าที่เซลล์ D31 ของ Report1 ก็สามารถแสดงค่าได้ตรงกับที่ต้องการ แต่ก็ไม่แน่ใจว่าทำถูกวิธีไหม
ส่วนที่ Report3 ยังหาวิธีปรับแก้ไขสูตรอยู่ยังไม่สำเร็จครับ

[snasui]

ลองส่งไฟล์ที่ Update ล่าสุดแล้วมาด้วยครับ จะได้ช่วยดูกันต่อสำหรับ Report3 ครับ

[joo]

รบกวนท่านอาจารย์ช่วยดูสูตรของซีท Report3 ที่เซลล์ D31 ด้วยครับจะต้องปรับแก้ไขอย่างไรดี