extract-array1

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

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

หลักการ

extract-array2

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

  • ใช้ IF เพื่อเช็คว่าข้อมูลในแถวนั้นๆ อยุ่ใน group a หรือไม่
      • ถ้าอยู่ใน group a ให้เอาข้อมูลแถวกลับมาว่าช่องนั้นว่าอยู่แถวที่เท่าไหร่ โดยใช้สูตร ROW เข้าช่วย
      • ถ้าไม่อยู่ใน group a ให้แสดงค่าเป็นค่าว่าง
    • ได้ว่า =IF($B$4:$B$9=$C$11,ROW($B$4:$B$9),””) อย่าลืมกด Ctrl+Shift+Enter
    • หากลองลากแถบดำแล้วกด F9 ดูจะเห็นว่าตัวเลขที่จะขึ้นมาก็จะมีเลข {4;””;6;””;””;9} ซึ่งเป็นแถวของข้อมูลที่อยู่ใน Group a นั่นเอง (อันไหนไม่ใช่ 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 เท่านี้ก็เป็นอันเรียบร้อย