เมนูแบบเลื่อนลงแบบโต้ตอบและตัวชี้วัดที่เชื่อมโยง

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

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

ในบทเรียนนี้คุณจะสร้างรายการแบบเลื่อนลง และเชื่อมยอดรวม จำนวน และมุมมองที่กรองแล้วเข้ากับรายการนั้น

รายการแบบเลื่อนลงสร้างจากการตรวจสอบข้อมูล ให้เลือกเซลล์ตัวเลือก เช่น B1 จากนั้นเปิดเมนูข้อมูล เลือกการตรวจสอบข้อมูล แล้วเลือก รายการ

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

• ช่วงแหล่งข้อมูล: =Lists!A2:A6 ซึ่งเก็บค่า ตะวันออก ตะวันตก เหนือ ใต้ และทั้งหมด
• หรือสร้างรายการด้วยสูตร เช่น =SORT(UNIQUE(Sales!A2:A500)) ในคอลัมน์ช่วย แล้วให้การตรวจสอบข้อมูลอ้างอิงคอลัมน์นั้น

ตอนนี้ B1 จะแสดงลูกศรเล็กๆ และยอมรับเฉพาะค่าจากรายการของคุณ เซลล์เดียวนี้จะกลายเป็นปุ่มควบคุมของแดชบอร์ด

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

ตัวชี้วัดแรกที่เชื่อมโยงกันคือยอดขายรวมของภูมิภาคที่เลือก เมื่อ B1 เก็บตัวเลือกไว้:

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

เพิ่มตัวเลขที่เชื่อมโยงอีกหนึ่งรายการ: ภูมิภาคที่เลือกมีคำสั่งซื้อกี่รายการ COUNTIF จะอ่านค่าจากเซลล์ตัวเลือกเดียวกัน

วางสูตรนี้ไว้ถัดจากยอดรวมของคุณ:

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

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

ใช้ IF เพื่อแยกกรณีเมื่อเลือกทั้งหมด:

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

นอกจากตัวเลขเพียงรายการเดียวแล้ว รายการแบบเลื่อนลงยังควบคุมตารางแถวรายละเอียดทั้งหมดได้ด้วย FILTER จะอ่านค่าตัวเลือกและกระจายแถวที่ตรงกันออกมา

ใต้ตัวชี้วัดของคุณ ให้วางสูตรนี้:

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

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

ใช้ SUMIFS เพื่อใช้ตัวเลือกทั้งสองพร้อมกัน:

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

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

ในเซลล์ชื่อเรื่อง ให้เขียนสูตรนี้:

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

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

ในพื้นที่ช่วย ให้วางสูตรนี้:

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

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

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

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

หลักการไม่กี่ข้อช่วยให้แดชบอร์ดแบบโต้ตอบได้น่าเชื่อถือ:

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

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

ตรวจสอบว่าคุณเข้าใจวิธีทำให้มุมมองข้อมูลทั้งหมดใช้งานได้

คุณได้เปลี่ยนรายงานแบบคงที่ให้เป็นแดชบอร์ดแบบโต้ตอบได้:

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

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