تابع len در اکسل

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

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

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

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

برای شمارش کلمات در یک سلول از ترکیب توابع 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)

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

تابع Substitute کلمه مورد نظر را از متن اصلی حذف میکند. در این مثال، ما کلمه ورودی به سلول b1 را از متن اصلی که در سلول a2 قرار گرفته حذف میکنیم:

=SUBSTITUTE(A2, $B$1,"")

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

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

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

در آخر، عدد بالا تقسیم بر طول رشته میشود. به عبارت دیگر، شما تعداد کاراکترهای تمام تکرارهای کلمه مورد نظر را بر تعداد کاراکترهای یک بار تکرار همان کلمه تقسیم میکنید. در این مثال 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 قرار دهید: