הסברים הדרכתיים

איך להתקבל לעבודה הדורשת אקסל?

By אוקטובר 12, 2017 No Comments

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

  1.    Pivot tables \ טבלאות ציר
  2.   VlookUp

ובעוד שהפשטות שבתשובה הזאת מעולם לא הייתה נכונה – בפוסט הזה – נלמד לעשות פיבוט (טבלאות וגרפים) ולהשתמש ב- VlookUp (והזדמנות אחרת – באח הקרוב של הפונקציה – HlookUp ובבן דוד המוכשר והמסובך יותר –  Index Match.

טבלאות ציר – יתרונות, חסרונות וכל מה שבניהם

נתחיל מהוידוי האפל הראשון שלי –

אני לא משתמש בטבלאות ציר כמעט בכלל ולמען האמת – אני די מתעב אותן.

'אבל למה?' תשאלו, 'הרי הן כל כך נוחות, ומציגות כל כך הרבה מידע ואתה יכול לשחק עם המידע הזה!'

אגיד לכם למה –

  1.   הפונקציונאליות שלה, בעיניי, יותר מגבילה ממאפשרת – עיצובים פחות גמישים, תחושת השליטה שפשוט כמעט ולא קיימת שם והעובדה שכל פעם שאני לוחץ על הטבלה תפריט הצד קופץ לי וזה פשוט לא ממשק משתמש נוח.
  2.   ניתן להשיג מה שאנחנו רוצים עם פונקציות אחרות – מה שמאפשרת לנו יותר גמישות גם במובן הגרפים וגם בהצגת הנתונים בטבלה (נגיד, עבור מפות חום קטנות) – מניח שאנחנו נראה פה פוסט על זה בעתיד הקרוב.
  3.   אני קצת פוץ שחושב שאם זה מה שכולם עושים – יכול להיות משהו קצת לא בסדר עם זה.

למען האמת, אם כבר אני עושה טבלאות ציר, אעדיף לעשות את זה עם פייתון (כמו שעשיתי כאן, כשלב מכין למפת החום) – אבל, התחייבנו להסביר על טבלאות ציר, וזה מה שנעשה!

מה היא טבלת ציר?

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

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

למה אנחנו צריכים את זה?

נניח ובחרתי להתמסחר עוד לפני שהבלוג הזה עשה משהו בחיים שלו ואני מוכר באתר שלי ציוד ממותג של אנאליסיס פראליסיס – חולצות, מקלדות וקפוצ'ונים מיוחדים להאקרים (לא יודע, נראה לי מתאים).

יש לי את הטבלה הזאת שמכילה מידע על כל ה-850 רכישות האחרונות שנעשו באתר שלי, נגיד בחודש האחרון, כדי לתחום את זה בזמן.

טבלת רכישות פיקטיבית מרוכזת

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

איך עושים טבלת ציר?

  1. הוספה >> לחיצה על Pivot Table.
  2. בחלון הקטן שנפתח – מסמנים את המידע שממנו תיגזר טבלת הציר.
  3. בוחרים איפה למקם את טבלת הציר – גיליון עבודה חדש או גיליון עבודה קיים. 
  4. אקסל יעביר אותנו למיקום שבו נמצאת טבלת הציר הריקה שתראה כך:
טבלת ציר ריקה - Empty Pivot Table

טבלת ציר ריקה המתאימה לטבלת מכירות

אנחנו רואים שיש לנו 4 אופציות למילוי –

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

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

ואיך עושים גרף ציר?

שתי אופציות –

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

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

יחד עם שיקול דעת בריא, אפשר לקבל החלטה עסקית מיידית(*) שתנסה לשנות את המצב הנוכחי על מנת לעמוד במטרות העסקיות שלי\ של הלקוח שלי.


איך עושים Vlookup?

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

בעצם מה הפנקציונליות של וילוקאפ?

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

או במלל אנושי – תחזיר לי את הערך בעמודה X שמתאים לערך שנבקש בתוך הטבלה.

צ'יש,עדיין נראה קצת גמלוני – בואו נפרק את זה.

ערכים אפשריים לפונקצית VlookUp

ערכים אפשריים לפונקציית VlookUp

  • הערך הראשון – lookup_value – איזה ערך אנחנו רוצים למצוא במערך\טבלה שלנו
  • מערך\טבלה שלנו – table_array – מה טווח הערכים שבו נחפש?
  • מספר האינדקס של העמודה – col_index_num – את הערך המתאים של איזה מספר עמודה להחזיר? שימור לב, בניגוד לפייתון או ג'אווה סקריפט – המערך הזה מתחיל מ-1 ולא מ-0.
  • סוג ההתאמה – אופציונלי (כמו בכל פעם שאנחנו רואים סוגריים מרובעים בנוסחאות אקסל) – האם אנחנו מחפשים התאמה מדוייקת (TRUEׂ או משוערת ׁ(0 או FLASE) – עדיין לא יצא לי להשתמש בהתאמה לא מדויקת ואני משתמש אקסל פעיל מ-2003 בערך, אז כאילו, לא נראה לי קריטי.

בואו נשחק עם זה קצת.

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

אז נניח שהוצאתי מהאנליטיקס את הטבלה הבאה (שוב, עבור תקופה של חודש, נגיד):

דו"ח פיקטיבי של נתוני אנליטיקס

וממערכת ה-CRM, את הטבלה הבאה:

דו"ח פיקטיבי של מערכת CRM

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

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

בעמודה בצד השמאלי של הטבלה שאליה נעביר את המידע נתחיל עם הנוסחה שלנו –

מה שאנחנו נבקש בנוסחה במלל הוא כזה דבר – חפש לי, במדויק, את שם המשתמש בטבלת האנליטיקס, אליו תעביר את הערך המתאים מטבלת ה-CRM מעמודה מספר 8.

הנוסחה תראה ככה:

=VLOOKUP(USER NAME CELL, ANALYTICS REPORT,8, 0)

חשוב לזכור, כיוון שאנחנו עובדים עם טבלאות ומסדי נתונים יותר גדולים מ-13 שורות, את השימוש במקש הקיבוע הנאמן – F4, במקרה הזה אנחנו מדברים על משהו פשוט – אנחנו רק רוצים לקבע את הטבלה (מה שקראתי לו ANALYTICS REPORT), אבל המתודה הזאת מספקת לנו גמישות מאוד גדולה בנוסחאות, ומאפשרת לנו לחסוך המון הקלדות מיותרות.

אחרי שסיימנו עם זה, אנחנו יכולים לראות את הנתונים שלפנינו, להתחיל להריץ עליהם מודלים שונים, מרגרסיה לינארית ועד קלאסטרינג (שוב, בהנחה שיש לנו כמות מידע משמעותית יותר מ-13 שורות).


לסיכום, בעוד שיש לי ביקורת לא מעטה על המדד הלאומי בישראל של ידיעת אקסל לשלבי ראיונות העבודה, אין לנו ברירה אלא לקבל את זה.

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

א. לבצע את המשימה שלי בצורה נכונה (מדד האפקטיביות).

ב. לדאוג שביצוע המשימה הזאת יהיה כמה שיותר יעיל, מהיר ומקיף  (מדד היעילות).

 


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