Excel Functions A to Z รวมสูตร 26 สูตร พร้อมวิธีใช้งาน และ ตัวอย่าง ให้เข้าใจได้ง่าย ๆ
9Expert ขอมอบของขวัญเป็นฟังก์ชัน Excel ให้กับทุกท่านที่สนใจสูตรคำนวณ Excel มีทั้งหมด 26 สูตร พร้อมวิธีใช้งาน และ ตัวอย่าง ให้เข้าใจได้ง่าย ๆ 26 สูตร
Microsoft Excel Function พร้อมวิธีใช้และตัวอย่าง
ฟังก์ชัน AVERAGE
ฟังก์ชัน Average ของ Excel หาค่าเฉลี่ยของข้อมูล เป็นสูตรยอดนิยมสูตรหนึ่งที่คนทำงานกับ Excel น่าจะต้องรู้จัก โดยจะทำการหาผลรวมของข้อมูล พร้อมหาร เป็นค่าเฉลี่ยให้อีกด้วย เช่น ทำการหาราคาเฉลี่ยสินค้า หาต้นทุนเฉลี่ย คะแนนเฉลี่ย เป็นต้นรูปแบบการใช้งาน
AVERAGE( number1, [number2],...)
ฟังก์ชัน BAHTTEXT
ฟังก์ชัน BAHTTEXT ของ Excel แปลงตัวเลขเป็นค่าทางการเงินไทย เพื่อแสดงในรูปแบบกี่บาท กี่สตางค์ เช่น =Bahttext(25.50) ก็จะได้ผลลัพธ์ว่า ยี่สิบห้าบาทห้าสิบสตางค์ เป็นต้น เหมาะกับการคำนวณเพื่อแสดงในใบเสนอราคา (Quotation) หรือ ใบแจ้งหนี้ (Invoice) หรือ ใบเสร็จรับเงิน (Receipt) เป็นต้น เป็นสูตรที่ Excel สร้างให้กับคนไทยโดยเฉพาะ โดยมีตำนานว่า เป็นสูตรที่สร้างให้กับแม่ค้าร้านอาหารในโรงอาหาร ของ Microsoft ใน Redmond เพื่อใช้ในการทำงานโดยเฉพาะ เพราะโปรแกรมเมอร์ที่นั่น ชอบอาหารไทยมาก ๆ และ แม่ค้าก็อัธยาศรัย เป็นที่รักกับคนของ Microsoft เป็นอย่างมาก ซึ่งหากจะแปลงเป็นภาษาอังกฤษ ใน Excel ยังไม่มีสูตรให้ทำงานนั้นโดยเฉพาะ จึงเป็นสูตรพิเศษมาก ๆ กับคนไทยเลยรูปแบบการใช้งาน
BAHTTEXT( number )
ฟังก์ชัน COUNTA
ฟังก์ชัน COUNTA ของ Excel ใช้นับจำนวน Cell ที่มีข้อมูล นับเป็นสูตรทางสถิติที่ใช้ในการนับข้อมูล โดยจะนับทุก Cell ที่มีข้อมูลไม่ว่าจะเป็นข้อมูลที่เป็นข้อความ ตัวเลข หรือวันที่ก็ตาม แต่จะไม่นับ Cell ที่ไม่มีค่าเท่านั้น ตัวอย่างการใช้งาน เช่น การนับจำนวนลูกค้า นับจำนวนสินค้า นับจำนวนพนักงาน เป็นต้น โดยความหมายของ A ที่ต่อท้ายนั้น คือ Alphabet ซึ่งก็คือการนับพยัญชนะด้วย นั่นเอง ซึ่งหากเป็นสูตร COUNT จะนับเฉพาะ Cell ที่เป็นตัวเลขเท่านั้นรูปแบบการใช้งาน
COUNTA( value1, [value2],… )
ฟังก์ชัน DATE
ฟังก์ชัน DATE แปลงเป็นวันที่ ซึ่งในหลายครั้งเราอาจจะมีข้อมูลที่แยกกัน เช่น มีวันที่ มีเดือน มีปี แต่อยู่กันคนละ Column เราสามารถทำการรวมค่าเหล่านั้นมาเป็นวันที่ได้ โดยการใช้ฟังก์ชัน DATE ได้ ดังนั้นเหมาะกับการจัดข้อมูลเพื่อให้พร้อมทำการคำนวณวันที่ ต่อไปได้รูปแบบการใช้งาน
DATE( year, month, day )
ฟังก์ชัน EOMONTH (End of Month)
ฟังก์ชัน EOMONTH (End of Month) หาวันสิ้นเดือนของวันที่ ๆ กำหนด เป็นสูตรที่มีประโยชน์มาก ๆ ในงานด้าน HR หรือ Payroll เช่น ใช้คำนวณหาค่า OT ในแต่ละเดือน ซึ่งจะช่วยให้เราทราบจำนวนวันในเดือนนั้น ๆ ว่ามี 28, 29, 30 หรือ 31 วัน โดยกำหนดเดือนถัดไป หรือก่อนหน้าวันที่เราอ้างอิงได้ เช่น =EOMONTH("01/01/2020",1) คือ วันสิ้นเดือนของเดือนถัดไป 1 เดือน ซึ่งก็จะเป็นวันที่ 29 ก.พ. 2020รูปแบบการใช้งาน
EOMONTH( start_date, months )
ฟังก์ชัน GETPIVOTDATA
ฟังก์ชัน GETPIVOTDATA ของ Excel ดึงข้อมูลจาก PivotTable โดยกำหนดเงื่อนไขที่ต้องการ เป็นสูตรการคำนวณที่คนทำ PivotTable ควรจะต้องรู้จัก โดยเป็นสูตรที่ใช้ดึงค่าจาก PivotTable โดยส่ง Parameter ที่ต้องการเพื่อ get ค่าที่ต้องการจาก PivotTable อีกที เพื่อที่แสดงค่าที่สัมพันธ์กับข้อมูลที่ต้องการ ตัวอย่างการใช้งาน เช่น การทำรายการที่ไม่ได้ต้องการ layout ของรายงานเหมือนกับ PivotTable แต่ต้องการ layout ตามที่กำหนดเอง ซึ่งเราจะพบได้บ่อย ๆ ในรายงานต่าง ๆ ขององค์กร / หน่วยงานรูปแบบการใช้งาน
GETPIVOTDATA(data_field, pivot_table,[field1, item1], [field2, item2]… )
ฟังก์ชัน HYPERLINK
ฟังก์ชัน HYPERLINK ของ Excel สร้างไฮเปอร์ลิงก์ไปยัง URL ที่ต้องการ ซึ่งสามารถกำหนดค่าการแสดงผลของ Hyperlinks ว่าจะแสดงข้อความอะไร และ จะให้ลิงก์ไปที่ใด เหมาะสำหรับการทำเอกสารลิงก์ไปยังเว็บไซต์ หรือไฟล์ต่าง ๆ เช่น คลิกเพื่อโหลดไฟล์ Catalog สินค้า หรือ โหลดไฟล์ pdf ได้ เป็นต้น ทำให้เราปรับแต่งลิงก์ให้ได้ตามรูปแบบที่ต้องการได้อีกด้วยรูปแบบการใช้งาน
HYPERLINK( link_location, [friendly_name] )
ฟังก์ชัน INDEX
ฟังก์ชัน INDEX ของ Excel ใช้หาค่าโดยระบุลำดับที่ต้องการ เป็นฟังก์ชันที่เราจะใช้อ้างอิง (Reference) ข้อมูล โดยกำหนดว่าช่วงข้อมูลที่เราต้องการ (array) พร้อมกับลำดับตัวเลข เพื่อดึงค่าที่เราต้องการ ตัวอย่างการใช้งาน เช่น การใช้ Index คู่กับ Match เพื่อทำการดึงข้อมูลแทน VLookup แต่จะได้ Performance ที่ดีกว่า การใช้ VLookup เป็นต้นรูปแบบการใช้งาน
INDEX( array, row_num, [col_num] )
ฟังก์ชัน LEN
ฟังก์ชัน LEN ของ Excel ใช้หาจำนวนตัวอักษรของข้อความ โดย LEN ย่อมาจาก Length ซึ่งก็คือความยาวของข้อความ มีประโยชน์ในการนำไปใช้เช็คความยาวของข้อมูล เช่น เช็คว่า รหัสพนักงาน กรอกข้อมูลครบตามถ้วนหรือไม่ มีใส่ขาด หรือ เกินหรือไม่ การนับว่าเลขประจำตัวประขาชนครบ 13 หลักหรือไม่ เป็นต้นรูปแบบการใช้งาน
LEN( text )
ฟังก์ชัน MID
ฟังก์ชัน MID ของ Excel ใช้ตัดข้อความ โดยระบุจุดเริ่มต้นและจำนวนตัวอักษร เป็นฟังก์ชันที่ใช้ในการแยกข้อมูล (Extract) เพื่อนำข้อมูลในส่วนตรงกลางออกมาโดยกำหนดจุดเริ่มต้นที่จะเริ่มดึงข้อมูล พร้อมจำนวนตัวอักษรที่ต้องการ เช่น MID("ABCD",2,1) จะได้ตัวอักษร B ออกมา เป็นต้นรูปแบบการใช้งาน
MID( text, start_num, num_chars )
ฟังก์ชัน OFFSET
ฟังก์ชัน OFFSET ของ Excel อ้างอิงเซลล์โดยกำหนดระยะ ซึ่งในฟังก์ชันนี้จะแสดงได้ทั้งเป็นตำแหน่ง Cell หรือ เป็น Range ข้อมูลได้ ตัวอย่างการนำไปใช้งาน เช่น การทำ Dynamic Dropdown List เป็นให้ได้เป็นช่วงข้อมูลที่ต้องการ เช่น =OFFSET(Customer!A2,0,0,CountA(Customers!A:A)-1,1) เพื่อนำไปใช้ในการ Dropdown List ในคำสั่ง Data Validation ของ Excel เพื่อให้ได้ List รายการที่อัตโนมัติตามข้อมูลที่เพิ่มขึ้น ซึ่งใน Sheet Customers จะมีรหัสลูกค้าที่ใส่ใน A2, A3 เป็นต้นไปรูปแบบการใช้งาน
OFFSET(reference, rows, cols, [height], [width])
ฟังก์ชัน RIGHT
ฟังก์ชัน RIGHT ของ Excel ใช้ ตัดตัวอักษรด้านขวามือตามจำนวนที่กำหนด เป็นฟังก์ชันในการตัด (Extract) ข้อมูลอีกตัวหนึ่ง ซึ่งไว้ตัดข้อความทางด้านขวามือ เช่น การ Extract ลำดับของใบเสร็จรับเงิน โดยสนใจ 4 ตัวท้าย เป็นต้น เช่น =RIGHT("IV-631001",4) ก็จะได้ผลลัพธ์ เป็น "1001" ซึ่งคือ 4 ตัวท้ายของรหัสสินค้า IV-631001 นั่นเองรูปแบบการใช้งาน
RIGHT( text, [num_chars] )
ฟังก์ชัน SUM
ฟังก์ชัน SUM ของ Excel หาค่าผลรวมของข้อมูล เป็นสูตรแรกที่คนใช้งาน Excel จะเริ่มรู้จัก โดยทำการรวมตัวเลขทุก ๆ ตัวในช่วงข้อมูลและตัวเลขที่เราสนใจ โดยหากจะทำการ SUM มี Trick ในการใช้งานคือ การใช้ Shortcut Key คือ กด Alt แล้วกด เครื่องหมายเท่ากับ ("=") ก็จะทำให้การหาผลรวมเราเร็วยิ่งขึ้นได้รูปแบบการใช้งาน
SUM( number1, [number2],… )
ฟังก์ชัน TEXTJOIN
ฟังก์ชัน TEXTJOIN ของ Excel ใช้สำหรับเชื่อมข้อความเข้าด้วยกัน โดยสามารถกำหนดตัวคั่น (delimiter) ได้อีกด้วย เช่น อยากนำเอาชื่อสินค้ามาต่อกัน โดยคั่นด้วยเครื่องหมายคอมม่า (,) ก็สามารถทำได้ และยังกำหนดว่าจะสนใจ Cell ที่เป็นค่าว่างหรือไม่ (ignore_empty) ได้อีกด้วยรูปแบบการใช้งาน
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
ฟังก์ชัน TRIM
ฟังก์ชัน TRIM ของ Excel ลบช่องว่างด้านหน้า ตรงกลางและหลังข้อความออก โดยหากมีช่องว่าง ข้างหน้า และ ข้างหลังจะถูกตัดออกทั้งหมด แต่หากเป็นช่วงว่างตรงกลางจะตัดให้เหลือช่องว่างเดียว เช่น =TRIM(" ABC DEF ") จะได้ผลลัพธ์ คือ "ABC DEF" นั่นเอง นับเป็นอีกฟังก์ชันที่จะช่วยในการ Clean ข้อมูลให้เราเป็นระเบียบยิ่งขึ้นโดยเฉพาะในกรณีที่ข้อมูลเป็นการนำเข้าด้วยการกรอกข้อมูลเองรูปแบบการใช้งาน
TRIM( text )
ฟังก์ชัน UPPER
ฟังก์ชัน UPPER ของ Excel แปลงข้อความเป็นตัวพิมพ์ใหญ่ สำหรับการ Clean ข้อมูลให้เป็นรูปแบบตัวพิมพ์ใหญ่เหมือนกันทั้งหมด นับเป็นฟังก์ชันที่ช่วยในการ Clean ข้อมูลให้เราเป็นระเบียบยิ่งขึ้นโดยเฉพาะในกรณีที่ข้อมูลเป็นการนำเข้าด้วยการกรอกข้อมูลเอง ใช้ได้เฉพาะกับข้อมูลที่เป็นภาษาอังกฤษเท่านั้นรูปแบบการใช้งาน
UPPER( text )
ฟังก์ชัน VLOOKUP
ฟังก์ชัน VLOOKUP ของ Excel สืบค้นข้อมูลโดยกำหนดค่าที่ใช้อ้างอิง สำหรับการค้นหาข้อมูลจาก 2 ตาราง เช่น ในตารางการขาย มีรหัสลูกค้า แต่ต้องการค้นหาว่ารหัสลูกค้า นั้น มีชื่อว่าอะไร ที่อยู่ ที่ไหน ติดต่อใคร ซึ่งจำเป็นต้องค้นหาอีกตารางหนึ่ง เป็นฟังก์ชันที่ไม่ควรพลาดสำหรับใครที่ต้องทำงานกับข้อมูลจำนวนมาก ๆ โดย V ย่อมาจาก Vertical ซึ่งเป็นการค้นหาตารางในแนวตั้ง แต่หากข้อมูลเป็นแนวนอนก็จะมี HLookup ที่จะช่วยได้
ดูรายละเอียดการใช้งานเพิ่มเติมได้ที่ https://www.9experttraining.com/articles/การใช้-vlookup-ใน-excel และ https://www.youtube.com/watch?v=H2ASi50yxww
รูปแบบการใช้งาน
VLOOKUP(lookup_value, table_array,col_index_num,[range_lookup])
ฟังก์ชัน WORKDAY.INTL
ฟังก์ชัน WORKDAY.INTL ของ Excel หาวันทำงานถัดไปโดยไม่นับวันหยุด สำหรับงานด้านบุคคล (HR) ที่ใช้คำนวณ Manday ในการทำงานของพนักงาน หรือ outsource ที่ทำงานว่าทำงานไปกี่วัน ไม่นับ วันเสาร์ วันอาทิตย์ หรือ วันหยุด ซึ่งกำหนดได้เอง โดย INTL ที่อยู่ท้ายสูตรย่อมาจาก International ซึ่งจะสามารถใช้หาวันทำงานได้หลากหลายแบบให้กับเรารูปแบบการใช้งาน
WORKDAY.INTL( start_date, days, [weekend], [holidays] )
ฟังก์ชัน XLOOKUP
ฟังก์ชัน XLOOKUP ของ Excel เป็นฟังก์ชันใหม่ใน Excel for Microsoft 365 เป็นฟังก์ชันที่สร้างมาเพื่อใช้แทน VLOOKUP, HLOOKUP ซึ่งสามารถสืบค้นข้อมูลโดยกำหนดค่าที่ใช้อ้างอิง สำหรับการค้นหาข้อมูลจาก 2 ตาราง เช่น ในตารางการขาย มีรหัสลูกค้า แต่ต้องการค้นหาว่ารหัสสินค้า นั้น มีชื่อว่าอะไร ราคาเท่าใด ต้นทุนเท่าใด ซึ่งจำเป็นต้องค้นหาอีกตารางหนึ่ง เป็นฟังก์ชันที่ไม่ควรพลาดสำหรับใครที่ต้องทำงานกับข้อมูลจำนวนมาก ๆ โดยค้นหาได้ทั้งตารางในแนวตั้ง (Vertical) และ ตารางแนวนอน (Horizontal)รูปแบบการใช้งาน
XLOOKUP (lookup, lookup_array, return_array,[if_not_found], [match_mode], [search_mode])
ฟังก์ชัน YEAR
ฟังก์ชัน YEAR ของ Excel หาค่าปี ค.ศ. จากวันที่ ๆ กำหนด เพื่อดึงเอาข้อมูลเฉพาะปี ไม่สนใจเดือน หรือวัน โดนย้ำว่าจะได้เป็นปี ค.ศ. ออกมา เพื่อนำไปใช้ในการจัดกลุ่ม หรือ Filter ข้อมูลต่อไป และหากในสูตรเจอกับค่า serial_number ก็ให้เข้าใจว่าคือ วันที่ นั่นเอง ซึ่งใน Excel นั้น จะเก็บวันที่เป็นตัวเลขประเภทหนึ่งที่จะนับต่อเนื่องกันโดยเพิ่มไปวันละ 1 เรื่อย ๆรูปแบบการใช้งาน
YEAR( serial_number )
ฟังก์ชัน Z.TEST
ฟังก์ชัน Z.TEST ของ Excel หาค่า One-tailed probability-value of a z-test ซึ่งจะเหมาะกับคนที่ต้องทำงานกับสถิติ (Statistic) นับเป็นฟังก์ชันเดียวใน Excel ที่มีคำว่า Z อยู่ใน สูตรคำนวณ ใครที่ต้องทำงานกับสถิติ น่าจะเข้าใจการนำไปประยุกต์ใช้งานกันรูปแบบการใช้งาน
Z.TEST( array, x, [sigma] )
แนะนำหลักสูตร Microsoft Excel ของ 9Expert Training
- Microsoft Excel Intermediate (2 Days)
- Microsoft Excel Advanced (3 Days)
- Microsoft Excel Powerful Functions (2 Days)
- Microsoft Excel Advanced for Financial (2 Days)
- Microsoft Excel Macro and VBA (3 Days)
- Microsoft Excel VBA Programming (3 Days)
- Microsoft Excel Advanced PivotTable and PivotChart (2 Days)
- Microsoft Excel Power BI Business Intelligence (1 Day)
- Microsoft Excel Power Query (2 Days)
อบรมแบบ Online ด้าน Microsoft Excel