טבלת ציר, בלי להשתמש בטבלת ציר (רק באקסל 365…)

פונקציות המערך החדשות של אקסל 365 הביאו הרבה חידושים ודרכי עבודה מתוחכמות.
אחד הדברים הנחמדים שאפשר לעשות איתן זה ליצור טבלת ציר, בלי להשתמש בטבלת ציר.
ולמה לנו לעשות את זה?
כי למרות אהבתי הגדולה לטבלאות ציר, שהן (בלי להגזים) קסם טהור,
כדי לעדכן  טבלת ציר צריך ללחוץ על ריענון, בעוד שפונקציה מתעדכנת באופן אוטומטי עם כל שינוי בנתונים.
חוץ מזה, זו הזדמנות נפלאה להראות דרך פרקטית לשימוש בפונקציות המערך הדינאמיות של 365: UNIQUE ו SORT
ולהראות גם איך פונקציות ותיקות, כמו SUMIFS ו TRANSPOSE משתלבות בחגיגה
אז בואו נתחיל…

ראשית, מה יש לנו?

טבלת מכירות חכמה בשם Sales, שמכילה את תאריך המכירה, המוצר ומחיר המכירה:

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

אז יאללה, לעבודה…

בשלב הראשון יצרתי בתא E6 רשימת תאריכים ייחודית באמצעות הפונקציה UNIQUE:

=UNIQUE(Sales[תאריך])

וזו התוצאה:

(לא מכירים את הפונקציה UNIQUE? קראו את המאמר הזה)

בשלב השני חישבתי באמצעות הפונקציה SUMIFS את הסכום עבור כל תאריך:

=SUMIFS(Sales[מחיר מכירה],Sales[תאריך],E6#)

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

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

ועכשיו, בואו נגדיל את הטווח:

הוספתי אל טבלת המכירות שלושה מוצרים שנמכרו ב 15/1/2020

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

טוב, אבל זו טבלת ציר פשוטה, ומה אם ארצה להוסיף נתונים גם לעמודות?

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

=UNIQUE(Sales[מוצר])

וזו התוצאה שהתקבלה:

ועכשיו, כדי להפוך את הרשימה לרשימה אופקית, הקפתי אותה בפונקציה TRANSPOSE

=TRANSPOSE(UNIQUE(Sales[מוצר]))

וזו התוצאה שהתקבלה:

אז עכשיו כל מה שנשאר זה רק למלא את הערכים באמצעות הפונקציה SUMIFS,

=SUMIFS(Sales[מחיר מכירה],Sales[תאריך],H6#,Sales[מוצר],I5#)

השינוי הגדול בפונקציה הוא שבמקום להפנות לתא ספציפי כקריטריון, אני מפנה לטבלת ערכים שנוצרה על ידי שימוש בסולמית,
ומנוע החישובים החדש של 365 יודע להתמודד עם הפניה לטבלה במקום לתא בודד.
כלומר – אני סוכמת את מחיר המכירה לכל התאריכים שנוצרו כתוצאה מהפעלת הפונקציה UNIQUE על טווח התאריכים בטבלה,
ומקבלת את הסכום עבור כל אחת מתוצאות הפונקציה, בלי שאצטרך לגרור את הנוסחה עבור כל תאריך.
בחלקה השני שלי הפונקציה אני עושה את אותה המניפולציה עבור המוצרים,
כאשר הקריטריון הוא שהם שווים לטווח שחושב בתא I5, והתפרס הצידה לקבלת רשימת המוצרים הייחודית.

ועוד מילה לסיכום

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

=SORT(UNIQUE(Sales[תאריך]))

עוד על הפונקציה SORT במאמר הזה

מעוניינים לתרגל עם הקובץ? תוכלו להוריד אותו מכאן:

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