ล็อกเซลล์เดียวด้วยการอ้างอิงแบบสัมบูรณ์

ความต้องการที่พบบ่อยมากคือการให้สูตรจำนวนมากอ้างอิง ค่าร่วมค่าเดียว เช่น อัตราภาษี อัตราแลกเปลี่ยน เป้าหมาย หรือค่าธรรมเนียมคงที่

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

การอ้างอิงแบบ สัมบูรณ์ใช้เครื่องหมายดอลลาร์หน้าทั้งคอลัมน์และแถว เช่น $A$1 จึงถูกล็อกไว้อย่างสมบูรณ์

คัดลอกสูตรที่มี $A$1 ไปที่ใดก็ได้ในชีต และส่วนนั้นจะยังคงอ้างอิงเซลล์ A1 เสมอ เฉพาะการอ้างอิงที่ไม่ได้ล็อกในสูตรเท่านั้นที่จะเลื่อน

ลองนึกภาพว่าอัตราภาษีขาย 0.08 อยู่ในเซลล์ E1 ยอดรวมคำสั่งซื้ออยู่ในคอลัมน์ B หากต้องการบวกภาษีให้แต่ละคำสั่งซื้อ ให้เขียนใน C2 ว่า:

=B2*$E$1

B2 เป็นการอ้างอิงแบบสัมพัทธ์ จึงเลื่อนตามแต่ละแถว ส่วน $E$1 เป็นการอ้างอิงแบบสัมบูรณ์ จึงอ่านค่าอัตราภาษีเสมอ

เติมสูตรจาก C2 ลงไปยัง C2, C3, C4 แล้วสังเกตว่าแต่ละส่วนเปลี่ยนแปลงอย่างไร:

• B2 เปลี่ยนเป็น B3 แล้วเป็น B4 (แบบสัมพัทธ์ จึงเลื่อน)
• $E$1 ยังคงเป็น $E$1 ทุกครั้ง (แบบสัมบูรณ์ จึงคงที่)

ดังนั้นแถว 3 จะแสดง =B3*$E$1 และแถว 4 จะแสดง =B4*$E$1 อัตราภาษีจึงไม่สูญหายไป

หากลืมเครื่องหมายดอลลาร์ ให้เขียน =B2*E1 เมื่อคัดลอกลงด้านล่าง E1 จะเลื่อนไปเป็น E2 และ E3 ซึ่งเป็นเซลล์ว่าง

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

หากต้องการเปลี่ยน E1 เป็น $E$1 อย่างรวดเร็ว ให้คลิกที่ E1 ภายในแถบสูตร แล้วกด F4 หนึ่งครั้ง Excel จะใส่เครื่องหมายดอลลาร์ทั้งสองให้คุณ

วิธีนี้เร็วกว่ามากและเกิดข้อผิดพลาดน้อยกว่าการพิมพ์สัญลักษณ์ด้วยตนเอง โดยเฉพาะในสูตรที่ยาว

สมมติว่า $B$1 เก็บอัตราแลกเปลี่ยนจาก USD เป็น EUR ที่ 0.92 และจำนวนเงินดอลลาร์เรียงลงมาในคอลัมน์ A ให้แปลงแต่ละจำนวนด้วยสูตรนี้:

=A2*$B$1

คัดลอกสูตรลงไปหลายร้อยแถว แล้วการแปลงทุกครั้งจะใช้อัตราเดียวกัน หากอัปเดตอัตราใน B1 เพียงครั้งเดียว ทั้งคอลัมน์จะคำนวณใหม่ทันที

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

คัดลอก =B2*$E$1 ไปทางขวา แล้วจะกลายเป็น =C2*$E$1 นั่นคือ B เปลี่ยนเป็น C แต่ $E$1 ยังคงอยู่ที่เดิม การล็อกนี้ทำงานได้ในทุกทิศทาง

การอ้างอิงแบบสัมบูรณ์มีประโยชน์ภายในฟังก์ชันเช่นกัน หากต้องการหาสัดส่วนของแต่ละค่าจากยอดรวมคงที่ใน B1:

=A2/$B$1

หรือหากต้องการเปรียบเทียบคะแนนแต่ละคะแนนกับเป้าหมายที่เก็บไว้ใน D1:

=IF(A2>=$D$1,"Pass","Fail")

เซลล์ที่ล็อกไว้จะยังคงเป็นจุดอ้างอิงในทุกแถว

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

เพิ่มอัตราภาษีใน E1 จาก 0.08 เป็น 0.09 แล้วทุกสูตร =B2*$E$1 จะคำนวณใหม่ทันที ไม่ต้องแก้ไขสูตรหลายสิบสูตร เพราะข้อมูลนำเข้าเพียงจุดเดียวควบคุมสูตรทั้งหมด

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

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

การอ้างอิงแบบสัมบูรณ์ เช่น $A$1 จะล็อกทั้งคอลัมน์และแถว จึงชี้ไปยังเซลล์เดิมเสมอเมื่อคัดลอก

• ใช้กับค่าที่ใช้ร่วมกัน เช่น อัตรา ค่าธรรมเนียม และเป้าหมาย
• ผสมกับการอ้างอิงแบบสัมพัทธ์ เพื่อให้เฉพาะส่วนที่เหมาะสมเลื่อนได้
• กด F4 เพื่อเพิ่มเครื่องหมายดอลลาร์ทั้งสองทันที

ถัดไป คุณจะเรียนรู้การล็อกทีละส่วนด้วยการอ้างอิงแบบผสม