ספירת ערכים מובחנים בטבלת הצירים של Excel (מדריך פשוט שלב אחר שלב)

טבלאות הציר של Excel מדהימות (אני יודע שאני מזכיר זאת בכל פעם שאני כותב על טבלאות ציר, אבל זה נכון).

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

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

ודבר כזה הוא לספור ערכים נפרדים בטבלת צירים.

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

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

ספירה מובחנת מול ספירה ייחודית

אמנם אלה עשויים להיראות אותו דבר, זה לא.

להלן דוגמה שבה קיים מערך שמות ורשמתי שמות ייחודיים ומובחנים בנפרד.

ערכים/שמות ייחודיים הם אלה המתרחשים פעם אחת בלבד. המשמעות היא שכל השמות שחוזרים על עצמם ויש להם כפילויות אינם ייחודיים. שמות ייחודיים מפורטים בעמודה C במערך הנתונים שלמעלה

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

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

ספירת ערכים מובחנים בטבלת הצירים של Excel

נניח שיש לך את נתוני המכירות כפי שמוצג להלן:

לחץ כאן להורדת קובץ הדוגמה והמשך

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

1. כמה נציגי מכירות יש בכל אזור (וזה רק הספירה המובהקת של נציגי המכירות בכל אזור)?
2. כמה נציגי מכירות מכרו את המדפסת בשנים 2020-2022?

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

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

שתי השיטות הבאות מכוסות במדריך זה:

• הוספת עמודת עוזר במערך הנתונים המקורי לספירת ערכים ייחודיים (פועלת בכל הגרסאות).
• הוספת הנתונים למודל נתונים ושימוש באפשרות ספירה מובחנת (זמין ב- Excel 2013 ובגרסאות לאחר מכן).

יש שיטה שלישית שמראה רוג'ר במאמר זה (שהוא מכנה את שיטת Pivot Pivot Table).

בואו נתחיל!

הוספת טור עוזר במערך הנתונים

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

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

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

תן לי להראות לך תחילה כיצד להוסיף טור עוזר ולקבל ספירה ברורה.

נניח שיש לי את מערך הנתונים כפי שמוצג להלן:

הוסף את הנוסחה הבאה בעמודה F והחל אותה על כל התאים המכילים נתונים בעמודות הסמוכות.

= IF (COUNTIFS ($ C $ 2: C2, C2, $ B $ 2: B2, B2)> 1,0,1)

הנוסחה שלעיל משתמשת בפונקציה COUNTIFS כדי לספור את מספר הפעמים שמופיע שם באזור הנתון. כמו כן, שים לב שטווח הקריטריונים הוא $ C $ 2: C2 ו- $ B $ 2: B2. המשמעות היא שהוא ממשיך להתרחב ככל שאתה יורד בעמודה.

לדוגמה, בתא E2, טווחי הקריטריונים הם $ C $ 2: C2 ו- $ B $ 2: B2 ובתא E3 טווחים אלה מתרחבים ל $ C $ 2: C3 ו- $ B $ 2: B3.

זה מבטיח שהפונקציה COUNTIFS סופרת את המופע הראשון של השם כ- 1, את המופע השני של השם כ- 2 וכן הלאה.

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

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

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

להלן השלבים לביצוע פעולה זו:

1. בחר תא כלשהו במערך הנתונים.
2. לחץ על הכרטיסייה הוספה.
3. לחץ על טבלת צירים (או השתמש בקיצור המקשים - ALT + N + V)
4. בתיבת הדו -שיח צור טבלת צירים, ודא שהטבלה/הטווח נכונים (וכוללים את עמודת העזר) ו- 'גליון עבודה חדש' שנבחרו.
5. לחץ על אישור.

השלבים שלעיל יכניסו גיליון חדש ובו טבלת הצירים.

גרור את השדה 'אזור' באזור השורות ושדה 'ספירת D' באזור הערכים.

תקבל טבלת ציר כפי שמוצג להלן:

כעת תוכל לשנות את כותרת העמודה מ'סכום מספר D 'ל'נציג מכירות'.

חסרונות של שימוש בעמוד עוזר:

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

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

הוסף נתונים למודל הנתונים וסכם באמצעות ספירה מובהקת

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

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

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

להלן השלבים לקבלת ערך ספירה מובהק בטבלת הצירים:

1. בחר תא כלשהו במערך הנתונים.
2. לחץ על הכרטיסייה הוספה.
3. לחץ על טבלת צירים (או השתמש בקיצור המקשים - ALT + N + V)
4. בתיבת הדו -שיח צור טבלת צירים, ודא שהטבלה/הטווח נכונים וגיליון עבודה חדש נבחר.
5. סמן את התיבה שאומרת - "הוסף נתונים אלה למודל הנתונים"
6. לחץ על אישור.

השלבים שלעיל יכניסו גיליון חדש ובו טבלת הצירים החדשה.

גרור את האזור באזור השורות ואת נציג המכירות באזור הערכים. תקבל טבלת ציר כפי שמוצג להלן:

טבלת הצירים לעיל נותנת את המספר הכולל של נציג המכירות בכל אזור (ולא את הספירה המובהקת).

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

1. לחץ לחיצה ימנית על כל תא בעמודה 'ספירת נציג מכירות'.
2. לחץ על הגדרות שדה ערך
3. בתיבת הדו -שיח הגדרות שדה ערך, בחר 'ספירה מובחנת' כסוג החישוב (ייתכן שיהיה עליך לגלול למטה מהרשימה כדי למצוא אותו).
4. לחץ על אישור.

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

כמה דברים שאתה יודע כשאתה מוסיף את הנתונים שלך למודל הנתונים:

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

לחץ כאן להורדת קובץ הדוגמה

מה אם אתה רוצה לספור ערכים ייחודיים (ולא ערכים מובחנים)?

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

זכור - ערכים ייחודיים וערכים ייחודיים אינם זהים. לחץ כאן כדי לדעת את ההבדל.

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

במקרים כאלה, עליך ליצור אחת מתוך יותר מעמודות עוזר אחד.

במקרה זה, הנוסחה שלהלן עושה את העבודה:

= IF (IF (COUNTIFS ($ C $ 2: $ C $ 1001, C2, $ B $ 2: $ B $ 1001, B2)/COUNTIF ($ C $ 2: $ C $ 1001, C2) 1,0,1), 0)

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

במקרה שהשם מופיע ביותר מאזור אחד, הוא מחזיר 0 אחרת הוא מחזיר אחד.

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

זה אולי נראה קצת מורכב, אבל זה שוב תלוי במה שאתה מנסה להשיג.

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

לחץ כאן להורדת קובץ הדוגמה

אולי תאהב גם את שיעורי טבלת הצירים הבאים:

• כיצד לסנן נתונים בטבלת צירים ב- Excel
• כיצד לקבץ תאריכים בטבלאות ציר ב- Excel
• כיצד לקבץ מספרים בטבלת צירים ב- Excel
• כיצד ליישם עיצוב מותנה בטבלת צירים ב- Excel
• פורסים בטבלת ציר Excel
• כיצד לרענן את טבלת הצירים ב- Excel
• מחק טבלת ציר ב- Excel