[ Home ]  [ Today 's Event ]  [ FAQ ]  [ บันทึกงาน ]
User: Passwd:
ค้นหาข้อมูล:

รวมสูตร Excel พิศดาร แปลกจริง เจ๋งจัง

รวมสูตร Excel พิศดาร แปลกจริง เจ๋งจัง

การเขียนสูตร 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 เช่นกัน ก็จบละ)

รวมสูตร Excel พิศดาร แปลกจริง เจ๋งจัง 2

ซึ่งจะเห็นว่าสูตรดูยุ่งยากมากจริงๆ ดังนั้นมาดูอีกเทคนิคนึงที่เจ๋งมากๆ เลยนั่นก็คือ แทนที่ช่องว่างด้วยช่องว่างเยอะๆ (เมื่อเทียบกับข้อความ) ไปเลย เช่น 20 ตัว แล้วค่อยตัดเลือกสิ่งที่ต้องการมา แล้วค่อย TRIM เอาช่องว่างออกไป

ซึ่งสรุปแล้วสามารถเขียนสูตรได้แบบนี้ ซึ้งสั้นกว่าเยอะ ใช้ง่ายด้วย แต่มีความลึกซึ้ง

=TRIM(MID(SUBSTITUTE(ข้อความ," ",REPT(" ",เลขเยอะ)),เลขเยอะ*(ลำดับ-1),เลขเยอะ))
รวมสูตร Excel พิศดาร แปลกจริง เจ๋งจัง 3

ใครสนใจรายละเอียดเทคนิคนี้ ลองดูคลิปของ 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)

เท่านี้เราจะสามารถแยกข้อความออกมาทีละอักระได้แล้ว

รวมสูตร Excel พิศดาร แปลกจริง เจ๋งจัง 4

คราวนี้เราสามารถคัดเลือกเฉพาะสิ่งที่ต้องการได้ แล้วค่อยเอามารวมกันด้วย CONCAT หรือ TEXTJOIN (ต้องมี Excel ใหม่ๆ)

สมมติอยากได้เฉพาะภาษาไทยเท่านั้น ก็สามารถเขียนเงื่อนไขว่าถ้าเป็นภาษาไทยให้เก็บไว้ ถ้าไม่ใช่ให้เป็น “” (blank text) ไป ซึ่งสามารถใช้ CODE() มาช่วยเช็คได้ ซึ่งตัวเลข ตัวหนังสือ แต่ละอันจะมี Code ต่างกัน เช่น ถ้าอยากได้แต่ภาษาไทย code >=161 ก็น่าจะพอ

รวมสูตร Excel พิศดาร แปลกจริง เจ๋งจัง 5

แต่ถ้าจะเอา space กับ . ด้วย อาจเช็คว่า ต้องมี code >=161 หรือ <=46 (คราวนี้ขอใช้ LET เพื่อจะได้ไม่ต้องเขียนสูตรเดิมๆ ซ้ำหลายรอบ แต่ถ้าไม่มีก็สามารถเขียนซ้ำๆ ได้นะ)

รวมสูตร Excel พิศดาร แปลกจริง เจ๋งจัง 6

จากนั้นค่อยใช้ CONCAT หรือ TEXTJOIN มาเชื่อมก็ได้ แล้วเอา TRIM ครอบอีกที ดังนี้

รวมสูตร Excel พิศดาร แปลกจริง เจ๋งจัง 7

สูตรดึงข้อมูลเฉพาะตัวเลขด้วย 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))
รวมสูตร Excel พิศดาร แปลกจริง เจ๋งจัง 8

แล้วสูตรการเงินแบบ NPV ทำแบบนี้ได้อย่างไร??

สิ่งที่สูตร NPV ทำคือเอาตัวเลขแต่ละตัวไปหารด้วย (1+อัตราดอกเบี้ย)^งวด ซึ่งเป็นการ Discount มูลค่าเงินตามกาลเวลามาอยู่ที่ปัจจุบันนั่นเอง

ซึ่งพอเราพลิกแพลงใส่อัตราดอกเบี้ยเป็นเลข 9 (ดอกเบี้ย 900%) มันจะได้เป็นเอาแต่ละตัวไปหารด้วย (1+9)^งวด หรือ 10^งวดนั่นเอง จะได้แบบนี้ ตอนแรกมันจะออกมาเป็นทศนิยม ถ้าต้องการทำเป็นเลขปกติ เราแค่เอาไปคูณ 10^จำนวนตัวเลข ก็จะได้ผลตามต้องการแล้วล่ะ

รวมสูตร Excel พิศดาร แปลกจริง เจ๋งจัง 9

ใครสนใจรายละเอียดเทคนิคนี้ ลองดูคลิปของ Excel Wizard ได้ครับ

ดึงข้อมูลเลขที่มีจุดทศนิยมปนอยู่

ในเคสนี้ เราจะใช้สูตร NPV จะใช้ตรงๆ ไม่ได้ เพราะมันจะสนแค่ตัวเลขอย่างเดียว

ทางแก้นึงที่ทำได้ (หากดึงดันว่ายังจะใช้ NPV ต่อไป) ก็คือ เปลี่ยนทศนิยมเป็นเลขประหลาดๆ ซักชุดนึง แล้วค่อยเปลี่ยนกลับเป็น . ทีหลัง เช่น

=--SUBSTITUTE(
NPV(9,IFERROR(--MID(SUBSTITUTE(ข้อความ,".","เลขประหลาด"),ROW($A$1:$A$เลขเยอะ),1),""))
*10^COUNT(--MID(SUBSTITUTE(ข้อความ,".","เลขประหลาด"),ROW($A$1:$A$เลขเยอะ),1)),
"เลขประหลาด",".")
รวมสูตร Excel พิศดาร แปลกจริง เจ๋งจัง 10

อย่างไรก็ตาม ถ้ามีจุดทศนิยมด้วยแบบนี้ ทางคุณโบแนะนำว่าใช้วิธีพิศดารอีกอันดีกว่า (แต่ก็โคตรลึกซึ้งนะ) ซึ่งก็คือวิธีนี้ครับ

รวมสูตร Excel พิศดาร แปลกจริง เจ๋งจัง 11

พอรวบสูตรแล้วจะเป็นแบบนี้

=-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} ก็ได้
    รวมสูตร Excel พิศดาร แปลกจริง เจ๋งจัง 12
  • {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 ต่างๆ ซ้อนกันได้เรื่อยๆ เช่น

ข้อมูลอะไรซักอย่าง
ข้อมูล1
ข้อมูล2
ข้อมูล3

เจ้าฟังก์ชัน FILTERXML นั้น สามารถคัดเลือกเอาข้อมูลเฉพาะใน tag ที่ต้องการได้ โดยระบุเข้าไปใน xpath ของสูตร FILTERXML

FILTERXML(xml, xpath)

จริงๆ แล้ว xpath สามารถกำหนดเงื่อนไขได้มากมาย (ลองดูได้ที่นี่) แต่ถ้าเอาแบบง่ายสุด หากเราต้องการ tag ที่ชื่อว่า tagB ก็เขียน xpath แบบนี้ได้เลย “//tagB”

เป็นประโยชน์ต่อฉัน
เป็นประโยชน์ต่อผู้ปกครอง
เป็นประโยชน์ต่อนักเรียน
มีประโยชน์ต่อทุกคน
บุคลากร 0 บุคคลภายนอก 0

อ่าน 0 ครั้ง