Microsoft Excel Tips and Tricks: การค้นหาตัวเลขทะเบียนรถที่ถูกโฉลก

การนำบรรทัดแรกของข้อมูลที่ได้จากการ 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

1. ให้รวมตัวเลขทุกตัวแล้วได้ค่าเป็น 9, 18, 27 หรือ 36
2. สามารถคัดชุดที่มีตัวเลขที่ไม่ต้องการออกไปได้ด้วย ยกตัวอย่างเช่นไม่รวมตัวเลขที่มีค่าต่อไปนี้ 0, 3, 7, 8

การหาค่าดังกล่าวสามารถใช้สูตรหรือ VBA มาคำนวณได้ครับ

กรณีใช้สูตรในการคำนวณมีวิธีการดังนี้

1. เปิดสมุดงานใหม่แล้วเลือกชีทว่าง
2. ที่ A1 คีย์ No. และ ที่ B1 คีย์ Select เพื่อเป็นชื่อ Field
3. ที่ A2 คีย์ Row()-1 > Copy ลงด้านล่างตามต้องการ
4. ที่ B2 คีย์สูตร
   =IF(MIN(LEN(SUBSTITUTE(A2,{0,3,7,8},"")))<LEN(A2),"",IF(ISNUMBER(MATCH(SUM(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0),{9,18,27,36},0)),SUM(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0),""))Ctrl+Shift+Enter > Copy ลงด้านล่าง

กรณีใช้ VBA ในการคำนวณมีวิธีการดังนี้

1. กดแป้น Alt+11 เพื่อเปิดหน้าต่าง VBE
2. คลิกเมนู Insert > Module
3. Copy Code ด้านล่างไปวาง
   
   

   Sub ListNum()
       Dim i As Integer, bln As Boolean
       Dim c As Integer, j As Integer
       Dim a As String, s As Integer
       For i = 1 To 9999
           bln = False
           s = 0
           For j = 1 To Len(CStr(i))
               a = Mid(i, j, 1)
               Select Case a
                   Case 0, 3, 7, 8
                       bln = True
                       Exit For
                   Case Else
                       s = s + a
               End Select
           Next j
           If bln = False Then
               Select Case s
                   Case 9, 18, 27, 36
                       c = c + 1
                       Cells(c + 1, 1) = i
               End Select
           End If
       Next i
   End Sub

4. กดแป้น Alt+Q เพื่อกลับมายังโปรแกรม Excel 
5. กดแป้น Alt+F8 > เลือก ListNum > Run

โปรแกรมจะทำการ Run ตัวเลขมาให้ตามเงื่อนไขที่ต้องการ

ภาพตัวอย่างการใช้งาน

Posted in: สอน office