Giúp em tự động lọc dữ liệu loại bỏ ô trống và ô chứa số 0 (1 người xem)

[Dauthivan]

Công việc của em rất cần đến việc lọc dữ liệu, cụ thể thế này ạh:
Em có sheet!Dau vao trong đó cột A của nó được nhập theo số phát sinh. Em muốn làm sao tại Sheet!Dau ra nó tự động cập nhật lấy dữ liệu từ Sheet!Dauvao, tuy nhiên nó bỏ qua không lấy ô trống hoặc ô chứa số 0. Em không biết phải làm sao bây giờ, kính mong được sự giúp đỡ

 

[MinhCong]

Bạn dùng tạm code sau vậy (đặt trong sheet Dau ra):

Private Sub Worksheet_Activate()
Dim tmp(1 To 65000, 1 To 1)
Dim jR As Long, iR As Long, enR As Long
Range("A:A").Clear
enR = Sheets("Dau vao").Range("A65500").End(xlUp).Row
    For i = 1 To enR
        With Sheets("Dau vao").Cells(i, 1)
            If .Value <> 0 And .Value <> "" Then
                jR = jR + 1
                tmp(jR, 1) = .Value
            End If
        End With
    Next
Range("A1").Resize(jR) = tmp
End Sub

 

[ndu96081631]

Bạn dùng tạm code sau vậy (đặt trong sheet Dau ra):

Private Sub Worksheet_Activate()
Dim tmp(1 To 65000, 1 To 1)
Dim jR As Long, iR As Long, enR As Long
Range("A:A").Clear
enR = Sheets("Dau vao").Range("A65500").End(xlUp).Row
    For i = 1 To enR
        With Sheets("Dau vao").Cells(i, 1)
            If .Value <> 0 And .Value <> "" Then
                jR = jR + 1
                tmp(jR, 1) = .Value
            End If
        End With
    Next
Range("A1").Resize(jR) = tmp
End Sub

AutoFilter theo điều kiện >0 rồi copy qua thôi chứ cần gì vòng lập ta?
Ngoài ra, dùng mảng kiểu này vẫn chưa tăng tốc được đâu nha... Lý do vì code vẫn chưa "thoát ly" được với cell

 

[MinhCong]

AutoFilter theo điều kiện >0 rồi copy qua thôi chứ cần gì vòng lập ta?

Lúc đầu Em cũng định dùng AutoFilter nhưng sợ trong dãy số đó nó có số âm nữa nên chuyển sang vòng lặp.

 

[ndu96081631]

Lúc đầu Em cũng định dùng AutoFilter nhưng sợ trong dãy số đó nó có số âm nữa nên chuyển sang vòng lặp.

Số âm có sao đâu --> AutoFilter theo điều kiện Custom <> 0 và khác "" là được rồi
Riêng về vòng lập dùng mảng, nên vầy mới là TĂNG TỐC

Private Sub Worksheet_Activate()
  Dim tmp, Arr(), i As Long, jR As Long
  Range("A:A").Clear
  With Sheets("Dau vao")
    tmp = .Range(.[A1], .[A65556].End(xlUp)).Value
  End With
  For i = LBound(tmp) To UBound(tmp)
    If tmp(i, 1) <> 0 And tmp(i, 1) <> "" Then
      jR = jR + 1
      ReDim Preserve Arr(1 To jR)
      Arr(jR) = tmp(i, 1)
    End If
  Next
  Range("A1").Resize(jR) = WorksheetFunction.Transpose(Arr)
End Sub

Từ lúc bắt đầu vòng lập trở xuống, code hoạt động hoàn toàn với mảng, hổng dính gì đến cell, range cả
(viết ở mức đơn giản nên code này vẫn chưa phải là hoàn hảo nhất --> Còn phải cải tiến thêm)

 

[Dauthivan]

AutoFilter theo điều kiện >0 rồi copy qua thôi chứ cần gì vòng lập ta?
Ngoài ra, dùng mảng kiểu này vẫn chưa tăng tốc được đâu nha... Lý do vì code vẫn chưa "thoát ly" được với cell

Cảm ơn các anh rất nhiều, nhưng trình độ dùng hàm của em kém lắm, em muốn tự mình học hỏi, giải quyết bài toán, em đã được đọc nhiều bài của anh 96081631 trên diễn đàn có sử dụng Name rồi mà sao không ứng dụng được, hôm nay rỗi em muốn tự mình mày mò làm thử một số bài để phục vụ công tác quyết toán trong năm, mong được anh giúp cho.

 

[MinhCong]

Cảm ơn các anh rất nhiều, nhưng trình độ dùng hàm của em kém lắm, em muốn tự mình học hỏi, giải quyết bài toán, em đã được đọc nhiều bài của anh 96081631 trên diễn đàn có sử dụng Name rồi mà sao không ứng dụng được, hôm nay rỗi em muốn tự mình mày mò làm thử một số bài để phục vụ công tác quyết toán trong năm, mong được anh giúp cho.

dạng bài này dùng hàm để lọc thì cũng đã nói nhiều trên diễn đàn rồi. Chỉ Bạn 1 cách dùng thêm 1 cột phụ phía trước sau đó dùng VLOOKUP để Bạn nghiên cứu nhé!.

 

[Dauthivan]

dạng bài này dùng hàm để lọc thì cũng đã nói nhiều trên diễn đàn rồi. Chỉ Bạn 1 cách dùng thêm 1 cột phụ phía trước sau đó dùng VLOOKUP để Bạn nghiên cứu nhé!.

Anh có thể giúp em theo cách dùng offset và các hàm khác được không ạh, hàm Vlookup em đã quen dùng nhưng khả năng của nó em thấy trên diễn đàn bảo không hiệu quả bằng match và Index, em chưa hiểu lắm các hàm này, rất mong nhận được sự gíup đỡ từ diễn đàn để em có thể thao tác được.

 

[Phanhanhdai]

Anh có thể giúp em theo cách dùng offset và các hàm khác được không ạh, hàm Vlookup em đã quen dùng nhưng khả năng của nó em thấy trên diễn đàn bảo không hiệu quả bằng match và Index, em chưa hiểu lắm các hàm này, rất mong nhận được sự gíup đỡ từ diễn đàn để em có thể thao tác được.

Bài này của bạn giống hệt bài của thày ndu96081631, mình post lên cho bạn tham khảo. Quả thực đọc xong bài của thày mình rất phấn khởi vì đã khám phá ra được cách dùng thật hữu ích. Mình xin cảm ơn các thành viên đặc biệt là thày ndu96081631 và anh Võ Tuấn Kiệt (ca_dafi) là những người giúp mình rất nhiều.

 

[Dauthivan]

Bài này của bạn giống hệt bài của thày ndu96081631, mình post lên cho bạn tham khảo. Quả thực đọc xong bài của thày mình rất phấn khởi vì đã khám phá ra được cách dùng thật hữu ích. Mình xin cảm ơn các thành viên đặc biệt là thày ndu96081631 và anh Võ Tuấn Kiệt (ca_dafi) là những người giúp mình rất nhiều.

Vâng! Em cũng đang cố gắng để học hỏi; thật ra bài của thày có khác đôi chút bài của em, nhưng vấn đề là em đọc nhưng không hiểu, ví dụ bài của thày (bài Unique_List_01) em có tách nhỏ công thức ra để hiểu thành phần của ví dụ em, tại sao tại ô L9 em đánh =MATCH(REPT("Z";255);OFFSET(Sheet1!$C$7;;;1000) thì lại cho kết quả là 15, em kéo xuống các ô khác cũng cho kết quả là 15. Em thắc mắc là tại sao MATCH(REPT("Z";255);OFFSET(Sheet1!$C$7;;;1000) =15. Rất mong được các bác hồi âm.

 

[ndu96081631]

Bài này của bạn giống hệt bài của thày ndu96081631, mình post lên cho bạn tham khảo. Quả thực đọc xong bài của thày mình rất phấn khởi vì đã khám phá ra được cách dùng thật hữu ích. Mình xin cảm ơn các thành viên đặc biệt là thày ndu96081631 và anh Võ Tuấn Kiệt (ca_dafi) là những người giúp mình rất nhiều.

Thật ra cũng không giống đâu bạn! Bài mà bạn post lên là LỌC DUY NHẤT còn bài này là LỌC THEO 1 ĐIỀU KIỆN CHO TRƯỚC
---------------------

Vâng! Em cũng đang cố gắng để học hỏi; thật ra bài của thày có khác đôi chút bài của em, nhưng vấn đề là em đọc nhưng không hiểu, ví dụ bài của thày (bài Unique_List_01) em có tách nhỏ công thức ra để hiểu thành phần của ví dụ em, tại sao tại ô L9 em đánh =MATCH(REPT("Z";255);OFFSET(Sheet1!$C$7;;;1000) thì lại cho kết quả là 15, em kéo xuống các ô khác cũng cho kết quả là 15. Em thắc mắc là tại sao MATCH(REPT("Z";255);OFFSET(Sheet1!$C$7;;;1000) =15. Rất mong được các bác hồi âm.

Bài của bạn không thể dùng MATCH(REPT("Z";255) được đâu, nó chỉ áp dụng được với dữ liệu là TEXT, còn file của bạn là NUMBER cơ mà
Giờ ta bắt đầu làm bài của bạn bằng công thức nhé. Nói trước: nếu bạn bảo đảm dữ liệu của bạn toàn là SỐ thì ta làm như sau:
- Tại sheet Dau vao, đặt con trỏ chuột ở dòng 1 rồi đặt 2 name:

Data =OFFSET('Dau vao'!$A$1,,,MATCH(9.99999999999999E+307,'Dau vao'!$A:$A),)

Pos =IF(Data=0,"",ROW(INDIRECT("1:"&ROWS(Data))))

- Tại Sheet Dau ra, cell A1 gõ công thức:

=IF(ROWS($1:1)>COUNT(Pos),"",INDEX(Data,SMALL(Pos,ROWS($1:1))))

Về mấy công thức MATCH(REPT("Z";255) và MATCH(9.99999999999999E+307, $A:$A) bạn tham khảo thêm tại đây:
http://www.giaiphapexcel.com/forum/showthread.php?7669-Những-công-thức-độc-đáo&
Nếu dữ liệu của bạn vừa TEXT vừa NUMBER thì hơi khó 1 chút, phải phối hợp cả 2 cái MATCH(REPT("Z";255) và MATCH(9.99999999999999E+307, $A:$A) vào trong name Data
Nói thêm: Với bất cứ bài toán trích lọc nào thì phần việc chính của chúng ta là tạo ra 2 name:
- Name 1: Xác định vùng dữ liệu động (trong bài này là name Data)
- Name 2: Xác định vị trí của những cell thỏa mản điều kiện (trong bài này là name Pos)
Cuối cùng là dùng INDEX phối hợp với hàm SMALL để lấy ra dữ liệu cần thiết

 

[Phanhanhdai]

Xin thày chỉ bảo cho cách cả số và chữ lẫn lộn tại dữ liệu đầu vào ah

Thật ra cũng không giống đâu bạn! Bài mà bạn post lên là LỌC DUY NHẤT còn bài này là LỌC THEO 1 ĐIỀU KIỆN CHO TRƯỚC
---------------------

Bài của bạn không thể dùng MATCH(REPT("Z";255) được đâu, nó chỉ áp dụng được với dữ liệu là TEXT, còn file của bạn là NUMBER cơ mà

Dạ vâng, thảo nào tối qua em thắc mắc nếu đánh thêm một số (number) vào dãy của thày thì công thức không update được. Như vậy nếu cả số và chứ lẫn lộn ta phải làm thế nào ah?

 

[ndu96081631]

Dạ vâng, thảo nào tối qua em thắc mắc nếu đánh thêm một số (number) vào dãy của thày thì công thức không update được. Như vậy nếu cả số và chứ lẫn lộn ta phải làm thế nào ah?

Thì phối hợp cả 2 thằng:
MATCH(9.99999999999999E+307,$A:$A) ---> Lấy vị trị dòng cuối cùng của cột A với dữ liệu là Number
MATCH(REPT("z",255),$A:$A) ---> Lấy vị trị dòng cuối cùng của cột A với dữ liệu là Text
Vậy với dữ liệu hổn hợp, ta dùng hàm MAX(MATCH(9.99999999999999E+307,$A:$A),MATCH(REPT("z",255),$A:$A))
Tức Name Data sẽ sửa lại thành:

=OFFSET('Dau vao'!$A$1,,,MAX(MATCH(9.99999999999999E+307,'Dau vao'!$A:$A),MATCH(REPT("z",255),'Dau vao'!$A:$A)),)

Ngoài ra, với dữ liệu thông thường (ta thường dùng), bạn có thể thay MATCH(REPT("z",255),$A:$A) thành MATCH("ÿ",$A:$A) cũng không có vấn đề gì

 

[Dauthivan]

Hichic...thật khủng khiếp quá,nhìn công thức xong là em cứ thấy hoa hết cả mắt, chắc tại IQ của em thấp quá, vì em chưa hiểu nên mong được thày giải thích hộ em đoạn ROW(INDIRECT("1:"&ROWS(Data))là như thế nào ah?
Em đọc các công thức dưới dạng đơn giản, ban đầu thấy bình thường mà khi kết hợp vào sao trông nó rắc rối thế, không biết là sau bao nhiêu thời gian nữa em mới tự mình làm được

 

[ndu96081631]

Hichic...thật khủng khiếp quá,nhìn công thức xong là em cứ thấy hoa hết cả mắt, chắc tại IQ của em thấp quá, vì em chưa hiểu nên mong được thày giải thích hộ em đoạn ROW(INDIRECT("1:"&ROWS(Data))là như thế nào ah?
Em đọc các công thức dưới dạng đơn giản, ban đầu thấy bình thường mà khi kết hợp vào sao trông nó rắc rối thế, không biết là sau bao nhiêu thời gian nữa em mới tự mình làm được

Vầy đi cho dễ!
Bạn mở 1 file mới, bôi đen từ A1:A10, xong gõ vào thanh Formula công thức =ROW(1:10) rồi bấm Ctrl + Shift + Enter
Thí nghiệm tiếp: Bôi đen B1:B10 rồi gõ vào thanh Formula công thức =ROW(INDIRECT("1:10")) rồi bấm Ctrl + Shift + Enter
Thí nghiệm tiếp: Gõ số 10 vào cell H1, xong bôi đen C1:C10 rồi gõ vào thanh Formula công thức =ROW(INDIRECT("1:"&H1)) rồi bấm Ctrl + Shift + Enter
----------------
Vài thí nghiệm đơn giản, hy vọng bạn "ngộ" ra được vấn đề