Thep Excel

ADDRESS สร้างข้อความที่เป็นตำแหน่งเซลล์จากหมายเลขแถวและคอลัมน์ รองรับ Absolute Mixed Relative และสไตล์ R1C1 มักใช้คู่กับ INDIRECT เพื่อสร้าง Dynamic Reference

=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

AREAS นับว่าการอ้างอิงที่เรากำหนดนั้นมีกี่พื้นที่ (area) พื้นที่หมายถึงช่วงเซลล์ต่อเนื่องหรือเซลล์เดี่ยว เช่น ถ้าเราอ้างอิง (A1:B2, D5:E5) ถือว่ามี 2 พื้นที่แยกกัน

=AREAS(reference)

CHOOSE เลือกค่าจากรายการค่าตามหมายเลขลำดับที่ระบุ เป็นเหมือนการเลือกรายการจากเมนู ส่งคืนค่าที่ตำแหน่งที่กำหนด มีประโยชน์ในการสร้างสูตรแบบเลือกตามเงื่อนไข

=CHOOSE(index_num, value1, [value2], ...)

CHOOSECOLS ใช้ดึงคอลัมน์ที่ต้องการจากตารางหรือ Array โดยระบุเลขลำดับคอลัมน์ สามารถดึงได้หลายคอลัมน์พร้อมกัน จัดลำดับใหม่ หรือทำซ้ำคอลัมน์เดิมได้ รองรับการนับคอลัมน์จากขวาสุดโดยใช้เลขลบ (เช่น -1 คือคอลัมน์ขวาสุด)

=CHOOSECOLS(array, col_num1, [col_num2], ...)

CHOOSEROWS ใช้ดึงแถวที่ต้องการจากตารางหรือ Array โดยระบุเลขลำดับแถว สามารถดึงได้หลายแถวพร้อมกัน จัดลำดับใหม่ หรือทำซ้ำแถวเดิมได้ รองรับการนับแถวจากล่างขึ้นบนโดยใช้เลขลบ (เช่น -1 คือแถวสุดท้าย)

=CHOOSEROWS(array, row_num1, [row_num2], ...)

COLUMN ส่งคืนหมายเลขคอลัมน์ (Column Number) ของเซลล์หรือช่วงที่ระบุ ส่งคืนตัวเลขคอลัมน์ 1, 2, 3… (A=1, B=2 เป็นต้น) มีประโยชน์ในการระบุตำแหน่งคอลัมน์แบบไดนามิก

=COLUMN([reference])

นับจำนวนคอลัมน์ทั้งหมดในช่วงข้อมูล (Array/Range)

=COLUMNS(array)

DROP จะตัดข้อมูลออกตามจำนวนที่ระบุ ถ้าใส่เลขบวกจะตัดจากจุดเริ่มต้น (บน/ซ้าย) ทิ้งไป ถ้าใส่เลขลบจะตัดจากจุดสิ้นสุด (ล่าง/ขวา) ทิ้งไป ส่วนที่เหลือจะถูกนำมาแสดงผล

=DROP(array, rows, [columns])

EXPAND ใช้ขยายขนาดของตารางข้อมูลให้ใหญ่ขึ้นตามจำนวนแถวหรือคอลัมน์ที่ระบุ หากตารางเดิมมีขนาดเล็กกว่า ส่วนที่เพิ่มขึ้นมาจะแสดงค่าเป็น #N/A (ค่าเริ่มต้น) หรือค่าที่เรากำหนดเองได้ (pad_with) มีประโยชน์มากในการปรับขนาดข้อมูลให้เท่ากันก่อนนำไปรวมด้วย VSTACK หรือ HSTACK

=EXPAND(array, rows, [columns], [pad_with])

FILTER กรองข้อมูลจาก Array ตามเงื่อนไขที่กำหนด แล้ว return เป็น Spill Range ที่ขยายอัตโนมัติ เป็น Dynamic Array Function ที่เปลี่ยนวิธีการทำงานกับข้อมูลใน Excel รองรับเงื่อนไขหลายตัว (AND/OR) และสามารถใช้ร่วมกับ SORT UNIQUE XLOOKUP เพื่อสร้างรายงานที่อัปเดตอัตโนมัติ

=FILTER(array, include, [if_empty])

FORMULATEXT ดึงสูตรจากเซลล์มาแสดงเป็นข้อความ ใช้สำหรับ Audit ตรวจสอบสูตร หรือทำ Documentation ของ Workbook ถ้าเซลล์ไม่มีสูตรจะคืนค่า #N/A

=FORMULATEXT(reference)

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

=GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)

HLOOKUP ค้นหาข้อมูลจากแถวแรกของตาราง แล้วคืนค่าจากแถวที่ระบุ ตรงข้ามกับ VLOOKUP ที่ค้นหาแนวตั้ง เหมาะสำหรับตารางที่หัวข้อมูลเรียงแบบแนวนอน

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

HSTACK เป็นฟังก์ชัน Dynamic Array ที่ใช้รวมข้อมูลจากหลายช่วงเข้าด้วยกันโดยนำมาเรียงต่อกันในแนวนอน (ต่อท้ายไปทางขวา) หากช่วงข้อมูลที่นำมารวมมีจำนวนแถวไม่เท่ากัน HSTACK จะเติมค่า #N/A ในส่วนที่ขาดหายไปให้โดยอัตโนมัติ

=HSTACK(array1, [array2], ...)

HYPERLINK สร้างลิงก์ที่คลิกได้ไปยังเว็บไซต์ ไฟล์ เซลล์ใน Sheet อื่น หรืออีเมล สามารถกำหนดข้อความที่แสดงต่างจาก URL จริงได้

=HYPERLINK(link_location, [friendly_name])

IMAGE แสดงรูปภาพจาก URL ในเซลล์ Excel โดยรูปจะเป็นส่วนหนึ่งของเซลล์ สามารถกำหนดขนาด อัตราส่วน และ Alt Text ได้ เหมาะสำหรับสร้างรายการสินค้าหรือ Dashboard

=IMAGE(source, [alt_text], [sizing], [height], [width])

INDEX คืนค่าหรือ reference ของเซลล์จากตำแหน่งที่ระบุใน range หรือ array โดยอ้างอิงจากหมายเลขแถวและคอลัมน์ที่เราระบุ มักถูกใช้ร่วมกับ MATCH เพื่อสร้าง INDEX-MATCH pattern ที่ยืดหยุ่นกว่า VLOOKUP เยอะ เพราะสามารถดึงข้อมูลจากทิศทางไหนก็ได้ และไม่พังเมื่อมีการแทรกหรือลบคอลัมน์

=INDEX(array, row_num, [column_num])

INDIRECT แปลงข้อความเป็นการอ้างอิงเซลล์จริง ช่วยให้สามารถเปลี่ยนตำแหน่งเซลล์ได้ไดนามิกโดยใช้ตัวเลขหรือชื่อเซลล์

=INDIRECT(ref_text, [a1])

LOOKUP ค้นหาค่าในช่วงข้อมูลที่เรียงลำดับแล้ว (ascending) และคืนค่าจากตำแหน่งเดียวกันในอีกช่วงหนึ่ง มีเทคนิคพิเศษคือหาค่าสุดท้ายที่ไม่ว่าง แนะนำใช้ XLOOKUP แทนสำหรับ Excel 365

=LOOKUP(lookup_value, lookup_vector, [result_vector])

MATCH คืนเลขลำดับตำแหน่งของค่าที่ค้นหาในช่วงข้อมูลแถวเดียวหรือคอลัมน์เดียว รองรับการค้นหา 3 โหมด คือ Exact Match (0) ที่ไม่ต้องเรียงข้อมูล, Approximate Match แบบ Less Than or Equal (1) ที่ต้องเรียงจากน้อยไปมาก, และ Greater Than or Equal (-1) ที่ต้องเรียงจากมากไปน้อย รองรับ Wildcard (* และ ?) ในโหมด Exact Match และมักใช้คู่กับ INDEX เป็นรูปแบบ INDEX-MATCH ที่ยืดหยุ่นกว่า VLOOKUP

=MATCH(lookup_value, lookup_array, [match_type])

ROW ส่งคืนหมายเลขแถว (Row Number) ของเซลล์หรือช่วงที่ระบุ ส่งคืนตัวเลขแถว 1, 2, 3… มีประโยชน์ในการกำหนดหมายเลขลำดับแบบไดนามิก และสร้างสูตรที่ปรับตัวตามตำแหน่ง

=ROW([reference])

ส่งกลับจำนวนแถวในอาร์เรย์หรือการอ้างอิง

=ROWS(array)

ฟังก์ชัน RTD ใช้สำหรับดึงข้อมูลแบบ Real-time จากโปรแกรมที่รองรับ COM automation เช่น ราคาหุ้น อัตราแลกเปลี่ยน หรือข้อมูลที่มีการอัพเดทอย่างต่อเนื่อง.ที่เจ๋งคือ RTD จะอัพเดทข้อมูลโดยอัตโนมัติเมื่อ Excel อยู่ในโหมดคำนวณอัตโนมัติ ไม่ต้องนั่งกด F9 หรือรีเฟรชด้วยตนเอง ซึ่งเหมาะมากสำหรับการติดตามข้อมูลตลาดการเงิน การวิเคราะห์หุ้น หรือการเชื่อมต่อกับ Data Server ภายนอก

=RTD(ProgID, server, topic1, [topic2], ...)

SORT เป็น Dynamic Array Function ที่เรียงลำดับข้อมูลจาก Array แล้ว return เป็น Spill Range ใหม่โดยไม่แก้ไขข้อมูลต้นฉบับ รองรับการเรียงตามคอลัมน์ที่ต้องการ (sort_index) ทั้งจากน้อยไปมาก (1) และมากไปน้อย (-1) รวมถึงเรียงแนวนอน (by_col=TRUE) ต่างจาก SORTBY ที่ใช้คอลัมน์ภายนอกเป็นเกณฑ์

=SORT(array, [sort_index], [sort_order], [by_col])

SORTBY เรียงลำดับข้อมูลตาม Array อื่นที่กำหนด รองรับหลายระดับการเรียง (multi-level) และสามารถกำหนดลำดับเอง (custom sort order) ด้วย XMATCH ต่างจาก SORT ที่เรียงตามคอลัมน์ภายในตัวเอง SORTBY ใช้คอลัมน์ภายนอกเป็นเกณฑ์ได้

=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)

TAKE ช่วยตัดข้อมูลบางส่วนออกมาใช้งาน โดยระบุจำนวนที่ต้องการ ถ้าใส่เลขบวกจะดึงจากจุดเริ่มต้น (บน/ซ้าย) ถ้าใส่เลขลบจะดึงจากจุดสิ้นสุด (ล่าง/ขวา) คล้ายกับคำสั่ง LIMIT หรือ TOP/BOTTOM ใน Database

=TAKE(array, rows, [columns])

TOCOL ช่วย "ตบ" ข้อมูลจากตารางหลายมิติให้มาเรียงต่อกันเป็นคอลัมน์เดียว สามารถเลือกวิธีเรียงลำดับได้ว่าจะอ่านจากซ้ายไปขวา (ทีละแถว) หรือบนลงล่าง (ทีละคอลัมน์) และยังมี Option ให้กรองช่องว่างหรือ Error ทิ้งไปโดยอัตโนมัติ เหมาะสำหรับการเตรียมข้อมูล (Data Preparation)

=TOCOL(array, [ignore], [scan_by_column])

TOROW ช่วยแปลงข้อมูลจากตารางหลายมิติให้มาเรียงต่อกันเป็นแถวเดียว (แนวนอน) สามารถเลือกวิธีเรียงลำดับได้ว่าจะอ่านจากซ้ายไปขวา (ทีละแถว) หรือบนลงล่าง (ทีละคอลัมน์) และเลือกข้ามช่องว่างหรือ Error ได้เหมือน TOCOL

=TOROW(array, [ignore], [scan_by_column])

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

=TRANSPOSE(array)

UNIQUE เป็น Dynamic Array Function ที่คืนค่าที่ไม่ซ้ำจาก Array โดยสามารถตรวจซ้ำตามแถวหรือคอลัมน์ (by_col) และเลือกคืนเฉพาะค่าที่พบครั้งเดียว (exactly_once) ผลลัพธ์เป็น Spill Range ที่อัปเดตอัตโนมัติ ใช้ร่วมกับ SORT FILTER COUNTIF เพื่อสร้างรายงานไดนามิกและ dropdown ที่อัปเดตเอง

=UNIQUE(array, [by_col], [exactly_once])

VLOOKUP เป็นฟังก์ชันค้นหาแนวตั้งที่ได้รับความนิยมสูงสุดใน Excel โดยค้นหาค่าที่ต้องการในคอลัมน์ซ้ายสุดของตาราง จากนั้นดึงข้อมูลจากคอลัมน์ที่ระบุในแถวเดียวกัน รองรับทั้งการค้นหาแบบตรงทุกตัวอักษร (Exact Match) และแบบประมาณค่า (Approximate Match) สำหรับข้อมูลที่เรียงลำดับ ข้อจำกัดหลักคือต้องค้นหาจากคอลัมน์ซ้ายสุดเท่านั้น ไม่สามารถดึงข้อมูลจากด้านซ้ายของคอลัมน์ค้นหาได้

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

VSTACK เป็นฟังก์ชัน Dynamic Array ที่ใช้รวมข้อมูลจากหลายช่วงเข้าด้วยกันโดยนำมาเรียงต่อกันในแนวตั้ง (ต่อท้ายลงไปด้านล่าง) หากช่วงข้อมูลที่นำมารวมมีจำนวนคอลัมน์ไม่เท่ากัน VSTACK จะเติมค่า #N/A ในส่วนที่ขาดหายไปให้โดยอัตโนมัติ

=VSTACK(array1, [array2], ...)

WRAPCOLS ห่อ (wrap) ข้อมูล 1 มิติให้กลายเป็นตาราง 2 มิติ โดยเรียงข้อมูลจากบนลงล่างในแต่ละคอลัมน์ เมื่อครบ wrap_count แถวจะขึ้นคอลัมน์ใหม่ รองรับ padding เมื่อข้อมูลไม่พอดี ใช้คู่กับ WRAPROWS TOCOL TOROW เพื่อ reshape ข้อมูล

=WRAPCOLS(vector, wrap_count, [pad_with])

WRAPROWS ห่อ (wrap) ข้อมูล 1 มิติให้กลายเป็นตาราง 2 มิติ โดยเรียงข้อมูลจากซ้ายไปขวาในแต่ละแถว เมื่อครบ wrap_count คอลัมน์จะขึ้นแถวใหม่ รองรับ padding เมื่อข้อมูลไม่พอดี ใช้คู่กับ WRAPCOLS TOCOL TOROW เพื่อ reshape ข้อมูล

=WRAPROWS(vector, wrap_count, [pad_with])

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

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

XMATCH คืนค่าตำแหน่งของข้อมูลที่ค้นหาในช่วงหรืออาร์เรย์ ถือเป็นฟังก์ชันรุ่นใหม่ที่ Microsoft พัฒนามาแก้จุดอ่อนของ MATCH โดยเฉพาะเรื่องค่า Default ที่เป็น Exact Match แทน Approximate Match ทำให้ใช้ง่ายและปลอดภัยกว่ามาก นอกจากนี้ยังรองรับการค้นหาย้อนกลับ Binary Search สำหรับข้อมูลขนาดใหญ่ และการค้นหาแบบ Wildcard ทำให้ยืดหยุ่นกว่า MATCH เดิมหลายเท่า

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

อ้างอิงช่วงข้อมูลที่เลื่อนจากตำแหน่งเริ่มต้น สร้าง dynamic range ได้