Hướng dẫn sum by cell color in excel - tính tổng theo màu ô trong excel

Trong bài viết này, bạn sẽ tìm hiểu các phương pháp hiệu quả mới để tổng hợp và đếm các ô bằng màu sắc. Các giải pháp này hoạt động cho các tế bào được tô màu bằng tay và với định dạng có điều kiện trong tất cả các phiên bản của Excel 2010 đến Excel 365.

Mặc dù Microsoft Excel có nhiều chức năng cho các mục đích khác nhau, nhưng không ai có thể tính toán các ô dựa trên màu sắc của chúng. Ngoài các công cụ của bên thứ ba, chỉ có một giải pháp hiệu quả - tạo các chức năng của riêng bạn. Nếu bạn biết rất ít về các chức năng do người dùng xác định hoặc chưa bao giờ nghe về thuật ngữ này trước đây, đừng hoảng sợ. Các chức năng đã được chúng tôi viết và kiểm tra. Tất cả những gì bạn cần làm là chèn chúng vào sổ làm việc của bạn :)

Cách đếm các tế bào theo màu sắc trong Excel

Dưới đây, bạn có thể thấy mã của hai hàm tùy chỉnh (về mặt kỹ thuật, chúng được gọi là các chức năng do người dùng xác định hoặc UDF). Cái đầu tiên được mục đích để đếm các ô có màu lấp đầy cụ thể và màu khác - màu phông chữ. Cả hai đều được viết bởi Alex, một trong những bậc thầy xuất sắc nhất của chúng tôi.

Hàm CountCellsByColor (data_range là phạm vi, cell_color là phạm vi) aslongdim indrefcolor aslongdim cellcirerent as dim cntres aslong application.Volatile cntres = 0 indrefcolor = cell_color.cells . . CountCellsByColor(data_range As Range, cell_color As Range) As Long Dim indRefColor As Long Dim cellCurrent As Range Dim cntRes As Long Application.Volatile cntRes = 0 indRefColor = cell_color.Cells(1, 1).Interior.Color For Each cellCurrent In data_range If indRefColor = cellCurrent.Interior.Color Then cntRes = cntRes + 1 End If Next cellCurrent CountCellsByColor = cntRes End Function Function CountCellsByFontColor(data_range As Range, font_color As Range) As Long Dim indRefColor As Long Dim cellCurrent As Range Dim cntRes As Long Application.Volatile cntRes = 0 indRefColor = font_color.Cells(1, 1).Font.Color For Each cellCurrent In data_range If indRefColor = cellCurrent.Font.Color Then cntRes = cntRes + 1 End If Next cellCurrent CountCellsByFontColor = cntRes End Function

Khi các chức năng được thêm vào sổ làm việc của bạn, chúng sẽ thực hiện tất cả các công việc đằng sau hậu trường và bạn có thể sử dụng chúng theo cách thông thường, giống như bất kỳ chức năng Excel bản địa nào khác. Từ quan điểm của người dùng cuối, các chức năng có giao diện sau:

Đếm các ô bằng màu lấp đầy

Để đếm các ô có màu nền cụ thể, đây là chức năng sử dụng:

CountCellsByColor (data_range, cell_color)

Where:

• Data_range là một phạm vi để đếm các ô.
• Cell_color là một tham chiếu đến ô với màu điền đích.

Để đếm các ô có màu cụ thể trong một phạm vi nhất định, hãy thực hiện các bước sau:

1. Chèn mã của chức năng CountCellsByColor vào sổ làm việc của bạn.
2. Trong một ô mà bạn muốn kết quả xuất hiện, hãy bắt đầu nhập công thức: = CountCellsByColor (
3. Đối với đối số đầu tiên, hãy nhập phạm vi mà bạn muốn đếm các ô màu.
4. Đối với đối số thứ hai, cung cấp cho ô màu mục tiêu.
5. Nhấn enterkey. Xong!Enterkey. Done!

Ví dụ: để tìm hiểu có bao nhiêu ô trong phạm vi B3: F24 có cùng màu với H3, công thức là:

=CountCellsByColor(B3:F24, H3)

Trong bộ dữ liệu mẫu của chúng tôi, các ô có giá trị nhỏ hơn 150 có màu vàng và các ô có giá trị cao hơn 350 màu xanh lá cây. Chức năng được cả hai tính dễ dàng:

Đếm các ô bằng màu phông chữ

Trong trường hợp các giá trị ô của bạn có màu phông chữ khác nhau, bạn có thể đếm chúng bằng hàm này:

CountCellsByfontColor (data_range, font_color)

Where:

• Data_range là một phạm vi để đếm các ô.
• Cell_color là một tham chiếu đến ô với màu điền đích.

Để đếm các ô có màu cụ thể trong một phạm vi nhất định, hãy thực hiện các bước sau:

=CountCellsByFontColor(B3:F24, H3)

Chèn mã của chức năng CountCellsByColor vào sổ làm việc của bạn. If you'd like to name the functions differently, feel free to change the names directly in the code.

Trong một ô mà bạn muốn kết quả xuất hiện, hãy bắt đầu nhập công thức: = CountCellsByColor (

Đối với đối số đầu tiên, hãy nhập phạm vi mà bạn muốn đếm các ô màu.

Nhấn enterkey. Xong! SumCellsByColor(data_range As Range, cell_color As Range) Dim indRefColor As Long Dim cellCurrent As Range Dim sumRes Application.Volatile sumRes = 0 indRefColor = cell_color.Cells(1, 1).Interior.Color For Each cellCurrent In data_range If indRefColor = cellCurrent.Interior.Color Then sumRes = WorksheetFunction.Sum(cellCurrent, sumRes) End If Next cellCurrent SumCellsByColor = sumRes End Function Function SumCellsByFontColor(data_range As Range, font_color As Range) Dim indRefColor As Long Dim cellCurrent As Range Dim sumRes Application.Volatile sumRes = 0 indRefColor = font_color.Cells(1, 1).Font.Color For Each cellCurrent In data_range If indRefColor = cellCurrent.Font.Color Then sumRes = WorksheetFunction.Sum(cellCurrent, sumRes) End If Next cellCurrent SumCellsByFontColor = sumRes End Function

Tổng giá trị bằng màu

Tóm lại bằng cách điền màu trong Excel, đây là chức năng sử dụng:

Sumcellsbycolor (data_range, cell_color)

Where:

• Data_range là một phạm vi để tổng hợp các giá trị.
• Cell_color là một tham chiếu đến ô với màu sắc quan tâm.

Ví dụ, để thêm các giá trị của tất cả các ô trong B3: F24 được tô màu với cùng màu với H3, công thức là:

=SumCellsByColor(B3:F24, H3)

Tổng giá trị bằng màu phông chữ

Để tổng hợp các giá trị số bằng một màu phông chữ cụ thể, hãy sử dụng hàm này:

Sumcellsbyfontcolor (data_range, font_color)

Where:

• Data_range là một phạm vi để tổng hợp các ô.
• FONT_COLOR là một tham chiếu đến ô có màu phông chữ đích.

Chẳng hạn, để thêm tất cả các giá trị trong các ô B3: F24 có cùng màu phông chữ với giá trị trong H3, công thức là:

=SumCellsByFontColor(B3:F24, H3)

Đếm và tổng hợp màu trên toàn bộ sổ làm việc

Để đếm và tổng các ô có màu nhất định trong tất cả các tờ của một sổ làm việc nhất định, chúng tôi đã tạo ra hai chức năng riêng biệt, được đặt tên là WBKcountbyColor và WbksumbyColor, tương ứng. Đây là mã:

Hàm wbkcountbycolor (cell_color AS range) Dim vwbkres dim wshcien as application application.screenUpdating = false application.calculation = True Application.Calculation = xlCalculationAutomatic WbkCountByColor = vWbkRes End FunctionFunction WbkSumByColor(cell_color As Range) Dim vWbkRes Dim wshCurrent As Worksheet Application.ScreenUpdating = False Application.Calculation = xlCalculationManual vWbkRes = 0 ForEach wshCurrent In Worksheets wshCurrent.Activate vWbkRes = vWbkRes + SumCellsByColor(wshCurrent .UsedRange, cell_color) Ứng dụng tiếp theo.ScreenUpdating = true Ứng dụng WbkCountByColor(cell_color As Range) Dim vWbkRes Dim wshCurrent As Worksheet Application.ScreenUpdating = False Application.Calculation = xlCalculationManual vWbkRes = 0 For Each wshCurrent In Worksheets wshCurrent.Activate vWbkRes = vWbkRes + CountCellsByColor(wshCurrent.UsedRange, cell_color) Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic WbkCountByColor = vWbkRes End Function Function WbkSumByColor(cell_color As Range) Dim vWbkRes Dim wshCurrent As Worksheet Application.ScreenUpdating = False Application.Calculation = xlCalculationManual vWbkRes = 0 For Each wshCurrent In Worksheets wshCurrent.Activate vWbkRes = vWbkRes + SumCellsByColor(wshCurrent.UsedRange, cell_color) Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic WbkSumByColor = vWbkRes End Function

Ghi chú. Để làm cho mã của các chức năng nhỏ gọn hơn, chúng tôi đề cập đến hai hàm khác đếm và tổng trong một phạm vi được chỉ định, CountCellsByColor và SumCellsByColor. Vì vậy, để "các chức năng sổ làm việc" hoạt động, hãy chắc chắn thêm mã này vào Excel của bạn. To make the functions' code more compact, we refer to the other two functions that count and sum within a specified range, CountCellsByColor and SumCellsByColor. So, for the "workbook functions" to work, be sure to add this code to your Excel too.

Đếm các ô màu trong toàn bộ sổ làm việc

Để tìm hiểu có bao nhiêu ô của một màu cụ thể trong tất cả các tờ của một sổ làm việc nhất định, hãy sử dụng chức năng này:

WbkCountByColor(cell_color)

Hàm chỉ lấy một đối số - một tham chiếu đến bất kỳ ô nào chứa đầy màu quan tâm. Vì vậy, một công thức thực tế có thể trông giống như thế này:

=WbkCountByColor(A1)

Trong đó A1 là ô có màu lấp đầy mẫu.

Sum màu tế bào trong toàn bộ sổ làm việc

Để có được tổng số giá trị trong tất cả các ô của sổ làm việc hiện tại được tô sáng với một màu cụ thể, hãy sử dụng chức năng này:

WbkSumByColor(cell_color)

Giả sử màu mục tiêu là trong ô B1, công thức có dạng này:

=WbkSumByColor(B1)

Đếm và tổng các ô định dạng có điều kiện

Các chức năng tùy chỉnh để thêm và đếm các ô được mã hóa màu thực sự tốt, phải không? Vấn đề là chúng không hoạt động cho các tế bào được tô màu với định dạng có điều kiện, than ôi :(

Để xử lý định dạng có điều kiện, chúng tôi đã viết một mã khác (một lần nữa cho Alex!). Nó hoạt động tốt với cả định dạng đặt trước và các quy tắc dựa trên công thức tùy chỉnh. Đối chiếu với các ví dụ trước, mã này là một macro, không phải là một hàm. Số lượng vĩ mô và tổng các ô định dạng có điều kiện bằng màu lấp đầy. Vui lòng chèn nó vào trình chỉnh sửa VBA của bạn, và sau đó làm theo các hướng dẫn dưới đây.macro, not a function. The macro counts and sums conditionally formatted cells by fill color. Please insert it in your VBA Editor, and then follow the below instructions.

SUB SUMCountByConditionAlformat () Dim IndrefColor AslongDim CellScolorsample As Phạm vi DIM CNTRES ASLONGDIM SUMRES DIM CNTCELLS Màu mẫu ", _ application.selection.address, loại: = 8) ifnot (cellscolorsample isNothing) Sau đó indrefcolor = cellscolorsample.cells (1, 1). . _ "Color =" & trái ("000000", 6 - len (hex (indrefcolor))) & _ hex (indrefcolor) & vbcrlf, " SumCountByConditionalFormat() Dim indRefColor As Long Dim cellsColorSample As Range Dim cntRes As Long Dim sumRes Dim cntCells As Long Dim indCurCell As Long On Error Resume Next cntRes = 0 sumRes = 0 cntCells = Selection.CountLarge Set cellsColorSample = Application.InputBox( _ "Select sample color:", "Select a cell with sample color", _ Application.Selection.Address, Type:=8) If Not (cellsColorSample Is Nothing) Then indRefColor = cellsColorSample.Cells(1, 1).DisplayFormat.Interior.Color For indCurCell = 1 To (cntCells) 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 If End Sub

Cách đếm và tổng các ô được định dạng có điều kiện bằng cách sử dụng macro VBA

Với mã macro được chèn vào excel của bạn, đây là những gì bạn cần làm:

1. Chọn một hoặc nhiều phạm vi nơi bạn muốn đếm và tổng các ô màu. Đảm bảo (các) phạm vi đã chọn chứa dữ liệu số.
2. Nhấn ALT + F8, chọn macro SumCountByConditionalFormat trong danh sách và nhấp vào Chạy.Alt + F8, select the SumCountByConditionalFormat macro in the list, and click Run.
3. Một hộp thoại nhỏ sẽ bật lên yêu cầu bạn chọn một ô có màu mẫu. Làm điều này và nhấp vào OK.

Trong ví dụ này, chúng tôi đã sử dụng các quy tắc ô nổi bật sẵn có và nhận được kết quả sau:

• Đếm (12) Số lượng ô trong phạm vi B2: E22 có cùng màu với G3. (12) the number of cells in range B2:E22 with the same color as G3.
• SUM (1512) là tổng các giá trị trong các ô được định dạng với màu đỏ nhạt. (1512) is the sum of values in cells formatted with Light Red Fill.
• Màu sắc là mã màu thập lục phân của ô mẫu. is a hexadecimal color code of the sample cell.

Mẹo. Sổ làm việc mẫu với macro SumCountByConditionalFormat có sẵn để tải xuống ở cuối bài này. The sample workbook with the SumCountByConditionalFormat macro is available for download at the end of this post.

Làm thế nào để có được màu tế bào trong Excel

Nếu bạn cần (hoặc tò mò) để biết màu của một ô cụ thể (điền hoặc màu phông chữ), hãy thêm các chức năng do người dùng xác định sau vào excel của bạn. Nó trả về ColorIndex như một số thập phân.

Chức năng GetCellColor (Cell_REF AS Range) Dim Indrow, IndColumn aslongdim arresults () application.Volatile nếu cell_ref isnothingthenset cell_ref = application.thiscell endifif cell_ref.count> ) Cho indrow = 1 đến cell_ref.rows.count cho indcolumn = 1 to cell_ref.columns.count urresults (indrow, indcolumn) = cell_ref (indrow, indcolumn). Endifend functionfunction getfontColor (cell_ref As range) Dim Indrow, IndColumn aslongDim Arresults () application. Đếm) cho indrow = 1 đến cell_ref.rows.count cho indcolumn = 1 to cell_ref.columns.count arre Ref.Font.Color Chức năng Endifend GetCellColor(cell_ref As Range) Dim indRow, indColumn As Long Dim arResults() Application.Volatile If cell_ref Is Nothing Then Set cell_ref = Application.ThisCell End If If cell_ref.Count > 1 Then ReDim arResults(1 To cell_ref.Rows.Count, 1 To cell_ref.Columns.Count) For indRow = 1 To cell_ref.Rows.Count For indColumn = 1 To cell_ref.Columns.Count arResults(indRow, indColumn) = cell_ref(indRow, indColumn).Interior.Color Next Next GetCellColor = arResults Else GetCellColor = cell_ref.Interior.Color End If End Function Function GetFontColor(cell_ref As Range) Dim indRow, indColumn As Long Dim arResults() Application.Volatile If cell_ref Is Nothing Then Set cell_ref = Application.ThisCell End If If cell_ref.Count > 1 Then ReDim arResults(1 To cell_ref.Rows.Count, 1 To cell_ref.Columns.Count) For indRow = 1 To cell_ref.Rows.Count For indColumn = 1 To cell_ref.Columns.Count arResults(indRow, indColumn) = cell_ref(indRow, indColumn).Font.Color Next Next GetFontColor = arResults Else GetFontColor = cell_ref.Font.Color End If End Function

Ghi chú. Các chức năng chỉ hoạt động cho các màu được áp dụng bằng tay, và không định dạng có điều kiện. The functions only work for colors applied manually, and not with conditional formatting.

Nhận màu lấp đầy của một ô

Để trả về một mã thập phân của màu, một ô đã cho được tô sáng, sử dụng hàm này:

GetCellColor(cell_ref)

Ví dụ: để có được màu của ô A2, công thức là:

=GetCellColor(A2)

Nhận màu phông chữ của một ô

Để có được màu phông chữ của ô, hãy sử dụng hàm tương tự:

GetFontColor(cell_ref)

Chẳng hạn, để tìm màu phông chữ của ô E2, công thức là:

=GetFontColor(E2)

Nhận mã màu thập lục phân của một ô

Để chuyển đổi một chỉ số màu thập phân được trả về bởi các hàm tùy chỉnh của chúng tôi thành mã màu thập lục phân, hãy sử dụng hàm DEC2HEX gốc của Excel.

Ví dụ:

="#"&DEC2HEX(GetCellColor(A2))

="#"&DEC2HEX(GetFontColor(E2))

Cách chèn mã VBA vào sổ làm việc của bạn

Để thêm mã của chức năng hoặc macro vào Excel của bạn, hãy tiếp tục với 4 bước sau:

1. Trong sổ làm việc của bạn, nhấn Alt + F11 để mở Trình chỉnh sửa Visual Basic.Alt + F11 to open Visual Basic Editor.
2. Ở khung bên trái, nhấp chuột phải vào tên sổ làm việc và sau đó chọn Chèn> Mô-đun từ menu ngữ cảnh.
3. Trong cửa sổ mã, chèn mã của (các) hàm mong muốn:
   • Đếm theo màu sắc: Chức năng CountcellsByColor và CountcellsByfontColor
   • Tóm lại: SumCellsByColor và SumCellsByFontColor Chức năng
   • Đếm và tổng hợp các ô màu trong toàn bộ sách bài tập: WBKCountbyColor và WbksumbyColor
   • Đếm và tổng các ô định dạng có điều kiện: Hàm SumCountByConditionalFormat
   • Nhận mã màu: Chức năng GetCellColor và GetFontColor
4. Lưu tệp của bạn dưới dạng sổ làm việc hỗ trợ macro (.xlsm).

Nếu bạn không thoải mái lắm với VBA, bạn có thể tìm thấy các hướng dẫn từng bước chi tiết và một số mẹo hữu ích trong hướng dẫn này: Cách chèn và chạy mã VBA trong Excel.

Cách cập nhật các chức năng tùy chỉnh để cập nhật

Khi tổng hợp và đếm các ô được mã hóa màu trong Excel, xin lưu ý rằng các công thức của bạn sẽ không tính toán lại tự động sau khi tô màu thêm một vài ô hoặc thay đổi màu sắc hiện có. Xin đừng giận chúng tôi, đây không phải là lỗi trong mã của chúng tôi :)

Vấn đề là việc thay đổi màu tế bào trong Excel không kích hoạt tính toán tính toán bảng tính. Để có được các công thức để cập nhật, nhấn F9 để tính toán lại tất cả các sổ làm việc mở hoặc Shift + F9 để chỉ tính toán lại bảng hoạt động. Hoặc chỉ cần đặt con trỏ vào bất kỳ ô nào và nhấn F2, sau đó nhấn Enter. Để biết thêm thông tin, xin vui lòng xem làm thế nào để buộc tính toán lại trong Excel.changing cell color in Excel does not trigger worksheet recalculation. To get the formulas to update, press either F9 to recalculate all open workbooks or Shift + F9 to recalculate only the active sheet. Or just place the cursor into any cell and press F2, and then hit Enter. For more information, please see How to force recalculation in Excel.

Cách nhanh nhất để tính toán các tế bào màu trong excel

Nếu bạn không muốn lãng phí thời gian để mày mò với mã VBA, tôi rất vui khi giới thiệu với bạn về công cụ & tổng số rất đơn giản nhưng mạnh mẽ của chúng tôi. Cùng với hơn 70 tiện ích bổ sung tiết kiệm thời gian khác, nó được bao gồm với Ultimate Suite cho Excel.

Sau khi được cài đặt, bạn sẽ tìm thấy nó trên tab Công cụ AberBits của Ribbon Excel của bạn:

Và đây là một bản tóm tắt ngắn về những gì mà số bổ trợ màu và tổng hợp có thể làm:

• Đếm và các ô tổng bằng màu trong tất cả các phiên bản của Excel 2016 - Excel 365.
• Tìm các giá trị trung bình, tối đa và tối thiểu trong các ô màu.
• Xử lý các tế bào màu bằng tay và với định dạng có điều kiện.
• Dán kết quả bất cứ nơi nào trong bảng tính là giá trị hoặc công thức.

Tổng và đếm các ô theo một màu

Chọn tùy chọn tổng & đếm theo một màu sẽ mở ngăn sau ở phần bên trái của bảng tính của bạn. Bạn chỉ định phạm vi nguồn và ô mẫu, sau đó nhấp vào tính toán.

Kết quả sẽ xuất hiện trên khung ngay lập tức! Không có macro, không công thức, không đau :)

Ngoài đếm và tổng, bổ trợ cũng hiển thị trung bình, tối đa và tối thiểu cho các số màu. Để chèn một giá trị cụ thể trong trang tính, nhấp vào nút Dán bên cạnh nó. Hoặc nhấp vào Dán tất cả để có tất cả các kết quả được chèn cùng một lúc:

Đếm và tổng hợp tất cả các ô màu cùng một lúc

Để xử lý tất cả các ô màu tại một thời điểm, hãy chọn tổng và đếm theo tất cả tùy chọn màu. Về cơ bản, nó hoạt động theo cùng một cách, ngoại trừ thay vì màu sắc, bạn chọn hàm để tính toán.

Mẹo. Để có kết quả được chèn vào bảng tính dưới dạng công thức (chức năng tùy chỉnh), hãy kiểm tra hộp tương ứng ở dưới cùng của khung. To have the results inserted in the worksheet as formulas (custom functions), check the corresponding box at the bottom of the pane.

Chà, tính toán các tế bào màu trong Excel khá dễ dàng, phải không? Tất nhiên, nếu bạn có viên ngọc nhỏ đó làm cho phép thuật xảy ra :) tò mò muốn xem bổ trợ của chúng tôi sẽ đối phó với các tế bào màu của bạn như thế nào? Liên kết tải xuống ở ngay bên dưới.

Tải xuống có sẵn

Tổng và đếm theo màu trong Excel-ví dụ (tệp .xlsm) Ultimate Suite phiên bản đầy đủ chức năng 14 ngày (tệp .exe)
Ultimate Suite 14-day fully-functional version (.exe file)

Bạn cũng có thể quan tâm

Tôi có thể tổng hợp các tế bào trong excel dựa trên màu sắc không?

Làm cách nào để tổng hợp tất cả các ô màu trong Excel?

Làm thế nào để bạn tổng hợp nếu một ô là một màu nhất định?