เจาะลึกฟังก์ชัน INDEX : เคล็ดวิชาการใช้งานสูตร INDEX

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

เริ่มกันด้วยพื้นฐาน Basic ๆ

INDEX อยู่ในฟังก์ชั่นหมวดของ Lookup & Reference โดยเจ้า INDEX มีหน้าที่แบบ Basic ที่สุดก็คือ เอาไว้ “ดึงข้อมูลกลับมาจากตารางอ้างอิงตามพิกัดที่เรากำหนด โดยพิกัดจะต้องระบุเป็นตัวเลขลำดับแถว และลำดับคอลัมน์”

เช่น มีตารางอยู่อันนึง สมมติชื่อ ตารางa มีขนาด 5 แถว x 4 คอลัมน์ เราสามารถกำหนดให้เจ้า INDEX นี่ดึงข้อมูลจากตารางa ในแถวที่ 3 คอลัมน์ที่ 2 กลับมาได้เป็นต้น

ถ้ามาดูตัวอย่างจริง จากรูป ช่อง B5:E9 ผมตั้งชื่อว่า ตารางa ถ้าผมต้องการค่าจากแถวที่3 คอลัมน์ที่ 2 ของตารางนั้น จะต้องเขียนว่า

= INDEX(ตารางอ้างอิง, ลำดับแถว, ลำดับคอลัมน์)

= INDEX(B5:E9,3,2) หรือ = INDEX(ตารางa,3,2)

ซึ่งจะได้ค่ากลับมาเป็น Tony Stark (เพราะอยู่ที่แถวที่ 3 คอลัมน์ที่ 2 ของตาราง B5:E9)

หยุดก่อน! ถ้าคุณคิดว่า INDEX ทำได้แค่นี้ล่ะก็…ผมจะบอกว่าคุณยังไม่รู้จักมันดีพอ ลองอ่าน 7 เคล็ดวิชาต่อไปนี้ แล้วคุณจะรู้จักมันมากขึ้นอีกเยอะครับ!

7 เคล็ดวิชาการใช้งานสูตร INDEX

 1. สูตร INDEX จริงๆแล้วให้ค่ากลับมาเป็น Cell Reference ไม่ใช่สิ่งที่อยู่ในช่องของพิกัดนั้นๆ

อันนี้เคยพูดไปแล้วใน แฉ 10 ความลับของ Excel ที่คุณอาจยังไม่เคยรู้มาก่อน! แต่จะขอพูดอีกครั้ง เพราะเป็น Concept ที่สำคัญมาก

ในตัวอย่างข้างบน =INDEX(B5:E9,3,2) จริงๆ INDEX จะให้ค่ากลับมาเป็น Cell Reference คือ C7 ก่อน แล้ว Excel จึงแปลง C7 เป็นคำว่า Tony Stark อีกทีหนึ่ง (เป็นเพราะว่าไม่ได้เอา Cell Reference ที่ได้ไปทำอย่างอื่นต่อ จึงคืนค่าในช่องมาให้เลย)

2. เลขลำดับแถว และ คอลัมน์ ถ้าเราใส่ค่าตัวเลขที่มากกว่า 0 จะให้ค่าเป็นจุดพิกัด

จะหมายถึงเอาค่าตามพิกัดลำดับนั้นกลับมาให้เป็น Cell Reference ของ ตารางที่กำหนด ในตำแน่งแถวที่กำหนด คอลัมน์ที่กำหนด

3. เลขลำดับแถว และ คอลัมน์ ถ้าเราใส่ค่าเป็น 0 หรือ เว้นว่างไว้ จะให้ค่าเป็นช่วง

หมายถึง INDEX จะให้ค่ากลับมาเป็น Cell Reference ทุกอันของแถวหรือคอลัมน์นั้นมาเป็นช่วงเลย เช่น

= INDEX(ตารางอ้างอิง, ลำดับแถว, ลำดับคอลัมน์)

= INDEX(ตารางa,0,2) หมายถึง เอาตารางa คอลัมน์ที่ 2 มาเลยทุกแถว จะได้ช่วง C5:C9 นั่นเอง

= INDEX(ตารางa,3,0) หมายถึง เอาตารางa แถว 3 มาเลยทุกคอลัมน์เอาล่ะครับ จะได้ช่วง B7:E7 นั่นเอง

= INDEX(ตารางa,0,0) หมายถึง เอาตารางa มาเลยทั้งตาราง ทุกแถว ทุกคอลัมน์ จะได้ช่วง B5:E9 เหมือนเดิม

ที่นี้การให้ค่ากลับมาเป็น Cell Reference แถมเป็นช่วงอีก เวลาจะเอาไปใช้จริง ก็ต้องเอาไปผสมกับสูตรอื่นๆ อีกทีครับ เช่น AVERAGE, SUM, MATCH เป็นต้น เช่น =SUM(INDEX(ตาราง,0,4)) เป็นต้น

4. ตารางอ้างอิง สามารถมีแค่แถวเดียว หรือ คอลัมน์เดียวได้

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

นั่นคือ ถ้ามีตารางอ้างอิงแค่แถวเดียว เลขลำดับที่ตามหลังก็จะเป็นคอลัมน์, ถ้ามีตารางอ้างอิงคอลัมน์เดียว เลขลำดับที่ตามหลังก็จะเป็นแถว
ตัวอย่างเช่น

=INDEX(C5:C9,3)

เพราะมีคอลัมน์เดียว เลข 3 ก็หมายถึงลำดับแถวไปโดยปริยาย ก็จะได้ C7 ซึ่งแปลงเป็นคำว่า Tony Stark เหมือนกัน

=INDEX(ฺB8:E8,2) เพราะมีแถวเดียว เลข 2 ก็หมายถึงลำดับคอลัมน์ไปโดยปริยาย ก็จะได้ C8 ซึ่งแปลงเป็นคำว่า ชิซูกะ เป็นต้น

5. จริงๆ แล้วสูตร INDEX สามารถใช้ตารางอ้างอิงหลายอันได้นะ

สูตร INDEX จริงๆ แล้วเขียนได้ 2 แบบ ซึ่งแบบแรก ผมได้พูดไปข้างบนแล้ว แบบนั้นจะใช้ตารางอ้างอิงได้แค่อันเดียว แต่ถ้าเราใช้ INDEX ร่างที่สอง จะสามารถเขียนตารางอ้างอิงได้หลายอัน!! ซึ่งสูตรแบบที่สองเขียนแบบนี้

= INDEX(reference, row_num, [column_num], [area_num])

แปลว่า = INDEX((ตารางอ้างอิง1,ตารางอ้างอิง2,ตารางอ้างอิง3,…,ตารางอ้างอิงn), ลำดับแถว, ลำดับคอลัมน์,ใช้ตารางอ้างอิงที่ท่าไหร่)

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

• สังเกตว่า reference ในสูตรที่สอง แทนด้วยตารางอ้างอิงหลายอันอยู่ในวงเล็บ คั่นด้วย comma เช่น (ตารางอ้างอิง1,ตารางอ้างอิง2,ตารางอ้างอิง3,…,ตารางอ้างอิงn)

เช่น ตามรูป เราใส่ตารางอ้างอิงลงไป 2 ตาราง คือ B5:B9 และ B11:E15 ใส่วงเล็บครอบไว้และคั่นไว้ด้วย comma

จากนั้นเราก็เลือกว่าจะเอาแถวที่ 3 คอลัมน์ที่ 4 จากตารางอันที่ 2 มันก็เลยได้ค่าเป็น E13 ซึ่งคือ 175 นั่นเอง

6.สูตร INDEX ถ้าใช้ร่วมกับฟังก์ชั่นอื่นเมื่อไหร่ = ไร้เทียมทาน

สูตร INDEX เป็นพวกขี้เหงา ทำอะไรคนเดียวแล้วทำได้ไม่ค่อยดี แค่หาข้อมูลแบบ VLOOKUP ก็หาไม่ได้
แต่ถ้าเราใช้ INDEX ร่วมกับฟังก์ชั่นอื่นเมื่อไหร่ มันจะแสดงพลังออกมามหาศาล ! เช่น

INDEX + MATCH

• MATCH จะค้นหาค่า แล้วส่งผลลัพธ์เป็นตัวเลขกลับมาให้ INDEX ใช้งาน เช่น
  • เราอาจ ใช้ MATCH หาก่อน ว่าคำที่เราต้องการอยู่ใน Row ไหน หรือ Column ไหน
  • =MATCH(คำค้นหา,ช่วงอ้างอิง,รูปแบบการค้นหา)
  • เวลาใช้ผสมกัน จึงใช้ MATCH แทนเลขลำดับพิกัดแถวหรือคอลัมน์ (หรือทั้งคู่) แบบนี้
    = INDEX(ตารางอ้างอิง,MATCH(คำค้นหาแถว,ช่วงอ้างอิงแถว,รูปแบบการค้นหา),MATCH(คำค้นหาคอลัมน์,ช่วงอ้างอิงคอลัมน์,รูปแบบการค้นหา))

7. INDEX สามารถประมวลผล Array Formula ได้ โดยไม่ต้องกด Ctrl+Shift+Enter

อันนี้ก็ตามชื่อหัวข้อครับ ข้อนี้ไม่เข้าใจไม่ต้องซีเรียสไป ใครยังไม่รู้จัก Array Formula เดี๋ยวผมจะเขียนอธิบายในอนาคตครับ (advance มากๆ)

เช่น

=AVERAGE(INDEX((D6:D9="ชาย")*(E6:E9),0,1))

ปกติการทำแบบนี้ (D6:D9=”ชาย”)*(E6:E9) ต้องกด Ctrl+Shift+Enter ไม่งั้นจะ Error
แต่พอมี INDEX มาครอบแล้ว ก็สามารถกด Enter ได้ตามปกติเลยครับ