สูตร excel แสดงข้อมูลที่ต้องการ

จากบทความก่อนหน้าได้เกริ่นเรื่องการทำงานของ Array Formula ไปแล้ว คราวนี้จะขอยกตัวอย่างการใช้ที่ค่อนข้าง Advance ขึ้นแล้วนั่นก็คือ การ Extract ข้อมูลหลายๆ รายการ ออกมาตามเงื่อนไขที่กำหนด เช่น โจทย์คือ “แสดงทุกรายการที่อยู่ใน group a ” ออกมา จะเห็นว่าแบบนี้ใช้ VLOOKUP ตรงๆ ไม่ได้แน่นอน เพราะ VLOOKUP จะเจอแค่ข้อมูลที่เจอเป็นแถวแรกเท่านั้น

แล้วถ้าเจอแบบนี้เราจะต้องเขียนสูตรยังไง มาดู VDO สอนเทคนิคการทำข้างล่างนี้ได้เลยครับ

หลักการ

1. หาแถวที่อยู่ใน Group ที่ต้องการ

• ใช้ IF เพื่อเช็คว่าข้อมูลในแถวนั้นๆ อยุ่ใน group a หรือไม่
  • ถ้าอยู่ใน group a ให้เอาข้อมูลแถวกลับมาว่าช่องนั้นว่าอยู่แถวที่เท่าไหร่ โดยใช้สูตร ROW เข้าช่วย
  • ถ้าไม่อยู่ใน group a ให้แสดงค่าเป็นค่าว่าง

2. หาแถวที่น้อยที่สุดเป็นลำดับ 1,2,3…

• ใช้สูตร SMALL(array,k) เพื่อหาข้อมูลที่มีค่าน้อยที่สุดเป็นลำดับ k คือ 1,2,3,… จากช่วง array ที่กำหนด  (ในที่นี้เป็นค่าตัวเลขแถว) โดยที่สูตร Small นี้จะไม่สนใจข้อมูลที่เป็นค่าว่าง
  • SMALL(ช่วงที่ต้องการ,1) หมายถึง ค่าที่น้อยที่สุดในช่วงที่กำหนด
    • ได้ว่า =SMALL(IF($B$4:$B$9=$C$11,ROW($B$4:$B$9),””),1) อย่าลืมกด Ctrl+Shift+Enter
    • ซึ่งถ้าช่วงเป็น {4;””;6;””;””;9} จะได้ว่าเลขที่น้อยที่สุดคือ 4
  • SMALL(ช่วงที่ต้องการ,2) หมายถึง ค่าที่น้อยที่สุดเป็นลำดับ2 ในช่วงที่กำหนด
    • ได้ว่า =SMALL(IF($B$4:$B$9=$C$11,ROW($B$4:$B$9),””),2) อย่าลืมกด Ctrl+Shift+Enter
    • ซึ่งถ้าช่วงเป็น {4;””;6;””;””;9} จะได้ว่าเลขที่น้อยที่สุดเป็นลำดับ2 คือ 6 

3. ดึงข้อมูลที่ต้องการออกมาด้วย INDEX

• ถึงขั้นนี้เราได้ลำดับแถวของข้อมูลใน Group a แล้ว ต่อไปเราสามารถใช้ INDEX ดึงข้อมูลที่ต้องการมาได้เลยครับ
  • และถ้าเราใช้ INDEX ครอบตั้งแต่ช่องแรก เราไม่ต้องมีการ Adjust ค่า Row เหมือนใน VDO ของผมก็ได้ครับ
  • เช่น ในช่องที่แสดงชื่อลำดับแรก (1) คือ สมชาย เขียนสูตรแค่นี้ก็ทำงานได้เช่นกัน โดยคอลัมน์ Name เป็นคอลัมน์ C ซึ่งเป็นคอลัมน์ที่ 3 ของตาราง excel
    =INDEX($A$1:$D$16,SMALL(IF($B$4:$B$9=$C$11,ROW($B$4:$B$9),””),1),3) อย่าลืมกด Ctrl+Shift+Enter
  • ในช่องที่แสดงชื่อลำดับสอง (2) คือ สมยศ เขียนสูตรแค่นี้ก็ทำงานได้เช่นกัน โดยคอลัมน์ Name เป็นคอลัมน์ C ซึ่งเป็นคอลัมน์ที่ 3 ของตาราง excel
    =INDEX($A$1:$D$16,SMALL(IF($B$4:$B$9=$C$11,ROW($B$4:$B$9),””),2),3) อย่าลืมกด Ctrl+Shift+Enter

4. กำจัด Error ซะ

• ถ้าเป็นชื่อลำดับที่ 4 จะขึ้น Error เป็น #NUM! เพราะมีข้อมูลใน Group a แค่ 3 ตัว ไม่มีตัวที่ 4
  • ทางแก้คือใช้สูตร IF กับ ISERROR มาช่วย ว่าถ้า Error ให้แสดงเป็นค่าว่าง
  • นั้นคือใช้ =IF(ISERROR(สูตรเดิมxxxx),””,สูตรเดิมxxxx) มาครอบอีกที อย่าลืมกด Ctrl+Shift+Enter เท่านี้ก็เป็นอันเรียบร้อย

ผมได้มีเียนบทความตอนใหม่เรื่องนี้ด้วย ลองไปดูได้ที่

สารพัดเทคนิคสำหรับคนอยาก VLOOKUP ผลลัพธ์หลายค่า จากคำค้นหาเดียว

แชร์ความรู้ให้เพื่อนๆ ของคุณ

2    

2    

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

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

ตามปกติในสูตร VLookup, Match, Offset, หรือสูตรด้านฐานข้อมูลใดๆ สามารถรับตำแหน่งตารางได้เพียงตารางเดียวในวงเล็บของสูตร เช่น สูตร =VLOOKUP($H$3,MyData,2,0) ใช้ Range Name ชื่อ MyData เป็นตำแหน่งตารางฐานข้อมูลที่ต้องการค้นหาคำตอบ ถ้าต้องการทำให้สูตร VLookup หรือสูตรฐานข้อมูลสูตรใดๆสูตรเดียวสามารถค้นหาข้อมูลจากหลากหลายตาราง ให้ใช้สูตร IF หรือ Choose ซ้อนเข้าไปในส่วนของ MyData

สมมติว่า มีตารางฐานข้อมูลของลูกค้าหลายๆกลุ่มตามเกรดของลูกค้า ซึ่งลักษณะของตารางฐานข้อมูลของแต่ละกลุ่มมีหน้าตาตารางเหมือนๆกัน เพียงแต่จัดเก็บแยกตารางไว้ต่างพื้นที่ (หรือต่างชีทกัน) แล้วตั้งชื่อตารางว่า ClassA, ClassB, และ ClassC

เมื่อต้องการค้นหาข้อมูล ให้พิมพ์ชื่อ Class A, B, หรือ C ลงไปในเซลล์ B10 ซึ่งตั้งชื่อว่า Class แล้วพิมพ์รหัสลูกค้าลงไปในเซลล์ B14 จะสามารถค้นหาข้อมูลของ Name และ Amount โดยใช้สูตร IF(Class="A", ClassA, IF(Class="B", ClassB, ClassC) ) ซ้อนลงไปในสูตร VLookup เพื่อทำให้สามารถเลือกได้ว่าจะหาข้อมูลจากตารางใดตามเงื่อนไขชื่อ Class กลายเป็นสูตรดังนี้

ในเซลล์ C14
=VLOOKUP( $B$14, IF(Class="A", ClassA, IF(Class="B", ClassB, ClassC) ), 2, 0)

ในเซลล์ D14
=VLOOKUP( $B$14, IF(Class="A", ClassA, IF(Class="B", ClassB, ClassC) ), 3, 0)

หากไม่ต้องการเสียเวลาพิมพ์ชื่อ Class เพื่อเพิ่มความสะดวกให้ใช้รหัสในเซลล์ B14 เลือกค้นหาข้อมูลได้ทันที ให้ใช้สูตร =Left(C14) สร้างลงไปในเซลล์ B10 ซึ่งตั้งชื่อว่า Class เพื่อดึงตัวอักษรที่อยู่ซ้ายสุดของรหัสมาใช้เป็นชื่อ Class ดังนั้นการตั้งชื่อรหัสให้มีความหมายจะทำให้เกิดประโยชน์ช่วยลัดขั้นตอนในการใช้งานอย่างมาก

การใช้สูตร IF เพื่อเลือกพื้นที่ตารางหลายๆแห่งมาใช้ จะกลายเป็นสูตร IF ซ้อนกัน ทำให้สร้างขึ้นมาได้ยากและกลายเป็นสูตรยาวขึ้นเรื่อยๆ จึงแนะนำให้ให้ใช้สูตร Choose แทน IF แต่ต้องใช้ตัวเลขที่ตาราง 1, 2, หรือ 3 มาใช้เป็นเงื่อนไขแทนชื่อตาราง A, B, หรือ C

เมื่อต้องการค้นหาข้อมูล ให้พิมพ์เลขที่ Class 1, 2, หรือ 3 ลงไปในเซลล์ B10 ซึ่งตั้งชื่อว่า Class แล้วพิมพ์รหัสลูกค้าลงไปในเซลล์ B14 จะสามารถค้นหาข้อมูลของ Name และ Amount โดยใช้สูตร CHOOSE(Class,Class1,Class2,Class3) ซ้อนลงไปในสูตร VLookup เพื่อทำให้สามารถเลือกได้ว่าจะหาข้อมูลจากตารางใดตามเงื่อนไขเลขที่ Class กลายเป็นสูตรดังนี้

ในเซลล์ C14
=VLOOKUP( $B$14, CHOOSE(Class,Class1,Class2,Class3), 2, 0)

ในเซลล์ D14
=VLOOKUP( $B$14, CHOOSE(Class,Class1,Class2,Class3), 3, 0)

หากไม่ต้องการเสียเวลาพิมพ์เลขที่ Class เพื่อเพิ่มความสะดวกให้ใช้รหัสในเซลล์ B14 เลือกค้นหาข้อมูลได้ทันที ต้องสร้างตารางสรุปความสัมพันธ์ระหว่างรหัสกับเลขที่ Class ขึ้นมาใช้งาน ตามภาพต่อไปนี้คือเซลล์ F10:G12 โดยตั้งชื่อว่า ClassTable ซึ่งมี column ซ้ายสุดเรียงลำดับเลขรหัส Id เฉพาะรหัสตัวแรกของแต่ละกลุ่ม แล้วให้ใช้สูตร VLookup แบบ Approaching Match =VLOOKUP(B14,ClassTable,2) สร้างลงไปในเซลล์ B10 ซึ่งมีชื่อว่า Class