Trong quá trình làm việc với Excel, chắc hẳn rằng chúng ta thường gặp khó khăn trong việc xác định đơn giá hay tính đơn giá theo nhiều điều kiện bởi lẽ việc tính đơn giá này đòi hỏi chúng ta cầ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. Vậy có những cách nào có thể tính đơn giá theo điều kiện trong Excel? Trong bài viết dưới đây, mình sẽ hướng dẫn các bạn cách tính đơn giá theo điều kiện trong Excel một cách dễ dàng và chi tiết nhất.
1. Cách tính đơn giá trong Excel bằng hàm VLOOKUP kết hợp hàm MATCH
Cho ví dụ minh họa, hãy tính đơn giá hàng hóa sau:
Để tham chiếu một giá trị cụ thể ở đây là đơn giá theo điều kiện từ 2 điều kiện trở lên thì chúng ta có thể sử dụng hàm VLOOKUP kết hợp hàm MATCH trong Excel.
Trong ví dụ này, chúng ta cần phải xác định VLOOKUP theo giá trị nào, MATCH theo giá trị nào, do đó chúng ta cần chú ý cấu trúc của bảng đơn giá như sau:
Mã công ty là cột nằm ở cột đầu tiên trong bảng đơn giá và theo nguyên tắc hàm VLOOKUP thì giá trị tìm kiếm phải nằm ở cột đầu tiên, do vậy hàm VLOOKUP sẽ tìm theo mã công ty.
Kết quả cần tìm của hàm VLOOKUP là đơn giá của các mã hàng, tương ứng theo cột thứ mấy trong bảng, do vậy để xác định cột thứ mấy, chúng ta sẽ sử dụng hàm MATCH để xét giá trị Mã hàng.
Chúng ta có cấu trúc như sau:
= VLOOKUP[Mã công ty, Bảng đơn giá, MATCH[mã hàng, dòng mã hàng, 0], 0]
Cụ thể trong công thức tính đơn giá của ví dụ minh họa trên tại ô G3 như sau:
=VLOOKUP[C3,$B$14:$F$17,MATCH[B3,$B$13:$F$13,0],0]
Hình ảnh minh họa về cách tính đơn giá trong Excel bằng hàm VLOOKUP kết hợp hàm MATCH
Sau đó, chúng ta nhấn Enter, như vậy là chúng ta đã thực hiện xong việc tính đơn giá theo điều kiện trong Excel
2. Cách tính đơn giá trong Excel bằng hàm HLOOKUP kết hợp hàm MATCH
Để tính đơn giá theo điều kiện trong Excel, chúng ta có thể sử dụng hàm HLOOKUP kết hợp hàm MATCH, hàm HLOOKUP cũng là một hàm tìm kiếm tương tự với VLOOKUP, chỉ thay đổi về phương hướng và chiều tìm kiếm.
Theo như ví dụ minh họa trên, chúng ta có thể thấy đố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, do 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, khi đó đối tượng tìm kiếm của hàm HLOOKUP trong trường hợp này là Mã hàng. Lúc này kết quả của hàm HLOOKUP sẽ lấy tương ứng xuống bao nhiêu dòng và căn cứ vào mã công ty để xác định dòng, do vậy chúng ta dùng hàm MATCH tìm theo Mã công ty.
Chúng ta thực hiện 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]
Cụ thể trong ví dụ chúng ta thực hiện công thức tại ô G3 như sau:
=HLOOKUP[C3,$B$14:$F$17,MATCH[B3,$B$13:$F$13,0],0]
Hình ảnh minh họa về cách tính đơn giá trong Excel bằng hàm HLOOKUP kết hợp hàm MATCH
3. Cách tính đơn giá trong Excel bằng hàm INDEX kết hợp hàm MATCH
Trong ví dụ trên, chúng ta cũng có thể tính đơn giá bằng cách sử dụng hàm INDEX kết hợp hàm MATCH trong đó chúng ta hãy ghi nhớ 3 yếu tố đó là thứ nhất là chúng ta cần tính đơn giá, do đó chúng ta xét vùng B13:F17 là Bảng đơn giá, thứ hai là xác định dòng dựa theo hàm MATCH, tìm theo Mã công ty, thứ ba là xác định cột dựa theo hàm MATCH, tìm theo Mã hàng.
Chúng ta có cấu trúc như 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]]
Cụ thể trong ví dụ minh họa trên, chúng ta thực hiện công thức tại ô G3 như sau:
=INDEX[$B$13:$F$17,MATCH[C3,$B$13:$B$17,0],MATCH[B3,$B$13:$F$13,0]]
Hình ảnh minh họa về cách tính đơn giá trong Excel bằng hàm INDEX kết hợp hàm MATCH
4. Cách tính đơn giá trong Excel bằng hàm SUMPRODUCT
Để tính đơn giá trong Excel, chúng ta cũng có thể sử dụng hàm SUMPRODUCT, hàm SUMPRODUCT là một trong những hàm được sử dụng phổ biến trong việc việc tính đơn giá theo nhiều điều kiện trong Excel.
Khi thực hiện hàm này để tính đơn giá, chúng ta cần lưu ý rằng chỉ được áp dụng để tính đơn giá khi bảng đơn giá không có các mã trùng nhau.
Cụ thể trong ví dụ minh họa trên, chúng ta 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 đó chúng ta thwucj hiện công thức tại ô G3 như sau:
=SUMPRODUCT[[$B$14:$B$17=C3]*[$C$13:$F$13=B3]*$C$14:$F$17]
Hình ảnh minh họa về cách tính đơn giá trong Excel trong Excel bằng hàm SUMPRODUCT
Trên đây là những chia sẻ của mình về các cách tính đơn giá theo điều kiện trong Excel bằng cách kết hợp những hàm cơ bản trong Excel, mình hy vọng rằng bài viết trên đây sẽ thực sự hữu ích đối với các bạn và chúc các bạn có thể tính đơn giá thành công.