การหา Unique Value จากค่าที่ได้จากการกรอง

สำหรับการหาค่า Unique Value เราสามารถหาได้จากหลาย ๆ วิธีตาม Link นี้ครับ การหารายการเฉพาะที่ไม่ซ้ำ แต่หากเป็นการหาค่าที่ไม่ซ้ำจากรายการที่ได้จากการกรองต้องพึ่งสูตรที่มีความซับซ้อนมากขึ้น

ยกตัวอย่างมีข้อมูลอยู่ที่ A2:B10 หลังจากกรองแล้วให้นับเฉพาะค่าที่ไม่ซ้ำในช่วง A2:A10

เราสามารถคีย์สูตรใน D1 ได้ตามด้านล่างครับ

=SUM(IF(MATCH(IF(SUBTOTAL(3,OFFSET(B2,ROW(B2:B10)-ROW(B2),0)),A2:A10),IF(SUBTOTAL(3,OFFSET( B2,ROW( B2:B10)-ROW(B2),0)),A2:A10),0)=ROW(A2:A10)-ROW(A2)+1,SUBTOTAL(3,OFFSET(B2,ROW(B2:B10)-ROW(B2),0))))

Ctrl+Shift+Enter

ภาพตัวอย่างภาพการใช้สูตรหาค่า Unique จากค่าที่ได้จากการกรอง

การรวมข้อมูลจากหลายชีทด้วย VBA

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

ภาพแสดงข้อมูลต้นแหล่งและลักษณะข้อมูลเป้าหมายที่ต้องการ

เราสามารถใช้ VBA ในการดำเนินการดังกล่าว โดยเขียน 3 Procedure แยกหน้าที่กันดังนี้

1. CollectData เพื่อนำข้อมูลจากแต่ละชีทมาวางต่อกันใน Sheet4 และใช้เป็น Procedure ในการ Run Code
2. SortData เพื่อจัดเรียงข้อมูลใหม่ตามคอลัมน์ D (ชื่อประเภท) เป็น Sub Procedure ถูกเรียกใช้จากข้อ 1
3. InsertRow เพื่อแทรกบรรทัด, สรุปยอดรวมและทำการจัด Format เป็น Sub Procedure ถูกเรียกใช้จากข้อ 1

ตัวอย่าง VBA Code

Option Explicit

Sub CollectData()
    Dim ws As Worksheet
    Dim r As Range
    Dim rTarget As Range
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        If ws.Name <> "Sheet4" Then
            With Sheets("Sheet4")
                Set rTarget = .Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            End With
            Set r = ws.Range("A2", ws.Range("A" & Rows.Count).End(xlUp))
                r.SpecialCells(xlCellTypeConstants).EntireRow.Copy
                rTarget.PasteSpecial xlPasteValues
        End If
    Next ws
    SortData
    InsertRow
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Sub SortData()
    Dim r As Range
    Dim rs As Range
    With Sheets("Sheet4")
        Set r = .Range("A1", .Range("H" & Rows.Count).End(xlUp))
        Set rs = r.Cells(2, 1).Offset(0, 3).Resize(r.Count - 1, 1)
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=rs _
            , SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        With .Sort
            .SetRange r
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub

Sub InsertRow()
    Dim r As Range, rAll As Range
    Dim rFmt As Range, rsHead As Range
    Dim rtHead As Range, rInsert As Range
    With Sheets("Sheet1")
        Set rFmt = .Range("A2")
        Set rsHead = .Range("A1:H1")
    End With
    With Sheets("Sheet4")
        Set rtHead = .Range("A1")
        Set rAll = .Range("D2", .Range("D" & Rows.Count).End(xlUp))
        For Each r In rAll
            If r <> r.Offset(1, 0) Then
                r.Offset(1, 5) = True
            End If
        Next r
       Set rInsert = .Range("I:I").SpecialCells(xlCellTypeConstants)
             For Each r In rInsert
                r.Resize(2, 1).EntireRow.Insert shift:=xlShiftDown
                r.Offset(-2, -7) = "Total"
                r.Offset(-2, -4).Formula = "=sum(" & r.Offset(-3, -4).Address & ":" & _
                    r.Offset(-3, -4).End(xlUp).Address & ")"
                Set r = r.Offset(-2, -4)
                r = Application.ConvertFormula(r.FormulaR1C1, xlR1C1, xlA1, xlRelative)
                r.Resize(1, 4).FillRight
                rFmt.Copy
                r.CurrentRegion.PasteSpecial xlPasteFormats
            Next r
       .Range("I:I").Clear
    End With
    rsHead.Copy rtHead
End Sub

 

ตามแสดงตัวอย่างการใช้งาน

 

การนำรายการแรกที่ได้จากการ Filter มาแสดง

การนำบรรทัดแรกของข้อมูลที่ได้จากการ Filter มาแสดงไม่สามารถใช้สูตร Link มาแบบธรรมดาได้ ทั้งนี้เนื่องจากบรรทัดของข้อมูลที่ได้จากการ Filter นั้นจะไม่คงที่ ขึ้นอยู่กับว่ารายการที่ได้จากการ Filter นั้นอยู่ในบรรทัดใดบ้าง การนำข้อมูลที่พบตัวแรกของรายการที่ได้จากการ Filter จึงต้องใช้สูตรที่ซับซ้อนกว่าปกติ

ยกตัวอย่างเช่นข้อมูล Product ตามภาพด้านล่าง ต้องการให้ Filter ราย Product แล้วนำ Prodcut บรรทัดแรกที่ได้จากการ Filter มาแสดง

ภาพข้อมูล Product

สามารถทำได้ดังนี้

ที่เซลล์ A2 คีย์สูตร

=INDEX(A$4:A$20,MATCH(1,SUBTOTAL(3,OFFSET($A$4,ROW($A$4:$A$20)-ROW($A$4),)),0))

Ctrl+Shift+Enter

โดยสูตร SUBTOTAL(3,OFFSET($A$4,ROW($A$4:$A$20)-ROW($A$4),)) เป็นสูตร Array ซึ่งจะแสดงผลลัพธ์เป็นชุดข้อมูล โดยจะแสดงผลลัพธ์เป็น 0 หากบรรทัดนั้นถูกซ่อนจากการ Filter และจะแสดง 1 หากบรรทัดนั้นได้จากการ Filter ซึ่งตัวอย่างสูตรจะแสดงตามด้านล่าง

=INDEX(A$4:A$20,MATCH(1,{0,0,1,0,0,0,0,0,1,1,0,0,1,0,0,1,0},0))

จากตัวอย่างด้านบนจะนำ A6 มาแสดง เนื่องจากเลข 1 ตัวแรกตรงกับ A6 ( เลข 0 ตัวแรกคือ A4) ซึ่งการจะเข้าใจสูตรนี้ต้องเข้าใจสูตร Index และ Match ก่อนครับ

ตัวอย่างการแสดง Prouduct จากการ Filter

เทคนิคการ Match Data ด้วย Subtotal

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

VDO ทางด้านล่างนี้ สาธิตการจัดกลุ่มตัวเลขด้วยการใช้ Subtotal เพื่อหาว่ามียอดรวมของกลุ่มข้อมูลใดไม่เป็น 0 ซึ่งทำให้ง่ายขึ้นในการจำแนกข้อมูลเพื่อนำข้อมูลที่ไม่เข้าคู่ไปใช้ต่อไป

โดยมีวิธีการดังนี้ครับ

1. เพิ่มคอลัมน์ด้านท้ายตาราง
2. ใช้ฟังก์ชั่น ABS เพื่อ Absolute
3. จัดเรียงข้อมูลตามคอลัมน์ที่เพิ่มขึ้นมาใหม่
4. ทำ Subtotal

ภาพประกอบการ Match Data ด้วย Subtotal

 

จะเห็นว่าการ Match ด้วย Subtotal เป็นเรื่องง่ายที่เราคาดไม่ถึง ซึ่งเรามักมุ่งไปแก้ปัญหาด้วยฟังก์ชั่นแทน ต่อไปนี้ก็คงเป็นเรื่องง่าย ๆ แล้วใช่ไหมครับ

การคำนวณจากพื้นที่แบบสามเหลี่ยม

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

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

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

ภาพประกอบ

 

A. สามเหลี่ยมบน

1. หาค่าผลรวม ที่ M2 คีย์
   =SUMPRODUCT(SUBTOTAL(9,OFFSET(C2,,ROW(K10)-ROW(K2:K10),ROW(K10)-ROW(K2:K10)+1)))
   Enter
2. หาค่าเฉลี่ยที่ M3 คีย์
   =M2/SUMPRODUCT(SUBTOTAL(3,OFFSET(C2,,ROW(K10)-ROW(K2:K10),ROW(K10)-ROW(K2:K10)+1)))
   Enter
3. หาค่า Max ที่ M4 คีย์
   =MAX(SUBTOTAL(4,OFFSET(C2,,ROW(K10)-ROW(K2:K10),ROW(K10)-ROW(K2:K10)+1)))
   Ctrl+Shift+Enter
4. หาค่า Min ที่ M5 คีย์
   =MIN(SUBTOTAL(5,OFFSET(C2,,ROW(K10)-ROW(K2:K10),ROW(K10)-ROW(K2:K10)+1)))
   Ctrl+Shift+Enter

B. สามเหลี่ยมล่าง

1. หาค่าผลรวมที่ M11 คีย์
   =SUMPRODUCT(SUBTOTAL(9,OFFSET(J12,,ROW(B4:B12)-ROW(B12),ROW(B4:B12)-ROW(B12)-1)))
   Enter
2. หาค่าเฉลี่ยที่ M12 คีย์
   =M11/SUMPRODUCT(SUBTOTAL(3,OFFSET(J12,,ROW(B4:B12)-ROW(B12),ROW(B4:B12)-ROW(B12)-1)))
   Enter
3. หาค่า Max ที่ M13 คีย์
   =MAX(SUBTOTAL(4,OFFSET(J12,,ROW(B4:B12)-ROW(B12),ROW(B4:B12)-ROW(B12)-1)))
   Ctrl+Shift+Enter
4. หาค่า Min ที่ M14 คีย์
   =MIN(SUBTOTAL(5,OFFSET(J12,,ROW(B4:B12)-ROW(B12),ROW(B4:B12)-ROW(B12)-1)))
   Crl+Shift+Enter

C. หาจุดตัดสามเหลี่ยมบนที่ M18 คีย์

1. =OFFSET(B2,MIN(MATCH(M17,C16:C25,0)-1,MATCH(N17,C16:C25,0)-1),MAX(MATCH(M17,C16:C25,0)-1,MATCH(N17,C16:C25,0)-1))
   Enter

D. หาจุดตัดสามเหลี่ยมล่างที่ M22 คีย์

1. =OFFSET(B3,MAX(MATCH(N21,B16:B25,0)-1,MATCH(M21,B16:B25,0)-1),MIN(MATCH(N21,B16:B25,0)-1,MATCH(M21,B16:B25,0)-1))
   Enter

การรวมยอดโดยไม่รวมบรรทัดที่ซ่อน

หลาย ๆ ท่านใช้การซ่อนบรรทัด (Hide) ไว้แทนการลบทิ้ง โดยเผื่อไว้ว่าโอกาสหน้าจะกลับมาใช้ จะได้ไม่ต้องคีย์เพิ่มเข้าไปใหม่ แต่หากมีการใช้สูตร Sum เพื่อรวมยอดตัวเลขไว้ ยอดผลรวมนั้นจะรวมบรรทัดที่ซ่อนด้วย

การรวมยอดเพื่อไม่ให้รวมบรรทัดที่ซ่อนไว้สามารถทำได้โดยใช้ฟังก์ชั่น Subtotal ครับ และนอกจากหายอดรวมแล้ว ยังหาค่าเฉลี่ย ค่าน้อยที่สุด ค่ามากที่สุด ค่าเบี่ยงเบนมาตรฐาน การนับเฉพาะตัวเลข การนับทั้งหมดยกเว้นค่าว่าง เป็นต้น

สมมุติข้อมูลอยู่ที่ B2:B50 จะขอยกตัวอย่างบางฟังก์ชั่นที่น่าใช้งานดังนี้ครับ

1. หากต้องการรวมยอดที่ B2:B50 โดยไม่รวมบรรทัดที่ซ่อน ที่ B51 คีย์
   =Subtotal(109,B2:B50)
2. หากต้องการนับเฉพาะตัวเลขที่ B2:B50 โดยไม่รวมบรรทัดที่ซ่อน ที่ B51 คีย์
   =Subtotal(102,B2:B50)
3. หากต้องการนับทั้งหมดยกเว้นค่าว่างที่ B2:B50 โดยไม่รวมบรรทัดที่ซ่อน ที่ B51 คีย์
   =Subtotal(103,B2:B50)
4. หากต้องการหาค่าที่มากที่สุดที่ B2:B50 โดยไม่รวมบรรทัดที่ซ่อน ที่ B51 คีย์
   =Subtotal(104,B2:B50)
5. หากต้องการหาค่าที่น้อยที่สุดที่ B2:B50 โดยไม่รวมบรรทัดที่ซ่อน ที่ B51 คีย์
   =Subtotal(105,B2:B50)
6. หากต้องการหาค่าเฉลี่ยที่ B2:B50 โดยไม่รวมบรรทัดที่ซ่อน ที่ B51 คีย์
   =Subtotal(101,B2:B50