ต้องรู้
- ในการคำนวณหลายรายการโดยยึดตามเซลล์อื่น และรักษาค่าคงที่ของแถวหรือคอลัมน์ ให้ใช้การอ้างอิงเซลล์แบบสัมบูรณ์
- ในสมการนี้ การอ้างอิงเซลล์แบบสัมบูรณ์คือ A$12:=+B2+B2+A$12.
- สัญลักษณ์ $ "ถือ" ค่าคงที่ของแถวหรือคอลัมน์ แม้ว่าจะคัดลอกหรือเติมคอลัมน์หรือแถวด้วยสูตรเดียวกัน
บทความนี้อธิบายวิธีใช้การอ้างอิงเซลล์แบบสัมบูรณ์ใน Excel 2010 และเวอร์ชันที่ใหม่กว่า เพื่อป้องกันไม่ให้สูตรปรับโดยอัตโนมัติเมื่อเทียบกับจำนวนแถวหรือคอลัมน์ที่คุณกรอก
การอ้างอิงเซลล์แบบสัมบูรณ์ใน Excel คืออะไร
อ้างอิงเซลล์ภายในสูตรใน Excel ได้ 2 วิธี คุณสามารถใช้การอ้างอิงเซลล์แบบสัมพัทธ์หรือการอ้างอิงเซลล์แบบสัมบูรณ์
- การอ้างอิงเซลล์สัมพัทธ์: ที่อยู่เซลล์ที่ไม่มีสัญลักษณ์ $ ที่ด้านหน้าของพิกัดแถวหรือคอลัมน์ ข้อมูลอ้างอิงนี้จะอัปเดตคอลัมน์หรือเซลล์ที่สัมพันธ์กับเซลล์ดั้งเดิมโดยอัตโนมัติเมื่อคุณกรอกข้อมูลลงหรือข้าม
- การอ้างอิงเซลล์แบบสัมบูรณ์: ที่อยู่เซลล์ที่มีสัญลักษณ์ $ อยู่ด้านหน้าพิกัดแถวหรือคอลัมน์ สิ่งนี้ "ถือ" ค่าคงที่ของแถวหรือคอลัมน์อ้างอิงแม้ว่าจะเติมคอลัมน์หรือแถวด้วยสูตรเดียวกัน
การอ้างอิงเซลล์อาจดูเหมือนเป็นแนวคิดนามธรรมสำหรับผู้ที่ยังใหม่กับมัน
วิธีใช้การอ้างอิงเซลล์แบบสัมบูรณ์ใน Excel
มีสองสามวิธีในการใช้การอ้างอิงแบบสัมบูรณ์ใน Excel วิธีที่คุณใช้ขึ้นอยู่กับส่วนอ้างอิงที่คุณต้องการให้คงที่: คอลัมน์หรือแถว
-
ตัวอย่างเช่น ใช้สเปรดชีตที่คุณต้องการคำนวณภาษีขายจริงสำหรับสินค้าหลายรายการ โดยอ้างอิงจากเซลล์อ้างอิงในส่วนอื่นๆ ในแผ่นงาน
Image -
ในเซลล์แรก คุณจะต้องป้อนสูตรโดยใช้การอ้างอิงเซลล์แบบสัมพัทธ์มาตรฐานสำหรับราคาสินค้า แต่หากต้องการดึงภาษีขายที่ถูกต้องมาคำนวณ คุณจะต้องใช้การอ้างอิงแบบสัมบูรณ์สำหรับแถว แต่ไม่ใช่สำหรับเซลล์
Image -
สูตรนี้อ้างอิงสองเซลล์ในรูปแบบต่างๆ B2 คือเซลล์ทางด้านซ้ายของเซลล์ภาษีขายแรก สัมพันธ์กับตำแหน่งของเซลล์นั้น การอ้างอิง A$12 ชี้ไปที่หมายเลขภาษีขายจริงที่แสดงใน A12 สัญลักษณ์ $ จะคงการอ้างอิงถึงค่าคงที่ของแถวที่ 12 โดยไม่คำนึงถึงทิศทางที่คุณเติมเซลล์ที่อยู่ติดกัน เติมเซลล์ทั้งหมดภายใต้เซลล์แรกนี้เพื่อดูการทำงานจริง
Image -
คุณจะเห็นว่าเมื่อคุณกรอกข้อมูลในคอลัมน์ เซลล์ในคอลัมน์ที่สองจะใช้การอ้างอิงเซลล์แบบสัมพัทธ์สำหรับค่าต้นทุนในคอลัมน์ B โดยเพิ่มคอลัมน์ให้ตรงกับคอลัมน์ภาษีขาย อย่างไรก็ตาม การอ้างอิงถึง A12 จะคงที่เนื่องจากสัญลักษณ์ $ ซึ่งทำให้การอ้างอิงแถวเหมือนกัน ตอนนี้เติมคอลัมน์ D โดยเริ่มจากเซลล์ C2
Image -
ตอนนี้ คุณจะเห็นว่าการกรอกแถวทางด้านขวาใช้ภาษีการขายของรัฐที่ถูกต้อง (อินเดียน่า) เนื่องจากการอ้างอิงคอลัมน์นั้นสัมพันธ์กัน (จะเลื่อนไปทางขวาเช่นเดียวกับเซลล์ที่คุณกำลังกรอก) อย่างไรก็ตาม การอ้างอิงแบบสัมพัทธ์สำหรับต้นทุนได้เลื่อนไปทางขวาแล้วเช่นกัน ซึ่งไม่ถูกต้อง คุณสามารถแก้ไขได้โดยสร้างสูตรดั้งเดิมใน C2 อ้างอิงคอลัมน์ B ด้วยการอ้างอิงแบบสัมบูรณ์แทน
Image -
โดยการวางสัญลักษณ์ $ ไว้หน้า "B" คุณได้สร้างการอ้างอิงแบบสัมบูรณ์สำหรับคอลัมน์ ตอนนี้เมื่อคุณกรอกไปทางขวา คุณจะเห็นว่าการอ้างอิงไปยังคอลัมน์ B สำหรับค่าใช้จ่ายยังคงเหมือนเดิม
Image -
ตอนนี้ เมื่อคุณเติมคอลัมน์ D ลง คุณจะเห็นว่าการอ้างอิงเซลล์แบบสัมพัทธ์และแบบสัมบูรณ์ทั้งหมดทำงานตรงตามที่คุณตั้งใจไว้
Image -
การอ้างอิงเซลล์แบบสัมบูรณ์ใน Excel มีความสำคัญสำหรับการควบคุมว่าจะอ้างอิงเซลล์ใดเมื่อคุณเติมคอลัมน์หรือแถว ใช้สัญลักษณ์ $ ตามที่คุณต้องการภายในการอ้างอิงเซลล์เพื่อคงค่าคงที่การอ้างอิงแถวหรือคอลัมน์ เพื่อที่จะอ้างอิงข้อมูลที่ถูกต้องในเซลล์ที่ถูกต้อง เมื่อคุณรวมการอ้างอิงเซลล์แบบสัมพัทธ์และแบบสัมบูรณ์ในลักษณะนี้ จะเรียกว่าการอ้างอิงเซลล์แบบผสม
หากคุณใช้การอ้างอิงแบบผสม การจัดแนวคอลัมน์หรือแถวของแหล่งข้อมูลให้ตรงกับคอลัมน์หรือแถวที่คุณพิมพ์สูตรเป็นสิ่งสำคัญ หากคุณทำให้การอ้างอิงแถวสัมพันธ์กัน โปรดจำไว้ว่าเมื่อคุณเติมข้อมูลด้านข้าง หมายเลขคอลัมน์ของข้อมูลต้นฉบับจะเพิ่มขึ้นพร้อมกับคอลัมน์ของเซลล์สูตร นี่เป็นหนึ่งในสิ่งที่ทำให้การกำหนดแอดเดรสแบบสัมพัทธ์และแบบสัมบูรณ์ซับซ้อนสำหรับผู้ใช้ใหม่ แต่เมื่อคุณเรียนรู้แล้ว คุณจะสามารถควบคุมการดึงเซลล์แหล่งข้อมูลไปยังการอ้างอิงเซลล์สูตรของคุณได้มากขึ้น
การใช้การอ้างอิงเซลล์แบบสัมบูรณ์เพื่อตรึงการอ้างอิงเซลล์เดียว
อีกวิธีหนึ่งในการใช้การอ้างอิงเซลล์แบบสัมบูรณ์คือการนำไปใช้กับทั้งคอลัมน์และแถวเพื่อ "ตรึง" สูตรโดยพื้นฐานแล้วให้ใช้เซลล์เพียงเซลล์เดียวไม่ว่าจะอยู่ที่ใด
ด้วยวิธีนี้ คุณสามารถเติมข้อมูลด้านข้างหรือด้านล่าง และการอ้างอิงเซลล์จะเหมือนเดิมเสมอ
การใช้การอ้างอิงเซลล์แบบสัมบูรณ์บนทั้งคอลัมน์และแถวจะมีผลก็ต่อเมื่อคุณอ้างถึงเซลล์เดียวในเซลล์ทั้งหมดที่คุณกำลังเติม
-
โดยใช้สเปรดชีตตัวอย่างเดียวกันจากด้านบน คุณสามารถอ้างอิงได้เฉพาะอัตราภาษีของรัฐเดียวโดยการเพิ่มสัญลักษณ์ $ ลงในคอลัมน์และการอ้างอิงแถว
Image -
ทำให้ทั้งคอลัมน์ "A" และแถว "12" คงที่ ไม่ว่าคุณจะเติมเซลล์ในทิศทางใด หากต้องการดูการดำเนินการนี้ ให้กรอกคอลัมน์ทั้งหมดสำหรับภาษีขาย ME หลังจากที่คุณได้อัปเดตสูตรด้วยคอลัมน์แบบสัมบูรณ์และการอ้างอิงแถว คุณจะเห็นว่าแต่ละเซลล์ที่เติมจะใช้การอ้างอิงแบบสัมบูรณ์ $A$12 เสมอ คอลัมน์หรือแถวไม่เปลี่ยนแปลง
Image -
ใช้สูตรเดียวกันสำหรับคอลัมน์ภาษีขายในรัฐอินเดียนา แต่คราวนี้ใช้การอ้างอิงแบบสัมบูรณ์สำหรับทั้งคอลัมน์และแถว ในกรณีนี้ นั่นคือ $B$12.
Image -
กรอกคอลัมน์นี้ แล้วคุณจะพบว่าการอ้างอิงถึง B12 จะไม่เปลี่ยนแปลงในทุกเซลล์ ต้องขอบคุณการอ้างอิงแบบสัมบูรณ์สำหรับทั้งคอลัมน์และแถว
Image -
อย่างที่คุณเห็น มีหลายวิธีที่คุณสามารถใช้การอ้างอิงเซลล์แบบสัมบูรณ์ใน Excel เพื่อทำงานที่คล้ายคลึงกันให้สำเร็จ การอ้างอิงแบบสัมบูรณ์ที่ให้คุณมีความยืดหยุ่นในการรักษาการอ้างอิงเซลล์คงที่แม้ว่าคุณจะกรอกคอลัมน์หรือแถวจำนวนมาก
คุณสามารถวนรอบการอ้างอิงเซลล์แบบสัมพัทธ์หรือแบบสัมบูรณ์โดยไฮไลต์การอ้างอิงแล้วกด F4 ทุกครั้งที่คุณกด F4 การอ้างอิงแบบสัมบูรณ์จะถูกนำไปใช้กับคอลัมน์ใดคอลัมน์หนึ่ง แถว ทั้งคอลัมน์และเซลล์ หรือไม่มีเลย นี่เป็นวิธีง่ายๆ ในการแก้ไขสูตรของคุณโดยไม่ต้องพิมพ์สัญลักษณ์ $
เมื่อคุณควรใช้การอ้างอิงเซลล์แบบสัมบูรณ์
ในเกือบทุกอุตสาหกรรมและทุกสาขา มีเหตุผลมากมายที่คุณอาจต้องการใช้การอ้างอิงเซลล์แบบสัมบูรณ์ใน Excel
- ใช้ตัวคูณคงที่ (เช่น ราคาต่อหน่วย) ในรายการขนาดใหญ่
- ใช้เปอร์เซ็นต์เดียวในแต่ละปีเมื่อคุณคาดการณ์เป้าหมายกำไรประจำปี
- เมื่อสร้างใบแจ้งหนี้ ให้ใช้การอ้างอิงแบบสัมบูรณ์เพื่ออ้างถึงอัตราภาษีเดียวกันในทุกรายการ
- ใช้การอ้างอิงเซลล์แบบสัมบูรณ์ในการจัดการโครงการเพื่ออ้างถึงอัตราความพร้อมใช้งานคงที่สำหรับทรัพยากรแต่ละรายการ
- ใช้การอ้างอิงคอลัมน์แบบสัมพัทธ์และการอ้างอิงแถวแบบสัมบูรณ์เพื่อจับคู่การคำนวณคอลัมน์ในเซลล์ที่อ้างอิงของคุณกับค่าคอลัมน์ในตารางอื่น
หากคุณใช้การอ้างอิงแบบสัมพัทธ์และแบบสัมบูรณ์สำหรับคอลัมน์หรือแถว คุณเพียงแค่ต้องตรวจสอบให้แน่ใจว่าตำแหน่งของคอลัมน์หรือแถวข้อมูลต้นฉบับตรงกับคอลัมน์หรือแถวของเซลล์ปลายทาง (ที่คุณ' พิมพ์สูตรใหม่อีกครั้ง).
