Các câu hỏi về hàm dò tìm dữ liệu (Lookup, Vlookup, Hlookup...)

[SA_DQ]

Hàm nào mà không trả về 1 kết quả. Hạn chế của Vlookup, Hlookup là phải tìm từ dòng đầu hay cột đầu (lookup_value). Rất hạn chế.
Còn hàm if thì như trên.

Thu Nghi chủ quan rồi: Hàm mảng trả về không là một kết quả; mà là nhiều kết quả;
Chúng ta thấy có người đã viết hàm mảng tự tạo trả về nhiều kết quả lọc từ 2 sheets đó thôi! Vài lời mong bạn cảm thông cho sự đường đột!

 

[anhtuan1066]

Cái hay của bảng tính Excel là dường như nó đã đưa ra khá đầy đủ công cụ và hàm đễ mặc sức chúng ta tung hoành. Việc còn lại là do sáng tạo và kinh nghiệm có dc sau thời giản giãi quyết bài tập. Vậy nếu như bạn muốn biết dc mặc mạnh yếu của từng hàm thì ko gì bằng bạn hãy giãi bài tập thật nhiều. Vì dù sao mỗi người nhìn mặt mạnh yếu của từng hàm là theo cách nghĩ riêng, chưa chắc ai giống ai! Vậy bạn còn chần chừ gì nữa mà ko download những bài người ta thắc mắc và đã giãi quyết trên diển đàn này về máy tính rồi tự giãi quyết theo cách của bạn. Tôi dám chắc thời gian tới bạn cũng sẽ giõi như ai!
ANH TUẤN

 

[Nguyễn Duy Tuân]

Em ko biết hàm Vlookup, Hlookup va hàm IF có Ưu Điểm và hạn chế của mấy hàm đó là gì? Ai biết xin chỉ em, em đang rất cần biết rất gấp ah . Cảm ơn nhiều ... Tuni

______________

Bạn xem cách đặt tên đề bài tại đây nha!.

Tôi xin nhận xét ngắn thế này.
Ưu điểm: Không chỉ 2 hàm trên mà tất cả các hmà của Excel đều hướng tới một cách dùng đơn giản, dễ áp dụng. Nói là đơn giản thôi chứ cũng phải học mãi mới áp dụng tốt được.

Nhược điểm: Chỉ khi bạn bắt đầu áp dụng nhiều, mức độ ứng dụng cao thì mới thấy cái nhược điểm chung của các hàm Excel.
Hàm VLookup và HLookup có ý nghĩa như nhau, hàm V* tìm theo chiều dọc (trên xuống dưới) tại cột đầu tiên, H* tìm theo chiều ngang (trái qua phải) và trả về giá trị khi tìm ở cột chỉ định khi tìm thấy.
Không chỉ 2 hàm trên mà tất cả các hàm tìm kiếm trong nhóm Lookup của Excel, điều kiện tìm là điều kiện đơn hay tìm đích danh. Tôi không thể tìm một nhân viêc có số ngày công >20 và số năm công tác là n, hay tìm một nhân viên mà có doanh số bán hàng >1 tỷ....

Nếu ai đó học Access rồi thì thấy không như vậy, nhóm hàm D* của nó rất động,...khó học hơn nhưng làm được những yêu cầu phức tạp.

Cón hàm IF giới hạn chỉ có 7 vòng lõ. Đây là một nhược điểm! Nhưng nếu bạn biết áp dụng thì làm được tất.
Xét một ví dụ kiểu "ngớ ngẩn" thế này: Nếu A là "Nam" thì 100, nếu A là "Nữ" thì là 50. Tôi có hai cách làm như thế này:
= IF(A="Nam",100,IF(A="Nữ,50,0)) --> Có 2 Hàm IF lồng nhau.

Như vậy bạn có thể làm = IF(A="Nam",100,0) + IF(A="Nữ",50,0)

Phân tích logic một chút bạn sẽ thấy A chỉ có thể là 1 trong phương án, chứ không bao giờ cùng xảy ra.

Với cách làm trên các hàm IF không bị bao nhau mà vẫn ra đúng kết quả.

Trong trường hợp có n điều kiện, ta có thể phân tích một chút theo hướng trên sẽ Ok.

------------------------------------------------------------------------
Lan man ngoài vấn đề của bài tôi nói thêm về nhược điểm của một số chức năng hay của Excel.
- Tất cả các phương thức lọc - Filter của Excel cũng chỉ lọc được trên một sổ duy nhất chứ không tạo được quan hệ, kết hợp.
- Chức năng PivotTable rất hay nhưng cũng chỉ tạo trên một sổ đơn không có điều kiện gì cả.
...


Tôi nghĩ để đạt được được ưu điểm chính của Excel làm và nhìn thấy ngay kết quả, một bảng tính thông minh và giản đơn thì mọi cái cũng phải nên đơn giản, vì thế MS đã cố tình không tạo thêm sự phức tạp nữa.

Chỉ những ai có như cầu cao về xử lý dữ liệu thì mấy hạn chế nói trên mới đáng để lo nghĩ mà thôi.


.

 

[phananhson]

Hàm VLOOKUP

Xin hỏi làm cách nào để hàm vlookup trong Excel có thể trả về giá trị trống hoặc #N/A nếu như giá trị dò không có, tôi đã thử và trong mọi trường hợp nếu vlookup dò không có giá trị đều trả về giá trị của ô liền kề trước đó mặc dù value không đúng.

 

[TranThanhPhong]

Chào bạn,

Do tham số cuối của hàm bạn dùng số 1 nên xảy ra vấn đề như bạn nêu, bạn đổi tham số sang số 0.

Tham số cuối của Vlookup dùng là 1 khi và chỉ khi danh sách tham chiếu đã được sắp xếp thứ tự trước.

TP.

 

[anhtuan1066]

VLOOKUP(trị dò, mãng, cột dò, kiểu dò)
Với kiểu dò là 0 thì mới cho kết quả chính xác... Tôi e rằng tham số này bạn đang ghi =1 hoặc -1 gì đó (cũng có thể bạn ko ghi gì chăng? Thử thêm tham số này = 0 xem)... Tóm lại, 1 hàm VLOOKUP đầy đũ phải có 4 tham số: Trị dò, mãng, cột dò, kiểu dò
Với kiểu dò =0, nếu VLOOKUP ko tìm dc giá trị thì sẽ cho kết quả =#N/A... Nếu bạn muốn tránh luôn lỗi này thì dùng thêm hàm ISNA
IF(ISNA(VLOOKUP(gì gì đó....)),"",VLOOKUP(gì gì đó...))
Làm thử xem
ANH TUẤN

 

[tsf]

Chú ý: cứ thích dùng mã bằng kiểu NUMBER thì dễ bị lỗi lắm bạn ạ (Bạn xem help thì biết là tại sao). Bạn nên chọn mã bằng kiểu Text (nếu dùng kiểu số thì bạn phải gõ dấu ' trước số hoặc format kiểu text trước khi nhập liệu).

 

[anhtuan1066]

Ko cần = TRUE đâu Diễm à... ko ghi gì nghĩa là true rồi...
ANH TUẤN

 

[phananhson]

Cám ơn cả nhà.
Lý do hàm Vlookup của mình không dùng được là do mình copy dữ liệu ( Bảng giá chứng khoán trực tuyến) từ 02 trang web khác nhau vào excel nhưng chúng lại không có cùng định dạng phần chữ ở Mã chứng khoán nên không dùng Vlookup được. Mình đã thử đổi sang Text, ... nhưng vẫn không được.
VD: A1=ABT B1=ABT tại C1:=A1=B1 ---> FALSE
Giúp mình với.

 

[minhlev]

Bạn thử tìm trong hai ô đó có khoảng trống không. Nếu có thì xoá toàn bộ khoảng trống đi. Hy vọng là được. Nếu vẫn không được thì bạn up File lên diễn đàn sẽ có người giúp bạn.

 

[anhtuan1066]

Định dạng chử thì ăn nhằm gì... Miễn nó đừng bỏ dấu tiếng Việt là OK hết... Bây giờ nếu có sai chăng là kiễm tra lại giống như minhlev đã nói...
ANH TUẤN

 

[phananhson]

Cám ơn minhlev
Đúng như bạn nói, mình thử căn lề trái + phải thì thấy có khoảng trống nên không dùng Vlookup được, bây giờ thì OK rồi.
Thank so much.

 

[anhtuan1066]

VLOOKUP trên tất cả các sheet!

Còn đây là 1 hàm tự tạo, có tác dụng giống VLOOKUP nhưng nó có thễ dò tìm trên tất cả các sheet... Các bạn xem file, cũng rất dể hiểu... Kết quả tìm dc nằm ở Sheet LookupAll
Mến
ANH TUẤN

 

[digita]

Tôi xin mến tăng anh bạn Anh Tuấn và mọi người khác trong GPE 1 UDF tìm giá trị với nhiều điều kiện.

Syntax:

Mlookup(mảng dò tìm, Mảng 1 = điều kiện 1, Mảng 2 = điều kiện 2, Mảng 3 = điều kiện 3, v.v.)

Tôi đính kèm 1 ví dụ nhỏ để dễ tham khảo. Vậy là ta có thể có cách giải quyết khi hàm lookup không làm được trong trường hợp này.

Mến

 

[ThuNghi]

Hàm VBA Vlookup trên các sh thấy thế nào, tìm từng sh nếu có 3 sh OK thì chỉ lấy sh đầu tiên.
Còn Mlookup thì có thể thế hàm Excel cũng OK
{=Mlookup(D37,A3:A7=H2,B3:B7=H3,C3:C7=H4)}
Ta có thể dùng
=INDEX($D$3:$D$7,SUMPRODUCT(($A$3:$A$7=$H$2)*($B$3:$B$7=$H$3)*($C$3:$C$7=$H$4)*ROW($A$3:$A$7))-ROW($A$2),0)

 

[Nguyễn Duy Tuân]

Thằng này rất hay đấy, liệu có thể biến thành công thức bình thường chứ không phải là công thức mảng được không vậy???

Thân!

Vì các đối số thứ 2 của hàm là biểu thức logic mà vùng = (só sánh) với một giá trị nên bắt buộc phải dùng CT mảng, nếu không lỗi là #VALUE đương nhiên. Hàm này phải dùng cách của CT mảng thì mới có ý nghĩa.

Để làm được hàm này, người làm ra có đẳng cấp cao về lập trình VBA, hiểu rõ cách làm việc của FormulaArray trong Excel.

Mời các bác tham khảo tại đây:
http://www.excelguide.eu/NL_MultipleLookup.html

 

[Mr Okebab]

Vì các đối số thứ 2 của hàm là biểu thức logic mà vùng = (só sánh) với một giá trị nên bắt buộc phải dùng CT mảng, nếu không lỗi là #VALUE đương nhiên. Hàm này phải dùng cách của CT mảng thì mới có ý nghĩa.

Để làm được hàm này, người làm ra có đẳng cấp cao về lập trình VBA, hiểu rõ cách làm việc của FormulaArray trong Excel.

Em không hiểu lắm (vì đầu óc hơi be be be !!!), nhưng hàm sumproduct cũng dùng các mảng như vậy, chỉ có khác là nó sẽ lấy các giá trị thỏa mãn cộng lại với nhau. Nếu chỉ có 1 giá trị thì sẽ là chính giá trị tìm thấy

Hàm trên cũng tìm như vậy, có cái khác là chỉ tìm 1 giá trị, tuy nhiên giá trị có thể là số hoặc chuỗi

Vậy sao sumproduct đâu có dùng mảng đâu ??

 

[Nguyễn Duy Tuân]

Theo nguyên tắc, các giá trị tham gia vào biểu thức tính toán phải có cùng kiểu giá trị. A1:A10 > B1 là sai kiểu, A1:A10 là một vùng (Range) còn B1 là một giá trị (Value)-->khác kiểu. Những trường hợp như vậy, Hàm Excel trả về giá trị #VALUE. Nhấn CTRL+SHIFT+ENTER (Công thức mảng) sẽ cho chạy từng dòng một từ A1->A10 và khi đó nó đồng nhất về kiểu giá trị.

Làm một thí nghiệm kiểm tra vùng A3:A7 có ô nào chứa "Smith" không?
1- Gõ công thức sau và ENTER
=OR(A3:A7="Smith")

2- Gõ công thức sau và CTRL+SHIFT+ENTER
=OR(A3:A7="Smith")

Vậy sao sumproduct đâu có dùng mảng đâu ??

Trường hợp này chắc MS có bí quyết riêng rồi, hic. Tớ phải ngâm cứu thêm đã.

 

[digita]

Không ngờ cái UDF này là đề tài thảo luận khá sôi nổi. Và câu hỏi của anh bạn Okebab rất hay.

Thật tình mà nói khi mới làm quen với XL tôi thấy công thức array là khó hiểu nhất và thường tránh dùng loại này, nếu có thể được.

Khi am tường về cách áp dụng array cho công thức sẽ giúp tận dụng hết cái chức năng tính toán tiềm ẩn trong XL. Giống như bạn chạy xe hơi và bấm nút “turbo overdrive – on” để máy chạy mạnh hơn và tăng giảm ga để xe chạy đầm hơn vậy. Bạn ThuNghi là vua của công thức sumproduct rồi đó.

Theo thiển ý mình thì như vầy. Trong công thức array từng phần tử trong mảng được tính vòng lập từng cái 1. Còn công thức thường thì XL tính theo 1 đơn vị vùng hợp nhất tuy là có nhiều ô (single block of cells). Như thí dụ của anh TuanVNUNI

1- Gõ công thức sau và ENTER
=OR(A3:A7="Smith")

2- Gõ công thức sau và CTRL+SHIFT+ENTER
=OR(A3:A7="Smith")

Thí dụ 1 là công thức thường không cho kết quả tìm trong khi cái thứ 2 là dưới dạng array evaluate từng ô trong mảng A3:A7.

Điều lý thú là khi khi ta gõ hàm sumproduct, mặc dù chi tiết địa chỉ các vùng tính toán và các phần tử vùng đều toàn là dạng array, ta chỉ bấm Enter.

Trở lại câu hỏi của Okebab - có cách nào làm cái UDF này như là 1 công thức bình thường (tức là chỉ gõ Enter thôi). Tôi nghĩ là không. Nếu ai có ý kiến khác thì xin mòi đóng góp để mọi người hiểu thêm về vấn đề nêu ra.

Các bạn có thể vô trang web nầy tham khảo thêm về khái niệm hàm ở dạng array. Dưới trang đó có cho down về cái workbook thí dụ nửa.

http://www.cpearson.com/excel/ArrayFormulas.aspx

 

[connhangheo]

các bác xem cho em hàm vlookup này với

em không hiểu em làm sai chỗ nào trong file excel bên dưới, mong các bác chỉ giáo. Thanks!