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

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

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

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

جدول مثال برای جمع در اکسل با رنگ سلول

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

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

=SUBTOTAL(9,B2:B11)
استفاده از تابع Subtotal برای جمع فیلتر در اکسل

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

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

  • سلولی را در صفحه خود انتخاب می‌کنیم و به زبانه Data می‌رویم.
انتخاب زبانه Data برای فیلتر کردن ستون در اکسل
  • سپس در بخش Sort & Filter، روی نماد Filter می‌زنیم. به این ترتیب می‌توانیم داده‌های خود را فیلتر کنیم.
فیلتر در اکسل
  • به منظور اعمال فیلتر، روی نماد فیلتر در سرتیتر ستون «فروش» کلیک می‌کنیم.
فیلتر کردن سلول های رنگی در اکسل
  • سپس گزینه Filter By Color را می‌زنیم و رنگ‌های مورد نظر را برمی‌گزینیم.
انتخاب رنگ برای فیلتر در اکسل

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

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

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

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

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

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

  • ابتدا دو کلید Alt+F11 را روی کیبورد می‌زنیم تا وارد محیط برنامه‌نویسی VBA شویم. سپس در پنجره جدید، روی «برگه» (Sheet) مورد نظر راست‌کلیک و گزینه‌های Insert و سپس Module را انتخاب می‌کنیم.
ماژول نویسی VBA در اکسل
  • سپس کد زیر را در کادر باز شده می‌نویسیم:
Function SumByColor(SumRange As Range, SumColor As Range)
Dim SumColorValue As Integer
Dim TotalSum As Long
SumColorValue = SumColor.Interior.ColorIndex
Set rCell = SumRange
For Each rCell In SumRange
If rCell.Interior.ColorIndex = SumColorValue Then
TotalSum = TotalSum + rCell.Value
End If
Next rCell
SumByColor = TotalSum
End Function

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

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

=SumByColor($B$2:$B$11,D2)
استفاده از Vba برای جمع در اکسل با شرط رنگ سلول

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

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

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

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

مطلب پیشنهادی:
برنامه نویسی VBA در اکسل (+ دانلود فیلم آموزش گام به گام)
شروع مطالعه

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

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

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

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

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

جدول مثال برای جمع در اکسل با رنگ سلول

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

  • وارد زبانه Formula می‌شویم. سپس در بخش Defined Names روی گزینه Name Manager می‌زنیم.
ساخت محدوده نام گذاری شده با Name manager در اکسل
  • بعد در کادر جدید، روی گزینه New کلیک می‌کنیم.
گزینه New در Name manager
  • در مرحله بعد، نام SumColor را در کادر Name می‌نویسیم.
انتخاب نام برای محدوده نام گذاری شده
  • کادر Refers to را با فرمول =GET.CELL(38,$B2)   پر می‌کنیم.
نوشتن فرمول برای محدوده نام گذاری شده
  • نهایتا روی گزینه OK می‌زنیم و کادر Name Manager را می‌بندیم.

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

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

  • ابتدا ستون جدیدی می‌سازیم و نام آن را «شاخص رنگ» می گذاریم.
اضافه کردن ستون جدید برای شاخص رنگ
  • سپس فرمول زیر را در سلول C2 می‌نویسیم:
=SumColor
فرمول Sumcolor برای جمع زدن سلول ها با شرط رنگ
  • در مرحله بعد به کمک Fill Handle، فرمول را به تمام سلول‌های ستون C گسترش می‌دهیم.

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

مطلب پیشنهادی:
نوشتن فرمول جمع در اکسل — به زبان ساده + مثال و فیلم آموزشی رایگان
شروع مطالعه

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

  • رنگ‌های مورد نظر را در سلول‌های E2 و E3 وارد می‌کنیم. در این مثال دو رنگ زرد و نارنجی را وارد کرده‌ایم.
مشخص کردن رنگ ها برای مقایسه شاخص رنگی
  • سپس فرمول زیر را در سلول F2 می‌نویسیم:
=SUMIF(C2:C15,SumColor,B2:B15)
فرمول sumif برای جمع سلول ها در اکسل با شرط رنگ
  • بعد این سلول را کپی و در سلول F3 پیست می‌کنیم تا فرمول روی آن اعمال شود.
گسترش فرمول sumif به سلول دوم

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

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

مطلب پیشنهادی:
جمع زدن اتوماتیک در اکسل – توضیح کامل جمع خودکار
شروع مطالعه

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

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

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

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

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

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

سخن نهایی

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

اگر این مطلب برای شما مفید بوده است، آموزش‌ها و مطالب زیر نیز به شما پیشنهاد می‌شوند:

بر اساس رای ۱۰ نفر
آیا این مطلب برای شما مفید بود؟
شما قبلا رای داده‌اید!
اگر بازخوردی درباره این مطلب دارید یا پرسشی دارید که بدون پاسخ مانده است، آن را از طریق بخش نظرات مطرح کنید.

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