ฟังก์ชัน 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 ได้ตามปกติเลยครับ
