ספירת ערכים מספריים ייחודיים על סמך קריטריונים ב-Excel

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

• ספירת ערכים מספריים ייחודיים על סמך קריטריונים ב-Excel 2019, 2016 וגרסאות קודמות
• ספירת ערכים מספריים ייחודיים על סמך קריטריונים ב-Excel 365

ספירת ערכים מספריים ייחודיים על סמך קריטריונים ב-Excel 2019, 2016 וגרסאות קודמות

בגרסאות Excel 2019 וקודמות יותר, ניתן לשלב את הפונקציות SUM, FREQUENCY ו-IF כדי ליצור נוסחה לספירת ערכים ייחודיים על סמך קריטריונים, התחביר הכללי הוא:

• criteria_range: טווח התאים שמתאימים לקריטריון שציינתם;
• criteria: התנאי עליו תרצו לספור ערכים ייחודיים;
• range: טווח התאים עם הערכים הייחודיים שיש לספור.

אנא החלו את הנוסחה הבאה לתוך תא ריק, ולחצו יחדיו על מקשי Ctrl + Shift + Enter כדי לקבל את התוצאה הנכונה, ראו צילום מסך:

הסבר לנוסחה:

=SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))

• IF(A2:A12=E2,C2:C12): פונקציית IF מחזירה את הערך בעמודה C אם המוצר בעמודה A הוא "חולצה", והתוצאה היא מערך כזה: {FALSE;300;500;FALSE;400;FALSE;300;FALSE;FALSE;FALSE;350}.
• FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)= FREQUENCY({FALSE;300;500;FALSE;400;FALSE;300;FALSE;FALSE;FALSE;350},{200;300;500;350;400;450;300;550;200;260;350}): הפונקציה FREQUENCY משמשת לספירת כל אחד מהערכים המספריים ברשימת המערך, ומחזירה את התוצאה כך: {0;2;1;1;1;0;0;0;0;0;0;0}.
• --(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0)=--({0;2;1;1;1;0;0;0;0;0;0;0}>0): בודקים אם כל ערך במערך גדול מ-0, ומקבלים את התוצאה כך: {FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}. לאחר מכן, הסימן השלילי הכפול ממיר את TRUE ו-FALSE ל-1 ו-0, ומחזיר מערך כזה: {0;1;1;1;1;0;0;0;0;0;0;0}.
• SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))=SUM({0;1;1;1;1;0;0;0;0;0;0;0}): לבסוף, השתמשו בפונקציית SUM כדי לחבר את הערכים הללו, ולקבל את הסכום הכולל: 4.

טיפים:

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

ספירת ערכים מספריים ייחודיים על סמך קריטריונים ב-Excel 365

ב-Excel 365, שילוב הפונקציות ROWS, UNIQUE ו-FILTER יכול לעזור בספירת ערכים מספריים ייחודיים על סמך קריטריונים, התחביר הכללי הוא:

• range: טווח התאים עם הערכים הייחודיים שיש לספור.
• criteria_range: טווח התאים שמתאימים לקריטריון שציינתם;
• criteria: התנאי עליו תרצו לספור ערכים ייחודיים;

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

הסבר לנוסחה:

=ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))

• A2:A12=E2: הביטוי הזה בודק אם הערך בתא E2 קיים בטווח A2:A12, ומקבל את התוצאה הזאת: {FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}.
• FILTER(C2:C12,A2:A12=E2): הפונקציה FILTER משמשת לספירת כל אחד מהערכים המספריים ברשימת המערך, ומחזירה את התוצאה כך: {0;2;1;1;1;0;0;0;0;0;0;0}.
• UNIQUE(FILTER(C2:C12,A2:A12=E2))=UNIQUE({300;500;400;300;350}): כאן, הפונקציה UNIQUE משמשת לחלץ ערכים ייחודיים מרשימת המערך כדי לקבל את התוצאה הזאת: {300;500;400;350}.
• ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))=ROWS({300;500;400;350}): הפונקציה ROWS מחזירה את מספר השורות על סמך טווח תאים או מערך, אז התוצאה היא: 4.

טיפים:

1. אם הערך המתאים לא קיים בטווח הנתונים, תקבלו ערך שגיאה, כדי להחליף את ערך השגיאה ב-0, אנא השתמשו בנוסחה הבאה:

2. כדי לספור את הערכים הייחודיים על סמך יותר מתנאי אחד, פשוט עליכם להוסיף תנאים נוספים לנוסחה עם תו * כך:

הפונקציות הקשורות:

מאמרים נוספים: