ספירת מילים מסוימות בטווח ב-Excel

המאמר הזה מסביר נוסחה לספירת מספר הפעמים שמילה מסוימת מופיעה בטווח תאים ב-Excel.

כיצד לספור מילים מסוימות בטווח תאים ב-Excel?

נוסחה כללית

=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))

ארגומנטים

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

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

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

טיפים: אתם יכולים להעתיק ולהדביק את הנוסחה ישירות לתא הנבחר.

=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")))/LEN("tool"))

הערות:

• 1. אנא שנה את הטווח B5:B8 ואת המילה "tool" לפי הצרכים שלכם;
• 2. אם המילה לא נמצאת, תוחזר הערך 0;
• 3. הנוסחה רגישה לאותיות גדולות וקטנות;
• 4. אם ברצונכם לספור מילה מסוימת בטווח תאים תוך התעלמות מהגבוהה או הקטנה של האותיות, השתמשו בפונקציה UPPER בתוך הפונקציה SUBSTITUTE כפי שמוצג בנוסחה הבאה:
• =SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(UPPER(B5:B8),UPPER("tool"),"")))/LEN("tool"))
• 

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

=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")))/LEN("tool"))

• 1. LEN(B5:B8): הפונקציה LEN מחשבת את אורך המחרוזת הכוללת עבור כל תא בטווח B5:B8, והיא תחזיר מערך מספרים {47;23;23;15};
• 2. LEN(SUBSTITUTE(B5:B8,"tool","")): כאן הפונקציה SUBSTITUTE מסירה את המילה המסוימת "tool" מכל תא בטווח B5:B8, ולאחר מכן הפונקציה LEN מחשבת את האורך לכל תא בטווח B5:B8 ללא המילה המסוימת "tool". היא מחזירה את המערך {35;19;19;15};
• 3. (LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")): כעת החלק הזה של הנוסחה הופך ל-{47;23;23;15}-{35;19;19;15}. לאחר החיסור, התוצאה היא {12;4;4;0};
• 4. LEN("tool"): הפונקציה LEN כאן מחשבת את האורך של "tool", ומחזירה את התוצאה 4;
• 5. LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")))/LEN("tool"): כפי שהוזכר לעיל, החלק הזה יוצג כ-{12;4;4;0}/4. חלקו את 4 מכל מספר במערך והתקבל התוצאה {3;1;1;0};
• 6. SUMPRODUCT({3;1;1;0}): הפונקציה SUMPRODUCT מסכמת את המספרים במערך ומחזירה את הספירה הכוללת של "tool" בטווח B5:B8.

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

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