Cách tính đơn giá trong Excel
Mục lục nội dung
Để thuận tiện trong việc quản lý vô số mặt hàng với nhiều thương hiệu khác nhau, ta sẽ mã hóa các sản phẩm thành các ký tự đặc biệt. Vì vậy, chúng ta sẽ cần dùng đến phép tính đơn giá trong Excel khi cần xác định giá cả của một hoặc một số mặt hàng sản phẩm nào đó nhằm phục vụ công tác cho báo giá, làm báo cáo bán hàng... Dưới đây là một số cách đơn giản mà Excel.net.vn sẽ hướng dẫn bạn để việc tính đơn giá trở nên đơn giản và dễ dàng hơn.
1. Sử dụng tổ hợp hàm VLOOKUP và MATCH
1.1 Hàm VLOOKUP
Mục đích: Hàm VLOOKUP được sử dụng khi cần tìm kiếm một giá trị hoặc một thông tin nào đó theo cột (theo hàng dọc) trong bảng lựa chọn tìm kiếm.
Cú pháp:
| =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) |
Trong đó:
+ Lookup_value: Giá trị cần tìm kiếm.
+ Table_array: Bảng cần tìm kiếm.
+ Col_index_num: Vị trí cột chứa giá trị cần tìm trong bảng.
+ Range_lookup: Kiểu tìm kiếm. Trong trường hợp này ta sẽ chọn kiểu “0” để được trả về kết quả chính xác với giá trị tìm kiếm.
1.2 Hàm MATCH
Mục đích: Hàm Match được sử dụng khi cần tìm kiếm vị trí dữ liệu trong vùng dữ liệu.
Cú pháp:
| = MATCH(lookup_value, lookup_array, [match type] |
Trong đó:
+ Lookup_value: Giá trị cần tìm kiếm vị trí trong vùng dữ liệu tìm kiếm.
+ Lookup_array: Vùng hoặc phạm vị cần tìm kiếm.
+ Match_type: Kiểu tìm kiếm. Trong trường hợp này ta sẽ chọn kiểu “0” để được trả về kết quả chính xác với giá trị tìm kiếm.
Sau khi đi qua định nghĩa và cú pháp của 2 hàm này, ta thấy, sử dụng tổ hợp hàm VLOOKUP và MATCH sẽ là phương pháp hợp lý khi cần tham chiếu 1 giá trị chứa 2 điều kiện trở lên. (Trong trường hợp này là đơn giá sản phẩm).
Tại ô cần trả về giá trị đơn giá ta nhập công thức sau:
| VLOOKUP(Mã công ty, Bảng tra cứu đơn giá hàng hoá, MATCH(Mã hàng, hàng chứa thông tin mã hàng,0),0) |
Lưu ý:
+ Mã công ty phải nằm ở cột đầu tiên trong bảng tra cứu đơn giá hàng hoá.
+ Cố định bảng tra cứu đơn giá hàng hoá và hàng chứa thông tin mã hàng bằng cách bôi đen và nhấn F4 (nhấn tổ hợp phím Fn + F4 với Laptop).
2. Sử dụng tổ hợp hàm HLOOKUP và MATCH
Hàm HLOOKUP
Mục đích: Hàm HLOOKUP được sử dụng khi cần tìm kiếm một giá trị hoặc một thông tin nào đó theo dòng (theo hàng ngang) trong bảng lựa chọn tìm kiếm.
Cú pháp:
| =VLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) |
Trong đó:
+ Lookup_value: Giá trị cần tìm kiếm.
+ Table_array: Bảng cần tìm kiếm.
+ Row_index_num: Vị trí dòng chứa giá trị cần tìm trong bảng, tính từ trên xuống dưới.
+ Range_lookup: Kiểu tìm kiếm. Trong trường hợp này ta sẽ chọn kiểu “0” để được trả về kết quả chính xác với giá trị tìm kiếm.
Tại ô cần trả về giá trị đơn giá ta nhập công thức sau:
| HLOOKUP(Mã hàng, Bảng tra cứu đơn giá hàng hoá, MATCH(Mã công ty, cột chứa thông tin tên công ty,0),0) |
Lưu ý:
+ Cố định bảng tra cứu đơn giá hàng hóa và cột chứa thông tin tên công ty bằng cách bôi đen và nhấn F4 (nhấn tổ hợp phím Fn + F4 với Laptop).
3. Sử dụng tổ hợp hàm INDEX và MATCH
Hàm INDEX
Mục đích: Hàm INDEX được sử dụng khi cần tìm một giá trị trong bảng hay trong một hay nhiều phạm vi xác định nào đó. Tương ứng với đó, hàm Index sẽ có 2 công thức sử dụng để lấy dữ liệu từ một hoặc nhiều mảng. Tuy nhiên, với mục đích để tính đơn giá ta sẽ sử dụng công thức cho 1 mảng dữ liệu.
Cú pháp:
| =INDEX(array, row_num, [column_num]) |
Trong đó:
+ Array: Phạm vi cần tìm kiếm.
+ Row_num: Vị trí của hàng trong vùng tìm kiếm giá trị chứa giá trị cần trả về.
+ Column_num: Vị trí cột chứa giá trị cần tìm trong bảng, tính từ trên xuống dưới.
Lưu ý:
+ Bắt buộc khai báo một trong 2 thành phần Row_num hoặc Column_num.
Theo cú pháp trên, ta hoàn toàn có thể nhập công thức và tìm được đơn giá sản phẩm bằng cách đếm số cột và số dòng tương ứng theo mã công ty và mã sản phẩm. Tuy nhiên trên thực tế, số lượng mặt hàng cần tra cứư giá sẽ lớn hơn rất nhiều. Vì vậy, ta sẽ dùng tổ hợp hàm INDEX và MATCH trong trường hợp này.
Tại ô cần trả về giá trị đơn giá ta nhập công thức sau:
| INDEX(Bảng tra cứu đơn giá hàng hoá, MATCH(Mã công ty, Cột chứa thông tin công ty,0), MATCH(Mã hàng, hàng chứa thông tin Mã hàng,0)) |
Lưu ý:
+ Cố định bảng tra cứu đơn giá hàng hoá, cột chứa thông tin tên công ty và hàng chứa thông tin mã hàng bằng cách bôi đen và nhấn F4 (nhấn tổ hợp phím Fn + F4 với Laptop).
4. Sử dụng hàm SUMPRODUCT
Để tính đơn giá ta sẽ sử dụng trường hợp nâng cao của hàm SUMPRODUCT để kết quả trả về khớp đủ 2 điều kiện về mã hàng và mã công ty.
Lưu ý: Áp dụng trong trường hợp bảng tra cứu đơn giá hàng hoá không có mã trùng nhau.
Tại ô cần trả về giá trị đơn giá ta nhập công thức sau:
| SUMPRODUCT((Cột chứa thông tin công ty = Mã công ty cần tìm)*( hàng chứa thông tin mã hàng = Mã hàng cần tìm)*Vùng chứa giá trị cần tìm) |
Lưu ý:
+ Cố định cột chứa thông tin tên công ty, hàng chứa thông tin mã hàng và vùng chứa giá trị cần tìm bằng cách bôi đen và nhấn F4 (nhấn tổ hợp phím Fn + F4 với Laptop).
Trên đây là 4 cách tính đơn giá trong Excel thông dụng và đơn giản nhất. Chúc bạn thao tác thành công!
Viết bình luận