در اکسل تعدادی از توابع وجود دارند که تقریباً میتوانند هر چیزی را شمارش کنند: تابع 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)
این فرمول چگونه کلمات خاص را شمارش میکند
- تابع 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 قرار دهید:
=SUMPRODUCT((LEN(range)-LEN(SUBSTITUTE(range, word,"")))/LEN(word))
یا
=SUM((LEN(range)-LEN(SUBSTITUTE(range, word,"")))/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- دوره فرمول های اساسی در اکسل
تابع COUNTIF در اکسل – شمارش به شرط خالی نبودن، بزرگتر بودن، تکراری بودن یا منحصر به فرد بودن
سلام برهمگی.آیا تابع یا توابعی یا راهکاری هست که بعد از رسیدن یک سلول به تعداد کاراکتر مشخص به سلول بعد پرش کند…دقیقا چیزی مشابه وارد کردن شماره کارت در خرید های اینترنتی که در 4 دسته 4 رقمی بعد از وارد کردن 4 رقم اول به دسته دوم انتقال می یابد.
سلام
اکسل یک سری محدودیت داره که نباید مثل یک محیط کامل برنامه نویسی در نظرش بگیرید. مثلا توی شیت ها و سلول ها نمی تونید دکمه هایی که فشرده میشن رو تشخیص بدید. ولی می تونید یک فرم توی قسمت وی بی ای ایجاد کنید و توی فرم از Text box ها استفاده کنید. توی این حالت می تونید کلید هایی که فشرده شده و تعداد فشرده شدنشون رو اندازه بگیرید و در صورت برقراری شرط های مورد نظر وارد text box بعدی بشید.
ممنونم
سلام
سلام من میخوام ازین فرمول برای پیدا کردن یک چیزی در یک محدوده استفاده کنم ، ولی محدوده ام مرتب تغییر میکنه
چکار کنم که محدوده ام رو بتونم به صورت خودکار تغییر بدم؟
سلام باید به صورت دقیقتر و با مثال توضیح بدید تا روش دقیق راهنمایی کنم. ولی به صورت کلی باید از تابع 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م با هم جمع بزنید.