Cách kết hợp hàm Sumif và hàm Vlookup trên Excel

Với những ai thường xuyên xử lý bảng dữ liệu Excel và tính toán các số liệu, thì chắc chắn sẽ biết đến 2 hàm Excel phổ biến là Sumif và hàm Vlookup. Hàm Sumif dùng để tính tổng các giá trị có điều kiện, còn hàm Vlookup để tìm kiếm giá trị trong mảng. Và việc kết hợp 2 hàm Sumif và hàm Vlookup trong Excel cùng được áp dụng rất nhiều.

Bài viết dưới đây sẽ giới thiệu cơ bản tới bạn đọc cách dùng hàm Sumif và hàm Vlookup với những bạn mới làm quen trên Excel, cũng như cách kết hợp của 2 hàm Sumif và hàm Vlookup trên Excel.

1. Cách dùng hàm Sumif Excel:

Khi hàm Sum cho phép người dùng tính tổng các giá trị của một vùng dữ liệu nào đó, thì hàm Sumif sẽ giúp bạn có thể tính tổng vùng dữ liệu mà bỏ qua giá trị nào trong dãy. Bạn có thể loại giá trị nào, sử dụng giá trị nào kèm với điều kiện để tính tổng.

Hàm Sumif có công thức SUMIF(range, Criteria, sum_range).

Trong đó:

• Range: vùng được lựa chọn chứa các ô điều kiện.
• Criteria: điều kiện để tính toán các dữ liệu.
• Sum_range: vùng cần tính tổng.

Bạn đọc có thể tham khảo cách áp dụng chi tiết hàm Sumif trong bài viết Cách sử dụng hàm SUMIF trong Excel.

2. Cách dùng hàm Vlookup trong Excel:

Hàm Vlookup trên Excel dùng để tìm kiếm dữ liệu trong một mảng nào đó. Hàm cho phép người dùng tra cứu dữ liệu trên một chuỗi nhất định theo các điều kiện cho trước. Hàm Vlookup thường được sử dụng ở những kiểu bảng dữ liệu tra cứu mã học sinh, mã nhân viên,...

Hàm Vlookup có công thức VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]).

Trong đó:

• Lookup_value: giá trị dùng để tìm kiếm.
• Table_array: bảng giá trị tìm kiếm, để ở dạng địa chỉ tuyệt đối (có dấu $ phía trước bằng cách nhấn F4).
• Col_index_num: thứ tự của cột cần lấy dữ liệu trên bảng giá trị cần tìm.
• Range_lookup: phạm vi tìm kiếm với TRUE tương đương với 1 (tìm kiếm tương đối), FALSE tương đương với 0 (tìm kiếm tuyệt đối).

Bạn có thể tham khảo cách sử dụng chi tiết hàm Vlookup trong bài viết Cách sử dụng hàm Vlookup trong Excel.

3. Cách kết hợp hàm Sumif và Vlookup:

Với những bảng dữ liệu cần tìm đối tượng, dữ liệu có điều kiện kèm theo thì hàm Sumif và hàm Vlookup sẽ tìm kiếm nhanh dữ liệu hơn, kết quả chính xác ngay cả khi thay đổi đối tượng. Đặc biệt bạn không cần phải gõ lại công thức.

Để hiểu rõ hơn về cách áp dụng hàm Sumif và hàm Vlookup, chúng ta sẽ có ví dụ như dưới đây.

Bảng doanh thu dưới đây có 3 bảng nhỏ với 3 nội dung khác nhau. Bảng 1 sẽ là nhân viên kèm mã số của từng người, Bảng 2 sẽ là mã số nhân viên và doanh số đạt được của từng người. Bảng 3 sẽ là bảng điền kết quả và được để trống.

Nội dung của bài đó là nhập kết quả tên nhân viên cũng như doanh số của người đó đạt được vào Bảng 3. Bên cạnh đó có thể tra cứu doanh số của những nhân viên khác khi thay đổi họ tên tương ứng.

Ở đây bạn cần sử dụng đến 2 hàm Sumif và hàm Vlookup, để tính tổng doanh số nhân viên với điều kiện cho trước.

Nếu chỉ sử dụng hàm Sumif thì chúng ta không thể tính tổng doanh thu của nhân viên vì cột Mã SV không ở cùng 1 bảng. Như vậy, bạn cần đến hàm Vlookup để tìm mã số nhân viên tưng ứng với từng người, rồi kết hợp thêm hàm Sumif để tính tổng doanh thu của nhân viên có kèm điều kiện.

Bước 1:

Chúng ta sẽ áp dụng công thức vào bảng. Công thức sẽ là:

=SUMIF(D:D,VLOOKUP(B12,A3:B7,2,FALSE),E:E)

Trong đó:

• SumiF và Vlookup là hàm tính tổng và hàm tìm kiếm có điều kiện.
• D:D là vùng chứa các ô điều kiện.
• B12 là vùng giá trị đối chiếu với cột doanh số, là giá trị cần tìm kiếm. Khi thay đổi tên thì cột doanh số cũng thay đổi theo.
• A3:B7 là vùng dữ liệu cột cần lấy dữ liệu để dò tìm giá trị cho vùng B12 ở bên trên.
• Số 2 là thứ tự xuất giá trị hiển thị lên màn hình, tùy theo cột cần lấy dữ liệu có bao nhiêu cột. Ở đây cột cần lấy dữ liệu là Mã NV ở vị trí thứ 2 cột B nên thứ tự sẽ là 2.
• Flase là phạm vi tìm kiếm tuyệt đối cho kết quả chính xác thay vì sử dụng True cho kết quả tương đối.
• E:E là khu vực cho trước doanh thu của từng nhân viên đạt được.

Bước 2:

Bạn sẽ nhập công thức bên trên tại ô C12 ở Bảng 3, rồi điền tên nhân viên muốn tính tổng doanh thu tại ô B12. Ở đây tôi sẽ tính tổng số doanh thu của nhân viên Phí Thanh Lan.

Ngay sau đó sẽ hiển thị tổng doanh thu mà nhân viên này đạt được. Tổng số tiền hoàn toàn chính xác.

Lưu ý với người dùng trong trường hợp khi tính tổng doanh thu và không hiển thị dấu phẩy phân cách các lớp hàng trong dãy số, bạn có thể tham khảo bài viết Cách phân cách hàng nghìn bằng dấu phẩy trong Excel để hiển thị lại dấu phẩy phân cách số trong Excel

Chúng ta sẽ cần đổi cột đó về định dạng Number là số, rồi điều chỉnh để hiển thị dấu phẩy phân cách trong Format Cells. Phần Decimal places chúng ta có thể tùy chỉnh tùy theo số mà bạn cần tính toán. Để dễ hơn có thể nhìn vào phần Sample.

Bạn có thể thay đổi định dạng của cột trước hoặc sau khi cho ra kết quả tính đều được.

Bước 3:

Bây giờ bạn có thể đổi tên của bất cứ nhân viên nào, không cần nhập công thức tính khác mà vẫn cho ra kết quả chính xác. Ví dụ tôi sẽ nhập vào ô B12 tên nhân viên Trần Thu Hà với mã số nhân viên là MS01, kết quả doanh thu cần tìm kiếm vẫn cho ra kết quả chính xác.

Trên đây là bài hướng dẫn chi tiết cách kết hợp hàm Sumif và hàm Vlookup trên Excel. Hai hàm Sumif và hàm Vlookup là hàm tính toán, hàm tìm kiếm dữ liệu vô cùng căn bản trên Excel. Và việc kết hợp 2 hàm lại sẽ giúp người dùng có thể tìm kiếm dữ liệu nhanh hơn, mà không cần tính toán thủ công cho dù đổi dữ liệu tìm kiếm đi chăng nữa.

Chúc các bạn thực hiện thành công!