Thách đố về cách lọc tìm dữ liệu (2 người xem)

[boyxin]

Đúng là thách đố. Tôi sẽ chờ kết quả xem nó là cái gì? Ứng dụng vào đâu?

he he he, sao giống ý tui vậy

(cho dùng 1 cột phụ chứa công thức thì công làm được)
tất nhiên là còn cần thêm 1 cột không chứa công thức đó là cột chứa thứ tự các chữ cần sắp xếp theo tiêu chuẩn săp xếp A, B, C​

 

[tigertiger]

Đã thử ---> Không được!
Tạm thời sửa lại:
Chia cột phụ cho 10^12 để giãm nhỏ số ---> cộng mổi cell với ROW()/10000 ---> giãi quyết được tình trạng tên gần trùng ---> Excel hiểu là trùng
Vẩn CP, nếu không thì bó hand.
Dù gì vẩn phải có bảng mã chứ
!!!

rất hay,

NHƯNG bạn có thể bỏ cột phụ C ở sheet1 đi

còn cột phụ ở Sheet2 chấp nhận được coi như là DL --> TUY NHIÊN múôn bỏ cũng được bằng cách định nghĩa thành mảng TT character trong NAME,

cố lên nhé, bạn

tôi nghĩ là có thể làm --> bạn nhận giải thưởng,

NHƯNG quan trọng là ứng dụng thì cũng khá hẹp và có cần phải phức tạp hóa vấn đề thế không nhỉ - cái này đợi tác giả đặt câu hỏi giải thích....

 

[hoangdanh282vn]

Gửi Danh: tôi thấy trong cột họ và tên ở bảng dữ liệu, đa số các dòng đều có tên cách họ lót 2 khoảng trắng nhưng có một số dòng có tên lại cách họ lót 1 khoảng trắng. Vậy có thể chỉnh sửa dữ liệu cho thống nhất không?????

Đúng là dữ liệu có nhiều chổ dư nhiều khoảng trắng, đây cũng là yêu cầu của câu hỏi để tạo sự đa dạng. Dữ liệu trả về sẽ không còn nữa.
Mình nói thêm, chỉ dùng 1 cột phụ để lưu danh sách các từ như Boyxinh đã nêu (vì độ lớn của Name có giới hạn là 255 ký tự nên mượn tạm cột phụ, còn nếu tách thành 2 Name thì làm cho công thức trở nên nặng nề hơn, máy chạy chậm hơn)

 

[hoangdanh282vn]

NHƯNG quan trọng là ứng dụng thì cũng khá hẹp và có cần phải phức tạp hóa vấn đề thế không nhỉ - cái này đợi tác giả đặt câu hỏi giải thích....

Như em nói ở tựa Topic là thách đố, để chúng ta suy nghĩ và ôn lại các công thức.
Còn ứng dụng thì còn tùy thuộc vào mỗi người, mỗi trường hợp mà ta lồng ghép công thức vào.
và đây là một ứng dụng nho nhỏ :

Câu hỏi thách đố thứ 2 :

ta có một list danh sách các mặt hàng xuất hiện nhiều lần trong NKBH (cột A). ở cột C làm sao sử dụng được Validation với danh sách được trích ra trong cột A là duy nhất và đã được sắp xếp theo thứ tự ABC.. (cho dễ tìm)
Ghi chú là không sử dụng bất kỳ cột phụ nào để trích lọc ra danh sách duy nhất ở Cột B

 

[ndu96081631]

Không khả thi vì như đã nói, Excel không tính toán được với số quá lớn!
Bảng mã có 90 ký tự, vậy phải dùng lủy thừa của 100 ---> số quá lớn
Có khả năng Excel sẽ nhầm ở Đầu máy VCD và Đầu máy DVD

 

[hoangdanh282vn]

Không khả thi vì như đã nói, Excel không tính toán được với số quá lớn!
Bảng mã có 90 ký tự, vậy phải dùng lủy thừa của 100 ---> số quá lớn
Có khả năng Excel sẽ nhầm ở Đầu máy VCD và Đầu máy DVD

Như mình đã nói, để sort thì chỉ cần 2 ký tự đầu, nếu trùng nhau thì ưu tiên nằm ở dòng trên.
Muốn sort mấy ký tự cũng được tất, nhưng ở đây không cần thiết phải như thế. Càng so sánh nhiều ký tự, file càng nặng và chạy càng chậm.
Các bạn cố lên. Có nhiều cách khác mà, đâu nhất thiết phải lũy thừa

 

[ndu96081631]

Như mình đã nói, để sort thì chỉ cần 2 ký tự đầu, nếu trùng nhau thì ưu tiên nằm ở dòng trên.
Muốn sort mấy ký tự cũng được tất, nhưng ở đây không cần thiết phải như thế. Càng so sánh nhiều ký tự, file càng nặng và chạy càng chậm.
Các bạn cố lên. Có nhiều cách khác mà, đâu nhất thiết phải lũy thừa

Thế thì quá dể, nhưng đó hỏng phải là sort mà là làm khó!
Không hay ---> Sort đúng như chức năng sort của Excel mới hay!

 

[tigertiger]

Thế thì quá dể, nhưng đó hỏng phải là sort mà là làm khó!
Không hay ---> Sort đúng như chức năng sort của Excel mới hay!

Đúng rồi,

Nếu thế thì dùng VBA cho nó đẹp,

VBA thì cũng khá hay và đơn giản, lại có tính ứng dụng cao
.

 

[boyxin]

Đúng rồi,

Nếu thế thì dùng VBA cho nó đẹp,

VBA thì cũng khá hay và đơn giản, lại có tính ứng dụng cao
.

lạc đề rồi, nếu làm văn mà tư tưởng thế này thì được mấy điểm nhỉ?

 

[hoangdanh282vn]

Thế thì quá dể, nhưng đó hỏng phải là sort mà là làm khó!
Không hay ---> Sort đúng như chức năng sort của Excel mới hay!

Ch­ưa hiểu ý bạn lắm.
Sort có sẵn của excel thì sort cho toàn bột các ký tự trong chuỗi. Còn ở câu đố này chỉ cần sort cho 2 ký tự đầu tiên thôi là ok.
Bạn gửi file cho mọi người xem thử cách của bạn nha.

 

[tigertiger]

lạc đề rồi, nếu làm văn mà tư tưởng thế này thì được mấy điểm nhỉ?

+ Oh, lại cho điểm trên Diễn đàn ah?, VBA là gợi ý thôi

+ Ý tưởng ndu96081631 tuy còn nhiều đến chưa tối ưu nhưng khá HAY,

+ ndu96081631 bạn có thể phát triển tiếp: Nên là Tên và 3 (hoặc 2) ký tự Họ như thế có tính ứng dụng cao và // hay hơn nữa thì Tên & Họ (không có điệm) -- vậy là giải quyết được

+ Cuối cùng nếu rinh giải, nhơ tách cafe cho tigertiger nhé
.

 

[ThuNghi]

Sort có sẵn của excel thì sort cho toàn bột các ký tự trong chuỗi. Còn ở câu đố này chỉ cần sort cho 2 ký tự đầu tiên thôi là ok.

Tôi vận dụng file của ndu96081631 làm lại thử, chưa biết có đúng ý không. Chỉ còn 1 cột phụ, hình như bỏ cột phụ này anhtuan1066 đã làm rồi.
Có vận dụng file của kimdong80 về tách tên.
Làm thử theo 3 ký tự của tên vì "ngh" + ... ít xảy ra.

 

[boyxin]

Tôi vận dụng file của ndu96081631 làm lại thử, chưa biết có đúng ý không. Chỉ còn 1 cột phụ, hình như bỏ cột phụ này anhtuan1066 đã làm rồi.
Có vận dụng file của kimdong80 về tách tên.
Làm thử theo 3 ký tự của tên vì "ngh" + ... ít xảy ra.

Em dùng 2 name này để tìm hiểu xem các bác làm thế nào

 CTName=GET.NAME('[Rank_n_Sort-TN.xls]KQ'!RC7)

List=NAMES(,3,)

Được kết quả: Nhìn đội ngũ name thế này thì em làm khán giả cổ vũ cho các bác nhận thưởng thôi

_FilterDatabase='[Rank_n_Sort-TN.xls]KQ'!R1C1:R65C1
Chuoi1={"a";"á";"à";"ả";"ã";"ạ";"ă";"ắ";"ằ";"ẳ";"ẵ";"ặ";"â";"ấ";"ầ";"ẩ";"ẫ";"ậ";"b";"c";"d";"đ";"e";"é";"è";"ẻ";"ẽ";"ẹ";"ê";"ế";"ề";"ể";"ễ";"ệ";"g";"h";"i";"í";"ì";"ỉ";"ĩ";"ị";"k";"l";"m";"n";"o";"ó";"ò";"ỏ"}
Chuoi2={"õ";"ọ";"ô";"ố";"ồ";"ổ";"ỗ";"ộ";"ơ";"ớ";"ờ";"ở";"ỡ";"ợ";"p";"q";"r";"s";"t";"u";"ú";"ù";"ủ";"ũ";"ụ";"ư";"ứ";"ừ";"ử";"ữ";"ự";"v";"x";"y";"ý";"ỳ";"ỷ";"ỹ";"ỵ"}
Code=OFFSET('[Rank_n_Sort-TN.xls]KQ'!DS,,2)
Count1=IF(ISNA(MATCH(FChar,'[Rank_n_Sort-TN.xls]KQ'!Chuoi1,0)),MATCH(FChar,'[Rank_n_Sort-TN.xls]KQ'!Chuoi2,0)+46,MATCH(FChar,'[Rank_n_Sort-TN.xls]KQ'!Chuoi1,0))
Count2=IF(sChar="",0,IF(ISNA(MATCH(sChar,'[Rank_n_Sort-TN.xls]KQ'!Chuoi1,0)),MATCH(sChar,'[Rank_n_Sort-TN.xls]KQ'!Chuoi2,0)+46,MATCH(sChar,'[Rank_n_Sort-TN.xls]KQ'!Chuoi1,0)))
Count3=IF(tChar="",0,IF(ISNA(MATCH(tChar,'[Rank_n_Sort-TN.xls]KQ'!Chuoi1,0)),MATCH(tChar,'[Rank_n_Sort-TN.xls]KQ'!Chuoi2,0)+46,MATCH(tChar,'[Rank_n_Sort-TN.xls]KQ'!Chuoi1,0)))
CTName=GET.NAME('[Rank_n_Sort-TN.xls]KQ'!RC7)
DS=OFFSET('[Rank_n_Sort-TN.xls]KQ'!R2C1,,,COUNTA('[Rank_n_Sort-TN.xls]KQ'!R2C1:R1000C1),)
FChar=LOWER(LEFT(Name,1))
List=NAMES(,3,)
Name=IF(ISERROR(FIND(" ",TRIM('[Rank_n_Sort-TN.xls]KQ'!RC1))),TRIM('[Rank_n_Sort-TN.xls]KQ'!RC1),TRIM(RIGHT(SUBSTITUTE(TRIM('[Rank_n_Sort-TN.xls]KQ'!RC1)," ",REPT(" ",10)),10)))
sChar=LOWER(MID(Name,2,1))
tChar=LOWER(MID(Name,3,1))
XL=OFFSET('[Rank_n_Sort-TN.xls]KQ'!DS,,1)

các bác cố lên nhé

 

[ThuNghi]

Tôi xin chú thích name nhé:
1/Name:
=IF(ISERROR(FIND(" ",TRIM(KQ!$A2))),TRIM(KQ!$A2),TRIM(RIGHT(SUBSTITUTE(TRIM(KQ!$A2)," ",REPT(" ",10)),10))) ie nếu không tìm thấy " " thì name là tên, nếu không thì nhân " " thành 10 lần và lấy trim(right(...,10)) = name vì tên VN không thể có độ dài > 10 (kimdong80)
2/ FChar, sChar, tChar:=LOWER(LEFT(Name,1)) Ký tự 1, 2, 3 của name (tên)
3/Chuoi1, chuoi2: mục đích là tìm thứ tự của FChar, sChar, tChar ={"a";"á";"à";"ả";"ã";"ạ";"ă";"ắ";"ằ";"ẳ";"ẵ";"ặ";"â";"ấ";"ầ";"ẩ";"ẫ";"ậ";"b";"c";"d";"đ";"e";"é";"è";"ẻ";"ẽ";"ẹ";"ê";"ế";"ề";"ể";"ễ";"ệ";"g";"h";"i";"í";"ì";"ỉ";"ĩ";"ị";"k";"l";"m";"n";"o";"ó";"ò";"ỏ"}
...
Tại sao có chuoi1, chuoi2 vì độ dài name không > nên phải tách 2 name
Chuoi1 có 46 ký tự
4/Count1:
=IF(ISNA(MATCH(FChar,KQ!Chuoi1,0)),MATCH(FChar,KQ!Chuoi2,0)+46,MATCH(FChar,KQ!Chuoi1,0))
Tương tự Count1, 2, 3
5/ Xếp hạng:
Count1*10000+IF(Count2=0,100,Count2*100)+IF(Count3=0,10,Count3*10)+ROW(),"")
ie
Khi trùng ký tự 1, xét đến 2, nếu trùng, ->3, nếu trùng thì + Row(), trên sẽ ưu tiên. Thuật toán này hơi giống với xếp hạng theo Lọai, ĐTB, HK ..., hay là xếp hạng bóng đá...
Mạn phép các bạn giải thích như thế!

Match(....,chuoi,0) => vị trí,

 

[tigertiger]

Tôi vận dụng file của ndu96081631 làm lại thử, chưa biết có đúng ý không. Chỉ còn 1 cột phụ, hình như bỏ cột phụ này anhtuan1066 đã làm rồi.
Có vận dụng file của kimdong80 về tách tên.
Làm thử theo 3 ký tự của tên vì "ngh" + ... ít xảy ra.

+ Sao anh không làm theo ý tưởng em (tigertiger) đề xuất trên nhỉ??? tên + 3 ký tự họ sẽ hay hơn

+ Cột phụ ở sheet họ tên khó bỏ - vì chúng ta xét thành mảng 2 chiều rùi (theo chiều ký tự của họ và 1 chiều theo DS

.

 

[hoangdanh282vn]

Tôi vận dụng file của ndu96081631 làm lại thử, chưa biết có đúng ý không. Chỉ còn 1 cột phụ, hình như bỏ cột phụ này anhtuan1066 đã làm rồi.
Có vận dụng file của kimdong80 về tách tên.
Làm thử theo 3 ký tự của tên vì "ngh" + ... ít xảy ra.

Bác làm hay lắm. Nhưng còn cột phụ thì vẫn chưa ok.
Bác làm xong thì làm luôn câu thứ 2 nha. Mình sắp ra câu đố thứ 3 cũng gần như thế nhưng khó hơn tí,.
Các bác hãy cố lên.

 

[Mr Okebab]

Tớ thì nhìn đã sợ rồi, cả 1 rừng name thế kia cơ mà.
Trong khi đó nếu dùng VBA thì chỉ 1 hàm là xong.

Không biết các bác định thử sức với công thức hay định giải quyết 1 vấn đề nào đó ???

Thân!

 

[boyxin]

1. Cho mình giải thích thêm vì hôm qua gấp quá nên chưa giải thích rõ.
   • Thứ nhất là không dùng VBA.
   • Thứ hai là không dùng cột phụ (có chứa công thức) để phụ giúp thao tác.
   • Thứ ba là sort theo 2 ký tự đầu của tên (càng nhiều ký tự càng phức tạp nên chỉ lấy Nếu trùng nhau thì ưu tiên lấy thứ tự hàng nhỏ nhất.
2. Bài này có thể áp dụng cho nhiều điều kiện trích lọc chứ không riêng gì xếp loại không. Nhưng bài trên chỉ lấy 1 điều kiện.
3. Ngoài ra, các Bác VBA hay các bạn có thể dùng cột phụ để tìm ra giải pháp về tốc độ

Thời gian cuối là trước 3 ngày sinh nhật GPE
Giải thường là một chầu Cafe để giao lưu thân tình.
Thân

Với 3 yêu cầu như trên

1. boyxin hoàn thành xuất sắc yêu cầu 1 (không dùng VBA)
2. boyxin hoàn thành trên cả xuất sắc về yêu cầu 3 (không chỉ là sort 2 ký tự đầu mà sort full tên luôn)
3. boyxin xin nhận 0 điểm về yêu cầu 2 (đang nghĩ mà chưa xong)

------------------------------------------------------------------
Để làm được bài này boyxin nhờ vào rất nhiều bài của các bác đã đăng trên GPE, đặc biệt là bài

Rank_n_Sort.xls
tại đây của
ndu96081631

Excel Member

-------------------------------------------------------------------------------------------
boyxin tập làm thôi, không giám mơ tưởng đến giải thưởng đâu, chỗ nào chưa hay mong nhận được sự góp ý chân thành

 

[ndu96081631]

Tôi cảm thấy không hài lòng lắm về yêu cầu của bài toán này. Nếu chỉ sort với 2 ký tự thì đó không thể gọi là sort. Xin mạn phép nâng độ khó của bài toán lên 1 cấp: Sort với tất cả khả năng có thể của Excel
Các anh chị tham khảo qua file này nhé
Giới thiệu sơ lược về khả năng của công thức lọc trong file:
1- ƯU ĐIỂM:

a) Có khả năng lọc được 1 danh sách không liên tục
b) Loại bỏ được khoảng trắng thừa trong chuỗi do sai sót trong quá trình nhập liệu
c) Sort được đến 5 ký tự (Maximum khả năng tính toán của Excel)
d) Không có bất cứ cột phụ nào
​

2- NHƯỢC ĐIỂM:

a) Không lọc được các ký tự đặc biệt hoặc các ký tự không có trong bảng mã (nếu có nhu cầu riêng có thể thêm vào bảng mã)
b) Vì không thể dùng hàm CODE để tính toán với các ký tự tiếng Việt có dấu nên buộc phải xây dựng 1 bảng mã riêng (Name Alp)
c) Tổng cộng có đến 94 ký tự trong bảng mã nên buộc phải dùng lũy thừa cơ số 100. Điều này là đương nhiên và cách làm này tương tự như việc xây dựng 1 hệ đếm mới. Từ đó đã dẩn đến tình trạng số quá lớn trong quá trình tính toán ---> không thể tính toán và sort trên 6 ký tự
d) Công thức mãng khá nặng nên cũng ảnh hưởng nhiều đến tốc độ tính toán nhất là khi làm việc với dử liệu lớn ---> Giãi pháp chỉ là giãi pháp, trên thực tế sẽ không hiệu quả bằng chức năng sort sẳn có của Excel
​

Đây là những gì mà file này làm được và chưa làm được. Xin các anh chị góp ý thêm!
Một chi tiết cần các anh chị xem lại giúp: không biết trật tự trong bảng Alphabet tôi sắp như vậy đã đúng chưa? Nếu có sai sót, nhờ anh chị sửa lại
NDU

 

[tigertiger]

Thế là bạn Rinh Giải rùi - CHúc mừng , chức mừng

1- ƯU ĐIỂM:a) Có khả năng lọc được 1 danh sách không liên tục
b) Loại bỏ được khoảng trắng thừa trong chuỗi do sai sót trong quá trình nhập liệu
c) Sort được đến 5 ký tự (Maximum khả năng tính toán của Excel)
d) Không có bất cứ cột phụ nào

Nếu có cột phụ thì ta có thể số ký tự dài hơn,

tại sao lại phụ thuộc vào số lũy thừa cơ số 100, chỉ phụ thuộc vào chiều dài ký tự tên (xét sort)

Theo tigertiger thì NÊN sd 1 cột phụ và sắp xếp tên+HỌ, đã thử độ lớn của số nằm trong phạm vi chấp nhận được,
.

Và Ví dụ bạn đưa lên -> chưa lọc đ/k nhỉ