แก้ไขข้อผิดพลาดทั่วไปจากการคัดลอก

ข้อผิดพลาดจากการคัดลอกส่วนใหญ่มีสาเหตุหลักร่วมกัน นั่นคือการอ้างอิงเลื่อนเมื่อควรคงที่ หรือคงที่เมื่อควรเลื่อน

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

บทเรียนนี้จะแสดงข้อผิดพลาดที่พบบ่อยและวิธีแก้ไขแต่ละข้อ

สมมติว่า E1 เก็บอัตราภาษี 0.08 และคุณเขียน =C2*E1 ใน D2 แล้วเติมลงด้านล่าง ลองดูว่าจะเกิดอะไรขึ้น:

• D2 = =C2*E1 (ถูกต้อง)
• D3 = =C3*E2 (E2 ว่างเปล่า!)
• D4 = =C4*E3 (ยังคงว่างเปล่า)

การอ้างอิง E เลื่อนหลุดจากเซลล์ที่เก็บอัตรา แถวที่อยู่ถัดจากแถว 2 จะคูณด้วยศูนย์ จึงได้ 0 ทุกแห่ง

ตรึงเซลล์ร่วมไว้เพื่อไม่ให้เลื่อน เปลี่ยนสูตรเป็น =C2*$E$1 ก่อนเติม

เครื่องหมายดอลลาร์จะล็อกทั้งคอลัมน์ E และแถว 1 ตอนนี้เมื่อเติมลงด้านล่าง:

• D3 = =C3*$E$1
• D4 = =C4*$E$1

ทุกแถวจะคูณด้วยอัตราที่ถูกต้อง C ยังคงเลื่อน ส่วน $E$1 จะอยู่กับที่

หากคุณคัดลอกสูตรไปยังตำแหน่งที่การอ้างอิงจะเลื่อนพ้นขอบของชีต คุณจะได้ #REF!

ตัวอย่างเช่น สูตรอย่าง =A2-B2 ที่วางในคอลัมน์ A แล้วลากไปทางซ้ายจะไม่มีตำแหน่งที่ถูกต้องให้อ้างอิง จึงแสดงผลเป็น #REF! ข้อผิดพลาดนี้หมายถึง "การอ้างอิงนั้นไม่มีอยู่อีกต่อไป"

วิธีแก้คือวางสูตรในตำแหน่งที่การอ้างอิงยังอยู่ภายในตาราง หรือเขียนสูตรใหม่ให้ชี้ไปยังเซลล์ที่ถูกต้อง

สูตรการหารที่คัดลอกอาจไปเจอเซลล์ว่างและหารด้วยศูนย์ จึงแสดง #DIV/0!

สมมติว่า =B2/C2 ถูกเติมลงด้านล่างในคอลัมน์ที่เซลล์ C บางเซลล์ว่าง การหารด้วยเซลล์ว่างจะทำให้เกิดข้อผิดพลาด

ครอบสูตรด้วย IFERROR เพื่อแสดงค่าว่างหรือศูนย์ที่อ่านเข้าใจง่ายแทน: =IFERROR(B2/C2,0) การหารยังคงทำงานในกรณีที่ทำได้ และข้อผิดพลาดจะถูกซ่อนไว้ในกรณีที่ทำไม่ได้

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

วิธีแก้ทันทีคือกด Ctrl+Z (Cmd+Z) เพื่อเลิกทำ สเปรดชีตจะเก็บประวัติการเลิกทำไว้หลายขั้น คุณจึงย้อนกลับการเติมข้อมูลที่ทำพลาดได้หลายครั้ง

หลังจากเลิกทำแล้ว ให้เติมข้อมูลใหม่อย่างระมัดระวัง โดยหยุดลากที่แถวสุดท้ายที่ถูกต้อง

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

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

สิ่งแรกที่ควรทำเมื่อมีบางอย่างดูผิดปกติจะเหมือนเดิมเสมอ นั่นคือคลิกเซลล์ที่มีปัญหาแล้วอ่าน แถบสูตร

ตรวจสอบว่าการอ้างอิงแต่ละรายการชี้ไปยังตำแหน่งที่คุณตั้งใจหรือไม่ หากค่าคงที่ควรเป็น $E$1 แต่แสดงเป็น E5 นั่นแสดงว่าลืมล็อกการอ้างอิง หากการอ้างอิงแสดงเป็น #REF! นั่นแสดงว่าเป้าหมายถูกลบไปแล้ว แถบสูตรจะเปลี่ยนตัวเลขผิดที่ดูเป็นปริศนาให้กลายเป็นจุดที่แก้ไขได้อย่างชัดเจน

หากต้องการตรวจสอบทั้งชีตพร้อมกัน ให้เปิดมุมมอง แสดงสูตร ในเอ็กเซล ให้กด Ctrl+` (ปุ่มเครื่องหมายเน้นเสียงย้อนกลับ) ส่วนใน Google ชีต ให้เลือกมุมมอง แล้วเลือกแสดงสูตร

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

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

ใส่เครื่องหมายดอลลาร์ให้กับสิ่งที่ต้องคงที่ เช่น อัตราเดียว ยอดรวมในส่วนหัว หรือตารางค้นหา วิธีเพิ่มเครื่องหมายอย่างรวดเร็วคือคลิกภายในการอ้างอิงแล้วกด F4 ซึ่งจะวนไปตามตัวเลือกการล็อกต่าง ๆ ล็อกก่อน แล้วค่อยเติม

สมมติว่าคอลัมน์ค่าคอมมิชชันแสดง 0 ในทุกแถวยกเว้นแถวแรก คุณคลิกแถว 5 แล้วแถบสูตรแสดง =C5*E4 แต่เซลล์ที่เก็บอัตราอยู่ใน E1

วินิจฉัยได้ชัดเจนว่า E1 ไม่เคยถูกล็อก จึงเลื่อนไปเป็น E4 แก้สูตรเดิมเป็น =C2*$E$1 แล้วเติมคอลัมน์ใหม่ ตอนนี้ทุกแถวแสดงผลถูกต้อง เครื่องหมายดอลลาร์เพียงตัวเดียวแก้ไขทั้งคอลัมน์ได้

ทดสอบทักษะการแก้ไขปัญหาจากการคัดลอกสูตร

ตอนนี้คุณสามารถซ่อมแซมสูตรที่ทำงานผิดพลาดเมื่อคัดลอกได้แล้ว:

• ค่าคงที่ที่เลื่อนผิดตำแหน่ง แก้ไขได้ด้วยการใส่เครื่องหมายดอลลาร์เพื่อล็อกไว้ เช่น $E$1
• #REF! หมายถึงการอ้างอิงเลื่อนพ้นตาราง ส่วน #DIV/0! หมายถึงการหารด้วยเซลล์ว่าง ซึ่งแก้ไขได้ด้วย IFERROR
• Ctrl+Z ใช้เลิกทำการเติมที่ผิดพลาด ส่วน เติมโดยไม่จัดรูปแบบ ช่วยรักษาการจัดรูปแบบให้เรียบร้อย
• วินิจฉัยปัญหาด้วย แถบสูตร หรือมุมมอง แสดงสูตร และล็อกการอ้างอิงก่อนเติม

คุณเรียนจบหัวข้อการคัดลอกสูตรอย่างถูกวิธีแล้ว