การอ้างอิงปรับอย่างไรเมื่อคัดลอก

เมื่อคุณเขียน =A2 ในสูตร สเปรดชีตไม่ได้จดจำ "เซลล์ A2" จริง ๆ แต่จดจำทิศทางและระยะห่าง เช่น "เซลล์ที่อยู่ทางซ้ายของฉันสองคอลัมน์"

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

ตั้งค่าตัวเลขในสองคอลัมน์ดังนี้:

• A2 = 100, B2 = 50
• A3 = 200, B3 = 80

ใน C2 ให้พิมพ์ =A2+B2 ผลลัพธ์จะแสดงเป็น 150 จากนั้นคัดลอก C2 ลงไปยัง C3 ผลลัพธ์คือ 280 เพราะสูตรเปลี่ยนเป็น =A3+B3

คุณไม่ได้แก้ไขสิ่งใดเลย การอ้างอิงเลื่อนตามสูตรลงไปหนึ่งแถว

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

เริ่มจาก =A2+B2:

• ลงหนึ่งแถวจะกลายเป็น =A3+B3
• ลงสองแถวจะกลายเป็น =A4+B4
• ลงสามแถวจะกลายเป็น =A5+B5

คอลัมน์ A และ B ไม่เคยเลื่อน เพราะคุณไม่ได้เลื่อนไปด้านข้าง

การคัดลอกสูตรไปทางด้านข้างจะเปลี่ยนตัวอักษรคอลัมน์และคงหมายเลขแถวไว้

สมมติว่า B2 มีค่า =B1*2 ให้คัดลอกไปทางขวายัง C2 และ D2:

• C2 จะกลายเป็น =C1*2
• D2 จะกลายเป็น =D1*2

แถวยังคงเป็น 1 เพราะคุณเลื่อนไปด้านข้าง ไม่ได้เลื่อนลงด้านล่าง หากเลื่อนไปทั้งสองทิศทางพร้อมกัน ทั้งสองส่วนจะเลื่อนไปด้วยกัน

แบบจำลองทางความคิดที่ช่วยได้คือ การอ้างอิงแบบสัมพัทธ์จะเก็บระยะเลื่อนจากเซลล์ที่มีสูตร

หาก C2 มีค่า =A2 ระยะเลื่อนคือ "ไปทางซ้ายสองคอลัมน์ ในแถวเดิม" เมื่อคัดลอกสูตรนั้นไปยัง C5 ระบบจะนำระยะเลื่อนกลับมาใช้จากตำแหน่งใหม่: ไปทางซ้ายสองคอลัมน์จาก C5 ในแถวเดิม ซึ่งก็คือ A5

ลูกศรระหว่างสูตรกับเป้าหมายจะคงความยาวและทิศทางเดิมไว้เสมอ

ลองนึกถึงใบแจ้งหนี้ที่มี 1,000 แถว แต่ละแถวต้องคูณจำนวนกับราคา หากไม่มีการอ้างอิงแบบสัมพัทธ์ คุณจะต้องเขียนสูตรที่แตกต่างกัน 1,000 สูตรด้วยตนเอง

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

การอ้างอิงแบบสัมพัทธ์เปลี่ยนงานที่น่าเบื่อให้กลายเป็นการดับเบิลคลิกเพียงครั้งเดียว

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

คัดลอก =A2+B2 ลงไปห้าแถว จากนั้นคลิกเซลล์สุดท้าย แถบสูตรจะแสดง =A7+B7 ไม่ใช่ =A2+B2 แต่ละเซลล์จะมีสูตรฉบับของตนเองที่ผ่านการปรับแล้ว

การคลิกดูแต่ละเซลล์ในคอลัมน์และอ่านแถบสูตรเป็นวิธีที่ดีที่สุดในการสร้างความเข้าใจเกี่ยวกับการทำงานของการคัดลอก

โดยทั่วไปการปรับแบบสัมพัทธ์เป็นสิ่งที่คุณต้องการ แต่ก็ไม่ใช่ทุกครั้ง สมมติว่าทุกแถวต้องคูณด้วยอัตราภาษีเดียวกันซึ่งอยู่ในเซลล์ E1

หากคุณเขียน =C2*E1 แล้วเติมลงด้านล่าง ส่วน E1 จะเลื่อนไปเป็น E2, E3, E4 และต่อไปเรื่อย ๆ จนไปอยู่ในเซลล์ว่าง ทำให้ผลลัพธ์ผิดพลาด

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

นี่คือตัวอย่างสั้น ๆ ของวิธีแก้ไข การใส่เครื่องหมายดอลลาร์หน้าส่วนใดส่วนหนึ่งของการอ้างอิงจะตรึงส่วนนั้นไว้ ทำให้ไม่สามารถเลื่อนได้

เขียน =C2*$E$1 แล้วเติมลงด้านล่าง ตอนนี้ C2 จะเลื่อนไปเป็น C3, C4 และต่อไปเรื่อย ๆ ขณะที่ $E$1 ยังคงตรึงอยู่ที่อัตราภาษีทุกครั้ง

อีกไม่นานคุณจะใช้เคล็ดลับเครื่องหมายดอลลาร์นี้ได้อย่างเชี่ยวชาญ ตอนนี้เพียงทราบว่ามีวิธีนี้อยู่ก็พอ

ทุกอย่างในบทนี้ทำงานเหมือนกันใน Microsoft Excel และ Google Sheets ทั้งคู่ใช้การอ้างอิงแบบสัมพัทธ์เป็นค่าเริ่มต้น ทั้งคู่เลื่อนแถวเมื่อเติมลงด้านล่าง และทั้งคู่เลื่อนคอลัมน์เมื่อเติมไปตามแนวนอน

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

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

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

ตรวจสอบความเข้าใจของคุณเกี่ยวกับการเลื่อนของการอ้างอิง

ตอนนี้คุณเข้าใจกลไกเบื้องหลังการเติมข้อมูลแล้ว:

• การอ้างอิงเป็นแบบสัมพัทธ์โดยค่าเริ่มต้น และจัดเก็บเป็นระยะเลื่อนจากเซลล์ที่มีสูตร
• การเติม<​strong>ลงด้านล่างจะเลื่อนหมายเลขแถว ส่วนการเติม<​strong>ไปตามแนวนอนจะเลื่อนตัวอักษรคอลัมน์
• การปรับโดยอัตโนมัตินี้ทำให้สูตรเดียวรองรับหลายพันแถวได้
• เมื่อการอ้างอิงต้องอยู่กับที่ เครื่องหมายดอลลาร์จะตรึงการอ้างอิงไว้ ซึ่งคุณจะได้เรียนรู้ในหลักสูตรถัดไป

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