ساده ترین روش شمارش کاراکترها در اکسل (گام به گام)

برخلاف ورد، تعداد کاراکترها در اکسل (Excel) به صورت خودکار نمایش داده نمی شوند اما با استفاده از چند تابع به ویژه تابع LEN می توانیم تعداد دقیق کاراکترهای یک سلول یا یک محدوده خاص را به دست آوریم.

کاراکترها در اکسل به چند دسته تقسیم می شوند:

• حروف: حروف بزرگ (A-Z) و کوچک (a-z) انگلیسی، حروف فارسی
• اعداد: از ۰ تا ۹
• علائم نگارشی: مانند: ویرگول (،). نقطه (.)، سمی کالون (؛) و …
• کاراکترهای خاص: مانند: @، !، # و …

در این آموزش انواع شمارش تعداد کاراکتر در اکسل را بیان می کنیم:

• شمارش تعداد کاراکترهای یک سلول
• شمارش تعداد کاراکترها در یک محدوده
• شمارش تعداد تکرار یک کاراکتر خاص در یک سلول
• شمارش تعداد تکرار یک کاراکتر خاص در یک محدوده

شمارش تعداد کاراکترهای یک سلول

با تابع LEN می توانید به آسانی تعداد کاراکترها حتی کاراکترهای خاص را در یک سلول شمارش کنید.

ساختار تابع LEN:

=LEN(text)

text متن یا آدرس سلولی است که می خواهید تعداد کاراکترهای آن را محاسبه کنید. برای اضافه کردن تابع LEN و محاسبه تعداد کاراکترها در یک سلول، مراحل زیر را دنبال کنید:

۱- فایل اکسل را باز کنید.

۲- یک ستون در سمت راست فیلدی که می خواهید شمارش کنید، اضافه کنید.

۳- برای ستون یک برچسب تعیین کنید. در این مثال برچسب ستون Dcount است.

۴- روی اولین سلول ستون، زیر سلول Dcount کلیک کنید.

۵- در تب Formulas، روی گزینه Insert Function کلیک کنید.

۶- مقدار لیست select a category را روی All تنظیم کنید. تابع LEN را از لیست انتخاب کنید. (نکته: برای جستجو سریع تر می توانید کلید L را فشار دهید.)

۷- روی OK کلیک کنید. پنجره Functions Arguments باز می شود.

۸- در سلولی که می خواهید کاراکترهای آن را شمارش کنید، کلیک کنید. آدرس سلول به کادر Text منتقل می شود. در این مثال، C2 می باشد. می توانید نتیجه فرمول را در داخل کادر قرمز رنگ در تصویر زیر مشاهده کنید.

۹- OK کنید.

۱۰- اکسل تعداد کاراکترهای سلول C2 را در سلول قرار می دهد.

به جای این روش می توانید، سلول مورد نظر برای نمایش تعداد کاراکتر را انتخاب کنید، سپس فرمول تابع LEN را به طور مستقیم در آن وارد کنید.

اگر می خواهید این فرمول را روی سلول های دیگر همان ستون کپی کنید، مراحل زیر را دنبال کنید:

۱- اشاره گر ماوس را در اولین سلول حاوی فرمول LEN قرار دهید.

۲- با ماوس به سمت پایین اسکرول کنید و همزمان کلید Shift را هم نگه دارید. تا آخرین سلول مورد نظر خود بکشید سپس ماوس و کلید را رها کنید.

۳- Ctrl + d را فشار دهید. تعداد کاراکترهای هر سلول از ستون در ستون متناظرش نمایش داده می شود.

کاراکتر فضای خالی

تابع LEN فاصله ها را نیز شمارش می کند. فاصله های بین کلمات عادی به نظر می رسند. با این حال، این تابع فاصله های ابتدا و انتها را نیز محاسبه می کند. فاصله های ابتدای متن قابل مشاهده هستند اما تشخیص فاصله ها در انتهای متن، سخت است.

به مثال زیر توجه کنید، تعداد کاراکترها در سلول C3 نسبت به سلول C2 بیشتر به نظر می رسند اما با این وجود تعداد کاراکترهای محاسبه شده در Dcount کاملاً متفاوت است و برای C2 عدد بیشتری را نشان می دهد. به این دلیل است که در انتهای سلول C2 فاصله های خالی اضافه شده است.

اکسل برای رفع این مشکل تابع TRIM را ارائه می دهد، این تابع فاصله های ابتدایی و انتهایی را حذف می کند.

= LEN(TRIM(C2))

فرمول بالا مقدار ۱۳ را برمی گرداند. تابع TRIM فاصله بین کلمات را حذف نمی کند.

۴۰ کلید میانبر در اکسل که سرعت کارتان را دو برابر می کنند

شمارش تعداد کاراکترها در یک محدوده

تابع LEN تعداد کاراکترهای یک سلول (یا یک متن) را شمارش می کند ولی نمی تواند مجموع تعداد کاراکترهای موجود در یک محدوده (آرایه) را به طور مستقیم محاسبه کند. به همین دلیل از تابع SUMPRODUCT همراه با LEN استفاده می شود.

ساختار فرمول:

=SUMPRODUCT(LEN(range))

استفاده از تابع LEN همراه با یک محدوده از سلول ها، خطای#VALUE!  را بر می گرداند! بنابراین مانند سایر توابع غیر آرایه ای از فرمول SUMPRODUCT برای کنترل آرایه استفاده می کنیم.

تابع LEN آرایه ای از تعداد کاراکتر سلول ها در محدوده را باز می گرداند، سپس SUMPRODUCT مجموع مقادیر آرایه را محاسبه می کند.

در ادامه نحوه شمارش تعداد تکرار یک کاراکتر خاص را در یک سلول و همچنین در یک محدوه توضیح خواهیم داد.

شمارش تعداد تکرار یک کاراکتر خاص در یک سلول

از تابع زیر برای شمارش تعداد تکرار یک کاراکتر خاص برای یک سلول استفاده می شود:

=LEN(cell)-LEN(SUBSTITUTE(cell,txt,””))

این فرمول ترکیبی از توابع LEN و SUBSTITUTE است. cell و txt به ترتیب نشان دهنده سلول مورد نظر و کاراکتر خاصی است که می خواهید آن را شمارش کنید.

ابتدا تابع SUBSTITUTE، این کاراکتر خاص را در سلول حذف می کند سپس تعداد کاراکترهای فعلی سلول (بدون وجود این کاراکتر خاص) با تابع LEN محاسبه می شود.

نکته: تابع SUBSTITUTE حساس به حروف است، به عنوان مثال، حرف “o” با “O” با یکدیگر متفاوت هستند.

دانلود رایگان آموزش صفر تا صد اکسل (فیلم فارسی+PDF)

مثال

شمارش تعداد تکرار یک حرف بزرگ لاتین در یک سلول:

=LEN(A4)-LEN(SUBSTITUTE(A4,”T”,””))

شمارش تعداد تکرار یک حرف کوچک لاتین در یک سلول:

=LEN(A5)-LEN(SUBSTITUTE(A5,”t”,””))

شمارش فضاهای خالی در یک سلول:

=LEN(A6)-LEN(SUBSTITUTE(A6,” “,””))

شمارش تعداد تکرار یک کاراکتر خاص در یک محدوده

برای شمارش تعداد یک کاراکتر خاص در محدوده سلول ها از توابع LEN و SUBSTITUTE همراه با تابع SUMPRODUCT استفاده می شود. فرم کلی فرمول به شکل زیر است:

=SUMPRODUCT(LEN(rng)-LEN(SUBSTITUTE(rng,txt,””)))

rng و txt به ترتیب نشان دهنده محدوه سلول ها و کاراکتر خاص هستند.

در مثال زیر، نحوه محاسبه تعداد تکرار کاراکتر “o” را در محدوده سلول های B3:B7 توضیح می دهیم:

=SUMPRODUCT(LEN(B3:B7)-LEN(SUBSTITUTE(B3:B7,”o”,””)))

توضیح مثال:

۱- تابع

SUBSTITUTE(B3:B7,”o”,””)

همه کاراکترهای “o” را برای هر سلول در محدوده حذف می کند،

۲- سپس

LEN(SUBSTITUTE(B3:B7,”o”,””))

تعداد کل کاراکترهای فعلی هر سلول (بدون کاراکتر “o”) را برمی گرداند.

۳-

LEN(B3:B7)-LEN(SUBSTITUTE(B3:B7,”o”,””))

برای هر سلول تعداد کاراکترهای محاسبه شده در مرحله قبل را از تعداد کل کاراکترها کم می کند تا تعداد تکرار کاراکتر “o” برای هر سلول در محدوده به دست آید.

۴- تا به اینجا، تعداد کاراکتر “o” برای هر سلول محاسبه شده است و یک لیست (آرایه) به اندازه تعداد سلول های محدوده ساخته شده است. در نهایت تابع SUMPRODUCT اعداد موجود در این لیست را جمع می کند و مجموع تعداد تکرار کاراکتر “o” در محدوه را برمی گرداند.

آموزش گزارش گیری در اکسل با ساده ترین روش موجود