دوشنبه, 15 آگوست 2022

آموزش تابع Subtotal در اکسل

در این مطلب می‌خواهیم با نحوه استفاده از تابع subtotal در اکسل آشنا بشیم . ما از تابع subtotal در اکسل برای انجام عملیاتی مانند جمع کردن، میانگین گرفتن و … روی یک محدوده خاص استفاده می‌کنیم. همونطور که میدونید توابعی مانند SUM، AVERAGE و … تنها یک عملیات مشخص را روی یک محدوده(Range) انجام می‌دهند، ولی تابع subtotal این قابلیت رو داره که با استفاده از پارامتری که ما برای آن تعیین می‌کنیم عملیات‌های مختلفی را روی محدوده مدنظر انجام دهد.

به زبان ساده عبارت sub به معنی جزء و بخش می‌باشد و کلمه subtotal به معنی جمع جزء یا جمع هربخش می‌باشد.

تابع subtotal از دو آرگومان پشتیبانی می‌کند:

=Subtotal(operation_code , Range1)

1	=Subtotal(operation_code , Range1)

آرگومان operation_code: کدعملیات
آرگومان Range1: محدوده عملیات

در اینجا operation_code نوع عملیات ریاضی را که می‌خواهید روی محدوده مشخص شده(Range1) انجام دهید، مشخص می‌کند و Range1 به معنای محدوده سلول ها است. در ادامه به بررسی بیشتر این تابع خواهیم پرداخت.

با سلام و احترام. به منظور درک بهتر شما علاقه‌مندان، ویدیو آموزشی با عنوان آموزش تابع Subtotal در اکسل به انتهای مقاله اضافه گردید.

قاعده کلی تابع Subtotal

ساختار(Syntax) تابع Subtotal بصورت زیر است:

= subtotal(function_num , ref1, [ref2] , … )

1	= subtotal(function_num , ref1, [ref2] , … )

تشریح آرگومان های تابع Subtotal:

آرگومان function_num یا شماره تابع: رفتار تابع subtotal توسط این آرگومان کنترل می شود. این آرگومان یک مقدار عددی است و مشخص می کند کدام تابع به کار گرفته شود. در واقع این شماره تابع متعلق به یکی از مجموعه های زیر است :

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

جدول زیر توابع موجود با شماره تابع مربوط به آن ها را نشان می دهد:

آرگومان ref1, [ref2] , … : محدوده مورد نظر برای اعمال subtotal است. توجه داشته باشید که فقط انتخاب اولین محدوده (ref1) اجباری بوده و سایر اختیاری[] می‌باشد.

به منظور درک بهتر شما عزیزان، مثالی از این تابع را در ادامه بررسی خواهیم نمود.

جدول زیر را در نظر بگیرید:

در این جدول لیست محصولات با قیمتهایشان درج شده است. همانطور که می‌دانید، برای نوشتن تابع در اکسل، در نوار Formula Bar و یا در سلول با درج علامت = اقدام به نوشتن عنوان تابع Subtotal خواهیم نمود:

سپس از لیست نمایان شده، تابع مورد نظر را با فشردن کلیدهای جهت نماها از صفحه‌کلید انتخاب خواهیم نمود:

همانطور که در تصویر فوق نیز مشاهده می کنید، لیستی از شماره تابع های موجود برای شما نمایش داده می‌شود. بر روی تابع مورد نظر دو بار کلیک می کنیم و یا با انتخاب تابع، کلید Tab از صفحه کلید را فشار می‌دهیم تا شماره تابع داخل Subtotal درج گردد. در این بخش ما از تابع SUM استفاده نمودیم:

سپس باید محدوده مورد نظر را برای جمع‌زدن(SUM) مشخص کنیم:

درنهایت با فشردن کلید Enter می توان نتیجه را مشاهده کرد:

در ادامه با توجه به جدول داده‌های فوق، محاسباتی گوناگونی را پیاده‌سازی نمودیم:

ریز توضیحات هریک از کدهای فوق:

(کد عملیات = 1) میانگین تمام مقادیر موجود در محدوده F3 تا F7 را محاسبه می‌کند.
(کد عملیات = 7) انحراف معیار تمام مقادیر موجود در محدوده F3 تا F7 را محاسبه می‌کند.
(کد عملیات = 2) تعداد تمام مقادیر موجود در محدوده F3 تا F7 را شمارش می‌کند.
(کد عملیات = 9) جمع مقادیر موجود در محدوده F3 تا F7 را محاسبه می‌کند.

فرق Sum و Subtotal در اکسل

حال که با این تابع آشنا شدید، این سوال پیش میاد که با وجود توابع اصلی(Sum, Average , …) چرا باید از این تابع استفاده کنیم؟در ادامه جواب این سوال رو تو چند ویژگی بررسی خواهیم نمود.

می دانیم که با استفاده از تابع Subtotal و کد عملیات 9 می توان مجموع یک محدوده را محاسبه کرد. اما اکسل قبلاً تابع Sum را برای این کار به شما معرفی نموده است، پس چرا باید از Subtotal استفاده کرد؟ پاسخ به این سوال ساده است.تابع Subtotal دو قابلیت مهم نسبت به سایر توابع سنتی اکسل دارد. این تابع می تواند نتایج را به صورت پویا محاسبه کند.

مثلا اگر در جدولی که داشتیم یک فیلتر در قسمت محصولات اعمال کنیم:

مشاهده می‌کنید که حاصل جمع فرمول Sum تغییری نکرده است اما تابع Subtotal به صورت خودکار محاسبه را دوباره انجام داده و تنها سلول های نمایش داده شده را جمع زده است! که در واقع این ویژگی نادیده گرفتن مقادیر مخفی شده در محاسبات بسیار حایز اهمیت است.

قابلیت دوم این تابع در نادیده گرفتن ردیف‌های پنهان است. در بسیاری از مواقع نیاز به پنهان‌سازی داده های خود را داریم. در صورتی که تابع جمع (Sum) را در این محدوده اعمال کنید، نمی‌تواند این رکوردهای پنهان را نادیده بگیرد از این رو در محاسبات آورده می‌شوند:

ولی تابع Subtotal این توانایی را به شما می دهد که رکوردهای قابل مشاهده را در یک محدوده بشمارید یا جمع کنید. تنها کافیه از کدهای عملیات 101 الی 111 استفاده کنید:

به عنوان مثال جمع اعداد قابل مشاهده (کدعملیات 109):

نکته: ویژگی بعدی این تابع این است که اگر محدوده معرفی شده به تابع Subtotal حاوی فرمولهای Subtotal دیگری باشد، این تابع آن محدوده را در محاسبات خود لحاظ نمی کند.

در ادامه به منظور درک بهتر شما علاقه‌مندان، ویدیویی آموزشی با عنوان آموزش تابع Subtotal در اکسل آماده نمودیم که به یادگیری شما عزیزان کمک بسزایی خواهد کرد:

در پایان امیدواریم مقاله آموزش تابع Subtotal در اکسل مورد تایید و رضایت شما علاقه‌مندان به اکسل قرار گرفته باشد. باتشکر از همراهی شما.