วิธีกำหนดช่วงของค่าใน Excel

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

เปลี่ยนช่วงเซลล์ใน Excel . โดยอัตโนมัติ

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

วิธีเปลี่ยนช่วงอัตโนมัติใน Excel

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

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

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

ดังนั้นจึงจำเป็นต้องแก้ปัญหานี้โดยใช้ชื่อไดนามิก ในการสร้างคุณต้องดำเนินการตามลำดับต่อไปนี้:

1. ไปที่แท็บ "สูตร" ซึ่งอยู่ในเมนูหลัก จะมีส่วน "ชื่อที่กำหนด" ซึ่งมีปุ่ม "กำหนดชื่อ" ซึ่งเราต้องคลิก
2. จากนั้นกล่องโต้ตอบจะปรากฏขึ้นซึ่งคุณต้องกรอกข้อมูลในฟิลด์ตามที่แสดงในภาพหน้าจอ สิ่งสำคัญคือต้องสังเกตว่าเราจำเป็นต้องใช้ฟังก์ชัน = การกำจัด พร้อมกับฟังก์ชั่น ตรวจสอบเพื่อสร้างช่วงการอัปเดตอัตโนมัติ 
3. หลังจากนั้นเราจำเป็นต้องใช้ฟังก์ชัน SUMซึ่งใช้ช่วงไดนามิกของเราเป็นอาร์กิวเมนต์ 

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

ฟังก์ชัน OFFSET ใน Excel

ลองดูฟังก์ชั่นที่เราบันทึกไว้ในฟิลด์ "ช่วง" ก่อนหน้านี้ การใช้ฟังก์ชัน การกำจัด เราสามารถกำหนดจำนวนช่วงที่กำหนดจำนวนเซลล์ในคอลัมน์ B ที่เติมได้ อาร์กิวเมนต์ของฟังก์ชันมีดังนี้:

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

คุณไม่จำเป็นต้องระบุสองอาร์กิวเมนต์สุดท้ายหากไม่ต้องการ ในกรณีนี้ ค่าช่วงจะเป็นเพียงเซลล์เดียว ตัวอย่างเช่น หากคุณระบุสูตร =ออฟเซ็ต(A1;0;0)สูตรนี้จะอ้างอิงถึงเซลล์เดียวกับเซลล์ในอาร์กิวเมนต์แรก หากตั้งค่าออฟเซ็ตแนวตั้งเป็น 2 หน่วย ในกรณีนี้ เซลล์จะอ้างอิงถึงเซลล์ A3 ทีนี้มาอธิบายรายละเอียดความหมายของฟังก์ชันกัน ตรวจสอบ.

ฟังก์ชัน COUNT ใน Excel

การใช้ฟังก์ชัน ตรวจสอบ เรากำหนดจำนวนเซลล์ในคอลัมน์ B ที่เราเติมทั้งหมด นั่นคือ การใช้สองฟังก์ชัน เราจะกำหนดจำนวนเซลล์ในช่วงที่เติม และกำหนดขนาดของช่วงตามข้อมูลที่ได้รับ ดังนั้นสูตรสุดท้ายจะเป็นดังนี้: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

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

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

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

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

แผนภูมิไดนามิกใน Excel

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

1. เราเลือกช่วงของเรา หลังจากนั้นเราแทรกแผนภูมิประเภท "ฮิสโตแกรมพร้อมการจัดกลุ่ม" คุณสามารถค้นหารายการนี้ได้ในส่วน "แทรก" ในส่วน "แผนภูมิ - ฮิสโตแกรม"
2. เราทำการคลิกเมาส์ซ้ายบนคอลัมน์สุ่มของฮิสโตแกรม หลังจากนั้นฟังก์ชัน =SERIES() จะแสดงในบรรทัดฟังก์ชัน ในภาพหน้าจอ คุณสามารถดูสูตรโดยละเอียด 
3. หลังจากนั้นจะต้องทำการเปลี่ยนแปลงบางอย่างกับสูตร คุณต้องเปลี่ยนช่วงหลัง “Sheet1!” ถึงชื่อของช่วง ซึ่งจะส่งผลให้ฟังก์ชันต่อไปนี้: =ROW(Sheet1!$B$1;;Sheet1!รายได้;1)
4. ตอนนี้ยังคงต้องเพิ่มระเบียนใหม่ลงในรายงานเพื่อตรวจสอบว่าแผนภูมิได้รับการอัปเดตโดยอัตโนมัติหรือไม่

ลองดูแผนภาพของเรา

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

ช่วงที่มีชื่อและการใช้งาน

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

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

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

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

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

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

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

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

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

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

ถ้าชื่อเรื่องมีหลายคำ Excel จะแยกคำเหล่านั้นด้วยขีดล่างโดยอัตโนมัติ

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