بیشتر افراد نرم‌افزار اکسل را به‌عنوان ابزاری برای کار با اعداد و محاسبات مربوط به آن می‌شناسند. اما این نرم‌افزار برای برخی محاسبات در مورد متن‌ها مانند شمارش کاراکترها در جدول هم کاربرد دارد. نتیجه این عملیات برای کنترل طول متن، آنالیز آن و ایجاد گزارش‌های سفارشی استفاده می‌شود. کنترل تعداد ارقام شماره تلفن، کد پستی یا محاسبه هزینه پیامک در جدول داده‌های شامل این اطلاعات نمونه‌هایی از استفاده شمارش کاراکترها در اکسل است. در این مطلب از مجله فرادرس چهار روش را برای شمارش تعداد کاراکتر در اکسل با استفاده از تابع LEN تشریح می‌کنیم. کلیه روش‌ها شامل نحوه فرمول‌نویسی و مثال‌های مختلف برای درک بهتر عملیات است.

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

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

=LEN(cell)

قبل از تشریح دقیق‌تر مراحل کار، پیشنهاد می‌کنیم نحوه فرمول‌نویسی در اکسل را با مشاهده فیلم آموزش توابع و فرمول‌نویسی در اکسل در فرادرس فرا بگیرید. این آموزش نکات لازم در خصوص کار با توابع اکسل با هدف درک بهتر ادامه کار را بیان می‌کند.

حال موضوع را با یک مثال بررسی می‌کنیم.در تصویر زیر می‌خواهیم تعداد کاراکترهای سلول A3 را بشماریم. در این صورت با تعریف فرمول =LEN(A3) تعداد کاراکترها با عدد ۲۹ نمایش داده می‌شود. به همین ترتیب با کپی کردن فرمول در سایر سلول‌ها، می‌توانیم تعداد کاراکترها را بعد از شمارش در سلول روبروی آن مشاهده کنیم.

لازم است توجه کنیم که کاراکتر شامل حروف، اعداد، علامت‌ها، نمادهای خاص و فضاهای خالی بین کلمات است. بنابراین تابع LEN همه این موارد را می‌شمارد. همچنین در مطلب زیر از مجله فرادرس در مورد تابع len در اکسل به‌طور کامل توضیح داده‌ایم:

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

گاهی مواقع لازم است تعداد کاراکترهای موجود در یک محدوده سلول اکسل را بشماریم. در این حالت از تابع LEN به شکل ترکیبی با تابع SUMPRODUCT استفاده می‌کنیم. بنابراین فرمول کلی به شکل زیر خواهد بود. در این فرمول منظور از range محدوده سلول مورد نظر برای شمارش کاراکتر است.

=SUMPRODUCT(LEN(range))

به‌عنوان مثال می‌خواهیم تعداد کاراکترها در محدوده سلول A3 تا A18 را بشماریم. در این حالت با تعریف فرمول =SUMPRODUCT(LEN(A3:A18)) نتیجه عدد ۴۹۱ خواهد بود.

بعد از تعریف این فرمول، ابتدا تابع LEN تعداد کاراکترهای موجود در رشته متنی هر سلول را به‌صورت جداگانه می‌شمارد. سپس تابع SUMPRODUCT اعداد هر سلول را جمع می‌کند و مجموع کل کاراکترها را به‌عنوان نتیجه مشخص می‌کند.

البته به روشی دیگر هم می‌توان بعد از ترکیب تابع LENبا تابع SUM و تعریف فرمول =SUM(LEN(A3:A18)) تعداد کاراکتر در این محدوده سلول را شمارش کرد. اما تفاوت‌هایی در کاربرد آن در نسخه‌های مختلف اکسل وجود دارد. در نسخه اکسل ۲۰۱۹ و پایین‌تر از آن بعد از استفاده از این فرمول، تابع SUM به‌شکل پیش‌فرض آرایه‌ها را پردازش نمی‌کند.

بنابراین لازم است برای رفع این مشکل، بعد از تعریف فرمول به‌جای استفاده از کلید Enter کلیدهای Ctrl+Shift+Enter را همزمان استفاده کنیم. اما نسخه‌های اکسل ۳۶۵ و ۲۰۲۱ به‌دلیل پشتیبانی از آرایه‌های داخلی پویا، آن را به عنوان یک فرمول معمولی در نظر می‌گیرد.

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

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

=LEN(cell) - LEN(SUBSTITUTE(cell, character, ""))

برای آشنایی با نکات کاربردی کار با اکسل، پیشنهاد می‌کنیم فیلم آموزش ترفندهای کاربردی در اکسل از فرادرس را مشاهده کنید که لینک آن در ادامه قرار گرفته است:

فرض می‌کنیم پایگاهی از داده‌ها داریم که هر یک شناسه یا کاراکتر منحصر‌به‌فرد خود را دارند. هر سلول شامل عباراتی است که با علامت کاما، فاصله یا هر جداکننده دیگری از یکدیگر مجزا می‌شوند. حال می‌خواهیم تعداد تکرار هر کاراکتر منحصر‌به فرد را در سلول‌ها محاسبه کنیم.

به‌عنوان مثال در تصویر زیر داده‌های متنی در ستون A قرار دارند و اولین ردیف آن با A3 مشخص است. کاراکتر هدف مورد نظر برای شمارش در ستون B همان ردیف قرار دارد. در این صورت فرمول برای اولین ردیف به صورت زیر خواهد بود.

=LEN(A3) - LEN(SUBSTITUTE(A3, B3, ""))

حال ببینیم این فرمول چگونه عمل می‌کند.

• بخش اول فرمول عبارت =LEN(A3) است که با استفاده از آن تعداد کل کاراکترها در سلول A3 را می‌شماریم.
• سپس در بخش دوم، فرمول SUBSTITUTE(A3, “A”, “”) همه تکرارهای کاراکتر «A» در سلول A3 را با رشته خالی (” “) جایگزین می‌کند. به‌عبارت دیگر در اینجا کاراکتر خاص «A» را در سلول حذف می‌کنیم.
• در گام سوم با دستور LEN(SUBSTITUTE(A3, “A”, “”)) طول رشته متنی بدون کاراکتر «A» را مشخص می‌کنیم.
• در نهایت با کم کردن تعداد کاراکترهای رشته متنی بدون «A» از تعداد کل کاراکترهای رشته متنی اولیه، تعداد کل تکرار کاراکتر «A» را به‌دست می آوریم.

لازم است به این موضوع توجه کنیم که تابع SUBSTITUTE به بزرگ و کوچک بودن حروف حساس است و هر یک را به‌عنوان کاراکتری جداگانه می‌شمارد. همان‌طور که در تصویر مشخص است، رشته‌های متنی سلول A4 دو حرف «a» و «A» دارد. بنابراین در نتیجه نهایی تنها حرف «A» شمارش می‌شود.

شمارش کاراکترها با حذف حساسیت به کوچک یا بزرگ بودن حروف در یک سلول

در برخی مواقع نیاز به فرمولی داریم که همه کاراکترها را بدون در نظر گرفتن کوچک یا بزرگ بودن حروف شمارش کند. برای این کار از توابع متنی UPPER یا LOWER در ترکیب با تابع LEN و تابع SUBSTITUTE استفاده می‌کنیم. به این شکل حساسیت تابع SUBSTITUTE را به بزرگ یا کوچک بودن حروف از بین می‌بریم.

به‌عنوان مثال اگر کاراکتر خاص مد نظر ما برای شمارش حرف «A» باشد، با استفاده از ترکیب تابع SUBSTITUTE و تابع UPPER فرمول به شکل زیر در می‌آید.

=LEN(A3) - LEN(SUBSTITUTE(UPPER(A3), "A", ""))

در این فرمول، عملکرد ترکیبی تابع SUSTITUTE و تابع UPPER همه حرف‌های «a» را با «A» جایگزین می‌کند. سپس عملیات شمارش همانند قبل با استفاده از تابع LEN انجام می‌گیرد.

به همین شکل اگر کاراکتر خاص مورد نظر ما حرف «a» باشد، ترکیب تابع SUBSTITUTE و LOWER را استفاده می‌کنیم. در اینجا نیز ترکیب این دو تابع همه حرف‌های «A» را با «a» کوچک جایگزین می‌کند. سپس شمارش کاراکترها را بدون حساسیت به بزرگ یا کوچک بودن آن‌ها انجام می‌دهد.

=LEN(A3) - LEN(SUBSTITUTE(LOWER(A3), "a", ""))

در حالت پیچیده‌تر، می‌توان با نوشتن یک فرمول تودرتو با تابع SUBSTITUTE، این دو عملکرد را به شکل همزمان انجام داد. هر دو فرمول، حروف بزرگ و کوچک را همراه یکدیگر می‌شمارند.

=LEN(A3) - LEN(SUBSTITUTE(SUBSTITUTE (A3, "A", ""), "a", "")

شمارش متن یا زیر رشته مشخص در یک سلول اکسل

اگر بخواهیم تعداد تکرار یک ترکیب خاص از کاراکترها (مانند C2 ، C-2 یا cat) را در سلول معین بشماریم، در این صورت تعداد کاراکترهای شمارش شده توسط تابع LEN و SUSTITUTE را بر طول زیر رشته تقسیم می‌کنیم.

به‌عنوان مثال در تصویر زیر دو فرمول را می‌توانیم برای این کار استفاده کنیم.

• فرمول حساس به بزرگ یا کوچک بودن حروف به‌صورت

=(LEN(A3) - LEN(SUBSTITUTE(A3, B3, ""))) / LEN(B3)

•  فرمول غیر حساس به بزرگ یا کوچک بودن حروف به‌صورت

=(LEN(A3)-LEN(SUBSTITUTE(UPPER(A3), UPPER(B3),""))) / LEN(B3)

در هر دو حالت، سلول A3 رشته متنی مبدا و سلول B3، زیر رشته متنی مورد نظر برای شمارش هستند.

یادگیری اصول شمارش کاراکتر در اکسل با فرادرس

تا این بخش از مطلب با بخش مهم و پایه‌ای مبحث شمارش کاراکتر در اکسل آشنا شدیم. اما پیشرفت در این خصوص نیاز به یادگیری کامل اصول فرمول‌نویسی و کار با توابع مختلف دارد. بنابراین پیشنهاد می‌کنیم برای جلوگیری از بروز خطا در محاسبات، فیلم آموزش زیر را در فرادرس مشاهده کنید.

همچنین از آنجا که اکسل کاربرد گسترده‌ای برای ساده‌سازی عملیات در جدول داده‌ها و انجام محاسبات مختلف دارد، تکمیل مهارت‌های کاربردی در این زمینه کمک بزرگی برای تبدیل شدن به یک کاربر حرفه‌ای است. در سه مجموعه آموزش تهیه شده زیر در فرادرس می‌توانید کلیه نکات لازم کار با اکسل را فرا بگیرید.

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

فرمول مورد استفاده برای شمارش کاراکترهای خاص در چند سلول شبیه فرمول شمارش کاراکترها در یک سلول است. با این تفاوت که تابع LEN را همراه با تابع SUMPRODUCT در قالب زیر به‌کار می‌بریم.

=SUMPRODUCT(LEN(range) - LEN(SUBSTITUTE(range, character, "")))

در این فرمول آرگومان range محدوده سلول مورد نظر و آرگومان character کاراکتر مورد نظر برای شمارش است.

به‌عنوان مثال اگر بخواهیم تعداد تکرار کاراکتر A را در محدوده سلول‌های A3 تا A18 تصویر زیر بشماریم، فرمول به این شکل خواهد بود. لازم‌ به‌ذکر است در این فرمول موقعیت مکانی کاراکتر A، یعنی D2 را در فرمول می‌نویسیم.

=SUMPRODUCT(LEN(A3:A18) - LEN(SUBSTITUTE(A3:A18, D2, "")))

روش عملکرد این فرمول به شکل زیر است.

• در ابتدا تابع SUSTITUTE همه کاراکترهای مشخص (مانند A) در محدوده سلول A3 تا A18 را با (” “) جایگزین می‌کند.
• در مرحله بعد تابع LEN تعداد کل رشته‌های متنی در این محدوده سلول را بدون در نظر گرفتن کاراکتر A می‌شمارد.
• در مرحله آخر نتیجه حاصل از تعداد کل کاراکترهای رشته متنی اصلی در این محدوده سلول کسر می‌شود. به این شکل تعداد تکرار کاراکتر مورد نظر در محدوده A3 تا A18 به‌دست می‌آید.

البته می‌توان به‌جای تابع SUMPRODUCT تابع SUM را نیز در قالب فرمول زیر استفاده کرد.

=SUM(LEN(A3:A18) - LEN(SUBSTITUTE(A3:A18, D2, "")))

اما برای اجرای فرمول حتما باید از کلید ترکیبی Ctrl+Shift+Enter استفاده کنیم. زیرا به‌جز نسخه‌های ۳۶۵ و ۲۰۲۱ اکسل، در سایر موارد تابع SUM تنها زمانی به‌شکل آرایه‌ای عمل می‌کند که درون فرمول‌های آرایه‌ای قرار گیرد.

شمارش کاراکترهای خاص با حذف حساسیت به کوچک یا بزرگ بودن حروف در یک محدوده سلول

همان‌طور که می‌دانیم تابع SUSTITUTE به کوچک یا بزرگ بودن حروف حساس است. بنابراین اگر بخواهیم این حساسیت را در شمارش کاراکترهای خاص یک محدوده سلول حذف کنیم، یکی از دو فرمول زیر را استفاده می‌کنیم.

=SUMPRODUCT(LEN(A3:A18) - LEN(SUBSTITUTE(UPPER(A3:A18), "A", "")))

=SUMPRODUCT(LEN(A3:A18) - LEN(SUBSTITUTE(LOWER(A3:A18), "a", "")))

همچنین می‌توانیم دو تابع SUSTITUTE را به‌صورت تودرتو، این بار در یک محدوده سلول به‌کار بریم.

=SUMPRODUCT(LEN(A3:A18) - LEN(SUBSTITUTE(SUBSTITUTE((A3:A18), "A", ""), "a", "")))

برای حذف حساسیت فرمول نسبت به بزرگ یا کوچک بودن نیز از تابع LOWER یا تابع UPEER در ترکیب با تابع SUMPRODUCT و تابع LEN در یک سلول مشخص استفاده می‌کنیم.

=SUMPRODUCT(LEN(A3:A18) - LEN(SUBSTITUTE(LOWER(A3:A18), LOWER(D2), "")))

در تصویر زیر نحوه عملکرد این فرمول نمایش داده شده است.

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

برای شمارش تعداد تکرار یک متن مشخص در محدوده‌ای از جدول داده‌های اکسل از فرمول زیر استفاده می‌کنیم.

=SUMPRODUCT((LEN(range) - LEN(SUBSTITUTE(range, text, ""))) / LEN(text))

به‌عنوان مثال در تصویر زیر می‌خواهیم تعداد تکرار کلمه «Life» در محدوده سلول A3 تا A18 را بشماریم. از آنجا که این کلمه در سلول D2 قرار دارد، فرمول به این شکل در خواهد آمد.

=SUMPRODUCT((LEN(A3:A18) - LEN(SUBSTITUTE(A3:A18, D2, ""))) / LEN(D2))

لازم به‌ذکر است که در این مثال، فرمول به بزرگ و کوچک بودن حروف حساس است و تنها کلمه «Life»، با حرف L بزرگ، را می‌شمارد. برای حذف این حساسیت می‌توانیم از تابع UPPER یا LOWER استفاده کنیم.

نکات مهم در شمارش تعداد کاراکتر در اکسل

برای جلوگیری از بروز خطا در شمارش تعداد کاراکتر در اکسل باید به محدودیت‌های این نرم‌افزار در ورود کاراکترها توجه کنیم. بنابراین رعایت نکات زیر ضروری است. اگر به یادگیری اصولی این نکات علاقه‌مند هستید، پیشنهاد می‌کنیم فیلم آموزش استفاده از توابع و فرمول‌نویسی در اکسل را از فرادرس مشاهده کنید. لینک این فیلم در زیر قرار گرفته است:

• تعداد کل کاراکترهای قابل درج در یک سلول اکسل ۳۲۷۶۷ عدد است.
• تنها ۱۰۲۴ کاراکتر در یک سلول اکسل نمایش داده می‌شود. بر همین اساس نوار ابزار فرمول هم می‌تواند ۳۲۷۶۷ نماد را نشان دهد.
• حداکثر طول یک فرمول در نسخه ۲۰۰۷ به بالاتر میکروسافت اکسل، ۸۱۹۲ کاراکتر و در نسخه ۲۰۰۳ برابر ۱۰۱۴ کاراکتر است.

جمع‌بندی پایانی

نرم‌افزار اکسل علاوه بر کاربری برای محاسبات عددی، برای مدیریت و کنترل رشته‌های متنی هم استفاده می‌شود. یکی از مهم‌ترین این عملیات، شمارش تعداد کاراکتر در یک سلول یا محدوده‌ای از سلول‌ها با تابع LEN است. به‌ این روش می‌توان نتایج را برای تحلیل داده‌ها یا تنظیم گزارش‌های خاص استفاده کرد. در این مطلب از مجله فرادرس چهار روش را برای شمارش تعداد کاراکتر در اکسل همراه مثال‌های مختلف بررسی کردیم.

این روش‌ها شامل نحوه استفاده از تابع LEN به تنهایی یا در ترکیب با توابعی مانند SUM و UPPER و SUBSTITUTE و LOWER و SUMPRODUCT بود. برای درک بهتر کاربرد هر یک از این توابع، آموزش نحوه فرمول‌نویسی در اکسل با توابع مختلف بسیار اهمیت دارد.