การอ้างอิงขั้นสูง — INDIRECT, OFFSET, ADDRESS

1. เมื่อสูตรต้องหาตำแหน่งเอง

บทความก่อนหน้าเราจัดการกับ Dynamic Arrays กันไปแล้ว คราวนี้ผมจะพาคุณเข้าสู่อีกโลกหนึ่งของสูตรที่เรียกว่า การอ้างอิงขั้นสูง ครับ ฟังดูซับซ้อน แต่จริงๆ แล้วมันคือการทำให้สูตรรู้จัก “ย้าย” และ “มองหา” ตำแหน่งได้ด้วยตัวเอง โดยที่เราไม่ต้องแก้สูตรทุกครั้ง

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

วันนี้เราจะมาทำความรู้จักฟังก์ชันสามตัวที่ทำงานคู่กันสุดแกร่งครับ

• INDIRECT เปลี่ยนข้อความให้กลายเป็นการอ้างอิงจริง
• OFFSET ย้ายจุดเริ่มต้นของช่วงไปตามที่บอก
• ADDRESS สร้างที่อยู่เซลล์ขึ้นมาจากเลขแถวกับคอลัมน์

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

ถ้ายังรู้สึกว่าแนวคิดนี้จับต้องยาก ให้ลองคิดง่ายๆ ว่า Excel ปกติแล้วเราพิมพ์ที่อยู่แบบตรงๆ อย่าง A1 หรือ B2 เข้าไปในสูตร แต่วิธีใหม่นี้เราสอนให้สูตร “อ่านที่อยู่ที่บอกไว้” แล้วไปหาข้อมูลเอง เช่น ถ้ามีช่องบอกว่า “ดูช่อง C2” สูตรก็จะวิ่งไปเอา C2 มาให้โดยไม่ต้องรื้อสูตร การทำแบบนี้ทำให้ไฟล์ที่ใช้ซ้ำทุกเดือนไม่ต้องมาแก้กันทุกครั้งอีกต่อไป

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

เปิดไฟล์ตัวอย่างแล้วทำไปด้วยกันนะครับ ผมจัดไว้ 3 ชีท เริ่มจาก INDIRECT เปลี่ยนข้อความเป็นที่อยู่จริง ข้ามไป OFFSET ขยับจุดเริ่มต้นของช่วง และปิดท้ายที่ ADDRESS สร้างที่อยู่จากเลขแถวกับคอลัมน์ ให้คุณเห็นการทำงานของแต่ละตัวชัดเจนแบบไม่ต้องจินตนาการ

ตัวอย่างทุกชีทใช้ตารางยอดขายสินค้าของร้านค้าชุมชน 3 สินค้าในปี ค.ศ. 2026 คือ ข้าวกล้อง น้ำผลไม้ และไข่ไก่ เก็บยอดขายรายเดือนไว้เป็นคอลัมน์ มกราคม กุมภาพันธ์ และมีนาคม เพื่อให้เห็นภาพว่าเมื่อผลลัพธ์ขยับตามตำแหน่งจริง มันช่วยลดงานเราได้มากแค่ไหน ลองไล่อ่านไปพร้อมกันในไฟล์นะครับ

แนวทางฝึกคือ เริ่มจากสูตรง่ายๆ ทีละตัวก่อน เช่น ใช้ INDIRECT ดึงค่าจากช่องที่พิมพ์ไว้อีกที แล้วค่อยเพิ่มความซับซ้อนขึ้น เช่น ต่อกับ ADDRESS ให้เกิดเป็นช่วงใหญ่ หรือเอา OFFSET ไปขยับจุดเริ่มต้นของยอด แล้วค่อยประเมินว่าโครงสร้างแบบไหนเหมาะกับไฟล์งานของเรามากที่สุด เพราะการใช้ทั้งสามตัวร่วมกันไม่ใช่เรื่องยาก แต่ต้องค่อยเป็นค่อยไปเพื่อไม่ให้งงครับ

📌 ข้อควรจำ: ตัวอย่างในไฟล์นี้เป็นข้อมูลปี ค.ศ. 2026 และใช้สูตรที่ตรงกับไฟล์จริง เปิดไฟล์แล้วลองเปลี่ยนตัวเลขในช่องที่พร้อมจะมองหาดูได้เลยครับ

2. INDIRECT — เปลี่ยนข้อความให้กลายเป็นที่อยู่

เริ่มต้นด้วยฟังก์ชันที่สร้างเซอร์ไพรส์ให้คนใช้ใหม่แทบทุกคนครับ INDIRECT รับค่าที่เป็น ข้อความ แล้วแปลงให้เป็น การอ้างอิงจริง ได้ในพริบตา

สมมติในชีท INDIRECT เรามีตารางยอดขายสินค้า 3 ตัวช่อง B2 ถึง D4 และอยากให้สูตรเลือกดูเฉพาะช่องที่เราพิมพ์ชื่อไว้อีกที เช่น พิมพ์ว่า C2 แล้วอยากให้สูตรดึงค่าจาก C2 ออกมา

=INDIRECT(B6)

ถ้า B6 มีข้อความว่า C2 สูตรนี้จะอ่านออกว่า “เอาค่าจาก C2 มาให้หน่อย” ซึ่ง C2 คือยอดขายข้าวกล้องเดือนกุมภาพันธ์ที่ 15,000 บาท ผลลัพธ์เลยเด้งออกมาเป็น 15,000 เหตุผลที่มันเจ๋งคือ ไม่ว่าคุณจะพิมพ์ที่อยู่ช่องไหนลงใน B6 ก็ตาม สูตรจะวิ่งไปหาข้อความนั้นให้โดยไม่ต้องแก้สูตรเลยสักครั้ง ลองเปลี่ยนเป็น D3 สูตรก็จะไปดึงค่าที่ D3 ทันที ลองทดลองกับช่องอื่นในไฟล์ตัวอย่างดูก็ได้ครับ

ความสนุกของ INDIRECT ยังมีอีก เพราะมันรับข้อความที่ต่อกันด้วยเครื่องหมายแอมเปอร์แซนด์ได้ด้วย เช่น ถ้าอยากให้รวมทั้งช่วง B2 ถึง D4 แบบไม่ต้องพิมพ์ช่วงตายตัว

=SUM(INDIRECT("B2:D4"))

เขียนเป็นข้อความทั้งหมดทั้งช่วง แล้ว INDIRECT แปลงให้เป็นช่วงจริงที่ SUM อ่านได้ทันที รวมยอดทั้ง 9 ช่องได้เต็มๆ โดยไม่ต้องค่อยๆ ไล่เลือกเซลล์

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

จุดที่คนไทยชอบใช้บ่อยคือการอ้างอิงข้ามชีทแบบไดนามิก สมมติเราอยากให้สูตรไปดึงค่าจากชีทชื่อ OFFSET ที่เซลล์ B2 เขียนได้แบบนี้

=INDIRECT("OFFSET!B2")

ข้อความ OFFSET!B2 แปลว่า ไปที่ชีท OFFSET เซลล์ B2 พอ INDIRECT รับไปก็แปลงเป็นที่อยู่จริงให้ทันที แบบนี้เวลาโครงสร้างไฟล์เปลี่ยน เราก็แค่เปลี่ยนข้อความ ไม่ต้องไปแกะสูตรทีละตัว ทั้งหมดนี้ทำให้ INDIRECT เหมาะกับไฟล์ที่ต้องสร้างตัวเลือกให้ผู้ใช้พิมพ์เองหรือเลือกจากดรอปดาวน์ได้อย่างมาก

เทคนิคที่คนทำรายงานชอบใช้คือจับ INDIRECT มาเข้ากับ Data Validation เพื่อทำดรอปดาวน์ให้ผู้ใช้เลือกเดือนได้เอง เช่น สร้างตารางแยกที่แต่ละคอลัมน์มีชื่อชีทหรือชื่อช่วงไว้ แล้วให้ผู้ใช้เลือกชื่อเดือนจากดรอปดาวน์ จากนั้นสูตรใช้ INDIRECT ดึงค่าจากคอลัมน์ที่เลือกนั้นมาแสดงทันที ข้อดีคือผู้ใช้แค่เลือก ไม่ต้องเข้าไปแก้สูตรแม้แต่ตัวเดียว เหมาะกับไฟล์ที่ใช้ร่วมกันหลายคนในทีม เพราะไม่มีใครต้องเสี่ยงไปลบหรือแก้สูตรผิดจุด

อีกมุมที่ INDIRECT เก่งคือการสร้างสูตรแบบสรุปหลายช่วงพร้อมกัน เช่น สมมติมีชีทแยกของแต่ละสาขา และอยากให้สรุปรวมดึงยอดจากสาขาที่เลือกได้ ก็เก็บชื่อชีทไว้ในเซลล์ตัวเลือก แล้วใช้ =SUM(INDIRECT(ชื่อชีท&"!B2:D4")) เพื่อให้สูตรไปรวมยอดของชีทนั้นทั้งชุด เวลาเปิดสาขาใหม่ก็แค่เพิ่มชีทแล้วเลือกชื่อในดรอปดาวน์ สูตรรวมจะวิ่งไปยังชีทใหม่ให้เอง โดยไม่ต้องไปแก้สูตรที่เขียนค้างไว้เลย

💡 เคล็ดลับ: INDIRECT เปรียบเหมือน “ล่าม” ที่คอยแปลข้อความให้เป็นที่อยู่ ใช้คู่กับ Data Validation ที่ให้ผู้ใช้เลือกชื่อเดือนหรือชื่อช่วงจากดรอปดาวน์ได้ จะช่วยลดการแก้สูตรลงได้เยอะมากครับ

⚠️ ข้อควรระวัง: ถ้าข้อความที่ใส่ใน INDIRECT ผิด เช่น พิมพ์ที่อยู่ไม่ถูกต้องหรือไม่มีชีทชื่อนั้นจริง จะได้ error #REF! ขึ้นทันที ดังนั้นควรเช็คชื่อชีทและที่อยู่ให้ตรงเป๊ะเสมอครับ

3. OFFSET — ย้ายจุดเริ่มต้นของช่วง

ต่อมาคือ OFFSET ครับ ฟังก์ชันนี้ต่างจาก INDIRECT ตรงที่มันรับ “ส่วนต่างของตำแหน่ง” แล้วค่อยขยับจากจุดเริ่มต้นไปหาจุดที่ต้องการ เหมือนเรายืนอยู่ที่จุดหนึ่งแล้วเดินไปข้างหน้าหรือข้างหลังกี่ก้าวก็ได้

โครงสร้างเป็นแบบนี้

=OFFSET(จุดเริ่มต้น, ขยับลง/ขึ้นกี่แถว, ขยับขวา/ซ้ายกี่คอลัมน์, ความสูง, ความกว้าง)

สมมติเรายืนที่ B2 (ยอดขายข้าวกล้อง เดือนมกราคม 12,000 บาท) แล้วอยากเริ่มต้นที่เดิม

=OFFSET(B2,0,0)

ขยับ 0 แถว 0 คอลัมน์ ก็ได้ค่า 12,000 กลับมาเหมือนเดิม แต่ถ้าอยากเดินลงไป 2 แถวถึงไข่ไก่เดือนมกราคม (B4 = 5,000 บาท)

=OFFSET(B2,2,0)

ก็จะดึงค่าที่ B4 ออกมา 5,000 บาท หรืออยากเดินไปทางขวา 2 คอลัมน์ถึงข้าวกล้องเดือนมีนาคม (D2)

=OFFSET(B2,0,2)

ได้ค่า 11,000 จาก D2 ทันที จะเห็นว่าตัวเลขสองตัวแรกคือ “ก้าวเท้า” ที่บอกให้ OFFSET เดินไปยังไง

แต่จุดเด่นจริงๆ อยู่ที่พารามิเตอร์ความสูงกับความกว้าง เพราะมันบอกให้ OFFSET สร้างช่วงขึ้นมาหนึ่งก้อน เช่น อยากรวมยอดขายทั้งหมด 3 สินค้า 3 เดือน (B2 ถึง D4)

=SUM(OFFSET(B2,0,0,3,3))

OFFSET(B2,0,0,3,3) สร้างช่วงขนาด 3 แถวคูณ 3 คอลัมน์นับจาก B2 จากนั้น SUM ก็รวมทั้งก้อนได้เลย เหมาะกับสรุปยอดหน้าตารางมาก

ส่วนใครอยากสรุปแค่ กุมภาพันธ์ กับ มีนาคม โดยไม่เอาเดือนมกราคมมาเกี่ยว

=SUM(OFFSET(B2,0,1,3,2))

ขยับไปทางขวา 1 คอลัมน์ (มาที่คอลัมน์ กุมภาพันธ์) แล้วสร้างช่วงกว้าง 2 คอลัมน์ สูง 3 แถว ได้ผลรวมเฉพาะ ก.พ. กับ มี.ค. ของทุกสินค้า โดยไม่ต้องไปลากเลือกทีละเซลล์ เรียกว่ายืดหยุ่นมากสำหรับรายงานที่โครงสร้างช่วงเปลี่ยนไปเรื่อยๆ

ถ้าอยากให้ OFFSET สร้างช่วงแบบ “ขยับตาม” เหมือนหน้าต่างที่เลื่อนไปเรื่อยๆ ก็ทำได้โดยให้พารามิเตอร์เหล่านั้นอ้างอิงค่าในเซลล์บางช่อง เช่น ให้ช่องที่บอกขั้นแถวเก็บตัวเลขไว้ แล้ว =SUM(OFFSET(B2,แถวที่ขยับ,0,3,3)) เมื่อเปลี่ยนตัวเลขในช่องนั้น ช่วงที่จะสรุปก็จะเลื่อนขึ้นหรือลงตามทันทีโดยไม่ต้องแก้สูตร เหมาะกับแดชบอร์ดที่อยากให้ผู้ใช้เลื่อนดูยอดแต่ละช่วงเดือนเป็นรอบๆ ได้เอง

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

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

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

4. ADDRESS — สร้างที่อยู่จากเลขแถวกับคอลัมน์

มาถึงตัวที่ดูเล็กแต่อยู่คู่กับอีกสองตัวได้สนิท คือ ADDRESS ครับ หน้าที่ของมันคือรับเลขแถวกับเลขคอลัมน์ แล้วสร้างเป็น “ที่อยู่” ในรูปแบบข้อความให้เรา

=ADDRESS(2,2)

แปลว่า “ที่อยู่แถวที่ 2 คอลัมน์ที่ 2” ได้ผลลัพธ์เป็นข้อความว่า $B$2 ส่วน

=ADDRESS(4,4)

ได้ $D$4 ครับ ตรงนี้ถ้าคุณเห็นเลข 2 กับ 4 แล้วงงว่ามันคืออะไร ให้เข้าใจง่ายๆ ว่าเลขตัวแรกคือแถว (row) ตัวที่สองคือคอลัมน์ (column) นับจากซ้ายสุดเป็น 1 ดังนั้น B คือคอลัมน์ที่ 2, D คือคอลัมน์ที่ 4

พอได้ที่อยู่เป็นข้อความมาแล้ว ก็นำไปต่อกับ INDIRECT ให้เป็นสูตรแบบไดนามิกได้ เช่น อยากดึงค่าที่ B2 ออกมาโดยไม่ต้องเขียนช่องตายตัว

=INDIRECT(ADDRESS(2,2))

ADDRESS(2,2) สร้างข้อความ $B$2 แล้ว INDIRECT แปลงเป็นที่อยู่จริงให้เราได้ค่า 12,000 จาก B2 มา เห็นพลังของการทำงานคู่กันแล้วใช่ไหมครับ

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

=SUM(INDIRECT(ADDRESS(2,2)&":"&ADDRESS(4,4)))

ลองแยกอ่าน: ADDRESS(2,2) ได้ B2, ADDRESS(4,4) ได้ D4, แล้วเชื่อมด้วยเครื่องหมาย & “:” & ก็จะได้ข้อความว่า B2:D4 จากนั้น INDIRECT แปลงให้เป็นช่วงจริง ให้ SUM รวมได้ทันที แบบนี้ถ้าโครงสร้างตารางเปลี่ยน เราก็แค่เปลี่ยนเลขแถวกับคอลัมน์ของจุดเริ่มและจุดจบ ไม่ต้องมานั่งคำนวณที่อยู่เองเลย

ความน่าสนใจอีกอย่างคือ ADDRESS มักใช้ร่วมกับ MATCH หรือฟังก์ชันหาตำแหน่งอื่น เพื่อบอกว่า “เจอสินค้าอยู่แถวไหน แล้วสร้างที่อยู่ให้หน่อย” โดยไม่ต้องเดาด้วยสายตา ยิ่งผูกกับ Data Validation และ Named Ranges ก็ยิ่งทำให้สูตรอ่านง่ายและดูแลง่ายขึ้นอีกชั้น

ลองนึกภาพว่าคุณมีตารางสินค้าที่เพิ่มแถวไปเรื่อยๆ ทุกวัน แล้วอยากให้สูตรหาจุดจบของข้อมูลโดยอัตโนมัติโดยไม่ต้องรู้ว่าสินค้าตัวสุดท้ายอยู่แถวไหน วิธีคือใช้ COUNTA นับจำนวนสินค้าที่ไม่ว่าง ได้เลขแถวสุดท้ายมา แล้วส่งให้ ADDRESS สร้างที่อยู่ของแถวสุดท้ายนั้นจากนั้นต่อด้วย INDIRECT จับมาเป็นช่วงให้ SUM รวมทั้งหมดได้แบบไม่ต้องเดาเลยว่าตารางยาวแค่ไหน ตรงนี้เองที่ทั้งสามตัวทำงานร่วมกันจนกลายเป็นระบบที่ยืดหยุ่นสุดๆ

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

ลองฝึกตามในชีท ADDRESS ของไฟล์ตัวอย่างดูครับ จะเห็นคอลัมน์สรุปที่ใช้ ADDRESS สร้างที่อยู่จากเลขแถวกับคอลัมน์ แล้วต่อกับ INDIRECT ให้ได้ช่วงจริงๆ ถ้าลองเปลี่ยนเลข 2 หรือ 4 ในสูตรดู คุณจะเห็นว่าช่วงที่สรุปเปลี่ยนตามทันที นี่คือภาพจริงของสิ่งที่เราอธิบายกันมาตลอดบทความครับ

📌 ข้อควรจำ: ADDRESS ให้ผลลัพธ์เป็นข้อความ ไม่ใช่ค่าข้อมูล จึงต้องจับคู่กับ INDIRECT เสมอถ้าอยากให้มันกลายเป็นการอ้างอิงที่ใช้กับ SUM หรือฟังก์ชันอื่นได้จริงครับ

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

5. ความหมายของคำว่า Volatile และผลข้างเคียง

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

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

ลองนึกภาพไฟล์รายงานบัญชีที่มี OFFSET กับ INDIRECT ซ้อนกันเป็นสิบๆ จุด ทุกครั้งที่คุณพิมพ์อะไรก็ตามลงในไฟล์ Excel ต้องไล่คำนวณสูตรเหล่านั้นใหม่ทั้งหมด ดังนั้นถ้าไฟล์เริ่มหนืด ให้ลองดูว่าเรามีสูตร volatile เยอะเกินไปหรือเปล่า แล้วหาทางลดการใช้ลงโดยเลือกใช้วิธีอ้างอิงแบบธรรมดาที่ไม่ volatile ก่อน

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

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

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

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

อีกเรื่องที่มือใหม่มักพลาดคือการใช้ OFFSET หรือ INDIRECT ซ้อนกันหลายชั้นจนอ่านยาก เช่น OFFSET(INDIRECT(…)) ต่อกันยาวเหยียด ถึงแม้จะได้ผลลัพธ์ก็ตาม แต่เวลามีปัญหาแล้วต้องตามแก้จะปวดหัวมาก เพราะสูตรซับซ้อนเกินจำเป็น ถ้าเป็นไปได้ควรแยกขั้นตอนหรือใช้ Named Ranges ตั้งชื่อช่วงไว้แทน เพื่อให้สูตรอ่านเหมือนประโยคสั้นๆ ต่อกัน มากกว่าจะเป็นตัวอักษรยาวๆ ที่เดาไม่ออกว่าทำอะไร

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

💡 เคล็ดลับ: ลองไล่อ่านสูตรยาวๆ ด้วยการแยกส่วนทีละชั้น เช่น SUM(INDIRECT(ADDRESS(2,2)&":"&ADDRESS(4,4))) อ่านว่า เริ่มจากสร้างที่อยู่ 2 จุด แล้วเชื่อมเป็นช่วง แล้วค่อยแปลงเป็นช่วงจริง จึงจะเห็นว่าจริงๆ แล้วไม่ยากอย่างที่คิดครับ

6. สถานการณ์งานจริง — ประยุกต์รวมกันทั้งชุด

ลองประยุกต์ความรู้ที่เพิ่งได้ไปกับงานจริงดูนะครับ หกสถานการณ์ด้านล่างเป็นโจทย์ที่พบบ่อยในออฟฟิศไทย

สถานการณ์ที่ 1: ฝ่ายขายทำรายงานเดือนที่เลือกได้

อยากทำดรอปดาวน์ให้ผู้ใช้เลือกเดือน แล้วสูตรสรุปยอดให้อัตโนมัติ ใช้ INDIRECT ผูกกับ Data Validation เช่น ให้เซลล์ตัวเลือกเก็บชื่อช่วงไว้ แล้ว =SUM(INDIRECT(ชื่อช่วง)) สรุปยอดทั้งเดือนโดยไม่ต้องแก้สูตร

สถานการณ์ที่ 2: ฝ่ายบัญชีสรุปยอดช่วงล่าสุด

อยากรวมยอดขายเฉพาะ 2 เดือนล่าสุดของตาราง ใช้ OFFSET ขยับจุดเริ่มต้นแล้วกำหนดความกว้าง เช่น =SUM(OFFSET(B2,0,1,3,2)) รวม ก.พ. กับ มี.ค. ได้ทันทีแม้เดือนจะเพิ่มเข้ามา

สถานการณ์ที่ 3: ฝ่ายคลังหาที่อยู่ของจุดที่ต้องการ

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

สถานการณ์ที่ 4: ฝ่ายการตลาดสรุปข้ามชีท

อยากให้สรุปรวมดึงข้อมูลจากหลายชีทโดยไม่ต้องเขียนสูตรแยก ใช้ =INDIRECT("OFFSET!B2") เพื่อไปดึงค่าข้ามชีทแบบไดนามิก เวลาเพิ่มชีทหรือเปลี่ยนชื่อก็แค่แก้ข้อความที่เดียว

สถานการณ์ที่ 5: ฝ่ายวางแผนทำไฟล์ที่ปรับได้ง่าย

อยากได้ไฟล์รายงานที่ผู้ใช้เลือกเองได้โดยไม่ต้องเข้าไปแก้สูตร ใช้ INDIRECT กับ ADDRESS ทำงานคู่กันเพื่อสร้างหลายชั้นของที่อยู่จากตัวเลือกหนึ่งเดียว ลดการแก้ไขด้วยมือในไฟล์ที่ใช้ซ้ำทุกเดือน

สถานการณ์ที่ 6: ฝ่ายการเงินทำสรุปเลื่อนช่วงรายเดือน

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

7. สรุป

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

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

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

สิ่งที่ควรจำที่สุดคือ ทั้งสามตัวทำงานได้ดีที่สุดเมื่อใช้ คู่กัน เช่น ADDRESS สร้างที่อยู่ แล้ว INDIRECT ปลดล็อกให้เป็นช่วงจริง แล้ว OFFSET ขยับไปหยิบช่วงที่ต้องการ การซ้อนกันแบบนี้ทำให้เราเขียนไฟล์รายงานที่ “ขยับตามตัวเอง” ได้โดยไม่ต้องมานั่งแก้สูตรทีละตัวทุกครั้งที่ข้อมูลเปลี่ยน

ก่อนจบผมอยากฝากเรื่องการออกแบบไว้อีกนิดนะครับ การใช้การอ้างอิงขั้นสูงไม่ได้หมายความว่าต้องใช้ทุกสูตรให้เป็นตัว volatile ไปหมด ควรเลือกใช้เฉพาะจุดที่ต้องการความยืดหยุ่นจริงๆ เช่น จุดที่ต้องสร้างตัวเลือกหรือย้ายช่วงแบบอัตโนมัติ แล้วจุดอื่นใช้การอ้างอิงธรรมดาหรือ Named Ranges แทน เพื่อให้ไฟล์ทั้งเร็วและดูแลง่าย

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

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

สุดท้ายอยากให้ลองเปิดไฟล์ตัวอย่างแล้วไล่เล่นทั้ง 3 ชีทดูนะครับ เริ่มจาก INDIRECT เปลี่ยนช่องที่พิมพ์ไว้อีกที ข้ามไป OFFSET ขยับจุดเริ่มต้น และปิดท้ายที่ ADDRESS สร้างที่อยู่จากเลขแถวกับคอลัมน์ ลองเปลี่ยนตัวเลขในช่องตัวเลือกดู แล้วจะเห็นว่าสูตรขยับตามให้โดยไม่ต้องแก้สักครั้ง

บทความหน้า เราจะมาสอนเรื่อง การกันข้อผิดพลาด — IFERROR, IFNA, ISERROR และการตรวจสูตร ให้ไฟล์ของคุณสะอาดตาและเลิกเจอ error ขวางทางกันครับ เจอกัน! 😊