Nếu như bạn đau đầu và bối rối vì những dữ liệu trùng lặp trong excel và muốn nội dung được nổi bật hơn? Vậy thì sao chúng ta không thử tô màu chúng lên dưới đây là tổng hợp các công thức hàm màu sắc trong excel nhanh và tự động sẽ giúp ích cho bạn rất nhiều. Hãy cùng Blogmausac.com tìm hiểu qua bài viết dưới này nha.

Bạn đang xem: Hàm màu sắc trong excel


Cách đổi hàm màu sắc trong excel

Hướng dẫn nhanh

Bạn bôi đen vùng cần tô màu > Nhấn chọn Conditional Formatting > New Rule để tạo định dạng mới > use a formula to determine which cells to format > Nhập công thức vào Format values where this formula is true > sau đó nhấn chọn Format > Chọn mục fill > tiếp theo chọn màu > Nhấn ok > hoàn thành.

Những hướng dẫn chi tiết đổi hàm màu sắc trong excel

Bước 1: Tô đen những vùng dữ liệu cần tô màu.

*
*
*
*
*
*
*
*
*
*
*
*

Hộp thoại trên cho chúng ta thấy: Có 3 ô nền màu đỏ thể hiện 3 đơn hàng đang ở một trạng thái quá hạn giao dịch, tổng số lượng hàng bị quá hạn gia là 29, và mã của màu đỏ được dùng trong ô là 7578 FF.

Lời kết:

Bài viết trên đã cung cấp cho bạn những kiến thức bổ ích về các hàm màu sắc trong excel. Với các yếu tố màu sắc, bạn có thể phân loại dữ liệu một cách chính xác và rõ ràng hơn, đồng thời tăng sự sinh động cho file excel của bạn. Chúng tôi hi vọng qua bài viết này bạn sẽ có những kiến thức bổ ích cũng lựa chọn cho mình những phương pháp hiệu quả. Chúc các bạn thành công.

Làm thế nào để vận dụng màu sắc trong trang tính để tăng phần sinh động cho file Excel? Nếu bạn đang băn khoăn câu hỏi này thì hàm COLOR trong Excel chính là câu trả lời bạn đang tìm kiếm. Hãy cùng emcanbaove.edu.vn học cách sử dụng hàm COLOR qua bài viết ngày hôm nay nhé.

*

Giới thiệu về hàm COLOR trong Excel

Không có gì kỳ lạ nếu đây là lần đầu tiên bạn nghe đến tên hàm COLOR trong Excel, bởi nó không nằm trong hệ thống hàm có sẵn của Excel. Thay 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 chúng ta viết một hàm COLOR mới là để đếm và tính tổng các ô Excel dựa trên màu sắc.

Nếu bạn còn chưa quen với thuật ngữ hàm UDF và tính năng viết hàm bằng lệnh VBA, hãy tham khảo bài viết dưới đây để mở khóa một thủ thuật vô cùng lợi hại trong Excel nhé.

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

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

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

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

Bước 1: Ấn tổ hợp phím Alt+F11 để mở giao diện Visual Basic Editor (VBE). Sau đó nhấn chuột phải ở tên trang tính trong mục Project - VBA Project, chọn Insert > Module.

*

Bước 2:Một bảng tính hiện ra. Các bạn nhập dòng code 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.Countar
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.Countar
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.Volatilecnt
Res = 0ind
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 Thencnt
Res = cnt
Res + 1End 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.Volatilesum
Res = 0ind
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 Thensum
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.Volatilecnt
Res = 0ind
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 Thencnt
Res = cnt
Res + 1End 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.Volatilesum
Res = 0ind
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 Thensum
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 vừa nhập code dưới tên“Excel Macro-Enabled Workbook (.xlsm)”. Như vậy, chúng ta đã xử lí xong phần code VBA.

Bước 3: Quay lại trang tính trạng thái giao hàng, ta tạo một bảng tổng hợp kết quả đếm ô theo màu. Để tiến hành phân loại và đếm số ô có màu tương ứng, ta dùng hàm COLOR trong Excel theo công thức:

=Count
Cells
By
Color(range, color code)

Trong đó:

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

Như vậy, ta sẽ nhập vào ô B15 công thức: =Count
Cells
By
Color($E$2:$E$12,A15)

*

Lưu ý: Chúng ta có thể sử dụng tham chiếu tuyệt đối để khóa phạm vi đếm.

Cách tính tổng theo màu ô bằng hàm COLOR trong Excel

Tương tự như công thức hàm COLOR phía trên, để tính tổng số liệu các ô theo màu tương ứng, chúng ta dùng hàm sau:

=Sum
Cells
By
Color(range, color code)

Trong ví dụ trên, giả sử chúng ta cần tính tổng số lượng hàng theo từng trạng thái giao hàng. Ta điền vào ô B15 công thức: =Sum
Cells
By
Color($C$2:$C$12,A15)

*

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

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

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

=Count
Cells
By
Font
Color(range, color code)

*
Cách tính tổng các ô theo màu chữ bằng hàm COLOR trong Excel

Để tính tổng các ô dựa vào màu chữ, chúng ta chỉ cần nhập công thức đơn giản như sau:

=Sum
Cells
By
Font
Color(range, color code)

*

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

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

Nếu các bạn muốn đếm và tính tổng số ô theo một màu nhất định thì hãy nhập code sau vào VBA:

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 = 0For Each wsh
Current In Worksheetswsh
Current.Activatev
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 = 0For Each wsh
Current In Worksheetswsh
Current.Activatev
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
Các bạn có thể sử dụng mã Macro phía trên cùng. công thức =Wbk
Count
Cells
Color() và =Wbk
Sum
Cells
By
Color(). Bằng cách nhập công thức vào một ô trống bất kì bên ngoài vùng lựa chọn, xác định vị trí ô chứa màu lựa chọn và nhập vị trí ô vào ngoặc đơn (), các bạn sẽ nhận được kết quả tổng các ô cùng màu trong trang tính.

Tổng hợp các chức năng tùy chỉnh màu sắc ô, màu chữ và lấy mã màu bằng hàm COLOR trong Excel:

Công thức đếm số ô bằng màu sắc:

Count
Cells
By
Color (range,color_code)
– đếm các ô có màu nền được chỉ định.Count
Cells
By
Font
Color (range,color_code)
– đế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.

Lưu ý: Tất cả các công thức chỉ hoạt động khi các bạn thêm chức năng được định nghĩa vào trang tính Excel.

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

Để đếm và tính tổng các ô màu ở bất kì định dạng có điều kiện nào, chúng ta cần nhập code VBA dưới đây:

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 = 0sum
Res = 0 cnt
Cells = Selection.Count
Largeind
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 Thencnt
Res = cnt
Res + 1sum
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
Chúng ta làm theo các bước sau:

Bước 1: Chọn vùng dữ liệu bằng số muốn thao tác.

Bước 2: Nhấn giữ phím Ctrl, chọn một ô có màu muốn thao tác trong vùng dữ liệu, thả phím Ctrl.

Bước 3: Nhấn tổ hợp Alt+F8 để mở danh sách Macros.

Bước 4: Chọn Sum
Count
By
Conditional
Format
và nhấn Run.

*

Sau khi hoàn thành, kết quả được hiển thị như trong ảnh:

*

Hộp thoại trên cho thấy: Có 3 ô nền màu đỏ thể hiện 3 đơn hàng đang ở trạng thái quá hạn giao hàng, tổng số lượng hàng bị quá hạn giao là 29, và mã của màu đỏ được dùng trong ô là 7578FF.

Tổng kết

Vậy là chúng ta đã học xong cách đếm và tính tổng các ô theo màu bằng hàm COLOR trong Excel rồi. Bằng cách sử dụng yếu tố màu sắc, các bạn có thể phân loại dữ liệu rõ ràng hơn, đồng thời tăng sự sinh động cho file Excel của mình. Nếu bạn muốn học thêm những kiến thức hữu ích tương tự về Excel và code VBA Excel, hãy đọc các bài viết trên blog emcanbaove.edu.vn và đăng kí các khóa học Excel của emcanbaove.edu.vn nhé.

Xem thêm: 【1️⃣】 5 Mẫu Báo Cáo Công Việc Tuần Excel 2023 Mới Nhất

Tất cả khóa học Excel tại emcanbaove.edu.vn đều có chương trình được xây dựng bởi chuyên gia tin học văn phòng có nhiều năm kinh nghiệm đào tạo cho nhân viên các tập đoàn lớn. Bạn sẽ học cùng chương trình như emcanbaove.edu.vn đã cung cấp cho các doanh nghiệp như Samsung, TH True Milk, Cốc Cốc, Vietinbank, VP Bank,... Trong quá trình học, giảng viên cũng luôn hỗ trợ sát sao, giải đáp 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 bài giảng nếu bạn có vấn đề chưa hiểu nhé.