1 · บทนี้เรียนอะไรออกบ่อย ต้องอ่านสูตรมา ผลลัพธ์ไป
งานธุรการในสถานีตำรวจใช้ Excel ทำบัญชีค่าปรับ สถิติคดี และรายชื่อกำลังพล ข้อสอบจึงชอบยกตารางเล็ก ๆ มาให้ (จากชุดข้อสอบเสมือนจริงที่เรารวบรวม (การประเมินของเรา)) แล้วถามว่าพิมพ์สูตรนี้แล้วได้อะไร หรืออยากได้ผลแบบนี้ต้องใช้ฟังก์ชันใด
บทนี้ใช้ตารางค่าปรับตัวอย่างตารางเดียวตลอดทั้งบท ทุกสูตรคิดผลให้ดูพร้อมวิธีคิด ลองคิดเองก่อนอ่านคำตอบ จะจำได้แม่นกว่าการท่องชื่อฟังก์ชัน ส่วน Word และ PowerPoint อยู่ในบทก่อนหน้า
ความรู้ทั่วไปด้านคอมพิวเตอร์ เนื้อหาทั้งบทเป็นความรู้ทั่วไปด้านคอมพิวเตอร์ ไม่มีระเบียบหรือกฎหมายฉบับใดกำหนดไว้ จึงไม่มีเลขหน้าอ้างอิง สูตรและคีย์ลัดยึด Microsoft 365 และ Excel 2016 ขึ้นไปบน Windows ใช้เครื่องหมายจุลภาค (,) คั่นค่าในฟังก์ชัน ตัวเลขในตารางตัวอย่างเป็นตัวเลขสมมติ
ข้อสอบถามแบบนี้ จากชุดข้อสอบเสมือนจริงที่เรารวบรวม (การประเมินของเรา) แบบที่พบบ่อยมีสามแบบ คือ ให้ข้อมูลในเซลล์แล้วถามผลของสูตร เช่น COUNTA MEDIAN ROUND MID หรือ IF ซ้อน AND ให้สถานการณ์แล้วถามว่าใช้ฟังก์ชันใด เช่น บวกเฉพาะแถวที่ตรงเงื่อนไขต้องใช้ SUMIF และให้ค่าผิดพลาดมาแล้วถามสาเหตุ เช่น #NAME? หรือ #REF! ตัวเลือกผิดมักเป็นผลที่ได้เมื่อคิดผิดลำดับหรือนับผิดชนิดข้อมูล
2 · พื้นฐานสูตรออกบ่อย ต้องอ่านเซลล์ เครื่องหมาย $ และลำดับการคำนวณ
ไฟล์ Excel หนึ่งไฟล์เรียกว่าสมุดงาน (Workbook) ข้างในมีหลายแผ่นงาน (Worksheet) แต่ละแผ่นแบ่งเป็นคอลัมน์ชื่อตัวอักษร (A, B, C …) และแถวชื่อตัวเลข (1, 2, 3 …) ช่องที่คอลัมน์ตัดกับแถวคือเซลล์ (Cell) เรียกชื่อด้วยคอลัมน์ก่อนแล้วตามด้วยแถว เช่น C4 ส่วน C2:C7 คือช่วง (Range) ตั้งแต่ C2 ถึง C7 รวม 6 เซลล์
สูตรต้องขึ้นต้นด้วยเครื่องหมาย = เสมอ ในเซลล์จะเห็นผลลัพธ์ ส่วนตัวสูตรดูได้ที่แถบสูตร (Formula Bar) ด้านบน ถ้าลืม = Excel จะถือว่าเป็นข้อความธรรมดา ไม่คำนวณ
การอ้างอิงเซลล์สามแบบ เมื่อคัดลอกสูตร
| เขียนว่า | ชื่อเรียก | เมื่อคัดลอกไปที่อื่น |
|---|
| A1 | สัมพัทธ์ (Relative) | เลื่อนทั้งคอลัมน์และแถว |
| $A$1 | สัมบูรณ์ (Absolute) | ไม่เลื่อนเลย |
| $A1 | ผสม (Mixed) | ล็อกคอลัมน์ แถวเลื่อนได้ |
| A$1 | ผสม (Mixed) | ล็อกแถว คอลัมน์เลื่อนได้ |
วิธีจำ: $ อยู่หน้าอะไร ล็อกสิ่งนั้น $ หน้าตัวอักษรล็อกคอลัมน์ $ หน้าตัวเลขล็อกแถว ขณะแก้สูตรใน Excel บน Windows กด F4 เพื่อสลับวนเป็น A1 → $A$1 → A$1 → $A1 → A1 (โน้ตบุ๊กบางรุ่นต้องกด Fn + F4)
ทำไมต้องมี $: ตัวอย่างร้อยละของยอดรวม จำนวนคดีของสามสถานีอยู่ใน B2:B4 คือ 40, 25 และ 35 เซลล์ B5 มี =SUM(B2:B4) ได้ 100 ถ้าเขียน C2 เป็น =B2/$B$5 จะได้ 40 ÷ 100 = 0.4 (จัดรูปแบบเป็นร้อยละได้ 40%) คัดลอกลงไป C3 ได้ =B3/$B$5 = 25 ÷ 100 = 25% และ C4 ได้ 35% แต่ถ้าลืม $ เขียน =B2/B5 พอคัดลอกลง C3 สูตรจะเลื่อนเป็น =B3/B6 ซึ่ง B6 ว่าง ผลจึงเป็น #DIV/0! (หารด้วยเซลล์ว่าง)
ลำดับการคำนวณของ Excel (คิดจากบนลงล่าง)
| ลำดับ | เครื่องหมาย | ตัวอย่าง → ผล |
|---|
| 1 | วงเล็บ ( ) | =(2+3)*4 → 20 |
| 2 | ลบหน้าตัวเลข (−) | =-3^2 → 9 |
| 3 | ร้อยละ % | =200*10% → 20 |
| 4 | ยกกำลัง ^ | =2*3^2 → 18 |
| 5 | คูณ * หาร / | =20/4*2 → 10 |
| 6 | บวก + ลบ − | =2+3*4 → 14 |
| 7 | ต่อข้อความ & | ="สภ."&"เมือง" → สภ.เมือง |
| 8 | เปรียบเทียบ = < > <= >= <> | =5>3 → TRUE |
เครื่องหมายระดับเดียวกันคิดจากซ้ายไปขวา =20/4*2 จึงคิด 20 ÷ 4 = 5 ก่อน แล้ว 5 × 2 = 10 ไม่ใช่ 20 ÷ 8 = 2.5 ข้อควรระวัง: Excel คิดเครื่องหมายลบหน้าตัวเลขก่อนยกกำลัง =-3^2 จึงเป็น (−3)² = 9 ต่างจากคณิตศาสตร์ในโรงเรียนที่ได้ −9 ส่วน =2*3^2 คิด 3² = 9 ก่อน แล้ว 2 × 9 = 18
เซลล์ C2 มีสูตร =$A1+B$1 ถ้าคัดลอกไปวางที่ D3 สูตรใน D3 จะเป็นอะไร
คิด 1นับระยะก่อน: จาก C2 ไป D3 คือเลื่อนไปทางขวา 1 คอลัมน์ (C → D) และลงล่าง 1 แถว (2 → 3)
คิด 2$A1 ล็อกคอลัมน์ไว้ที่ A แต่แถวเลื่อนได้ แถว 1 บวก 1 เป็นแถว 2 จึงได้ $A2
คิด 3B$1 ล็อกแถวไว้ที่ 1 แต่คอลัมน์เลื่อนได้ B เลื่อนไป 1 คอลัมน์เป็น C จึงได้ C$1
คำตอบ: =$A2+C$1
กับดัก: ตัวเลือกที่ชอบหลอกคือ “=$B2+C$2” ซึ่งเลื่อนส่วนที่ล็อกไว้ด้วย และ “=$A1+B$1” ซึ่งคิดว่ามี $ แล้วไม่เลื่อนเลย ทั้งที่แต่ละตัวล็อกแค่ครึ่งเดียว ให้ดูทีละตัวว่า $ อยู่หน้าคอลัมน์หรือหน้าแถว
สูตร =10+20/5*2-3^2 ได้ผลเท่าไร
คิด 1ยกกำลังก่อน: 3^2 = 9
คิด 2คูณหารจากซ้ายไปขวา: 20/5 = 4 แล้ว 4*2 = 8
คิด 3บวกลบจากซ้ายไปขวา: 10 + 8 = 18 แล้ว 18 − 9 = 9
คำตอบ: 9
กับดัก: ถ้าคิดจากซ้ายไปขวาตรง ๆ แบบเครื่องคิดเลขธรรมดา จะได้ (10 + 20) ÷ 5 × 2 = 12 แล้ว 12 − 3 = 9 ยกกำลัง 2 = 81 ข้อสอบมักใส่ผลจากการคิดผิดลำดับแบบนี้ไว้เป็นตัวเลือก (จากชุดข้อสอบเสมือนจริงที่เรารวบรวม (การประเมินของเรา))
3 · ฟังก์ชันที่ออกบ่อยออกบ่อย ต้องอ่านคิดผลจากตารางค่าปรับตารางเดียว
ฟังก์ชันในส่วนนี้คัดจากที่พบบ่อยจากชุดข้อสอบเสมือนจริงที่เรารวบรวม (การประเมินของเรา) ทุกฟังก์ชันในส่วนนี้ใช้ตารางด้านล่าง แถว 1 เป็นหัวตาราง ข้อมูลอยู่แถว 2 ถึง 7 คอลัมน์ A เป็นรหัสใบสั่ง B เป็นข้อหา C เป็นค่าปรับ (บาท) สังเกตว่าC6 เป็นข้อความ “รอตรวจ” ไม่ใช่ตัวเลข จุดนี้ทำให้ผลของหลายฟังก์ชันต่างกัน
ตารางค่าปรับตัวอย่าง (ตัวเลขสมมติ) แถว 2–7
| A รหัส | B ข้อหา | C ค่าปรับ |
|---|
| TR-001 | ขับเร็ว | 1000 |
| TR-002 | ไม่สวมหมวก | 400 |
| TR-003 | ขับเร็ว | 1500 |
| TR-004 | ฝ่าไฟแดง | 1000 |
| TR-005 | ไม่สวมหมวก | รอตรวจ |
| TR-006 | ขับเร็ว | 2000 |
ตัวเลขใน C2:C7 มีห้าค่า คือ 1000, 400, 1500, 1000 และ 2000 รวมกันได้ 5,900 ส่วน C6 เป็นข้อความ ฟังก์ชันคำนวณส่วนใหญ่จะข้ามเซลล์ข้อความไป
แตะดูสูตรแต่ละตัว พร้อมผลที่ได้จากตารางนี้
คิดคำนวณตัวเลขข้ามเซลล์ข้อความและเซลล์ว่าง
นับนับและรวมแบบมีเงื่อนไขCOUNT นับตัวเลข COUNTA นับเซลล์ไม่ว่าง
IFให้ Excel ตัดสินIF เลือกคำตอบ AND OR รวมเงื่อนไข
ตัดตัดข้อความนับตัวอักษรรวมขีดและช่องว่าง
หาค้นหาในตารางหาในคอลัมน์แรก แล้วดึงคอลัมน์อื่น
คำนวณตัวเลขSUM · =SUM(C2:C7) ได้ 5900บวกทุกตัวเลขในช่วง 1000 + 400 + 1500 + 1000 + 2000 = 5,900 ข้อความ “รอตรวจ” ถูกข้ามไป กับดัก: ถ้าเขียนบวกทีละเซลล์ =C2+C3+C4+C5+C6+C7 จะได้ #VALUE! เพราะเครื่องหมาย + เอาข้อความมาบวกไม่ได้
จากตารางค่าปรับ หัวหน้าถามสามคำถาม: มีใบสั่งกี่ใบ มีกี่ใบที่บันทึกค่าปรับเป็นตัวเลขแล้ว และค่าปรับเฉลี่ยของใบที่บันทึกแล้วเท่าไร ต้องใช้สูตรใดและได้ผลเท่าไร
คิด 1“มีใบสั่งกี่ใบ” คือนับทุกแถวที่มีข้อมูล ใช้ =COUNTA(C2:C7) หรือ =COUNTA(A2:A7) ได้ 6
คิด 2“บันทึกเป็นตัวเลขแล้วกี่ใบ” คือนับเฉพาะตัวเลข ใช้ =COUNT(C2:C7) ได้ 5 เพราะ C6 เป็นข้อความ
คิด 3“เฉลี่ยของใบที่บันทึกแล้ว” ใช้ =AVERAGE(C2:C7) Excel หารด้วยจำนวนตัวเลขเอง 5,900 ÷ 5 = 1,180
คำตอบ: COUNTA ได้ 6 · COUNT ได้ 5 · AVERAGE ได้ 1,180
กับดัก: ข้อสอบชอบใส่ “5,900 ÷ 6 ≈ 983.33” ไว้หลอก (จากชุดข้อสอบเสมือนจริงที่เรารวบรวม (การประเมินของเรา)) ซึ่งเป็นการหารด้วยจำนวนแถวทั้งหมด AVERAGE ไม่นับเซลล์ข้อความและเซลล์ว่าง แต่นับเลข 0 อีกคู่ที่สลับกันบ่อยคือ COUNTIF (นับ) กับ SUMIF (บวก): ถ้าโจทย์ถามเป็นเงิน ต้องเป็น SUMIF
ผู้สมัครคนหนึ่งได้คะแนนข้อเขียน (G2) 52 และพลศึกษา (H2) 70 สูตร =IF(AND(G2>=60,H2>=60),"ผ่าน",IF(G2>=50,"สอบแก้","ไม่ผ่าน")) ให้ผลอะไร
คิด 1คิด IF ตัวนอกก่อน: AND(G2>=60,H2>=60) คือ 52 ≥ 60 เท็จ และ 70 ≥ 60 จริง AND ต้องจริงทุกข้อ จึงได้ FALSE
คิด 2เมื่อเงื่อนไขเท็จ Excel ไปทำส่วน “ค่าถ้าเท็จ” ซึ่งเป็น IF ตัวที่สอง
คิด 3IF ตัวที่สองถาม G2>=50 คือ 52 ≥ 50 จริง จึงได้ “สอบแก้”
คำตอบ: สอบแก้
กับดัก: ถ้าอ่าน AND เป็น OR จะเห็นว่าพลศึกษาผ่านแล้วตอบ “ผ่าน” ทันที IF ซ้อนต้องคิดจากตัวนอกเข้าไปตัวใน และหยุดทันทีที่เจอเงื่อนไขจริง
4 · ค่าผิดพลาดออกบ่อย ต้องอ่านอ่านรหัส # ให้ออกว่าผิดตรงไหน
เมื่อสูตรคำนวณไม่ได้ Excel จะแสดงรหัสที่ขึ้นต้นด้วย # แทนผลลัพธ์ แต่ละรหัสบอกสาเหตุคนละแบบ ข้อสอบถามแทบทุกชุดว่า “รหัสนี้เกิดจากอะไร” (จากชุดข้อสอบเสมือนจริงที่เรารวบรวม (การประเมินของเรา)) ให้จำคู่รหัสกับสาเหตุจากตารางนี้
ค่าผิดพลาดที่ต้องรู้
| เห็น | สาเหตุ | ตัวอย่างจากตาราง |
|---|
| #DIV/0! | หารด้วยศูนย์หรือหารด้วยเซลล์ว่าง | =C2/H9 เมื่อ H9 ว่าง |
| #VALUE! | ชนิดข้อมูลผิด เช่น เอาข้อความไปคำนวณด้วยเครื่องหมาย | =C6+100 (C6 คือ รอตรวจ) |
| #REF! | อ้างเซลล์ที่ไม่มีอยู่แล้ว เช่น ลบแถวหรือคอลัมน์ที่สูตรอ้าง | VLOOKUP ขอคอลัมน์ 4 ในช่วง A2:C7 |
| #NAME? | Excel ไม่รู้จักชื่อ เช่น พิมพ์ชื่อฟังก์ชันผิด หรือลืม "…" ครอบข้อความ | =SUMM(C2:C7) |
| #N/A | หาค่าที่ค้นไม่พบ | =VLOOKUP("TR-010",A2:C7,2,FALSE) |
| #### | คอลัมน์แคบเกินกว่าจะแสดงตัวเลขหรือวันที่ทั้งหมด | ขยายคอลัมน์แล้วหายไป |
#### ไม่ใช่สูตรผิด ค่าในเซลล์ยังถูกต้อง แค่แสดงไม่พอ ขยายความกว้างคอลัมน์ก็เห็นตัวเลข (วันที่หรือเวลาที่ติดลบก็แสดงเป็น #### ได้) วิธีจำ: DIV มาจากการหาร (Divide) REF มาจากการอ้างอิง (Reference) N/A มาจากไม่มีค่าให้ (Not Available)
เจ้าหน้าที่สองคนรวมค่าปรับในตารางเดียวกัน คนแรกใช้ =SUM(C2:C7) ได้ 5900 คนที่สองใช้ =C2+C3+C4+C5+C6+C7 ได้ #VALUE! ทำไมผลต่างกัน
คิด 1สองสูตรอ้างเซลล์ชุดเดียวกัน ต่างกันแค่วิธีบวก
คิด 2ฟังก์ชัน SUM ถูกออกแบบให้ข้ามเซลล์ข้อความในช่วง จึงบวกเฉพาะตัวเลขห้าตัวได้ 5,900
คิด 3เครื่องหมาย + ต้องเอาค่าทุกตัวมาบวกจริง เมื่อเจอ C6 ซึ่งเป็นข้อความ “รอตรวจ” จึงคำนวณไม่ได้และแสดง #VALUE!
คำตอบ: เพราะ C6 เป็นข้อความ SUM ข้ามข้อความได้ แต่เครื่องหมาย + ข้ามไม่ได้ จึงเกิด #VALUE!
กับดัก: ตัวเลือก “#NAME?” ดูใกล้เคียง แต่ #NAME? เกิดเมื่อ Excel ไม่รู้จักชื่อในสูตร ส่วนที่นี่ชื่อเซลล์ถูกหมด ปัญหาอยู่ที่ชนิดข้อมูล จึงเป็น #VALUE! ข้อสอบมักสลับสาเหตุของ #VALUE! #NAME? และ #REF! ให้เลือก (จากชุดข้อสอบเสมือนจริงที่เรารวบรวม (การประเมินของเรา))
จุดที่คนพลาดบ่อย และเหตุผลลืม $ เมื่อทุกแถวต้องอ้างเซลล์เดียวกัน · เกิดเพราะสูตรแถวแรกถูกต้อง จึงไม่ทันเห็นว่าแถวถัดไปเลื่อน วิธีแก้: ถามว่าเซลล์ไหนต้องคงที่ทุกแถว เช่น ยอดรวมหรืออัตรา แล้วใส่ $ ให้เซลล์นั้น เช่น =B2/$B$5
สลับ COUNT กับ COUNTA และหาร AVERAGE ผิด · เกิดเพราะชื่อคล้ายกัน วิธีแก้: COUNT นับตัวเลข COUNTA นับเซลล์ไม่ว่าง ส่วน AVERAGE หารด้วยจำนวนตัวเลขเท่านั้น ข้ามข้อความและเซลล์ว่าง แต่นับเลข 0
คิดสูตรจากซ้ายไปขวาโดยไม่ดูลำดับ · เกิดเพราะชินกับเครื่องคิดเลขธรรมดา วิธีแก้: วงเล็บ → ยกกำลัง → คูณหาร → บวกลบ ระดับเดียวกันจึงค่อยคิดซ้ายไปขวา และจำว่า =-3^2 ใน Excel ได้ 9
คิดว่า #### คือสูตรผิด · เกิดเพราะเห็นเครื่องหมาย # เหมือนรหัสผิดพลาดอื่น วิธีแก้: #### แปลว่าคอลัมน์แคบเกินไป ขยายคอลัมน์แล้วเห็นค่าเดิม ส่วนรหัสผิดพลาดจริงจะมีคำตามหลัง เช่น #DIV/0! หรือ #N/A
เช็กความเข้าใจ
ตอบด้วยเหตุผล ไม่ใช่ความจำ
เซลล์ D3 มีสูตร =B3*$F$2+C$1 ถ้าคัดลอกไปวางที่ E6 สูตรใน E6 จะเป็นอะไร
เซลล์ B2:B7 บันทึกเบี้ยเลี้ยงดังนี้ 300, (ว่าง), งดเบิก, 0, 700, 500 สูตร =AVERAGE(B2:B7) ได้ผลเท่าไร
ตารางรายชื่อกำลังพลอยู่ในช่วง A2:D80 ซึ่งมีสี่คอลัมน์ H2 มีรหัสที่อยู่ในตาราง เจ้าหน้าที่พิมพ์ =VLOOKUP(H2,A2:D80,5,FALSE) จะเห็นผลอะไร
K2 = 45 และ L2 = 80 สูตร =IF(OR(K2>=50,L2>=90),"ดี",IF(AND(K2>=40,L2>=60),"พอใช้","ปรับปรุง")) ให้ผลอะไร
ลองอธิบายด้วยคำพูดตัวเองอธิบายให้เพื่อนฟังใน 3 ประโยคว่า COUNT COUNTA และ COUNTIF ต่างกันอย่างไร โดยใช้ช่วง C2:C7 ของตารางค่าปรับ (1000, 400, 1500, 1000, รอตรวจ, 2000)