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

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

כיצד לספור ערכים ייחודיים עם קריטריונים ב-Excel?

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

נוסחאות כלליות

{=SUM(--(FREQUENCY(IF(range=criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.firstcell)+1)>0))}

ארגומנטים

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

כיצד להשתמש בנוסחאות אלו?

1. בחר תא ריק כדי להציב בו את התוצאה.

2. הקלד את הנוסחה הבאה לתוכו ולאחר מכן לחץ בו זמנית על המקשים Ctrl + Shift + Enter כדי לקבל את התוצאה.

=SUM(--(FREQUENCY(IF(E3:E16=H3,MATCH(D3:D16,D3:D16,0)),ROW(D3:D16)-ROW(D3)+1)>0))

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

כיצד עובדת נוסחה זו?

{=SUM(--(FREQUENCY(IF(E3:E16=H3,MATCH(D3:D16,D3:D16,0)),ROW(D3:D16)-ROW(D3)+1)>0))}

• IF(E3:E16=H3,MATCH(D3:D16,D3:D16,0)):

• IF({TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;},{1;2;3;2;1;1;3;2;1;1;1;2;3;2}): כעת, עבור כל ערך TRUE במערך 1, נקבל את המיקום המתאים במערך 2, ועבור FALSE, נקבל FALSE. כאן תקבל מערך חדש כזה {1;FALSE;FALSE;2;FALSE;FALSE;3;FALSE;FALSE;1;FALSE;FALSE;3;FALSE}.
• ROW(D3:D16)-ROW(D3)+1: כאן הפונקציה ROW מחזירה את מספר השורה של הפניה D3:D16 ו-D3, ותקבל {3;4;5;6;7;8;9;10;11;12;13;14;15;16}-{3}+1.
• כל מספר במערך מחסיר את המספר 3 ואז מוסיף 1 ולבסוף מחזיר {1;2;3;4;5;6;7;8;9;10;11;12;13;14}.
• FREQUENCY({1;FALSE;FALSE;2;FALSE;FALSE;3;FALSE;FALSE;1;FALSE;FALSE;3;FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14}): כאן הפונקציה FREQUENCY מחזירה את התדירות של כל מספר במערך הנתון: {2;1;2;0;0;0;0;0;0;0;0;0;0;0}.
• =SUM(--({2;1;2;0;0;0;0;0;0;0;0;0;0;0}>0)):

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

נוסחאות קשורות