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

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

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

נניח שיש 4 גליונות עבודה (Sheet2, Sheet3, Sheet4 ו-Sheet5) בחוברת העבודה שלך, ואתה רוצה לספור כמה פעמים מופיע הטקסט "מחשב נייד" על פני 4 גליונות אלו, תוכל לעשות זאת לפי ההנחיות הבאות.

נוסחה כללית

=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&range1&”‘!”&range2),criteria))

ארגומנטים

איך להשתמש בנוסחה זו?

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

2. בחר תא ריק להצגת התוצאה.

3. הזן אחת מהנוסחאות הבאות לתוך התא והקש על מקש Enter כדי לקבל את התוצאה.

אם כבר הזנת את הטקסט הספציפי לתא מסוים (כמו B3), תוכל להתייחס לתא זה בנוסחה באופן הבא:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),B3))

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

=SUMPRODUCT(COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),"Laptop"))

הערה: טווח A1:Z10000 הוא טווח שרירותי, ניתן לשנות אותו בהתאם לנתונים שלך.

איך עובדת הנוסחה הזו?

=SUMPRODUCT(COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),B3))

• "'"&B7:B10&"'!A1:Z10000": כאן כל שם גיליון במערך מחובר באמצעות שרשור לטווח A1:Z10000, ותקבל מערך כמו {"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000";"'Sheet4'!A1:Z10000";"'Sheet5'!A1:Z10000"}.
• INDIRECT({"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000";"'Sheet4'!A1:Z10000";"'Sheet5'!A1:Z10000"}): הפונקציה INDIRECT מתייחסת לטווחים בארבעת הגליונות.
• COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),B3): הפונקציה COUNTIF סופרת את מספר הפעמים שהערך הספציפי מופיע בכל גיליון ומחזירה מערך {13;13;13;13}. כלומר, הטקסט הספציפי מופיע 13 פעמים בכל גיליון.
• SUMPRODUCT({13;13;13;13}): הפונקציה SUMPRODUCT מסכמת את כל המספרים במערך ומחזירה את התוצאה הסופית כ-52.

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

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