جمع زدن سلول های رنگی در اکسل

جمع زدن سلول بر اساس رنگ

خیلی وقت ها پیش میاد که داده های موجود در اکسل رو بر اساس منطقی رنگ کردیم که از تمیز داده بشن (بصورت دستی یا با استفاده از Conditional formatting). حالا میخوایم بر اساس این رنگ ها محاسباتی رو مثل جمع، شمارش و … انجام بدیم. مثلا میخوایم جمع سلول های قرمز رنگ رو داشته باشیم. همونطور که میدونیم، در اکسل تابعی که بتونه روی سلول های رنگی محاسبات انجام بده (مثلا جمع سلول های رنگی رو بده) وجود نداره. برای این کار یا باید از توابع ساخته شده یا User-defined استفاده بشه که باید کد VBA مربوطه رو داشته باشیم. یا از یک سری ترفندهای دیگه. در ادامه به تشریح روشهای مختلف برای انجام این نوع محاسبات ارائه خواهیم داد.

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

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

شکل ۱- داده های رنگ شده بصورت دستی

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

Function GetCellColor(xlRange As Range)
    Dim indRow, indColumn As Long
    Dim arResults()
 
    Application.Volatile
 
    If xlRange Is Nothing Then
        Set xlRange = Application.ThisCell
    End If
 
    If xlRange.Count > 1 Then
      ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
       For indRow = 1 To xlRange.Rows.Count
         For indColumn = 1 To xlRange.Columns.Count
           arResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Color
         Next
       Next
     GetCellColor = arResults
    Else
     GetCellColor = xlRange.Interior.Color
    End If
End Function

این کد برای فراخوانی رنگ یک سلول هست (رنگ پس زمینه). کد ایجاد تابع برای شمارش سلول های رنگی، در زیر آمده و کافیه مثل توابع قبلی، کد به اکسل اضافه بشه.

Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long
 
    Application.Volatile
    cntRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Interior.Color Then
            cntRes = cntRes + 1
        End If
    Next cellCurrent
 
    CountCellsByColor = cntRes
End Function

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

Function SumCellsByColor(rData As Range, cellRefColor As Range)
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim sumRes
 
    Application.Volatile
    sumRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Interior.Color Then
            sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
        End If
    Next cellCurrent
 
    SumCellsByColor = sumRes
End Function

بعد از اینکه این کدها به اکسل اضافه شد و فایل بصورت XLSM ذخیره شد، همه توابع مورد نظر به اکسل اضافه میشن و قابل استفاده هستن.  حالا طبق شکل ۲، از توابع اضافه دشه به اکسل استفاده میکنیم. تابع CountCellsByColor برای شمارش سلول های رنگی استفاده میشه که آرگومان اولش محدوده سلول های رنگی است و آرگومان دومش، رنگ مورد نظر.

=CountCellsByColor(range, color code)

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

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

SumCellsByColor(range, color code)

شکل ۳- جمع زدن مقادیر سلول های رنگی با استفاده از تابع ایجاد شده

 با استفاده از تابع GetCellColor میتونیم کد رنگ سلول مورد نظر رو فراخوان یکنیم که با این کار، میتونیم هر فرمول نویسی که خواستیم روی کدهای رنگ انجام بدیم. مثل Sumif, Countif و ….

شکل ۴- فراخوانی کد رنگ سلول با استفاده از تابع ایجاد شده

 این کار رو (فراخوانی کد رنگ سلول) با یکی از توابع پنهان اکسل هم میتونیم انجام بدیم. به اینصورت که در Name Manager مطابق شکل ۵، یک نام تعریف میکنیم و تابع زیر رو بهش اختصاص میدیم:

=get.cell(۳۸,سلولی که رنگ داره)

شکل ۵- تعریف یک نام و تخصیص تابع Get.Cell (بدون کد وی بی)

بعد روبروی سلول رنگ شده، = رو تایپ کرده و نام مورد نظر رو می نویسیم. مطابق شکل ۶، این Name کدی رو برای هر رنگ، نمایش میده که ما میتونیم از این کدها برای انجام هر نوع محاسباتی استفاده کنیم:

شکل ۶- فراخوانی کد رنگ بدون VBA

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

متاسفانه در خصوص سلول هایی که با استفاده از Conditional Formatting رنگ شده اند، تابع مشخصی حتی با کد نویسی وجود نداره. چون قواعد مختلفی در این ابزار استفاده میشه و هر کدومش باید جداگانه در نظر گرفته بشه، نمیشه کد مشخصی برای محاسبات نوشت.اما من پیشنهاد میکنم که از منطق خود این ابزار برای فرمول نویسی استفاده بشه. چون رنگی کردن با استفاده از ابزار Conditional formatting تابع منطق هست مثلا تکراری ها رو رنگ میکنه، یا مثلا داده های بزرگتر از ۱۰ و ….ما میتونیم با استفاده از همین شرط ها، فرمول نویسی خودمون رو انجام بدیم و نتایج مورد نظر مثل جمع و تعداد سلول ها رو داشته باشیم.اما اگر به هر دلیلی این روش پاسخگو نبود، میتونیم از کد زیر برای جمع و شمارش سلول های رنگی استفاده کنیم. البته این یک ماژول هست و باید اجرا بشه هر بار. تابع نیست که به اکسل اضافه بشه:

Sub SumCountByConditionalFormat()
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long
    Dim sumRes
    Dim cntCells As Long
    Dim indCurCell As Long
 
    cntRes = 0
    sumRes = 0
 
    cntCells = Selection.CountLarge
    indRefColor = ActiveCell.DisplayFormat.Interior.Color
 
    For indCurCell = 1 To (cntCells - 1)
        If indRefColor = Selection(indCurCell).DisplayFormat.Interior.Color Then
            cntRes = cntRes + 1
            sumRes = WorksheetFunction.Sum(Selection(indCurCell), sumRes)
        End If
    Next
   MsgBox "Count=" & cntRes & vbCrLf & "Sum= " & sumRes & vbCrLf & vbCrLf & _
        "Color=" & Left("000000", 6 - Len(Hex(indRefColor))) & _
        Hex(indRefColor) & vbCrLf, , "Count & Sum by Conditional Format color"
End Sub

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

شکل ۷- انتخاب محدوده رنگی شده و سلول مورد نظر برای شمارش

بعد از اینکه کد رو به فایل اکسل اضافه کردیم، طبق زیر عمل میکنیم:

• محدوده رنگی رو انتخاب میکنیم. (ستون C در شکل ۷)
• کلید Ctrl رو نگه میداریم و روی سلولی که رنگ مورد نظر ما رو داره کلیک میکنیم. (سلول C2 در شکل ۷)
• کلید ترکیبی Alt+F8 رو میزنیم و لیست ماکروهای موجود در اکسل نمایش داده میشه.
• ماکروی مورد نظر یعنی SumCountByConditionalFormat رو انتخاب میکنیم و Run رو میزنیم.
• نتیجه در یک msgbox نمایش داده میشه. (شکل ۸)

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

همه این کدها (Function و Sub) و تنظیمات Name Manager در فایل اکسل زیر موجود هست. میتونید دانلود کنید و آموزش بالا رو روی داده های این فایل امتحان کنید.

دانلود فایل این آموزش

برای دانلود فایل این آموزش روی لینک زیر کلیک کنید (جهت دانلود مستقیم به حساب کاربری خود وارد شوید)