คนส่วนใหญ่คุ้นเคยกับการใช้ข้อมูลอ้างอิงที่เกี่ยวข้อง ใน Excel ทั้งนี้เนื่องจากการอ้างอิงเซลล์ในสเปรดชีต Excel มีค่าเริ่มต้นเป็นวิธีการอ้างอิงแบบสัมพัทธ์
อย่างไรก็ตาม มีบางครั้งที่การอ้างอิงแบบสัมพัทธ์อาจสร้างความรำคาญ โดยจะเปลี่ยนการอ้างอิงเซลล์ทุกครั้งที่คุณคัดลอกเซลล์หรือเติมคอลัมน์และแถว ถ้าคุณไม่ต้องการให้การอ้างอิงเปลี่ยนแปลง คุณจะต้องใช้การอ้างอิงแบบสัมบูรณ์หรือการอ้างอิงแบบผสม (รวมถึงการอ้างอิงแบบสัมพัทธ์และแบบสัมบูรณ์)
ในการอ้างอิงแบบสัมบูรณ์ การอ้างอิงคอลัมน์และแถวคือ " ถูกล็อก” ดังนั้น สิ่งเหล่านี้จะไม่เปลี่ยนแปลงเมื่อคุณคัดลอกหรือเติมจากเซลล์นั้น
บทความนี้จะกล่าวถึงวิธีที่คุณสามารถใช้การอ้างอิงแบบสัมบูรณ์เพื่อทำให้ Excel มีพฤติกรรมในแบบของคุณ ต้องการข้อมูล
วิธีการทำงานของการอ้างอิงแบบสัมพัทธ์ใน Excel
เมื่อคุณป้อนค่าลงในสเปรดชีต Excel แต่ละเซลล์จะมีตัวอักษรและตัวเลขเฉพาะที่กำหนดให้กับเซลล์ นี่แสดงถึงคอลัมน์และแถวของเซลล์นั้น
ตัวอย่างเช่น ค่าของ “1” ในสเปรดชีตด้านล่างจะอยู่ในคอลัมน์ A และแถวที่ 2 ดังนั้น “การอ้างอิง” ไปยังเซลล์นี้คือ A2
ถ้าคุณต้องการทำการคำนวณในเซลล์ถัดไปตาม เซลล์นี้โดยการเพิ่ม 1 คุณจะเขียนสูตรต่อไปนี้:
=A2+1
สูตรนี้จะแทรกค่าจาก A2 ลงใน สูตรคำนวณแล้วส่งออกผลลัพธ์ลงในเซลล์ที่มีสูตรนี้
เมื่อคุณกด Enterคุณจะเห็น ผลลัพธ์
ด้วยการอ้างอิงแบบสัมพัทธ์ คุณไม่จำเป็นต้องพิมพ์สูตรนี้ในเซลล์อื่นๆ สิ่งที่คุณต้องทำคือลากมุมของเซลล์โดยให้สูตรดั้งเดิมลงมาตามต้องการ
ในเซลล์ถัดไป การอ้างอิงถึง A2 จะกลายเป็น A3 ในเซลล์ด้านล่างนั้น A3 จะกลายเป็น A4 กล่าวอีกนัยหนึ่ง Excel รู้ว่าคุณต้องการเพิ่ม 1 ลงในเซลล์ก่อนหน้า ดังนั้น Excel จะอัปเดตตัวเลข (การอ้างอิงแถว) ตามที่คุณลากลง
วิธีนี้ได้ผล ในลักษณะเดียวกันหากคุณลากสูตรข้ามคอลัมน์ แทนที่จะอัปเดตตัวเลข Excel จะอัปเดตส่วนหลังของข้อมูลอ้างอิง (คอลัมน์) เพื่ออ้างอิงเซลล์ที่อยู่ด้านบนเสมอ
คอลัมน์ทางด้านขวาประกอบด้วย B2 ทางด้านขวาของที่มี C2 และอื่นๆ
นี่คือตัวอย่างง่ายๆ ของการทำงานของการกำหนดที่อยู่แบบสัมพัทธ์สำหรับการอ้างอิงทั้งคอลัมน์และเซลล์
วิธีอ้างอิงแบบสัมบูรณ์ใน Excel
การอ้างอิงแบบสัมบูรณ์ใน Excel ช่วยให้คุณสามารถอ้างอิงเซลล์เดียวกัน แทนที่จะให้ Excel อัพเดทอัตโนมัติ อ้างอิงแถวหรือคอลัมน์สำหรับคุณ การอ้างอิง "ผสม" คือถ้าคุณล็อกเฉพาะแถวหรือคอลัมน์ และ "การอ้างอิงแบบสัมบูรณ์" คือเมื่อคุณล็อกทั้งสองอย่าง
มาดูตัวอย่างกัน
สมมติว่าสเปรดชีตของคุณ มีเลข “10” ในแถวบนสุด และคุณต้องการให้ทุกแถวด้านล่างคูณตัวเลขนั้นด้วยตัวเลขในเซลล์ทางซ้าย
การทำเช่นนี้ คุณจะต้องป้อนสูตรที่มีลักษณะดังนี้:
=B$2*A3
การดำเนินการนี้จะล็อกการอ้างอิง "2" เพื่อไม่ให้เปลี่ยนการอ้างอิงแถวหากคุณลากเซลล์ที่มีสูตรนี้ลงไปที่เซลล์ด้านล่าง เนื่องจาก A3 ยังคง "ปลดล็อก" ทั้งการอ้างอิงแถวและคอลัมน์จะยังคงเปลี่ยนโดยอัตโนมัติและอ้างอิงเซลล์ทางด้านซ้ายเสมอ
คุณจะสังเกตได้ว่าวิธีนี้ใช้ได้เพราะคุณเท่านั้น ลากลงไปในเซลล์ในคอลัมน์เดียวกันอีกครั้ง ดังนั้นคุณจึงไม่ต้องล็อกคอลัมน์ (B) โดยวางเครื่องหมายดอลลาร์ ($) ไว้ข้างหน้า
ปัญหาคือถ้าคุณต้องการใช้สูตรเดียวกันทางด้านขวาของ สูตรเดิม การอ้างอิง "B" จะเปลี่ยนไป และสูตรจะไม่อ้างอิง B2 ตามที่ตั้งใจอีกต่อไป
มาดูวิธีการใช้การอ้างอิงแบบสัมบูรณ์แทน การอ้างอิงแบบผสมเพื่อให้การกรอกทั้งสองทิศทางทำงานได้อย่างถูกต้อง
การใช้การอ้างอิงแบบสัมบูรณ์ใน Excel อย่างถูกต้อง
หากต้องการใช้การอ้างอิงที่ถูกต้องในสูตรนี้ คุณต้องพิจารณาให้แน่ชัดว่าคุณกำลังพยายามทำอะไร ทำ
ในกรณีนี้ เราต้องการลักษณะการทำงานต่อไปนี้เมื่อกรอกข้อมูลทางด้านขวา
เมื่อดูพฤติกรรมเหล่านี้แล้ว คุณจะรู้ว่าคุณต้องการอะไร เพื่อ “ล็อค” และสิ่งที่คุณไม่ทำ ต้องล็อกทั้ง "B" และ "2" (ไม่เปลี่ยนแปลง) นอกจากนี้ คอลัมน์ A ยังต้องล็อกอยู่
ดังนั้น สูตรของคุณใน B3 จึงต้องมีลักษณะดังนี้: =$B$2*$A3
ตอนนี้เมื่อ คุณลากเซลล์เดียวกันนี้ลงหรือขึ้น สูตรจะทำงานตามที่ต้องการ
การใช้การอ้างอิงแบบสัมบูรณ์อย่างถูกต้องอาจทำได้ยาก ดังนั้น จึงจำเป็นต้องใช้เวลาในการพิจารณาอย่างรอบคอบ คุณต้องการให้ Excel อัปเดตสูตรเมื่อคุณเติม คอลัมน์หรือแถว ในทิศทางใดทิศทางหนึ่ง
การปั่นจักรยานผ่านประเภทการอ้างอิงใน Excel
คุณสามารถเร่งกระบวนการได้เมื่อคุณ เป็น สูตรการพิมพ์ ที่มีการอ้างอิงแบบสัมบูรณ์โดยการกดปุ่ม F4ซึ่งจะทำให้การอ้างอิงเซลล์เป็นแบบสัมบูรณ์
เคอร์เซอร์สามารถอยู่ด้านใดด้านหนึ่งของการอ้างอิงเซลล์ (หรือแม้แต่ตรงกลาง) เมื่อคุณกด F4 และเคอร์เซอร์จะยังคงแปลงการอ้างอิงเดียวนั้นเป็นแบบสัมบูรณ์
หากคุณไม่ต้องการค่าสัมบูรณ์ (เช่น ผสมแทน) ให้แตะ F4 ต่อไปจนกว่าข้อมูลอ้างอิงจะออกมาตามที่คุณต้องการ
หากคุณต้องการเพิ่มการอ้างอิงใดๆ ในสูตร เพียงวางเคอร์เซอร์ไว้ที่นั่นแล้วเริ่มวนซ้ำใน F4 อีกครั้ง
เมื่อคุณกำหนดค่าสูตรของคุณแล้ว เพียงกด Enter และเริ่มกรอกสเปรดชีตของคุณในทิศทางใดก็ได้ที่คุณต้องการ หากคุณตั้งค่าข้อมูลอ้างอิงถูกต้อง ทุกอย่างจะทำงานตามที่คาดไว้