วิถีแห่ง $

ตรวจร่างกาย หา $ ที่บกพร่อง

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

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

วิธีตรวจสอบตารางว่ามีสูตรที่ใช้ $ ควบคุมตำแหน่งอ้างอิงไว้ถูกหรือไม่

วิธีแรก : ให้เลือกพื้นที่ตารางสูตรทั้งหมด แล้วกดปุ่ม F5 > Special > กาช่อง Row differences หรือ Column differences หากในพื้นที่ตารางที่เลือกไว้ ใช้สูตรที่สร้างจากเซลล์ต้นตอเซลล์เดียวกัน Excel จะบอกว่า "No cells were found" แต่ถ้ามีสูตรซึ่งมีรูปแบบแตกต่างไป Excel จะกระโดดไปเลือกเซลล์เหล่านั้น

วิธีที่สอง : เปลี่ยนวิธีกำหนดตำแหน่งอ้างอิงไปใช้แบบเลขที่ Row และ เลขที่ Column โดยสั่ง Tools > Options > General > กาช่อง R1C1 reference style แล้วสั่งให้ Excel แสดงสูตรให้เห็นทุกเซลล์โดยสั่ง Tools > Options > View > กาช่อง Formulas จากนั้นให้สังเกตรูปแบบสูตรในตารางว่าต่างกันหรือไม่ ถ้าเป็นสูตรที่สร้างจากเซลล์ต้นตอเซลล์เดียวกัน จะแสดงสูตรเหมือนกันตลอด เช่น เดิมสูตร =B$2*$A3 หรือ =B$2*$A4 จะใช้สูตรแบบ R1C1 เหมือนกันเป็น =R2C*RC1

สูตร Sum แบบสะสม ถึงไหนถึงนั่น

สมมติว่าต้องการรวมเลขในเซลล์ A1:A5 โดยให้รวมแบบสะสมทีละเซลล์ต่อไปเรื่อยๆ จาก A1:A1 เป็น A1:A2 > A1:A3 > A1:A4 > A1:A5

ในเซลล์ B1 ให้สร้างสูตรหายอดรวมข้างต้น คือ =Sum($A$1:A1) จากนั้นจึง Copy สูตรนี้ลงไปตามแนวตั้ง พอถึงเซลล์ B5 จะได้สูตร =Sum($A$1:A5)

สูตรนี้ดูเผินๆก็ไม่น่าจะยากอะไร แค่กำกับตำแหน่งอ้างอิงแรกไว้แบบ Absolute $A$1 แล้วปล่อยตำแหน่งอ้างอิงปลายทางแบบ Realative A1 ไม่ต้องใส่ $ แต่ส่วนที่นึกไม่ถึงกันนั้นเป็นวิธีที่มนุษย์ใช้ในการสร้างสูตรนี้ต่างหาก

1. เริ่มจากคลิกลงไปในเซลล์ B1
2. พิมพ์สูตร =sum(
3. คลิกที่เซลล์ A1 จะได้สูตร =sum(A1
4. กดปุ่มจุดทศนิยม . จะได้สูตร =sum(A1:A1 ให้เอง
5. พิมพ์เครื่องหมายวงเล็บปิดด้านท้ายสูตร
6. คลิกลงไปที่ตัวสูตรบน Formula Bar ที่คำบอกตำแหน่ง A1 แรก
7. กดปุ่ม F4 เพื่อเปลี่ยน A1 ให้เป็น $A$1
8. กด Enter เพื่อรับสูตรที่สร้างเสร็จแล้วลงไปในเซลล์ B1
9. Copy สูตรลงไปตามแนวตั้งจากเซลล์ B1 ไปถึงเซลล์ B5

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

โครงสร้างสูตรแบบสะสมสามารถนำไปใช้กับสูตรอื่นๆได้อีก เช่น

• =SumIF($A$1:A1, A1, $B$1:B1)
  ใช้หายอดรวมสะสมของค่าที่เกิดซ้ำ
• =Count($A$1:A1)
  ใช้นับสะสมจำนวนเซลล์ที่เป็นตัวเลข
• =CountIF($A$1:A1,A1)
  ใช้นับสะสมจำนวนเซลล์ของค่าที่เกิดซ้ำ
• =CountIF(Indirect("$A$1:A" & Row(A1:A5)) , A1:A5)
  ใช้นับสะสมจำนวนเซลล์แบบของค่าที่เกิดซ้ำ โดยจะคืนค่าเป็นลำดับตัวเลขหลายตัวแบบ Array

ตำแหน่งอ้างอิงแบบเซลล์เดียว แต่ไร้ขอบเขต

ในช่วงต้นชั่วโมง ก่อนการอบรม Excel Expert Training จะเริ่มขึ้น จะเปิดโอกาสให้ผู้เข้าอบรมแนะนำตัว โดยไม่ต้องบอกชื่อเสียงเรียงนาม แต่ขอให้แนะนำฝีไม้ลายมือของแต่ละคนว่า สามารถสร้างตารางสูตรคูณต่อไปนี้ได้ถูกต้องและรวดเร็วแค่ไหน

วิธีที่ 1 : เซลล์ E6 สร้างสูตรแบบ Mixed References =E$5*$D6 แล้ว Copy ไปใช้ทั้งตาราง

วิธีที่ 2 : สร้างสูตร =RowTop*ColumnLeft โดยกำหนดให้ใช้ Range Name ชื่อ RowTop =$E$5:$P$5 และ ColumnLeft =$D$6:$D$10

วิธีที่ 3 : สร้างสูตร =MultiplyFML โดยกำหนดให้ใช้ Formula Name ชื่อ MultiplyFML =E$5*$D6

วิธีที่ 4 : เซลล์ E6 สร้างสูตรแบบ Absolute References =$E$5:$P$5*$D$6:$D$10 แล้ว Copy ไปใช้ทั้งตาราง

วิธีที่ 5 : เลือกพื้นที่ตาราง E6:P10 แล้วสร้างสูตร Array {=E5:P5*D6:D10}

ทั้ง 5 วิธีนี้ สูตรวิธีที่ 3 เป็นวิธีที่น่าใช้มากที่สุด เนื่องจากสูตรนี้ใช้ตำแหน่งอ้างอิงแบบเซลล์เดียว ทำให้ E$5 จึงหมายถึงทุกเซลล์ที่อยู่ใน Row 5 และ $D6 จึงหมายถึงทุกเซลล์ที่อยู่ใน Column D ทำให้ Formula Name ชื่อ MultiplyFML นำไปใช้งานได้ทุกตำแหน่งในตาราง และเมื่อใช้คำสั่ง Insert > Name > Define กลับไปแก้ไขสูตรชื่อนี้ เช่น สูตรเดิมเป็นสูตรคูณ =E$5*$D6 ถูกแก้ใหม่เป็นสูตรบวก =E$5+$D6 จะส่งผลให้ทุกเซลล์ที่ใช้สูตรชื่อ MultiplyFML คำนวณใหม่แบบสูตรบวกให้ทันที ช่วยประหยัดเวลาไม่ต้องค้นหาเซลล์เพื่อแก้ไขสูตรแล้วต้อง Copy ไปทับใหม่

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

สร้างทรัพย์สินทางปัญญา เมื่อไร้ $

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