การอ้างอิงตำแหน่ง หรือ Reference ใน Excel เป็นเรื่องพื้นฐานที่สำคัญมาก เพราะการใช้สูตรหรือฟังก์ชันต่าง ๆ ล้วนแล้วแต่ต้องการอ้างอิงตำแหน่งทั้งสิ้น ไม่ว่าจะเป็นการอ้างอิง Cell หรือ Range ก็ตาม
การอ้างอิงตำแหน่งขั้นพื้นฐาน
สมมติ ตัวอย่าง เราเอา ราคาสินค้าต่อหน่วย ไว้ที่ Cell B2 จำนวนสินค้าที่ขายได้ไว้ที่ C2 เราคำนวณจำนวนเงินที่ขายได้ในช่อง D2 ซึ่งจะเป็น ราคาสินค้า x จำนวนสินค้า ในช่อง C1 ก็จะเป็นสูตร
=B2*C2
B2 และ C2 ก็คือตำแหน่ง Cell ที่เราอ้างอิง เป็นการอ้างอิงแบบ Cell เดียว โดด ๆ เวลาเขียนสูตรก็แค่ระบุตำแหน่ง ส่วนตัวเลขข้างในจะเปลี่ยนไปก็ตามแต่…
แต่ถ้าการอ้างอิง ไม่ได้แค่ Cell เดียว บางทีมันจะไม่ได้อ้างแค่ Cell ใด Cell หนึ่ง แต่เป็น ช่วงของ Cell อย่างเช่น D2 ถึง D11 เป็นรายได้จากการจำหน่ายสินค้าทั้งหมด
สมมติว่า เราจะเอาผลลัพธ์ไปไว้ที่ G2 เราก็เขียนสูตร
=D2+D3+D4+D5+D6+D7+D8+D9+D10+D11
โปรดสังเกต เมื่อเราคลิกที่ formula bar ตรง ชื่อ Cell ที่อ้างอิงจะมีสีเดียวกับที่กรอบในตำแหน่ง Cell นั้น
นี่คือตัวอย่างการอ้างอิง Cell แบบง่าย ๆ ว่าทำงานอย่างไร
แต่เขียน D2+D3+D4+D5+D6+D7+D8+D9+D10+D11 ออกจะยุ่งยาก ถ้าเกิดต้องคำนวณสัก 1,000 หรือ 10,000 คงเขียนกันตาเหลือก สิ่งที่เราจะใช้แทนคือการอ้างอิงแบบ Range เพื่อช่วยย่อเชตข้อมูลที่จะนำไปใช้
Range
ถ้าให้นิยามสั้น ๆ Range ก็คือช่วงเซลของข้อมูลที่เราต้องการคำนวณ จากจุด (Cell) หนึ่งไปอีกจุด (Cell) หนึ่ง
เช่นจะคำนวณผลรวมตั้งแต่ D2 มาจนถึง D11 แทนที่จะเขียน =D2+D3+D4+D5+D6+D7+D8+D9+D10+D11 ยาวยืดให้มันยุ่งยาก ยิ่งถ้าข้อมูลมีเป็นร้อยเป็นพัน หรือ จนถึงหลักหมื่น คงพิมพ์ไม่ไหว ตรวจสอบก็ลำบาก ก็ต้องใช้ Range หรือการกำหนด “ช่วง” เข้ามาแทน
เช่น ถ้าต้องการกำหนดช่วงที่ต่อเนื่องกัน ให้ใช้โคลอน (:) โดยเริ่มที่เซลแรกคั่นด้วย : แล้วต่อด้วยเซลสุดท้าย เช่น D2:D11 ก็หมายถึงช่วงตั้งแต่ D2 ไปจนถึง D11
ดังนั้น เมื่อเราใช้ function Sum มาผสมกับการอ้างอิงตำแหน่งแบบช่วง หรือ Range ก็จะได้เป็น
=SUM(D2:D11)
เครื่องหมาย โคลอน (:) นี้ ใช้เพื่อแสดงช่วงว่าจากไหนถึงไหน อย่างเช่น ตัวอย่าง เราอยากรวม ผลรวม (SUM) ของข้อมูล ในคอลัมน์ Site A (A2 ถึง A10 Site B (B2 ถึง B10) และ Site C (C2-C10) เราสามารถใช้ A2:C10 ได้เลย
=SUM(A2:C10)
แต่ถ้า จะเอาแค่จาก Site A กับ Site C โดยไม่เอา Site B ล่ะ จะทำอย่างไร
Union
Union จะเป็นการรวม ช่วง หรือ cell ที่แยกกันอยู่ ไม่ได้อยู่ติดเรียงกันแบบใน Range ยกตัวอย่าง เราจะหาผลรวม (SUM) ของข้อมูลใน Site A กับ Site C ( A2 – A10 กับ C2 – C10) เราจะใช้ Range ไม่ได้ เพราะมี Site B คั่นกลางอยู่
ตรงนี้เราจะใช้ Union โดย การใช้ Union จะใช้เครื่องหมาย คอมมา (,) ในการเชื่อมหรือ union สูตรการเขียน ก็จะเป็นดังนี้
=SUM(A2:A10,C2:C10)
Intersect
ในขณะที่ Range และ Union เป็นการ “รวม” แต่ Intersect จะเป็นการเอาเฉพาะ ส่วนที่มีร่วมกัน เท่านั้น โดยจะใช้ วรรค เป็นเครื่องหมายกำกับ ยกตัวอย่างเช่น
=SUM(E1:F9 A3:I4)
จะได้ผลลัพธ์เป็น 10 เพราะจะเป็นการ SUM ส่วนที่คาบเกี่ยวกัน ระหว่าง E1:F9 กับ A3:I4 เท่านั้น ซึ่งก็จะเท่ากับผลรวมของ 1 + 2 + 3 + 4 เท่านั้น
Relative References
เวลาเราเขียนสูตร โดยอ้างอิงตำแหน่ง เวลาต้องคำนวณต่อเนื่องกัน หลายคนมักจะชอบ “ลากสูตร” กัน อันนี้เป็นจุดเด่น ที่สะดวกดี
หมายถึง เขียนสูตรที่ Cell ได้ Cell หนึ่ง แล้วเอาเมาส์ไปวางตรงมุมขวาล่าง ให้เคอร์เซอร์เป็นเครื่องหมายบวก แล้วคลิกซ้ายลากลง ขึ้น ซ้าย ขวา ตามใจชอบ ว่าจะวางสูตรต่อเนื่องไปทางไหน จะได้ สูตรจะเหมือนเดิม แต่ตัวอ้างอิงจะเปลี่ยนไปตามทิศทางการลาก
รูปแบบการอ้างอิงตำแหน่งที่เปลี่ยนไปตามทิศทางที่ชัดเจน แบบนี้เรียกว่า Relative References (การอ้างอิงแบบสัมพัทธ์) หรือ การอ้างอิงที่แปรผันไปตามที่เราลากไปซ้าย ขวาหรือว่า บน ล่าง ซึ่งมีประโยชน์มาก และใช้งานสะดวกมาก
ลองย้อนกลับไปที่ตัวอย่างแรกสุด ของบล็อกนี้ เราใส่สูตร =B2*C2 ที่ช่อง D2 แล้ว เราไม่จำเป็นต้องเขียนสูตรที่ช่อง D3 เพื่อให้มันคำนวณ B3*C3 เลย เราแค่เอาเมาส์ไปวางตรงมุมขวาล่าง ให้เคอร์เซอร์เป็นเครื่องหมายบวก แล้วลากมาเลยจนถึง cell สุดท้ายที่เราต้องการ
ทำแบบนี้ ช่อง D3 ก็จะเป็น B3*C3 ช่อง D4 ก็จะเป็น B4*C4 ช่อง D5 ก็จะเป็น B5*C5 แบบนี้ต่อไปเรื่อย ๆ
Absolute References
แต่ บางครั้งเราก็อยากให้ตำแหน่งที่อ้างอิงคงที่ ด้วยเหตุผลจำเป็นที่ต้องการเฉพาะเจาะจง เช่น ตัวอย่าง เราอยากคำนวณค่าเฉลี่ยของค่าทั้งหมดใน Site A แล้วหารด้วยค่า indicated ใน E2 สูตรก็จะเป็น
=AVERAGE(A2:A10)/E2
แต่ทีนี้ ถ้า เราจะลากไปทางซ้าย แบบ Relative References เหมือนในตัวอย่างข้างบน เพื่อจะคำนวณของ Site B, C ต่อไปด้วย มันจะกลายเป็นว่า E2 ก็จะเลื่อนเป็น F2 G2 ซึ่งผิดประเด็นที่ต้องการ เราต้องการแค่ E2 สำหรับการเอาไปหาร เท่านั้น
ดังนั้น เราต้องบังคับในสูตรว่า การอ้างอิงเฉพาะตำแหน่ง E2 นี้ จะต้องเป็นตำแหน่งเดิม ไม่เลื่อนตาม โดยการใส่เครื่องหมายดอลลาร์ ($) กำกับ จะพิมพ์ใส่ หรือจะกด F4 ก็ได้เหมือนกัน สูตรจะได้แบบนี้
=AVERAGE(A2:A10)/$E$2
การใส่เครื่องหมาย $ เป็นการระบุตำแหน่งคงที่ หรือ Absolute References (การอ้างอิงแบบสัมบูรณ์) $E$2 จะตรึง Cell E2 ไว้ คือ ตรึงคอลัมน์ E และตรึงแถว 2 ไว้ ไม่เลื่อนตำแหน่งไปที่อื่น
โปรดสังเกตว่า เราใส่ $ ไว้หน้าคอลัมน์ หรือ แถว ที่เราต้องการตรึง นั่นหมายความว่า เราเลือกจะตรึงอย่างใดอย่างหนึ่งก็ได้ เช่น
$E2 จะตรึงคอลัมน์ E ไว้ แต่จะเลื่อนแถวได้ จะเลื่อนเป็น E3 E4 E5… ได้
E$2 จะตรึงแถว 2 ไว้ แต่จะเลื่อนคอลัมน์ได้ จะเป็น F2 G2 H2… ได้
เช่น ถ้าเราใส่สูตร
=AVERAGE(A3:A11)/$E3
แบบนี้ เวลาเราลากสูตรไปทางขวา Cell E3 ที่ตรึงไว้จะไม่ขยับ เพราะมันตรึงคอลัมน์ E เอาไว้ ไม่สามารถเลื่อนเป็น F G H … ได้
แต่ถ้าลากสูตรลงมา จะเปลี่ยนเป็น E4 E5 E6 ทันที เพราะเราไม่ได้ตรึงแถวไว้ ลากลงมาก็เปลี่ยนไปตามลำดับแถว
การอ้างอิงตำแหน่งข้าม sheet
หรือถ้าอยากจะอ้างอิงข้อมูลจากชีตอื่น ก็ได้เหมือนกัน ง่าย ๆ เพียงแค่ใส่ชื่อชีตแล้วตามด้วยเครื่องหมายอัศเจรีย์ (!)
เช่น
=SUM(A2:A10)/Range!G2
ก็จะหมายถึงให้รวมผลลัพธ์ ตั้งแต่ A2 ถึง A10 แล้วหารด้วยค่าใน G2 ของชีตที่ชื่อ Range
การอ้างอิงข้ามชีตแบบนี้ มีข้อควรระวังคือ การสะกดชื่อชีต จะต้องถุกต้องตรงกัน สะกดผิดมันจะ error หรือ ถ้าชีตนั้นเปลี่ยนชื่อ หรือ โดนลบไปในภายหลัง ก็จะ error
การตั้งชื่อชีต ควรตั้งเป็นตัวอักษรและไม่เว้นช่องว่าง เพราะถ้าใช้ชื่อชีตเป็นตัวเลข หรือ มีข่องว่างในชื่อชีตจะต้องใส่เครื่องหมายอัญประกาศ (‘) ที่ชื่อชีตเสมอ เช่น ถ้าชื่อชีตเป็น 999 สูตรจะเป็น
=SUM(A2:A10)/’999’!G2
หรือถ้าตั้งชื่อชีตว่า The Score ก็จะกลายเป็น
=SUM(A2:A10)/’The Score’!G2
ถ้าไม่ใส่ ‘ กำกับหน้าหลังชื่อชีต ก็จะ error เช่นกัน
การอ้างอิงตำแหน่งข้าม workbook
การอ้างอิงตำแหน่ง สามารถอ้างอิงข้ามไฟล์งาน (workbook) ได้เช่นกัน โดยใส่ วงเล็บเหลี่ยมครอบชื่อไฟล์ – [ชื่อไฟล์] ต่อด้วยชื่อชีตและเซลล์ ยกตัวอย่างเช่น จะใช้ค่าในเซลล์ B2 ไปคูณกับ เซลล์ A2 ในชีต S1 จากไฟล์ ref.xlsx ก็จะได้สูตร
=B2*[ref.xlsx]S1!A2
การอ้างอิงไฟล์อื่นจะสังเกตได้ง่ายเพราะเมื่อเปิดไฟล์ขึ้นมาจะมีหน้าต่างถามว่าจะอัพเดทข้อมูลจากไฟล์ที่ถูกอ้างอิงหรือไม่ ถ้าเลือก Update เป็นการนำค่าล่าสุดจากไฟล์ที่ถูกอ้างอิงมา ถ้าหาไฟล์นั้นไม่เจอ จะมีหน้าต่างให้ระบุตำแหน่งของไฟล์ใหม่ ถ้าเลือก Don’t Update จะแสดงค่าเดิมตอนที่บันทึกไฟล์ครั้งล่าสุด
Tips: ปรกติจะไม่แนะนำให้อ้างอิงข้ามไฟล์ เพราะถ้าย้ายที่เก็บไฟล์ไปคอมพิวเตอร์เครื่องอื่น บางทีจะลืมเอาไฟล์อีกไฟล์ไปด้วย จะทำให้อ้างอิงไม่ได้ ถ้าเป็นไปได้ สำเนาชีตที่ต้องการอ้างอิงจากอีกไฟล์มารวมไว้ในไฟล์เดียวกันจะดีกว่า แต่ในกรณีที่ไฟล์ทำโดยคนละคน การเลือกอ้างอิงตำแหน่งข้ามไฟล์ก็ยังเป็นตัวเลือกที่ดี
ตั้งชื่อช่วงไว้ใช้งาน
สำหรับการอ้างอิงข้อมูลเป็นปริมาณมาก และต้องอ้างอิงหลายแห่ง การอ้างอิงโดยใช้ชื่อเซลล์อาจจะทำให้สับสน เราตั้งชื่อช่วงไว้เลยจะง่ายกว่า
อย่างเข่น เราจะใช้ช่วง A2:F717 เวลาเขียนสูตรอาจจะจำยากไปหน่อย เราจะตั้งชื่อช่วงว่า locate ก็ทำได้ง่าย ๆ โดยเลือกเซลล์ตั้งแต่ A2 จนถึง F717 แล้วไปที่ Namebox พิมพ์คำว่า locate (หรืออื่น ๆ ตามต้องการ) ได้เลย
หรืออีกวิธีหนึ่ง เลือกเซลล์ตั้งแต่ A2 จนถึง F717 แล้วคลิกขวา เลือก define name… เมื่อมีป็อปอัพขึ้นมาก็ใส่ชื่อลงไปในช่อง name
และถ้าจะดู / แก้ไข ในภายหลัง ก็เข้าไปที่ Formula > Name Manager ซึ่งที่ตรงนี้เราจะดูได้ว่าเราได้กำหนดชื่อช่วงขอบเขตข้อมูลอะไรไว้บ้าง หรือถ้าอยากจะแก้ไขก็เรียกข้อมูลมาแก้ไขได้เลย
การอ้างอิงตำแหน่งแบบสุดท้ายที่จะนำเสนอคือแบบ R1C1 สไตล์การอ้างอิง R1C1 ระบุไปเลยว่าแถว (Row) ที่เท่าไหร่ คอลัมน์ (Column) ที่เท่าไหร่ วิธีนี้จะมีประโยชน์มากตอนเขียน VBA หรือ แมโครต่าง ๆ
อ้างอิง เรื่องการอ้างอิงตำแหน่งในเว็บไมโครซอฟต์