--- title: รวมสูตร Excel พิศดาร แปลกจริง เจ๋งจัง url: https://www.thepexcel.com/strange-excel-formula/ type: post date: 2020-11-22 updated: 2025-12-22 author: Sira Ekabut categories: ["Highlights : บทความแนะนำ", Excel Advanced Formula] tags: [LOOKUP, textjoin, TRIM, filterxml, FIND, rept, NPV, CONCAT, Count, SEQUENCE, CHOOSE, LET, counta, CHAR, IFERROR, index, CODE, len, ROW, substitute, min, MID] --- # รวมสูตร Excel พิศดาร แปลกจริง เจ๋งจัง การเขียนสูตร Excel นั้น มีทั้งสูตรที่ง่ายและซับซ้อน บางสูตรต้องใช้ Logic หรือตรรกะในการคิดค่อนข้างสูง แต่ก็มีบางสูตรที่ดูเหมือนว่ามันจะอยู่ในระดับ “พิศดาร” ซึ่งเรียกว่าเหนือ Logic ปกติ หรือบางตัวถึงขั้นเหนือจินตนาการเลยก็ว่าได้ครับเพราะ**ฟังก์ชันหลายตัวมันไม่ได้ถูกออกแบบให้ทำงานนั้นๆ ด้วยซ้ำ แต่ด้วยการประยุกต์ขั้นสุดยอด มันจึงสามารถสร้างผลลัพธ์เจ๋งๆ ได้อย่างน่าอัศจรรย์** บอกไว้ก่อนว่าการที่เกิดสูตรพิศดารแบบนี้เพราะ Excel ดันยังไม่มีฟังก์ชันเจ๋งๆ ให้ใช้ได้แบบตรงไปตรงมาเหมือนของ Google Sheets น่ะสิ หึ!! แต่อย่าลืมว่า**ในชีวิตจริงเราไม่จำเป็นต้องแก้ปัญหาด้วยสูตรเสมอไป ปัญหาทุกข้อที่ผมเอามาเขียนในบทความนี้ สามารถแก้ได้ง่ายๆด้วย Power Query ของ Excel ด้วยซ้ำ** แต่ในบางครั้งเราต้องการให้ได้ผลลัพธ์ทันที (รวมถึงต้องการผูกกับ Conditional Format, Data Validation, หรือไม่ก็เพื่อฝึกสมอง 555) ก็เลยต้องเขียนเป็นสูตรออกมา เทคนิคหัวใจหลักของสูตรในบทความนี้ **ผมไม่ได้เป็นคนคิดเอง แต่มาจากการเห็นคนเก่ง Excel ท่านอื่นใช้มัน โดยเฉพาะ [คุณโบ Excel Wizard](http://facebook.com/XcWizard) **ซึ่งเอาสูตรเจ๋งๆ มาเผยแพร่เพียบเลย (หลายสูตรก็เห็นมาจากชาวต่างชาติเก่งๆ อีกที เรียกได้ว่ารวมสุดยอดเทคนิค) ที่ผมทำบทความนี้เพื่อจะได้รวบรวมสูตรเจ๋งๆ ที่ผมประทับใจไว้ในบทความเดียว (บางสูตรผมก็มีปรับนิดหน่อยตามความชอบของตัวเองด้วย) และจะทำการอธิบายหลักการทำงานของสูตรนั้นๆ ให้ เผื่อบางท่านสงสัยว่ามันทำงานยังไง? จะได้เป็นการเพิ่มความรู้ให้ตนเองได้นะครับ ## สูตรดึงข้อความที่มีตัวคั่นหลายตัว ปกติแล้วถ้าเรามีข้อมูลที่มีตัวคั่นหลายตัว การแยกด้วยสูตรจะค่อนข้างยุ่งยาก เพราะถ้าคิดแบบตรงไปตรงมาคือต้องค่อยๆ หาตำแหน่งของตัวคั่นแต่ละตัวให้ได้ก่อนแล้วตัดคำที่ต้องการออกมา ซึ่งวิธีที่ผมเคยใช้ในอดีตคือใช้ SUBSTITUTE แทนที่ช่องว่างลำดับต่างๆ ด้วยตัวประหลาดๆ แล้วค่อยหาตำแหน่งของตัวประหลาดนั้นๆ อีกทีด้วย FIND ซึ่งจะค่อนข้างยุ่งยาก (ถ้าใน Google Sheets ใช้ SPLIT, หรือใน Power Query ก็มี Split เช่นกัน ก็จบละ) ![1](https://www.thepexcel.com/wp-content/uploads/2020/11/strange-formula-001-1024x210.png) ซึ่งจะเห็นว่าสูตรดูยุ่งยากมากจริงๆ ดังนั้นมาดูอีกเทคนิคนึงที่เจ๋งมากๆ เลยนั่นก็คือ **แทนที่ช่องว่างด้วยช่องว่างเยอะๆ (เมื่อเทียบกับข้อความ) ไปเลย เช่น 20 ตัว แล้วค่อยตัดเลือกสิ่งที่ต้องการมา แล้วค่อย TRIM เอาช่องว่างออกไป** ซึ่งสรุปแล้วสามารถเขียนสูตรได้แบบนี้ ซึ้งสั้นกว่าเยอะ ใช้ง่ายด้วย แต่มีความลึกซึ้ง ``` =TRIM(MID(SUBSTITUTE(ข้อความ," ",REPT(" ",เลขเยอะ)),เลขเยอะ*(ลำดับ-1),เลขเยอะ)) ``` ![2](https://www.thepexcel.com/wp-content/uploads/2020/11/strange-formula-002-1024x309.png) ใครสนใจรายละเอียดเทคนิคนี้ ลองดู[คลิปของ Excel Wizard](https://youtu.be/7koBmXT3L10)ได้ครับ (ใช้แยกที่อยู่ ดีมากๆ) นอกจากวิธีนี้แล้ว เรายังสามารถใช้ฟังก์ชัน FILTERXML มาช่วยเลือกเอา item ลำดับที่ต้องการได้ด้วย [รายละเอียดอยู่ใน section หลัง](https://www.thepexcel.com/strange-excel-formula/#Append_%E0%B8%82%E0%B9%89%E0%B8%AD%E0%B8%A1%E0%B8%B9%E0%B8%A5%E0%B8%94%E0%B9%89%E0%B8%A7%E0%B8%A2%E0%B8%AA%E0%B8%B9%E0%B8%95%E0%B8%A3_FILTERXML)ครับ ## สูตรแยกข้อมูลออกมาทีละอักขระ แล้วคัดเอาตัวที่ต้องการ การทำงานแบบนี้ ถ้าจะทำง่ายๆ ใน Excel ก็สามารถทำได้ด้วย [Flash Fill](https://www.thepexcel.com/flash-fill-quick-win/) หรือไม่ก็ [Text.Select ของ Power Query](https://www.thepexcel.com/m-code-power-query-04-useful-tips/) แต่ถ้าเราจะเขียนด้วยสูตรจริงๆ ล่ะ จะทำไงดี? ถ้าเรามีคำอยู่ แล้วต้องการแยกแต่ละอักขระออกมาให้เป็นคนละ 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) ``` เท่านี้เราจะสามารถแยกข้อความออกมาทีละอักระได้แล้ว ![3](https://www.thepexcel.com/wp-content/uploads/2020/11/strange-formula-003x-1024x540.png) คราวนี้เราสามารถคัดเลือกเฉพาะสิ่งที่ต้องการได้ แล้วค่อยเอามารวมกันด้วย CONCAT หรือ TEXTJOIN (ต้องมี Excel ใหม่ๆ) สมมติผมอยากได้เฉพาะภาษาไทยเท่านั้น ผมก็สามารถเขียนเงื่อนไขว่าถ้าเป็นภาษาไทยให้เก็บไว้ ถ้าไม่ใช่ให้เป็น “” (blank text) ไป ซึ่งสามารถใช้ CODE() มาช่วยเช็คได้ ซึ่งตัวเลข ตัวหนังสือ แต่ละอันจะมี Code ต่างกัน เช่น ถ้าอยากได้แต่ภาษาไทย code >=161 ก็น่าจะพอ ![4](https://www.thepexcel.com/wp-content/uploads/2020/11/strange-formula-004-1-1024x408.png) แต่ถ้าจะเอา space กับ . ด้วย ผมอาจเช็คว่า ต้องมี code >=161 หรือ <=46 (คราวนี้ขอใช้ LET เพื่อจะได้ไม่ต้องเขียนสูตรเดิมๆ ซ้ำหลายรอบ แต่ถ้าไม่มีก็สามารถเขียนซ้ำๆ ได้นะ) ![5](https://www.thepexcel.com/wp-content/uploads/2020/11/strange-formula-005-1024x403.png) จากนั้นค่อยใช้ CONCAT หรือ TEXTJOIN มาเชื่อมก็ได้ แล้วเอา TRIM ครอบอีกที ดังนี้ ![6](https://www.thepexcel.com/wp-content/uploads/2020/11/strange-formula-006-1024x347.png) ## สูตรดึงข้อมูลเฉพาะตัวเลขด้วย NPV การที่เราได้ข้อมูลที่ปนมาทั้งตัวหนังสือและตัวเลข แล้วเราต้องการเฉพาะตัวเลขนั้น ถ้าแก้ปัญหาด้วย Logic ปกติ ก็อาจจะพยายามแยกอักขระแต่ละตัวมาพิจารณา แล้วคัดเลือกเอาไว้เฉพาะ 0-9 เท่านั้น คล้ายๆ วิธีในข้อข้างบน แต่หากทำด้วยท่าพิศดาร ปรากฏว่ามันสามารถใช้ฟังก์ชันทางการเงินที่ชื่อว่า [NPV (Net Present Value)](https://www.thepexcel.com/finance-01-theory/) มาช่วยได้เฉยเลย และดีกว่าวิธีข้างบนตรงที่ไม่จำเป็นต้องใช้ 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)) ``` ![7](https://www.thepexcel.com/wp-content/uploads/2020/11/strange-formula-007-1024x410.png) ### แล้วสูตรการเงินแบบ NPV ทำแบบนี้ได้อย่างไร?? สิ่งที่สูตร NPV ทำคือเอาตัวเลขแต่ละตัวไปหารด้วย (1+อัตราดอกเบี้ย)^งวด ซึ่งเป็นการ Discount มูลค่าเงินตามกาลเวลามาอยู่ที่ปัจจุบันนั่นเอง ซึ่งพอเราพลิกแพลงใส่อัตราดอกเบี้ยเป็นเลข 9 (ดอกเบี้ย 900%) มันจะได้เป็นเอาแต่ละตัวไปหารด้วย (1+9)^งวด หรือ 10^งวดนั่นเอง จะได้แบบนี้ ตอนแรกมันจะออกมาเป็นทศนิยม ถ้าต้องการทำเป็นเลขปกติ เราแค่เอาไปคูณ 10^จำนวนตัวเลข ก็จะได้ผลตามต้องการแล้วล่ะ ![8](https://www.thepexcel.com/wp-content/uploads/2020/11/strange-formula-009-1024x351.png) ใครสนใจรายละเอียดเทคนิคนี้ ลองดู[คลิปของ Excel Wizard ไ](https://youtu.be/dbTH590_RDg)ด้ครับ ## ดึงข้อมูลเลขที่มีจุดทศนิยมปนอยู่ ในเคสนี้ เราจะใช้สูตร NPV จะใช้ตรงๆ ไม่ได้ เพราะมันจะสนแค่ตัวเลขอย่างเดียว ทางแก้นึงที่ทำได้ (หากดึงดันว่ายังจะใช้ NPV ต่อไป) ก็คือ เปลี่ยนทศนิยมเป็นเลขประหลาดๆ ซักชุดนึง แล้วค่อยเปลี่ยนกลับเป็น . ทีหลัง เช่น ``` =--SUBSTITUTE( NPV(9,IFERROR(--MID(SUBSTITUTE(ข้อความ,".","เลขประหลาด"),ROW($A$1:$A$เลขเยอะ),1),"")) *10^COUNT(--MID(SUBSTITUTE(ข้อความ,".","เลขประหลาด"),ROW($A$1:$A$เลขเยอะ),1)), "เลขประหลาด",".") ``` ![9](https://www.thepexcel.com/wp-content/uploads/2020/11/strange-formula-015-1024x408.png) อย่างไรก็ตาม ถ้ามีจุดทศนิยมด้วยแบบนี้ ทางคุณโบแนะนำว่าใช้วิธีพิศดารอีกอันดีกว่า (แต่ก็โคตรลึกซึ้งนะ) ซึ่งก็คือวิธีนี้ครับ ![10](https://www.thepexcel.com/wp-content/uploads/2020/11/strange-formula-017-1024x463.png) พอรวบสูตรแล้วจะเป็นแบบนี้ ``` =-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} ก็ได้ ![11](https://www.thepexcel.com/wp-content/uploads/2020/11/strange-formula-018.png) - {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](https://youtu.be/7F3miufBSXU) ได้ครับ ## Append ข้อมูลด้วยสูตร FILTERXML สิ่งนึงที่สูตร Excel ยังทำได้ค่อนข้างลำบาก ทั้งๆ ที่เป็นเรื่องที่ควรทำได้ง่ายๆ ก็คือการเอาข้อมูล 2 ตารางมาต่อแถวกันด้วยสูตร ซึ่งถ้าใช้ Power Query ก็จะมีคำสั่ง Append Query ให้ทำได้แบบชิลๆ เลย หรือถ้าเป็นใน DAX ก็จะมี UNION ให้ใช้แบบสบายๆ เช่นกัน แต่ใน Excel ดันไม่มีวิธี Append ที่ง่ายๆ เลย นอกจากการประยุกต์เอาฟังก์ชัน FILTERXML มาใช้ ### ทำความรู้จัก FILTERXML ปกติแล้วฟังก์ชัน FILTERXML เอาไว้ใช้งานกับข้อมูล XML หรือแม้แต่ HTML บนเว็บไซต์ซึ่งจะมี Tag เปิด ปิด แต่ละ Element ที่เป็นองค์ประกอบของเว็บนั้นๆ อยู่ด้วย ซึ่งจะมีวิธีการเขียนประมาณนี้ ซึ่งสังเกตว่าจะมีการเปิด และปิด tag ด้วย ``` ข้อมูล ``` ซึ่งเราสามารถใส่ tag ต่างๆ ซ้อนกันได้เรื่อยๆ เช่น ``` ข้อมูลอะไรซักอย่าง ข้อมูล1 ข้อมูล2 ข้อมูล3 ``` เจ้าฟังก์ชัน FILTERXML นั้น สามารถคัดเลือกเอาข้อมูลเฉพาะใน tag ที่ต้องการได้ โดยระบุเข้าไปใน xpath ของสูตร FILTERXML ``` FILTERXML(xml, xpath) ``` จริงๆ แล้ว xpath สามารถกำหนดเงื่อนไขได้มากมาย ([ลองดูได้ที่นี่](https://www.w3schools.com/xml/xpath_syntax.asp)) แต่ถ้าเอาแบบง่ายสุด หากเราต้องการ tag ที่ชื่อว่า tagB ก็เขียน xpath แบบนี้ได้เลย “//tagB” เช่น ถ้าเรามีข้อมูล xml แบบนี้ในช่อง A1 ``` Excel Level Up All Foundations 1 Excel Power Up Power Query 2 Powerful Data with Power BI Power BI 2 ``` เราจะดึงองค์ประกอบแต่ละส่วนได้แบบนี้เลย ซึ่งจะเห็นว่ามี tag ย่อยชื่อว่า name, topic, level ให้เราสามารถดึงได้ ![12](https://www.thepexcel.com/wp-content/uploads/2020/11/strange-formula-010-1024x330.png) ### วิธีที่เหมาะกับ Append คอลัมน์เดียว แบบง่าย คือ Append ข้อมูลที่มีแค่คอลัมน์เดียว เช่นแบบนี้ ![13](https://www.thepexcel.com/wp-content/uploads/2020/11/strange-formula-011-1024x383.png) ซึ่งรวบสูตรได้เป็นแบบนี้ ``` =FILTERXML(""&TEXTJOIN("",TRUE,A1:A4,C1:C3)&"","//b") ``` สามารถเขียนในรูปทั่วไปได้ว่า ``` =FILTERXML(""&TEXTJOIN("",TRUE,Range1,Range2,Range3...)&"","//b") ``` ### วิธี Append หลายคอลัมน์ ทำได้หลายแบบ #### ใช้ CHOOSE (เหมาะกับคอลัมน์แค่ 2) ถ้ามีแค่ 2 คอลัมน์ เราจะใช้วิธีคล้ายเดิม แต่ผสมกับ CHOOSE อีกทีเพื่อทำการรวม 2 คอลัมน์เข้าด้วยกัน ตามนี้ ![14](https://www.thepexcel.com/wp-content/uploads/2020/11/strange-formula-012-1024x432.png) ถ้ารวบสูตรก็จะได้ประมาณนี้ ``` =LET( range1,A1:B4, range2,A6:B8, Mycol1,FILTERXML(""&TEXTJOIN("",TRUE,INDEX(range1,0,1),INDEX(range2,0,1))&"","//b"), Mycol2,FILTERXML(""&TEXTJOIN("",TRUE,INDEX(range1,0,2),INDEX(range2,0,2))&"","//b"), CHOOSE({1,2},Mycol1,Mycol2)) ``` #### ใช้ INDEX+SEQUENCE (วิธีนี้ใช้ได้กับกี่คอลัมน์ก็ได้) แต่ถ้าจำนวนคอลัมน์มากกว่านี้ ผมแนะนำวิธีของคุณโบ Excel Wizard ซึ่งรองรับหลายคอลัมน์ (แต่จำนวนแถวอาจจะได้แค่ประมาณหลักร้อยบรรทัด เพราะติดข้อจำกัดการเก็บข้อมูล text ของ Excel) ซึ่งของคุณโบทำได้ด้วยวิธีนี้ (ผมมีดัดแปลงตอนจบนิดหน่อย ให้เปลี่ยน range ได้สะดวกขึ้น) ![15](https://www.thepexcel.com/wp-content/uploads/2020/11/strange-formula-013-1024x399.png) ถ้ารวบสูตร และทำให้ Range เหลือแค่ 2 ตัว ให้อ้างอิงง่ายๆ ด้วย LET ก็ทำได้ตามนี้ (ซึ่ง Range จะใช้เป็น Table ก็ได้ จะได้งอกตามได้ง่ายๆ) ``` =LET(range1,A1:C4, range2,A6:C8, INDEX(FILTERXML(""&TEXTJOIN("",TRUE,range1,range2)&"","//b"),SEQUENCE(COUNTA(INDEX(range1,0,1),INDEX(range2,0,1)),COUNTA(INDEX(range1,1,0))))) ``` ![16](https://www.thepexcel.com/wp-content/uploads/2020/11/strange-formula-014-1024x418.png) ใครสนใจรายละเอียดเทคนิคนี้ ลองดู[คลิปของ Excel Wizard](https://youtu.be/0mxhftN4Fk4) ได้ครับ ### วิธีใช้ FILTERXML เลือก item ตัวที่ต้องการ จริงๆ แล้วเราสามารถใช้ FILTERXML ให้ได้ผลลัพธ์ทุกตัว แล้วใช้ INDEX เลือกเอาตัวที่ต้องการมาอีกทีก็ได้ แต่มันยุ่งยากไปวิธีที่ดีกว่าคือใส่ xpath ให้เอาตัวนั้นๆ กลับมาให้เราเลย เราสามารถ**ใส่ xpath ว่า //tag[ลำดับitem] เพื่อดึงเอาเฉพาะ item ลำดับที่ต้องการได้เลย **แปลว่าสามารถใช้แทนวิธีแทนด้วยช่องว่างเยอะๆ ได้ทันทีครับ ![17](https://www.thepexcel.com/wp-content/uploads/2020/11/strange-formula-016-1024x509.png) ## จบแล้ว เป็นยังไงบ้างกับเทคนิคสูตร Excel พิศดารที่ผมเอามาแนะนำให้รู้จัก หลายๆ สูตรนั้นมีความลึกซึ้งสูงมาก ผมมั่นใจว่าคนที่เหลือรอดมาถึงข้อความบรรทัดนี้ได้คงมีไม่ถึง 20% ของคนที่เข้ามาอ่านบทความนี้แน่นอน (หึหึ) แต่คุณก็น่าจะได้ไอเดียดีๆ ไปประยุกต์กับสูตรของคุณได้อีกมากมายครับ ขอบคุณอีกครั้งที่อ่านมาจนจบนะครับ --- _Source: [https://www.thepexcel.com/strange-excel-formula/](https://www.thepexcel.com/strange-excel-formula/)_