Trong bài viết này, Học Excel Online sẽ đếm các ô tính bằng màu sắc và tính tổng các ô đượ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 các ô tính bằng nhiều màu trong Excel 2010, 2013, 2016.

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


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

Giả sử bạn có bảng liệt kê danh sách đơn đặc hàng của công ty, trong đó cột Delivery được tô màu dựa trên giá trị 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 các ô 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.

Mở bảng tính Excel và nhấn Alt + F11 để mở Visual Basic Editor (VBE).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.

*
Module to add a new user-defined function to your worksheet." />

Thêm đoạn mã sau vào bảng tính.
Function Get
Cell
Color(xl
Range As Range)
*

*

Dim ind
Row, ind
Column As Long

Dim ar
Results()

Application.Volatile

If xl
Range Is Nothing Then

Set xl
Range = Application.This
Cell

End If

If xl
Range.Count > 1 Then

Re
Dim ar
Results(1 To xl
Range.Rows.Count, 1 To xl
Range.Columns.Count)

For ind
Row = 1 To xl
Range.Rows.Count

For ind
Column = 1 To xl
Range.Columns.Count

ar
Results(ind
Row, ind
Column) = xl
Range(ind
Row, ind
Column).Interior.Color

Next

Next

Get
Cell
Color = ar
Results

Else

Get
Cell
Color = xl
Range.Interior.Color

End If

End Function

Function Get
Cell
Font
Color(xl
Range As Range)

Dim ind
Row, ind
Column As Long

Dim ar
Results()

Application.Volatile

If xl
Range Is Nothing Then

Set xl
Range = Application.This
Cell

End If

If xl
Range.Count > 1 Then

Re
Dim ar
Results(1 To xl
Range.Rows.Count, 1 To xl
Range.Columns.Count)

For ind
Row = 1 To xl
Range.Rows.Count

For ind
Column = 1 To xl
Range.Columns.Count

ar
Results(ind
Row, ind
Column) = xl
Range(ind
Row, ind
Column).Font.Color

Next

Next

Get
Cell
Font
Color = ar
Results

Else

Get
Cell
Font
Color = xl
Range.Font.Color

End If

End Function

Function Count
Cells
By
Color(r
Data As Range, cell
Ref
Color As Range) As Long

Dim ind
Ref
Color As Long

Dim cell
Current As Range

Dim cnt
Res As Long

Application.Volatile

cnt
Res = 0

ind
Ref
Color = cell
Ref
Color.Cells(1, 1).Interior.Color

For Each cell
Current In r
Data

If ind
Ref
Color = cell
Current.Interior.Color Then

cnt
Res = cnt
Res + 1

End If

Next cell
Current

Count
Cells
By
Color = cnt
Res

End Function

Function Sum
Cells
By
Color(r
Data As Range, cell
Ref
Color As Range)

Dim ind
Ref
Color As Long

Dim cell
Current As Range

Dim sum
Res

Application.Volatile

sum
Res = 0

ind
Ref
Color = cell
Ref
Color.Cells(1, 1).Interior.Color

For Each cell
Current In r
Data

If ind
Ref
Color = cell
Current.Interior.Color Then

sum
Res = Worksheet
Function.Sum(cell
Current, sum
Res)

End If

Next cell
Current

Sum
Cells
By
Color = sum
Res

End Function

Function Count
Cells
By
Font
Color(r
Data As Range, cell
Ref
Color As Range) As Long

Dim ind
Ref
Color As Long

Dim cell
Current As Range

Dim cnt
Res As Long

Application.Volatile

cnt
Res = 0

ind
Ref
Color = cell
Ref
Color.Cells(1, 1).Font.Color

For Each cell
Current In r
Data

If ind
Ref
Color = cell
Current.Font.Color Then

cnt
Res = cnt
Res + 1

End If

Next cell
Current

Count
Cells
By
Font
Color = cnt
Res

End Function

Function Sum
Cells
By
Font
Color(r
Data As Range, cell
Ref
Color As Range)

Dim ind
Ref
Color As Long

Dim cell
Current As Range

Dim sum
Res

Application.Volatile

sum
Res = 0

ind
Ref
Color = cell
Ref
Color.Cells(1, 1).Font.Color

For Each cell
Current In r
Data

If ind
Ref
Color = cell
Current.Font.Color Then

sum
Res = Worksheet
Function.Sum(cell
Current, sum
Res)

End If

Next cell
Current

Sum
Cells
By
Font
Color = sum
Res

End Function

Lưu bảng tính thành “Excel Macro-Enabled Workbook (.xlsm)”.Như vậy, tất cả những thao tác lặt vặ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 Count
Cells
By
Color.=Count
Cells
By
Color(range, color code)

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

Tương tự, bạn viết công thức để đếm các ô 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 có dữ liệu số trong ô màu, như cột Qty, bạn có thể thêm giá trị dựa vào màu bằng cách sử dụng hàm Sum
Cells
By
Color

=Sum
Cells
By
Color(range, color code) 

*

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

Tương tự, bạn có thể đếm ô và tính tổng các ô tính bằng màu thông qua hàm Count
Cells
By
Font
Color và Sum
Cells
By
Font
Color.

*

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

Trên thực tế, đó là đặc điểm bình thường của tất cả các Macro, VBA scripts và hàm User-Defined. Vấn đề là tất cả các 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 không nhận thấy sự thay đổi màu chữ hoặc màu sắc của ô này. Vì vậy, sau khi tô màu các ô 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 chính xác những gì Connor yêu cầu, cụ thể là đếm và tính tổng các ô cùng màu trong tất cả trang tính của bảng tính. Vì vậy, ở đây có mã số:

Function Wbk
Count
Cells
By
Color(cell
Ref
Color As Range)

Dim v
Wbk
Res

Dim wsh
Current As Worksheet

Application.Screen
Updating = False

Application.Calculation = xl
Calculation
Manual

v
Wbk
Res = 0

For Each wsh
Current In Worksheets

wsh
Current.Activate

v
Wbk
Res = v
Wbk
Res + Count
Cells
By
Color(wsh
Current.Used
Range, cell
Ref
Color)

Next

Application.Screen
Updating = True

Application.Calculation = xl
Calculation
Automatic

Wbk
Count
Cells
By
Color = v
Wbk
Res

End Function

Function Wbk
Sum
Cells
By
Color(cell
Ref
Color As Range)

Dim v
Wbk
Res

Dim wsh
Current As Worksheet

Application.Screen
Updating = False

Application.Calculation = xl
Calculation
Manual

v
Wbk
Res = 0

For Each wsh
Current In Worksheets

wsh
Current.Activate

v
Wbk
Res = v
Wbk
Res + Sum
Cells
By
Color(wsh
Current.Used
Range, cell
Ref
Color)

Next

Application.Screen
Updating = True

Application.Calculation = xl
Calculation
Automatic

Wbk
Sum
Cells
By
Color = v
Wbk
Res

End Function

Bạn sử dụng Macro tương tự như mã trước đó và sử dụng kết quả của các công thức =Wbk
Count
Cells
Color() và =Wbk
Sum
Cells
By
Color(). 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 địa chỉ ô tính chứa màu bạn muốn vào ngoặc đơn, ví dụ = Wbk
Sum
Cells
By
Color (A1), và công thức sẽ hiển thị tổng của tất cả các ô được tô cùng trong bảng tính của bạn.

Tùy chỉnh các 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ả các chức năng chúng ta đã sử dụng trong ví dụ này cũng như một vài cái chức năng mới để lấy mã màu.

Chú ý. Hãy nhớ rằng tất cả các công thức chỉ hoạt động nếu bạn đã thêm chức năng do người dùng định nghĩa vào bảng tính Excel như đã trình bày ở trên.

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

Count
Cells
By
Color (phạm vi, mã màu) – đếm các ô có 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 các ô theo màu =Count
Cells
By
Color (F2: F14, A17), trong đó F2:F14 là vùng dữ liệu đã chọn và A17 là ô có màu nền cần thiết. Bạn có thể sử dụng tất cả các công thức khác được liệt kê dưới đây theo cách tương tự.

Count
Cells
By
Font
Color (phạm vi, mã màu) – đế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:

Sum
Cells
By
Color (range, color code) – tính tổng của các ô có cùng màu nền.

Sum
Cells
By
Font
Color (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:

Get
Cell
Font
Color (cell) – trả về mã màu chữ của một ô xác định.

Get
Cell
Color (cell) – trả về mã màu nền của một ô xác định.

*

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

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

Sub Sum
Count
By
Conditional
Format()

Dim ind
Ref
Color As Long

Dim cell
Current As Range

Dim cnt
Res As Long

Dim sum
Res

Dim cnt
Cells As Long

Dim ind
Cur
Cell As Long

cnt
Res = 0

sum
Res = 0

cnt
Cells = Selection.Count
Large

ind
Ref
Color = Active
Cell.Display
Format.Interior.Color

For ind
Cur
Cell = 1 To (cnt
Cells – 1)

If ind
Ref
Color = Selection(ind
Cur
Cell).Display
Format.Interior.Color Then

cnt
Res = cnt
Res + 1

sum
Res = Worksheet
Function.Sum(Selection(ind
Cur
Cell), sum
Res)

End If

Next

Msg
Box “Count=” & cnt
Res & vb
Cr
Lf & “Sum= ” & sum
Res & vb
Cr
Lf & vb
Cr
Lf & _

“Color=” & Left(“000000”, 6 – Len(Hex(ind
Ref
Color))) & _

Hex(ind
Ref
Color) & vb
Cr
Lf, , “Count & Sum by Conditional Format color”

End Sub

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

Thêm đoạn mã trên vào trang tính như đã giải thích ở ví dụ.Chọn một dãy hoặc nhiều vùng dữ liệu bạn cần nếu bạn có dữ liệu dạng số.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.Nhấn Alt+F8 để mở danh sách macros trong bảng tính.Chọn Sum
Count
By
Conditional
Format 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à có các 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 giá trị các ô 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 không nào. Không chỉ có 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 các báo cáo tự động, điều khiển các chương trình khác như word, power point, outlook… giúp biến file excel của bạn trở thành 1 phần mềm chuyên nghiệp…

Để có thể học VBA một cách đầy đủ, có hệ thống, bạn hãy tham gia khoá học VBA101 – VBA cơ bản dành cho người mới bắt đầu của hệ thống Học Excel Online. Hiện nay hệ thống đang có nhiều ưu đãi khi bạn đăng ký khóa học này. Chi tiết xem tại:

Sử dụng màu sắc trong bảng tính Excel sẽ giúp dữ liệu của bạn sinh động, dễ phân biệt và tìm kiếm hơn. Vậy làm thế nào để đếm ô theo màu một cách nhanh chóng? Bài viết sau đây sẽ mách bạn 3 cách thực hiện tính năng này, chắc chắn sẽ hữu ích cho công việc của bạn đấy.

1. Kết hợp hàm SUBTOTAL và công cụ lọc theo màu

Cách này dựa trên 2 tính năng cực kỳ tiện lợi của Excel:

Hàm SUBTOTAL là hàm ứng dụng linh hoạt trong nhiều trường hợp khác nhau: tính tổng, trung bình cộng, đếm số ô,… Ở đây, bạn sử dụng hàm SUBTOTAL với chức năng đếm ô theo màu để phân biệt các ô với nhau.Công cụ lọc màu (Filter) trong Excel: giúp lọc các ô được tô theo màu, bạn có thể tùy chọn một hay nhiều màu sắc mà mình muốn hiển thị.

2. Dùng hàm SUBTOTAL để đếm ô theo màu.

Bước 1: Nhập công thức =SUBTOTAL(2;C2:C11)

Trong đó:

2: tương đương hàm COUNT, dùng để đếm các ô có màu trong vùng dữ liệu.C2:C11: vùng ô cần đếm.
*

Bước 2: Nhấn Enter và nhận kết quả.

*

3. Dùng bộ lọc Filter (Autofilter) để lọc các ô theo màu tương ứng.

Bước 1: Tại thẻ Data trên thanh công cụ, nhấn chọn Filter để áp dụng bộ lọc màu cho bảng dữ liệu.


*

*

*

4. Kết hợp hàm COUNTIF với hàm GET.CELL

Dùng hàm GET.CELL để xác định mã màu của ô.

Bước 1: Nhấp chuột vào ô E2. Sau đó, nhấn chọn Define Name trong tab Formulas


*

Bước 2: Hộp thoại New name xuất hiện.

*
Mục Name: nhập tên bất kỳ. Ở đây, mình nhập là KQScope: Workbook
Refers to: nhập công thức =GET.CELL(38;Sheet1!$E2)

Trong đó:

38: tham số xác định màu nền trong ô.Sheet1!$E2: là chỉ vị trí ô bắt đầu xác định màu. Ở đây, bạn chỉ cố định cột C, không cố định dòng 2 (để hàm có thể sử dụng cho các dòng khác ở cột C)

Bước 3: Viết công thức =KQ vào ô F2 bên cạnh ô E2, nhấn OK để hiển thị kết quả. Sau đó, copy công thức cho các dòng còn lại trong cột F.


Dùng hàm COUNTIFS để đếm ô với điều kiện mã màu.

Nhập công thức =COUNTIFS(F2:F11;”=20″), rồi nhấn Enter để hiển kết quả.


Làm tương tự với các mã màu còn lại.

Xem thêm: Tổng hợp các bài viết hướng dẫn sử dụng excel 2016, cách sử dụng excel 2016

Lời kết

Như vậy, bạn có thể đếm ô theo màu bằng 2 cách:

Kết hợp hàm SUBTOTAL và công cụ lọc màu
Kết hợp hàm GET CELL và COUNTIFS

Hy vọng bạn đã nhận được những kiến thức hữu ích cho công việc của mình. Cám ơn bạn đã xem bài viết.