تابع SUBTOTAL در برگه‌های Google به شما امکان می‌دهد تا به سرعت مجموع، میانگین‌ها و غیره را برای محدوده خاصی از سلول‌ها نمایش دهید. در اینجا نحوه استفاده از آن آورده شده است.
هنگام کار بر روی صفحه‌گسترده بزرگ در Google Sheets، ممکن است بخواهید به صورت دوره‌ای تعداد فرعی داده‌های صفحه‌گسترده خود را بررسی کنید. انجام این کار به شما کمک می کند تا جریان داده ها را بهتر درک کنید و همچنین جستجوی خطاها را آسان تر می کند.
استفاده از تابع SUBTOTAL در Google Sheets یکی از ساده ترین راه ها برای انجام این کار است. بیایید نگاهی به نحو فرمول و نحوه استفاده از آن در صفحه گسترده خود بیاندازیم. ما همچنین به یک مثال گام به گام می پردازیم تا کار را برای شما آسان تر کنیم.
چرا به جای SUM از SUBTOTAL استفاده کنیم؟
تابع SUM در برگه‌های Google می‌تواند باعث شمارش مضاعف شود. با استفاده از تابع SUBTOTAL به راحتی می توانید از این امر جلوگیری کنید. اگرچه برای مبتدیان چندان مناسب نیست و قوانین جدیدی را ارائه می دهد که ممکن است یادگیری آنها برای یک مبتدی دشوار باشد، SUBTOTAL یک فرمول بسیار قدرتمند است که در زرادخانه شما وجود دارد.
همچنین به صورت پویا خود را تنظیم می‌کند و دقیق‌تر می‌کند و می‌تواند معیارهای کاری را هنگام ساخت صفحه‌گسترده ارائه دهد. همچنین می‌توانید از یک کد عددی برای یافتن مجموع‌های فرعی با چندین تابع مختلف، به جای مجموع یک ستون استفاده کنید.
نحو برای SUBTOTAL در Google Sheets
در اینجا نحو فرمول SUBTOTAL در Sheets آمده است:
=SUBTOTAL(کد، محدوده 1، …)
فرمول حداقل به دو پارامتر اول نیاز دارد تا به درستی کار کند. بسته به عملکردی که می خواهید انجام دهید می توانید محدوده های اضافی اضافه کنید. در اینجا پارامترهای مورد استفاده برای این فرمول آمده است:
- کد: این کد عددی برای تابعی است که می خواهید استفاده کنید. لیست کد در زیر نوشته شده است.
- range1: این اولین محدوده ای است که می خواهید جمع فرعی آن را محاسبه کنید.
می توانید چندین محدوده اضافه کنید تا جمع های فرعی بعد از پارامتر range1 محاسبه شود.
کنترل عملکرد در Google Sheets چیست؟
ویژگی کنترل عملکرد در Google Sheets به شما امکان می دهد مشخص کنید که می خواهید یک عملکرد خاص چگونه رفتار کند. اگر به سادگی توضیح داده شود، اساساً یک تابع فرعی در داخل یک تابع است.
در تابع SUBTOTAL، در مجموع 11 کنترل تابع وجود دارد که شما از آنها به عنوان آرگومان کد در نحو استفاده می کنید. این کد به فرمول SUBTOTAL می گوید که کدام محاسبه را باید انجام دهد. ممکن است کمی گیج کننده به نظر برسد، اما برای کمک به درک بهتر شما بیشتر به عمق خواهیم پرداخت.
شما مجبور نیستید لیست را حفظ کنید، مگر اینکه فردی باشید که اغلب از این تابع استفاده می کند. با خیال راحت این مقاله را نشانه گذاری کنید و زمانی که نیاز دارید بدانید از کدام کد استفاده کنید به آن بازگردید، یا کدهای کنترلی زیر را کپی و جایگذاری کنید تا آنها را در جایی ذخیره کنید.
کدهای کنترل عملکرد SUBTOTAL
در اینجا لیست کدهای پارامتر اول آمده است:
- 1 برای AVERAGE است
- 2 برای COUNT است
- 3 برای COUNTA است
- 4 برای MAX است
- 5 برای MIN است
- 6 برای PRODUCT است
- 7 برای STDEV است
- 8 برای STDEVP است
- 9 برای SUM است
- 10 برای VAR است
- 11 برای VARP است
همانطور که در بالا می بینید، فرمول SUBTOTAL در مجموع از 11 تابع یکپارچه دیگر استفاده می کند. بنابراین، می‌توانید برای هر یک از این محاسبات، یک زیرمجموع کارآمد پیدا کنید.
تغییر کد همچنین می تواند به فرمول بگوید که سلول های پنهان را نادیده بگیرد. می توانید این کار را با نوشتن کد در 100 ثانیه انجام دهید. بنابراین اگر می‌خواهید از تابع AVERAGE در SUBTOTAL استفاده کنید در حالی که سلول‌های پنهان را نادیده می‌گیرید، آن را به صورت 101 بنویسید. بنابراین یک فرمول کامل با استفاده از محدوده A1:A15 خواهد بود:
=SUBTOTAL(101,A1:A15)
اگر می‌خواهید از تابع COUNT در SUBTOTAL استفاده کنید و سلول‌های پنهان را نادیده بگیرید، از 102، 103 برای COUNTA و غیره استفاده می‌کنید.
مثال تابع SUBTOTAL
در این مثال مجموعه ای از داده های فروش داریم. همچنین برای هر سه ماهه سال زیربخش هایی وجود دارد که شامل مجموع فروش های انجام شده در سه ماه می شود. بیایید ببینیم چگونه می توانید از این تابع در اینجا استفاده کنید.
برای استفاده از فرمول SUBTOTAL در Google Sheets این مراحل را دنبال کنید:
- روی سلولی که می خواهید فرمول را وارد کنید کلیک کنید. در این حالت سلول B5 است.
- شروع به تایپ قسمت اول فرمول کنید که =SUBTOTAL(. وقتی شروع به تایپ می کنید، Google Sheets سعی می کند فرمول را به صورت خودکار پر کند. می توانید از این به نفع خود نیز استفاده کنید.
- حالا پارامتر اول را تایپ کنید. یک عدد از لیست کدهای قسمت نحو انتخاب کنید. در اینجا می خواهیم محتویات سلول ها را اضافه کنیم، بنابراین عدد 9 را می نویسیم که SUM است.
- برای جداسازی پارامترها یک کاما اضافه کنید.
- اکنون، در محدوده سلولی اضافه کنید. در اینجا، محدوده سلول B2:B4 است. به جای آن می توانید از ماوس خود برای برجسته کردن محدوده استفاده کنید.
- برای تکمیل فرمول یک براکت بسته قرار دهید.
- Enter را فشار دهید.
برای یافتن مجموع فرعی برای بقیه ربع ها، مراحل همان است که در بالا ذکر شد، اما با محدوده سلولی متفاوت به عنوان آرگومان range1.
وقتی به Total می رسید یک چیز جالب باید بدانید. اگر از فرمول SUM استفاده کرده باشید و محدوده سلولی B2:B17 را وارد کرده باشید، فرمول مقادیر سلول های Q1، Q2، Q3 و Q4 را نیز اضافه می کند.
ما چنین چیزی را نمی‌خواهیم، زیرا انجام این کار با شمارش مضاعف داده‌ها، مقادیر را در Total خراب می‌کند. بنابراین، می‌خواهیم از تابع SUBTOTAL در آن سلول‌ها نیز استفاده کنیم، زیرا سایر SUBTOTALS در برگه را نادیده می‌گیرد.
در تصویر مثال بالا، تابع SUBTOTAL را در سلول های Total B18، C18 و D18 اضافه کردیم. این تابع مقادیر موجود در سلول‌های Q1، Q2، Q3 و Q4 را نادیده گرفت تا یک کل کلی دقیق ارائه دهد.
چند نکته در مورد عملکرد SUBTOTAL
- سلول های پنهان شده به دلیل معیارهای فیلتر، صرف نظر از پارامتر کد استفاده شده، در SUBTOTAL گنجانده نمی شوند.
- سلول‌های داخل محدوده سلولی مشخص شده که حاوی ارجاع به تابع SUBTOTAL دیگر هستند، همیشه نادیده گرفته می‌شوند. این برای جلوگیری از وقوع دوبار شمارش است.
- از SUBTOTAL می توان برای تجزیه و تحلیل سریع مجموعه های مختلف داده استفاده کرد و می توانید از چندین آرگومان کد مختلف در یک صفحه استفاده کنید.
- می‌توانید از SUBTOTAL برای ساخت داشبوردهای پویا استفاده کنید که در آن سلول به سلول یا محدوده سلولی دیگر اشاره دارد. هنگامی که با اعتبارسنجی داده‌ها در برگه‌های Google بر اساس فهرست‌ها ترکیب می‌شود، می‌تواند به یک لیست کشویی تبدیل شود که کل داشبورد را به‌روزرسانی می‌کند.
به تمرین عملکرد SUBTOTAL ادامه دهید
سعی کنید هر بار که فرصت پیدا کردید از این عملکرد استفاده کنید. در حالی که شما اغلب از 09 (SUM) به عنوان آرگومان کد استفاده می کنید، برخی دیگر نیز گاهی مطرح می شوند.
هنگامی که به کنترل‌های تابع عادت کردید، عملکرد SUBTOTAL در Google Sheets کمی آسان‌تر می‌شود و از آن به عنوان بخشی از طیف گسترده فرمول‌های قدرتمند خود برای ساخت صفحات گسترده کاربردی استفاده خواهید کرد.