Làm nỗ lực nào để vận dụng màu sắc trong trang tính để tăng phần nhộn nhịp cho file Excel? nếu khách hàng đang do dự câu hỏi này thì hàm màu sắc trong Excel chính là câu vấn đáp bạn đang tìm kiếm. Hãy thuộc thamsannhapkhau.com học cách thực hiện hàm color qua nội dung bài viết ngày bây giờ nhé.

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

Tuyệt đỉnh Excel - vươn lên là bậc thầy Excel trong 16 giờ

Giới thiệu về hàm màu sắc trong Excel

Không gồm gì kỳ cục nếu đó là lần đầu tiên bạn tìm đến tên hàm màu sắc trong Excel, bởi vì nó không nằm trong hệ thống hàm gồm sẵn của Excel. Cụ 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 bọn họ viết một hàm color mới là nhằm đếm với tính tổng những ô Excel dựa trên màu sắc.

Nếu chúng ta còn chưa quen với thuật ngữ hàm UDF và tuấn kiệt viết hàm bằng lệnh VBA, hãy tham khảo bài viết dưới đây để unlock một thủ thuật khôn xiết lợi sợ hãi trong Excel nhé.

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

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

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

Cách đếm số ô theo color ô bởi hàm màu sắc trong ExcelBước 1: Ấn tổ hợp phím Alt+F11
nhằm mở đồ họa Visual Basic Editor (VBE). Tiếp đến nhấn chuột đề xuất ở thương hiệu trang tính trong mục Project - VBA Project, chọn Insert > Module.

*

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

Bước 3: quay trở lại trang tính tâm trạng giao hàng, ta tạo ra một bảng tổng hợp công dụng đếm ô theo màu. Để thực hiện phân các loại và đếm số ô có màu tương ứng, ta cần sử dụng hàm color trong Excel theo công thức:

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

Trong đó:

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

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

*

Lưu ý: bạn cũng có thể sử dụng tham chiếu tuyệt vời để khóa phạm vi đếm.

Cách tính tổng theo color ô bằng hàm màu sắc trong Excel

Tương tự như cách làm hàm color phía trên, nhằm tính toàn bô liệu những ô theo màu tương ứng, bọn họ dùng hàm sau:

=SumCellsByColor(range, color code)

*

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

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

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

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

*

Cách tính tổng những ô theo màu chữ bởi hàm color trong Excel

Để tính tổng những ô phụ thuộc màu chữ, họ chỉ đề xuất nhập công thức dễ dàng như sau:

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

*

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

Xem thêm: Các Cách Xuống Dòng Trong 1 Ô Excel 2010, Các Cách Xuống Dòng Trong Excel Dễ Nhất

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

Nếu các bạn có nhu cầu đếm với tính tổng số ô theo một màu độc nhất vô nhị đị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 bên trên cùng. Công thức =WbkCountCellsColor() cùng =WbkSumCellsByColor(). Bằng cách nhập công thức vào trong 1 ô trống bất kì phía bên ngoài vùng lựa chọn, xác định vị trí ô chứa màu tuyển lựa và nhập địa chỉ ô vào ngoặc đơn (), các bạn sẽ nhận được hiệu quả tổng các ô cùng màu vào trang tính.

Tổng hợp những chức năng tùy chỉnh thiết lập màu sắc ô, màu sắc chữ cùng lấy mã màu bởi hàm color trong Excel:

Công thức đếm số ô bởi màu sắc:CountCellsByColor (range,color_code)
– đếm các ô tất cả màu nền được chỉ định.CountCellsByFontColor (range,color_code)– đếm những ô với màu sắc chữ được chỉ định.Công thức tính tổng bằng màu sắc:SumCellsByColor (range, màu sắc code)– tính tổng của những ô gồm cùng màu sắc nền.SumCellsByFontColor (range, màu sắc code)– tính tổng của những ô tất cả cùng color chữ.Công thức rước mã màu:GetCellFontColor(cell)– trả về mã color 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ỉ vận động khi chúng ta thêm công dụng được tư tưởng 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 mang lại thấy: gồm 3 ô nền màu đỏ thể hiện 3 giao dịch đang ngơi nghỉ trạng thái hết hạn giao hàng, tổng con số hàng bị hết hạn giao là 29, cùng mã của màu đỏ được dùng trong ô là 7578FF.

Tổng kết

Vậy là bọn họ đã học xong xuôi cách đếm với tính tổng các ô theo màu bởi hàm color trong Excel rồi. Bằng phương pháp sử dụng yếu hèn tố màu sắc sắc, các chúng ta cũng có thể phân nhiều loại dữ liệu rõ ràng hơn, mặt khác tăng sự sinh động cho tệp tin Excel của mình. Nếu bạn muốn học thêm những kỹ năng hữu ích tương tự về Excel cùng code VBA Excel, hãy xem thêm các nội dung bài viết trên blog thamsannhapkhau.com cùng đăng kí những khóa học tập của thamsannhapkhau.com nhé.