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

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

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

1- صفحه کد نویسی وی بی ای را در اکسل باز کنید. برای این کار کافی هست از کلید ترکیبی ctrl+F11 استفاده کنید.

2- در پنجره باز شده مطالبق تصویر زیر یک Module جدید ایجاد کنید.

3- کد زیر را در قسمت سفید رنگ Module ایجاد شده (مطابق شماره 3 در عکس بالا) کپی و پیست کنید.

Function CountCcolor(range_data As range, criteria As range) As Long
     Dim datax As range
     Dim xcolor As Long
     xcolor = criteria.Interior.ColorIndex
     For Each datax In range_data
          If datax.Interior.ColorIndex = xcolor Then
               CountCcolor = CountCcolor + 1
          End If
     Next datax
 End Function

4- پنجره برنامه نویسی vba را ببندید تا مجددا وارد صفحه اکسل شوید.

5- برای تست کردن تابعی که ایجاد کرده اید یک داده نمونه مانند عکس زیر ایجاد کنید.

6- در سلول F2 تابع زیر را بنویسید.

=CountCcolor(range_data,criteria)

به جای range_data بازه سلولی مورد نظر خود را انتخاب کنید (در اینجا بازه C2:C24) و به جای criteria سلولی که رنگ مورد نظر شما را دارد جایگزین کنید (در اینجا سلول F1).

پس از زدن اینتر خواهید دید که تابع سلول ها با رنگ مورد نظر شما را خواهد شمرد.

ایجاد تابع جهت جمع مقدار سلول ها با رنگ مشخص (sumif بر اساس رنگ سلول)

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

Function SumByColor(CellColor As Range, rRange As Range)
Dim cSum As Long
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
 If cl.Interior.ColorIndex = ColIndex Then
 cSum = WorksheetFunction.Sum(cl, cSum)
 End If
Next cl
SumByColor = cSum
End Function

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

حال می توانید برای جمع زدن مقدار سلول های زرد رنگ از فرمول زیر استفاده کنید.

=SumByColor(A2,A2:A13)

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

ایجاد تابع جهت جمع مقدار سلول ها با رنگ مشخص در بازه های متفاوت (sumif بر اساس رنگ سلول)

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

Public Function SumifColor( CellColor As Range,ColorRange As Range, SumRange As Range)
Dim cSum As Double
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For i = 1 To ColorRange.Count
If ColorRange(i).Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(SumRange(i), cSum)
End If
Next i
SumifColor = cSum
End Function

برای استفاده از تابع بالا کافی هست در یک سلول فرمول زیر را بنویسید و مقدار دهی کنید.

=SumifColor(CellColor ,ColorRange,SumRange)

که به جای cellColor سلول با رنگ پس زمینه مورد نظر، به جای colorRange بازی که شرط رنگ باید بر روی آن اعمال شود و به جای sumRange بازه ای که جمع زدن باید بر روی آن انجام شود استفاده کنید.

در صورتی که کمی با وی بی ای بیشتر آشنا شوید به می توانید توابع بالا را تغییر دهید و از سایر ویژگی های سلول جهت بررسی و شرط گذاری استفاده کنید. مثلا به جای قسمت Interior.ColorIndex که رنگ پس زمینه سلول ها را مشخص کنید از Font.color استفاده کنید تا رنگ متن سلول ها به عنوان شرط بررسی شوند. مثلا سلول هایی را شمارش کن که رنگ متن آنها قرمز است و …

امیدوارم که از این مطلب لذت برده باشید. در صورتی که سوالی داشتید می تونید در قسمت نظرات بپرسید تا در اولین فرصت پاسخگو باشیم.