5 Cách tính đơn giá trong Excel chuẩn với ví dụ chi tiết

Đơn giá là một đơn vị của hàng hóa hoặc dịch vụ có thể được tính bằng nhiều cách trên Excel. Trong bài viết này, hãy cùng Gitiho tìm hiểu tất cả 5 cách tính đơn giá trong Excel đơn giản giúp bạn tiết kiệm tối đa thời gian xử lý dữ liệu nhé!

Xem thêm: Khóa học Tuyệt đỉnh Excel giúp bạn làm chủ Excel sau 16h:

Cách tính đơn giá trong Excel bằng hàm VLOOKUP

Hàm VLOOKUP là gì?

VLOOKUP là viết tắt của Vertical (nghĩa là hàng dọc) và Look Up (nghĩa là tìm kiếm). Hàm VLOOKUP được sử dụng để tìm kiếm, tra cứu, dò tìm các dữ liệu trong bảng, phạm vi theo hàng dọc.

Hàm VLOOKUP có thể tuỳ ý sử dụng độc lập hoặc kết hợp với các hàm khác trong Excel chẳng hạn như hàm VLOOKUP IF tính đơn giá và khuyến mại,.... 

Công thức tính đơn giá trong Excel bằng hàm VLOOKUP

Chúng ta có một ví dụ về bảng theo dõi bán hàng và bảng giá để thực hành tính đơn giá dựa vào mã hàng trong Excel như trong hình dưới đây:

Nhiệm vụ của chúng ta là điền đúng giá tương ứng với các mã hàng vào trong bảng theo dõi bán hàng. Ta nhận thấy tại bảng giá, các mã hàng được hiển thị dưới dạng các ký tự ghép giữa 2 ký tự đầu và 2 ký tự cuối trong mã hàng tại bảng theo dõi bán hàng.

Bước 1: Đầu tiên, chúng ta cần xác định mã ghép tại bảng giá.

• Để lấy 2 ký tự đầu của mã hàng, chúng ta sử dụng hàm LEFT.
• Để lấy 2 ký tự cuối của mã hàng, chúng ta sử dụng hàm RIGHT.

Sau đó, chúng ta sử dụng hàm CONCAT để nối kết quả của hàm LEFT và hàm RIGHT vừa thực hiện:

=CONCAT(LEFT(A2,2),RIGHT(A2,2))

Bước 2: Tiếp theo, chúng ta sử dụng hàm VLOOKUP để tìm và tính đơn giá Excel dựa vào mã tạo bởi hàm CONCAT bằng công thức:

=VLOOKUP(CONCAT(LEFT(C3,2),RIGHT((C3,2)),$I$3:$K$6,3,0)

Trong đó:

lookup_value là kết quả hàm CONCAT.

lookup_array là bảng giá (I3:K6). Nhấn F4 để cố định vùng dữ liệu thành $I$3:$K$6.

col_index_num là cột Đơn giá (cột K) trong bảng giá.

range_lookup là phương thức tìm kiếm chính xác theo mã hàng.

Kết quả như sau:

Một số lưu ý với hàm VLOOKUP trong Excel

Khi sử dụng hàm VLOOKUP để tính đơn giá dựa vào mã hàng trong Excel, bạn cần lưu ý các điểm sau đây:

• Hàm VLOOKUP không phân biệt chữ hoa và chữ thường.
• Danh sách tìm kiếm phải được sắp xếp trước khi sử dụng hàm.
• Hàm VLOOKUP chỉ tìm kiếm từ trái qua phải.
• Hàm VLOOKUP chỉ tìm kiếm và trả về giá trị đầu tiên xuất hiện.
• Số cột tìm kiếm trong công thức hàm phải nhỏ hơn hoặc bằng tổng số cột trong bảng tìm kiếm.

Tham khảo: Dùng hàm IF kết hợp VLOOKUP để tìm kiếm theo điều kiện tùy chọn

Xác định đơn giá theo điều kiện với hàm INDEX và MATCH

Hàm INDEX và MATCH cũng là hai hàm vô cùng hữu hiệu để tìm đơn giá theo 2 điều kiện hoặc nhiều hơn nữa.

Những điều chúng ta cần lưu ý khi sử dụng INDEX và MATCH là:

• Xác định dòng dựa theo hàm Match theo "Mã công ty"
• Xác định cột dưa theo hàm INDEX theo "Mã hàng"
• Để xác định vùng dữ liệu thì ta cần tính đơn giá, bởi vậy mà ta cần xét vùng B13:F17 là Bảng đơn giá.

Ta sẽ sử dụng cấu trúc cụ thể qua hàm sau:

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))

Qua đó ta sẽ áp dụng toạ độ tham chiếu:

G3=INDEX($B$13:$F$17,MATCH(C3,$B$13:$B$17,0),MATCH(B3,$B$13:$F$13,0))

Kết hợp hàm VLOOKUP và MATCH để tính đơn giá

Dùng hàm VLOOKUP trong tính đơn giá với mục đích tham chiếu dữ liệu theo nhiều điều kiện. Vậy sẽ thế nào nếu như ta dùng VLOOKUP và hàm MATCH để thực hiện chung một nghiệm vụ.

Điều ta cần phải lưu ý khi dùng cách thức này là xác định VLOOKUP sẽ theo giá trị nào và MATCH sẽ theo giá trị nào. Nhìn vào hình ảnh minh hoạ dưới đây ta sẽ cần chú ý vào cấu trúc của bảng đơn giá dưới đây:

• Theo như nguyên lý của VLOOKUP thì giá trị cần tìm sẽ phải nằm ở cột đầu tiên, bởi vậy ta cần xếp Mã công ty ngay cột đầu tiên trong bảng đơn giá.
• Tiếp theo, để xác định mã hàng nằm ở cột bao nhiêu thì ta dùng hàm MATCH để xét giá trị Mã hàng.

Cấu trúc hàm sẽ là: 

VLOOKUP(Mã công ty, Bảng đơn giá, MATCH(mã hàng, dòng mã hàng, 0), 0)

Thay toạ độ tham chiếu vào, ta có.

G3=VLOOKUP(C3,$B$14:$F$17,MATCH(B3,$B$13:$F$13,0),0)

Kết hợp hàm HLOOKUP và MATCH khi tính đơn giá

Tương tự như VLOOKUP thì HLOOKUP cũng là hàm để tham chiếu dữ liệu nhưng chỉ khác về hướng tham chiếu. Bên cạnh đó ta cũng cần lưu ý một số điều kiện khi chuyển sang HLOOKUP.

• Hàm HLOOKUP sẽ tìm kiếm ở dòng đầu tiên của bảng tìm kiếm, bởi thế mà dòng Mã hàng ở dòng 13 sẽ là dòng đầu tiên của vùng bảng thuộc đơn giá hàng hoá.
• Tới đây ta sẽ cần căn cứ vào mã công ty để xác định dạng, qua đó ta dùng hàm MATCH để tìm theo Mã công ty.

Cấu trúc hàm sẽ là: 

HLOOKUP(Mã hàng, Bảng đơn giá, MATCH(Mã công ty, cột Mã công ty, 0), 0)

Thay vào nội dung tham chiếu sẽ là:

G3=VLOOKUP(C3,$B$14:$F$17,MATCH(B3,$B$13:$F$13,0),0)

Cách tính đơn giá trong Excel bằng hàm SUMPRODUCT

Ta dùng hàm SUMPRODUCT khi bảng đơn giá không có các mã trùng nhau. Bên cạnh đó là cần xét 2 điêu kiện mảng 2 chiều. Như ở bảng tính này là Mã hàng thuộc vùng C13:F13 và Mã công ty thuộc vùng B14:B17, tương tự như vậy ở vùng C14:F17.

Áp dụng công thức ta sẽ có.

G3=SUMPRODUCT(($B$14:$B$17=C3)*($C$13:$F$13=B3)*$C$14:$F$17)

Kết luận

Hi vọng 5 cách tính đơn giá trong Excel được chia sẻ trong bài viết này có thể giúp các bạn làm việc hiệu quả và nhanh chóng hơn. Có thể thấy Excel có rất nhiều tác dụng tốt cho công việc.

Gitiho.com thường xuyên để cập nhập và học tập thêm nhiều kiến thức về tin học văn phòng nữa các bạn nhé!