Excel Dynamic Named Range [4 วิธี]

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

ไดนามิกเรนจ์เรนจ์พร้อมฟังก์ชัน OFFSET ของ Excel

ในการสร้างช่วงไดนามิก ฟังก์ชันออฟเซ็ต สามารถใช้ได้

ฟังก์ชันออฟเซ็ตของ Excel

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

OFFSET (การอ้างอิง แถว cols [ความสูง] [ความกว้าง])

อาร์กิวเมนต์หมายถึงอะไรในฟังก์ชัน:

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

ไดนามิกเนมเรนจ์พร้อมฟังก์ชัน OFFSET

ใน Name Manager กล่องโต้ตอบ สามารถใช้สำหรับกำหนด Dynamic Named Ranges ได้

คุณสามารถทำสิ่งนี้ได้ดังนี้:

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

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

สูตรในช่อง “อ้างอิงถึง” จะมีลักษณะดังนี้:

=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 Dynamic Named Range ตามค่าของเซลล์ (5 วิธีง่ายๆ)

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

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

ไดนามิกเนมเรนจ์พร้อมฟังก์ชัน INDEX

ฟังก์ชันอื่นๆ ยังสามารถใช้สำหรับ Dynamic Named Ranges เช่น 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

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

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

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

ActiveWorkbook.names.Add Name:=”NAME”, RefersTo =”RANGE ที่คุณต้องการเลือก”

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

การตั้งชื่อย่อย()

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

จบซับ

อ่านเพิ่มเติม:สร้าง Dynamic Named Range ด้วย VBA ใน Excel (คำแนะนำทีละขั้นตอน)

ไดนามิกเนมช่วงที่มีเซลล์ว่าง

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

รูปภาพด้านล่างแสดงสถานการณ์ที่มีเซลล์ว่าง:

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

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

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

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

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

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

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

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

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