การเขียนสูตร Excel นั้น มีทั้งสูตรที่ง่ายและซับซ้อน บางสูตรต้องใช้ Logic หรือตรรกะในการคิดค่อนข้างสูง แต่ก็มีบางสูตรที่ดูเหมือนว่ามันจะอยู่ในระดับ “พิศดาร” ซึ่งเรียกว่าเหนือ Logic ปกติ หรือบางตัวถึงขั้นเหนือจินตนาการเลยก็ว่าได้ครับเพราะฟังก์ชันหลายตัวมันไม่ได้ถูกออกแบบให้ทำงานนั้นๆ ด้วยซ้ำ แต่ด้วยการประยุกต์ขั้นสุดยอด มันจึงสามารถสร้างผลลัพธ์เจ๋งๆ ได้อย่างน่าอัศจรรย์
บอกไว้ก่อนว่าการที่เกิดสูตรพิศดารแบบนี้เพราะ Excel ดันยังไม่มีฟังก์ชันเจ๋งๆ ให้ใช้ได้แบบตรงไปตรงมาเหมือนของ Google Sheets น่ะสิ หึ!!
แต่อย่าลืมว่าในชีวิตจริงเราไม่จำเป็นต้องแก้ปัญหาด้วยสูตรเสมอไป ปัญหาทุกข้อที่ผมเอามาเขียนในบทความนี้ สามารถแก้ได้ง่ายๆด้วย Power Query ของ Excel ด้วยซ้ำ แต่ในบางครั้งเราต้องการให้ได้ผลลัพธ์ทันที (รวมถึงต้องการผูกกับ Conditional Format, Data Validation, หรือไม่ก็เพื่อฝึกสมอง 555) ก็เลยต้องเขียนเป็นสูตรออกมา
เทคนิคหัวใจหลักของสูตรในบทความนี้ ผมไม่ได้เป็นคนคิดเอง แต่มาจากการเห็นคนเก่ง Excel ท่านอื่นใช้มัน โดยเฉพาะ คุณโบ Excel Wizard ซึ่งเอาสูตรเจ๋งๆ มาเผยแพร่เพียบเลย (หลายสูตรก็เห็นมาจากชาวต่างชาติเก่งๆ อีกที เรียกได้ว่ารวมสุดยอดเทคนิค)
ที่ผมทำบทความนี้เพื่อจะได้รวบรวมสูตรเจ๋งๆ ที่ผมประทับใจไว้ในบทความเดียว (บางสูตรผมก็มีปรับนิดหน่อยตามความชอบของตัวเองด้วย) และจะทำการอธิบายหลักการทำงานของสูตรนั้นๆ ให้ เผื่อบางท่านสงสัยว่ามันทำงานยังไง? จะได้เป็นการเพิ่มความรู้ให้ตนเองได้นะครับ
สารบัญ
สูตรดึงข้อความที่มีตัวคั่นหลายตัว
ปกติแล้วถ้าเรามีข้อมูลที่มีตัวคั่นหลายตัว การแยกด้วยสูตรจะค่อนข้างยุ่งยาก เพราะถ้าคิดแบบตรงไปตรงมาคือต้องค่อยๆ หาตำแหน่งของตัวคั่นแต่ละตัวให้ได้ก่อนแล้วตัดคำที่ต้องการออกมา ซึ่งวิธีที่ผมเคยใช้ในอดีตคือใช้ SUBSTITUTE แทนที่ช่องว่างลำดับต่างๆ ด้วยตัวประหลาดๆ แล้วค่อยหาตำแหน่งของตัวประหลาดนั้นๆ อีกทีด้วย FIND ซึ่งจะค่อนข้างยุ่งยาก (ถ้าใน Google Sheets ใช้ SPLIT, หรือใน Power Query ก็มี Split เช่นกัน ก็จบละ)
ซึ่งจะเห็นว่าสูตรดูยุ่งยากมากจริงๆ ดังนั้นมาดูอีกเทคนิคนึงที่เจ๋งมากๆ เลยนั่นก็คือ แทนที่ช่องว่างด้วยช่องว่างเยอะๆ (เมื่อเทียบกับข้อความ) ไปเลย เช่น 20 ตัว แล้วค่อยตัดเลือกสิ่งที่ต้องการมา แล้วค่อย TRIM เอาช่องว่างออกไป
ซึ่งสรุปแล้วสามารถเขียนสูตรได้แบบนี้ ซึ้งสั้นกว่าเยอะ ใช้ง่ายด้วย แต่มีความลึกซึ้ง
=TRIM(MID(SUBSTITUTE(ข้อความ," ",REPT(" ",เลขเยอะ)),เลขเยอะ*(ลำดับ-1),เลขเยอะ))
ใครสนใจรายละเอียดเทคนิคนี้ ลองดูคลิปของ Excel Wizard ได้ครับ (ใช้แยกที่อยู่ ดีมากๆ)
นอกจากวิธีนี้แล้ว เรายังสามารถใช้ฟังก์ชัน FILTERXML มาช่วยเลือกเอา item ลำดับที่ต้องการได้ด้วย รายละเอียดอยู่ใน section หลังครับ
สูตรแยกข้อมูลออกมาทีละอักขระ แล้วคัดเอาตัวที่ต้องการ
การทำงานแบบนี้ ถ้าจะทำง่ายๆ ใน Excel ก็สามารถทำได้ด้วย Flash Fill หรือไม่ก็ Text.Select ของ Power Query แต่ถ้าเราจะเขียนด้วยสูตรจริงๆ ล่ะ จะทำไงดี?
ถ้าเรามีคำอยู่ แล้วต้องการแยกแต่ละอักขระออกมาให้เป็นคนละ item กัน เพื่อที่จะเอาไปทำงานต่อ เราสามารถใช้ MID มาช่วย
=MID(text,start_num,num_chars)โดยใส่ข้อมูลใน start_num แบบ Array เป็นเลขลำดับได้ โดยจะใช้ SEQUENCE หรือพวก ROW สร้างขึ้นมาก็ได้
ถ้ามี SEQUENCE ให้ใช้ (Excel 365)
MID(ข้อความ,SEQUENCE(LEN(ข้อความ)),1)ถ้าไม่มี SEQUENCE
MID(ข้อความ,ROW($A$1:$A$เลขเยอะๆ),1)เท่านี้เราจะสามารถแยกข้อความออกมาทีละอักระได้แล้ว
คราวนี้เราสามารถคัดเลือกเฉพาะสิ่งที่ต้องการได้ แล้วค่อยเอามารวมกันด้วย CONCAT หรือ TEXTJOIN (ต้องมี Excel ใหม่ๆ)
สมมติผมอยากได้เฉพาะภาษาไทยเท่านั้น ผมก็สามารถเขียนเงื่อนไขว่าถ้าเป็นภาษาไทยให้เก็บไว้ ถ้าไม่ใช่ให้เป็น “” (blank text) ไป ซึ่งสามารถใช้ CODE() มาช่วยเช็คได้ ซึ่งตัวเลข ตัวหนังสือ แต่ละอันจะมี Code ต่างกัน เช่น ถ้าอยากได้แต่ภาษาไทย code >=161 ก็น่าจะพอ
แต่ถ้าจะเอา space กับ . ด้วย ผมอาจเช็คว่า ต้องมี code >=161 หรือ <=46 (คราวนี้ขอใช้ LET เพื่อจะได้ไม่ต้องเขียนสูตรเดิมๆ ซ้ำหลายรอบ แต่ถ้าไม่มีก็สามารถเขียนซ้ำๆ ได้นะ)
จากนั้นค่อยใช้ CONCAT หรือ TEXTJOIN มาเชื่อมก็ได้ แล้วเอา TRIM ครอบอีกที ดังนี้
สูตรดึงข้อมูลเฉพาะตัวเลขด้วย NPV
การที่เราได้ข้อมูลที่ปนมาทั้งตัวหนังสือและตัวเลข แล้วเราต้องการเฉพาะตัวเลขนั้น ถ้าแก้ปัญหาด้วย Logic ปกติ ก็อาจจะพยายามแยกอักขระแต่ละตัวมาพิจารณา แล้วคัดเลือกเอาไว้เฉพาะ 0-9 เท่านั้น คล้ายๆ วิธีในข้อข้างบน
แต่หากทำด้วยท่าพิศดาร ปรากฏว่ามันสามารถใช้ฟังก์ชันทางการเงินที่ชื่อว่า NPV (Net Present Value) มาช่วยได้เฉยเลย และดีกว่าวิธีข้างบนตรงที่ไม่จำเป็นต้องใช้ Excel365 ด้วย (ไม่จำเป็นต้องมี CONCAT หรือ TEXTJOIN) ซึ่งทำได้ดังนี้
=NPV(9,ช่วงข้องมูล)*10^COUNT(ช่วงข้อมูล)โดยที่ถ้าข้อมูลปนกันมาใน Cell เดียวเลย จะยากขึ้น คือต้องใช้สูตรแตกอักระออกมาให้ได้ก่อน (เหมือนหัวข้อที่แล้ว) แล้วค่อยใช้ NPV ดังนี้
=NPV(9,IFERROR(--MID(ข้อความ,ROW($A$1:$A$เลขเยอะๆ),1),""))
*10^COUNT(--MID(ข้อความ,ROW($A$1:$A$เลขเยอะๆ),1))
แล้วสูตรการเงินแบบ NPV ทำแบบนี้ได้อย่างไร??
สิ่งที่สูตร NPV ทำคือเอาตัวเลขแต่ละตัวไปหารด้วย (1+อัตราดอกเบี้ย)^งวด ซึ่งเป็นการ Discount มูลค่าเงินตามกาลเวลามาอยู่ที่ปัจจุบันนั่นเอง
ซึ่งพอเราพลิกแพลงใส่อัตราดอกเบี้ยเป็นเลข 9 (ดอกเบี้ย 900%) มันจะได้เป็นเอาแต่ละตัวไปหารด้วย (1+9)^งวด หรือ 10^งวดนั่นเอง จะได้แบบนี้ ตอนแรกมันจะออกมาเป็นทศนิยม ถ้าต้องการทำเป็นเลขปกติ เราแค่เอาไปคูณ 10^จำนวนตัวเลข ก็จะได้ผลตามต้องการแล้วล่ะ
ใครสนใจรายละเอียดเทคนิคนี้ ลองดูคลิปของ Excel Wizard ได้ครับ
ดึงข้อมูลเลขที่มีจุดทศนิยมปนอยู่
ในเคสนี้ เราจะใช้สูตร NPV จะใช้ตรงๆ ไม่ได้ เพราะมันจะสนแค่ตัวเลขอย่างเดียว
ทางแก้นึงที่ทำได้ (หากดึงดันว่ายังจะใช้ NPV ต่อไป) ก็คือ เปลี่ยนทศนิยมเป็นเลขประหลาดๆ ซักชุดนึง แล้วค่อยเปลี่ยนกลับเป็น . ทีหลัง เช่น
=--SUBSTITUTE(
NPV(9,IFERROR(--MID(SUBSTITUTE(ข้อความ,".","เลขประหลาด"),ROW($A$1:$A$เลขเยอะ),1),""))
*10^COUNT(--MID(SUBSTITUTE(ข้อความ,".","เลขประหลาด"),ROW($A$1:$A$เลขเยอะ),1)),
"เลขประหลาด",".")
อย่างไรก็ตาม ถ้ามีจุดทศนิยมด้วยแบบนี้ ทางคุณโบแนะนำว่าใช้วิธีพิศดารอีกอันดีกว่า (แต่ก็โคตรลึกซึ้งนะ) ซึ่งก็คือวิธีนี้ครับ
พอรวบสูตรแล้วจะเป็นแบบนี้
=-LOOKUP(0,-MID(ข้อความ,MIN(FIND({0,1,2,3,4,5,6,7,8,9},ข้อความ&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12}))โดยที่สามารถทำให้สั้นลงได้อีก เป็นแบบนี้ (มันคือเรื่องเดียวกัน)
=-LOOKUP(0,-MID(A2,MIN(FIND(ROW($A$1:$A$10)-1,A2&1/17)),ROW($A$1:$A$เลขเยอะๆ)))- {0,1,2,3,4,5,6,7,8,9} ทดแทนได้ด้วย ROW($A$1:$A$10)-1 หรือแทนด้วย {0,1,2,3,4}+{0;5} ก็ได้
- {1,2,3,4,5,6,7,8,9,10,11,12,…} ทดแทนได้ด้วย ROW($A$1:$A$เลขเยอะๆ)
- &”0123456789″ ทดแทนได้ด้วย &1/17
- เพราะว่า 1/17 จะได้เลขครบตั้งแต่ 0-9 เลย คือ 0.0588235294117647 (เหอๆ ใครคิดมาเนี่ย…)
ใครสนใจรายละเอียดเทคนิคนี้ ลองดูคลิปของ Excel Wizard ได้ครับ
Append ข้อมูลด้วยสูตร FILTERXML
สิ่งนึงที่สูตร Excel ยังทำได้ค่อนข้างลำบาก ทั้งๆ ที่เป็นเรื่องที่ควรทำได้ง่ายๆ ก็คือการเอาข้อมูล 2 ตารางมาต่อแถวกันด้วยสูตร ซึ่งถ้าใช้ Power Query ก็จะมีคำสั่ง Append Query ให้ทำได้แบบชิลๆ เลย หรือถ้าเป็นใน DAX ก็จะมี UNION ให้ใช้แบบสบายๆ เช่นกัน
แต่ใน Excel ดันไม่มีวิธี Append ที่ง่ายๆ เลย นอกจากการประยุกต์เอาฟังก์ชัน FILTERXML มาใช้
ทำความรู้จัก FILTERXML
ปกติแล้วฟังก์ชัน FILTERXML เอาไว้ใช้งานกับข้อมูล XML หรือแม้แต่ HTML บนเว็บไซต์ซึ่งจะมี Tag เปิด ปิด แต่ละ Element ที่เป็นองค์ประกอบของเว็บนั้นๆ อยู่ด้วย ซึ่งจะมีวิธีการเขียนประมาณนี้ ซึ่งสังเกตว่าจะมีการเปิด และปิด tag ด้วย
<tag>ข้อมูล</tag>ซึ่งเราสามารถใส่ tag ต่างๆ ซ้อนกันได้เรื่อยๆ เช่น
<tagA>ข้อมูลอะไรซักอย่าง
<tagB>ข้อมูล1</tagB>
<tagB>ข้อมูล2</tagB>
<tagB>ข้อมูล3</tagB>
</tagA>เจ้าฟังก์ชัน FILTERXML นั้น สามารถคัดเลือกเอาข้อมูลเฉพาะใน tag ที่ต้องการได้ โดยระบุเข้าไปใน xpath ของสูตร FILTERXML
FILTERXML(xml, xpath)จริงๆ แล้ว xpath สามารถกำหนดเงื่อนไขได้มากมาย (ลองดูได้ที่นี่) แต่ถ้าเอาแบบง่ายสุด หากเราต้องการ tag ที่ชื่อว่า tagB ก็เขียน xpath แบบนี้ได้เลย “//tagB”
เช่น ถ้าเรามีข้อมูล xml แบบนี้ในช่อง A1
<ThepExcelCourse>
<Course>
<name>Excel Level Up</name>
<topic>All Foundations</topic>
<level>1</level>
</Course>
<Course>
<name>Excel Power Up</name>
<topic>Power Query</topic>
<level>2</level>
</Course>
<Course>
<name>Powerful Data with Power BI</name>
<topic>Power BI</topic>
<level>2</level>
</Course>
</ThepExcelCourse>เราจะดึงองค์ประกอบแต่ละส่วนได้แบบนี้เลย ซึ่งจะเห็นว่ามี tag ย่อยชื่อว่า name, topic, level ให้เราสามารถดึงได้
วิธีที่เหมาะกับ Append คอลัมน์เดียว
แบบง่าย คือ Append ข้อมูลที่มีแค่คอลัมน์เดียว เช่นแบบนี้
ซึ่งรวบสูตรได้เป็นแบบนี้
=FILTERXML("<a><b>"&TEXTJOIN("</b><b>",TRUE,A1:A4,C1:C3)&"</b></a>","//b")สามารถเขียนในรูปทั่วไปได้ว่า
=FILTERXML("<a><b>"&TEXTJOIN("</b><b>",TRUE,Range1,Range2,Range3...)&"</b></a>","//b")วิธี Append หลายคอลัมน์ ทำได้หลายแบบ
ใช้ CHOOSE (เหมาะกับคอลัมน์แค่ 2)
ถ้ามีแค่ 2 คอลัมน์ เราจะใช้วิธีคล้ายเดิม แต่ผสมกับ CHOOSE อีกทีเพื่อทำการรวม 2 คอลัมน์เข้าด้วยกัน ตามนี้
ถ้ารวบสูตรก็จะได้ประมาณนี้
=LET(
range1,A1:B4,
range2,A6:B8,
Mycol1,FILTERXML("<a><b>"&TEXTJOIN("</b><b>",TRUE,INDEX(range1,0,1),INDEX(range2,0,1))&"</b></a>","//b"),
Mycol2,FILTERXML("<a><b>"&TEXTJOIN("</b><b>",TRUE,INDEX(range1,0,2),INDEX(range2,0,2))&"</b></a>","//b"),
CHOOSE({1,2},Mycol1,Mycol2))ใช้ INDEX+SEQUENCE (วิธีนี้ใช้ได้กับกี่คอลัมน์ก็ได้)
แต่ถ้าจำนวนคอลัมน์มากกว่านี้ ผมแนะนำวิธีของคุณโบ Excel Wizard ซึ่งรองรับหลายคอลัมน์ (แต่จำนวนแถวอาจจะได้แค่ประมาณหลักร้อยบรรทัด เพราะติดข้อจำกัดการเก็บข้อมูล text ของ Excel)
ซึ่งของคุณโบทำได้ด้วยวิธีนี้ (ผมมีดัดแปลงตอนจบนิดหน่อย ให้เปลี่ยน range ได้สะดวกขึ้น)
ถ้ารวบสูตร และทำให้ Range เหลือแค่ 2 ตัว ให้อ้างอิงง่ายๆ ด้วย LET ก็ทำได้ตามนี้ (ซึ่ง Range จะใช้เป็น Table ก็ได้ จะได้งอกตามได้ง่ายๆ)
=LET(range1,A1:C4,
range2,A6:C8,
INDEX(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",TRUE,range1,range2)&"</b></a>","//b"),SEQUENCE(COUNTA(INDEX(range1,0,1),INDEX(range2,0,1)),COUNTA(INDEX(range1,1,0)))))
ใครสนใจรายละเอียดเทคนิคนี้ ลองดูคลิปของ Excel Wizard ได้ครับ
วิธีใช้ FILTERXML เลือก item ตัวที่ต้องการ
จริงๆ แล้วเราสามารถใช้ FILTERXML ให้ได้ผลลัพธ์ทุกตัว แล้วใช้ INDEX เลือกเอาตัวที่ต้องการมาอีกทีก็ได้ แต่มันยุ่งยากไปวิธีที่ดีกว่าคือใส่ xpath ให้เอาตัวนั้นๆ กลับมาให้เราเลย
เราสามารถใส่ xpath ว่า //tag[ลำดับitem] เพื่อดึงเอาเฉพาะ item ลำดับที่ต้องการได้เลย แปลว่าสามารถใช้แทนวิธีแทนด้วยช่องว่างเยอะๆ ได้ทันทีครับ
จบแล้ว
เป็นยังไงบ้างกับเทคนิคสูตร Excel พิศดารที่ผมเอามาแนะนำให้รู้จัก หลายๆ สูตรนั้นมีความลึกซึ้งสูงมาก ผมมั่นใจว่าคนที่เหลือรอดมาถึงข้อความบรรทัดนี้ได้คงมีไม่ถึง 20% ของคนที่เข้ามาอ่านบทความนี้แน่นอน (หึหึ) แต่คุณก็น่าจะได้ไอเดียดีๆ ไปประยุกต์กับสูตรของคุณได้อีกมากมายครับ ขอบคุณอีกครั้งที่อ่านมาจนจบนะครับ
