PDA

View Full Version : Lọc ô theo màu nền+xóa khoản trắng



phonglan99
25-09-09, 03:15 PM
Chào các anh chị,
-Cho em hỏi có cách nào lọc các ô theo màu được không ạ?(chỉ lọc lấy ra những ô có tô màu nền thôi).
-Em muốn loại bỏ các khoản trắng phía sau các chuỗi text thì phải làm sao a?
Rất mong các anh chị chỉ giúp
em xin cám ơn.

haituan
25-09-09, 03:57 PM
Được mà bạn. Bạn chọn sort by color đó bạn.

ptlong04x1
25-09-09, 04:14 PM
-Em muốn loại bỏ các khoản trắng phía sau các chuỗi text thì phải làm sao a?Bajn dùng công thức sau : =TRIM(A1) trong đó A1 là ô chứa Text có khoảng trắng.

ExcelCongCuTuyetVoiCuaBan
25-09-09, 04:15 PM
Được mà bạn. Bạn chọn sort by color đó bạn.
Cái này chỉ có tác dụng với excel 2007 trở lên thôi bạn.
Nếu muốn filter ở 2003 về trước thì phải dùng VBA thôi

ExcelCongCuTuyetVoiCuaBan
25-09-09, 04:17 PM
Bajn dùng công thức sau : =TRIM(A1) trong đó A1 là ô chứa Text có khoảng trắng.
Cái này chưa chắc dúng nhe bạn, có thể nó còn 1 khoảng trắng.
Bạn nên dùng Replace with hay hơn

ptlong04x1
25-09-09, 04:18 PM
Cái này chưa chắc dúng nhe bạn, có thể nó còn 1 khoảng trắng.
Bạn nên dùng Replace with hay hơn

Bạn lưu ý yêu cầu của tác giả.


-Em muốn loại bỏ các khoản trắng phía sau các chuỗi text thì phải làm sao a?

huuthang_bd
25-09-09, 04:21 PM
Được mà bạn. Bạn chọn sort by color đó bạn.
Chức năng này chỉ có ở Excel 2007 thôi nha bạn.


-Cho em hỏi có cách nào lọc các ô theo màu được không ạ?(chỉ lọc lấy ra những ô có tô màu nền thôi).
Bạn dùng cột phụ với hàm Macro4 GET.CELL(13) rồi lọc theo cột phụ này. Xem cách tôi làm trong file.

Hai Lúa Miền Tây
25-09-09, 05:25 PM
Chức năng này chỉ có ở Excel 2007 thôi nha bạn.

Bạn dùng cột phụ với hàm Macro4 GET.CELL(13) rồi lọc theo cột phụ này. Xem cách tôi làm trong file.
Dùng cách như bạn lọc ra hết những cell có tô màu, nó không phân biệt là màu gì.
Ở 2007 thì không nói, vì nó có sẵn chức năng lọc theo màu, nhưng 2003 trở về trước thì không.
Nếu dùng cột phụ để lọc ra theo màu bạn muốn thì phải thêm 1 function là lấy mã màu, sau đó so với điều kiện lọc là được.

Đây là Function đó:


Option Explicit
'---------------------------------------------------------------------
Function ColorIndex(rng As Range, _
Optional text As Boolean = False) As Variant
'---------------------------------------------------------------------
On Error Resume Next
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim iWhite As Long, iBlack As Long
Dim aryColours As Variant
If rng.Areas.Count > 1 Then
ColorIndex = CVErr(xlErrValue)
Exit Function
End If
iWhite = WhiteColorindex(rng.Worksheet.Parent)
iBlack = BlackColorindex(rng.Worksheet.Parent)
If rng.Cells.Count = 1 Then
If text Then
aryColours = DecodeColorIndex(rng, True, iBlack)
Else
aryColours = DecodeColorIndex(rng, False, iWhite)
End If
Else
aryColours = rng.Value
i = 0
For Each row In rng.Rows
i = i + 1
j = 0
For Each cell In row.Cells
j = j + 1
If text Then
aryColours(i, j) = _
DecodeColorIndex(cell, True, iBlack)
Else
aryColours(i, j) = _
DecodeColorIndex(cell, False, iWhite)
End If
Next cell
Next row
End If
ColorIndex = aryColours
End Function
'---------------------------------------------------------------------
Private Function WhiteColorindex(oWB As Workbook)
'---------------------------------------------------------------------
Dim iPalette As Long
WhiteColorindex = 0
For iPalette = 1 To 56
If oWB.Colors(iPalette) = &HFFFFFF Then
WhiteColorindex = iPalette
Exit Function
End If
Next iPalette
End Function
'---------------------------------------------------------------------
Private Function BlackColorindex(oWB As Workbook)
'---------------------------------------------------------------------
Dim iPalette As Long
BlackColorindex = 0
For iPalette = 1 To 56
If oWB.Colors(iPalette) = &H0 Then
BlackColorindex = iPalette
Exit Function
End If
Next iPalette
End Function
'---------------------------------------------------------------------
Private Function DecodeColorIndex(rng As Range, _
text As Boolean, _
idx As Long)
'---------------------------------------------------------------------
Dim iColor As Long
If text Then
iColor = rng.Font.ColorIndex
Else
iColor = rng.Interior.ColorIndex
End If
If iColor < 0 Then
iColor = idx
End If
DecodeColorIndex = iColor
End Function
'---------------------------------------------------------------------



Bạn xem file đính kèm nhé

Hai Lúa Miền Tây
25-09-09, 05:41 PM
Còn nếu như dùng 1 cell để so sánh với các cell có chứa màu thì dùng code sau:


Sub FilterByColor()
On Error Resume Next
Sheet1.Range("F1") = ColorIndex(Sheet1.Range("d1"))
Application.ScreenUpdating = False
Dim cel As Range, rng As Range
Set rng = Sheet1.Range("F3", Sheet1.Range("D65536").End(xlUp).Offset(, 2))
For Each cel In rng
If cel.Offset(, -2).Interior.ColorIndex = Sheet1.Range("F1") Then

cel.Value = Sheet1.Range("F1")
End If
Next cel

With Sheet1.Rows("2:65536")
.AutoFilter

.AutoFilter Field:=6, Criteria1:=Sheet1.Range("F1")
End With
Application.ScreenUpdating = True
End Sub

Bỏ lọc:


Sub UnFilterMe()
Application.ScreenUpdating = False
If Sheet1.AutoFilterMode Then
Sheet1.Cells.AutoFilter
Sheet1.Range("f1", Sheet1.Range("f65536").End(xlUp)).Clear
' Sheet1.Range("d1").Clear
End If
Application.ScreenUpdating = True
End Sub


Bạn tham khảo thêm file nhe.

huuthang_bd
25-09-09, 08:33 PM
Dùng cách như bạn lọc ra hết những cell có tô màu, nó không phân biệt là màu gì.
Ở 2007 thì không nói, vì nó có sẵn chức năng lọc theo màu, nhưng 2003 trở về trước thì không.
Nếu dùng cột phụ để lọc ra theo màu bạn muốn thì phải thêm 1 function là lấy mã màu, sau đó so với điều kiện lọc là được.

Đâu cần phải như vậy đâu. Do ở đây tác giả chỉ muốn lọc ra những ô có tô màu. Nghĩa là chỉ phân biệt có tô màu hay không có tô màu thôi.

-Cho em hỏi có cách nào lọc các ô theo màu được không ạ?(chỉ lọc lấy ra những ô có tô màu nền thôi).
Còn nếu như muốn lấy theo từng màu thì dùng GET.CELL(63) thay cho GET.CELL(13) ở cột phụ là được.

phonglan99
26-09-09, 03:06 PM
Em cám ơn các anh đã giúp, nhưng em không biết gì về VBA cả, theo em nghĩ thì copy đoạn code của các anh sau đó vào menu tools\Macro\visual basic ..\ paste đoạn code của anh vào phải không ạ? Em đã xem file các anh gửi nhưng khi mở lên thì excel báo lỗi, mặc dù em đã chọn mức security là low rồi.
Mong các anh chỉ thêm giúp em

Hai Lúa Miền Tây
26-09-09, 03:13 PM
Em Cám ơnsau đó vào menu tools\Macro\visual basic ..\ paste đoạn code của anh vào phải không ạ
Bạn nhấn tổ hợp phím Alt+F11 để vào cửa sổ VBA, tiếp tục vào Insert\Module, rồi dán đoạn code đó vào.
Đóng cửa sổ VBA lại gán tên cho nút, chạy...

anhlong
05-10-09, 02:14 PM
Bạn nào chèn code lọc màu vào file đính kèm sau giúp tôi với.
Tôi làm hoài không được+-+-+-+

ptlong04x1
05-10-09, 03:49 PM
Bạn nào chèn code lọc màu vào file đính kèm sau giúp tôi với.
Tôi làm hoài không được+-+-+-+

Lọc cái gì đây bạn, ô nào có màu thì lọc ra hả? Tôi thấy bạn có màu mè đủ cho các loại rồi mà.

anhlong
05-10-09, 04:43 PM
Ý mình muốn bấm 1 phát là bảng tính chỉ còn lại mấy hàng có ô màu xanh lá ở cột E đó.
và lần lượt từng màu khác ... Mong các bạn giúp nhe

Bên shêt "KHO" ở cột "Tồn" mình định dạng các ô tự đổi thành màu đen khi = 0 . Mình cũng muốn bấm 1 phát thì bảng tính chỉ còn lại nhung74 hàng có ô màu đen đó, để mình biết mã hàng nào đã hết mà Mua thêm thì phải làm sao các bạn?

ChanhTQ@
05-10-09, 05:23 PM
Ý mình muốn bấm 1 phát là bảng tính chỉ còn lại mấy hàng có ô màu xanh lá ở cột E đó.
và lần lượt từng màu khác ... Mong các bạn giúp nhe
Bên shêt "KHO" ở cột "Tồn" mình định dạng các ô tự đổi thành màu đen khi = 0 . Mình cũng muốn bấm 1 phát thì bảng tính chỉ còn lại nhung74 hàng có ô màu đen đó, để mình biết mã hàng nào đã hết mà Mua thêm thì phải làm sao các bạn?

Nếu các ô đó được bôi màu bằng Condition Formattinng thì khó lắm;
Bạn đến đây: http://www.giaiphapexcel.com/forum/showthread.php?t=344 đọc những bài cuối xem có dễ để hiểu không đã.

(*) Tại 'Kho" sao ta không thể căn cứ vào mấy con số 0 đó mà cho ẩn hay hiện các dòng nhỉ?!?

anhlong
05-10-09, 05:39 PM
Cảm ơn bạn đã nhắc , shêt "KHO" đã lọc = autofilter rất đơn giản.

Còn bên shêt "BAN" không phải màu của Condition Formattinng ,mình thấy có các bạn lọc màu được, nhưng mình vẫn chưa làm được. Mong giúp đở.

ptlong04x1
05-10-09, 08:50 PM
Cảm ơn bạn đã nhắc , shêt "KHO" đã lọc = autofilter rất đơn giản.

Còn bên shêt "BAN" không phải màu của Condition Formattinng ,mình thấy có các bạn lọc màu được, nhưng mình vẫn chưa làm được. Mong giúp đở.

Tôi thấy trong cột dữ liệu của bạn, các màu nó gắn liền với "Trả hết", "Nợ", vậy sao không Autofilter với các giá trị đó cho xong, nó cũng ra từng màu tương ứng vì các ô cùng giá trị bạn đã gán màu giống nhau rồi, mỗi Click đổi màu 1 phát bằng VBA thì qủa thật vẫn chưa nghĩ ra.

ChanhTQ@
06-10-09, 06:03 AM
. . .Mỗi Click đổi màu 1 phát bằng VBA thì qủa thật vẫn chưa nghĩ ra.

Cũng có cách làm thôi:

* Nghe chừng Macro4 làm được;

* Thưc hiiện các bước:
- Cho hiện hết các dòng;
- Tạo vòng lặp duyệt toàn bộ records, nếu không phải màu ưng í thì cho ẩn dòng đó đi
(Chắc rằng chậm hơn các kiểu lọc nhiều & chỉ là biện pháp cuối cùng, một khi không có biện pháp nào khả dĩ hơn :-= --=0)

huuthang_bd
06-10-09, 10:45 AM
Cảm ơn bạn đã nhắc , shêt "KHO" đã lọc = autofilter rất đơn giản.

Còn bên shêt "BAN" không phải màu của Condition Formattinng ,mình thấy có các bạn lọc màu được, nhưng mình vẫn chưa làm được. Mong giúp đở.
Sao không dùng GET.CELL(63) như bài #10? Làm thử một file mẫu nhé. Click vào màu nào ở cột A sẽ lọc theo màu đó.

anhlong
06-10-09, 04:37 PM
Cái này khó quá bạn HuuThang ơi. bấm lọc ròi không bít làm sao trả lại?
Thấy mấy bạn lọc màu = cột phụ ,hay = nút bấm VBA gì đó hay quá.
Mấy bạn biết giúp mình thử trực tiếp lên file đính kèm trên dùm nhe.

huuthang_bd
06-10-09, 04:39 PM
Cái này khó quá bạn HuuThang ơi. bấm lọc ròi không bít làm sao trả lại?
Thấy mấy bạn lọc màu = cột phụ ,hay = nút bấm VBA gì đó hay quá.
Mấy bạn biết giúp mình thử trực tiếp lên file đính kèm trên dùm nhe.
Thì cái này dùng cột phụ đó. Vẫn là AutoFilter nên muốn trả lại thì xả Filter ở cột phụ ra. Có gì khó khăn đâu.

ndu96081631
06-10-09, 07:35 PM
Chào các anh chị,
-Cho em hỏi có cách nào lọc các ô theo màu được không ạ?(chỉ lọc lấy ra những ô có tô màu nền thôi).
-Em muốn loại bỏ các khoản trắng phía sau các chuỗi text thì phải làm sao a?
Rất mong các anh chị chỉ giúp
em xin cám ơn.
Về vấn đề Filter theo màu ta có thể dùng nhiều cách ---> Với Excel 2007 thì chức năng này là sẳn có, cứ thể mà dùng
Với Excel 2003 nếu dùng thường xuyên, ta có thể cài Add-In này vào máy:
http://www.rondebruin.nl/easyfilter.htm
Sau khi cài xong, trong mau Data\Filter sẽ xuất hiện mục con EasyFilter ---> Khám phá thử, Cũng khá hay đấy!

anhlong
08-10-09, 10:23 PM
Cảm ơn bạn
http://www.giaiphapexcel.com/forum/member.php?u=61139

Add-in này http://www.rondebruin.nl/easyfilter.htm

hay lắm mình lọc màu rất như ý , có điều phải click nhiều lần chuột (vào Data - Filter - Easyfilter - Color - Fiter interior Color - Go)

ndu96081631
09-10-09, 07:38 AM
Cảm ơn bạn
http://www.giaiphapexcel.com/forum/member.php?u=61139

Add-in này http://www.rondebruin.nl/easyfilter.htm

hay lắm mình lọc màu rất như ý , có điều phải click nhiều lần chuột (vào Data - Filter - Easyfilter - Color - Fiter interior Color - Go)
Cái này bạn tự mình tùy biến có khó khăn gì đâu ---> Muốn nhanh thì "lôi cổ" thằng EasyFilter ra ngoài luôn

anhlong
09-10-09, 12:09 PM
Sao không dùng GET.CELL(63) như bài #10? Làm thử một file mẫu nhé. Click vào màu nào ở cột A sẽ lọc theo màu đó.

Bạn giúp mình chèn code GET.CELL đó vào file đính kèm SoMuaBan-11-2009.rar của mình dùm nhe. Cảm ơn bạn nhiều.

huuthang_bd
09-10-09, 01:03 PM
Bạn giúp mình chèn code GET.CELL đó vào file đính kèm SoMuaBan-11-2009.rar của mình dùm nhe. Cảm ơn bạn nhiều.
Bạn xem file. Làm cũng tương tự như trong file ví dụ của tôi thôi.

anhlong
11-10-09, 10:14 PM
Cám ơn huuthang rất nhìu.
Nhưng Ngoài cách tắt Marco Mà mình muốn chỉnh sữa (như đổi màu khác) các ô ở cột A đó thì làm sao hả bạn ? (dùng chuột hay phím mũi tên đều không được )

Mình thấy bấm lọc rồi có thể chỉnh sữa , nhưng có cách nào nhanh hơn không ? (tạo nút tắt mở chẳng hạn)
Nếu có thể nhờ bạn giúp dùm.

huuthang_bd
12-10-09, 08:12 AM
Cám ơn huuthang rất nhìu.
Nhưng Ngoài cách tắt Marco Mà mình muốn chỉnh sữa (như đổi màu khác) các ô ở cột A đó thì làm sao hả bạn ? (dùng chuột hay phím mũi tên đều không được )

Mình thấy bấm lọc rồi có thể chỉnh sữa , nhưng có cách nào nhanh hơn không ? (tạo nút tắt mở chẳng hạn)
Nếu có thể nhờ bạn giúp dùm.
Bạn có thể tạo một Checkbox để bật tắt chế độ lọc. Ví dụ như trong file. Checkbox ở ô C2.

the_wind
12-10-09, 09:33 AM
Được mà bạn. Bạn chọn sort by color đó bạn.
Cái này dùng thế nào hả bạn? Mình dùng Excel 2003

huuthang_bd
12-10-09, 10:21 AM
Cái này dùng thế nào hả bạn? Mình dùng Excel 2003
Excel 2003 thì làm gì có Sort by color mà dùng.

MNThao
23-08-10, 11:50 AM
Nếu các ô đó được bôi màu bằng Condition Formattinng thì khó lắm;
Bạn đến đây: http://www.giaiphapexcel.com/forum/showthread.php?t=344 đọc những bài cuối xem có dễ để hiểu không đã.

(*) Tại 'Kho" sao ta không thể căn cứ vào mấy con số 0 đó mà cho ẩn hay hiện các dòng nhỉ?!?

Các bác làm ơn cho em hỏi chút. E dùng cái add in lọc màu của các bác thì ok rồi. Nhưng cái add in này nó không chạy tự động. Có nghĩa là khi e thay đổi màu trong ô thì cần phải chạy lại hàm. Có cách nào để nó tự động cập nhật thông tin mà không cần chạy lại hàm khôgn hả các bác, nếu tự động chạy thì mới chuẩn xác đc chứ mỗi lần thay đổi lại phải chạy lại hàm thì nguy cơ sai là dễ lắm.
Mong các đại ca giúp sức.

Ếch Xanh
23-08-10, 01:01 PM
Các bác làm ơn cho em hỏi chút. E dùng cái add in lọc màu của các bác thì ok rồi. Nhưng cái add in này nó không chạy tự động. Có nghĩa là khi e thay đổi màu trong ô thì cần phải chạy lại hàm. Có cách nào để nó tự động cập nhật thông tin mà không cần chạy lại hàm khôgn hả các bác, nếu tự động chạy thì mới chuẩn xác đc chứ mỗi lần thay đổi lại phải chạy lại hàm thì nguy cơ sai là dễ lắm.
Mong các đại ca giúp sức.

Quan điểm của tôi là không muốn nó động, bởi mỗi lần nó tự động làm cho màn hình bị chớp chớp khó chịu và mình làm dang dỡ enter nó chạy mất tiêu cái mình đang làm. Theo tôi thì bạn đặt một nút lệnh trên sheet của bạn, làm xong công việc nhấp cái "kịt" là OK. Thêm nữa là mỗi lần thay đổi gì đó thì Undo không có được!

Nếu bạn muốn tự động chạy trong sheet thì viết code này tại sheet đó:

Private Sub Worksheet_Change(ByVal Target As Range)
YourMacro
End Sub

Chúc vui.

phihndhsp
06-08-11, 10:16 PM
Trong bảng dữ liệu có những ô có màu, không nhất thiết phải cùng cột có cách nào lọc lấy ra được những hàng mà trên hàng đó có được ít nhất 1 ô có màu vàng được?, Yêu cầu đã ghi trong file và có mẫu dữ liệu sẵn, xin cảm ơn

http://www.mediafire.com/?rvammvzcqlawz7r

ChanhTQ@
06-08-11, 10:30 PM
Trong bảng dữ liệu có những ô có màu, không nhất thiết phải cùng cột có cách nào lọc lấy ra được những hàng mà trên hàng đó có được ít nhất 1 ô có màu vàng được?, Yêu cầu đã ghi trong file và có mẫu dữ liệu sẵn, xin cảm ơn http://www.mediafire.com/?rvammvzcqlawz7r
Mình chỉ có E2003 nên nói theo E2003: Nguyên tắc là được, chỉ lâu thôi:
Duyệt qua hết các ô có dử liệu, ô nào có màu nên ưng í thì chép sang trang cần đến;
Để đến nhanh các ô có màu thì có 1 số cách fụ trợ, như
Ta biết chắc chúng là ~ ô chứa công thức (Hay ô chứa số liệu), ta dùng như fương thức Goto để loại các ô không dính dáng đến.
/-)ại loại là vậy, cứ xem như ếch ngồi đáy giếng, bàn về E2007, khì, khì,. . . . . !

phihndhsp
06-08-11, 10:38 PM
Đây là file E2003 anh xem dùng em nha, cảm ơn anh nhiều
http://www.mediafire.com/?9mphtbis9ftymbk

HYen17
07-08-11, 06:04 AM
Option Explicit
Sub gpeFilterRowsForBColor()
Dim Jj As Long, Rws As Long
Dim Cls As Range, Rng As Range, fRng As Range, Clls As Range
Set Rng = Sheet1.UsedRange
Rws = Rng.Rows.Count: Rng(1).Resize(Rws).Font.ColorIndex = 1
On Error Resume Next For Each Cls In Rng(1).Resize(Rws) [/left]
Set fRng = Cls.EntireRow.SpecialCells(xlCellTypeFormulas, 3)
For Each Clls In fRng
If Clls.Interior.ColorIndex > 2 Then
Cls.Font.ColorIndex = 3 : Exit For
End If
Next Clls
Next Cls
End Sub

phihndhsp
07-08-11, 01:34 PM
Em vẫn không thấy nó lọc ra những hàng mà có ít nhất 1 ô có màu thầy ơi.

HYen17
07-08-11, 02:30 PM
Em vẫn không thấy nó lọc ra những hàng mà có ít nhất 1 ô có màu thầy ơi. macro đó chưa lọc mà, nó chỉ mới sơn màu font ô đầu hàng cho ~ hàng thỏa mà thôi!