Bài viết này minh họa cách tính tổng các ô theo màu chữ của chúng trong Excel. Nếu bạn có nhiều ô với các màu chữ khác nhau, thì bạn có thể cần đến điều này. Hình ảnh sau đây làm nổi bật mục đích của bài viết. Hãy nhìn nhanh qua để học cách làm điều đó.
Bạn có thể tải xuống tệp thực hành từ nút tải xuống bên dưới.
2 cách hiệu quả tính tổng theo màu chữ trong Excel
1. Tính tổng theo màu chữ với SUBTOTAL và Bộ lọc (Filter)
Giả sử bạn có tập dữ liệu với các màu chữ đa dạng trong một cột như sau. thì phương pháp này có thể là tốt nhất để tính tổng chúng theo màu chữ. Làm theo các bước dưới để áp dụng phương pháp này.
📌 Các bước
- Đầu tiên, nhập công thức sau vào ô C13. Số 9 trong công thức tham chiếu đến hàm SUM. Nó cho phép hàm SUBTOTAL trả về tổng của phạm vi ưu tiên.
=SUBTOTAL(9,C5:C11)
- Bây giờ chọn bất kỳ đâu trong tập dữ liệu. Sau đó nhấn CTRL+SHIFT+L để áp dụng Bộ lọc (Filter) lên tập dữ liệu. Sau đó, bạn sẽ thấy các nút lọc ở góc dưới bên phải của các ô tiêu đề như hình dưới.
- Bây giờ, lọc tập dữ liệu theo một màu cụ thể bằng nút lọc như hình dưới.
- Sau đó, bạn sẽ nhận được kết quả sau.
Đọc thêm: Cách tính tổng ô đã lọc trong Excel (5 cách phù hợp)
Bài đọc tương tự
- Cách tính tổng ô được chọn trong Excel (4 phương pháp dễ dàng)
- Tính tổng đến cuối cột trong Excel (8 phương pháp tiện lợi)
- Cách tính tổng cột trong Excel (7 phương pháp)
- [Đã sửa!] Công thức SUM Excel không hoạt động và trả về 0 (3 giải pháp)
- Cách tính tổng nhiều hàng trong Excel (4 cách nhanh)
2. Tính tổng theo màu chữ với Hàm do người dùng định nghĩa (UDF) trong Excel
Phương pháp trước sẽ không hoạt động nếu bạn có tập dữ liệu như sau. Bạn có thể dùng Hàm do người dùng định nghĩa (UDF) thay vào đó. Làm theo các bước dưới để làm điều đó.
📌 Các bước
- Đầu tiên, lưu sổ làm việc dưới dạng sổ làm việc hỗ trợ macro. Tiếp theo, nhấn ALT+F11 để mở cửa sổ VBA. Sau đó chọn Insert >> Module (Chèn >> Mô-đun) như hình dưới. Thao tác này sẽ tạo một mô-đun trống mới.
- Sau đó, sao chép mã dưới đây.
Function SUMBYFONTCOLOR(ref_color As Range, sum_range As Range) Dim cell_color, sum_color As Long Dim Cell As Range Application.Volatile sum_color = 0 cell_color = ref_color.Font.ColorIndex For Each Cell In sum_range If cell_color = Cell.Font.ColorIndex Then sum_cell = WorksheetFunction.Sum(Cell, sum_cell) End If Next Cell SUMBYFONTCOLOR = sum_cell End Function- Bây giờ, dán mã đã sao chép vào cửa sổ mô-đun như sau.
- Sau đó, quay lại trang tính của bạn. Sau đó ghi tên màu mong muốn vào ô I5:I9. Tiếp theo, thay đổi màu chữ của chúng cho phù hợp.
- Bây giờ, nhập công thức sau vào ô J5. Sau đó, dùng chấm tay Fill Handle để áp dụng công thức cho các ô dưới.
=SUMBYFONTCOLOR(I5,$B$4:$G$9)Cuối cùng, bạn sẽ nhận được kết quả sau.👇
Bây giờ, nếu bạn thay đổi màu chữ của bất kỳ ô nào trong phạm vi, kết quả sẽ không cập nhật. Bạn cần nhấp đúp vào một ô rồi nhấp ra ngoài để buộc tính toán lại. Thay vào đó, bạn có thể nhấn CTRL+ALT+F9 để có cùng kết quả.
- Bây giờ, sao chép mã sau nếu bạn không muốn làm cả hai việc trên.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("A:Z")) Is Nothing Then ActiveSheet.Calculate End If End Sub- Sau đó, quay lại cửa sổ VBA. Tiếp theo, nhấp đúp vào trang tính tên UDF. Thao tác này sẽ mở một cửa sổ trống. Sau đó, dán mã đã sao chép vào cửa sổ đó như hình sau.
Bây giờ bạn có thể thay đổi màu chữ và nhấp ra ngoài để cập nhật kết quả.
Giải thích mã VBA
Function SUMBYFONTCOLOR(ref_color As Range, sum_range As Range)
Hàm công khai này nhận hai đối số. Đối số ref_color sẽ lấy màu chữ từ ô tham chiếu. Chúng ta sẽ cộng giá trị ô dựa trên màu chữ của chúng trong phạm vi được tham chiếu bởi đối số sum_range.
Dim cell_color, sum_color As Long
Dim Cell As Range
Khai báo các biến cần thiết.
Application.Volatile
Dòng mã này buộc Excel tính toán lại mỗi khi người dùng thực hiện bất kỳ thay đổi nào.
sum_color = 0
Định nghĩa giá trị ban đầu cho biến.
cell_color = ref_color.Font.ColorIndex
Biến cell_color lưu màu chữ của ô được tham chiếu bởi đối số ref_color.
For Each Cell In sum_range
Vòng lặp For này sẽ thực thi các dòng mã tiếp theo qua từng ô trong phạm vi được tham chiếu bởi đối số sum_range.
If cell_color = Cell.Font.ColorIndex Then
Chỉ khi màu chữ của ô trong sum_range khớp với màu chữ của ô được tham chiếu bởi đối số ref_color thì các dòng mã tiếp theo sẽ thực thi.
sum_cell = WorksheetFunction.Sum(Cell, sum_cell)
Dòng mã này kích hoạt hàm SUM để cộng các giá trị ô có màu chữ khớp.
SUMBYFONTCOLOR = sum_cell
Dòng mã này lưu tổng để hàm có thể trả về giá trị này.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Thủ tục chủ đề riêng tư này chỉ hoạt động nếu có bất kỳ thay đổi nào xảy ra trong trang tính cụ thể đó.
If Not Intersect(Target, Range(“A:Z”)) Is Nothing Then
Dòng mã này đảm bảo dòng mã tiếp theo chỉ thực thi nếu người dùng thực hiện thay đổi trong phạm vi đã định (A:Z).
ActiveSheet.Calculate
Buộc tính tính toán lại tất cả công thức trong trang tính đang hoạt động để các thay đổi được cập nhật.
Đọc thêm: Cách tính tổng phạm vi ô trong hàng dùng Excel VBA (6 phương pháp dễ dàng)
Lưu ý quan trọng
- Đừng quên nhấp đúp vào một ô và nhấp ra ngoài để xem kết quả cập nhật nếu bạn thay đổi màu chữ. Thay vào đó, bạn cần nhấn CTRL+SHIFT+F9 cho việc đó.
- Bạn phải nhấp ra ngoài trong phạm vi đã định (A:Z) để cập nhật kết quả sau khi thay đổi màu chữ.
Kết luận
Bây giờ bạn đã biết 2 cách hiệu quả nhất để tính tổng ô theo màu chữ của chúng trong Excel. Vui lòng cho chúng tôi biết nếu bài viết này đã giúp bạn giải quyết vấn đề. Bạn cũng có thể dùng phần bình luận bên dưới cho thêm thắc mắc hoặc góp ý. Hãy ghé thăm blog ExcelDemy để học thêm về Excel. Hãy ở lại cùng chúng tôi và tiếp tục học tập.
Các bài viết liên quan
- Cách tính tổng ô có màu trong Excel (4 cách)
- Tính tổng ô trong Excel: Liên tục, Ngẫu nhiên, Có điều kiện, v.v.
- Cách tính tổng ô có màu trong Excel không dùng VBA (7 cách)
- Cộng số trong Excel (2 cách dễ dàng)
- Cách tính tổng nhiều hàng và cột trong Excel