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

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

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

• مطالب پیشنهادی برای مطالعه:
  • جمع زدن اتوماتیک در اکسل – توضیح کامل جمع خودکار
  • آموزش جدول در اکسل – نحوه درست کردن + تصاویر مراحل
  • چگونه اکسل یاد بگیریم؟ – راهنمای یادگیری آسان و سریع
  • جمع کردن اعداد در اکسل — آموزش سریع روش ها و فرمول ها + فیلم آموزشی
  • نحوه تیک زدن در اکسل و اضافه کردن چک باکس — راهنمای تصویری (+ فیلم)

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

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

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

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

فیلم آموزش اکسل Microsoft Excel 2013 در فرادرس

کلیک کنید

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

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

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

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

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

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

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

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

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

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

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

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

1=SUBTOTAL(9,F2:F12)

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

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

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

استفاده از Total row

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

فیلم آموزش اکسل – ابزارهای کاربردی Excel در فرادرس

کلیک کنید

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

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

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

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)

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

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

سخن نهایی

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