Lookup ข้อมูล

[joo]

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

[snasui]

ลองตามไฟล์แนบครับ

ที่ E5 คีย์

=SUMPRODUCT(--($B5=บันทึกการลา!$B$5:$B$16),--(LOOKUP(CHAR(255),$C$2:E$2)=บันทึกการลา!$H$5:$H$16),บันทึกการลา!$G$5:$G$16)

Enter > Copy ลงด้านล่างและ Copy ไปยังเซลล์ที่เกี่ยวข้อง

ที่ B16 คีย์

=SUMPRODUCT(--($A16=บันทึกการลา!$B$5:$B$16),--(LOOKUP(9.99999999999999E+307,$B$14:B$14)=บันทึกการลา!$A$5:$A$16),--(B$15=บันทึกการลา!$H$5:$H$16),บันทึกการลา!$G$5:$G$16)

Enter > Copy ลงด้านล่างและ Copy ไปยังเซลล์ที่เกี่ยวข้อง

[joo]

ขอบคุณครับที่แนะนำ
ข้อมุลที่ E5 ลิงค์มาไม่ตรงครับค่าที่ได้มันควรจะเป็น1 แต่กลับเป็น 3 ซึ่งไปรวมเอาของปี2554มาด้วย
ข้อมุลที่ B16 ลิงค์มาได้ตรงครับ

[snasui]

สามารถดูรูปแบบของ B16 มาใช้ได้ครับ สังเกตในฟังก์ชั่น Sumproduct ว่าช่วงเงื่อนไขใดเป็นของปี ก็เอาเงื่อนไขช่วงนั้นมาใส่เพิ่มในลักษณะเดียวกันได้เลยครับ

[joo]

ที่ E5 ผมคีย์แบบนี้ครับ
=SUMPRODUCT(--($B6=บันทึกการลา!$B$5:$B$16),--(LOOKUP(9.99999999999999E+307,$A$5:A$10)=บันทึกการลา!$A$5:$A$16),--(C$2=บันทึกการลา!$H$5:$H$16),บันทึกการลา!$G$5:$G$16) ค่าที่ลิงค์มาคือ 2
แต่ถ้าคียล์แบบนี้ค่าที่ลิงค์มาจะถูกต้องครับ
=SUMPRODUCT(--($B6=บันทึกการลา!$B$5:$B$16),--(LOOKUP(9.99999999999999E+307,$A$5:A$7)=บันทึกการลา!$A$5:$A$16),--(C$2=บันทึกการลา!$H$5:$H$16),บันทึกการลา!$G$5:$G$16)
ทำไมเวลาเราเลือกช่วงเซลล์ตั้งแต่ $A$5:A$10 มันจึงแยกระหว่างปีไม่ได้ครับ
แล้วถ้าต้องการให้รายงานแสดงแบบ PivotTable แบบซีทสรุปการลาพอทำได้ไหมครับ

[snasui]

โทษทีครับ ผมดูข้อมูลผิดตาราง มาดูใหม่พบว่าปีอยู่กันคนละแนว ผมเขียนสูตรมาให้ใหม่ ดูในไฟล์แนบครับ

เซลล์ E5 แ้ก้สูตรใหม่เป็นตามด้านล่างครับ

=SUMPRODUCT(--($B5=บันทึกการลา!$B$5:$B$16),--(LOOKUP(CHAR(255),C$2:E$2)=บันทึกการลา!$H$5:$H$16),--($A5=บันทึกการลา!$A$5:$A$16),บันทึกการลา!$G$5:$G$16)

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

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

[joo]

ข้อมูลที่ E5 มาตรงแล้วครับ
ที่Privote Table ถ้าต้องการให้แสดงเพิ่มชื่อ นามสกุล และยอดคงเหลือการลาแต่ละประเภท ต้องสร้างสูตรหรือลิงค์มายังไง ช่วยแนะนำหน่อยได้ไหมครับ

[snasui]

เนื่องจากเครื่องนี้ไม่มี Camtasia เลย Capture ภาพและอธิบายไว้ในไฟล์ ลองประยุกต์ใช้ดูครับ

[joo]

ขอบคุณครับ ผมลองเปลี่ยนแปลงข้อมูลในซีทบันทึกการลา ที่ sheet2 ข้อมูลไม่ลิงค์มาเลยครับ ใน Pirvote Table ถ้าให้แสดงรหัสบุคคล ชื่อ นามสกุล ทำได้ไหมครับ

[snasui]

การ Update ข้อมูลในฐานข้อมูลจะต้องมา Refresh PivotTable ด้วยเสมอครับข้อมูลถึงจะทำการ Update ให้ เราสามารถคลิกขวาลงในตาราง PivotTable แล้วเลือก Refresh ได้เลย แต่ต้องมั่นใจว่าเราคลุมเลือกข้อมูลไว้ครบถ้วนแล้ว

เพื่อให้มั่นใจว่าเราคลุมเลือกข้อมูลไว้ครบถ้วนแล้วเราสามารถคลิกขวาลงไปในตาราง PivotTable > PivotTable Wizard > Back > คลุมข้อมูล
กรณีที่เราไม่ต้องการคลุมข้อมูลทุกครั้งที่มีการเปลี่ยนแปลงข้อมูลสามารถเลือกทำได้ 2 วิธีเป็นอย่างน้อย คือ

1. สร้าง List วิธีการคือ คลิกขวาลงในฐานข้อมูล > Create List > ทำเครื่องหมายที่ My list has headers การทำเช่นนี้ไม่ว่าสูตรหรือ PivotTable ที่เกี่ยวข้องกับตารางนี้จะเพิ่มลดตามปริมาณข้อมูลได้เดง
2. ให้ชื่อกับข้อมูล ซึ่งต้องให้ชื่อแบบ Dynamic เพื่อให้เพิ่มลดตามปริมาณข้อมูล เช่น ข้อมูลใน Sheet1 เริ่มที่ A1 เราสามารถให้ชื่อกับข้อมูลดังนี้
เลือก Sheet1 > เข้าเมนู Insert > Name > Define > Name in workbook: คีย์ชื่อที่ต้องการ > Refers to: กรอกสูตร

=Offset($A$1,0,0,Counta($A:$A),Counta($1:$1))

คลิก Add
3. นำชื่อตามข้อ 2 ไปใช้ใน PivotTable โดยเขียนชื่อที่ให้ไว้แทนการคลุมข้อมูลด้านบน

[joo]

- พอจะมีวิธีไหมครับให้มัน Refresh อัตโนมัติ คือเมื่อฐานข้อมูลมีการเปลี่ยนแปลงก็ให้ข้อมูลใน Privote UpDate ตาม
ผมทำวิธีนี้ก็พอได้อยู่ครับ โดยการคลิดขวาลงในตาราง Privote Table > ตัวเลือกตาราง ทำเครื่องหมายที่ ฟื้นฟู้ข้อมูลเมื่อเปิด >ตกลง
แล้วเปิดข้อมูลขึ้นมาดูใหม่ ข้อมูลที่ Privot Table ก็จะ Update ให้ครับ
- ใน Privote Table ต้องทำยังไงครับผมลองแล้วมาเฉพาะชื่อเดียวหรือว่าต้องรวมชื่อและนามสกุลอยู่ในเซลล์เดียวแล้วค่อยสร้างใหม่ครับ

[snasui]

ต้องใช้ VBA ครับ ดูตัวอย่างตาม Link ครับ http://www.ozgrid.com/VBA/pivot-table-refresh.htm

ใน PivotTable สามารถลาก Field มาวางได้ตามต้องการ ไม่ต้องรวมชื่อและนามสกุลกันก่อนครับ ลองทดสอบดูครับ ลากแล้วติดปัญหาอย่างไร ลองส่งตัวอย่างที่ลองลากแล้วมาให้ดูด้วยครับ จะได้เห็นภาพ

[joo]

ขอคุณครับ ทดลองแล้วใช้งานได้ดีครับ ตอนแรกผมลองใช้แบบนี้มันเกิด Bug ครับ
Sub PivotMacro()
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables("MyPivot")-----เกิด bug ที่บรรทัดนี้ครับ
pt.RefreshTable
End Sub
ผมเปลี่ยนมาใช้แบบนี้ใช้ได้ครับ
Sub AllWorksheetPivots()
Dim pt As PivotTable
For Each pt In ActiveSheet.PivotTables
pt.RefreshTable
Next pt
End Sub
อาจารย์ช่วยอธิบายเหตุการณ์ที่เกิด Bug หน่อยครับ
ส่วน PivotTable ที่ Sheet1 ผมลากมาวางทำได้ครับ แต่ที่ Sheet2 ข้อมูลมาไม่ตรงครับแนะนำด้วยครับ
ที่ Sheet3 ครับ ต้องการกรอกข้อมูลแล้วให้ไปเก็บที่ซีท "บันทึกการลา" โดยเรียงข้อมูลต่อกันไปเรื่อยๆ ต้องเขียนโค้ดยังไงครับ

[snasui]

ที่เกิด Bug เพราะ PivotTable คุณ Joo ไม่ได้ตั้งชื่อว่า MyPivot ครับ ใน Sheet2 PivotTable จะชื่อว่า PivotTable2 ครับ

วิธีการดูชื่อของ PivotTable ให้คลิกขวา > PivotTable Option > สังเกตที่ Name (ผมดูจาก Excel 2010)

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

Code การบันทึกข้อมูลตามด้านล่างครับ

Code: Select all

Sub RecordData()
Dim rSource As Range
Dim rTarget As Range
Set rSource = Sheets("Sheet3").Range("J4:S4")
Set rTarget = Sheets("บันทึกการลา"). _
    Range("A65536").End(xlUp).Offset(1, 0)
rSource.Copy
rTarget.PasteSpecial xlPasteValues
Application.CutCopyMode = False
MsgBox "Record Complete"
End Sub

[joo]

ขอบคุณครับ ลองตามที่แนะนำทำได้แล้วครับตั้งชื่อ Privote Table ไม่ถูกจริงๆ
สำหรับโค้ดลองแล้วใช้ได้ครับ แต่อยากให้ Clear Cell ในช่วง D5;D13 หลังจากกดปุ่มบันทึกแล้ว ทำยังไงครับ ผมลองใช้แบนนี้ Sheets("Sheet3").Range("D5:D13").ClearContents ต่อท้าย MsgBox "Record Complete" มันเกิด bug ครับ
ที่โค้ดบรรทัดนี้ Range("A65536").End(xlUp).Offset(1, 0) หมายถึงอะไรครับ

[snasui]

ลองส่งไฟล์ที่เพิ่ม Code แล้วมาให้ดูด้วยครับ จะได้ทดสอบครับ

สำหรับ Range("A65536").End(xlUp).Offset(1, 0) หมายถึง

ให้ตั้งต้นจากเซลล์ A65536 วิ่งขึ้นไปยังเซลล์ในคอลัมน์ A ที่มีข้อมูล จากนั้นให้ลงมาด้านล่าง 1 เซลล์ครับ

[joo]

ทำได้แล้วครับเช็คดูใหม่เลือกเรนจ์ผิดครับ Code แบบนี้ครับ
....
MsgBox "Record Complete"
Sheets("Sheet3").Range("D5:E13").ClearContents
End Sub
ติดขัดปัญหาจะกลับมาถามใหม่ครับ ขอบคุณครับ

[joo]

มีปัญหามารบกวนครับ ผมปรับฟอร์มคียข้อมูลใหม่ทำให้ข้อมูลที่ลิงค์ไปยัง Privote Table ไม่มาเลยครับ
ที่ซีท Main ผมแยก จำนวนวันสะสมกับจำนวนวันลาออกจากกันให้อยู่คนละเซลล์ เพราะที่ D14 ผมใส่สูตรไว้จึงทำให้ใส่วันสะสมไม่ได้ หรือว่าต้องให้ข้อมูล D14 ใส่ได้ทั้งจำนวนวันลาและจำนวนวันสะสมเหมือนเดิม หรือพอมีวิธีอื่นไหมครับ

[snasui]

ผมปรับ Code ในหน้า Main ให้ Refresh PivotTable อัตโนมัติเมื่อคลิกปุ่มบันทึก

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

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

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

เช่นข้อมูลสิทธิ์ในการลาก็แยกไว้ 1 ตารางจะรวมกับวันลายกมาที่พนักงานมีสิทธิ์ก็น่าจะทำได้
ข้อมูลการลาก็แยกไว้ 1 ตาราง

กาำรคำนวณเพื่อสรุปวันลาก็ค่อยคำนวณจาก 2 ตารางข้างต้น อันนี้เป็นเบื้องต้นนะครับ

[joo]

ขอบคุณครับ
ไฟล์ที่แก้ล่าสุดผมลองดูแล้วค่ามันก็ยังลิงค์มาไม่ตรงอยู่ดีครับ
ผมลองแบบนี้ครับ ที่ D14 ผมใส่สูตร=IF(D9="ลา",IF(D12="","",IF(D13="","",SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(D12&":"&D13)),2)<6)))),IF(D9="ยกมา",D14=D10)) มันฟ้องว่าเป็นสูตรแบบวงกลม ช่วยดูสูตรให้หน่อยครับพอปรับแก้ตรงไหน ถ้าไม่ได้ก็จะเก็บแยกคนละตารางอย่างที่อาจารย์แนะนำทำสรุปผลง่ายกว่าครับ