การ์ด KPI และการเน้นตามเงื่อนไข

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

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

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

เริ่มจากตัวชี้วัดหลัก รายได้รวมจากชีตยอดขายก็คือการใช้ SUM กับคอลัมน์จำนวนเงิน

วางสูตรนี้ในเซลล์ค่าของการ์ด:

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

KPI หลายตัวเป็นอัตราส่วน มูลค่าคำสั่งซื้อเฉลี่ยคำนวณจากรายได้รวมเทียบกับจำนวนคำสั่งซื้อ สร้างค่านี้จากค่ารวมสองค่า

วิธีที่กระชับคือใช้ AVERAGE โดยตรง:

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

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

ค่าความแตกต่างคือค่าปัจจุบันลบด้วยเป้าหมาย:

คุณยังสามารถแสดงค่าเป็นเปอร์เซ็นต์ของเป้าหมายได้ด้วย =SUM(Sales!C2:C500)/B1 การแสดงค่า 1.12 บอกผู้อ่านว่าคุณทำได้ 112 เปอร์เซ็นต์ของเป้าหมาย การเปรียบเทียบเปลี่ยนตัวเลขดิบให้กลายเป็นเรื่องราว

ผู้อ่านมักต้องการเห็นแนวโน้ม ให้คำนวณเปอร์เซ็นต์การเปลี่ยนแปลงจากงวดก่อน สมมติว่ายอดรวมของงวดนี้อยู่ใน D2 และยอดรวมของงวดก่อนอยู่ใน D3

สูตรการเติบโตคือ:

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

คุณสามารถแสดงทิศทางด้วยคำหรือสัญลักษณ์โดยใช้ IF อ่านเซลล์การเปลี่ยนแปลงแล้วเลือกป้ายกำกับ

สูตรนี้จะแสดงเครื่องหมายบอกแนวโน้มขึ้นหรือลงพร้อมเปอร์เซ็นต์:

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

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

หากต้องการทำเครื่องหมายการ์ดที่ต่ำกว่าเป้าหมายด้วยสีแดง ให้ใช้กฎสูตร เช่น:

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

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

หากต้องการระบุผลการดำเนินงานเทียบกับเป้าหมาย:

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

KPI ในการใช้งานจริงมักมีมากกว่าสองสถานะ เช่น ดี เตือน และวิกฤต ฟังก์ชัน IFS จะตรวจสอบเงื่อนไขตามลำดับและคืนค่าที่ตรงกันรายการแรก จึงอ่านเข้าใจง่ายกว่า IF ที่ซ้อนกัน

หากต้องการแบ่งระดับผลการดำเนินงานเป็นสามช่วง:

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

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

เลือกช่วงข้อมูล เปิดการจัดรูปแบบตามเงื่อนไข แล้วเลือกมาตราสี เช่น ไล่จากสีเขียวไปสีแดง

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

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

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

• การ์ด 1: รายได้รวม, =SUM(Sales!C2:C500) พร้อมค่าความแตกต่างจากเป้าหมาย
• การ์ด 2: คำสั่งซื้อ, =COUNTA(Sales!A2:A500)
• การ์ด 3: มูลค่าคำสั่งซื้อเฉลี่ย, =AVERAGE(Sales!C2:C500) พร้อมเปอร์เซ็นต์การเติบโต

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

คุณได้สร้างชั้นข้อมูลพาดหัวของแดชบอร์ด:

• สูตรการรวมข้อมูล เช่น SUM, COUNTA และ AVERAGE สร้างตัวเลข KPI ขนาดใหญ่
• สูตรค่าความแตกต่างและการเติบโตเพิ่มบริบทการเปรียบเทียบกับเป้าหมายและงวดก่อน
• IF และ IFS เปลี่ยนตัวเลขให้เป็นคำแสดงสถานะ เช่น อยู่ตามเป้าหมาย เฝ้าระวัง และวิกฤต
• การจัดรูปแบบตามเงื่อนไขที่ใช้ร่วมกับกฎสูตรจะเปลี่ยนสีเซลล์โดยอัตโนมัติเพื่อทำเครื่องหมายสิ่งที่สำคัญ

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