แก้ไขปัญหาสูตร Excel ไม่คำนวณใหม่: 6 วิธีแก้ที่พิสูจน์แล้ว

นี่คือภาพรวมวิดีโอของบทความนี้

เพื่อสาธิตเหตุผลต่างๆ ที่ทำให้สูตร Excel อาจไม่คำนวณใหม่โดยอัตโนมัติหรือไม่ถูกต้อง และวิธีแก้ไข เราจะใช้ชุดข้อมูลต่อไปนี้ที่มีรายการขายสำหรับเดือนมกราคม กุมภาพันธ์ และ มีนาคม ชุดข้อมูลของเรามี 5 คอลัมน์: สินค้า, ยอดขาย ใน มกราคม, กุมภาพันธ์ และ มีนาคม, และ ยอดขายรวม

สูตร Excel ต่างๆ ไม่ได้คำนวณโดยอัตโนมัติในชุดข้อมูลของเรา มาแก้ไขปัญหาเหล่านี้

หมายเหตุ: ในบทช่วยสอนนี้เราใช้ Microsoft Office 365 ตำแหน่งของบางคำสั่งอาจแตกต่างเล็กน้อยในเวอร์ชัน Excel ที่ต่างกัน

เหตุผลที่ 1 – ตัวเลือกการคำนวณไม่เหมาะสม

หากฟีเจอร์ Calculation Options ตั้งเป็น Manual สูตร Excel จะไม่คำนวณโดยอัตโนมัติ

วิธีแก้ – ตั้งค่า Calculation Options เป็น Automatic

ขั้นตอน:

• คลิกแท็บ Formulas ใน Ribbon
• คลิกลูกศร ดรอปดาวน์ ภายใต้ Calculation Options ในกลุ่ม Calculation
• เลือก Automatic
• สูตรตอนนี้อัปเดตโดยอัตโนมัติ

ผลลัพธ์เดียวกันสามารถทำได้โดยใช้กล่องโต้ตอบ Excel Options

ขั้นตอน:

• คลิกแท็บ File ใน Ribbon
• คลิก Options

• กล่องโต้ตอบ Excel Options ปรากฏ
• คลิกที่แท็บ Formula
• ตั้งค่า Workbook Calculation เป็น Automatic
• คลิก OK

อ่านเพิ่มเติม: [Solved]: Excel Formulas Not Updating Until Save

เหตุผลที่ 2 – ค่าเซลล์จัดรูปแบบเป็นข้อความ

อีกเหตุผลทั่วไปที่ทำให้สูตร Excel ไม่คำนวณโดยอัตโนมัติ คือการจัดรูปแบบเซลล์ที่มีสูตรเป็นข้อความโดยไม่ได้ตั้งใจ

วิธีแก้ – แปลงค่าข้อความเป็นรูปแบบตัวเลข

ขั้นตอน:

• เลือกเซลล์ E6 และกด Ctrl + 1 กล่องโต้ตอบ Format Cells จะโผล่ขึ้นมา
• เลือกแท็บ Number
• เลือก Number จากรายการดรอปดาวน์ Category
• ติ๊กถูกตัวเลือก Use 1000 Separator(,)
• คลิก OK

สูตร Excel ตอนนี้คำนวณโดยอัตโนมัติ

หมายเหตุ: รูปแบบ Number หรือ General สามารถใช้แทน Text ได้

อ่านเพิ่มเติม: [Fixed!] Formula Not Working in Excel and Showing as Text

เหตุผลที่ 3 – มีออสโทรฟี (Apostrophes)

ในชุดข้อมูลของเรามีออสโทรฟีค้างอยู่ในหลายเซลล์

วิธีแก้ – ลบออสโทรฟี

เครื่องหมายออสโทรฟีหรือช่องว่างนำหน้าจะต้องลบทีละอัน

ขั้นตอน:

• เลือกเซลล์ที่มีเครื่องหมายออสโทรฟีค้าง ที่นี่เริ่มจากเซลล์ C8 แม้เครื่องหมายจะไม่แสดงในเซลล์ แต่ชัดเจนจากการสังเกตการจัดเรียงข้อมูลว่ารูปแบบไม่ถูกต้อง

• คลิกที่ Formula Bar
• กดปุ่ม Delete เพื่อลบเครื่องหมาย ออสโทรฟี ด้วยตนเอง
• กด Enter

• ใช้กระบวนการเดียวกันลบออสโทรฟีที่ค้างอื่นๆ จากเซลล์ที่มีอยู่

สูตรคำนวณโดยอัตโนมัติแล้ว

เหตุผลที่ 4 – ช่องว่างนำหน้าหรือท้าย

ในชุดข้อมูลของเรามีช่องว่างนำหน้าหรือท้ายหลายอย่างที่ป้องกันไม่ให้สูตร Excel คำนวณโดยอัตโนมัติ

วิธีแก้ – ลบช่องว่างนำหน้าหรือท้าย

ขั้นตอน:

• เลือกเซลล์ที่มีช่องว่างนำหน้าหรือท้ายค้าง
• คลิกที่ Formula Bar
• กด Backspace เพื่อลบช่องว่างนำหน้าหรือท้ายด้วยตนเอง
• กด Enter
• เช่นเดียวกัน ลบช่องว่างนำหน้าหรือท้ายที่ค้างอื่นๆ จากเซลล์ทั้งหมดที่มีอยู่

เหตุผลที่ 5 – การอ้างอิงเซลล์ผิด

ข้อผิดพลาดทั่วไปที่ทำให้สูตรไม่คำนวณตามที่คาดหวัง คือการใช้การอ้างอิงเซลล์แบบสัมบูรณ์ผิดพลาด

ตัวอย่าง:

• ป้อน =B5 ลงในเซลล์และ AutoFill ลงไป จะได้ =B6 ถูกคัดลอกลงไป
• ป้อน =$B$5 ลงในเซลล์และ AutoFill ลงไป จะได้ =$B$5 ถูกคัดลอกลงไป

ในภาพหน้าจอข้างต้น สูตรที่ใช้อ้างอิงแบบสัมบูรณ์ทำให้เกิดผลลัพธ์ผิดในเซลล์ D17 และ E17

วิธีแก้ – ใช้การอ้างอิงเซลล์ที่เหมาะสม

เพียงแค่ลบ สัญลักษณ์ดอลลาร์ ($) ออกจากสูตร Excel ที่เป็นตัวทำให้เกิดปัญหา

ขั้นตอน:

• เลือกเซลล์ C17 และลบ สัญลักษณ์ดอลลาร์ ($) ออกจากสูตร
• AutoFill สูตรจนถึงคอลัมน์ E

หมายเหตุ – การแปลงทวิณุเป็นทศนิยม

คอมพิวเตอร์ใช้ระบบทวิณุ ซึ่งประกอบด้วยการผสมผสานของ 1 และ 0 เพื่อเก็บตัวเลข แต่การเข้าใจของเราเกี่ยวกับตัวเลขอิงตามระบบทศนิยม ซึ่งทำงานบนฐาน 10 ดังนั้น Excel จึงต้องแปลงตัวเลขระหว่างรูปแบบทวิณุและทศนิยม บางครั้งทำให้เกิดความแตกต่างที่ไม่คาดคิด

ความแม่นยำสูงสุดของการคำนวณ Excel คือ 15 หลักสำคัญ ดังนั้น ผลลัพธ์จากการแปลงจากทวิณุเป็นทศนิยม อาจมีข้อผิดพลาดในการปัดเศษเล็กน้อย ด้วยบางอาร์กิวเมนต์ตรรกะ สามารถสร้างผลลัพธ์ที่ไม่คาดคิดได้

เหตุผลที่ 6 – ตัวเลขถูกล้อมรอบด้วยเครื่องหมายคำพูดสองชั้น

ตัวเลขที่ถูกล้อมรอบด้วยเครื่องหมายคำพูดสองชั้น ทำให้เกิดความสับสนระหว่างค่าข้อความและตัวเลข ทำให้สูตรหยุดคำนวณโดยอัตโนมัติ ในภาพด้านล่าง ฟังก์ชัน SUM ส่งคืนผลลัพธ์ผิดเพราะเหตุผลนี้

• ข้อผิดพลาดเกิดจากเครื่องหมายคำพูดสองชั้นใน ฟังก์ชัน IF ทำให้บางตัวเลขถูกนับเป็นข้อความ

วิธีแก้ – ใช้อาร์กิวเมนต์ที่เหมาะสมในสูตร

เพียงแค่ลบเครื่องหมายคำพูดสองชั้นออกจากอาร์กิวเมนต์ของ ฟังก์ชัน IF จะทำให้ได้ผลลัพธ์ที่ถูกต้อง

อ่านเพิ่มเติม: [Fixed!] SUM Formula Not Working in Excel

เคล็ดลับแก้สูตรที่ไม่คำนวณโดยอัตโนมัติใน Excel

เคล็ดลับที่ 1 – ใช้การจัดรูปแบบเซลล์ที่เหมาะสม

การเลือกการจัดรูปแบบเซลล์ที่ผิดเป็นอีกสาเหตุที่เป็นไปได้ที่ทำให้สูตร Excel ไม่คำนวณโดยอัตโนมัติ การเลือกการจัดรูปแบบเซลล์ที่เหมาะสมจะแก้ไขข้อผิดพลาดนี้

ขั้นตอน:

• เลือกช่วง C6:E16
• คลิกแท็บ Home
• เลือกรูปแบบที่เหมาะสมจากกลุ่ม Number เช่น Currency สำหรับเซลล์ของเรา

สูตรคำนวณถูกต้องตอนนี้

หมายเหตุ:

เมื่อป้อน 12/10 ลงในเซลล์ เราต้องการให้คืนค่าอะไรในเซลล์นั้น? ผลลัพธ์ของ 12/10? หรือผลลัพธ์ของ 10 ธันวาคม? โดยค่าเริ่มต้น Excel สมมติว่าเราต้องการ 10 ธันวาคม ของปีปัจจุบัน

ประเภทการแปลงอัตโนมัติที่พบบ่อยที่สุดใน Excel เกี่ยวกับวันที่ ใน Excel วันที่แสดงเป็นตัวเลข แสดงจำนวนวันนับจาก 31 ธันวาคม 1899 ปีปัจจุบันของเราคือ 2023 หากเราป้อน 12/10 Excel คิดว่าเราต้องการ 10 ธันวาคม และส่งคืน 45271 เป็นตัวเลขของวันที่นั้น

เคล็ดลับที่ 2 – ยกเลิกการติ๊กตัวเลือก Circular Reference

การเปิดใช้งานตัวเลือก circular reference บางครั้งก่อให้เกิดปัญหาสำหรับสูตร Excel ในการประเมินค่าอัตโนมัติ การปิดฟีเจอร์นี้จะแก้ไขปัญหา

ขั้นตอน:

• ไปที่แท็บ Formula
• ในกลุ่ม Formula Auditing คลิกลูกศรดรอปดาวน์ภายใต้ Error Checking
• ปิด Circular Reference

สูตรที่ผิดปกติตอนนี้คำนวณถูกต้องแล้ว

เคล็ดลับที่ 3 – ปิดคำสั่ง Show Formulas

ขั้นตอน:

• คลิกแท็บ Formula
• จากกลุ่ม Formula Auditing คลิกที่ตัวเลือก Show Formula เพื่อปิด

สูตรที่ผิดปกติตอนนี้คำนวณถูกต้องแล้ว

เหตุผลในการใช้โหมดการคำนวณแบบ Manual ใน Excel

1. ควบคุมการคำนวณ: โดยค่าเริ่มต้น Excel จะคำนวณสูตรทั้งหมดใหม่ทุกครั้งที่มีการเปลี่ยนแปลงเซลล์ โหมดการคำนวณแบบ Manual ให้คุณควบคุมได้มากขึ้นว่าเมื่อใดควรคำนวณ
2. ปรับปรุงประสิทธิภาพ: การคำนวณแบบ Manual สามารถปรับปรุงประสิทธิภาพ Excel ได้ โดยเฉพาะเมื่อทำงานกับสเปรดชีตขนาดใหญ่และซับซ้อน ที่การคำนวณใหม่อัตโนมัติหลังการเปลี่ยนแปลงทุกครั้งอาจทำให้ Excel ตอบสนองช้าลง ด้วยการใช้การคำนวณแบบ Manual คุณสามารถทำการเปลี่ยนแปลงหลายอย่างในสเปรดชีต แล้วคำนวณสูตรทั้งหมดพร้อมกันครั้งเดียว ประหยัดกำลังประมวลผลและเพิ่มประสิทธิภาพ
3. การตรวจจับข้อผิดพลาดและการแก้ไขบั๊ก: โหมดการคำนวณแบบ Manual ช่วยให้คุณตรวจสอบและตรวจสอบสูตรก่อนทำการคำนวณ สามารถช่วยระบุข้อผิดพลาดและความไม่สอดคล้องในสเปรดชีต ด้วยการกดคำนวณใหม่ด้วยตนเอง คุณสามารถตรวจสอบความแม่นยำของสูตร ตรวจสอบข้อผิดพลาดที่อาจเกิดขึ้น และแก้ไขปัญหาที่อาจเกิดขึ้น
4. การตรวจสอบข้อมูล: ในบางกรณี คุณอาจต้องตรวจสอบข้อมูลก่อนทำการคำนวณ โหมดการคำนวณแบบ Manual ช่วยให้คุณป้อนและตรวจสอบข้อมูลโดยไม่กระทบการคำนวณใหม่อัตโนมัติ สิ่งนี้ทำให้มั่นใจว่าข้อมูลแม่นยำและสมบูรณ์ก่อนคำนวณ ช่วยรักษาความสมบูรณ์ของข้อมูล
5. หลีกเลี่ยง Circular References: Circular references เกิดขึ้นเมื่อสูตรอ้างอิงถึงเซลล์ที่ตัวมันอยู่ สร้างลูปการคำนวณแบบไม่รู้จบ โหมดการคำนวณแบบ Manual ช่วยระบุและแก้ไข circular references โดยต้องการให้คุณคำนวณสูตรใหม่ด้วยตนเอง ทำให้ง่ายต่อการระบุและแก้ไข circular references ป้องกันข้อผิดพลาดการคำนวณ
6. การจัดการทรัพยากร: การคำนวณอัตโนมัติใช้ทรัพยากรคอมพิวเตอร์ โดยเฉพาะในสเปรดชีตขนาดใหญ่ ด้วยการใช้โหมดการคำนวณแบบ Manual คุณสามารถประหยัดทรัพยากรระบบ ลดการใช้ CPU และเพิ่มประสิทธิภาพคอมพิวเตอร์ของคุณ

สำคัญคือ ต้องจำว่า เมื่อใช้โหมดการคำนวณแบบ Manual คุณต้องจำให้คำนวณสูตรใหม่ด้วยตนเองเพื่อให้ได้ผลลัพธ์ที่แม่นยำ

วิธีบังคับให้สูตรคำนวณใหม่ในสมุดงานหรือแผ่นงาน Excel

ในกรณีที่คุณต้องการให้ตัวเลือก Calculation ตั้งเป็น Manual มี 2 วิธีในการบังคับให้สูตรคำนวณใหม่: ใช้ปุ่ม Calculate บน Ribbon หรือใช้ลัดปุ่มต่อไปนี้

➤ เพื่อคำนวณใหม่ แผ่นงานที่ใช้งานอยู่:

• กด Shift + F9, หรือ
• ไปที่แท็บ Formulas >> เลือก Calculate Sheet จากกลุ่ม Calculation

➤ เพื่อคำนวณใหม่ สมุดงานทั้งเล่ม:

• กด F9, หรือ
• จากแท็บ Formulas เลือก Calculate Now จากกลุ่ม Calculation

➤ เพื่อคำนวณใหม่ ทุกแผ่นงานในสมุดงานที่เปิดอยู่ทั้งหมด ให้กด Ctrl + Alt + F9

➤ เพื่อคำนวณใหม่ สูตรเฉพาะบนแผ่นงาน:

• เลือกเซลล์ที่มีสูตร
• เข้าสู่โหมดแก้ไขโดยกด F2 หรือดับเบิลคลิกเซลล์
• กด Enter เพื่อดำเนินการคำนวณใหม่

สิ่งที่ควรจำ

• กดปุ่ม “F9” เพื่อคำนวณใหม่แผ่นงานที่ใช้งานอยู่ด้วยตนเอง
• ตรวจสอบให้แน่ใจว่าเซลล์ที่มีสูตรจัดรูปแบบเป็น “General” หรือรูปแบบที่เหมาะสมสำหรับผลลัพธ์ที่คาดหวัง การจัดรูปแบบที่ไม่เหมาะสมอาจทำให้คำนวณผิดหรือไม่คำนวณ
• ระบุและแก้ไขข้อผิดพลาด circular reference ใดๆ
• หากสูตรของคุณมีการอ้างอิงภายนอกไปยังสมุดงานหรือไฟล์อื่น ให้ตรวจสอบว่าไฟล์ที่เชื่อมโยงเข้าถึงได้ และสูตรอ้างอิงถูกต้อง

คำถามที่พบบ่อย

1. ควรทำอย่างไรหากกด F9 แล้วสูตรไม่คำนวณใหม่?

คำตอบ: หากการกด F9 ไม่กระตุ้นการคำนวณใหม่ ให้ตรวจสอบว่าสูตรมีการอ้างอิงภายนอกไปยังไฟล์หรือสมุดงานอื่นหรือไม่ ตรวจสอบว่าการอ้างอิงเหล่านี้ถูกต้องและไฟล์ที่เชื่อมโยงเข้าถึงได้ ตรวจสอบด้วยว่ามีการตั้งค่าการคำนวณหรือการพึ่งพาการคำนวณเฉพาะที่ป้องกันการคำนวณใหม่โดยอัตโนมัติหรือไม่

2. จะแก้ไขสูตรที่ไม่คำนวณในช่วงเซลล์เฉพาะได้อย่างไร?

คำตอบ: หากสูตรไม่คำนวณในช่วงเฉพาะ ให้ตรวจสอบว่ามีกฎการจัดรูปแบบตามเงื่อนไขหรือการตั้งค่าตรวจสอบข้อมูลที่อาจป้องกันการคำนวณหรือไม่ ตรวจสอบว่าเซลล์จัดรูปแบบถูกต้อง และไม่มีการลบล้างด้วยตนเองที่ป้องกันการคำนวณอัตโนมัติ

3. เซลล์หรือแผ่นงานที่ป้องกันส่งผลต่อการคำนวณสูตรได้ไหม?

คำตอบ: ใช่ เซลล์หรือแผ่นงานที่ป้องกันสามารถจำกัดการคำนวณสูตรได้ ตรวจสอบให้แน่ใจว่าเซลล์หรือแผ่นงานที่จำเป็นไม่ถูกล็อกหรือป้องกัน หากต้องการการคำนวณโดยอัตโนมัติ

ดาวน์โหลดไฟล์ฝึกฝน

บทความที่เกี่ยวข้อง

• วิธี Refresh สูตรใน Excel
• [Fixed!]: Excel Formula Not Showing Correct Result
• [Fixed!] Excel Formulas Not Working on Another Computer
• [Solved:] Excel Formula Not Working unless Double Click Cell
• [Solved]: Excel Array Formula Not Showing Result

<< กลับไปที่ Formulas not Working in Excel | Excel Formulas | Learn Excel

รับแบบฝึกหัด Excel ขั้นสูงพร้อมเฉลยฟรี!