Nhờ các bạn giúp minh viết hàm lọc dữ liệu với? (1 người xem)

[ngotuanthuy]

hiện mình đang có bảng dữ liệu, bao gồm một sheet trong đó có các cột STT, Tên tỉnh,TênNV,chi tiêu đặt ra (đạt hay không đạt)

[TABLE="width: 582"]
[TR]
[TD][/TD]
[TD]STT
[/TD]
[TD]tên tỉnh
[/TD]
[TD]tên nv
[/TD]
[TD]chỉ tiêu
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1
[/TD]
[TD]hải phòng
[/TD]
[TD]nguyễn văn a
[/TD]
[TD]đạt
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2
[/TD]
[TD]hải phòng
[/TD]
[TD]nguyễn văn b
[/TD]
[TD]ko đạt
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3
[/TD]
[TD]thái bình
[/TD]
[TD]nguyễn thị c
[/TD]
[TD]không đạt
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4
[/TD]
[TD]thái bình
[/TD]
[TD]nguyễn văn d
[/TD]
[TD]đạt
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5
[/TD]
[TD]thái bình
[/TD]
[TD]nguyễn văn e
[/TD]
[TD]đạt
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]

mình muốn tính xem số lượng đạt trên từng tỉnh là bao nhiêu (VD số lượng đạt trên tỉnh A là bao nhiêu, tỉnh B là bao nhiêu) nhưng không biết phải viết hàm như thế nào, nếu dùng filter lọc theo từng tỉnh thì sẽ mất rất nhiều thời gian, vì tất cả dữ liệu thì sẽ có tất cả 63 tỉnh, mỗi tỉnh bao gồm rất nhiều nhân viên, có ai có thể bày giúp mình một thuật toán hoặc viết hàm như thế nào đó để mình có làm việc này đơn giản hơn không ạ? mình cảm ơn các bạn rất nhiều!

 

[Lê Duy Thương]

hiện mình đang có bảng dữ liệu, bao gồm một sheet trong đó có các cột STT, Tên tỉnh,TênNV,chi tiêu đặt ra (đạt hay không đạt)


mình muốn tính xem số lượng đạt trên từng tỉnh là bao nhiêu (VD số lượng đạt trên tỉnh A là bao nhiêu, tỉnh B là bao nhiêu) nhưng không biết phải viết hàm như thế nào, nếu dùng filter lọc theo từng tỉnh thì sẽ mất rất nhiều thời gian, vì tất cả dữ liệu thì sẽ có tất cả 63 tỉnh, mỗi tỉnh bao gồm rất nhiều nhân viên, có ai có thể bày giúp mình một thuật toán hoặc viết hàm như thế nào đó để mình có làm việc này đơn giản hơn không ạ? mình cảm ơn các bạn rất nhiều!

bạn có thể đưa file lên được không ?.

 

[hophoang]

Bạn thử xem cái này có đúng ý bạn không

 

[ngotuanthuy]

Minh gửi lại bảng số liệu chi tiết để các bạn xem và giúp đỡ mình với ạ?

Minh gửi lại bảng số liệu chi tiết để các bạn xem và giúp đỡ mình với , cảm ơn các bạn rất nhiều?

 

[Ba Tê]

Minh gửi lại bảng số liệu chi tiết để các bạn xem và giúp đỡ mình với , cảm ơn các bạn rất nhiều?

Xem thử file này đi:
Tất cả các ô từ E7:F40 đều là công thức !

 

[hoamattroicoi]

Nếu không thích dùng name thì cột Mã Tỉnh ( Cột F) dùng CT này nhé :

=LOOKUP(REPT("z",255),CHOOSE({1;2},"",INDEX($A$7:$A$1000,MATCH(TRUE,INDEX(COUNTIF($F$6:F6,$A$7:$A$1000)=0,0),0))))

Cho 1000 dòng và dữ liệu liên tục!
Và dùng Sumproduct như bác Bate nhé!

Xem file đính kèm để hiểu rõ hơn:

 

[vodoi2x]

Nếu không thích dùng name thì cột Mã Tỉnh ( Cột F) dùng CT này nhé :

=LOOKUP(REPT("z",255),CHOOSE({1;2},"",INDEX($A$7:$A$1000,MATCH(TRUE,INDEX(COUNTIF($F$6:F6,$A$7:$A$1000)=0,0),0))))

Công thức hay quá, ngắn gọn,

Bạn sử dụng Lookup rất hay, thật uyển chuyển,

Nhưng phân tích ra để hiểu thì không phải đơn giản, cám ơn HMTc

 

[hoamattroicoi]

Công thức hay quá, ngắn gọn,

Bạn sử dụng Lookup rất hay, thật uyển chuyển,

Nhưng phân tích ra để hiểu thì không phải đơn giản, cám ơn HMTc

Cũng nhờ tiếng hát của anh hàng xóm cạnh nhà tớ cứ văng vẳng bên tai làm tớ cứ thao thức cả đêm không ngủ mò lên diễn đàn tìm bài mãi mới chen vô được cái Topic này

Cám ơn tiếng hát của anh hàng xóm nhà tớ đi nhé!!!! He he (Xì pam tý)

 

[phihndhsp]

Công thức hay quá, ngắn gọn,

Bạn sử dụng Lookup rất hay, thật uyển chuyển,

Nhưng phân tích ra để hiểu thì không phải đơn giản, cám ơn HMTc

Cho bạn thêm công thức nữa nè, cách làm này của anh bebo tôi học theo thấy cũng hay lắm
F7=INDEX($A$7:$A$40,MATCH(0,COUNTIF(F$6:$F6,$A$7:$A$40),0),0)

 

[bebo021999]

để trích lọc DS duy nhất nhỉ, công thức của hoamattroicoi :

=LOOKUP(REPT("z",255),CHOOSE({1;2},"",INDEX($A$7:$A$1000,MATCH(TRUE,INDEX(COUNTIF($F$6:F6,$A$7:$A$1000)=0,0),0))))

có khác gì với công thức hay dùng:

=IF(ROWS($1:1)<=SUMPRODUCT(1/COUNTIF($A$7:$A$40,$A$7:$A$40)),INDEX($A$7:$A$40,MATCH(0,COUNTIF($I$6:I6,$A$7:$A$40),0)),"")

không nhỉ?
Dùng LOOKUP có khắc phục được nhược điểm của COUNTIF, nghĩa là có trích lọc được danh sách ảo không?

 

[hoamattroicoi]

Lookup dùng trong trường hợp này không phải dùng để lọc duy nhất mà nó chỉ dùng để Remove lỗi thôi anh ạ.

Anh có thể dùng Match thay cho countif cho công thức trên như thề này, em chưa thử test xem nó có làm việc được với mảng ảo không?

=LOOKUP(REPT("z",255),CHOOSE({1;2},"",INDEX($A$7:$A$1000,MATCH(TRUE,INDEX(ISNA(MATCH($A$7:$A$1000,$F$6:F6,0)),0),0))))

Cái hay nữa là không cần ctrl+shift+Enter mà chạy vẫn mượt!

 

[ndu96081631]

Vậy mới là ngắn nhất:

=IF(AND(COUNTIF($F$6:F6,$A$7:$A$40)),"",INDEX($A$7:$A$40,MATCH(0,COUNTIF($F$6:F6,$A$7:$A$40),0)))

Nói thêm: mấy công thức dạng này vẫn ra kết quả không chính xác trong trường hợp dữ liệu gốc có cell rổng
Tuy nhiên, công thức của tôi ra kết quả tương đối nhất (kể cả trường hợp dữ liệu có cell rổng)
---------------------------------------
Việc lọc Unique có hàng đống cách, trong đó cách dùng công thức là thứ dở ẹc nhất, kế đến là VBA... Phương pháp tuyệt hảo nhất là PivotTable

 

[hoamattroicoi]

Vậy mới là ngắn nhất:

=IF(AND(COUNTIF($F$6:F6,$A$7:$A$40)),"",INDEX($A$7:$A$40,MATCH(0,COUNTIF($F$6:F6,$A$7:$A$40),0)))

Nói thêm: mấy công thức dạng này vẫn ra kết quả không chính xác trong trường hợp dữ liệu gốc có cell rổng
Tuy nhiên, công thức của tôi ra kết quả tương đối nhất (kể cả trường hợp dữ liệu có cell rổng)
---------------------------------------

Công thức này của thầy chỉ mới Remove được lỗi #N/A thôi, dữ liệu gốc có cell rỗng ---> công thức cho kết quả sai

Sao không thế này trong trường hợp có cell rỗng trong dữ liệu gốc :

=INDEX($A$7:$A$40;MATCH(0;($A$7:$A$40="")+(COUNTIF($F$6:F6;$A$7:$A$40));0))

 

[vodoi2x]

Vậy mới là ngắn nhất:

=IF(AND(COUNTIF($F$6:F6,$A$7:$A$40)),"",INDEX($A$7:$A$40,MATCH(0,COUNTIF($F$6:F6,$A$7:$A$40),0)))

Nói thêm: mấy công thức dạng này vẫn ra kết quả không chính xác trong trường hợp dữ liệu gốc có cell rổng
Tuy nhiên, công thức của tôi ra kết quả tương đối nhất (kể cả trường hợp dữ liệu có cell rổng)
---------------------------------------
Việc lọc Unique có hàng đống cách, trong đó cách dùng công thức là thứ dở ẹc nhất, kế đến là VBA... Phương pháp tuyệt hảo nhất là PivotTable

Bác Ndu hồi này thích ngoại lai nhỉ (vợ hai VBA) hehehe

Đọc lại mấy bài xưa trước thấy, xưa kia bác nói thích công thức nhất dễ hiểu nhanh chóng

Nói vậy thôi, tùy phạm vi ứng dụng rồi, ít số liệu khoảng vài trăm dòng thì dùng công thức cũng được và lại dễ hiệu chỉnh, linh động, hiệu quả. Thích nhanh thì chuyển thành value

và quan trọng người dùng thích dùng cái gì người ta có thể hiểu và quản lý được , thú thật VBA còn rối lắm và phụ thuộc vào người viết code - chả nhẽ mỗi khi mắc lại chạy lên GPE

 

[ndu96081631]

Công thức này của thầy chỉ mới Remove được lỗi #N/A thôi, dữ liệu gốc có cell rỗng ---> công thức cho kết quả sai

Sao không thế này trong trường hợp có cell rỗng trong dữ liệu gốc :

=INDEX($A$7:$A$40;MATCH(0;($A$7:$A$40="")+(COUNTIF($F$6:F6;$A$7:$A$40));0))

Có thấy đúng gì đâu trời?
Cho file lên thử xem
-----------------------------

Bác Ndu hồi này thích ngoại lai nhỉ (vợ hai VBA) hehehe
Đọc lại mấy bài xưa trước thấy, xưa kia bác nói thích công thức nhất dễ hiểu nhanh chóng

Chuyện thường ---> KHI XƯA TA BÉ TA NGU (Chưa biết VBA nên tưởng công thức là vô địch)
----------------------------

ít số liệu khoảng vài trăm dòng thì dùng công thức cũng được và lại dễ hiệu chỉnh, linh động, hiệu quả. Thích nhanh thì chuyển thành value
và quan trọng người dùng thích dùng cái gì người ta có thể hiểu và quản lý được , thú thật VBA còn rối lắm và phụ thuộc vào người viết code - chả nhẽ mỗi khi mắc lại chạy lên GPE

VBA khó, đồng ý! Thế còn PivoTable thì sao? Đừng nói là PivotTable lại KHÓ hơn mấy công thức phức tạp ở trên nha!
Ngoài ra: Bị rắc rối với VBA thì chạy lên GPE, thế đối với mấy công thức rắc rối ở trên thì... CHẠY ĐI ĐÂU?

 

[trungvdb]

Việc lọc Unique có hàng đống cách, trong đó cách dùng công thức là thứ dở ẹc nhất, kế đến là VBA... Phương pháp tuyệt hảo nhất là PivotTable

Thày làm bằng cách này cho em học theo với ah?

 

[hoamattroicoi]

Thày làm bằng cách này cho em học theo với ah?

Bạn tìm tài liệu về Pivot table của sư phụ Mỹ đọc nhé, ở ngay trang chủ đó - Quà tặng thành viên nhân dịp SN lần thứ 4 của GPE!

 

[trungvdb]

=IF(AND(COUNTIF($F$6:F6,$A$7:$A$40)),"",INDEX($A$7:$A$40,MATCH(0,COUNTIF($F$6:F6,$A$7:$A$40),0)))

Hôm nay học được sư phụ cách dùng Countif rất hay từ bài này và bài http://www.giaiphapexcel.com/forum/...ông-thức-tách-chuỗi-ký-tự&p=390926#post390926

---> Em thấy để học Excel, VBA, cứ chịu khó theo sau, đọc những bài thày Ndu giải là phương pháp nhanh tiến bộ nhất. Cảm ơn thày rất nhiều.

 

[ndu96081631]

Thày làm bằng cách này cho em học theo với ah?

Mọi chuyện chỉ là: Dùng chuột nắm, kéo và thả... Muốn tổng hợp theo tiêu chí gì, kéo field vào vị trí phù hợp là được rồi
Xem video clip dưới đây để thấy dùng PivotTable chỉ như 1 cuộc dạo chơi... Ẹc... Ẹc...

[video=youtube;9uhMi8p9Qn8]http://www.youtube.com/watch?v=9uhMi8p9Qn8&feature=youtu.be[/video]

 

[ndu96081631]

Tiếp theo là 1 vài tùy chỉnh sau đó (nếu muốn thay đổi hình thức report)
[video=youtube;kR0AudGc1bA]http://www.youtube.com/watch?v=kR0AudGc1bA&feature=youtu.be[/video]

Tóm lại: Chỉ việc kéo thả là xong