سبد خرید
0

هیچ محصولی در سبد خرید نیست.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

تابع Subtotal در اکسل و نکات جالب آن

تابع Subtotal
۴.۸/۵ - (۱۷ امتیاز)

تابع Subtotal در اکسل و کاربردهای جالب آن

یکی از راه های رایج و پر استفاده برای نمایش داده ها در اکسل، دسته بندی کردن داده ها از طریق Hide/Unhide و یا Filter هست. برای دسته بندی کردن داده ها و انجام عملیات بر روی آنها، ابزاری رو معرفی کردیم به نام ابزار Subtotal. و گفتیم که این ابزار ترکیبی است از تابع Subtotal و گروه بندی دستی داده ها (که در واقع همون Hide/Unhide کردن راحت تر و شکیل تر هست). در این مقاله میخوایم به آموزش تابع Subtotal بپردازیم که ۳ ویژگی و کاربرد منحصر بفرد داره. یکی از کاربردهای این تابع انجام محاسبات روی داده های فیلتر شده هست که در ادامه توضیح داده میشه.

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

Function_Num: عددی است از ۱ تا ۱۱ و ۱۰۱ تا ۱۱۱ که هر کدوم یک عمل رو انجام میده.

Ref1,[Ref2],…: محدوده ای که میخوایم عملیات مورد نظر روی اون انجام بشه.

جدول زیر نمایش دهنده حالت های مختلف آرگومان اول هست:

عملکرد تابع function_num
(داده های Hide شده رو در محاسبات در نظر نمیگیره)
function_num
(داده های Hide شده رو در محاسبات در نظر میگیره)
میانگین AVERAGE ۱ ۱۰۱
شمارش اعداد COUNT ۲ ۱۰۲
شمارش سلول های پر COUNTA ۳ ۱۰۳
ماکزیمم MAX ۴ ۱۰۴
مینیمم MIN ۵ ۱۰۵
ضرب PRODUCT ۶ ۱۰۶
انحراف معیار نمونه STDEV ۷ ۱۰۷
انحراف معیار جامعه STDEVP ۸ ۱۰۸
جمع SUM ۹ ۱۰۹
واریانس VAR ۱۰ ۱۱۰
واریانس جامعه VARP ۱۱ ۱۱۱

این دو سری آرگومان در خصوص داده های:

  • فیلتر شده یکسان عمل میکنن و در محاسبات خودشون داده های پنهان شده رو حساب نمیکنن.
  • Hide شده متفاوت عمل میکنن. ۱-۱۱ داده های پنهان شده در نظر میگیره و ۱۰۱-۱۱۱ داده های پنهان شده رو در محاسبات در نظر نمیگیره.

مثال اول:

در مثال زیر (شکل ۱) میخوایم وقتی فیلتر میکنیم روی تیم ۱، جمع فروش اون تیم رو ببینیم. همونطور که در شکل ۱ میبینید تابع Sum اصلا به فیلتر حساس نیست و همه داده ها رو جمع میزنه. پس باید از تابع Subtotal استفاده کنیم. که با توجه به جدول بالا، عدد ۹ یا ۱۰۹ جمع رو برای ما محاسبه میکنه.

تابع Subtotal - جمع داده های فیلتر شده با Subtotal

شکل ۱- تابع Subtotal – جمع داده های فیلتر شده با Subtotal

لینک نکات پیشرفته در فیلتر رو هم مشاهده کنید.

مثال دوم:

میخوایم جمع فروش در هر ماه به تفیکیک ببینیم. برای این کار یا بصورت دستی گروه بندی میکنیم یا از ابزار Subtotal استفاده میکنیم.

تابع Subtotal - جمع فروش بر اساس ماه

شکل ۲- تابع Subtotal – جمع فروش بر اساس ماه

اگر از ابزار Subtotal استفاده کنید، خودش فرمول نویسی رو هم انجام میده. اما اگه از گروه بندی دستی استفاده کنید باید فرمول نویسی رو خودتون انجام بدید. در هر صورت نهایتا خروجی مشابه شکل ۲ باید باشه.

تا اینجا دو ویژگی این تابع رو توضیح دادیم که در خصوص داده های Filter/Hide شده بود. حالا میخوایم ویژگی سوم این تابع رو توضیح بدی:

همونطور که در شکل ۲ می بینید در قسمت Grand Total که جمع کل فروش رو نمایش میده، Subtotal روی کل ستون فروش اعمال شده. بعبارتی انتظار داریم که در مجموع، دوبرابر نشون داده بشه چرا که یکی بار خود داده های فروش، یکبار هم جمع فروش در هر ماه. اما نکته مهم در خصوص تابع Subtotal این هست که این تابع سلول های حاوی تابع Subtotal رو در محاسبات در نظر نمیگیره. بعبارتی در مثال شکل ۲، سلول های C4, C8, C12, C16, C20 که جمع هر ماه رو نشون میدن، در محاسبه جمع کل در نظر گرفته نشده اند.

ویژگی تابع Subtotal –در نظر نگرفتن سلول های حاوی این تابع

شکل ۳- ویژگی تابع Subtotal –در نظر نگرفتن سلول های حاوی این تابع

همونطور که در شکل ۳ می بینید، اگر بجای Subtotal از تابع Sum استفاده میشد، جمع کل دو برابر میشد. چرا؟

چون یک بار خود داده های مربوط به ماه ها رو جمع میکرد و یکبار سلولهایی که جمع ماه ها هستند. پس این ویژگی تابع Subtotal باعث میشه محاسبات براحتی و با درستی انجام بشه.

با تابع Subtotal و منطق و روش کار اون آشنا شدیم. تابع قدرتمندتر با ویژگی های بیشتری از اکسل ۲۰۱۰ به بعد اضافه شده به نام Aggregate که منطق و نوع کارش مثل Subtotal هست با این تفاوت که هم توابع بیشتری رو ارائه میده و هم حالت های مختلف اینکه این توابع روی چه داده هایی اعمال بشه.

یکی از کاربردهای جالب دیگه این تابع شماره گذاری برای محدود های فیلتر شده هست که حتما بهتون پیشنهاد میکنم حل مسئله شماره گذاری خودکار در اکسل رو ببینید.

دانلود فایل این آموزش


توجه: جهت دانلود فایل حتما در حساب کاربری خود وارد شوید.
کلیدواژه : تابع Subtotalمتوسط
133

من سامان چراغی هستم. دانش آموخته مقطع فوق لیسانس دانشگاه تربیت مدرس در رشته مهندسی صنایع. از سال 1388 اکسل و برنامه نویسی VBA رو به صورت حرفه ای شروع کردم.

دیدگاه کاربران
  • Aligol ۱۴ دی ۱۴۰۱ / ۹:۱۵ ق٫ظ

    سلام،
    توضیحات دوم هم واضح نبود؟ 🤔
    “توضیح سوم”
    نمیدونم چطوری بگم ولی سعی میکنم ساده بگم که شفاف باشه.
    صورت حساب مشتری رو تصور کنید،
    با تفاوت اینکه فاکتور همه مشتری ها در یک شیت باشند و وقتی نام یک مشتری رو فیلتر می کنیم فقط وضعیت حساب اون مشتری رو نشون بده و مانده حساب رو بصورت ردیف به ردیف نشون بده،
    برای اینکه اگر نام مشتری و شماره فاکتور رو فیلتر کردیم نشون بده که اون فاکتور صفر هست یا مانده داره.
    یک ستون قراره مانده حساب رو ردیف به ردیف در کل شیت نشون بده و بعد فیلتر کردن هم درست نشون بده.
    مشکل من در بعد از فیلتر کردن هست که مانده رو اشتباه نشون میده.
    امیدوارم این بار واضح بوده باشه 🤕

    • آواتار
      حسنا خاکزاد ۱۸ دی ۱۴۰۱ / ۱۰:۴۵ ب٫ظ

      پیشنهاد میکنم دنبال خروجی گرفتن با فیلتر نباشید!
      خروجی مورد نظر رو با ابزارها و توابع جستجو از دیتابیس بکشید بیرون و محاسبات رو بر اساس خروجی انجام بدید
      چون اگر مسئله با فیلتر حل میشد همون subtotal ج میداد

  • Aligol ۱۹ آذر ۱۴۰۱ / ۱۲:۵۸ ب٫ظ

    *ممنون از پاسخگویی شما*
    ۹ ستون با قابلیت فیلتر دارم که فقط ۳ ستون H و i و J عدد هستند، ستون مد نظر ما برای درج فرمول ‘J’ می باشد(آخرین ستون)،
    بطور مثال میخوام سلول J50 حاصل زیر رو نشون بده،
    J50 = (H50 + i50) – J49 *یا* J50 = J49 + H50 + i50
    (منظور از J49 سلول قبلی J50 میباشد نه خود J49)
    تنها مشکل من سلول بالای سلول J50 هست که وقتی فیلتر میکنم، سلول بالای J50 تغییر میکند و حاصل اشتباه میشه،
    میخوام که سلول بالایی J50 هرچی که بود فرمول اون رو در نظر بگیره، (J49 یا J35 یا J20 یا هرچی)
    (شبیه مانده حساب لحظه ای حساب مشتری)
    *تشکر از شما*

    • آواتار
      حسنا خاکزاد ۲۴ دی ۱۴۰۱ / ۲:۴۸ ب٫ظ

      درود بر شما
      فیلتر راه مناسبی برای گزارشگیری نیست و اینطوری خیلی کارتون سخت میشه
      بهتره برید سراغ روش های دیگه گزارشگیری
      چون برای فیلتر، توابع محدودی وجود داره و دستتون خیلی باز نیست

  • Aligol ۵ آذر ۱۴۰۱ / ۱۰:۵۷ ق٫ظ

    ممنون که حدود ۲ هفته گذشته و سوال من رو منتشر نکردید و جواب هم ندادید،
    از سایت به این خوبی انتظار نمیرفت.

  • Aligol ۲۴ آبان ۱۴۰۱ / ۵:۱۰ ب٫ظ

    سلام وقت شما بخیر
    *** سوالی داشتم که چند روزه نتونستم جوابی براش پیدا کنم،
    جدولی با قابلیت فیلتر دارم که جمع سلول ستون های H و i را با استفاده از تابع Subtotal در سلولهای ستون J نمایش میده،
    میخواستم سلول H5 با i5 جمع بشه و از J4 کم بشه و نتیجه در J5 نمایش داده بشه.
    وقتی فیلتر میکنم نتیجه صحیح نیست!

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

    • آواتار
      حسنا خاکزاد ۱۵ آذر ۱۴۰۱ / ۲:۲۹ ب٫ظ

      درود بر شما
      سوالتون اصلا واضح نیست
      باید ساختار دیتا مشخص باشه
      هدف شما مشخص باشه
      بهتره سوال اصلی رو توضیح بدید (نه راه حل) که کمکی بکنه به شرح موضوع

  • 777jalali ۱۲ اسفند ۱۳۹۹ / ۱۱:۵۸ ب٫ظ

    سلام من یک تیبل دارم می خواستم با انجام فیلتر تعداد سلول های غیر تکراری یک ستون شمارش بشه ( حساس باشه به فیلتری که کردم )
    این کار شدنی هست

    • سامان چراغی ۱۵ اسفند ۱۳۹۹ / ۰:۰۱ ق٫ظ

      درود، میتونید اول با استفاده از Conditional Formatting سلول های Unique رو رنگی کنید (تنظیمات Duplicate Values رو روی Unique بذارید)، بعد با استفاده از Filter by Color رنگی که در قسمت قبل مشخص کردید رو فیلتر و نهایتا با انتخاب همه سلول ها تعداد آنها رو در Status Bar ببینید.

  • کیارش پولادی ۲۴ شهریور ۱۳۹۸ / ۶:۰۶ ب٫ظ

    سلام
    من میخواستم تو اکسل وقتی در یک سلول حرف O یا حرف L رو تایپ کنم در سلول مجاورش مثلا شکل دایره یا L رو با اشکال shape ترسیم کنه.
    چطوری میتونم اینکار رو انجام بدم؟

    • آواتار
      حسنا خاکزاد ۲۷ شهریور ۱۳۹۸ / ۱۲:۴۱ ب٫ظ

      درود بر شما
      اگ رمیخواید با فرمول اینکار و بکنید و راحت تر باشید
      بهتره از symbol استفاده کنید بجای shape

  • مهسا ۲۶ خرداد ۱۳۹۸ / ۸:۵۸ ق٫ظ

    خیلی کاربردی و خوب بود. ممنون. و صد بار دیگه ممنون بابت اینکه فرمول درست تایپ شده و مثل بعضی سایت های دیگه آدم رو سردرگم نمیکنید

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان