Function

{Clip} BYROW ฟังก์ชันที่ทำให้เลิกก๊อปปี้สูตรใน Excel

คุณรู้จักฟังก์ชัน BYROW ไหมครับ? BYROW คือฟังก์ชันที่ทำให้คุณไม่ต้องก๊อปปี้สูตรใน Excelเพราะเจ๋งกว่าการก๊อปปี้สูตรแบบธรรมดา แถมยังมีข้อดีอีก 3 ข้อ เจ๋งกว่ายังไง ข้อดีคืออะไร คลิปนี้จะบอกคุณทุกเม็ด จากนี้ไปไม่ต้องก๊อปปี้สูตรแล้ว 😊

Function

หล่อมาก! สูตร Excel หาผลรวมสะสมแบบไดนามิก

อยากหาผลรวมสะสมใน Excel ทำไง? ถ้าเป็นเมื่อก่อน จะเขียนสูตรว่า ข้อเสียคือต้องลากสูตรลงมา ถ้าใช้ Excel 365 ให้ลองสูตรนี้ ข้อดีคือ ไม่ต้องลากสูตร สูตรจะไหลได้เองอัตโนมัติ ข้อเสียคือ ถ้ามีข้อมูลเพิ่ม (เช่น B7, B8, B9) ต้องปรับสูตรใหม่ งั้นลองปรับสูตรเป็นแบบนี้สิ 😊 ข้อดีคือถ้ามีข้อมูลเพิ่ม (เช่น B7, B8, B9) ผลลัพธ์จะเพิ่มได้เองแบบอัตโนมัติ(เพราะใช้ฟังก์ชัน TOCOL ปรับช่วงข้อมูลให้เป็นไดนามิก)(หรือจะใส่ช่วงข้อมูลเผื่อเป็น TOCOL(B2:B1000000,1) ก็ได้นะ 🙂) ข้อเสียคือใช้ได้ใน Excel 365 เท่านั้น ระวัง! ใช้สูตรนี้แล้วจะหล่อมาก 😁

Function

อย่างหล่อ! สูตร Excel หาผลรวมสะสม

อยากหาผลรวมสะสมตั้งแต่เซลล์ B2 ทำไง? ถ้าเป็นเมื่อก่อน จะเขียนสูตรว่า แล้วลากสูตรลงมา แต่ถ้าใช้ Excel 365 ให้ลองสูตรนี้ ข้อดีคือ ไม่ต้องลากสูตร สูตรจะไหลได้เองอัตโนมัติ (Dynamic Array Formula) 😊 SCAN เป็นฟังก์ชันที่มีในบางภาษา เช่น Rust, Scala, C++หลักการคือ running accumulation หรือสะสมค่าตามลำดับข้อมูล สำหรับ Excel ใช้ได้ใน Excel 365 เท่านั้น (เวอร์ชันอื่นใช้ไม่ได้) ระวัง! ใช้สูตรนี้แล้วหล่อมาก 😁

Function

สูตร 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 ครับ 🙂

Function

เทคนิคเปลี่ยน TRUE, FALSE เป็น 1,0

อยากเปลี่ยน TRUE, FALSE เป็น 1, 0 ทำไง? =–A2 จบ! หลักการคือเปลี่ยนให้เป็นตัวเลขใส่ — นำหน้า เพื่อเปลี่ยนให้เป็นตัวเลข (มองว่า ลบลบ เป็น บวก ก็ได้) จริง ๆ แล้วใช้เทคนิค A2+0 หรือ A2*1 ก็ได้เช่นกันแต่ — คำนวณเร็วกว่าเล็กน้อย แถมพิมพ์ง่ายกว่าด้วย 😊