Nhiều người tô màu vàng cho hóa đơn chưa thu tiền, tô màu xanh cho hóa đơn đã thu, rồi cần biết mỗi màu có bao nhiêu hóa đơn và tổng bao nhiêu tiền. Excel không có hàm sẵn để đếm ô có màu trong Excel, vì COUNTIF và SUMIF chỉ xét giá trị trong ô, không xét màu tô. Bài viết này hướng dẫn 4 cách làm và chỉ rõ cách nào dùng được trong từng tình huống.

Cách nhanh nhất như sau: bạn bật Filter, lọc cột Số tiền theo màu vàng, rồi gõ =SUBTOTAL(103,A2:A13) vào ô D15 dưới bảng để đếm và =SUBTOTAL(109,D2:D13) vào ô D16 để tính tổng. Với bảng công nợ trong bài, hai công thức trả về 5 hóa đơn màu vàng và tổng 79.550.000. Nếu bạn cần công thức tự đếm mà không phải lọc, bạn tạo hàm DEMMAU bằng VBA và gõ =DEMMAU($D$2:$D$13,F2) vào ô G2, với ô F2 là ô mẫu tô màu vàng.

Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh, chạy trên Windows. Bảng trong ảnh dùng dấu phẩy ngăn cách hàng nghìn, nên số 79.550.000 hiện thành 79,550,000.

Bạn nên chọn cách đếm ô có màu nào?

Bảng công nợ tháng 10 có 12 hóa đơn ở vùng A1:D13. Cột D là Số tiền, và màu nền ở cột D cho biết trạng thái: vàng là chưa thu tiền, xanh là đã thu tiền, không tô màu là chưa đối chiếu.

Cách làm

Đếm được màu do Conditional Formatting tô không

Kết quả có tự cập nhật không

File có cần macro không

Cách 1: lọc theo màu rồi dùng SUBTOTAL

Cách này đếm được.

Kết quả chỉ đúng khi bảng đang lọc theo màu đó.

File không cần macro.

Cách 2: Find theo định dạng

Cách này không tìm thấy các ô đó, vì Find tìm theo màu nền thật.

Bạn phải tìm lại mỗi lần cần đếm.

File không cần macro.

Cách 3: hàm DEMMAU, TONGMAU bằng VBA

Hàm không đếm được, vì hàm đọc màu nền thật của ô.

Kết quả đổi khi bạn nhấn F9 sau khi đổi màu.

File phải lưu dạng .xlsm và bật macro.

Cách 4: cột trạng thái với COUNTIF, SUMIF

Bạn không cần đếm màu, vì bạn đếm theo chữ hoặc theo điều kiện tô màu.

Kết quả tự đổi khi dữ liệu đổi.

File không cần macro.

Nếu bạn chỉ cần đếm một lần, bạn dùng cách 1 hoặc cách 2. Nếu bảng được cập nhật hằng ngày và bạn cần một ô tổng hợp luôn có số, bạn dùng cách 3. Nếu bạn đang tự thiết kế bảng, Nghề Excel khuyên bạn dùng cách 4 ngay từ đầu, vì màu tô tay không phải là dữ liệu mà công thức đọc được.

Bạn đếm ô có màu bằng lọc màu và hàm SUBTOTAL như thế nào?

Cách này dùng tính năng lọc theo màu có sẵn của Excel. Hàm SUBTOTAL bỏ qua các dòng bị ẩn khi lọc, nên công thức chỉ đếm và cộng các dòng đang hiện.

Bước 1: Bạn bật Filter cho bảng

Bạn chọn một ô bất kỳ trong bảng, ví dụ ô A1. Bạn chọn thẻ Data rồi bấm Filter. Bạn cũng có thể nhấn Ctrl + Shift + L để bật Filter.

Thẻ Data đang mở, con trỏ chuột chỉ vào nút Filter, bảng công nợ có ô số tiền tô vàng và xanh
Nút Filter thêm mũi tên lọc vào từng ô tiêu đề của bảng

Bước 2: Bạn lọc cột Số tiền theo màu vàng

Bạn bấm mũi tên lọc ở ô tiêu đề D1, trỏ chuột vào Filter by Color. Trong nhóm Filter by Cell Color, bạn bấm ô màu vàng.

Menu lọc của cột Số tiền đang mở mục Filter by Color, con trỏ chuột chỉ vào ô màu vàng
Excel chỉ liệt kê các màu đang có trong cột, kèm mục No Fill cho các ô không tô màu

Sau khi lọc, bảng chỉ còn 5 dòng có ô số tiền màu vàng. Các dòng còn lại bị ẩn, nhưng dữ liệu vẫn còn nguyên.

Bước 3: Bạn gõ SUBTOTAL để đếm và tính tổng theo màu

Bạn chọn ô D15, gõ công thức sau rồi nhấn Enter:

=SUBTOTAL(103,A2:A13)

Bảng đã lọc còn 5 dòng màu vàng, ô D15 đang gõ công thức =SUBTOTAL(103,A2:A13)
Dòng 15 và dòng 16 nằm ngoài vùng lọc, nên hai dòng này không bị ẩn khi bạn lọc

Bạn chọn ô D16 và gõ =SUBTOTAL(109,D2:D13), rồi nhấn Enter.

Ô D16 đang chọn với công thức =SUBTOTAL(109,D2:D13), ô D15 trả về 5 và ô D16 trả về 79,550,000
Hai ô kết quả chỉ tính 5 hóa đơn màu vàng đang hiện

Ô D15 trả về 5 và ô D16 trả về 79.550.000. Số đầu tiên trong hàm SUBTOTAL cho biết phép tính:

Số trong SUBTOTAL

Phép tính

Bạn dùng khi

103

Hàm đếm các ô có dữ liệu đang hiện, giống COUNTA.

Bạn đếm số dòng, kể cả cột chứa chữ như Số HĐ.

102

Hàm chỉ đếm các ô chứa số đang hiện, giống COUNT.

Bạn đếm trên cột chứa số. Với cột Số HĐ chứa chữ, =SUBTOTAL(102,A2:A13) trả về 0.

109

Hàm cộng các ô đang hiện, giống SUM.

Bạn tính tổng theo màu.

Số 103 và số 3 đều bỏ qua các dòng bị lọc ẩn. Số 103 còn bỏ qua cả các dòng bạn ẩn bằng tay (Hide), nên bạn dùng 103 và 109 để kết quả luôn khớp với những dòng đang nhìn thấy. Bài công thức tính tổng khi lọc dữ liệu trong Excel bằng hàm SUBTOTAL giải thích kỹ hơn các số khác của hàm này.

Muốn đếm màu xanh, bạn mở lại mũi tên lọc ở ô D1 và chọn ô màu xanh. Hai ô D15, D16 tự đổi thành 4 hóa đơn và 28.150.000. Muốn đếm ô không tô màu, bạn chọn mục No Fill, và hai ô trả về 3 hóa đơn, 11.250.000.

Bạn lưu ý 2 điểm khi dùng cách này:

  • Kết quả chỉ đúng khi bảng đang lọc theo màu. Khi bạn bỏ lọc, ô D15 trả về 12 và ô D16 trả về 118.950.000, là tổng của cả bảng.

  • Ô công thức phải nằm ngoài các dòng dữ liệu. Nếu bạn đặt công thức ở cột trống cạnh bảng, ví dụ ô F3, thì dòng 3 của hóa đơn HĐ0102 màu xanh bị ẩn khi bạn lọc màu vàng, và bạn không nhìn thấy kết quả.

Bạn đếm ô có màu bằng Find (Ctrl + F) như thế nào?

Cách này không cần lọc và tìm được ô màu ở nhiều cột cùng lúc. Bạn dùng hộp thoại Find để tìm mọi ô có cùng định dạng với một ô mẫu.

Bước 1: Bạn chọn định dạng từ ô mẫu

Bạn nhấn Ctrl + F để mở hộp thoại Find and Replace. Bạn để trống ô Find what, bấm Options nếu phần tùy chọn đang thu gọn, bấm mũi tên bên phải nút Format, rồi chọn Choose Format From Cell.

Hộp thoại Find and Replace có ô Find what để trống, menu của nút Format đang mở, con trỏ chuột chỉ vào Choose Format From Cell
Mục Choose Format From Cell lấy định dạng của ô bạn bấm vào

Con trỏ chuột đổi thành hình ống hút màu. Bạn bấm vào ô D2, là một ô màu vàng trong bảng.

Bước 2: Bạn bấm Find All để xem số ô tìm thấy

Bạn bấm Find All. Danh sách phía dưới hộp thoại liệt kê các ô màu vàng, và dòng cuối hộp thoại ghi "5 cell(s) found".

Hộp thoại Find and Replace sau khi bấm Find All, danh sách có 5 ô màu vàng ở cột D, dòng cuối ghi 5 cell(s) found
Ô Preview cạnh nút Format hiện định dạng Excel đang tìm. Excel 365 đang bật Focus Cell nên tô nhạt dòng và cột của ô đang chọn, vì vậy màu ô trong ảnh hơi khác màu thật

Bước 3: Bạn nhấn Ctrl + A để xem tổng ở thanh trạng thái

Bạn bấm vào một dòng trong danh sách kết quả, rồi nhấn Ctrl + A. Excel chọn cả 5 ô màu vàng trên sheet. Thanh trạng thái ở góc dưới bên phải cửa sổ hiện Count là 5 và Sum là 79.550.000.

Cả 5 ô màu vàng đang được chọn, thanh trạng thái hiện Average, Count 5 và Sum 79,550,000
Thanh trạng thái cộng các ô đang chọn mà không cần công thức. Các ô màu vàng trông sẫm hơn vì đang được chọn

Bạn lưu ý 3 điểm khi dùng cách này:

  • Mục Choose Format From Cell lấy định dạng của ô mẫu, không chỉ lấy màu. Excel chỉ tìm thấy những ô giống ô mẫu ở mọi phần định dạng đã lấy. Khi Nghề Excel đổi riêng định dạng số của một ô vàng, ví dụ từ #,##0 thành 0, Find không còn tìm thấy ô đó. Nếu các ô cùng màu nhưng khác định dạng, bạn bấm thẳng nút Format, chọn thẻ Fill, chọn màu cần tìm rồi bấm OK.

  • Số đếm và số tổng không được ghi vào ô nào. Bạn phải tìm lại mỗi lần bảng thay đổi.

  • Excel giữ định dạng vừa chọn cho các lần tìm sau, cho tới khi bạn đóng Excel. Muốn tìm chữ bình thường, bạn bấm mũi tên bên phải nút Format và chọn Clear Find Format.

Bạn tạo hàm đếm màu DEMMAU, TONGMAU bằng VBA như thế nào?

Excel không có hàm đếm màu sẵn, nhưng bạn có thể tự tạo hàm bằng VBA. Sau khi tạo, bạn dùng hàm DEMMAU và TONGMAU trong ô như một hàm bình thường. Cách này chạy trên Excel cài trên máy tính có VBA, kể cả các bản cũ như Excel 2010, 2016, 2019. Nghề Excel đã thử trên Excel 365 cho Windows. Excel bản web không chạy được cách này.

Bước 1: Bạn mở trình soạn VBA và thêm Module

Bạn nhấn Alt + F11 để mở trình soạn VBA. Trong trình soạn VBA, bạn bấm menu Insert rồi chọn Module.

Trình soạn VBA với menu Insert đang mở, con trỏ chuột chỉ vào mục Module
Mục Module tạo một trang code mới trong file đang mở

Trong khung Project bên trái, tên sheet tiếng Việt có thể hiện dấu hỏi chấm, ví dụ "Hàm d?m màu" thay cho "Hàm đếm màu". Trình soạn VBA hiện chữ có dấu theo cách này trên đa số máy, và file của bạn không bị lỗi.

Bước 2: Bạn chép code vào Module

Bạn chép toàn bộ đoạn code sau, từ dòng Function DEMMAU đầu tiên tới dòng End Function cuối cùng, rồi dán vào cửa sổ code vừa mở:

Function DEMMAU(VungDem As Range, OMau As Range, Optional TheoMauChu As Boolean = False) As Long
    Dim o As Range, mau As Long
    Application.Volatile
    mau = MaMau(OMau.Cells(1, 1), TheoMauChu)
    For Each o In VungDem.Cells
        If MaMau(o, TheoMauChu) = mau Then DEMMAU = DEMMAU + 1
    Next o
End Function

Function TONGMAU(VungMau As Range, OMau As Range, Optional VungCong As Range, Optional TheoMauChu As Boolean = False) As Variant
Dim i As Long, mau As Long, v As Variant, tong As Double
Application.Volatile
If VungCong Is Nothing Then Set VungCong = VungMau
If VungCong.Rows.Count <> VungMau.Rows.Count Or VungCong.Columns.Count <> VungMau.Columns.Count Then
TONGMAU = CVErr(xlErrValue)
Exit Function
End If
mau = MaMau(OMau.Cells(1, 1), TheoMauChu)
For i = 1 To VungMau.Cells.Count
If MaMau(VungMau.Cells(i), TheoMauChu) = mau Then
v = VungCong.Cells(i).Value
If VarType(v) >= vbInteger And VarType(v) <= vbCurrency Then tong = tong + v
End If
Next i
TONGMAU = tong
End Function

Private Function MaMau(o As Range, TheoMauChu As Boolean) As Long
If TheoMauChu Then
If IsNull(o.Font.Color) Then MaMau = -2 Else MaMau = o.Font.Color
ElseIf o.Interior.ColorIndex = xlColorIndexNone Then
MaMau = -1
Else
MaMau = o.Interior.Color
End If
End Function

Cửa sổ code Module1 có hàm DEMMAU, TONGMAU và MaMau
Hàm MaMau có chữ Private nên không hiện trong danh sách hàm của Excel

Đoạn code gồm 3 hàm:

Hàm

Việc hàm làm

DEMMAU(vùng đếm, ô mẫu, [theo màu chữ])

Hàm này đếm số ô trong vùng có cùng màu với ô mẫu.

TONGMAU(vùng màu, ô mẫu, [vùng cộng], [theo màu chữ])

Hàm này cộng các số có cùng màu với ô mẫu. Nếu bạn ghi vùng cộng, hàm xét màu ở vùng màu và cộng số ở vùng cộng cùng dòng.

MaMau

Hàm phụ này lấy mã màu nền hoặc mã màu chữ của một ô.

Code xử lý 3 trường hợp mà nhiều đoạn code trên mạng làm sai:

  • Hàm so mã màu đầy đủ (Interior.Color) thay vì số thứ tự màu (ColorIndex). ColorIndex chỉ có 56 màu, nên Excel gộp các màu gần nhau vào cùng một số. Ví dụ, màu vàng nhạt FFE699 dùng trong bài và màu vàng FFFF99 cùng có ColorIndex là 36.

  • Hàm phân biệt ô không tô màu với ô tô màu trắng. Excel trả về cùng mã màu trắng cho cả hai loại ô, nên code phải kiểm tra riêng ô không tô màu.

  • Hàm TONGMAU cộng bằng kiểu số Double. Số có phần lẻ như 8,5 được cộng đúng, và tổng trên 2,1 tỷ không bị lỗi tràn số. Hàm bỏ qua ô chứa chữ, ô trống và ô ngày tháng.

Bước 3: Bạn dùng hàm DEMMAU, TONGMAU trên sheet

Bạn nhấn Alt + Q để đóng trình soạn VBA và quay lại Excel. Trên sheet Hàm đếm màu, các ô F2, F3, F4 là ô mẫu màu vàng, màu xanh và không tô màu. Ô F5 có chữ màu đỏ, dùng để đếm tên khách hàng được ghi chữ đỏ.

Bạn chọn ô G2, gõ công thức sau rồi nhấn Enter:

=DEMMAU($D$2:$D$13,F2)

Ô G2 đang chọn với công thức =DEMMAU($D$2:$D$13,F2) và trả về 5, cột F có ô mẫu màu vàng, xanh, không màu và chữ đỏ
Ô G2 trả về 5 hóa đơn màu vàng. Vùng đếm dùng dấu $ để giữ nguyên khi kéo công thức xuống

Bạn gõ vào ô H2 công thức =TONGMAU($D$2:$D$13,F2), rồi kéo cả hai ô G2:H2 xuống tới dòng 4. Ở dòng 5, bạn gõ hai công thức đếm theo màu chữ như bảng dưới.

Ô H2 đang chọn với công thức =TONGMAU($D$2:$D$13,F2), cột G và H có số hóa đơn và số tiền của từng màu
Các số trong cột G và H khớp với kết quả lọc màu ở cách 1

Ô

Công thức

Kết quả

G2

=DEMMAU($D$2:$D$13,F2)

5 hóa đơn màu vàng

H2

=TONGMAU($D$2:$D$13,F2)

79.550.000

G3

=DEMMAU($D$2:$D$13,F3)

4 hóa đơn màu xanh

H3

=TONGMAU($D$2:$D$13,F3)

28.150.000

G4

=DEMMAU($D$2:$D$13,F4)

3 hóa đơn không tô màu

H4

=TONGMAU($D$2:$D$13,F4)

11.250.000

G5

=DEMMAU($B$2:$B$13,F5,TRUE)

3 khách hàng ghi chữ đỏ

H5

=TONGMAU($B$2:$B$13,F5,$D$2:$D$13,TRUE)

51.300.000

Ở dòng 5, tham số TRUE cho hàm biết bạn đếm theo màu chữ. Hàm TONGMAU ở ô H5 xét màu chữ ở cột B và cộng số tiền ở cột D cùng dòng. Bạn dùng cách ghi vùng cộng này khi màu nằm ở một cột còn số cần cộng nằm ở cột khác, ví dụ khi bạn tô màu cột Khách hàng.

Bước 4: Bạn nhấn F9 sau khi đổi màu ô

Excel không tính lại công thức khi bạn chỉ đổi màu ô. Ví dụ, bạn tô ô D5 của hóa đơn HĐ0104 thành màu vàng, và ô G2 vẫn hiện 5.

Ô D5 vừa được tô màu vàng nhưng ô G2 vẫn hiện 5 và ô G4 vẫn hiện 3
Đổi màu là đổi định dạng, nên Excel không tự chạy lại công thức

Bạn nhấn F9 để Excel tính lại. Ô G2 đổi thành 6, ô H2 đổi thành 82.800.000, ô G4 đổi thành 2 và ô H4 đổi thành 8.000.000.

Sau khi nhấn F9, ô G2 hiện 6, ô H2 hiện 82,800,000, ô G4 hiện 2
Dòng Application.Volatile trong code giúp hàm chạy lại mỗi lần bạn nhấn F9

Dòng Application.Volatile làm hàm chạy lại mỗi lần Excel tính lại cả sheet, kể cả khi bạn sửa một ô bất kỳ. Với bảng vài nghìn dòng có nhiều ô dùng DEMMAU, Excel có thể chậm hơn khi bạn nhập liệu.

Bước 5: Bạn lưu file dạng .xlsm

Bạn vào File, chọn Save As, rồi chọn kiểu Excel Macro-Enabled Workbook (*.xlsm). Nếu bạn lưu dạng .xlsx, Excel bỏ phần code khi lưu, và lần sau mở file, các ô dùng DEMMAU sẽ báo lỗi #NAME? khi Excel tính lại.

Khi mở lại file .xlsm, bạn bấm Enable Content trên thanh vàng để bật macro. Nếu Excel không hiện thanh này hoặc vẫn chặn macro, bạn xem bài cách bật macro trong Excel bằng Enable Content, Unblock, Trust Center. Theo trang hỗ trợ của Microsoft, Excel bản web không chạy macro VBA, nên hàm DEMMAU không dùng được trên Excel bản web.

Bạn đếm màu do Conditional Formatting tô bằng cách nào?

Conditional Formatting tô màu theo điều kiện, ví dụ tô đỏ các hóa đơn quá hạn trên 30 ngày. Màu này chỉ là màu hiển thị và không đổi màu nền thật của ô. Trong bảng ví dụ dưới đây, các ô tô bằng Conditional Formatting vẫn có màu nền thật là không tô màu, nên phần lớn các cách đếm màu không nhìn thấy màu đỏ nhạt.

Sheet Tô có điều kiện dưới đây có 10 hóa đơn. Quy tắc Conditional Formatting =$D2>30 tô đỏ nhạt cả dòng khi cột D (Số ngày quá hạn) lớn hơn 30. Ô E13 là ô mẫu được tô tay cùng màu đỏ nhạt.

Bảng 10 hóa đơn có 4 dòng tô đỏ nhạt bằng Conditional Formatting, ô E14 dùng DEMMAU trả về 0, ô E15 dùng COUNTIF trả về 4
Hàm DEMMAU không thấy màu do Conditional Formatting tô, nên trả về 0

Các ô kết quả trong ảnh dùng những công thức sau:

Ô

Công thức

Kết quả

E14

=DEMMAU(E2:E11,E13)

0, vì hàm không thấy màu do Conditional Formatting tô

E15

=COUNTIF(D2:D11,">30")

4 hóa đơn quá hạn trên 30 ngày

E16

=SUMIF(D2:D11,">30",E2:E11)

36.700.000

E17

=SUBTOTAL(109,E2:E11)

74.000.000 khi chưa lọc

Cách đúng là đếm theo chính điều kiện đã dùng để tô màu. Quy tắc tô màu là "Số ngày quá hạn lớn hơn 30", nên bạn đếm bằng =COUNTIF(D2:D11,">30") và cộng bằng =SUMIF(D2:D11,">30",E2:E11). Hai công thức này tự đổi khi số ngày quá hạn thay đổi. Ví dụ, khi Nghề Excel sửa số ngày quá hạn của hóa đơn HĐ0092 từ 12 thành 31, ô E15 tự tăng lên 5.

Lọc theo màu là cách đếm màu duy nhất trong bài nhận màu do Conditional Formatting tô. Bạn bật Filter, bấm mũi tên lọc ở ô E1, chọn Filter by Color và chọn ô màu đỏ nhạt.

Menu lọc của cột Số tiền hiện ô màu đỏ nhạt trong nhóm Filter by Cell Color, con trỏ chuột chỉ vào ô màu đó
Danh sách màu lọc có cả màu do Conditional Formatting tô
Bảng đã lọc còn 4 dòng đỏ nhạt, ô E17 với công thức =SUBTOTAL(109,E2:E11) trả về 36,700,000
Ô E17 trả về đúng tổng của 4 hóa đơn quá hạn trên 30 ngày

Bảng còn 4 dòng, và ô E17 trả về 36.700.000, bằng kết quả của SUMIF. Bảng dưới tóm tắt cách nào đếm được màu do Conditional Formatting tô:

Cách

Kết quả khi Nghề Excel thử trên sheet Tô có điều kiện

Lọc theo màu rồi dùng SUBTOTAL

Cách này đếm đúng 4 hóa đơn.

COUNTIF, SUMIF theo điều kiện tô màu

Cách này đếm đúng và tự cập nhật.

Find theo định dạng

Find không tìm thấy ô nào trong bảng, chỉ thấy ô mẫu tô tay E13.

Hàm DEMMAU, TONGMAU

Hàm trả về 0, vì hàm đọc màu nền thật của ô.

GET.CELL

Hàm trả về mã 0, là mã màu nền thật của các ô này.

Hàm tự tạo không đọc được màu này vì lý do kỹ thuật. Trang tài liệu VBA của Microsoft ghi rằng thuộc tính DisplayFormat, là thuộc tính đọc màu đang hiển thị, không chạy trong hàm tự tạo gọi từ ô và trả về lỗi #VALUE!. Vì vậy, hàm tự tạo viết theo cách thông thường như DEMMAU không đọc được màu do Conditional Formatting tô.

Nếu bạn chưa quen Conditional Formatting, bài tô màu có điều kiện trong Excel bằng Conditional Formatting hướng dẫn cách tạo quy tắc và cách xem lại điều kiện của quy tắc đang có.

Hàm GET.CELL có đếm ô màu được không?

Một số bài hướng dẫn trên mạng dùng hàm GET.CELL để lấy mã màu nền. Bạn chọn một ô ở dòng 2, rồi tạo một tên trong Name Manager với công thức dạng =GET.CELL(38,Sheet1!$D2). Bạn phải chọn ô ở dòng 2 trước, vì $D2 thay đổi theo ô đang chọn khi bạn tạo tên. Sau đó, bạn gõ tên đó vào một cột phụ để lấy mã màu của từng dòng, rồi đếm mã màu bằng COUNTIF.

Nghề Excel không khuyên bạn dùng cách này, vì những lý do sau:

  • GET.CELL là hàm macro Excel 4.0 đời cũ. Theo trang hỗ trợ của Microsoft, Excel báo lỗi #BLOCKED! khi thiết lập macro tắt macro Excel 4.0.

  • Hàm trả về số thứ tự màu trong bảng 56 màu. Khi Nghề Excel thử, màu vàng nhạt FFE699 trả về mã 36, giống màu vàng FFFF99, nên hai màu khác nhau bị đếm chung.

  • Hàm đọc màu nền thật của ô. Khi Nghề Excel thử, các ô tô màu bằng Conditional Formatting trả về mã 0, giống ô không tô màu.

  • File vẫn phải lưu dạng .xlsm, và kết quả không tự đổi khi bạn đổi màu ô.

Vì file vẫn phải lưu dạng .xlsm, hàm DEMMAU ở cách 3 dễ dùng hơn và không cần cột phụ.

Bạn ghi trạng thái bằng cột chữ thay vì tô màu như thế nào?

Màu tô tay có 3 điểm yếu: công thức thường không đọc được, người khác có thể tô nhầm sắc màu gần giống, và màu không hiện khi bạn xuất dữ liệu sang phần mềm khác. Vì vậy, bạn nên ghi trạng thái bằng chữ trong một cột riêng, rồi để Conditional Formatting tô màu theo chữ đó.

Sheet Trạng thái có cột C là Số tiền và cột D ghi "Chưa thu tiền", "Đã thu tiền" hoặc "Chưa đối chiếu". Bảng này không có cột Ngày HĐ, nên cột Số tiền nằm ở cột C thay vì cột D như các phần trước. Các ô F2, F3, F4 ghi sẵn 3 trạng thái cần đếm. Quy tắc Conditional Formatting tô vàng ô "Chưa thu tiền" và tô xanh ô "Đã thu tiền", nên bảng trông giống bảng tô tay ở các phần trước.

Bạn chọn ô G2, gõ công thức sau rồi nhấn Enter:

=COUNTIF($D$2:$D$13,F2)

Sheet Trạng thái, ô G2 đang chọn với công thức =COUNTIF($D$2:$D$13,F2) và trả về 5, cột D ghi trạng thái bằng chữ
Ô F2 chứa chữ "Chưa thu tiền", nên công thức đếm theo đúng chữ trong cột D

Bạn gõ vào ô H2 công thức =SUMIF($D$2:$D$13,F2,$C$2:$C$13), rồi kéo cả hai ô G2:H2 xuống tới dòng 4.

Ô H2 đang chọn với công thức SUMIF, cột G và H có số hóa đơn và số tiền theo từng trạng thái
Kết quả khớp với cách đếm màu, và tự cập nhật khi bạn sửa trạng thái

Ô G2 trả về 5 và ô H2 trả về 79.550.000, giống hệt kết quả đếm màu vàng. Khi hóa đơn được thu tiền, bạn chỉ sửa chữ trong cột D. Màu ô, số đếm và số tiền tự đổi theo mà không cần nhấn F9 hay lọc lại.

Bạn hay gặp lỗi gì khi đếm ô có màu trong Excel?

Lỗi

Nguyên nhân

Cách sửa

SUBTOTAL trả về 0 khi đếm cột Số HĐ

Bạn dùng số 2 hoặc 102, là phép đếm chỉ tính ô chứa số.

Bạn đổi thành =SUBTOTAL(103,A2:A13).

SUBTOTAL đếm cả bảng

Bảng chưa lọc, hoặc bạn đã bỏ lọc.

Bạn lọc lại theo màu cần đếm.

Bạn không thấy ô kết quả sau khi lọc

Ô công thức nằm trên một dòng dữ liệu, nên dòng đó bị ẩn.

Bạn đặt công thức dưới bảng, cách bảng một dòng trống, hoặc đặt phía trên dòng tiêu đề.

Find không tìm thấy hết các ô cùng màu

Choose Format From Cell lấy cả định dạng khác của ô mẫu, ví dụ định dạng số.

Bạn bấm nút Format, chọn màu ở thẻ Fill thay vì lấy từ ô mẫu.

Find không tìm thấy ô nào dù bảng có màu

Màu ô do Conditional Formatting tô, không phải màu tô tay.

Bạn lọc theo màu, hoặc dùng COUNTIF theo điều kiện tô màu.

Ô dùng DEMMAU báo #NAME?

File chưa có code, file đã lưu dạng .xlsx làm mất code, hoặc macro đang tắt.

Bạn chép code vào Module của file đang dùng, lưu dạng .xlsm và bật macro khi mở file.

DEMMAU không đổi sau khi bạn tô màu

Đổi màu không làm Excel tính lại công thức.

Bạn nhấn F9.

DEMMAU đếm thiếu ô trông cùng màu

Hai ô có sắc màu gần giống nhưng khác mã, ví dụ vàng FFE699 và vàng FFFF00.

Bạn tô lại bằng đúng ô màu trong bảng màu, hoặc dùng Format Painter chép màu từ ô mẫu.

DEMMAU trả về 0

Màu ô do Conditional Formatting tô, hoặc ô mẫu không cùng màu với vùng đếm.

Bạn dùng COUNTIF theo điều kiện tô màu, hoặc chọn lại ô mẫu.

TONGMAU báo #VALUE!

Vùng màu và vùng cộng không cùng số dòng, số cột.

Bạn sửa hai vùng cho bằng nhau, ví dụ $B$2:$B$13 và $D$2:$D$13.

Người đọc thường hỏi gì thêm về đếm ô có màu trong Excel?

Excel có hàm COUNTIF theo màu không?

Excel không có hàm này. COUNTIF, COUNTIFS, SUMIF và SUMIFS chỉ xét giá trị trong ô như số, chữ, ngày. Vì vậy, bạn phải lọc theo màu, dùng Find, tự tạo hàm bằng VBA, hoặc ghi trạng thái bằng chữ để đếm.

Tôi đếm ô màu vàng trong Excel như thế nào?

Bạn lọc cột theo màu vàng rồi gõ =SUBTOTAL(103,A2:A13) như cách 1. Nếu cần công thức cố định, bạn tô một ô mẫu màu vàng, ví dụ ô F2, rồi gõ =DEMMAU($D$2:$D$13,F2). Với bảng trong bài, cả hai cách đều trả về 5.

Tôi đếm ô không tô màu như thế nào?

Khi lọc, bạn chọn mục No Fill trong Filter by Color. Với hàm DEMMAU, bạn dùng một ô mẫu không tô màu, ví dụ =DEMMAU($D$2:$D$13,F4) với ô F4 không tô màu. Với bảng trong bài, hai cách đều trả về 3. Hàm DEMMAU không tính ô tô màu trắng là ô không tô màu, dù hai loại ô trông giống nhau.

Tôi đếm hoặc tính tổng theo màu chữ như thế nào?

Khi lọc, bạn chọn Filter by Color rồi chọn màu trong nhóm Filter by Font Color. Với hàm tự tạo, bạn thêm TRUE vào cuối công thức, ví dụ =DEMMAU($B$2:$B$13,F5,TRUE) để đếm và =TONGMAU($B$2:$B$13,F5,$D$2:$D$13,TRUE) để cộng. Ô trống cũng có màu chữ đen mặc định, nên khi đếm chữ màu đen, hàm đếm cả ô trống trong vùng.

Tôi có dùng được hàm DEMMAU ở file khác không?

Hàm DEMMAU chỉ dùng được trong file chứa code. Muốn dùng ở file khác, bạn chép code vào Module của file đó. Nếu bạn gửi file cho người khác, người nhận cũng phải bật macro thì hàm mới chạy.

Bạn muốn học Excel bài bản hơn?

Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.

Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.