جمع فیلتر در اکسل با ۴ روش کاربردی

۵۲۳۲ بازدید
آخرین به‌روزرسانی: ۲۶ اردیبهشت ۱۴۰۲
زمان مطالعه: ۶ دقیقه
جمع فیلتر در اکسل با ۴ روش کاربردی

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

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

4 روش برای جمع فیلتر در اکسل

در ادامه این مطلب، از جدول زیر برای توضیح مثال‌ها استفاده خواهیم کرد. در این مجموعه داده، ستون‌هایی با سرتیترهای مختلف داریم که قرار است بنا به شروط متفاوت، آن‌ها را فیلتر و عملیات جمع را روی آن‌ها اجرا کنیم.

جدول مثال برای جمع فیلتر در اکسل

استفاده از SUBTOTAL برای جمع ستون‌های فیلتر در اکسل

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

افزودن تابع SUBTOTAL از گزینه AutoSum

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

در ابتدا نیاز است از داده‌های خود یک جدول بسازید و AutoSum را روی آن‌ها اعمال کنید. برای این کار، در زبانه DATA گزینه Filter‌ را بزنید. سپس نماد کوچک فیلتر را روی هر سرتیتر جدول خود خواهید دید.

فیلتر کردن داده‌های جدول

در مرحله بعد می‌خواهیم جدول را بر اساس ستون «محل تولید»، فیلتر یا مرتب‌سازی کنیم. برای این کار، علامت فلش کوچک کنار سرتیتر «محل تولید» را بزنید. سپس در منوی ظاهر شده، تیک تمام گزینه‌های «Text Filter» به غیر از گزینه C را بردارید تا فقط اطلاعات مربوط به این «محل تولید» در جدول نشان داده شوند. سپس گزینه OK را بزنید.

اعمال فیلتر محل تولید روی داده‌های جدول

در نتیجه می‌بینیم که جدول کوچک‌تر شده و فقط داده‌هایی را نشان می‌دهد که مربوط به محل تولید C هستند.

جدول فیلتر شده بر اساس محل تولید

سپس برای انجام جمع فیلتر در اکسل، روی سلول F13 کلیک و در زبانه HOME گزینه AutoSum را بزنید.

تابع subtotal برای جمع فیلتر در اکسل

در این مرحله می‌بینیم که تابع SUBTOTAL در سلول F13 نشان داده می‌شود. برای محاسبه جمع، ستون «ارزش کل» را انتخاب کنید و کلید Enter را فشار دهید.

استفاده از subtotal برای جمع فیلتر

در نهایت، دکمه Enter را بزنید تا نتیجه جمع مانند مثال زیر در سلول F13 نشان داده شود.

محاسبه جمع فیلتر با subtotal

استفاده مستقیم از تابع SUBTOTAL برای جمع فیلتر در اکسل

همان‌طور که در مثال قبل دیدید، تابع SUBTOTAL را به کمک AutoSum فعال کردیم. در ادامه به‌طور مستقیم این تابع را برای جمع فیلتر در اکسل به کار خواهیم بست.

قبل از هر چیز، تمام داده‌ها را انتخاب و با فشردن دکمه‌های Ctrl+T روی کیبورد، آن‌ها را به جدول تبدیل کنید. در پنجره کوچکی که باز می‌شود، تیک گزینه «My table has headers» را بزنید.

ساحت جدول در اکسل

در این مرحله باید فرمول SUBTOTAL را به شکل زیر در سلول نتیجه وارد کنید:‌

1=SUBTOTAL(9,F2:F12)
افزودن فرمول subtotal

بعد از زدن دکمه Enter، مجموع داده‌های سلول F2 تا F12 محاسبه شده و به نمایش درمی‌آید.

جمع سلول‌ها با subtotal قبل از فیلتر

در مرحله بعد جدول خود را بر اساس ستون «تولید کننده» مرتب خواهیم کرد. برای این کار روی فلش کنار سرتیتر «تولید کننده» بزنید. در منوی باز شده، فقط تیک گزینه J را زده و دکمه OK را فشار دهید.

فیلتر جدول بر اساس تولید کننده j

در نتیجه، می‌بینیم که مقدار جمع در سلول F13 بروزسانی شده است و در حال حاضر فقط داده‌های فیلتر شده را جمع می‌کند.

نتیجه جمع فیلتر با subtotal

استفاده از Total row

روش دیگری که برای جمع فیلتر در اکسل استفاده می‌کنیم، ویژگی Total Row است. استفاده از این روش نیز چندان سخت نیست و در ادامه به آموزش آن پرداخته‌ایم:

ابتدا مانند مثال‌های قبلی، داده‌های خود را به جدول تبدیل کنید. در این مرحله کافی است با انتخاب کل داده‌ها، ترکیب کلیدهای Ctrl+T را روی صفحه کلید فشار دهید. سپس یکی از سلول‌های جدول را انتخاب کرده و در زبانه Design، قسمت «Table Style Options»، تیک گزینه Total Row را بزنید.

انتخاب گزینه total row

در این مرحله خواهید دید که ردیف جدیدی زیر جدول شما اضافه شده است. اولین سلول این ردیف (یعنی A2) گزینه Total است و در آخرین سلول آن (یعنی F13)، مقادیر ستون «ارزش کل» با هم جمع شده‌اند. سپس فلش کنار سرتیتر «تولید کننده» را زده و تیک تمام گزینه‌ها به غیر از C را بردارید.

بعد از زدن دکمه OK خواهید دید که فقط داده‌های مربوط به تولید کننده C قابل مشاهده هستند و جمع ستون «ارزش کل» هم تغییر کرده و تنها جمع مقادیر مربوط به تولید کننده C را نشان می‌دهد.

جمع فیلتر در اکسل با Total row

استفاده از تابع AGGREGATE برای جمع فیلتر در اکسل

تابع AGGREGATE هم می‌تواند مجموع ستون‌ها را بعد از فیلتر شدن به دست آورد. قبل از هر چیز، برای اینکه بفهمیم چرا به تابع جمع بندی AGGREGATE در اکسل نیاز داریم، باید بدانید که تابع SUM در شرایط اعمال فیلتر روی داده‌ها به درستی کار نمی‌کند. در ادامه با ذکر مثال، به توضیح این قضیه پرداخته‌ایم:

ابتدا تمام داده‌ها را انتخاب کرده و دکمه‌های Ctrl+T را فشار دهید تا تبدیل به جدول شوند. سپس ستون «تولید کننده» را به شکلی فیلتر کنید که فقط داده‌های تولید کننده J نشان داده شوند. سپس تابع SUM را در سلول نتیجه وارد کرده و ستون «ارزش کل» را مانند مثال زیر، به‌عنوان آرگومان آرایه در آن وارد کنید.

تابع SUM در اکسل

در نهایت خواهید دید که نتیجه جمع، فقط مربوط به داده‌های فیلتر شده نیست. در عوض تمام سلول‌های محدوده F2 تا F13 محاسبه شده‌اند. همچنین مقدار جمع با آنچه در پیش‌نمایش اکسل (پایین صفحه) نشان داده می‌شود نیز متفاوت است. برای رفع این مشکل از تابع AGGREGATE کمک می‌گیریم.

 

برای افزودن تابع AGGREGATE، ابتدا داده‌ها را بر اساس تولید کننده C فیلتر و سپس فرمول تابع را در سلول F13 وارد کنید. به جای اولین آرگومان این فرمول باید عدد «9» را وارد کنید یا گزینه SUM را در منوی ظاهر شده برگزینید.

نوشتن فرمول تابع AGGREGATE در اکسل

سپس عدد «5» را وارد کرده یا گزینه «Ignore hidden rows» را بزنید.

آرگومان دوم تابع AGGREGATE

در مرحله بعد، مجموعه داده‌های ستون «ارزش کل» را برای محاسبه مجموع، انتخاب کنید.

آرگومان سوم تابع AGGREGATE

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

 

توجه داشته باشید که:

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

افزودن کد VBA‌ برای محاسبه جمع فیلتر در اکسل

توابع نقش مهمی در اکسل داشته و در کار با داده‌ها به کاربران کمک می‌کنند. مثلا تابع SUMIF در اکسل یکی از پر کاربردترین توابع است که برای جمع در شرایط خاص مورد استفاده قرار می‌گیرد. با این حال در برخی شرایط نیاز است که توابع جدید تعریف کنیم. این کار را می‌شود به کمک ماکروهای VBA در اکسل انجام داد.

زبان ماکرونویسی VBA‌ روشی برای افزودن کدهای Visual Basic به برگه‌های اکسل است. استفاده از یک ماکروی VBA‌ ساده می‌تواند سرعت استخراج بخشی از متن به عنوان رشته را بسیار کمتر کند. برای افزودن یک ماژول به کمک VBA مراحل زیر را طی کنید:

ابتدا کلیدهای Alt+F11 را روی صفحه کلید فشار دهید. سپس در صفحه باز شده، از زبانه Insert گزینه Module را بزنید.

افزودن ماژول vba در اکسل

سپس کد زیر را کپی کرده و در پنجره ماژول جای‌گذاری کنید.

1Function SumFilteredData(myRange As Range)
2    For Each myCell In myRange
3        If myCell.Rows.Hidden = False And myCell.Columns.Hidden = False Then
4            Total = Total + myCell.Value
5        End If
6    Next
7    SumFilteredData = Total
8End Function

بعد از بستن پنجره ماژول، تمام داده‌ها را در برگه اکسل انتخاب کرده و دکمه‌های Ctrl+T را بزنید تا به جدول تبدیل شوند. در مرحله بعد، فرمول تابعی که در ماژول ساختید را مانند زیر در سلول F13 وارد کنید:

1=SumFilteredData(F2:F12)
افزودن تابع ماژول VBA در اکسل

با این کار، مجموع مقادیر ستون «ارزش کل» در سلول F13 نشان داده می‌شود. حال روی فلش کنار سرتیتر «تولید کننده» کلیک کنید و در منوی باز شده، گزینه‌های S ،K و V را تیک بزنید.

بعد از فشردن گزینه OK، خواهید دید که مقدار سلول F13 بروزرسانی شده و این بار فقط مجموع داده‌های فیلتر شده را نشان می‌دهد. این عدد با مقدار جمع در گزینه پیش‌نمایش اکسل هم یکسان است، یعنی تابع جدید برای محاسبه جمع فیلتر در اکسل درست کار می‌کند.

نتیجه تابع ماژول VBA برای جمع فیلتر

سخن نهایی

در این مقاله به آموزش جمع فیلتر در اکسل پرداختیم. با 4 روش معرفی شده، می‌توانید به سادگی جمع فیلتر در اکسل را محاسبه کرده و با داده‌های خود تعامل داشته باشید. در روش اول، تابع SUBTOTAL را به کمک ویژگی AutoSum‌ فراخوانی کردیم. سپس از Total Row کمک گرفتیم، بعد به سراغ تابع AGGREGATE رفتیم و در نهایت به کمک VBA تابعی جدید برای جمع فیلتر در اکسل تولید کردیم.

مطلبی که در بالا مطالعه کردید بخشی از مجموعه مطالب «چگونه در اکسل جمع کنیم؟» است. در ادامه، می‌توانید فهرست این مطالب را ببینید:

بر اساس رای ۹ نفر
آیا این مطلب برای شما مفید بود؟
اگر بازخوردی درباره این مطلب دارید یا پرسشی دارید که بدون پاسخ مانده است، آن را از طریق بخش نظرات مطرح کنید.
منابع:
excelhowexceldemy
نظر شما چیست؟

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