03 Phương pháp tính tổng theo màu trong Google Sheets đơn giản

Trong Google Sheets, việc tính tổng theo màu giúp người dùng dễ dàng phân loại dữ liệu. Tuy nhiên, Google Sheets không hỗ trợ trực tiếp một hàm nào để tính tổng theo màu. Nhưng trong bài viết này, GCS Việt Nam sẽ bật mí cho các bạn 03 phương pháp tính tổng theo màu trong Google Sheets một cách gián tiếp vô cùng đơn giản, cùng tham khảo ngay nhé. 

Tính tổng theo màu trong Google Sheets để làm gì?

Khi làm việc với bảng dữ liệu có sử dụng màu sắc để phân loại, chẳng hạn như bảng tính lợi nhuận có chi phí đánh dấu màu vàng, khoản doanh thu sử dụng màu vàng. Để tính tổng các mức chi phí hay doanh thu đã được đánh màu, bạn có thể sử dụng các công thức tính tổng theo màu để thu thập dữ liệu trực quan nhất. 

Ngoài ra, việc tính tổng theo màu trong Google Sheets còn giúp bạn nhiều lợi ích thiết thực khác, như là:

• Phân loại và tổng hợp dữ liệu nhanh chóng theo màu sắc

Bạn có thể đánh dấu các chi phí theo nhóm màu, sau đó tính tổng theo từng nhóm chỉ với vài thao tác đơn giản. Điều này không chỉ giúp bạn tiết kiệm thời gian và còn giảm thiểu được các nhầm lẫn khi đọc dữ liệu thủ công. 

• Theo dõi trạng thái công việc theo từng tiến độ

Trong quá trình quản lý dự án hoặc nhân sự, bạn có thể sử dụng màu sắc để đánh dấu cho các phần công việc. Ví dụ như màu xanh: hoàn thành; màu vàng: đang làm; xám: chưa bắt đầu. Sau đó, bạn có thể tính tổng theo màu cho phép trực tiếp để theo dõi được trạng thái của các phần công việc dễ dàng. 

• Phân tích dữ liệu trực quan và tối ưu được quy trình

Google Sheets không hỗ trợ trực tiếp công thức để tính tổng theo màu. Nhưng bạn vẫn có thể sử dụng các giải pháp thay thế để tính tổng theo màu trong Google Sheets. Như là sử dụng Filter by Color + hàm SUBTOTAL, Tiện ích bổ sung Power Tools – Function by Color, Google Apps Script tùy biến để quét màu và tổng hợp giá trị

• Tính tổng được nhiều nhóm màu cùng lúc

Một số công cụ để tính tổng theo màu trong Google Sheets còn hỗ trợ tính cho nhiều màu cùng lúc trong một vùng dữ liệu. Ngoài ra, bạn có thể lựa chọn được các chế độ nhất định để nhận được kết quả từng màu hoặc tổng cả vùng mà không cần dùng nhiều công thức riêng biệt. 

03 Cách tính tổng theo màu trong Google Sheets 

Để giải quyết các bài toán tính tổng theo màu trong Google Sheets, bạn có thể tham khảo 03 phương pháp dưới đây. Mỗi phương pháp đều sẽ có một số những ưu điểm và nhược điểm nhất định. Tuy nhiên, tất cả các phương pháp này để mang lại giải pháp tối ưu nhất cho công việc của mình. 

Phương pháp 1 – Sử dụng hàm FILTER + SUBTOTAL

Đây là phương pháp cơ bản nhất trong việc tính tổng theo màu trong Google Sheets. Công thức kết hợp giữa hàm FILTER và SUBTOTAL sẽ hoạt động dựa trên sự kết hợp thông minh giữa tính năng Lọc theo màu Filter by color và tính toán các ô đã lọc với hàm SUBTOTAL. 

*Ví dụ: Bạn đang quản lý dữ liệu bán hàng với các cột “Sản phẩm”, “Số lượng bán” và “Trạng thái đơn hàng” được tô màu để dễ nhận biết: màu xanh lá cây cho đơn hàng “Hoàn thành”, màu vàng cho đơn hàng “Đang chờ” và màu đỏ cho đơn hàng “Đã hủy”. Bạn muốn tính tổng số lượng bán ra cho từng trạng thái.

Các bước thực hiện tính tổng số lượng các sản phẩm được bạn theo từng trạng thái như sau:

– Bước 1: Tạo bộ lọc dữ liệu 

Đầu tiên, bạn cần chọn toàn bộ phạm vi dữ liệu của bảng tính. Sau đó trên thanh công cụ của Google Sheets, bạn nhấn vào mục Dữ liệu (Data) >> chọn Tạo bộ lọc (Create a filter). Lúc này, bạn sẽ thấy biểu tượng hình phễu nhỏ xuất hiện ở tiêu đề của mỗi cột.

– Bước 2: Lọc dữ liệu theo màu sắc

Tiếp theo, bạn nhấp vào biểu tượng phễu ở tiêu đề cột chứa màu sắc muốn tính tổng. Trong menu thả xuống, bạn di chuột đến phần Lọc theo màu (Filter by color). 

Sau đó, bạn cần chọn vào mục Tô màu >> chọn màu cụ thể (ví dụ: màu xanh lá cây).

Lúc này, Google Sheets sẽ tự động ẩn tất cả các hàng không có màu xanh lá cây.

– Bước 3: Sử dụng hàm SUBTOTAL để tính tổng

Sau khi dữ liệu đã được lọc, bạn di chuyển đến một ô trống và nhập công thức sau

=SUBTOTAL(9; B2:B7)

Trong đó:

• 9: Đây là mã số hàm cho phép tính tổng (SUM) trong hàm SUBTOTAL. Hàm SUBTOTAL có nhiều mã số khác nhau cho các phép tính như trung bình (1), đếm (2), v.v.
• B2:B7: Đây là phạm vi chứa các giá trị số mà bạn muốn tính tổng.

Cuối cùng, bạn chỉ cần bấm Enter và hàm SUBTOTAL sẽ tự động tính tổng chỉ các giá trị đang hiển thị (tức là các ô có màu xanh lá cây sau khi lọc).

Để tính tổng cho các màu vàng hoặc màu đỏ, bạn chỉ cần lặp lại các thao tác như trên. 

Phương pháp 2 – Sử dụng Tiện ích bổ sung (Power Tools)

Nếu bạn cần một giải pháp tự động hơn, có thể sử dụng Tiện ích bổ sung (Add-ons). Khi sử dụng các tiện ích bổ sung để tính tổng theo màu trong Google Sheets, bạn có thể thực hiện được các tác vụ phức tạp hơn. 

Một trong những tiện ích phổ biến để tính tổng theo màu trong Google Sheets được nhiều người lựa chọn nhất. Đó chính là tiện  ích bổ sung Power Tools với tính năng “Function by Color”.

*Ví dụ: Bạn muốn tính tổng số lượng bán ra cho các đơn hàng “Hoàn thành” (màu xanh lá cây), “Đang chờ” (màu vàng) và “Đã hủy” (màu đỏ) một cách tự động hơn.

Các bước thực hiện tính tổng theo màu trong Google Sheets bằng cách sử dụng tiện ích bổ sung như sau:

– Bước 1: Cài đặt Tiện ích bổ sung Power Tools

Đầu tiên, bạn mở bảng tính Google Sheets trên trình duyệt web của minh.

Sau đó, bạn đến thanh công cụ menu và chọn Tiện ích (Extensions) >> chọn Tiện ích bổ sung (Add-ons) >> chọn Tải tiện ích bổ sung (Get add-ons).

Trong phần Google Workspace Marketplace, bạn cần tìm kiếm Power Tools hoặc Function by Color.

Tiếp theo, bạn chỉ cần bấm vào phần Cài đặt (Install) để tải tiện ích và cấp quyền truy cập vào tài khoản Google Sheets. 

– Bước 2: Chuẩn bị ô tham chiếu màu

Để việc tính toán trở nên tiện lợi, bạn có thể tạo vùng nhỏ trong bảng tính chứa các ô màu sắc. Ví dụ ở ô B2 tô màu xanh lá cây, B3 tô màu vàng, và B4 tô màu đỏ,….. như trong bảng biểu mẫu. 

– Bước 3: Sử dụng hàm để tính tổng 

Sau khi cài đặt thành công xong tiện ích bổ sung, bạn vào lại Tiện ích >> chọn Power Tools >> chọn Start.

Sau đó phía bên phải giao diện màn hình xuất menu, bạn chọn vào Process >> chọn Function by color.

Lúc này, bạn cần nhập các thông tin bao gồm:

• Select range (Phạm vi lựa chọn): B1:B7
• Select color (Chọn màu sắc)
• Paste results to (Kết quả)

Sau đó, bạn chọn vào Insert function

– Bước 4: Kết quả hiển thị

Cuối cùng, kết quả tính tổng theo màu trong Google Sheets sẽ hiển thị như hình.

Phương pháp 3 – Sử dụng hàm App Script

Đây là phương pháp linh hoạt và cho phép bạn tạo ra các hàm tùy chỉnh chính xác. Khi sử dụng phương pháp này để tính tổng theo màu trong Google Sheets bạn cần biết một chút về ngôn ngữ mà Google Apps Script sử dụng. Tuy nhiên, các đoạn mã đều có sẵn và bạn chỉ cần sao chép, dán rồi chỉnh sửa lại.

*Ví dụ: Bạn muốn một giải pháp hoàn toàn tùy biến để tính tổng số lượng bán ra cho các trạng thái đơn hàng (màu xanh lá cây, vàng, đỏ).

Các bước thực hiện tính tổng theo màu trong Google Sheets bằng cách sử dụng hàm App Script như sau:

– Bước 1: Đầu tiên, bạn thực hiện mở Google Sheets >> chọn vào Tiện ích mở rộng (Extensions) >> chọn vào Apps Script. Một cửa sổ trình chỉnh sửa code mới sẽ mở ra trong trình duyệt của bạn.

– Bước 2: Trong trình chỉnh sửa Apps Script, bạn sẽ thấy một tệp mặc định có tên Mã.gs. Xóa bỏ mọi thứ có sẵn trong đó và dán đoạn mã sau vào:

function SUMCOLOREDCELLS(data_range, color_reference) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getActiveSheet();

  var range = sheet.getRange(data_range);

  var color_cell = sheet.getRange(color_reference);

  var fill_color = color_cell.getBackground();

  var values = range.getValues();

  var total = 0;

  for (var i = 0; i < values.length; i++) {

    for (var j = 0; j < values[i].length; j++) {

      var cell = range.getCell(i + 1, j + 1);

      if (cell.getBackground() == fill_color) {

        if (typeof values[i][j] == ‘number’) {

          total += values[i][j];

        }

      }

    }

  }

  return total;

}

– Bước 3: Tiếp theo, bạn chỉ cần thực hiện nhấp vào biểu tượng lưu 

– Bước 4: Cuối cùng, bạn chỉ cần thực hiện tính tổng các ô màu trong Google Sheets theo công thức. Bằng cách nhập công thức

=SUMCOLOREDCELLS(“A1:C7”; “E2”)

Trong đó: 

• “A1:C7” : bằng phạm vi dữ liệu thực tế của bạn 
• “E2” : bằng ô chứa màu bạn muốn tính tổng.

Lỗi thường gặp khi tính tổng theo màu trong Google Sheets

Khi thực hiện tính tổng theo màu trong Google Sheets, bạn có thể sẽ gặp một số lỗi nhỏ. Tuy nhiên, các lỗi này đều có thể khắc phục được nếu bạn hiểu rõ được nguyên nhân. Cụ thể như sau:

Lỗi #ERROR! hoặc #VALUE! với Apps Script

Các lỗi này thường xảy ra khi bạn thực hiện sai cú pháp hàm. Hoặc bạn tham chiếu sai phạm vi dữ liệu làm công thức bị lỗi. Ngoài ra, trong quá trình thực hiện tính tổng theo màu trong Google Sheets bằng Apps Script, bạn nên chú ý để ô tham chiếu màu. Bạn cần đảm bảo ô bạn chọn là một ô hợp lệ và không bị xóa. 

Một điều chú ý nữa là về lỗi trong Script, nếu bạn tự viết có thể lỗi cú pháp hoặc logic trong code. Từ đó, hàm sẽ không hoạt động nên bạn cần kiểm tra kỹ ở cửa sổ Apps Script xe có thông báo lỗi hiển thị không. 

Không tự động cập nhật kết quả khi màu sắc thay đổi

Hàm tùy chỉnh trong Google Sheets không tự động kích hoạt lại khi màu nền thay đổi thủ công. Google Sheets chỉ kích hoạt lại các hàm khi giá trị trong ô mà hàm tham chiếu thay đổi.

Do đó, bạn có thể buộc hàm cập nhập bằng cách nhấp vào ô chứa công thức và chỉnh sửa nhẹ thủ công. Hoặc đối với Apps Script, bạn có thể thiết lập một “trigger” để hàm tự động chạy lại khi có sự kiện thay đổi trên bảng tính.

Không tính được màu của “Định dạng có điều kiện” (Conditional Formatting)

Các hàm tùy chỉnh được cung cấp ở trên thường chỉ lấy màu nền thực tế của ô. Các hàm này sẽ không thể đọc được màu sắc ô khi áp dụng Định dạng có điều kiện. 

Khi gặp trường hợp này, bạn chỉ cần khắc phục bằng cách thay vì tính tổng theo màu, bạn tính tổng dựa trên chính điều kiện đã tạo ra màu đó. Ví dụ: Nếu bạn tô màu đỏ cho các số lớn hơn 100, thay vì SUM_BY_COLOR, bạn sẽ dùng SUMIF(phạm_vi, “>100”).

Lưu ý quan trọng khi tính tổng theo màu trong Google Sheets

Tính tổng theo màu trong Google Sheets không quá khó. Nhưng trong quá trình thực hiện, bạn cũng cần phải lưu ý một số vấn đề quan trọng như sau:

• Đầu tiên, bạn cần phân biệt màu thủ công và màu định dạng có điều kiện

Hầu hết, các phương pháp Apps Script và tiện ích bổ sung chỉ áp dụng cho màu thủ công. Chúng sẽ không đọc được các màu sắc được áp dụng Định dạng có điều kiện. Nếu màu sắc trong ô tính toán của bạn được tạo bởi Định dạng có điều kiện. Bạn hãy sử dụng chính điều kiện đó để tính tổng (ví dụ dùng hàm SUMIF).

• Thứ hai, bạn cần quan tâm đến vấn đề cập nhật tự động của Script

Các hàm tùy chỉnh trong Apps Script không tự động kích hoạt lại khi chỉ có màu sắc của ô thay đổi. Nếu bạn thay đổi màu trong một ô thủ công thì kết quả tổng theo màu sẽ không thay đổi. Giải pháp tốt nhất là bạn phải sửa đổi công thức thủ công hoặc nhấn Enter lại ô công thức chứa hàm tùy chỉnh.

• Thứ ba, bạn cần tạo mã Script dễ đọc

Khi sử dụng App Script để tính tổng theo màu trong Google Sheets, bạn cần chú thích rõ ràng và tạo mã dễ hiểu. Đặc biệt, bạn cần thống nhất hệ thống mã màu để tránh nhầm lẫn khi thực hiện chạy công thức.

>>> Xem thêm: Cách tính tổng trong Google Sheets với hàm SUM

Lời kết

Tóm lại, bạn không thể tính tổng theo màu trong Google Sheets trực tiếp, nhưng vẫn có thể linh hoạt áp dụng với 03 phương pháp được giới thiệu bên trên. Bạn có thể lựa chọn phương pháp phù hợp để thực hiện tính tổng sao cho tối ưu nhất trong bảng tính của mình.

Ngoài ra, để hỗ trợ tốt hơn cho công việc, người dùng nên sở hữu ứng dụng Google Sheet bản quyền bằng cách mua gói Google Workspace chính hãng.

Và nếu trong quá trình thực hiện, bạn có bất kỳ thắc mắc hay khó khăn gì thì đừng ngần ngại liên hệ với GCS Việt Nam. Chúng tôi luôn sẵn sàng hỗ trợ và giải đáp cho các bạn mọi vấn đề về kỹ thuật nhanh chóng và hiệu quả nhất.

• Fanpage: GCS – Google Cloud Solutions 

• Hotline: 024.9999.7777