Đánh giá lequocthai.com:
Vài tuần trước, một độc giả thân thiết đã gửi email cho tôi, mô tả một vấn đề anh ấy gặp phải. Nhiệm vụ của anh ấy là tính tổng giá trị trong một số ô nhất định dựa trên màu nền của chúng.
Như chúng ta đều biết, Excel mặc định không có sẵn công thức hay tính năng nào để thực hiện việc này. Vì vậy, trong bài viết này, tôi sẽ chia sẻ một vài phương pháp giúp bạn đạt được mục tiêu trên.
Để hình dung rõ hơn, chúng ta hãy xem hình ảnh dưới đây.
Hình ảnh cho thấy chúng ta không cần tính tổng tất cả các ô, mà chỉ cần tổng của những ô có cùng màu nền.
Bài viết liên quan:
[Tính Tổng Có Trọng Số (Weighted SUM) trong Excel]()
Phương pháp 1: Tính tổng các ô dựa trên màu nền bằng hàm SUMIF:
Chúng ta biết rằng hàm SUMIF là sự kết hợp giữa hàm SUM và IF, và do đó nó có thể khá hữu ích để cộng các ô dựa trên màu nền.
Đầu tiên, hãy cùng tìm hiểu cách thực hiện:
Giả sử chúng ta có một bảng dữ liệu như hình dưới đây.
Tiếp theo, chúng ta sẽ thêm một cột nữa vào bảng này, trong đó chúng ta nhập thủ công màu nền của các ô tương ứng như hình dưới đây.
Bây giờ, hãy thử sử dụng hàm SUMIF để tìm tổng các ô có nền màu vàng như sau:
=SUMIF(B2:B13,"Yellow",A2:A13)
Tương tự, để tìm tổng các ô có nền màu Cam và Xanh lá, chúng ta sẽ sử dụng các hàm
=SUMIF(B2:B13,"Orange",A2:A13) và =SUMIF(B2:B13,"Green",A2:A13)tương ứng.
Tuy nhiên, như có thể thấy, phương pháp này khá rườm rà, đặc biệt nếu chúng ta cần áp dụng cho danh sách có hàng trăm dòng dữ liệu.
Vậy, giải pháp thay thế nhanh hơn là gì?
Để làm cho quy trình trên dễ sử dụng hơn, chúng ta cần giảm bớt công sức nhập màu nền thủ công.
Khi đó, một Hàm do Người dùng Tự định nghĩa (UDF) nhỏ sẽ hữu ích.
“`
Function ColorIndex(CellColor As Range)
ColorIndex = CellColor.Interior.ColorIndex
End Function
“`
Lưu ý: Hàm này không trả về tên màu mà trả về chỉ số màu (color index), cũng là một giá trị duy nhất và có thể được sử dụng trong nhiệm vụ của chúng ta.
Làm theo các bước dưới đây để sử dụng UDF:
- Trước hết, mở bảng tính nơi bạn cần tính tổng các ô dựa trên màu nền.
- Tiếp theo, nhấn ALT + F11 để mở Trình soạn thảo VB (VB Editor). Điều hướng đến ‘Insert’ > ‘Module’.
- Sau đó, dán UDF “ColorIndex” vào Trình soạn thảo.
- Bây giờ, thêm một cột bên cạnh phạm vi mà bạn muốn tính tổng. Trong cột mới này, nhập công thức như sau:
=ColorIndex(ô_cần_lấy_mã_màu)Ví dụ: `=ColorIndex(A2)`
- Sau đó, kéo công thức này cho toàn bộ phạm vi.
- Bây giờ, bạn có thể sử dụng hàm SUMIF để tính tổng các ô có cùng màu nền như hình trên.
Phương pháp 2: Sử dụng một UDF nhanh hơn và tốt hơn nhiều:
UDF mà chúng ta sẽ sử dụng trong phương pháp này đơn giản là một phiên bản mở rộng của hàm ở trên.
Hàm này như sau:
“`
Function SumByColor(CellColor As Range, rRange As Range)
Dim cSum As Double
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
SumByColor = cSum
End Function
“`
Cách sử dụng UDF này:
Làm theo các bước dưới đây:
- Mở bảng tính mục tiêu của bạn.
- Nhấn ALT + F11 để mở Trình soạn thảo VBA và điều hướng đến ‘Insert’ > ‘Module’.
- Dán Hàm “SumByColor” vào Trình soạn thảo.
- Bây giờ, chỉ cần nhập hàm “SumByColor” để gọi nó và truyền các đối số sau:
=SumByColor(ô_mẫu_có_màu_nền, phạm_vi_tính_tổng)Ví dụ: `=SumByColor(A2, A2:A13)`
Lưu ý: Trong công thức được hiển thị trong hình trên, thay vì đối số đầu tiên “A2”, chúng ta cũng có thể sử dụng bất kỳ ô nào trong số A2, A5, A8, A10, A12. Bởi vì tất cả các ô này đều có nền màu vàng.
Trong trường hợp của chúng ta, chúng ta có thể sử dụng các công thức sau:
* Tổng các ô Vàng:
=SumByColor(A2,A2:A13)(Vì ‘A2’ là ô mẫu màu vàng và A2:A13 là phạm vi cần tính tổng)
* Tổng các ô Cam:
=SumByColor(A3,A2:A13)(Vì ‘A3’ là ô mẫu màu cam và A2:A13 là phạm vi cần tính tổng)
* Tổng các ô Xanh lá:
=SumByColor(A4,A2:A13)(Vì ‘A4’ là ô mẫu màu xanh lá và A2:A13 là phạm vi cần tính tổng)
Vậy, đó là tất cả từ tôi về chủ đề này. Đừng quên tải xuống bảng tính mẫu [tại đây](http://exceltrick.com/wp-content/uploads/2013/07/Sum_based_on_background_color.xls) và hãy cho tôi biết nếu bạn gặp bất kỳ vấn đề nào khi sử dụng các phương pháp này.
