Trong bài viết này, học Excel Online đang đếm những ô tính bằng màu sắc và tính tổng các ô được sơn màu. Những phương án này công dụng đối với cả ô tính đánh màu thủ công bằng tay và ô tính được định hình theo điều kiện. Đồng thời, bạn cũng biết cách lọc những ô tính bằng nhiều color trong Excel 2010, 2013, 2016.

Bạn đang xem: Tính tổng theo màu trong excel


Cách đếm với tính tổng bởi màu vào trang tính Excel

Giả sử bạn có bảng liệt kê danh sách đơn quánh hàng của công ty, trong những số đó cột Delivery được sơn màu dựa vào giá trị của chúng: “Due in X Days” được tô màu sắc cam, “Delivered” được tô greed color lá với “Past Due” được tô màu đỏ.

*

Những gì bọn họ muốn là auto tính những ô theo màu sắc, có nghĩa là tính số ô màu đỏ, xanh lá cây và cam vào bảng tính. Hãy thường xuyên với 5 bước dưới đây và các bạn sẽ biết số ô từng màu và tổng của chúng.

Mở bảng tính Excel cùng nhấn Alt + F11 để mở Visual Basic Editor (VBE). Bấm chuột phải vào thương hiệu bảng tính dưới Project-VBAProject ở phía tay cần của màn hình, tiếp nối chọn Insert > Module từ danh sách tùy chọn.

*
Module to địa chỉ cửa hàng a new user-defined function to your worksheet." />

Thêm đoạn mã sau vào bảng tính.
Function GetCellColor(xlRange As Range)Dim indRow, indColumn As LongDim arResults()Application.VolatileIf xlRange Is Nothing ThenSet xlRange = Application.ThisCellEnd IfIf xlRange.Count > 1 ThenReDim arResults(1 lớn xlRange.Rows.Count, 1 lớn xlRange.Columns.Count)For indRow = 1 to lớn xlRange.Rows.CountFor indColumn = 1 khổng lồ xlRange.Columns.CountarResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.ColorNextNextGetCellColor = arResultsElseGetCellColor = xlRange.Interior.ColorEnd IfEnd FunctionFunction GetCellFontColor(xlRange As Range)Dim indRow, indColumn As LongDim arResults()Application.VolatileIf xlRange Is Nothing ThenSet xlRange = Application.ThisCellEnd IfIf xlRange.Count > 1 ThenReDim arResults(1 khổng lồ xlRange.Rows.Count, 1 to xlRange.Columns.Count)For indRow = 1 lớn xlRange.Rows.CountFor indColumn = 1 khổng lồ xlRange.Columns.CountarResults(indRow, indColumn) = xlRange(indRow, indColumn).Font.ColorNextNextGetCellFontColor = arResultsElseGetCellFontColor = xlRange.Font.ColorEnd IfEnd FunctionFunction CountCellsByColor(rData As Range, cellRefColor As Range) As LongDim indRefColor As LongDim cellCurrent As RangeDim cntRes As LongApplication.VolatilecntRes = 0indRefColor = cellRefColor.Cells(1, 1).Interior.ColorFor Each cellCurrent In rDataIf indRefColor = cellCurrent.Interior.Color ThencntRes = cntRes + 1End IfNext cellCurrentCountCellsByColor = cntResEnd FunctionFunction SumCellsByColor(rData As Range, cellRefColor As Range)Dim indRefColor As LongDim cellCurrent As RangeDim sumResApplication.VolatilesumRes = 0indRefColor = cellRefColor.Cells(1, 1).Interior.ColorFor Each cellCurrent In rDataIf indRefColor = cellCurrent.Interior.Color ThensumRes = WorksheetFunction.Sum(cellCurrent, sumRes)End IfNext cellCurrentSumCellsByColor = sumResEnd FunctionFunction CountCellsByFontColor(rData As Range, cellRefColor As Range) As LongDim indRefColor As LongDim cellCurrent As RangeDim cntRes As LongApplication.VolatilecntRes = 0indRefColor = cellRefColor.Cells(1, 1).Font.ColorFor Each cellCurrent In rDataIf indRefColor = cellCurrent.Font.Color ThencntRes = cntRes + 1End IfNext cellCurrentCountCellsByFontColor = cntResEnd FunctionFunction SumCellsByFontColor(rData As Range, cellRefColor As Range)Dim indRefColor As LongDim cellCurrent As RangeDim sumResApplication.VolatilesumRes = 0indRefColor = cellRefColor.Cells(1, 1).Font.ColorFor Each cellCurrent In rDataIf indRefColor = cellCurrent.Font.Color ThensumRes = WorksheetFunction.Sum(cellCurrent, sumRes)End IfNext cellCurrentSumCellsByFontColor = sumResEnd Function
lưu lại bảng tính thành “Excel Macro-Enabled Workbook (.xlsm)”. Như vậy, toàn bộ những làm việc lặt lặt vặt đã triển khai xong, chỉ việc thêm hàm được chỉ định, chọn ô tính ao ước hiển thị công thức và gõ phương pháp CountCellsByColor.

=CountCellsByColor(range, màu sắc code)


*

*

Trong lấy ví dụ này, chúng ta sử dụng công thức = CountCellsByColor (F2:F14, A17) trong số ấy F2: F14 là hàng chứa những ô tất cả màu bạn có nhu cầu đếm với A17 là ô gồm màu nền độc nhất định, trong ví dụ đó là những ô màu sắc đỏ.

Tương tự, bạn viết cách làm để đếm những ô màu sắc khác, như màu đá quý và màu xanh da trời lá cây trong bảng của chúng tôi.

*

Nếu chúng ta có tài liệu số trong ô màu, như cột Qty, bạn cũng có thể thêm giá chỉ trị phụ thuộc vào màu bằng phương pháp sử dụng hàm SumCellsByColor

=SumCellsByColor(range, màu sắc code) 

*

Như đã trình bày, cửa hàng chúng tôi sử dụng bí quyết =SumCellsByColor (D2:D14, A17) trong số đó D2: D14 là dãy cùng A17 là ô với một mẫu mã màu.

Tương tự, chúng ta cũng có thể đếm ô cùng tính tổng những ô tính bởi màu trải qua hàm CountCellsByFontColor SumCellsByFontColor.

*

Chú ý. Sau khi vận dụng mã VBA, bạn sẽ cần thêm màu cho một số ô khác theo cách thủ công, tổng và số ô được tô màu sẽ không còn được tính lại tự động hóa để làm phản ánh đông đảo thay đổi.

Trên thực tế, sẽ là đặc điểm bình thường của toàn bộ các Macro, VBA scripts với hàm User-Defined. Vấn đề là toàn bộ các chức năng đó sẽ đi kèm với sự chuyển đổi dữ liệu của một bảng tính cùng Excel không nhận ra sự đổi khác màu chữ hoặc màu sắc của ô này. Vì vậy, sau khi tô màu những ô một bí quyết thủ công, chỉ cần đặt con trỏ loài chuột vào ngẫu nhiên ô nào, dìm F2 và Enter, số ô và tổng của chúng sẽ tiến hành cập nhật.

Tính tổng với đếm số ô bằng color trên tổng thể bảng tính:

Đoạn VB dưới được viết theo câu trả lời của Connor (một bậc thầy Excel) với thực hiện đúng mực những gì Connor yêu cầu, rõ ràng là đếm với tính tổng những ô cùng màu trong tất cả trang tính của bảng tính. Vày vậy, tại đây có mã số:

Function WbkCountCellsByColor(cellRefColor As Range)Dim vWbkResDim wshCurrent As WorksheetApplication.ScreenUpdating = FalseApplication.Calculation = xlCalculationManualvWbkRes = 0For Each wshCurrent In WorksheetswshCurrent.ActivatevWbkRes = vWbkRes + CountCellsByColor(wshCurrent.UsedRange, cellRefColor)NextApplication.ScreenUpdating = TrueApplication.Calculation = xlCalculationAutomaticWbkCountCellsByColor = vWbkResEnd FunctionFunction WbkSumCellsByColor(cellRefColor As Range)Dim vWbkResDim wshCurrent As WorksheetApplication.ScreenUpdating = FalseApplication.Calculation = xlCalculationManualvWbkRes = 0For Each wshCurrent In WorksheetswshCurrent.ActivatevWbkRes = vWbkRes + SumCellsByColor(wshCurrent.UsedRange, cellRefColor)NextApplication.ScreenUpdating = TrueApplication.Calculation = xlCalculationAutomaticWbkSumCellsByColor = vWbkResEnd Function

Bạn thực hiện Macro giống như như mã trước đó cùng sử dụng kết quả của các công thức =WbkCountCellsColor()=WbkSumCellsByColor(). Chỉ việc nhập cách làm vào ngẫu nhiên ô trống làm sao trên trang tính nằm ngoài vùng tài liệu được chọn, xác định địa chỉ cửa hàng ô tính đựng màu bạn muốn vào ngoặc đơn, lấy ví dụ = WbkSumCellsByColor (A1), và bí quyết sẽ hiển thị tổng của toàn bộ các ô được tô cùng trong bảng tính của bạn.

Xem thêm: Cách gỡ bỏ password khi share máy in trong mạng lan

Tùy chỉnh các tính năng để tô màu sắc nền ô, tô color chữ và mã màu sắc

Ở đây, các bạn sẽ tìm thấy một bạn dạng tóm tắt toàn bộ các chức năng chúng ta đã áp dụng trong ví dụ như này cũng tương tự một vài ba cái chức năng mới để mang mã màu.

Chú ý. Hãy nhớ rằng tất cả các bí quyết chỉ vận động nếu các bạn đã thêm tác dụng do người tiêu dùng định nghĩa vào bảng tính Excel như đã trình bày ở trên.

Chức năng đếm theo màu sắc sắc:

CountCellsByColor (phạm vi, mã màu) – đếm các ô gồm màu nền được chỉ định.

Trong lấy ví dụ như trên, chúng tôi sử dụng cách làm sau nhằm đếm những ô theo màu =CountCellsByColor (F2: F14, A17), trong các số ấy F2:F14 là vùng tài liệu đã lựa chọn và A17 là ô có màu nền yêu cầu thiết. Chúng ta cũng có thể sử dụng tất cả các bí quyết khác được liệt kê sau đây theo phương pháp tương tự.

CountCellsByFontColor (phạm vi, mã màu) – đếm những ô với màu sắc chữ được chỉ định.

Công thức tính tổng bởi màu sắc:

SumCellsByColor (range, color code) – tính tổng của các ô gồm cùng color nền.

SumCellsByFontColor (range, color code) – tính tổng của những ô gồm cùng color chữ.

Công thức để đưa mã màu:

GetCellFontColor (cell) – trả về mã màu chữ của một ô xác định.

GetCellColor (cell) – trả về mã màu sắc nền của một ô xác định.

*

Cách đếm với tính tổng những ô màu định dạng có điều kiện:

VBA sau đây sẽ hiển thị số ô màu và tổng những giá trị vào ô, bất kỳ loại format có đk nào được sử dụng.

Sub SumCountByConditionalFormat()Dim indRefColor As LongDim cellCurrent As RangeDim cntRes As LongDim sumResDim cntCells As LongDim indCurCell As LongcntRes = 0sumRes = 0cntCells = Selection.CountLargeindRefColor = ActiveCell.DisplayFormat.Interior.ColorFor indCurCell = 1 to lớn (cntCells – 1)If indRefColor = Selection(indCurCell).DisplayFormat.Interior.Color ThencntRes = cntRes + 1sumRes = WorksheetFunction.Sum(Selection(indCurCell), sumRes)End IfNextMsgBox “Count=” & cntRes và vbCrLf & “Sum= ” và sumRes và vbCrLf & vbCrLf và _“Color=” & Left(“000000”, 6 – Len(Hex(indRefColor))) & _Hex(indRefColor) & vbCrLf, , “Count & Sum by Conditional Format color”End Sub

Cách thực hiện mã để đếm số ô màu cùng tính tổng vốn của chúng

Thêm đoạn mã bên trên vào trang tính như đã phân tích và lý giải ở ví dụ. Lựa chọn một dãy hoặc các vùng dữ liệu bạn cần nếu khách hàng có tài liệu dạng số. Nhấn giữ phím Ctrl, chọn 1 ô tính với màu cần thao tác, thả phím Ctrl. Nhận Alt+F8 nhằm mở danh sách macros trong bảng tính. Lựa chọn SumCountByConditionalFormat với nhấp vào Run.

*

Kết quả hiển thị như sau.

*

Trong ví dụ này, chúng ta chọn cột Qty. Và có các con số sau:

Count là số ô tính trong một màu cố kỉnh thể, như số ô red color trong lấy ví dụ được khắc ghi là “Past Due”. Sum là tổng giá trị các ô màu đỏ trong cột Qty, ví dụ là toàn bô ô “Past Due”. Color là mã màu sắc của ô được chọn, ví dụ là ô D2.Qua bài viết này họ thấy việc thực hiện VBA vào Excel mang lại kết quả rất nhiều trong công việc đúng ko nào. Không chỉ tất cả vậy, VBA còn khiến cho được các điều thú vị hơn thế nữa như: giúp bạn tăng tốc trong quá trình xử lý trên excel, giúp lập các report tự động, tinh chỉnh và điều khiển các chương trình khác như word, power point, outlook… giúp biến chuyển file excel của công ty trở thành 1 phần mềm siêng nghiệp…Để rất có thể học VBA một giải pháp đầy đủ, tất cả hệ thống, bạn hãy tham gia khoá học VBA101 – VBA cơ phiên bản dành cho những người mới bắt đầu của khối hệ thống Học Excel Online. Hiện giờ hệ thống đang có không ít ưu đãi khi chúng ta đăng ký khóa huấn luyện và đào tạo này. Chi tiết xem tại: