การใช้ Count, Search และ Lookup ค้นหาข้อมูล

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

Video ด้านล่างนี้เป็นการแสดงการค้นหาข้อมูลด้วยการใช้ฟังก์ชั่นที่สำคัญคือ Count, Search และ Lookup ร่วมกัน เพื่อทำการค้นหาข้อมูลใด ๆ จากทุกคอลัมน์ของฐานข้อมูล แล้วนำข้อมูลที่พบมาแสดงยังพื้นที่เป้าหมายตามต้องการ ซึ่งจะมีการอธิบายและแปลความหมายของแต่ละฟังก์ชั่นอย่างละเอียด มีความเป็นอัตโนมัติและช่วยลดเวลาให้การค้นหาข้อมูลใด ๆ จากข้อมูลจำนวนมากได้อย่างมหาศาล

Video แสดงการใช้ Count, Search และ Lookup ค้นหาข้อมูล

ดาวน์โหลดไฟล์ตัวอย่างได้ที่ Example file

ศึกษาตัวอย่างการใช้งาน Count, Seach และ Lookup เพิ่มเติมได้ที่ Count Seach and Lookup

สอบถามปัญหาการใช้งาน Excel และ VBA ได้ที่ snasui.com

Lookup

Lookup เป็นฟังก์ชั่นสำหรับการค้นหาข้อมูลที่สามารถประยุกต์ใช้งานได้อย่างหลากหลาย ความสามารถที่่สำคัญอย่างหนึ่งคือมีความแข็งแรงพอที่จะใช้งานแบบ Array โดยไม่จำเป็นต้องกดแป้น Ctrl+Shift+Enter ซึ่งมีเพียงไม่กี่ฟังก์ชั่นที่สามารถทำได้เช่นนี้ อีกความเก่งกาจที่สำคัญ คือสามารถทำงานได้อย่างรวดเร็วเนื่องจากเป็นการค้นหาแบบ Binary Search การใช้งานโดยทั่วไป จะต้องเรียงข้อมูลที่เป็นส่วนประกอบที่สองของฟังก์ชั่น Lookup จากน้อยไปหามากเสมอ แต่กรณีเป็นการใช้งานในเชิงประยุกต์อาจไม่จำเป็นจะต้องเรียงข้อมูลดังกล่าว

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

Video แสดงการใช้งาน Lookup

สามารถดาวน์โหลดไฟล์ตัวอย่างตาม Video ได้ที่ Example File
ศึกษาฟังก์ชั่น Lookup เพิ่มเติมได้ที่ Lookup
สอบถามปัญหาการใช้งาน Excel และ VBA ได้ที่ snasui.com

การนำข้อมูลจากหลาย Sheet มาต่อกันใน Sheet เดียว (2)

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

VDO แสดงการนำข้อมูลหลายชีตมาต่อกันในชีตเดียว

การเลือกข้อมูลจากฐานข้อมูลมาแสดงตามเงื่อนไขที่ต้องการ

ในการนำข้อมูลจากแหล่งใด ๆ มาแสดงตามเงื่อนไขนั้นสามารถทำได้หลายวิธี เช่น Advanced Filter, Array Fomulas, PivotTable, Consolidation หรือ เขียนโปรแกรมด้วย VBA ซึ่งจากที่กล่าวมาทั้งหมดเป็นการกระทำโดยไม่ต้องเพิ่มคอลัมน์ช่วยที่ฐานข้อมูลแต่อย่างใด ลักษณะเช่นนี้เป็นแนวทางการทำงานกับฐานข้อมูลที่ควรจะเป็น โดยหลักการแล้วในฐานข้อมูลเราไม่ควรมีสูตรใด ๆ หรือหากมีก็ควรมีให้น้อยที่สุด ทั้งนี้เนื่องจากหากฐานข้อมูลมีขนาดใหญ่และบางคอลัมน์เป็นสูตรแล้ว จะทำให้ไฟล์มีขนาดใหญ่ขึ้นอีกมากทั้งใช้ระยะเวลาในการคำนวณนานกว่าปกติ

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

ลักษณะงานนี้จะมีส่วนประกอบอยู่ 3 ส่วนประกอบหลักคือ

1. ฐานข้อมูล
2. ส่วนกำหนดเงื่อนไข
3. ส่วนที่ใช้แสดงผล

ภาพประกอบการแสดงรายงานโดยระบุเงื่อนไขที่ต้องการ

จากภาพด้านบน มีขั้นตอนในการทำงานดังนี้

A. กำหนดให้เลือก “ปี” และ “เดือน” โดยไม่ต้องคีย์ลงไปในเซลล์โดยตรง

1. คลิกที่ J2 > เข้าเมนู Data > Data Validation แล้วกำหนดตามภาพ
   
2. คลิกที่ J3 > เข้าเมนู Data > Data Validation แล้วกำหนดตามภาพ
   

B. เขียนสูตรเพื่อดึงข้อมูลมาแสดงผล หลังจากเลือก “เดือน” และ “ปี”

1. เซลล์ J5 คีย์สูตรเพื่อนับว่าพบตามเงื่อนไขกี่รายการ
   =COUNTIFS($B$2:$B$3000,J2,$C$2:$C$3000,J3)
   Enter
2. เซลล์ G2 คีย์สูตรเพื่อตรวจสอบว่ารายการใดเข้าเงื่อนไขให้แสดงหมายเลขลำดับ
   =IF(AND(B2=$J$2,C2=$J$3),COUNT(G$1:G1)+1,"")
   Enter > Copy ลงด้านล่างตามจำนวนข้อมูลที่มี
3. เซลล์ I6 คีย์สูตรเพื่อแสดงรายการที่พบตามเงื่อนไข
   =IF(ROWS(I$6:I6)>$J$4,"",LOOKUP(ROWS(I$6:I6),$G$2:$G$3000,A$2:A$3000))
   Enter > Copy ไปด้านขวาถึง N6 แล้ว Copy I6:N6 ลงด้านล่าง

เมื่อเลือก “ปี” และ “เดือน” ที่เซลล์ J2 และ J3 แล้ว จะนำข้อมูลจากฐานข้อมูลที่ตรงกับเงื่อนไขมาแสดง

Revised: January 29, 2017 at 07:02

การเปรียบเทียบข้อมูลจาก 2 ชีทแล้วนำผลต่างไปแสดงในชีทที่ 3

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

สำหรับการสรุปผลต่างของข้อมูลแล้วแสดงรายการข้อมูลที่ต่างกันด้วยสูตรดูเหมือนจะเป็นเรื่องยากลำบากที่จะทำเช่นนี้ แต่ก็ไม่เกินความสามารถของ Excel โดยไม่ต้องพึ่งพา VBA แต่อย่างใด แต่จะมีหลายลำดับขั้นตอน ดังตัวอย่างตามภาพด้านล่างซึ่งเป็นข้อมูลของ Sheet1, Sheet2 และ Sheet3 ซึ่งเป็นชีทสรุปข้อมูล

ภาพข้อมูลใน Sheet1

ภาพข้อมูลใน Sheet2

ภาพการสรุปผลต่างของข้อมูลระหว่าง Sheet1 กับ Sheet2

จากภาพด้านบนจะเป็นการหาผลต่างระหว่าง 2 ชีทโดยยึดค่าในคอลัมน์ A เป็นหลัก เราสามารถเขียนสูตรที่ Sheet3 ได้ดังนี้ครับ

1. ที่เซลล์ G1:G2 คีย์ ไม่มีในชีท1, ไม่มีในชีท2 ตามลำดับ
2. ที่เซลล์ H1 คีย์สูตร
   =SUM(IF(ISNA(MATCH(sheet2!A2:A117,sheet1!A2:A119,0)),1))
   Ctrl+Shift+Enter
3. ที่เซลล์ H2 คีย์สูตร
   =SUM(IF(ISNA(MATCH(sheet1!A2:A119,sheet2!A2:A117,0)),1))
   Ctrl+Shift+Enter
4. ที่เซลล์ H3 คีย์สูตร
   =SUM(H1:H2)
   Enter
5. ที่เซลล์ I1 คีย์สูตร
   =SUM(H$1:H1)-H1+1
   Enter > Copy ไปยัง I2
6. ที่เซลล์ E3 คีย์สูตร
   =IF(ROWS(E$3:E3)>$H$3,"",LOOKUP(ROWS(E$3:E3),$I$1:$I$2,$G$1:$G$2))
   Enter > Copy ลงด้านล่าง
7. ที่เซลล์ B3 คีย์สูตร
   =IF(ROWS(B$3:B3)>$H$3,"",CHOOSE(MATCH($E3,$G$1:$G$2,0),INDEX(sheet1!B$2:B$119,SMALL(IF(ISNA(MATCH(sheet2!$A$2:$A$117,sheet1!$A$2:$A$119,0)),ROW(sheet2!$A$2:$A$117)-ROW(sheet2!$A$2)+1),COUNTIF($E$3:$E3,$E3))),INDEX(sheet1!B$2:B$119,SMALL(IF(ISNA(MATCH(sheet1!$A$2:$A$119,sheet2!$A$2:$A$117,0)),ROW(sheet1!$A$2:$A$119)-ROW(sheet1!$A$2)+1),COUNTIF($E$3:$E3,$E3)))))
   Ctrl+Shift+Enter > Copy ไปทางขวาและลงด้านล่าง

การสรุปผลต่างออกมาลักษณะนี้จะช่วยลดเวลาในการตรวจสอบข้อมูลด้วยสายตาและลดความผิดพลาดลงได้ครับ

การแสดงข้อมูลจากฐานข้อมูลตามเงื่อนไขด้วย VBA

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

หลังจากนำข้อมูลมาวางแล้วก็กำหนดให้จัดรูปแบบให้ด้วยเพื่อให้มีความสวยงามน่าใช้งาน โดยทำการจัดรูปแบบเฉพาะช่วงที่มีข้อมูลเท่านั้น ดูภาพ Database และ Report ตามด้านล่างครับ

ภาพตัวอย่างฐานข้อมูลพนักงาน

ภาพตัวอย่าง Report ที่ต้องการ

ภาพแสดงตัวอย่างการทำงาน

โดยใช้ Code VBA ดังต่อไปนี้

 Code ด้านล่างวางไว้ที่ชีท Report

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$2" And Target <> "" Then
        ShowEmp
    ElseIf Target.Address = "$E$2" And Target = "" Then
        MsgBox "Please select data."
    End If
End Sub

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

Option Explicit
Option Base 1

Sub ShowEmp()
    Dim a() As Variant, lng As Long
    Dim r As Range, rAll As Range
    Dim rt As Range, rl As Long
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    rl = Rows.Count
    With Worksheets("Database")
        Set rAll = .Range("F2", .Range("F" & rl).End(xlUp))
    End With
    For Each r In rAll
        If r = Worksheets("Report").Range("E2") Then
            lng = lng + 1
            ReDim Preserve a(5, lng)
            a(1, lng) = lng
            a(2, lng) = r.Offset(0, -5)
            a(3, lng) = r.Offset(0, -4)
            a(4, lng) = r.Offset(0, -3)
            a(5, lng) = r.Offset(0, -2)
        End If
    Next r
    If lng > 0 Then
        With Worksheets("Report")
            Set rt = .Range("A5", .Range("E" & lng - 1 + 5))
            .Range("A5", .Range("A" & rl).End(xlUp).Offset(0, 4)).ClearContents
            .Range("A5:E5").Copy
            rt.PasteSpecial xlPasteFormats
            rt = Application.Transpose(a)
            .Range("B5", .Range("B" & rl).End(xlUp)).NumberFormat = "000000"
            .Range(.Range("A4").End(xlDown).Offset(1, 0), .Range("E" & rl)).Clear
            .Range("E2").Activate
        End With
    Else
        MsgBox "Data not found."
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

ซึ่งมีหลักการทำงานสำคัญ ๆ ดังนี้

1. นำเซลล์ E2 ในชีท Report ไปเปรียบเทียบกับค่าในคอลัมน์ F ของชีท Database
   
2. ค่าที่ตรงกันจะเก็บไว้ใน Array
   
3. นำ Array มาวางที่ชีท Report
   
4. จัดรูปแบบให้กับรายงานที่ได้

นอกจากการใช้ Array เข้ามาช่วยแล้ว ทางเลือกอื่นในการเขียน Code เช่น สามารถเลือกเขียน Code ให้ทำ Advanced Filter แล้วเลือกเฉพาะข้อมูลที่ต้องการมาแสดงเช่นนี้เป็นต้นครับ

การแสดงรายการซ้ำตามจำนวนที่กำหนด

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

กรณีแบบยากเราจะใช้สูตร Array ในการแสดงรายการซ้ำ ๆ ตามจำนวนที่กำหนด ยกตัวอย่างเช่นตามภาพด้านล่าง ข้อมูลอยู่ที่ A2:A7 จำนวนที่ระบุว่าซ้ำกันกี่ครั้งอยู่ที่ B2:B7 โดยจะนำรายการมาแสดงตั้งแต่ F2 เป็นต้นไป

ภาพแสดงการแสดงรายการซ้ำตามจำนวนที่กำหนดแบบยาก

วิธีการ

1. ที่เซลล์ B8 คีย์สูตรเพื่อหายอดรวมตัวเลขทั้งหมด เพื่อประโยชน์ในการกำหนดจำนวนบรรทัดสูงสุดที่จะแสดงรายการ
   =SUM(B2:B7)
   Enter
2. ที่เซลล์ B9 หาค่าสูงสุดของช่วง B2:B7
   =MAX(B2:B7)
   Enter
3. ที่เซลล์ F2 คีย์สูตรเพื่อแสดงรายการ
   =IF(ROWS($F$2:F2)>$B$8,"",INDEX($A$2:$A$7,SMALL(IF(TRANSPOSE(ROW(INDIRECT("1:"&$B$9)))<=$B$2:$B$7,ROW($B$2:$B$7)-ROW($B$2)+1),ROWS($F$2:F2))))
   Ctrl+Shift+Enter > Copy ลงด้านล่างตามต้องการ สังเกตการกดแป้นจะต้องกด Ctrl+Shift ค้างไว้ก่อนแล้วตามด้วย Enter หากกดแป้นถูกต้องจะเห็นเครื่องหมายปีกกาคร่อมสูตร ปีกกานี้จะคีย์เข้าไปเองไม่ได้ครับ การแก้ไขปรับปรุงสูตรจะต้องกด Ctrl+Shift+Enter ทุกครั้ง

กรณีแบบง่ายจะใช้สูตร Lookup และเพิ่ม C2:C7 มาช่วย โดยจะนำข้อมูลมาแสดงตั้งแต่ G2 เป็นต้นไปตามภาพด้านล่างครับ

ภาพแสดงการแสดงรายการซ้ำตามจำนวนที่กำหนดแบบง่าย

วิธีการ

1. ที่เซลล์ B8 คีย์สูตรเพื่อหายอดรวมตัวเลขทั้งหมด เพื่อประโยชน์ในการกำหนดจำนวนบรรทัดสูงสุดที่จะแสดงรายการ
   =SUM(B2:B7)
   Enter
2. ที่เซลล์ C2 คีย์สูตรเพื่อประโยชน์ในการ Lookup ข้อมูล
   =SUM(B$2:B2)-B2+1
   Enter > Copy ลงด้านล่าง
3. ที่เซลล์ G2 คีย์สูตรเพื่อแสดงรายการ
   =IF(ROWS($G$2:G2)>$B$8,"",LOOKUP(ROWS($G$2:G2),$C$2:$C$7,$A$2:$A$7))
   Enter > Copy ลงด้านล่างตามต้องการ

การรวมข้อมูลโดยอ้างอิงค่าที่เว้นเป็นช่วง ๆ

ปกติรายงานที่ได้มาจากสรุปข้อมูลแล้วมีการเว้นช่วงเอาไว้เช่นรายงานที่ได้จาก PivotTable แล้วมีการ Copy ค่าตามรูปแบบเดิม ๆ มาใช้ จากภาพด้านล่างจะเห็นว่าคอลัมน์ B มีการเว้นข้อมูลไว้เป็นช่วง ๆ การจะให้ง่ายต่อการใช้งานต้องเติม Field ด้านล่างให้เหมือนด้านบน แล้วใช้สูตร Sumif เข้ามาช่วย
แต่หากต้องการจะใช้ความสามารถของสูตรต่าง ๆ เพื่อสรุปข้อมูลโดยไม่ต้องปรับแต่งฐานข้อมูลก็สามารถทำได้แต่สูตรค่อนข้างซับซ้อนและยากต่อการทำความเข้าใจ แต่ก็เป็นประโยชน์ในการนำเสนอแนวความคิด เพื่อนำไปต่อยอดในการใช้งานด้านอื่น ๆ ครับ

ภาพประกอบการรวมข้อมูลโดยอ้างอิงที่เว้นเป็นช่วง ๆ



การที่เราจะรวมข้อมูลในอลัมน์ D จึงต้องประยุกต์สูตรต่าง ๆ มาใช้ โดยคีย์สูตรที่ D5 ดังนี้

=IF(AND(LOOKUP(CHAR(255),$B$2:B5)=LOOKUP(CHAR(255),$B$2:B6),C6=""),SUM(OFFSET($C$1,MATCH(9.99999999999999E+307,C:C),0):OFFSET($C$1,MATCH(9.99999999999999E+307,$D$2:D4)+1,0)),IF(LOOKUP(CHAR(255),$B$2:B5)<>LOOKUP(CHAR(255),$B$2:B6),SUM(OFFSET($C$1,MATCH(CHAR(255),$B$2:B5),0):OFFSET($C$1,MATCH(CHAR(255),$B$2:B6)-1,0)),""))

โดยมีสูตรสำคัญ ๆ ดังนี้

1. LOOKUP(CHAR(255),$B$2:B5) เป็นการหาค่าอักขระสุดท้ายในช่วง B2:B5
2. OFFSET(…):OFFSET(…) เป็นการนำผลลัพธ์ของสูตร OFFSET มาเป็นช่วงข้อมูลใหม่
3. MATCH(CHAR(255),$B$2:B5),0) เป็นการหาตำแหน่งของอักขระสุดท้ายในช่วง B2:B5
4. MATCH(9.99999999999999E+307,C:C) เป็นการหาตำแหน่งสุดท้ายที่พบตัวเลขในช่วง C:C

การ Lookup ข้อมูลพร้อมทำ Hyperlink กลับไปยังต้นแหล่ง

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

ยกตัวอย่างการดึงข้อมูลตามเงื่อนไขตามภาพด้านล่าง ใน Sheet2 เป็นการดึงข้อมูลเฉพาะเซลล์ที่มีความยาวข้อมูลระหว่าง 60 ถึง 70 ตัวอักษรจาก Sheet1 และสำหรับเซลล์ที่แสดงผลนั้น สามารถอ้างถึงตำแหน่งที่มาของข้อมูลและยังมีความพิเศษที่สามารถคลิกลงบนรายการที่สนใจ เพื่อ Link กับไปยังข้อมูลต้นแหล่งได้อีกด้วย

ภาพประกอบการ Lookup พร้อมทำ Hyperlink กลับไปยังต้นแหล่ง

ตามวิธีการด้านล่างนี้จะต้อง Save ไฟล์ก่อน เพราะจะต้องนำชื่อไฟล์มาใช้ในสูตร

ซึ่งมีขั้นตอนดังนี้

1. Sheet1 เซลล์ B1 คีย์ 0 เพื่อใช้เป็นเซลล์ต้นทางในสูตร Lookup
2. Sheet1 เซลล์ B2 คีย์สูตรเพื่อให้ลำดับกับข้อมูลที่ตรงตามเงื่อนไข
   =IF(AND(LEN(A2)>=60,LEN(A2)<=70),LOOKUP(9.99999999999999E+307,$B$1:B1)+1,"")
   Enter > Copy ลงด้านล่าง
3. Sheet2 เซลล์ B1 คีย์สูตรเพื่อหาจำนวนที่พบทั้งหมดจากข้อ 2
   =LOOKUP(9.99999999999999E+307,Sheet1!B:B)
   Enter
4. Sheet2 เซลล์ A2 คีย์สูตรเพื่อ List รายการที่ตรงตามเงื่อนไข
   =IF(ROWS($A$2:A2)>$B$1,"",HYPERLINK("[LookupListHyperlink.xls]"&"Sheet1!A"&MATCH(ROWS($A$2:A2),Sheet1!$B:$B),"Sheet1 Cell A"&MATCH(ROWS($A$2:A2),Sheet1!$B:$B)))
   Enter > Copy ลงด้านล่าง

Note: จากสูตรในข้อ 4 ขอเพิ่มเติมคร่าว ๆ เพื่อเป็นแนวทางครับ

1. LookupListHyperlink.xls คือชื่อไฟล์
2. Sheet1!A เป็นการระบุว่าให้นำมาจาก Sheet1 คอลัมน์ A
3. MATCH(ROWS($A$2:A2),Sheet1!$B:$B) หาตำแหน่งที่พบข้อมูล

การนำข้อมูลจากหลาย Sheet มาต่อกันใน Sheet เดียว

สำหรับข้อมูลที่อยู่ในฟอร์มเดียวกันแต่แยกเป็นหลาย Sheet และต้องการนำมาต่อกันเป็นฐานข้อมูลใน Sheet เดียวกันนั้น สามารถใช้สูตรทำได้ครับ แต่อาจจะมีวิธีการหลายขั้นตอน ดังจะแสดงตามด้านล่าง

ภาพ Sheet ที่ใช้รวมข้อมูล 



ภาพ Sheet ที่ 1 



ภาพ Sheet ที่ 2 



ภาพ Sheet ที่ 3 



จากภาพด้านบนเป็นการนำข้อมูล Sheet1, 2, 3 มาต่อกัน ให้ทำตามลำดับดังนี้ครับ

1. เขียนชื่อชีทเรียงกันไว้ตามต้องการที่ G1:G3
2. เซลล์ H1 คีย์สูตรเพื่อนับว่ามีข้อมูลใน Sheet1 เท่าไร
   =COUNTA(INDIRECT("'"&G1&"'!"&"A2:A65536"))
   Enter แล้ว Copy ลงด้านล่าง
3. เซลล์ H4 รวมจำนวนจากด้านบน คีย์
   =SUM(H1:H3)
   Enter
4. เซลล์ I1 ใช้สูตรในการ Run ข้อมูลใหม่เพื่อใช้ในสูตร Lookup
   =SUM($H$1:H1)-H1+1
   Enter แล้ว Copy ลงด้านล่าง
5. เซลล์ E2 คีย์สูตรเพื่อ List รายชื่อ Sheet
   =IF(ROWS($E$2:E2)>$H$4,"",LOOKUP(ROWS($E$2:E2),$I$1:$I$3,$G$1:$G$3))
   Enter แล้ว Copy ไปด้านล่าง
6. เซลล์ A2 คีย์สูตรเพื่อนำข้อมูลใน Sheet ต่าง ๆ มาแสดงต่อกัน
   =IF($E2<>"",INDIRECT("'"&$E2&"'!"&SUBSTITUTE(ADDRESS(1,COLUMN()),1,"")&COUNTIF($E$2:$E2,$E2)+1),"")
   Enter แล้ว Copy ไปทางขวาและลงด้านล่าง

จะเห็นว่าการนำข้อมูลจากหลาย ๆ Sheet มาต่อกันนั้น ไม่ใช่เรื่องยากอีกต่อไปสำหรับพวกเราครับ

การหาค่าสุดท้ายที่ไม่เป็นค่าว่าง

กรณีค่าหาค่าสุดท้าย สามารถดูได้ที่นี่ซึ่งมีทั้งหาค่าสุดท้ายที่เป็นตัวเลข, เป็นอักขระ และค่าสุดท้ายโดยไม่สนใจว่าเป็นตัวเลขหรืออักขระ

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

ค่าว่างอาจเป็นค่าที่ว่างจริง ๆ ไม่มีข้อมูลใดบรรจุอยู่ข้างในเซลล์นั้นหรือ ค่าว่างที่ได้จากสูตร เช่น =If(A1=1,1,"") หาก A1 ไม่เท่ากับ 1 จะได้ผลลัพธ์เป็นช่องว่าง และหากเป็นช่องว่างแล้ว สูตรการหาค่าสุดท้ายที่เรากำลังกล่าวถึงจะไม่สนใจค่านี้

สมมุติ A1:A1000 คือขอบเขตที่ต้องการแสดงข้อมูลตัวสุดท้าย แต่ไม่รวมค่าว่างที่ได้จากสูตร

ลองคีย์ที่ Cell ใดๆ ตามนี้ครับ

=Lookup(2,1/(A1:A1000<>""),A1:A1000)

Enter

จากสูตร

=Lookup(2,1/(A1:A1000<>""),A1:A1000)

แต่ละส่วนประกอบสูตรจะถูกแบ่งด้วยเครื่องหมายคอมม่า

ดังนั้น สิ่งที่เราต้องทราบมีดังนี้

1. 2 หมายถึงอะไร
2. 1/(A1:A1000<>"") หมายถึงอะไร
3. A1:A1000 หมายถึงอะไร

การจะทำความเข้าใจสูตรนี้ ต้องเข้าใจสูตร Lookup แบบเวกเตอร์ก่อนครับ ไวยากรณ์ของ Lookup แบบเวกเตอร์คือ

LOOKUP(lookup_value,lookup_vector,result_vector)

เวคเตอร์คือช่วงของแถวเพียงหนึ่งแถวหรือคอลัมน์เพียงหนึ่งคอลัมน์ รูปแบบเวคเตอร์ของ LOOKUP จะค้นหาค่าในช่วงของแถวหนึ่งแถวหรือคอลัมน์หนึ่งคอลัมน์ (ที่รู้จักกันในชื่อเวคเตอร์) และส่งคืนค่าจากตำแหน่งเดียวกันในช่วงของหนึ่งแถวหรือหนึ่งคอลัมน์ช่วงที่สอง

Lookup_value ค่าที่ LOOKUP จะค้นหาในเวคเตอร์แรก หรือค่าที่จะค้นหาใน lookup_vector

Lookup_value อาจเป็นตัวเลข ข้อความ ค่าตรรกะ หรือชื่อหรือการอ้างอิงที่อ้างถึงค่า

Lookup_vector ช่วงที่ประกอบด้วยแถวเพียงหนึ่งแถวหรือคอลัมน์เพียงหนึ่งคอลัมน์เท่านั้น ค่าใน lookup_vector อาจเป็นข้อความ ตัวเลขหรือค่าตรรกะ
สิ่งสำคัญ ค่าใน lookup_vector ต้องอยู่ในลำดับจากน้อยไปหามาก: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE หากไม่เรียงในลักษณะนี้ ฟังก์ชั่น LOOKUP อาจไม่แสดงค่าที่ถูกต้อง ข้อความที่เป็นตัวพิมพ์ใหญ่และตัวพิมพ์เล็กนั้นเทียบเท่ากัน

Result_vector ช่วงที่ประกอบด้วยแถวหรือแถวหรือคอลัมน์หนึ่งคอลัมน์เท่านั้น ช่วงนี้ต้องมีขนาดเดียวกับ lookup_vector

อันนี้สำคัญ

หาก LOOKUP ไม่พบ lookup_value ฟังก์ชั่นนี้จะจับคู่ค่าที่ใหญ่ที่สุดใน lookup_vector ที่น้อยกว่าหรือเท่ากับ lookup_value
หาก lookup_value น้อยกว่าค่าที่น้อยที่สุดใน lookup_vector ฟังก์ชั่น LOOKUP จะให้ค่าข้อผิดพลาดเป็น #N/A

ดังนั้น ถ้าเทียบตามคำอธิบายข้างบน

1. เลข 2 หมายถึง Lookup value หรือค่าที่ต้องการค้นหาในช่วงข้อมูลตามข้อ 2
2. 1/(A1:A1000<>"") หมายถึงช่วงข้อมูลที่ต้องการค่า Lookup_value ในข้อ 1
3. A1:A1000 หมายถึง ช่วงข้อมูลผลลัพธ์ที่ต้องการนำมาแสดง
   หมายถึงว่า หากพบค่าข้อ 1 ในช่วงข้อมูลตามข้อ 2 อยู่ที่ตำแหน่งไหน ให้นำค่าในข้อ 3 ในตำแหน่งเดียวกันมาแสดง

ทีนี้มาแกะสูตรกัน เพื่อให้ง่าย ต้องสมมุติโจทย์ให้ง่ายก่อน สมมุติว่า A1:A5 ประกอบด้วย
A1=1
A2=5
A3=B
A4 คีย์สูตร=if(a2=5,"",0)
A5=8
ตามลำดับ

ที่ B1 เขียนสูตร

=LOOKUP(2,1/(A1:A5<>""),A1:A5)

การแกะสูตรต่อไปนี้จะทำที่ Formula bar หรือคลิก B1 แล้วกด F2 ก็สามารถทำใน Cell B1 ได้เลยเช่นกัน

วิธีการมีดังนี้

1. ลากเมาส์คลุม (A1:A5<>"") แล้วกด F9 จะได้
   =LOOKUP(2,1/{TRUE;TRUE;TRUE;FALSE;TRUE},A1:A5)
2. ลากเมาส์คลุม 1/{TRUE;TRUE;TRUE;FALSE;TRUE}แล้วกด F9 จะได้
   =LOOKUP(2,{1;1;1;#DIV/0!;1},A1:A5)
3. ลากเมาส์คลุม A1:A5 แล้วกด F9 จะได้
   =LOOKUP(2,{1;1;1;#DIV/0!;1},{1;5;"B";"";8})
   จะเห็นว่าไม่สามารถค้นหาค่า 2 จากช่วง {1;1;1;#DIV/0!;1} ได้เพราะไม่มี แต่จากคำอธิบายข้างบนบอกว่า lookup_vector จะต้องเรียงกันจากน้อยไปหามาก และหากไม่มีค่าที่ตรงกันกับค่าที่ค้นหา จะใช้ค่าที่ใหญ่ที่สุดหรือค่าที่มากที่สุดที่เจอใน lookup_vector ที่น้อยกว่าหรือเท่ากับ lookup_value
   จากความหมายข้างต้น ผลลัพธ์ที่ได้จะเป็นเลข 1 ตัวสุดท้ายเท่านั้น เพราะถือว่าเรียงจากน้อยไปหามาก และเป็นค่าใหญ่สุดแล้ว เมื่อได้ดังนี้แล้ว 1 ตัวสุดท้ายจะตรงกับตัวใดในช่วง result_vector
   =LOOKUP(2,{1;1;1;#DIV/0!;1},{1;5;"B";"";8})
   ตอบที่ได้ก็คือ 8

ลองเขียนสูตรใหม่เหมือนตอนเริ่มต้น ลองลบเลข 8 ใน A5 ออกแล้วสังเกตดูผลลัพธ์ครับ

การหาค่าสุดท้ายที่ไม่เป็นค่าว่างที่ได้จากสูตร

กรณีค่าหาค่าสุดท้าย สามารถดูได้ที่ http://snasui.blogspot.com/2008/11/blog-post_09.html ซึ่งมีทั้งหาค่าสุดท้ายที่เป็นตัวเลข, เป็นอักขระ และค่าสุดท้ายโดยไม่สนใจว่าเป็นตัวเลขหรืออักขระ

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

ค่าว่างที่ได้จากสูตรที่พูดถึงในวรรคก่อนมีลักษณะเป็นดังนี้ครับ เช่น =If(A1=1,1,””) หาก A1 ไม่เท่ากับ 1 จะได้ผลลัพธ์เป็นช่องว่าง และหากเป็นช่องว่างแล้ว สูตรการหาค่าสุดท้ายที่เรากำลังกล่าวถึงจะไม่สนใจค่านี้

สมมุติ A1:A1000 คือขอบเขตที่ต้องการแสดงข้อมูลตัวสุดท้าย แต่ไม่รวมค่าว่างที่ได้จากสูตร

ลองคีย์ที่ Cell ใดๆ ตามนี้ครับ
=Lookup(2,1/(A1:A1000<>""),A1:A1000)
แล้ว Enter

จากสูตร

=Lookup(2,1/(A1:A1000<>""),A1:A1000)

แต่ละส่วนประกอบสูตรจะถูกแบ่งด้วยเครื่องหมายคอมม่า

ดังนั้น สิ่งที่เราต้องทราบมีดังนี้

1) 2 หมายถึงอะไร
2) 1/(A1:A1000<>"") หมายถึงอะไร
3) A1:A1000 หมายถึงอะไร

การจะทำความเข้าใจสูตรนี้ ต้องเข้าใจสูตร Lookup แบบเวกเตอร์ก่อนครับ

LOOKUP(lookup_value,lookup_vector,result_vector)

เวคเตอร์คือช่วงของแถวเพียงหนึ่งแถวหรือคอลัมน์เพียงหนึ่งคอลัมน์ รูปแบบเวคเตอร์ของ LOOKUP จะค้นหาค่าในช่วงของแถวหนึ่งแถวหรือคอลัมน์หนึ่งคอลัมน์ (ที่รู้จักกันในชื่อเวคเตอร์) และส่งคืนค่าจากตำแหน่งเดียวกันในช่วงของหนึ่งแถวหรือหนึ่งคอลัมน์ช่วงที่สอง

Lookup_value  ค่าที่ LOOKUP จะค้นหาในเวคเตอร์แรก หรือค่าที่จะค้นหาใน lookup_vector

Lookup_value อาจเป็นตัวเลข ข้อความ ค่าตรรกะ หรือชื่อหรือการอ้างอิงที่อ้างถึงค่า

Lookup_vector  ช่วงที่ประกอบด้วยแถวเพียงหนึ่งแถวหรือคอลัมน์เพียงหนึ่งคอลัมน์เท่านั้น ค่าใน lookup_vector อาจเป็นข้อความ ตัวเลขหรือค่าตรรกะ
สิ่งสำคัญ  ค่าใน lookup_vector ต้องอยู่ในลำดับจากน้อยไปหามาก: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE หากไม่เรียงในลักษณะนี้ ฟังก์ชั่น LOOKUP อาจไม่แสดงค่าที่ถูกต้อง ข้อความที่เป็นตัวพิมพ์ใหญ่และตัวพิมพ์เล็กนั้นเทียบเท่ากัน

Result_vector  ช่วงที่ประกอบด้วยแถวหรือแถวหรือคอลัมน์หนึ่งคอลัมน์เท่านั้น ช่วงนี้ต้องมีขนาดเดียวกับ lookup_vector

อันนี้สำคัญ

หาก LOOKUP ไม่พบ lookup_value ฟังก์ชั่นนี้จะจับคู่ค่าที่ใหญ่ที่สุดใน lookup_vector ที่น้อยกว่าหรือเท่ากับ lookup_value
หาก lookup_value น้อยกว่าค่าที่น้อยที่สุดใน lookup_vector ฟังก์ชั่น LOOKUP จะให้ค่าข้อผิดพลาดเป็น #N/A

ดังนั้น ถ้าเทียบตามคำอธิบายข้างบน

1. เลข 2 หมายถึง Lookup value หรือค่าที่ต้องการค้นหาในช่วงข้อมูลตามข้อ 2
2. 1/(A1:A1000<>"") หมายถึงช่วงข้อมูลที่ต้องการค่า Lookup_value ในข้อ 1
3. A1:A1000 หมายถึง ช่วงข้อมูลผลลัพธ์ที่ต้องการนำมาแสดง
หมายถึงว่า หากพบค่าข้อ 1 ในช่วงข้อมูลตามข้อ 2 อยู่ที่ตำแหน่งไหน ให้นำค่าในข้อ 3 ในตำแหน่งเดียวกันมาแสดง

ทีนี้มาแกะสูตรกัน เพื่อให้ง่าย ต้องสมมุติโจทย์ให้ง่ายก่อน

สมมุติว่า A1:A5 ประกอบด้วย
A1=1
A2=5
A3=B
A4 คีย์สูตร=if(a2=5,"",0)
A5=8
ตามลำดับ

ที่ B1 เขียนสูตร

=LOOKUP(2,1/(A1:A5<>""),A1:A5)

ลองมาแกะสูตรกัน ต่อไปนี้จะทำที่ Formula bar หรือคลิก B1 แล้วกด F2 ก็สามารถทำใน Cell B1 ได้เลยเช่นกัน

ลากเมาส์คลุม (A1:A5<>"") แล้วกด F9 จะได้

=LOOKUP(2,1/{TRUE;TRUE;TRUE;FALSE;TRUE},A1:A5)

ลากเมาส์คลุม 1/{TRUE;TRUE;TRUE;FALSE;TRUE}แล้วกด F9 จะได้

=LOOKUP(2,{1;1;1;#DIV/0!;1},A1:A5)

ลากเมาส์คลุม A1:A5 แล้วกด F9 จะได้

=LOOKUP(2,{1;1;1;#DIV/0!;1},{1;5;"B";"";8})

จะเห็นว่าไม่สามารถค้นหาค่า 2 จากช่วง {1;1;1;#DIV/0!;1} ได้เพราะไม่มี แต่จากคำอธิบายข้างบนบอกว่า lookup_vector จะต้องเรียงกันจากน้อยไปหามาก และหากไม่มีค่าที่ตรงกันกับค่าที่ค้นหา จะใช้ค่าที่ใหญ่ที่สุดหรือค่าที่มากที่สุดที่เจอใน lookup_vector ที่น้อยกว่าหรือเท่ากับ lookup_value

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

=LOOKUP(2,{1;1;1;#DIV/0!;1},{1;5;"B";"";8})

คำตอบที่ได้ก็คือ 8

ลองเขียนสูตรใหม่เหมือนตอนเริ่มต้น ลองลบเลข 8 ใน A5 ออกแล้วสังเกตดูผลลัพธ์ครับ

หาค่าสุดท้าย

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

1. การหาตัวเลขสุดท้าย
   =Lookup(9.99999999999999e307,A:A)
   เมื่อ A คือคอลัมน์ที่ต้องการหาค่าสุดท้าย และ
   =Lookup(9.99999999999999e307,5:5)
   เมื่อบรรทัดที่ 5 คือบรรทัดที่ต้องการหาค่าสุดท้าย
2. การหาตัวอักษรสุดท้าย
   =Lookup(Char(255),A:A)
   เมื่อต้องการหาค่าสุดท้ายที่เป็นตัวอักษรในคอลัมน์ A และ
   =Lookup(Char(255),5:5)
   เมื่อต้องการหาค่าสุดท้ายที่เป็นตัวอักษรในบรรทัดที่ 5
3. หากต้องการหาค่าสุดท้ายโดยไม่สนใจว่าค่านั้นเป็นตัวเลขหรืออักขระ
   =Lookup(2,1/(A1:A65535<>""),A1:A65535)
   เมื่อต้องการหาค่าสุดท้ายในคอลัมน์ A และ
   =Lookup(2,1/(5:5<>""),5:5)
   เมื่อต้องการหาค่าสุดท้ายในบรรทัดที่ 5

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