เชี่ยวชาญฟังก์ชัน INDIRECT ใน Excel: 8 เทคนิคอ้างอิงช่วงข้อมูลแบบไดนามิก

ฟังก์ชัน INDIRECT (กลุ่ม Lookup & Reference) ใช้ส่งคืน Reference (การอ้างอิง) ที่ระบุโดยสตริงข้อความ (Text String) ทำให้สามารถดึงค่าจากเซลล์หรือช่วงข้อมูลได้อย่างยืดหยุ่น ไม่ต้องฮาร์ดโค้ดที่อยู่เซลล์ ในบทความนี้จะสำรวจ 8 วิธีการใช้ INDIRECT Range อย่างครบถ้วน

เพื่อความเข้าใจง่าย ใช้ชุดข้อมูลยอดขาย 4 ไตรมาส มี 5 คอลัมน์: Sales Person, Quarter-1, Quarter-2, Quarter-3, Quarter-4

ดาวน์โหลดไฟล์ฝึกปฏิบัติ

8 วิธีใช้ Excel INDIRECT Range

1. ดึงค่าด้วย Cell Reference โดยตรง

ใช้ INDIRECT อ้างอิงช่วงเซลล์โดยตรงผ่านข้อความ

ต้องการค่าช่วง C4:C8 (Quarter-1)

• ที่เซลล์ H4 ใส่สูตร: =INDIRECT("C4:C8")

ใน INDIRECT ใส่ ref_text เป็นสตริง "C4:C8"

• กด ENTER → ได้ค่าทุกเซลล์ในช่วงนั้น

2. ดึงค่าผ่าน Cell Reference ที่เก็บไว้ในเซลล์

เก็บที่อยู่ช่วงไว้ในเซลล์ แล้วอ้างอิงผ่าน INDIRECT

ต้องการค่า D4:D8 (Quarter-2) โดยเก็บที่อยู่ไว้ที่ H4

• ที่ I4 ใส่: =INDIRECT(H4)

INDIRECT อ่านค่าใน H4 (ซึ่งคือ "D4:D8") แล้วดึงข้อมูลมา

• กด ENTER → ได้ค่าจาก D4:D8

3. สร้าง Reference จากค่าในเซลล์ (Concatenate Column + Row)

รวมชื่อคอลัมน์ (ตัวอักษร) กับหมายเลขแถวจากเซลล์เพื่อสร้าง Reference

• ที่ I4 ใส่: =INDIRECT("E"&H4)

H4 เก็บค่า 5 → "E" & 5 = "E5" → INDIRECT ดึงค่าเซลล์ E5

• กด ENTER → ได้ค่า E5

4. หาผลรวม (SUM) ของ Named Range ด้วย INDIRECT

ใช้ Named Range ร่วมกับ INDIRECT แล้วคำนวณด้วย SUM

สร้าง Named Range:

1. เลือกช่วง C4:C8
2. ที่ Address Bar (กล่องชื่อ) พิมพ์ชื่อ: Quarter_1 กด Enter

คำนวณยอดรวม Quarter_1:

• ที่ I4 ใส่: =SUM(INDIRECT(I3)) (I3 เก็บชื่อ "Quarter_1")

INDIRECT(I3) → ช่วง C4:C8 → SUM รวมค่า → ได้ยอดขาย Quarter_1

• กด ENTER → ได้ผลรวม

5. หาค่าเฉลี่ย (AVERAGE) ของ Named Range

• ที่ I4 ใส่: =AVERAGE(INDIRECT(I3))

INDIRECT ดึงช่วงข้อมูล → AVERAGE คำนวณค่าเฉลี่ย

• กด ENTER → ได้ค่าเฉลี่ย Quarter_1

6. หาค่าสูงสุด (MAX) ของ Named Range

• ที่ I4 ใส่: =MAX(INDIRECT(I3))

• กด ENTER → ได้ยอดขายสูงสุด Quarter_1

7. หาค่าต่ำสุด (MIN) ของ Named Range

• ที่ I4 ใส่: =MIN(INDIRECT(I3))

• กด ENTER → ได้ยอดขายต่ำสุด Quarter_1

8. หาค่าเฉลี่ยของ 3 ค่าที่น้อยที่สุด (SMALL + INDIRECT)

รวม AVERAGE, SMALL, ROW, INDIRECT เพื่อหาค่าเฉลี่ย 3 ค่าน้อยสุดของ Quarter-4 (F4:F8)

• ที่ I4 ใส่: =AVERAGE(SMALL(F4:F8,ROW(INDIRECT("1:3"))))

การทำงาน:

• INDIRECT("1:3") → สร้างอาร์เรย์ {1;2;3}
• ROW(...) → {1;2;3} (ตำแหน่ง k สำหรับ SMALL)
• SMALL(F4:F8, {1;2;3}) → 3 ค่าน้อยสุด
• AVERAGE(...) → ค่าเฉลี่ยของ 3 ค่านั้น

• กด ENTER → ได้ค่าเฉลี่ย 3 ค่าน้อยสุด Quarter_4

ข้อควรจำ

• 🔺 #REF! Error: ref_text ไม่ใช่ Reference ที่ถูกต้อง หรือเกินขอบเขตช่วง
• 🔺 #NAME? Error: สะกดชื่อฟังก์ชันผิด

ส่วนฝึกปฏิบัติ

สรุป

บทความนี้ครอบคลุม 8 รูปแบบการใช้ INDIRECT Range: อ้างอิงตรง, ผ่านเซลล์, สร้าง Reference จากค่า, Named Range กับ SUM/AVERAGE/MAX/MIN และสูตรขั้นสูงกับ SMALL/ROW หากมีข้อเสนอแนะ สามารถทิ้งความคิดเห็นได้ครับ

บทความเกี่ยวข้อง

• How to Use Indirect Address in Excel (12 Examples)
• INDIRECT Function with Sheet Name in Excel (4 Criteria)
• INDIRECT Function Excel: Get values from different sheet
• Create Drop-Down List Using INDIRECT Function in Excel