Link ข้อมูลมาแล้วใช้ pivot และสูตรร่วมกันเพื่อวิเคราะห์ต่อ

[godman]

สวัสดีครับ อาจารย์คนควนรอบที่สองของวัน
จากกระทู้ที่แล้ว เกี่ยวกับการดึงข้อมูลโดยใช้ VBA ตอนแรกก็อยากจะใช้แบบนั้นแหละครับแต่ว่าผมไม่เก่ง เขียน code ก็เลยไม่ดีกว่า และหันมาใช้ pivot ร่วมกับสูตรแทน
โจทย์ของผมคือ user คือผู้บริหารต้องการดูข้อมูลว่า ในแต่ละวันมีการสลับสับเปลี่ยนพนักงานกี่ตำแหน่ง ใช้คำว่า Change และมีพนักงานที่มาใหม่กี่ตำแหน่งผมใช้คำว่าNew comer ครับ NOTE ว่าเป็นการเปรียบเทียบ วันต่อวัน หรือ day by day เท่านั้น แต่ถ้าท่านผู้บริหารต้องการแค่เปอร์เซนต์ก็คงไม่ค่อยยากนักแต่ท่านต้องการทราบรายละเอียดด้วยว่ามีพนักงานชื่ออะไรบ้างครับ

ผมเลยทำข้อมูลดึงมาจากฐานข้อมูลของฝ่ายผลิตที่เขาจะคีย์ทุกวัน แล้วผมก็ใช้สูตรดีงมาเฉพาะสัปดาห์ NOTE ว่าเขาต้องการดูแค่สัปดาห์ล่าสุดหรือ week to date เท่านั้นครับ
แต่ปัญหาของผมก็คือว่าใน sheet update opt นี่คือหน้าตาที่จะทำและผมก็ทำจะเสร็จแล้ว ด้วยการใช้สูตรของอาจารย์และประยุกต์บ้างร่วามกับ pivot
แต่ปัญหาก็คือว่า
เมื่อวนพรุ่งนี้มาถึง วันที่เปลี่ยน สัปดาห์เปลี่ยนไป แต่ pivot มันไม่เปลี่ยนตามนี่สิ ผมมี pivot ทั้งหมด 6 อัน ก็ตามวันใน 1 สัปดาห์ครับ โดย pivot อันแรกคือวันที่ที่เป็นวันนี้ แล้วก็ไล่ไปจนถึงวันที่ 6 ของสัปดาห์ ผมอยากให้เปิดไฟล์มาแล้ว ข้อมูลเปลี่ยน automatic update ครับ

สรุปความต้องการ
1. ให้ข้อมูลใน ยรอนะ 1-6 ใน sheet update opt ที่ดึงข้อมูลมากจาก database1 เปลี่ยนแบบอัตโนมัตครับ
2.วันที่ใน pivot 1 2 3 4 5 6 เปลี่ยนจากวันที่ล่าสุดไปหาวันที่น้อยที่สุดครับใน 1 สัปดาห์นั้น ครับ
ขออาจารย์ให้คำแนะนำหรือ code vba ก้ได้ครับเพราะผมเขียนไม่เป็นได้แต่ copy และมาประยุกต์นิดหน่อยครับ

ไฟล์ใหญ่ผมขอส่งให้ทาง mail นะครับเพราะเป็นข้อมูลสัปดาห์

[snasui]

ผมเขียนตัวอย่าง Code มาให้ตามด้านล่าง ลองนำไป Run ดูครับ

โดยให้แทรก Sheet1 มา 1 ชีท

จากนั้นนำ Code ด้านล่างไปวางที่ Module ปกติ

Code: Select all

Option Explicit

Sub UniqueDate()
Application.Calculation = xlCalculationManual
    With Sheets("Sheet1")
        .Select
        .Range("A1:A7").Clear
        Sheets("Database1").Range("A3:A1000").AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Range("A1"), Unique:=True
        .Range("A1:A7").Sort Key1:=Range("A2"), Order1:=xlDescending, Header:= _
            xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    End With
Sheets("Update opt").Activate
Application.Calculation = xlCalculationAutomatic
End Sub

Sub RefreshPiv()
Dim i As Integer
Dim r As Range
Set r = Sheets("Sheet1").Range("A3")
With Sheets("Update opt")
    .Range("B1") = r
    .Range("B6") = r
    .Range("I6") = r.Offset(1, 0)
    .Range("P6") = r.Offset(2, 0)
    .Range("W6") = r.Offset(3, 0)
    .Range("AD6") = .r.Offset(4,0)
    For i = 1 To .PivotTables.Count
        .PivotTables(i).PivotCache.Refresh
    Next i
 End With
End Sub

UniqueDate จะเป็นการสร้างวันที่แบบ Unique ก่อนนำไปใช้ ส่วน RefreshPiv จะทำการ Refresh PivotTable ทั้งหมดที่มีหลังจากได้นำวันที่มาวางตามตำแหน่งที่กำหนดไว้

และให้นำ Code ด้านล่างไปวางที่ ThisWorkbook เพื่อให้ Run Code ทั้งสองด้านบนแบบอัตโนมัติเมื่อเปิดไฟล์

Code: Select all

Option Explicit

Private Sub Workbook_Open()
    UniqueDate
    RefreshPiv
End Sub

[godman]

ขอบคุณมากครับ แต่ว่าต้องใส่วันที่เอง copy ไปวางเองใช่ไหมครับ ไม่สามาถ auto ได้ใช่ไหมครับ ทางที่สอง คือผมอาจจะใช้สูตรดึงข้อมูลพวก if(isnumber (search(...)),lookup(9.999999999e+07,....)+1,"") คือผมเล่นจำสูตรได้ครับ อาจจะไม่เข้าใจตรรกะแต่ผมท่องจำเอาครับ
ทำไม ก็เพราะว่าคนที่จะนำไปใช้ พื้นฐาน excel ไม่มาก เขาแค่เปิดมาปั้ปไม่อยากทำอะไร ดูได้เลย ผมจะลองดูครับ แต่ว่า code pivot refresh automatic ผมจะนำไป ประยุกต์ดูกับอันอื่น

[snasui]

แต่ว่าต้องใส่วันที่เอง copy ไปวางเองใช่ไหมครับ ไม่สามาถ auto ได้ใช่ไหมครับ

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

if(isnumber (search(...)),lookup(9.999999999e+07,....)+1,"") คือผมเล่นจำสูตรได้ครับ อาจจะไม่เข้าใจตรรกะ

ความหมายของสูตรคือ ถ้า Seach แล้วได้ค่าเป็น Number (isnumber (search(...)) เป็นการตรวจสอบว่าค่าที่ search มานั้นได้เป็น Number หรือไม่) ก็ให้แสดงผลจากสูตร lookup(9.99999999999999e+307,....)+1 ถ้า Search แล้วไม่ได้ค่าเป็น Number นั่นคือหาไม่พบ ก็ให้แสดงเป็นค่าว่าง ส่วน lookup(9.99999999999999e+307,....)+1 เป็นการนำค่าสุดท้ายที่เป็นตัวเลขในช่วงแล้วบวกด้วย 1 ดังนั้น ทุกครั้งที่พบจะได้ค่าลำดับที่สูงขึ้น