วิธี Extract ข้อมูลหลายๆ รายการ แบบใช้และไม่ใช้ Array Formula

จากบทความก่อนหน้าได้เกริ่นเรื่องการทำงานของ 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 เท่านี้ก็เป็นอันเรียบร้อย

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