במאמר הקודם השתמשנו פונקציית QUERY לקבץ לפי סכום, ממוצע ועוד,
במאמר זה, נלמד ליצור שדות מחושבים, כמו בטבלאות ציר רגילות באקסל וגוגל שיטס.
כמו כן נלמד איך "לפתוח" את הציר בחלוקה למספר טורים.
לפנינו טבלת נתונים (בדומה למאמרים הקודמים) נרצה לפי מחלקה לסכום הוצאות והכנסות זה בצד זה.
נכתוב את הנוסחה:
=QUERY(A1:F73,"SELECT B, SUM(C), SUM(D) GROUP BY B")
בחלק הראשון כמו תמיד, נבחר את טווח הטבלה (טקסט בכתום) ובחלק השני (טקסט בירוק) נבחר לראות את שמות המחלקות בטור B סכום ההכנסות בטור C וסכום ההוצאות בטור D.
נקבל טבלת ציר עם טור הוצאות והכנסות זה בצד זה.
עכשיו אנחנו מעוניינים ליצור שדה מחושב-
לבחור את אותם טורים ועליהם להוסיף את הרווח של אותה מחלקה,
נכתוב את הנוסחה:
=QUERY(A1:F73,"SELECT B, SUM(C), SUM(D), SUM(C)-SUM(D) GROUP BY B")
כתבנו את אותה הפונקציה כמו בדוגמה מעל אבל הוספנו טור נוסף (בטקסט באדום) שמחשב את סכום ההכנסות בטור C פחות סכום ההוצאות בטור D
קיבלנו טבלת ציר עם שדה מחושב בטור L.
ניתן לעשות את אותו הדבר עם כל פעולת חשבון אחרת,
לדוגמה נחשב את אחוז הרווח הנקי ביחס להכנסה ברוטו.
נכתוב את הנוסחה:
=QUERY(A1:F73,"SELECT B, SUM(C), SUM(D), (SUM(C)-SUM(D))/SUM(C) GROUP BY B")
באדום כתבנו, סכום ההכנסות פחות ההוצאות (רווח נטו), לחלק בסכום ההכנסות ברוטו.
עיצבנו את התא כאחוזים וקיבלנו אחוז הרווח הנקי ביחס ביחס להכנסה של אותה מחלקה.
פותחים ציר...
נרצה כעת לקבל סכום ההכנסה וסכום הההוצאה לפי מחלקה,
אבל נוסיף מימד נוסף בחלוקה לפי סוג הוצאה/הכנסה בטור E.
נכתוב את הנוסחה:
=QUERY(A1:F73,"SELECT B, SUM(C), SUM(D) GROUP BY B PIVOT E")
בדיוק כמו בדוגמאות הקודמות נסכום את טור C וטור D, אבל הפעם נוסיף את המימד הנוסף, פתיחת ציר-PIVOT לפי טור E, שהוא סוג ההוצאה/הכנסה.
קיבלנו 4 טורי סיכום. הציר (PIVOT) עשה סכום הכנסות וסכום הוצאות פעמיים, פעם אחת לפי סוג 'Service' ופעם אחת לפי סוג 'Product'.
בדוגמה האחרונה נרצה לספור כמה פעמים מופיעה כל מדינה בטור F לפי סוג הוצאה/הכנסה עם פתיחת ציר לפי D.
נכתוב את הנוסחה:
=QUERY(A1:F73,"SELECT F, COUNT(F) GROUP BY F PIVOT E")
ז"א, בחר להציג את טור שמות המדינות, ספור את המדינות קבץ אותן ופתח ציר לפי סוג ההוצאה/הכנסה.
בהצלחה!
Comentarios