Đối với bất kỳ việc xác định đơn giá hoặc tính đơn giá theo điều kiện thường cũng đã khiến cho chúng ta gặp nhiều khó khăn và những lỗi dữ liệu cơ bản. Chính bởi vậy mà việc sử dụng hàm để áp dụng, kết hợp thuần thục để đạt hiệu quả tốt nhất.
Hãy cùng Box.edu tham khảo 4 cách tính đơn giá trong Excel sau đây đảm bảo bạn sẽ dễ dàng sử dụng hàm, tổ chức bảng dữ liệu logic và chuẩn xác nhất. Cùng tìm hiểu ngay trong bài viết dưới đây nhé!
Xem thêm: Bật mí cách tạo bảng chấm công trong Excel chuyên nghiệp
Kết hợp hàm VLOOKUP với hàm MATCH
Việc sử dụng kết hợp hàm Vlookup với Match là cách tính đơn giá trong Excel sẽ giúp bạn tham chiếu 1 giá trị cụ thể theo điều kiện chuẩn xác nhất. Cụ thể ở đây là tính đơn giá với hàm Vlookup và Match.
Đối với hàm kết hợp này bạn cần phải xác định chuẩn xác hàm Vlookup theo giá trị nào và Hàm Match theo giá trị nào. Điều này sẽ được xây dựng theo cấu trúc bảng đơn giá như sau:
- Mã công ty được đặt ở cột đầu tiên trong bảng đơn giá. Điều này sẽ giúp cho nguyên tắc hàm Vlookup thực hiện được giá trị tìm kiếm nằm ở cột đầu tiên. Vì thế hàm Vlookup sẽ được tìm theo mã công ty.
- Đối với kết quả cần tìm kiếm theo hàm Vlookup thì các đơn giá của các mã hàng sẽ tương ứng theo cột trong bảng. Để xác định được chính xác cột thứ mấy bạn cần sử dụng hàm Match để xét giá trị Mã hàng.
Công thức:
VLOOKUP[Mã công ty, Bảng đơn giá, MATCH[mã hàng, dòng mã hàng, 0], 0]
Ta có ví dụ minh hoạ sau đây:
Áp dụng công thức vào ví dụ sau đây sẽ giúp bạn hiểu chi tiết hơn:
G3=VLOOKUP[C3,$B$14:$F$17,MATCH[B3,$B$13:$F$13,0],0]
Cùng xem kết quả trả về được hiển thị ở bảng bên dưới nhé!
Kết hợp hàm HLOOKUP và hàm MATCH
Trong Excel, hàm HLOOKUP là một hàm tìm kiếm tương tự giống như hàm VLOOKUP. Hàm này chỉ thay đổi về phương hướng và chiều tìm kiếm để đưa ra kết quả. Bạn cần hiểu chi tiết như sau:
- Hàm HLOOKUP sẽ tìm kiếm đối tượng nằm ở dòng đầu tiên của bảng tìm kiếm. Vì vậy đối với hàng mã hàng [Dòng thứ 13] sẽ là dòng đầu tiên của vùng bảng giá hàng hoá hiển thị.
- Căn cứ vào mà công ty mà hàm HLOOKUP sẽ xác định chính xác dòng kết quả tương ứng. Vì thế chúng ta cần sử dụng hàm Match theo mã công ty để nhận được kết quả như mong đợi
Công thức:
HLOOKUP[Mã hàng, Bảng đơn giá, MATCH[Mã công ty, cột Mã công ty, 0], 0]
Áp dụng công thức vào ví dụ sau đây sẽ giúp bạn hiểu chi tiết hơn:
G3=VLOOKUP[C3,$B$14:$F$17,MATCH[B3,$B$13:$F$13,0],0]
Dùng hàm INDEX và MATCH
Đối với cách tính đơn giá trong Excel bằng việc sử dụng hàm này bạn cần biết rằng khi sử dụng Index và Match thì sẽ không hiệu quả bằng việc sử dụng kết hợp hàm Vlookup và Hlookup. Để minh chứng cho sự so sánh này hãy theo dõi ví dụ minh hoạ sau đây tìm hiểu chi tiết hơn.
Đối với sự kết hợp giữa hàm Index và Match có 3 yếu tố sau đây bạn cần chú ý:
- Vùng dữ liệu nào cần xác định? Đối với trường hợp cần tính đơn giá bạn cần chú ý phạm vi, cụ thể ở ví dụ này là từ B13:F17
- Kết quả ở dòng nào? Việc xác định chính xác dòng theo hàm Match sẽ được tìm theo mã công ty
- Kết quả ở cột nào? Xác định cột dựa theo hàm Match để tìm mã hàng tương ứng
Công thức:
INDEX[Bảng dữ liệu, MATCH[Mã công ty, Cột Mã công ty,0], MATCH[Mã hàng, dòng Mã hàng,0]]
Áp dụng công thức vào ví dụ sau đây sẽ giúp bạn hiểu chi tiết hơn:
G3=INDEX[$B$13:$F$17,MATCH[C3,$B$13:$B$17,0],MATCH[B3,$B$13:$F$13,0]]
Dùng hàm SUMPRODUCT để xác định đơn giá dựa trên nhiều điều kiện
Sử dụng hàm Sumproduct được áp dụng cho nhiều trường hợp. Hàm này cũng được sử dụng để xác định đơn giá theo điều kiện. Cách tính đơn giá trong excel này vô cùng hiệu quả, tuy nhiên bạn cần xác định được những điểm lưu ý sau đây:
- Khi nào áp dụng được? Không có các mã trùng nhau trong bảng đơn giá
- Hàm cần xét 2 điều kiện dạng mảng 2 chiều để mã hàng C13:F13 [Cụ thể trong vị dụ minh hoạ dưới đây] và mã công ty từ B14:B17 để đưa ra kết quả tương ứng trong vùng dữ liệu C14:F17
Sử dụng công thức sau với ví dụ minh hoạ này:
G3=SUMPRODUCT[[$B$14:$B$17=C3]*[$C$13:$F$13=B3]*$C$14:$F$17]
Tổng kết
Hy vọng với 4 cách tính đơn giá trong Excel được chia sẻ trong bài viết đã giúp bạn hiểu chi tiết từng bước làm kèm dẫn chứng minh hoạ dễ hiểu. Với cách làm này bạn hoàn toàn có thể quản lý dữ liệu, tính toán Logic và chuẩn xác nhất. Đừng quên cập nhập thường xuyên các bài viết chia sẻ về kiến thức tổng quát Tin học văn phòng giúp bạn thành thạo toàn bộ Excel, Word, Powerpoint nhé! Chúc bạn đạt được hiệu quả ngoài mong đợi!
Bạn có biết cách tính đơn giá trong Excel dựa vào mã hàng chi tiết chưa? Việc xác định đơn giá [hay tính đơn giá] theo nhiều điều kiện thường khiến chúng ta gặp nhiều khó khăn. Bời việc này đòi hỏi bạn phải sử dụng tốt các hàm, biết cách tổ chức bảng dữ liệu và có tư duy logic tốt. Trong bài viết hôm nay mình sẽ chia sẻ với các bạn các cách tính đơn giá trong Excel dựa vào mã hàng chi tiết nhé.
1. Công thức tính đơn giá trong Excel
Sau đây mình sẽ chia sẻ một số công thức tính đơn giá trong Excel để các bạn có thể tham khảo nhé.
Cách thứ 1: Dùng hàm HLOOKUP kết hợp hàm MATCH
Hàm HLOOKUP cũng là 1 hàm tìm kiếm tương tự với VLOOKUP, chỉ thay đổi về phương hướng, chiều tìm kiếm.
-
Đối tượng tìm kiếm của hàm HLOOKUP phải nằm ở dòng đầu tiên của bảng tìm kiếm. Vì vậy chúng ta thấy dòng Mã hàng [dòng 13] là dòng đầu tiên của vùng bảng đơn giá hàng hóa. Do đó đối tượng tìm kiếm của hàm HLOOKUP trong trường hợp này là Mã hàng.
-
Khi đó kết quả của hàm HLOOKUP sẽ lấy tương ứng xuống bao nhiêu dòng? Căn cứ vào mã công ty để xác định dòng. Vì vậy chúng ta dùng hàm MATCH tìm theo Mã công ty.
Cấu trúc như sau: HLOOKUP[Mã hàng, Bảng đơn giá, MATCH[Mã công ty, cột Mã công ty, 0], 0]
Khi thay các nội dung thành tọa độ tham chiếu ta có:
G3=VLOOKUP[C3,$B$14:$F$17,MATCH[B3,$B$13:$F$13,0],0]
Cách thứ 2: Dùng hàm INDEX và MATCH
Bạn có biết rằng dùng Index + Match thì tốt hơn dùng Vlookup hay Hlookup? Trong trường hợp này, chúng ta cùng tìm hiểu cách sử dụng INDEX MATCH như thế nào nhé.
Khi nhắc tới INDEX MATCH, các bạn hãy ghi nhớ 3 yếu tố:
-
Vùng dữ liệu nào? Chúng ta cần tính đơn giá, do đó xét vùng B13:F17 là Bảng đơn giá.
-
Kết quả ở dòng nào? Xác định dòng dựa theo hàm MATCH, tìm theo Mã công ty.
-
Kết quả ở cột nào? Xác định cột dựa theo hàm MATCH, tìm theo Mã hàng.
Cấu trúc cụ thể: INDEX[Bảng dữ liệu, MATCH[Mã công ty, Cột Mã công ty,0], MATCH[Mã hàng, dòng Mã hàng,0]]
Khi thay các nội dung thành tọa độ tham chiếu ta có:
G3=INDEX[$B$13:$F$17,MATCH[C3,$B$13:$B$17,0],MATCH[B3,$B$13:$F$13,0]]
Cách thứ 3: Dùng hàm SUMPRODUCT xác định đơn giá theo nhiều điều kiện
Hàm SUMPRODUCT là một hàm sử dụng được trong rất nhiều trường hợp. Và chúng ta hoàn toàn có thể ứng dụng hàm này trong việc xác định đơn giá theo nhiều điều kiện. Cụ thể như sau:
-
Khi nào áp dụng được? Khi bảng đơn giá không có các mã trùng nhau.
-
Cách dùng: Xét 2 điều kiện dạng mảng 2 chiều: Mã hàng [vùng C13:F13] và Mã công ty [vùng B14:B17], kết quả tương ứng lấy trong vùng C14:F17
Khi thay các nội dung thành tọa độ tham chiếu ta có:
G3=SUMPRODUCT[[$B$14:$B$17=C3]*[$C$13:$F$13=B3]*$C$14:$F$17]
2. Video hướng dẫn
Một số video hướng dẫn cách tính đơn giá trong Excel dựa vào mã hàng chi tiết. Nếu như trong trường hợp các bạn thực hiện theo các bước như phần 1 mà vẫn chưa hiểu rõ thì các bạn có thể xem ví dụ trong video nhé.
//youtu.be/bfiGZvHxN8w
Tập 40 EXCEL Đề 2 Tính đơn giá theo điều kiện giảm 3% Chứng chỉ ứng dụng CNTT cơ bản
Qua bài viết trên đây chắc hẳn các bạn đã có thể nắm rõ được các cách tính đơn giá trong Excel dựa vào mã hàng chi tiết. Cảm ơn các bạn đã theo dõi bài viết này và hẹn các bạn ở các bài viết tiếp theo nhé. Chúc các bạn thực hiện thành công.