רוצה לקבל את ספירת המילים ב- Excel? תאמין או לא, לאקסל אין מונה מילים מובנה.
אבל אל תדאג.
חבורה מגניבה של פונקציות אקסל (או קצת VBA אם אתה מרגיש מפואר) יכולה לעשות זאת עבורך בקלות.
במדריך זה אראה מספר דרכים לספור מילים ב- Excel באמצעות נוסחאות פשוטות. ובסוף, יכסה גם טכניקה ליצירת נוסחה מותאמת אישית באמצעות VBA שתיתן לך במהירות את ספירת המילים של כל טקסט בכל תא.
נוסחה לקבלת ספירת מילים ב- Excel
לפני שאני נותן לך את הנוסחה המדויקת, בואו נכסה במהירות את ההיגיון כדי לקבל את ספירת המילים.
נניח שיש לי משפט כפי שמוצג להלן שאני רוצה לקבל את ספירת המילים.
בעוד ש- Excel אינו יכול לספור את מספר המילים, הוא יכול לספור את מספר הרווחים במשפט.
אז כדי לקבל את ספירת המילים, נוכל לספור רווחים אלה במקום מילים ולהוסיף 1 לסכום הכולל (מכיוון שמספר הרווח יהיה אחד פחות ממספר המילים).
עכשיו יכולות להיות שתי אפשרויות:
- יש רווח אחד בין כל מילה
- ישנם מרווחים מרובים בין מילים.
אז בואו נראה איך לספור את מספר המילים הכולל בכל מקרה ומקרה.
דוגמא 1 - כשיש רווח אחד בין מילים
נניח שיש לי את הטקסט הבא בתא A1: שחרר את החתול מהתיק
כדי לספור את מספר המילים, הנה הנוסחה שבה הייתי משתמש:
= LEN (A1) -LEN (SUBSTITUTE (A1, "", ""))+1
זה יחזיר '7' כתוצאה מכך.
כך פועלת נוסחה זו:
- LEN (A1) - חלק זה של הנוסחה מחזיר 26, שהוא המספר הכולל של התווים בטקסט בתא A1. הוא כולל את תווי הטקסט ואת תווי החלל.
- SUBSTITUTE (A1, "", "") - חלק זה של הנוסחה מסיר את כל הרווחים מהטקסט. אז התוצאה, במקרה זה, תהיה תן את התיק של התיק.
- LEN (תחליף (A1, "",“”) - חלק זה של הנוסחה סופר את מספר התווים הכולל בטקסט שאין בו רווחים. אז התוצאה של זה תהיה 20.
- LEN (A1) -LEN (תחליף (A1, "",“”)) - הדבר יגרע את אורך הטקסט ללא רווחים מאורך הטקסט עם רווחים. בדוגמה לעיל, זה יהיה 26-20 שזה 6.
- = LEN (A1) -LEN (תחליף (A1, "",“”))+1 - אנו מוסיפים 1 לתוצאה הכוללת מכיוון שמספר הרווחים הכולל הוא אחד פחות ממספר המילים הכולל. לדוגמה, יש רווח אחד בשתי מילים ושני רווחים בשלוש מילים.
עכשיו, זה עובד היטב אם יש לך רק תו רווח אחד בין מילים. אבל זה לא יעבוד אם יש לך יותר מרווח אחד בין המילים.
במקרה זה, השתמש בנוסחה בדוגמה הבאה.
דוגמה 2: כשיש מרווחים מרובים בין מילים
נניח שיש לך את הטקסט הבא: שחרר את החתול מהתיק
במקרה זה, ישנם מספר תווי רווח בין מילים.
כדי לקבל את ספירת המילים, תחילה עלינו להסיר את כל הרווחים הנוספים (כך שיש רק תו רווח אחד בין שתי מילים) ולאחר מכן לספור את מספר הרווחים הכולל.
להלן הנוסחה שתיתן לנו את מספר המילים הנכון:
= LEN (TRIM (A1))-LEN (SUBSTITUTE (A1, "", ""))+1
זו נוסחה דומה המשמשת בדוגמה לעיל, עם שינוי קל - השתמשנו גם בפונקציית TRIM כאן.
פונקציית TRIM של Excel מסירה כל רווח מוביל, נגרר ותוספת (למעט רווחים בודדים בין מילים).
שאר הנוסחה פועלת זהה (כפי שהוסבר בדוגמה 1).
הערה: אם אין רווחים בין מילים, היא נחשבת כמילה אחת.
שימוש בפונקציה מותאמת אישית של VBA לספירת מילים ב- Excel
למרות שהנוסחאות לעיל עובדות מצוין, אם יש לך צורך לחשב את ספירת המילים לעתים קרובות, תוכל להשתמש ב- VBA כדי ליצור פונקציה מותאמת אישית (נקראת גם פונקציה מוגדרת משתמש).
היתרון בשימוש בפונקציה מותאמת אישית הוא שאתה יכול ליצור אותה פעם אחת ולאחר מכן להשתמש בה כמו כל פונקציית Excel רגילה אחרת. אז במקום ליצור נוסחה מורכבת וארוכה כפי שעשינו בשתי הדוגמאות למעלה, יש לך נוסחה פשוטה שלוקחת את הפניה לתא ונותנת לך מיידית את ספירת המילים.
להלן הקוד שיצור פונקציה מותאמת אישית זו בכדי לקבל את ספירת המילים ב- Excel.
פונקציה WordCount (CellRef כטווח) Dim TextStrng כתוצאה מחרוזת עמומה () כתוצאה מחרוזת = פיצול (גליון WorksFunction.Trim (CellRef.Text), "") WordCount = UBound (תוצאה ()) + פונקציית סיום 1
לאחר יצירתו, תוכל להשתמש בפונקציית WordCount בדיוק כמו כל פונקציית Excel רגילה אחרת.
בקוד לעיל עבור הפונקציה המותאמת אישית, השתמשתי בפונקציית TRIM בגיליון העבודה כדי להסיר כל רווח מוביל, נגרר וכפול בין המילים. זה מבטיח שכל שלושת התאים נותנות את אותה התוצאה, מכיוון שרק המילים נספרות ולא הרווחים הכפולים.
כיצד פועלת נוסחה זו:
קוד VBA לעיל משתמש תחילה בפונקציית TRIM כדי להסיר את כל הרווחים המובילים, הנגררים והכפולים ממחרוזת הטקסט בתא המפנה.
ברגע שיש לו את המחרוזת המנקה, הוא משתמש בפונקציית SPLIT ב- VBA כדי לפצל את מחרוזת הטקסט המבוססת על המפריד, שציינו שהוא תו הרווח. כך שכל מילה מופרדת כפי שהיא מאוחסנת כפריט נפרד במשתנה התוצאה.
לאחר מכן אנו משתמשים בפונקציה UBOUND כדי לספור את המספר הכולל של הפריטים שנשמרו במשתני התוצאה. מכיוון של- VBA יש בסיס של 0, עלינו להוסיף 1 כדי לקבל את מספר המילים הכולל.
המשמעות היא שתוצאה (0) שומרת את המילה הראשונה, תוצאה (1) שומרת את המילה השנייה וכן הלאה. מכיוון שספירה זו מתחילה מ -0, עלינו להוסיף 1 כדי לקבל את ספירת המילים האמיתית.
היכן לשים את הקוד הזה?
בעת יצירת פונקציה מותאמת אישית, עליך להכניס את הקוד לעורך VB של חוברת העבודה (שהיא הקצה האחורי של חוברת העבודה שבה תוכל לכתוב קוד כדי להפוך משימות לאוטומטיות וליצור פונקציות מותאמות אישית).
להלן השלבים להכנסת הקוד לפונקציה 'GetNumeric' בחוברת העבודה.
- עבור לכרטיסייה מפתחים.
- לחץ על האפשרות Visual Basic. פעולה זו תפתח את עורך ה- VB בקצה האחורי.
- בחלונית סייר הפרויקט בעורך VB, לחץ באמצעות לחצן העכבר הימני על כל אובייקט עבור חוברת העבודה שבה ברצונך להכניס את הקוד. אם אינך רואה את סייר הפרוייקטים עבור לכרטיסייה תצוגה ולחץ על סייר הפרוייקטים.
- עבור אל הוספה ולחץ על מודול. זה יכניס אובייקט מודול לחוברת העבודה שלך.
- העתק והדבק את הקוד בחלון המודול.
לאחר שהעתקת את הקוד בחלון הקוד, תוכל לחזור לדף העבודה שלך ולהשתמש בפונקציה זו בדיוק כמו כל פונקציית Excel רגילה אחרת.
פשוט תקליד = מילה וזה יראה לך את הנוסחה ברשימה.
זה דורש טיעון אחד, שהוא הפניה לתא ונותן לך מייד את ספירת המילים בו.
