Bạn đang xem bài viết Chọn Lọc Ô Cell Theo Màu Sắc Trong Excel được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Bình thường, bạn có thể chọn các ô với các màu sắc khác nhau cho bất kỳ mục đích trong Excel. Nếu bạn có một bảng tính với việc sử dụng màu sắc khác nhau để chỉ loại khác nhau của nội dung và bạn muốn lọc hoặc chọn những ô màu sắc, bạn có thể cho nó thực hiện như sau:
Bình thường, bạn có thể chọn các ô với các màu sắc khác nhau cho bất kỳ mục đích trong Excel. Nếu bạn có một bảng tính với việc sử dụng màu sắc khác nhau để chỉ loại khác nhau của nội dung và bạn muốn lọc hoặc chọn những ô màu sắc, bạn có thể cho nó thực hiện như sau:
Bạn có thể lọc các hàng bằng màu sắc với các bước sau:
Bước 2 : Sau đó các nút mũi tên bộ lọc sẽ được xuất hiện bên cạnh các tiêu đề của cột được lựa chọn.
Bước 3 : Nhấp vào nút, và di chuyển chuột trên các Lọc theo Màu mục, và chọn một màu để lọc.
Sau đó, nó cho thấy các hàng mà các ô được lấp đầy bởi màu sắc lựa chọn, và ẩn các hàng khác một cách nhanh chóng. Xem ảnh chụp màn hình:
Đếm Số Cell Theo Màu
Như các bạn đã biết, trong Microsoft Excel có cung cấp hàm cho phép đếm số ô (cell) thỏa mãn điều kiện nào đó gọi là COUNTIF. Phần điều kiện của hàm này có thể là chữ hoặc số. Tuy nhiên, có những khi bạn cần đếm số cell này theo màu sắc thì COUNTIF không thực hiện được.
1. Mở Microsoft Excel, nhấn tổ hợp phím ALT+F11 để mở cửa sổ Visual Basic
Function CountByColor(range_data As range, criteria As range) As Long Dim datax As range Dim xcolor As Long xcolor = criteria.Interior.ColorIndex For Each datax In range_data If datax.Interior.ColorIndex = xcolor Then CountByColor = CountByColor + 1 End If Next datax End Function3. Đóng VBA quay trở lại Excel
5. Ở cell E2, viết hàm: =CountCcolor(Vùng dữ liệu, điều kiện) – Với “Vùng dữ liệu” chọn từ A1 to B28 – Với “điều kiện” chọn cell E1
6. Nhấn Enter and trong ô F2 the result is 4. Có nghĩa là 4 ô có màu xanh lá cây (green)
7. Bạn cũng có thể kiểm tra việc đếm các màu khác nữa bằng cách thay màu trong ô E1
Lưu thành Add-In để sử dụng cho file Excel hay máy tính khác
1. Ở file excel vào File chọn Save As
– Chọn nơi bạn cần lưu (tôi để là ở Desktop – màn hình).
– Ở ô File Name, điền tên cho file mà bạn muốn (Tôi để là CountByColor).
– Ở ô Save as type chọn Excel Add-In (.xlam).
Bạn có thể tải về Add-In của tôi ở đây: CountByColor
Cài đặt và sử dụng Add-In
1. Mở Excel trên máy tính mà bạn muốn sử dụng Add-In, Vào F ile chọn Option
3. Cửa sổ hiện ra, nhấn Browse và chọn đến file Add-In bạn đã tạo ra ở phần trước, nhấn OK
4. Cửa sổ Add-Ins hiện ra chọn ” Countbycolor” và nhấn Ok. Và bạn có thể sử dụng hàm CountByColor cho chương trình Excel.
Tagged: count by color, Excel, đếm theo màu
Hướng Dẫn Cách Đếm Và Tính Tổng Dựa Theo Màu Sắc Của Ô Tính Bằng Vba
Trong bài viết này, Học Excel Online sẽ đếm các ô tính bằng màu sắc và tính tổng các ô được tô màu. Những giải pháp này hiệu quả đối với cả ô tính tô màu thủ công và ô tính được định dạng theo điều kiện. Đồng thời, bạn cũng biết cách lọc các ô tính bằng nhiều màu trong Excel 2010, 2013, 2016.
Cách đếm và tính tổng bằng màu trong trang tính Excel
Giả sử bạn có bảng liệt kê danh sách đơn đặc hàng của công ty, trong đó cột Delivery được tô màu dựa trên giá trị của chúng: “Due in X Days” được tô màu cam, “Delivered” được tô màu xanh lá và “Past Due” được tô màu đỏ.
Mở bảng tính Excel và nhấn để mở Visual Basic Editor (VBE).
Thêm đoạn mã sau vào bảng tính.
Function GetCellColor(xlRange As Range)
Dim indRow, indColumn As Long
Dim arResults()
Application.Volatile
If xlRange Is Nothing Then
Set xlRange = Application.ThisCell
End If
ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
For indRow = 1 To xlRange.Rows.Count
For indColumn = 1 To xlRange.Columns.Count
arResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Color
Next
Next
GetCellColor = arResults
Else
GetCellColor = xlRange.Interior.Color
End If
End Function
Function GetCellFontColor(xlRange As Range)
Dim indRow, indColumn As Long
Dim arResults()
Application.Volatile
If xlRange Is Nothing Then
Set xlRange = Application.ThisCell
End If
ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
For indRow = 1 To xlRange.Rows.Count
For indColumn = 1 To xlRange.Columns.Count
arResults(indRow, indColumn) = xlRange(indRow, indColumn).Font.Color
Next
Next
GetCellFontColor = arResults
Else
GetCellFontColor = xlRange.Font.Color
End If
End Function
Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
Application.Volatile
cntRes = 0
indRefColor = cellRefColor.Cells(1, 1).Interior.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.Color Then
cntRes = cntRes + 1
End If
Next cellCurrent
CountCellsByColor = cntRes
End Function
Function SumCellsByColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes
Application.Volatile
sumRes = 0
indRefColor = cellRefColor.Cells(1, 1).Interior.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.Color Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent
SumCellsByColor = sumRes
End Function
Function CountCellsByFontColor(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
Application.Volatile
cntRes = 0
indRefColor = cellRefColor.Cells(1, 1).Font.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Font.Color Then
cntRes = cntRes + 1
End If
Next cellCurrent
CountCellsByFontColor = cntRes
End Function
Function SumCellsByFontColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes
Application.Volatile
sumRes = 0
indRefColor = cellRefColor.Cells(1, 1).Font.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Font.Color Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent
SumCellsByFontColor = sumRes
End Function
Lưu bảng tính thành “Excel Macro-Enabled Workbook (.xlsm)”.
Như vậy, tất cả những thao tác lặt vặt đã thực hiện xong, chỉ cần thêm hàm được chỉ định, chọn ô tính muốn hiển thị công thức và gõ công thức CountCellsByColor.
=CountCellsByColor(range, color code)
Trong ví dụ này, chúng ta sử dụng công thức = CountCellsByColor (F2:F14, A17) trong đó F2: F14 là dãy chứa các ô có màu bạn muốn đếm và A17 là ô có màu nền nhất định, trong ví dụ đó là các ô màu đỏ.
Tương tự, bạn viết công thức để đếm các ô màu khác, như màu vàng và màu xanh lá cây trong bảng của chúng tôi.
Chú ý. Sau khi áp dụng mã VBA, bạn sẽ cần thêm màu cho một số ô khác theo cách thủ công, tổng và số ô được tô màu sẽ không được tính lại tự động để phản ánh những thay đổi.
Trên thực tế, đó là đặc điểm bình thường của tất cả các , VBA scripts và hàm User-Defined. Vấn đề là tất cả các chức năng đó sẽ đi kèm với sự thay đổi dữ liệu của một bảng tính và Excel không nhận thấy sự thay đổi màu chữ hoặc màu sắc của ô này. Vì vậy, sau khi tô màu các ô một cách thủ công, chỉ cần đặt con trỏ chuột vào bất kỳ ô nào, nhấn và Enter, số ô và tổng của chúng sẽ được cập nhật.
Tính tổng và đếm số ô bằng màu sắc trên toàn bộ bảng tính:
Đoạn VB bên dưới được viết theo câu trả lời của Connor (một bậc thầy Excel) và thực hiện chính xác những gì Connor yêu cầu, cụ thể là đếm và tính tổng các ô cùng màu trong tất cả trang tính của bảng tính. Vì vậy, ở đây có mã số:
Function WbkCountCellsByColor(cellRefColor As Range)
Dim vWbkRes
Dim wshCurrent As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
vWbkRes = 0
For Each wshCurrent In Worksheets
wshCurrent.Activate
vWbkRes = vWbkRes + CountCellsByColor(wshCurrent.UsedRange, cellRefColor)
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
WbkCountCellsByColor = vWbkRes
End Function
Function WbkSumCellsByColor(cellRefColor As Range)
Dim vWbkRes
Dim wshCurrent As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
vWbkRes = 0
For Each wshCurrent In Worksheets
wshCurrent.Activate
vWbkRes = vWbkRes + SumCellsByColor(wshCurrent.UsedRange, cellRefColor)
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
WbkSumCellsByColor = vWbkRes
End Function
Tùy chỉnh các chức năng để tô màu nền ô, tô màu chữ và mã màu sắc
Bạn sử dụng tương tự như mã trước đó và sử dụng kết quả của các công thức =WbkCountCellsColor() và =WbkSumCellsByColor(). Chỉ cần nhập công thức vào bất kỳ ô trống nào trên trang tính nằm ngoài vùng dữ liệu được chọn, xác định địa chỉ ô tính chứa màu bạn muốn vào ngoặc đơn, ví dụ = WbkSumCellsByColor (A1), và công thức sẽ hiển thị tổng của tất cả các ô được tô cùng trong bảng tính của bạn.
Ở đây, bạn sẽ tìm thấy một bản tóm tắt tất cả các chức năng chúng ta đã sử dụng trong ví dụ này cũng như một vài cái chức năng mới để lấy mã màu.
Chú ý. Hãy nhớ rằng tất cả các công thức chỉ hoạt động nếu bạn đã thêm chức năng do người dùng định nghĩa vào bảng tính Excel như đã trình bày ở trên.
Chức năng đếm theo màu sắc:
CountCellsByColor (phạm vi, mã màu) – đếm các ô có màu nền được chỉ định.
Công thức tính tổng bằng màu sắc:
CountCellsByFontColor (phạm vi, mã màu) – đếm các ô với màu chữ được chỉ định.
SumCellsByColor (range, color code) – tính tổng của các ô có cùng màu nền.
Công thức để lấy mã màu:
SumCellsByFontColor (range, color code) – tính tổng của các ô có cùng màu chữ.
GetCellFontColor (cell) – trả về mã màu chữ của một ô xác định.
Cách đếm và tính tổng các ô màu định dạng có điều kiện:
Sub SumCountByConditionalFormat()
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
Dim sumRes
Dim cntCells As Long
Dim indCurCell As Long
cntRes = 0
sumRes = 0
cntCells = Selection.CountLarge
indRefColor = ActiveCell.DisplayFormat.Interior.Color
For indCurCell = 1 To (cntCells – 1)
If indRefColor = Selection(indCurCell).DisplayFormat.Interior.Color Then
cntRes = cntRes + 1
sumRes = WorksheetFunction.Sum(Selection(indCurCell), sumRes)
End If
Next
MsgBox “Count=” & cntRes & vbCrLf & “Sum= ” & sumRes & vbCrLf & vbCrLf & _
“Color=” & Left(“000000”, 6 – Len(Hex(indRefColor))) & _
Hex(indRefColor) & vbCrLf, , “Count & Sum by Conditional Format color”
End Sub
Cách sử dụng mã để đếm số ô màu và tính tổng giá trị của chúng
Thêm đoạn mã trên vào trang tính như đã giải thích ở ví dụ.
Chọn một dãy hoặc nhiều vùng dữ liệu bạn cần nếu bạn có dữ liệu dạng số.
Nhấn giữ phím , chọn một ô tính với màu cần thao tác, thả phím .
Nhấn để mở danh sách trong bảng tính.
Chọn SumCountByConditionalFormat và nhấp vào Run.
Trong ví dụ này, chúng ta chọn cột Qty. và có các con số sau:
Count là số ô tính trong một màu cụ thể, như số ô màu đỏ trong ví dụ được đánh dấu là “Past Due”.
Sum là tổng giá trị các ô màu đỏ trong cột Qty, cụ thể là tổng số ô “Past Due”.
Color là mã màu của ô được chọn, cụ thể là ô D2.
Qua bài viết này chúng ta thấy việc sử dụng VBA trong Excel đem lại hiệu quả rất nhiều trong công việc đúng không nào. Không chỉ có vậy, VBA còn làm được nhiều điều thú vị hơn nữa như: giúp bạn tăng tốc trong quá trình xử lý trên excel, giúp lập các báo cáo tự động, điều khiển các chương trình khác như word, power point, outlook… giúp biến file excel của bạn trở thành 1 phần mềm chuyên nghiệp…
Hướng Dẫn Hàm Cell Tra Cứu Thông Tin Ô Trong Excel
Trong bài viết này, Học Excel Online sẽ hướng dẫn bạn về hàm CELL, một hàm sẽ giúp ích khi bạn muốn tra cứu thông tin trong một ô.
Hàm CELL, công dụng và cú pháp
Công dụng hàm CELL
Hàm CELL trả về thông tin về định dạng, vị trí hay nội dung của một ô theo các đối số được nhập vào.
Cú pháp của hàm như sau:
=CELL(info_type, [reference])
Trong đó:
info_type: Giá trị văn bản xác định bạn muốn trả về kiểu thông tin ô nào
reference: không bắt buộc. Ô mà bạn muốn có thông tin.
Lưu ý:
Nếu bị bỏ qua, thông tin đã xác định trong Info_type được trả về cho ô cuối cùng đã được thay đổi.
Nếu tham chiếu một vùng, hàm CELL chỉ trả về thông tin cho ô trên cùng bên trái của vùng đó.
Ví dụ giá trị info_type như sau:
address đưa ra tham chiếu của ô đầu tiên trong tham chiếu, ở dạng văn bản
col trả về số cột của ô trong tham chiếu
color đưa giá trị 1 nếu ô được định dạng màu cho các giá trị âm; nếu không sẽ trả về 0 (không)
contents đưa ra giá trị của ô được tham chiếu
filename trả về tên file. Giá trị trả về bằng rỗng (“”) nếu file chưa lưu
format đưa ra định dạng file theo bảng bên dưới:
prefix trả về giá trị văn bản tương ứng với “tiền tố nhãn” của ô. Trả về (‘) nếu ô chứa văn bản được canh trái, (“) nếu ô chứa văn bản được canh phải, (^) nếu ô chứa văn bản được canh giữa, () nếu ô chứa văn bản được canh đều hai bên và (“”) nếu ô chứa bất kỳ thứ gì khác.
protect đưa giá trị 0 nếu ô không bị khóa; 1 nếu ô bị khóa.
row trả về số hàng của ô trong tham chiếu
type trả về “b” nếu ô trống, “l” nếu ô chứa văn bản và “v” nếu ô chứa bất kỳ thứ gì khác.
width đưa ra độ rộng của cột
Hàm CELL được sử dụng để tra cứu thông tin trong ô như sau:
Cập nhật thông tin chi tiết về Chọn Lọc Ô Cell Theo Màu Sắc Trong Excel trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!