تابع SUMIF بر اساس رنگ سلول در اکسل

29 دسامبر تابع SUMIF بر اساس رنگ سلول در اکسل

تمام کسانی که کمی حرفه ای تر با نرم افزار اکسل کار کرده باشند با تابع SUMIF آشنا هستند. تابع SUMIF یک مقدار رو در یک محدوده جستجو میکنه و اگر مقدار مورد نظر در اون محدوده پیدا شد، عبارت متناظر رو از یک محدوده دیگه برمیگردونه. بعضی وقتها ممکنه پیش بیاد که ما بخوایم تابع SUMIF رو بر اساس رنگ سلول انجام بدیم. مثلاً تمام سلولهایی که رنگ اونها قرمز هست رو با هم جمع کنیم. اینکار توسط تابع SUMIF در اکسل امکان پذیر نیست. در این مطلب میبینیم که چطور با استفاده از یک تابع در VBA اینکار رو انجام بدیم.

خب برای شروع بیاید شیت زیر رو در نظر بگیرید:

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

افزودن تابع Sumifcolor به اکسل:

برای اینکار ابتدا روی یکی از شیت های فایل اکسلمون راست کلیک میکنیم و مثل تصویر زیر گزینه view code رو انتخاب میکنیم:

با انتخاب این گزینه وارد پنجره Visual Basic Editor میشیم.

اگر با این پنجره زیاد آشنایی ندارید نگران نباشید. ما زیاد کاری باهاش ندارید. حالا از منوی Insert گزینه Module رو مثل تصویر زیر انتخاب میکنیم:

حالا کد زیر رو در پنجره ای که باز شده کپی کنید:

 

Public Function SumifColor(ColorRange As Range, CellColor 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

الان باید چیزی شبیه تصویر زیر داشته باشیم:

خب کار ما با Visual Basic Editor تموم شد و میتونیم این پنجره رو ببندیم.

استفاده از تابع SumifColor

حالا یه تابع به اسم SumifColor به اکسل ما اضافه شده که درست مثل تابع SUMIF عمل کنه. با این تفاوت که در تابع SUMIF شرط بر اساس مقدار سلول بود اما در این تابع شرط بر اساس رنگ سلول هست.

خب همونطور که گفتم ما میخواستیم سلول هایی که رنگ متناظرشون سبز هست رو با هم جمع کنیم پس در سلول D1 فرمول زیر رو مینویسیم:

 

=SumifColor(A1:A10;A8;B1:B10)

حالا باید شما هم مثل تصویر زیر مقدار 15 رو در سلول D1 داشته باشید. (جمع سلولهایی که رنگ ستون A اونها سبزه یعنی 12 و 3)

حالا بیاید فرمول بالا رو بررسی کنیم. در واقع این فرمول داره میگه که اگر هر یک از سلول های موجود در محدوده A1:A10 رنگشون مشابه رنگ سلول A8 بود، سلول متناظرش در محدوده B1:B10 رو با هم جمع کن. 

امیدوارم این آموزش براتون مفید بوده باشه.

فایل نمونه:

[private]sumifcolor[/private]