Thực hiện hàm sumif kết hợp vlookup trên excel như thế nào? Khi nào thì ta cần áp dụng sự kết hợp của 2 hàm sumif và vlookup ?
Chắc hẳn những ai chuyên dùng công cụ Excel đều biết đến 2 hàm : Sumif – tính tổng giá trị theo điều kiện và Vlookup – tìm kiếm giá trị trong chuỗi. Đây là 2 hàm đặc biệt sử dụng nhiều trong Excel và sự kết hợp giữa 2 hàm này là rất phổ biến.
1. Cách dùng hàm vlookup
Hàm Vlookup được dùng để tra cứu dữ liệu trong một mảng xác định nào đó. Hàm cho phép người sử dụng tìm kiếm dữ liệu trên 1 chuỗi nhất định theo các điều kiện cho trước.
Công thức hàm vlookup : =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Trong đó :
Lookup_value : giá trị dùng để tìm kiếm
Table_array: là 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).
Cách thức hàm sumif kết hợp vlookup
2. Dùng hàm Sumif như nào ?
Sumif là hàm tính tổng có điều kiện. Hàm thường được sử dụng nhiều trong kế toán, nó hỗ trợ tính toán nhanh, chính xác và gia tăng hiệu quả trong công việc của bạn như :
– Tính tổng phụ cấp cho nhân viên
– Tính lương cho nhân viên theo một điều kiện nào đó
– Lập bảng cân đối phát sinh theo tháng/năm, bảng nhập xuất tồn kho,…
Cú pháp của hàm : =SUMIF(range, criteria, [sum_range])
Trong đó :
=SUMIF : là cú pháp câu lệnh bắt buộc.
Range ( bắt buộc ) : là vùng chọn có chứa các ô điều kiện. Ví dụ : từ ô B2 đến ô B5 (B2:B5).
Criteria ( bắt buộc ): là điều kiện, có thể ở dạng chữ, số, ô tham chiếu, biểu thức hay một hàm xác định sẽ cộng các ô đó. Biểu hiện như : “chuối”, 99, B52, “<3”, “11” hoặc TODAY().
Sum_range ( tùy chọn ) : vùng chọn thực tế cần tính tổng mà không giống vùng đã chọn trong đối số Range. Nếu như đối số Sum_range được bỏ qua thì Excel sẽ mặc định cộng các ô được xác định trong Range.
3.Cách thức mà hàm Sumif kết hợp Vlookup
Đây là một trong những bài học excel nâng cao mà bạn cần nắm rõ. Cách sử dụng hàm sumif kết hợp vlookup giúp chúng ta truy xuất nhanh mọi đối tượng cần tìm với kết quả chính xác mà khi ta thay đổi đối tượng sẽ không cần phải nhập lại công thức.
Để có thể hiểu rõ hơn về cách cách dùng hàm sumif kết hợp vlookup, mời các bạn theo dõi ví dụ cụ thể ngay dưới đây:
Đầu tiên, ta có 3 bảng dữ liệu sau :
Bài ví dụ về cách sử dụng hàm sumif kết hợp vlookup
Bảng 1: Bao gồm họ và tên, mã nhân viên
Bảng 2: Bao gồm mã nhân viên và doanh số
Bảng 3: Bao gồm họ và tên , doanh số (tình trạng để trống không có dữ liệu)
Đề bài yêu cầu xuất kết quả ra bảng số 3 bao gồm họ tên và tổng doanh số mà người đó đạt được. Ngoài ra có thể tra cứu được điểm thi của những sinh viên khác khi thay đổi họ và tên tương ứng.
Bước 1: Ở đề bài này chúng ta sẽ sử dụng hàm sumif kết hợp vlookup để tính tổng doanh số của nhân viên đó với điều kiện tìm kiếm thỏa mãn các yêu cầu.
Trong trường hợp này, nếu sử dụng hàm Sumif không thể xuất ra kết quả được vì giá trị doanh số nằm ở cột Mã SV không thuộc bảng 2 và không liên quan gì đến bảng 1. Do đó cần sử dụng hàm tìm kiếm Vlookup để dò tìm mã nhân viên tương ứng sau đó kết hợp với hàm SUM để tính tổng với điều kiện là mã nhân viên thỏa mãn.
Công thức của bài này là : =SUMIF(D:D,VLOOKUP(G6,$A$6:$B$9,2,FALSE),E:E)
– Sumif và Vlookup là tên hàm tính tổng và hàm tìm kiếm theo điều kiện.
– D:D là vùng được lựa chọn để chứa các ô điều kiện
– G6 là giá trị đối chiếu với cột doanh số, là giá trị dò tìm. Tại đây khi bạn thay đổi tên thì cột doanh số cũng thay đổi theo.
– $A$6:$B$9 là thứ tự cột cần lấy dự liệu để dò gì cho giá trị G6 ở 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ó mấy cột, vì cột Mã NV ở vị trí thứ 2 nên chúng ta đặt là 2.
– False 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 ô H6 ở 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 Trần Thị Yến.
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
Cách dùng hàm sumif kết hợp vlookup
Trên đây là bài hướng dẫn chi tiết cách sử dụng hàm Sumif kết hợp 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ù dữ liệu tìm kiếm có thay đổi đi chăng nữa.
Chúc các bạn thực hiện thành công!