COUNTIF theo màu Excel

Roger đang tự hỏi liệu có cách nào để sử dụng hàm COUNTIF bằng cách sử dụng màu nền của ô làm tiêu chí "nếu" không. Anh ấy có một cuốn lịch và anh ấy muốn có thể đếm số ngày mà anh ấy đánh dấu bằng màu tím hoặc các màu khác

Câu trả lời ngắn gọn là không thể sử dụng COUNTIF để kiểm tra màu nền hoặc bất kỳ định dạng nào; . Nếu bạn chỉ cần tính số ô màu tím một hoặc hai lần, bạn có thể sử dụng tính năng Tìm và Thay thế của Excel để tính số đó. Thực hiện theo các bước sau

1. Chọn các ô tạo nên lịch của bạn
2. Nhấn Ctrl + F. Excel hiển thị tab Find của hộp thoại Find and Replace
3. Nhấp vào nút Tùy chọn, nếu có. Excel mở rộng hộp thoại. [Xem Hình 1. ]

//www.cpearson.com/excel/colors.aspx //www.ozgrid.com/VBA/sum-count-cells-by-color.htm //xldynamic.com/source/xld.ColourCounter.html

Ngoài ra còn có một số tiện ích bổ sung của bên thứ ba mà bạn có thể sử dụng. Một tiện ích bổ sung như vậy được người đọc đề xuất là Kutools cho Excel. Bạn có thể tìm thêm thông tin về tiện ích bổ sung tại đây

//www.extendoffice.com/product/kutools-for-excel.html

Một lưu ý cuối cùng—các ý tưởng trong mẹo này hoạt động tốt nếu bạn đang làm việc với các ô được tô màu rõ ràng. Chúng sẽ không hoạt động với các ô được tô màu bằng Định dạng có điều kiện. Đó là một điều hoàn toàn khác để đun sôi, vì Định dạng có điều kiện không thực sự cung cấp cho bạn bất cứ thứ gì bạn có thể nắm bắt dễ dàng

Sử dụng màu sắc trong sổ làm việc là một cách tuyệt vời để làm cho nó trông hấp dẫn hơn. Tuy nhiên, vì không có bất kỳ hàm tích hợp nào để đếm các ô được tô màu trong Excel nên mọi người thường tránh tô màu các ô. Nhưng nó có thể được thực hiện với một số thủ thuật. Trong bài viết này, chúng tôi sẽ hướng dẫn các bạn cách đếm ô màu trong Excel

Mục lục ẩn

Tải xuống mẫu thực hành

4 cách dễ dàng để đếm các ô màu trong Excel

1. Sử dụng lệnh Find & Select để đếm các ô màu trong Excel

2. Áp dụng Bộ lọc và Hàm SUBTOTAL trong Excel để Đếm các ô được tô màu

3. Thực hiện NHẬN. CELL 4 Các hàm Macro và COUNTIFS trong Excel để đếm các ô được tô màu

4. Nhúng mã VBA [Hàm do người dùng xác định] để đếm các ô màu trong Excel

Phần kết luận

Có Thể Bạn Cũng Thích Khám Phá

Tải xuống mẫu thực hành

Bạn có thể tải xuống mẫu Excel thực hành miễn phí từ đây và tự mình thực hành

Đếm các ô màu trong Excel. xlsm

4 cách dễ dàng để đếm các ô màu trong Excel

Trong phần này, bạn sẽ học cách đếm các ô màu trong Excel bằng cách sử dụng các công cụ lệnh Excel và Hàm do người dùng xác định [UDF]

1. Sử dụng lệnh Find & Select để đếm các ô màu trong Excel

Lệnh Tìm & Chọn là một trong những công cụ hữu ích nhất trong Excel để thực hiện bất kỳ tác vụ nào liên quan đến Excel. Ở đây, chúng tôi sẽ sử dụng nó để đếm các ô màu trong Excel

Hãy xem xét tập dữ liệu sau, trong đó có ba loại dữ liệu, Danh mục. Trái cây, Hoa và Thực phẩm. Và mỗi danh mục được phân biệt bởi các màu sắc khác nhau. Danh mục Trái cây khai báo màu Xanh lam, danh mục Hoa màu Cam và danh mục Thực phẩm không có màu nền

Bây giờ chúng ta sẽ học cách tìm ra số lượng của mỗi màu mà mỗi ô của mỗi danh mục nắm giữ

bước

• Chọn tập dữ liệu với các ô màu
• Trong tab Chỉnh sửa, chọn Tìm & Chọn -> Tìm
• Từ hộp Tìm và Thay thế bật lên, nhấp vào Tùy chọn
• Từ hộp Tìm và Thay thế bật lên tiếp theo, nhấp vào danh sách thả xuống trong Định dạng -> Chọn Định dạng Từ Ô
• Biểu tượng dấu cộng bốn chiều sẽ xuất hiện. Đặt biểu tượng đó trên bất kỳ ô màu nào và nhấp vào ô đó [chúng tôi chọn màu Xanh lam]
• Một lần nữa, hộp Tìm và Thay thế bật lên sẽ xuất hiện và bạn sẽ nhận thấy rằng hộp nhãn Xem trước* sẽ được tô màu tương tự như màu của ô mà bạn đã chọn trước đó
• Nhấp vào Tìm tất cả

Bạn sẽ nhận được tất cả các chi tiết của các ô được tô màu đã chỉ định cùng với số lượng các ô được tô màu đó

Theo cách tương tự, bạn có thể đếm tất cả các ô được tô màu khác trong trang tính của mình trong Excel

Đọc thêm. Cách đếm ô màu trong Excel mà không cần VBA [3 phương pháp]

2. Áp dụng Bộ lọc và Hàm SUBTOTAL trong Excel để Đếm các ô được tô màu

Sử dụng công cụ Bộ lọc của Excel và chèn hàm SUBTOTAL vào đó, là một cách hiệu quả khác để đếm các ô được tô màu trong Excel. Và chúng ta cũng có thể sử dụng nó để đếm các ô màu trong Excel

Hãy xem xét tập dữ liệu sau được tô màu bởi Danh mục. Bây giờ chúng ta sẽ tìm hiểu các bước để tìm ra số lượng ô được tô màu đó trong Excel bằng cách sử dụng Bộ lọc và hàm SUBTOTAL

bước

• Trong một ô khác trong trang tính, hãy viết công thức SUBTOTAL sau đây,

=SUBTOTAL[102,B5:B16]


Đây,

102 = Số lượng ô hiển thị trong phạm vi đã chỉ định.

B5. B16 = Phạm vi ô được tô màu.

• Bạn sẽ nhận được tổng số ô được tô màu trong trang tính [e. g. chúng tôi có 12 ô có màu nền, vì vậy SUBTOTAL cho chúng tôi kết quả là 12]
• Tiếp theo, chỉ chọn các tiêu đề của tập dữ liệu
• Chuyển đến Dữ liệu -> Bộ lọc
• Nó sẽ chèn một nút thả xuống trong mỗi tiêu đề của tập dữ liệu
• Nhấp vào nút thả xuống từ tiêu đề của cột có các ô màu trong đó [e. g. Tên sản phẩm]
• Từ danh sách thả xuống, chọn Lọc theo màu và bạn sẽ nhận được tất cả các màu từ tập dữ liệu của mình trong danh sách phụ
• Bấm vào màu mà bạn muốn đếm [e. g. chúng tôi đã chọn màu Xanh lam]
• Nó sẽ chỉ hiển thị cho bạn các ô được tô bằng màu đã chỉ định đó cùng với số lượng các ô đó trong ô kết quả SUBTOTAL [e. g. có 4 ô màu xanh lam trong tập dữ liệu của chúng tôi]
• Theo cách tương tự, bạn có thể đếm tất cả các ô được tô màu khác trong trang tính của mình trong Excel [e. g. khi chúng tôi chọn màu Cam từ danh sách thả xuống, nó sẽ cho chúng tôi các ô được tô màu Cam và vì chúng tôi có 5 ô được tô màu Cam trong tập dữ liệu nên ô kết quả SUBTOTAL tạo ra 5]

Đọc thêm. Đếm ô theo màu với định dạng có điều kiện trong Excel [3 phương pháp]

3. Thực hiện NHẬN. CELL 4 Các hàm Macro và COUNTIFS trong Excel để đếm các ô được tô màu

Sử dụng excel4. 0 Các chức năng macro bị hạn chế do tính tương thích và độ khó của nó. Một lý do khác là đây là một hàm macro cũ trong Excel, vì vậy một số tính năng mới bị thiếu. Nhưng nếu bạn vẫn cảm thấy thoải mái khi làm việc với EXCEL 4. 0 Macro, sau đây chúng tôi sẽ giúp bạn sử dụng chức năng đếm ô màu trong Excel

Với cùng tập dữ liệu vừa thực hành, chúng ta sẽ tìm hiểu cách triển khai hàm Macro 4 để đếm ô màu trong Excel

• Chuyển đến Công thức -> Xác định tên
• Trong hộp bật lên Tên mới, hãy viết như sau,
  • Tên. GetColorCode [đây là tên do người dùng xác định]
  • Phạm vi. Sách bài tập
  • đề cập đến. =NHẬN. TẾ BÀO[38,GetCell. $B5]

Đây,

GetCell = Tên trang tính có tập dữ liệu của bạn

$B5 = Tham chiếu cột có màu nền

• Nhấp vào OK

Bây giờ bạn có một công thức do người dùng xác định, =GetColorCode

• Ở phần liền kề với dữ liệu, hãy viết công thức và nhấn Enter
• Nó sẽ tạo ra một số [e. g. 42]
• Bây giờ hãy kéo ô xuống bằng Fill Handle để áp dụng công thức tương tự cho các ô còn lại

Công thức sẽ trả về các số cụ thể được chỉ định cho màu sắc. Vì vậy, tất cả các ô có cùng màu nền sẽ nhận được cùng một số và nếu không có màu nền, công thức sẽ trả về 0

• Bây giờ hãy xác định các màu đó trong các ô khác trong cùng một trang tính để lấy số lượng

Xem hình bên dưới để hiểu thêm

Chúng tôi đã tạo một bảng có tên là Đếm màu và trong bảng đó, chúng tôi đã xác định các Ô G5 và G6 theo màu của chúng tôi lần lượt là Xanh lam và Cam, đồng thời để trống các ô bên cạnh các ô này [Ô H5 & H6] để chúng tôi có thể lấy

• Viết công thức sau vào ô mà bạn sẽ đếm ô được tô màu,

=COUNTIFS[$E5:$E$16,GetColorCode]


Đây,

$E5. $E$16 = phạm vi mã màu mà chúng tôi đã trích xuất từ ​​công thức do người dùng xác định

• nhấn nút Enter

Bạn sẽ nhận được số lượng ô được xác định màu [e. g. có 4 ô màu Xanh lam trong tập dữ liệu của chúng tôi, vì vậy, bên cạnh ô được xác định màu Xanh lam [G5], nó cho chúng tôi số đếm là 4]

• Bây giờ hãy kéo ô qua toàn bộ cột bằng Fill Handle để có được tất cả số lượng ô được tô màu của bạn trong trang tính

Vì chúng tôi có 5 ô được tô màu Cam trong tập dữ liệu của mình, nên công thức GetColorCode do người dùng xác định đã cho chúng tôi số đếm là 5

4. Nhúng mã VBA [Hàm do người dùng xác định] để đếm các ô màu trong Excel

Việc triển khai mã VBA trong các tác vụ liên quan đến Excel là phương pháp an toàn và hiệu quả nhất, do đó nó đòi hỏi kỹ năng nâng cao từ người dùng. Và hãy nhớ về các tính năng mới mà chúng tôi đã đề cập trong phần Macro 4 trước đó, VBA là sự cải tiến của Excel 4. 0 vĩ mô

Hãy bắt đầu với việc triển khai mã VBA để đếm các ô được tô màu trong Excel

bước

• Nhấn Alt + F11 trên bàn phím của bạn hoặc chuyển đến tab Nhà phát triển -> Visual Basic để mở Visual Basic Editor
• Trong cửa sổ mã bật lên, từ thanh menu, nhấp vào Chèn -> Mô-đun
• Sao chép đoạn mã sau và dán vào cửa sổ mã, ____4

Đây không phải là Quy trình phụ để chương trình VBA chạy, đây là tạo Hàm do người dùng xác định [UDF]. Vì vậy, sau khi viết mã, đừng nhấp vào nút Chạy từ thanh menu

• Bây giờ hãy quay lại tập dữ liệu và xác định các ô có màu sắc như chúng ta đã làm trong phương pháp trước
• Nhìn vào hình bên dưới để hiểu rõ hơn
• Trong ô, viết công thức sau,

=Count_Colored_Cells[E5,$B$5:$B$16]


Đây,

Count_Colored_Cells = hàm do người dùng xác định mà bạn đã tạo trong mã VBA [Count_Colored_Cells, trong dòng đầu tiên của mã].

E5 = Ô được xác định bằng màu xanh lam

$B5. $B$16 = phạm vi của tập dữ liệu có các ô được tô màu.

• nhấn nút Enter

Bạn sẽ nhận được số lượng ô được xác định màu [e. g. có 4 ô màu Xanh lam trong tập dữ liệu của chúng tôi, vì vậy bên cạnh ô được xác định màu Xanh lam [E5], nó cho chúng tôi số đếm 4]

• Bây giờ hãy kéo ô qua toàn bộ cột bằng Fill Handle để có được tất cả số lượng ô được tô màu của bạn trong trang tính

Vì chúng tôi có 5 ô được tô màu Cam trong tập dữ liệu của mình, nên hàm Count_Colored_Cells do người dùng xác định đã cho chúng tôi số đếm là 5

Phần kết luận

Bài viết này đã hướng dẫn bạn cách đếm ô màu trong Excel một cách dễ dàng. Tôi hy vọng bài viết này đã rất hữu ích cho bạn. Vui lòng hỏi nếu bạn có bất kỳ câu hỏi nào liên quan đến chủ đề

Bạn có thể Countif theo màu trong Excel không?

Hàm COUNT trong Excel đếm các ô chứa số trong Excel. Bạn không thể đếm các ô được tô màu hoặc đánh dấu bằng hàm COUNT .

Bạn có thể đếm văn bản màu trong Excel không?

Right-click on a colored cell in the data. Make sure to select the cell with the font color you want to count. Click Filter > Filter by Selected Cell's Font Color to filter the cells with the selected font color. Type in the following formula =SUBTOTAL[103,] where is the reference of your data.