6 Cách tính đơn giá trong Excel đơn giản ai cũng làm được

Trong công việc kế toán, việc quản lý và tính toán đơn giá hàng hóa, dịch vụ là một nhiệm vụ cốt yếu, đòi hỏi sự chuẩn xác tuyệt đối. Với cách tính đơn giá trong Excel sẽ giúp bạn tự động hóa thực hiện công việc này một cách nhanh chóng và hiệu quả. Hãy cùng AntBook đi tìm hiểu chi tiết các cách tính để công việc của bạn trở nên nhẹ nhàng hơn nhé!

1. Công thức tính đơn giá trong Excel khi sử dụng hàm VLOOKUP

Hàm VLOOKUP (Vertical Lookup) là hàm dò tìm theo cột, nghĩa là Excel sẽ tìm giá trị bạn chỉ định ở cột đầu tiên của một bảng, sau đó trả về giá trị tương ứng từ cột khác trong cùng dòng. Điều này cực kỳ hữu ích khi bạn muốn tra cứu giá sản phẩm, mã nhân viên, thông tin khách hàng, hoặc đơn giá theo mã hàng.

Cấu trúc chuẩn của hàm VLOOKUP:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

đó:

• lookup_value: Giá trị bạn cần tìm kiếm (ví dụ: mã sản phẩm, mã nhân viên…).
• table_array: Vùng dữ liệu chứa thông tin bạn muốn dò tìm.
• col_index_num: Vị trí cột (tính từ trái sang phải, bắt đầu từ 1) trong bảng dữ liệu chứa kết quả bạn muốn lấy.
• range_lookup (tuỳ chọn):
  • TRUE (hoặc 1): Tìm kiếm tương đối (gần đúng).
  • FALSE (hoặc 0): Tìm kiếm chính xác tuyệt đối.
  • Nếu không khai báo, Excel mặc định là TRUE.

Lưu ý quan trọng: Khi muốn dò tìm chính xác (ví dụ tra đơn giá theo mã hàng), bạn nên dùng range_lookup = FALSE để tránh sai lệch dữ liệu.

Hàm VLOOKUP có thể sử dụng độc lập hoặc kết hợp với các hàm khác trong Excel

2. Thao tác tính đơn giá trong Excel bằng hàm IF

Khi làm việc với bảng bán hàng có nhiều loại sản phẩm và công ty khác nhau, việc tính đúng đơn giá cho từng dòng dữ liệu là cực kỳ quan trọng. Trong trường hợp bạn không muốn (hoặc chưa dùng) đến các hàm dò tìm như VLOOKUP, thì hàm IF lồng nhau là một giải pháp hiệu quả.

Áp dụng công thức: 

=IF(AND(C3=$A$14,B3=$B$13),$B$14,IF(AND(C3=$A$14,B3=$C$13),$C$14,IF(AND)

Thực hiện công thức vào ô hiển thị kết quả

3. Hướng dẫn cách tính đơn giá trong Excel bằng hàm VLOOKUP và MATCH

Khi làm việc với bảng dữ liệu có nhiều điều kiện như mã hàng và mã công ty, việc tìm đúng đơn giá không thể chỉ dựa vào một yếu tố duy nhất. Lúc này, việc kết hợp hàm VLOOKUP với MATCH sẽ giúp bạn xử lý bài toán hai chiều một cách chính xác và chuyên nghiệp.

Để áp dụng đúng, bạn cần hiểu rõ vai trò của từng hàm:

• Hàm VLOOKUP dùng để tìm kiếm theo cột dọc, vì vậy nó sẽ dò theo mã công ty – nằm ở cột đầu tiên trong bảng đơn giá.
• Hàm MATCH giúp xác định vị trí cột chứa mã hàng trong hàng tiêu đề. Kết quả trả về là số thứ tự của cột cần lấy dữ liệu, đóng vai trò là đối số thứ ba trong hàm VLOOKUP.

Nói cách khác, MATCH sẽ chỉ cho VLOOKUP biết cần lấy giá trị ở cột nào tương ứng với từng mã hàng.

Cấu trúc công thức:

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

Trong đó: 

• C3: Ô chứa mã công ty đang được xét.
• $B$14:$F$17: Vùng dữ liệu bảng đơn giá.
• MATCH(B3, $B$13:$E$13, 0): Xác định vị trí của mã hàng trong hàng tiêu đề, giúp tìm đúng cột đơn giá.
• 0: Tham số tìm kiếm chính xác tuyệt đối trong VLOOKUP.

Khi thay các nội dung thành tọa độ tham chiếu

4. Tính đơn giá trong Excel dựa vào mã hàng với hàm HLOOKUP và MATCH

Nếu bạn đã quen với VLOOKUP để tìm dữ liệu theo cột dọc, thì HLOOKUP chính là “phiên bản ngang” của nó. Hàm HLOOKUP hoạt động theo hàng ngang, rất phù hợp khi bạn cần tra cứu dữ liệu có cấu trúc theo hàng, chẳng hạn như bảng đơn giá với hàng tiêu đề là các mã hàng.

Nguyên lý hoạt động:

Trong ví dụ này, chúng ta cần tìm đơn giá dựa trên hai yếu tố: mã hàng và mã công ty. Cách tiếp cận hợp lý là:

• HLOOKUP sẽ dò tìm mã hàng ở hàng đầu tiên trong vùng bảng đơn giá. Đây là điều kiện bắt buộc để hàm hoạt động đúng, vì HLOOKUP chỉ tra cứu trong hàng đầu tiên.
• Để xác định số thứ tự hàng chứa đơn giá tương ứng với mã công ty, ta sử dụng hàm MATCH. MATCH giúp xác định mã công ty nằm ở dòng thứ mấy trong bảng đơn giá.

Cấu trúc công thức:

=HLOOKUP(B3, $B$13:$E$17, MATCH(C3, $A$13:$A$17, 0), 0)

Trong đó: 

• B3: Ô chứa mã hàng cần tra cứu.
• $B$13:$E$17: Bảng dữ liệu đơn giá có hàng đầu là mã hàng, các hàng phía dưới là đơn giá theo công ty.
• MATCH(C3, $A$13:$A$17, 0): Tìm vị trí dòng tương ứng với mã công ty trong cột A.
• 0: Tham số yêu cầu tìm kiếm chính xác tuyệt đối trong HLOOKUP.

Áp dụng công thức =HLOOKUP(B3,$B$13:$E$17,MATCH(C3,$A$13:$A$17,0),0)

5. Cách tính đơn giá trong Excel bằng hàm INDEX và MATCH

Đây là bộ đôi thường được dân Excel chuyên nghiệp sử dụng để xử lý các bảng dữ liệu phức tạp, giúp tra cứu thông tin nhanh chóng mà vẫn đảm bảo độ chính xác cao. Bởi không bị giới hạn phải tìm kiếm theo cột đầu tiên, rất dễ kiểm soát và đặc biệt là thay đổi cấu trúc bảng.

Phương pháp này có thể tìm theo cả dòng và cột – điều mà VLOOKUP hay HLOOKUP không làm được nếu không kết hợp thêm hàm khác.

Bài toán đặt ra:

Bạn cần lấy đơn giá dựa trên mã công ty và mã hàng. Vùng bảng chứa dữ liệu là từ B13 đến F17, trong đó:

• Cột B (B13:B17) là danh sách mã công ty.
• Hàng 13 (B13:E13) là danh sách mã hàng.
• Các ô giao giữa hàng và cột chính là đơn giá tương ứng.

Cách giải quyết:

• Hàm INDEX sẽ lấy dữ liệu tại giao điểm của hàng và cột trong vùng bảng.
• Hàm MATCH sẽ giúp xác định vị trí hàng (theo mã công ty) và vị trí cột (theo mã hàng).

Công thức:

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

Trong đó: 

• INDEX($B$13:$F$17, …): Vùng bảng chứa toàn bộ dữ liệu cần dò tìm.
• MATCH(C3, $B$14:$B$17, 0): Tìm vị trí hàng tương ứng với mã công ty trong ô C3.
• MATCH(B3, $C$13:$F$13, 0): Tìm vị trí cột tương ứng với mã hàng trong ô B3.

Sử dụng công thức

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

Hàm SUMPRODUCT thường được biết đến với vai trò tính tổng của tích các mảng, nhưng ít ai biết rằng nó cũng có thể tính đơn giá dựa trên nhiều điều kiện một cách cực kỳ linh hoạt.

Hàm này đặc biệt hiệu quả khi bảng đơn giá:

• Không có mã hàng hay mã công ty bị trùng lặp.
• Dữ liệu được trình bày dưới dạng ma trận 2 chiều, với mã hàng nằm ngang (hàng tiêu đề) và mã công ty nằm dọc (cột bên trái).

Cách sử dụng:

SUMPRODUCT sẽ xét đồng thời 2 điều kiện:

• Một là Mã công ty khớp (so sánh theo cột dọc).
• Hai là Mã hàng khớp (so sánh theo hàng ngang).

Khi cả hai điều kiện cùng đúng, công thức sẽ lấy đúng đơn giá tại điểm giao nhau giữa hàng và cột đó.

Công thức:

=SUMPRODUCT(($A$14:$A$17=C3)*($B$13:$E$13=B3)*$B$14:$E$17)

Giải thích chi tiết:

• ($A$14:$A$17=C3): So sánh mã công ty ở cột A với ô C3 (Mã công ty cần tìm).
• ($B$13:$E$13=B3): So sánh mã hàng ở hàng 13 với ô B3 (Mã hàng cần tìm).
• $B$14:$E$17: Là bảng chứa các giá trị đơn giá cần truy xuất.
• Hai điều kiện trên sẽ tạo ra các mảng TRUE/FALSE (hay 1/0), và chỉ tại vị trí có cả hàng và cột thỏa điều kiện, giá trị đơn giá mới được nhân và cộng vào kết quả.

Áp dụng vào ta có

7. Khi nào cần tính đơn giá trong Excel?

Tính đơn giá trong Excel là thao tác không thể thiếu khi bạn cần xác định chính xác mức giá của từng sản phẩm hoặc dịch vụ trong các bảng báo giá, bảng kê chi phí hay báo cáo bán hàng. Đặc biệt trong môi trường doanh nghiệp, việc xác định đơn giá một cách nhanh chóng và chuẩn xác không chỉ giúp tiết kiệm thời gian mà còn đảm bảo tính minh bạch và hiệu quả trong quản lý tài chính.

Dù bạn là nhân viên kế toán, nhân sự làm báo cáo hay chủ doanh nghiệp theo dõi chi phí, công thức tính đơn giá trong Excel sẽ luôn là công cụ đắc lực để xử lý dữ liệu một cách khoa học và chuyên nghiệp.

Tính đơn giá trong Excel giúp xác định mức giá của từng sản phẩm hoặc dịch vụ

Việc thành thạo các cách tính đơn giá trong Excel bằng các hàm IF, VLOOKUP, HLOOKUP, INDEX, MATCH hay SUMPRODUCT là kỹ năng vàng cho bất kỳ ai làm công tác kế toán hay xử lý dữ liệu kinh doanh. Thay vì tính toán thủ công mất thời gian và dễ nhầm lẫn, hãy tận dụng Excel để tự động hóa, nâng cao hiệu suất và đảm bảo tính chính xác cho công việc của bạn.

Nếu bạn đang tìm kiếm một nguồn tài liệu uy tín, được hệ thống hóa khoa học, bám sát thực tế công việc và hướng dẫn chi tiết từng bước để nâng cao toàn diện kỹ năng Excel của mình, đặc biệt là các kỹ năng ứng dụng trong công tác kế toán, các đầu sách học Excel từ AntBook chính là lựa chọn hàng đầu dành cho bạn. Được biên soạn bởi các chuyên gia giàu kinh nghiệm, sách Excel của AntBook sẽ là người bạn đồng hành tin cậy giúp bạn chinh phục mọi thách thức dữ liệu.