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

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

วิธีที่ 1 : ใช้สูตร Indirect(“Text บอกตำแหน่ง”) แบบเซลล์เดียว

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

แทนที่จะสร้างสูตรตรงๆลงไปว่า =A1 คราวนี้ให้ยอมเปลืองเซลล์หน่อย เริ่มจากใช้เซลล์ C4 และ C5 เป็นเซลล์รับตัวอักษร a และเลข 1 ลงไปตามลำดับ จากนั้นนำตัวอักษรกับตัวเลขมาต่อกันในเซลล์ C6 โดยใช้สูตร =C4&C5 จะได้คำว่า a1 ซึ่งเป็น Text ที่บอกตำแหน่งตามแบบมาตรฐานของตำแหน่งอ้างอิงใน Excel (ถ้าอยากลัดขั้นตอน จะใช้เซลล์ C6 สำหรับพิมพ์ตำแหน่งเซลล์ a1 ลงไปเลยก็ได้ โดยไม่ต้องเสียเซลล์ C4 และ C5 เพื่อแยกตำแหน่งแต่ละส่วนไว้ก่อน)

จากนั้นเซลล์ C7 ใช้สูตร =Indirect(C6) จะได้ค่าเป็นตัวเลข 11 ซึ่งเป็นค่าจากเซลล์ A1 นั่นเอง ทีนี้หากต้องการค่าจากเซลล์ใด ให้ใช้ตำแหน่งอ้างอิงตามค่าในเซลล์ C6 ซึ่งอาจเกิดจากการพิมพ์แยกส่วนของตัวอักษรและตัวเลขที่บอกตำแหน่ง Column และตำแหน่ง Row ลงไปในเซลล์ C4 และ C5 หรือจะใช้วิธีพิมพ์ตำแหน่งเซลล์ที่ต้องการลงไปเองโดยตรงในเซลล์ C6 เลยก็ได้ เช่น

• ถ้าพิมพ์คำว่า a2 ลงไปในเซลล์ C6 ก็จะได้คำตอบในเซลล์ C7 เท่ากับค่าจากเซลล์ a2
• ถ้าพิมพ์คำว่า a3 ลงไปในเซลล์ C6 ก็จะได้คำตอบในเซลล์ C7 เท่ากับค่าจากเซลล์ a3
• ถ้าพิมพ์คำว่า sheet3!a2 ลงไปในเซลล์ C6 ก็จะได้คำตอบในเซลล์ C7 เท่ากับค่าจากเซลล์ a2 แต่คราวนี้มาจากชีทชื่อ Sheet3 เป็นสูตรดึงค่าข้ามชีทตามชื่อชีทที่ต้องการ

วิธีที่ 2 : ใช้สูตร Indirect(“Text บอกตำแหน่ง”) แบบใช้ตำแหน่งขอบเขตตาราง

วิธีนี้มีหลักการคล้ายกับวิธีแรกที่ใช้ตำแหน่งอ้างอิงจากเซลล์เดียว เพียงแต่ตำแหน่งอ้างอิงแบบขอบเขตตารางต้องอยู่ในโครงสร้างของ ตำแหน่งเซลล์ต้นทาง:ตำแหน่งเซลล์ปลายทาง เช่น A1:A3 จากนั้นจึงนำตำแหน่งอ้างอิงที่ได้ไปใช้กับสูตร Indirect

สมมติว่าคุณต้องการหาผลรวมของตัวเลขในเซลล์ A1:A3 ซึ่งปกติต้องใช้สูตร =Sum(A1:A3) ซึ่งสูตรนี้ยังไงๆก็จะหาผลรวมจากเซลล์ A1:A3 ไปตลอด แต่ถ้าต้องการให้หายอดรวมจากเซลล์ใดก็ได้ถึงเซลล์ใดก็ได้ล่ะ จะสร้างสูตรอย่างไรครั้งเดียวแล้วไม่ต้องแก้ไขสูตรอีกเลยในภายหลัง

จากภาพข้างต้น มี Text ที่บอกตำแหน่งเซลล์ต้นทาง a1 ในเซลล์ C6 ตรงกับตัวอย่าง Indirect แบบเซลล์เดียว เพียงแต่คราวนี้ให้ใช้เซลล์ E4, E5, และ E6 สำหรับสร้าง Text ที่บอกตำแหน่งของเซลล์ปลายทาง a3

จากนั้นใช้เซลล์ D8 นำตำแหน่งเซลล์ต้นทางมาเชื่อมต่อกับเครื่องหมาย : แล้วเชื่อมต่อกับตำแหน่งเซลล์ปลายทางด้วยสูตร =C6&”:”&E6 จะได้ค่า a1:a3

หากต้องการหายอดรวมก็สามารถนำ Text ที่บอกตำแหน่งในเซลล์ D8 ไปใช้ร่วมกับสูตร Sum และ Indirect เช่น เซลล์ D9 ใช้สูตร =Sum(Indirect(D8)) จะหายอดรวมตัวเลขจากตารางในขอบเขตที่ต้องการ

นอกจากนี้แทนที่จะนำค่าในเซลล์ D8 ซึ่งมีสูตรเป็น =C6&”:”&E6 ไปใช้ต่อในสูตร Sum เพื่อหาผลรวม เรายังสามารถนำขอบเขตตำแหน่งอ้างอิงที่ได้นี้ไปใช้ในสูตรอื่นได้เช่นเดียวกัน เพียงแต่ต้องใช้สูตร Indirect นำหน้าตำแหน่งอ้างอิงไว้เสมอ เช่น =Max(Indirect(D8)), =Min(Indirect(D8)), =Average(Indirect(D8)), =Index(Indirect(D8),2,1) เป็นต้น

ยิ่งกว่านั้นหากนำสูตร =$C$6&”:”&$E$6 หรือ =Indirect($C$6&”:”&$E$6) ไปตั้งชื่อเป็น Formula Name ว่า MyRange1 และ MyRange2 ตามลำดับ (หรือชื่ออื่นใดก็ได้ โดยตำแหน่งอ้างอิงที่ใช้ใน Formula Name ต้องกำหนดตำแหน่งแบบ Absolute จึงจะหาค่าได้ถูกต้อง) จะช่วยให้การนำตำแหน่งอ้างอิงไปใช้ต่อได้สะดวกขึ้น เช่น จากเดิมต้องใช้สูตร =Sum(Indirect(D8)) ก็จะเทียบเท่ากับสูตร =Sum(Indirect(MyRange1)) หรือ =Sum(MyRange2) ซึ่งนอกเหนือจากจะเป็นสูตรที่ดูดีกว่ามากแล้วยังไม่ขึ้นกับค่าในเซลล์ D8 ที่อาจถูกแก้ไขอย่างพลั้งเผลอให้ผิดเพี้ยนไปในภายหลังได้อีกด้วย

วิธีที่ 3 : ใช้สูตร Indirect(“Text บอกตำแหน่ง”) แบบใช้ชื่อ Range Name

ตัวอย่างนี้ต่างจากตัวอย่างที่ผ่านมาเพียงแค่นำชื่อ Range Name มาใช้แทนตำแหน่งอ้างอิงโดยตรง เช่น หากตั้งชื่อพื้นที่ตาราง D3:D6 ว่า Chonburi และตั้งชื่อพื้นที่ตาราง C4:E4 ว่า Year2 (ให้ใช้คำสั่ง Formulas > Create from selection เพื่อตั้งชื่อพื้นที่ตามคำที่อยู่บนของตารางรวดเดียว)

จากนั้นใช้เซลล์ C9 และ C10 สำหรับพิมพ์ชื่อ Range Name ลงไป เช่น พิมพ์คำว่า Chonburi และ Year2 ลงไปตามลำดับ

• เซลล์ D9 หายอดรวมของ Chonburi ได้โดยใช้สูตร =Sum(Indirect(C9))
• เซลล์ D10 หายอดรวมของ Year2 ได้โดยใช้สูตร =Sum(Indirect(C10))
• เซลล์ E9 หาค่าของ Chonburi ในปี Year2 ได้โดยใช้สูตรหาค่าแบบ Intersection
  =Indirect(C9) Indirect(C10)

ข้อควรระวังในการใช้สูตร Indirect

สูตรทุกสูตรของ Excel มีทั้งข้อดีและข้อด้อย สูตรบางสูตรที่พวกเราชอบใช้เพราะเป็นสูตรที่เขียนสั้นๆ แต่กลับทำให้ Excel ทำงานช้าลงก็มี (เช่น สูตร VLookup เป็นต้น) ซึ่งสูตร Indirect ก็มีข้อควรระวังอยู่ด้วยกล่าวคือ สูตร Indirect สามารถ link ข้อมูลข้ามแฟ้มได้ แต่แฟ้มต้นทางต้องเปิดขึ้นพร้อมกับแฟ้มปลายทางที่ใช้สูตรนี้อยู่ และเป็นสูตรหนึ่งที่ถือเป็น Volatile Function

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

สูตรที่จัดเป็นประเภท Volatile Function ได้แก่ สูตร Areas, Cell, Column, Columns, Indirect, Info, Now, Offset, Rand, Row, Rows, Today

วิธีที่ 4 : ใช้สูตรเพื่อย้ายตำแหน่งอ้างอิงโดยอัตโนมัติ

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

• สูตรที่สามารถเปลี่ยนย้ายตำแหน่งอ้างอิงตามเงื่อนไขที่กำหนด เช่น IF, Choose, Index, Offset
• สูตรที่สามารถเปลี่ยนย้ายตำแหน่งอ้างอิงโดยอัตโนมัติ เช่น VLookup, Match ที่นำมาใช้ร่วมกับสูตร Index หรือ Offset, Address ที่นำมาใช้ร่วมกับสูตร Indirect

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