Clean Functions หลักการเลือกสูตรสะอาด สูตรที่น่าใช้หรือไม่น่าใช้

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

Syntax คือ โครงสร้างของสูตร
Argument คือ แต่ละส่วนในวงเล็บที่คั่นด้วยเครื่องหมายคอมมา

☝️ หลักที่ดีที่สุดก็คือไม่ต้องใส่อะไรในวงเล็บเลย จำง่ายที่สุด ใช้ง่ายที่สุดด้วย เช่น TODAY() NOW()

แต่ถ้ามี Argument ไม่ควรมีเกิน 3 ส่วน เช่น
MATCH(lookup_value, lookup_array, [match_type])
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

ถ้านับ Argument ในวงเล็บของ VLookup พบว่าแบ่งเป็น 4 ส่วน คือ
1. lookup_value
2. table_array
3. col_index_num
4. [range_lookup] ส่วนที่ใส่วงเล็บ [ ] ถือเป็น optional จะมีหรือไม่ก็ได้

ส่วนที่ยากต่อไปอีกก็คือใน Argument หนึ่งยังแตกเป็นตัวเลือกย่อยลงไปอีกจนยากจะจำและเสี่ยงที่จะใช้งานผิดหากไม่เข้าใจอย่างถ่องแท้ เช่น

AGGREGATE(function_num, options, ref1, [ref2], …) แม้มี Argument ที่จำเป็นต้องมีในวงเล็บ 3 ส่วน แต่ว่า ...
function_num มีตัวเลือกให้ใช้อีกถึง 19 อย่าง
options มีตัวเลือกอีก 8 อย่าง

✋ สูตร Aggregate นี้แม้แต่ผมเองก็ยังไม่กล้าใช้ เพราะไม่มั่นใจ 100% ว่าในการใช้งานนั้นมีข้อดี ข้อเสีย และข้อควรระวังอะไรอีกบ้าง

ผมเลือกบางสูตรมาแสดงให้ดูครับ ยากทีเดียวใช่ไหมครับที่จะจำให้ได้ทั้งหมด

AGGREGATE(function_num, options, ref1, [ref2], …)
CEILING(number, significance)
COUNTIF(criteria_range1, criteria1)
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
DATE(year,month,day)
EOMONTH(start_date, months)
FILTER(a range of data, criteria)
GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
HYPERLINK(link_location, [friendly_name])
IF(logical_test, value_if_true, [value_if_false])
IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3)
INDEX(array, row_num, [column_num])
MATCH(lookup_value, lookup_array, [match_type])
MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
MOD(number, divisor)
NETWORKDAYS(start_date, end_date, [holidays])
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
NPV(rate,value1,[value2],...)
OFFSET(reference, rows, cols, [height], [width])
PMT(rate, nper, pv, [fv], [type])
PRODUCT(number1, [number2], ...)
RANK(number,ref,[order])
ROUND(number, num_digits)
SEQUENCE(rows,[columns],[start],[step])
SMALL(array, k)
STDEV(number1,[number2],...)
SUBSTITUTE(text, old_text, new_text, [instance_num])
SUBTOTAL(function_num,ref1,[ref2],...)
SUM(number1,[number2],...)
SUMIF(range, criteria, [sum_range])
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
SUMPRODUCT(array1, [array2], [array3], ...)
SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])
TEXTAFTER(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])
TEXTBEFORE(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])
TIME(hour, minute, second)
UNIQUE(array,[by_col],[exactly_once])
VALUETOTEXT(value, [format])
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
VSTACK(array1,[array2],...)
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

แนะนำว่าหากจะเลือกใช้สูตรใด ควรเข้าใจอย่างชัดเจนว่าแต่ละ Argument ใช้ทำอะไร มีเงื่อนไขอะไรเกี่ยวข้องบ้าง และพยายามหลีกเลี่ยงการใช้ Argument ที่เป็น [optional] เว้นแต่จะเข้าใจดีแล้วอีกนั่นแหละครับ

เชิญศึกษาสูตรทั้งหมดของ Excel ได้จากเว็บของไมโครซอฟท์