تمرین ۱۶: تعداد مجموعه ارقام با استفاده از ترکیب توابع SUBSTITUTE و LEN

دیدگاه‌ خود را بنویسید / از /
بازگشت به نتایج جستجو
ویژه⭐ محبوب✨ , ,

محاسبه تعداد ارقام در سلول اکسل با ترکیب توابع SUBSTITUTE و LEN

📝 مقدمه

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

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

 

🔑 کلمات کلیدی

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

تابع LEN در اکسل برای محاسبه تعداد کل کاراکترهای موجود در یک سلول استفاده می‌شود. از طرفی، تابع SUBSTITUTE برای جایگزینی یک کاراکتر یا رشته خاص با مقدار دیگر در یک متن به کار می‌رود. حال اگر بخواهیم تعداد دفعات تکرار یک رقم خاص (مثلاً عدد 1) را در یک سلول بشماریم، می‌توانیم از ترکیب این دو تابع استفاده کنیم.

✅ فرمول کلی:

=LEN(A1) – LEN(SUBSTITUTE(A1,”1″,””))

در این فرمول:

  • LEN(A1) طول کل رشته در سلول A1 را محاسبه می‌کند.
  • SUBSTITUTE(A1,”1″,””) تمام عدد 1 را از رشته حذف می‌کند.
  • LEN(SUBSTITUTE(…)) طول رشته بدون عدد 1 را محاسبه می‌کند.
  • در نهایت، با کم کردن این دو مقدار، تعداد دفعات تکرار عدد 1 به دست می‌آید.

این روش برای شمارش هر کاراکتر دلخواهی قابل استفاده است؛ کافی است مقدار “1” را با کاراکتر مورد نظر جایگزین کنید.

🧩 سناریوی کاربردی

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

=LEN(B2) – LEN(SUBSTITUTE(B2,”7″,””))

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

🎯 هدف:

شمارش تعداد ارقام (یا مجموعه‌های عددی) در یک سلول متنی، مثلاً در رشته‌ای مثل "abc123def456gh789".

✅ روش اول: شمارش تعداد تمام ارقام

اگر بخوایم تعداد کل ارقام (0 تا 9) موجود در رشته رو بشماریم:

=LEN(A1) – LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,”0″,””),”1″,””),”2″,””),”3″,””),”4″,””),”5″,””),”6″,””),”7″,””),”8″,””),”9″,””))

🔹 این فرمول طول رشته اصلی رو با طول رشته‌ای که تمام ارقام ازش حذف شده مقایسه می‌کنه. تفاوت این دو، تعداد ارقام موجود در رشته است.

✅ روش دوم: شمارش تعداد مجموعه‌های عددی پشت‌سرهم

اگر بخوایم تعداد مجموعه‌های عددی (مثل 123, 456, 789) رو بشماریم، باید از ترکیب توابع پیشرفته‌تر مثل LET, TEXTJOIN, یا حتی FILTERXML استفاده کنیم. اما با فرمول ساده، می‌تونیم از یک ترفند با SUBSTITUTE و LEN برای شمارش تعداد فاصله‌ها بین اعداد و حروف استفاده کنیم.

مثلاً اگر بین مجموعه‌های عددی از یک جداکننده مثل فاصله یا کاما استفاده شده باشه، می‌تونیم از این فرمول استفاده کنیم:

=LEN(A1) – LEN(SUBSTITUTE(A1,” “,””))

🔹 این فرمول تعداد فاصله‌ها رو می‌شماره، که می‌تونه نماینده تعداد مجموعه‌ها باشه (اگر فرمت داده‌ها منظم باشه).

🧠 نکته حرفه‌ای:

اگر بخوای این رو در قالب داشبورد یا گزارش‌های اکسل استفاده کنی، می‌تونی این فرمول‌ها رو داخل Named Range بذاری یا با LET ترکیب کنی تا خوانایی و کارایی بیشتر بشه.

حالا با تمرین عملی در محیط واقعی اکسل ترکیب توابع LEN و SUBSTITUTE رو بررسی می‌کنیم.

❓ سوالات متداول:

مشخصات و دانلود

دیدگاهتان را بنویسید

این سایت از اکیسمت برای کاهش جفنگ استفاده می‌کند. درباره چگونگی پردازش داده‌های دیدگاه خود بیشتر بدانید.

سبد خرید
پیمایش به بالا