Top 5 # Xem Nhiều Nhất Cách Dùng Hàm If Kết Hợp And Mới Nhất 4/2023 # Top Like

Kết hợp hàm IF và hàm And. Đây là một trong những cách kết hợp phổ biến của hàm IF bởi nó được áp dụng rất nhiều trong các tình huống thực tế tại Doanh nghiệp, Trường học, Trung tâm,…

Để hiểu được cách kết hợp của 2 hàm này chúng ta phải hiểu được cách sử dụng của từng hàm:

2. Ví dụ về cách kết hợp hàm if và hàm and

Tiếp đến, chúng ta sẽ cùng giải quyết 1 yêu cầu trong bài tập về cách kết hợp hàm Excel để thấy được hàm If và hàm and kết hợp như thế nào:

Yêu Cầu: Dựa vào Điểm Thực Hành (TH) và Điểm Lý Thuyết (LT) để tính điểm nhận xét biết rằng:

A/ – Nếu cả 2 điểm LT và TH đều lớn hơn hoặc bằng 8 thì Điểm nhận xét là 1

B/ – Nếu cả 2 điểm LT và TH đều lớn hơn hoặc bằng 7.5 thì Điểm nhận xét là 0.5

C/ – Còn lại thì điểm nhận xét bằng 0

3. Phân tích đề bài và cách sử dụng hàm

Như vậy trong trường hợp này mình có 3 điều kiện và chúng ta phải sử dụng hàm IF trong trường hợp này:

If (A thỏa mãn, 1, If(B thỏa mãn, 0.5, 0))

Vấn đề nằm ở chỗ là làm thế nào để biết A hoặc B có thỏa mãn hay không bởi lẽ mình có 2 điểm (TH và LT) dùng để so sánh:

Nếu điều kiện của chúng là tất cả các giá trị đều phải thỏa mãn điều kiện con của nó thì chúng ta sẽ dùng hàm AND:

Chúng ta có thể thấy:

HS số 1 (Kim), Số 3 (Minh) có 2 điểm LT và TH đều lớn hơn hoặc bằng 8 nên được 1 điểm nhận xét

HS số 2 (Hoa) có cả 2 điểm nhỏ hơn 7.5 nên 0 điểm nhận xét

HS số 4 (Quân) có 1 điểm nhỏ hơn 8 nhưng lớn hoặc bằng 7.5 nên chỉ được 0.5 điểm nhận xét

Còn đối với HS số 5 (Duy) lại có 1 điểm nhỏ hơn 7.5 nên dù điểm còn lại có là 10 thì cũng 0 điểm nhận xét.

Đó là ý nghĩa của việc dùng hàm AND (Tất cả đều phải thỏa mãn)

Trung Tâm Đào tạo Kỹ năng làm việc chúng tôi

Hàm IF kết hợp hàm LEFT trong excel. Khi điều kiện sử dụng trong hàm if được lấy từ một hoặc một vài ký tự của một chuỗi text tính từ bên trái.

Ví dụ như Mã phiếu chi chẳng hạn: PC01001.

PC đại diện cho Phiếu Chi

01 đại diện cho tháng phát sinh

001 – Phiếu chi số 001 của tháng.

HƯỚNG DẪN SỬ DỤNG HÀM IF KẾT HỢP HÀM LEFT

Tìm tên mặt hàng cho từng lần bán hàng căn cứ vào Mã Hóa Đơn biết rằng:

Nếu ký tự đầu tiên của Mã HĐ là J thì tên mặt hàng là Jean, còn lại nếu là K thì tên mặt hàng Kaki.

Mỗi một nghiệp vụ bán hàng đều có một Mã Hóa đơn riêng, Mã hóa đơn trong ví dụ này được cấu tạo bởi:

– Ký tự đầu tiên: là mã của từng mặt hàng

– Ký tự tiếp theo: là mã giảm giá

– 3 ký tự cuối cùng: là mã số của nghiệp vụ

Như vậy để hoàn thành yêu cầu trên chúng ta phải dùng hàm if để tìm tên mặt hàng với diễn giải hàm như sau:

= IF ( Ký tự đầu tiên của Mã HĐ = “J”, “Jean”, “Kaki”)

Hàm IF ở đây chỉ có 3 thành tố mà không cần phải lồng thêm hàm nào nữa đó là bởi vì chúng ta chỉ có hai mặt hàng theo như danh sách này là: Jean và Kaki,

Do đó khi so sánh ký tự đầu tiền của Mã HĐ với “J”, nếu nó thỏa mãn thì tên mặt hàng sẽ là Jean, còn lại nếu không thỏa mãn thì tên mặt hàng sẽ là Kaki.

Vấn đề là làm thế nào để tách được ký tự đầu tiên từ Mã HĐ:

Để làm được điều này ta sẽ sử dụng hàm LEFT.

= LEFT ( Mã HĐ, 1)

Số 1 trong hàm left có nghĩa là chúng ta chỉ muốn lấy 1 ký tự tính từ bên trái của Mã HĐ.

[sociallocker id=”515″]

Cách sử dụng hàm IF kết hợp hàm LEFT trong ví dụ này được viết như sau:

=IF (LEFT (B3,1) = “J”, “Jean”, “Kaki”)

Bạn cần nhập công thức trên vào ô đầu tiên trong cột mặt hàng, sau đó copy công thức cho các dòng còn lại trong cột mặt hàng.

Xong, như vậy là bạn đã tìm được tên của một danh sách hàng nghìn dòng mặt hàng chỉ trong nháy mắt với việc sử dụng hàm excel.

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.