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

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

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

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

 

NOTE
 
1

          การอ้างอิงใน 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
สูตรจะแทนที่เฉพาะค่าที่คำนวณได้






HOME          PARTNERS    
Copyright 2009 Permsak. All rights reserved.