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

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

ดาวน์โหลดไฟล์ตัวอย่างได้ฟรี

วิดีโอ: การอ้างอิงแบบสัมบูรณ์

การอ้างอิงแบบสัมบูรณ์คืออะไร

การอ้างอิงแบบสัมบูรณ์คือประเภทของการอ้างอิงเซลล์ใน Excel

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

การอ้างอิงแบบสัมบูรณ์ถูกสร้างขึ้นโดยการเพิ่มเครื่องหมายดอลลาร์ ($) ก่อนการอ้างอิงคอลัมน์และแถวในสูตร ตัวอย่างเช่น หากต้องการสร้างข้อมูลอ้างอิงแบบสัมบูรณ์สำหรับเซลล์ A1 คุณควรแสดงเป็น $A$1

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

ตัวอย่างเช่น ช่วง A4:C7 มีราคาของผลิตภัณฑ์ และคุณต้องการรับภาษีที่ต้องชำระของแต่ละผลิตภัณฑ์ตามอัตราภาษีในเซลล์ B2

หากคุณใช้การอ้างอิงสัมพัทธ์ในสูตร เช่น "=B5*B2" ผลลัพธ์ที่ไม่ถูกต้องบางอย่างจะถูกส่งกลับเมื่อคุณลากที่จับการเติมอัตโนมัติลงเพื่อใช้สูตรนี้ เนื่องจากการอ้างอิงไปยังเซลล์ B2 จะเปลี่ยนไปเมื่อเทียบกับเซลล์ในสูตร ตอนนี้ สูตรในเซลล์ C6 คือ "=B6*B3" และสูตรในเซลล์ C7 คือ "=B7*B4"

แต่ถ้าคุณใช้การอ้างอิงแบบสัมบูรณ์ไปยังเซลล์ B2 ด้วยสูตร "=B5*$B$2" จะทำให้แน่ใจว่าอัตราภาษียังคงเหมือนเดิมสำหรับทุกเซลล์ เมื่อสูตรถูกลากลงโดยใช้ที่จับเติมอัตโนมัติ ผลลัพธ์จะถูกต้อง

โดยใช้การอ้างอิงแบบสัมพัทธ์		โดยใช้การอ้างอิงแบบสัมบูรณ์

วิธีสร้างการอ้างอิงแบบสัมบูรณ์

หากต้องการอ้างอิงแบบสัมบูรณ์ใน Excel คุณต้องใส่เครื่องหมายดอลลาร์ ($) ก่อนการอ้างอิงคอลัมน์และแถวในสูตร มีสองวิธีในการสร้างการอ้างอิงแบบสัมบูรณ์:

เพิ่มเครื่องหมายดอลลาร์ในการอ้างอิงเซลล์ด้วยตนเอง

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

ตัวอย่างเช่น ถ้าคุณต้องการบวกตัวเลขในเซลล์ A1 และ B1 และทำให้ทั้งคู่เป็นค่าสัมบูรณ์ ให้พิมพ์สูตรเป็น "=$A$1+$B$1" วิธีนี้จะช่วยให้มั่นใจได้ว่าการอ้างอิงเซลล์จะคงที่เมื่อสูตรถูกคัดลอกหรือย้ายไปยังเซลล์อื่น

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

ใช้ทางลัด F4 เพื่อแปลงการอ้างอิงแบบสัมพัทธ์เป็นแบบสัมบูรณ์

1. ดับเบิลคลิกเซลล์ที่มีสูตรเพื่อเข้าสู่โหมดแก้ไข
2. วางเคอร์เซอร์ที่การอ้างอิงเซลล์ที่คุณต้องการทำให้เป็นค่าสัมบูรณ์
3. ข่าวประชา F4 บนแป้นพิมพ์เพื่อสลับประเภทการอ้างอิงจนกว่าจะมีการเพิ่มเครื่องหมายดอลลาร์ก่อนการอ้างอิงทั้งคอลัมน์และแถว
4. ข่าวประชา เข้าสู่ ปุ่มเพื่อออกจากโหมดแก้ไขและใช้การเปลี่ยนแปลง

ปุ่ม F4 สามารถสลับการอ้างอิงระหว่างการอ้างอิงแบบสัมพัทธ์ การอ้างอิงแบบสัมบูรณ์ และการอ้างอิงแบบผสม

A1 → $A$1 → A$1 → $A1 → A1

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

A1+B1 → $A$1+$B$1 → A$1+B$1 → $A1+$B1 → A1+B1

ใช้การอ้างอิงแบบสัมบูรณ์พร้อมตัวอย่าง

ส่วนนี้มี 2 ตัวอย่างเพื่อแสดงเวลาและวิธีการใช้การอ้างอิงแบบสัมบูรณ์ในสูตร Excel

ตัวอย่างที่ 1 คำนวณเปอร์เซ็นต์ของผลรวม

สมมติว่าคุณมีช่วงข้อมูล (A3:B7) ที่มียอดขายของผลไม้แต่ละชนิด และเซลล์ B8 มียอดขายรวมของผลไม้เหล่านี้ ตอนนี้คุณต้องการคำนวณเปอร์เซ็นต์ของการขายผลไม้แต่ละอย่างของผลรวม

สูตรทั่วไปในการคำนวณเปอร์เซ็นต์ของผลรวม:

Percentage = Sale/Amount

ใช้การอ้างอิงสัมพัทธ์ในสูตรเพื่อรับเปอร์เซ็นต์ของผลไม้แรกดังนี้:

=B4/B8

เมื่อลากจุดจับเติมอัตโนมัติลงเพื่อคำนวณเปอร์เซ็นต์ของผลไม้อื่น #DIV/0! ข้อผิดพลาดจะถูกส่งกลับ

เนื่องจากเมื่อคุณลากที่จับเติมอัตโนมัติเพื่อคัดลอกสูตรไปยังเซลล์ด้านล่าง การอ้างอิงสัมพัทธ์ B8 จะถูกปรับโดยอัตโนมัติเป็นการอ้างอิงเซลล์อื่นๆ (B9, B10, B11) ตามตำแหน่งสัมพัทธ์ และเซลล์ B9, B10 และ B11 ว่างเปล่า (ศูนย์) เมื่อตัวหารเป็นศูนย์ สูตรกลับเป็นข้อผิดพลาด

ในการแก้ไขข้อผิดพลาด ในกรณีนี้ คุณต้องทำให้การอ้างอิงเซลล์ B8 แบบสัมบูรณ์ ($B$8) ในสูตรเพื่อไม่ให้เกิดการเปลี่ยนแปลงเมื่อคุณย้ายหรือคัดลอกสูตรไปที่ใดก็ได้ ตอนนี้สูตรได้รับการปรับปรุงเป็น:

=B4/$B$8

จากนั้นลากที่จับเติมอัตโนมัติลงเพื่อคำนวณเปอร์เซ็นต์ของผลไม้อื่นๆ

ตัวอย่างที่ 2 ค้นหาค่าและกลับไปที่ค่าที่ตรงกัน

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

สูตรทั่วไปในการค้นหาคือ:

=VLOOKUP(lookup_value, table_range, column_index, logical)

หากคุณใช้การอ้างอิงสัมพัทธ์ในสูตรเพื่อค้นหาค่าและส่งกลับค่าที่ตรงกันดังนี้:

=VLOOKUP(D4,A4:B8,2,FALSE)

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

เมื่อคุณลากที่จับเติมลงเพื่อคัดลอกสูตรไปยังเซลล์ด้านล่าง การอ้างอิงในสูตรจะปรับลงโดยอัตโนมัติหนึ่งแถว ด้วยเหตุนี้ การอ้างอิงถึงช่วงตาราง A4:B8 จึงกลายเป็น A5:B9 เนื่องจากไม่พบ "Lisa: ในช่วง A5:B9 สูตรจึงส่งกลับข้อผิดพลาด

เพื่อหลีกเลี่ยงข้อผิดพลาด ให้ใช้การอ้างอิงสัมบูรณ์ $A$4:$B$8 แทนการอ้างอิงสัมพัทธ์ A4:B8 ในสูตร:

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

จากนั้นลากที่จับเติมอัตโนมัติลงเพื่อรับเงินเดือนของ Lisa

2 คลิกเพื่อแบทช์ทำให้การอ้างอิงเซลล์สมบูรณ์ด้วย Kutools

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

เลือกเซลล์สูตรที่คุณต้องการสร้างการอ้างอิงเซลล์แบบสัมบูรณ์ คลิก Kutools > เพิ่มเติม (fx) > แปลงอ้างอิง. จากนั้นเลือกไฟล์ เป็นค่าสัมบูรณ์ ตัวเลือกและคลิก Ok or ใช้. ตอนนี้การอ้างอิงเซลล์ทั้งหมดของสูตรที่เลือกได้ถูกแปลงเป็นแบบสัมบูรณ์

การอ้างอิงแบบสัมพัทธ์และการอ้างอิงแบบผสม

นอกเหนือจากการอ้างอิงแบบสัมบูรณ์แล้ว ยังมีการอ้างอิงอีกสองประเภท ได้แก่ การอ้างอิงแบบสัมพัทธ์และการอ้างอิงแบบผสม

การอ้างอิงแบบสัมพัทธ์ เป็นประเภทการอ้างอิงเริ่มต้นใน Excel ซึ่งไม่มีเครื่องหมายดอลลาร์ ($) ก่อนการอ้างอิงแถวและคอลัมน์ และเมื่อสูตรที่มีการอ้างอิงสัมพัทธ์ถูกคัดลอกหรือย้ายไปยังเซลล์อื่น การอ้างอิงจะเปลี่ยนโดยอัตโนมัติตามตำแหน่งสัมพัทธ์

ตัวอย่างเช่น เมื่อคุณพิมพ์สูตรในเซลล์ เช่น "=A1+1" แล้วลากแฮนเดิลป้อนอัตโนมัติลงเพื่อเติมสูตรนี้ในเซลล์ถัดไป สูตรจะเปลี่ยนเป็น "=A2+1" โดยอัตโนมัติ

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

ใช้ตารางสูตรคูณเป็นตัวอย่าง แถวและคอลัมน์แสดงตัวเลขตั้งแต่ 1 ถึง 9 ซึ่งคุณจะคูณกัน

ในการเริ่มต้น คุณสามารถใช้สูตร "=B3*C2" ในเซลล์ C3 เพื่อคูณ 1 ในเซลล์ B3 ด้วยตัวเลข (1) ในคอลัมน์แรก อย่างไรก็ตาม เมื่อคุณลากจุดจับป้อนอัตโนมัติไปทางขวาเพื่อเติมเซลล์อื่นๆ คุณจะสังเกตเห็นว่าผลลัพธ์ทั้งหมดไม่ถูกต้อง ยกเว้นเซลล์แรก

นี่เป็นเพราะเมื่อคุณคัดลอกสูตรไปทางขวา ตำแหน่งแถวจะไม่เปลี่ยนแปลง แต่ตำแหน่งคอลัมน์จะเปลี่ยนจาก B3 เป็น C3, D3 เป็นต้น ดังนั้น สูตรในเซลล์ด้านขวา (D3, E3, ฯลฯ) เปลี่ยนเป็น "=C3*D2", "=D3*E2" และอื่นๆ เมื่อคุณต้องการให้เป็น "=B3*D2", "=B3*E2" และอื่นๆ

ในกรณีนี้ คุณต้องเพิ่มเครื่องหมายดอลลาร์ ($) เพื่อล็อกการอ้างอิงคอลัมน์ของ “B3” ใช้สูตรดังนี้

=$B3*C2

ตอนนี้เมื่อคุณลากสูตรไปทางขวา ผลลัพธ์จะถูกต้อง

จากนั้นคุณต้องคูณจำนวน 1 ในเซลล์ C2 ด้วยตัวเลขในแถวด้านล่าง

เมื่อคุณคัดลอกสูตรลงมา ตำแหน่งคอลัมน์ของเซลล์ C2 จะไม่เปลี่ยนแปลง แต่ตำแหน่งแถวจะเปลี่ยนจาก C2 เป็น C3, C4 เป็นต้น ด้วยเหตุนี้ สูตรในเซลล์ด้านล่างจึงเปลี่ยนเป็น "=$B4C3" "=$B5C4" ฯลฯ ซึ่งจะให้ผลลัพธ์ที่ไม่ถูกต้อง

ในการแก้ปัญหานี้ ให้เปลี่ยน "C2" เป็น "C$2" เพื่อป้องกันไม่ให้การอ้างอิงแถวเปลี่ยนเมื่อลากที่จับเติมอัตโนมัติลงเพื่อเติมสูตร

=$B3*C$2

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

สิ่งที่ต้องจำ

• สรุปการอ้างอิงเซลล์

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

• โดยทั่วไป การอ้างอิงแบบสัมบูรณ์จะไม่เปลี่ยนแปลงเมื่อมีการย้ายสูตร อย่างไรก็ตาม การอ้างอิงแบบสัมบูรณ์จะปรับโดยอัตโนมัติเมื่อมีการเพิ่มหรือลบแถวหรือคอลัมน์จากด้านบนหรือด้านซ้ายในเวิร์กชีต ตัวอย่างเช่น ในสูตร "=$A$1+1" เมื่อคุณแทรกแถวที่ด้านบนของแผ่นงาน สูตรจะเปลี่ยนเป็น "=$A$2+1" โดยอัตโนมัติ

  

• พื้นที่ F4 คีย์สามารถสลับไปมาระหว่างการอ้างอิงแบบสัมพัทธ์ การอ้างอิงแบบสัมบูรณ์ และการอ้างอิงแบบผสม