چگونه مجموع رقمهای یک عدد را حساب کنیم

چگونه مجموع رقمهای یک عدد را حساب کنیم

چگونه مجموع رقمهای یک عدد را حساب کنیم

فرض کنید عدد 265 را در خانه M3 نوشته ایم و میخواهیم با پیدا کردن یک فرمول جمع رقمهای این عدد را بدست آوریم یعنی (13=5+6+2)

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

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

=MID(Text,Start_num,num_chars)

در این فرمولtext  رشته ای است که میخواهیم تابع MID روی آن عمل کند. Strat_num شروع و num_chars تعداد حروفی است که می خواهیم تابع آن را جدا کند. در مثال فوق برای جدا کردن رقمها، تابع را به صورت زیر استفاده می کنیم.

=MID(M3,1,1)

این فرمول از خانه M3 و از حرف اول شروع کرده و عبارتی به طول یک حرف را جدا می کند. نتیجه عدد 2 یعنی رقم اول خواهد بود.

=MID(M3,2,1)

=MID(M3,3,1)

این فرمولها نیز از حروف دوم و سوم شروع کرده و رشته ای به طول یک کاراکتر را بیرون می کشند که اعداد 6 و 5 می باشند. اکنون می خواهیم با استفاده از تابع sum این اعداد را با هم جمع کنیم.  فرمول (sum(K1:K3= را در خانه ای دلخواه وارد می کنیم. همانطور که می بینید پاسخ عدد صفر را نمایش می دهد. دلیل این مشکل این است که خروجی تابع MID متن است و نمی توان روی آن عملیات ریاضی انجام داد. برای حل این مشکل از ترکیب تابع Value استفاده می کنیم. این تابع عبارتهای عددی که حالت متنی دارند را دوباره به عدد تبدیل می کند. پس فرمول را به شکل زیر اصلاح می کنیم.

=VALUE(MIDE(M3,1,1)

=VALUE(MIDE(M3,2,1)

=VALUE(MIDE(M3,3,1)

 اکنون می بینید که پاسخ صحیح بدست آمد.

این فرمول فقط برای یک عدد سه رقمی کاربرد دارد. حال اگر بخواهیم تعداد رقمهای عدد را بیشتر کنیم باید فرمولهای بیشتری بنویسیم. ضمناً باید بتوانیم نقطه شروع تابع MID را به صورت متغییر وارد کنیم. مثلاً در فرمولهای بالا نقطه شروع برای رقم اول عدد 1 و برای رقم دوم عدد 2 و برای رقم سوم عدد 3 می باشد.

در گروه توابع متنی تابعی به نام ROW وجود دارد که شماره ستون خانه ای را که به آن داده شود را نمایش می دهد. مثلاً نتیجه تابع (ROW(F12= عدد 12 می باشد. پس می توانیم توابع بالا را به شکل زیر بنویسیم.

=VALUE(MID(M$3,ROW(A1),1)

حال این تابع را تا سلول M5 کپی می کنیم. سلول M3 را به صورت مطلق تبدیل کرده ایم تا در هنگام کپی کردن مقدار آن تغییر نکند. اما چون A1 مطلق نشده قسمت ROW(A1)در هنگام کپی شدن به سلولهای پایینی به صورت (ROW(A3), ROW(A2 تغییر می کند و در نتیجه مقادیر 1،2،3  در این سه فرمول قرار می گیرد و باعث می شود تا رقمهای اول تا سوم عدد اصلی بدست آید. در انتها نیز با استفاده از دستور SUM می توان این اعداد را با هم جمع کرد.

  =VALUE(MID(M$3,ROW(A1),1)

=VALUE(MID(M$3,ROW(A2),1)

=VALUE(MID(M$3,ROW(A3),1)

تذکر: به جای نوشتن سه تابع مجزا می توان با استفاده از فرمول آرایه ای (برداری)  آنها را در قالب یک تابع نمایش داد. در یک خانه دلخواه فرمول زیر را بنویسید.

=sum(VALUE(MID(M3,ROW(A1:A3),1)))

برای ثبت فرمول آرایه ای باید پس از نوشتن فرمول کلیدهای Ctrl,shift,enter به طور همزمان فشار دهید که در این حالت فرمول به شکل زیر ظاهر خواهد شد.

{=sum(VALUE(MID(M3,ROW(A1:A3),1)))}

تذکر: علامت {} را به صورت دستی وارد نکنید. بلکه پس از فشردن همزمان Ctrl,shift,enter در ابتدا و انتهای فرمول ظاهر خواهند شد.

تا این مرحله فرمول را خلاصه کرده و 4 فرمول را در قالب یک فرمول نوشتیم. اما همانطور که قبلاً گفتیم این فرمول فقط برای اعداد سه رقمی کاربرد دارد. برای اینکه تعداد رقم ها متغییر باشد باید محدوده استفاده شده در فرمول یعنی A1:A3 را به صورت متغییر بنویسیم. مثلاً اگر عدد وارد شده 5 رقمی باشد باید درون تابع ROW محدوده A1:A5 نوشته شود.

برای محاسبه تعداد رقمهای یک عدد از تابع LEN استفاده می کنیم. شکل کلی تابع به صورت (LEN(Text می باشد که در آن text رشته ای است که قصد داریم تعداد کاراکترهای آن را حساب کنیم.

تذکر: فاصله خالی بین حروف نیز یک کاراکتر حساب می شود. مثلاً اگر در سلول A5 عبارت “ALI REZA” را تایپ کنیم عبارت (LEN(A5 مقدار 8 را برمیگرداند (با احتساب یک فاصله خالی)

اما برای ساختن آدرس محدوده به صورت متغییر باید از دستور ترکیب متن استفاده کنیم. متنهای مجزا را می توان با استفاده از علامت (&) با هم ترکیب کرد. مثلاً اگر در سلول A1 عبارت “محمد: و در سلول A2 عبارت “رضا”  را تایپ کنیم با استفاده از دستور A1&A2= عبارت ترکیبی “محمدرضا” را بدست می آوریم.

اگر در خانه M3 مثلاً عددی 5 رقمی را وارد کنیم و دستور زیر را در یک خانه دلخواه وارد نماییم

 =”A1:A”&LEN(M3)

قسمت (LEN(M3 عدد 5 را بر میگرداند و دستور & عدد 5 را در کنار A1:A قرار می دهد و عبارت A1:A5 ساخته می شود. پس درون تابع ROW می توانیم از این ترکیب استفاده نماییم.

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

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

{=sum(value(mid(M3,row(indirect(“A1:A”&len(M3))),1)))}

حال هر عددی و با هر تعداد رقمی که در خانه M3 وارد شود جمع رقم هایش توسط این فرمول محاسبه شده و نمایش داده می شود.

بار دیگر یادآوری میگردد: علامت {} نشان دهنده ثبت فرمول به صورت آرایه ای است و به صورت خودکار پس از ثبت فرمول با استفاده از کلیدهای crtl+shift+enter در ابتدا و انتهای فرمول قرار میگیرد و نباید آن را تایپ کرد.

منبع:کلید ترفندهای اکسل