Màu văn bản Sumif excel

Excel có một số hàm thực sự tuyệt vời, nhưng nó không có hàm có thể tính tổng các giá trị ô dựa trên màu ô

Ví dụ: tôi có tập dữ liệu như hình bên dưới và tôi muốn lấy tổng của tất cả các ô màu cam và vàng

Thật không may, không có chức năng tích hợp để làm điều này

Nhưng đừng bao giờ nói không bao giờ

Trong hướng dẫn này, tôi sẽ chỉ cho bạn ba kỹ thuật đơn giản mà bạn có thể sử dụng để tính tổng theo màu trong Excel

Hãy đi sâu vào

Hướng dẫn này bao gồm

SUM các ô theo màu bằng cách sử dụng bộ lọc và SUBTOTAL

Hãy bắt đầu với cái dễ nhất

Dưới đây tôi có một tập dữ liệu nơi tôi có tên nhân viên và số lượng bán hàng của họ

Và trong tập dữ liệu này, tôi muốn lấy tổng của tất cả các ô được tô màu vàng và cam

Mặc dù không có hàm dựng sẵn trong Excel để tính tổng các giá trị dựa trên màu ô, nhưng có một cách giải quyết đơn giản dựa trên thực tế là bạn có thể lọc các ô dựa trên màu ô

Đối với phương pháp này, hãy nhập công thức bên dưới vào ô B17 [hoặc bất kỳ ô nào trong cùng cột bên dưới tập dữ liệu ô được tô màu]

=SUBTOTAL[9,B2:B15]

Trong công thức SUBTOTAL ở trên, tôi đã sử dụng 9 làm đối số đầu tiên, cho hàm biết rằng tôi muốn lấy tổng của phạm vi được đưa ra làm đối số thứ hai

Nhưng tại sao không sử dụng công thức SUM thay thế?

Điều này là do khi tôi có công thức SUBTOTAL và tôi lọc các ô để chỉ hiển thị những ô có màu cụ thể trong đó, công thức SUBTOTAL sẽ chỉ hiển thị cho tôi tổng của các ô hiển thị [điều mà công thức SUM không thể làm được]

Vì vậy, khi bạn có công thức SUBTOTAL, hãy làm theo các bước bên dưới để lấy TỔNG dựa trên màu ô

1. Chọn bất kỳ ô nào trong tập dữ liệu
2. Nhấp vào tab Dữ liệu

1. Trong nhóm Sắp xếp và Lọc, bấm vào biểu tượng Bộ lọc. Thao tác này sẽ áp dụng bộ lọc cho tập dữ liệu và bạn sẽ có thể thấy biểu tượng bộ lọc trong tiêu đề

1. Trong ô tiêu đề Bán hàng, nhấp vào biểu tượng Bộ lọc

1. Chuyển đến tùy chọn Lọc theo màu”
2. Chọn màu dựa vào đó bạn muốn lọc tập dữ liệu

Ngay sau khi bạn làm điều này, bạn sẽ nhận thấy rằng kết quả tổng phụ thay đổi và giờ đây nó sẽ chỉ cung cấp cho bạn tổng của những ô hiển thị [chỉ những ô có màu mà bạn đã lọc tập dữ liệu]

Tương tự, nếu bạn lọc theo một số màu khác trong tập dữ liệu [chẳng hạn như màu cam thay vì màu vàng], hàm SUBTOTAL sẽ điều chỉnh tương ứng và cung cấp cho bạn tổng của tất cả các ô có màu cam

Mẹo chuyên nghiệp. Phím tắt để áp dụng bộ lọc cho tập dữ liệu là Control + Shift + L [giữ phím Control và phím Shift, sau đó nhấn phím L]. Nếu dùng Mac thì dùng Command + Shift + L

Tính tổng các ô theo màu bằng VBA

Tôi đã đề cập rằng không có công thức sẵn có trong Excel để tính tổng dựa trên giá trị màu của ô. Tuy nhiên, bạn có thể tạo công thức của riêng mình để làm điều này bằng VBA

Với VBA, bạn có thể tạo một hàm tùy chỉnh mà bạn có thể giữ ở phần cuối, sau đó sử dụng nó như bất kỳ hàm thông thường nào khác trong trang tính

Dưới đây là mã VBA sẽ tạo hàm tùy chỉnh cho phép bạn tính tổng theo màu trong Excel

'Code created by Sumit Bansal from //trumpexcel.com/
'This VBA code created a function that can be used to sum cells based on color
Function SumByColor[SumRange As Range, SumColor As Range]
Dim SumColorValue As Integer
Dim TotalSum As Long
SumColorValue = SumColor.Interior.ColorIndex
Set rCell = SumRange
For Each rCell In SumRange
If rCell.Interior.ColorIndex = SumColorValue Then
TotalSum = TotalSum + rCell.Value
End If
Next rCell
SumByColor = TotalSum
End Function

Để sử dụng chức năng tùy chỉnh VBA này, trước tiên bạn phải sao chép mã này và dán vào phần cuối của trình soạn thảo VB

Sau khi hoàn tất, bạn sẽ có thể sử dụng chức năng này trong trang tính

Dưới đây là các bước để thêm mã này vào trình soạn thảo VB

1. Nhấp vào tab Nhà phát triển trong dải băng [nếu bạn không thấy tab Nhà phát triển, hãy nhấp vào đây để tìm hiểu cách tải xuống]
2. Bấm vào biểu tượng Visual Basic. Thao tác này sẽ mở trình soạn thảo Visual Basic của Excel

1. Nhấp vào tùy chọn Chèn trong menu
2. Nhấp vào Mô-đun. Thao tác này sẽ chèn một mô-đun mới và bạn sẽ có thể nhìn thấy mô-đun đó trong trình khám phá dự án [một ô bên trái hiển thị tất cả các đối tượng]. Nếu bạn không thấy khung Project Explorer, hãy bấm vào Xem rồi bấm vào Project Explorer

1. Copy đoạn code VBA trên dán vào cửa sổ Module code vừa chèn

1. Đóng Trình soạn thảo VB

Bây giờ bạn đã có mã ở mặt sau trong excel, bạn sẽ có thể sử dụng hàm mà chúng tôi đã tạo [SumByColor] trong trang tính

Để chức năng này hoạt động, tôi sẽ cần một ô trong trang tính có cùng màu mà tôi muốn lấy tổng

Trong ví dụ của chúng tôi, tôi đã làm điều đó với các ô D2 và D3, trong đó D2 có màu vàng và D3 có màu cam

Bây giờ tôi có thể sử dụng công thức dưới đây trong các ô này

=SumByColor[$B$2:$B$15,D2]

Công thức trên có hai đối số

• Phạm vi ô có màu mà tôi muốn thêm
• Tham chiếu đến bất kỳ ô nào có chứa màu [để công thức có thể chọn chỉ mục màu và sử dụng chỉ mục đó làm điều kiện để thêm các giá trị]

Lưu ý rằng công thức là công thức động và sẽ tự động cập nhật trong trường hợp bạn thực hiện bất kỳ thay đổi nào đối với tập dữ liệu [chẳng hạn như thay đổi bất kỳ giá trị nào hoặc áp dụng/xóa màu khỏi một số ô]. Và trong trường hợp bạn nhận thấy rằng công thức không cập nhật, hãy nhấn phím F9 và nó sẽ cập nhật

Vì chúng tôi đã sử dụng mã VBA trong sổ làm việc, nó cần được lưu dưới dạng sổ làm việc hỗ trợ macro [với. phần mở rộng XLSM]

Mẹo chuyên nghiệp. Nếu việc thêm các ô dựa trên màu nền của chúng là điều bạn cần thực hiện khá thường xuyên, thì tôi khuyên bạn nên sao chép và dán mã VBA này cho công thức tùy chỉnh trong Sổ làm việc Macro cá nhân. Bằng cách này, nó sẽ có sẵn trên tất cả các sổ làm việc trên hệ thống của bạn

Tính tổng các ô theo màu bằng cách sử dụng GET. TẾ BÀO

Phương pháp cuối cùng tôi muốn chỉ cho bạn bao gồm một công thức Excel ẩn [không nhiều người biết về nó]

Phương pháp này sử dụng lệnh GET. Hàm CELL, có thể cho chúng ta giá trị chỉ số màu của các ô được tô màu

Và khi chúng ta có giá trị chỉ mục màu của mỗi ô, thì chúng ta có thể sử dụng công thức sum if đơn giản để chỉ lấy tổng của các ô có một màu cụ thể trong đó

Nó không thanh lịch như chức năng tùy chỉnh VBA mà tôi đã đề cập trước đó, nhưng nếu bạn không muốn sử dụng VBA, thì đây có thể là cách phù hợp với bạn

LẤY. CELL là một hàm Macro 4 cũ đã được lưu giữ trong Excel vì lý do tương thích, nhưng bạn sẽ không tìm thấy nhiều thông tin chi tiết về nó [vì nó hiếm khi được sử dụng]

Dưới đây tôi có một tập dữ liệu nơi tôi có các ô màu mà tôi muốn tính tổng

Để kỹ thuật này hoạt động, trước tiên chúng ta cần tạo một phạm vi được đặt tên sẽ sử dụng lệnh GET. Hàm CELL để đưa ra giá trị màu của một ô màu

Dưới đây là các bước để làm điều này

1. Bấm vào tab Công thức trong Dải băng
2. Trong nhóm Tên được xác định, nhấp vào 'Trình quản lý tên'

1. Trong hộp thoại Trình quản lý tên, bấm vào Mới

1. Trong hộp thoại ‘Tên mới, nhập Tên – SumColor

1. Trong trường Refers to, nhập công thức sau. =NHẬN. TẾ BÀO[38,$B2]

1. Nhấp vào OK
2. Đóng hộp thoại Trình quản lý tên

Các bước trên đã tạo một phạm vi được đặt tên mà bây giờ chúng ta có thể sử dụng trong sổ làm việc

Ghi chú. LẤY. Hàm CELL có hai đối số, đối số đầu tiên là một số cho hàm biết chúng ta cần thông tin gì và đối số thứ hai là tham chiếu ô của chính ô đó. Trong trường hợp này, tôi có 38 làm đối số đầu tiên, nó sẽ cung cấp cho chúng tôi giá trị chỉ số màu của ô được tham chiếu

Bây giờ, bước thứ hai là lấy các giá trị chỉ số màu của tất cả các màu trong cột B

Dưới đây là các bước để làm điều này

1. Trong ô C1, nhập tiêu đề - Chỉ mục màu [hoặc bất kỳ thứ gì bạn gọi nó]

1. Trong ô C2, nhập công thức sau. =SumColor

1. Áp dụng cùng một công thức cho tất cả các ô trong cột C [bạn có thể sử dụng núm điều khiển điền hoặc chỉ cần sao chép và dán ô C2]

Các bước trên sẽ cung cấp cho bạn một giá trị đại diện cho chỉ số màu của ô trong cột B [ô bên trái]

SumColor là phạm vi được đặt tên mà chúng tôi đã tạo và nó sử dụng lệnh GET. Hàm CELL để lấy giá trị chỉ số màu của ô bên trái. Bạn có thể sử dụng công thức này trong bất kỳ cột nào, nhưng công thức phải luôn bắt đầu từ ô thứ hai trong cột đó. Ví dụ: thay vì cột C, bạn có thể đặt cột này trong cột H hoặc J, nhưng từ các ô thứ hai trong các cột này

Bây giờ chúng ta có một số duy nhất cho mỗi màu, chúng ta có thể sử dụng số này để lấy TỔNG của các ô dựa trên màu của chúng

Dưới đây là các bước để làm điều này

1. Trong ô E2 và E3, hãy tô màu cho các ô mà bạn muốn lấy tổng. Trong trường hợp của tôi, tôi có màu vàng ở ô E2 và màu cam ở ô E3

1. Trong ô F2, nhập công thức sau. =SUMIF[C2. C15,Tổng màu,B2. B15]

1. Sao chép ô và dán vào ô F3 [điều này cũng có thể sao chép công thức và điều chỉnh tham chiếu]

Các bước trên sẽ cung cấp cho bạn tổng của tất cả các ô có màu trong cột bán hàng liền kề F

Chúng tôi đã sử dụng công thức SUMIF, trong đó công thức này cộng tất cả các giá trị trong cột B, nếu chỉ số màu của ô bên trái [trong cột F] giống với chỉ mục màu trong cột C

Mặc dù đây chắc chắn là cách dài hơn một chút để tính tổng các ô theo màu [so với SUBTOTAL hoặc VBA], nhưng cách này sẽ hoàn thành công việc và bạn chỉ cần thực hiện thiết lập này một lần

Lưu ý rằng mặc dù công thức là động, nhưng trong trường hợp bạn thực hiện bất kỳ thay đổi nào [chẳng hạn như thay đổi các ô màu trong cột B hoặc xóa màu khỏi cột B], thay đổi đó có thể không được phản ánh ngay lập tức trong công thức SUMIF. Một cách giải quyết đơn giản là chuyển đến ô có công thức, nhấp vào phím F2 để vào sân bay, sau đó nhấn phím enter. Điều này sẽ buộc công thức tính toán lại và bạn sẽ nhận được kết quả cập nhật

Vì vậy, đây là ba phương pháp bạn có thể sử dụng để tính tổng theo màu trong Excel. Mặc dù SUBTOTAL khá dễ dàng và đơn giản nhưng cá nhân tôi thích phương pháp VBA hơn

Bạn có thể Sumif bằng màu văn bản?

Bạn có thể Sumif theo màu trong Excel không?

Công thức Excel có thể phát hiện màu phông chữ không?