การอ้างอิงแบบผสมสำหรับตาราง

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

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

การอ้างอิงแบบผสมมีอยู่สองรูปแบบเท่านั้น:

• $A1 ล็อกคอลัมน์ แต่แถวไม่ถูกล็อก
• A$1 ล็อกแถว แต่คอลัมน์ไม่ถูกล็อก

จำกฎนี้ไว้: เครื่องหมายดอลลาร์จะล็อกสิ่งที่อยู่ถัดจากมันทันที

$A1 จะอยู่ในคอลัมน์ A เสมอ แต่แถวสามารถเปลี่ยนได้ คัดลอกลงด้านล่างแล้วจะได้ $A2 และ $A3 คัดลอกไปทางขวาแล้วจะยังคงเป็น $A1 เพราะคอลัมน์ถูกล็อกไว้

ใช้รูปแบบนี้เมื่อทุกคอลัมน์ในตารางต้องอ่านค่าจากคอลัมน์นำเข้าเดียวกัน เช่น ป้ายกำกับหรือค่าฐานที่อยู่ทางด้านซ้าย

A$1 จะอยู่ในแถว 1 เสมอ แต่คอลัมน์สามารถเปลี่ยนได้ คัดลอกไปทางขวาแล้วจะได้ B$1 และ C$1 คัดลอกลงด้านล่างแล้วจะยังคงเป็น A$1 เพราะแถวถูกล็อกไว้

ใช้รูปแบบนี้เมื่อทุกแถวในตารางต้องอ่านค่าจากแถวหัวตารางเดียวกันที่อยู่ด้านบน

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

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

สมมติว่ายอดขายเรียงลงมาในคอลัมน์ A (ใน A2, A3, ...) และอัตราค่าคอมมิชชันเรียงไปตามแถวที่ 1 (ใน B1, C1, ...) หากต้องการเติมข้อมูลให้ทั้งตาราง ให้เขียนสูตรใน B2:

=$A2*B$1

$A2 จะอ้างอิงคอลัมน์ยอดขายเสมอ ส่วน B$1 จะอ้างอิงแถวอัตราค่าคอมมิชชันเสมอ

คัดลอก =$A2*B$1 จาก B2 ลงไปยัง B3:

• $A2 เปลี่ยนเป็น $A3 (แถวไม่ถูกล็อก จึงเลื่อนลงด้านล่าง)
• B$1 ยังคงเป็น B$1 (แถวถูกล็อกไว้)

ดังนั้น B3 จึงอ่านค่าเป็น =$A3*B$1 ซึ่งหมายถึงยอดขายถัดไปคูณด้วยอัตราแรกเดียวกัน ถูกต้องพอดี

ตอนนี้คัดลอก =$A2*B$1 จาก B2 ไปทางขวายัง C2:

• $A2 ยังคงเป็น $A2 (คอลัมน์ถูกล็อกไว้)
• B$1 เปลี่ยนเป็น C$1 (คอลัมน์ไม่ถูกล็อก จึงเลื่อนไปทางขวา)

ดังนั้น C2 จึงอ่านค่าเป็น =$A2*C$1 ซึ่งหมายถึงยอดขายเดิมคูณด้วยอัตราถัดไป สูตรเดียวจึงปรับตามได้ทั้งสองทิศทาง

หากต้องการตัดสินใจว่าจะวางเครื่องหมายดอลลาร์ไว้ที่ใด ให้ถามว่าการอ้างอิงต้อง คงอยู่ ในทิศทางใดขณะเติมข้อมูลให้ตาราง:

• ต้องอยู่ในคอลัมน์เดียวกันใช่ไหม ให้ล็อกคอลัมน์: $A2
• ต้องอยู่ในแถวเดียวกันใช่ไหม ให้ล็อกแถว: B$1

ล็อกส่วนที่ไม่ควรเลื่อน และปล่อยส่วนที่ควรเลื่อนให้ไม่ถูกล็อก

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

สูตรค้นหาใช้ $A2 สำหรับเมืองในแถว และใช้ B$1 สำหรับเมืองในคอลัมน์ ดังนั้นสูตรเดียวจึงเติมข้อมูลให้ทั้งตารางได้เหมือนกับตารางสูตรคูณ

การกด F4 ซ้ำ ๆ จะสลับผ่านรูปแบบทั้งสี่แบบ ทำให้คุณเลือกใช้รูปแบบผสมแบบใดแบบหนึ่งได้:

• กดครั้งที่ 1: $A$1
• กดครั้งที่ 2: A$1 (ล็อกแถว)
• กดครั้งที่ 3: $A1 (ล็อกคอลัมน์)
• กดครั้งที่ 4: A1

หยุดกดเมื่อถึงรูปแบบที่ล็อกส่วนที่คุณต้องการ

ตรวจสอบความเข้าใจเรื่องการอ้างอิงแบบผสม

การอ้างอิงแบบผสมจะล็อกส่วนหนึ่งและปล่อยให้อีกส่วนเลื่อนได้:

• $A1 ล็อกคอลัมน์ แถวจะเลื่อน
• A$1 ล็อกแถว คอลัมน์จะเลื่อน

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