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 ô Show 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à SUBTOTALHã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 ô
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 VBATô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 https://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
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ố
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ÀOPhươ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
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
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
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?Không có hàm gốc nào trong Excel để tính tổng theo màu phông chữ nên chúng tôi cần Hàm do người dùng xác định.
Bạn có thể Sumif theo màu trong Excel không?Không có cách đơn giản nào để tính tổng các ô dựa trên màu nền trong Excel . Đối với ví dụ này, điều quan trọng là gán một giá trị cho mỗi màu nền và sử dụng giá trị đó làm tiêu chí cho hàm SUMIF của chúng ta.
Công thức Excel có thể phát hiện màu phông chữ không?Thật không may, Excel không cung cấp bất kỳ phương thức tích hợp nào để truy xuất màu phông chữ của ô . Điều đó - như thường lệ - có nghĩa là bạn phải sử dụng macro VBA. |