บทเรียนที่ 15

หลักการอ้างอิงเซลล์และแทนที่สูตร

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

รูปแบบการอ้างอิงเซลล์

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

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

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

จากตารางเมื่อมีการใช้สูตร SUM เพื่อหาผลรวมของยอดขายสินค้าในแต่ละวัน โดยให้มีการอ้างอิงช่วง B2:D2 ซึ่งเป็นการอ้างอิงแบบสัมพัทธ์ ถ้าเราต้องการผลรวมของสิ้นค้ามรวันที่ 2 เราสามารถคัดลอกสูตรไปที่ F3 และ F4 การอ้างอิงจะถูกเปลี่ยนไปโดยอัตโนมัติทั้นทีโดยที่เราไม่ต้องสร้างสูตรที่มีการอ้างอิงใหม่

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

การอ้างอิงแบบสัมบูรณ์ (Absolute References)

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

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

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

การอ้างอิงแบบผสมผสาน (Switch between Relative and Absolute References)

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

การอ้างอิงไปยังตำแหน่งต่าง ๆ เราสามารถกำหนดให้ล็อคเฉพาะคอลัมน์หรือแถวได้ โดยใช้เครื่องหมาย $ เช่นเดียวกัน โดยใส่เครื่องหมาย $ ไว้ข้างหน้าเฉพาะตำแหน่งที่ต้องการจะล็อค เช่น ในตำแหน่ง F2 ถ้าต้องการล็อคเฉพาะคอลัมน์ให้ใส่ $F2 หรือล็อคเฉพาะแถวให้ใส่ F$2 เมื่อมีการย้ายหรือคัดลอกสูตรตำแหน่งจะเปลี่ยนเฉพาะส่วนที่ไม่ได้ล็อค

TIP

เราสามารถใส่เครื่องหมาย $ ให้กับเซลล์ที่ต้องการล็อคได้ง่าย ๆ โดยเลือกเซลล์ที่มีสูตรนั้นอยู่ แล้วกดปุ่ม <F2> เพื่อเข้าไปในสูตร แล้วเลื่อนเคอร์เซอร์ไปยังตำแหน่งเซลล์ที่ต้องการจะล็อค จากนั้นกดปุ่ม <F4> Excel จะใส่เครื่องหมาย $ ให้อัตโนมัติ และถ้าเรากดปุ่ม <F4> ไปเรื่อย ๆ Excel ก็จะเปลี่ยนวิธีล็อคตำแหน่งการอ้างอิงตาม เช่น ถ้าเราเลื่อนเคอร์เซอร์ไปที่ A1 แล้วกดปุ่ม <F4> การอ้างอิงจะกลายเป็น $A$1 และเมื่อกดปุ่ม <F4> อีกครั้งจะกลายเป็น A$1 และถ้ากดอีกครั้งจะกลายเป็น $A1 โดยเราสามารถกดไปเรื่อย ๆ ตามที่เราต้องการก็จะล็อคตำแหน่งนั้น ๆ

NOTE
	การอ้างอิงใน Excel สามารหถทำได่หลายรูปแบบดังตารางต่อไปนี้ รูปแบบการอ้างอิง วิธีใช้ เซลล์ในคอลัมน์ A ที่อยู่ในแถวที่ 10 A10 ช่วงของเซลล์ในคอลัมน์ A จากแถวที่ 4 ถึง 9 A4:A9 ช่วงของเซลล์ในแถวที่ 1 จากคอลัมน์ A ถึง M A1:M1 ช่วงของเซลล์ในแถวที่ 1ถึง 5 จากคอลัมน์ A ถึง M A1:M5 เซลล์ทั้งหมดในแถวที่ 1 1:1 เซลล์ในแถวที่ 1ถึง 5 1:5 เซลล์ทั้งหมดในคอลัมน์ A A:A เซลล์ทั้งหมดในคอลัมน์ A ถึง Z A:Z

การอ้างอิงข้ามชีทหรือข้ามไฟล์

บางครั้งเราอาจจำเป็นต้องใช้ข้อมูลที่มีอยู่ในชีทอื่นหรือไฟล์อื่น การที่เราจะนำข้อมูลมาเก็บไว้ในชีทเดียวกันหรือไฟล์เดียวกันทั้งหมด อาจจะไม่สะดวกต่อการใช้หรืออาจทำให้ไฟล์งานนั้น ๆ ใหญ่เกินไป

การลิงค์ (Link) ข้อมูลจากชีทงานที่อยู่ในไฟล์เดียวกัน ในการสร้างตารางข้อมูลใน Excel เรามักจะใช้ชีทหนึ่งเป็นฐานข้อมูล และสร้างรายงานรูปแบบต่าง ๆ ไว้ในชีทอื่น หรือการทำรายงานโดยแยกเป็นรายเดือนในแต่ละชีท ดังนั้นในไฟล์เดียวกันจึงมักจะมีการลิงค์ข้อมูลระหว่างชีทงานซึ่งจะแสดงเป็นตัวอย่างให้ดูได้ดังนี้

=SUM(sale!B6:D6)
sale	ชื่อของชีทงานที่ถูกลิงค์
B6:D6	ช่วงของเซลล์ที่ถูกอ้างอิง
ช่วงของเซลล์ที่ถูกอ้างอิงและชื่อชีทงาน ต้องมีเครื่องหมายอัศเจรีย์ (!) คั่นด้วยเสมอ

การแทนที่ของสูตรที่มีการคัดลอก

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

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

1. แดรกเมาส์เลือกเซลล์ที่ต้องการคัดลอก แล้วกดปุ่ม <Ctrl+C> เพื่อสั่ง Copy
2. เลือกตำแหน่งเซลล์ที่ต้องการแทนที่

3. คลิกขวาเลือกคำสั่งวางแบบพิเศษ

4. คลิกเลือกตัวเลือก ค่า เพื่อสั่งให้วางเฉพาะค่า

5.	คลิกปุ่ม ตกลง
6	สูตรจะแทนที่เฉพาะค่าที่คำนวณได้