ในบทความก่อนหน้านี้ผมได้เขียนบทความ บันได 10 ขั้น เริ่มเรียนรู้ M Code ขุมพลังของ Power Query ไป ซึ่งจะต้องเป็นคนใช้งาน Power Query ขั้นสูงจึงจะได้ใช้มัน
คราวนี้เลยขอขยับมาเขียนบทความที่คนส่วนใหญ่สามารถอ่านได้ ซึ่งก็คือ การเขียนสูตร Excel นั่นเอง
มาดูกันเล่นๆ ว่าการเขียนสูตร Excel ของคุณอยู่ในระดับไหน อิอิ
สารบัญ
LV1: การอ้างอิง Cell และ Operator พื้นฐาน
- บวก ลบ คูณ หาร ทั่วไป :
=(A1+B1)*C1 - การใช้ $ ตรึงตำแหน่ง Cell แบบ Absolute Reference :
= (A1+B1)*$C$1 - การใช้ $ ตรึงตำแหน่ง Cell แบบ Mixed Reference :
= (A1+B1)*C$1 - การแปลง Text ให้กลายเป็นเลข
=A1*1 หรือ =–A1 - การเชื่อมข้อความเข้าด้วยกัน
=A1&B1 - การอ้างอิงข้อความในสูตร
=”คุณ “&A1 - การเปรียบเทียบให้ได้ค่า TRUE/FALSE
=A1>B1 - การตรวจสอบว่า cell ที่สนใจเป็นค่าว่างหรือ Blank text หรือไม่
=A1=””
LV2: ฟังก์ชันพื้นฐานที่ Input ไม่ซับซ้อน : input ไม่เกิน 1 ชุด
- การหาผลรวม
=SUM(number1,[number2],…) - วันที่ปัจจุบัน:
=TODAY() - นับจำนวนอักขระ
= LEN(text) - หาเลขปีจากวันที่
=YEAR(serial_number) - Absolute value
=ABS(number) - ตรวจสอบว่าเป็นตัวเลขหรือไม่
=ISNUMBER(value)
LV3: ฟังก์ชันพื้นฐานที่ Input ไม่ซับซ้อน : input เกิน 1 ชุด
- การเขียนเงื่อนไข
=IF(logical_test,[value_if_true],[value_if_false]) - การปัดเศษตามหลักคณิตศาสตร์
=ROUND(number,num_digits) - การหาเศษเหลือของการหาร
=MOD(number,divisor) - การดึงข้อความที่อยู่ด้านซ้าย
=LEFT(text,[num_chars]) - การดึงบางส่วนของข้อความจากตำแหน่งที่ระบุ
=MID(text,start_num,num_chars) - การหาตำแหน่งของอักขระแบบไม่สนพิมพ์เล็กพิมพ์ใหญ่ =FIND(find_text,within_text,start_num)
- การแทนที่ข้อความ
=SUBSTITUTE(text,old_text,new_text,[instance_num]) - การเช็คค่าแล้วถ้า error ให้เปลี่ยนเป็นอีกค่า
=IFERROR(value,value_if_error)
LV4: ฟังก์ชันที่รับ input ค่อนข้างซับซ้อน
- การหาผลรวมแบบมีเงื่อนไข รายละเอียดดูคลิปนี้
=SUMIFS(sum_range,criteria_range,criteria,…) - แปลงเลขเป็น Text ด้วย Custom Format ที่ระบุ
ซึ่งคุณต้องเข้าใจเรื่อง Custom Format ก่อน เช่น
=TEXT(value,format_text) เช่น
=TEXT(วันที่,”yyyy-mm-dd”)
=TEXT(เลขเบอร์โทรศัพท์,”000-000-0000″) - การหาวันครบกำหนด
=WORKDAY.INTL(start_date,days,weekend,holidays)
ซึ่ง weekend สามารถใส่วันหยุดประจำสัปดาห์แบบ Custom เช่น “0010010” ได้
โดยที่เริ่มจาก จันทร์-อาทิตย์ 0=ไม่หยุด, 1=หยุด
นั่นคือ “0010010” แปลว่า หยุดวันพุธกับเสาร์ - การคำนวณมูลค่าเงินในอนาคต
=FV(rate,nper,pmt,[pv],[type])
เงินเข้าตัวเป็นบวก เงินออกเป็นลบ หน่วยของ rate, nper, pmt ต้องสอดคล้องกัน - การแปลงข้อความเป็น Cell Reference :
=INDIRECT(ref_text,[a1]) เช่น
=INDIRECT(“‘”&C1&”‘!$A$1:$A$50”) คือ
อ้างอิงข้อมูลจาก $A$1:$A$50 จากชื่อชีทที่ระบุใน C1 - การดึง Cell Reference จากพิกัดที่ระบุ
=INDEX(array,row_num,column_num) - การดึง Lookup ข้อมูล รายละเอียดดูคลิปนี้
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) - การ Lookup ด้วย XLOOKUP รายละเอียดดูคลิปนี้ =XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])
- การเลื่อนตำแหน่ง Range
=OFFSET(reference,rows,cols,[height],[width])
LV5: การใช้ฟังก์ชันซ้อนกัน
- การเขียน IF ผสม AND,OR :
=IF(AND(A1 > B1, B1 > C1), “A มากกว่า B และ B มากกว่า C”, “อื่นๆ”) - การเขียน IF ซ้อน IF :
=IF(A1 > 100, “Large”, IF(A1 > 50, “Medium”, “Small”)) - Combining functions:
=VLOOKUP(A1, INDIRECT(“‘”&C1&”‘!$B$1:$E$50”), 4, FALSE)
เพื่อทำการ VLOOKUP ข้อมูลจาก $B$1:$E$50 จากชื่อชีทที่ระบุใน C1 - การใช้ INDEX + MATCH เพื่อดึงข้อมูลแบบ Flexible
=INDEX(A2:A5,MATCH(A8,C2:C5,0))
LV6: Array Formulas พื้นฐาน
- ให้ผลลัพธ์ค่าเดียว
- หาผลรวมของผลคูณทีละคู่
=SUMPRODUCT(A1:A10, B1:B10) - การรวมข้อความเข้าด้วยกันด้วยตัวคั่นที่กำหนด
=TEXTJOIN(“/”,TRUE,A1:A10) - การนับข้อมูลแบบไม่ซ้ำ (สำหรับ Excel version เก่า)
=SUM(1/COUNTIFS(A1:A10, A1:A10))
- หาผลรวมของผลคูณทีละคู่
- ให้ผลลัพธ์หลายค่า
- การใช้ Array Constant ในฟังก์ชัน เพื่อหาค่า Top3
- =LARGE(A1:A10, {1, 2, 3})
- การเรียงข้อมูล
=SORT(A1:A10) - การทำให้เหลือค่าไม่ซ้ำ
=UNIQUE(A1:A10) - เข้าใจการทำงานของ Array Operation
เช่น เมื่อ Cross Row กับ Column จะได้ออกมาเป็นตาราง
=A3:A6*B2:D2
- การ Filter ข้อมูลตามเงื่อนไข รายละเอียดดูคลิปนี้
=FILTER(MyData[TXID],(MyData[ยอดขาย]>300)*(MyData[วิธีการชำระเงิน]=”เงินสด”))
LV7: ใช้ LET เพื่อประกาศตัวแปร
- พื้นที่สามเหลี่ยม เมื่อรู้ 3 ด้าน
=LET(a, A1, b, B1, c, C1,
s, (a + b + c) / 2,
SQRT(s * (s – a) * (s – b) * (s – c))
) - แทนที่อักขระ “-“,”/”,”|” ด้วย “_”
=LET( inputText, A1,
step1, SUBSTITUTE(inputText, “-“, “_”),
step2, SUBSTITUTE(step1, “/”, “_”),
result, SUBSTITUTE(step2, “|”, “_”)
) - ดึงข้อมูลเฉพาะตัวเลข
(แบบประกาศตัวแปรแยก step ละเอียด)
=LET(
inputText, A3,
textLen, LEN(inputText),
seq, SEQUENCE(1, textLen),
charArray, MID(inputText, seq, 1),
numArray, –charArray,
isNumber, ISNUMBER(numArray),
result, IF(isNumber, charArray, “”),
TEXTJOIN(“”, TRUE, result)
)
LV8: ใช้ Array Formulas ที่ค่อนข้างซับซ้อน
- หาผลรวมของค่าที่มากกว่าในแต่ละคู่
=SUM(IF(A1:A10 > B1:B10, A1:A10, B1:B10)) - หาค่าเฉลี่ยถ่วงน้ำหนัก
=SUMPRODUCT(A1:A10, B1:B10) / SUM(B1:B10) - VLOOKUP ข้อมูลด้านซ้าย
=VLOOKUP(Z1, CHOOSE({1, 2}, B1:B5, A1:A5), 2, FALSE) - เขียนเงื่อนไข Array แบบ and ด้วย Boolean logic และสรุปค่า
=MEDIAN(IF((A2:A7=”A”) * (B2:B7>0), B2:B7)) - ใช้ Matrix MMULT ช่วยในการคำนวณ Array
LV9: ใช้ LAMBDA ให้เป็นประโยชน์
LAMBDA เดี่ยวๆ ใช้สร้าง Custom Function
- LAMBDA สามารถทำความรู้จักได้ที่นี่ หรือดูคลิปนี้
- นับข้อมูลแบบไม่ซ้ำสำหรับ Excel Version ใหม่
=LAMBDA(rng, COUNTA(UNIQUE(rng))) - ระยะห่างระหว่างจุดสองจุด
=LAMBDA(x1, y1, x2, y2, SQRT((x2 – x1)^2 + (y2 – y1)^2)) - พื้นที่สามเหลี่ยม เมื่อรู้ 3 ด้าน
=LAMBDA(a, b, c, LET(s, (a + b + c) / 2, SQRT(s * (s – a) * (s – b) * (s – c))))
LAMBDA + Helper Function เพื่อสร้างสูตรที่ซับซ้อน
- LAMBDA + Helper Function สามารถทำความรู้จักได้ที่นี่
- ลองใช้ BYROW + LAMBDA (รายละเอียดดูคลิปนี้)
เพื่อให้ได้ Effect เดียวกับ MMULT ข้างบน
โดยที่ BYROW จะส่งข้อมูลเข้าไปให้ LAMBDA ทีละแถว
=BYROW(B6:E8,LAMBDA(x,SUMPRODUCT(x,B2:E2)))
LV10: เขียนสูตรที่เหมาะสมกับสถานการณ์ได้อย่างอิสระ
ใน Level 10 นี้ จริงๆ คือการที่เราควรมีความรู้ในการเขียนสูตรได้อย่างยืดหยุ่น คือรู้ว่าเมื่อไหร่ควรใช้สูตรไหน เมื่อไหร่เหมาะกับการเขียนสูตร เมื่อไหร่เหมาะกับการใช้เครื่องมืออื่นดีกว่าเขียนสูตร
ยกตัวอย่างเช่น ถ้าเราต้องรวบรวมตารางจาก Excel หลายๆ ไฟล์เข้าด้วยกัน การเขียนสูตรไม่มีทางสู้วิธีการใช้ Power Query หรือ เขียนโปรแกรมอย่าง VBA, Python ได้เลย
รวมถึงงานบางเรื่องก็สามารถใช้การเขียนสูตร เพื่อช่วยหาผลลัพธ์ต่างๆที่ดีที่สุด เช่น การทำ Optimization ด้วย Excel Solver เป็นต้น
รวมถึงควรจะหาวิธีเขียนสูตรที่คำนวณผลลัพธ์ได้รวดเร็ว ถูกต้อง เช่น หากมีการเขียนสูตรเยอะๆ พยายามอย่าใช้ Volatile Function ที่จะคำนวณตลอดเวลา (แต่ถ้าเขียนไม่กี่ช่องก็คงไม่เป็นไร)
หรือบางทีควรจะหาวิธีเขียนสูตรที่ยืดหยุ่นขึ้นกว่าเดิม เช่น อยากจะแทนที่อักขระ “-“,”/”,”|” ด้วย “_” ในตัวอย่างข้างบน เราอาจจะใช้ REDUCE + LAMBDA ดีกว่า SUBSTITUTE หลายรอบเป็นต้น เพราะในอนาคตสามารถเพิ่มอักขระได้ง่ายกว่าการเขียน SUBSTITUTE ซ้อนไปเรื่อยๆ เป็นต้น
=REDUCE(A3,{“-“,”/”,”|”},LAMBDA(a,v,SUBSTITUTE(a,v,”_”)))
ต่อจาก Level 10 คืออะไร?
หลังจาก Level 10 นี้ก็ใช่ว่าการเรียนรู้ Excel จะสิ้นสุด เพราะจริงๆ แล้วโลกของ Excel นั้นช่างกว้างใหญ่ไพศาลนัก เพราะ Excel นั้นมี Function ใหม่เพิ่มมาเรื่อยๆ
นอกจากนี้บางทีการเขียนสูตร Excel นั้นมันเต็มไปด้วยความพิสดาร เช่น การจะดึงเอาเลขจากข้อความ สามารถใช้สูตร NPV ที่เอาไว้คำนวณด้านการเงินมาประยุกต์ใช้ได้เฉยเลย ใครสนใจลองอ่านใน Link ได้ครับ
ดังนั้นอย่าลืมลองศึกษาหาความรู้เพิ่มเติมจากผู้เชี่ยวชาญทั้งหลาย รวมถึงการทำโจทย์ปัญหาที่หลากหลาย ตัวอย่างเช่น โจทย์ที่ Excel Wizard นำมาถาม หรือศึกษาเทคนิคจาก VDO Clip Live ต่างๆ เช่น ที่เค้าใช้ตอนแข่ง Excel Speed Run
ผมมั่นใจว่าหากลองทำตามคำแนะนำเหล่านี้ คุณจะเก่ง Excel ขึ้นอีกมากเลยครับ (ผมเองก็ยังต้องพัฒนาต่อไปเช่นกัน ฮึบๆ)
