Excel 2016 - บทที่ 15: การอ้างอิงเซลล์แบบสัมพัทธ์และแบบสัมบูรณ์

• Excel 2016 (ตอนที่ 12): จัดรูปแบบหน้าและพิมพ์สเปรดชีต
• Excel 2016 (ตอนที่ 13): สูตรเบื้องต้น
• Excel 2016 (ตอนที่ 14): สร้างสูตรที่ซับซ้อน

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

โปรดเข้าร่วม LuckyTemplates ในบทความนี้พร้อมคำแนะนำเกี่ยวกับการอ้างอิงเซลล์แบบสัมพันธ์และแบบสัมบูรณ์ใน Excel 2016

การอ้างอิงเซลล์แบบสัมพัทธ์และแบบสัมบูรณ์ใน Excel

• การอ้างอิงแบบสัมพัทธ์
  • สร้างและคัดลอกสูตรที่ใช้การอ้างอิงแบบสัมพันธ์
• การอ้างอิงที่แน่นอน
  • สร้างและคัดลอกสูตรโดยการอ้างอิงแบบสัมบูรณ์
• ใช้การอ้างอิงเซลล์กับแผ่นงานหลายแผ่น
  • อ้างอิงเซลล์บนแผ่นงาน

ดูวิดีโอด้านล่างเพื่อเรียนรู้เพิ่มเติมเกี่ยวกับการอ้างอิงเซลล์:

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

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

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

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

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. พิมพ์เครื่องหมายเท่ากับ (=)ชื่อตารางตามด้วยเครื่องหมายอัศเจรีย์ (!)และที่อยู่ของเซลล์ ในตัวอย่าง เราจะพิมพ์='MenuOrder'! E14

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

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

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

อ้างถึงบทความเพิ่มเติมบางส่วน:

• Excel 2016 (ตอนที่ 3): วิธีสร้างและเปิดสเปรดชีตที่มีอยู่
• Excel 2016 (ตอนที่ 4): วิธีจัดเก็บและแชร์สเปรดชีต
• Excel 2016 (ตอนที่ 5): พื้นฐานของเซลล์และช่วง

ขอให้สนุก!