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?
Bạn có thể định dạng các ô, hàng hoặc cột để thay đổi văn bản hoặc màu nền nếu chúng đáp ứng các điều kiện nhất định. Ví dụ: nếu chúng chứa một từ hay số nào đó. Ví dụ Giáo viên có thể đánh dấu điểm kiểm tra để xem học viên nào có điểm dưới 80%.
Sử dụng định dạng có điều kiện nâng cao
Sử dụng công thức tùy chỉnh với định dạng có điều kiện
Bạn có thể sử dụng công thức tùy chỉnh để áp dụng định dạng cho một hoặc nhiều ô dựa trên nội dung của ô khác.
- Mở một bảng tính trong Google Trang tính trên máy tính.
- Chọn ô bạn muốn định dạng.
- Nhấp vào phần Định dạng Định dạng có điều kiện.
- Trong trình đơn thả xuống có tên "Định dạng ô nếu", hãy nhấp vào phần Công thức tùy chỉnh là Nếu đã có quy tắc, hãy nhấp vào quy tắc đó hoặc mục Thêm quy tắc mới Công thức tùy chỉnh là.
- Nhấp vào phần Giá trị hoặc công thức và thêm công thức cùng với quy tắc.
- Nhấp vào phần Xong.
Lưu ý: Công thức chỉ có thể tham chiếu cùng một trang tính, sử dụng ký hiệu chuẩn "[='sheetname'! Cell]". Để tham chiếu một trang tính khác trong công thức, hãy sử dụng hàm INDIRECT.
Ví dụ 1
Cách đánh dấu khi có nhiều lần xuất hiện cùng một giá trị trong dữ liệu của bạn:
- Mở một bảng tính trong Google Trang tính trên máy tính.
- Chọn dải ô bạn muốn định dạng. Ví dụ: ô A1 đến A100.
- Nhấp vào phần Định dạng Định dạng có điều kiện.
- Trong trình đơn thả xuống có tên "Định dạng ô nếu", hãy nhấp vào phần Công thức tùy chỉnh là Nếu đã có quy tắc, hãy nhấp vào quy tắc đó hoặc mục Thêm quy tắc mới Công thức tùy chỉnh là.
- Viết quy tắc cho hàng đầu tiên. Trong trường hợp này, quy tắc sẽ là: "=COUNTIF[$A$1:$A$100;A1]>1".
- Chọn thuộc tính định dạng khác.
- Nhấp vào phần Xong.
Ví dụ 2
Để định dạng toàn bộ hàng dựa vào giá trị của một trong những ô trong hàng đó:
- Mở một bảng tính trong Google Trang tính trên máy tính.
- Chọn dải ô bạn muốn định dạng, ví dụ như cột A:E.
- Nhấp vào phần Định dạng Định dạng có điều kiện.
- Trong trình đơn thả xuống có tên "Định dạng ô nếu", hãy nhấp vào phần Công thức tùy chỉnh là Nếu đã có quy tắc, hãy nhấp vào quy tắc đó hoặc mục Thêm quy tắc mới Công thức tùy chỉnh là.
- Viết quy tắc cho hàng đầu tiên. Ví dụ: nếu bạn muốn đánh dấu màu xanh lục cho toàn bộ hàng khi giá trị trong cột B là "Có", hãy viết công thức như sau: =$B1="Yes".
- Chọn thuộc tính định dạng khác.
- Nhấp vào phần Xong.
Tham chiếu tương đối và tham chiếu tuyệt đối
Thông thường, bạn cần phải thêm ký hiệu đô la [$] vào trước chữ cái và số trong các công thức này để áp dụng định dạng bằng tham chiếu tuyệt đối trái ngược với tham chiếu tương đối [A1 đến B1, A2 đến B2].
Sử dụng ký tự đại diện với định dạng có điều kiện
Bạn có thể sử dụng ký tự đại diện để khớp nhiều biểu thức. Bạn có thể sử dụng ký tự đại diện với các trường "Nội dung có chứa" hoặc "Nội dung không chứa" trong khi định dạng.
- Để khớp với bất kỳ ký tự đơn nào, hãy sử dụng dấu chấm hỏi [?]. Ví dụ: một quy tắc văn bản chứa "a?c" sẽ định dạng ô có chứa "abc" nhưng không chứa "ac" hoặc "abbc".
- Sử dụng dấu hoa thị [*] để khớp không [0] ký tự trở lên. Ví dụ: quy tắc nội dung chứa "a*c" sẽ định dạng ô có chứa "abc", "ac" và "abbc" nhưng không chứa "ab" hoặc "ca".
- Để khớp dấu hỏi chấm hoặc dấu hoa thị trong văn bản, bạn có thể thoát các ký tự đại diện bằng cách thêm dấu ngã [~] vào trước chúng. Ví dụ: quy tắc văn bản chứa "a~?c" sẽ định dạng các ô chứa "a?c" nhưng không chứa "abc" hoặc "a~?c".
Lưu ý:
- Để xóa một quy tắc, hãy trỏ đến quy tắc đó rồi nhấp vào biểu tượng Xóa .
- Các quy tắc được đánh giá theo thứ tự được liệt kê. Quy tắc đầu tiên được cho là đúng sẽ xác định định dạng của ô hoặc dải ô. Để sắp xếp lại quy tắc, hãy nhấp và kéo chúng.
- Nếu bạn sao chép và dán từ một ô hoặc dải ô có quy tắc định dạng thì khi dán dữ liệu được sao chép đó, các quy tắc này sẽ được áp dụng.
Thông tin này có hữu ích không?
Chúng tôi có thể cải thiện trang này bằng cách nào?