October 2024

สูตร Excel แยกเฉพาะตัวเลข (RegEx)

อยากแยกตัวเลขออกจากข้อความแต่ตัวเลขมีทั้งจุดทศนิยม คอมมา แถมยังติดลบทำไงดีเนี่ย? ใช้สูตรนี้สิ 🙂 REGEXEXTRACT คือฟังก์ชันใหม่ใน Excel ใช้แยกสิ่งที่ต้องการออกจากข้อความ “([\-\+]? ?[\.,]?[0-9][\.,0-9]*( ?e ?[\-\+]? ?\d+)?)” คือ RegEx Pattern ใช้แยกตัวเลขโดยคำนึงถึงจุดทศนิยม ( . ) เครื่องหมายลบ ( – ) และ comma ( , )(ให้ ChatGPT ช่วยเขียน RegEx Pattern ได้) (ถ้าไม่ใช้ REGEXEXTRACT สูตรจะยาวกว่านี้มาก) อ้อ, สูตรนี้ใช้ได้กับ Excel 365 Beta Channel เท่านั้นถ้าใครใช้ Excel 365 แต่ไม่ใช่ Beta Channel (Insider Program) อดใจรอสักระยะนะครับ 🙂

Function

เทคนิคหาค่าเฉลี่ยแบบไม่รวม Max, Min

หาค่าเฉลี่ยแบบไม่รวม Max, Min ทำไง? ใช้สูตรนี้ได้เลย 🙂 TRIMMEAN คือฟังก์ชันคำนวณค่าเฉลี่ยโดยตัดค่าสูงและต่ำ (Outliers) ทิ้ง โครงสร้างคือ TRIMMEAN( array, percent ) percent คือเปอร์เซ็นต์รวมของข้อมูลสูงและต่ำที่ต้องการตัด (เรียงข้อมูลจากน้อยไปมาก) percent หมายถึง สัดส่วนข้อมูล ไม่ใช่เปอร์เซ็นต์ไทล์ (Percentile)ถ้าระบุ 20% แปลว่า ไม่รวมข้อมูล 10% มากสุด และ 10% น้อยสุด 2/COUNT(A2:A9) คือสูตรคำนวณ percent โดยไม่รวมค่า Max, Min สูตรนี้ เจ๋งดีเนอะ 😊

Function

เทคนิค Coalesce ใน Excel

อยากดึงค่าแรกที่ไม่ใช่ค่าว่าง ทำไง? ใช้สูตรนี้ได้เลย 🙂 COALESCE คือเทคนิคใน SQL ใช้ดึงค่าแรกที่ไม่ใช่ค่าว่าง COALESCE( 1, 2, 3 ) ผลลัพธ์คือ 1COALESCE( NULL, 2, 3 ) ผลลัพธ์คือ 2 Excel ไม่มีฟังก์ชัน COALESCE แต่ประยุกต์ใช้ XLOOKUP แทนได้ 🙂 หรือใช้ INDEX+MATCH ก็ได้เช่นกัน ถ้าใช้ Power Query ใช้ coalesce operator ( ?? ) ได้เลย สนุกดีเนอะ 😊

Function

สูตร Excel แยกตัวเลขจากข้อความ

อยากแยกตัวเลขออกจากข้อความแถมตัวเลขมีมากกว่า 1 ชุด ทำไงดี? ใช้สูตรนี้ได้เลย 🙂 “0;;0; ” คือเทคนิคดึงเฉพาะตัวเลข – ใช้หลักการของ Custom Number Format– โครงสร้างคือ บวก;ลบ;ศูนย์;text– มีช่องว่าง (space) ด้านหลังสุด เพื่อเปลี่ยน text เป็นช่องว่าง แล้วกำจัดด้วย TRIM (เพื่อเป็นช่องว่างคั่นกรณีมีตัวเลขมากกว่า 1 ชุด) สูตรนี้ใช้ได้กับ Excel 365 หรือ Excel 2021 เท่านั้น (เพราะใช้ฟังก์ชัน SEQUENCE) ป.ล. ผมได้เทคนิคนี้จากคุณ Wittaya Chainim ครับ 🙂

Function

สูตร Excel ดึงเฉพาะตัวเลข (RegEx)

อยากแยกตัวเลขออกจากข้อความ ทำไง?ใช้สูตรนี้ จบ! REGEXEXTRACT คือฟังก์ชันใหม่ใน Excel ใช้แยกสิ่งที่ต้องการออกจากข้อความ “[\d,]+” คือ RegEx Pattern ที่ใช้แยกตัวเลข โดยไม่สนใจ comma ( , )ไม่ต้องกังวลเรื่อง RegEx Pattern นะ ให้ ChatGPT ช่วยเขียนได้ 😊 อ้อ, ใส่เครื่องหมาย — นำหน้าฟังก์ชันเพื่อเปลี่ยน text ให้เป็น number อนึ่ง สูตรนี้ใช้ได้กับ Excel 365 Beta Channel เท่านั้นถ้าใครใช้ Excel 365 แต่ไม่ใช่ Beta Channel (Insider Program) อาจต้องรอสักระยะนะครับ 🙂

Function

สูตร Excel ดึงเฉพาะตัวเลข

อยากแยกตัวเลขออกจากข้อความ ทำไง?ใช้สูตรนี้ได้เลย 🙂 “0;;0;” คือเทคนิคการดึงเฉพาะตัวเลข โดยใช้หลักการของ Custom Number Format(โครงสร้างคือ บวก;ลบ;ศูนย์;text) อ้อ, สูตรนี้ใช้ได้กับ Excel 365 หรือ Excel 2021 เท่านั้น ป.ล. ผมได้เทคนิคนี้จากคุณ Wittaya Chainim ครับ 🙂