جمع در اکسل با شرط رنگ سلول – آموزش تصویری و ساده

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

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

جمع در اکسل با شرط رنگ سلول به کمک فیلتر و SUBTOTAL

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

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

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

کلیک کنید

به همین منظور، فرمول زیر را در سلول نتیجه وارد می‌کنیم:

1=SUBTOTAL(9,B2:B11)

در این فرمول، عدد 9 را به جای آرگومان اول وارد کرده‌ایم. این عدد به تابع Subtotal می‌گوید که می‌خواهیم از تابع SUM برای جمع کردن داده‌های منتخب استفاده کنیم. همان‌طور که می‌بینید، تابع SUM را به طور مستقیم ننوشته‌ایم، چون وقتی سلول‌ها را بر حسب شرط خاصی (در این مثال، رنگ) فیلتر می‌کنیم، تابع Subtotal برای جمع فیلتر در اکسل استفاده می‌شود و فقط سلول‌های قابل مشاهده را با هم جمع می‌کند. در حالی که تابع SUM، مجموع تمام سلول‌ها را محاسبه خواهد کرد.

حالا که تابع Subtotal را نوشتیم، مراحل زیر را در پیش می‌گیریم تا جمع براساس شرط رنگ محاسبه شود:

• سلولی را در صفحه خود انتخاب می‌کنیم و به زبانه Data می‌رویم.

• سپس در بخش Sort & Filter، روی نماد Filter می‌زنیم. به این ترتیب می‌توانیم داده‌های خود را فیلتر کنیم.

• به منظور اعمال فیلتر، روی نماد فیلتر در سرتیتر ستون «فروش» کلیک می‌کنیم.

• سپس گزینه Filter By Color را می‌زنیم و رنگ‌های مورد نظر را برمی‌گزینیم.

به محض انجام این کار، نتیجه تابع Subtotal تغییر می‌کند و فقط مجموع سلول‌هایی را نشان می‌دهد که قابل مشاهده هستند (در این مثال، سلول‌های رنگی) و جمع در اکسل با شرط رنگ سلول انجام می‌شود.

به همین شکل اگر ستون را برای رنگ نارنجی فیلتر کنید نیز نتیجه تغییر کرده و مجموع سلول‌های نارنجی نشان داده می‌شود. نکته قابل توجه اینکه کلیدهای میانبر اعمال فیلتر روی مجموعه داده در ویندوز، Ctrl+Shift+L و در مک، Command+Shift+L است.

• مطالب پیشنهادی برای مطالعه:
  • جمع کردن اعداد در اکسل — آموزش سریع روش ها و فرمول ها + فیلم آموزشی
  • انواع روش های جمع در اکسل – آموزش ۱۳ روش + مثال
  • جمع سلول های خاص در اکسل با ۴ روش کاربردی
  • جمع زدن چند سطر در اکسل – ۴ روش کاربردی
  • جمع ستون در اکسل — ۵ روش کاربردی به زبان ساده + فیلم آموزشی رایگان

کدنویسی VBA برای جمع در اکسل با شرط رنگ سلول

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

برای انجام این کار، مراحل زیر را دنبال می‌کنیم:

• ابتدا دو کلید Alt+F11 را روی کیبورد می‌زنیم تا وارد محیط برنامه‌نویسی VBA شویم. سپس در پنجره جدید، روی «برگه» (Sheet) مورد نظر راست‌کلیک و گزینه‌های Insert و سپس Module را انتخاب می‌کنیم.

• سپس کد زیر را در کادر باز شده می‌نویسیم:

1Function SumByColor(SumRange As Range, SumColor As Range)
2Dim SumColorValue As Integer
3Dim TotalSum As Long
4SumColorValue = SumColor.Interior.ColorIndex
5Set rCell = SumRange
6For Each rCell In SumRange
7If rCell.Interior.ColorIndex = SumColorValue Then
8TotalSum = TotalSum + rCell.Value
9End If
10Next rCell
11SumByColor = TotalSum
12End Function

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

در مثال زیر، از دو سلول D2 و D3 برای نمایش رنگ‌های مورد نظر استفاده کرده‌ایم. رنگ زرد در سلول D2 و رنگ نارنجی در سلول D3 قرار دارد. فرمول زیر را در این سلول‌ها می‌نویسیم:

1=SumByColor($B$2:$B$11,D2)

این فرمول، دو آرگومان دارد که به شرح زیر هستند:

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

توجه داشته باشید که این تابع پویا است و در صورتی که اعداد رنگی تغییر کنند، به‌روزرسانی می‌شود (مانند کم و زیاد کردن مقادیر یا حذف و اضافه کردن آن‌ها). با این حال اگر در شرایطی، فرمول به درستی بروزرسانی نشد می‌توانید کلید F9 را روی کیبورد فشار دهید تا آپدیت شود.

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

استفاده از GET.CELL برای جمع سلول ها با شرط رنگ

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

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

توجه داشته باشید که GET.CELL یک تابع Macro 4 قدیمی است که برای برهم نخوردن سازگاری اکسل با فایل‌های قدیمی‌تر، هنوز هم استفاده می‌شود. اما از آن‌جایی که به ندرت مورد استفاده قرار می‌گیرد، جزئیات زیادی درباره آن وجود ندارد.

در مثال زیر، مجموعه داده‌ای داریم که برخی از سلول‌های آن رنگ شده‌اند.

برای اینکه این روش کار کند، ابتدا باید یک محدوده نام‌گذاری شده بسازیم که از تابع GET.CELL برای دادن مقدار رنگ یک سلول استفاده کند. برای انجام این کار با مراحل زیر پیش می‌رویم:

• وارد زبانه Formula می‌شویم. سپس در بخش Defined Names روی گزینه Name Manager می‌زنیم.

• بعد در کادر جدید، روی گزینه New کلیک می‌کنیم.

• در مرحله بعد، نام SumColor را در کادر Name می‌نویسیم.

• کادر Refers to را با فرمول =GET.CELL(38,$B2)   پر می‌کنیم.

• نهایتا روی گزینه OK می‌زنیم و کادر Name Manager را می‌بندیم.

با انجام این کار یک محدوده نام گذاری شده ساخته‌ایم که می‌توانیم در صفحه خود از آن استفاده کنیم. تابع GET.CELL دو آرگومان دارد. اولی عددی است که به تابع می‌گوید چه اطلاعاتی را نیاز داریم و دومی مرجع همان سلول است. در این مثال از عدد 38 به عنوان اولین آرگومان استفاده کردیم که مقدار شاخص رنگ سلول ارجاع شده را برمی‌گرداند.

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

کلیک کنید

قدم دوم این است که مقدار شاخص رنگ تمام رنگ‌های موجود در ستون B یا «فروش» را به دست بیاوریم. برای انجام این کار مانند مراحل زیر عمل می‌کنیم:

• ابتدا ستون جدیدی می‌سازیم و نام آن را «شاخص رنگ» می گذاریم.

• سپس فرمول زیر را در سلول C2 می‌نویسیم:

1=SumColor

• در مرحله بعد به کمک Fill Handle، فرمول را به تمام سلول‌های ستون C گسترش می‌دهیم.

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

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

• رنگ‌های مورد نظر را در سلول‌های E2 و E3 وارد می‌کنیم. در این مثال دو رنگ زرد و نارنجی را وارد کرده‌ایم.

• سپس فرمول زیر را در سلول F2 می‌نویسیم:

1=SUMIF(C2:C15,SumColor,B2:B15)

• بعد این سلول را کپی و در سلول F3 پیست می‌کنیم تا فرمول روی آن اعمال شود.

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

توجه داشته باشید با اینکه فرمول پویا است اما اگر تغییری در سلول‌ها اعمال کنید (مانند عوض کردن عدد سلول‌های رنگی یا حذف کردن آن‌ها)، نتیجه نهایی فورا عوض نمی‌شود. یک راه‌حل برای رفع مشکل، این است که به سلول حاوی فرمول بروید و دکمه F2 را روی کیبورد فشار دهید و بعد دکمه Enter را بزنید. این کار فرمول را مجبور به محاسبه مجدد می‌کند و به این شکل، نتیجه به‌روزرسانی خواهد شد.

پاسخ به سوالات رایج

در ادامه چند پرسش رایج درباره جمع در اکسل با شرط رنگ را همراه با پاسخ‌های آن‌ها می‌بینیم.

آیا می‌شود از تابع Sumif برای جمع سلول‌های رنگی در اکسل استفاده کرد؟

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

جمع سلول‌های قابل مشاهده در اکسل چگونه انجام می‌شود؟‌

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

سخن نهایی

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