การสร้างตารางสูตรคูณ

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

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

ขั้นแรกให้จัดวางหัวตาราง ใส่ตัวเลข 1 ถึง 10 ไปตามด้านบนในแถวที่ 1 ตั้งแต่ B1 ถึง K1 จากนั้นใส่ตัวเลข 1 ถึง 10 เรียงลงมาตามด้านข้างในคอลัมน์ A ตั้งแต่ A2 ถึง A11

เซลล์ที่แถวกับคอลัมน์ตัดกัน เช่น B2 จะเก็บผลคูณของป้ายกำกับแถวกับหัวคอลัมน์ของเซลล์นั้น

ลองดูเซลล์ B2 เซลล์นี้ควรคูณ ป้ายกำกับแถว ในคอลัมน์ A กับ หัวคอลัมน์ ในแถวที่ 1

• ป้ายกำกับแถวอยู่ใน A2 (อยู่ในคอลัมน์ A เสมอ)
• หัวคอลัมน์อยู่ใน B1 (อยู่ในแถวที่ 1 เสมอ)

ดังนั้น B2 คือ A2 * B1 แต่เราต้องล็อกส่วนที่ถูกต้อง เพื่อให้คัดลอกสูตรได้อย่างถูกต้อง

ป้ายกำกับแถวต้องมาจาก คอลัมน์ A เสมอ แต่แถวควรเปลี่ยนตามแต่ละแถวของตาราง ดังนั้นเราจะล็อกเฉพาะคอลัมน์:

$A2

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

หัวคอลัมน์ต้องมาจาก แถวที่ 1 เสมอ แต่คอลัมน์ควรเปลี่ยนตามแต่ละคอลัมน์ของตาราง ดังนั้นเราจะล็อกเฉพาะแถว:

B$1

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

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

=$A2*B$1

เพียงเท่านี้ สูตรเดียวที่คัดลอกไปยังเซลล์ทั้ง 100 เซลล์ก็จะสร้างตารางสูตรคูณได้ครบถ้วน

เลือก B2 แล้วคัดลอก จากนั้นเลือกตารางทั้งหมดตั้งแต่ B2:K11 แล้ววาง หรือจะลากจุดจับเติมข้อมูลไปทางขวาแล้วลากลงด้านล่างก็ได้

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

คลิกเซลล์ D5 เพื่อตรวจสอบ สูตรของเซลล์นี้คือ:

=$A5*D$1

คอลัมน์ A เลื่อนลงมาถึงแถวที่ 5 (ป้ายกำกับคือ 4) และแถวที่ 1 เลื่อนไปทางขวาถึงคอลัมน์ D (หัวคอลัมน์คือ 3) ดังนั้น 4 * 3 = 12 การอ้างอิงแบบผสมจึงชี้ไปยังข้อมูลนำเข้าที่ถูกต้องพอดี

ลองใช้สูตรแบบง่าย =A2*B1 โดยไม่ใส่เครื่องหมายดอลลาร์ คัดลอกไปทางขวายัง C2 แล้วสูตรจะกลายเป็น =B2*C1 ซึ่งตอนนี้เป็นการคูณเซลล์ในตารางกับหัวตาราง จึงไม่สมเหตุสมผล

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

วิธีรวดเร็วในการตรวจสอบว่าทั้งตารางถูกต้องคือเช็กมุมตาราง เซลล์ B2 ควรมีค่าเป็น 1*1=1 และมุมไกลสุด K11 ควรมีค่าเป็น 10*10=100

คลิก K11 แล้วสูตรจะเป็น =$A11*K$1 คอลัมน์ A เลื่อนมาถึงแถวที่ 11 (ป้ายกำกับคือ 10) และแถวที่ 1 เลื่อนไปถึงคอลัมน์ K (หัวคอลัมน์คือ 10) หากมุมทั้งสองถูกต้อง การอ้างอิงแบบผสมก็ทำงานถูกต้องทั่วทั้งตาราง

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

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

ตรวจสอบตารางสูตรคูณเป็นครั้งสุดท้าย

คุณสร้างตารางสูตรคูณทั้งหมดจากสูตรเดียว: =$A2*B$1

• $A2 ล็อกคอลัมน์ เพื่อให้อ้างอิงป้ายกำกับแถวเสมอ
• B$1 ล็อกแถว เพื่อให้อ้างอิงหัวคอลัมน์เสมอ
• คัดลอกเพียงครั้งเดียวเพื่อเติมข้อมูลให้ทั้งตาราง

รูปแบบการอ้างอิงแบบผสมนี้ใช้สร้างตารางที่มีข้อมูลสองทิศทางได้ทุกประเภท ตอนนี้คุณเข้าใจการอ้างอิงแบบสัมบูรณ์ แบบสัมพัทธ์ และแบบผสมแล้ว