ספירת כל ההתאמות / כפילויות בין שתי עמודות ב-Excel

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

• ספירת כל ההתאמות בין שתי עמודות עם פונקציות SUMPRODUCT ו-COUNTIF
• ספירת כל ההתאמות בין שתי עמודות עם פונקציות COUNT ו-MATCH
• ספירת כל ההתאמות בין שתי עמודות עם פונקציות SUMPRODUCT, ISNUMBER ו-MATCH

ספירת כל ההתאמות בין שתי עמודות עם פונקציות SUMPRODUCT ו-COUNTIF

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

• range1, range2: שתי הטווחים המכילים את הנתונים שברצונך לספור את כל ההתאמות שלהם.

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

הסבר לנוסחה:

=SUMPRODUCT(COUNTIF(A2:A12,C2:C12))

• COUNTIF(A2:A12,C2:C12): פונקציית COUNTIF הזו בודקת אם כל שם מהעמודה C קיים בעמודה A. אם השם קיים, מוצג המספר 1, אחרת מוצג המספר 0. הפונקציה תחזיר את התוצאה כך: {1;1;0;0;0;1;0;0;1;0;1}.
• SUMPRODUCT(COUNTIF(A2:A12,C2:C12))=SUMPRODUCT({1;1;0;0;0;1;0;0;1;0;1}): פונקציית SUMPRODUCT סוכמת את כל הפריטים במערך זה ומקבלת את התוצאה: 5.

ספירת כל ההתאמות בין שתי עמודות עם פונקציות COUNT ו-MATCH

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

• range1, range2: שתי הטווחים המכילים את הנתונים שברצונך לספור את כל ההתאמות שלהם.

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

הסבר לנוסחה:

=COUNT(MATCH(A2:A12,C2:C12,0))

• MATCH(A2:A12,C2:C12,0): פונקציית MATCH הזו תחפש את השמות מהעמודה A בעמודה C, ותחזיר את המיקום של כל ערך תואם. אם ערך לא נמצא, יוצג ערך שגיאה. אז, תקבל את רשימת המערך כך: {11;2;#N/A;#N/A;#N/A;6;1;#N/A;#N/A;#N/A;9}.
• COUNT(MATCH(A2:A12,C2:C12,0))= COUNT({11;2;#N/A;#N/A;#N/A;6;1;#N/A;#N/A;#N/A;9}): פונקציית COUNT תספור את המספרים ברשימת המערך כדי לקבל את התוצאה: 5.

ספירת כל ההתאמות בין שתי עמודות עם פונקציות SUMPRODUCT, ISNUMBER ו-MATCH

ב-Excel, אתה יכול לנסות למצוא את ההתאמות בשתי העמודות ולספור אותן באמצעות פונקציות SUMPRODUCT, ISNUMBER ו-MATCH, התחביר הכללי הוא:

• range1, range2: שתי הטווחים המכילים את הנתונים שברצונך לספור את כל ההתאמות שלהם.

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

הסבר לנוסחה:

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A12,C2:C12,0))))

• MATCH(A2:A12,C2:C12,0): פונקציית MATCH הזו תחפש את השמות מהעמודה A בעמודה C, ותחזיר את המיקום של כל ערך תואם. אם הערך לא נמצא, יוצג ערך שגיאה. אז, תקבל את רשימת המערך כך: {11;2;#N/A;#N/A;#N/A;6;1;#N/A;#N/A;#N/A;9}.
• ISNUMBER(MATCH(A2:A12,C2:C12,0))= ISNUMBER({11;2;#N/A;#N/A;#N/A;6;1;#N/A;#N/A;#N/A;9}): כאן, פונקציית ISNUMBER ממירה את המספרים ל-TRUE ואת הערכים האחרים ל-FALSE במערך. אז, תקבל מערך כזה: {TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE}.
• --(ISNUMBER(MATCH(A2:A12,C2:C12,0)))=--({TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE}): --סימן השלילה הכפול הזה משמש להמרת ערך TRUE ל-1 וערך FALSE ל-0 ומחזיר את התוצאה כך: {1;1;0;0;0;1;1;0;0;0;1}.
• SUMPRODUCT(--(ISNUMBER(MATCH(A2:A12,C2:C12,0))))=SUMPRODUCT({1;1;0;0;0;1;1;0;0;0;1}): לבסוף, פונקציית SUMPRODUCT תסכום את כל הפריטים במערך זה ותקבל את התוצאה: 5.

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

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