Hướng Dẫn Tính Tổng Theo Màu Sắc Trong Excel: 3 Phương Pháp Từ Cơ Bản Đến Nâng Cao

Table of Contents

Thời gian đọc ước tính: 12 phút

Những điểm chính:

Hạn chế của Excel: Excel mặc định không hỗ trợ hàm tính tổng trực tiếp dựa trên màu nền của ô (background color).
Phương pháp Filter & SUBTOTAL: Giải pháp thủ công đơn giản nhất, sử dụng chức năng lọc và hàm SUBTOTAL để tính toán các ô hiển thị.
Phương pháp VBA (Macro): Tạo hàm tự định nghĩa (User Defined Function) để tự động hóa quy trình tính tổng màu sắc một cách linh hoạt.
Phương pháp GET.CELL: Sử dụng hàm macro cũ (Excel 4.0) kết hợp với Name Manager để trích xuất mã màu và tính tổng qua SUMIF.

Excel là một công cụ mạnh mẽ để xử lý dữ liệu số, tuy nhiên, phần mềm này lại thiếu hụt một tính năng mà rất nhiều người dùng tài chính và kế toán mong đợi: khả năng tính tổng giá trị các ô dựa trên màu sắc của chúng.

Trong thực tế quản lý dự án hoặc theo dõi công nợ, chúng ta thường sử dụng màu sắc để đánh dấu trạng thái (ví dụ: màu vàng cho “chờ xử lý”, màu cam cho “quá hạn”). Việc không thể cộng tổng trực tiếp các nhóm màu này gây ra nhiều trở ngại đáng kể.

Ví dụ, bạn có một tập dữ liệu như hình dưới đây và muốn tính tổng giá trị của tất cả các ô màu cam và màu vàng.

Bảng dữ liệu Excel chứa các ô được tô màu vàng và cam cần tính tổng

Mặc dù không có hàm tích hợp sẵn, chúng ta hoàn toàn có thể giải quyết vấn đề này bằng ba kỹ thuật chuyên sâu dưới đây.

Tính Tổng Theo Màu Sử Dụng Filter Và Hàm SUBTOTAL

Đây là phương pháp tiếp cận đơn giản nhất, không yêu cầu kiến thức về lập trình hay các thiết lập phức tạp. Kỹ thuật này tận dụng khả năng của hàm SUBTOTAL trong việc chỉ tính toán các giá trị trên những ô đang hiển thị (visible cells) sau khi dữ liệu đã được lọc.

Giả sử chúng ta có danh sách nhân viên và doanh số bán hàng tương ứng.

Danh sách nhân viên và doanh số với các ô được tô màu khác nhau

Mục tiêu là tính tổng doanh số của các ô được tô màu vàng và màu cam. Quy trình thực hiện dựa trên nguyên lý: Lọc màu cần tính -> Hàm SUBTOTAL tự động cập nhật kết quả.

Bước 1: Thiết lập hàm SUBTOTAL

Tại ô B17 (hoặc bất kỳ ô nào nằm dưới cùng của cột dữ liệu), bạn nhập công thức sau:

=SUBTOTAL(9, B2:B15)

Công thức Subtotal được nhập vào ô để tính tổng doanh số

Phân tích tham số hàm:

Số 9: Đây là tham số định danh cho phép tính TỔNG (SUM). Điều đặc biệt của hàm SUBTOTAL với tham số 9 là nó sẽ bao gồm cả các ô bị ẩn thủ công nhưng sẽ loại bỏ các ô bị ẩn do bộ lọc (Filter).
B2:B15: Đây là vùng dữ liệu chứa các giá trị cần tính toán.

Tại sao không dùng hàm SUM?
Hàm SUM sẽ luôn cộng tất cả các giá trị trong vùng chọn, bất kể chúng đang hiển thị hay bị ẩn. Ngược lại, SUBTOTAL linh hoạt hơn và phản ứng trực tiếp với hành động lọc dữ liệu của người dùng.

Bước 2: Áp dụng bộ lọc theo màu

Sau khi đã có công thức, hãy thực hiện các bước sau để lấy kết quả theo màu:

Chọn bất kỳ ô nào có chứa dữ liệu trong bảng.
Nhấn vào tab Data trên thanh công cụ (Ribbon).

Vị trí tab Data trên thanh công cụ Excel

Trong nhóm “Sort and Filter”, chọn biểu tượng Filter (hình chiếc phễu). Bạn sẽ thấy các mũi tên lọc xuất hiện tại tiêu đề các cột.

Nhấn vào biểu tượng Filter để kích hoạt bộ lọc cho bảng dữ liệu

Tại ô tiêu đề “Sales”, nhấn vào mũi tên lọc.

Biểu tượng mũi tên lọc xuất hiện trên tiêu đề cột Sales

Di chuyển chuột đến tùy chọn Filter by Color.
Chọn màu sắc cụ thể mà bạn muốn tính tổng (ví dụ: màu vàng).

Menu chọn màu sắc để lọc dữ liệu theo màu vàng hoặc cam

Ngay khi bạn chọn màu, Excel sẽ ẩn tất cả các hàng không chứa màu đó. Kết quả của hàm SUBTOTAL ở phía dưới sẽ thay đổi ngay lập tức, chỉ hiển thị tổng của các ô màu vàng đang hiện diện trên màn hình.

Kết quả hàm SUBTOTAL thay đổi chỉ tính tổng các ô màu được lọc

Nếu bạn đổi bộ lọc sang màu cam, kết quả sẽ tự động cập nhật tương ứng.

Mẹo chuyên nghiệp: Sử dụng phím tắt Control + Shift + L (trên Windows) hoặc Command + Shift + L (trên Mac) để bật/tắt nhanh chế độ lọc dữ liệu.

Tính Tổng Theo Màu Bằng VBA (Hàm Tự Tạo)

Phương pháp Filter tuy đơn giản nhưng có nhược điểm là không thể hiển thị tổng của nhiều màu cùng một lúc (bạn phải lọc từng màu). Để khắc phục điều này và tạo ra một giải pháp chuyên nghiệp, tự động hóa cao hơn, chúng ta sử dụng VBA (Visual Basic for Applications).

Giải pháp này cho phép bạn tạo ra một hàm tùy chỉnh (ví dụ: =SumByColor()) hoạt động giống hệt các hàm có sẵn như SUM hay AVERAGE.

Mã VBA Cần Thiết

Dưới đây là đoạn mã VBA được viết để thực hiện nhiệm vụ này:

Function SumByColor(SumRange As Range, SumColor As Range)
    Dim SumColorValue As Integer
    Dim TotalSum As Long
    Dim rCell As Range

    'Lấy mã màu từ ô mẫu
    SumColorValue = SumColor.Interior.ColorIndex

    'Duyệt qua từng ô trong vùng dữ liệu
    For Each rCell In SumRange
        If rCell.Interior.ColorIndex = SumColorValue Then
            TotalSum = TotalSum + rCell.Value
        End If
    Next rCell

    SumByColor = TotalSum
End Function

Quy trình cài đặt mã vào Excel

Để sử dụng đoạn mã trên, bạn cần thao tác với trình soạn thảo VBA (VB Editor). Hãy làm theo các bước chi tiết sau:

Mở tab Developer trên thanh Ribbon. (Nếu chưa thấy tab này, bạn cần vào File > Options > Customize Ribbon và tích chọn Developer).
Nhấn vào nút Visual Basic ở góc trái ngoài cùng.

Nhấn vào nút Visual Basic để mở trình soạn thảo mã

Trong cửa sổ Microsoft Visual Basic for Applications, chọn menu Insert.
Chọn Module. Một cửa sổ soạn thảo trắng sẽ xuất hiện. Nếu bạn không thấy khung Project Explorer bên trái, hãy vào View > Project Explorer.

Chọn Insert Module để tạo không gian chứa mã VBA mới

Sao chép đoạn mã VBA ở trên và dán vào cửa sổ Module vừa tạo.

Dán đoạn mã VBA vào cửa sổ Module

Đóng cửa sổ VB Editor và quay trở lại bảng tính Excel.

Cách sử dụng hàm SumByColor

Sau khi dán mã, hàm SumByColor đã sẵn sàng hoạt động trong file Excel hiện tại.

Để hàm hoạt động chính xác, bạn cần thiết lập các ô tham chiếu chứa màu mẫu (ví dụ: ô D2 chứa màu vàng, ô D3 chứa màu cam) để làm điều kiện tính toán.

Cú pháp sử dụng như sau:

=SumByColor($B$2:$B$15, D2)

Sử dụng hàm SumByColor trong bảng tính để tính tổng theo màu mẫu

Giải thích tham số:

$B$2:$B$15: Vùng dữ liệu cần tính tổng (lưu ý nên cố định vùng này bằng phím F4 để khi copy công thức không bị lệch).
D2: Ô chứa màu mẫu mà bạn muốn tính tổng các ô tương ứng trong vùng dữ liệu.

Lưu ý quan trọng:

Hàm này không tự động cập nhật ngay lập tức nếu bạn chỉ thay đổi màu sắc của một ô (do việc đổi màu không kích hoạt sự kiện tính toán lại của Excel). Nếu kết quả không đổi, hãy nhấn phím F9 để buộc Excel tính toán lại.
Bạn bắt buộc phải lưu file dưới định dạng Excel Macro-Enabled Workbook (.xlsm) để giữ lại mã VBA.

Tính Tổng Theo Màu Sử Dụng Hàm GET.CELL

Nếu bạn e ngại việc sử dụng VBA nhưng vẫn muốn một giải pháp linh hoạt hơn bộ lọc Filter, hàm GET.CELL là một lựa chọn thú vị. Đây là một hàm Macro 4.0 cũ, được giữ lại để tương thích ngược.

Cơ chế hoạt động: Chúng ta sẽ tạo một “Cột phụ” để trích xuất mã số màu (Color Index) của từng ô, sau đó dùng hàm SUMIF để tính tổng dựa trên các mã số này.

Bước 1: Tạo Named Range với GET.CELL

Vì GET.CELL không thể dùng trực tiếp trong ô tính, chúng ta phải gọi nó qua Defined Names (Name Manager).

Vào tab Formulas trên thanh Ribbon.
Chọn Name Manager.

Nhấn vào nút Name Manager trong tab Formulas

Trong hộp thoại, nhấn nút New.

Nhấn nút New để tạo một tên định nghĩa mới

Tại mục Name, đặt tên là SumColor.
Tại mục Refers to, nhập công thức chính xác sau:
```
=GET.CELL(38, $B2)
```

Nhập tên SumColor và công thức GET.CELL vào hộp thoạiNhập công thức GET.CELL với tham số 38 để lấy mã màu

Nhấn OK và đóng cửa sổ.

Giải thích: Tham số 38 yêu cầu hàm trả về mã màu nền (Background Color Index) của ô tham chiếu. $B2 là tham chiếu tương đối theo dòng, đảm bảo khi copy công thức xuống dưới, nó sẽ lấy màu của ô B3, B4 tương ứng.

Bước 2: Trích xuất mã màu ra cột phụ

Tạo một cột mới bên cạnh dữ liệu (ví dụ cột C) để hiển thị mã màu.

Tại ô C2, nhập công thức: =SumColor
Sao chép công thức này xuống toàn bộ cột C tương ứng với dữ liệu.

Cột phụ hiển thị các mã số màu sắc tương ứng

Lúc này, bạn sẽ thấy các con số đại diện cho màu sắc (ví dụ: màu vàng có thể là 6, màu cam là 44…).

Bước 3: Dùng SUMIF để tính tổng

Khi đã có mã màu dạng số, việc tính tổng trở nên rất đơn giản với hàm SUMIF.

Tại ô E2 và E3, tô màu nền tương ứng (Vàng và Cam) để làm mẫu, hoặc bạn có thể nhập trực tiếp mã số màu nếu biết. Tuy nhiên, cách tốt nhất là dùng lại Name SumColor để lấy mã màu của ô mẫu này.
Nhập công thức sau vào ô kết quả (F2):

=SUMIF($C$2:$C$15, SumColor, $B$2:$B$15)

Lưu ý: Trong công thức trên, tham số SumColor ở giữa đóng vai trò lấy mã màu của ô F2 (ô đang chứa công thức) hoặc ô E2 bên cạnh tùy theo cách bạn tham chiếu.

Cách đơn giản hơn, nếu bạn đã biết mã màu (ví dụ từ cột C, màu vàng là 6):

=SUMIF(C2:C15, 6, B2:B15)

Nhưng để chuyên nghiệp, hãy làm như sau:
Tại ô chứa kết quả tổng màu vàng (ví dụ F2), bên cạnh ô mẫu màu vàng (E2), công thức logic nhất là so sánh Cột Mã Màu (C) với Mã Màu Của Ô Mẫu (E2).

Tuy nhiên, GET.CELL trong Name Manager tham chiếu đến ô bên trái hoặc cố định. Để chính xác, bạn hãy dùng công thức:
=SUMIF(C2:C15, C2, B2:B15) (giả sử C2 là mã màu vàng).

Trong ví dụ của bài viết gốc, tác giả dùng logic so sánh trực tiếp với Name Range:

Sử dụng hàm SUMIF kết hợp với cột mã màu để tính tổng

Kết quả trả về sẽ là tổng chính xác của các ô có màu tương ứng.

Câu Hỏi Thường Gặp (FAQ)

1. Tại sao hàm SumByColor của tôi hiển thị lỗi #NAME?

Lỗi này xuất hiện khi Excel không nhận diện được hàm. Nguyên nhân thường do bạn chưa bật Macro (Enable Macros) khi mở file, hoặc bạn lưu file dưới dạng .xlsx (không chứa macro) thay vì .xlsm. Hãy chắc chắn rằng mã VBA đã được dán vào đúng Module chứ không phải Sheet hay ThisWorkbook.

2. Dùng Conditional Formatting (Định dạng có điều kiện) có tính tổng được không?

Các phương pháp trên tính tổng dựa trên màu nền được tô thủ công (Manual Fill). Nếu màu sắc của bạn được tạo ra bởi Conditional Formatting, hàm VBA Interior.ColorIndex và GET.CELL thường sẽ không trả về đúng màu hiển thị mà trả về màu gốc của ô. Với Conditional Formatting, bạn nên tính tổng dựa trên chính điều kiện logic đã tạo ra màu đó (ví dụ: SUMIF các giá trị > 1000) thay vì dựa vào màu.

3. Làm thế nào để cập nhật kết quả khi tôi đổi màu ô?

Excel không coi việc “đổi màu” là một sự thay đổi dữ liệu để kích hoạt tính toán lại.

Với VBA: Nhấn phím F9.
Với Filter: Phải bỏ lọc và lọc lại (Re-apply).
Với GET.CELL: Vào ô công thức, nhấn F2 rồi Enter, hoặc nhấn F9.

4. Phương pháp nào an toàn và ổn định nhất?

Phương pháp Filter + SUBTOTAL là an toàn nhất vì không dùng Macro, không lo vấn đề bảo mật hay lỗi phiên bản. Phương pháp VBA phù hợp cho người dùng cần xử lý dữ liệu lớn và thường xuyên.

Nguồn Tham Khảo

Để tìm hiểu sâu hơn về các hàm và kỹ thuật được đề cập trong bài, bạn có thể tham khảo các tài liệu kỹ thuật sau:

Microsoft Support: Hướng dẫn sử dụng hàm SUBTOTAL trong Excel. Xem tại đây
Microsoft Developer Network: Tài liệu về thuộc tính Range.Interior.ColorIndex trong VBA. Xem tại đây
Excel Jet: Cách sử dụng hàm GET.CELL và các hàm Macro 4.0 cũ.
MrExcel: Các thảo luận chuyên sâu về kỹ thuật Sum by Color.

Bằng cách nắm vững ba phương pháp này, bạn có thể linh hoạt xử lý các bảng dữ liệu được mã hóa màu sắc, giúp quy trình báo cáo và phân tích trở nên chính xác và chuyên nghiệp hơn.