ל- Excel יש כמה פונקציות טקסט מדהימות שיכולות לעזור לך בעבודה עם נתוני הטקסט.
במקרים מסוימים, ייתכן שיהיה עליך לחשב את מספר התווים הכולל בתא/טווח או את מספר הפעמים שתו ספציפי מתרחש בתא.
אמנם קיימת פונקציית LEN שיכולה לספור את מספר התווים בתא, אך ניתן לבצע את השאר גם עם שילוב נוסחאות (כפי שנראה בהמשך הדוגמאות).
במדריך זה אכסה דוגמאות שונות בהן ניתן לספור תווים בסך הכל או ספציפיים בתא/טווח ב- Excel.
ספור את כל הדמויות בתא
אם אתה פשוט רוצה לקבל ספירה כוללת של כל הדמויות בתא, תוכל להשתמש בפונקציית LEN.
הפונקציה LEN לוקחת ארגומנט אחד, שיכול להיות הטקסט במרכאות כפולות או התייחסות התא לתא שיש בה את הטקסט.
לדוגמה, נניח שיש לך את מערך הנתונים כפי שמוצג להלן וברצונך לספור את מספר התווים הכולל בכל תא:
להלן הנוסחה שתעשה זאת:
= LEN (A2)
כשלעצמו, הפונקציה LEN אולי לא נראית הרבה, אבל כשאתה משלב אותה עם נוסחאות אחרות, היא יכולה לעשות כמה דברים נפלאים (כגון קבלת מילת המלים בתא או פיצול שם פרטי ושם משפחה).
הערה: פונקציית LEN תספור את כל התווים בתא, בין אם זה תו מיוחד, מספרים, סימני פיסוק ותווי רווח (רווחים מובילים, נגררים וכפולים בין מילים).
מכיוון שפונקציית LEN סופרת כל תו בתא, לפעמים אתה עלול לקבל את התוצאה הלא נכונה במקרה שיש לך רווחים נוספים בתא.
לדוגמה, במקרה שלהלן, הפונקציה LEN מחזירה 25 עבור הטקסט בתא A1, בעוד שהיא הייתה צריכה להיות 22. אבל מכיוון שהיא גם סופרת תווי רווח נוספים, אתה מקבל את התוצאה הלא נכונה.
כדי להימנע מספירת רווחים נוספים, תחילה תוכל להשתמש בפונקציית TRIM כדי להסיר כל רווח מוביל, נגרר וכפול ולאחר מכן להשתמש בפונקציית LEN עליה כדי לקבל את ספירת המילים האמיתית.
הנוסחה שלהלן תעשה זאת:
= LEN (TRIM (A2))
ספור את כל הדמויות בטווח תאים
תוכל גם להשתמש בפונקציית LEN כדי לספור את מספר התווים הכולל בטווח שלם.
לדוגמה, נניח שיש לנו את אותו מערך הנתונים והפעם, במקום לקבל את מספר התווים בכל תא, אני רוצה לדעת כמה יש בכל הטווח.
אתה יכול לעשות זאת באמצעות הנוסחה שלהלן:
= SUMPRODUCT (LEN (A2: A7)))
תן לי להסביר כיצד נוסחה זו עובדת.
בנוסחה שלעיל, חלק ה- LEN של הפונקציה לוקח טווח שלם של תאים וסופר את התווים בכל תא.
התוצאה של פונקציית LEN תהיה:
{22;21;23;23;23;31}
כל אחד מהמספרים הללו מייצג את ספירת התווים בתא.
וכאשר אתה משתמש בפונקציית SUMPRODUCT איתה, זה פשוט היה מוסיף את כל המספרים האלה.
כעת, אם אתה תוהה מדוע אינך יכול להשתמש ב- SUM במקום ב- SUMPRODUCT, הסיבה היא שמדובר במערך ו- SUMPRODUCT יכול להתמודד עם מערך אך הפונקציה SUM לא יכולה.
עם זאת, אם אתה עדיין רוצה להשתמש ב- SUM, תוכל להשתמש בנוסחה שלהלן (אך זכור שעליך להשתמש ב- Control + Shift + Enter כדי לקבל את התוצאה במקום Enter רגיל)
= SUM (LEN (A2: A7))
ספירת תווים ספציפיים בתא
כפי שציינתי כי התועלת האמיתית של פונקציית LEN היא כאשר היא משמשת בשילוב עם נוסחאות אחרות.
ואם אתה רוצה לספור תווים ספציפיים בתא (זה יכול להיות אות, מספר, תו מיוחד או תו רווח), אתה יכול לעשות זאת בעזרת שילוב נוסחה.
לדוגמה, נניח שיש לך את מערך הנתונים כפי שמוצג להלן וברצונך לספור את מספר המילים הכולל בכל תא.
אמנם אין נוסחה מובנית כדי לקבל את ספירת המילים, אך ניתן לספור את תווי הרווח ולאחר מכן להשתמש בה כדי לדעת את מספר המילים הכולל בתא.
להלן הנוסחה שתיתן לך את המספר הכולל של תווי שטח בתא:
= LEN (A2) -LEN (SUBSTITUTE (A2, "", ""))+1
הנוסחה לעיל סופרת את המספר הכולל של תווים בחלל ולאחר מכן מוסיפה 1 למספר זה כדי לקבל את ספירת המילים.
כך פועלת נוסחה זו:
- הפונקציה SUBSTITUTE משמשת להחלפת כל תווי הרווח בריק. לאחר מכן משתמשים בפונקציית LEN לספירת המספר הכולל של התווים כאשר אין תווי רווח.
- התוצאה של ה- LEN (SUBSTITUTE (A2, "", "")) לאחר מכן מופחתת מ- LEN (A2). זה נותן לנו את המספר הכולל של תווי שטח שיש בתא.
- נוספה 1 בנוסחה ומספר המילים הכולל יהיה אחד יותר ממספר התווים הכולל (כאשר שתי מילים מופרדות עם תו אחד).
שים לב שבמקרה שיש רווחים מובילים, נגררים או כפולים, אתה תקבל את התוצאה הלא נכונה. במקרה כזה, עדיף להשתמש בפונקציית TRIM יחד עם פונקציית LEN.
אתה יכול גם להשתמש באותה לוגיקה כדי למצוא תו או מילה או ביטוי ספציפיים בתא.
לדוגמה, נניח שיש לי מערך נתונים כפי שמוצג להלן שבו יש לי קבוצות שונות, כאשר לכל אצווה יש אלפבית ומספר לייצוגו (כגון A1, J2 וכו ')
להלן הנוסחה שתיתן לך את מספר הפעמים הכולל שנוצרה אצווה עם האלף בית A בכל חודש:
= LEN (B2) -LEN (תחליף (B2, "A", ""))
הנוסחה לעיל שוב משתמשת באותה היגיון - מצא את אורך הטקסט בתא עם ובלי התו שברצונך לספור ולאחר מכן קח את ההבדל בין שני אלה.
בנוסחה שלעיל, קידדתי את הדמות שאני רוצה לספור, אך תוכל גם להכניס אותה לתא ולאחר מכן להשתמש בהתייחסות לתא. זה הופך את זה לנוח יותר מכיוון שהנוסחה תתעדכן בפעם הבאה שתשנה את הטקסט בתא.
ספירת תווים ספציפיים באמצעות נוסחה חסרת רגישות
יש בעיה אחת בנוסחה המשמשת לספירת תווים ספציפיים בתא.
הפונקציה SUBSTITUTE תלויה באותיות רישיות. המשמעות היא שאתה "A" אינו שווה ל"א ". זו הסיבה שאתה מקבל את התוצאה הלא נכונה בתא C5 (התוצאה הייתה צריכה להיות 3).
אז איך אתה יכול לקבל את ספירת התווים של דמות ספציפית כאשר זה יכול היה להיות בכל מקרה (תחתון או עליון).
אתה עושה את זה על ידי הפיכת הנוסחה שלך לא רגישה. למרות שאתה יכול ללכת על נוסחה מורכבת, פשוט הוספתי את ספירת התווים של שני המקרים (אותיות קטנות ואותיות גדולות A).
= LEN (B2) -LEN (SUBSTITUTE (B2, "A", ""))+LEN (B2) -LEN (SUBSTITUTE (B2, "a", "")))
ספירת תווים/ספרות לפני ואחרי עשרוני
אני לא יודע למה, אבל זוהי שאלה נפוצה שאני מקבל מהקוראים שלי וראיתי בפורומים רבים - כמו זו
נניח שיש לך מערך נתונים כפי שמוצג להלן ואתה רוצה לספור את התווים לפני העשרוני ואחרי העשרוני.
להלן הנוסחאות שיעשו זאת.
לספור תווים/מספרים לפני העשרוני:
= LEN (INT (A2))
לספור תווים/מספרים אחרי העשרוני:
= LEN (A2) -FIND (".", A2)
שים לב כי נוסחאות אלה מיועדות רק לספרות משמעותיות בתא. במקרה שיש לך אפסים מובילים או נגררים או שהשתמשת בעיצוב מספר מותאם אישית כדי להציג מספרים/פחות יותר, הנוסחאות שלעיל עדיין יספקו לך ספרות משמעותיות לפני ואחרי העשרוני.
אז אלו חלק מהתרחישים שבהם ניתן להשתמש בנוסחאות לספירת תווים בתא או בטווח תאים ב- Excel.
אני מקווה שמצאת את ההדרכה שימושית!
