ไปยังเนื้อหาหลัก

วิธีสร้างช่วงที่ตั้งชื่อแบบไดนามิกใน Excel

ผู้เขียน: Xiaoyang แก้ไขล่าสุด: 2024-07-30

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

สร้างช่วงที่มีชื่อแบบไดนามิกใน Excel โดยสร้างตาราง

สร้างช่วงที่ตั้งชื่อแบบไดนามิกใน Excel ด้วยฟังก์ชัน

สร้างช่วงที่ตั้งชื่อแบบไดนามิกใน Excel ด้วยรหัส VBA


สร้างช่วงที่มีชื่อแบบไดนามิกใน Excel โดยสร้างตาราง

หากคุณใช้ Excel 2007 หรือเวอร์ชันที่ใหม่กว่าวิธีที่ง่ายที่สุดในการสร้างช่วงที่มีชื่อแบบไดนามิกคือการสร้างตาราง Excel ที่มีชื่อ

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

doc ไดนามิกเรนจ์ 1

1. ประการแรกฉันจะกำหนดชื่อช่วงสำหรับช่วงนี้ เลือกช่วง A1: A6 แล้วป้อนชื่อ วันที่ เข้าไปใน ชื่อ: กล่องจากนั้นกด เข้าสู่ สำคัญ. กำหนดชื่อสำหรับช่วง B1: B6 เป็น Saleprice ด้วยวิธีเดียวกัน ในเวลาเดียวกันฉันสร้างสูตร = ผลรวม (Saleprice) ในเซลล์ว่างดูภาพหน้าจอ:

doc ไดนามิกเรนจ์ 2

2. เลือกช่วงและคลิก สิ่งที่ใส่เข้าไป > ตารางดูภาพหน้าจอ:

doc ไดนามิกเรนจ์ 3

3. ใน สร้างตาราง กล่องข้อความให้เลือก ตารางของฉันมีส่วนหัว (หากช่วงไม่มีส่วนหัวให้ยกเลิกการเลือก) คลิก OK และข้อมูลช่วงถูกแปลงเป็นตารางแล้ว ดูภาพหน้าจอ:

doc ไดนามิกเรนจ์ 4 -2 doc ไดนามิกเรนจ์ 5

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

doc ไดนามิกเรนจ์ 6 -2 doc ไดนามิกเรนจ์ 7

หมายเหตุ:

1. ข้อมูลที่ป้อนใหม่ของคุณต้องอยู่ติดกับข้อมูลด้านบนหมายความว่าไม่มีแถวหรือคอลัมน์ว่างระหว่างข้อมูลใหม่และข้อมูลที่มีอยู่

2. ในตารางคุณสามารถแทรกข้อมูลระหว่างค่าที่มีอยู่ได้


สร้างช่วงที่ตั้งชื่อแบบไดนามิกใน Excel ด้วยฟังก์ชัน

ใน Excel 2003 หรือเวอร์ชันก่อนหน้าจะใช้วิธีแรกไม่ได้ดังนั้นนี่จึงเป็นอีกวิธีหนึ่งสำหรับคุณ ดังต่อไปนี้ ออฟเซ็ต () ฟังก์ชันนี้สามารถช่วยคุณได้ แต่มันค่อนข้างลำบาก สมมติว่าฉันมีช่วงของข้อมูลที่มีชื่อช่วงที่ฉันกำหนดไว้ตัวอย่างเช่น A1: A6 ชื่อช่วงคือ วันที่และ B1: B6 ชื่อช่วงคือ ลดราคาในเวลาเดียวกันฉันสร้างสูตรสำหรับไฟล์ ลดราคา. ดูภาพหน้าจอ:

doc ไดนามิกเรนจ์ 2

คุณสามารถเปลี่ยนชื่อช่วงเป็นชื่อช่วงไดนามิกโดยทำตามขั้นตอนต่อไปนี้:

1. ไปที่คลิก สูตร > ชื่อผู้จัดการดูภาพหน้าจอ:

doc ไดนามิกเรนจ์ 8

2. ใน ชื่อผู้จัดการ กล่องโต้ตอบเลือกรายการที่คุณต้องการใช้แล้วคลิก Edit ปุ่ม

doc ไดนามิกเรนจ์ 9

3. ในการโผล่ออกมา แก้ไขชื่อ ให้ป้อนสูตรนี้ = OFFSET (Sheet1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1) เข้าไปใน อ้างถึง กล่องข้อความดูภาพหน้าจอ:

doc ไดนามิกเรนจ์ 10

4. จากนั้นคลิก OKแล้วทำซ้ำขั้นตอนที่ 2 และขั้นตอนที่ 3 เพื่อคัดลอกสูตรนี้ = OFFSET (Sheet1! $ B $ 1, 0, 0, COUNTA ($ B: $ B), 1) ลงใน อ้างถึง กล่องข้อความสำหรับไฟล์ ลดราคา ชื่อช่วง

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

doc ไดนามิกเรนจ์ 6 -2 doc ไดนามิกเรนจ์ 7

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

เคล็ดลับ: คำอธิบายสำหรับสูตรนี้:

  • = OFFSET (การอ้างอิงแถวคอลัมน์ [ความสูง] [ความกว้าง])
  • -1
  • = OFFSET (Sheet1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1)
  • การอ้างอิง สอดคล้องกับตำแหน่งเซลล์เริ่มต้นในตัวอย่างนี้ Sheet1! $ ก $ 1;
  • แถว หมายถึงจำนวนแถวที่คุณกำลังจะเลื่อนลงโดยเทียบกับเซลล์เริ่มต้น (หรือขึ้นไปถ้าคุณใช้ค่าลบ) ในตัวอย่างนี้ 0 แสดงว่ารายการจะเริ่มจากแถวแรกลงไป
  • คอลัมน์ สอดคล้องกับจำนวนคอลัมน์ที่คุณจะย้ายไปทางขวาโดยเทียบกับเซลล์เริ่มต้น (หรือทางซ้ายโดยใช้ค่าลบ) ในสูตรตัวอย่างข้างต้น 0 หมายถึงขยาย 0 คอลัมน์ไปทางขวา
  • [ความสูง] สอดคล้องกับความสูง (หรือจำนวนแถว) ของช่วงที่เริ่มต้นที่ตำแหน่งที่ปรับ $ A: $ A จะนับรายการทั้งหมดที่ป้อนในคอลัมน์ A
  • [ความกว้าง] สอดคล้องกับความกว้าง (หรือจำนวนคอลัมน์) ของช่วงที่เริ่มต้นที่ตำแหน่งที่ปรับ ในสูตรข้างต้นรายการจะกว้าง 1 คอลัมน์

คุณสามารถเปลี่ยนข้อโต้แย้งเหล่านี้ตามความต้องการของคุณ


สร้างช่วงที่ตั้งชื่อแบบไดนามิกใน Excel ด้วยรหัส VBA

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

1. เปิดใช้งานแผ่นงานของคุณ

2. กด ALT + F11 และจะเปิดไฟล์ หน้าต่าง Microsoft Visual Basic for Applications.

3. คลิก สิ่งที่ใส่เข้าไป > โมดูลและวางรหัสต่อไปนี้ในไฟล์ หน้าต่างโมดูล.

รหัส Vba: สร้างช่วงที่ตั้งชื่อแบบไดนามิก

Sub CreateNamesxx()
'Update 20131128
Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, Start As String
Const Rowno = 1
Const Colno = 1
Const Offset = 1
On Error Resume Next
Set wb = ActiveWorkbook
Set ws = ActiveSheet
lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address
wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno & ")"
wb.Names.Add Name:="myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
For i = Colno To lcol
    myName = Replace(Cells(Rowno, i).Value, " ", "_")
    If myName <> "" Then
        wb.Names.Add Name:=myName, RefersToR1C1:="=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
    End If
Next
End Sub

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

5. เมื่อคุณป้อนค่าใหม่หลังแถวหรือคอลัมน์ช่วงจะถูกขยายด้วย ดูภาพหน้าจอ:

doc ไดนามิกเรนจ์ 12
-1
doc ไดนามิกเรนจ์ 13

หมายเหตุ:

1. ด้วยรหัสนี้ชื่อช่วงจะไม่แสดงในไฟล์ ชื่อ: กล่องเพื่อดูและใช้ชื่อช่วงอย่างสะดวกฉันได้ติดตั้ง Kutools สำหรับ Excelเดียวกันกับที่ บานหน้าต่างนำทางชื่อช่วงไดนามิกที่สร้างขึ้นจะแสดงรายการ

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

3. เมื่อคุณใช้รหัสนี้ช่วงข้อมูลของคุณควรเริ่มต้นที่เซลล์ A1


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

วิธีอัปเดตแผนภูมิอัตโนมัติหลังจากป้อนข้อมูลใหม่ใน Excel

สุดยอดเครื่องมือเพิ่มผลผลิตในสำนักงาน

🤖 Kutools AI ผู้ช่วย: ปฏิวัติการวิเคราะห์ข้อมูลโดยยึดตาม: การดำเนินการที่ชาญฉลาด   |  สร้างรหัส  |  สร้างสูตรที่กำหนดเอง  |  วิเคราะห์ข้อมูลและสร้างแผนภูมิ  |  เรียกใช้ฟังก์ชัน Kutools...
คุณสมบัติยอดนิยม: ค้นหา เน้น หรือระบุรายการที่ซ้ำกัน   |  ลบแถวว่าง   |  รวมคอลัมน์หรือเซลล์โดยไม่สูญเสียข้อมูล   |   รอบโดยไม่มีสูตร ...
การค้นหาขั้นสูง: VLookup หลายเกณฑ์    VLookup หลายค่า  |   VLookup ข้ามหลายแผ่น   |   การค้นหาที่ไม่ชัดเจน ....
รายการแบบเลื่อนลงขั้นสูง: สร้างรายการแบบหล่นลงอย่างรวดเร็ว   |  รายการแบบหล่นลงขึ้นอยู่กับ   |  เลือกหลายรายการแบบหล่นลง ....
ผู้จัดการคอลัมน์: เพิ่มจำนวนคอลัมน์เฉพาะ  |  ย้ายคอลัมน์  |  สลับสถานะการมองเห็นของคอลัมน์ที่ซ่อนอยู่  |  เปรียบเทียบช่วงและคอลัมน์ ...
คุณสมบัติเด่น: กริดโฟกัส   |  มุมมองการออกแบบ   |   บาร์สูตรใหญ่    สมุดงานและตัวจัดการชีต   |  ห้องสมุดทรัพยากร (ข้อความอัตโนมัติ)   |  เลือกวันที่   |  รวมแผ่นงาน   |  เข้ารหัส/ถอดรหัสเซลล์    ส่งอีเมลตามรายการ   |  ซุปเปอร์ฟิลเตอร์   |   ตัวกรองพิเศษ (กรองตัวหนา/ตัวเอียง/ขีดทับ...) ...
ชุดเครื่องมือ 15 อันดับแรก12 ข้อความ เครื่องมือ (เพิ่มข้อความ, ลบอักขระ, ... )   |   50 + แผนภูมิ ประเภท (แผนภูมิ Gantt, ... )   |   40+ ใช้งานได้จริง สูตร (คำนวณอายุตามวันเกิด, ... )   |   19 การแทรก เครื่องมือ (ใส่ QR Code, แทรกรูปภาพจากเส้นทาง, ... )   |   12 การแปลง เครื่องมือ (ตัวเลขเป็นคำ, การแปลงสกุลเงิน, ... )   |   7 ผสานและแยก เครื่องมือ (แถวรวมขั้นสูง, แยกเซลล์, ... )   |   ... และอื่น ๆ

เพิ่มพูนทักษะ Excel ของคุณด้วย Kutools สำหรับ Excel และสัมผัสประสิทธิภาพอย่างที่ไม่เคยมีมาก่อน Kutools สำหรับ Excel เสนอคุณสมบัติขั้นสูงมากกว่า 300 รายการเพื่อเพิ่มประสิทธิภาพและประหยัดเวลา  คลิกที่นี่เพื่อรับคุณสมบัติที่คุณต้องการมากที่สุด...


แท็บ Office นำอินเทอร์เฟซแบบแท็บมาที่ Office และทำให้งานของคุณง่ายขึ้นมาก

  • เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
  • เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
  • เพิ่มประสิทธิภาพการทำงานของคุณ 50% และลดการคลิกเมาส์หลายร้อยครั้งให้คุณทุกวัน!