สูตร Excel SUM และ INDIRECT Dynamic Range

Microsoft Excel มีเทคนิคที่ยอดเยี่ยมและการใช้สูตรช่วงไดนามิก SUM และ INDIRECT เป็นเพียงสองวิธีที่สามารถจัดการกับข้อมูลที่คุณมีได้อย่างง่ายดาย

สรุปสูตรสูตรแบบ SUM - INDIRECT

การใช้ ฟังก์ชัน INDIRECT ในสูตร Excel ทำให้สามารถเปลี่ยนช่วงของการอ้างอิงเซลล์ที่ใช้ในสูตรโดยไม่ต้องแก้ไขสูตรเอง

INDIRECT สามารถใช้กับฟังก์ชันจำนวนหนึ่งที่ยอมรับการอ้างอิงเซลล์เป็นอาร์กิวเมนต์เช่น ฟังก์ชัน OFFSET และ SUM

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

ทางอ้อมทำเช่นนี้โดยอ้างถึงข้อมูลในเซลล์โดยทางอ้อมผ่านตำแหน่งกลาง

ตัวอย่าง: SUM - INDIRECT สูตรที่ใช้ในการรวมช่วงของค่าแบบไดนามิก

ตัวอย่างนี้ใช้ข้อมูลที่แสดงในภาพด้านบน

สูตร SUM - INDIRECT ที่สร้างขึ้นโดยใช้ขั้นตอนการสอนด้านล่างคือ:

= SUM (INDIRECT ("D" & E1 & ": D" และ E2))

ในสูตรนี้อาร์กิวเมนต์ฟังก์ชันของ INDIRECT ที่ซ้อนกันมีการอ้างอิงไปยังเซลล์ E1 และ E2 ตัวเลขในเซลล์เหล่านั้น 1 และ 4 เมื่อรวมกับส่วนที่เหลือของอาร์กิวเมนต์ของ INDIRECT ให้สร้างเซลล์อ้างอิง D1 และ D4

เป็นผลให้ช่วงของตัวเลขโดยรวมของ ฟังก์ชัน SUM คือข้อมูลที่อยู่ใน ช่วง ของเซลล์ D1 ถึง D4 - ซึ่งเท่ากับ 50

โดยการเปลี่ยนตัวเลขที่อยู่ในเซลล์ E1 และ E2; อย่างไรก็ตามคุณสามารถเปลี่ยนช่วงที่จะรวมได้โดยง่าย

ตัวอย่างนี้จะใช้สูตรด้านบนเพื่อรวมข้อมูลในเซลล์ D1: D4 แล้วเปลี่ยนช่วงที่สรุปเป็น D3: D6 โดยไม่ต้องแก้ไขสูตรในเซลล์ F1

01 จาก 03

การป้อนสูตร - ตัวเลือก

ตัวเลือกสำหรับการป้อนสูตรประกอบด้วย:

• พิมพ์สูตรข้างต้นลงในเซลล์ F1 โดยตรงและกดปุ่ม Enter บนแป้นพิมพ์
• ป้อนฟังก์ชัน INDIRECT เป็นอาร์กิวเมนต์โดยใช้ ไดอะล็อกบ็อกซ์ ของฟังก์ชัน SUM

ฟังก์ชันส่วนใหญ่ใน Excel มีกล่องโต้ตอบซึ่งช่วยให้คุณสามารถป้อนอาร์กิวเมนต์แต่ละฟังก์ชันในบรรทัดที่แยกออกได้โดยไม่ต้องกังวลเกี่ยวกับ ไวยากรณ์

ในกรณีนี้กล่องโต้ตอบของฟังก์ชัน SUM สามารถใช้เพื่อลดความซับซ้อนของสูตรในระดับหนึ่ง เนื่องจากฟังก์ชัน INDIRECT ถูกซ้อนกันภายใน SUM ฟังก์ชัน INDIRECT และอาร์กิวเมนต์จะต้องถูกป้อนด้วยตนเอง

ขั้นตอนด้านล่างใช้กล่องโต้ตอบ SUM เพื่อป้อนสูตร

การป้อนข้อมูลบทแนะนำ

ข้อมูล เซลล์ D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4

1. ป้อน ข้อมูล ต่อไปนี้ลงในเซลล์ D1 ถึง E2

เริ่มต้นสูตร SUM - INDIRECT - การเปิดกล่องโต้ตอบฟังก์ชัน SUM

1. คลิกบนเซลล์ F1 - นี่คือที่ที่ผลลัพธ์ของตัวอย่างนี้จะปรากฏขึ้น
2. คลิกแท็บ สูตร ในเมนู ริบบัว
3. เลือก คณิตศาสตร์และ Trig จากริบบิ้นเพื่อเปิดรายการแบบเลื่อนลงฟังก์ชั่น
4. คลิกที่ SUM ในรายการเพื่อเปิดกล่องโต้ตอบของฟังก์ชั่น

02 จาก 03

การป้อนฟังก์ชันทางอ้อม - คลิกเพื่อดูภาพขนาดใหญ่

ต้องป้อนสูตรทางอ้อม (INDIRECT) เป็นอาร์กิวเมนต์สำหรับฟังก์ชัน SUM

ในกรณีของฟังก์ชันที่ซ้อนกัน Excel ไม่อนุญาตให้เปิดกล่องโต้ตอบของฟังก์ชันที่สองเพื่อป้อนอาร์กิวเมนต์

ฟังก์ชัน INDIRECT ต้องถูกป้อนด้วยตนเองในบรรทัด Number1 ของกล่องโต้ตอบ SUM Function

1. ในกล่องโต้ตอบคลิกที่บรรทัด Number1
2. ป้อนฟังก์ชัน INDIRECT ต่อไปนี้: INDIRECT ("D" & E1 & ": D" & E2)
3. คลิกตกลงเพื่อทำหน้าที่และปิดกล่องโต้ตอบ
4. จำนวน 50 ควรปรากฏในเซลล์ F1 เนื่องจากเป็นข้อมูลรวมสำหรับข้อมูลที่อยู่ในเซลล์ D1 ถึง D4
5. เมื่อคุณคลิกที่เซลล์ F1 สูตรที่สมบูรณ์ = SUM (INDIRECT ("D" & E1 & ": D" & E2)) จะปรากฏขึ้นใน แถบสูตร เหนือ แผ่นงาน

การแบ่งฟังก์ชั่นทางอ้อม

เพื่อที่จะสร้างช่วงไดนามิคในคอลัมน์ D โดยใช้ INDIRECT เราต้องรวมตัวอักษร D ในอาร์กิวเมนต์ฟังก์ชันของ INDIRECT กับตัวเลขที่มีอยู่ในเซลล์ E1 และ E2

นี้สามารถทำได้โดยต่อไปนี้:

• สัญลักษณ์ ( & ) ใช้เพื่อ ต่อ หรือรวมข้อมูลข้อความ (ในกรณีนี้คือตัวอักษร D) กับการอ้างอิงเซลล์ (E1 และ E2)
• เพิ่มเติมข้อมูลข้อความที่ถูก concatenated กับการอ้างอิงเซลล์ต้องล้อมรอบด้วยเครื่องหมายคำพูดคู่ ( "" )
• ท้ายสุดจุดสิ้นสุดของช่วงจะถูกคั่นด้วยเครื่องหมายจุดคู่ (:)

ดังนั้นจุดเริ่มต้นของช่วงที่กำหนดโดยตัวอักษร: "D" และ E1

ชุดที่สองของอักขระ: ": D" และ E2 รวมลำไส้ใหญ่กับจุดสิ้นสุด เนื่องจากเครื่องหมายจุดคู่เป็นตัวอักษรดังนั้นจึงต้องรวมอยู่ในเครื่องหมายคำพูด

เครื่องหมายกลางที่สามอยู่ตรงกลางใช้เพื่อต่อส่วนทั้งสองเป็น อาร์กิวเมนต์ หนึ่ง:

"D" & E1 & ": D" และ E2

03 จาก 03

การเปลี่ยนช่วงของฟังก์ชัน SUM แบบไดนามิก

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

การเปลี่ยนตัวเลขในเซลล์ E1 และ E2 จะเปลี่ยนช่วงของเซลล์ที่อ่านโดยฟังก์ชัน SUM

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

1. คลิกที่เซลล์ E1
2. พิมพ์หมายเลข 3
3. กดปุ่ม Enter บนแป้นพิมพ์
4. คลิกที่เซลล์ E2
5. พิมพ์หมายเลข 6
6. กดปุ่ม Enter บนแป้นพิมพ์
7. คำตอบในเซลล์ F1 ควรเปลี่ยนเป็น 90 ซึ่งเป็นตัวเลขทั้งหมดที่มีอยู่ในเซลล์ D3 ถึง D6
8. ทดสอบสูตรต่อไปโดยเปลี่ยนเนื้อหาของเซลล์ B1 และ B2 เป็นตัวเลขระหว่าง 1 ถึง 6

INDIRECT และ #REF! ค่าข้อผิดพลาด

#REF! ค่าความผิดพลาดจะปรากฏในเซลล์ F1 ถ้าอาร์กิวเมนต์ฟังก์ชันของ INDIRECT:

• ไม่ใช่การอ้างอิงเซลล์ที่ถูกต้อง
• มีการอ้างอิงภายนอกไปยังสมุดงานอื่นและสมุดงานนั้นไม่ได้เปิดอยู่
• หมายถึงช่วงเซลล์ที่อยู่นอกวงเงินของแผ่นงาน (แถว 1,048,576 หรือคอลัมน์ XFD)