Dò tìm cho 1 lookup_value nhưng có nhiều giá trị cùng thỏa mãn lookup_value (1 người xem)

[hcmthm]

Chào mọi người, mình có bài toán về lookup nhưng chưa biết phải làm như thế nào, mong nhận được sự góp ý của mọi người

Mình có bảng tính bao gồm 2 cột: TênDealer + TỈNH (xem file đính kèm)
Yêu cầu là xác định Dealer đó thuộc tỉnh nào. Nhưng do có một số Dealer đó có thể có ở nhiều tỉnh nên không thể sử dụng được hàm Vlookup. Không biết có thể có cách nào để giải quyết được không (Non-VBA, VBA)?

Thanks

 

[sealand]

Trong khi chờ các bạn cho công thức hợp lý, mình gửi 1 hàm UDF.Hàm này chưa phải tối ưu nhưng dễ viết, dễ hiểu và có kết quả đạt yêu cầu.

Function Dsach(Rg1 As Range, Ch As String)
Dim i, kq
For i = 1 To Rg1.Cells.Count
    If Rg1.Cells(i, 1) = Ch Then
        If InStr(1, kq, Rg1.Cells(i, 1).Offset(, 1)) = 0 _
        Then kq = kq & Rg1.Cells(i, 1).Offset(, 1) & ";"
    End If
 Next
  Dsach = Left(kq, Len(kq) - 1)
End Function

 

[ditimdl]

Hàm UDF này muốn trả về địa chỉ những cell thoã mãn điều kiện và lấy địa chỉ những cell đó gán vào 1 hay nhiều cell cố định thì sửa như thế nào vậy bạn?
Ví dụ B1 chứa giá trị cần tìm, tìm trong vùng $C$1:$C$100 cho ra 3 giá trị ở vị trí C10, C20, C25. Mình không cần lấy giá trị ở ô C10, C20, C25 mà lấy địa chỉ của nó luôn (kết quả cho ra C10,C20,C25)
Mong sealand và các bạn giúp đỡ!

 

[nghiaphuc]

Hàm UDF này muốn trả về địa chỉ những cell thoã mãn điều kiện và lấy địa chỉ những cell đó gán vào 1 hay nhiều cell cố định thì sửa như thế nào vậy bạn?
Ví dụ B1 chứa giá trị cần tìm, tìm trong vùng $C$1:$C$100 cho ra 3 giá trị ở vị trí C10, C20, C25. Mình không cần lấy giá trị ở ô C10, C20, C25 mà lấy địa chỉ của nó luôn (kết quả cho ra C10,C20,C25)
Mong sealand và các bạn giúp đỡ!

Cách đơn giản nhất (sử dụng code của anh sealand chứ không viết lại code mới) là sửa Rg1.Cells(i, 1).Offset(, 1) thành Rg1.Cells(i, 1).Offset(, 1).Address(0,0)

 

[ditimdl]

Được rồi cảm ơn bạn nhưng có vẻ xử lý hơi chậm.

 

[MinhKhai]

Trong khi chờ các bạn cho công thức hợp lý, mình gửi 1 hàm UDF.Hàm này chưa phải tối ưu nhưng dễ viết, dễ hiểu và có kết quả đạt yêu cầu.

Function Dsach(Rg1 As Range, Ch As String)
Dim i, kq
For i = 1 To Rg1.Cells.Count
    If Rg1.Cells(i, 1) = Ch Then
        If InStr(1, kq, Rg1.Cells(i, 1).Offset(, 1)) = 0 _
        Then kq = kq & Rg1.Cells(i, 1).Offset(, 1) & ";"
    End If
 Next
  Dsach = Left(kq, Len(kq) - 1)
End Function

Cảm ơn bác đã tạo code hay.
Cũng từ bài toán trên (file trên) và code của bác, bác có thể cải tạo theo hướng: Tại ô kết quả
1. Nếu số lượng Lookup_value = 1 thì trả ngay kết quả
2. Nếu số lượng Lookup_value >1 thì ô kết quả là 1 list để người dùng chọn.

 

[nghiaphuc]

Được rồi cảm ơn bạn nhưng có vẻ xử lý hơi chậm.

Vậy thì bạn sửa code trên một chút xíu, thành:

Function DSach(Rg1 As Range, Ch As String)
    Dim i, kq
    For i = 1 To Rg1.Cells.Count
        If Rg1.Cells(i, 1) = Ch Then kq = kq & Rg1.Cells(i, 1).Address(0, 0) & ";"
    Next
    DSach = Left(kq, Len(kq) - 1)
End Function

 

[nghiaphuc]

Cảm ơn bác đã tạo code hay.
Cũng từ bài toán trên (file trên) và code của bác, bác có thể cải tạo theo hướng: Tại ô kết quả
1. Nếu số lượng Lookup_value = 1 thì trả ngay kết quả
2. Nếu số lượng Lookup_value >1 thì ô kết quả là 1 list để người dùng chọn.

Ý của bạn là trong trường hợp Lookup_value > 1 thì tại "ô nào đấy" sẽ hiện ra một danh sách kiểu như Validation để người dùng chọn kết quả ấy à? Nếu đúng như vậy thì Function không làm được.

 

[SA_DQ]

Cũng từ bài toán trên (file trên) và code của bác, bác có thể cải tạo theo hướng: Tại ô kết quả
1. Nếu số lượng Lookup_value = 1 thì trả ngay kết quả
2. Nếu số lượng Lookup_value >1 thì ô kết quả là 1 list để người dùng chọn.

Bạn tham khảo hàm mảng tự tạo sau & xin cho ý kiến tiếp theo

 

[ndu96081631]

Cũng từ bài toán trên (file trên) và code của bác, bác có thể cải tạo theo hướng: Tại ô kết quả
1. Nếu số lượng Lookup_value = 1 thì trả ngay kết quả
2. Nếu số lượng Lookup_value >1 thì ô kết quả là 1 list để người dùng chọn.

Sao giống yêu cầu ở đây quá ta:
http://www.giaiphapexcel.com/forum/...ợ-nhập-liệu-dựa-vào-tác-dụng-cùa-TRA-CỨU-CHÉO
Đã làm từ đời nào rồi

 

[MinhKhai]

Sao giống yêu cầu ở đây quá ta:
http://www.giaiphapexcel.com/forum/...ợ-nhập-liệu-dựa-vào-tác-dụng-cùa-TRA-CỨU-CHÉO
Đã làm từ đời nào rồi

Cách Vlookup ngược xuôi (2 chiều) thì em đã có thể làm được vì em cũng sưu tầm đc từ lâu. Như file trong link của bác nó chỉ có 1 Lookup_value thôi nên đây ko phải là điều em quan tâm.

Ý của bạn là trong trường hợp Lookup_value > 1 thì tại "ô nào đấy" sẽ hiện ra một danh sách kiểu như Validation để người dùng chọn kết quả ấy à? Nếu đúng như vậy thì Function không làm được.

Em nghĩ có thể làm được chứ bác.
- Đầu tiên mình xét Lookup_value. nếu = 1 thì cho trả kết quả vào ngay ô kết quả. Nếu tồn tại >1 Lookup_value, Function sẽ list hết các kết quả Vlookup ra vùng trung gian. Vùng trung gian này làm nguồn Validation. Tất nhiên ô kết quả được cài sẵn Data Validation
- Ý tưởng này của em có hạn chế là ô kết qủa cần cố định và cần trung gian. Em không biết Function của bác có trả các kết quả dưới dạng Name được không (để ko dùng vùng trung gian)

Em diễn giải như vậy không biết đã rõ ý chưa ??

 

[ndu96081631]

Em nghĩ có thể làm được chứ bác.
- Đầu tiên mình xét Lookup_value. nếu = 1 thì cho trả kết quả vào ngay ô kết quả. Nếu tồn tại >1 Lookup_value, Function sẽ list hết các kết quả Vlookup ra vùng trung gian. Vùng trung gian này làm nguồn Validation. Tất nhiên ô kết quả được cài sẵn Data Validation
- Ý tưởng này của em có hạn chế là ô kết qủa cần cố định và cần trung gian. Em không biết Function của bác có trả các kết quả dưới dạng Name được không (để ko dùng vùng trung gian)

Em diễn giải như vậy không biết đã rõ ý chưa ??

Bạn đưa dữ liệu của bạn lên đây, tôi làm cho

 

[MinhKhai]

Bạn tham khảo hàm mảng tự tạo sau & xin cho ý kiến tiếp theo

Em trả lời 2 bài của 2 bác kia trước, trước khi xem bài của bác. Xử lý của bác rất sát với ý của em. Vấn đề là khi kết quả có được chỉ là 1, hiện luôn tại ô C3. Còn nếu nhiều hơn 1 kết quả, thì nó list tại "1 nơi kín đáo" để em làm Data Validation List cho ô C3.
Với xử lý của bác đã đáp ứng được cơ bản yêu cầu tức là cho hiện kết quả tại 1 nơi trung gian và nơi này làm List cho Validation (dùng Name động). Nếu chỉ dừng ở đây thì người dùng luôn phải thêm 1 thao tác chọn kết quả ở ô kết quả, dù trong ô kết quả droplist chỉ có 1 dòng.

 

[MinhKhai]

Bạn đưa dữ liệu của bạn lên đây, tôi làm cho

Vâng, em nhờ bác!

https://dl.dropboxusercontent.com/s...arwIwX4RIr4-mIKjjGlAtkc2rDNZQwP9o1Hg9zbw&dl=1

 

[Let'GâuGâu]

Chào mọi người, mình có bài toán về lookup nhưng chưa biết phải làm như thế nào, mong nhận được sự góp ý của mọi người

Mình có bảng tính bao gồm 2 cột: TênDealer + TỈNH (xem file đính kèm)
Yêu cầu là xác định Dealer đó thuộc tỉnh nào. Nhưng do có một số Dealer đó có thể có ở nhiều tỉnh nên không thể sử dụng được hàm Vlookup. Không biết có thể có cách nào để giải quyết được không (Non-VBA, VBA)?

Thanks

vba có rồi, thử làm một cái bằng cthức

 

[Let'GâuGâu]

Vâng, em nhờ bác!

https://dl.dropboxusercontent.com/s...arwIwX4RIr4-mIKjjGlAtkc2rDNZQwP9o1Hg9zbw&dl=1

trong khi chờ đợi các bậc thầy ra tay, bạn thử vậy xem được ko
1- dùng advanced filter để lọc ra các số dt duy nhất ra một cột tạm nào đó
2- dùng vòng lặp để nạp cái list đó vào validation hoặc cobobox
vậy là bạn được list duy nhât của số dt
3-cũng dùng advanced filter với dk là số dt-->lọc ra các tên-->ra một cột phụ nào đó
4-dùng vòng lặp nạp nó vào list
5- xong xóa các cột phụ đi
tuy ko được siêu nhưng cũng khá hiệu quả

 

[ndu96081631]

Vâng, em nhờ bác!

https://dl.dropboxusercontent.com/s...arwIwX4RIr4-mIKjjGlAtkc2rDNZQwP9o1Hg9zbw&dl=1

Bài của bạn lý ra là chẳng khó khăn gì nhưng bạn merge cell tùm lum khiến cho việc xử lý vô cùng... oải
Cũng ráng làm cho xong. Xem file nhé
(Tôi thấy mấy dữ liệu kiểu này đúng ra là cóc thèm làm luôn)

 

[MinhKhai]

trong khi chờ đợi các bậc thầy ra tay, bạn thử vậy xem được ko
1- dùng advanced filter để lọc ra các số dt duy nhất ra một cột tạm nào đó
2- dùng vòng lặp để nạp cái list đó vào validation hoặc cobobox
vậy là bạn được list duy nhât của số dt
3-cũng dùng advanced filter với dk là số dt-->lọc ra các tên-->ra một cột phụ nào đó
4-dùng vòng lặp nạp nó vào list
5- xong xóa các cột phụ đi
tuy ko được siêu nhưng cũng khá hiệu quả

Cảm ơn bạn đã quan tâm, nhưng hình như bạn chưa hiểu hết ý của mình.

 

[dhnnhue]

Em là ma mới và xin mạo muội đào lại vấn đề này. Em đã làm theo hướng dẫn và sử dụng code của bác sealand và đã làm được. Cảm ơn bác Sealand nhiều nhé.Tuy nhiên theo cách này giá trị trả về theo chuỗi và nằm trong 1 ô. Vậy nếu mình muốn giá trị trả về theo 1 list các ô dọc thì phải làm thế nào ạ?( tức là 1 giá trị nằm trong 1 ô í). Em cảm ơn cả nhà.

 

[nhannv.banker]

Em có danh sách data như trong file đính kèm. Ở sheet 1 em muốn nhập số cif thì sẽ cho ra 1 danh sách các tài khoản có cùng số cif. Nhờ các cao thủ gpe giúp đỡ.

 

[Phan Thế Hiệp]

Em có danh sách data như trong file đính kèm. Ở sheet 1 em muốn nhập số cif thì sẽ cho ra 1 danh sách các tài khoản có cùng số cif. Nhờ các cao thủ gpe giúp đỡ.

Dữ liệu của bạn nhiều, nên chọn giải pháp dùng cột phụ, hoặc phải nhờ VBA.
Liệt kê số Tài khoản theo cách cột phụ:

=IFERROR(OFFSET(Data!$C$1,MATCH(ROW(A1),Data!$D$2:$D$12000,),)&"","")

Xem file kèm.
Thân

 

[nhannv.banker]

Dữ liệu của bạn nhiều, nên chọn giải pháp dùng cột phụ, hoặc phải nhờ VBA.
Liệt kê số Tài khoản theo cách cột phụ:

=IFERROR(OFFSET(Data!$C$1,MATCH(ROW(A1),Data!$D$2:$D$12000,),)&"","")

Xem file kèm.
Thân

Cảm ơn Anh Phan Thế Hiệp nhiều nhé.
Em mò mấy ngày mà không bằng anh trong vài phút. Diễn đàn toàn cao thủ, bái phục.

 

[nhannv.banker]

Dữ liệu của bạn nhiều, nên chọn giải pháp dùng cột phụ, hoặc phải nhờ VBA.
Liệt kê số Tài khoản theo cách cột phụ:

=IFERROR(OFFSET(Data!$C$1,MATCH(ROW(A1),Data!$D$2:$D$12000,),)&"","")

Xem file kèm.
Thân

Cảm ơn anh vì sự giúp đỡ.
Tiếp theo file trên em có thêm cột số dư. Để chỉ liệt kê các tài khoản chỉ có số dư >0 thì công thức của anh cần thêm gì nữa ạ.
Cảm ơn anh.

 

[Phan Thế Hiệp]

Cảm ơn anh vì sự giúp đỡ.
Tiếp theo file trên em có thêm cột số dư. Để chỉ liệt kê các tài khoản chỉ có số dư >0 thì công thức của anh cần thêm gì nữa ạ.
Cảm ơn anh.

Nếu bạn vẫn thiết tha với cách dùng cột phụ, thì thêm cột kế bên cột Số dư, tức cột E làm cột phụ:
E2=IF((B2='Thong tin KH'!$B$1)*(D2>0),MAX($E$1:E1)+1,"")
Và Sheet ThongTinKH:
A5=IFERROR(OFFSET(Data!$C$1,MATCH(ROW(A1),Data!$E$2:$E$12000,),)&"","")

Nhưng theo tôi, với dữ liệu lớn như vậy, bạn nên nghiên cứu bảng Pivot Table.
Xem file kèm.
Thân

 

[be_09]

Với dữ liệu lớn như vậy, bạn nên nghiên cứu bảng Pivot Table.
Xem file kèm.
Thân

Cảm ơn anh vì sự giúp đỡ.
Tiếp theo file trên em có thêm cột số dư. Để chỉ liệt kê các tài khoản chỉ có số dư >0 thì công thức của anh cần thêm gì nữa ạ.
Cảm ơn anh.

Sử dụng Pivot Table là được rồi, thêm Slicer phải chờ load nữa nên sẽ chậm đi 1 tí.
Bạn muốn CIFNO thì vào B1 của Sheet1 chọn là có kết quả.
Hoặc muốn xem số dư của tất cả các ACNAME thì vào C3 và bỏ chọn số 0.

 

[nhannv.banker]

Nếu bạn vẫn thiết tha với cách dùng cột phụ, thì thêm cột kế bên cột Số dư, tức cột E làm cột phụ:
E2=IF((B2='Thong tin KH'!$B$1)*(D2>0),MAX($E$1:E1)+1,"")
Và Sheet ThongTinKH:
A5=IFERROR(OFFSET(Data!$C$1,MATCH(ROW(A1),Data!$E$2:$E$12000,),)&"","")

Nhưng theo tôi, với dữ liệu lớn như vậy, bạn nên nghiên cứu bảng Pivot Table.
Xem file kèm.
Thân

Em cũng có nghiên cứu về Pivot table nhưng em thấy sao khi click vào nó hay nhảy ra sheet mới, với lại lấy nhiều cột trong Pivot table em làm chưa được, em đang học thêm cái đó anh à. Bao la quá. Thanks anh!

 

[longkhanhck]

Dạ khi em dùng công thức này

=IFERROR(OFFSET(MPR!$O$1,MATCH(ROW(A1),MPR!$A$2:$A$12000,),)&"","")

Cho cột có giá trị ngày tháng năm (25/4/2019) thì lại không cho kết quả ngày tháng năm mong muốn mà lại cho kết quả giá trị số (43373) dù em đã định dạng lại cell nhưng vẫn không được.

Mong Anh/Chị giúp clear vấn đề này với ạ

 

[tranthemai]

em cần thống kê các công đoạn của mỗi người từng ngày trong tháng, nhưng em không biết viết hàm VBA nào cho hợp lý ddnahf phải dùng công thức của excel, nhưng số lượng công thức nhập vào 1 cell nhiều quá, nên bảng kết quả load rất chậm,
Rất mong các bác viết hộ em 1 hàm VBA nào đó để giúp cho file chạy nhanh đc ạ,
Cám ơn các bác