Thep Excel
INDEXดึงค่าจากตำแหน่งที่ระบุใน Range หรือ Array
INDEX คืนค่าหรือ reference ของเซลล์จากตำแหน่งที่ระบุใน range หรือ array โดยอ้างอิงจากหมายเลขแถวและคอลัมน์ที่เราระบุ มักถูกใช้ร่วมกับ MATCH เพื่อสร้าง INDEX-MATCH pattern ที่ยืดหยุ่นกว่า VLOOKUP เยอะ เพราะสามารถดึงข้อมูลจากทิศทางไหนก็ได้ และไม่พังเมื่อมีการแทรกหรือลบคอลัมน์
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | ค่าเริ่มต้น | คำอธิบาย |
|---|---|---|---|
| array | Range/Array | ช่วงข้อมูล (range) หรือ array constant ที่ต้องการดึงค่า สำหรับ array form | |
| reference | Reference | การอ้างอิงถึงหนึ่งหรือหลาย range สำหรับ reference form ถ้าเป็นหลาย range ต้องใส่วงเล็บครอบ เช่น (A1:C5, E1:G5) | |
| row_num | Number | หมายเลขแถวใน array หรือ reference ที่ต้องการดึงค่า (นับจาก 1) ใส่ 0 เพื่อ return ทั้งคอลัมน์ | |
| [column_num]ไม่บังคับ | Number | 1 | หมายเลขคอลัมน์ใน array หรือ reference ที่ต้องการดึงค่า (นับจาก 1) ใส่ 0 เพื่อ return ทั้งแถว ถ้า array เป็นคอลัมน์เดียวไม่ต้องระบุ |
| [area_num]ไม่บังคับ | Number | 1 | สำหรับ reference form ระบุว่าจะใช้ range ไหนถ้ามีหลาย range (1 = range แรก, 2 = range ที่สอง) ถ้ามีหลาย range ต้องระบุ |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
INDEX ดึงค่าหรือ reference จากตำแหน่งที่ระบุใน range หรือ array โดยใช้หมายเลขแถว (row_num) และคอลัมน์ (column_num)
ส่วนตัวผมว่านี่คือหนึ่งในฟังก์ชันที่ “ต้องรู้” จริงๆ ถ้าจะใช้งาน Excel ให้เก่ง 😎 เพราะมันเป็นพื้นฐานของการค้นหาข้อมูลแบบไดนามิก
ฟังก์ชันนี้มักถูกใช้ร่วมกับ MATCH เป็น INDEX-MATCH pattern ซึ่งเหนือกว่า VLOOKUP หลายเรื่อง:
1. ค้นหาจากคอลัมน์ซ้ายไปขวาได้ (VLOOKUP ทำไม่ได้)
2. ไม่พังเมื่อมีการแทรกหรือลบคอลัมน์
3. ทำ two-way lookup (ค้นหาทั้งแถวและคอลัมน์พร้อมกัน) ได้ง่ายกว่าเยอะ
INDEX มี 2 รูปแบบคือ Array form และ Reference form โดย Array form เป็นที่นิยมใช้งานมากกว่า (ประมาณ 95% ของกรณี)
How it works
สร้างสูตรค้นหาแบบไดนามิกทดแทน VLOOKUP
ใช้ INDEX-MATCH เพื่อสร้างสูตรค้นหาที่ยืดหยุ่นกว่า VLOOKUP โดยสามารถค้นหาจากคอลัมน์ซ้ายไปขวาได้ ไม่พังเมื่อมีการแทรกหรือลบคอลัมน์ และไม่ต้องจำ column_index_num ที่อาจเปลี่ยนแปลงได้
ค้นหาข้อมูลจากตารางสองมิติ (Matrix Lookup)
ใช้ INDEX กับ MATCH สองชุดเพื่อค้นหาข้อมูลจากตารางที่มีทั้งแถวและคอลัมน์เป็นตัวแปร เช่น ตารางราคาตามปริมาณและประเภทสินค้า หรือยอดขายตามเดือนและสาขา
สร้าง Dynamic Range สำหรับสูตรอื่น
ใช้ INDEX ร่วมกับ COUNTA หรือ MATCH เพื่อสร้าง range ที่ขยายหรือหดตามข้อมูลจริง ทำให้สูตรทำงานอัตโนมัติเมื่อมีการเพิ่มหรือลดข้อมูล
ดึงข้อมูลทั้งแถวหรือคอลัมน์มาประมวลผล
ใช้ INDEX กับ row_num=0 หรือ column_num=0 เพื่อ return ข้อมูลทั้งแถว/คอลัมน์ แล้วนำไปใช้กับฟังก์ชันอื่น เช่น SUM, AVERAGE, MAX สำหรับการคำนวณแบบไดนามิก
Examples
| A | B | C | D | |
|---|---|---|---|---|
| 1 | รหัสสินค้า | ชื่อสินค้า | สถานะ | ราคา |
| 2 | P001 | เมาส์ไร้สาย | ปิด | 590 |
| 3 | P002 | คีย์บอร์ด | เปิด | 1,290 |
| 4 | P003 | จอ 27 นิ้ว | ปิด | 8,900 |
| 5 | P004 | หูฟัง | เปิด | 2,450 |
| 6 | P005 | เว็บแคม | ปิด | 1,750 |
ทุกสูตรด้านล่างอ้างถึงตารางนี้ พิมพ์ตามได้เลย ผลลัพธ์จะตรงกับที่เขียนไว้
| A | B | |
|---|---|---|
| 1 | คะแนนขั้นต่ำ | เกรด |
| 2 | 0 | F |
| 3 | 50 | D |
| 4 | 60 | C |
| 5 | 70 | B |
| 6 | 80 | A |
ตารางนี้ใช้กับการค้นหาแบบใกล้เคียง (TRUE/1) ซึ่งบังคับว่าคอลัมน์แรกต้องเรียงจากน้อยไปมาก
ตัวอย่างที่ 1: Foundation – ดึงค่าจาก Array คอลัมน์เดียวINDEX({"เมาส์ไร้สาย";"คีย์บอร์ด";"จอ 27 นิ้ว";"หูฟัง";"เว็บแคม"}, 3)
สูตรนี้ดึงค่าจากตำแหน่งที่ 3 ในอาร์เรย์คอลัมน์เดียวที่พิมพ์ไว้ตรงๆ ในสูตร ได้ "จอ 27 นิ้ว" กลับมา
เพราะอาร์เรย์มีแค่คอลัมน์เดียว จึงไม่ต้องใส่ column_num เลยครับ นี่คือรูปแบบพื้นฐานที่สุดของ INDEX ยังไม่เกี่ยวกับตารางจริงบนชีตเลยด้วยซ้ำ
ข้อดีคือเห็นชัดว่า INDEX แค่นับตำแหน่งแล้วดึงค่าออกมา ไม่ได้ค้นหาอะไรเหมือน MATCH หรือ VLOOKUP
ตัวอย่างที่ 2: Foundation – ดึงค่าจาก Range สองมิติINDEX(A2:D6, 4, 2)
ดึงค่าจากแถวที่ 4 คอลัมน์ที่ 2 ของช่วง A2:D6 ซึ่งเป็นข้อมูลของสินค้าล้วนๆ ไม่รวมแถวหัวตาราง
แถวที่ 4 ของช่วงนี้คือแถวของ P004 และคอลัมน์ที่ 2 คือชื่อสินค้า จึงได้ "หูฟัง" กลับมา
สังเกตว่าเลขแถวเลขคอลัมน์นับจากขอบของ array ที่เราเลือก ไม่ใช่นับจากแถวหรือคอลัมน์จริงบนชีต ถ้าเปลี่ยน column_num เป็น 4 ก็จะได้ราคาแทน
ตัวอย่างที่ 3: Practical – INDEX-MATCH แบบง่าย (แทน VLOOKUP)INDEX(D2:D6, MATCH("P003", A2:A6, 0))
MATCH ค้นหา "P003" ในคอลัมน์รหัสสินค้า A2:A6 เจอที่ตำแหน่งที่ 3 แล้ว INDEX ก็ไปดึงค่าจากตำแหน่งที่ 3 ของคอลัมน์ราคา D2:D6 มาให้ ได้ 8,900 บาท
นี่คือ INDEX-MATCH pattern พื้นฐานที่ใช้แทน VLOOKUP ได้เลยครับ ผลลัพธ์เหมือนกันเป๊ะ
ข้อดีคือ INDEX ไม่สนใจว่าคอลัมน์ราคาอยู่ซ้ายหรือขวาของคอลัมน์รหัส เพราะเราชี้ทั้งสองช่วงแยกกันตรงๆ
ตัวอย่างที่ 4: Practical – ค้นหาจากซ้ายไปขวา (VLOOKUP ทำไม่ได้)INDEX(A2:A6, MATCH("หูฟัง", B2:B6, 0))
MATCH ค้นหาชื่อ "หูฟัง" ในคอลัมน์ B2:B6 เจอที่ตำแหน่งที่ 4 แล้ว INDEX ดึงค่าจากตำแหน่งที่ 4 ของคอลัมน์ A2:A6 ซึ่งอยู่ทางซ้ายของคอลัมน์ที่ค้นหา ได้รหัส "P004" กลับมา
นี่คือจุดที่ VLOOKUP ทำไม่ได้เลยครับ เพราะ VLOOKUP บังคับให้คอลัมน์ค้นหาต้องอยู่ซ้ายสุดเสมอ แต่ที่นี่เราค้นหาจากคอลัมน์ B แล้วดึงค่าจากคอลัมน์ A ซึ่งอยู่ซ้ายกว่า
INDEX-MATCH ไม่สนทิศทางเลย จะค้นซ้ายไปขวาหรือขวาไปซ้ายก็ทำได้เหมือนกัน
ตัวอย่างที่ 5: Advanced – Two-Way Lookup (Matrix Lookup)INDEX(A2:D6, MATCH("P005", A2:A6, 0), MATCH("สถานะ", A1:D1, 0))
สูตรนี้ใช้ MATCH สองครั้ง ครั้งแรกหาตำแหน่งแถวของ "P005" ในคอลัมน์รหัส ได้แถวที่ 5 ครั้งที่สองหาตำแหน่งคอลัมน์ของหัวตาราง "สถานะ" ในแถว A1:D1 ได้คอลัมน์ที่ 3
จากนั้น INDEX ดึงค่าที่จุดตัดของแถว 5 คอลัมน์ 3 ในช่วง A2:D6 ออกมา ได้ "ปิด" ซึ่งคือสถานะของ P005
นี่คือ Two-Way Lookup ที่ค้นหาทั้งแถวและคอลัมน์พร้อมกัน มีประโยชน์มากเวลาตารางมีหัวคอลัมน์เยอะและอาจถูกสลับตำแหน่งภายหลัง เพราะสูตรจะยังหาเจอแม้โครงสร้างเปลี่ยน
ตัวอย่างที่ 6: Advanced – Return ทั้งคอลัมน์และใช้ร่วมกับฟังก์ชันอื่นAVERAGE(INDEX(A2:D6, 0, 4))
ใส่ row_num เป็น 0 บอก INDEX ว่าให้คืนทั้งคอลัมน์ที่ 4 ของช่วง A2:D6 ออกมา ซึ่งก็คือคอลัมน์ราคาทั้งหมด แล้ว AVERAGE ก็คำนวณค่าเฉลี่ยของราคาทั้ง 5 รายการ ได้ 2,996 บาท
เทคนิคนี้ใช้ INDEX เป็นตัวชี้ range แบบไดนามิกให้ฟังก์ชันอื่นแทนการเลือก range เอง
ถ้าใส่ column_num เป็น 0 แทน ก็จะได้ทั้งแถวแทนทั้งคอลัมน์ ทำงานสมมาตรกัน
ตัวอย่างที่ 7: Advanced – Dynamic Range กับ COUNTASUM(D2:INDEX(D:D, COUNTA(D:D)))
COUNTA(D:D) นับจำนวนเซลล์ที่มีข้อมูลในคอลัมน์ D ทั้งคอลัมน์ ซึ่งรวมหัวตาราง "ราคา" กับข้อมูล 5 แถว รวมเป็น 6
INDEX(D:D, 6) จึงชี้ไปที่เซลล์ D6 ซึ่งเป็นแถวสุดท้ายที่มีข้อมูลพอดี ทำให้ D2:INDEX(D:D, 6) กลายเป็นช่วง D2:D6 แบบไดนามิก
SUM รวมราคาทั้งหมดได้ 14,980 บาท ถ้าวันหลังมีคนเพิ่มสินค้าใหม่ต่อท้ายตาราง สูตรนี้จะขยายตามอัตโนมัติโดยไม่ต้องแก้ range เอง
ตัวอย่างที่ 8: Advanced – Reference Form กับหลาย RangeINDEX((A2:D6, F2:G6), 3, 2, 2)
นี่คือ Reference form ของ INDEX ที่รับหลาย range พร้อมกัน ในวงเล็บคู่นอกมี 2 ranges คือ A2:D6 กับ F2:G6
area_num ตัวสุดท้ายใส่เป็น 2 หมายถึงเลือก range ที่สอง คือ F2:G6 ซึ่งเป็นตารางตัดเกรด แล้วดึงค่าที่แถวที่ 3 คอลัมน์ที่ 2 ของ range นั้น ได้ "C"
เทคนิคนี้มีประโยชน์เวลาต้องเลือกข้อมูลจากตารางหลายก้อนที่ไม่ติดกัน แต่ต้องระวังว่าต้องพิมพ์วงเล็บครอบ range ทั้งหมดอีกชั้นเสมอ
ตัวอย่างที่ 9: Complex – INDEX-MATCH กับ Approximate MatchINDEX(G2:G6, MATCH(82, F2:F6, 1))
MATCH ใช้ match_type=1 หาค่ามากที่สุดในคอลัมน์คะแนนขั้นต่ำ F2:F6 ที่ยังไม่เกิน 82 ซึ่งคือ 80 อยู่ตำแหน่งที่ 5
INDEX ดึงเกรดจากตำแหน่งที่ 5 ของคอลัมน์ G2:G6 ได้ "A"
วิธีนี้ใช้ได้เพราะคอลัมน์คะแนนขั้นต่ำเรียงจากน้อยไปมากอยู่แล้ว ถ้าไม่เรียง MATCH แบบ approximate จะให้ผลผิดโดยไม่ฟ้อง error เลยครับ
ตัวอย่างที่ 10: Complex – INDEX-MATCH ซ้อนกับ IFERROR สำหรับหลาย CriteriaIFERROR(INDEX(D2:D6, MATCH(1, (B2:B6="หูฟัง")*(C2:C6="เปิด"), 0)), "ไม่พบ")
สูตรนี้เช็คสองเงื่อนไขพร้อมกัน คือชื่อสินค้าต้องเป็น "หูฟัง" และสถานะต้องเป็น "เปิด" การคูณ boolean array สองชุด (B2:B6="หูฟัง")*(C2:C6="เปิด") จะได้ 1 เฉพาะแถวที่ตรงทั้งสองเงื่อนไข นอกนั้นเป็น 0
MATCH หาตำแหน่งของค่า 1 นั้น เจอที่แถวที่ 4 แล้ว INDEX ดึงราคาจากตำแหน่งเดียวกันในคอลัมน์ D2:D6 ได้ 2,450 บาท
ครอบด้วย IFERROR ไว้เผื่อไม่มีแถวไหนตรงเงื่อนไขทั้งคู่เลย จะได้ข้อความ "ไม่พบ" แทนที่จะเป็น #N/A
FAQs
INDEX แตกต่างจาก VLOOKUP อย่างไร ควรใช้อันไหน?+
INDEX (โดยเฉพาะ INDEX-MATCH) มีข้อดีกว่า VLOOKUP หลายเรื่องครับ:
1. ค้นหาจากคอลัมน์ซ้ายไปขวาได้ (VLOOKUP ต้องเป็นซ้ายไปขวาเท่านั้น)
2. ไม่พังเมื่อแทรกหรือลบคอลัมน์ (VLOOKUP ใช้ column_index_num ที่เปลี่ยนตามโครงสร้าง)
3. ทำ two-way lookup ได้ง่าย
4. ประสิทธิภาพดีกว่าในตารางขนาดใหญ่
ส่วนตัวผมว่า… ถ้ามี Excel 365/2021 แนะนำใช้ XLOOKUP ดีกว่า ซึ่งรวมข้อดีของทั้งสองและใช้งานง่ายกว่า 😎
ใส่ row_num หรือ column_num เป็น 0 จะเกิดอะไร?+
INDEX จะ return reference ทั้งแถวหรือทั้งคอลัมน์ครับ 😊
ตัวอย่าง: INDEX(A1:E10, 0, 3) จะ return ทั้งคอลัมน์ C (คอลัมน์ที่ 3) จาก range นั้น
ซึ่งสามารถนำไปใช้กับฟังก์ชันอื่นได้:
– =SUM(INDEX(Data, 0, 3)) รวมค่าทั้งคอลัมน์ที่ 3
– =AVERAGE(INDEX(Data, 5, 0)) หาค่าเฉลี่ยทั้งแถวที่ 5
เป็นเทคนิคที่ทรงพลังมากสำหรับสูตรแบบไดนามิก 💡
Array form และ Reference form ต่างกันอย่างไร?+
Array form มี syntax: INDEX(array, row_num, [column_num]) → ใช้กับ range เดียว เหมาะสำหรับการใช้งานทั่วไป (95% ของกรณี)
Reference form มี syntax: INDEX(reference, row_num, [column_num], [area_num]) → รองรับหลาย range ที่ไม่ติดกัน เช่น (A1:C5, E1:G5) แล้วใช้ area_num เลือก range
ความแตกต่างหลัก:
– Array form: ใช้ range ติดกัน
– Reference form: ใช้หลาย range แยกกัน
ส่วนตัวผมว่า… ส่วนใหญ่ใช้ Array form ก็เพียงพอแล้วครับ 😊
ทำไม INDEX-MATCH จึงดีกว่า VLOOKUP กับข้อมูลขนาดใหญ่?+
INDEX-MATCH มีประสิทธิภาพดีกว่าเพราะ MATCH ค้นหาเฉพาะคอลัมน์เดียว (lookup column) เท่านั้น
ในขณะที่ VLOOKUP ต้องประมวลผล entire table_array ทุกครั้ง
เมื่อมีการ copy สูตรลงไปหลายพันแถว INDEX-MATCH จะเร็วกว่าเนื่องจาก Excel ต้องคำนวณ range ที่น้อยกว่า
นอกจากนี้ INDEX-MATCH ยังใช้ reference แบบ absolute ได้ง่ายกว่า ทำให้ copy สูตรแล้วไม่เสียครับ 😎
จะทำ Two-Way Lookup (Matrix Lookup) ด้วย INDEX ได้อย่างไร?+
ใช้ INDEX กับ MATCH สองชุดครับ:
=INDEX(data_table, MATCH(row_lookup, row_headers, 0), MATCH(col_lookup, col_headers, 0))
– MATCH แรกหาตำแหน่งแถว
– MATCH ที่สองหาตำแหน่งคอลัมน์
– INDEX ดึงค่าจากจุดตัด
ตัวอย่างจริง: ตารางราคาที่มีผลิตภัณฑ์เป็นแถวและภูมิภาคเป็นคอลัมน์ สามารถหาราคาของ "Product A" ใน "Region North" ได้ทันที 💡
INDEX กับ XMATCH ต่างจาก INDEX กับ MATCH อย่างไร?+
XMATCH เป็นฟังก์ชันใหม่ใน Excel 365 ที่พัฒนาจาก MATCH มีข้อดีคือ:
1. ค้นหาจากล่างขึ้นบนได้ (search_mode=-1)
2. รองรับ wildcard และ binary search mode
3. มี if_not_found ในตัว ไม่ต้องใช้ IFERROR
4. Syntax ชัดเจนกว่า
สำหรับ INDEX-XMATCH vs INDEX-MATCH:
– ถ้าใช้ Excel 365 → แนะนำ XMATCH เพราะมีความสามารถมากกว่า 😎
– ถ้าต้องการ compatibility กับเวอร์ชันเก่า → ใช้ MATCH ครับ
ทำไม INDEX ถึง return #REF! error?+
#REF! error เกิดเมื่อ row_num หรือ column_num อยู่นอก range ที่กำหนดครับ
ตัวอย่าง: =INDEX(A1:C10, 15, 2) → error เพราะมีแค่ 10 แถวแต่ขอแถวที่ 15
วิธีแก้:
1. ตรวจสอบว่า row_num และ column_num ไม่เกินขนาด range
2. ใช้ IFERROR ครอบเพื่อจัดการ error → =IFERROR(INDEX(…), "ไม่พบข้อมูล")
3. ถ้าใช้กับ MATCH ให้ตรวจสอบว่า MATCH พบค่าหรือไม่ (MATCH return #N/A ถ้าไม่พบ)
ตรงนี้เจอบ่อยมากเวลาใช้กับ MATCH ครับ 😅
ควรใช้ INDEX-MATCH หรือ XLOOKUP ในปัจจุบัน?+
ขึ้นอยู่กับสถานการณ์ครับ:
**ใช้ XLOOKUP ถ้า:**
1. ใช้ Excel 365 หรือ 2021
2. ต้องการ syntax ที่เรียบง่าย
3. ต้องการ if_not_found และ search_mode ในตัว
**ใช้ INDEX-MATCH ถ้า:**
1. ต้อง compatibility กับเวอร์ชันเก่า (Excel 2019 ลงมา)
2. ต้องการ return ทั้งแถว/คอลัมน์ด้วย row_num=0
3. ต้องการควบคุมละเอียดกว่า (เช่น ใช้ MATCH แบบ approximate)
**สรุป:** XLOOKUP ง่ายกว่าสำหรับ simple lookup แต่ INDEX-MATCH ยืดหยุ่นกว่าสำหรับ advanced scenarios 😎
INDEX รองรับเวอร์ชัน Excel ไหนบ้าง?+
INDEX รองรับทุกเวอร์ชันของ Excel ตั้งแต่ Excel 97 จนถึง Excel 365 ปัจจุบันครับ 😊
เป็นฟังก์ชันพื้นฐานที่มีมาตั้งแต่ต้นและไม่เคยเปลี่ยนแปลง behavior ทำให้เป็น universal function ที่ใช้ได้ในทุกสถานการณ์ โดยไม่ต้องกังวลเรื่อง compatibility
แตกต่างจากฟังก์ชันใหม่ เช่น XLOOKUP, FILTER ที่ใช้ได้เฉพาะ Excel 365/2021 เท่านั้น
นี่เป็นเหตุผลหนึ่งที่ทำให้ INDEX-MATCH ยังได้รับความนิยมอยู่เสมอครับ
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ INDEX จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
ADDRESS สร้างข้อความที่เป็นตำแหน่งเซลล์จากหมายเลขแถวและคอลัมน์ รองรับ Absolute Mixed Relative และสไตล์ R1C1 มักใช้คู่กับ INDIRECT เพื่อสร้าง Dynamic Reference
AREAS นับว่าการอ้างอิงที่เรากำหนดนั้นมีกี่พื้นที่ (area) พื้นที่หมายถึงช่วงเซลล์ต่อเนื่องหรือเซลล์เดี่ยว เช่น ถ้าเราอ้างอิง (A1:B2, D5:E5) ถือว่ามี 2 พื้นที่แยกกัน
CHOOSE เลือกค่าจากรายการค่าตามหมายเลขลำดับที่ระบุ เป็นเหมือนการเลือกรายการจากเมนู ส่งคืนค่าที่ตำแหน่งที่กำหนด มีประโยชน์ในการสร้างสูตรแบบเลือกตามเงื่อนไข
CHOOSEROWS ใช้ดึงแถวที่ต้องการจากตารางหรือ Array โดยระบุเลขลำดับแถว สามารถดึงได้หลายแถวพร้อมกัน จัดลำดับใหม่ หรือทำซ้ำแถวเดิมได้ รองรับการนับแถวจากล่างขึ้นบนโดยใช้เลขลบ (เช่น -1 คือแถวสุดท้าย)
นับจำนวนคอลัมน์ทั้งหมดในช่วงข้อมูลหรืออาร์เรย์ที่ระบุ ใช้บ่อยคู่กับ OFFSET หรือ VLOOKUP เพื่อทำสูตรแบบ dynamic ที่ปรับตามขนาดตารางเอง
FILTER กรองข้อมูลจาก Array ตามเงื่อนไขที่กำหนด แล้ว return เป็น Spill Range ที่ขยายอัตโนมัติ เป็น Dynamic Array Function ที่เปลี่ยนวิธีการทำงานกับข้อมูลใน Excel รองรับเงื่อนไขหลายตัว (AND/OR) และสามารถใช้ร่วมกับ SORT UNIQUE XLOOKUP เพื่อสร้างรายงานที่อัปเดตอัตโนมัติ
GETPIVOTDATA ช่วยดึงข้อมูลจาก PivotTable โดยอ้างอิงชื่อฟิลด์และเงื่อนไข แทนที่จะอ้างอิงตำแหน่งเซลล์
.
เหมาะสำหรับการสร้างรายงานแบบไดนามิกและการวิเคราะห์ข้อมูลที่ต้องการความแม่นยำสูง เพราะสูตรจะยังคงทำงานได้ถูกต้องแม้ PivotTable จะเปลี่ยนแปลง 💡
HLOOKUP ค้นหาข้อมูลจากแถวแรกของตาราง แล้วคืนค่าจากแถวที่ระบุ ตรงข้ามกับ VLOOKUP ที่ค้นหาแนวตั้ง เหมาะสำหรับตารางที่หัวข้อมูลเรียงแบบแนวนอน
INDIRECT แปลงข้อความเป็นการอ้างอิงเซลล์จริง ช่วยให้สามารถเปลี่ยนตำแหน่งเซลล์ได้ไดนามิกโดยใช้ตัวเลขหรือชื่อเซลล์
LOOKUP ค้นหาค่าในช่วงข้อมูลที่เรียงลำดับแล้ว (ascending) และคืนค่าจากตำแหน่งเดียวกันในอีกช่วงหนึ่ง มีเทคนิคพิเศษคือหาค่าสุดท้ายที่ไม่ว่าง แนะนำใช้ XLOOKUP แทนสำหรับ Excel 365
MATCH คืนเลขลำดับตำแหน่งของค่าที่ค้นหาในช่วงข้อมูลแถวเดียวหรือคอลัมน์เดียว รองรับการค้นหา 3 โหมด คือ Exact Match (0) ที่ไม่ต้องเรียงข้อมูล, Approximate Match แบบ Less Than or Equal (1) ที่ต้องเรียงจากน้อยไปมาก, และ Greater Than or Equal (-1) ที่ต้องเรียงจากมากไปน้อย รองรับ Wildcard (* และ ?) ในโหมด Exact Match และมักใช้คู่กับ INDEX เป็นรูปแบบ INDEX-MATCH ที่ยืดหยุ่นกว่า VLOOKUP
อ้างอิงช่วงข้อมูลที่เลื่อนจากตำแหน่งเริ่มต้น สร้าง dynamic range ได้
SORT เป็น Dynamic Array Function ที่เรียงลำดับข้อมูลจาก Array แล้ว return เป็น Spill Range ใหม่โดยไม่แก้ไขข้อมูลต้นฉบับ รองรับการเรียงตามคอลัมน์ที่ต้องการ (sort_index) ทั้งจากน้อยไปมาก (1) และมากไปน้อย (-1) รวมถึงเรียงแนวนอน (by_col=TRUE) ต่างจาก SORTBY ที่ใช้คอลัมน์ภายนอกเป็นเกณฑ์
TAKE ช่วยตัดข้อมูลบางส่วนออกมาใช้งาน โดยระบุจำนวนที่ต้องการ ถ้าใส่เลขบวกจะดึงจากจุดเริ่มต้น (บน/ซ้าย) ถ้าใส่เลขลบจะดึงจากจุดสิ้นสุด (ล่าง/ขวา) คล้ายกับคำสั่ง LIMIT หรือ TOP/BOTTOM ใน Database
VLOOKUP เป็นฟังก์ชันค้นหาแนวตั้งที่ได้รับความนิยมสูงสุดใน Excel โดยค้นหาค่าที่ต้องการในคอลัมน์ซ้ายสุดของตาราง จากนั้นดึงข้อมูลจากคอลัมน์ที่ระบุในแถวเดียวกัน รองรับทั้งการค้นหาแบบตรงทุกตัวอักษร (Exact Match) และแบบประมาณค่า (Approximate Match) สำหรับข้อมูลที่เรียงลำดับ ข้อจำกัดหลักคือต้องค้นหาจากคอลัมน์ซ้ายสุดเท่านั้น ไม่สามารถดึงข้อมูลจากด้านซ้ายของคอลัมน์ค้นหาได้
ฟังก์ชัน XLOOKUP ใช้สำหรับค้นหาข้อมูลในตารางทั้งแนวตั้งและแนวนอน มีความยืดหยุ่นสูงกว่า VLOOKUP โดยสามารถค้นหาจากซ้ายไปขวา ขวาไปซ้าย และกำหนดค่าเริ่มต้นเมื่อไม่พบข้อมูลได้
XMATCH คืนค่าตำแหน่งของข้อมูลที่ค้นหาในช่วงหรืออาร์เรย์ ถือเป็นฟังก์ชันรุ่นใหม่ที่ Microsoft พัฒนามาแก้จุดอ่อนของ MATCH โดยเฉพาะเรื่องค่า Default ที่เป็น Exact Match แทน Approximate Match ทำให้ใช้ง่ายและปลอดภัยกว่ามาก นอกจากนี้ยังรองรับการค้นหาย้อนกลับ Binary Search สำหรับข้อมูลขนาดใหญ่ และการค้นหาแบบ Wildcard ทำให้ยืดหยุ่นกว่า MATCH เดิมหลายเท่า
RANDBETWEEN คืนค่าจำนวนเต็มสุ่มที่อยู่ระหว่างค่าต่ำสุดและสูงสุดที่ระบุ ใช้สำหรับจำลองข้อมูล สร้างรหัสสุ่ม หรือเลือกข้อมูลแบบสุ่ม
ส่งกลับหมายเลขลำดับของแผ่นงาน (sheet) ที่ระบุ ใช้เมื่อต้องการรู้ว่าชีท A อยู่ลำดับที่เท่าไหร่
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่