4.6/5 - (15 امتیاز)
در این مطلب نحوه شمارش کلمات با استفاده از تابع len در اکسل در ترکیب با دیگر توابع اکسل توضیح داده میشود. همچنین فرمول های حساس و غیر حساس به حروف بزرگ و کوچک را که کلمات و متن را در یک محدوده شمارش میکنند را ارائه میکنیم.

در اکسل تعدادی از توابع وجود دارند که تقریباً میتوانند هر چیزی را شمارش کنند: تابع count برای شمارش سلول های دارای عدد، تابع counta برای شمارش سلول های غیر خالی، تابع countif و countifs برای شمارش مشروط سلول ها و len برای محاسبه طول یک رشته متن.

متأسفانه، اکسل ابزاری برای شمارش کلمات ارائه نکرده است. ولی با ترکیب چند تابع و نوشتن فرمول های پیچیده تر میتوان هر کاری را انجام داد. ما نیز از این شیوه برای شمارش کلمات استفاده خواهیم کرد.

تابع  LENدر اکسل:

خلاصه: تابع len در اکسل به شما تعداد کاراکترهای متنی که به آن داده اید را میدهد.Len تعداد کاراکترها را میشمارد اما فورمت شماره را شامل نمیشود.

  • هدف: گرفتن طور متن.
  • خروجی:

شماره ای که تعداد کاراکترهای متن را نشان میدهد.

  • دستور

len (text)=

مقدار ورودی

  • text : متنی که قرار است طول آن شمارش شود.

کاربردهای تابع Len:

  • زمانی که بخواهید تعداد کاراکترهای موجود در یک متن را بشمارید تابع len مورد استفاده شما قرار میگیرد.
  • شماره ها و تاریخ ها نیز طول خواهند داشت.
  • فورمت شماره را شامل نمیشود.(به عنوان مثال طول 100 هم میشود 3؛ طول 100.000$ هم میشود 3.)

فیلم های آموزشی

معرفی تابع Len در اکسل

تابع Len یکی از ساده ترین توابع در اکسل است. این تابع کار شمارش را انجام میدهد.همچنین در این آموزش یاد آوری میکنیم که چگونه متن ها را با استفاده از & کنار هم قرار میدهیم. در این آموزش میخواهیم با این دو مورد آشنا شویم.

 

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

با استفاده از ترکیب دو تابع len و substitute میتوانیم تعداد کلمات مورد نظرمان را در متن مورد نظر را شمارش کنیم که در این آموزش با یک مثال به طور کامل توضیح داده شده است.

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

برای شمارش کلمات در یک سلول از ترکیب توابع len ، substitute و trim استفاده کنید:

=LEN(TRIM(cell))-LEN(SUBSTITUTE(cell," ",""))+1

 

که cell آدرس سلولی است که میخواهید کلمات درون آن را شمارش کنید.

برای مثال برای شمارش کلمات موجود در سلول A2 از فرمول زیر استفاده میکنیم:

=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1

 

سپس میتوانید برای شمارش کلمات در دیگر سلول های ستون A همین فرمول را کپی کنید:

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

این فرمول شمارش کلمات چگونه کار میکند

ابتدا با استفاده از تابع substitiute تمام فاصله ها را در رشته تبدیل به رشته خالی “” میکنید. تا در نتیجه تابع len طول کل رشته را بدون وجود هیچ فاصله ای اندازه گیری کند:

=LEN(SUBSTITUTE(A2," ",""))

 

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

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

فرمولی توسعه یافته برای کار با سلول های خالی

فرمولی که در بالا توضیح داده شد در تمام زمینه ها بی نقض کار میکند فقط یک اشکال دارد و اینکه برای سلول های خالی عدد 1 را برمیگرداند. برای حل این موضوع، میتوانید یک تابع if اضافه کنید تا سلول های خالی را چک کند:

=IF(A2="", 0, LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1)

 

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

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

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

=(LEN(cell)-LEN(SUBSTITUTE(cell, word,"")))/LEN(word)

برای مثال، تعداد تکرار کلمه “زندگی” را در سلول a2 به شکل زیر بدست می آوریم:

=(LEN(A2)-LEN(SUBSTITUTE(A2, "زندگی","")))/LEN("زندگی")

 

نحوه شمارش کلمات خاص در یک سلولبه جای وارد کردن مستقیم کلمه مورد نظر در فرمول خود میتوانید آن را در یک سلول وارد کرده و آدرس آن سلول را به فرمول بدهید. با این کار فرمول شما بسیار فراگیرتر خواهد شد.

نکته: اگر میخواهید فرمول خود را به چندین سلول کپی کنید، حتما آدرس سلولی که کلمه مورد نظر در آن قرار گرفته را با استفاده از $ ثابت کنید. برای مثال:

=(LEN(A2)-LEN(SUBSTITUTE(A2, $B$1,"")))/LEN($B$1)

 

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

این فرمول چگونه کلمات خاص را شمارش میکند

  1. تابع Substitute کلمه مورد نظر را از متن اصلی حذف میکند. در این مثال، ما کلمه ورودی به سلول b1 را از متن اصلی که در سلول a2 قرار گرفته حذف میکنیم:
=SUBSTITUTE(A2, $B$1,"")

 

  1. سپس، تابع len، طول رشته اصلی را بدون در نظر گرفتن کلمه مورد نظر شمارش میکند. در این مثال، LEN(SUBSTITUTE(A2, $B$1,””)) طول رشته موجود در سلول a2 را پس از حذف کاراکترهای تمام تکرارهای کلمه «زندگی»، برمیگرداند.
  2. سپس، عدد بالا از کل طول رشته اصلی کم میشود:
=(LEN(A2)-LEN(SUBSTITUTE(A2, $B$1,"")))

 

نتیجه این عملیات عددی میشود که تعداد کاراکتر تکرارهای کلمه مورد نظر را نشان میدهد که در این مثال عدد 12 است ( سه بار تکرار کلمه «زندگی»)

  1. در آخر، عدد بالا تقسیم بر طول رشته میشود. به عبارت دیگر، شما تعداد کاراکترهای تمام تکرارهای کلمه مورد نظر را بر تعداد کاراکترهای یک بار تکرار همان کلمه تقسیم میکنید. در این مثال 12 بر 4 تقسیم میشود و نتیجه نهایی 3 میشود.

علاوه بر شمارش یک کلمه در یک سلول میتوانید از این فرمول برای شمارش یک زیر رشته یا متن خاص در یک سلول نیز استفاده کنید. برای مثال، میتوانید تعداد تکرار متن “شاد” را در سلول A2 شمارش کنید:

این فرمول چگونه کلمات خاص را شمارش میکند

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

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

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

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

اگر میخواهید کلمه مورد نظر را هم از لحاظ حروف بزرگ و هم کوچک شمارش کنید، می بایست از توابع upper و lower در درون تابع substitute استفاده کنید تا متن اصلی و متنی را که میخواهید جستجو کنید به یک نوع حروف تبدیل کند.

=(LEN(cell)-LEN(SUBSTITUTE(UPPER(cell),UPPER(text),"")))/LEN(text)

 

یا

=(LEN(cell)-LEN(SUBSTITUTE(LOWER(cell),LOWER(text),"")))/LEN(text)

 

برای مثال، برای شمارش تعداد تکرار کلمه موجود در سلول b1 در سلول A1 بدون در نظر گرفتن حروف بزرگ و کوچک،  از فرمول زیر استفاده میکنیم:

=(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($B$1),"")))/LEN($B$1)

 

همانطور که در تصویر زیر نشان داده شده است، فرمول بدون توجه به بزرگ(سلول b1) یا کوچک (سلول d1) بودن حروف یا به صورت جمله بودن (سلول C1) یک عدد را نشان میدهد.

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

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

برای پیدا کردن تعداد کلمات در یک محدوده، از همان فرمول شمارش کلمات در یک سلول استفاده کنید و آن را در درون تابع Sumproduct یا Sum قرار دهید:

=SUMPRODUCT(LEN(TRIM(range))-LEN(SUBSTITUTE(range," ",""))+1)

 

یا

=SUM(LEN(TRIM(range))-LEN(SUBSTITUTE(range," ",""))+1)

 

تابع Sumproduct یکی از چند تابع اکسل است که با آرایه ها کار میکند و شما میتوانید با زدن کلید enter و به صورت معمولی فرمول را تکمیل کنید

اما در مورد تابع sum با وجود اینکه این تابع میتواند به راحتی به صورت آرایه ای کار کند،ولی می بایست برای تبدیل آن به آرایه کلید های ترکیبی Ctrl+Shift+Enter را فشار دهید.

برای مثال، برای شمارش تمام کلمات در محدوده A2:a4، از یکی از فرمول های زیر استفاده کنید:

=SUMPRODUCT(LEN(TRIM(A2:A4))-LEN(SUBSTITUTE(A2:A4," ",""))+1)

 

=SUM(LEN(TRIM(A2:A4))-LEN(SUBSTITUTE(A2:A4," ",""))+1)

 

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

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

اگر میخواهید تعداد تکرار یک کلمه خاص را در یک محدوده بدست آورید، می توانید از همان فرمولی که برای شمارش کلمات خاص در سلول استفاده میشد استفاده کنید و فقط آن را در درون تابع sum یا Sumproduct قرار دهید:

=SUMPRODUCT((LEN(range)-LEN(SUBSTITUTE(rangeword,"")))/LEN(word))

 

یا

=SUM((LEN(range)-LEN(SUBSTITUTE(rangeword,"")))/LEN(word))

 

لطفا به یاد داشته باشید که برای تکمیل فرمول Sum کلید های Ctrl+Shift+Enter را فشار دهید.

برای مثال، برای شمارش تعداد تکرار کلمه وارد شد در سلول c1 در محدوده a2:a4 از فرمول زیر استفاده کنید:

=SUMPRODUCT((LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4, C1,"")))/LEN(C1))

 

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

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

اگر به یاد داشته باشید تابع substitute نسبت به حروف بزرگ و کوچک حساس است و فرمول بالا نیز بین این حروف تفاوت قائل میشود:

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

برای غیر حساس کردن فرمول، از یکی از توابع upper یا lower استفاده کنید:

=SUMPRODUCT((LEN(A2:A4)-LEN(SUBSTITUTE((UPPER(A2:A4)),UPPER(C1),"")))/LEN(C1))

 

یا

=SUMPRODUCT((LEN(A2:A4)-LEN(SUBSTITUTE((LOWER(A2:A4)),LOWER(C1),"")))/LEN(C1))

 

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

برای مطالعه بیشتر میتوانید به مطالب زیر مراجعه کنید:

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

معرفی تابع Count در اکسل

تابع CountA در اکسل

تابع COUNTIF در اکسل – شمارش به شرط خالی نبودن، بزرگتر بودن، تکراری بودن یا منحصر به فرد بودن

نحوه استفاده از توابع COUNTIF و COUNTIFS با شرط های چندگانه

مشترک شدن
Notify of
guest

9 نظرات
نظردهی درون متنی
مشاهده همه نظرات
علی احمدی

سلام برهمگی.آیا تابع یا توابعی یا راهکاری هست که بعد از رسیدن یک سلول به تعداد کاراکتر مشخص به سلول بعد پرش کند…دقیقا چیزی مشابه وارد کردن شماره کارت در خرید های اینترنتی که در 4 دسته 4 رقمی بعد از وارد کردن 4 رقم اول به دسته دوم انتقال می یابد.

امیر دایی

سلام
اکسل یک سری محدودیت داره که نباید مثل یک محیط کامل برنامه نویسی در نظرش بگیرید. مثلا توی شیت ها و سلول ها نمی تونید دکمه هایی که فشرده میشن رو تشخیص بدید. ولی می تونید یک فرم توی قسمت وی بی ای ایجاد کنید و توی فرم از Text box ها استفاده کنید. توی این حالت می تونید کلید هایی که فشرده شده و تعداد فشرده شدنشون رو اندازه بگیرید و در صورت برقراری شرط های مورد نظر وارد text box بعدی بشید.

humane66@gmail.com

ممنونم

رضا

سلام
سلام من میخوام ازین فرمول برای پیدا کردن یک چیزی در یک محدوده استفاده کنم ، ولی محدوده ام مرتب تغییر میکنه
چکار کنم که محدوده ام رو بتونم به صورت خودکار تغییر بدم؟

فائزه قارونی

سلام باید به صورت دقیقتر و با مثال توضیح بدید تا روش دقیق راهنمایی کنم. ولی به صورت کلی باید از تابع search یا find و یک کلمه یا کاراکتر خاص پیدا کنید و محدوده مورد نظر جدا کنید.
این مطلب بخونید تا بهتون دید بهتری بده:
نحوه جدا سازی متن در اکسل

حسین

سلام من سلول هایی از اسامی افراد دارم که برای هر ردیف تعداد آنها متفاوت است مثلاً ردیف 1 که داخل سلول آن نوشته شده (احمد تقوی,محمد رضایی,علی محمودی) یعنی بین اسامی هر نفر علامت کاما قرار دارد . چطور میتونم تعداد این افراد رو بشمارم . با فرمول ترکیبی len و substitute نتونستم . اگه ممکنه ساده برام توضیح بدید . ممنون از شما

امیر دایی

سلام
اگر منظورتون از ساده توضیح دادن نوشتن فرمول هست کافی هست فرمول اول استفاده کنید به جای ” ” از “،” استفاده کنید.

نقدی

من یک اکسل دارم که در هر سل باید به اندازه 16کاراکتر کد بنویسم. کدها به این شکل هستند GM3 L8 NZ$ . هر سل دو تا فاصله بین کارکترها هست که باید محاسبه شود. محاسبه هم براساس اعداد تعلق گرفته به هر حرف یا کاراکتر هست. ( مثلا عبارت بالا به این شکل میشه:26-14- -8-12- -3-13-77-30، اعدادهم به روش دیگری در این سل محاسبه میشوند و باید حداقل 10 باشند ، سپس به این شک ادامه پیدا میکند: (دو به دو با هم جمع میشوند : 3+0=3 و هر جای خالی نیز که معادل 27 =2+7=9 هست، حداقل یکی… مطالعه بیشتر »

امیر دایی

سلام
ابتدا با قابلیت text to column در تب Data همه کاراکترها را جدا کنید تا یک شیت با 16 ستون داشته باشید که در هر ستون یک کارکتر قرار داد.
در شیت دیگر یک جدول درست کنید که ستون اول کاراکتر و ستون دوم عدد کاراکتر باشد.
با استفاده از تابع vlookup در شیت اول برای هر کاراکتر مقدار عددی را فراخوانی کنید.
در نهایت نتیجه را ستون 33م با هم جمع بزنید.