עיבוד נתונים על גבי אקסל 2016

By | 3 באוקטובר 2015

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

טעינת נתונים ממקור חיצוני

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

Excel2016_Query

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


Optin Architect

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

Excel2016_Facebook

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

Excel2016_QueryEdit

PowerPivot

כמו תוספים אחרים (PowerQuery) שבעבר דרשו הורדה והתקנה בנפרד. גם PowerPivot  הוא עכשיו חלק אינטגרלי מאקסל. ברם, כברירת מחדל הוא אינו פעיל. כדי להפעיל תוסף זה יש לגשת לקבצים –> הגדרות –> תוספי COM ולהפעילו. PowerPivot זכה למספר פונקציות DAX חדשות אך לדעתי לא להרבה יותר מכך. חלק מתכונות PowerPivot "זלגו" אל טבלת הציר הרגילה. למשל ניתן כעת להגדיר בטבלת ציר חישובים באמצעות DAX. על כך בהמשך.

Pivot Table – טבלת ציר

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

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

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

Excel2016_AddCalculateMeasure

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

Eexcel2016_CalculateMeasure

חיבור מספר טבלאות נתונים לטבלת ציר אחת

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

Excel2016_MultipleTable

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

 הגדרה של SET / ערכות

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

Excel2016_Set

עוד כמה דברים…

Eexcel2016Pivotבצילום שלמעלה הוספתי מסגרות ומספרים:

  1. חיפושים: ניתן לחפש שדה מסוים או לחפש ערך בתוך שדה. למשל ברשימת הלקוחות ניתן לחפש את השם רימון. כאשר החיפוש מבוצע בעת הכתיבה: כותבים ר' מקבלים את כל הלקוחות שמתחילים ב- ר'. וכן הלאה.
  2. ערכות וערכים מחושבים – תוארו למעלה.
  3. טיפול בזמן ותאריכים – בטבלת המקור ישנו שדה אחד עם תאריך ערך – Posting Date. כאשר אקסל "פוגש" שדה תאריך הוא בונה לו באופן אוטומטי שדות עם היררכיה של תאריכים (רבעון, חודש, שנה). בטבלה אפשר לראות את ההיררכיה הזו אשר כאמור נוצרה באופן אוטומטי כולל שם הרבעון בעברית. בנושא תאריכים אפשר לשים לב גם לכפתור חדש – ציר זמן אשר יוצר אובייקט ציר זמן (נמצא מעל הטבלה) באופן אוטמטי.
  4. דחה עדכון פריסה תכונה שאיני מבין מדוע לקח כל כך הרבה זמן להוסיפה. כאשר אנו רוצים לבצע מספר שינויים בטבלה וישנה כמות גדולה של נתונים. כל שינוי יכול לקחת מספר שניות וזה מעצבן. אפשרות זו קובעת שנבצע את כל השינויים ללא עדכון נתונים בפועל ורק לחיצה על כפתור עדכן יעודכנו הנתונים בפועל.
  5.  התנהגות ה- Slicer – כלי פריסה. בעבר לחיצה על כפתור הייתה בוחרת לחתוך לפי האובייקט של הכפתור וכדי לבחור למשל מספר פריטים היינו צריכים להחזיר CTRL לחוץ. עתה אפשר לבחור כברירת מחדל שלחיצה על כפתור אחרי כפתור תבצע בחירות מרובות.
Share

One thought on “עיבוד נתונים על גבי אקסל 2016

  1. יוסי אלקיים

    רימון יישר כוח , כול הכבוד , תמיד חוקר ומפתיע בחידושים 🙂

כתיבת תגובה

האימייל לא יוצג באתר.