Tính tổng theo màu trong excel

Làm rứa nào để vận dụng color trong trang tính để tăng phần sinh động cho file Excel? nếu bạn đang do dự câu hỏi này thì hàm màu sắc trong Excel đó là câu vấn đáp bạn đã tìm kiếm. Hãy cùng h3qvn.com học cách sử dụng hàm màu sắc qua bài viết ngày từ bây giờ nhé.

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

Tuyệt đỉnh Excel - trở nên 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 đây là lần thứ nhất bạn biết đến tên hàm color trong Excel, bởi nó không nằm trong hệ thống hàm gồm sẵn của Excel. Thế vào đó, đây là một hàm UDF (user-defined function) - hàm do người tiêu dùng tự tạo dựa vào code VBA. Mục đích họ viết một hàm màu sắc 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 cùng với thuật ngữ hàm UDF và nhân tài viết hàm bằng lệnh VBA, hãy tham khảo nội dung 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 và tính tổng các ô màu bởi hàm màu sắc trong Excel

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

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

Cách đếm số ô theo màu sắc ô bằng hàm color trong ExcelBước 1: Ấn tổng hợp phím Alt+F11 để mở hình ảnh Visual Basic Editor (VBE). Tiếp đến nhấn chuột yêu cầu ở tên trang tính trong mục Project - VBA Project, lựa chọn Insert > Module.

*

Bước 2:Một bảng tính hiện nay ra. Chúng ta nhập loại 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 xlRange.Columns.Count)For indRow = 1 lớn 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 khổng lồ 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 dưới tên“Excel Macro-Enabled Workbook (.xlsm)”. Như vậy, bọn họ đã xử lí ngừng phần code VBA.

Bước 3: quay lại trang tính tâm trạng giao hàng, ta sản xuất một bảng tổng hợp tác dụng đếm ô theo màu. Để thực hiện phân một số loại và đếm số ô bao gồm màu tương ứng, ta dùng hàm màu sắc trong Excel theo công thức:

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

Trong đó:

range: là phạm vi đếmcolor code: là code màu sắc 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 ý: bạn cũng có thể sử dụng tham chiếu tuyệt đối hoàn hảo để khóa phạm vi đếm.

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

Tương từ như phương pháp hàm màu sắc phía trên, nhằm tính tổng thể liệu các ô theo màu tương ứng, họ dùng hàm sau:

=SumCellsByColor(range, color code)

Trong lấy ví dụ trên, trả sử bọn họ cần tính tổng con số hàng theo từng tinh thần giao hàng. Ta điền vào ô B15 công thức: =SumCellsByColor($C$2:$C$12,A15)

*

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

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

Thay vì sử dụng màu ô để đếm số ô tương ứng, họ còn có thể đếm số ô theo màu 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 color chữ bởi hàm color trong Excel

Để tính tổng các ô dựa vào màu chữ, họ chỉ nên nhập công thức đơn giản 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 họ cần thêm những ô tài liệu mới, họ sẽ bắt buộc tô color ô hoặc màu sắc chữ thủ công. Công dụng số ô cùng tổng những ô đang tính đang không tự động cập nhật thêm số đông ô dữ liệu mới. Nuốm vào đó, chúng ta cần di bé trỏ chuột vào một trong những ô bất kể trong phạm vi, ấn F2 rồi Enter. Thời điểm đó Excel mới cập nhật kết quả sau cùng.

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

Nếu các bạn có nhu cầu đếm cùng tính toàn bô ô theo một màu duy 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ũng có thể sử dụng mã Macro bên 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 trong những ô trống bất kì phía bên ngoài vùng lựa chọn, xác xác định trí ô cất màu gạn lọc và nhập địa điểm ô vào ngoặc solo (), các các bạn sẽ nhận được kết quả tổng những ô cùng màu vào trang tính.

Xem thêm: 40 Hình Ảnh Nữ Sinh Đẹp Nhất

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

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

Lưu ý: tất cả các cách làm chỉ hoạt động khi chúng ta thêm chức năng được tư tưởng vào trang tính Excel.

*

Cách đếm với tính tổng các ô màu định hình có điều kiện bằng hàm màu sắc trong Excel

Để đếm với tính tổng những ô color ở bất cứ định dạng có đk nào, bọn họ cần nhập code VBA bên dưới đây:

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 khổng lồ (cntCells – 1)If indRefColor = Selection(indCurCell).DisplayFormat.Interior.Color ThencntRes = cntRes + 1sumRes = WorksheetFunction.Sum(Selection(indCurCell), sumRes)End IfNextMsgBox “Count=” và cntRes & vbCrLf & “Sum= ” và sumRes và vbCrLf & vbCrLf và _“Color=” và Left(“000000”, 6 – Len(Hex(indRefColor))) và _Hex(indRefColor) & vbCrLf, , “Count & Sum by Conditional Format color”End SubChúng ta làm cho theo quá trình sau:

Bước 1: lựa chọn vùng tài liệu bằng số muốn thao tác.Bước 2: dấn giữ phím Ctrl
, lựa chọn 1 ô có màu muốn thao tác làm việc trong vùng dữ liệu, thả phím Ctrl.Bước 3: Nhấn tổ hợp Alt+F8 nhằm mở danh sách Macros.Bước 4: chọn SumCountByConditionalFormat và nhấn Run.

*

Sau khi trả thành, tác dụng được hiển thị như trong ảnh:

*

Hộp thoại trên cho thấy: gồm 3 ô nền màu đỏ thể hiện nay 3 deals đang nghỉ ngơi trạng thái hết hạn giao hàng, tổng số lượng hàng bị hết hạn sử dung giao là 29, và mã của màu đỏ được sử dụng trong ô là 7578FF.

Tổng kết

Vậy là bọn họ đã học xong cách đếm cùng tính tổng các ô theo màu bởi hàm màu sắc 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 bạn có thể phân loại dữ liệu ví dụ hơn, bên cạnh đó tăng sự nhộn nhịp cho file Excel của mình. Nếu bạn có nhu cầu học thêm những kỹ năng hữu ích tương tự về Excel cùng code VBA Excel, hãy đọc các nội dung bài viết trên blog h3qvn.com và đăng kí các khóa học Excel của h3qvn.com nhé.

Tất cả khóa đào tạo Excel tại h3qvn.com đều phải sở hữu chương trình được kiến tạo bởi chuyên viên tin học văn phòng có không ít năm kinh nghiệm tay nghề đào tạo nên nhân viên các tập đoàn lớn. Các bạn sẽ học cùng công tác như h3qvn.com đã cung ứng cho những doanh nghiệp như Samsung, TH True Milk, cốc Cốc, Vietinbank, VP Bank,... Trong quy trình học, giáo viên cũng luôn cung ứng sát sao, câu trả lời mọi thắc mắc của các bạn trong vòng. Hãy đặt câu hỏi dưới video clip bài giảng nếu bạn có vụ việc chưa đọc nhé.