Hàm Lookup

Liên hệ QC

mai07

Thành viên mới
Tham gia
2/6/10
Bài viết
38
Được thích
3
Gía trị 2 trong hàm Lookup ô H2 này dựa vào cơ sở nào ah?

=LOOKUP(2;1/($A$2:$A$72=A2);$E$2:$E$72)

Bác nào giải thích hộ em Worksheet trong Name với ?
 

File đính kèm

  • DSPS - Copy.xlsx
    17.4 KB · Đọc: 1,048
File này thấy quen quen.--=0 Hình như mình đã giải đáp bài này.

Bạn hình dung thế này:

A2:A72 là vùng ký tự gồm, ví dụ: {A,A,A,B,B,C,...}

E2:E72 là vùng số gồm, ví dụ: {1,7,6,4,2,9,...}

Câu hỏi đặt ra là tìm số bên cột E tương ứng ký tự A cuối cùng bên cột A. Số đó là số 6.

Vậy gán ký tự A cho số 1, còn lại là số 0, bằng cách dùng (A2:A72="A"), tạo thành mảng "ảo" khi

so sánh từng ký tự trong vùng A2:A72 với A, trả về mảng

{TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,...} với TRUE=1, FALSE=0. (Mảng ảo không tồn tại

trên 1 vùng cụ thể trên sheet, mà ở trong memory)

Sau đó tìm cách loại bỏ các giá trị FALSE (=0) chỉ giữ lại giá trị TRUE (=1) bằng cách dùng

1/(A2:A72="A") để trả về mảng {1,1,1,#DIV/0,#DIV/0,#DIV/0,...}

Đến đây ta có 2 mảng :

mảng X = {1,1,1,#DIV/0,#DIV/0,#DIV/0,...}

mảng Y = {1,7,6,4,2,9,...}

Bài toán trở thành tìm số 1 cuối cùng ở mảng này, trả về kết quả tương ứng ở mảng kia.

Có nhiều cách, tuy nhiên người ta thường dùng LOOKUP với giá trị tìm gần đúng:

LOOKUP(n,mảng X, mảng Y), với n là số có giá trị lớn hơn 1, ví dụ là 2.

Mảng X chỉ bao gồm các giá trị 1 và giá trị lỗi, LOOKUP gần đúng sẽ tham chiếu đến giá trị

gần đúng cuối cùng, tức là giá trị 1 thứ 3 trong mảng X, và trả về giá trị thứ 3 trong mảng Y,

là số 6.
 
File này thấy quen quen.--=0 Hình như mình đã giải đáp bài này.


Có nhiều cách, tuy nhiên người ta thường dùng LOOKUP với giá trị tìm gần đúng:

LOOKUP(n,mảng X, mảng Y), với n là số có giá trị lớn hơn 1, ví dụ là 2.

Mảng X chỉ bao gồm các giá trị 1 và giá trị lỗi, LOOKUP gần đúng sẽ tham chiếu đến giá trị

gần đúng cuối cùng, tức là giá trị 1 thứ 3 trong mảng X, và trả về giá trị thứ 3 trong mảng Y,

là số 6.
ôi đúng câu hỏi đi hỏi bõ khác

vay n ở đay có thể là 1 số khác >2 đuoc ko?
 
Thay vì hỏi, bạn có thể thí nghiệm để có câu trả lời (nhanh hơn là chờ đợi người khác)

Ôi bác khó tính quá hihi lúc đó em online bằng đt nên ko tự kiểm tra được
Thật ra ý em muốn hỏi là nếu sử dụng số khác 2 thì có làm thay đổi chức năng của hàm đó không, vì lúc chưa đọc bài này em đang hiểu lầm số 2 đó là lookup với 2 điều kiện, đọc xong bài này thì vẫn chưa hiểu lắm chức năng của n này nhưng hiểu vế 1/(...) rồi và tự tin hơn khi sử dụng hàm này
 
Có ai vui lòng giải thích cho mình ý nghĩa của công thức này được không, tại sao lại là 10^10 hả các cao thủ? cảm ơn
 
Lý do là đây ạ:

[FONT=&quot]LOOKUP(lookup_value,lookup_vector,result_vector)
[/FONT] [FONT=&quot]Lưu ý:[/FONT]
  • [FONT=&quot]Lookup_vector phải được sắp xếp theo thứ tự tăng dần như: -2,-1,0,1,2; A,B,C..Z; FALSE, TRUE. Nếu không, kết quả trả về nhiều khi không chính xác.[/FONT]
  • [FONT=&quot]Nếu như không tìm thấy Lookup_value trong vùng dò tìm, hàm sẽ lấy giá trị lớn nhất có trong vùng dò tìm (Lookup_vector) mà có giá trị nhỏ hơn hoặc bằng giá trị dò tìm Lookup_vector.[/FONT]
  • [FONT=&quot]Nếu giá trị nhỏ nhất trong vùng dò tìm Lookup_vector mà lớn hơn giá trị của lookup_value thì hàm báo lỗi #N/A.


    --> Số 2 ở đây vì khi đặt 1/...... nghĩa là số đó sẽ luôn nhỏ hơn 1, vậy nên sẽ ko có giá trị nào = 2 được.
    --> Tương tự 10^10 là số rất lớn [/FONT]
 
A2:A72 là vùng ký tự gồm, ví dụ: {A,A,A,B,B,C,...}

E2:E72 là vùng số gồm, ví dụ: {1,7,6,4,2,9,...}

Câu hỏi đặt ra là tìm số bên cột E tương ứng ký tự A cuối cùng bên cột A. Số đó là số 6
vậy nếu câu hỏi đặt ra trong ví dụ của bác là " tim giá trị bên cột E tương ứng với ký tự A đầu tiên bên cột A" thì công thức nó sẽ là như thế nào ạ?
 
File này thấy quen quen.--=0 Hình như mình đã giải đáp bài này.

Bạn hình dung thế này:

A2:A72 là vùng ký tự gồm, ví dụ: {A,A,A,B,B,C,...}

E2:E72 là vùng số gồm, ví dụ: {1,7,6,4,2,9,...}

Câu hỏi đặt ra là tìm số bên cột E tương ứng ký tự A cuối cùng bên cột A. Số đó là số 6.

Vậy gán ký tự A cho số 1, còn lại là số 0, bằng cách dùng (A2:A72="A"), tạo thành mảng "ảo" khi

so sánh từng ký tự trong vùng A2:A72 với A, trả về mảng

{TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,...} với TRUE=1, FALSE=0. (Mảng ảo không tồn tại

trên 1 vùng cụ thể trên sheet, mà ở trong memory)

Sau đó tìm cách loại bỏ các giá trị FALSE (=0) chỉ giữ lại giá trị TRUE (=1) bằng cách dùng

1/(A2:A72="A") để trả về mảng {1,1,1,#DIV/0,#DIV/0,#DIV/0,...}

Đến đây ta có 2 mảng :

mảng X = {1,1,1,#DIV/0,#DIV/0,#DIV/0,...}

mảng Y = {1,7,6,4,2,9,...}

Bài toán trở thành tìm số 1 cuối cùng ở mảng này, trả về kết quả tương ứng ở mảng kia.

Có nhiều cách, tuy nhiên người ta thường dùng LOOKUP với giá trị tìm gần đúng:

LOOKUP(n,mảng X, mảng Y), với n là số có giá trị lớn hơn 1, ví dụ là 2.

Mảng X chỉ bao gồm các giá trị 1 và giá trị lỗi, LOOKUP gần đúng sẽ tham chiếu đến giá trị

gần đúng cuối cùng, tức là giá trị 1 thứ 3 trong mảng X, và trả về giá trị thứ 3 trong mảng Y,

là số 6.

xin lỗi lại lôi lên bài này, nhưng bác có thể giải thích hộ em 1/... ý nghĩa thế nào được không
 
Lý do là đây ạ:

[FONT=&amp]LOOKUP(lookup_value,lookup_vector,result_vector)
[/FONT] [FONT=&amp]Lưu ý:[/FONT]
  • [FONT=&amp]Lookup_vector phải được sắp xếp theo thứ tự tăng dần như: -2,-1,0,1,2; A,B,C..Z; FALSE, TRUE. Nếu không, kết quả trả về nhiều khi không chính xác.[/FONT]
  • [FONT=&amp]Nếu như không tìm thấy Lookup_value trong vùng dò tìm, hàm sẽ lấy giá trị lớn nhất có trong vùng dò tìm (Lookup_vector) mà có giá trị nhỏ hơn hoặc bằng giá trị dò tìm Lookup_vector.[/FONT]
  • [FONT=&amp]Nếu giá trị nhỏ nhất trong vùng dò tìm Lookup_vector mà lớn hơn giá trị của lookup_value thì hàm báo lỗi #N/A.


    --> Số 2 ở đây vì khi đặt 1/...... nghĩa là số đó sẽ luôn nhỏ hơn 1, vậy nên sẽ ko có giá trị nào = 2 được.
    --> Tương tự 10^10 là số rất lớn [/FONT]

xin lỗi lại lôi lên bài này, nhưng bác có thể giải thích hộ em 1/... ý nghĩa thế nào được không?
 
Có ai vui lòng giải thích cho mình ý nghĩa của công thức này được không, tại sao lại là 10^10 hả các cao thủ? cảm ơn
10^10 is a very large number. When the LOOKUP() function gets a very large number as a lookup_value input, then it searches for the first number in the lookup_array from right to left. In searching from right to left, it ignores text, errors and blanks cells. You also use other large munber like 1E+100
 
Lý do là đây ạ:

[FONT=&quot]LOOKUP(lookup_value,lookup_vector,result_vector)
[/FONT] [FONT=&quot]Lưu ý:[/FONT]
  • [FONT=&quot]Lookup_vector phải được sắp xếp theo thứ tự tăng dần như: -2,-1,0,1,2; A,B,C..Z; FALSE, TRUE. Nếu không, kết quả trả về nhiều khi không chính xác.[/FONT]
  • [FONT=&quot]Nếu như không tìm thấy Lookup_value trong vùng dò tìm, hàm sẽ lấy giá trị lớn nhất có trong vùng dò tìm (Lookup_vector) mà có giá trị nhỏ hơn hoặc bằng giá trị dò tìm Lookup_vector.[/FONT]
  • [FONT=&quot]Nếu giá trị nhỏ nhất trong vùng dò tìm Lookup_vector mà lớn hơn giá trị của lookup_value thì hàm báo lỗi #N/A.


    --> Số 2 ở đây vì khi đặt 1/...... nghĩa là số đó sẽ luôn nhỏ hơn 1, vậy nên sẽ ko có giá trị nào = 2 được.
    --> Tương tự 10^10 là số rất lớn [/FONT]
cho em hỏi với ạ,
TẠI SAO HÀM LOOKUP LẠI TÌM 2, VÀ 1
và khi nào mình dùng 1 và khi nào mình dùng 2 ạ.Hôm nay được làm quen với hàm lookup đến chỗ giá trị lookup_value thì vẫn chưa hiểu rõ lắm.
o_Oo_Oo_O
 
cho em hỏi với ạ,
TẠI SAO HÀM LOOKUP LẠI TÌM 2, VÀ 1
và khi nào mình dùng 1 và khi nào mình dùng 2 ạ.Hôm nay được làm quen với hàm lookup đến chỗ giá trị lookup_value thì vẫn chưa hiểu rõ lắm.
o_Oo_Oo_O
Thay vì tô đậm, phông lớn đọc nhức con mắt bỏ bố thì bạn cho thử ví dụ câu hỏi của mình, người ta sẽ trả lời dễ hơn.
Tự dưng rồi hỏi "tìm 2, tìm 1" mà có thấy mặt mũi cái công thức đâu? Thớt này có cả chục bài, muốn nói công thức nào? Có chắc là cái công thức ấy nó "tìm 2, tìm 1"?
 
Thay vì tô đậm, phông lớn đọc nhức con mắt bỏ bố thì bạn cho thử ví dụ câu hỏi của mình, người ta sẽ trả lời dễ hơn.
Tự dưng rồi hỏi "tìm 2, tìm 1" mà có thấy mặt mũi cái công thức đâu? Thớt này có cả chục bài, muốn nói công thức nào? Có chắc là cái công thức ấy nó "tìm 2, tìm 1"?
dạ vâng em xin rút kinh nghiệm lần sau ạ ,em gửi file đính kém câu hỏi của em ạ. Em có thử tìm trên google rất ít về phần lookup_value trong hàm lookup tại sao là 1 và 2,...Và tại sao lai dùng như thế ạ, và khi nào nên dùng lookup_value = 1 và khi nào dùng số khác ạ
Em cám ơn
 

File đính kèm

  • hoi_dap.xlsx
    9.9 KB · Đọc: 15
Lookup(1, 1/...) hoặc Lookup(2, 1/...) là một xảo thuật (lưu ý từ xảo thuật có nghĩa là một mánh khoé, không phải là kỹ thuật chính thống) thường dùng cho một trong hai trường hợp sau đây:

1. tìm với nhiều điều kiện (ở phạm vi thớt này không thấy nói tới)

2. tìm với mảng không được sắp xếp và muốn tìm trị xuất hiện cuối cùng thay vì đầu tiên (hàm Match cũng dò được mảng không sắp xếp nhưng nó lấy trị đầu tiên)

Muốn biết tại sao có cái vụ 2,1 thì phải hiểu nguyên tắc của dò mảng sắp xếp.
Đối với mảng không sắp xếp, các hàm dò sẽ dò theo tuyến tính (linear search). Tức là dò từ đầu đến cuối, tới chỗ match thì dừng.
Đối với mảng sắp sếp, các hàm sẽ dò theo nhị phân (binary search).

Lookup có ba đặc tính sau đây:
1. nó là hàm mảng ngầm. Tức là nó tự biết triển khai các con toán mảng thành kết quả mảng.
2. nó luôn coi như mảng dò đã sắp xếp, tức là nó sẽ dò theo nhị phân.
3. nó sẽ bỏ qua các trị số error

Xảo thuật mà người ta lợi dụng là:
- Lookup(n, 1/...) dò tìm số n trong mảng tạo ra bởi con tính 1/...
- vì biểu thức (...) là biểu thức so sánh cho nên kết quả chỉ trả về {true, false, ...}. Nhưng ô thoả điều kiện trả về true, và nhưng ô không thoả thì trả về false.
- vì True/False có tính chất lượng nên Excel tự đặt luật để số lượng hoá True thành 1 và False thành 0 khi cần thiết phải làm toán số lượng. Như vậy, đem làm mẫu số cho con toán 1/... thì biểu thức sẽ tự số lượng hoá nó thành {1, 0,... } để có thể thực hiện phép chia.
- như vậy, biểu thức 1/{true, false, true, ...} sẽ thành { 1/1, 1/0, 1/1, ... }, và kế tiếp là { 1, error, 1, ... }
- Lookup sau đó sẽ cố tìm trị n trong mảng { 1, error, 1, ... }. Lưu ý là ở đây sẽ có người cho rằng không phải error, mà Lookup đã tự động biến error thành 0. Tôi không tranh cãi, bởi vì điều này không quan trọng, miễn nó không lớn hơn 1 là không ảnh hưởng đến phép dò nhị phân.
- Khi dò nhị phân một trị n trong mảng { ... }, phép dò sẽ dừng lại ở phần tử bên phải cùng và thoả điều kiện <= n. Lưu ý đây là tính chất dò nhị phân của MS chứ không nên kết luận do nó dò từ bên phải.
- trong mảng { 1, error, 1, ... } nếu n >= 1 thì phép dò sẽ dừng lại ở phần tử đầu tiên bên phải có trị là 1. Và đó chính là phần tử ở vị trí ô thoả các điều kiện ban đầu đặt ra.

Suy ra, theo lý thuyết, ở trường hợp của bạn thì Lookup(1, 1/...) hay Lookup(2, 1/...) đều như nhau. Cả 12 đều thoả điều kiện >=1
 
Lookup(1, 1/...) hoặc Lookup(2, 1/...) là một xảo thuật (lưu ý từ xảo thuật có nghĩa là một mánh khoé, không phải là kỹ thuật chính thống) thường dùng cho một trong hai trường hợp sau đây:

1. tìm với nhiều điều kiện (ở phạm vi thớt này không thấy nói tới)

2. tìm với mảng không được sắp xếp và muốn tìm trị xuất hiện cuối cùng thay vì đầu tiên (hàm Match cũng dò được mảng không sắp xếp nhưng nó lấy trị đầu tiên)

Muốn biết tại sao có cái vụ 2,1 thì phải hiểu nguyên tắc của dò mảng sắp xếp.
Đối với mảng không sắp xếp, các hàm dò sẽ dò theo tuyến tính (linear search). Tức là dò từ đầu đến cuối, tới chỗ match thì dừng.
Đối với mảng sắp sếp, các hàm sẽ dò theo nhị phân (binary search).

Lookup có ba đặc tính sau đây:
1. nó là hàm mảng ngầm. Tức là nó tự biết triển khai các con toán mảng thành kết quả mảng.
2. nó luôn coi như mảng dò đã sắp xếp, tức là nó sẽ dò theo nhị phân.
3. nó sẽ bỏ qua các trị số error

Xảo thuật mà người ta lợi dụng là:
- Lookup(n, 1/...) dò tìm số n trong mảng tạo ra bởi con tính 1/...
- vì biểu thức (...) là biểu thức so sánh cho nên kết quả chỉ trả về {true, false, ...}. Nhưng ô thoả điều kiện trả về true, và nhưng ô không thoả thì trả về false.
- vì True/False có tính chất lượng nên Excel tự đặt luật để số lượng hoá True thành 1 và False thành 0 khi cần thiết phải làm toán số lượng. Như vậy, đem làm mẫu số cho con toán 1/... thì biểu thức sẽ tự số lượng hoá nó thành {1, 0,... } để có thể thực hiện phép chia.
- như vậy, biểu thức 1/{true, false, true, ...} sẽ thành { 1/1, 1/0, 1/1, ... }, và kế tiếp là { 1, error, 1, ... }
- Lookup sau đó sẽ cố tìm trị n trong mảng { 1, error, 1, ... }. Lưu ý là ở đây sẽ có người cho rằng không phải error, mà Lookup đã tự động biến error thành 0. Tôi không tranh cãi, bởi vì điều này không quan trọng, miễn nó không lớn hơn 1 là không ảnh hưởng đến phép dò nhị phân.
- Khi dò nhị phân một trị n trong mảng { ... }, phép dò sẽ dừng lại ở phần tử bên phải cùng và thoả điều kiện <= n. Lưu ý đây là tính chất dò nhị phân của MS chứ không nên kết luận do nó dò từ bên phải.
- trong mảng { 1, error, 1, ... } nếu n >= 1 thì phép dò sẽ dừng lại ở phần tử đầu tiên bên phải có trị là 1. Và đó chính là phần tử ở vị trí ô thoả các điều kiện ban đầu đặt ra.

Suy ra, theo lý thuyết, ở trường hợp của bạn thì Lookup(1, 1/...) hay Lookup(2, 1/...) đều như nhau. Cả 12 đều thoả điều kiện >=1
[/QU
em cám ơn anh nhé, 1 bài viết rất hay ạ :clapping:
 
Web KT
Back
Top Bottom