טבלת ציר עם טקסט בערכים

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

בואו ננסה לעקוף את המגבלה הזו ולהמציא "זוג קביים" במצב דומה.

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

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

שיטה 1. הכי קל - השתמש ב-Power Query

Power Query הוא כלי רב עוצמה לטעינה והמרת נתונים ב- Excel. התוסף הזה מובנה ב-Excel כברירת מחדל מאז 2016. אם יש לך Excel 2010 או 2013, תוכל להוריד ולהתקין אותו בנפרד (חינם לחלוטין).

את כל התהליך, למען הבהירות, ניתחתי צעד אחר צעד בסרטון הבא:

אם לא ניתן להשתמש ב- Power Query, אז אתה יכול ללכת בדרכים אחרות - דרך טבלת ציר או נוסחאות. 

שיטה 2. סיכום עזר

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

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

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

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

בנוסף, אתה יכול לבטל סכומי ביניים וסיכומי ביניים בכרטיסייה קונסטרוקטור - סיכומים כלליים и סיכומי ביניים (עיצוב - סכומים גדולים, סיכומי משנה) ובאותו מקום מעבירים את הסיכום לפריסת טבלה נוחה יותר עם הכפתור דווח על דגם (פריסת דוח).

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

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

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

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

אספקה[[מְכוֹלָה]:[מְכוֹלָה]]

... נדרש רק כדי להפנות את העמודה מכולה היה מוחלט (כמו הפניה עם סימני $ לטבלאות "לא חכמות" רגילות) ולא החליק לעמודות שכנות בעת העתקת הנוסחה שלנו ימינה.

בעתיד, בעת שינוי הנתונים בטבלת המקור משלוחים, עלינו לזכור לעדכן את סיכום העזר שלנו במספרי שורות על ידי לחיצה ימנית עליו ובחירה בפקודה עדכן ושמור (לְרַעֲנֵן).

שיטה 3. נוסחאות

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

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

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

אם מראה הסיכום אינו מתאים במיוחד לדוח שלך, אז אתה יכול לחלץ ממנו מספרי שורות לטבלה הסופית לא ישירות, כפי שעשינו, אלא באמצעות הפונקציה GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). כיצד לעשות זאת ניתן למצוא כאן.

• כיצד ליצור דוח באמצעות טבלת ציר
• כיצד להגדיר חישובים בטבלאות ציר
• ספירה סלקטיבית עם SUMIFS, COUNTIFS וכו'.