Làm sao SD ham Vlookup cho TH này?

[Thiên Thanh]

Các bạn cho mình hỏi: Giả sử mình bán một mặt hàng A cho nhiều khách hàng X,Y,Z. Mình tạo 1 sheet Dlieu (có NMUA-MH-DGIA) và sheet THDT mình SD hàm Vlookup để tìm Dgiá mặt hàng A để tính DT, nhưng giá trị tìm đc chỉ là số liệu Dgia của khách hàng ở dòng đầu tiên. Vậy có cách nào mình có thể tìm chính xác Dgia của mặt hàng A cho từng khách hàng X,Y,Z ko? Mình có đọc qua hàm SUMPRODUCT nhưng chưa hiểu cách Sd lắm, ko biết có áp dụng trong TH này đc ko nhỉ??

 

[workman]

Các bạn cho mình hỏi: Giả sử mình bán một mặt hàng A cho nhiều khách hàng X,Y,Z. Mình tạo 1 sheet Dlieu (có NMUA-MH-DGIA) và sheet THDT mình SD hàm Vlookup để tìm Dgiá mặt hàng A để tính DT, nhưng giá trị tìm đc chỉ là số liệu Dgia của khách hàng ở dòng đầu tiên. Vậy có cách nào mình có thể tìm chính xác Dgia của mặt hàng A cho từng khách hàng X,Y,Z ko? Mình có đọc qua hàm SUMPRODUCT nhưng chưa hiểu cách Sd lắm, ko biết có áp dụng trong TH này đc ko nhỉ??

Tôi hiểu là bạn muốn Lookup theo 2 điều kiện, nhưng Vlookup lại chỉ cho bạn gõ 1 điều kiện thôi. Tôi giả định 1 khách hàng của bạn chỉ có 1 đơn giá cho 1 mặt hàng thôi.

Cách dễ nhất làm bạn dùng hàm mảng {=SUM((A1=sheetDlieu!$A$1:$A$60)*(B1=sheetDlieu!$B$1:$B$60)*(sheetDlieu!$C$1:$C$60))}. Nhớ đây làm hàm mảng nhé, lưu ý nhấn Ctrl+Shift+Enter
Cách thứ 2 củ chuối hơn, nhưng nếu bạn không rành mảng thì vấn áp dụng được. Bạn insert thêm 1 cột vào sheet Dlieu. Sau đó ban hành công thức: NMUA&MH vào cái cột mới insert.
Bạn dùng vlookup như thế này: vlookup(A1&B1,Dlieu,2,0).

Bạn thử đi nhé

 

[handung107]

{=SUM((A1=sheetDlieu!$A$1:$A$60)*(B1=sheetDlieu!$B $1:$B$60)*(sheetDlieu!$C$1:$C$60))}
Hãy thay bằng hàm SUMPRODUCT, sẽ đỡ tốn bộ nhớ hơn.
=SUMPRODUCT((A1=sheetDlieu!$A$1:$A$60)*(B1=sheetDlieu!$B $1:$B$60)*(sheetDlieu!$C$1:$C$60))
Và hàm này sử dụng bình thường, không cần gõ Ctrl + Shift + Enter

 

[workman]

Ở chị Handung107 giỏi quá, có thế mà tôi nghĩ không ra. Easy thinking quen thói, riết rồi không chịu suy nghĩ gì hết trơn, cứ theo lối cũ làm mãi.

 

[Thiên Thanh]

Rất cám ơn chị HanDung107 và Workman, em đã làm đc rồi, loay hoay cả tháng trời mà chưa nghĩ ra cách nào để làm, may mà có mọi người giúp, xong rồi lại thấy đơn giản ghê.

 

[adam_tran]

Sao bạn không tạo sheet Dlieu dạng table, có cột là mặt hàng, dòng là khách hàng sau đó dùng MATCH và INDEX mà tìm.
__________X ________Y_________Z
____A_____100______101______102
____B_____200______201______202
____C_____300______301______302

 

[handung107]

Trong thực tế, việc sử dụng hàm rất phong phú, theo lẽ thường, trường hợp này sử dụng hàm Matah và Index giống như adam tran nói. Nhưng nếu bạn quen với việc dùng hàm SUMPRODUCT, bạn sẽ thấy hàm này rất phong phú, nó hoàn toàn có thể thay thế toàn bộ công thức mảng, áp dụng cho các hàm đếm và tính tổng như COUNT, SUM, và một phần trường hợp các hàm dò tìm như VLOOKUP, MAtch...Bản thân nó lại dễ sử dụng, bạn không cần nhớ quá nhiều các đối số của hàm, không tốn bộ nhớ nhiều, tôi thành thật khuyên các bạn nên sử dụng hàm SUMPRODUCT...

 

[Thiên Thanh]

Mình chưa SD các hàm Match, Index bao giờ nên cũng ko biết cách áp dụng cho TH này, vả lại theo cách của Adam tran thì khách hàng và mặt hàng mình rất nhiều nếu làm theo cách này thì số lượng cột rất nhiều, mà mình thì ko muốn theo dõi số cột quá nhiều như vậy. Rất cám ơn các bạn.

 

[adam_tran]

Mình chưa SD các hàm Match, Index bao giờ nên cũng ko biết cách áp dụng cho TH này, vả lại theo cách của Adam tran thì khách hàng và mặt hàng mình rất nhiều nếu làm theo cách này thì số lượng cột rất nhiều, mà mình thì ko muốn theo dõi số cột quá nhiều như vậy. Rất cám ơn các bạn.

Bạn tìm đọc bài về Match và Index trong EFC, hoặc đọc Help từ Excel. Mình nghỉ đã có nhiều bài viết về cách sử dụng 2 hàm này vì nó được sử dụng rất phổ biến trong các ứng dụng thực tế:
- Dò tìm dữ liệu trong mảng 2 chiều (gồm nhiều hàng và nhiều cột).
- Trích lọc dữ liệu...
Theo ý tôi, dù có nhiều KH và nhiều hàng hóa, bạn cũng nên tổ chức theo hàng và cột. Điều này làm cấu trúc CSDL của bạn chặt chẽ hơn và "Bảng giá" trở thành bảng chuẩn để dò tìm. Nếu số KH < Số HH thì đưa số KH làm cột.
Trong trường này nếu không cần thiết phải đưa đơn giá ra thì sử dụng SUMPRODUCT như chị hanhdung hướng dẫn là tốt nhất. Hàm SUMPRODUCT có thể thay thế hàm mảng nếu trong điều kiện chỉ có các toán tử căn bản =, >, >=, <, <=, <>... mà không có các hàm khác.
Hàm SUMPRODUCT cũng có thể thay hàm COUNTIF với nhiều điều kiện. Thí dụ hai hàm này sẽ tương đương:
= COUNTIF(DmHH; "SPA")
= SUMPRODUCT((DmHH="SPA")*(1))
Nếu có 2 điều kiện là "bao nhiều SPA có SL>10" thì ko dùng được COUNTIF mà phải dùng SUMPRODUCT:
= SUMPRODUCT((DmHH="SPA")*(Soluong>10)*(1))

 

[Thiên Thanh]

Cám ơn các bạn nhiều lắm, quả thật là Excel mình cũng chỉ biết SD một vài hàm thôi, nên sẽ còn cần sự trợ giúp của các bạn nhiều nữa.

 

[Thiên Thanh]

Nếu mình Sd hàm Match và Index giống như Adam tran thì trong TH này mình phải làm sao:
Mình có 1 bảng dữ liệu với các chỉ tiêu sau:
CỘT: A B C D E
TENKH TENMH DGIA SL TTIEN
X 1 20.000 5 100.000
Y 2 10.000 8 80.000
Z 3 15.000 3 45.000
G 1 20.000 2 40.000
M 3 15.000 7 105.000
Nếu mình muốn tổng hợp dữ liệu với CỘT: TENKH và DÒNG: TENMH, thì công thức để mình muốn lấy số tổng ở cell SL và TTIEN sẽ áp dụng ntn? à còn nếu muốn theo dõi cho từng đơn giá thì ntn?
Các bạn giúp mình với nhé!

 

[workman]

Bạn đưa bài toán khó hiểu nhỉ. Mình đọc đi đọc lại mãi mà không biết vấn đề bạn hỏi như thế nào. Có lẽ tại hôm nay bận bịu quá, đầu óc lú lẫn cả rồi.

 

[Thiên Thanh]

Có gì đâu, giả sử từ A1:E6 là vùng dữ liệu. Mình muốn tổng hợp dữ liệu theo bảng sau:
TENMH_______________TENKH_________
____________________SL___TT________
X___________________?_____?________
Y___________________?_____?________
Z___________________?_____?________
G___________________?_____?________
M___________________?_____?________

 

[SA_DQ]

Hàng => cột

Theo Mình nghĩ bạn biến một trong 2 thứ đó (mà bạn muốn tổng hợp) thành cột để trở thành cột là Mã hàng hay cột là mã khách hàng (còn thứ kia thì ngược lại) tuỳ thuộc vào cái nào nhiều/ít hơn & fát sinh của nó! :banana:

 

[WhoamI]

Cũng không khó hiểu nhưng nếu diễn giải trên này thì dài dòng lắm. Bạn gửi cho mình một bản vào Hanghtt2003@yahoo.com nhé. Hiểu và nắm chắc các hàm Indirect, Index, match, offset... thì việc lập các sổ theo quy định sẽ trở nên đơn giản hơn nhiều.

 

[Thiên Thanh]

Ko biết Adam tran lập bảng theo hàng - cột khi mà số lượng hàng cột quá nhiều có thấy bất tiện không, còn mình thì thấy quá bất tiện bởi khi mình in số lượng giấy rất nhiều và như vậy khi xem số liệu từ dòng đầu đến cuối hoặc để đối chiếu rất khó khăn, trước đây mình thường SD Pivot table để tạo bảng, nhưng mỗi tháng mình phải tạo lại do thay đổi số liệu, cho nên mình vẫn muốn có một bảng chuẩn để mỗi tháng mình chỉ cần nhập số liệu và tự lên bảng TH cho mình, nhưng xem ra cũng khó khăn quá nhỉ????

 

[Thiên Thanh]

Adam tran hướng dẫn cho mình đi chứ, bởi vì hướng xử lý lập nhiều hàng và cột là do bạn đưa ý kiến cho mình mà. Quả thật là dùng mảng mình thấy bất tiện vô cùng, mấy hôm trước máy mình chạy ngon ơ, sau khi dùng mảng nó chạy ì à ì ạch ( do mình lập bảng theo hàng - cột giống như bạn nói, nên công thức mảng rất nhiều). Nên mình nghĩ nếu quả thật có cách khác để hạn chế dùng CT mảng thì vẫn tốt hơn. Tiện đây, các bạn đổi từ công thức mảng sang SUMPRODUCT dùm mình được không? bởi vì dùng mảng mình thấy bất tiện lắm lắm!! Thanks.
CTHỨC MẢNG NHỜ CÁC BẠN ĐỔI SANG SUMPRODUCT
{=SUM(IF(BE10&BD10=Q7:Q430&E7:E430;L7:L430))}

 

[handung107]

=SUMPRODUCT(($Q$7:$Q$430=$BE10)*($E$7:$E$430=$BD10)*($L$7:$L$430))
Không biết có đúng với ý của bạn không? Bạn nói rõ hơn, nếu công thức chưa phù hợp

 

[workman]

Adam tran hướng dẫn cho mình đi chứ, bởi vì hướng xử lý lập nhiều hàng và cột là do bạn đưa ý kiến cho mình mà. Quả thật là dùng mảng mình thấy bất tiện vô cùng, mấy hôm trước máy mình chạy ngon ơ, sau khi dùng mảng nó chạy ì à ì ạch ( do mình lập bảng theo hàng - cột giống như bạn nói, nên công thức mảng rất nhiều). Nên mình nghĩ nếu quả thật có cách khác để hạn chế dùng CT mảng thì vẫn tốt hơn. Tiện đây, các bạn đổi từ công thức mảng sang SUMPRODUCT dùm mình được không? bởi vì dùng mảng mình thấy bất tiện lắm lắm!! Thanks.
CTHỨC MẢNG NHỜ CÁC BẠN ĐỔI SANG SUMPRODUCT
{=SUM(IF(BE10&BD10=Q7:Q430&E7:E430;L7:L430))}

Theo tôi hiểu thì ý của Adam là nếu bạn muốn dùng bảng thì không cần dùng mảng nữa.

Theo như yêu cầu của bạn thì có thể sử dụng Pivot Table mà không cần thiết phải dùng bất cứ công thức nào. Dùng Pivot sẽ nhanh hơn do đây là những công cụ có sẵn của Excel.

Chúc bạn vui. Nếu vẫn không được, xin gửi file của bạn cho tôi. Email cho tôi: workmanvn@gmail.com