Tính Tổng Các Ô Dựa Trên Màu Của Chúng Trong Excel

Tính Tổng Các Ô Dựa Trên Màu Của Chúng Trong Excel

Ví dụ: Mình sở hữu bảng dữ liệu như bên dưới:

Yêu cầu: Mình sẽ phải tính tổng vào những trị giá ô sở hữu màu.

Để làm được tương tự, mình sẽ hướng dẫn 3 cách làm nhanh mà thuần tuý:

Tính tổng theo màu với hàm SUBTOTAL 

Thường thì để tính tổng, bạn sẽ sử dụng hàm SUM để tính. Tuy nhiên, trong trường hợp này, mình sẽ sử dụng hàm SUBTOTAL để tính tổng theo màu để lúc bạn lọc chọn ra điều kiện thì hàm SUBTOTAL sẽ cập nhật lại kết quả nhanh chóng.

Những bước như sau:

Bước 1: Tại ô cần hiển thị kết quả tính tổng, bạn gõ công thức sau: =SUBTOTAL(9,C3:C17)

Giảng giải: 9: Số thứ tự để tính tổng.
            C3:C17: Vùng dữ liệu muốn tính tổng.

Bước 3: Bạn sẽ chọn bộ lọc tại tiêu đề cột tính tổng, tiếp theo chọn vào Color Filter và chọn màu muốn hiển thị.

Sau lúc bạn chọn màu để lọc hiển thị ra. Thì dưới ô hiển thị kết quả tính tổng sẽ tự cập nhật lại tổng những trị giá đang hiển thị.

Tính tổng theo màu với hàm VBA

Hiện nay ko sở hữu công thức Excel sở hữu sẵn sở hữu thể làm được cho bạn như những gì những bạn muốn. Tuy nhiên, bạn sở hữu thể làm là sử dụng VBA rất thuần tuý và tạo một hàm riêng cho bạn và sau đó bạn sở hữu thể sử dụng hàm tùy chỉnh ở đó bất cứ đâu trong trang tính hoặc trong sổ làm việc và lấy tổng những ô màu.

Những bước thực hiện như sau:

Bước 1: Tại trang tính chứa dữ liệu cần tính tổng. Bạn gõ phím tắt Alt + F11.

Bước 2: Hiện ra giao diện VBA

Bước 3: Tại module mới tạo, bạn gõ đoạn code như sau:

Bước 4: Nhấn vào Run trên thành phương tiện hoặc nhấn F5 để chạy chương trình nhé!

Bước 5: Bạn sẽ thoát ra ngoài file Excel, bạn chọn Hai ô trống bất kì: Một ô hiển thị màu cam, Một ô hiển thị màu xanh.

Bước 6: Tại ô màu xanh hoặc Một ô bất kì bạn muốn tính tổng những trị giá trong ô màu xanh, bạn gõ công thức sau: =SumByColor(C3:C17,F4)

Trong đó:

  •     Hàm SumByColor: là hàm mình đã tạo trong giao diện VBA.
  •     C3:C17: Cột dữ liệu muốn tính tổng theo màu.
  •     F4: Ô chứa màu xanh muốn tính tổng.    

Tương tự để tính tổng những trị giá theo vùng màu cam, bạn gõ công thức sau: =SumByColor(C3:C17,F5)

Trong đó:

  •     Hàm SumByColor: là hàm mình đã tạo trong giao diện VBA.
  •     C3:C17: Cột dữ liệu muốn tính tổng theo màu.
  •     F5: Ô chứa màu cam muốn tính tổng.    

Tính tổng theo màu bằng cách sử dụng Get.CELL + Mẹo Named Range

Trước lúc để tính tổng từng màu những trị giá trong ô, thì bạn sẽ phải cho hiển thị số tượng trưng cho những màu đó bằng cách:

Trước tiên, bạn chọn vào Một ô trống bất kì để hiển thị trị giá số tượng trưng.

Hiển thị ra bảng Name Manager, bạn chọn vào New để tạo ra tên mới. Tiếp tới, hiển thị bảng New Name

Tại ô Name: bạn gõ tên bất kì
Tại ô Refers to: bạn gõ như sau: =Get.Cell(38,C3)

Giảng giải: 38: yêu cầu công thức này trích xuất trị giá màu của ô mà nó tham chiếu tới
                  B2: khởi đầu tính trị giá từ vựng trí này trở đi.

Tiếp theo, nhấn OK để hoàn thành.

Sau lúc hoàn thành xong việc đặt tên, bạn chọn một ô ngay cạnh trị giá muốn tính tổng và gõ: =Sumvalue

Để tính những ô tiếp theo, bạn cũng đặt con chuột tại vị trí cuối ô, hiển thị dấu cùng và tích chuột trái Hai lần để hiển thị những trị giá khác.

Article post on: nongdanmo.com

Sau lúc hiển thị, thì chúng ta biết được trị giá tượng trưng của những màu: màu xanh là số 37, màu cam là số 40.

Tiếp theo, để tính tổng những trị giá sở hữu màu xanh, bạn gõ: =SUMIF($D$3:$D$17,37,$C$3:$C$17)

Giảng giải: $D$3:$D$17: Vùng dữ liệu chứa trị giá số tượng trưng cho màu.
                   $C$3:$C$17: Vùng dữ liệu chứa những trị giá tính tổng.

Tương tự, để tính tổng những trị giá sở hữu màu cam, bạn gõ: =SUMIF($D$3:$D$17,40,$C$3:$C$17)        

Vậy là mình đã hướng dẫn cho bạn xong mẹo tính tổng những ô dựa trên những trị giá của chúng. Kỳ vọng bài viết này sẽ giúp ích cho bạn. Nếu thấy hay đừng quên thẩm định bài viết ngay bên dưới nhé! Trân trọng.


--- Cập nhật: 13-01-2023 --- nongdanmo.com tìm được thêm bài viết Hướng dẫn cách đếm và tính tổng dựa theo màu sắc của ô tính bằng VBA từ website blog.hocexcel.online cho từ khoá tính tổng những ô cùng màu trong excel.

Trong bài viết này, Học Excel Online sẽ đếm những ô tính bằng màu sắc và tính tổng những ô được tô màu. Những giải pháp này hiệu quả đối với cả ô tính tô màu thủ công và ô tính được định dạng 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 màu trong Excel 2010, 2013, 2016.

Cách đếm và tính tổng bằng màu trong trang tính Excel

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

Những gì chúng ta muốn là tự động tính những ô theo màu sắc, tức là tính số ô màu đỏ, xanh lá cây và cam trong bảng tính. Hãy tiếp tục với 5 bước dưới đây và bạn sẽ biết số ô mỗi màu và tổng của chúng.

  1. Mở bảng tính Excel và nhấn Alt + F11 để mở Visual Basic Editor (VBE).
  2. Nhấp chuột phải vào tên bảng tính dưới Project-VBAProject ở phía tay phải của màn hình, sau đó chọn Insert > Module từ danh sách tùy chọn.
  1. Thêm đoạn mã sau vào bảng tính.
Function GetCellColor(xlRange As Range) Dim indRow, indColumn As Long Dim arResults()   Application.Volatile   If xlRange Is Nothing Then Set xlRange = Application.ThisCell End If   If xlRange.Count > Một Then ReDim arResults(Một To xlRange.Rows.Count, Một To xlRange.Columns.Count) For indRow = Một To xlRange.Rows.Count For indColumn = Một To xlRange.Columns.Count arResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Color Next Next GetCellColor = arResults Else GetCellColor = xlRange.Interior.Color End If End Function   Function GetCellFontColor(xlRange As Range) Dim indRow, indColumn As Long Dim arResults()   Application.Volatile   If xlRange Is Nothing Then Set xlRange = Application.ThisCell End If   If xlRange.Count > Một Then ReDim arResults(Một To xlRange.Rows.Count, Một To xlRange.Columns.Count) For indRow = Một To xlRange.Rows.Count For indColumn = Một To xlRange.Columns.Count arResults(indRow, indColumn) = xlRange(indRow, indColumn).Font.Color Next Next GetCellFontColor = arResults Else GetCellFontColor = xlRange.Font.Color End If   End Function   Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long Dim indRefColor As Long Dim cellCurrent As Range Dim cntRes As Long   Application.Volatile cntRes = 0 indRefColor = cellRefColor.Cells(1, 1).Interior.Color For Each cellCurrent In rData If indRefColor = cellCurrent.Interior.Color Then cntRes = cntRes + Một End If Next cellCurrent   CountCellsByColor = cntRes End Function   Function SumCellsByColor(rData As Range, cellRefColor As Range) Dim indRefColor As Long Dim cellCurrent As Range Dim sumRes   Application.Volatile sumRes = 0 indRefColor = cellRefColor.Cells(1, 1).Interior.Color For Each cellCurrent In rData If indRefColor = cellCurrent.Interior.Color Then sumRes = WorksheetFunction.Sum(cellCurrent, sumRes) End If Next cellCurrent   SumCellsByColor = sumRes End Function   Function CountCellsByFontColor(rData As Range, cellRefColor As Range) As Long Dim indRefColor As Long Dim cellCurrent As Range Dim cntRes As Long   Application.Volatile cntRes = 0 indRefColor = cellRefColor.Cells(1, 1).Font.Color For Each cellCurrent In rData If indRefColor = cellCurrent.Font.Color Then cntRes = cntRes + Một End If Next cellCurrent   CountCellsByFontColor = cntRes End Function   Function SumCellsByFontColor(rData As Range, cellRefColor As Range) Dim indRefColor As Long Dim cellCurrent As Range Dim sumRes   Application.Volatile sumRes = 0 indRefColor = cellRefColor.Cells(1, 1).Font.Color For Each cellCurrent In rData If indRefColor = cellCurrent.Font.Color Then sumRes = WorksheetFunction.Sum(cellCurrent, sumRes) End If Next cellCurrent   SumCellsByFontColor = sumRes End Function
  1. Lưu bảng tính thành “Excel Macro-Enabled Workbook (.xlsm)”.
  2. Tương tự, tất cả những thao tác nhỏ nhặt đã thực hiện xong, chỉ cần thêm hàm được chỉ định, chọn ô tính muốn hiển thị công thức và gõ công thức CountCellsByColor.

=CountCellsByColor(range, color code)

Trong ví dụ này, chúng ta sử dụng công thức = CountCellsByColor (F2:F14, A17) trong đó F2: F14 là dãy chứa những ô sở hữu màu bạn muốn đếm và A17 là ô sở hữu màu nền nhất định, trong ví dụ đó là những ô màu đỏ.

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

Nếu bạn sở hữu dữ liệu số trong ô màu, như cột Qty, bạn sở hữu thể thêm trị giá dựa vào màu bằng cách sử dụng hàm SumCellsByColor

=SumCellsByColor(range, color code) 

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

Source: nongdanmo.com

Tương tự, bạn sở hữu thể đếm ô và tính tổng những ô tính bằng màu thông qua hàm CountCellsByFontColor SumCellsByFontColor.

Chú ý. Sau lúc 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ẽ ko được tính lại tự động để phản ánh những thay đổi.

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

Tính tổng và đếm số ô bằng màu sắc trên toàn bộ bảng tính:

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

Function WbkCountCellsByColor(cellRefColor 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, cellRefColor) Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic   WbkCountCellsByColor = vWbkRes End Function   Function WbkSumCellsByColor(cellRefColor 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, cellRefColor) Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic   WbkSumCellsByColor = vWbkRes End Function

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

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

Ở đây, bạn sẽ tìm thấy một bản tóm tắt tất cả những chức năng chúng ta đã sử dụng trong ví dụ này cũng như một vài dòng chức năng mới để lấy mã màu.

Chú ý. Hãy nhớ rằng tất cả những công thức chỉ hoạt động nếu bạn đã thêm chức năng do người sử dụng khái niệm vào bảng tính Excel như đã trình bày ở trên.

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

CountCellsByColor (phạm vi, mã màu) – đếm những ô sở hữu màu nền được chỉ định.

Trong ví dụ trên, chúng tôi sử dụng công thức sau để đếm những ô theo màu =CountCellsByColor (F2: F14, A17), trong đó F2:F14 là vùng dữ liệu đã chọn và A17 là ô sở hữu màu nền cần thiết. Bạn sở hữu thể sử dụng tất cả những công thức khác được liệt kê dưới đây theo cách tương tự.

CountCellsByFontColor (phạm vi, mã màu) – đếm những ô 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 những ô sở hữu cùng màu nền.

SumCellsByFontColor (range, color code) – tính tổng của những ô sở hữu 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.

Via @: nongdanmo.com

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

VBA dưới đây sẽ hiển thị số ô màu và tổng những trị giá trong ô, bất kể loại định dạng sở hữu điều kiện nào được sử dụng.

Sub SumCountByConditionalFormat() Dim indRefColor As Long Dim cellCurrent As Range Dim cntRes As Long Dim sumRes Dim cntCells As Long Dim indCurCell As Long   cntRes = 0 sumRes = 0   cntCells = Selection.CountLarge indRefColor = ActiveCell.DisplayFormat.Interior.Color   For indCurCell = Một To (cntCells – 1) If indRefColor = Selection(indCurCell).DisplayFormat.Interior.Color Then cntRes = cntRes + Một 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 Sub

Cách sử dụng mã để đếm số ô màu và tính tổng trị giá của chúng

  1. Thêm đoạn mã trên vào trang tính như đã giảng giải ở ví dụ.
  2. Chọn một dãy hoặc nhiều vùng dữ liệu bạn cần nếu bạn sở hữu dữ liệu dạng số.
  3. Nhấn giữ phím Ctrl, chọn một ô tính với màu cần thao tác, thả phím Ctrl.
  4. Nhấn Alt+F8 để mở danh sách macros trong bảng tính.
  5. Chọn SumCountByConditionalFormat và 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à sở hữu những con số sau:

  • Count là số ô tính trong một màu cụ thể, như số ô màu đỏ trong ví dụ được đánh dấu là “Past Due”.
  • Sum là tổng trị giá những ô màu đỏ trong cột Qty, cụ thể là tổng số ô “Past Due”.
  • Color là mã màu của ô được chọn, cụ thể là ô D2.

Qua bài viết này chúng ta thấy việc sử dụng VBA trong Excel đem lại hiệu quả rất nhiều trong công việc đúng ko nào. Ko chỉ sở hữu vậy, VBA còn làm được nhiều điều thú vị hơn nữa như: giúp bạn tăng tốc trong quá trình xử lý trên excel, giúp lập những báo cáo tự động, điều khiển những chương trình khác như word, power point, outlook… giúp biến file excel của bạn trở thành Một phần mềm giỏi… Để sở hữu thể học VBA một cách đầy đủ, sở hữu hệ thống, bạn hãy tham gia khoá học VBA101 – VBA cơ bản dành cho người mới khởi đầu của hệ thống Học Excel Online. Hiện nay hệ thống đang sở hữu nhiều ưu đãi lúc bạn đăng ký khóa học này. Chi tiết xem tại:


--- Cập nhật: 24-01-2023 --- nongdanmo.com tìm được thêm bài viết Cách đếm số ô và tính tổng dựa trên màu nền trên file Excel từ website blogchiasekienthuc.com cho từ khoá tính tổng những ô cùng màu trong excel.

Như những bạn đã biết thì trong những bảng tính Excel, để làm vượt bậc những trị giá, dữ liệu trong ô tính thì chúng ta thường sử dụng cách tô màu nền Background. Điều này giúp chúng ta tiện dụng làm việc và hạn chế nhầm lẫn giữa những ô tính hơn.

Tuy nhiên, đối với những bảng tính dài thì việc đếm những trị giá tiêu biểu vẫn sẽ gặp đôi chút khó khăn, nếu chúng ta thực hiện theo cách thủ công.

Chính vì thế, để tương trợ những bạn trong việc đếm ô và tính tổng những trị giá ô sở hữu màu Background tương ứng mà bạn đã thiết lập, thì trong bài viết ngày hôm nay mình sẽ san sẻ với bạn thủ thuật sử dụng code VBA và hàm Colorfunction, để đếm ô và tính tổng những trị giá tương ứng trong ô dựa trên màu sắc.

Nghe đã thấy rất thú vị rồi phải ko nào 😀 Vậy thì khởi đầu luôn nha những bạn!

Đọc thêm:

  • Cách tô màu trị giá MIN, MAX trên bảng tính Excel, thuần tuý!
  • Hướng dẫn tô màu trị giá theo điều kiện trong Excel thuần tuý
  • Tự động tô màu hàng, cột lúc click chuột vào ô trong Excel
  • Cách tô màu xen kẽ những dòng, những cột trong Excel vô cùng thuần tuý

#1. Đếm số ô theo màu nền Background

+ Bước 3: Tiếp theo bạn Copy đoạn code sau….

… và Paste vào hộp thoại Module như hình bên dưới:

+ Bước 4: Hiện tại bạn quay trở lại file Excel của mình. Lúc này bạn nhập công thức vào vị trí hiển thị số ô chứa Background tương ứng.

Chúng ta sẽ sử dụng công thức sau:

Vận dụng vào ví dụ trên ta được công thức: =colorfunction(B2;$B$2;$E$7;FALSE)

+ Bước 5: Ta được kết quả như hình dưới. Cuối cùng là bạn Fill công thức xuống danh sách để vận dụng cho tất cả bảng tính.

Và đây là kết quả đếm số ô theo màu nền Background trên bảng tính.

#2. Tính tổng trị giá những ô sở hữu cùng màu nền Background

Để tính tổng những trị giá của ô sở hữu chung màu nền Background bạn cũng thực hiện công thức tương tự. Chỉ sửa kết quả xuất trong công thức là TRUE (trị giá thật), chứ ko phải FALSE (trị giá sai).

Vận dụng vào ví dụ cụ thể: =colorfunction(B2;$B$2;$E$7;TRUE)

Rồi nhấn Enter để được kết quả như hình dưới.

Cuối cùng bạn Fill ô công thức xuống danh sách để vận dụng cho những ô còn lại thôi.

#3. Lời kết

Tương tự là mình vừa trình bày với những bạn rất chi tiết cách đếm số ô theo màu và tính tổng những trị giá trong ô dựa trên màu sắc của ô trong Excel rồi nha.

Tới đây thì bài hướng dẫn đếm số ô cùng màu và tính tổng dựa trên màu nền trong file Excel của mình cũng xin được tạm giới hạn. Hi vọng thủ thuật này sẽ hữu ích trong công việc của những bạn.

Chúc những bạn thành công !

CTV: Lương Trung – Blogchiasekienthuc.com

Article post on: nongdanmo.com

Recommended For You

About the Author: Bảo