تابع subtotal در اکسل | 0 تا 100 اکسل در پرشین فای
تابع subtotal
sub به معنی جزء و total به معنی جمع کل است.
زمانی که با جداول در اکسل کار می کنید هر تغییری که در شرط فیلتر اعمال می کنید، باعث می شود که میزان سطرهای نمایش داده شده، کم یا زیاد شود
حال اگر از توابعی مانند Sum ,Averege ,Min ,Max ,Var و … استفاده کرده باشیم، اکسل بصورت خودکار سطر های مخفی را هم در محاسبات اعمال می کند، اما اگر بخواهیم فقط جمع سطرهای نمایش داده شده را محاسبه نماییم، یا فقط میانگین سطرهای در حال نمایش را مشاهده نماییم، از تابع Subtotal استفاده می کنیم.
نکته: تابع Sum محدودیت های دارد که در ادامه بحث با ذکر آنها به راه حل آن نیز می پردازیم.
1 : اگرحتی یک مقدار خطا بین سلول ها قرار گیرد نتیجه تابع نیز خطا می باشد.
2 : اگر سطری یا سلولی را Hide کنید، تابع Sum آنها را نیز در نظر می گیرد.
3 : هنگام فیلتر کردن (البته اگر از جدول(Table) و خصوصیت Total Row آن استفاده نمی کنید) تابع Sum سلول های فیلتر شده را محاسبه می نماید.
4 : نکات فوق در مورد توابع دیگر نیز صدق می کند، این مشکل با تابع Subtotal حل می شود.
نکته : اگر دو یا چند سلول را Merge کرده باشید، نتیجه تابع به تمام سلول های موجود در سلول های Merge شده تعمیم داده می شود.
آرگومان های تابع subtotal
تابع Subtotal دارای دو آرگومان می باشد.
اولین آرگومان Function_Num مشخص کننده تابعی است که قصد استفاده از آن را دارید،(در جدول بعد نمایش داده شده) و ما در این کادر فقط شماره تابع مورد نظر را درج می کنیم.
دومین آرگومان Ref1;Ref2;Ref3,… می باشد،که می توانید در آنها بصورت تک تک یا تجمعی آدرس سلول های مورد نظر را وارد نمایید.
نکته : سعی کنید Sort کردن داده ها را در اکسل بصورت پیش نیاز تمرین کنید، این موضوع برای کارکرد صحیح توابع امری ضروریست.
نکته : این تابع به خودش حساس است، اگر آدرس سلول هایی را بدهیم که برخی از آنها حاوی فرمول Subtotal باشد، آن سلول ها را محاسبه نمی کند.
نکته : این تابع به فیلتر حساس بوده و مقادیر فیلتر شده را محاسبه نمی کند، ولی در مورد سلول های Hide شده، می توان تعیین نمود که محاسبه شوند یا خیر. در تصویر فوق توابع از (1 تا 11 ) مقادیر Hide شده را محاسبه می کند، و از (101 تا 111) به مقادیر Hide شده حساس می باشد، و آنها را محاسبه نمی کند.
مثال از تابع subtotal
مثال : فرض کنید شما به عنوان یک حسابرس یا مدیر مالی شرکتی، با توجه به خرید و فروش بالای شرکت و اجرای یک کنترل داخلی مناسب و قابل اتکاء و پیگیری خط مشی شرکت (در مورد مقدار و درصد سود فروش در روز به نسبت هزینه های متعلقه) و کنترل کارایی بخش فروش، با پیش بینی های قبلی، قصد دارید:
1 : مقدار فروش روزانه را به تفکیک برای هر کالا محاسبه و با مقدار موجودی انبار چک کنید.
2 : میانگین قیمت فروش هر کالا را در روز محسبه نمایید.
3 : مقدار فروش کل و درصد سود ناخالص را محاسبه نمایید.
نکته: در مورد تفکیک فروش روزانه به ترتیب تاریخ، در مبحث تابع Mid توضیح کامل داده شد، و در مورد محاسبات مدیریتی نیز در پروژه عملی(پیوست کتاب ژنتیک حسابداری و حسابرسی مبتنی بر اکسل) به تفضیل بحث خواهد شد، در این مثال فقط جنبه آموزشی تابع Subtotal مورد توجه قرار گرفته.
1 : داده ها را با روش های گفته شده وارد اکسل می کنیم.
2 : پیش نیاز استفاده صحیح از این تابع،مرتب کردن (Sort) آن بر اساس ستونی است که تابع بر اساس آن محاسبه را انجم می دهد. جدول را بر اساس نام کالا مرتب می کنیم.
Home -> Editing -> Sort & Filter -> Filter
3 : در سلول E2 فرمول =B2*D2 را نوشته.و به سلول های زیرین کپی می کنیم.(جمع مبلغ فروش)
4 : در سلول F2 فرمول 1- =(D2/C2)را نوشته، و به سلول های زیرین کپی می کنیم.(درصد سود فروش)
نکته : فرمت ستون F را در پنجره تنظیمات و قسمت custom در کادر Type بدین شکل تنظیم نمایید. (0.0%)
5 : فرمول سلول A10 =Subtotal(3;A2:A9)
محاسبه مجموع خانه های حاوی داده را محاسبه می کند.(تعداد دفعات فاکتور شده).
6 : فرمول سلول B10 =Subtotal(9;B2:B9) محاسبه مجموع فروش
7 : فرمول سلول D10 =Subtotal(1;D2:D9) محاسبه میانگین فی فروش
8 : فرمول سلول E10 =Subtotal(9;E2:E9) محاسبه مبلغ کل فروش
9 : فرمول سلول F10 =Subtotal(1;F2:F9) محاسبه میانگین درصد سود ناخالص فروش
10 : برای اینکه محاسبات فقط در مورد کالای AA انجام شود، روی مثلث کنار نام کالا کلیک و ابتدا تیک تمام گزینه ها را برداشته، و گزینه AA را تیک دار می کنیم.برای مابقی کالاها نیز به همین ترتیب عمل می کنیم.
اکسل بصورت پویا تغییرات را در آخرین سطر (سطری که فرمول نویسی در آن انجام شده) اعمال می کند.
مطلب را با دوستان خود به اشتراک بگذارید
همچنین مطالعه کنید : تابع round در اکسل | گرد کردن اعداد
