ไปยังเนื้อหาหลัก

การอ้างอิงแบบสัมบูรณ์ของ Excel (วิธีทำและใช้งาน)

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

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


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

 


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

 

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

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

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

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

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

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

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

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

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

 

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

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

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

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

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

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

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

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

การอ้างอิงสัมบูรณ์ f4 สลับ 1

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

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

การอ้างอิงสัมบูรณ์ f4 สลับ 2


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

 

ส่วนนี้มี 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 ซึ่งคุณจะคูณกัน

เอกสารอ้างอิงสัมบูรณ์ 15 1

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

เอกสารอ้างอิงสัมบูรณ์ 16 1

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

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

=$B3*C2

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

เอกสารอ้างอิงสัมบูรณ์ 17 1

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

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

เอกสารอ้างอิงสัมบูรณ์ 18 1

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

=$B3*C$2

เอกสารอ้างอิงสัมบูรณ์ 19 1

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

เอกสารอ้างอิงสัมบูรณ์ 20 1


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

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

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

    $A1/A$1

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

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

สุดยอดเครื่องมือเพิ่มผลผลิตในสำนักงาน

🤖 Kutools AI ผู้ช่วย: ปฏิวัติการวิเคราะห์ข้อมูลโดยยึดตาม: การดำเนินการที่ชาญฉลาด   |  สร้างรหัส  |  สร้างสูตรที่กำหนดเอง  |  วิเคราะห์ข้อมูลและสร้างแผนภูมิ  |  เรียกใช้ฟังก์ชัน Kutools...
คุณสมบัติยอดนิยม: ค้นหา เน้น หรือระบุรายการที่ซ้ำกัน   |  ลบแถวว่าง   |  รวมคอลัมน์หรือเซลล์โดยไม่สูญเสียข้อมูล   |   รอบโดยไม่มีสูตร ...
การค้นหาขั้นสูง: VLookup หลายเกณฑ์    VLookup หลายค่า  |   VLookup ข้ามหลายแผ่น   |   การค้นหาที่ไม่ชัดเจน ....
รายการแบบเลื่อนลงขั้นสูง: สร้างรายการแบบหล่นลงอย่างรวดเร็ว   |  รายการแบบหล่นลงขึ้นอยู่กับ   |  เลือกหลายรายการแบบหล่นลง ....
ผู้จัดการคอลัมน์: เพิ่มจำนวนคอลัมน์เฉพาะ  |  ย้ายคอลัมน์  |  สลับสถานะการมองเห็นของคอลัมน์ที่ซ่อนอยู่  |  เปรียบเทียบช่วงและคอลัมน์ ...
คุณสมบัติเด่น: กริดโฟกัส   |  มุมมองการออกแบบ   |   บาร์สูตรใหญ่    สมุดงานและตัวจัดการชีต   |  ห้องสมุดทรัพยากร (ข้อความอัตโนมัติ)   |  เลือกวันที่   |  รวมแผ่นงาน   |  เข้ารหัส/ถอดรหัสเซลล์    ส่งอีเมลตามรายการ   |  ซุปเปอร์ฟิลเตอร์   |   ตัวกรองพิเศษ (กรองตัวหนา/ตัวเอียง/ขีดทับ...) ...
ชุดเครื่องมือ 15 อันดับแรก12 ข้อความ เครื่องมือ (เพิ่มข้อความ, ลบอักขระ, ... )   |   50 + แผนภูมิ ประเภท (แผนภูมิ Gantt, ... )   |   40+ ใช้งานได้จริง สูตร (คำนวณอายุตามวันเกิด, ... )   |   19 การแทรก เครื่องมือ (ใส่ QR Code, แทรกรูปภาพจากเส้นทาง, ... )   |   12 การแปลง เครื่องมือ (ตัวเลขเป็นคำ, การแปลงสกุลเงิน, ... )   |   7 ผสานและแยก เครื่องมือ (แถวรวมขั้นสูง, แยกเซลล์, ... )   |   ... และอื่น ๆ

เพิ่มพูนทักษะ Excel ของคุณด้วย Kutools สำหรับ Excel และสัมผัสประสิทธิภาพอย่างที่ไม่เคยมีมาก่อน Kutools สำหรับ Excel เสนอคุณสมบัติขั้นสูงมากกว่า 300 รายการเพื่อเพิ่มประสิทธิภาพและประหยัดเวลา  คลิกที่นี่เพื่อรับคุณสมบัติที่คุณต้องการมากที่สุด...

รายละเอียด


แท็บ Office นำอินเทอร์เฟซแบบแท็บมาที่ Office และทำให้งานของคุณง่ายขึ้นมาก

  • เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
  • เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
  • เพิ่มประสิทธิภาพการทำงานของคุณ 50% และลดการคลิกเมาส์หลายร้อยครั้งให้คุณทุกวัน!