วิธีการตรวจจับข้อผิดพลาดในสูตรที่ซับซ้อนด้วยกลไกการแก้ไขข้อผิดพลาดอัจฉริยะของ Excel

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

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

แนวคิดพื้นฐานเพื่อให้แน่ใจว่าสูตรจะไม่ล้มเหลวตั้งแต่เริ่มต้น

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

สิ่งแรกและสำคัญที่สุดคือ ทุกสูตรต้องขึ้นต้นด้วยเครื่องหมายเท่ากับ (=)หากละเว้นเครื่องหมายเท่ากับ Excel จะไม่เข้าใจว่าคุณต้องการทำการคำนวณ แต่จะเข้าใจว่าคุณกำลังป้อนข้อความหรือวันที่ ตัวอย่างเช่น หากคุณพิมพ์ SUM(A1:A10) โดยไม่ใส่เครื่องหมายเท่ากับ เซลล์จะแสดงผล SUM(A1:A10) ตามตัวอักษร และจะไม่ทำการคำนวณใดๆ เหตุการณ์คล้ายกันนี้เกิดขึ้นกับวันที่เช่นกัน หากคุณพิมพ์ 11/2 และรูปแบบเซลล์เป็นแบบทั่วไป Excel อาจแสดง 2-nov (วันที่ 2 พฤศจิกายน) แทนที่จะหาร 11 ด้วย 2 เพราะมันตีความว่าคุณกำลังป้อนวันที่ ไม่ใช่การคำนวณ นี่เกี่ยวข้องกับ... ความผิดพลาดแปลกๆ เกี่ยวกับวันที่ใน Excel.

อีกประเด็นสำคัญคือการใช้วงเล็บอย่างถูกต้อง ฟังก์ชันทุกฟังก์ชันที่ใช้วงเล็บจะต้องมีวงเล็บที่ถูกต้อง วงเล็บเปิดและปิดที่วางไว้อย่างเหมาะสมเมื่อทำงานร่วมกับ ฟังก์ชันซ้อนกันเป็นเรื่องปกติมากที่จะเผลอใส่เครื่องหมายวงเล็บมากเกินไปหรือน้อยเกินไป ซึ่งทำให้เกิดข้อผิดพลาดทางไวยากรณ์ ลองนึกถึงสูตรอย่างเช่น =IF(B5<0,"Not valid",B5*1,05) ถ้าคุณเขียน =IF(B5<0,"Not valid",B5*1,05)) โดยไม่ได้ตั้งใจให้มีวงเล็บปิดเกินมาหนึ่งอัน สูตรนั้นจะใช้งานไม่ได้เพราะจำนวนวงเล็บจะไม่ตรงกัน การตรวจสอบเสมอว่าจำนวนวงเล็บเปิดและปิดเท่ากันและอยู่ในตำแหน่งที่ถูกต้องนั้นเป็นสิ่งสำคัญเพื่อหลีกเลี่ยงข้อผิดพลาดที่ยากต่อการค้นหา

นอกจากนี้ การจัดการช่วงข้อมูลอย่างถูกต้องก็สำคัญเช่นกัน ในการอ้างอิงเซลล์หลายเซลล์ที่อยู่ติดกัน คุณต้องใช้ เครื่องหมายโคลอน (:) ระหว่างเซลล์แรกและเซลล์สุดท้ายตัวอย่างเช่น ช่วงเซลล์จะเขียนเป็น A1:A5 หากคุณละเว้นเครื่องหมายโคลอนและเขียนเป็นสูตรเช่น =SUM(A1 A5) Excel จะตีความว่าเป็นการอ้างอิงที่ไม่ถูกต้องระหว่างเซลล์ที่ไม่เกี่ยวข้องกันและจะแสดงข้อผิดพลาด #โมฆะ!ซึ่งเป็นกรณีที่เกิดขึ้นเมื่อไม่มีจุดตัดหรือมีการอ้างอิงที่ไม่สอดคล้องกัน

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

ประเภทของอาร์กิวเมนต์: ตัวเลข ข้อความ และขีดจำกัดการซ้อน

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

ตัวอย่างเช่น ฟังก์ชันอย่าง SUM, AVERAGE หรือ PRODUCT ต้องการค่าตัวเลขเป็นอาร์กิวเมนต์ หากคุณส่งค่าข้อความเข้าไปในตำแหน่งที่ฟังก์ชันเหล่านั้นต้องการตัวเลข Excel อาจคืนค่า 0 ละเว้นเซลล์ หรือแสดงข้อผิดพลาด ขึ้นอยู่กับบริบท ในทางกลับกัน ฟังก์ชันอย่าง REPLACE, CONCAT หรือ RIGHT ถูกออกแบบมาให้ทำงานกับสตริงข้อความ ในฟังก์ชันเหล่านี้ อย่างน้อยหนึ่งในอาร์กิวเมนต์ต้องเป็นค่าข้อความหรือการอ้างอิงถึงเซลล์ที่มีข้อความ หากคุณบังคับให้ตัวเลขถูกมองว่าเป็นข้อความโดยไม่จัดการอย่างถูกต้อง ฟังก์ชันอาจทำงานไม่เป็นไปตามที่คาดหวัง

นอกจากนี้ Excel ยังมีข้อจำกัดอย่างมากในการรวมฟังก์ชันต่างๆ เข้าด้วยกัน คุณไม่สามารถทำเช่นนั้นได้ สามารถซ้อนฟังก์ชันได้มากกว่า 64 ระดับในสูตรเดียวหมายความว่า หากคุณมีสูตรที่มีคำสั่ง IF ซ้อนคำสั่ง IF ซ้อนคำสั่ง IF ไปเรื่อยๆ Excel จะไม่ยอมรับระดับความซับซ้อนที่มากเกินไปอีกต่อไป แม้ว่าในทางปฏิบัติจะมีคนไม่กี่คนที่ไปถึงระดับสุดขั้วนี้ แต่ในแบบจำลองที่ซับซ้อนมากหรือออกแบบมาไม่ดี ขีดจำกัดนี้อาจเกินได้ ทำให้เกิดข้อผิดพลาดที่ยากต่อการตีความ เมื่อถึงระดับความซับซ้อนที่กำหนด มักจะฉลาดกว่าที่จะแบ่งตรรกะออกเป็นเซลล์เสริมหลายๆ เซลล์ แทนที่จะพยายามรวมทุกอย่างไว้ในสูตรเดียวที่ยาวมาก สำหรับเรื่องนี้ ขอแนะนำให้ตรวจสอบ... สาเหตุและวิธีแก้ไขปัญหาประสิทธิภาพการทำงานใน Excel.

ตัวอย่างทั่วไปของข้อผิดพลาดเนื่องจากประเภทและจำนวนของอาร์กิวเมนต์คือฟังก์ชัน ABS ฟังก์ชันนี้รับเฉพาะอาร์กิวเมนต์ประเภทและจำนวนเท่านั้น อาร์กิวเมนต์ตัวเลขเดี่ยว และส่งคืนค่าสัมบูรณ์ หากคุณพิมพ์เช่น =ABS(-2;134) Excel จะแสดงข้อผิดพลาดเนื่องจากฟังก์ชันไม่รู้จักวิธีการจัดการกับอาร์กิวเมนต์สองตัวที่คั่นด้วยเครื่องหมายเซมิโคลอน วิธีที่ถูกต้องคือ =ABS(-2134) หรือ =ABS(A1) หากเซลล์ A1 มีตัวเลขติดลบที่คุณต้องการแปลงเป็นค่าบวก

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

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

การอ้างอิงถึงหน้าเว็บและหนังสืออื่นๆ: วิธีหลีกเลี่ยงข้อผิดพลาดในการเชื่อมโยง

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

หากในสูตร คุณอ้างอิงถึงชีตที่มีชื่อประกอบด้วยช่องว่างหรืออักขระที่ไม่ใช่ตัวอักษร (ตัวเลข เครื่องหมายขีดกลาง สัญลักษณ์...) คุณต้องระบุชื่อชีตนั้นด้วยเสมอ ในเครื่องหมายอัญประกาศเดี่ยวตัวอย่างเช่น หากคุณมีชีตชื่อ Quarterly Data การอ้างอิงที่ถูกต้องไปยังเซลล์ D3 ในชีตนั้นคือ ='Quarterly Data'!D3 ในทำนองเดียวกัน หากชีตชื่อ 123 คุณจะต้องเขียน ='123'!A1 เพื่อให้ Excel เข้าใจชื่อชีตและไม่สับสนกับตัวเลขสุ่ม

นอกจากนี้ เมื่อใดก็ตามที่สูตรชี้ไปยังชีตอื่น จะต้องใส่เครื่องหมายขีดกลาง (-) ไว้หลังชื่อชีตทันที เครื่องหมายอัศเจรีย์ (!)เครื่องหมายอัศเจรีย์ (```) แสดงถึงการเปลี่ยนผ่านระหว่างตัวระบุแผ่นงานและการอ้างอิงเซลล์เฉพาะ ตัวอย่างที่สมบูรณ์คือ: ='Quarterly Data'!D3 หากละเว้นเครื่องหมายอัศเจรีย์หรือวางผิดตำแหน่ง สูตรจะไม่ถูกต้องและจะเกิดข้อผิดพลาดในการอ้างอิง

เมื่อข้อมูลอยู่ในเวิร์กบุ๊กอื่น Excel ต้องการบริบทเพิ่มเติม ในกรณีเหล่านี้ การอ้างอิงภายนอกจะต้องรวมถึง ชื่อไฟล์ในวงเล็บ ชื่อชีต และช่วงข้อมูลตัวอย่างเช่น หากคุณต้องการนับจำนวนแถวในช่วง A1:A8 ของเวิร์กบุ๊ก Operations T2.xlsx ในชีต Sales คุณสามารถใช้สูตรเช่น =ROWS('Sales'!A1:A8) ได้ หากไฟล์ไม่ได้เปิดอยู่ คุณต้องระบุพาธของไฟล์แบบเต็มด้วย เช่น 'C:\My Documents\Sales'!A1:A8 ในสูตรด้วย

สูตรทั่วไปในบริบทนี้จะเป็นประมาณว่า =ROWS('C:\My Documents\Sales'!A1:A8) คำสั่งนี้จะส่งคืนจำนวนแถวในช่วง A1:A8 ในเวิร์กบุ๊กอื่น ซึ่งในกรณีนี้คือ 8 แถว หากพิมพ์เส้นทาง ชื่อเวิร์กบุ๊ก ชื่อชีต หรือวงเล็บเหลี่ยมผิดพลาด ผลลัพธ์ที่ได้จะเป็นข้อผิดพลาด ข้อผิดพลาดในการอ้างอิงหรือค่าที่ไม่ได้รับการอัปเดต เนื่องจาก Excel ไม่สามารถค้นหาแหล่งข้อมูลภายนอกได้

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

การจัดรูปแบบตัวเลขในสูตร: ข้อผิดพลาดเนื่องจากสัญลักษณ์และตัวคั่น

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

ตัวเลขในสูตรไม่ควรมีการจัดรูปแบบ กล่าวคือ ถ้าค่าคือ 1000 ยูโร จะต้องแสดงในสูตรเป็น 1000 โดยไม่มีสัญลักษณ์ € เครื่องหมายคั่นหลักพัน หรือเครื่องหมายจุลภาค ถ้าคุณเขียน 1.000 หรือ 1,000 (ขึ้นอยู่กับการตั้งค่าภูมิภาค) Excel อาจตีความว่าเป็นเครื่องหมายคั่นอาร์กิวเมนต์หรือค่าอื่นที่ไม่ใช่ค่าที่คุณต้องการ ตัวเลขจะถูกจัดรูปแบบด้วยสกุลเงิน เครื่องหมายคั่นหลักพัน หรือเครื่องหมายคั่นทศนิยม แล้วก็ใช้ตัวเลือกการจัดรูปแบบเซลล์ ไม่ใช่ภายในนิพจน์ สำหรับรายละเอียดเพิ่มเติมเกี่ยวกับวิธีการใช้การจัดรูปแบบอย่างถูกต้อง โปรดดูที่ รูปแบบข้อมูลใน Excel 365.

ลองนึกภาพว่าคุณต้องการบวก 3100 เข้ากับค่าในเซลล์ A3 หากคุณพิมพ์สูตร `=SUM(3.100,A3)` โดยคิดว่าหมายถึง 3100 โปรแกรม Excel จะตีความว่าเป็นการบวก 3 และ 100 ก่อน แล้วจึงบวกค่าในเซลล์ A3 เข้ากับผลลัพธ์ กล่าวคือ มันจะคำนวณเป็น (3 + 100) + A3 ซึ่งไม่เหมือนกับ A3 + 3100 สูตรที่ถูกต้องคือ `=SUM(3100,A3)` โดยไม่ต้องพยายามแสดงรูปแบบตัวคั่นหลักพัน

สิ่งที่คล้ายกันนี้เกิดขึ้นกับฟังก์ชันที่รับอาร์กิวเมนต์ได้เพียงจำนวนจำกัด เช่น ฟังก์ชัน ABS ซึ่งรับเฉพาะค่าตัวเลขเท่านั้น หากคุณพยายามใส่เครื่องหมายจุลภาคหรือเครื่องหมายอัฒภาคไว้ในตัวเลขเพื่อจำลองหลักพัน โปรแกรม Excel จะถือว่าเป็นอาร์กิวเมนต์แยกกันหลายตัว และฟังก์ชันจะไม่ทำงาน จะส่งคืนข้อผิดพลาดทางไวยากรณ์ดังนั้น นิพจน์เช่น =ABS(-2;134) จึงใช้ไม่ได้ ในขณะที่ =ABS(-2134) นั้นใช้ได้

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

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

การตรวจสอบข้อผิดพลาดและการดีบักอัจฉริยะใน Excel

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

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

อย่างไรก็ตาม ใน Excel Online (เวอร์ชันบนเว็บ) ปัจจุบันยังไม่สามารถกำหนดค่าหรือใช้งานกฎการตรวจสอบข้อผิดพลาดขั้นสูงเหล่านี้ได้ในลักษณะเดียวกัน บริการบนคลาวด์มีฟังก์ชันพื้นฐาน แต่ ไม่รวมชุดกฎการตรวจสอบสูตรทั้งหมด สามารถใช้งานได้บนเดสก์ท็อป ดังนั้น เมื่อทำงานกับเวิร์กบุ๊กที่ซับซ้อนมาก การเปิดเวิร์กบุ๊กด้วยแอปพลิเคชันบนเดสก์ท็อปจึงเป็นวิธีที่สะดวกที่สุด เพื่อใช้ประโยชน์จากเครื่องมือแก้ไขข้อผิดพลาดได้อย่างเต็มประสิทธิภาพ

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

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

หากคุณต้องการเข้าถึงแอปพลิเคชัน Office ทั้งหมด (Word, Excel, PowerPoint ฯลฯ) และบริการที่เกี่ยวข้องอย่างครอบคลุมมากขึ้น คุณสามารถทดลองใช้หรือซื้อชุดโปรแกรมเต็มรูปแบบได้ที่ Office.com ซึ่งจะช่วยให้คุณมีเครื่องมือตรวจสอบข้อผิดพลาดที่ทันสมัยที่สุดสำหรับสเปรดชีตทั้งหมดของคุณ

ข้อผิดพลาดที่เกิดจากการจัดเก็บตัวเลขในรูปแบบข้อความ และวิธีการแก้ไขข้อผิดพลาดเหล่านั้นจำนวนมาก

สถานการณ์ที่พบได้บ่อยมาก โดยเฉพาะในแวดวงบัญชีหรือการเงิน คือการส่งออกข้อมูลบัญชีแยกประเภททั่วไปแบบละเอียด (Detailed GL) จากซอฟต์แวร์การจัดการ ในหลายกรณี แอปพลิเคชันประเภทนี้ พวกเขาส่งออกค่าตัวเลขเป็นข้อความผลที่ตามมาคือ เมื่อเปิดไฟล์ใน Excel สูตรที่พยายามบวก ลบ หรือวิเคราะห์ข้อมูลในฟิลด์เหล่านั้นจะไม่รู้จักค่าเหล่านั้นว่าเป็นตัวเลข และการดำเนินการจะล้มเหลวหรือให้ผลลัพธ์ที่ไม่ถูกต้อง

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

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

มีกลยุทธ์หลายอย่างที่สามารถนำมาใช้ได้โดยตรงจากโปรแกรม Excel บนเดสก์ท็อปเพื่อแก้ไขสถานการณ์ประเภทนี้ หนึ่งในกลยุทธ์ที่ตรงที่สุดคือการใช้... กาวพิเศษที่มีการคำนวณทางคณิตศาสตร์ตัวอย่างเช่น คุณสามารถพิมพ์เลข 1 ลงในเซลล์ว่าง คัดลอกเซลล์นั้น เลือกช่วงเซลล์ที่มีตัวเลขที่จัดเก็บเป็นข้อความ (เช่น คอลัมน์ทั้งหมดของจำนวนเงิน) แล้วใช้ วางแบบพิเศษ > คูณ การดำเนินการนี้จะทำให้ Excel ตีความข้อความแต่ละรายการเป็นตัวเลข คูณด้วย 1 และบันทึกผลลัพธ์เป็นค่าตัวเลขจริง โดยคงจำนวนเงินเดิมไว้ เป็นวิธีที่มีประสิทธิภาพมากในการแปลงข้อมูลจำนวนมากในคราวเดียว

อีกทางเลือกหนึ่งคือการใช้ฟังก์ชัน VALUE ในคอลัมน์เสริม ตัวอย่างเช่น หากจำนวนเงินอยู่ในคอลัมน์ B คุณสามารถสร้างสูตรเช่น =VALUE(B2) ในคอลัมน์ C คัดลอกลงมา แล้วคัดลอกและวางผลลัพธ์เป็นค่าลงในคอลัมน์เดิม โดยแทนที่ข้อความ วิธีนี้ยังช่วยให้คุณตรวจสอบด้วยสายตาได้ว่าทุกอย่างตรงกันก่อนที่จะลบข้อมูลเดิม

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

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

กล่าวโดยสรุป แม้ว่าการตรวจสอบข้อผิดพลาดมาตรฐานมักจะทำงานทีละแถว แต่การผสมผสานคำเตือนเหล่านั้นเข้ากับเทคนิคต่างๆ เช่น ฟังก์ชันการวางและการแปลงพิเศษ ก็เป็นไปได้ เพื่อแก้ไขข้อมูลผิดรูปแบบจำนวนมากในคราวเดียว และคืนสถานะให้เป็นตัวเลขโดยไม่ต้องทำการตรวจสอบทีละเซลล์

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