Cách Dùng Hàm If Kết Hợp Vlookup / TOP 7 # Xem Nhiều Nhất & Mới Nhất 3/2023 # Top View

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).

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.

Đầu tiên, ta có 3 bảng dữ liệu sau :

Đề 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.

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

Hàm VLOOKUP kết hợp hàm if / Hàm hlookup trong excel với các phiên bản hoàn toàn giống nhau. Trường cũng nói về cách sử dụng hàm HLOOKUP kết hợp hàm if.

Vlookup là một hàm tìm kiếm rất phổ biến trong excel. Bài viết gồm có: Cú pháp, ví dụ minh họa, bài tập thực hành.

1/ Khi nào dùng hàm vlookup và hlookup trong excel.

Vlookup – là hàm tìm kiếm theo cột:

Hlookup – là hàm tìm kiếm theo dòng.

Điểm chung giữa 2 hàm tìm kiếm này:

– Đều là hàm tìm kiếm giá trị

– Cú pháp hàm đều gồm 4 thành tố: Điều kiện tìm kiếm, vùng tìm kiếm, cột/ dòng chỉ định và kiểu tìm kiếm.

Sự khác nhau giữa hai cú pháp hàm excel trên là ở thành tố thứ 3:

+ Cột chỉ định – áp dụng cho hàm vlookup

+ Dòng chỉ định – áp dụng cho hàm hlookup

2/ Ví dụ về minh họa vlookup kết hợp if/ hlookup kết hợp if

Tính Số Điện tiêu thụ trong và ngoài định mức – Dựa trên “Bảng Định Mức” phía dưới.

Nếu số tiêu thụ < số định mức: Số trong định mức = số tiêu thụ

Số tiêu thụ = Số mới – Số cũ

Số định mức: dùng hàm vlookup hoặc hàm hlookup để tìm từ Bảng định mức.

Nhiệm vụ tiếp theo là tìm số tiêu thụ định mức để so sánh với số tiêu thụ là chúng ta sẽ biết được số trong và ngoài định mức.

Làm thế nào để tính được Số tiêu thụ định mức của từng hộ gia đình.

Theo như Bảng Định Mức ở dưới thì Số tiêu thụ định mức của mỗi hộ được xác định dựa trên 2 yếu tố:

Cột: A, B, C, D

Dòng: NN, CB, ND, KD, SX

Ở trong Bảng Thanh Toán Tiền Điện:

Chúng ta sẽ thấy mỗi một hộ gia đình đều có Mã Hộ ở cột D, trong đó Mã hộ gồm 2 phần:

– 2 Ký tự đầu tiên: là một trong 5 loại sau: NN, CB, ND, KD, SX

– 1 Ký tự cuối cùng: là một trong 4 loại sau: A, B, C, D

Như vậy ta có thể dùng hàm Vlookup để tìm theo A, B, C, D hoặc dùng hàm Hlookup để tìm theo NN, CB, ND, KD, SX.

Trong bài này tôi sẽ hướng dẫn dùng hàm Vlookup.

Cấu trúc của hàm Vlookup ( Lookup_value, Table_array , Col_index_num, Range_lookup )

2.1/ Cách sử dụng hàm Vlookup kết hợp hàm if trong ví dụ này:

IF(LEFT(D3,2)=”NN”,2,IF(LEFT(D3,2)=”CB”,3,IF(LEFT(D3,2)=”ND”,4,

(2) – Table_array: Vùng tìm kiếm, chính là Bảng Định Mức, trong đó cột đầu tiên trong vùng tìm kiếm là cột chứa A, B, C và D

Vùng điều kiện nên được cố định cả dòng cả cột.

Vì như thế khi copy công thức sang ô khác thì vùng điều kiện không bị thay đổi.

Bởi lẽ tất cả các hộ tiêu thụ điện đều có số định mức nằm trong bảng định mức.

(3) – Col_index_num: Số thứ Cột chứa giá trị đang tìm kiếm (Cột chỉ định)

Ở trường số (3) chúng ta sẽ dễ dàng nhận thấy rằng:

Nếu 2 ký tự đầu của mã hộ là NN thì Cột chỉ định sẽ là 2 tính từ cột đầu tiên. Còn nếu không phải NN mà là CB thì cột chỉ định sẽ là 3, …

Chúng ta suy luận tương tự thì chúng ta sẽ tìm được tất cả các trường hợp cho cột chỉ định.

Ở trường này chúng ta sẽ phải dùng hàm Left để tách ra 2 ký tự đầu tiên trong Mã hộ. Sau đó dùng kết quả của hàm này để tìm số thứ tự cột chỉ định

(4) – Range_lookup: Kiểu tìm kiếm là 0 – Kiểu tìm kiếm chính xác 100%.

2.2/ Cách sử dụng hàm Hlookup kết hợp hàm if trong ví này:

Sự khác nhau giữa 2 hàm này là:

– Tên hàm: Hlookup thay vì Vlookup

– Điều kiện tìm kiếm: khi dùng hàm hlookup thì dòng đầu tiên (row 18) của vùng dữ liệu chứa 2 ký tự đầu của mã hộ NN hoặc CB. Do đó điều kiện tìm kiếm cần tách ra 2 ký tự đầu tiên của mã từng hộ dân.

– Thành tố thứ 3 là Dòng chỉ định, thay vì cột chỉ định như CỘT chỉ định.

Nhìn ở Bảng định mức:

A: là dòng thứ 2 tính từ dòng 18

B: là dòng thứ 3 tính từ dòng 18

….

Như vậy, Trường đã hướng dẫn xong các bạn về Hàm vlookup kết hợp hàm if/ Hàm hlookup kết hợp hàm if.

Tổng hợp bài tập excel cơ bản đến nâng cao

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.

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.

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.

Như các bạn đã biết, hàm VLOOKUP và hàm IF trong Excel là hai hàm có giá trị khác nhau. Hàm VLOOKUP là một hàm dùng để tra cứu kết quả trả về theo hàng dọc. Vậy, làm sao để hàm VLOOKUP kết hợp được với hàm IF chuẩn? Bài viết này sẽ trả lời cho các bạn.

1. CÁC TRƯỜNG HỢP SỬ DỤNG HÀM VLOOKUP KẾT HỢP HÀM IF

Có 3 trường hợp cần chú ý, cụ thể như sau:

Trường hợp 1: Dùng để so sánh kết quả VLOOKUP,

Trường hợp 2: Xử lý lỗi

Trường hợp 3: Giúp tra cứu dựa trên hai giá trị.

Lưu ý: Để sử dụng các hàm IF và VLOOKUP cùng nhau, bạn nên lồng hàm VLOOKUP bên trong hàm IF.

2. XÂY DỰNG CÚ PHÁP CHO HÀM VLOOKUP

Hàm VLOOKUP ( Tra cứu dọc) tìm kiếm một giá trị trong cột ngoài cùng bên trái của bảng và sau đó trả về một giá trị trong cùng một hàng từ một cột khác mà bạn chỉ định.

Cú pháp:

=VLOOKUP(Lookup_value, Table, Col_index_ num, [Range_lookup])

Lookup_value – Giá trị cần tìm trong cột đầu tiên của bảng.

Table – Bảng để truy xuất một giá trị.

Col_index – Cột trong bảng để truy xuất một giá trị.

range_lookup – [tùy chọn] TRUE = đối sánh gần đúng (mặc định). FALSE = đối sánh chính xác

3. VÍ DỤ VỀ SỰ KẾT HỢP CỦA HÀM VLOOKUP VỚI HÀM IF

Ví dụ 1: Dùng IF để so sánh giá trị trong hàm VLOOKUP

Mô tả: Dựa vào cột dữ liệu có trong các ô , hãy tìm hiểu xem tên James được đề cập trong ô có phần thưởng dựa trên doanh số lớn hơn 10000$ hay không?

Các bước thực hiện:

Bước 3: Nhấn Enter được kết quả trả về như hình phía bên dưới

Yêu cầu: Sử dụng hàm VLOOKUP kết hợp với hàm IF để tìm ra các lỗi

Khi thực hiện xong sẽ trả về lỗi #N/A. Có nghĩa là tên Jam không tồn tại trong các ô

Vì vậy, Để xử lý lỗi này, ta thực hiện bằng cách lồng hàm VLOOKUP và ISNA vào bên trong hàm IF.

Bước 2: Nhập công thức: =IF(ISNA(VLOOKUP(E3,B3:C9,2,FALSE )), “Name not found”, VLOOKUP(E3,B3:C9,2,FALSE )) vào ô F3.

Bước 3: Nhấn Enter được kết quả trả về như hình dưới

Ví dụ 3: Dùng IF để tra cứu hai giá trị trong hàm VLOOKUP

Xét ví dụ, các ô B2:B7 chứa giá trị cho các sản phẩm ở hai hàng khác nhau.

Để tìm giá trị của sản phẩm trong ô F3 , bạn thực hiện như sau:

Bước 2: Trong ô H3 nhập công thức sau:

Bước 3: Ấn Enter được kết quả như hình dưới

VIDEO HƯỚNG DẪN SỬ DỤNG HÀM VLOOKUP, HÀM HLOOKUP: