تفاوت SUM و SUBTOTAL

در این آموزش می‌خواهیم به تفاوت دو تابع SUM و SUBTOTAL و همچین ترفندهای آن که طی این آموزش به شما دوستان خواهیم گفت بپردازیم .

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

***در جدول سمت چپ از SUM استفاده می کنیم و در جدول سمت راست از SUBTOTAL ***

تابع SUM در اکسل

یکی از توابع پر کاربرد اکسل که همگی آن را می شناسیم تابع sum می باشد.

ما می‌توانیم از کلید AutoSum استفاده کنیم .اگر کلیدی که در تب Home و گزینه AutoSum قرار دارد بزنیم سیستم در صورتی که فاصله‌ای بین داده ها نباشد یعنی سلول خالی بین داده‌ها نباشد ، درست این محدوده من رو حدس میزنه و من فقط کافی است یک Enter بزنم

برای سلول‌های کناری هم که ارتفاعشون دقیقا سه تا سلوله می‌توانیم این سلول رو با Ctrl + C کپی کرده و اینجا محدوده رو انتخاب کنم، Ctrl + Vیا Paste کنیم.

در اینجا این سلول رو دیگر نمی‌توانیم کپی کنیم ، چون در جدول قبلی ارتفاع سه تا سلول بود ولی در اینجا ارتفاع بیشتره پس من اینجا دوباره باید AutoSum یا کلید Alt + = رو بزنم تا محدوده رو انتخاب کنم و Enter را بزنم

برای باقی سطرها دوباره می‌توانیم کپی کنیم یا اینکه از ابزار Fill Handle که مربع پر رنگ در صورتی که اکسل شما از چپ به راست باشه(گوشه سمت راست ) و در صورتی که اکسل از راست به چپ باشه (گوشه سمت چپ)

این را با موس می‌توانیم درگ کنیم تا جمع رتبه ما بدهد

اینجا هم به جای اینکه من یک بار Alt + = رو بزنم و بعد درگ کنم یا کپی کنم من می‌توانم کل این محدوده رو انتخاب کنم، Alt + = رو بزنم تا جمع همه اینا رو داشته باشیم .

****ولی حتماً چک کنید که ببینید حدس اکسل درست باشد***

خب حالا اگر ما جمع این جمع‌ها رو بخواهیم ، چه کار می‌کنیم؟

اگر سلول خالی بین این محدوده نباشد یک ترفندی اکسلی هست …..

اکسل میتواند با AutoSum دقیقاً تشخیص بدهد کدام موارد جمع داده‌های شما بوده و آن جمع رو می‌خواهید شما همه رو با هم جمع کنید.

من اینو AutoSum یا Alt + = رو میزنم، ببینید دقیقاً SUM رو نوشته و این سه تا سلولی که فرمول SUM دارد را انتخاب کرده است و فقط من یه Enter نیاز دارم و اینم می‌‍‌‌تونم درگ کنم.

حالا اگر فاصله‌ای بین این سلول‌ها باشد یا ردیف خالی وجود داشته باشد اکسل نمی‌تواند به درستی حدس بزند در اینصورت ما دو راه حل داریم:
راه حل اول این است که من Alt + = را که زدم و اکسل اشتباه تشخیص داد با نگه داشتن کلید Ctrl سلول هامو انتخاب کنم، سلول اول ، سلول دوم، سلول سوم،

اما این راه حل در صورتی که ارتفاع داده های شما زیاد باشد کمی طولانی است، راه حل دوم که راحت‌تر است و شما می‌توانید کل محدوده رو انتخاب کنید( یعنی از ابتدا با همه جمع‌هایی که بین این داده‌ها هست انتخاب می‌کنیم)

و نتیجه‌ای که به ما می‌دهد!!

نتیجه دو برابر است پس ما می‌توانیم جمعمون رو تقسیم بر 2 کنیم تا به نتیجه درست برسیم و اینم می‌توانیم کپی Paste کنیم.

تابع SUBTOTAL در اکسل

این تابع توانایی استفاده از عملیات مختلفی از جمله جمع کردن (SUM) شمارش سلول ها (COUNT)، محاسبه میانگین (AVERAGE)، تعیین حداقل مقدار (MIN) یا حداکثر مقدار (MAX) و موارد دیگر را دارد. اما این عملیات با شرایط منحصر به فردی همراه هستند. در ادامه به بررسی این تابع و ویژگی های خاص آن می پردازیم.

SUBTOTAL(function_num,ref1,[ref2]=

آرگومان اول (function_num): یک عدد صحیح است که نوع محاسبات موردنظر را تعیین می‌کند (مانند میانگین، مجموع و …). مقدار آن بین ‎1-‎11 و ‎101-111‎ است. در جدول زیر مقادیر این آرگومان و نوع محاسبات آن آمده است.

آرگومان دوم ، سوم (ref1, [ref2], …‎): محدوده یا محدوده‌هایی که محاسبات آرگومان اول بر روی آن انجام خواهد شد.

شماره های ۱ تا ۱۱ ← سلولهای فیلتر شده (Filter) را در محاسبات نادیده می گیرند، اما ردیف های پنهان شده (Hide) را شامل می شوند.
شماره های ۱۰۱ تا ۱۱۱ ← همه سلول های فیلتر شده و پنهان شده را در محاسبات نادیده می گیرند.

اگر در این مثال بخواهم از تابع SUBTOTAL استفاده کنم دیگر از گزینه AUTOSUM نمی‌توانیم استفاده کنیم!

پس اینجا می‌توانیم به صورت دستی بنویسیم البته یک نکته‌ای وجود دارد ….

اگر محدوده شما فیلتر باشد گزینه AutoSum برای شما تابع SUBTOTAL رو اتوماتیک می‌نویسد.

ولی من چون اینجا چیزی فیلتر نیست پس Alt + = و AutoSum برای من SUBTOTAL رو نخواهد نوشت و خودم باید دستی بنویسم.

مساوی SUBTOTAL پرانتز باز، اینجا میگه چه تابعی رو می‌خوای؟ که من چون جمع رو می‌خوام عدد 9، یا باید 9 رو تایپ کنم یا روی این SUM دبل کلیک کنم تا 9 رو بنویسه،

کاما (,) رو می‌گذارم قسمت دوم می‌گوید Reference یا محدودت کجاست؟ من محدودم این سه تا سلوله، چون یک پرانتز باز داریم، پرانتز آخر رو اگر نبندیم خود اکسل بدون هیچ اروری برای ما پرانتز رو می‌بنده.

خوب اینجا هم که می‌توانیم این تابع را کپی و Paste کنم.

برای محدوده بعدی دوباره مساوی SUBTOTAL ، عدد 9 کاما (,)محدوده و Enter و دوباره درگ می‌کنیمSUBTOTAL رو می‌نویسد

حالا می‌خواهیم همه رایک دفعه بنویسم!!

می‌نویسم مساوی SUBTOTAL، محدوده جمع‌هام رو انتخاب کردم اول، بعد SUBTOTAL رو می‌نویسم، عدد 9 که برای جمع زدن هست، کاما (,) محدودم، انتخاب محدوده اول، Ctrl + Enter، به جای Enter، چون چند تا سلول رو انتخاب کردم، Ctrl + Enter، ببینید دقیقاً SUBTOTAL رو توی هر سه تا سلول نوشت.

حالا اگه بخواهیم جمع بزنیم و از AutoSum استفاده کنیم چه می‌شود؟

محدوده رو اصلا درست تشخیص نداد!!!!

اگرمن بیام این محدوده رو کلاً انتخاب کنم وEnter بزنم، جوابی که به من میده دوبل هست!!!

یعنی دو برابره و دوباره باید تقسیم بر 2 کنم ولی از AutoSum اگر استفاده نکنم و SUBTOTAL دوباره بنویسم

SUBTOTAL، عدد 9 که برای جمع هست، کاما (,) محدودم، همه این محدوده این داده‌ها به اضافه SUBTOTALهایی که نوشتم. Enter رو که بزنم، ببینید جمع درست رو به من میده، چرا؟

چون SUBTOTAL خودش رو در جمع زدن در نظر نمی‌گیره و ما اینجا از تابع SUBTOTAL استفاده کردیم ، و دیگر تقسیم بر 2 هم نیاز نداریم.

اینجا هم می‌توانیم کپی کنیم.

نکته طلایی

یک ویژگی دیگری که تابع SUBTOTAL دارد این است که اگر من اینجا تابع SUBTOTAL استفاده کردم و این محدوده رو انتخاب کردم، اگر محدوده رو فیلتر کنم

تابع SUBTOTAL فقط داده‌هایی که توی فیلتر نمایش داده می‌شود را جمع میزند و مثل SUM نیست.

تابع SUM اینجوریه که اگه شما داده‌ای رو هم فیلتر کنید و پنهان باشه، اون داده هم جمع می‌زند ولی SUBTOTAL فقط داده‌هایی که نمایش داده می‌شود را جمع می‌زند.

جهت مشاهده آموزش ویدئویی تفاوت توابع SUM و SUBTOTAL بر روی لینک زیر کیلیک کرده و آن را داخل پیج ایسنتاگرام مطالب کمیاب در اکسل و حسابداری ( softwaretrain ) مشاهده فرمایید .