วิธีสร้างช่วงที่ตั้งชื่อแบบไดนามิกใน Excel
ปกติ ช่วงที่ตั้งชื่อ มีประโยชน์มากสำหรับผู้ใช้ Excel คุณสามารถกำหนดชุดของค่าในคอลัมน์ตั้งชื่อคอลัมน์นั้นจากนั้นคุณสามารถอ้างถึงช่วงนั้นตามชื่อแทนการอ้างอิงเซลล์ได้ แต่ส่วนใหญ่แล้วคุณต้องเพิ่มข้อมูลใหม่เพื่อขยายค่าข้อมูลของช่วงที่คุณอ้างอิงในอนาคต ในกรณีนี้คุณต้องกลับไปที่ สูตร > ชื่อผู้จัดการ และกำหนดช่วงใหม่เพื่อรวมค่าใหม่ เพื่อหลีกเลี่ยงปัญหานี้คุณสามารถสร้างช่วงที่ตั้งชื่อแบบไดนามิกซึ่งหมายความว่าคุณไม่จำเป็นต้องปรับการอ้างอิงเซลล์ทุกครั้งเมื่อคุณเพิ่มแถวหรือคอลัมน์ใหม่ในรายการ
สร้างช่วงที่มีชื่อแบบไดนามิกใน Excel โดยสร้างตาราง
สร้างช่วงที่ตั้งชื่อแบบไดนามิกใน Excel ด้วยฟังก์ชัน
สร้างช่วงที่ตั้งชื่อแบบไดนามิกใน Excel ด้วยรหัส VBA
สร้างช่วงที่มีชื่อแบบไดนามิกใน Excel โดยสร้างตาราง
หากคุณใช้ Excel 2007 หรือเวอร์ชันที่ใหม่กว่าวิธีที่ง่ายที่สุดในการสร้างช่วงที่มีชื่อแบบไดนามิกคือการสร้างตาราง Excel ที่มีชื่อ
สมมติว่าคุณมีช่วงข้อมูลต่อไปนี้ซึ่งจำเป็นต้องกลายเป็นช่วงที่ตั้งชื่อแบบไดนามิก
1. ประการแรกฉันจะกำหนดชื่อช่วงสำหรับช่วงนี้ เลือกช่วง A1: A6 แล้วป้อนชื่อ วันที่ เข้าไปใน ชื่อ: กล่องจากนั้นกด เข้าสู่ สำคัญ. กำหนดชื่อสำหรับช่วง B1: B6 เป็น Saleprice ด้วยวิธีเดียวกัน ในเวลาเดียวกันฉันสร้างสูตร = ผลรวม (Saleprice) ในเซลล์ว่างดูภาพหน้าจอ:
2. เลือกช่วงและคลิก สิ่งที่ใส่เข้าไป > ตารางดูภาพหน้าจอ:
3. ใน สร้างตาราง กล่องข้อความให้เลือก ตารางของฉันมีส่วนหัว (หากช่วงไม่มีส่วนหัวให้ยกเลิกการเลือก) คลิก OK และข้อมูลช่วงถูกแปลงเป็นตารางแล้ว ดูภาพหน้าจอ:
 |
 |
 |
4. และเมื่อคุณป้อนค่าใหม่หลังข้อมูลช่วงที่ตั้งชื่อจะปรับโดยอัตโนมัติและสูตรที่สร้างขึ้นก็จะเปลี่ยนไปเช่นกัน ดูภาพหน้าจอต่อไปนี้:
 |
|
 |
หมายเหตุ:
1. ข้อมูลที่ป้อนใหม่ของคุณต้องอยู่ติดกับข้อมูลด้านบนหมายความว่าไม่มีแถวหรือคอลัมน์ว่างระหว่างข้อมูลใหม่และข้อมูลที่มีอยู่
2. ในตารางคุณสามารถแทรกข้อมูลระหว่างค่าที่มีอยู่ได้
สร้างช่วงที่ตั้งชื่อแบบไดนามิกใน Excel ด้วยฟังก์ชัน
ใน Excel 2003 หรือเวอร์ชันก่อนหน้าจะใช้วิธีแรกไม่ได้ดังนั้นนี่จึงเป็นอีกวิธีหนึ่งสำหรับคุณ ดังต่อไปนี้ ออฟเซ็ต () ฟังก์ชันนี้สามารถช่วยคุณได้ แต่มันค่อนข้างลำบาก สมมติว่าฉันมีช่วงของข้อมูลที่มีชื่อช่วงที่ฉันกำหนดไว้ตัวอย่างเช่น A1: A6 ชื่อช่วงคือ วันที่และ B1: B6 ชื่อช่วงคือ ลดราคาในเวลาเดียวกันฉันสร้างสูตรสำหรับไฟล์ ลดราคา. ดูภาพหน้าจอ:
คุณสามารถเปลี่ยนชื่อช่วงเป็นชื่อช่วงไดนามิกโดยทำตามขั้นตอนต่อไปนี้:
1. ไปที่คลิก สูตร > ชื่อผู้จัดการดูภาพหน้าจอ:
2. ใน ชื่อผู้จัดการ กล่องโต้ตอบเลือกรายการที่คุณต้องการใช้แล้วคลิก Edit ปุ่ม
3. ในการโผล่ออกมา แก้ไขชื่อ ให้ป้อนสูตรนี้ = OFFSET (Sheet1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1) เข้าไปใน อ้างถึง กล่องข้อความดูภาพหน้าจอ:
4. จากนั้นคลิก OKแล้วทำซ้ำขั้นตอนที่ 2 และขั้นตอนที่ 3 เพื่อคัดลอกสูตรนี้ = OFFSET (Sheet1! $ B $ 1, 0, 0, COUNTA ($ B: $ B), 1) ลงใน อ้างถึง กล่องข้อความสำหรับไฟล์ ลดราคา ชื่อช่วง
5. และสร้างช่วงที่ตั้งชื่อแบบไดนามิกแล้ว เมื่อคุณป้อนค่าใหม่หลังข้อมูลช่วงที่ตั้งชื่อจะปรับโดยอัตโนมัติและสูตรที่สร้างขึ้นก็จะเปลี่ยนไปเช่นกัน ดูภาพหน้าจอ:
|
|
|
หมายเหตุ หากมีเซลล์ว่างอยู่ตรงกลางช่วงผลลัพธ์ของสูตรจะไม่ถูกต้อง นั่นเป็นเพราะไม่นับเซลล์ที่ไม่ว่างเปล่าดังนั้นช่วงของคุณจะสั้นกว่าที่ควรและเซลล์สุดท้ายในช่วงจะถูกทิ้งไว้
เคล็ดลับ: คำอธิบายสำหรับสูตรนี้:
- = OFFSET (การอ้างอิงแถวคอลัมน์ [ความสูง] [ความกว้าง])
- = 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. เมื่อคุณป้อนค่าใหม่หลังแถวหรือคอลัมน์ช่วงจะถูกขยายด้วย ดูภาพหน้าจอ:
 |
|
 |
หมายเหตุ:
1. ด้วยรหัสนี้ชื่อช่วงจะไม่แสดงในไฟล์ ชื่อ: กล่องเพื่อดูและใช้ชื่อช่วงอย่างสะดวกฉันได้ติดตั้ง Kutools สำหรับ Excelเดียวกันกับที่ บานหน้าต่างนำทางชื่อช่วงไดนามิกที่สร้างขึ้นจะแสดงรายการ
2. ด้วยรหัสนี้ช่วงทั้งหมดของข้อมูลสามารถขยายได้ในแนวตั้งหรือแนวนอน แต่อย่าลืมว่าไม่ควรมีแถวหรือคอลัมน์ว่างระหว่างข้อมูลเมื่อคุณป้อนค่าใหม่
3. เมื่อคุณใช้รหัสนี้ช่วงข้อมูลของคุณควรเริ่มต้นที่เซลล์ A1
บทความที่เกี่ยวข้อง:
วิธีอัปเดตแผนภูมิอัตโนมัติหลังจากป้อนข้อมูลใหม่ใน Excel
สุดยอดเครื่องมือเพิ่มผลผลิตในสำนักงาน
เพิ่มพูนทักษะ Excel ของคุณด้วย Kutools สำหรับ Excel และสัมผัสประสิทธิภาพอย่างที่ไม่เคยมีมาก่อน Kutools สำหรับ Excel เสนอคุณสมบัติขั้นสูงมากกว่า 300 รายการเพื่อเพิ่มประสิทธิภาพและประหยัดเวลา คลิกที่นี่เพื่อรับคุณสมบัติที่คุณต้องการมากที่สุด...
แท็บ Office นำอินเทอร์เฟซแบบแท็บมาที่ Office และทำให้งานของคุณง่ายขึ้นมาก
- เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
- เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
- เพิ่มประสิทธิภาพการทำงานของคุณ 50% และลดการคลิกเมาส์หลายร้อยครั้งให้คุณทุกวัน!
