Note: The other languages of the website are Google-translated. Back to English

Làm cách nào để đếm hoặc tính tổng các ô dựa trên màu ô trong Google sheet?

Đếm hoặc tổng hợp các giá trị ô dựa trên màu nền ô cụ thể để có được kết quả như ảnh chụp màn hình sau. Bài viết này, tôi sẽ nói về cách giải quyết tác vụ này trong Google sheet và Microsoft Excel.

Đếm giá trị ô dựa trên màu ô với tập lệnh trong Google sheet

Tính tổng các giá trị ô dựa trên màu ô có tập lệnh trong Google sheet

Đếm hoặc tổng hợp các giá trị ô trên màu ô với Kutools cho Excel trong Microsoft Excel


Đếm giá trị ô dựa trên màu ô với tập lệnh trong Google sheet

Tập lệnh sau có thể giúp bạn đếm các giá trị ô dựa trên màu ô cụ thể, vui lòng thực hiện như sau:

1. Nhấp chuột CÔNG CỤ > Biên tập kịch bản, xem ảnh chụp màn hình:

2. Trong cửa sổ dự án đã mở, nhấp vào Tập tin > Mới > Tập kịch bản để mở cửa sổ mã, hãy xem ảnh chụp màn hình:

3. Và trong hộp nhắc nhở, vui lòng nhập tên cho mã tập lệnh này, xem ảnh chụp màn hình:

4. Nhấp chuột OK và sau đó sao chép và dán mã sau để thay thế mã gốc vào mô-đun mã, xem ảnh chụp màn hình:

function countColoredCells(countRange,colorRef) {
  var activeRg = SpreadsheetApp.getActiveRange();
  var activeSht = SpreadsheetApp.getActiveSheet();
  var activeformula = activeRg.getFormula();
  var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
  var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
  var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
  var BackGround = activeSht.getRange(colorRefAddress).getBackground();
  var countCells = 0;
  for (var i = 0; i < backGrounds.length; i++)
    for (var k = 0; k < backGrounds[i].length; k++)
      if ( backGrounds[i][k] == BackGround )
        countCells = countCells + 1;
  return countCells;
};

5. Sau đó lưu mã tập lệnh này và quay lại trang tính, nhập công thức này: = ô đếm màu (A1: E11, A1) vào một ô trống, rồi nhấn đăng ký hạng mục thi phím để lấy kết quả tính toán. Xem ảnh chụp màn hình:

Chú thích: Trong công thức này: A1: E11 là phạm vi dữ liệu mà bạn muốn sử dụng, A1 là ô được tô bằng màu cụ thể mà bạn muốn đếm.

6. Lặp lại công thức trên để đếm các ô có màu cụ thể khác.


Tính tổng các giá trị ô dựa trên màu ô có tập lệnh trong Google sheet

Để tính tổng các giá trị ô với một màu ô cụ thể, vui lòng áp dụng mã tập lệnh bên dưới.

1. Nhấp chuột CÔNG CỤ > Biên tập kịch bản để chuyển đến cửa sổ dự án và nhấp vào Tập tin > Mới > Tập kịch bản để chèn một mô-đun mã mới khác, sau đó, trong hộp nhắc nhở, vui lòng nhập tên cho tập lệnh này, xem ảnh chụp màn hình:

2. Nhấp chuột OK và trong mô-đun mã đã mở, hãy sao chép và dán mã tập lệnh bên dưới để thay thế mã gốc, xem ảnh chụp màn hình:

function sumColoredCells(sumRange,colorRef) {
  var activeRg = SpreadsheetApp.getActiveRange();
  var activeSht = SpreadsheetApp.getActiveSheet();
  var activeformula = activeRg.getFormula();
  var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
  var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
  var sumValues = activeSht.getRange(countRangeAddress).getValues();  
  var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
  var BackGround = activeSht.getRange(colorRefAddress).getBackground();
  var totalValue = 0;
  for (var i = 0; i < backGrounds.length; i++)
    for (var k = 0; k < backGrounds[i].length; k++)
      if ( backGrounds[i][k] == BackGround )
        if ((typeof sumValues[i][k]) == 'number')
          totalValue = totalValue + (sumValues[i][k]);
  return totalValue;
};

3. Và sau đó lưu mã này, quay lại trang tính và nhập công thức này: = sumcoloredcells (A1: E11, A1) vào một ô trống và nhấn đăng ký hạng mục thi để lấy kết quả được tính toán, xem ảnh chụp màn hình:

Chú thích: Trong công thức này: A1: E11 là phạm vi dữ liệu mà bạn muốn sử dụng, A1 là ô có màu nền cụ thể mà bạn muốn tính tổng.

4. Và sau đó bạn có thể lặp lại công thức trên để tính tổng các ô có màu cụ thể khác.


Đếm hoặc tổng hợp các giá trị ô trên màu ô với Kutools cho Excel trong Microsoft Excel

Trong Microsoft Excel, để đếm hoặc tính tổng các giá trị ô dựa trên màu ô cụ thể, Kutools cho Excel'S Đếm theo màu tiện ích có thể giúp bạn hoàn thành công việc này nhanh nhất có thể.

Kutools cho Excel : với hơn 300 bổ trợ Excel tiện dụng, dùng thử miễn phí không giới hạn trong 30 ngày. 

Sau khi cài đặt Kutools cho Excel, hãy làm như sau:

1. Chọn các ô trong phạm vi mà bạn muốn đếm hoặc tính tổng dựa trên màu ô, sau đó bấm Kutools Plus > Đếm theo màu, xem ảnh chụp màn hình:

2. Trong Đếm theo màu hộp thoại, chọn Định dạng tiêu chuẩn từ Phương pháp màu danh sách thả xuống, sau đó chọn Bối cảnh từ Loại đếm thả xuống, xem ảnh chụp màn hình:

3. Sau đó nhấn vào Tạo báo cáo và bảng tính mới với kết quả được tính toán được tạo cùng một lúc, xem ảnh chụp màn hình:

Chú thích: Với tính năng mạnh mẽ này, bạn cũng có thể tính toán các giá trị ô dựa trên định dạng có điều kiện hoặc màu phông chữ.

Nhấp vào Tải xuống và dùng thử miễn phí Kutools cho Excel ngay!


Các công cụ năng suất văn phòng tốt nhất

Kutools cho Excel giải quyết hầu hết các vấn đề của bạn và tăng 80% năng suất của bạn

  • Tái sử dụng: Chèn nhanh công thức phức tạp, biểu đồ và bất cứ thứ gì bạn đã sử dụng trước đây; Mã hóa ô với mật khẩu; Tạo danh sách gửi thư và gửi email ...
  • Thanh siêu công thức (dễ dàng chỉnh sửa nhiều dòng văn bản và công thức); Bố cục đọc (dễ dàng đọc và chỉnh sửa số lượng ô lớn); Dán vào Dải ô đã Lọchữu ích. Cảm ơn !
  • Hợp nhất các ô / hàng / cột mà không làm mất dữ liệu; Nội dung phân chia ô; Kết hợp các hàng / cột trùng lặp... Ngăn chặn các ô trùng lặp; So sánh các dãyhữu ích. Cảm ơn !
  • Chọn trùng lặp hoặc duy nhất Hàng; Chọn hàng trống (tất cả các ô đều trống); Tìm siêu và Tìm mờ trong Nhiều Sổ làm việc; Chọn ngẫu nhiên ...
  • Bản sao chính xác Nhiều ô mà không thay đổi tham chiếu công thức; Tự động tạo tài liệu tham khảo sang Nhiều Trang tính; Chèn Bullets, Hộp kiểm và hơn thế nữa ...
  • Trích xuất văn bản, Thêm Văn bản, Xóa theo Vị trí, Xóa không gian; Tạo và In Tổng số phân trang; Chuyển đổi giữa nội dung ô và nhận xéthữu ích. Cảm ơn !
  • Siêu lọc (lưu và áp dụng các lược đồ lọc cho các trang tính khác); Sắp xếp nâng cao theo tháng / tuần / ngày, tần suất và hơn thế nữa; Bộ lọc đặc biệt bằng cách in đậm, in nghiêng ...
  • Kết hợp Workbook và WorkSheets; Hợp nhất các bảng dựa trên các cột chính; Chia dữ liệu thành nhiều trang tính; Chuyển đổi hàng loạt xls, xlsx và PDFhữu ích. Cảm ơn !
  • Hơn 300 tính năng mạnh mẽ. Hỗ trợ Office / Excel 2007-2021 và 365. Hỗ trợ tất cả các ngôn ngữ. Dễ dàng triển khai trong doanh nghiệp hoặc tổ chức của bạn. Đầy đủ tính năng Dùng thử miễn phí 30 ngày. Bảo đảm hoàn lại tiền trong 60 ngày.
tab kte 201905

Tab Office mang lại giao diện Tab cho Office và giúp công việc của bạn trở nên dễ dàng hơn nhiều

  • Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
  • Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
  • Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
officetab dưới cùng
Nhận xét (77)
Chưa có xếp hạng. Hãy là người đầu tiên xếp hạng!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào! Tập lệnh tuyệt vời nhưng tôi gặp sự cố khi cố gắng chỉnh sửa các giá trị trong các ô được tô màu. Tập lệnh không làm mới các giá trị nếu tôi thay đổi màu của ô. Tôi chỉ có thể buộc nó làm mới nếu tôi xóa nội dung của ô, thay đổi màu và sau đó nhập lại giá trị.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Này, cảm ơn vì kịch bản này. Tôi đang gặp sự cố trong tập lệnh cho google trang tính: TypeError: Không thể gọi phương thức "pop" của null. (dòng 5, tệp "SumByColor"). Bạn có thể vui lòng giúp đỡ ?, cảm ơn
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Такая же ошибка. Не срабатывает(
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Cảm ơn sự trợ giúp với "Đếm ô dựa trên màu cho trang tính của google". Giới hạn dường như là khi màu ô được thay đổi, trang tính không làm mới và tổng số không chính xác. Tôi phải xóa mã trong ô tổng và sao chép lại từ ô hàng xóm. Không lý tưởng, nhưng nó hoạt động. Cảm ơn một lần nữa.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, Todd,
Có, như bạn đã nói, bạn nên nhập lại công thức khi màu ô thay đổi.
Có thể không có cách nào khác tốt để giải quyết vấn đề này.
Nếu khác có phương pháp nào hay, vui lòng comment tại đây.
Cảm ơn bạn!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Hàm sumColoredCells () của bạn không chính xác vì nó không sử dụng sumRange
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, Dave,
Tập lệnh trên hoạt động tốt trong trang tính Google của tôi.
Khi áp dụng công thức, bạn nên lưu cửa sổ tập lệnh trước.
Hãy thử nó, Cảm ơn bạn!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Này, cảm ơn vì kịch bản này. Tôi đang gặp sự cố trong tập lệnh cho google trang tính: TypeError: Không thể gọi phương thức "pop" của null. (dòng 5, tệp "SumByColor"). Bạn có thể vui lòng giúp đỡ ?, cảm ơn
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào Laura,
Tập lệnh hoạt động tốt trong các trang tính của tôi trên google.
Nếu có vấn đề trong google sheet của bạn, tôi có thể chia sẻ google sheet của tôi với bạn.
Vui lòng cung cấp địa chỉ email của bạn.
Cảm ơn bạn!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, tôi có cùng một vấn đề, bạn đã sửa chữa nó chưa?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Vấn đề có thể là cấu hình khu vực của bạn, tập lệnh này cho rằng bạn đang phân tách các tham số bằng ',', nhưng có lẽ không phải trường hợp của bạn. Trong trường hợp của tôi, tôi sử dụng ';', vì vậy tôi đã sửa đổi tập lệnh, thay đổi ký tự trong các dòng

var countRangeAddress = activeformula.match (/\((.*)\;/). pop (). trim ();

var colorRefAddress = activeformula.match (/\;(.*)\)/). pop (). trim ();
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
cambio lahesia (,) por punto y kiss (;) pero aun así no me cuenta el color. tôi bán como resultado (0 cero).
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Kỷ nguyên isso mesmo. Chức năng perfeito aqui
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Có cách nào để đưa các số âm vào hỗn hợp và nhận được tổng đúng không? Của tôi tiếp tục thêm tất cả các số trong phạm vi, ngay cả khi số đó có dấu âm trên đó.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi nhận được "Hành động không được phép Dòng 0" Tôi đang làm gì sai?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi tiếp tục gặp lỗi "Hành động không được phép (Dòng 0)". Điều đó có nghĩa là gì?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
nó có tự động cập nhật khi dữ liệu mới được thêm vào không?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Không có vẻ như nó
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Bạn đã bao giờ tìm ra cách để cập nhật này tự động chưa?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Có ai tìm ra cách tự động cập nhật không?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi cần phải tìm cách làm cho nó tự động cập nhật! ... Có ai tìm thấy câu trả lời không?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Có ai tìm ra cách tự động cập nhật không?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Không, và tôi đang cố gắng tìm câu trả lời
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Thay thế các dòng này:
var countRangeAddress = activeformula.match (/\((.*)\;/). pop (). trim ();


var colorRefAddress = activeformula.match (/\;(.*)\)/). pop (). trim ();

Thông báo hơn, đã được thay đổi cho;
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào bạn,


Tôi có thể cần một số trợ giúp với một công thức tương tự với SUM của tổng số từ khóa phù hợp và phản ánh số lượng màu sắc không.


Tôi phải làm gì để đạt được nó xác định số lượng máy tính xách tay là một từ khóa nhưng nhận ra Có sẵn dựa trên màu sắc của các ô?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
tôi đang cố gắng làm một cái gì đó như thế này
= countColoredCells (H62: H, B2) countif ("MBA13R15")
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào bạn,
Công thức không hoạt động đối với tôi.
Nếu tôi thực hiện việc này trong bảng tính thì chỉ có lỗi xảy ra.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Cảm ơn, điều này làm việc tuyệt vời!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tập lệnh SumColoredCells hoạt động tuyệt vời. Bạn không biết có cách nào để hiển thị (trong một ô duy nhất) tổng của 2 màu không? Ví dụ: nếu tôi muốn tổng của cả Ô màu vàng và ô màu xanh lục hiển thị trong một ô duy nhất, điều đó có khả thi không? Tôi nghĩ công thức sẽ là:
= SUM (sumcoloredcells (A1: E11, A1)), (sumcoloredcells (A1: E11, A5)) .... Nhưng điều đó dẫn đến LỖI. Có ý kiến ​​gì không?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, Geoff,
Có thể không có công thức trực tiếp để giải quyết vấn đề của bạn, nhưng, bạn có thể tính tổng các ô màu riêng biệt trước tiên, sau đó, tính tổng hai kết quả được tính toán khi bạn cần.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Cảm ơn vi đa trả lơi. Tôi đã làm điều đó, nhưng rất thích hợp nhất công thức vào một ô duy nhất, vì tôi phải cập nhật các phạm vi hàng tuần. Dù sao, cảm ơn đã kiểm tra!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào bạn,


Tuy nhiên, hàm này hoạt động khi tôi cố gắng tính tổng 2 trường hợp của nó trong cùng một ô như trong (trong trường hợp của tôi):


= sumColoredCells (A103: AW103, C171) + sumColoredCells (A138: AW138, C171)


Nó cho tôi lỗi #Error - Không tìm thấy dải ô (dòng 6).


Nó không hoạt động ngay cả khi tôi sử dụng SUM () hoặc ADD (). Có ý kiến ​​gì không?


Cảm ơn trước.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Cảm ơn :*
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào bạn,
trên Google trang tính, có vẻ như không hoạt động, tôi đã gỡ lỗi và đối với cả hai quy trình, ở hàng 5 dường như là lỗi loại, thông báo là: không thể nhớ lại "phương thức pop2 của null. Nó có ý nghĩa không? Cảm ơn a rất nhiều!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Internet có rất nhiều trường hợp của giải pháp này và nó không hoạt động.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tập lệnh này từng hoạt động tốt, nhưng nó không còn hoạt động nữa. Có một sự thay đổi nào đã làm cho nó sụp đổ?
Không có bình luận nào được đăng ở đây
Tải thêm
Để lại ý kiến ​​của bạn
Đăng với tư cách khách
×
Đánh giá bài viết này:
0   Nhân vật
Các vị trí được đề xuất

Kết nối với chúng tôi

Bản quyền © 2009 - www.extendoffice.com. | Đã đăng ký Bản quyền. cung cấp bởi ExtendOffice. | BẢN ĐỒ CHI NHÁNH
Microsoft và logo Office là các nhãn hiệu hoặc nhãn hiệu đã đăng ký của Microsoft Corporation tại Hoa Kỳ và / hoặc các quốc gia khác.
Được bảo vệ bởi Sectigo SSL