Bạn đang xem bài viết Hàm If Kết Hợp Với Vlookup Và Hàm And, Or Trong Excel được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Trong bài viết này chúng ta cùng tìm hiểu xem làm thế nào để kết hợp IF với Vlookup hay IF với And và OR trong Excel. Trên đã giới thiệu chi tiết về cách sử dụng hàm IF, hàm Vlookup, tuy nhiên lại chưa giới thiệu cụ thể với các bạn về hàm And và hàm OR. Vì vậy, trước tiên chúng ta cùng tìm hiểu cách sử dụng hàm AND và OR.
Hàm AND trong excel
– Công dụng hàm AND: Trả về kết quả là TRUE nếu TẤT CẢ các đối số của hàm có giá trị là TRUE và trả về kết quả là FALSE nếu có ít nhất MỘT đối số của hàm có giá trị là FALSE.
Tức là, Hàm AND chỉ ĐÚNG khi tất cả các đối số (điều kiện) đều ĐÚNG, các trường hợp còn lại là SAI.
cú pháp hàm AND: =AND(logical1, [logical2], …)
– logical1: là đối số bắt buộc, điều kiện thứ nhất phải được trả về giá trị logic.
– logical2, … : tùy chọn, các điều kiện bổ sung tối đa 255 điều kiện.
– Các đối số đều phải trả về kết quả là giá trị logic như TRUE hoặc FALSE.
– Nếu một đối số mảng hoặc tham chiếu chứa văn bản hoặc các ô trống thì sẽ bỏ qua đối số đó.
– Nếu không chứa giá trị logic nào thì hàm AND trả về giá trị lỗi #VALUE!
Ví dụ 2: And(5<6, 10<9) = FALSE.
Trong hàm And() trên có 2 điều kiện: Điều kiện 1 là 5<6, Điều kiện 2 là 10<9. Điều kiện 1 trả về TRUE còn Điều kiện 2 trả về FALSE. Nên kết quả của hàm And() trả về sẽ là FALSE.
Hàm OR
– Công dụng hàm OR: Trả về kết quả là TRUE nếu có ít nhất MỘT đối số của hàm có giá trị là TRUE và trả về kết quả là FALSE nếu TẤT CẢ các đối số của hàm có giá trị là FALSE. Tóm lại, Hàm OR chỉ SAI khi tất cả các đối số (điều kiện) đều SAI, các trường hợp còn lại là ĐÚNG.
cú pháp hàm OR: =OR(logical1, [logical2], …)
Trong đó:
– Logical 1: bắt buộc, là một giá trị logic.
– Logical 2: tùy chọn, là một giá trị logic (tối đa 255 điều kiện khác nhau).
Ví dụ 1: Or(6<8, 10<9) = TRUE.
Trong hàm Or() trên có 2 điều kiện: Điều kiện 1 là 6<8, Điều kiện 2 là 10<9. Điều kiện 1 trả về TRUE còn Điều kiện 2 trả về FALSE. Nên kết quả của hàm OR() trả về sẽ là TRUE.
Ví dụ 2: Or(8<6, 10<9)= FALSE.
Trong hàm Or() trên có 2 điều kiện: Điều kiện 1 là 8<6, Điều kiện 2 là 10<9. Điều kiện 1 trả về FALSE còn Điều kiện 2 trả về FALSE. Nên kết quả của hàm OR() trả về sẽ là FALSE.
Sau khi đã hiểu cách sử dụng hàm AND và OR, ta tìm hiểu cách sử dụng kết hợp IF với Vlookup và IF với AND, OR.
Hàm IF kết hợp với Vlookup
Cho bảng dữ liệu như sau:
Đầu tiên hàm dò tìm giá trị C4 trong bảng $B$13:$D$17 sau đó kiểm tra giá trị D4 là khu vực nào, nếu là khu vực 1 lấy cột 2 (cột điểm chuẩn 1) là cột trả về, nếu không phải khu vực 1 thì lấy cột 3 (cột điểm chuẩn 2) là cột trả về trong hàm vlookup, sau đó kiểm tra giá trị H4 trong cột tổng điểm có lớn hơn điểm chuẩn không, nếu lớn hơn điểm chuẩn kết quả là Đậu, nếu không lớn hơn kết quả là Rớt.
Trong đó:
“Đậu”: kết quả trả về nếu biểu thức điều kiện trên Đúng
“Rớt”: kết quả trả về nếu biểu thức điều kiện trên Sai
Giải thích công thức VLOOKUP(C4,$B$13:$D$17,IF(D4=”1″,2,3),0)
Trong đó:
C4: là giá trị cần dò tìm (cột ngành thi)
$B$13:$D$17: Bảng dò tìm (có chứa cột ngành thi)
IF(D4=”1″,2,3): Giá trị cột trả về của hàm Vlookup
0: Kiểu dò tìm chính xác
Giải thích công thức: IF(D4=”1″,2,3)
Trong đó:
D4=”1″: Biểu thức điều kiện của hàm IF
2: Giá trị trả về nếu biểu thức điều kiện Đúng
3: Giá trị trả về nếu biểu thức điều kiện Sai
Yêu cầu: Có Học bổng nếu tổng điểm khu vực 1 lớn hơn 22, khu vực 2 lớn hơn 23
Chúng ta kết hợp hàm IF với hàm AND và OR, nhập công thứ như hình dưới và kéo kết quả xuống
Trong đó:
Để làm theo hướng dẫn các bạn có thể tải file mẫu ở liên kết sau: https://goo.gl/5QoLH2
Hướng Dẫn Cách Kết Hợp Hàm Vlookup Với Hàm If Trong Excel
Chào mừng các bạn đến với Seri bài viết: Tùy biến các hàm trong thực tế công việc.
Trong seri này chúng ta sẽ tìm hiểu những ví dụ thực tế, khi các yêu cầu công việc đòi hỏi phải tùy biến, kết hợp nhiều hàm với nhau thì mới giải quyết được yêu cầu.
Bài học ngày hôm nay là : Kết hợp hàm VLOOKUP với hàm IF
Dùng hàm IF để tránh lỗi #N/A cho hàm VLOOKUP
Ở hình trên, chúng ta thấy công thức ở ô E2 cho kết quả lỗi. Bởi vì nội dung ở ô D2 không có giá trị, tức là Lookup_Value không có nội dung nên sẽ báo lỗi #N/A
Để khắc phục lỗi này, chúng ta có thể sử dụng kết hợp hàm IF như sau:
= IF(D2=””,””, VLOOKUP(D2,$A$2:$B$7,2,0))
Nếu ô D2 (lookup_value của hàm vlookup) là rỗng thì sẽ rỗng
Nếu ô D2 không rỗng thì sẽ sử dụng hàm Vlookup
Dùng hàm IF để tự động thay đổi số cột kết quả trong hàm Vlookup
Ở ví dụ này, chúng ta muốn khi thay đổi điều kiện ở ô E1 để xét kết quả của hàm Vlookup tương ứng với điều kiện này.
Nếu E1 là Số tiền thì sẽ
Cột Giới tính: Cột 3
Chúng ta kết hợp hàm IF như sau:
= IF(D2=””,””,VLOOKUP(D2,$A$2:$C$7, IF(E1=”Số tiền”,2,3),0))
Khác với ví dụ 1, ở đây chúng ta cần mở rộng bảng tham chiếu bao gồm cả cột C
Để có thể sử dụng tốt việc kết hợp các hàm với nhau, chúng ta cần nắm rõ được logic của vấn đề trước, sau đó mới xác định sử dụng hàm nào, đặt thứ tự các hàm tại vị trí nào.
Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…
Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học Excel online tại Gitiho.com
Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…
Gitiho tự hào khi được đồng hành cùng:
50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
Hàm If Kết Hợp Hàm Or, Cách Kết Hợp Hàm If Với Hàm Or.
1.Chức năng của hàm IF trong Excel.
Hàm IF là một trong những hàm logic giúp người dùng kiểm tra một điều kiện nhất định và trả về giá trị mà bạn chỉ định nếu điều kiện là TRUE hoặc trả về một giá trị khác nếu điều kiện là FALSE.
Hàm IF kết hợp hàm OR trong excel giúp ta tính toán, kiểm tra và đối chiếu nhiều điều kiện khác nhau trả về kết quả tương ứng.
2.Cú pháp của hàm IF trong Excel.
Trong đó:
Logical_test (bắt buộc): Là một giá trị hay biểu thức logic trả về giá trị (đúng) hoặc (sai). Bắt buộc phải có. Đối với tham số này, bạn có thể chỉ rõ đó là ký tự, ngày tháng, con số hay bất cứ biểu thức so sánh nào.
Value_if_true (không bắt buộc): Là giá trị mà hàm sẽ trả về nếu biểu thức logic cho giá trị hay nói cách khác là điều kiện thỏa mãn.
Value_if_false (không bắt buộc): Là giá trị mà hàm sẽ trả về nếu biểu thức logic cho giá trị hay nói cách khác là điều kiện không thỏa mãn.
Trong một số trường hợp bài toán chứa nhiều điều kiện bạn cần sử dụng thêm hàm AND, OR để kết hợp nhiều điều kiện.
Bài toán có nhiều điều kiện, giá trị trả về chỉ cần thỏa mãn 1 trong các điều kiện bạn sử dụng thêm hàm OR trong biểu thức điều kiện. Cú pháp hàm OR: OR(logical1, logical2,..).Trong đó logical là các biểu thức điều kiện.
3.Hướng dẫn sử dụng hàm IF nhiều điều kiện.
3.1. Sử dụng hàm IF chỉ chứa 1 điều kiện cần xét.
Ví dụ: Đưa ra kết quả thi tuyển vào lớp 10 dựa vào kết quả thi 3 môn, nếu tổng điểm lớn hơn hoặc bằng 24 thì học sinh thi đỗ, ngược lại thí sinh thi trượt.
Hình 1: Kiểm tra nhiều điều kiện trong Excel.
Vậy trong trường hợp này chúng ta sẽ sử dụng hàm IF với điều kiện cơ bản nhất là nếu không đúng thì sai. Ở đây chúng ta sẽ gán cho hàm IF điều kiện là nếu tổng điểm lớn hơn hoặc bằng 24 thì “Đỗ” còn tổng điểm nhỏ hơn 24 thì “Trượt”.
Trong đó:
“Đỗ”: Giá trị trả về của hàm IF nếu biếu thức so sánh trả về là đúng.
“Trượt”: Giá trị trả về của hàm IF nếu biểu thức so sánh trả về là sai.
Sau khi nhập công thức cho ô I4, ta kéo xuống copy công thức cho những học sinh còn lại. Hoàn tất ta sẽ được kết quả như hình sau:
Hình 2: Hàm IF kết hợp hàm OR.
3.3. Hàm IF nhiều điều kiện kết hợp hàm OR.
Cú pháp của hàm OR trong Excel: OR(logical1, logical2,…)
Trong đó logical1 và logical2 là 2 mệnh đề logic.
Kết quả trả về của hàm OR:.
TRUE: Khi có một mệnh đề logic bất kì trong hàm OR là đúng.
FALSE: Khi tất cả các mệnh đề bên trong hàm OR đều sai.
Bạn sử dụng kết hợp các hàm IF và hàm OR theo cách tương tự như với hàm AND ở trên.
Trong đó:
“Đỗ”: Giá trị trả về của hàm nếu biếu thức so sánh trả về là đúng.
“Trượt”: Giá trị trả về của hàm IF nếu biểu thức so sánh trả về là sai.
Sau khi nhập công thức cho ô E4, ta kéo xuống copy công thức cho những học sinh còn lại. Hoàn tất ta sẽ được kết quả như hình sau:
Hình 4: Hàm IF kết hợp hàm OR.
3.4. Hàm IF nhiều điều kiện kết hợp hàm AND và hàm OR.
Trong trường hợp bạn phải đánh giá dữ liệu của mình dựa trên nhiều điều kiện, chúng ta sẽ phải sử dụng cả hai hàm AND và OR cùng một lúc.
Ở những ví dụ trên chúng ta đã nắm được cách sử dụng hàm IF kết hợp với hàm AND và hàm IF kết hợp với hàm OR. Nên ở phần này chúng ta chỉ cần kết hợp 2 hàm này lại để đặt điều kiện cho biểu thức logic sao cho khoa học phù hợp với yếu cầu thực tế của bài toán.
Hình 5: Kiểm tra nhiều điều kiện trong Excel.
Với điều kiện trên, ta có thể phân tích thành 2 điều kiện nhỏ:
Điều kiện 1 và điều kiện 2 ta viết bằng hàm AND, cuối cùng sử dụng hàm OR kết hợp 2 kiều kiện trên làm điều kiện kiểm tra logic trong hàm IF và cung cấp các đối số TRUE (Đúng) và FALSE (Sai). Kết quả là bạn sẽ nhận được công thức IF sau với nhiều điều kiện AND/OR:
Trong đó:
“Đỗ”: Giá trị trả về của hàm nếu biếu thức so sánh trả về là đúng.
“Trượt”: Giá trị trả về của hàm IF nếu biểu thức so sánh trả về là sai.
Sau khi nhập công thức cho ô E4, ta kéo xuống copy công thức cho những học sinh còn lại. Hoàn tất ta sẽ được kết quả như hình sau:
Hình 6: Hàm IF kết hợp hàm OR. 4. Lưu ý khi sử dụng hàm IF nhiều điều kiện trong Excel.
Như bạn vừa thấy, dùng hàm IF nhiều điều kiện trong Excel không đòi hỏi phương pháp, công thức cao siêu. Để cải thiện công thức hàm IF lồng nhau và tránh những lỗi thông thường, hãy luôn nhớ điều cơ bản sau:
Hàm IF không phân biệt chữ hoa và chữ thường. Cũng giống như phần lớn những hàm khác, hàm IF được mặc định không phân biệt chữ hoa hay chữ thường. Điều này có nghĩa rằng các biểu thức logic có chứa ký tự không thể phân biệt được kiểu chữ hoa hay thường trong công thức hàm IF. Ví dụ khi so sánh một ô nào đó với “Hà Nội” thì hàm IF sẽ hiểu Hà Nội, hà nội, HÀ NỘI, … là như nhau.
Video hướng dẫn. Gợi ý học tập mở rộng.
Trọn bộ khoá học Excel cơ bản miễn phí: Học Excel cơ bản
Sử Dụng Hàm If Kết Hợp Với Hàm Dò Tìm Vlookup, Hlookup
Như đã biết, hàm và là hàm dò tìm và trả về các giá trị tương ứng từ bảng tham chiếu.
Trong thực tế nhiều trường hợp yêu cầu cần phải kết hợp hàm với các hàm dò tìm trên. chúng tôi sẽ cùng các bạn tìm hiểu về sự kết hợp mạnh mẽ này.
Ví dụ bài toán yêu cầu: Nếu thỏa điểu kiện 1 thì dò tìm và trả về các giá trị trong bảng A, nếu thỏa điều kiện 2 thì dò tìm và trả về các giá trị trong bảng B,…
Công thức chung
IF(điều kiện 1, VLOOKUP(ô cần tham chiếu, bảng tham chiếu, cột lấy giá trị trả về trong bảng tham chiếu, dò tìm chính xác hay tương đối), giá trị khi không thỏa điều kiện 1)
Hàm này sẽ chạy theo thứ tự:
Xét điều kiện 1, nếu thỏa sẽ chạy hàm VLOOKUP
Nếu không thỏa sẽ trả về giá trị không thỏa điều kiện 1.
VLOOKUP(IF(điều kiện, ô tham chiếu 1, ô tham chiếu 2), IF(điều kiện, bảng tham chiếu 1, bảng tham chiếu 2), IF(điều kiện, cột lấy giá trị trả về trong bảng tham chiếu, cột khác), IF(điều kiện, dò tìm chính xác, tương đối))
Như vậy tùy vào vấn đề mà ta có thể linh hoạt đưa hàm vào các vị trị khác nhau của các hàm dò tìm.
Ví dụ minh họa
Ví dụ 1:
Dựa vào bảng tham chiếu BMI của người châu Á và châu Mỹ trả về kết quả trong cột phân loại.
Ta có công thức = IF(B2=”mỹ”, VLOOKUP(C2,$A$15:$C$20,3,TRUE), VLOOKUP(C2,$B$15:$C$20,2,TRUE))
Ở công thức này trình tự thực hiện của hàm sẽ là: nếu bệnh nhân là người châu Mỹ thì sẽ dò tìm ở bảng A15:C20. Nếu bệnh nhân không phải là người châu Mỹ (vì trường hợp này chỉ cho châu Mỹ và châu Á nên trường hợp ngược lại sẽ là châu Á) thì dò tìm ở bảng B15:C20.
HOẶC
= VLOOKUP(C2, IF(B2=”mỹ”,$A$15:$C$20,$B$15:$C$20), IF(B2=”mỹ”,3,2),TRUE)
Ở công thức này tương tự như công thức trên, tuy nhiên trường hợp này là lồng hàm IF vào hàm dò tìm.
Ví dụ 2:
Xây dựng công thức cho cột phần thưởng, giá trị phần thưởng dựa vào điều kiện trong bảng tham chiếu.
Ta có công thức: = VLOOKUP(C2,$A$14:$C$17, IF(B2=”nam”,2,3),TRUE)
Công thức này sẽ dò tìm trong bảng A14:C17, sau đó khi trả về kết quả là cột 2 (dành cho nam) hay cột 3 (dành cho nữ) sẽ xét đến hàm IF.
Như vậy ở những vấn đề phức tạp sự lồng ghép hàm IF và các hàm dò tìm giúp ta đơn giản hóa công thức, công thức dễ nhớ và tư duy hơn.
Bài viết của TTTH – chúng tôi
Cập nhật thông tin chi tiết về Hàm If Kết Hợp Với Vlookup Và Hàm And, Or Trong Excel trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!