12 สูตร Excel และ Google Sheets พื้นฐานที่คนทำงานต้องรู้ พร้อมตัวอย่างตาราง
ใครที่ทำงานกับตัวเลข รายชื่อ หรือรายการสินค้า น่าจะเคยเจอสถานการณ์ที่ต้องนั่งบวกเลขทีละช่อง นับว่ามีกี่รายการ หรือค้นหาข้อมูลจากอีกตารางหนึ่งด้วยตา เสียเวลาและพลาดง่ายมาก ทั้งที่โปรแกรมตารางคำนวณอย่าง Excel และ Google Sheets ทำงานเหล่านี้ให้ได้ในไม่กี่วินาทีด้วย “สูตร”
บทความนี้เรารวม 12 สูตรพื้นฐานที่คนทำงานควรรู้ เรียงจากง่ายไปยาก แต่ละสูตรมีหน้าที่ รูปแบบการเขียน ตัวอย่างตาราง และข้อควรระวัง ใช้ได้ทั้งใน Excel และ Google Sheets เพราะสูตรเหล่านี้เขียนเหมือนกันเป็นส่วนใหญ่
ถ้าเครื่องหรือเวอร์ชันของคุณเป็นภาษาไทย ชื่อสูตรยังเป็นภาษาอังกฤษเหมือนเดิม แต่ในบางการตั้งค่าตัวคั่นระหว่างค่าอาจเป็นเครื่องหมายอัฒภาค (;) แทนจุลภาค (,) ถ้าพิมพ์สูตรแล้วขึ้นข้อผิดพลาดให้ลองเปลี่ยนตัวคั่น
หลักการเขียนสูตรที่ต้องรู้ก่อน
- ขึ้นต้นด้วยเครื่องหมายเท่ากับ (=) ทุกสูตรต้องเริ่มด้วย = ไม่เช่นนั้นโปรแกรมจะมองเป็นข้อความธรรมดา
- ชื่อเซลล์ อ้างด้วยตัวอักษรของคอลัมน์ตามด้วยเลขแถว เช่น B2 คือคอลัมน์ B แถวที่ 2
- ช่วงเซลล์ ใช้เครื่องหมายทวิภาค (:) เช่น B2:B10 หมายถึงเซลล์ B2 ถึง B10 ทั้งหมด
- ข้อความในสูตร ต้องอยู่ในเครื่องหมายอัญประกาศคู่ เช่น "ผ่าน"
- ใช้การอ้างเซลล์แทนการพิมพ์ตัวเลข เมื่อข้อมูลเปลี่ยน ผลลัพธ์จะอัปเดตเอง
เพื่อให้เห็นภาพตรงกัน ตัวอย่างในบทความนี้ใช้ตารางยอดขายเล็กๆ ที่คอลัมน์ A คือชื่อสินค้า B คือหมวด C คือจำนวนชิ้น และ D คือยอดขายรวม
| A (สินค้า) | B (หมวด) | C (จำนวน) | D (ยอดขาย) |
|---|---|---|---|
| ปากกา | เครื่องเขียน | 50 | 1,000 |
| สมุด | เครื่องเขียน | 30 | 1,500 |
| แฟ้ม | เอกสาร | 20 | 800 |
| เมาส์ | อุปกรณ์ | 10 | 2,500 |
| ปากกา | เครื่องเขียน | 40 | 800 |
สมมติว่าข้อมูลเริ่มที่แถว 2 ถึงแถว 6 ตามตารางนี้ (แถว 1 คือหัวตาราง)
1. SUM รวมค่าตัวเลข
สูตรแรกที่ทุกคนต้องรู้ ใช้บวกตัวเลขในช่วงที่เลือก
| สูตร | ผลลัพธ์ | ความหมาย |
|---|---|---|
| =SUM(D2:D6) | 6,600 | รวมยอดขายทั้งหมด |
| =SUM(C2:C6) | 150 | รวมจำนวนชิ้น |
| =SUM(D2,D4,D6) | 2,600 | รวมเฉพาะเซลล์ที่ระบุ |
ถ้าสูตรรวมไม่ได้ ผลลัพธ์เป็นศูนย์ ให้ตรวจว่าตัวเลขในเซลล์ถูกเก็บเป็นข้อความหรือไม่ ซึ่งมักเกิดตอนนำข้อมูลมาวางจากที่อื่น สังเกตว่าตัวเลขชิดซ้ายในเซลล์ แสดงว่าเป็นข้อความ
2. AVERAGE หาค่าเฉลี่ย
ใช้หาค่าเฉลี่ยของตัวเลข เช่น ยอดขายเฉลี่ยต่อรายการ หรือคะแนนเฉลี่ย
| สูตร | ผลลัพธ์ | ความหมาย |
|---|---|---|
| =AVERAGE(D2:D6) | 1,320 | ยอดขายเฉลี่ยต่อรายการ |
| =AVERAGE(C2:C6) | 30 | จำนวนชิ้นเฉลี่ย |
สูตรนี้ข้ามเซลล์ที่ว่างและเซลล์ที่เป็นข้อความโดยอัตโนมัติ แต่ถ้าเซลล์เป็นเลข 0 จะถูกนับรวมด้วย ซึ่งทำให้ค่าเฉลี่ยลดลง ต้องดูให้แน่ใจว่าตั้งใจใส่ 0 จริงหรือไม่
3. MAX และ MIN หาค่าสูงสุดและต่ำสุด
ใช้ดูว่าค่าไหนสูงที่สุดหรือต่ำที่สุดในช่วง โดยไม่ต้องไล่หาด้วยตา
| สูตร | ผลลัพธ์ | ความหมาย |
|---|---|---|
| =MAX(D2:D6) | 2,500 | ยอดขายสูงสุด |
| =MIN(D2:D6) | 800 | ยอดขายต่ำสุด |
เหมาะกับการหาวันที่ขายดีที่สุด คะแนนสูงสุดในห้อง หรือราคาต่ำสุดในรายการ
4. COUNT และ COUNTA นับจำนวนช่อง
COUNT นับเฉพาะช่องที่เป็นตัวเลข ส่วน COUNTA นับทุกช่องที่ไม่ว่าง ไม่ว่าจะเป็นตัวเลขหรือข้อความ
| สูตร | ผลลัพธ์ | ความหมาย |
|---|---|---|
| =COUNT(C2:C6) | 5 | มีตัวเลข 5 ช่อง |
| =COUNTA(A2:A6) | 5 | มีรายการสินค้า 5 ช่อง |
ใช้ COUNTA เมื่อต้องการรู้ว่ามีกี่รายการในคอลัมน์ข้อความ เช่น จำนวนรายชื่อลูกค้า และใช้ COUNT เมื่อสนใจเฉพาะตัวเลข
5. IF ตัดสินใจตามเงื่อนไข
IF คือสูตรที่ทำให้ตารางฉลาดขึ้น ใช้ตรวจเงื่อนไขแล้วให้ผลต่างกัน รูปแบบคือ =IF(เงื่อนไข, ค่าถ้าจริง, ค่าถ้าเท็จ)
| สูตร | ผลลัพธ์ในแถว 2 | ความหมาย |
|---|---|---|
| =IF(D2>=1000,"ขายดี","ปกติ") | ขายดี | ถ้ายอดตั้งแต่ 1,000 ให้แสดง ขายดี ถ้าไม่ถึงให้แสดง ปกติ |
| =IF(C2<25,"ควรสั่งเพิ่ม","พอ") | พอ | ใช้เตือนสต็อกต่ำ |
ถ้าต้องการหลายเงื่อนไข สามารถซ้อน IF หรือใช้ AND กับ OR เช่น =IF(AND(C2>=30,D2>=1000),"ดีมาก","ธรรมดา") จะให้ผลว่า ดีมาก ต่อเมื่อทั้งสองเงื่อนไขเป็นจริง
6. COUNTIF นับตามเงื่อนไข
นับจำนวนช่องที่ตรงเงื่อนไข เช่น มีสินค้ากี่รายการในหมวดเครื่องเขียน
| สูตร | ผลลัพธ์ | ความหมาย |
|---|---|---|
| =COUNTIF(B2:B6,"เครื่องเขียน") | 3 | นับรายการที่หมวดเป็น เครื่องเขียน |
| =COUNTIF(D2:D6,">1000") | 2 | นับรายการที่ยอดขายมากกว่า 1,000 |
| =COUNTIF(A2:A6,"ปากกา") | 2 | ปากกาปรากฏกี่ครั้ง |
ถ้าต้องการนับตามหลายเงื่อนไขพร้อมกัน ให้ใช้ COUNTIFS ซึ่งใส่คู่ช่วงและเงื่อนไขได้หลายคู่
7. SUMIF รวมตามเงื่อนไข
คล้าย COUNTIF แต่เปลี่ยนจากนับเป็นรวมค่า รูปแบบคือ =SUMIF(ช่วงที่ตรวจเงื่อนไข, เงื่อนไข, ช่วงที่ต้องการรวม)
| สูตร | ผลลัพธ์ | ความหมาย |
|---|---|---|
| =SUMIF(B2:B6,"เครื่องเขียน",D2:D6) | 3,300 | รวมยอดขายเฉพาะหมวด เครื่องเขียน |
| =SUMIF(A2:A6,"ปากกา",C2:C6) | 90 | รวมจำนวนชิ้นของปากกาทุกแถว |
สูตรนี้ใช้ทำสรุปยอดตามหมวด ตามชื่อพนักงาน หรือตามเดือน ได้ทันทีโดยไม่ต้องกรองข้อมูลก่อน ถ้ามีหลายเงื่อนไข ให้ใช้ SUMIFS
8. VLOOKUP และ XLOOKUP ค้นหาข้อมูลจากตาราง
เมื่อมีข้อมูลสองตารางและต้องการดึงข้อมูลจากตารางหนึ่งมาไว้อีกตาราง เช่น ดึงราคาจากตารางสินค้าโดยใช้รหัสสินค้า สูตรกลุ่มนี้คือคำตอบ
VLOOKUP เป็นสูตรที่รู้จักกันมานาน รูปแบบ =VLOOKUP(ค่าที่ค้นหา, ตารางที่ค้น, เลขคอลัมน์ที่ต้องการ, 0) ตัวเลข 0 ตัวสุดท้ายหมายถึงให้ค้นหาแบบตรงตัวเท่านั้น
| ตัวอย่าง | สูตร | ผลลัพธ์ |
|---|---|---|
| หายอดขายของ เมาส์ | =VLOOKUP("เมาส์",A2:D6,4,0) | 2,500 |
| หาหมวดของ แฟ้ม | =VLOOKUP("แฟ้ม",A2:D6,2,0) | เอกสาร |
ข้อจำกัดของ VLOOKUP คือ ค่าที่ใช้ค้นหาต้องอยู่คอลัมน์ซ้ายสุดของตาราง และเมื่อเพิ่มหรือลบคอลัมน์ เลขคอลัมน์อาจเลื่อนผิดได้ ส่วนกรณีที่ค่าที่ค้นหามีซ้ำ เช่น ปากกาที่มีสองแถว สูตรจะคืนค่าแถวแรกที่เจอเท่านั้น
XLOOKUP เป็นสูตรรุ่นใหม่ที่แก้ข้อจำกัดเหล่านั้น รูปแบบคือ =XLOOKUP(ค่าที่ค้นหา, ช่วงที่ค้น, ช่วงที่ต้องการดึงค่ากลับ) ใช้ได้ใน Excel รุ่นใหม่และ Google Sheets
| ตัวอย่าง | สูตร | ผลลัพธ์ |
|---|---|---|
| หายอดขายของ เมาส์ | =XLOOKUP("เมาส์",A2:A6,D2:D6) | 2,500 |
| ถ้าไม่เจอให้แสดงข้อความ | =XLOOKUP("ดินสอ",A2:A6,D2:D6,"ไม่พบ") | ไม่พบ |
XLOOKUP อ่านง่ายกว่าเพราะระบุช่วงที่ค้นและช่วงที่ดึงค่าแยกกันชัดเจน ไม่ต้องนับคอลัมน์ และค้นหาจากซ้ายหรือขวาก็ได้ ถ้าเวอร์ชัน Excel ของคุณยังไม่มีสูตรนี้ ให้ใช้ VLOOKUP ไปก่อน
9. CONCAT และ TEXTJOIN ต่อข้อความ
ใช้รวมข้อความจากหลายเซลล์เข้าด้วยกัน เช่น รวมชื่อกับนามสกุล หรือสร้างรหัสจากหลายส่วน
| สูตร | ผลลัพธ์ | ความหมาย |
|---|---|---|
| =CONCAT(A2," - ",B2) | ปากกา - เครื่องเขียน | ต่อชื่อสินค้ากับหมวด |
| =A2&" ("&C2&" ชิ้น)" | ปากกา (50 ชิ้น) | ใช้เครื่องหมาย & ต่อข้อความได้เหมือนกัน |
| =TEXTJOIN(", ",TRUE,A2:A4) | ปากกา, สมุด, แฟ้ม | รวมหลายเซลล์ด้วยตัวคั่นที่กำหนด |
เครื่องหมาย & ใช้ได้ในทุกเวอร์ชัน ส่วน CONCAT และ TEXTJOIN ใช้ได้ใน Excel รุ่นใหม่และ Google Sheets ถ้าใช้ Excel รุ่นเก่า ให้ใช้ & หรือสูตร CONCATENATE
10. TODAY และ NOW แสดงวันที่และเวลาปัจจุบัน
TODAY คืนค่าวันที่วันนี้ ส่วน NOW คืนค่าวันที่พร้อมเวลา ทั้งสองจะอัปเดตเองทุกครั้งที่เปิดไฟล์หรือคำนวณใหม่
| สูตร | ความหมาย |
|---|---|
| =TODAY() | แสดงวันที่วันนี้ |
| =NOW() | แสดงวันที่และเวลาตอนนี้ |
| =B2-TODAY() | จำนวนวันที่เหลือก่อนถึงวันครบกำหนดที่อยู่ในเซลล์ B2 |
| =TODAY()-B2 | จำนวนวันที่ผ่านมาแล้วจากวันที่ใน B2 |
เหมาะกับการนับถอยหลังวันครบกำหนดส่งงาน อายุของสัญญา หรือสถานะค้างชำระ เมื่อใช้ร่วมกับ IF จะสร้างคอลัมน์แจ้งเตือนได้ เช่น =IF(B2 เมื่อคำนวณแล้วได้ทศนิยมยาว ใช้ ROUND ช่วยปัดให้อยู่ในรูปที่ต้องการ รูปแบบคือ =ROUND(ตัวเลข, จำนวนทศนิยม) ข้อแตกต่างจากการตั้งรูปแบบเซลล์ให้แสดงทศนิยมน้อยลงคือ ROUND เปลี่ยนค่าจริง ส่วนการจัดรูปแบบเซลล์เปลี่ยนแค่ที่แสดง ทำให้ผลรวมที่คำนวณต่อมีโอกาสคลาดเคลื่อนจากที่ตาเห็น บางครั้งสูตรให้ข้อผิดพลาด เช่น #DIV/0! ที่เกิดจากการหารด้วยศูนย์ หรือ #N/A ที่เกิดจากค้นหาไม่เจอ IFERROR ช่วยแทนที่ข้อผิดพลาดด้วยข้อความหรือค่าที่เรากำหนด ควรใช้อย่างระมัดระวัง เพราะ IFERROR ซ่อนข้อผิดพลาดทุกชนิด รวมถึงข้อผิดพลาดที่เกิดจากสูตรเขียนผิด ควรตรวจสูตรให้ถูกต้องก่อนแล้วค่อยครอบด้วย IFERROR เคล็ดลับเพิ่มเติมคือ เมื่อค้นหาไม่เจอทั้งที่ข้อมูลดูเหมือนกัน ให้ตรวจว่ามีช่องว่างเกินท้ายข้อความหรือไม่ ซึ่งแก้ได้ด้วยสูตร TRIM ที่ตัดช่องว่างส่วนเกินออก ถ้าไม่แน่ใจรูปแบบของสูตรไหน ให้พิมพ์ = ตามด้วยชื่อสูตร โปรแกรมจะแสดงคำแนะนำรูปแบบการเขียนและอธิบายแต่ละส่วนให้เห็นทันที Excel กับ Google Sheets ใช้สูตรเหมือนกันไหม? เหมือนกันเป็นส่วนใหญ่ โดยเฉพาะสูตรพื้นฐานในบทความนี้ แต่บางสูตรขั้นสูงอาจมีเฉพาะฝั่งใดฝั่งหนึ่ง ควรเรียนสูตรไหนก่อน? เริ่มที่ SUM, AVERAGE และ IF ซึ่งใช้บ่อยที่สุด จากนั้นค่อยเรียน COUNTIF, SUMIF และสูตรค้นหา ทำไมสูตรแสดงเป็นข้อความแทนผลลัพธ์? มักเกิดจากเซลล์ถูกตั้งรูปแบบเป็นข้อความ หรือพิมพ์ช่องว่างหน้าเครื่องหมายเท่ากับ ให้เปลี่ยนรูปแบบเซลล์เป็นทั่วไปแล้วกด Enter ใหม่ ต้องจำสูตรทั้งหมดไหม? ไม่ต้อง จำหลักการและชื่อสูตรไว้ที่เหลือใช้ตัวช่วยของโปรแกรมได้ 12 สูตรนี้ครอบคลุมงานส่วนใหญ่ที่คนทำงานเจอ ตั้งแต่รวม หาค่าเฉลี่ย นับ ตัดสินใจ ค้นหา ต่อข้อความ ไปจนถึงจัดการวันที่และข้อผิดพลาด เมื่อเข้าใจหลักการเขียนสูตรแล้ว คุณจะสร้างตารางที่คำนวณเองได้ ประหยัดเวลาและลดความผิดพลาดจากการคำนวณด้วยมือ ขั้นตอนถัดไปที่ทำได้เลยคือ เปิดไฟล์งานที่คุณทำอยู่ แล้วเลือกงานหนึ่งอย่างที่ยังคำนวณด้วยมือ เช่น ยอดรวมหรือการนับรายการ ลองเปลี่ยนมาใช้ SUM หรือ COUNTIF แล้วเพิ่มสูตรอื่นทีละตัว11. ROUND ปัดเศษ
สูตร ผลลัพธ์ ความหมาย =ROUND(1320.456,2) 1320.46 ปัดเป็นทศนิยม 2 ตำแหน่ง =ROUND(1320.456,0) 1320 ปัดเป็นจำนวนเต็ม =ROUND(1320,-2) 1300 ปัดเป็นหลักร้อย 12. IFERROR ซ่อนข้อผิดพลาดให้สวยงาม
สูตร ความหมาย =IFERROR(D2/C2,0) ถ้าหารไม่ได้ให้แสดง 0 แทน =IFERROR(VLOOKUP("ดินสอ",A2:D6,4,0),"ไม่พบข้อมูล") ถ้าค้นหาไม่เจอให้แสดง ไม่พบข้อมูล ข้อผิดพลาดที่พบบ่อยและวิธีแก้
อาการ สาเหตุที่พบบ่อย วิธีแก้ #NAME? พิมพ์ชื่อสูตรผิด หรือลืมใส่อัญประกาศครอบข้อความ ตรวจตัวสะกดและเครื่องหมาย " #VALUE! ใช้ข้อความในที่ที่ต้องเป็นตัวเลข ตรวจว่าเซลล์เป็นตัวเลขจริง #DIV/0! หารด้วยศูนย์หรือเซลล์ว่าง ตรวจตัวหาร หรือใช้ IFERROR #N/A ค้นหาไม่เจอ ตรวจตัวสะกด ช่องว่างเกิน และใช้แบบตรงตัว #REF! อ้างเซลล์ที่ถูกลบไปแล้ว แก้สูตรให้ชี้ไปยังเซลล์ที่ถูกต้อง วิธีฝึกให้ใช้สูตรคล่อง
คำถามที่พบบ่อย
สรุป
