Càng sử dụng Google Trang tính, tôi càng hiểu chúng hiệu quả như thế nào. Và đồng thời, có một số thứ mà tôi muốn nhúng vào Google Spreadsheets. Một tính năng mà tôi muốn có trong Google Trang tính là đếm số lượng ô dựa trên màu nền.
Ví dụ: giả sử tôi có tập dữ liệu được hiển thị bên dưới và tôi muốn nhanh chóng tìm ra tổng số ô có màu nền xanh lục và vàng.
Nhưng vì tính năng này chưa phải là một phần của Google Spreadsheets, bạn có thể thực hiện việc này bằng cách sử dụng tập lệnh ứng dụng gốc của Google để tạo tính năng của riêng bạn trong Google Spreadsheets.
Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách tạo một hàm cho phép bạn đếm các ô dựa trên màu nền dễ dàng như thế nào.
Vậy hãy bắt đầu!
Số lượng ô dựa trên màu nền
Giả sử bạn có tập dữ liệu được hiển thị bên dưới và muốn nhanh chóng biết tổng số ô có màu nền xanh lục và vàng.
Bước đầu tiên là tạo tập lệnh Google Apps để tạo một chức năng tùy chỉnh có thể thực hiện việc này trong Google Trang tính.
Tôi đã cố gắng tìm một tập lệnh như vậy trên web và cuối cùng đã tìm thấy nó trên blog này.
Dưới đây là tập lệnh ứng dụng google cho một chức năng tùy chỉnh sẽ cho phép bạn đếm ô dựa trên màu của ô nền.
function countColoredCells (countRange, colorRef) {var activeRange = SpreadsheetApp.getActiveRange (); var activeSheet = activeRange.getSheet (); var công thức = 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] == màu) count = count + 1; số lượng trả lại; };
Dưới đây là các bước cần thực hiện để đặt tập lệnh này trong tài liệu Google Trang tính:
- Trong tài liệu Google Trang tính của bạn, nhấp vào Công cụ
- Trong các tùy chọn xuất hiện, nhấp vào Trình chỉnh sửa tập lệnh. Trình chỉnh sửa tập lệnh ứng dụng sẽ mở ra.
- Theo mặc định, bạn sẽ ở trong cửa sổ mã Code.gs. Xóa bất cứ thứ gì đã có và sao chép và dán đoạn mã trên.
- Nhấp vào biểu tượng Lưu trên thanh công cụ.
- Đóng cửa sổ tập lệnh ứng dụng.
Sau khi làm theo các bước ở trên, tôi đã thêm mã tập lệnh vào Google Trang tính để bây giờ tôi có thể sử dụng các công thức tùy chỉnh mới tạo của mình trong trang tính (giống như các công thức bình thường).
Ghi chú. Nếu bạn đã có một số mã khác trong cửa sổ Code.gs và không muốn làm rối nó, cách tốt nhất của bạn là dán vào một tệp tập lệnh mới và dán mã vào đó. Để thực hiện việc này, hãy nhấp vào biểu tượng Dấu cộng bên cạnh Tệp và chọn Tập lệnh. Thao tác này sẽ dán một tệp script mới (đặt tên cho nó) và sao chép và dán đoạn mã trên vào tệp script mới này.
Bây giờ chúng ta đã có mã, 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 cần đếm tất cả các tên được đánh dấu bằng màu xanh lá cây.
Để thực hiện việc này, hãy nhập công thức sau vào ô mà bạn muốn đếm số ô được tô màu:
= ô đếm màu (A2: A11, C1)
Trong công thức ở trên, tôi đã sử dụng ô C1 làm ô mà từ đó công thức sẽ chọn màu nền. Bạn cũng có thể sử dụng bất kỳ ô nào trong phạm vi dữ liệu để lấy màu và sau đó tính toán dựa trên nó.
Cũng giống như công thức đếm ô màu xanh lục, công thức dưới đây đếm tất cả các ô màu vàng:
= ô đếm màu (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 những gì đang xảy ra ở đây.
Công thức tùy chỉnh mà chúng tôi đã tạo này có hai đối số:
- một loạt các ô có các ô màu mà chúng ta cần đếm
- ô có màu nền được đếm
Trong ví dụ của chúng tôi, tôi đã lấy đối số thứ hai là ô C1 vì nó cũng có cùng màu mà tôi muốn đếm. Nếu muốn, bạn có thể chọn bất kỳ ô nào từ phạm vi và sử dụng ô đó thay vì tạo một ô mới.
Ví dụ: bạn có thể chọn ô A3 thay vì C1 làm đối số thứ hai vì nó cũng có cùng màu.
Khi bạn sử dụng công thức này, công thức này sẽ xem xét 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 ô khớp với màu nền của đối số thứ hai thì ô đó sẽ được tính, ngược lại thì không.
Sau khi công thức đi qua tất cả các ô trong phạm vi, công thức chỉ cần trả về tổng số ô được tìm thấy có cùng màu nền.
Ghi chú. Bạn có thể sử dụng cùng một công thức để đếm các ô có màu nền khác nhau. Ví dụ: nếu tôi có màu xanh lá cây và màu đỏ trong cùng một tập dữ liệu, thì tôi có thể sử dụng công thức này trong hai ô khác nhau: một ô đề cập đến ô màu xanh lục và đếm tổng số ô màu xanh lục và ô còn lại đề cập đến số lượng màu đỏ tế bào. tổng số hồng cầu.
Cách tạo công thức tự động cập nhật khi nó 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 nhất định, nhưng có một yếu tố gây khó chịu nhỏ.
Nó không tự động cập nhật khi các thay đổi được thực hiện. Ví dụ: nếu tôi xóa màu khỏi một trong các ô hoặc thêm màu theo cách thủ công vào một trong các ô, công thức sẽ không tự động cập nhật để cung cấp cho tôi kết quả chính xác.
Ngay cả khi tôi đi đến ô có công thức, vào chế độ chỉnh sửa và nhấn phím enter, nó vẫn sẽ không được tính toán lại.
Lý do là anh không xem đây là một sự thay đổi cần phải tính toán lại. Tôi thậm chí đã thử cập nhật sổ làm việc nhưng nó cũng không hoạt động (có lẽ do một số vấn đề về bộ nhớ cache)
Rất tiếc là không có cách tự động nào để thực hiện việc này, nhưng có một cách thủ công thực sự dễ dàng.
Vì vậy, đây là cách khắc phục – điều hướng đến bất kỳ ô nào trong phạm vi, vào chế độ chỉnh sửa (nhấn F2 hoặc nhấp đúp vào ô), thêm khoảng trắng ở cuối nội dung của ô và nhấn Enter. Khi bạn làm điều này, bạn sẽ thấy rằng 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 khoảng trắng và xóa nó.
Mặc dù hơi bất tiện nhưng hiện tại đó là cách duy nhất tôi có thể tìm ra để đảm bảo rằng chúng ta đang nhận được số lượng ô màu chính xác bằng cách sử dụng công thức tùy chỉnh này.
Nếu bạn đang 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 thậm chí vài phút để tính tổng số ô có màu nền cụ thể. Lúc này, bạn có thể nhìn thấy dòng chữ “Đang tải” trong ô (như hình bên dưới).
Đây là cách bạn có thể tạo và sử dụng chức năng của riêng mình để tính doanh số bán hàng dựa trên màu nền trong Google Trang tính.
Hy vọng bạn thấy hướng dẫn này hữu ích.
