Nhờ các Anh, Chị lập công thức tính số ngày nhập hàng. Chân thành cám ơn

nguoi_ham_hoc

Thành viên hoạt động
Tham gia ngày
1 Tháng hai 2017
Bài viết
125
Được thích
83
Điểm
180
Chào các Anh, Chị ở GPE,
Mình cám ơn Anh quocgiacan đã nhắc nhở. Bây giờ mình bổ sung thêm dữ liệu của các dữ liệu khác (như mã vật tư VT0001024, VT0001030, VT0001045, VT0001057, VT0001063) và mình lập ra chủ đề mới với tên phù hợp với nội quy của diễn đàn.
Mình mong các Anh, Chị Admin, MOD diễn đàn xóa bài của chủ đề trước giúp.
Mình đang có vấn đề này, mình mong các Anh, Chị giúp đỡ cho mình
Cụ thể mình đang lập bảng theo dõi việc nhập xuất vật tư để đặt hàng cho việc sử dụng ở lần tiếp theo.
Trong file Excel mình gửi đính kèm thì :
Ở dòng ngày 29/8/2016 là mình có phiếu nhập (N) nhập vật tư VT0001024 (giả sử mình lấy thời gian 29/8/2016 làm thời gian đầu tiên mình nhập vật tư VT0001024 vào kho).
Ở dòng ngày 16/10/2016, mình có phiếu nhập (N) nhập vật tư VT0001024. Như thế sau khoảng thời gian 48 ngày, mình sẽ đặt hàng vật tư VT0001024.
Tương tự với các mã vật tư VT0001030, VT0001045, VT0001057, VT0001063.
Mình gặp khó khăn trong việc lập công thức trong bảng theo dõi việc nhập xuất vật tư này, vì :
-Mình không biết dùng công thức nào của Excel để lập công thức. Trong file mình gửi đính kèm thì có 02 điều kiện đó là loại phiếu N (N : phiếu nhập) và số lượng phiếu nhập >= 1. Hàm SumProduct thì mình có tham khảo trên diễn đàn GPE này, mình biết chút chút công thức này (cộng 2 điều kiện, đếm có điều kiện). Nhưng trường hợp này mình loay hoay mãi với hàm SumProduct hoài không ra.
-Trong file đính kèm, sau khoảng thời gian bao nhiêu ngày thì mình sẽ có nhập được vật tư VT0001024 (thời gian không cố định : 48 ngày, 17 ngày, .... xem file đính kèm).
-Tương tự với các mã vật tư VT0001030, VT0001045, VT0001057, VT0001063.
Trường hợp này, mình có các mã vật tư VT0001030, VT0001045, VT0001057, VT0001063, mình chỉ theo dõi sau bao nhiêu ngày mình sẽ đặt hàng cho 01 loại vật tư là cụ thể nào đó (chẳng hạn cho VT0001024 mà mình không biết làm sao)+-+-+-+. Nếu trường hợp, trong bảng theo dõi việc nhập xuất vật tư để đặt hàng, mình có nhiều loại vật tư thì mình cũng không biết làm sao luôn ?+-+-+-++-+-+-++-+-+-+
Nay mình mong các Anh, Chị giúp đỡ cho mình trường hợp này.
Chân thành cám ơn.
 

File đính kèm

nguoi_ham_hoc

Thành viên hoạt động
Tham gia ngày
1 Tháng hai 2017
Bài viết
125
Được thích
83
Điểm
180
Mình cám ơn anh HieuCD nhé. Nhưng sau khi mình add công thức của HieuCD vào thì không đúng rồi -+*/.
Mình gửi lại file đính kèm. Trong file đính kèm có 2 sheets, 01 sheet thì chưa áp dụng công thức, 01 sheet đã dùng công thức của anh HieuCD chỉ dẫn.
 

File đính kèm

quocgiacan

-⍟-
Tham gia ngày
22 Tháng ba 2008
Bài viết
3,397
Được thích
5,901
Điểm
860
Nơi ở
Sài Gòn
Mình cám ơn anh HieuCD nhé. Nhưng sau khi mình add công thức của HieuCD vào thì không đúng rồi -+*/.
Mình gửi lại file đính kèm. Trong file đính kèm có 2 sheets, 01 sheet thì chưa áp dụng công thức, 01 sheet đã dùng công thức của anh HieuCD chỉ dẫn.
Bạn phải đặt công thức của anh HieuCD ngay tại K4.

Khi xét tại cột C, dòng nào có chữ "N", thì nó sẽ truy lục lại phía trên dòng nào có: cùng Mã và gần nhất, để lấy ngày rồi trừ với ngày của dòng đang tại.

Bạn xem lại file kèm tôi đã dùng "filter" cột C để bạn dễ thấy hơn.

Chúc bạn ngày vui.
 

File đính kèm

Ba Tê

Gội Rồi Mới Cạo
Tham gia ngày
5 Tháng năm 2009
Bài viết
11,560
Được thích
16,505
Điểm
1,860
Tuổi
61
Nơi ở
An Giang
Mình cám ơn anh HieuCD nhé. Nhưng sau khi mình add công thức của HieuCD vào thì không đúng rồi -+*/.
Mình gửi lại file đính kèm. Trong file đính kèm có 2 sheets, 01 sheet thì chưa áp dụng công thức, 01 sheet đã dùng công thức của anh HieuCD chỉ dẫn.
- Công thức ghi rõ là K4=......
Bạn lại copy công thức trên vào ô K7, "trớt quớt" là phải rồi.
- Vừa vào GPE thấy chuyện "màu mè vui quá" nên tôi làm "tào lao" với VBA.
Có 1 nút bấm, 2 ô chọn L2, M2. Bạn xem cho vui nhé.
 

File đính kèm

Lần chỉnh sửa cuối:

nguoi_ham_hoc

Thành viên hoạt động
Tham gia ngày
1 Tháng hai 2017
Bài viết
125
Được thích
83
Điểm
180
Sorry mọi người nhé. Mình đúng thật là ngớ ngẩn quá rồi. Sáng nay thức dậy trễ, thấy anh HieuCD đưa công thức, mình quá mừng rỡ, nhìn lộn số 4 trên Stt với số 4 của dòng Excel thành ra mình làm trớt quớt.
@ anh Ba Tê ơi : em làm màu mè để em cho mọi người biết là em có 5 mã vật tư cần tính thời gian nhập hàng tiếp theo đó mà. hihihi
Dù sao đi nữa, em cám ơn mọi người nhé.
 

nguoi_ham_hoc

Thành viên hoạt động
Tham gia ngày
1 Tháng hai 2017
Bài viết
125
Được thích
83
Điểm
180
Các Anh, Chị ơi,
Sau khi xem xong công thức của anh HieuCD, mình đã suy nghĩ hoài và có 01 vài thắc mắc (chắc trình độ Excel mình còn kém), mong anh HieuCD hay các Anh, Chị khác giúp đỡ giải thích cho mình hiểu thêm, cụ thể :
1/ trong hàm tìm kiếm LOOKUP, biến số Lookup_Value (tạm dịch là giá trị tìm kiếm), vì sao anh HieuCD lại chọn 2 ? Có thể chọn số khác được không ? Ý nghĩa của việc chọn 2 trong biến số của hàm LOOKUP này là gì ?
2/ Trong Excel, công thức ở dòng (hàng) thứ 34 này, ở hàm LOOKUP, biến số Lookup_vector, mình thấy anh HieuCD có ghi "... 1/($F$3:F33=F34)/($C$3:C33="N")...". Cho mình hỏi vì sao mình lấy 1 chia cho các hàng của cột F và cột C đó ? Chuyện đó có ý nghĩa như thế nào ? Việc lấy 1 chia cho các hàng của cột F và cột C có tác dụng để làm gì ?
Mình đọc Example của hàm LOOKUP thì mình thấy biến số Lookup_vector Excel lấy từ vùng đến vùng, làm cho mình bối rối và thắc mắc.
Nay mình rất mong anh HieuCD và các Anh, Chị khác giúp đỡ giải thích cho mình hiểu được thông suốt hơn.
Cám ơn rất nhiều.
 

quocgiacan

-⍟-
Tham gia ngày
22 Tháng ba 2008
Bài viết
3,397
Được thích
5,901
Điểm
860
Nơi ở
Sài Gòn
Các Anh, Chị ơi,
Sau khi xem xong công thức của anh HieuCD, mình đã suy nghĩ hoài và có 01 vài thắc mắc (chắc trình độ Excel mình còn kém), mong anh HieuCD hay các Anh, Chị khác giúp đỡ giải thích cho mình hiểu thêm, cụ thể :
1/ trong hàm tìm kiếm LOOKUP, biến số Lookup_Value (tạm dịch là giá trị tìm kiếm), vì sao anh HieuCD lại chọn 2 ? Có thể chọn số khác được không ? Ý nghĩa của việc chọn 2 trong biến số của hàm LOOKUP này là gì ?
2/ Trong Excel, công thức ở dòng (hàng) thứ 34 này, ở hàm LOOKUP, biến số Lookup_vector, mình thấy anh HieuCD có ghi "... 1/($F$3:F33=F34)/($C$3:C33="N")...". Cho mình hỏi vì sao mình lấy 1 chia cho các hàng của cột F và cột C đó ? Chuyện đó có ý nghĩa như thế nào ? Việc lấy 1 chia cho các hàng của cột F và cột C có tác dụng để làm gì ?
Mình đọc Example của hàm LOOKUP thì mình thấy biến số Lookup_vector Excel lấy từ vùng đến vùng, làm cho mình bối rối và thắc mắc.
Nay mình rất mong anh HieuCD và các Anh, Chị khác giúp đỡ giải thích cho mình hiểu được thông suốt hơn.
Cám ơn rất nhiều.
Hàm này trên diễn đàn có nói nhiều, cũng do thầy NDU hướng dẫn, bạn có thể xem:
Bài 12:
http://www.giaiphapexcel.com/forum/showthread.php?73521-Có-khi-nào-hàm-Lookup-không-thể-thay-thế-được-Vlookup&p=450126#post450126

Bài 33:
http://www.giaiphapexcel.com/forum/showthread.php?73521-Có-khi-nào-hàm-Lookup-không-thể-thay-thế-được-Vlookup&p=459069#post459069

Bài 6:
http://www.giaiphapexcel.com/forum/showthread.php?46905-HelP-Tìm-kiếm-giá-trị-với-2-ô-điều-kiện-Lookupvalue&p=300128#post300128

Tuy nhiên, vì thấy bạn có nick name dễ thương, nên tôi gom lại thành 1 chỗ để bạn tiện học hỏi, như sau:
(Trích dẫn và chú thêm theo giải thích của thầy ndu96081631 )

Dò tìm nhiều điều kiện:


Cú pháp chung:
=LOOKUP(
2, 1/(Biểu thức điều kiện 1)/(Biểu thức điều kiện 2)/.... /(Biểu thức điều kiện n) , Mảng cần lấy kết quả)


.....1/(Biểu thức điều kiện 1)/(Biểu thức điều kiện 2)/.... /(Biểu thức điều kiện n): Được gọi là "Véc tơ" chỉ hướng
  1. Dựa vào biểu thức toán học: 1/n/m thì bằng 1/(n*m) (Vd: 1/2/8 = 1/ (2*8) = 1/ 16 = 0.0625 )
  2. Với các biểu thức điều kiện, để giản lược, ta có thể áp dụng toán tử "*" (nhân) thay cho hàm AND(), và toán tử "+" (cộng) thay cho hàm OR().
  3. Các biểu thức điều kiện này, theo từng phần của biểu thức thực hiện, sẽ trả kết quả từng mảng chứa các trị hoặc Đúng (TRUE hay 1) hoặc sai (FALSE hay 0), sau đó nó kết hợp lại như hàm AND(): 1/ (Mảng1 * Mảng2 *....* Mảngn). Ví dụ:
    • 1/ ( {1;1;0;0;1;1} * {1;0;0;0;1;0} ) => 1/ ({1;0;0;0;1;0}) => {1;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!}
    • Do kết quả có hai giá trị 1 (xanh và đỏ), thì "Véc tơ" chỉ hướng sẽ báo cho LOOKUP chọn theo số 1 sau cùng (màu đỏ), Ví dụ như trên thì đếm theo số dòng nó ở là: 5, LOOKUP() sẽ lấy giá trị tương ứng với dòng 5 trong "Mảng cần lấy kết quả". Tức là khi có nhiều kết quả phù hợp với các điều kiện trong các biểu thức mang lại, "Véc tơ" chỉ hướng sẽ chọn kết quả phù hợp đứng thứ tự cuối cùng.
    • Để ý rằng các kết quả trả về trong "Véc tơ" chỉ hướng có thể chứa nhiều kiểu giá trị: số, chuỗi, luận lý (True-False), lỗi (#DIV/0!, #N/A....), tuy nhiên nó chỉ xét và tính trên các kết quả kiểu: số, chuỗi và luận lý, nó "phớt lờ" bỏ qua không tính các giá trị Lỗi. Đây là điểm mạnh của hàm LOOKUP(), nhờ điều này mà ta lọc ra được các giá trị phù hợp để truy xuất dữ liệu mà không cần phải dùng thêm bất kỳ hàm phụ trợ nào khác.
.....Tại sao là số 2?
  1. Do biểu thức "Véc tơ" chỉ hướng dạng: 1/n, ta biết: 1/n<=1 , nên kết quả có số lớn nhất là 1. Nên "giá trị tìm" cho LOOKUP() ta có thể đặt bất cứ số nào tùy ý, miễn sao >1.
  2. Nói chung, tùy theo giá trị kết quả tại biểu thức "Véc tơ" chỉ hướng mà ta đặt số "giá trị tìm" cho phù hợp, tức đặt giá trị tìm lúc nào cũng lớn hơn hoặc bằng giá trị lớn nhất mà "Véc tơ" chỉ hướng trả về.

  • Lưu ý:
    1. So sánh với kiểu truy lục của VLOOKUP(trị dò, bảng dò, cột dò, kiểu dò):
      1. Với kiểu dò = 0 là dò chính xác,
      2. Với kiểu dò = 1 là dò không chính xác, và bắt buộc dữ liệu phải được sort trước.
    2. Vậy thì:
      1. LOOKUP(trị dò, bảng dò, bảng kết quả) sẽ tương đương với VLOOKUP mà kiểu dò =1 ---> như vậy cũng như VLOOKUP, bắt buộc dữ liệu phải sort trước.
      2. LOOKUP(2, 1/(....), ) sẽ tương đương với VLOOKUP mà kiểu dò =0 ---> Cũng như VLOOKUP, dữ liệu không cần sort.

  • So sánh với các hàm truy lục khác:
    • VLOOKUP hoặc HLOOKUP: chỉ so được 1 điều kiện, hoặc có thể nhiều điều kiện NHƯNG các điều kiện này được kết hợp giản đơn với toán tử &. Ví dụ:
      • VLOOKUP("mục1" & "mục2" &..."mụcN", CHOOSE({1,2}, "Vùng1" & "Vùng2" &..."VùngN", Mảng lấy kết quả ),2,0) nhấn Ctrl+Shift+Enter
    • SUMPRODUCT:
      • Nếu tìm thấy từ 2 kết quả trở lên (dạng Number) thì SUMPRODUCT sẽ cộng dồn, dẫn đến sai kết quả
      • Nếu giá trị cần tìm thuộc dạng Text, đương nhiên không thể dùng SUMPRODUCT
    • INDEX + MATCH:
      • Nếu dùng INDEX + MATCH với dò tìm nhiều điều kiện, có thể bạn phải dùng tổ hợp phím Ctrl+Shift+ Enter (trong khi LOOKUP chỉ cần Enter bình thường)
      • Nếu tìm thấy từ 2 kết quả trở lên thì LOOKUP sẽ lấy kết quả cuối mà nó tìm thấy, còn INDEX + MATCH sẽ lấy kết quả đầu tiên.

  • Những biến thể LOOKUP() trong thực tế áp dụng:
    • LOOKUP("zzzzz", Vùng/Mảng so, [Mảng cần lấy kết quả]) --> Ra kết quả đơn
    • LOOKUP(Mảng sắp đặt DÒNG theo điều kiện, Mảng chứa số dòng, Mảng cần lấy kết quả) --> Ra kết quả 1 Mảng.

Chúc bạn ngày vui. /-*+//-*+//-*+/
 
Lần chỉnh sửa cuối:

nguoi_ham_hoc

Thành viên hoạt động
Tham gia ngày
1 Tháng hai 2017
Bài viết
125
Được thích
83
Điểm
180
Dạ, em cám ơn thầy quocgiacan nhiều.
Em chúc thầy và gia đình luôn mạnh khỏe và hạnh phúc.
 

quocgiacan

-⍟-
Tham gia ngày
22 Tháng ba 2008
Bài viết
3,397
Được thích
5,901
Điểm
860
Nơi ở
Sài Gòn
Dạ, em cám ơn thầy quocgiacan nhiều.
Em chúc thầy và gia đình luôn mạnh khỏe và hạnh phúc.
Trời đất qủy thần ơi! --=0--=0--=0

Bạn "đôn" tôi lên chi cao vậy, mới chấn thương cột sống còn nằm nhà, nên sợ cao lắm. Khà khà khà. }}}}}}}}}}}}}}}

"Thầy" tôi chỉ để dành gọi thầy NDU mà thôi. /-*+//-*+//-*+/
 

Đỗ Tú Anh

Thành viên tiêu biểu
Tham gia ngày
17 Tháng mười 2017
Bài viết
535
Được thích
356
Điểm
235
Nơi ở
The dream world
Nếu tìm thấy từ 2 kết quả trở lên thì LOOKUP sẽ lấy kết quả cuối mà nó tìm thấy, còn INDEX + MATCH sẽ lấy kết quả đầu tiên.
Chào quocgiacan,
Trước khi đi vô vấn đề chính, xin cho mình mạn phép “lan man” 1 xíu. Phải nói là mình hiếm khi gặp ai hết lòng vì mọi người như bạn. Mình không chắc điều gì chứ mình chắc chắn 1 điều là sau này bạn sẽ “bất tử” trong con tim của anh em bè bạn. Chúc mừng! (Congratulation!);)

Okay, trở lại với điều mình muốn thắc mắc. Đây sẽ là bài viết dài, dài & rất dài, xin thông cảm vì cái tính “chàng giang đại hải”, xin đừng phiền lòng!

Câu hỏi 1:
upload_2017-11-6_18-57-59.png
Như đã liệt kê ở trên, những chỗ mình đặt dấu chấm hỏi là những chỗ mình còn vướng mắc chưa thông.

  • Nếu LOOKUP(trị dò, bảng dò, bảng kết quả) & bảng dò được sort ascending thì có phải là Lookup sẽ dò từ trên xuống & lấy cell đầu tiên thỏa điều kiện???
  • LOOKUP(trị dò, bảng dò, bảng kết quả) & bảng dò không được sort ascending thì Lookup sẽ dò như thế nào??? Lúc thì mình thấy Lookup dò từ trên xuống & lấy giá trị đầu tiên, lúc thì lấy giá trị cuối cùng (xin xem file đính kèm).

Ví dụ 1
upload_2017-11-6_19-1-23.png
= LOOKUP(C3,B3:B18,A3:A18)
Vì bảng dò không được sort ascending → tìm 360 trong 1 cột bắt đầu là 200, kế là 300, kế là 20, kế là 432.
→ Lookup dò từ trên xuống thấy số đầu tiên lớn hơn 360 là 432, nó sẽ lùi lại lấy số bên trên 432 là 20. Chiếu sang cột A là 10
→ Trong trường hợp này vì bảng dò không có sort thì Lookup sẽ dò từ trên xuống & lấy số đầu tiên.
Bài 23 - http://www.giaiphapexcel.com/dienda...-không-thể-thay-thế-được-vlookup.73521/page-2

Ví dụ 2

upload_2017-11-6_19-3-52.png

=LOOKUP(C22,A22:A29,B22:B29) → tìm 220 trong 1 cột bắt đầu là 100, kế là 150, kế là 220, kế là 160, kế là 220, kế là 600.
→ Qua khỏi 220 thứ nhì, nó đã thấy 600 (là số đầu tiên > 220), nó cho rằng theo quy luật tăng dần thì hết rồi, nên nó quay lại.
Có phải vì bảng dò không có sort nên Lookup không dừng lại tại 220 đầu tiên & vì 600 > 220 nên nó mới dừng lại tại 220 thứ 2 chứ không phải 220 đầu tiên???
Bài 31 - http://www.giaiphapexcel.com/dienda...-không-thể-thay-thế-được-vlookup.73521/page-2
Mình có thử thay thế 150 = 900 (hình bên dưới) nhưng Lookup vẫn không dừng ở 220 đầu tiên mà lại dừng ở 220 thứ 2 → quy trình làm việc của Lookup trong trường hợp bảng dò không có sort là mỗi lúc 1 kiểu à???
upload_2017-11-6_19-6-57.png
Còn thêm 2 ví dụ mà mình liệt kê trong file (Ex. 2)
→ Vẫn chưa hiểu cách vận hành của Lookup nếu bảng dò không có sort???
→ Nếu bảng dò không có sort → Lookup(2,1...)

Ví dụ 3 - (Ex. 3 trong file)
Bài 38 - http://www.giaiphapexcel.com/dienda...-không-thể-thay-thế-được-vlookup.73521/page-2
VMH0307 lại kết luận là → Với dữ liệu không được sort trước thì chỉ nên dùng trong trường hợp tìm vị trí (hoặc giá trị) tương ứng với số cuối cùng của Lookup_vector (bảng dò) với Lookup_value (trị dò) luôn > các giá trị của Lookup_vector.
→ Sao kết luận này về cách vận hành của Lookup ngược với cách vận hành của Lookup trong ví dụ 1 vậy??? (hic hic, chắc bị “Tẩu Wả Nhập Ma” wá!!!)
upload_2017-11-6_19-10-33.png

Câu hỏi 2:
Bài 4 - http://www.giaiphapexcel.com/dienda...i-2-ô-điều-kiện-lookupvalue.46905/#post300128
upload_2017-11-6_19-15-12.png
Ex. 5 trong file
I2=LOOKUP(2,1/(($C$16:$C$200=$B2)*($D$16:$D$200=$D2)),$E$16:$E$200)
Tại sao chỉ có 10 dòng thôi mà Biểu thức điều kiện 1 & Biểu thức điều kiện 2 lại đặt tới $C$200 & $D$200 & $E$200 luôn vậy? Xin giải thích dùm mình lý lẽ đằng sau vì mình nghĩ mãi mà không ra. Chân thành cảm ơn.:)
Ex. 6 cũng là câu hỏi tương tự.
Xin lỗi vì cái post này hơi “dài dòng lê thê”, bị là mình muốn giải thích cặn kẽ chỗ mình chưa có thông. Xin đừng phiền lòng!
Chúc bạn một buối tối tràn đầy niềm vui. :):):)

 

File đính kèm

Đỗ Tú Anh

Thành viên tiêu biểu
Tham gia ngày
17 Tháng mười 2017
Bài viết
535
Được thích
356
Điểm
235
Nơi ở
The dream world
Trời đất qủy thần ơi! --=0--=0--=0
Bạn "đôn" tôi lên chi cao vậy, mới chấn thương cột sống còn nằm nhà, nên sợ cao lắm. Khà khà khà. }}}}}}}}}}}}}}}
"Thầy" tôi chỉ để dành gọi thầy NDU mà thôi. /-*+//-*+//-*+/
Chắc là lúc ấy bạn đau lắm nhỉ, sao xui dzữ dzậy??? :(
Giờ còn sợ cao hôn?
Người đầu tiên “dắt tay mình những bước đầu chập chững” với Excel cũng là bạn vì vậy đối với mình, bạn cũng là thầy. Vừa là thầy, vừa là bạn. Đừng sợ cao nữa!!!
Chúc bạn một buổi tối “dzui thiệt là dzui” :D:D:D
 

hoangtechu

Thành viên hoạt động
Tham gia ngày
8 Tháng bảy 2011
Bài viết
105
Được thích
48
Điểm
370
Hàm này trên diễn đàn có nói nhiều, cũng do thầy NDU hướng dẫn, bạn có thể xem:
Bài 12:
http://www.giaiphapexcel.com/forum/showthread.php?73521-Có-khi-nào-hàm-Lookup-không-thể-thay-thế-được-Vlookup&p=450126#post450126

Bài 33:
http://www.giaiphapexcel.com/forum/showthread.php?73521-Có-khi-nào-hàm-Lookup-không-thể-thay-thế-được-Vlookup&p=459069#post459069

Bài 6:
http://www.giaiphapexcel.com/forum/showthread.php?46905-HelP-Tìm-kiếm-giá-trị-với-2-ô-điều-kiện-Lookupvalue&p=300128#post300128

Tuy nhiên, vì thấy bạn có nick name dễ thương, nên tôi gom lại thành 1 chỗ để bạn tiện học hỏi, như sau:
(Trích dẫn và chú thêm theo giải thích của thầy ndu96081631 )

Dò tìm nhiều điều kiện:


Cú pháp chung:
=LOOKUP(
2, 1/(Biểu thức điều kiện 1)/(Biểu thức điều kiện 2)/.... /(Biểu thức điều kiện n) , Mảng cần lấy kết quả)


.....1/(Biểu thức điều kiện 1)/(Biểu thức điều kiện 2)/.... /(Biểu thức điều kiện n): Được gọi là "Véc tơ" chỉ hướng
  1. Dựa vào biểu thức toán học: 1/n/m thì bằng 1/(n*m) (Vd: 1/2/8 = 1/ (2*8) = 1/ 16 = 0.0625 )
  2. Với các biểu thức điều kiện, để giản lược, ta có thể áp dụng toán tử "*" (nhân) thay cho hàm AND(), và toán tử "+" (cộng) thay cho hàm OR().
  3. Các biểu thức điều kiện này, theo từng phần của biểu thức thực hiện, sẽ trả kết quả từng mảng chứa các trị hoặc Đúng (TRUE hay 1) hoặc sai (FALSE hay 0), sau đó nó kết hợp lại như hàm AND(): 1/ (Mảng1 * Mảng2 *....* Mảngn). Ví dụ:
    • 1/ ( {1;1;0;0;1;1} * {1;0;0;0;1;0} ) => 1/ ({1;0;0;0;1;0}) => {1;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!}
    • Do kết quả có hai giá trị 1 (xanh và đỏ), thì "Véc tơ" chỉ hướng sẽ báo cho LOOKUP chọn theo số 1 sau cùng (màu đỏ), Ví dụ như trên thì đếm theo số dòng nó ở là: 5, LOOKUP() sẽ lấy giá trị tương ứng với dòng 5 trong "Mảng cần lấy kết quả". Tức là khi có nhiều kết quả phù hợp với các điều kiện trong các biểu thức mang lại, "Véc tơ" chỉ hướng sẽ chọn kết quả phù hợp đứng thứ tự cuối cùng.
    • Để ý rằng các kết quả trả về trong "Véc tơ" chỉ hướng có thể chứa nhiều kiểu giá trị: số, chuỗi, luận lý (True-False), lỗi (#DIV/0!, #N/A....), tuy nhiên nó chỉ xét và tính trên các kết quả kiểu: số, chuỗi và luận lý, nó "phớt lờ" bỏ qua không tính các giá trị Lỗi. Đây là điểm mạnh của hàm LOOKUP(), nhờ điều này mà ta lọc ra được các giá trị phù hợp để truy xuất dữ liệu mà không cần phải dùng thêm bất kỳ hàm phụ trợ nào khác.
.....Tại sao là số 2?
  1. Do biểu thức "Véc tơ" chỉ hướng dạng: 1/n, ta biết: 1/n<=1 , nên kết quả có số lớn nhất là 1. Nên "giá trị tìm" cho LOOKUP() ta có thể đặt bất cứ số nào tùy ý, miễn sao >1.
  2. Nói chung, tùy theo giá trị kết quả tại biểu thức "Véc tơ" chỉ hướng mà ta đặt số "giá trị tìm" cho phù hợp, tức đặt giá trị tìm lúc nào cũng lớn hơn hoặc bằng giá trị lớn nhất mà "Véc tơ" chỉ hướng trả về.

  • Lưu ý:
    1. So sánh với kiểu truy lục của VLOOKUP(trị dò, bảng dò, cột dò, kiểu dò):
      1. Với kiểu dò = 0 là dò chính xác,
      2. Với kiểu dò = 1 là dò không chính xác, và bắt buộc dữ liệu phải được sort trước.
    2. Vậy thì:
      1. LOOKUP(trị dò, bảng dò, bảng kết quả) sẽ tương đương với VLOOKUP mà kiểu dò =1 ---> như vậy cũng như VLOOKUP, bắt buộc dữ liệu phải sort trước.
      2. LOOKUP(2, 1/(....), ) sẽ tương đương với VLOOKUP mà kiểu dò =0 ---> Cũng như VLOOKUP, dữ liệu không cần sort.

  • So sánh với các hàm truy lục khác:
    • VLOOKUP hoặc HLOOKUP: chỉ so được 1 điều kiện, hoặc có thể nhiều điều kiện NHƯNG các điều kiện này được kết hợp giản đơn với toán tử &. Ví dụ:
      • VLOOKUP("mục1" & "mục2" &..."mụcN", CHOOSE({1,2}, "Vùng1" & "Vùng2" &..."VùngN", Mảng lấy kết quả ),2,0) nhấn Ctrl+Shift+Enter
    • SUMPRODUCT:
      • Nếu tìm thấy từ 2 kết quả trở lên (dạng Number) thì SUMPRODUCT sẽ cộng dồn, dẫn đến sai kết quả
      • Nếu giá trị cần tìm thuộc dạng Text, đương nhiên không thể dùng SUMPRODUCT
    • INDEX + MATCH:
      • Nếu dùng INDEX + MATCH với dò tìm nhiều điều kiện, có thể bạn phải dùng tổ hợp phím Ctrl+Shift+ Enter (trong khi LOOKUP chỉ cần Enter bình thường)
      • Nếu tìm thấy từ 2 kết quả trở lên thì LOOKUP sẽ lấy kết quả cuối mà nó tìm thấy, còn INDEX + MATCH sẽ lấy kết quả đầu tiên.

  • Những biến thể LOOKUP() trong thực tế áp dụng:
    • LOOKUP("zzzzz", Vùng/Mảng so, [Mảng cần lấy kết quả]) --> Ra kết quả đơn
    • LOOKUP(Mảng sắp đặt DÒNG theo điều kiện, Mảng chứa số dòng, Mảng cần lấy kết quả) --> Ra kết quả 1 Mảng.

Chúc bạn ngày vui. /-*+//-*+//-*+/
ôi các cao nhân , đọc thôi em cũng đau cả đầu anh @Phan Thế Hiệp ạ.
Cảm ơn các anh GPE nhiều lắm
 

congnt92

Thành viên hoạt động
Tham gia ngày
29 Tháng năm 2011
Bài viết
172
Được thích
158
Điểm
380
Tuổi
28
Chào quocgiacan,
mình muốn giải thích cặn kẽ chỗ mình chưa có thông. Xin đừng phiền lòng!
Vô tình tìm được bài post này nhờ anh Hiệp dẫn qua từ đây
Để trả lời ngắn gọn: tất cả các ví dụ ở trên đều chỉ đơn giản 1 chữ: dữ liệu không được sort.
Khi dùng lookup mà dữ liệu không được sort thì đơn giản là kết quả sẽ bị sai, không có 1 giải thích phù hợp nào về cơ chế dò nào ở đây cả.
Quote từ M$
I
The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.
M$ chỉ nói, nếu không sort tăng dần thì kết quả có thể bị sai. Còn sai theo "cơ chế" nào thì không nói. Luật không nói thì không nên cố phán đoán và giải thích. Vì mỗi lúc sẽ cho 1 kiểu.
Với dữ liệu không sort, tại sao dùng lookup(value,1/...) lại cho kết quả đúng??
Trả lời: Vì khi thực hiện phép chia 1/(...), mặc nhiên kết quả của bảng dò chỉ còn là 1 và #DIV/0!, vì lookup bỏ qua lỗi nên bảng dò này chỉ toàn là phần tử "1"
Hàm lookup luôn "giả định" bảng dò đã sort tăng dần rồi, và nếu giả định này sai thì kết quả sai, giả định đúng thì cho kết quả đúng. Với bảng dò {1;1;1;1;1;1}, lookup giả định bảng dò đã được sort tăng dần và dĩ nhiên là giả định này đúng (chẳng qua là các giá trị bằng nhau mà thôi).
Khi đó, nếu tìm thấy trị dò, lookup trả về kết quả chính xác, nếu không tìm thấy trị dò hoặc tìm được nhiều trị dò thì lookup trả về kết quả ứng với giá trị lớn nhất trong bảng dò mà giá trị này nhỏ hơn hoặc bằng trị dò.
Trong bảng dò {1;1;1;1;1;1}, vì đã giả định sort tăng dần (giả định đúng) nên nghiễm nhiên số 1 sau cùng là giá trị lớn nhất.
Đây là cách giải thích duy nhất, chứ không có khái niệm lúc thì lookup dò trên xuống, lúc dò dưới lên.
 

File đính kèm

Lần chỉnh sửa cuối:

hoangtechu

Thành viên hoạt động
Tham gia ngày
8 Tháng bảy 2011
Bài viết
105
Được thích
48
Điểm
370
Vô tình tìm được bài post này nhờ anh Hiệp dẫn qua từ đây
Để trả lời ngắn gọn: tất cả các ví dụ ở trên đều chỉ đơn giản 1 chữ: dữ liệu không được sort.
Khi dùng lookup mà dữ liệu không được sort thì đơn giản là kết quả sẽ bị sai, không có 1 giải thích phù hợp nào về cơ chế dò nào ở đây cả.
Quote từ M$
I
M$ chỉ nói, nếu không sort tăng dần thì kết quả có thể bị sai. Còn sai theo "cơ chế" nào thì không nói. Luật không nói thì không nên cố phán đoán và giải thích. Vì mỗi lúc sẽ cho 1 kiểu.
Với dữ liệu không sort, tại sao dùng lookup(value,1/...) lại cho kết quả đúng??
Trả lời: Vì khi thực hiện phép chia 1/(...), mặc nhiên kết quả của bảng dò chỉ còn là 1 và #DIV/0!, vì lookup bỏ qua lỗi nên bảng dò này chỉ toàn là phần tử "1"
Hàm lookup luôn "giả định" bảng dò đã sort tăng dần rồi, và nếu giả định này sai thì kết quả sai, giả định đúng thì cho kết quả đúng. Với bảng dò {1;1;1;1;1;1}, lookup giả định bảng dò đã được sort tăng dần và dĩ nhiên là giả định này đúng (chẳng qua là các giá trị bằng nhau mà thôi).
Khi đó, nếu tìm thấy trị dò, lookup trả về kết quả chính xác, nếu không tìm thấy trị dò hoặc tìm được nhiều trị dò thì lookup trả về kết quả ứng với giá trị lớn nhất trong bảng dò mà giá trị này nhỏ hơn hoặc bằng trị dò.
Trong bảng dò {1;1;1;1;1;1}, vì đã giả định sort tăng dần (giả định đúng) nên nghiễm nhiên số 1 sau cùng là giá trị lớn nhất.
Đây là cách giải thích duy nhất, chứ không có khái niệm lúc thì lookup dò trên xuống, lúc dò dưới lên.
Chào anh,
em thấy file excel của anh ở ô D6, nhưng giá trị dò tìm lại là C5 ==> có phải anh nhầm hay không ạ?
Nếu như giải thích trong cột F của anh , thì giá trị dò tìm là C6 và phải bằng 201 , nhưng em nhập vào đó nó báo lỗi N/A .
Anh và anh @Phan Thế Hiệp có thể giải thích giúp em được không ạ?
Em cảm ơn các anh nhiều.
 

File đính kèm

congnt92

Thành viên hoạt động
Tham gia ngày
29 Tháng năm 2011
Bài viết
172
Được thích
158
Điểm
380
Tuổi
28
Chào anh,
em thấy file excel của anh ở ô D6, nhưng giá trị dò tìm lại là C5 ==> có phải anh nhầm hay không ạ?
Nếu như giải thích trong cột F của anh , thì giá trị dò tìm là C6 và phải bằng 201 , nhưng em nhập vào đó nó báo lỗi N/A .
Anh và anh @Phan Thế Hiệp có thể giải thích giúp em được không ạ?
Em cảm ơn các anh nhiều.
Xin lỗi. Đúng là mình nhầm ở cách lấy ví dụ cho trường hợp ở dòng 6. Mình đã sửa lại file ở bài viết trên.
Cảm ơn bạn đã góp ý.
 
Tham gia ngày
10 Tháng mười 2017
Bài viết
2,868
Được thích
8,438
Điểm
360
Nơi ở
Sài Gòn
Chào anh,
em thấy file excel của anh ở ô D6, nhưng giá trị dò tìm lại là C5 ==> có phải anh nhầm hay không ạ?
Nếu như giải thích trong cột F của anh , thì giá trị dò tìm là C6 và phải bằng 201 , nhưng em nhập vào đó nó báo lỗi N/A .
Anh và anh @Phan Thế Hiệp có thể giải thích giúp em được không ạ?
Em cảm ơn các anh nhiều.
Như bài #8 có ghi:
  • LOOKUP(trị dò, bảng dò, bảng kết quả) sẽ tương đương với VLOOKUP mà kiểu dò =1 ---> như vậy cũng như VLOOKUP, bắt buộc dữ liệu phải sort trước.
  • LOOKUP(2, 1/(....), ) sẽ tương đương với VLOOKUP mà kiểu dò =0 ---> Cũng như VLOOKUP, dữ liệu không cần sort.
Như bạn đã biết VLOOKUP( 'Giá trị tìm' , 'Vùng So và Kết quả' , 'Cột cần lấy' , 'Kiểu dò' ):
  • Với Kiểu dò= 0 (hay FALSE), thì Vlookup() sẽ dò giá trị chính xác trong 'Vùng So và Kết quả', mà Vùng so này không cần Sort trước. Ví dụ: =VLOOKUP(15,A1: B6,2,0)--> tìm số 15 trong Vùng A1: B6= {5,"A";20,"C";15,"F";2,"GH";1,"L";8,"K"}, thì nó sẽ tìm đến đúng giá trị 15, để lấy ra "F". Tìm giá trị 1, dứt khoát nó ra "L". Nếu tìm giá trị 7, do không có số 7 trong Vùng A1: A6 nên báo lỗi #N/A.
  • Với Kiểu dò= 1 (hay TRUE), thì Vlookup() sẽ dò "giá trị trong khoảng" (tức có cận dưới và cận trên) trong 'Vùng So và Kết quả', mà Vùng so này bắt buộc phải được Sort trước. Ví dụ: =VLOOKUP(16,A1: B6,2,1)--> tìm giá trị 16 trong Vùng A1:B6= {1,"L";2,"GH";5,"A";8,"K";15,"F";20,"C"}, ta thấy cột A1:A6 chứa {1;2;5;8;15;20} đã được sắp xếp từ nhỏ đến lớn, khi ta so giá trị 16 do không có giá trị bằng nó ở trong A1: A6, nó tìm đến khoảng phù hợp là khoảng giá trị từ >=15 và <20, và do 16 không vượt qua mốc 20, nên nó sẽ lấy mốc thấp hơn là mốc 15, và qua cột B lấy ra giá trị "F"; Nếu giá trị tìm là 37, =VLOOKUP(37,A1: B6,2,1), số 37 lớn hơn mốc 20, và không có số nào giới hạn nó nữa thì nó lấy mốc 20, trả giá trị cột B tương ứng là "C".
Tương tự vậy:
  • =VLOOKUP(15,A1: B6,2,0) ~tương đương~ Lookup(2,1/(15=A1:A6),B1:B6) --> tức "Dò giá trị chính xác", và bảng dò không cần sort. Chỉ có 1 điều khác nhau giữa hai hàm ở chỗ: Nếu có nhiều giá trị tìm khớp trong bảng dò thì Vlookup() lấy giá trị của lần so khớp đầu tiên, còn Lookup() lấy giá trị so khớp cuối cùng.
  • =VLOOKUP(16,A1: B6,2,1) ~tương đương~ Lookup(16, A1:A6 , B1:B6) --> tức "Dò giá trị trong khoảng", và bảng dò PHẢI sort trước.
Thân
 

File đính kèm

Lần chỉnh sửa cuối:

hoangtechu

Thành viên hoạt động
Tham gia ngày
8 Tháng bảy 2011
Bài viết
105
Được thích
48
Điểm
370
Như bài #8 có ghi:

Như bạn đã biết VLOOKUP( 'Giá trị tìm' , 'Vùng So và Kết quả' , 'Cột cần lấy' , 'Kiểu dò' ):
  • Với Kiểu dò= 0 (hay FALSE), thì Vlookup() sẽ dò giá trị chính xác trong 'Vùng So và Kết quả', mà Vùng so này không cần Sort trước. Ví dụ: =VLOOKUP(15,A1: B6,2,0)--> tìm số 15 trong Vùng A1: B6= {5,"A";20,"C";15,"F";2,"GH";1,"L";8,"K"}, thì nó sẽ tìm đến đúng giá trị 15, để lấy ra "F". Tìm giá trị 1, dứt khoát nó ra "L". Nếu tìm giá trị 7, do không có số 7 trong Vùng A1: A6 nên báo lỗi #N/A.
  • Với Kiểu dò= 1 (hay TRUE), thì Vlookup() sẽ dò "giá trị trong khoảng" (tức có cận dưới và cận trên) trong 'Vùng So và Kết quả', mà Vùng so này bắt buộc phải được Sort trước. Ví dụ: =VLOOKUP(16,A1: B6,2,1)--> tìm giá trị 16 trong Vùng A1:B6= {1,"L";2,"GH";5,"A";8,"K";15,"F";20,"C"}, ta thấy cột A1:A6 chứa {1;2;5;8;15;20} đã được sắp xếp từ nhỏ đến lớn, khi ta so giá trị 16 do không có giá trị bằng nó ở trong A1: A6, nó tìm đến khoảng phù hợp là khoảng giá trị từ >=15 và <20, và do 16 không vượt qua mốc 20, nên nó sẽ lấy mốc thấp hơn là mốc 15, và qua cột B lấy ra giá trị "F"; Nếu giá trị tìm là 37, =VLOOKUP(37,A1: B6,2,1), số 37 lớn hơn mốc 20, và không có số nào giới hạn nó nữa thì nó lấy mốc 20, trả giá trị cột B tương ứng là "C".
Tương tự vậy:
  • =VLOOKUP(15,A1: B6,2,0) ~tương đương~ Lookup(2,1/(15=A1:A6),B1:B6) --> tức "Dò giá trị chính xác", và bảng dò không cần sort. Chỉ có 1 điều khác nhau giữa hai hàm ở chỗ: Nếu có nhiều giá trị tìm khớp trong bảng dò thì Vlookup() lấy giá trị của lần so khớp đầu tiên, còn Lookup() lấy giá trị so khớp cuối cùng.
  • =VLOOKUP(16,A1: B6,2,1) ~tương đương~ Lookup(16, A1:A6 , B1:B6) --> tức "Dò giá trị trong khoảng", và bảng dò PHẢI sort trước.
Thân
em cảm ơn anh @Phan Thế Hiệp và các tiền bối đã chỉ bảo ạ
 
Lần chỉnh sửa cuối:
Top Bottom