نحوه محاسبه مجموع رقمهاي يك عدد

فرض كنيد عدد 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 در ابتدا و انتهاي فرمول قرار ميگيرد و نبايد آن را تايپ كرد.

 منبع:كليد ترفندهاي اكسل