เชิญสมัครเข้าอบรมวันอาทิตย์ที่ 5 และ 12 พฤษภาคมนี้ครับ ... คลิกที่นี่เพื่อดูรายละเอียดและลงทะเบียน

Arrow up
Arrow down

Smart Formula แปลว่า สูตรฉลาด ซึ่งสูตรฉลาดที่ว่านี้จะฉลาดได้ก็ต่อเมื่อใช้ความฉลาดของคุณในการสร้างสูตรขึ้นมา เพื่อให้มีลักษณะการใช้งานได้ตามนี้

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

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

ตำแหน่งอ้างอิงแบบ Absolute Reference มีเครื่องหมาย $ วางไว้ด้านหน้าของทั้งตำแหน่ง Row และ Column เช่น =$A$1 โดยให้ใช้ตำแหน่งอ้างอิงแบบนี้กับกรณีที่ต้องการนำค่าคงที่ไปใช้

ตำแหน่งอ้างอิงแบบ Relative Reference เป็นตำแหน่งอ้างอิงซึ่งไม่มีเครื่องหมาย $ เช่น =A1 ซึ่งสูตรที่อ้างอิงแบบนี้จะต้องใช้ที่เซลล์เดิมเซลล์เดียวไปตลอด ไม่สามารถ Copy นำไป Paste เพื่อทำให้ได้ค่าเดิมซ้ำที่เซลล์อื่นได้อีก เพราะตำแหน่งอ้างอิงจะเขยื้อนต่างจากตำแหน่งเดิมเสมอ ดังนั้นจึงเป็นการอ้างอิงแบบที่ใช้ในเซลล์สูตรกันน้อยที่สุด แต่กลับถูกนำมาใช้กันมากใน Conditional Formatting หรือ Data Validation

ตำแหน่งอ้างอิงแบบ Mixed Reference เป็นตำแหน่งอ้างอิงที่นิยมใช้กันมากที่สุด โดยผู้สร้างสูตรต้องเลือกใส่เครื่องหมาย $ ด้านหน้าตำแหน่ง Row หรือ Column เพียงอย่างใดอย่างหนึ่ง เช่น =A$1 เพื่อคงตำแหน่ง Row 1 ไว้ตามเดิมตลอด หรือ =$A1 เพื่อคงตำแหน่ง Column A ไว้ตามเดิมตลอด ไม่ว่าจะ Copy นำสูตรไป Paste ที่ตำแหน่งอื่นก็ตาม


วิธีสร้างสูตรให้มีตำแหน่งอ้างอิงที่มีเครื่องหมาย $ นำหน้าตำแหน่งของ Row และหรือ Column นี้ ให้ใช้วิธี Pointing แล้วกดปุ่ม F4 ซ้ำไปเรื่อยๆจนกว่าจะได้ตำแหน่ง $ ตามต้องการ

  1. ให้เริ่มจากพิมพ์เครื่องหมายเท่ากับ = ลงไปในเซลล์ที่ต้องการสร้างสูตร (เพื่อแสดงการยึดครองเซลล์นั้นไว้ก่อนว่า เป็นเซลล์ที่จะรับสูตรลงไปเมื่อเรากดปุ่ม Enter)
  2. จากนั้นใช้วิธีชี้ลงไปในเซลล์ที่ต้องการ (Pointing แปลว่า ชี้) โดยใช้ Mouse คลิกเลือกเซลล์ที่ต้องการ ไม่ว่าจะอยู่ต่างชีทหรือต่างแฟ้มกันก็ตาม เช่น คลิกที่เซลล์ A1 จะได้สูตร =A1
  3. จากนั้นให้กดปุ่ม F4 ไปเรื่อยๆ จะพบว่ามีเครื่องหมาย $ แทรกลงในสูตรให้เองตามลำดับที่มีการกดปุ่ม F4 ดังนี้ A1 > $A$1 > A$1 > $A1 > A1 >>> แล้วจะมีเครื่องหมาย $ วนซ้ำไปอีก
  4. เมื่อได้ตำแหน่ง $ ควบคุมตำแหน่ง Row และหรือ Column ตามต้องการแล้วให้กดปุ่ม Enter เพื่อรับสูตรลงไปในเซลล์ แล้วจะพบว่า Excel จะพากลับมาที่เซลล์ซึ่งพิมพ์เครื่องหมายเท่ากับนำหน้าสูตรเอาไว้ตั้งแต่แรกนั่นเอง


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

นอกจากตำแหน่งอ้างอิงที่ต้องมีเครื่องหมาย $ เป็นองค์ประกอบสำคัญแล้ว ในโครงสร้างของสูตร Smart Formula ยังอาจจำเป็นต้องพึ่งสูตร IF, And, Or หรือสูตรอื่นๆซึ่งทำหน้าที่ตัดสินใจ เพื่อช่วยทำให้สูตรมีเงื่อนไขในการคำนวณต่างไปจากเดิมได้เองทันที เมื่อมีการนำสูตรเดียวกันไปใช้ซ้ำที่ตำแหน่งเซลล์อื่น

 

ตัวอย่าง Smart Formula แบบใช้ตำแหน่งอ้างอิงเซลล์เดียว

ขอให้สร้างตารางสูตรคูณ ซึ่งนำตัวเลขบนหัวตารางมาคูณกับตัวเลขด้านข้างซ้ายของตาราง

 


ขั้นตอนการสร้าง

1. เริ่มจากเลือกเซลล์ C3 แล้วพิมพ์เครื่องหมายเท่ากับ = ลงไป
2. จากนั้นคลิกเซลล์ C2 จะได้สูตร =C2
3. กดปุ่ม F4 เพื่อเปลี่ยนสูตรให้เป็น =C$2
4. พิมพ์เครื่องหมายคูณ *
5. จากนั้นคลิกเลือกเซลล์ B3 จะได้สูตร =C$2*B3
6. กดปุ่ม F4 เพื่อเปลี่ยนสูตรเป็น =C$2*$B3
7. กดปุ่ม Enter เพื่อรับสูตรลงไปในเซลล์ C3
8. Copy ไปทับตาราง C3:F5


คำอธิบาย

  • เซลล์ C3 เป็นเซลล์ที่มีสูตร Smart Formula ซึ่งโดยทั่วไปจะเลือกให้เซลล์หัวมุมด้านซ้ายบนของตารางเป็นเซลล์ที่มีสูตร Smart Formula
  • คุณอาจเลือกสร้างสูตรคูณโดยใช้เลข 2 นำหน้าแล้วนำไปคูณกับเลข 10 เพื่อทำให้ได้สูตร =C$2*$B3 หรือจะเลือกใช้เลข 10 นำหน้าแล้วนำไปคูณกับเลข 2 ซึ่งจะทำให้เกิดสูตร =$B3*C$2 ก็ได้และทำให้เกิดผลลัพธ์ถูกต้องเท่ากันทั้งสองแบบ แต่ขอแนะนำให้สร้างพฤติกรรมในการสร้างสูตรของตนว่า “ทุกสูตรที่สร้างขึ้นจะนำตำแหน่งอ้างอิงตามแนวนอนขึ้นก่อนตำแหน่งอ้างอิงตามแนวตั้งเสมอ” ซึ่งหลักนี้จะช่วยให้คุณแกะสูตรของตัวเองได้ง่ายขึ้น และหากสังเกตสูตรอื่นๆของ Excel เช่น Index หรือ Offset จะใช้ตำแหน่งอ้างอิงจากแนวนอน Row ขึ้นก่อนตำแหน่งอ้างอิงจากแนวตั้ง Column เช่นกัน
  • C$2 ต้องมีเครื่องหมาย $ วางไว้หน้าเลข 2 เนื่องจากตารางสูตรคูณนี้มี Row 2 เป็นตำแหน่งอ้างอิงที่ต้องควบคุมไว้ไม่ให้เปลี่ยน ส่วนตัว C ไม่ต้องมีเครื่องหมาย $ นำหน้า เพื่อปล่อยให้ C เปลี่ยนตำแหน่งเป็น Column D E F หรือ Column อื่นๆตามแต่ว่าจะใช้สูตรนี้หรือไม่
  • $B3 ต้องมีเครื่องหมาย $ วางไว้หน้า Column B เนื่องจากตารางสูตรคูณนี้มี Column B เป็นตำแหน่งอ้างอิงที่ต้องควบคุมไว้ไม่ให้เปลี่ยน ส่วนเลข 3 ไม่ต้องมีเครื่องหมาย $ นำหน้า เพื่อปล่อยให้เลข 3 เปลี่ยนตำแหน่งเป็น Row 4 5 6 หรือ Row อื่นๆตามแต่ว่าจะใช้สูตรนี้หรือไม่
  • หลักการใส่เครื่องหมาย $ นี้ ให้จำไว้ว่า “ถ้าตำแหน่งอ้างอิงในสูตรมาจากค่าตามแนวนอน ให้ใส่ $ ไว้หน้า Row (เพราะ แนวนอน = Row) แต่ถ้าตำแหน่งอ้างอิงในสูตรมาจากค่าตามแนวตั้ง ให้ใส่ $ ไว้หน้า Column (เพราะ แนวตั้ง = Column)”

 

ตัวอย่าง Smart Formula แบบใช้ตำแหน่งอ้างอิงตามขอบเขตพื้นที่หัวตาราง

ขอให้สร้างตารางสูตรคูณ ซึ่งนำตัวเลขบนหัวตารางมาคูณกับตัวเลขด้านข้างซ้ายของตาราง

 


วิธีการสร้างคล้ายกับวิธีแรก แทนที่จะคลิกเลือกเซลล์ C2 หรือเซลล์ B3 วิธีนี้ให้คลิกเลือกเซลล์ C2:F2 และ B3:B5 ซึ่งเป็นหัวตารางด้านบนและด้านซ้ายทั้งหมด แล้วกดปุ่ม F4 เพื่อทำให้เกิดสูตร =$C$2:$F$2*$B$3:$B$5

1. เริ่มจากเลือกเซลล์ C3 แล้วพิมพ์เครื่องหมายเท่ากับ = ลงไป
2. จากนั้นคลิกเลือกเซลล์บนหัวตารางด้านบนจากเซลล์เลข 2 - 5 จะได้สูตร =C2:F2
3. กดปุ่ม F4 เพื่อเปลี่ยนสูตรให้เป็น =$C$2:$F$2
4. พิมพ์เครื่องหมายคูณ *
5. จากนั้นคลิกเลือกเซลล์บนหัวตารางด้านซ้ายจากเซลล์เลข 10 - 30 จะได้สูตร =$C$2:$F$2*B3:B5
6. กดปุ่ม F4 เพื่อเปลี่ยนสูตรเป็น =$C$2:$F$2*$B$3:$B$5
7. กดปุ่ม Enter เพื่อรับสูตรลงไปในเซลล์ C3
8. Copy ไปทับตาราง C3:F5


คำอธิบาย

  • สูตรแบบที่อ้างอิงกับตำแหน่งทั้งหมดบนหัวตารางหรือข้างตาราง แล้วใส่เครื่องหมาย $ ให้กับตำแหน่งทั้ง Row และ Column นี้ ผมตั้งชื่อเรียกว่า สูตรแบบล้อมรั้ว โดยที่สูตร =$C$2:$F$2*$B$3:$B$5 จะทำหน้าที่นำตำแหน่งอ้างอิงบนรั้วตามแนวตั้งฉากที่ตัดกันมาคูณกันทีละตัวให้เอง ทำให้ไม่ต้องคอยพะวงว่าจะต้องเลือกใส่เครื่องหมาย $ เพื่อควบคุมตำแหน่งอ้างอิงตามแนวใดเป็นหลัก
  • ถ้าเปรียบเทียบกับสูตรคูณวิธีแรกซึ่งใช้ตำแหน่งอ้างอิงแบบเซลล์เดียว จะพบว่าการใช้สูตรอ้างอิงแบบเซลล์เดียวมีความยืดหยุ่นมากกว่า เช่นเมื่อหัวตารางมีขนาดใหญ่มากขึ้น ก็สามารถ Copy สูตรไปใช้ต่อได้ทันที แต่ถ้าสร้างสูตรแบบล้อมรั้ว จะต้องสร้างสูตรใหม่เพื่อกำหนดตำแหน่งอ้างอิงในสูตรให้มีขนาดขยายตาม (เว้นแต่ว่าคุณทราบวิธีขยายขอบเขตตำแหน่งอ้างอิง ซึ่งจะนำมาอธิบายต่อไปในภายหลัง)
  • จุดอ่อนของสูตรแบบล้อมรั้วก็คือ สูตรแบบนี้ใส่เครื่องหมาย $ ควบคุมตำแหน่งแบบ Absolute จึงคำนวณหาคำตอบให้ได้เฉพาะตารางจาก C3:F5 ซึ่งอยู่ภายในขอบเขตของรั้วเป็นแนวของตำแหน่งคงที่ หาก Copy สูตรไปใช้นอกรั้วจะเกิด Error ขึ้นตามรูป
     

 

ตัวอย่าง Smart Formula แบบใช้ตำแหน่งอ้างอิงแบบ Array ตามขอบเขตพื้นที่หัวตาราง

ขอให้สร้างตารางสูตรคูณ ซึ่งนำตัวเลขบนหัวตารางมาคูณกับตัวเลขด้านข้างซ้ายของตาราง

 


ขั้นตอนการสร้าง

1. แทนที่จะเลือกเซลล์ C3 เพียงเซลล์เดียว คราวนี้ให้เลือกพื้นที่ตารางจากเซลล์ C3:F5 ทั้งหมดพร้อมกันไว้ก่อน
2. จากนั้นพิมพ์เครื่องหมายเท่ากับ =
3. คลิกเลือกเซลล์ C2:F2 บนหัวตาราง จะได้สูตร =C2:F2
4. พิมพ์เครื่องหมายคูณ *
5. คลิกเลือกเซลล์ B3:B5 จากข้างตาราง จะได้สูตร =C2:F2*B3:B5
6. จากนั้นแทนที่จะกดปุ่ม Enter ให้กดปุ่ม Ctrl+Shift+Enter พร้อมกันทีเดียว 3 ปุ่ม จะทำให้เกิดเครื่องหมายวงเล็บปีกกาปิดหัวท้ายสูตรให้เองกลายเป็น {=C2:F2*B3:B5}


คำอธิบาย

  • สาเหตุที่ต้องเลือกพื้นที่ตาราง C3:F5 ไว้ก่อนแทนการเลือกเซลล์ C3 เพียงเซลล์เดียว เพื่อทำให้ผลลัพธ์ที่ได้จากสูตร Array คืนค่ากระจายคำตอบลงไปในตารางให้ครบทุกตำแหน่ง
  • ลองใช้ Mouse คลิกลงไปในช่อง Formula Bar เลือกเฉพาะส่วนของสูตร C2:F2 แล้วกดปุ่ม F9 จะพบว่าตำแหน่งอ้างอิงส่วนนี้เปลี่ยนเป็น {2,3,4,5} จากนั้นให้กดปุ่ม Esc เพื่อคืนสภาพสูตรตามเดิม
  • ลองใช้ Mouse คลิกลงไปในช่อง Formula Bar เลือกเฉพาะส่วนของสูตร B3:B5 แล้วกดปุ่ม F9 จะพบว่าตำแหน่งอ้างอิงส่วนนี้เปลี่ยนเป็น {10;20;30} จากนั้นให้กดปุ่ม Esc เพื่อคืนสภาพสูตรตามเดิม
  • หากกดปุ่ม F2 ตามด้วย F9 จะพบว่าสูตร Array เดิมบน Formula Bar {=C2:F2*B3:B5} จะเปลี่ยนเป็น ={20,30,40,50;40,60,80,100;60,90,120,150} แล้วขอให้สังเกตว่ามีเครื่องหมาย Comma และ Semi-Colon ขั้นอยู่ระหว่างตัวเลข (ซึ่งเครื่องหมาย Comma , นี้แสดงถึงว่าค่าถัดไปยังคงอยู่ใน Row เดิม ส่วนเครื่องหมาย Semi-Colon ; แสดงว่าค่าถัดไปต้องขึ้น Row ใหม่)
  • ดังนั้น ={20,30,40,50;40,60,80,100;60,90,120,150} จึงแสดงให้เห็นว่าสูตร Array นี้คืนค่าเป็นคำตอบซึ่งมีขนาดความกว้าง 4 Column และมีความสูง 3 Row ตามขนาดพื้นที่ตั้งแต่ C3:F5 ที่ต้องเลือกไว้ก่อน เพื่อให้กระจายค่าลงไปให้ครบนั่นเอง
  • หากหัวตารางมีขนาดเปลี่ยนไป ต้องลบสูตร Array ที่สร้างไว้เดิมนี้ทิ้งไปก่อนแล้วจึงสร้างสูตร Array ใหม่ลงไป ซึ่งในการลบสูตร Array ทิ้งนี้ หากตอนที่สร้างสูตรเกิดจากการสร้างพร้อมกันทีเดียวหลายเซลล์ จะเลือกลบสูตรทิ้งบางเซลล์ไม่ได้ ต้องเลือกเซลล์ C3:F5 ทั้งหมดแล้วจึงสั่งลบสูตรทิ้งพร้อมกันทีเดียวจึงจะลบได้
  • เนื่องจากสูตร Array ในตัวอย่างนี้เกิดจากการสร้างพร้อมกันทั้งตารางด้วยสูตรเดียว จึงทำให้ Excel คำนวณสูตรทั้งตารางสูตรคูณเพียงครั้งเดียว ถือเป็นเคล็ดลับและลัดที่น่าสนใจเป็นอย่างยิ่งเพราะถ้าสร้างได้จะทำให้แฟ้มคำนวณเร็วขึ้นอย่างผิดหูผิดตาทีเดียว (เร็วกว่าตัวอย่างสูตรคูณซึ่งสร้างสูตรลงไปในเซลล์เดียวแล้วต้อง Copy ไป Paste เพื่อให้คำนวณซ้ำอีกในเซลล์อื่น)
  • หากต้องการค้นหาว่าสูตร Array ที่เห็นนั้นสร้างขึ้นพร้อมกันจากเซลล์ใดถึงเซลล์ใด ให้เริ่มจากคลิกเลือกเซลล์สูตร Array นั้นๆก่อนเพียงเซลล์เดียว แล้วกดปุ่ม F5 > Special > กาช่อง Current array จะพบว่า Excel เลือกพื้นที่ตารางส่วนที่เกิดจากการสร้างด้วยสูตร Array พร้อมกันนั้นให้ทันที จากนั้นหากต้องการลบหรือสร้างสูตร Array ใหม่ลงไปก็ให้กระทำต่อพื้นที่ตารางที่เลือกให้แล้วนั้นร่วมกันเสมอ

Author: สมเกียรติ ฟุ้งเกียรติEmail: This email address is being protected from spambots. You need JavaScript enabled to view it.

E-Learning

Go to top