การนำบรรทัดแรกของข้อมูลที่ได้จากการ Filter มาแสดงไม่สามารถใช้สูตร Link มาแบบธรรมดาได้ ทั้งนี้เนื่องจากบรรทัดของข้อมูลที่ได้จากการ Filter นั้นจะไม่คงที่ ขึ้นอยู่กับว่ารายการที่ได้จากการ Filter นั้นอยู่ในบรรทัดใดบ้าง การนำข้อมูลที่พบตัวแรกของรายการที่ได้จากการ Filter จึงต้องใช้สูตรที่ซับซ้อนกว่าปกติ
ยกตัวอย่างเช่นข้อมูล Product ตามภาพด้านล่าง ต้องการให้ Filter ราย Product แล้วนำ Prodcut บรรทัดแรกที่ได้จากการ Filter มาแสดง
=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
ยกตัวอย่างเช่นข้อมูล 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
- ให้รวมตัวเลขทุกตัวแล้วได้ค่าเป็น 9, 18, 27 หรือ 36
- สามารถคัดชุดที่มีตัวเลขที่ไม่ต้องการออกไปได้ด้วย ยกตัวอย่างเช่นไม่รวมตัวเลขที่มีค่าต่อไปนี้ 0, 3, 7, 8
กรณีใช้สูตรในการคำนวณมีวิธีการดังนี้
- เปิดสมุดงานใหม่แล้วเลือกชีทว่าง
- ที่ A1 คีย์ No. และ ที่ B1 คีย์ Select เพื่อเป็นชื่อ Field
- ที่ A2 คีย์ Row()-1 > Copy ลงด้านล่างตามต้องการ
- ที่ 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 ในการคำนวณมีวิธีการดังนี้
- กดแป้น Alt+11 เพื่อเปิดหน้าต่าง VBE
- คลิกเมนู Insert > Module
- 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
- กดแป้น Alt+Q เพื่อกลับมายังโปรแกรม Excel
- กดแป้น Alt+F8 > เลือก ListNum > Run
โปรแกรมจะทำการ Run ตัวเลขมาให้ตามเงื่อนไขที่ต้องการ
0 ความคิดเห็น:
แสดงความคิดเห็น