Hàm SUMIF kết hợp VLOOKUP: công thức, cách sử dụng

Google Sheet, Microsoft Excel là một phần mềm hỗ trợ tính toán, thống kê rất phổ biến trong giới văn phòng. Một trong những tính năng quan trọng của Excel là dùng hàm SUMIF kết hợp VLOOKUP để truy tìm, đối chiếu các giá trị thỏa mãn điều kiện rồi tính tổng các giá trị ở một bảng tính khác. Cùng Máy Tính Vui tìm hiểu rõ hơn qua bài viết sau các bạn nhé.

Giới thiệu Hàm SUMIF là gì, hàm VLOOKUP là gì?

Giới thiệu hàm SUMIF

Như đã biết, hàm SUM là hàm cho phép bạn tính tổng các giá trị trong cột.

Tuy nhiên, nếu bạn không muốn tính tổng toàn bộ giá trị trong cột mà chỉ tập trung vào vài dữ liệu cần thiết để tính toán thì phải dùng hàm SUMIF.

Khác với hàm Sum, công thức hàm SUMIF sẽ thêm vào điều kiện để người dùng loại bỏ các giá trị không thỏa mãn và chỉ tính tổng những giá trị đáp ứng tiêu chuẩn mà bạn đã định rõ, ví dụ như =SUMIF(A2:A10,”>10″).

Cú pháp chuẩn của hàm SUMIF trong Excel được khái quát như sau:

= SUMIF(range, criteria, [sum_range])

Trong đó:

• Range: đây là vùng được chọn chứa các điều kiện cần thiết.
• Criteria: là tập hợp các điều kiện để công thức biết cần tính tổng giá trị nào, biểu thị dưới dạng số, tham chiếu ô, biểu thức, hay hàm Excel khác.
• [sum_range]: là phần xác định dải ô nơi mà các giá trị của ô tương ứng sẽ được cộng.

Giới thiệu về hàm VLOOKUP

Vai trò của hàm Vlookup trong Excel đúng như tên gọi, là dùng để tìm kiếm dữ liệu bạn mong muốn. Dựa trên một chuỗi nhất định theo các điều kiện cho trước, người dùng sẽ tra cứu dữ liệu và nhận được kết quả theo ý muốn.

Hàm Vlookup trong Excel được sử dụng rất phổ biến, thường thấy nhất là dùng để tra cứu các loại mã số như mã sinh viên, mã nhân viên thuận tiện cho việc truy xuất dữ liệu. Công thức chuẩn của hàm Vlookup như sau:

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

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

Cách dùng hàm SUMIF kết hợp VLOOKUP đúng nhất

Trong chương trình học Excel nâng cao, bạn nhất định sẽ bắt gặp các bài tập bắt buộc sử dụng kết hợp cả hai hàm SUMIF và VLOOKUP để tính toán.

Việc kết hợp hai hàm trên giúp truy xuất dữ liệu rất nhanh và chính xác.

Để hiểu hơn về cách kết hợp hai hàm SUMIF và VLOOKUP, ta hãy theo dõi ví dụ kết hợp ba bảng dữ liệu ngay sau đây. Trong hình ta thấy rõ các bảng lần lượt theo thứ tự là: Họ tên, danh số kèm một bảng điền dữ liệu.

Nhiệm vụ của chúng ta là sử dụng các thuật toán phù hợp, trong trường hợp này là hai hàm hàm SUMIF và VLOOKUP để xuất dữ liệu họ tên khớp với doanh thu mà mỗi người đạt được vào vị trí chỗ trống ở bảng số 3.

Để hoàn thành bảng, các bạn làm theo hướng dẫn chi tiết sau:

Thực hành ví dụ sử dụng hàm SUMIF kết hợp VLOOKUP

Bước 1: Đầu tiên, bạn tiến hành lập công thức chung sử dụng hai hàm VLOOKUP và SUMIF với nhau. Nếu chỉ dùng mỗi hàm SUMIF thì chắc chắn bạn sẽ không xuất được kết quả mong muốn vì chênh lệch giá trị ở cột dữ liệu.

Có thể thấy, cả hai bảng 1 và 2 có điểm chung duy nhất là mã nhân viên. Vậy nên bạn cần kết hợp thêm hàm VLOOKUP để dò tìm mã nhân viên tương ứng, sau đó thực hiện tính tổng với điều kiện là mã nhân viên.

Công thức chuẩn sẽ là:

=SUMIF(D:D,VLOOKUP(G7,A7:B13,2,FALSE),E:E)

Trong đó:

• D:D là vùng giá trị được lựa chọn có dữ liệu các ô điều kiện
• G7 là giá trị đối chiếu so sánh với cột doanh số của trang tính, là giá trị cần tìm. Tại đây khi bạn thay đổi tên dữ liệu thì cột doanh số cũng thay đổi theo.
• A7:B13 là thứ tự lần lượt các cột cần lấy dữ liệu để kiểm tra cho giá trị ô G7 ở trên.
• Số 2 là thứ tự xuất giá trị dữ liệu, hiển thị lên màn hình trang tí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 đặt là 2.
• False là phạm vi tìm kiếm giá trị tuyệt đối ra kết quả chính xác thay cho việc sử dụng True cho kết quả tương đối.

Bước 2: Bạn nhập công thức vào ô H7 tức giá trị đầu tiên của cột doanh số ở bảng 3. Trước đó bạn nhớ phải nhập họ tên nhân viên cần tìm kiếm ở cột G7. Để chắc chắn, bạn có thể thử kiểm tra lại bằng cách thủ công.

Hi vọng bài viết thủ thuật Excel trên đã hướng dẫn bạn cách sử dụng hàm Sumif kết hợp Vlookup tiêu chuẩn để tính toán các giá trị cần thiết một cách dễ dàng. Bạn có thể thực hiện ngay cho bảng dữ liệu của mình một cách chính xác.

Cách này có thể sử dụng trên cả Google Sheet hoặc Excel một cách dễ dàng vì cả 2 đều sử dụng chung cấu trúc câu lệnh này khi sử dụng để lọc dữ liệu cho bạn.

Lưu ý hai hàm này này có có thể kết hợp và lồng vào nhau để truy xuất dữ liệu ở rất nhiều bảng khác nhau dễ nhất dành cho bạn. Khi dữ liệu quá lớn bạn nên lựa chọn lọc theo cách đơn giản nhất tránh làm chậm máy nhé!