หัวใจ ของ Excel

ลองใช้วิธีต่อไปนี้

1. ในเซลล์ E6 ให้พิมพ์เครื่องหมายเท่ากับลงไป แล้วคลิกลงไปที่หัวตารางเซลล์ E5 ซึ่งมีค่าเท่ากับ 1
2. พอใส่ตำแหน่ง =E5 ลงไปใน formula bar ให้กดปุ่ม F4
3. กดปุ่ม F4 ไปเรื่อยๆ จะพบว่ามีเครื่องหมาย $ สลับตำแหน่งให้เอง
   E5 >> $E$5 >> E$5 >> $E5 >> E5 >> วนกลับไปเริ่มใส่ $ ให้อีก
4. ให้กด F4 ไปจนกระทั่งได้เครื่องหมาย $ ตรงตำแหน่งที่ต้องการ คือ E$5 ซึ่งในตัวอย่างนี้ ต้องการใช้ค่าบนหัวตาราง นำไปคำนวณในทุกเซลล์ในตารางสูตรคูณ ดังนั้นจึงต้องใช้ค่าใน row 5 เช่นเดียวกันทั้งหมด จึงต้องกำหนด $ ให้อยู่หน้าเลข 5 เพื่อคุมไม่ให้เลข 5 เปลี่ยนแปลง ส่วน E ให้คงเดิมไว้ เพื่อปล่อยให้เปลี่ยนตำแหน่งเป็น column อื่นๆ เมื่อเรา copy สูตรนี้ไปยัง column นั้นๆ
5. จากนั้นใส่เครื่องหมายคูณ แล้วคลิกที่เลข 2 ในเซลล์ D6 ซึ่งอยู่ด้านข้างตาราง ให้กด F4 เพื่อเปลี่ยนเป็น $D6 เนื่องจากต้องใช้ค่าใน column D ในการคำนวณเช่นเดียวกันทั้งหมด แล้วกด Enter เพื่อรับสูตร จะได้สูตร =E$5*$D6
6. copy สูตรนี้ไปใช้คำนวณทั้งตาราง จะพบว่า ได้ผลคำนวณถูกต้องทุกเซลล์ เป็นผลมาจาก Excel ปรับตำแหน่งอ้างอิงให้เองเมื่อเรา copy สูตรไปใช้ที่เซลล์อื่นๆ
   

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

รูปแบบของการใส่ตำแหน่งอ้างอิง $

1. Absolute Reference
   ใส่เครื่องหมาย $ คุมทั้งตำแหน่ง row และ column เช่น $E$5 มักใช้กับการนำค่าคงที่ไปใช้คำนวณ หรืออ้างอิงกับตารางเก็บข้อมูลซึ่งมีตำแหน่งแน่นอนตายตัว
2. Mixed Reference
   ใส่เครื่องหมาย $ คุมตำแหน่ง row หรือ column อย่างใดอย่างหนึ่ง เช่น E$5 หรือ $E5 เป็นรูปแบบใช้กับการคำนวณทั่วไปซึ่งอ้างอิงกับตำแหน่งตัวแปร กระจายคู่ขนานกับแนวของสูตร
3. Relative Reference
   ไม่ใส่เครื่องหมาย $ ใช้กับสูตรคำนวณซึ่งใช้ในเซลล์นั้นเซลล์เดียว

เฉลยแบบทดสอบสร้างตารางสูตรคูณ

1. วิธีที่ 1 : =E$5*$D6
2. วิธีที่ 2 : =RowTop*ColumnLeft
   ใช้ range name ตั้งชื่อให้กับหัวตารางและข้างตาราง แล้วนำชื่อมาใช้ในการคำนวณ
3. วิธีที่ 3 : =MultiplyFML ใช้ formula name โดยใช้เมนูคำสั่ง Insert > Name > Define ตั้งชื่อสูตรว่า MultiplyFML และให้ Refers to: =UserTest!E$5*UserTest!$D6 (สังเกตว่า =UserTest!E$5*UserTest!$D6 ใช้รูปแบบตำแหน่งอ้างอิงแบบ Mixed Reference ดังนั้นก่อนจะสั่งเมนู Insert ต้องคลิกเลือกเซลล์ E6 ก่อน เพื่อกำหนดให้สูตรที่สร้างรู้จักว่า กำลังอ้างอิงกับตำแหน่งเซลล์ E6)
4. วิธีที่ 4 : =$E$5:$P$5*$D$6:$D$10
   เรียกว่า สูตรคำนวณแบบล้อมรั้ว โดยกำหนดให้ใช้ตำแหน่งอ้างอิงแบบ Absolute Reference ซึ่ง Excel จะนำค่าซึ่งอยู่บนรั้วบนและรั้วด้านข้าง มาคำนวณ ณ เซลล์ตรงจุดตัดกันให้เอง วิธีนี้ใช้หลักคำนวณเช่นเดียวกับวิธีที่ 2 และจะคำนวณให้ผลลัพธ์ถูกต้องเฉพาะในพื้นที่ใต้รั้วที่กำหนดเท่านั้น
5. วิธีที่ 5 : {=E5:P5*D6:D10}
   ใช้สูตรคำนวณ แบบ Arrays ต้องเลือกพื้นที่ในตารางคำตอบทั้งหมดก่อน แล้วจึงสร้างสูตร =E5:P5*D6:D10 ให้กดปุ่ม Crtl+Shift+Enter เพื่อรับสูตรแบบ Arrays และจะเกิดเครื่องหมายวงเล็บปีกกาปิดหัวท้ายสูตรให้เอง

หากต้องการแกะสูตร array ให้คลิกเลือกตัวสูตรบน formula bar แล้วกดปุ่ม F9 จะพบว่า Excel คำนวณสูตรเป็นขั้นๆ เริ่มจากเปลี่ยนตำแหน่งเซลล์ เป็นลำดับของตัวเลข

={1,2,3,4,5,6,7,8,9,10,11,12}*{2;3;4;5;6}

={2,4,6,8,10,12,14,16,18,20,22,24
   ;3,6,9,12,15,18,21,24,27,30,33,36
   ;4,8,12,16,20,24,28,32,36,40,44,48
   ;5,10,15,20,25,30,35,40,45,50,55,60
   ;6,12,18,24,30,36,42,48,54,60,66,72}

ลำดับตัวเลขในสูตร array นี้ ใช้เครื่องหมาย comma , แบ่งตัวเลขซึ่งเรียงตัวในแนวนอนเดียวกัน และใช้เครื่องหมาย semi-colon ; แบ่งตัวเลขซึ่งอยู่คนละบรรทัด

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

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