Hàm Tìm kiếm nhiều vùng

quick87 · 27/7/13

Kính gửi các Thầy, các anh chị GPE.

Em có 1 câu hỏi nhỏ về hàm tìm kiếm lấy giá trị với dữ liệu nằm ở nhiều vùng.

Cụ thể, em có ghi trong file đính kèm.

Mong nhận được sự giúp đỡ của mọi người.

Em xin cảm ơn !

hieuxd · 27/7/13

quick87 đã viết:
Kính gửi các Thầy, các anh chị GPE.

Em có 1 câu hỏi nhỏ về hàm tìm kiếm lấy giá trị với dữ liệu nằm ở nhiều vùng.

Cụ thể, em có ghi trong file đính kèm.

Mong nhận được sự giúp đỡ của mọi người.

Em xin cảm ơn !

Tại E16 sử dụng tạm công thức này nhé
=IF(COUNTIF($B$2:$B$10;B16);VLOOKUP(B16;$B$2:$D$10;2;0);IF(COUNTIF($E$2:$E$10;B16);VLOOKUP(B16;$E$2:$G$10;2;0);IF(COUNTIF($H$2:$H$10;B16);VLOOKUP(B16;$H$2:$J$10;2;0);VLOOKUP(B16;$K$2:$M$10;2;0))))
Ngẫm lại chút thì rút gọn xuống
Tại E16 sử dụng tạm công thức này nhé
=VLOOKUP(B16;IF(COUNTIF($B$2:$B$10;B16);$B$2:$D$10;IF(COUNTIF($E$2:$E$10;B16);$E$2:$G$10;IF(COUNTIF($H$2:$H$10;B16);$H$2:$J$10;$K$2:$M$10)));2;0)
Ngắn hơn đợi thành viên khác

ndu96081631 · 27/7/13

hieuxd đã viết:
Tại E16 sử dụng tạm công thức này nhé
=IF(COUNTIF($B$2:$B$10;B16);VLOOKUP(B16;$B$2:$D$10;2;0);IF(COUNTIF($E$2:$E$10;B16);VLOOKUP(B16;$E$2:$G$10;2;0);IF(COUNTIF($H$2:$H$10;B16);VLOOKUP(B16;$H$2:$J$10;2;0);VLOOKUP(B16;$K$2:$M$10;2;0))))

Hên quá: Chỉ có 4 bảng.
Nếu có 30 bảng thì IF làm sao?
???

hieuxd · 27/7/13

Cố ý dùng hàm thôi thầy ạ
nếu có 30 bảng thì soay sang dùng VBA của thầy thôi
MVlookUp
http://www.giaiphapexcel.com/forum/showthread.php?42773-T%E1%BA%A1o-h%C3%A0m-t%C6%B0%C6%A1ng-t%E1%BB%B1-Vlookup-t%C3%ACm-trong-nhi%E1%BB%81u-b%E1%BA%A3ng
Em chưa đọc kỹ xem có dùng được không

ndu96081631 · 27/7/13

hieuxd đã viết:
Cố ý dùng hàm thôi thầy ạ
nếu có 30 bảng thì soay sang dùng VBA của thầy thôi
MVlookUp
http://www.giaiphapexcel.com/forum/showthread.php?42773-T%E1%BA%A1o-h%C3%A0m-t%C6%B0%C6%A1ng-t%E1%BB%B1-Vlookup-t%C3%ACm-trong-nhi%E1%BB%81u-b%E1%BA%A3ng

Nếu vẫn dùng công thức thì vầy xem:

Mã:

=VLOOKUP(B16,OFFSET($B$2:$D$10,,LOOKUP(2,1/COUNTIF(OFFSET($B$2:$B$10,,3*(ROW($1:$100)-1)),B16),3*(ROW($1:$100)-1))),2,0)

Có cách nào rút gọn nữa không nhỉ?
Ẹc... Ẹc...

nhapmon · 27/7/13

quick87 đã viết:
Kính gửi các Thầy, các anh chị GPE.

Em có 1 câu hỏi nhỏ về hàm tìm kiếm lấy giá trị với dữ liệu nằm ở nhiều vùng.
Cụ thể, em có ghi trong file đính kèm.
Em xin cảm ơn !

đặt các vùng là zone1-->zone4
đặt zone là vùng chứa các zone1--->zone4
F16=VLOOKUP(B16,INDIRECT(INDEX(zone,MATCH(1,INDEX(--(COUNTIF(INDIRECT(zone),B16)>0),),0))),2,FALSE)

quick87 · 27/7/13

Quá tuyệt vời, cảm ơn thầy ndu96081631 , nhapmon và anh hieuxd nhiều.

Áp dụng trong thực tiễn nếu có vướng mắc em sẽ phản hồi thêm mong Thầy, anh và mọi người giúp đơc thêm.

Em xin cảm ơn !

leonguyenz · 27/7/13

ndu96081631 đã viết:
Nếu vẫn dùng công thức thì vầy xem:

Mã:

=VLOOKUP(B16,OFFSET($B$2:$D$10,,LOOKUP(2,1/COUNTIF(OFFSET($B$2:$B$10,,3*(ROW($1:$100)-1)),B16),3*(ROW($1:$100)-1))),2,0)

Có cách nào rút gọn nữa không nhỉ?
Ẹc... Ẹc...

Cách này gọn nhất rồi Thầy ạ, em dùng mảng INDEX với MATCH kết hợp nhiều vùng, nhưng dài ngoằng (còn dài hơn VLOOKUP từng vùng), sợ người ta tặng gạch nên hỏng dám đưa lên --=0

hieuxd · 27/7/13

ndu96081631 đã viết:
Nếu vẫn dùng công thức thì vầy xem:

Mã:

=VLOOKUP(B16,OFFSET($B$2:$D$10,,LOOKUP(2,1/COUNTIF(OFFSET($B$2:$B$10,,3*(ROW($1:$100)-1)),B16),3*(ROW($1:$100)-1))),2,0)

Có cách nào rút gọn nữa không nhỉ?
Ẹc... Ẹc...

Ngắn rùi thầy ạ
Lúc đầu em cũng nghĩ dùng Offset để lấy bảng dò tìm cho Vlookup nhưng bí mấy tham số của Offset
Giờ thì đã thông

siwtom · 27/7/13

quick87 đã viết:
Kính gửi các Thầy, các anh chị GPE.

Em có 1 câu hỏi nhỏ về hàm tìm kiếm lấy giá trị với dữ liệu nằm ở nhiều vùng.

Cụ thể, em có ghi trong file đính kèm.

Mong nhận được sự giúp đỡ của mọi người.

Em xin cảm ơn !

Mọi người chơi không thức hay quá. Thôi ta chơi cách "quái đản" vậy.
Với dữ liệu kiểu như thế ta có thể làm như sau, ưu điểm là dễ hiểu:

1. Đặt con trỏ tại E16

2. Đặt name pos

Mã:

=MIN(IF($B$2:$L$10=$B16;COLUMN($B$2:$L$10)*1000+ROW($B$2:$L$10);""))

3. Công thức cho E16

Mã:

=INDEX($A$1:$L$10;MOD(pos;1000);INT(pos/1000)+1)

Gọi là làm chơi để trêu ngươi các sư phụ GPE, he he.

dhn46 · 27/7/13

Dhn46 cũng góp vui với công thức

Mã:

=INDEX($B$2:$M$10,SUMPRODUCT(($B$2:$M$10=$B17)*ROW($B$2:$M$10))-1,SUMPRODUCT(($B$2:$M$10=$B17)*COLUMN($B$2:$M$10))+1)

Enter bình thường

Mã:

=INDEX($B$2:$M$10,SUM(($B$2:$M$10=$B16)*ROW($B$2:$M$10))-1,SUM(($B$2:$M$10=$B16)*COLUMN($B$2:$M$10))+1)

Ctrl + Shift + Enter

siwtom · 28/7/13

Thấy dhn46 gửi dự thi tận 2 công thức nên tôi gửi thêm cho chắc ăn

Nếu cô giáo cứ bắt dùng Vlookup thì ... lần này làm bình thường

Mã:

=VLOOKUP($B16;OFFSET($B$2:$C$10;;MAX(($B$2:$L$10=$B16)*(COLUMN($B$2:$L$10)-2)));2;0)

kết thúc bằng Ctrl+Shift+Enter

hoặc nếu dữ liệu không trùng (như trong file) thì

Mã:

=VLOOKUP($B16;OFFSET($B$2:$C$10;;SUMPRODUCT(($B$2:$L$10=$B16)*(COLUMN($B$2:$L$10)-2)));2;0)

kết thúc bình thường

ndu96081631 · 28/7/13

dhn46 đã viết:
Dhn46 cũng góp vui với công thức

Mã:

=INDEX($B$2:$M$10,SUMPRODUCT(($B$2:$M$10=$B17)*ROW($B$2:$M$10))-1,SUMPRODUCT(($B$2:$M$10=$B17)*COLUMN($B$2:$M$10))+1)

Enter bình thường

Mã:

=INDEX($B$2:$M$10,SUM(($B$2:$M$10=$B16)*ROW($B$2:$M$10))-1,SUM(($B$2:$M$10=$B16)*COLUMN($B$2:$M$10))+1)

Ctrl + Shift + Enter

---------------------

siwtom đã viết:
Thấy dhn46 gửi dự thi tận 2 công thức nên tôi gửi thêm cho chắc ăn

Nếu cô giáo cứ bắt dùng Vlookup thì ... lần này làm bình thường

Mã:

=VLOOKUP($B16;OFFSET($B$2:$C$10;;MAX(($B$2:$L$10=$B16)*(COLUMN($B$2:$L$10)-2)));2;0)

kết thúc bằng Ctrl+Shift+Enter

hoặc nếu dữ liệu không trùng (như trong file) thì

Mã:

=VLOOKUP($B16;OFFSET($B$2:$C$10;;SUMPRODUCT(($B$2:$L$10=$B16)*(COLUMN($B$2:$L$10)-2)));2;0)

kết thúc bình thường

Đang hình dung nếu TÌM ĐƯỢC TỪ 2 PHẦN TỬ THỎA ĐIỀU KIỆN thì... sao đây ta?
Ẹc... Ẹc...

dhn46 · 28/7/13

ndu96081631 đã viết:
---------------------

Đang hình dung nếu TÌM ĐƯỢC TỪ 2 PHẦN TỬ THỎA ĐIỀU KIỆN thì... sao đây ta?
Ẹc... Ẹc...

Vậy thì em liệt kê hết luôn tất cả các phần tử thoả mãn

Mã:

=INDEX($B$2:$M$10,--RIGHT(LARGE(($B$2:$M$10=$B$16)*10^COLUMN($B$2:$M$10)+ROW($B$2:$M$10),[COLOR=#ff0000]ROW(1:1)[/COLOR]),2)-1,--RIGHT(LARGE(($B$2:$M$10=$B$16)*(10^COLUMN($B$2:$M$10))+COLUMN($B$2:$M$10),ROW(1:1)),2))

Kéo xuống "mỏi tay" thì thôi
Công thức trên muốn lấy phần tử cuối cùng thì đơn giản rồi, chỉ cần thay đổi phần màu đỏ thành 1.

siwtom · 28/7/13

ndu96081631 đã viết:
---------------------

Đang hình dung nếu TÌM ĐƯỢC TỪ 2 PHẦN TỬ THỎA ĐIỀU KIỆN thì... sao đây ta?
Ẹc... Ẹc...

Thế với công thức của Tuấn:

Mã:

=VLOOKUP(B16,OFFSET($B$2:$D$10,,LOOKUP(2,1/COUNTIF(OFFSET($B$2:$B$10,,3*(ROW($1:$100)-1)),B16),3*(ROW($1:$100)-1))),2,0)

Mà "hình dung nếu TÌM ĐƯỢC TỪ 2 PHẦN TỬ THỎA ĐIỀU KIỆN thì... sao đây ta?"

Vì nếu tôi hiểu code thì lúc đó nó sẽ trả lại kết quả cuối cùng.
-------------
Công thức của tôi

Mã:

=VLOOKUP($B16;OFFSET($B$2:$C$10;;MAX(($B$2:$L$10=$B16)*(COLUMN($B$2:$L$10)-2)));2;0)

kết thúc bằng Ctrl+Shift+Enter

cũng thế thôi. Nó sẽ trả về kết quả ở cột cuối. Nếu cột cuối có >= 2 kết quả thì nó sẽ trả về kết quả ở dòng trước.

Riêng code

Mã:

=VLOOKUP($B16;OFFSET($B$2:$C$10;;SUMPRODUCT(($B$2:$L$10=$B16)*(COLUMN($B$2:$L$10)-2)));2;0)

thì nếu có dữ liệu trùng thì sẽ sai. Vì tôi là người có trách nhiệm nên tôi ghi rất rõ để thông báo cho người dùng: "hoặc nếu dữ liệu không trùng (như trong file) thì"
---------------
Hay tôi quên một cái gì đó và ý Tuấn nói về vấn đề khác?
------------------
code của tôi ở bài #10 Tuấn không nêu ra nhưng với dữ liệu trùng thì cũng tương tự: trả về 1 trong các kết quả.

ndu96081631 · 28/7/13

siwtom đã viết:
Mà "hình dung nếu TÌM ĐƯỢC TỪ 2 PHẦN TỬ THỎA ĐIỀU KIỆN thì... sao đây ta?"

Vì nếu tôi hiểu code thì lúc đó nó sẽ trả lại kết quả cuối cùng.

Vâng! Đương nhiên thế mà anh (vì em dùng LOOKUP)
Nhưng thà rằng khi có trùng nó lấy kết quả nào cũng được (dù là đầu hay cuối) chứ đừng có... trật lất
Ẹc... Ẹc...
---------------

siwtom đã viết:
Hay tôi quên một cái gì đó và ý Tuấn nói về vấn đề khác?

Ý em là: Bài toán DÒ TÌM nó "kỵ" SUMPRODUCT anh à (tức nguy cơ cho kết quả sai rất cao)

siwtom · 29/7/13

ndu96081631 đã viết:
Vâng! Đương nhiên thế mà anh (vì em dùng LOOKUP)
Nhưng thà rằng khi có trùng nó lấy kết quả nào cũng được (dù là đầu hay cuối) chứ đừng có... trật lất
Ẹc... Ẹc...
---------------

Ý em là: Bài toán DÒ TÌM nó "kỵ" SUMPRODUCT anh à (tức nguy cơ cho kết quả sai rất cao)

Về SUMPRODUCT tôi cảnh báo rõ ràng mà. Vả lại Tuấn còn liệt kê cả code thứ 2 nữa nên tôi mới không hiểu.

Ngoài ra theo tôi dùng gì cũng được nhưng phải ý thức được. Sợ dao mổ người (dao giải phẫu) nó sắc bén quá mà không dùng trong mọi trường hợp thì có lẽ không đúng.

dhn46 · 29/7/13

siwtom đã viết:
....................................................................................................................................
Ngoài ra theo tôi dùng gì cũng được nhưng phải ý thức được. Sợ dao mổ người (dao giải phẫu) nó sắc bén quá mà không dùng trong mọi trường hợp thì có lẽ không đúng.

- Bác SiwTom nói chí phải. Khi đưa ra một vấn đề thì hãy tìm mọi cách để giải quyết, chí ít là vấn đề trước mắt còn kinh nghiệm là những trường hợp "chưa lường hết được" để rồi sau áp dụng.

- Với trường hợp bài này, thì cũng "đoán" nếu dữ liệu không trùng => Sumproduct hay Sum đều được nhưng nếu trùng thì sẽ nên nghĩ tới các giải pháp khác

- Chủ Topic lần này "hên" quá được 3 công thức có thể áp dụng trong đó có 2 cách dùng Vlookup, 1 cách dùng Index, vấn đề là khả năng áp dụng như thế nào mà thôi

- dhn46 post bài #11 cũng 1 phần do động lực bài #10. Công thức "quái đản" 1 chút xem sao...

Hàm Tìm kiếm nhiều vùng

(/ội...

File đính kèm

Thành viên tích cực

File đính kèm

Huyền thoại GPE

Thành viên tích cực

Huyền thoại GPE

Thành viên tích cực

File đính kèm

(/ội...

Thành viên gạo cội

Thành viên tích cực

Thành viên gắn bó

Hướng tới tương lai

Thành viên gắn bó

Huyền thoại GPE

Hướng tới tương lai

Thành viên gắn bó

Huyền thoại GPE

Thành viên gắn bó

Hướng tới tương lai

Facebook

Group