אנליסטים אנונימיים

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

תשכחו מ-Vlookup, בואו ללמוד איך עובדים המקצוענים.

 

 

 

 

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

אז מה הפתרון? SQL!

SQL (קיצור של Structured Query Language) היא שפת מחשב לשליפה, מידול, ומניפולציה של נתונים בבסיסי נתונים רלציוניים. מדובר בכלי ייעודי לעיבוד של כמויות אדירות של מידע, אשר נמצע בבסיסה של כל מערכת מידע מודרנית. כל אנליסט חייב להכיר SQL לפחות ברמה הבסיסית, לכן אם את/ה עדיין לא בעניינים מומלץ להתחיל כאן: לימוד SQL.

 

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

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

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

בדוגמא הבאה נדגים הקמה של שתי טבלאות נתונים עם שדה מקשר ביניהן (Foreign Key בשפה ה-SQLית), ובניית דוח אשר ירכז נתונים משתי הטבלאות, על בסיס שאילתת SQL מסוג Inner Join. והכל, במסגרת האקסל בלבד.

 

1. נתחיל בהוספת נתונים.

להלן נתוני היקף הזמנות לפי תאריך ולקוח.

נמקם את הנתונים בגיליון חדש החל מתא A1, ונקרא לו Orders.

השם והמיקום חשוב. ברגע שהנתונים נמצאים בתא A1, האקסל יתייחס לגיליון כולו כטבלת נתונים דטה-בייסית ששמה "Orders".

 

 

 

 

 

 

 

2. נחזור על הפעולה עם נתוני שיוך לקוחות לאזור גאוגרפי.

נמקם את הנתונים בגיליון חדש ונקרא לו "Clients".

 

 

 

 

 

 

 

 

 

 

3. נשמור את הקובץ כדי שנוכל להתייחס אליו כאל מקור נתונים. בדוגמא להלן, שמרנו תחת השם "OrderAnalysis.xlsx":

 

4. נוסיף גיליון חדש (נקרא לו: "Join"), שאליו נרצה להביא נתונים מעובדים.

בגיליון החדש, קליק על לשונית "נתונים" -> "מקורות אחרים" -> Microsoft Query:

 

5. בחלון שייפתח, נבחר "Excel Files" כמקור הנתונים, ואז קליק על "OK":

 

6. ננווט אל הקובץ "OrderAnalysis.xlxs" שאותו שמרנו מקודם:

 

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

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

 

ולאחר מכן, נבחר את הערך של ההזמנות מטבלת "Orders":

 

8. לחיצה על אישור תקפיץ ברוב המקרים את ההודעה הבאה, לפיה יש ליצור חיבור ידני בין הטבלאות:

 

לחיצה על "OK" תפתח את מסך ניהול השאילתות, אשר יציג לנו את המבנה הטבלאי של מקורות הנתונים, ומדגם של התוצאות שיתקבלו במידה ונריץ את השאילתה. על מנת ליצור חיבור בין הטבלאות, נגרור את השדה "ClientID" מטבלת ה-Clients לאותו השדה בטבלת ה-Orders, ונקבל:

 

9. כעת, יש להחליט איזו אגרגציה נרצה להפעיל על הנתונים. דבל-קליק על שדה ה-Value בטבלת התוצאות -> בחירה ב-SUM ועדכון שם השדה החדש ל-Total Orders:

 

 

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

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

נוכל לעצב את טבלת התוצאות איך שנרצה, ככל טבלת אקסל רגילה:

 

10. במידה ונרצה לערוך את שאילתת ה-SQL ישירות, קליק על "נתונים" -> "חיבורים" -> בחירה בחיבור שיצרנו -> "מאפיינים":

 

כאן נוכל לתת שם בעל משמעות לחיבור, וכן לערוך את קוד ה-SQL ישירות. למשל, בדוגמא להלן, נעדכן את שם השדה ל-'Total Orders' ונוסיף מיון בסדר יורד לפי גובה ההזמנות. כמובן, אפשר לכתוב שאילתות בכל רמת מורכבות, כולל סוגים שונים של Join's:

 

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

 

יתרונות

1. ביצועים. כשמדובר בכמויות נתונים קטנות (מספר אלפי שורות לכל היותר), ל-SQL אין יתרון ביצועים על פני vlookup או נוסחאות אקסל דומות. היתרון המשמעותי מגיע עם הגודל. ניקח למשל את הדוגמא של ההזמנות לעיל. עם כ-925,000 שורות בטבלת ההזמנות וכ-12,000 שורות בטבלת הלקוחות, דוח ההזמנות לפי אזור הופק עם SQL ב-8.2 שניות בעוד של-Vlookup ו-Sumif נדרשו מעל 35 שניות להשלים את העבודה (וזה על מחשב חזק במיוחד: מעבד i7 מרובע ליבות, 8GB זיכרון פנימי, כונן קשיח SSD, ווינדווס 10).

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

3. עבודה אפקטיבית. דברים מסוימים מסורבלים לביצוע באקסל ומצד שני הינם מאד פשוטים עם SQL. למשל פילטרים. נכון שאפשר להפעיל סינון אוטומטי, אבל רק פעם אחת בכל גיליון. יתרה מכך, כשהסינון מופעל, שורות מסוימות מוסתרות עבור כל העמודות, ולעתים מסתירות תרשימים ואלמנטים נוספים. לעומת זאת, שאילתת SQL משפיעה אך ורק על הטבלה הספציפית, ולא על העמודות הסמוכות. דוגמאות נוספות כוללות ביצוע פעולות על מספר לא ידוע מראש של שורות ופקודות כגון Left/Right Join, Select Distinct, Union – קל משמעותית ליישם באמצעות SQL.

 

הערות והמלצות

1. קוד ה-SQL בעריכת השאילתה במסגרת הפתרון הנ"ל חייב להיות באותיות גדולות (Upper Case).

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

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

 

 

 

בהצלחה!