Sum các ô tô màu trong excel, cách tính tổng các ô tô màu trong excel

lúc sếp bạn kêu yêu cầu lọc ra những giá trị vào vùng tài liệu chứa màu nhằm tính tổng nhưng bạn băn khoăn phải có tác dụng sao. Đừng lo lắng, trong bài viết này đang hướng dẫn cho chính mình mẹo tính tổng các ô dựa trên màu của bọn chúng trong Excel. Hãy thuộc mình tò mò hết bài viết này nha.

Ví dụ: Mình có bảng dữ liệu như bên dưới:

Yêu cầu: bản thân sẽ bắt buộc tính tổng vào những giá trị ô tất cả màu.

Bạn đang xem: Sum các ô tô màu

Để có tác dụng được như vậy, bản thân sẽ lí giải 3 giải pháp làm cấp tốc mà 1-1 giản:

Tính tổng theo màu sắc với hàm SUBTOTAL

Thường thì nhằm tính tổng, các bạn sẽ sử dụng hàm SUM nhằm tính. Tuy nhiên, trong trường hòa hợp này, bản thân sẽ thực hiện hàm SUBTOTAL để tính tổng theo color để khi bạn lọc chọn ra đk thì hàm SUBTOTAL sẽ cập nhật lại tác dụng nhanh chóng.

Các cách như sau:

Bước 1: trên ô đề xuất hiển thị hiệu quả tính tổng, các bạn gõ công thức sau: =SUBTOTAL(9,C3:C17)

Giải thích: 9: Số vật dụng tự nhằm tính tổng. C3:C17: Vùng dữ liệu muốn tính tổng.

Bước 2: chọn 1 ô bất kể trong vùng dữ liệu, chúng ta vào thẻ Data => chọn vào hiện tượng Filter để hiển thị bộ lọc ngay ở bên cạnh mỗi title cột.

Bước 3: các bạn sẽ chọn bộ lọc tại tiêu đề cột tính tổng, tiếp theo chọn vào Color Filter và lựa chọn màu ý muốn hiển thị.

Sau khi bạn chọn màu để lọc hiển thị ra. Thì bên dưới ô hiển thị tác dụng tính tổng vẫn tự update lại tổng phần đông giá trị đang hiển thị.

Tính tổng theo màu sắc với hàm VBA

Hiện nay không tồn tại công thức Excel có sẵn hoàn toàn có thể làm được cho bạn như phần đa gì các bạn muốn. Mặc dù nhiên, chúng ta cũng có thể làm là thực hiện VBA rất dễ dàng và chế tạo ra một hàm riêng cho chính mình và sau đó bạn cũng có thể sử dụng hàm tùy chỉnh thiết lập ở đó bất cứ đâu trong trang tính hoặc vào sổ thao tác làm việc và đem tổng những ô màu.

Các bước triển khai như sau:

Bước 1: tại trang tính chứa tài liệu cần tính tổng. Chúng ta gõ phím tắt Alt + F11.

Bước 2: hiện ra giao diện VBA

Tiếp theo, bạn vào thẻ Insert => lựa chọn vào Module.

Bước 3: trên module bắt đầu tạo, chúng ta gõ đoạn code như sau:

Function Sum
By
Color (Sum
Range, Sum
Color As Range)Dim Sum
Color
Value As Integer
Dim Total
Sum As Long
Sum
Color
Value = Sum
Color.Interior.Color
Index
Set r
Cell = Sum
Range
For Each r
Cell In Sum
Range
If r
Cell.Interior.Color
Index = Sum
Color
Value Then
Total
Sum = Total
Sum + r
Cell.value
End If
Next r
Cell
Sum
By
Color = Total
Sum
End Function

Bước 4: bấm vào Run trên thành công cụ hoặc nhấn F5 nhằm chạy chương trình nhé!

Bước 5: bạn sẽ thoát ra phía bên ngoài file Excel, các bạn chọn 2 ô trống bất kì: 1 ô hiển thị color cam, 1 ô hiển thị color xanh.

Bước 6: trên ô blue color hoặc 1 ô bất kì bạn có nhu cầu tính tổng số đông giá trị vào ô màu xanh, các bạn gõ công thức sau: =Sum
By
Color(C3:C17,F4)

Trong đó:

Hàm Sum
By
Color: là hàm tôi đã tạo trong bối cảnh VBA. C3:C17: Cột dữ liệu muốn tính tổng theo màu. F4: Ô chứa màu xanh da trời muốn tính tổng.

Tương tự nhằm tính tổng các giá trị theo vùng color cam, bạn gõ bí quyết sau: =Sum
By
Color(C3:C17,F5)

Trong đó:

Hàm Sum
By
Color: là hàm tôi đã tạo trong bối cảnh VBA. C3:C17: Cột tài liệu muốn tính tổng theo màu. F5: Ô chứa màu cam muốn tính tổng.

Tính tổng theo màu bằng phương pháp sử dụng Get.CELL + Mẹo Named Range

Trước lúc đặt tính tổng từng màu những giá trị trong ô, thì bạn sẽ phải mang lại hiển thị số đặc trưng cho những màu đó bằng cách:

Đầu tiên, bạn chọn vào 1 ô trống bất kể để hiển thị quý giá số biểu trưng.

Tiếp theo, bạn vào Formulas => lựa chọn vào Name Manager.

Hiển thị ra bảng Name Manager, các bạn chọn vào New để tạo thành tên mới. Tiếp đến, hiển thị bảng New Name

Tại ô Name: các bạn gõ tên bất kì
Tại ô Refers to: chúng ta gõ như sau: =Get.Cell(38,C3)

Giải thích: 38: yêu cầu cách làm này trích xuất cực hiếm màu của ô nhưng nó tham chiếu đến B2: bắt đầu tính cực hiếm từ địa điểm này trở đi.

Xem thêm: Cách tra cứu phạt nguội ô tô hà nội mới nhất? 4 cách tra cứu phạt nguội nhanh và chuẩn xác nhất

Tiếp theo, nhận OK để hoàn thành.

Sau khi xong xuôi xong câu hỏi đặt tên, bạn chọn 1 ô ngay gần giá trị hy vọng tính tổng cùng gõ: =Sumvalue

Để tính các ô tiếp theo, bạn cũng đặt nhỏ chuột tại địa điểm cuối ô, hiển thị dấu cộng và tích chuột trái gấp đôi để hiển thị những giá trị khác.

Sau khi hiển thị, thì họ biết giá tốt trị biểu trưng của các màu: greed color là số 37, color cam là số 40.

Tiếp theo, để tính tổng những giá trị tất cả màu xanh, chúng ta gõ: =SUMIF($D$3:$D$17,37,$C$3:$C$17)

Giải thích: $D$3:$D$17: Vùng tài liệu chứa quý hiếm số hình tượng cho màu. $C$3:$C$17: Vùng dữ liệu chứa các giá trị tính tổng.

Tương tự, nhằm tính tổng những giá trị tất cả màu cam, bạn gõ: =SUMIF($D$3:$D$17,40,$C$3:$C$17)

Vậy là tôi đã hướng dẫn đến bạn kết thúc mẹo tính tổng các ô dựa trên những giá trị của chúng. Hy vọng bài viết này để giúp đỡ ích mang lại bạn. Ví như thấy hay nhớ rằng đánh giá nội dung bài viết ngay bên dưới nhé! Trân trọng.

Làm nạm nào để vận dụng màu sắc trong trang tính nhằm tăng phần sinh động cho tệp tin Excel? nếu bạn đang do dự câu hỏi này thì hàm color trong Excel chính là câu vấn đáp bạn vẫn tìm kiếm. Hãy thuộc phukienotocaocap.com học cách áp dụng hàm color qua nội dung bài viết ngày lúc này nhé.

Giới thiệu về hàm màu sắc trong Excel

Không tất cả gì kỳ quái nếu đây là lần đầu tiên bạn biết đến tên hàm color trong Excel, bởi vì nó không ở trong hệ thống hàm có sẵn của Excel. Thế vào đó, đó là một hàm UDF (user-defined function) - hàm do người dùng tự tạo dựa trên code VBA. Mục đích bọn họ viết một hàm màu sắc mới là nhằm đếm cùng tính tổng các ô Excel dựa trên màu sắc.

Nếu bạn còn chưa quen cùng với thuật ngữ hàm UDF và tài năng viết hàm bởi lệnh VBA, hãy tham khảo nội dung bài viết dưới đây để bẻ khóa một thủ thuật hết sức lợi sợ hãi trong Excel nhé.

Cách đếm và tính tổng những ô màu bằng hàm color trong Excel

Chúng ta hãy đi vào trong 1 ví dụ ví dụ về hàm màu sắc trong Excel để xác định công việc cần làm. Dưới đó là trang tính thể thực trạng thái ship hàng của một doanh nghiệp vận tải, trong các số đó "Đang giao hàng trong X ngày" được tô màu sắc vàng, "Giao hàng thành công" được tô color xanh, "Quá hạn giao hàng" được tô màu đỏ. Hãy cùng xem cách áp dụng hàm màu sắc trong Excel để đếm cùng tính tổng các ô theo màu tương xứng nhé.

Cách đếm cùng tính tổng những ô theo color ô bằng hàm màu sắc trong Excel

Cách đếm số ô theo màu ô bởi hàm màu sắc trong Excel

Bước 1: Ấn tổ hợp phím Alt+F11 để mở hình ảnh Visual Basic Editor (VBE). Kế tiếp nhấn chuột bắt buộc ở tên trang tính vào mục Project - VBA Project, chọn Insert > Module.

Bước 2:Một bảng tính hiện ra. Các bạn nhập chiếc code sau vào bảng tính:

Function Get
Cell
Color(xl
Range As Range)Dim ind
Row, ind
Column As Long
Dim ar
Results() Application.Volatile If xl
Range Is Nothing Then
Set xl
Range = Application.This
Cell
End If If xl
Range.Count > 1 Then
Re
Dim ar
Results(1 khổng lồ xl
Range.Rows.Count, 1 to xl
Range.Columns.Count)For ind
Row = 1 to lớn xl
Range.Rows.Count
For ind
Column = 1 khổng lồ xl
Range.Columns.Countar
Results(ind
Row, ind
Column) = xl
Range(ind
Row, ind
Column).Interior.Color
Next
Next
Get
Cell
Color = ar
Results
Else
Get
Cell
Color = xl
Range.Interior.Color
End If
End Function Function Get
Cell
Font
Color(xl
Range As Range)Dim ind
Row, ind
Column As Long
Dim ar
Results() Application.Volatile If xl
Range Is Nothing Then
Set xl
Range = Application.This
Cell
End If If xl
Range.Count > 1 Then
Re
Dim ar
Results(1 khổng lồ xl
Range.Rows.Count, 1 lớn xl
Range.Columns.Count)For ind
Row = 1 to lớn xl
Range.Rows.Count
For ind
Column = 1 khổng lồ xl
Range.Columns.Countar
Results(ind
Row, ind
Column) = xl
Range(ind
Row, ind
Column).Font.Color
Next
Next
Get
Cell
Font
Color = ar
Results
Else
Get
Cell
Font
Color = xl
Range.Font.Color
End If over Function Function Count
Cells
By
Color(r
Data As Range, cell
Ref
Color As Range) As Long
Dim ind
Ref
Color As Long
Dim cell
Current As Range
Dim cnt
Res As Long Application.Volatilecnt
Res = 0ind
Ref
Color = cell
Ref
Color.Cells(1, 1).Interior.Color
For Each cell
Current In r
Data
If ind
Ref
Color = cell
Current.Interior.Color Thencnt
Res = cnt
Res + 1End If
Next cell
Current Count
Cells
By
Color = cnt
Res
End Function Function Sum
Cells
By
Color(r
Data As Range, cell
Ref
Color As Range)Dim ind
Ref
Color As Long
Dim cell
Current As Range
Dim sum
Res Application.Volatilesum
Res = 0ind
Ref
Color = cell
Ref
Color.Cells(1, 1).Interior.Color
For Each cell
Current In r
Data
If ind
Ref
Color = cell
Current.Interior.Color Thensum
Res = Worksheet
Function.Sum(cell
Current, sum
Res)End If
Next cell
Current Sum
Cells
By
Color = sum
Res
End Function Function Count
Cells
By
Font
Color(r
Data As Range, cell
Ref
Color As Range) As Long
Dim ind
Ref
Color As Long
Dim cell
Current As Range
Dim cnt
Res As Long Application.Volatilecnt
Res = 0ind
Ref
Color = cell
Ref
Color.Cells(1, 1).Font.Color
For Each cell
Current In r
Data
If ind
Ref
Color = cell
Current.Font.Color Thencnt
Res = cnt
Res + 1End If
Next cell
Current Count
Cells
By
Font
Color = cnt
Res
End Function Function Sum
Cells
By
Font
Color(r
Data As Range, cell
Ref
Color As Range)Dim ind
Ref
Color As Long
Dim cell
Current As Range
Dim sum
Res Application.Volatilesum
Res = 0ind
Ref
Color = cell
Ref
Color.Cells(1, 1).Font.Color
For Each cell
Current In r
Data
If ind
Ref
Color = cell
Current.Font.Color Thensum
Res = Worksheet
Function.Sum(cell
Current, sum
Res)End If
Next cell
Current Sum
Cells
By
Font
Color = sum
Res
End Function
Lưu bảng tính vừa nhập code dưới tên“Excel Macro-Enabled Workbook (.xlsm)”. Như vậy, bọn họ đã xử lí chấm dứt phần code VBA.

Bước 3: trở lại trang tính trạng thái giao hàng, ta chế tạo một bảng tổng hợp công dụng đếm ô theo màu. Để triển khai phân một số loại và đếm số ô bao gồm màu tương ứng, ta cần sử dụng hàm màu sắc trong Excel theo công thức:

=Count
Cells
By
Color(range, màu sắc code)

Trong đó:

range: là phạm vi đếmcolor code: là code color tương ứng

Như vậy, ta đã nhập vào ô B15 công thức: =Count
Cells
By
Color($E$2:$E$12,A15)

Lưu ý: chúng ta có thể sử dụng tham chiếu tuyệt đối để khóa phạm vi đếm.

Như chúng ta thấy thì việc đếm cùng tô màu các ô trong Excel bây giờ nhanh giường và tiện lợi hơn bao giờ hết với VBA. Ví như bạn không biết học VBA như nào và có khó không thì hãy đăng ký hoặc học thử ngay các khóa học dưới đây của phukienotocaocap.com nhé: