Unpivot בגוגל שיטס

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

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

בגוגל שיטס אין POWER QUERY 🤔.

הפתרון: נוסחה אחת המורכבת משלוש פונקציות.

להלן הדוגמה: קיימים שלושה בניינים, שלכל בניין 4 קומות.

מהנדס ביצע בדיקה במשך 4 ימים על שלושת הבניינים, בתמונה 1 הדוח שלו.

גוגל שיטס UNPIVOT

תמונה 1: דוח מהנדס

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

סדר הנתונים בתמונה 1 לא מאפשר לנו לבצע טבלת ציר.

עלינו להגיע לסידור הבא לטובת הניתוח:

תמונה 2: סדר הנתונים הנכון

נפרק את הפתרון לשלושה שלבים ונשתמש בשלוש פונקציות:

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

1. הפונקציה ARRAYFORMULA מאפשרת תצוגה של ערכים המוחזרים מנוסחת מערך לתוך מספר שורות ו/או עמודות וגם את השימוש במערכים בפונקציות שאינן מקבלות מערכים. https://support.google.com/docs/answer/3093275
   

2. הפונקציה FLATTEN - מאחדת בעמודה אחת את כל הערכים מטווח אחד או יותר. https://support.google.com/docs/answer/10307761
   

3. הפונקציה SPLIT - מחלקת את הטקסט שסביב התו או המחרוזת שצוינו, ומציבה כל אחד מהחלקים שלו לתא נפרד בשורה. https://support.google.com/docs/answer/3094136

---

שלב א': קיבוץ המערך לתאים בעלי ערך חד-חד ערכי.

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

3. שימוש ב ARRAYFORMULA

שלב ב': הפיכת המערך לטור

נוסיף לתא A8 את הפונקציה FLATTEN, שתהפוך את מערך התאים לטור אחד בתמונה 4

4. פירוק המערך לטור אחד

שלב ג': פירוק הטקסט לתאים

נוסיף לתא A8 את הפונקציה SPLIT ונפצל את הערכים שבתוך התא ע"י המפריד ***

5. פירוק התאים באמצעות הגדרת מפריד

כל שנותר לעשות הוא, להוסיף כותרות לטבלה, לסמן אותה ולהכניס טבלת ציר לניתוח.