Tạo mảng từ các giá trị tìm kiếm tương ứng hàm Vlookup (1 người xem)

[dhn46]

Nhờ các anh chị giúp đỡ công thức tạo mảng từ các giá trị tìm kiếm tương ứng hàm Vlookup trong bảng dữ liệu được sắp xếp ngẫu nhiên như mô tả trong file đính kèm
Xin cảm ơn GPE!

 

[ptm0412]

Thì tạo bình thường:
=VLOOKUP(Sheet1!$D$2:$D$5,Sheet1!$A$2:$B$8,2,0)

Tạo name cũng công thức này. Muốn động thì tạo 2 mảng động: 1 mảng giá trị dò và 1 mảng Bảng dò

 

[ndu96081631]

Thì tạo bình thường:
=VLOOKUP(Sheet1!$D$2:$D$5,Sheet1!$A$2:$B$8,2,0)

Tạo name cũng công thức này. Muốn động thì tạo 2 mảng động: 1 mảng giá trị dò và 1 mảng Bảng dò

Không được đâu sư phụ à!
VLOOKUP chỉ tạo ra mảng 1 phần tử ---> Trừ phi sư phụ quét chọn 4 cell rồi gõ công thức ấy thì nó mới ra kết quả 4 phần tử
Dùng OFFSET thì được:

=OFFSET(B2:B8,MATCH(D2:D5,A2:A8,0)-1,,1,)

 

[dhn46]

Thì tạo bình thường:
=VLOOKUP(Sheet1!$D$2:$D$5,Sheet1!$A$2:$B$8,2,0)

Tạo name cũng công thức này. Muốn động thì tạo 2 mảng động: 1 mảng giá trị dò và 1 mảng Bảng dò

Chú Mỹ.
Với công thức

=VLOOKUP(Sheet1!$D$2:$D$5,Sheet1!$A$2:$B$8,2,0)

khi bấm F9 không cho ra mảng mong muốn, cháu đã test theo cách này và đếm phần tử của mảng tạo được với công thức
=Counta(VLOOKUP(Sheet1!$D$2:$D$5,Sheet1!$A$2:$B$8,2,0)) chỉ cho giá trị = 1
Cháu cũng đã tạo mảng với Lookup nhưng với dữ liệu không sắp xếp thì cho kết quả không đúng

 

[ptm0412]

Không được đâu sư phụ à!
VLOOKUP chỉ tạo ra mảng 1 phần tử ---> Trừ phi sư phụ quét chọn 4 cell rồi gõ công thức ấy thì nó mới ra kết quả 4 phần tử
Dùng OFFSET thì được:

=OFFSET(B2:B8,MATCH(D2:D5,A2:A8,0)-1,,1,)

Quả đúng như ndu nói, vlookup có thể cho kết quả 4 phần tử trên 4 cells, nhưng không truy xuất bằng index được.

Offset cũng không

 

[dhn46]

Không được đâu sư phụ à!
VLOOKUP chỉ tạo ra mảng 1 phần tử ---> Trừ phi sư phụ quét chọn 4 cell rồi gõ công thức ấy thì nó mới ra kết quả 4 phần tử
Dùng OFFSET thì được:

=OFFSET(B2:B8,MATCH(D2:D5,A2:A8,0)-1,,1,)

Cảm ơn thầy NDU
Đúng là cách này sau khi F9 cho ra kết quả mảng mong muốn, nhưng nếu thực hiện việc đếm các phần tử trong mảng đó(chưa bấm F9) thì kết quả cho ra 1, và khi dùng Evaluate Formulas cũng không hiện mảng
Vậy nhờ thầy giải thích giùm em, và mong thầy chỉ dẫn cách làm sao để đếm phần tử trong mảng đó(không cần F9), và trong cửa sổ Evaluate Fomulas có thể diễn giải được mảng.
Em cảm ơn!

 

[ndu96081631]

Cảm ơn thầy NDU
Đúng là cách này sau khi F9 cho ra kết quả mảng mong muốn, nhưng nếu thực hiện việc đếm các phần tử trong mảng đó(chưa bấm F9) thì kết quả cho ra 1, và khi dùng Evaluate Formulas cũng không hiện mảng
Vậy nhờ thầy giải thích giùm em, và mong thầy chỉ dẫn cách làm sao để đếm phần tử trong mảng đó(không cần F9), và trong cửa sổ Evaluate Fomulas có thể diễn giải được mảng.
Em cảm ơn!

Thì bạn hỏi vậy, tôi trả lời ra kết quả đúng thì thôi (đương nhiên tôi thừa biết khi bấm F9 thì thấy kết quả nhưng không thể truy xuất mảng được)
Vậy cuối cùng bạn muốn điều gì? Ý tôi muốn hỏi mục đích sử dụng ấy

 

[dhn46]

Vâng, mục đích chính của em là có thể truy xuất từ mảng đó, như là: đếm, trích xuất phần tử, lọc duy nhất, áp dụng cho tính Sumproduct... biến mảng đó như 1 Range
Em cảm ơn!

 

[ndu96081631]

Vâng, mục đích chính của em là có thể truy xuất từ mảng đó, như là: đếm, trích xuất phần tử, lọc duy nhất, áp dụng cho tính Sumproduct... biến mảng đó như 1 Range
Em cảm ơn!

Đặt 4 name:

Data =Sheet1!$A$2:$A$8

Find_Rng =Sheet1!$D$2:$D$5

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

tmp =SMALL(pos,ROW(INDIRECT("1:"&COUNT(pos))))

Mảng kết quả là cái này:

ResArr =LOOKUP(tmp,ROW(INDIRECT("1:"&ROWS(Data))),OFFSET(Data,,1))

ResArr chính là cái bạn cần

 

[quancui]

Thì bạn hỏi vậy, tôi trả lời ra kết quả đúng thì thôi (đương nhiên tôi thừa biết khi bấm F9 thì thấy kết quả nhưng không thể truy xuất mảng được)
Vậy cuối cùng bạn muốn điều gì? Ý tôi muốn hỏi mục đích sử dụng ấy

1.
CŨng bài toán này em áp dụng vào làm bảng tính của em:
Khi em dùng mãng này để đếm Countifs với một điều kiện thì được, còn thêm nhiều điều kiện vào thì nó lỗi.
Vậy là sao vậy các thầy?
2.
Hàm =MATCH(E2:E6,A2:A9,0) cảu thầy ndu sao em phải bấm Ctrl+Shift+Enter thì nó mới tính còn không thì báo lỗi giá trị.
Trong khi đó em cũng tạo hàm tương tự với dử liệu của em thì nó lại không cần bấn tổ hợp ba phím trên.
Mong các thầy giãi đáp.
Em đã ghi chú trong file đính kèm!
Em cảm ơn!

 

[bebo021999]

làm sao để đếm phần tử trong mảng đó(không cần F9), và trong cửa sổ Evaluate Fomulas có thể diễn giải được mảng.
Em cảm ơn!

Vì hàm MATCH(mảng,mảng) là hàm mảng và tại 1 thời điểm chỉ lưu giữ 1 kết quả tìm kiếm. Do đó phải dùng SUBTOTAL để lưu giữ các kết quả phụ.
dhn46 thử đếm bằng cái này nhé:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B8,MATCH(D2:D5,A2:A8,0)-1,,1,)))