Nếu bạn đánh dấu dữ liệu bằng màu trong Google Sheets, bạn có thể nhận ra rằng thiếu công thức để tổng hợp loại thông tin này. Đây là lý do tại sao chúng ta thêm một hàm tùy chỉnh để xem phông chữ và màu nền của các ô. Bạn có thể tìm thấy trong số các tính năng thông minh của Power Tools. Phiên bản Pro hiện tại miễn phí, vì vậy bạn có thể có tiện ích này chỉ với một cú nhấp chuột tại Google Sheets Store.

Cách đếm các ô được tô màu

Hãy thực hiện một trong những việc làm phổ biến nhất đối với các ô màu trong bảng tính: đếm các ô có cùng định dạng. Ở đây mình có một danh sách và mình muốn biết số lần học sinh thi đậu của mỗi bài kiểm tra, tức là đếm tất cả các ô màu xanh lá cây trong một cột:

Nếu bạn làm việc với vùng dữ liệu tương tự bao gồm những định dạng khác: số, văn bản, hoặc giá trị ngày, dùng hàm COUNTA với công cụ Sum By Colour. Để bắt đầu, mở công cụ Sum By Color. Bạn có thể nhìn thấy nó nếu bạn xổ xuống danh sách AutoSum trong thanh công cụ thông minh của Power Tools:

Dưới đây là bốn bước đơn giản mà bạn cần chỉ định để có được kết quả:

1. Pattern cell: Chức năng này sẽ dùng phông chữ và màu nền của ô được chọn và tìm các ô khác giống nó.  mình chọn B2 như ví dụ.

2. Source range: Vùng dữ liệu được phủ màu bạn muốn kiểm tra: Mình chọn các cột thể hiện kết quả kiểm tra.

3. Use function: Khi bạn muốn đếm các ô có định dạng cụ thể, tốt nhất nên chọn hàm COUNTA bởi vì chỉ có nó hoạt động với các giá trị không phải là số. Bằng cách này, chúng ta có thể chắc chắn rằng định dạng của dữ liệu trong các ô sẽ không ảnh hưởng kết quả.

4. Insert function after: Làm thế nào để áp dụng nó. Chúng ta có thể chọn đếm các ô trong mỗi hàng, cột, hoặc trong tất cả các ô đã chọn. Vì mình muốn đếm các ô màu xanh lá cây cho mỗi bài kiểm tra, mình chọn sau mỗi cột (Each column). Nếu mình muốn một kết quả tổng quát cho cả ba bài kiểm tra, mình sẽ chọn toàn bộ vùng dữ liệu (Entire range).

Khi mình nhấp vào Insert function, công cụ thêm công thức dưới mỗi cột, vì vậy mình sẽ thấy kết quả cho mỗi bài kiểm tra.

Hãy để mình chia nhỏ cú pháp và giải thích từng phần:

• = COUNTA là một trong những tính năng của Google Sheets mà bạn chọn để sử dụng với các ô được tô màu.
• #B6d6a8 là mã màu của ô được dùng cho thuật toán, tức là màu nền của ô được chọn.
• #000000 là màu chữ của ô được chọn. Nếu bạn chỉ muốn dùng màu nền, bạn có thể xóa giá trị này. Sau đó công thức sẽ như sau: = COUNTA (valuesByColor (“# b6d7a8”, “”, B2: B40)).
• D2: D40 là vùng dữ liệu có các ô được đánh dấu màu cần đếm. Nó là cột D trong ví dụ của mình.

Làm thế nào để cộng các ô theo màu?

Bạn A đang theo dõi các đơn hàng thiết bị cho lớp học. Bạn A ghi chú những vật được đặt hàng bằng màu vàng, vật đang chuyển tới bằng màu xanh dương, và thiết bị đã giao màu xanh lá cây:

Việc của bạn A là xem có bao nhiêu bàn làm việc, máy vi tính, và các thứ khác đã được chuyển tới. Chúng được phân biệt bằng màu sắc. Vì vậy, bạn A mở Sum By Color và sử dụng với các cài đặt sau:

1. Bạn A chọn C2 là ô mẫu để xác định định dạng của các ô mình muốn tính toán.

2. Bạn A chọn toàn bộ vùng dữ liệu để kiểm tra.

3. Sau đó chọn hàm SUM để cộng các số của ô màu xanh lá cây.

4. Để xem số lượng hàng đã giao của mỗi sản phẩm, áp dụng hàm cho mỗi hàng (each row)

Nhấp vào Insert function để có được công thức sau mỗi dòng trong bảng.

Sự thuận tiện khi có công thức là bạn có thể sửa đổi bất kỳ bộ phận nào của nó và dán nó vào bất cứ nơi nào bạn cần trong Google Sheets.

Cập nhật kết quả

Bạn có nhớ rằng Google Sheets không có các chức năng làm việc với màu sắc không? Điều này có nghĩa là chúng không xem sự thay đổi định dạng ô như là một lý do để tính lại công thức của bạn. Tin vui là bạn có thể sử dụng hai giải pháp rất đơn giản sau.

1. Bạn có thể thay đổi bất kỳ giá trị nào trong vùng dữ liệu. Bạn đang đếm các ô màu xanh lá cây trong A1: C254. Bạn chỉ cần thêm ký tự vào bất kỳ ô nào trong vùng này, và sau đó xóa nó để có được kết quả cập nhật.

2. Nếu bạn có nhiều công thức valuesByColor trong trang tính, hãy nhấp vào tùy chọn Refresh ở bên cạnh Sum By Color trong Power Tools để cập nhật tất cả các công thức trong một lần.

Xử lý sự cố

Nếu bạn thấy lỗi ở vị trí công thức, có thể bạn đang làm việc với file chưa cài đặt ngôn ngữ. Khi điều này xảy ra, công thức không biết những dấu phân cách nên sử dụng, vì vậy báo lỗi. Nếu bạn gặp điều này, vui lòng chọn File -> Spreadsheet setting… trong Google Sheets và cài đặt ngôn ngữ.

Nguồn: Ablebits, dịch và biên tập bởi Hocexcel Online.

Học Excel ở đâu tốt?