תאריכים הם תמיד אתגר אמיתי גם באקסל וגם בגוגל שיטס, במיוחד לנו, הקוראים בעברית.
גם בפונקציית QUERY נדרש מעט תחכום כדי להגיע לתוצאות הרצויות.
במאמר זה אתן שתי דוגמאות לניתוח נתונים הנשען על תאריכים, האחד סינון לפי תאריכים והשני קיבוץ לפי חודש ושנה.
מבנה מסד הנתונים:
המשימה הראשונה לסנן את מסד הנתונים לפי טווח תאריכים:
נכתוב את הנוסחה:
=QUERY(A1:F73,"SELECT A WHERE A>=DATE'2023-05-01' AND A<=DATE'2023-05-30' ")
כתבנו בתא I2, לבחור את טור A כאשר הוא קטן שווה לתאריך 01/05/2023 וגדול שווה לתאריך 30/05/2023
שימו לב כיצד נגדיר תאריך בפונקציה QUERY:
בדוגמה המודגשת באדום כתבנו DATE ואז את התאריך במרכאות ' ' בפורמט YYYY-MM-DD.
קיבלנו את רשימת התאריכים בין הטווחים.
המשימה השניה מורכבת יותר, אנחנו מעוניינים לסכום את ההכנסות בטור C בקיבוץ חודש ושנה של התאריך שמופיע בטור A.
ראשית אנחנו צריכים לעבד את מסד הנתונים, כדי שבטור התאריכים כל התאריכים שמופיעים בחודש ושנה מסויימת יהיו עם מכנה משותף כדי שיהיה אפשרי לקבץ אותם.
נשתמש בפונקציה EOMNTH כדי לקבל עבור כל תאריך את התאריך האחרון באותו החודש.
מסד הנתונים החדש ייבנה כך:
={A1:F1;ARRAYFORMULA(EOMONTH(A2:A73,0)),B2:F73}
ראשית נשתמש בסוגריים מסולסלים כדי לבנות מערך חדש, מי שלא מכיר את השימוש בסוגריים אלו, מוזמן לפנות למאמר זה.
בסוגריים המסולסלים נתחיל עם שורת הכותרות A1:F1 (בנוסחה למעלה בכתום) נפריד בנקודה פסיק ; כדי ששאר המערך יהיה מתחת לשורת הכותרות.
החלק השני הוא הפיכת טווח A2:A73 לתאריך אחיד (בנוסחה למעלה באדום), כדי שלא משנה מה יהיה היום בחודש, תמיד נקבל את התאריך האחרון באותו החודש.
בתמונה למטה נראה שכל מה שהיה בינואר 2023 יופיע באמצעות הפונקציה EOMNTH כתאריך האחרון באותו החודש - 31/01/2023.
חלק אחרון בתוך הסוגריים המסולסלים הטווח B2:F73 מופרד בפסיק ולא נקודה פסיק בכדי שיופיע בצד טור A ולא בהמשך לו.
קיבלנו מערך חדש שנראה כך, דומה למערך המקורי רק שכאן התאריך השתנה לתאריך האחרון בכל חודש.
עכשיו ניגש לשימוש ב QUERY לקבלת קיבוץ וסיכום הנתונים, נכתוב את הפונקציה כך:
=QUERY({A1:F1;ARRAYFORMULA(EOMONTH(A2:A73,0)),B2:F73}, "SELECT Col1 , SUM(Col3) GROUP BY Col1")
החלק בכחול הוא בחירת המערך שבנינו בשלב א' בדוגמה זו,
בחלק הירוק נבחר טור 1, וסיכום טור 3.
*** שימו לב בנוסחה עכשיו אנחנו כבר לא פונים לטורים בשמם A,B,C,D,E וכו', מאחר והסוגריים המסולסלים מפנים אותנו למערך (אינו תלוי במיקום הטורים בגיליון), עכשיו נפנה לטורים לפי מיקומם במערך ולא לפי שמם המקורי.
לטור שהיה טור A נקרא Col1
לטור C נקרא Col3 וכו'.
נקבץ לפי טור 1 - טור התאריך
ונסכום לפי טור 3- טור ההכנסה.
=QUERY({A1:F1;ARRAYFORMULA(EOMONTH(A2:A73,0)),B2:F73}, "SELECT Col1 , SUM(Col3) GROUP BY Col1")
עכשיו נשתמש בתוספת פקודה נוספת של FORMAT כדי לשנות את עיצוב הנתונים.
נכתוב את הפונקציה כך:
=QUERY({A1:F1;ARRAYFORMULA(EOMONTH(A2:A73,0)),B2:F73}, "SELECT Col1 , SUM(Col3) GROUP BY Col1 FORMAT Col1'YYYY-MMM', SUM(Col3)'$##,###'")
כל החלק בנוסחה בשחור, הוא החלק מהשלב הקודם,
באדום נוסיף את הפקודה FORMAT.
אנחנו משנים את הפורמט של טור 1 לשנה וחודש בלבד ללא יום, ואת הסכום של טור 3 אנחנו משנים לפורמט עם סימן ה-$ ופסיק מפריד לאלפים.
נקבל טבלת ציר מקובצת לפי חודש ושנה עם סכום ההכנסה.
אכן הפונקציה QUERY מאוד גמישה, אבל גם קצת מורכבת, נסו לעקוב אחרי השלבים מהמאמר הראשון ובנו את ההיגיון שלב אחר שלב.
בהצלחה!
Comments