Hướng dẫn dùng hàm màu sắc trong Excel để đếm và tính tổng

Khi làm việc trên bảng tính Excel, chắc hẳn chúng ta sẽ thường xuyên bối rối khi phải xử lý những dữ liệu trùng nhau. Tuy nhiên, chúng ta cũng không cần quá lo lắng bởi Excel đã tích hợp hàm màu sắc để làm nổi bật những dữ liệu trùng nhau. Đọc ngay để tìm hiểu về hàm màu sắc trong Excel, hàm tô màu có điều kiện, hàm IF với điều kiện màu và code VBA đổi màu trong Excel. 

Nội Dung Chính

Hàm màu sắc trong Excel

Không có điều gì kỳ lạ khi bạn chưa từng tiếp xúc hay sử dụng hàm màu sắc trong Excel bởi vốn dĩ nó không phải một hàm có sẵn của Excel. Đây chính là hàm COLOR, một hàm User Defined Function viết tắt là UDF – Đây chính là hàm tự tạo bởi người dùng trên giao diện của VBA. Công dụng của hàm này chính là đếm và tính tổng toàn bộ các ô dữ liệu trùng nhau dựa trên màu sắc.

Hàm màu sắc trong Excel

Hàm màu sắc trong Excel

Thuật ngữ hàm UDF vẫn là một thuật ngữ mới, đây chính là tính năng cho phép viết hàm tự do bằng VBA. Cách đếm và tính tổng các ô trùng nhau bằng hàm COLOR như sau: 

Bước 1: Nhấn tổ hợp phím Alt + F11để mở giao diện VBA (Visual Basic Editor). Tiếp đó, vào mục Project trong VBA Project, nhấn đúp chuột phải vào trang tính, chọn Insert, chọn mục Module.

Bước 2: Một bảng tính sẽ hiện lên, lúc này bạn hãy copy dòng code dưới đây và dán vào:

Function GetCellColor(xlRange As Range)

End If

If xlRange.Count > 1 Then

Dim arResults()

Application.Volatile

Set xlRange = Application.ThisCell

For indRow = 1 To xlRange.Rows.Count

Next

GetCellColor = arResul

GetCellColor = xlRange.Interior.Color

Dim arResults()

If xlRange Is Nothing Then

End If 

Function GetCellFontColor(xlRange As Range)

End If

End Function

Dim indRefColor As Long

If xlRange.Count > 1 Then

For indRow = 1 To xlRange.Rows.Count

Next

GetCellFontColor = arResults

GetCellFontColor = xlRange.Font.Color

Dim centRes As Long

End If

centRes = 0

Dim centRes As Long

Application.Volatile

CountCellsByColor = centers

Dim indRefColor As Long

For Each cellCurrent In rData

If in hRef Color = cell Current.Font.Color Then

For Each cellCurrent In rData

centRes = centRes + 1

End If

Dim sum Res

sumiRe = 0

For Each cellCurrent In rData

If in hRef Color = cell Current.Interior.Color Then

End If

Dim indRefColor As Long

CountCellsByFontColor = centErs

Application.Volatile

indRef Color = cellRef Color.Cells(1, 1).Font.Color

If indRefColor = cell Current.Font.Color Then

End If

Next cell Current

SumCellsByColor = sumRes

Dim sum Res

End Function

Bước 3: Lưu bảng tính và nhập tên gọi dưới dạng (.xlsm) tức Excel Macro-Enabled Workbook. 

Bước 4: Quay lại trang Excel và tạo bảng kết quả theo màu sắc của ô tính theo công thức hàm sau: =CountCellsByColor(range, color code)

Trong đó:

Cách sắp xếp màu sắc trong Excel

Cùng với cách dùng hàm màu sắc trong Excel thì cách sắp xếp màu sắc trong Excel lại dễ dàng hơn. Chúng ta không cần thiết phải dùng tới mã code VBA hay công thức hàm nào cả. Chúng ta hoàn toàn có thể dùng tính năng Custom Sort có sẵn. 

Bước 1: Bôi đen vùng dữ liệu mà chúng ta muốn sắp xếp.

Bước 2: Kích chuột vào thẻ Home trên thanh công cụ, kích chọn mục Editing, chọn Sort & Filter và chọn mục Custom Sort. 

Bước 3: Tại hộp thoại Sort chúng ta có thể đặt các tiêu chí sắp xếp như ý muốn.

Chọn Cell Color trong Sorting để chọn màu cho ô tính mà bạn muốn hiển thị trên cùng. 

Bước 4: Kích chọn nút Copy Level để thêm nhiều tiêu chí khác nhau cho việc hiển thị dữ liệu. Tiếp đó, ở ô Order, chọn màu thứ 2 theo các thứ tự ưu tiên. Lần lượt như vậy cho tới khi hết các tiêu chí. 

Bước 5: Nhấn chọn OK và xác nhận rằng các hàng đã được sắp xếp đúng theo màu sắc muốn hiển thị chưa. 

Cách sắp xếp màu sắc trong Excel

Cách sắp xếp màu sắc trong Excel

Chúng ta sử dụng tính năng Custom Sort. Trên file dữ liệu muốn sắp xếp, chọn mục Home trên thanh công cụ, kích chọn mục Sort & Filter, chọn mục Custom Sort. Chọn Font Color ở bên dưới của phần Sorting thay vì bên trên như thông thường. 

Tô màu dữ liệu trùng trong Excel

Để tô màu dữ liệu trùng trong Excel, chúng ta thực hiện như sau: 

Bước 1: Chuẩn bị một bảng dữ liệu đầy đủ. Kích chọn vào bất kỳ ô dữ liệu nào đó và nhấn tổ hợp phím Ctrl + T. Tiếp đó, hộp thoại Create Table sẽ xuất hiện, tìm tới mục “My table has headers” và nhấn OK. 

Bước 2: Bôi đen tất cả vùng dữ liệu mà chúng ta muốn kiểm tra trùng lặp. 

Bước 3: Chọn mục Home trên thanh công cụ, nhấn vào biểu tượng Conditional Formatting, kích chọn Highlight Cells Rules, chọn mục Duplicates Value.

Bước 4: Thiết lập định dạng cho các ô trùng lặp.

Tô màu dữ liệu trùng trong Excel

Tô màu dữ liệu trùng trong Excel

Từ danh sách “Values ​With” được sổ xuống từ hộp thoại “Duplicate Values”, nhấn OK để hoàn thiện định dạng. 

Bước 5. Hoàn thành. Lúc này chúng ta sẽ thử nhập bất kỳ dữ liệu nào trong danh sách thì các ô dữ liệu trùng nhau đã được đánh dấu bằng màu sắc nổi bật hơn để thu hút sự chú ý. 

Hàm tô màu có điều kiện trong Excel

Hàm tô màu có điều kiện trong Excel được sử dụng như sau: 

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

Bước 2: Kích chọn mục Conditional Formatting. 

Bước 3: Kích chọn New Rule, tại mục Select a rule type chọn Use a formula to determine which cells to format. 

Bước 4: Tìm tới ô Format values where this formula is true, nhập công thức muốn dùng để định dạng màu cho ô. Ví dụ: = MOD(ROW()/2)>0.

Bước 5: Kích chọn mục Format.

Bước 7: Kích chọn mục Fill để chọn màu sắc cho ô và nhấn OK và hoàn thiện.

Hàm tô màu có điều kiện trong Excel

Hàm tô màu có điều kiện trong Excel

Bỏ tô màu có điều kiện trong Excel

Bỏ tô màu có điều kiện trong Excel như sau: 

Bước 1: Kích chuột vào mục nhà phát triển, chọn Visual Basic để mở Microsoft Visual Basic. Kích chuột vào mục Chèn, chọn Mô đun và nhập mã code sau: 

Sub DeleteConditionalFormats() ‘Update 20130912 Dim As Range On Resume Next xTitleId Set WorkRng = Application.Selection WorkRng = Application.InputBox(“Range”, xTitleId, WorkRng, Type:=8) WorkRng.Delete End Sub\

Bước 2: Nhấn F5 để Excel bắt đầu chạy mã. Cuối cùng là nhấn OK.

Hàm IF với điều kiện màu

Hàm IF chính là hàm được dùng để kiểm tra dữ liệu xem chúng có thỏa mãn điều kiện mà người dùng đặt ra hay không. Từ đó chúng sẽ trả về kết quả là đúng hoặc sai. 

Hàm IF với điều kiện màu được thực hiện như sau: =IF(Logical_test;Value_if_true;Value_if_false)

Trong đó:

Hàm IF với điều kiện màu

Hàm IF với điều kiện màu

​Lưu ý: Nếu điều kiện thỏa mãn thì giá trị trả về sẽ là 0 và điều kiện không thỏa mãn thì giá trị trả về sẽ là FALSE. Không được bỏ trống Value_if_true và Value_if_false.

Code đổi màu trong Excel

Code đổi màu trong Excel như sau: 

Dim wshCurrent As Worksheet

For Each wshh Current In Worksheets

Next

WbkCountCellsByColor = vW bRakes

End Function

Dim vWbkRes

vWbkRes = 0

For Each wshh Current In Worksheets

wshCurrent.Activate

Next

End Function

Ngoài ra, chúng ta hoàn toàn có thể kết hợp việc dùng mã Macro phía trên bằng công thức =WbkCountCellsColor() hoặc =WbkSumCellsByColor(). 

Một số tính năng tự đổi màu, tùy chỉnh màu sắc của ô hay màu sắc của chữ: 

– Đếm các ô với màu chữ được chỉ định: Count Cells By Font Color (range,color code)

– Đếm các ô có màu nền được chỉ định: CountCellsByColor (range,color_code)

– Tính tổng của các ô có cùng màu chữ: Sum Cells By Font Color (range, color code)

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

– Trả về mã màu nền của một ô xác định: GetCell Color(cell)

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

Sub SumCountByConditionalFormat()

Dim centRes As Long

centRes = 0

sumiRe = 0

centRes = centRes + 1

End If

Next

End Sub

End If ‘Kết thúc câu lệnh IF.

shp.Fill.Visible = msoFalse ‘Trả về trạng thái ban đầu của shape.

[F9] = 1 ‘Trả về giá trị là 1, có thể bỏ qua nếu không cần thiết.

Else ‘Trường hợp không phải giá trị là 1.

shp.Fill.ForeColor.RGB = RGB(255, 0, 0)

shp.Fill.Visible = msoTrue 

[F9] = 2 ‘Lấy kết quả ô F9 bằng 2 để ghi nhận đã đổi màu.

If [F9] = 1 Then ‘Xét kết quả tại ô F9 nếu có giá trị là 1.

Set shp = Sheet1.Shapes(“Rectangle 1”): Logic thực hiện việc đổi màu.

Dim oshp As Object: Xác định Shape cần đổi màu.

Sub Shape Change Colour() ‘Lệnh đổi màu Shape.

Trên đây là thông tin về hàm màu sắc trong Excel, hàm tô màu có điều kiện, hàm IF với điều kiện màu và code VBA đổi màu trong Excel. Hy vọng với những thủ thuật trên đây sẽ giúp rút ngắn được thời gian khi sử dụng Excel này. Chúc các bạn thành công!

Xem thêm: Cách chỉnh dấu phẩy trong Excel đơn giản, nhanh chóng

Office -