แผนภูมิตามเซลล์ที่เลือก

สมมติว่าคุณและฉันต้องเห็นภาพข้อมูลจากตารางต่อไปนี้ด้วยมูลค่ายอดขายรถยนต์ตามประเทศต่างๆ ในปี 2021 (ข้อมูลจริงนำมาจากที่นี่โดยวิธีการ):

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

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

การดำเนินการนี้ง่ายมาก คุณเพียงต้องการสองสูตรและมาโครขนาดเล็กหนึ่งรายการใน 3 บรรทัด

ขั้นตอนที่ 1. หมายเลขบรรทัดปัจจุบัน

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

ขั้นตอนที่ 2. เชื่อมโยงไปยังชื่อเรื่อง

ในการแสดงประเทศที่เลือกในชื่อและคำอธิบายแผนภูมิ เราจำเป็นต้องได้รับการอ้างอิงไปยังเซลล์ที่มีชื่อ (ประเทศ) จากคอลัมน์แรก เมื่อต้องการทำเช่นนี้ เราสร้างท้องถิ่นอื่น (เช่น พื้นที่ = แผ่นงานปัจจุบัน ไม่ใช่ Book!) ช่วงที่ตั้งชื่อด้วยสูตรต่อไปนี้:

ที่นี่ ฟังก์ชัน INDEX จะเลือกจากช่วงที่กำหนด (คอลัมน์ A ซึ่งประเทศที่ลงนามของเราตั้งอยู่) เซลล์ที่มีหมายเลขแถวที่เรากำหนดไว้ก่อนหน้านี้

ขั้นตอนที่ 3 เชื่อมโยงไปยัง data

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

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

ขั้นตอนที่ 4 การแทนที่ลิงก์ในแผนภูมิ

ตอนนี้เลือกส่วนหัวของตารางและแถวแรกที่มีข้อมูล (ช่วง) และสร้างแผนภูมิโดยใช้ แทรก – แผนภูมิ (แทรก — แผนภูมิ). หากคุณเลือกแถวที่มีข้อมูลในแผนภูมิ ฟังก์ชันจะแสดงในแถบสูตร แถว (ชุด) เป็นฟังก์ชันพิเศษที่ Excel ใช้โดยอัตโนมัติเมื่อสร้างแผนภูมิเพื่ออ้างอิงข้อมูลและป้ายชื่อต้นฉบับ:

เรามาแทนที่อาร์กิวเมนต์แรก (ลายเซ็น) และอาร์กิวเมนต์ที่สาม (ข้อมูล) อย่างระมัดระวังในฟังก์ชันนี้ด้วยชื่อช่วงของเราจากขั้นตอนที่ 2 และ 3:

แผนภูมิจะเริ่มแสดงข้อมูลการขายจากแถวปัจจุบัน

ขั้นตอนที่ 5. มาโครการคำนวณใหม่

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

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

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

ขั้นตอนที่ 6. เน้นเส้นปัจจุบัน

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

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

แค่นั้นแหละ – เรียบง่ายและสวยงามใช่ไหม

หมายเหตุ / รายละเอียดเพิ่มเติม

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

  =IF(เซลล์(“แถว”)<4,IF(เซลล์("แถว")>4,เซลล์(“แถว”)))
  

• เน้นคอลัมน์ที่ระบุในแผนภูมิ
• วิธีสร้างแผนภูมิเชิงโต้ตอบใน Excel
• การเลือกพิกัด