Làm thế nào để vận dụng màu sắc trong trang tính để tăng phần sinh động cho file Excel? Nếu bạn đang băn khoăn câu hỏi này thì hàm COLOR trong Excel chính là câu trả lời bạn đang tìm kiếm. Hãy cùng daihoangde.vn học cách sử dụng hàm COLOR qua bài viết ngày hôm nay nhé.

Bạn đang xem: Hàm đếm màu trong excel

Tuyệt đỉnh Excel - Trở thành bậc thầy Excel trong 16 giờ

Giới thiệu về hàm COLOR trong Excel

Không có gì kỳ lạ nếu đây là lần đầu tiên bạn nghe đến tên hàm COLOR trong Excel, bởi nó không nằm trong hệ thống hàm có sẵn của Excel. Thay vào đó, đây là một hàm UDF (user-defined function) - hàm do người dùng tự tạo dựa trên code VBA. Mục đích chúng ta viết một hàm COLOR mới là để đếm và tính tổng các ô Excel dựa trên màu sắc.

Nếu bạn còn chưa quen với thuật ngữ hàm UDF và tính năng viết hàm bằng lệnh VBA, hãy tham khảo bài viết dưới đây để mở khóa một thủ thuật vô cùng lợi hại trong Excel nhé.

Cách đếm và tính tổng các ô màu bằng hàm COLOR trong Excel

Chúng ta hãy đi vào một ví dụ cụ thể về hàm COLOR trong Excel để xác định các bước cần làm. Dưới đây là trang tính thể hiện trạng thái giao hàng của một công ty vận tải, trong đó "Đang giao hàng trong X ngày" được tô màu vàng, "Giao hàng thành công" được tô màu xanh, "Quá hạn giao hàng" được tô màu đỏ. Hãy cùng xem cách sử dụng hàm COLOR trong Excel để đếm và tính tổng các ô theo màu tương ứng nhé.

Cách đếm và tính tổng các ô theo màu ô bằng hàm COLOR trong Excel

Cách đếm số ô theo màu ô bằng hàm COLOR trong ExcelBước 1: Ấn tổ hợp phím Alt+F11
để mở giao diện Visual Basic Editor (VBE). Sau đó nhấn chuột phải ở tên trang tính trong mục Project - VBA Project, chọn Insert > Module.

*

Bước 2:Một bảng tính hiện ra. Các bạn nhập dòng code 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 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)For indRow = 1 To xlRange.Rows.CountFor indColumn = 1 To 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 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)For indRow = 1 To xlRange.Rows.CountFor indColumn = 1 To 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 FunctionLưu bảng tính vừa nhập code dưới tên“Excel Macro-Enabled Workbook (.xlsm)”. Như vậy, chúng ta đã xử lí xong phần code VBA.

Bước 3: Quay lại trang tính trạng thái giao hàng, ta tạo một bảng tổng hợp kết quả đếm ô theo màu. Để tiến hành phân loại và đếm số ô có màu tương ứng, ta dùng hàm COLOR trong Excel theo công thức:

=CountCellsByColor(range, color code)

Trong đó:

range: là phạm vi đếmcolor code: là code màu tương ứng

Như vậy, ta sẽ nhập vào ô B15 công thức: =CountCellsByColor($E$2:$E$12,A15)

*

Lưu ý: Chúng ta có thể sử dụng tham chiếu tuyệt đối để khóa phạm vi đếm.

Cách tính tổng theo màu ô bằng hàm COLOR trong Excel

Tương tự như công thức hàm COLOR phía trên, để tính tổng số liệu các ô theo màu tương ứng, chúng ta dùng hàm sau:

=SumCellsByColor(range, color code)

*

Cách đếm và tính tổng các ô theo màu chữ bằng hàm COLOR trong Excel

Cách đếm số ô theo màu chữ bằng hàm COLOR trong Excel

Thay vì sử dụng màu ô để đếm số ô tương ứng, chúng ta còn có thể đếm số ô theo màu chữ bằng cách dùng hàm:

=CountCellsByFontColor(range, color code)

*

Cách tính tổng các ô theo màu chữ bằng hàm COLOR trong Excel

Để tính tổng các ô dựa vào màu chữ, chúng ta chỉ cần nhập công thức đơn giản như sau:

=SumCellsByFontColor(range, color code)

*

Lưu ý: Khi sử dụng code VBA phía trên, trong trường hợp chúng ta cần thêm các ô dữ liệu mới, chúng ta sẽ phải tô màu ô hoặc màu chữ thủ công. Kết quả số ô và tổng các ô đã tính sẽ không tự động cập nhật thêm những ô dữ liệu mới. Thay vào đó, các bạn cần di con trỏ chuột vào một ô bất kì trong phạm vi, ấn F2 rồi Enter. Lúc đó Excel mới cập nhật kết quả sau cùng.

Cách đếm và tính tổng số ô theo màu trên toàn bộ bảng tính

Nếu các bạn muốn đếm và tính tổng số ô theo một màu nhất định thì hãy nhập code sau vào VBA:

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

Các bạn có thể sử dụng mã Macro phía trên cùng. công thức =WbkCountCellsColor() và =WbkSumCellsByColor(). Bằng cách nhập công thức vào một ô trống bất kì bên ngoài vùng lựa chọn, xác định vị trí ô chứa màu lựa chọn và nhập vị trí ô vào ngoặc đơn (), các bạn sẽ nhận được kết quả tổng các ô cùng màu trong trang tính.

Tổng hợp các chức năng tùy chỉnh màu sắc ô, màu chữ và lấy mã màu bằng hàm COLOR trong Excel:

Công thức đếm số ô bằng màu sắc:CountCellsByColor (range,color_code)
– đếm các ô có màu nền được chỉ định.CountCellsByFontColor (range,color_code)– đếm các ô với màu chữ được chỉ định.Công thức tính tổng bằng màu sắc:SumCellsByColor (range, color code)– tính tổng của các ô có cùng màu nền.SumCellsByFontColor (range, color code)– tính tổng của các ô có cùng màu chữ.Công thức lấy 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 nền của một ô xác định.

Lưu ý: Tất cả các công thức chỉ hoạt động khi các bạn thêm chức năng được định nghĩa vào trang tính Excel.

*

Sau khi hoàn thành, kết quả được hiển thị như trong ảnh:

*

Hộp thoại trên cho thấy: Có 3 ô nền màu đỏ thể hiện 3 đơn hàng đang ở trạng thái quá hạn giao hàng, tổng số lượng hàng bị quá hạn giao là 29, và mã của màu đỏ được dùng trong ô là 7578FF.

Xem thêm: Cuộc Kháng Chiến Chống Thực Dân Pháp Năm Bao Nhiêu, Hai Cuộc Kháng Pháp Của Dân Tộc Việt Nam

Tổng kết

Vậy là chúng ta đã học xong cách đếm và tính tổng các ô theo màu bằng hàm COLOR trong Excel rồi. Bằng cách sử dụng yếu tố màu sắc, các bạn có thể phân loại dữ liệu rõ ràng hơn, đồng thời tăng sự sinh động cho file Excel của mình. Nếu bạn muốn học thêm những kiến thức hữu ích tương tự về Excel và code VBA Excel, hãy đọc các bài viết trên blog daihoangde.vn và đăng kí các khóa học của daihoangde.vn nhé.