הצגת רשימת טופ 5 בדשבורד הינה אחת הדרכים האפקטיביות ביותר להמחשת ביצועים.

ניתן להעצים את החוויה על ידי מתן אפשרות למקבלי החלטות לבחור את חתך התקופה באופן דינאמי. בפוסט זה אציג כיצד מבצעים זאת.

רשימת טופ 5 סטטית

ראשית, אתייחס בקצרה לאיך בונים רשימת טופ 5 סטטית (ללא אפשרות לבחור תקופה). לצורך כך נשתמש בנוסחת Large, אשר מחזירות את הערך ה-N בגודלו במערך של ערכים, כאשר הפרמטרים הם טווח הערכים ו-N.

להלן נתוני מכירות של 10 סוכנים. במקרה זה, ניתן להציג רשימת טופ 5 לפי גובה המכירות או לפי כמות העסקאות.

נתחיל עם גובה המכירות. נשתמש בנוסחת Large על עמודת סה"כ מכירות, כאשר לכל שורה פרמטר N בנוסחה יגדל ב-1: עבור השורה הראשונה נקבל את הסכום הגבוה ביותר, עבור השניה את השני בגודלו וכו'.

כעת, נשתמש בנוסחאות Index ו-Match על מנת להשלים את שם הסוכן ומספר העסקאות בהתאם. (שכחתם איך משתמשים ב-Match ו-Index? תזכורת בלינק הזה):

על מנת לבצע דירוג לפי כמות העסקאות יש לבחור את עמודת "מספר עסקאות" בנוסחאות שתוארו, אך יש לשים לב למקרים בהם מספר העסקאות יכול להיות זהה בין שני סוכנים או יותר. במקרה כזה, יופיע הסוכן הראשון ברשימה בלבד. אבל אל דאגה, יש שיטה להתמודד עם המצב: תשתמשו בנוסחה הבאה כדי להוסיף ערך מזערי לכמות העסקאות של כל סוכן – מזערי מספיק כדי לא להשפיע משמעותית על התוצאות ובו בזמן גדול מספיק כדי שנוסחאות Large או Small לא יתעלמו ממנו: למשל, Rand()/99999+. בגלל שהערך שיתווסף הינו אקראי לכל שורה, הסיכוי ששני סוכנים יקבלו בדיוק את אותה התוספת הוא אפסי וכך נפתרה הבעיה של ערכים זהים בין סוכנים.

רשימת טופ 5 דינאמית

המשמעות של רשימה דינאמית היא שנרצה לקבל רשימת טופ 5 רלוונטית לתקופה מסוימת, שאותה נוכל לבחור ע"י פקד בתרשים או תיבת קומבו (למשל, 5 הסוכנים המובילים בחודש ינואר 2014). משמעות הדבר היא שנצטרך נוסחה שתדע לקבל תקופה כפרמטר (שנה וחודש במקרה שלנו) להחזיר רשימת טופ 5 רלוונטית. לצורך כך, נגדיר תאי קלט לחודש ושנה ונשתמש בטכניקת נוסחאות מערך בשילוב עם נוסחאות Index, Match ו-Large:

קליק להגדלה

הנוסחה מאתרת את שם הסוכן הרלוונטי לכל מיקום ברשימה. יש להזין את הנוסחה בתא הראשון (H9). לאחר מכן, יש ללחוץ F2 כדי להיכנס למצב עריכה ואז ללחוץ ctrl+shift+enter – פעולה זו תהפוך את הנוסחה לנוסחת מערך. בשלב הסופי יש להעתיק את הנוסחה בתא H9 ולהדביק אותה בשאר השורות בעמודת "סוכן מכירות": H10:H13.

הנוסחה מבצעת התאמה בין תא השנה לעמודת "שנה" ותא החודש לעמודת "חודש" בטבלת הנתונים. במידה ונמצאה התאמה, הנוסחה תשמור בזיכרון את ערך המכירות של השורה המתאימה, אחרת תשמור 0. לאחר חישוב זה, יתבצע איתור בעזרת Large כפי שהוסבר לעיל, ולאחריו שימוש ב-Match ו-Index לאיתור שם הסוכן הרלוונטי. הפיכת הנוסחה לנוסחת מערך מאפשר ביצוע חישובים מורכבים עם מערכים בתוך תא בודד. לדוגמא נוספת לשימוש בטכניקה זו ראו את הפוסט הזה.

בשלב האחרון, כל מה שנשאר לעשות הוא להשלים את הנתונים של עמודות "מספר עסקאות" ו-"סה"כ מכירות" בעזרת Index ו-Match או Vlookup.

יצירת תרשים אינטראקטיבי לדשבורד

עכשיו מגיע החלק המעניין: כיצד שילוב של פקד לשליטה דינאמית על הנתונים המוצגים – במקרה זה: חתך התקופה  – ישירות בתוך הדשבורד/התרשים שלכם.

1. נתחיל עם יצירת הפקד לשילטה על התקופה המוצגת. קליק על לשונית מפתחים (Developers) -> הוספה -> פקדי טופס -> סרגל גלילה (ScrollBar)

2. סימון סרגל גלילה אופקי עם העכבר בגודל הרצוי:

קליק להגדלה

3. קליק ימני על סרגל הגלילה שיצרתם ולחיצה על מאפיינים:

יש להזין ערך מינימאלי ומקסימאלי לסרגל. במקרה שלנו, הערך המינימאלי יהיה שווה 1 והמקסימאלי יהיה שווה לסה"כ חודשי הנתונים (בדוגמא: 24 חודשים – שנתיים מלאות). כמו כן, יש לבחור תא ריק שיישמש כתא היעד לשינויים בסרגל הגלילה ויישקף את ערכו. (בדוגמא: נבחר בתא J4). לחיצה על אישור תסיים את תהליך הגדרת הסרגל.

בשלב זה, ברגע שנזיז את סרגל הגלילה, תא J4 יקבל ערך בין 1-24, בהתאם למיקום היחסי של הסמן על הסרגל.

בגלל שהשתמשנו בפקדי חודש ושנה נפרדים (תאים I4 ו-I5 בהתאמה), יש צורך לתרגם את הטווח הרציף של הפקד (1-24) לחודש ושנה ספציפיים. בדוגמא שלנו יש נתונים עבור שנים 2013 ו-2014, לכן בתא I5 המכיל את השנה נכתוב את הנוסחה הבאה:

=2013+INT((J4-1)/12)

ובתא I4 המכיל את החודש נכתוב את הנוסחה הבאה:

=IF(MOD(J4,12)=0,12,MOD(J4,12))

התוצאה: ברגע שנזיז את סרגל התקופה שיצרנו, השנה והחודש יתעדכנו בהתאם.

קליק להגדלה

4. כעת נעבור ליצירת התרשים:

אפשר לעצב בהתאם לטעמכם: גודל פונטים, צבעים, סרגל הערכים, תויות נתונים וכו'.

כמו כן, ניתן להוסיף כותרת דינאמית שתשקף את הנתונים הנצפים בתרשים: בתא כלשהו (נניח, J4) נזין את הנוסחה הבאה:

="טופ 5 אנשי מכירות "&$I$5&" – "&$I$4

לאחר מכן, קליק על כותרת התרשים והזנה של "J4=" בשורת הנוסחה. כך קיבלנו כותרת תרשים דינאמית אשר מקושרת ישירות לתאי הקלט של חודש ושנה (תאים I5 ו-I4). ברגע שנזיז את סרגל התקופה, כותרת התרשים תתעדכן בהתאם:

5. בשלב הסופי והאחרון, נמקם את סרגל בחירת התקופה בתחתית התרשים ונקבע אותו לתרשים ע"י Group:

קליק על הסרגל -> לחיצה על ctrl + קליק על התרשים -> כששניהם מסומנים, קליק ימני על אחד מהם -> Group (קיבוץ).

זהו. אפשר למקם את התרשים בדשבורד, ולתת למקבלי ההחלטות חווית שימוש של מערכת BI מקצועית!

אהבתם? תרשימים מגניבים נוספים ניתן למצוא כאן

יש לעיין בדיסקליימר

אתם מוזמנים להוריד את קובץ ההדגמה עם מערכת פעילה ומתפקדת.

בהצלחה!

האם פוסט זה הינו מועיל? נשמח לקבל פידבק בתגובות למטה. תודה!