ช่วงชื่อไดนามิกใน Excel: 4 เทคนิคที่พิสูจน์แล้ว

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

ช่วงชื่อไดนามิกด้วยฟังก์ชัน OFFSET ของ Excel

เพื่อสร้างช่วงไดนามิก สามารถใช้ ฟังก์ชัน Offset ได้

ฟังก์ชัน Offset ของ Excel

นี่คือไวยากรณ์ของฟังก์ชัน OFFSET ของ Excel:

OFFSET (reference, rows, cols, [height], [width])

ความหมายของอาร์กิวเมนต์ในฟังก์ชัน:

1. Reference – อ้างอิงที่คุณต้องการใช้เป็นฐานออฟเซต
2. Rows – จำนวนแถว ขึ้นหรือลงจากเซลล์อ้างอิง
3. Cols – จำนวนคอลัมน์ ขวาหรือซ้ายจากเซลล์อ้างอิง
4. Height, width – กำหนดความสูงและความกว้างของการเลือก จากเซลล์ที่อ้างอิง

ช่วงชื่อไดนามิกด้วยฟังก์ชัน OFFSET

ในกล่องโต้ตอบ ตัวจัดการชื่อ สามารถใช้สำหรับกำหนดช่วงชื่อไดนามิกได้

นี่คือวิธีการทำ:

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

ช่วงชื่อไดนามิกจะถูกสร้างขึ้นเพื่อเลือกช่วงเซลล์ทั้งหมดที่เริ่มต้นที่เซลล์ต่ำกว่า A1 หนึ่งเซลล์ ในคอลัมน์เดียวกัน และเลือกจำนวนคอลัมน์และแถวที่กำหนดในเซลล์ K1 และ K2 จากตัวอย่างภาพด้านล่าง:

สูตรในฟิลด์ “Refers to” มีลักษณะดังนี้:

=OFFSET(Sheet1!$A$1;1;0;Sheet1!$K$1;Sheet1!$K$2)

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

นี่คือบางส่วนของผลลัพธ์:

=OFFSET (Sheet1!$A$1; 1; 0; 6; 2) = จะอ้างอิงช่วง A2:B7

สูตรที่จะขึ้นอยู่กับรายการใน K1, K2, K3 และ K4 ควรจะมีลักษณะดังนี้:

=OFFSET(Sheet1!$A$1;Sheet1!$K$3;Sheet1!$K$4;Sheet1!$K$1;Sheet1!$K$2)

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

=OFFSET(Sheet1!$A$1;1;1;COUNT(Sheet1!$B:$B);1)

อ่านเพิ่มเติม: ช่วงชื่อไดนามิกของ Excel ตามค่าเซลล์ (5 วิธีง่าย)

การอ่านที่คล้ายกัน

• ช่วงไดนามิกของ Excel ตามค่าเซลล์
• Excel VBA: ช่วงไดนามิกตามค่าเซลล์ (3 วิธี)
• วิธีใช้ช่วงไดนามิกสำหรับแถวสุดท้ายด้วย VBA ใน Excel (3 วิธี)

ช่วงชื่อไดนามิกด้วยฟังก์ชัน INDEX

ฟังก์ชันอื่นๆ ก็สามารถใช้สำหรับช่วงชื่อไดนามิกได้ เช่น INDEX เช่น คุณอยากเลือก “วันจันทร์” ทั้งหมด ไม่ว่าจะมีกี่วัน:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A;COUNTA(Sheet1!$A:$A))

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

=OFFSET(INDIRECT(ADDRESS(2;MATCH(Sheet1!$K$6;Sheet1!$1:$1;0)));0;0;COUNT(Sheet1!$A:$A);1)

อ่านเพิ่มเติม: Excel OFFSET ช่วงไดนามิกหลายคอลัมน์อย่างมีประสิทธิภาพ

ช่วงชื่อไดนามิกด้วย VBA

ในงานประจำวันของพวกเขา ผู้เชี่ยวชาญเผชิญสถานการณ์ที่ต้องทำซ้ำการดำเนินการเดียวกันไปเรื่อยๆ สิ่งเดียวกันอาจเกิดขึ้นเมื่อสร้างช่วงชื่อไดนามิก เพื่อหลีกเลี่ยงงานซ้ำ VBA สามารถใช้สำหรับสิ่งนี้ได้เช่นกัน

VBA ทำงานเหมือนกับสูตรทุกประการ เพียงแค่คุณต้องปฏิบัติตามไวยากรณ์ที่เป็นลักษณะเฉพาะสำหรับการเพิ่มช่วงชื่อ

ไวยากรณ์เป็นดังนี้:

ActiveWorkbook.names.Add Name:=”NAME”, RefersTo =”RANGE THAT YOU WANT TO SELECT”

ตัวอย่างเช่น หนึ่งในตัวอย่างก่อนหน้าสามารถนำไปใช้ใน VBA ได้ง่าย แมโครที่สมบูรณ์จะมีลักษณะดังนี้:

Sub naming()

ActiveWorkbook.names.Add Name:=”NAME9″, RefersTo:=”=OFFSET(Sheet1!$A$1,0,1,counta(A:A),2)”

End sub

อ่านเพิ่มเติม: สร้างช่วงชื่อไดนามิกด้วย VBA ใน Excel (แนวทางทีละขั้นตอน)

ช่วงชื่อไดนามิกกับเซลล์ว่าง

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

ภาพด้านล่างแสดงสถานการณ์กับเซลล์ว่าง:

หากคุณต้องการเลือก “วันจันทร์” ทั้งหมด การใช้สูตรเหมือนตัวอย่างก่อนหน้าจะเลือกทุกวันจันทร์ยกเว้นวันสุดท้าย เนื่องจากมีเซลล์ว่าง – A5 วิธีแก้ปัญหานี้คือใช้สูตรที่แตกต่าง – การรวมของสูตร:

=OFFSET(Sheet1!$A$1;1;0;SUMPRODUCT(MAX((Sheet1!$A:$A<>””)*ROW(Sheet1!$A:$A)))-1;1)

มันจะเลือกเซลล์ทั้งหมดจาก A2 ถึง A7

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

อ่านเพิ่มเติม: สร้างช่วงผลรวมไดนามิกตามค่าเซลล์ใน Excel (4 วิธี)

ดาวน์โหลดไฟล์ทำงาน

ดาวน์โหลดไฟล์ทำงานจากลิงก์ด้านล่าง:

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

• วิธีใช้ช่วงไดนามิก VBA ใน Excel (11 วิธี)
• การตรวจสอบข้อมูลรายการดรอปดาวน์พร้อมช่วงไดนามิกตาราง Excel
• สร้างช่วงแผนภูมิไดนามิกใน Excel (2 วิธี)
• วิธาสร้างช่วงตัวเลขใน Excel (3 วิธีง่าย)