Bài viết này minh họa cách tính tổng các ô theo màu phông chữ của chúng trong excel. Nếu bạn có nhiều ô với các màu phông chữ khác nhau, thì bạn có thể cần điều này. Hình ảnh sau đây nêu bật mục đích của bài báo này. Hãy xem nhanh nó để tìm hiểu cách thực hiện điều đó.
Bạn có thể tải xuống sách bài 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 phông chữ trong Excel
1. Tính tổng theo Màu phông chữ với SUBTOTAL và Bộ lọc
Giả sử bạn có một tập dữ liệu với các phông chữ màu khác nhau trong một cột duy nhất như sau. Sau đó, phương pháp này có thể là tốt nhất để tổng hợp chúng theo màu chữ của chúng. Làm theo các bước bên dưới để áp dụng phương pháp này.
📌 Các bước
- Đầu tiên, hãy nhập công thức sau vào ô C13 . 9 trong công thức đề cập đến hàm SUM . Nó cho phép chức năng SUBTOTAL để trả về tổng của dải ô ưa thích.
=SUBTOTAL(9,C5:C11)
- Bây giờ hãy chọn bất kỳ vị trí nào trong tập dữ liệu. Sau đó nhấn CTRL + SHIFT + L để áp dụng Bộ lọc trên tập dữ liệu. Sau đó, bạn sẽ thấy các nút bộ lọc ở góc dưới bên phải của các ô tiêu đề như trong hình bên dưới.
- Bây giờ, hãy lọc tập dữ liệu theo một màu cụ thể bằng cách sử dụng nút bộ lọc như hình bên dưới.
- Sau đó, bạn sẽ nhận được kết quả sau.
Đọc thêm: Cách tính tổng các ô đã lọc trong Excel (5 cách thích hợp)
Bài đọc tương tự
- Cách tính tổng cá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 dụng)
- Cách tính tổng các cột trong Excel (7 phương pháp)
- [Đã sửa!] Công thức SUM trong 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 phông chữ với Hàm do Người dùng Xác định (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ể sử dụng Chức năng do người dùng xác định (UDF) thay vì. Làm theo các bước bên dưới để thực hiện điều đó.
📌 Các bước
- Trước 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 Chèn>> Mô-đun như trong hình bên dưới. Thao tác này sẽ tạo một mô-đun trống mới.
- Sau đó, sao chép mã được cung cấp bên dưới.
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ờ, hãy dán mã đã sao chép vào cửa sổ mô-đun như sau.
- Sau đó, truy cập trang tính của bạn. Sau đó, viết tên màu mong muốn vào các ô I5:I9 . Tiếp theo, hãy thay đổi màu chữ của chúng cho phù hợp.
- Bây giờ, hãy nhập công thức sau vào ô J5 . Sau đó, sử dụng biểu tượng Fill Handle để áp dụng công thức cho các ô bên 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 phông 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 ô và sau đó nhấp đi để buộc tính toán lại. Ngoài ra, bạn có thể nhấn CTRL + ALT + F9 để có được kết quả tương tự.
- Bây giờ, hãy sao chép đoạn mã sau nếu bạn không muố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 có tên UDF . Thao tác này sẽ mở ra một cửa sổ trống. Sau đó, dán mã đã sao chép vào cửa sổ đó như trong hình sau.
Bây giờ, bạn có thể thay đổi màu phông chữ và nhấp vào để cập nhật kết quả.
Giải thích về mã VBA
Hàm SUMBYFONTCOLOR (ref_color As Range, sum_range As Range)
Hàm công khai này sẽ có hai đối số. ref_color đối số sẽ lấy màu phông chữ từ ô tham chiếu. Chúng tôi sẽ tính tổng các giá trị ô dựa trên màu phông chữ của chúng trong phạm vi được tham chiếu bởi sum_range đối số.
Dim cell_color, sum_color As Long
Làm mờ ô theo phạm vi
Khai báo các biến cần thiết.
Ứng dụng. Đa năng
Dòng mã này buộc excel phải tính toán lại bất cứ khi nào người dùng thực hiện bất kỳ thay đổi nào.
sum_color =0
Xác định giá trị ban đầu cho biến.
cell_color =ref_color.Font.ColorIndex
cell_color biến lưu trữ màu phông chữ của ô được tham chiếu bởi ref_color đối số.
Đối với mỗi ô trong sum_range
Đối với vòng lặp 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 sum_range đối số.
Nếu cell_color =Cell.Font.ColorIndex Thì
Chỉ khi màu phông chữ của ô trong sum_range khớp với màu phông chữ của ô được tham chiếu bởi ref_color thì các dòng mã tiếp theo sẽ thực thi.
sum_cell =WorksheetFunction.Sum (Ô, sum_cell)
Dòng mã này kích hoạt hàm SUM để thêm các giá trị ô có màu phông chữ phù hợp.
SUMBYFONTCOLOR =sum_cell
Dòng mã này lưu trữ 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ể.
Nếu không giao nhau (Mục tiêu, Phạm vi (“A:Z”)) thì không có gì
Dòng mã này đảm bảo rằng dòng mã tiếp theo sẽ chỉ thực thi nếu người dùng thực hiện bất kỳ thay đổi nào trong phạm vi đã xác định ( A:Z ).
ActiveSheet.Calculate
Điều này buộc phải tính toán lại tất cả cá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 bằng Excel VBA (6 phương pháp dễ dàng)
Những điều cần nhớ
- Đừng quên nhấp đúp vào một ô và nhấp vào ô để xem kết quả cập nhật nếu bạn thay đổi màu phông chữ. Ngoài ra, bạn cần nhấn CTRL + SHIFT + F9 vì điều đó.
- Bạn phải nhấp đi trong phạm vi đã xác định ( A:Z ) để cập nhật kết quả sau khi thay đổi màu phông chữ.
Kết luận
Bây giờ bạn đã biết 2 cách hiệu quả nhất để tính tổng các ô 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 với vấn đề của bạn. Bạn cũng có thể sử dụng phần bình luận bên dưới cho các truy vấn hoặc đề xuất thêm. Hãy truy cập ExcelDemy của chúng tôi blog để tìm hiểu thêm về excel. Hãy ở lại với chúng tôi và tiếp tục học hỏi.
Các bài viết liên quan
- Cách tính tổng các ô tô màu trong Excel (4 cách)
- Tổng số ô trong Excel:Liên tục, Ngẫu nhiên, Có Tiêu chí, v.v.
- Cách tính tổng các ô có màu trong Excel mà không cần VBA (7 cách)
- Thêm số trong Excel (2 cách dễ dàng)
- Cách tính tổng nhiều hàng và cột trong Excel