Excel 2019 (ตอนที่ 14): การอ้างอิงเซลล์แบบสัมพัทธ์และแบบสัมบูรณ์

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

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

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

วิธีสร้างและคัดลอกสูตรโดยใช้การอ้างอิงแบบสัมพันธ์

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

1. เลือกเซลล์ที่จะมีสูตร ตัวอย่างเช่น เราจะเลือกเซลล์D4

2. กรอกสูตรเพื่อคำนวณค่าที่ต้องการ ตัวอย่างเช่น ป้อน=B4*C4

3. กดEnterบนแป้นพิมพ์ สูตรจะถูกคำนวณและผลลัพธ์จะแสดงในเซลล์

4. ค้นหาจุดจับเติมที่มุมขวาล่างของเซลล์ที่ต้องการ ตัวอย่างจะกำหนดตำแหน่งตัวจัดการเติมสำหรับเซลล์D4

5. คลิกและลากจุดจับเติมไปยังเซลล์ที่คุณต้องการเติม ตัวอย่างเช่น เราจะเลือกเซลล์D5:D13

6. ปล่อยเมาส์ สูตรจะถูกคัดลอกลงในเซลล์ที่เลือกโดยมีการอ้างอิงแบบสัมพันธ์ โดยแสดงผลลัพธ์ในแต่ละเซลล์

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

การอ้างอิงที่แน่นอน

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

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

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

เมื่อเขียนสูตร คุณสามารถกด ปุ่ม F4บนแป้นพิมพ์เพื่อสลับระหว่างการอ้างอิงเซลล์แบบสัมพันธ์และแบบสัมบูรณ์ ดังที่แสดงในวิดีโอด้านล่าง นี่เป็นวิธีง่ายๆ ในการแทรกการอ้างอิงแบบสัมบูรณ์อย่างรวดเร็ว

วิธีสร้างและคัดลอกสูตรโดยใช้การอ้างอิงแบบสัมบูรณ์

ตัวอย่างด้านล่างนี้จะใช้เซลล์E2 (ซึ่งมีอัตราภาษี 7.5%) ในการคำนวณภาษีการขายสำหรับแต่ละรายการในคอลัมน์D เพื่อให้แน่ใจว่าการอ้างอิงไปยังอัตราภาษียังคงเหมือนเดิม - แม้ว่าสูตรจะถูกคัดลอกและเติมลงในเซลล์อื่น - เซลล์$E$2จำเป็นต้องตั้งค่าเป็นข้อมูลอ้างอิงแบบสัมบูรณ์

1. เลือกเซลล์ที่จะมีสูตร ตัวอย่างเช่น เราจะเลือกเซลล์D4

2. กรอกสูตรเพื่อคำนวณค่าที่ต้องการ ตัวอย่างจะป้อน=(B4*C4)*$E$2ทำให้$E$2เป็นข้อมูลอ้างอิงที่แน่นอน

3. กดEnterบนแป้นพิมพ์ สูตรจะคำนวณและผลลัพธ์จะแสดงในเซลล์

4. ค้นหาจุดจับเติมที่มุมขวาล่างของเซลล์ที่ต้องการ ตัวอย่างจะกำหนดตำแหน่งตัวจัดการเติมสำหรับเซลล์D4

5. คลิกและลากจุดจับเติมไปยังเซลล์ที่คุณต้องการเติม (เซลล์D5: D13ในตัวอย่าง)

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

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

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

ใช้การอ้างอิงเซลล์กับแผ่นงานหลายแผ่น

Excel ให้คุณอ้างอิงเซลล์ใดก็ได้ในเวิร์กชีตใดก็ได้ ซึ่งจะมีประโยชน์อย่างยิ่งหากคุณต้องการอ้างอิงค่าเฉพาะจากเวิร์กชีตหนึ่งไปยังอีกเวิร์กชีตหนึ่ง ในการดำเนินการนี้ เพียงเริ่มการอ้างอิงเซลล์ด้วยชื่อชีต ตามด้วยเครื่องหมายอัศเจรีย์ ( ! ) ตัวอย่างเช่น ถ้าคุณต้องการอ้างอิงเซลล์A1บนSheet1การอ้างอิงเซลล์จะเป็นSheet1!A1

โปรดทราบว่าหากชื่อแผ่นงานมีการเว้นวรรค คุณจะต้องใส่เครื่องหมายคำพูด ( '' ) ล้อมรอบชื่อ ตัวอย่างเช่น หากคุณต้องการอ้างอิงเซลล์A1บนเวิร์กชีตชื่อJuly Budgetการอ้างอิงเซลล์จะเป็น'July Budget'!A1

วิธีอ้างอิงเซลล์บนแผ่นงาน

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

1. ค้นหาเซลล์ที่คุณต้องการอ้างอิงและจดบันทึกแผ่นงาน ตัวอย่างเช่น เราต้องการอ้างอิงเซลล์E14 บน แผ่นงานลำดับเมนู

2. นำทางไปยังแผ่นงานที่ต้องการ ตัวอย่างเช่น เราจะเลือกสเปรดชีตใบแจ้งหนี้การจัดเลี้ยง

3. ค้นหาและเลือกเซลล์ที่คุณต้องการให้ค่าปรากฏ ตัวอย่างเช่น เราจะเลือกเซลล์C4

4. ป้อนเครื่องหมายเท่ากับ ( = ) ชื่อเวิร์กชีตตามด้วยเครื่องหมายอัศเจรีย์ (!) และที่อยู่ของเซลล์ ตัวอย่างเช่น ป้อน='ลำดับเมนู'!E14

5. กดEnterบนแป้นพิมพ์ ค่าของเซลล์ที่อ้างอิงจะปรากฏขึ้น ในตอนนี้ หากค่าของเซลล์E14เปลี่ยนแปลงใน เวิร์กชีต ลำดับเมนู ค่าดังกล่าวจะได้รับการอัปเดตโดยอัตโนมัติในเวิร์กชีต Catering Invoice

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

หากคุณป้อนชื่อชีตผิด#REF! ข้อผิดพลาดจะปรากฏขึ้นในเซลล์ ในตัวอย่างด้านล่าง ชื่อของแผ่นงานสะกดผิด หากต้องการแก้ไข เพิกเฉย หรือตรวจสอบข้อผิดพลาด ให้คลิก ปุ่ม ข้อผิดพลาดถัดจากเซลล์แล้วเลือกตัวเลือกจากเมนู