ช่วงไดนามิกที่มีการปรับขนาดอัตโนมัติ

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

• ลิงก์ในสูตรรายงานที่อ้างถึงตารางของเรา
• ช่วงเริ่มต้นของตารางเดือยที่สร้างขึ้นตามตารางของเรา
• ช่วงเริ่มต้นของแผนภูมิที่สร้างขึ้นตามตารางของเรา
• ช่วงสำหรับดรอปดาวน์ที่ใช้ตารางของเราเป็นแหล่งข้อมูล

ทั้งหมดนี้จะไม่ทำให้คุณเบื่อ😉

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

วิธีที่ 1. ตารางอัจฉริยะ

เน้นช่วงของเซลล์และเลือกจากแท็บ หน้าแรก – รูปแบบเป็นตาราง (หน้าแรก – รูปแบบเป็นตาราง):

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

ตอนนี้ เราสามารถใช้ลิงก์แบบไดนามิกไปยัง "ตารางอัจฉริยะ" ของเราได้แล้ว:

• 1 ตาราง – ลิงก์ไปยังทั้งตารางยกเว้นแถวส่วนหัว (A2:D5)
• ตารางที่1[#ทั้งหมด] – ลิงก์ไปยังทั้งตาราง (A1:D5)
• ตารางที่ 1[ปีเตอร์] – อ้างอิงถึงช่วงคอลัมน์ที่ไม่มีส่วนหัวของเซลล์แรก (C2:C5)
• ตารางที่1[#ส่วนหัว] – เชื่อมโยงไปยัง “ส่วนหัว” พร้อมชื่อคอลัมน์ (A1:D1)

ข้อมูลอ้างอิงดังกล่าวใช้งานได้ดีในสูตรต่างๆ เช่น

= SUM (ตารางที่ 1[มอสโก]) – การคำนวณผลรวมสำหรับคอลัมน์ “มอสโก”

or

=วีพีอาร์(F5;1 ตาราง;3;0) – ค้นหาในตารางสำหรับเดือนจากเซลล์ F5 และออกผลรวมของเซนต์ปีเตอร์สเบิร์กสำหรับเดือนนั้น (VLOOKUP คืออะไร)

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

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

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

เหล่านั้น. ลิงก์ไปยังตารางอัจฉริยะในรูปแบบของสตริงข้อความ (ในเครื่องหมายอัญประกาศ!) จะกลายเป็นลิงก์ที่ครบถ้วน และรายการดรอปดาวน์ตามปกติจะรับรู้

วิธีที่ 2: ช่วงชื่อไดนามิก

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

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

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

ค้นหาเซลล์สุดท้ายโดยใช้ MATCH

MATCH(lookup_value, ช่วง, match_type) – ฟังก์ชันที่ค้นหาค่าที่กำหนดในช่วง (แถวหรือคอลัมน์) และส่งกลับเลขลำดับของเซลล์ที่พบ ตัวอย่างเช่น สูตร MATCH("March";A1:A5;0) จะแสดงผลตัวเลข 4 เนื่องจากคำว่า "March" อยู่ในเซลล์ที่สี่ในคอลัมน์ A1:A5 อาร์กิวเมนต์ฟังก์ชันสุดท้าย Match_Type = 0 หมายความว่าเรากำลังมองหาการจับคู่แบบตรงทั้งหมด หากไม่ได้ระบุอาร์กิวเมนต์นี้ ฟังก์ชันจะสลับไปที่โหมดการค้นหาเพื่อหาค่าที่น้อยที่สุดที่ใกล้ที่สุด ซึ่งเป็นค่าที่สามารถใช้ค้นหาเซลล์สุดท้ายที่เหลืออยู่ในอาร์เรย์ของเราได้สำเร็จ

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

หากอาร์เรย์ของเรามีเพียงตัวเลขเท่านั้น เราสามารถระบุตัวเลขเป็นค่าที่ต้องการได้ ซึ่งมากกว่าค่าใดๆ ในตารางอย่างเห็นได้ชัด:

สำหรับการรับประกัน คุณสามารถใช้ตัวเลข 9E + 307 (9 คูณ 10 ยกกำลัง 307 เช่น 9 ที่มีศูนย์ 307 ตัว) ซึ่งเป็นจำนวนสูงสุดที่ Excel สามารถใช้ได้ตามหลักการ

หากคอลัมน์ของเรามีค่าข้อความ คุณสามารถแทรกโครงสร้าง REPEAT("i", 255) ซึ่งเทียบเท่ากับจำนวนที่มากที่สุดเท่าที่จะเป็นไปได้ - สตริงข้อความที่ประกอบด้วย 255 ตัวอักษร "i" - อักษรตัวสุดท้ายของ ตัวอักษร. เนื่องจาก Excel จะเปรียบเทียบรหัสอักขระจริงเมื่อค้นหา ข้อความใดๆ ในตารางของเราจะ "เล็กกว่า" ในทางเทคนิค กว่าบรรทัด "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy" ที่ยาวเช่นนี้:

สร้างลิงค์โดยใช้ INDEX

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

INDEX(ช่วง; row_num; column_num)

มันให้เนื้อหาของเซลล์จากช่วงตามหมายเลขแถวและคอลัมน์เช่นฟังก์ชั่น =INDEX(A1:D5;3;4) ในตารางของเราที่มีเมืองและเดือนจากวิธีก่อนหน้าจะให้ 1240 – เนื้อหา จากแถวที่ 3 และคอลัมน์ที่ 4 เช่น เซลล์ D3 หากมีเพียงคอลัมน์เดียว สามารถละเว้นตัวเลขได้ เช่น สูตร INDEX(A2:A6;3) จะให้ "Samara" ในภาพหน้าจอสุดท้าย

และมีความแตกต่างกันเล็กน้อยที่ไม่ชัดเจนทั้งหมด: ถ้า INDEX ไม่ได้ป้อนลงในเซลล์หลังจากเครื่องหมาย = ตามปกติ แต่ถูกใช้เป็นส่วนสุดท้ายของการอ้างอิงช่วงหลังเครื่องหมายทวิภาค จะไม่แจกแจงอีกต่อไป เนื้อหาของเซลล์ แต่ที่อยู่ของมัน! ดังนั้น สูตรอย่าง $A$2:INDEX($A$2:$A$100;3) จะอ้างอิงถึงช่วง A2:A4 ที่เอาต์พุต

และนี่คือที่มาของฟังก์ชัน MATCH ซึ่งเราแทรกเข้าไปใน INDEX เพื่อกำหนดจุดสิ้นสุดของรายการแบบไดนามิก:

=$A$2:INDEX($A$2:$A$100; MATCH(ตัวแทน("ฉัน";255);A2:A100))

สร้างช่วงที่มีชื่อ

มันยังคงที่จะบรรจุทั้งหมดไว้ในที่เดียว เปิดแท็บ สูตร (สูตร) และคลิก ชื่อผู้จัดการ (ผู้จัดการชื่อ). ในหน้าต่างที่เปิดขึ้น ให้คลิกปุ่ม สร้างบัญชีตัวแทน (ใหม่)ป้อนชื่อช่วงและสูตรของเราในฟิลด์ พิสัย (อ้างอิง):

มันยังคงคลิกที่ OK และช่วงที่พร้อมใช้งานสามารถใช้ได้ในสูตร รายการแบบหล่นลง หรือแผนภูมิใดๆ

• การใช้ฟังก์ชัน VLOOKUP เพื่อเชื่อมโยงตารางและค่าการค้นหา
• วิธีสร้างรายการดรอปดาวน์ที่เติมข้อมูลอัตโนมัติ
• วิธีสร้างตารางสาระสำคัญเพื่อวิเคราะห์ข้อมูลจำนวนมาก