דף הבית > אקסל מתקדם, תרשימים > רשימת טופ 5 דינאמית לדשבורד – ככה עושים את זה

רשימת טופ 5 דינאמית לדשבורד – ככה עושים את זה

FB_Pic

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

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

 

 

 

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

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

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

db1

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

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

db2

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

 

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

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

db4

קליק להגדלה

 

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

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

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

 

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

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

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

db6

 

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

 

קליק להגדלה

קליק להגדלה

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. כעת נעבור ליצירת התרשים:

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

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

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

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

db10

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

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

db11

 

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

 

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

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

 

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

 

בהצלחה!

 

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

  1. ויזואלייזר
    19 ינואר, 2015 מתוך 11:00 | #1

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

  2. אסף
    29 נובמבר, 2015 מתוך 01:32 | #2

    הי,
    שיטה נחמדה אך אפשר לפשט קצת ע"י שמוש ב OFFSET כדי ליצור את הטבלה/תרשים דינמי במקום הפונקציות הארוכות

  3. שחר לוי
    4 אוגוסט, 2016 מתוך 15:45 | #3

    היי מי יכול לעזור לי עם הקובץ?
    אני מנסה לקחת את זה לקובץ שהכנתי והלכתי לאיבוד,
    הבעיה שזה לא על מכירות וזה טיפה שונה

    מי מוכן לעזור?

  4. שחר לוי
    4 אוגוסט, 2016 מתוך 16:28 | #5

    שלחתי :)

    תודה רבה על התגובה המהירה

  5. שחר לוי
    7 אוגוסט, 2016 מתוך 13:42 | #6

    היי מקווה שזה לא מסובך מה ששלחתי, אני חושב שיהיה לי יותר קל ללמוד ברגע שיהיה תרשים שמתאים לטבלה שלי

  1. אין הפניות עדיין.