Khi tôi sử dụng Google Sheets càng nhiều, tôi nhận ra sức mạnh của nó. Tuy nhiên, có một số điều tôi muốn thấy tích hợp vào chương trình như cách đếm các ô màu trong Google Sheets. Điều này có nghĩa là cách đếm số lượng ô dựa trên màu nền.
Ví dụ, giả sử tôi có một tập dữ liệu như hình dưới đây và tôi muốn nhanh chóng biết tổng số ô có màu nền xanh và vàng.
Tuy nhiên, vì tính năng này không có sẵn trong Google Sheets, bạn có thể làm điều đó bằng cách sử dụng một kịch bản Google Apps tùy chỉnh để tạo chức năng của riêng bạn trong Google Sheets.
Trong bài hướng dẫn này, tôi sẽ chỉ cho bạn cách dễ dàng tạo một hàm để đếm các ô có màu trong Google Sheets dựa trên màu nền. Bạn có thể sao chép VÍ DỤ BẢNG và làm theo hướng dẫn.
Vậy thì bắt đầu nào!
Làm quen với kĩ năng phức tạp của Google Sheets
Bài hướng dẫn này bao gồm việc tạo chức năng tùy chỉnh của riêng bạn bằng cách sử dụng Google Apps Script, đây là một kỹ năng khá cao cấp. Nếu điều này vượt quá khả năng hiện tại của bạn để theo dõi hướng dẫn này, chúng tôi khuyên bạn nên tham gia một khóa học về Google Sheets trên Udemy để làm quen với kỹ năng tổng thể của bạn về bảng tính.
Đếm các ô dựa trên màu nền
Giả sử bạn có một tập dữ liệu như hình dưới đây và bạn muốn nhanh chóng biết tổng số ô có màu nền xanh và vàng.
Bước đầu tiên sẽ là sử dụng kịch bản Google Apps để tạo một chức năng tùy chỉnh có thể làm điều đó trong Google Sheets.
Tôi đã cố gắng tìm một kịch bản như vậy trực tuyến và cuối cùng tôi đã tìm thấy một cái trên blog này.
Đây là kịch bản Google Apps cho chức năng tùy chỉnh cho phép bạn đếm các ô dựa trên màu nền:
function countColoredCells(countRange,colorRef) {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = activeRange.getSheet();
var formula = activeRange.getFormula();
var rangeA1Notation = formula.match(/((.*),/).pop();
var range = activeSheet.getRange(rangeA1Notation);
var bg = range.getBackgrounds();
var values = range.getValues();
var colorCellA1Notation = formula.match(/,(.*))/).pop();
var colorCell = activeSheet.getRange(colorCellA1Notation);
var color = colorCell.getBackground();
var count = 0;
for(var i=0;i<bg.length;i++)
for(var j=0;j<bg[0].length;j++)
if( bg[i][j] == color )
count=count+1;
return count;
};Dưới đây là các bước để đưa kịch bản này vào Google Sheets của bạn:
- Trong tài liệu Google Sheets của bạn, nhấp vào Công cụ.
- Trong các tùy chọn hiện ra, nhấp vào Trình chỉnh sửa.
- Theo mặc định, bạn sẽ ở trang mã Code.gs. Xóa tất cả những gì đã có và sau đó sao chép và dán mã trên.
- Nhấp vào biểu tượng lưu trong thanh công cụ.
- Đóng cửa sổ Apps Script.
Bằng cách thực hiện các bước trên, tôi đã thêm mã script vào Google Sheets để hiện tại tôi có thể sử dụng các công thức tùy chỉnh mới của mình trong bảng tính (giống như các công thức bình thường).
Lưu ý: Nếu bạn đã có một mã khác trong cửa sổ Code.gs và bạn không muốn làm rối nó, thì tốt nhất là chèn một tệp script mới và sao chép dán mã vào đó. Để làm điều này, nhấp vào biểu tượng Thêm gần Các tệp, sau đó chọn Kịch bản. Điều này sẽ chèn một tệp script mới (đặt tên cho nó) và sao chép dán mã trên vào tệp script mới này.
Bây giờ chúng ta đã có mã ở đây, hãy xem cách sử dụng công thức tùy chỉnh để đếm các ô có màu nền cụ thể.
Trong ví dụ của chúng tôi, tôi muốn đếm tất cả các tên có màu xanh.
Để làm điều này, nhập công thức sau vào ô mà bạn muốn nhận số ô có màu:
=countcoloredcells(A2:A11,C1)
Trong công thức trên, tôi đã sử dụng ô C1 làm ô mà công thức phải lấy màu nền từ đó. Bạn cũng có thể sử dụng bất kỳ ô nào trong dải dữ liệu để có màu, sau đó đếm dựa trên nó.
Tương tự như công thức để đếm các ô có màu xanh, công thức dưới đây đếm tất cả các ô có màu vàng:
=countcoloredcells(A2:A11,D1)
Công thức này hoạt động như thế nào?
Hãy để tôi giải thích nhanh về những gì đang diễn ra ở đây.
Công thức tùy chỉnh chúng tôi đã tạo ra nhận 2 đối số:
- Phạm vi ô chứa các ô đã tô màu chúng tôi muốn đếm.
- Ô chứa màu nền để đếm.
Trong ví dụ của chúng tôi, tôi đã chọn đối số thứ hai là ô C1, vì nó cũng có màu tôi muốn đếm. Nếu bạn muốn, bạn có thể chọn ô A3 thay vì C1 là đối số thứ hai, vì nó cũng có màu giống nhau.
Khi sử dụng công thức này, nó sẽ duyệt qua tất cả các ô trong phạm vi và kiểm tra màu nền của tất cả các ô. Nếu màu nền của một ô khớp với màu nền của đối số thứ hai, thì nó sẽ được đếm, nếu không thì nó sẽ không được đếm.
Sau khi công thức đã duyệt qua tất cả các ô trong phạm vi, nó chỉ đơn giản trả về tổng số ô nó đã tìm thấy có cùng màu nền.
Lưu ý: Bạn có thể sử dụng cùng một công thức để đếm các ô có các màu nền khác nhau. Ví dụ, nếu trong cùng một tập dữ liệu, tôi có màu xanh và đỏ, tôi có thể sử dụng công thức này trong hai ô khác nhau – một tham chiếu ô xanh và đếm tổng số ô xanh, và một tham chiếu ô đỏ và đếm tổng số ô đỏ.
Làm sao để công thức tự động cập nhật khi thay đổi?
Mặc dù công thức hoạt động hoàn hảo và đếm số lượng ô có màu nền cụ thể, có một điểm nhỏ gây khó chịu.
Nó không tự động cập nhật khi bạn thay đổi. Ví dụ, nếu tôi xóa màu trong một trong các ô hoặc thêm màu vào một trong các ô bằng cách thủ công, công thức sẽ không tự động cập nhật để cho tôi kết quả chính xác.
Ngay cả khi tôi đi vào ô chứa công thức, chuyển sang chế độ chỉnh sửa và nhấn Enter, công thức cũng không tính lại.
Lý do là nó không coi điều đó là một thay đổi đòi hỏi tính toán lại. Tôi đã thử cập nhật lại bảng tính nhưng nó cũng không hoạt động (có thể do vấn đề bộ nhớ cache).
Rất tiếc, không có cách tự động để làm điều này, nhưng có một phương pháp thủ công rất đơn giản để làm điều đó.
Dưới đây là giải pháp: hãy vào một trong các ô trong phạm vi, chuyển sang chế độ chỉnh sửa (nhấn F2 hoặc nhấp đúp vào ô), thêm một khoảng trống vào cuối nội dung của ô và nhấn Enter. Ngay khi bạn làm điều đó, bạn sẽ thấy công thức được tính toán lại và đưa ra kết quả chính xác.
Bây giờ bạn có thể quay lại ô mà bạn đã thực hiện thay đổi này bằng cách thêm một khoảng trống và xóa nó.
Mặc dù điều này gây khó chịu nhưng hiện tại, đây là cách duy nhất tôi đã tìm thấy để đảm bảo chúng tôi có số đếm chính xác của các ô có màu bằng cách sử dụng công thức tùy chỉnh này.
Nếu bạn làm việc với một tập dữ liệu lớn, công thức này có thể mất vài giây hoặc vài phút để tính toán tổng số ô có màu nền cụ thể. Bạn có thể thấy một văn bản “Đang tải” trong ô trong thời gian này (như hình dưới đây).
Cách đếm các ô màu sử dụng hàm SOUSTOTAL
Hiện tại, không có công thức COUNTIF màu nào trong Google Sheets mà bạn có thể sử dụng để đếm các ô theo màu. Thay vào đó, bạn có thể sử dụng hàm SOUSTOTAL của Google Sheets để đếm các ô với màu. Quá trình này tương tự với cách bạn sẽ sử dụng để đếm các ô được tô màu sắc trong Excel.
Trước tiên, bạn sẽ cần sử dụng chức năng bộ lọc trên dữ liệu để sắp xếp theo màu. Chúng tôi sẽ giúp bạn làm điều này trong một hướng dẫn từng bước. Dưới đây là cách đếm các ô có màu trong Google Sheets bằng cách sử dụng hàm SOUSTOTAL:
- Chọn dải dữ liệu
- Đi đến Dữ liệu > Tạo bộ lọc.
- Nếu bạn đi đến tùy chọn bộ lọc trên hàng tiêu đề và chọn “Lọc theo màu” > “Màu nền”, bạn sẽ thấy các tùy chọn màu của bạn.
- Chọn màu bạn muốn đếm. Trong trường hợp của chúng tôi, chúng tôi sẽ chọn màu vàng.
- Bây giờ, chúng ta có thể sử dụng công thức SOUSTOTAL =SOUSTOTAL(103, A2:A11). Kết quả sẽ là 4.
Số 103 được sử dụng để chỉ định rằng loại tổng phụ mà chúng tôi sử dụng là phiên bản đếm. Chúng tôi sử dụng hàm SOUSTOTAL thay vì COUNTIF vì nó chỉ đếm các ô hiển thị. COUNTIF, åväwj sử dụng để đếm các ô chứa văn bản cụ thể.
Cách đếm các ô màu sử dụng tiện ích
Bạn cũng có thể đếm các ô theo màu bằng cách sử dụng tiện ích Function by Color. Tiện ích này thực sự là một phần của tiện ích Power Tools, có nhiều tính năng hơn. Bạn có thể chọn tải tiện ích Power Tools hoặc chỉ bằng tiện ích Function by Color.
Phưong pháp này ít gây sợ hãi hơn so với việc sử dụng kịch bản Google Apps.
Dưới đây là cách đếm các ô theo màu trong Google Sheets bằng cách sử dụng tiện ích Function by Color:
- Để tải tiện ích, hãy vào Tiện ích > Tiện ích bổ sung.
- Nhấp vào Lấy tiện ích.
- Trong cửa sổ tiện ích, gõ “function by color” và nhấp vào Tìm kiếm.
- Chọn tiện ích “Function by color” và nhấp vào Cài đặt.
- Bạn có thể cần phải cho phép quyền. Nhấp vào Tiếp tục và chọn tài khoản Google của bạn.
Để sử dụng tiện ích để đếm theo màu trong Google Sheets:
- Chọn dải dữ liệu.
- Đi vào Tiện ích.
- Chọn Function by color và nhấp vào Bắt đầu.
- Trong cửa sổ tiện ích, chọn màu nền bạn muốn đếm và thêm công thức vào. Trong trường hợp này, chúng tôi sẽ sử dụng công thức COUNTA.
- Nhấp vào hộp kết quả dán và đưa ra ô D2.
- Bạn có thể chọn bỏ chọn tùy chọn “Điền kết quả bằng màu mẫu của bạn”.
- Nhấp vào Chèn công thức.
Bạn sẽ nhận được kết quả là 4 trong ô D2 với một công thức đặc biệt trong hộp công thức.
Nếu bạn đã chọn tải tiện ích Power Tools thay vì, bạn có thể tìm thấy tiện ích Function by Color trong thanh công cụ cho Power Tools.
Tiện ích là giống nhau, vì vậy bạn có thể làm theo các bước trên để đếm các ô theo màu.
Cách tổng hợp các giá trị theo màu
Cho đến nay, chúng tôi đã cho bạn thấy cách đếm các ô được tô màu trong Google Sheets. Tiện ích Function by Color cũng hữu ích cho các chức năng khác ngoài việc đếm đơn giản. Bạn có thể sử dụng nó để tìm tổng các giá trị theo màu cũng.
Hãy sử dụng ví dụ của chúng tôi dưới đây để tìm tổng các giá trị được tô màu vàng:
- Chọn cột chứa các giá trị.
- Đi vào Tiện ích.
- Chọn Function by color và nhấp vào Bắt đầu.
- Trong thanh bên của tiện ích, hãy chọn màu nền.
- Trong hộp drop-down để sử dụng các hàm, hãy chọn SUM.
- Chọn nơi kết quả sẽ được dán. Trong trường hợp của chúng tôi, chúng tôi đã chọn ô B12.
- Nhấp vào Insert Function.
Bạn sẽ nhận được kết quả trong ô B12, đó là tổng của tất cả các giá trị màu vàng. Một điều quan trọng cần lưu ý là tiện ích Function by Color không cập nhật tự động. Nếu bạn cập nhật bảng tính của mình, bạn vẫn có thể vào Tiện ích > Function by Color và nhấp vào Refresh Results.
Tóm lại, trong bài viết này, chúng tôi đã chỉ cho bạn 3 cách đếm các ô được tô màu trong Google Sheets. Bạn có thể sử dụng một công thức tùy chỉnh trong Google Sheets hoặc tiện ích Function by Color. Bạn cũng có thể sử dụng hàm SOUSTOTAL như công thức COUNTIF màu bằng cách áp dụng bộ lọc trước.
Hy vọng rằng bạn đã tìm thấy hướng dẫn này hữu ích. Bạn cũng có thể xem cách đếm các ô nếu chúng không trống trong Google Sheets.
Có thể các hướng dẫn khác về Google Sheets cũng sẽ thu hút bạn:
- Đếm các ô NẾU KHÔNG Trống (các ô không trống) trong Google Sheets.
- Làm thế nào để đếm các ô với văn bản cụ thể trong Google Sheets.
- Làm thế nào để sắp xếp theo màu trong Google Sheets.
- Làm thế nào để tô sáng các hàng xen kẽ trong Google Sheets.
- Làm thế nào để lọc theo màu trong Google Sheets (sử dụng công thức).
- Làm thế nào để làm nổi bật giá trị cao nhất hoặc thấp nhất trong Google Sheets.
- Làm thế nào để đếm các ô kiểm trong Google Sheets (công thức dễ dàng).
- Excel có thể đếm các ô có chứa văn bản không? [Hướng dẫn dễ dàng].
- Làm thế nào để đếm các giá trị duy nhất trong Excel [Hướng dẫn].
