Excel

ช่วงที่มีชื่อใน Excel

Named Ranges Excel

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



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

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



พื้นฐานของช่วงที่มีชื่อใน Excel

ช่วงชื่อคืออะไร?

ช่วงที่มีชื่อเป็นเพียงชื่อที่มนุษย์สามารถอ่านได้สำหรับช่วงของเซลล์ใน Excel ตัวอย่างเช่น ถ้าฉันตั้งชื่อช่วง A1:A100 'data' ฉันสามารถใช้ MAX เพื่อรับค่าสูงสุดด้วยสูตรง่ายๆ:

 
 = MAX (data) // max value

ช่วงชื่อง่ายที่เรียกว่า



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

 
= MAX (data) // max value = MIN (data) // min value = COUNT (data) // total values = AVERAGE (data) // min value

วิดีโอ: วิธีสร้างช่วงที่มีชื่อ

การสร้างช่วงที่มีชื่อเป็นเรื่องง่าย

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

สร้างช่วงที่ตั้งชื่ออย่างรวดเร็วด้วยกล่องชื่อ

หากต้องการทดสอบช่วงใหม่อย่างรวดเร็ว ให้เลือกชื่อใหม่ในรายการดรอปดาวน์ถัดจากช่องชื่อ Excel จะเลือกช่วงบนแผ่นงาน

Excel สามารถสร้างชื่อได้โดยอัตโนมัติ (ctrl + shift + F3)

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

สร้างชื่อจากคำสั่งการเลือกบน ribbon

คุณยังสามารถใช้แป้นพิมพ์ลัดควบคุม + shift + F3

เมื่อใช้คุณสมบัตินี้ เราสามารถสร้างช่วงที่มีชื่อสำหรับประชากร 12 รัฐในขั้นตอนเดียว:

วิธีเขียนมาโครใน excel 2013

สร้างชื่อจากการเลือกด้วยข้อมูลและป้ายกำกับที่เลือก

เมื่อคุณคลิกตกลง ชื่อจะถูกสร้างขึ้น คุณจะพบชื่อที่สร้างขึ้นใหม่ทั้งหมดในเมนูแบบเลื่อนลงถัดจากช่องชื่อ:

ชื่อใหม่ยังปรากฏในเมนูแบบเลื่อนลงของกล่องชื่อ

ด้วยชื่อที่สร้างขึ้น คุณสามารถใช้มันในสูตรเช่นนี้

 
= SUM (MN,WI,MI)

อัปเดตช่วงที่มีชื่อในตัวจัดการชื่อ (Control + F3)

เมื่อคุณสร้างช่วงที่มีชื่อแล้ว ให้ใช้ ผู้จัดการชื่อ (Control + F3) เพื่ออัปเดตตามต้องการ เลือกชื่อที่คุณต้องการใช้งาน จากนั้นเปลี่ยนข้อมูลอ้างอิงโดยตรง (เช่น แก้ไข 'อ้างอิงถึง') หรือคลิกปุ่มทางด้านขวาแล้วเลือกช่วงใหม่

อัปเดตช่วงที่ตั้งชื่อด้วย Name Manager

ไม่จำเป็นต้องคลิกปุ่มแก้ไขเพื่ออัปเดตข้อมูลอ้างอิง เมื่อคุณคลิกปิด ชื่อช่วงจะได้รับการอัปเดต

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

ดูช่วงที่มีชื่อทั้งหมด (control + F3)

หากต้องการดูช่วงที่มีชื่อทั้งหมดในเวิร์กบุ๊กอย่างรวดเร็ว ให้ใช้เมนูดรอปดาวน์ถัดจากกล่องชื่อ

หากคุณต้องการดูรายละเอียดเพิ่มเติม ให้เปิดตัวจัดการชื่อ (Control + F3) ซึ่งแสดงรายการชื่อทั้งหมดที่มีข้อมูลอ้างอิง และมีตัวกรองด้วย:

ตัวจัดการชื่อแสดงชื่อที่สร้างขึ้นใหม่ทั้งหมด

หมายเหตุ: บน Mac ไม่มีโปรแกรมจัดการชื่อ ดังนั้นคุณจะเห็นกล่องโต้ตอบกำหนดชื่อแทน

คัดลอกและวางช่วงที่มีชื่อทั้งหมด (F3)

ถ้าคุณต้องการบันทึกช่วงที่มีชื่อแบบถาวรมากขึ้นในเวิร์กบุ๊ก คุณสามารถวางรายชื่อทั้งหมดได้ทุกที่ที่คุณต้องการ ไปที่ สูตร > ใช้ในสูตร (หรือใช้ปุ่มลัด F3) จากนั้นเลือก วางชื่อ > วางรายการ:

กล่องโต้ตอบวางชื่อ

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

หลังจากวางช่วงที่มีชื่อลงในเวิร์กชีต

ดูชื่อโดยตรงบนแผ่นงาน

ถ้าคุณตั้งค่าระดับการย่อ/ขยายให้น้อยกว่า 40% Excel จะแสดงชื่อช่วงบนเวิร์กชีตโดยตรง:

ที่ระดับการซูม <40% Excel จะแสดงชื่อช่วง

ขอบคุณสำหรับเคล็ดลับนี้ เฟลิเป้!

ชื่อมีกฎ

เมื่อสร้างช่วงที่มีชื่อ ให้ปฏิบัติตามกฎเหล่านี้:

  1. ชื่อต้องขึ้นต้นด้วยตัวอักษร ขีดล่าง (_) หรือแบ็กสแลช ()
  2. ชื่อต้องไม่มีช่องว่างและเครื่องหมายวรรคตอนส่วนใหญ่
  3. ชื่อต้องไม่ขัดแย้งกับการอ้างอิงเซลล์ คุณไม่สามารถตั้งชื่อช่วงเป็น 'A1' หรือ 'Z100'
  4. ตัวอักษรเดี่ยวใช้ได้กับชื่อ ('a', 'b', 'c' ฯลฯ) แต่ตัวอักษร 'r' และ 'c' สงวนไว้
  5. ชื่อไม่คำนึงถึงขนาดตัวพิมพ์ - 'home', 'HOME' และ 'HoMe' ล้วนเหมือนกันกับ Excel

ช่วงที่มีชื่อในสูตร

ช่วงที่ตั้งชื่อนั้นใช้งานง่ายในสูตร

ตัวอย่างเช่น สมมติว่าคุณตั้งชื่อเซลล์ในสมุดงานว่า 'อัปเดต' แนวคิดคือคุณสามารถใส่วันที่ปัจจุบันในเซลล์ (Ctrl + ) และอ้างอิงวันที่อื่นในเวิร์กบุ๊ก

การใช้ช่วงที่มีชื่อภายในสูตรข้อความ

สูตรใน B8 มีลักษณะดังนี้:

 
='Updated: '&  TEXT (updated, 'ddd, mmmm d, yyyy')

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

ช่วงที่มีชื่อปรากฏขึ้นเมื่อพิมพ์สูตร

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

ช่วงที่มีชื่อปรากฏขึ้นเมื่อป้อนสูตร

ช่วงที่มีชื่อสามารถทำงานเหมือนค่าคงที่

เนื่องจากช่วงที่มีชื่อถูกสร้างขึ้นในตำแหน่งศูนย์กลาง คุณจึงสามารถใช้ช่วงดังกล่าวได้เหมือนค่าคงที่โดยไม่ต้องอ้างอิงเซลล์ ตัวอย่างเช่น คุณสามารถสร้างชื่อเช่น 'MPG' (ไมล์ต่อแกลลอน) และ 'CPG' (ราคาต่อแกลลอน) ด้วยและกำหนดค่าคงที่:

ช่วงที่มีชื่อสามารถทำงานเหมือนค่าคงที่ โดยไม่มีการอ้างอิงเซลล์

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

การใช้ช่วงที่มีชื่อเหมือนค่าคงที่ในสูตร

ช่วงที่มีชื่อเป็นค่าสัมบูรณ์โดยค่าเริ่มต้น

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

 
=C5/$D

สูตรมาตรฐานพร้อมที่อยู่ที่แน่นอน

การอ้างอิงถึง D2 เป็นแบบสัมบูรณ์ (ล็อกไว้) จึงสามารถคัดลอกสูตรได้โดยไม่ต้องเปลี่ยน D2

ถ้าเราตั้งชื่อ D2 'MPG' สูตรจะกลายเป็น:

 
=C5/MPG

การใช้ช่วงที่มีชื่อเหมือนค่าคงที่ในสูตร

เนื่องจาก MPG เป็นค่าสัมบูรณ์โดยค่าเริ่มต้น สูตรจึงสามารถคัดลอกลงในคอลัมน์ D ได้ตามที่เป็นอยู่

ช่วงที่มีชื่อสามารถสัมพันธ์กันได้

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

ตัวอย่างเช่น คุณสามารถสร้างช่วงชื่อทั่วไป 'CellAbove' ได้ดังนี้:

  1. เลือกเซลล์ A2
  2. ควบคุม + F3 เพื่อเปิดตัวจัดการชื่อ
  3. แท็บในส่วน 'อ้างอิงถึง' จากนั้นพิมพ์: =A1

CellAbove จะดึงค่าจากเซลล์ด้านบนไม่ว่าจะใช้ที่ไหน

สำคัญ: ตรวจสอบให้แน่ใจว่าเซลล์ที่ใช้งานอยู่อยู่ในตำแหน่งที่ถูกต้องก่อนสร้างชื่อ

ใช้ช่วงที่มีชื่อกับสูตรที่มีอยู่

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

กล่องโต้ตอบใช้ชื่อ

จากนั้น Excel จะแทนที่การอ้างอิงที่มีช่วงชื่อที่สัมพันธ์กันด้วยชื่อเอง

คุณยังสามารถใช้ชื่อด้วยการค้นหาและแทนที่:

การใช้ช่วงชื่อด้วยการค้นหาและแทนที่

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

ประโยชน์หลักของช่วงที่มีชื่อ

ช่วงที่มีชื่อทำให้อ่านสูตรได้ง่ายขึ้น

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

 
= VLOOKUP ($H,$B:$E,2,0)

หากไม่มีช่วงที่มีชื่อสูตรอาจเป็นความลับ

คำนวณระยะเวลาระหว่างวันที่สองวัน

อย่างไรก็ตาม ด้วย B3:E11 ชื่อ 'data' และ H4 ชื่อ 'planet' เราสามารถเขียนสูตรได้ดังนี้:

 
= VLOOKUP (planet,data,2,0) // position = VLOOKUP (planet,data,3,0) // diameter = VLOOKUP (planet,data,4,0) // satellites

ด้วยช่วงที่มีชื่อ สูตรจึงเป็นเรื่องง่าย

คุณจะเห็นความแตกต่างเพียงอย่างเดียวในสูตรเหล่านี้ในดัชนีคอลัมน์

ช่วงที่มีชื่อทำให้สูตรพกพาและนำกลับมาใช้ใหม่ได้

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

ตัวอย่างเช่น สูตรนี้นับค่าที่ไม่ซ้ำในช่วงข้อมูลตัวเลข:

 
= SUM (--( FREQUENCY (data,data)>0))

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

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

สามารถใช้ช่วงที่มีชื่อสำหรับการนำทางได้

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

ช่วงที่มีชื่อช่วยให้นำทางได้ง่าย

ช่วงที่มีชื่อทำงานได้ดีกับไฮเปอร์ลิงก์

ช่วงที่มีชื่อทำให้ไฮเปอร์ลิงก์เป็นเรื่องง่าย ตัวอย่างเช่น หากคุณตั้งชื่อ A1 ใน 'home' ของ Sheet1 คุณสามารถสร้างไฮเปอร์ลิงก์ที่อื่นที่จะพาคุณกลับไปที่นั่น

การสร้างไฮเปอร์ลิงก์ไปยังช่วงที่มีชื่อ

ตัวอย่างไฮเปอร์ลิงก์ของช่วงที่มีชื่อบนเวิร์กชีต

ในการใช้ช่วงที่มีชื่อภายในฟังก์ชัน HYPERLINK ให้เพิ่มสัญลักษณ์ปอนด์ที่ด้านหน้าของช่วงที่มีชื่อ:

 
= HYPERLINK ('#home','take me home')

หมายเหตุ: น่าแปลกที่คุณไม่สามารถไฮเปอร์ลิงก์ไปยังตารางได้เหมือนกับชื่อช่วงปกติ อย่างไรก็ตาม คุณสามารถกำหนดชื่อให้เท่ากับตาราง (เช่น =Table1) และไฮเปอร์ลิงก์ไปยังตารางนั้นได้ หากใครรู้วิธีเชื่อมโยงตารางโดยตรง บอกเลย!

ช่วงที่มีชื่อสำหรับการตรวจสอบความถูกต้องของข้อมูล

ช่วงชื่อทำงานได้ดีสำหรับการตรวจสอบความถูกต้องของข้อมูล เนื่องจากช่วยให้คุณสามารถใช้การอ้างอิงที่มีชื่อตามตรรกะเพื่อตรวจสอบการป้อนข้อมูลด้วยเมนูแบบเลื่อนลง ด้านล่าง ช่วง G4:G8 มีชื่อว่า 'statuslist' จากนั้นใช้การตรวจสอบความถูกต้องของข้อมูลกับ List ที่ลิงก์ดังนี้:

การใช้ช่วงที่มีชื่อสำหรับการตรวจสอบข้อมูลด้วย list

ผลลัพธ์คือเมนูแบบเลื่อนลงในคอลัมน์ E ที่อนุญาตเฉพาะค่าในช่วงที่มีชื่อ:

การตรวจสอบข้อมูลด้วยตัวอย่างช่วงที่มีชื่อ

Dynamic Named Ranges

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

ช่วงที่ตั้งชื่อแบบไดนามิกด้วย Table

ตารางเป็นวิธีที่ง่ายที่สุดในการสร้างช่วงที่มีชื่อแบบไดนามิก เลือกเซลล์ใดก็ได้ในข้อมูล จากนั้นใช้ปุ่มลัด Control + T:

การสร้างตาราง am Excel

เมื่อคุณสร้างตาราง Excel ชื่อจะถูกสร้างขึ้นโดยอัตโนมัติ (เช่น Table1) แต่คุณสามารถเปลี่ยนชื่อตารางได้ตามต้องการ เมื่อคุณสร้างตารางแล้ว ตารางจะขยายโดยอัตโนมัติเมื่อมีการเพิ่มข้อมูล

ตารางจะขยายโดยอัตโนมัติและสามารถเปลี่ยนชื่อได้

ช่วงชื่อไดนามิกพร้อมสูตร

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

  • ตัวอย่างสูตรช่วงไดนามิกที่มี INDEX
  • ตัวอย่างสูตรช่วงไดนามิกกับ OFFSET

ชื่อตารางในการตรวจสอบข้อมูล

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

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

วิดีโอ: วิธีใช้ช่วงที่มีชื่อกับตาราง

กำลังลบช่วงที่มีชื่อ

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

ช่วงที่ตั้งชื่อจะปรับเมื่อลบและแทรกเซลล์

เมื่อคุณลบ *บางส่วน* ของช่วงที่มีชื่อ หรือหากแทรกเซลล์/แถว/คอลัมน์ภายในช่วงที่มีชื่อ การอ้างอิงช่วงจะปรับตามนั้นและยังคงใช้ได้ อย่างไรก็ตาม หากคุณลบเซลล์ทั้งหมดที่ล้อมรอบช่วงที่มีชื่อ ช่วงที่มีชื่อจะสูญเสียข้อมูลอ้างอิงและแสดงข้อผิดพลาด #REF ตัวอย่างเช่น ถ้าฉันตั้งชื่อ A1 'test' จากนั้นลบคอลัมน์ A ตัวจัดการชื่อจะแสดง 'refers to' เป็น:

 
=Sheet1!#REF!

ลบชื่อด้วย Name Manager

หากต้องการลบช่วงที่มีชื่อออกจากเวิร์กบุ๊กด้วยตนเอง ให้เปิดตัวจัดการชื่อ เลือกช่วง แล้วคลิกปุ่มลบ หากคุณต้องการลบมากกว่าหนึ่งชื่อพร้อมกัน คุณสามารถ Shift + คลิก หรือ Ctrl + คลิก เพื่อเลือกหลายชื่อ จากนั้นลบในขั้นตอนเดียว

ลบชื่อที่มีข้อผิดพลาด

หากคุณมีชื่อจำนวนมากที่มีข้อผิดพลาดในการอ้างอิง คุณสามารถใช้ปุ่มตัวกรองในตัวจัดการชื่อเพื่อกรองชื่อที่มีข้อผิดพลาดได้:

เมนูตัวกรองชื่อผู้จัดการ

จากนั้น shift+click เพื่อเลือกชื่อทั้งหมดและลบ

ช่วงและขอบเขตที่มีชื่อ

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

วัตถุประสงค์ของขอบเขต

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

ขอบเขตท้องถิ่น

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

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

 
Sheet1!total_revenue Sheet2!total_revenue Sheet3!total_revenue

ชื่อช่วงที่สร้างด้วย กล่องชื่อ มีขอบเขตทั่วโลกโดยอัตโนมัติ หากต้องการลบล้างการทำงานนี้ ให้เพิ่มชื่อแผ่นงานเมื่อกำหนดชื่อ:

 
Sheet3!my_new_name

ขอบเขตทั่วโลก

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

 
=last_update

ชื่อสากลต้องไม่ซ้ำกันภายในสมุดงาน

ขอบเขตท้องถิ่น

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

การจัดการขอบเขตช่วงที่มีชื่อ

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

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

Jan Karel Pieterse และ Charles Williams ได้พัฒนายูทิลิตี้ที่เรียกว่า Name Manager ซึ่งมีการดำเนินการที่มีประโยชน์มากมายสำหรับช่วงที่มีชื่อ คุณสามารถ ดาวน์โหลดยูทิลิตี้ Name Manager ที่นี่ .

ผู้เขียน Dave Bruns


^