Tìm hiểu thuật toán trích lọc dữ liệu theo điều kiện

[bebo021999]

Trên diễn đàn hiện nay có rất nhiều bài viết về giải pháp trích lọc dữ liệu theo điều kiện, rất hiệu quả và chính xác. Tuy nhiên, đối với những người chưa dùng excel nhiều, để áp dụng vào thực tế của mình, vẫn còn những khoảng trống trong kiến thức, chưa thể hiểu hết những công thức, thuật toán, giải pháp mà các cao thủ đã sử dụng.
Ví dụ: công thức: =IF(ROWS($1:1)>COUNT(P),"",INDEX(Data,SMALL(P,ROWS($1:1)),1))
hoặc: =IF(OFFSET(Data,,2,,1)="y",ROW(INDIRECT("1:"&ROWS(Data))),"")
Nếu không thực sự hiểu hết cách sử dụng mềm dẻo những OFSET, ROW, SMALL hay INDEX, MATCH... bạn sẽ gặp rất nhiều khó khăn khi áp dụng giải bài tóan của mình. Thực sự không ai muốn bê nguyên xi công thức đó làm của mình, nếu không hiểu nó! Biết đâu đó, một ngày đẹp trời, sếp đùng đùng nổi giận vì bảng tính thuế phải nộp của công ty bị tính sai làm thiệt hại mất vài chục triệu!
Thực sự mình cũng mới gia nhập diễn đàn, mới tiếp xúc được với những cách dùng mới mẻ của các hàm, các giải pháp từ các cao thủ (mà không thể tìm thấy ở mục Help của Excel); nhiều công thức độc đáo mà anh Gate đọc được cũng phải giật thót mình ...
Nhiều bạn đã đặt câu hỏi nhờ giải thích chi tiết, một số vấn đề đã được giải đáp, tuy nhiên một số vấn đề rất khó để người giải thích giúp bạn hiểu được thấu đáo. Lý do là một số bạn không chịu bỏ công ra nghiên cứu, tìm hiểu thấu thấu đáo những cách sử dụng căn bản của các hàm, các thủ thuật ... (Có rất nhiều từ sách vở căn bản, từ mục Help ...) đôi khi gây ra sự khó chịu, nán chí, bất bình của người giải thích. (ví dụ hỏi : hàm if sử dụng như thế nào, vlookup có mấy tham số; hoặc không đọc kỹ, thực hiện theo hướng dẫn,...) Nên không trách dược đôi khi chán không muốn nói nữa ...

"Vui xuân kể chuyện dông dài", mình chỉ muốn mở một topic cho các bạn mới sử dụng excel, post lên đây những thắc mắc về cách sử dụng của một công thức, một thuật toán, một hàm cụ thể nào đó mà bạn chưa hiểu, để chúng ta cùng nghiền ngẫm, bàn luận và trao đổi, sao cho áp dụng cho nó trở thành riêng của mình.
Mình xin đóng góp bài đầu tiên "Tìm hiểu thuật toán trích lọc dữ liệu theo điều kiện" ở bài tiếp theo. Các bạn cùng đọc và đóng góp ý kiến nhé.

 

[bebo021999]

Nổ phát pháo đầu tiên đây!!!
các bạn xem và góp ý nhé.
Nếu hay nhớ nhấn nút nhỏ nhỏ phía dưới có chữ Thanks nhé

 

[ndu96081631]

Nổ phát pháo đầu tiên đây!!!
các bạn xem và góp ý nhé.
Nếu hay nhớ nhấn nút nhỏ nhỏ phía dưới có chữ Thanks nhé

Tại sheet KQ, bạn có 4 công thức cho cột A, B, C và D như sau:

=IF(ROWS($1:1)>COUNT(Vitri),"",INDEX(Data,SMALL(Vitri,ROWS($1:1)),[COLOR=red][B]1[/B][/COLOR]))
=IF(ROWS($1:1)>COUNT(Vitri),"",INDEX(Data,SMALL(Vitri,ROWS($1:1)),[COLOR=red][B]2[/B][/COLOR]))
=IF(ROWS($1:1)>COUNT(Vitri),"",INDEX(Data,SMALL(Vitri,ROWS($1:1)),[B][COLOR=red]3[/COLOR][/B]))
=IF(ROWS($1:1)>COUNT(Vitri),"",INDEX(Data,SMALL(Vitri,ROWS($1:1)),[B][COLOR=red]4[/COLOR][/B]))

Thật ra chỉ cần 1 công thức duy nhất cho cell A3 là được

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

Kéo fill sang phải và xuống dưới
Đây là cái hay của INDEX mà ít người biết đến
Ẹc... Ẹc...

 

[bebo021999]

Tại sheet KQ, bạn có 4 công thức cho cột A, B, C và D như sau:

=IF(ROWS($1:1)>COUNT(Vitri),"",INDEX(Data,SMALL(Vitri,ROWS($1:1)),[COLOR=red][B]1[/B][/COLOR]))
=IF(ROWS($1:1)>COUNT(Vitri),"",INDEX(Data,SMALL(Vitri,ROWS($1:1)),[COLOR=red][B]2[/B][/COLOR]))
=IF(ROWS($1:1)>COUNT(Vitri),"",INDEX(Data,SMALL(Vitri,ROWS($1:1)),[B][COLOR=red]3[/COLOR][/B]))
=IF(ROWS($1:1)>COUNT(Vitri),"",INDEX(Data,SMALL(Vitri,ROWS($1:1)),[B][COLOR=red]4[/COLOR][/B]))

Thật ra chỉ cần 1 công thức duy nhất cho cell A3 là được

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

Kéo fill sang phải và xuống dưới
Đây là cái hay của INDEX mà ít người biết đến
Ẹc... Ẹc...

Hay qúa Edu, bạn có thể giải thích làm sao INDEX hiểu được tại cột B,C,D sẽ lấygiá trị cột tham chiếu là 2,3,4? mình thấy công thức y chang nhau mà. Điều nay rất quan trọng cho những bảng tính có nhiều cột.

 

[concogia]

Hay qúa Edu, bạn có thể giải thích làm sao INDEX hiểu được tại cột B,C,D sẽ lấygiá trị cột tham chiếu là 2,3,4? mình thấy công thức y chang nhau mà. Điều nay rất quan trọng cho những bảng tính có nhiều cột.

Index mà hiểu được cái in đậm và nghiêng mình ....chết liền
Sử dụng công thức trên chỉ đúng với bài của bạn thôi: Vùng tạo bảng dò và vùng trả kết quả hoàn toàn trùng khớp về địa chỉ tham chiếu. Tham chiếu hàng : OK ( vì bạn dùng công thức tìm hàng thỏa điều kiện), tham chiếu cột: bạn thử cho kết quả bắt đầu không phải ở cột A và kết quả trả về không theo thứ tự A, B, C, D xem sao. Híc, nó sẽ "tò tí te" ngay
(Nếu kết quả trả về không bắt đầu từ cột A & thứ tự dò đúng như trong bài thì bạn dùng hàm Column() để thể hiện cột trong =Index(bảng, hàng, cột) để công thức mang tính tổng quát)

Để con trỏ ở ô màu vàng.......(Xem xong nhấn Esc nhé, đừng nhấn Enter mà mang khổ đấy!)

Cái này chẳng sao cả, hột vịt, trứng cút còn lộn mà chứ nói chi con người, ai cũng có khi.......lộn, lộn thì .....làm lại, hổng sao đâu, bạn "dọa" làm anh em ...sợ
Cái "zụ" này còn nhiều cách chứ sao chỉ có 3 nhỉ ???( hình như Thầy ndu còn "chơi" tuyệt chiêu cóc cần cột phụ hay name nữa mà). Híc
Thân

 

[bebo021999]

Cái này chẳng sao cả, hột vịt, trứng cút còn lộn mà chứ nói chi con người, ai cũng có khi.......lộn, lộn thì .....làm lại, hổng sao đâu, bạn "dọa" làm anh em ...sợ
Cái "zụ" này còn nhiều cách chứ sao chỉ có 3 nhỉ ???( hình như Thầy ndu còn "chơi" tuyệt chiêu cóc cần cột phụ hay name nữa mà). Híc
Thân

Em xin cam đoan là nếu nhấn enter thì công thức sẽ biến mất, thay vào đó là mảng kết quả {1,"",3,...} - Em không biết tại sao? Bác cò già và bác "ở, ăn, sống với GPE, ngủ với ai?" giải thích giúp em với. Còn cách khác nữa thì các bác pot lên cho ACE học hỏi với...

 

[ndu96081631]

Em xin cam đoan là nếu nhấn enter thì công thức sẽ biến mất, thay vào đó là mảng kết quả {1,"",3,...} - Em không biết tại sao? Bác cò già và bác "ở, ăn, sống với GPE, ngủ với ai?" giải thích giúp em với.

Ý bác concogia nói tức là lộn thì làm lại. Trường hợp của bạn, lỡ nhấn enter thì bùn lắm là Ctrl + Z, có sao đâu nè

Còn cách khác nữa thì các bác pot lên cho ACE học hỏi với...

Cách khác tức là mang toàn bộ name cho hết vào công thức, gõ trực tiếp lên bảng tính ---> Vấn đề ở đây là phải tối giản công thức đến mức có thể để nó không dài quá
Ví dụ công thức này gõ tại cell A3 của sheet KQ

=IF(ROWS($1:1)>COUNTIF(Data!$D$3:$D$102,"Y"),"",INDEX(Data!$A$3:$D$102,SMALL(IF(Data!$D$3:$D$102="Y",ROW($1:$100),""),ROWS($1:1)),COLUMNS($A:A)))

Hổng có cái name nào đâu nha

 

[ndu96081631]

Thử làm lại bài toán cũ

Ngày tôi mới gia nhập diễn đàn, tôi có từng đọc qua bài này:
http://www.giaiphapexcel.com/forum/...ỏi-về-cách-lọc-có-điều-kiện&p=13168#post13168
Với trình độ của tôi khi ấy, quả thật không tài nào làm được. Và khi tôi đọc qua giải pháp của Mr.Okebab, tôi choáng ngợp vô cùng
Đó chính là bài học đầu tiên về việc phối hợp công thức, name... giúp tôi trưởng thành
Bây giờ các bạn thử làm lại bài này bằng phương pháp mới xem (tối ưu nhất và không dùng cột phụ)
Hay và rất khó đấy nha

 

[ndu96081631]

Ái dzà... bebo021999 mở topic này đến đây rồi trốn luôn à?
Nói về LỌC thì.. vô vàn ---> Nghiên cứu tiếp đi

 

[bebo021999]

Ái dzà... bebo021999 mở topic này đến đây rồi trốn luôn à?
Nói về LỌC thì.. vô vàn ---> Nghiên cứu tiếp đi

Em làm 1 ly trước rồi, bác đến sau phạt bác gấp đôi. Bác gửi bài lên đi cho ACE học hỏi.

 

[ndu96081631]

Em làm 1 ly trước rồi, bác đến sau phạt bác gấp đôi. Bác gửi bài lên đi cho ACE học hỏi.

Thì bài số 8 đó --> Nghiên cứu đi

 

[yeuthamhangxom]

Ngày tôi mới gia nhập diễn đàn, tôi có từng đọc qua bài này:
http://www.giaiphapexcel.com/forum/showthread.php?2166-H%E1%BB%8Fi-v%E1%BB%81-c%C3%A1ch-l%E1%BB%8Dc-c%C3%B3-%C4%91i%E1%BB%81u-ki%E1%BB%87n&p=13168#post13168
Với trình độ của tôi khi ấy, quả thật không tài nào làm được. Và khi tôi đọc qua giải pháp của Mr.Okebab, tôi choáng ngợp vô cùng
Đó chính là bài học đầu tiên về việc phối hợp công thức, name... giúp tôi trưởng thành
Bây giờ các bạn thử làm lại bài này bằng phương pháp mới xem (tối ưu nhất và không dùng cột phụ)
Hay và rất khó đấy nha

Bài toán không dùng cột phụ thì cực kì khó thầy ạ. Những người mới vào nghề như chúng em mà không có người hướng dẫn thì đi không được thầy ạ. Mặc dù người phương khi thấy em bẽ ngã không nâng đỡ bao giờ. Nhưng người việt nam thì khác.
Thầy có thể bật mí một chút để bọn em động não được không ạ. bọn em bây giờ

 

[Nguyễn Duy Tuân]

Trên diễn đàn hiện nay có rất nhiều bài viết về giải pháp trích lọc dữ liệu theo điều kiện, rất hiệu quả và chính xác. Tuy nhiên, đối với những người chưa dùng excel nhiều, để áp dụng vào thực tế của mình, vẫn còn những khoảng trống trong kiến thức, chưa thể hiểu hết những công thức, thuật toán, giải pháp mà các cao thủ đã sử dụng.
Ví dụ: công thức: =IF(ROWS($1:1)>COUNT(P),"",INDEX(Data,SMALL(P,ROWS($1:1)),1))
hoặc: =IF(OFFSET(Data,,2,,1)="y",ROW(INDIRECT("1:"&ROWS(Data))),"")
Nếu không thực sự hiểu hết cách sử dụng mềm dẻo những OFSET, ROW, SMALL hay INDEX, MATCH... bạn sẽ gặp rất nhiều khó khăn khi áp dụng giải bài tóan của mình. Thực sự không ai muốn bê nguyên xi công thức đó làm của mình, nếu không hiểu nó! Biết đâu đó, một ngày đẹp trời, sếp đùng đùng nổi giận vì bảng tính thuế phải nộp của công ty bị tính sai làm thiệt hại mất vài chục triệu!
Thực sự mình cũng mới gia nhập diễn đàn, mới tiếp xúc được với những cách dùng mới mẻ của các hàm, các giải pháp từ các cao thủ (mà không thể tìm thấy ở mục Help của Excel); nhiều công thức độc đáo mà anh Gate đọc được cũng phải giật thót mình ...
Nhiều bạn đã đặt câu hỏi nhờ giải thích chi tiết, một số vấn đề đã được giải đáp, tuy nhiên một số vấn đề rất khó để người giải thích giúp bạn hiểu được thấu đáo. Lý do là một số bạn không chịu bỏ công ra nghiên cứu, tìm hiểu thấu thấu đáo những cách sử dụng căn bản của các hàm, các thủ thuật ... (Có rất nhiều từ sách vở căn bản, từ mục Help ...) đôi khi gây ra sự khó chịu, nán chí, bất bình của người giải thích. (ví dụ hỏi : hàm if sử dụng như thế nào, vlookup có mấy tham số; hoặc không đọc kỹ, thực hiện theo hướng dẫn,...) Nên không trách dược đôi khi chán không muốn nói nữa ...

"Vui xuân kể chuyện dông dài", mình chỉ muốn mở một topic cho các bạn mới sử dụng excel, post lên đây những thắc mắc về cách sử dụng của một công thức, một thuật toán, một hàm cụ thể nào đó mà bạn chưa hiểu, để chúng ta cùng nghiền ngẫm, bàn luận và trao đổi, sao cho áp dụng cho nó trở thành riêng của mình.
Mình xin đóng góp bài đầu tiên "Tìm hiểu thuật toán trích lọc dữ liệu theo điều kiện" ở bài tiếp theo. Các bạn cùng đọc và đóng góp ý kiến nhé.

Bạn hãy học SQL & VBA đi! Muốn giải bài toán trích lọc phức tạp và nhiều dữ liệu thì đừng loanh quanh mấy hàm Excel!

Học SQL không khó đâu, chỉ cơ bản là SELECT...FROM...WHERE....

 

[ndu96081631]

Bài toán không dùng cột phụ thì cực kì khó thầy ạ. Những người mới vào nghề như chúng em mà không có người hướng dẫn thì đi không được thầy ạ. Mặc dù người phương khi thấy em bẽ ngã không nâng đỡ bao giờ. Nhưng người việt nam thì khác.
Thầy có thể bật mí một chút để bọn em động não được không ạ. bọn em bây giờ

Thử nghiên cứu hàm FREQUENCY xem

 

[queluatb]

xin mọi người giúp đỡ

xin tất cả các sư huynh giúp đỡ giùm cho đệ