Thắc mắc: Hạn chế của hàm Vlookup khi tham chiếu dữ liệu trùng

[VetMini]

Của bác con thử và đã ra kết quả đúng rồi.
Mà cho con hỏi tại sao con làm công thức như này lại không ra đúng:
...

Cái khổ ở GPE này là xài công thức mẹo riết, người ta quên mất công thức căn bản.
Đọc lại cách dùng hàm Lookup căn bản sẽ thấy rằng hàm này bắt buộc mảng dò phải được sắp xếp.

 

[xuongrongdat]

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ả 1 và 2 đều thoả điều kiện >=1

Cái khổ ở GPE này là xài công thức mẹo riết, người ta quên mất công thức căn bản.
Đọc lại cách dùng hàm Lookup căn bản sẽ thấy rằng hàm này bắt buộc mảng dò phải được sắp xếp.

Trước giờ con thấy mọi người xài Lookup mà không biết đâu là xài mẹo, đâu là xài căn bản. Chỉ nghĩ là có nhiều cách dùng. Không lẽ Sumproduct cũng vậy?!
Con có tìm kiếm và xem lại cái bài giải thích ở trên của bác về hàm Lookup.
Nhưng con chưa biết cái này: thế nào là mảng sắp xếp và thế nào là mảng không sắp xếp. Nhờ bác giải thích thêm.

 

[batman1]

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ả 1 và 2 đều thoả điều kiện >=1

Không chính xác bác ạ. Tôi đã cho ví dụ khi n = 1 thì kết quả sai.

Bài #3 trong

Cho mình hỏi cấu trúc hàm lookup

Mình search công thức nhưng không hiểu dấu / Bạn nào giải thích dùm mình cấu trúc hàm được không: I8=LOOKUP(2,1/(F8=Tienlai!$A$4:$A$64)/(G8=Tienlai!$B$4:$B$64),Tienlai!$D$4:$D$64)

www.giaiphapexcel.com

Với cấu trúc lookup(n, 1/...) thì bắt buộc n > 1. Tức vd. n = 1,1. Đơn giản hơn nên lấy n = 2. Còn n = 1 sẽ sai.

Vd.

Kết quả sai



Kết quả đúng

 

[hungtin1997]

Mọi người cho mình hỏi trường hợp sau, tại ô E3 nếu mình vẫn muốn dùng Vlookup thì làm sao để cho nó ra đúng kết quả được ạ?
Với ngoài Vlookup và Sumif ra thì còn cách nào khác gọn bằng hoặc gọn hơn không?
View attachment 248249

Mặc dù chỉ là minh họa nhưng đây là trường hợp thực tế. Nguyễn Văn A là người lập Bảng lương cho toàn công ty nên bị vướng tình huống trên.
Xin cảm ơn.

Chào bạn,
Nếu dùng công thức đơn giản trực quang thì mình bày như sau:

Thêm cột phụ "Tần Suất" và "STT"​

Công thức tại ô A2 là =COUNTIF($B$2:B2,B2)&"-"&B2​

Công thức tại ô F2 là =IFERROR(VLOOKUP(E2&"-"&$F$1,$A$2:$C$17,3,0),0)​

Fill xuống rồi Sum thôi, có thể dùng Conditional Formating để ẩn mấy cái không cần thiết.​

​

 

[xuongrongdat]

Chào bạn,
Nếu dùng công thức đơn giản trực quang thì mình bày như sau:

Thêm cột phụ "Tần Suất" và "STT"​

Công thức tại ô A2 là =COUNTIF($B$2:B2,B2)&"-"&B2​

Công thức tại ô F2 là =IFERROR(VLOOKUP(E2&"-"&$F$1,$A$2:$C$17,3,0),0)​

Fill xuống rồi Sum thôi, có thể dùng Conditional Formating để ẩn mấy cái không cần thiết.​

Bạn hiểu sai "đề bài" của mình á, hi. Bạn xem bài #11 mình có giải thích rõ hơn á. Đây là Bảng lương Khối văn phòng. Mỗi họ tên chỉ xuất hiện có 1 lần hà. Duy có cái ông Lập bảng lương á, xuất hiện rất nhiều lần chỗ ký tên, vì có nhiều bảng lương của nhiều phòng ban; và ổng lại là người của 1 phòng. Nên khi lập danh sách chuyển khoản gửi ngân hàng mình dùng vlookup dò tiền lương nó bị sai cái trường hợp của cái ông lập biểu.

Bài này mọi người đã giúp mình dùng những hàm sau đều giải quyết được hết rồi đó bạn: max+if, lookup, sumif.

Cảm ơn bạn nhiều nhé.

 

[tigertiger]

Tình huống thực tế mình gặp đây:
View attachment 248255

Cái ông tô vàng ổng lập nhiều biểu bên trên và bên dưới nữa, của các phòng ban, đơn vị khác trong công ty. Bị trùng cột B.
Xong cái mình qua sheet khác làm danh sách chuyển khoản tiền lương cho toàn công ty nên dò ngược sang sheet tính lương này. Dùng vlookup sẽ bị trùng tên cái ông tô vàng nên bị hiện thành 0, thay vì phải dò ra số tiền lương của ổng.

Thôi mình xài Sumif vậy. Tại muốn dùng Vlookup, hi. Cảm ơn mọi người nhiều.

Sao phải nhọc vậy nhỉ, cái ông lập biểu lại đưa vào đúng vị trí cột họ Tên , Nên đặt ông ấy ở cột khác ví dụ A
- Hơn nữa đấy là bảng in (Report) thì mới có người lập biểu, mà bản in thì tính toán tìm kiếm làm chi nữa - Nên phân định phần tính toán chung ở 1 sheet TinhToan khác
Như thế khỏi phải công thức phức tạp, nặng file

 

[xuongrongdat]

Nhưng con chưa biết cái này: thế nào là mảng sắp xếp và thế nào là mảng không sắp xếp. Nhờ bác giải thích thêm.

@VetMini : con tìm kiếm tiếp thì tự thấy tự trả lời được luôn rồi bác:

 

[tigertiger]

Sao phải nhọc vậy nhỉ, cái ông lập biểu lại đưa vào đúng vị trí cột họ Tên , Nên đặt ông ấy ở cột khác ví dụ A
- Hơn nữa đấy là bảng in (Report) thì mới có người lập biểu, mà bản in thì tính toán tìm kiếm làm chi nữa - Nên phân định phần tính toán chung ở 1 sheet TinhToan khác
Như thế khỏi phải công thức phức tạp, nặng file

Còn nếu không làm được như trên, thì thay ông Lập biểu đi , Lập biểu gì mà không tìm được chính mình

 

[xuongrongdat]

Sao phải nhọc vậy nhỉ, cái ông lập biểu lại đưa vào đúng vị trí cột họ Tên , Nên đặt ông ấy ở cột khác ví dụ A
- Hơn nữa đấy là bảng in (Report) thì mới có người lập biểu, mà bản in thì tính toán tìm kiếm làm chi nữa - Nên phân định phần tính toán chung ở 1 sheet TinhToan khác
Như thế khỏi phải công thức phức tạp, nặng file

Bảng tính toán với bảng in đang là 1 á bác.
Còn đặt ông ấy sang cột A là con phải cut paste 52 chỗ (cả khối gián tiếp lẫn trực tiếp) rồi canh cho nó ngay ngắn ở mỗi biểu nữa. Nó nhọc hơn là chỉ chỉnh công thức con dò tìm qua. Gửi ngân hàng chuyển khoản nó có cái mẫu biểu khác nữa.

Còn nếu không làm được như trên, thì thay ông Lập biểu đi , Lập biểu gì mà không tìm được chính mình

Thôi đừng thay bác ưi, tội nghiệp con. Con cũng đang trên đường tìm lại chính mình, hi.

Cảm ơn bác nhiều.

 

[tigertiger]

Bảng tính toán với bảng in đang là 1 á bác.
Còn đặt ông ấy sang cột A là con phải cut paste 52 chỗ (cả khối gián tiếp lẫn trực tiếp) rồi canh cho nó ngay ngắn ở mỗi biểu nữa. Nó nhọc hơn là chỉ chỉnh công thức con dò tìm qua. Gửi ngân hàng chuyển khoản nó có cái mẫu biểu khác nữa.

Thôi đừng thay bác ưi, tội nghiệp con. Con cũng đang trên đường tìm lại chính mình, hi.

Cảm ơn bác nhiều.

Nên tách vấn đề tính toán ra riêng phần in (report)

Chỉnh lại vị trí 1 lần dùng mãi mãi và máy không phải làm gì nữa
Chỉnh công tức tiếp kiệm được vài phút - Nhưng máy phải tính toán cả đời file

 

[VetMini]

Không chính xác bác ạ. Tôi đã cho ví dụ khi n = 1 thì kết quả sai.
...

Ừ nhỉ. Tôi nhầm rồi. Vậy thì bắt buộc phải dùng số lớn hơn 1.
Có lẽ phép search của MS nó không thuần nhị phân mà có thêm phép tối ưu nào đó (ví dụ đoán theo độ cân bằng của cây nhị phân?).