مقدمه
شمارش تعداد وقوع یک کاراکتر در رشتهها یکی از نیازهای متداول در تحلیل متنی داخل اکسل است. ترکیب توابع LEN و SUBSTITUTE ساده، سریع و بسیار قابل تکرار است؛ بدون نیاز به ماکرو یا توابع پیچیده میتوانید برای حساب کردن کاراکترها، جداکنندهها یا الگوهای تکحرفی فرمولی دقیق بسازید.
تابعهای LEN و SUBSTITUTE در اکسل وقتی با هم ترکیب میشن، میتونن برای شمارش تعداد دفعات وقوع یک کاراکتر یا رشته خاص در یک سلول استفاده بشن. این ترکیب یکی از ترفندهای محبوب برای تحلیل دادههای متنی هست.
کلمات کلیدی
شمارش کاراکتر در اکسل، LEN و SUBSTITUTE، فرمول شمارش تکرار کاراکتر، Excel count character، آموزش اکسل فارسی
فرمول پایه برای شمارش تعداد وقوع یک کاراکتر مشخص در یک سلول، از تفاوت طول متن قبل و بعد از حذف آن کاراکتر استفاده میکند. ابتدا طول کل متن را با LEN محاسبه میکنیم، سپس با SUBSTITUTE آن کاراکتر را حذف کرده و طول حاصل را مجدداً با LEN اندازه میگیریم. اختلاف این دو طول برابر با تعداد کاراکترهای حذفشده است.
فرمول عمومی:
- =LEN(A1)-LEN(SUBSTITUTE(A1,”x”,””))
در این فرمول A1 سلولی است که متن در آن قرار دارد و “x” جایگزین کاراکتری میشود که میخواهید شمارش کنید. اگر کاراکتر مورد نظر یک رشته چندکاراکتری باشد، این روش تعداد دفعاتی را که آن رشته کامل در متن ظاهر شده بشمار میآورد.
نکات کلیدی و موارد احتیاط:
- حساسیت به حروف بزرگ و کوچک: SUBSTITUTE بهصورت پیشفرض حساس به حالت حروف است؛ برای شمارش مستقل از کیس باید متن را با UPPER یا LOWER یکدست کنید، سپس کاراکتر را نیز همانی کنید. مثال:
- =LEN(UPPER(A1))-LEN(SUBSTITUTE(UPPER(A1),”A”,””)).
- شمارش کاراکترهای خاص: برای شمارش جداکنندهها مثل کاما، خط فاصله، یا اسلش از همان روش استفاده کنید؛ توجه کنید کاراکترها باید داخل نقلقول قرار گیرند.
- کاراکترهای یونیکد: برخی کاراکترهای خاص یا فاصلههای نامرئی ممکن است بهدرستی حذف نشوند؛ در این موارد از توابع پاکسازی مانند TRIM یا SUBSTITUTE با کدهای خاص استفاده کنید.
- شمارش رشتههای چندحرفی: اگر رشتهی مورد نظر بیش از یک کاراکتر باشد، نتیجه اختلاف طول تقسیم بر طول رشته هدف است تا تعداد تکرار دقیق بهدست آید؛ مثال برای شمارش:
- “ab”: =(LEN(A1)-LEN(SUBSTITUTE(A1,”ab”,””)))/LEN(“ab”)
مزایای روش LEN+SUBSTITUTE:
- سریع، بدون محاسبات آرایهای یا ماکرو
- قابل ترکیب با توابع شرطی مثل IF یا SUMPRODUCT برای آنالیز گسترده
- مناسب برای پردازش لیستهای متنی و گزارشگیری
سناریوی کاربردی
فرض کنید در یک ستون A لیستی از نظرات مشتریان دارید و میخواهید تعداد دفعاتی که حرف «a» یا کلمه «error» ظاهر شده را در هر نظر بسنجید و سپس تعداد کل در کل نظرات را محاسبه کنید.
مرحلههای عملی:
- در ستون B برای هر سطر فرمول تکحرفی را وارد کنید: =LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),”a”,””))
- برای شمارش کلمه error در ستون C از فرمول رشتهای استفاده کنید: =(LEN(LOWER(A2))-LEN(SUBSTITUTE(LOWER(A2),”error”,””)))/LEN(“error”)
- برای جمع سراسری از تابع SUM روی ستون B یا C استفاده کنید: =SUM(B:B)
- برای فیلتر کردن نظراتی که بیش از N تکرار دارند از فیلتر یا شرط IF در ستون کمکی بهره ببرید:
=IF(B2>3,”پر تکرار”,”عادی”)
این سناریو به شما کمک میکند سریعترین دید از پراکندگی کاراکترها یا واژگان در دیتاست متنی را بهدست آورید و بر اساس آن اولویتبندی تحلیل یا بازبینی متن انجام دهید.
🎯 کاربرد اصلی: شمارش تعداد دفعات وقوع یک کاراکتر یا رشته
فرض کن میخوای بدونی چند بار حرف “a” در سلول A1 ظاهر شده. فرمول زیر رو استفاده کن:
=LEN(A1)-LEN(SUBSTITUTE(A1,”a”,””))
🧠 نحوه عملکرد:
LEN(A1)طول کل متن رو برمیگردونه.SUBSTITUTE(A1,"a","")تمام “a”ها رو حذف میکنه.LEN(SUBSTITUTE(...))طول متن بدون “a”ها رو میده.- تفاوت این دو طول، تعداد “a”های حذفشده است، یعنی تعداد دفعات وقوع “a”.
📌 مثالهای کاربردی:
| متن در سلول | کاراکتر مورد نظر | فرمول | نتیجه |
|---|---|---|---|
| “banana” | “a” | =LEN(A1)-LEN(SUBSTITUTE(A1,"a","")) |
3 |
| “hello world” | “l” | =LEN(A1)-LEN(SUBSTITUTE(A1,"l","")) |
3 |
| “2025/10/01” | “/” | =LEN(A1)-LEN(SUBSTITUTE(A1,"/","")) |
2 |
💡 نکات پیشرفته:
- میتونی این ترکیب رو با
IFیاCOUNTIFهم ترکیب کنی برای تحلیلهای شرطی. - برای شمارش رشتههای چند حرفی (مثلاً “ab”) هم همین روش جواب میده، فقط دقت کن که رشته جایگزین باید دقیقاً همون باشه.
حالا با یک تمرین عملی در محیط واقعی اکسل یک مسئله حل میکنیم.
سئوالات متداول:
- سطح / ناشرEXCEL، مقاله تخصصی
- بازدید401