در مطلب قبل در مورد تابع LEN توضیح دادیم که تمام کاراکترها در یک محدوده را شمارش میکند. تابع LEN  تابع مفیدی است ولی با ترکیب با سایر توابع از قبیل SUM,SUMPRODUCT و SUBSTITUTE میتواند کارهای بسیار پیچیده تری را انجام دهد.

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

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

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

در این مطلب، میخواهیم به فرمول های ساده و پیشرفته ای بپردازیم که کاراکترها را در یک محدوده شمارش میکنند.

برای شمارش تعداد کل کاراکترها در یک محدوده اولین راه حلی که به ذهن میرسد شمارش سلول ها در تک تک سلولها و سپس بدست آوردن مجموع این مقادیر است.

(LEN(A2)+LEN(A3)+LEN(A4=

یا

((SUM(LEN(A2),LEN(A3),LEN(A4=

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

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

((SUMPRODUCT(LEN(range=

و در شرایط واقعی فرمول شما احتمالاٌ به شکل زیر خواهد بود:

(SUMPRODUCT(LEN(A1:A7)=

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

(SUM(LEN(A1:A7=

بر خلاف تابع SUMPRODUCT فرمول SUM به صورت پیش فرض آرایه ها را محاسبه نمیکند بنابراین میبایست پس از نوشتن فرمول از کلیدهای ترکیبی Ctrl + Shift + Enter استفاده کنید تا آن را تبدیل به آرایه کند.

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

تابع sum در اکسل | مثال های کاربردی تابع sum

فیلم آموزشی-تابع sum در اکسل– دوره فرمول های اساسی در اکسل

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

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

در مثال بالا، آرایه ای از 7 عدد که در واقع نشان دهنده تعداد کاراکتر در سلول های a1 تا a7 است، با هم جمع میشوند:

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

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

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

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

((“”,LEN(cell)-LEN(SUBSTITUTE(cell, character=

برای فهم بهتر فرمول مثال های زیر را مورد توجه قرار دهید:

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

با فرض اینکه لیست آیتم های تحویل داده شده در ستون B (از B2 شروع میشود) قرار دارد و هدف ما شمارش تعداد تکرار حرف “A” است، فرمول به شکل زیر خواهد بود:

((“”,”LEN(B2)-LEN(SUBSTITUTE(B2,”A=

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

برای فهم بهتر این فرمول آن را به قسمت های کوچکتری تقسیم میکنیم:

• ابتدا تعداد کل کاراکتر را در سلول B2 شمارش میکنیم: LEN(B2)
• سپس با استفاده از تابع SUBSITITUE تمام تکرارهای حرف A را در سلول B2 با رشته خالی “” جایگزین میکنیم:

(“”,”SUBSTITUTE(B2,”A=

• سپس طول رشته را بدون در نظر گرفتن کاراکتر a شمارش میکنیم:

((“”,”LEN(SUBSTITUTE(B2,”A=

• در آخر میزان طول رشته بدون A را از کل طول رشته کمک میکنیم.

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

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

نکته: تابع SUBSTITUTE  نسبت به حروف بزرگ و کوچک حساس است و در نتیجه فرمول بالا نیز از این حساسیت برخوردار است. برای مثال، در تصویر بالا، سلول B3 شامل سه بار تکرار حرف A است. دوبار به صورت بزرگ و یک بار به صورت کوچک. فرمول فقط کاراکتر بزرگ را شمارش میکند زیرا ما از حرف بزرگ در فرمول خود استفاده کرده ایم.

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

در صورتی که میخواهید بدون حساسیت به بزرگ و کوچک بودن حروف آنها را شمارش کنید می بایست از تابع UPPER در درون SUBSTITUTE استفاده کنید. تا حروف مشخص شده را قبل از شروع به حروف بزرگ تبدیل کند. همچنین می بایست حتماً از حروف بزرگ در فرمول خود استفاده کنید.

برای مثال، برای شمارش A و a در سلول B2 از این فرمول استفاده کنید:

((“”,”LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),”A=

راه دیگر استفاده از تابع Substitute به صورت تو در تو است:

(“”,”LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE (B2,”A”,””),”a=

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

در بعضی از موارد، ممکن است بخواهید کاراکترهای متفاوتی را در یک جدول شمارش کنید، ولی نمیخواهید که هر بار فرمول خود را تغییر دهید. در این موارد، یک تابع  SUBSTITUTE  را در درون یک تابع SUBSTITUTE دیگر قرار دهید، کاراکتری را که میخواهید جستجو کنید تایپ کنید (D1 در این مثال) و با استفاده از توابع LOWER  و UPPER این کاراکترها را تبدیل به حروف کوچک و بزرگ کنید:

((“”,(LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE(B2, UPPER($D$1), “”), LOWER($D$1=

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

((“”,(LEN(B2)-LEN(SUBSTITUTE(UPPER(B2), UPPER($C$1=

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

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

در صورت یکه به دنبال مشخص کردن تعداد تکرار یک ترکیب (متن خاص یا زیر رشته) در یک سلول هستید برای مثال “A2” یا “SS” ، کافیست تعداد کاراکتر های برگردانده شده توسط فرمول های بالا را بر طول زیر رشته تقسیم کنید.

فرمول حساس به حروف بزرگ و کوچک:

(LEN(B2)-LEN(SUBSTITUTE(B2, $C$1,””)))/LEN($C$1=

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

(LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),LOWER($C$1),””)))/LEN($C$1=

B2 سلولی است که کل رشته در آن قرار گرفته و c1 متنی است که میخواهید شمارش کنید.

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

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

(((“”,SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range, character=

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

(((“”,”SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, “A=

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

(((“”,”SUM(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, “A=

در مقایسه با فرمول اول، یک تفاوت واضح وجود دارد و آن استفاده از SUM به جای SUMPRODUCT است. یک تفاوت دیگر این است که برای این تابع میبایست از کلیدهای ترکیبی CTRL + SHIFT + ENTER استفاده کنید زیرا برخلاف SUMPRODUCT تابع SUM فقط زمانی به صورت آرایه ای کار میکند که در درون فرمول های آرایه قرار بگیرد.

در صورتی که نمیخواهید کاراکترها را درون فرمول قرار دهید،میتوانید آنها را در یک سلول تایپ کنید سپس آدرس سلول را در فرمول قرار دهید:

 (((“”,SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, D1=

نکته:

در مواقعی که یک زیر رشته خاص را در یک محدوده شمارش میکنید(برای مثال سفارش هایی که با “KK” یا “AA” شروع میشود)، می بایست تعداد کاراکتر شمارش شده را بر طول زیر رشته تقسیم کرد، در غیر اینصورت هر کاراکتر در زیر رشته به صورت جداگانه شمارش میشود. برای مثال:

((SUM((LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, D1, “”))) / LEN(D1=

آموزش های مربوط به SUMPRODUCT:

فیلم آموزشی: معرفی تابع SUMPRODUCT

تابع SUMPRODUCT در اکسل به همراه مثال های فرمولی

فیلم آموزشی: تابع SUMPRODUCT در اکسل- دوره فرمول های اساسی در اکسل

نحوه کار این  فرمول شمارش کاراکتر

اگر به یاد داشته باشید، تابع SUBSTITIUE برای جایگزین کردن یک کاراکتر خاص(مثلا “A” با یک رشته خالی”” به کار گرفته میشد.

سپس، رشته متنی را که توسط تابع Substitute برگردانده میشد به تابع len  میدادیم تا طول آن را بدون “A” شمارش کند .سپس، مقدار کاراکتر بدست آمده را از تعداد کل کاراکترها کم می کردیم. نتیجه این محاسبات آرایه ای ازشمارش سلول ها میشود که برای هر سلول یک تعداد کاراکتر وجود دارد.

در آخر، تابع SUMPRODUCT اعداد آرایه را با هم جمع میکند و تعداد کل تکرار کاراکتر مورد نظر را در محدوده نشان میدهد.

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

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

برای اینکه این حساسیت رفع شود، از شیوه های توضیح داده شده در مثال های قبل استفاده کنید: ( فرمول های غیر حساس به حروف بزرگ و کوچک برای شمارش کاراکترهای خاص در یک محدوده)

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

• استفاده از تابع upper و وارد کردن یک کاراکتر با حرف بزرگ:

(((“”,”SUMPRODUCT(LEN(B2:B8) – LEN(SUBSTITUTE(UPPER(B2:B8),”A=

• استفاده از تابع Substitute به صورت تو در تو:

(((“”,”SUMPRODUCT(LEN(B2:B8) – LEN(SUBSTITUTE(SUBSTITUTE((B2:B8),”A”,””),”a=

• استفاده از توابع upper و lower ، تایپ کردن حروف به صورت بزرگ و کوچک در یک سلول و قرار دادن آدرس آن سلول در فرمول:

(((“”,(SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(SUBSTITUTE((B2:B8),UPPER($E$1),””), LOWER($E$1=

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

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

از مطالعه این مطلب سپاسگذاریم و امیدواریم دوباره شما را در این وبسایت ملاقات کنیم.