Cách sử dụng hàm VLOOKUP kết hợp hàm IF
|
HàmIF và hàm VLOOKUP là hai hàm phổ biến trongExcel mà hầu như chúng tađều biết. Vậy bạn có biết cách kết hợp 2 hàm này trong cùng 1 công thứcđể tăng hiệu quả không? Hãy cùng HọcExcel Online tìm hiểu cáchviết hàm IF kết hợp hàm VLOOKUP trongExcel nhé: Show
Ví dụ 1: DùngIFđể bẫy lỗi 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àmIF như sau: =IF(D2=,,VLOOKUP(D2,$A$2:$B$7,2,0))
Ví dụ 2: DùngIFđể tùy biến vị trí cột tham chiếu 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àmIF 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 Ví dụ 3: Xácđịnhđơn giá sản phẩm khi đơn giá có sự thayđổi theo thángCho bảng dữ liệu như sau: Trong bảng dữ liệu này chúng ta thấy thời gian phát sinh gồm 2 tháng 4 và 5.Và trong tháng 4, các sản phẩm có 1đơn giá. Sang tháng 5đơn giáđó thayđổi theo bảngđơn giá tháng 5. Vậy làm thế nàođể xácđịnhđượcđúngđơn giá theo từng thờiđiểm phát sinh? Cách làm là chúng ta phải xácđịnhđược tháng của từng lần phát sinh, rồi dựa vàođóđể biện luận xem lần phát sinhđó là tháng mấy. Bước 1: Xácđịnh tháng với hàm MONTH Hàm MONTH là hàm giúp xácđịnh số tháng trong 1ô chứa dữ liệu thời gian. Khi xét MONTH(A2) ta thuđược kết quả bằng 4, MONTH(A5) cho kết quả bằng 5 Bước 2: Biện luận bằng hàmIF Kết quả của hàm MONTH chính làcăn cứ để xác định bảng tham chiếucủa hàm VLOOKUP.Nếu kết quả bằng 4 thì tham chiếu trong bảngđơn giá tháng 4, kết quả bằng 5 thì tham chiếu trong bảngđơn giá tháng 5. Vậy nên ta có thể viết: D2=IF(MONTH(A2)=4,sử dụng vlookup tham chiếu tới bảng tháng 4, sử dụng tham chiếu tới bảng tháng 5) Ởđây chỉ có tháng 4 hoặc tháng 5, nên chúng ta có thể biện luận theo hướng không phải tháng 4 thì là tháng 5. Bước 3: Tham chiếu bằng hàm VLOOKUP Với việc sử dụng bảng tháng 4để tham chiếu, ta có hàm vlookup như sau: =VLOOKUP(B2,G3:H7,2,0) Trongđó:
Tương tự nếu tham chiếu bảng đơn giá tháng 5 chúng ta có: =VLOOKUP(B2,J3:K7,2,0) Bước 4: Kết hợp các hàm trong cùng 1 công thức Khi xácđịnhđược từng hàm riêng lẻ, chúng ta có thể ghép các hàm vào trong công thức theo thứ tự: D2=IF(MONTH(A2)=4,=VLOOKUP(B2,G3:H7,2,0),VLOOKUP(B2,J3:K7,2,0)) Tuy nhiên chúng ta cần lưuý là các bảng tham chiếuđơn giá tháng 4, tháng 5 là những bảng có phạm vi xácđịnh. Nếu tham chiếu tới các vùng bảng này phải cốđịnh tọađộ lạiđể tránh vùng tham chiếu bị thayđổi (xem thêm bài:Cách giữ ô tham chiếu cố định trong Excel) Khiđó công thức sẽđược sửa lại là: =IF(MONTH(A2)=4,VLOOKUP(B2,$G$3:$H$7,2,0),VLOOKUP(B2,$J$3:$K$7,2,0)) Filldown công thức từ D2 tới D8 chúng ta có kết quả là: Kết quảđã tạo rađược sự khác biệt trongđơn giá của tháng 4 với tháng 5 rồi. Như vậy với bất kỳ cách tổchức dữ liệu nào chúng ta cũngđều có thể tùy biến công thức cho phù hợpđểđưa rađược kết quả. Lưuý: Trước khi xây dựng công thức, chúng ta cần phải xácđịnh từng bước, chia nhỏ vấnđề cho rõ ràng. Sau khiđã xácđịnh rõ các hàm cần dùng, vị tríđặt các hàm và làm rõ nội dung từng hàm, chúng ta mới ghép lại vào 1 công thức. Như vậy dù công thức có dài, có phức tạp nhưng chúng ta vẫn có thể hiểu rõ và hoàn toàn hiểu, tự viết lạiđược Ví dụ 4: Kết hợp VLOOKUP và hàm IF để trả về giá trị TRUE/FALSE hoặc 1 và 0Ví dụ 4.1: Giả sử bạn có 1 danh sách hàng hoá trong kho và số lượng của các loại hàng hoá này, nếu bạn muốn có một phân tích đơn giản bằng việc điền vào dòng tương ứng với số lượng bằng 0 là hết hàng, điền vào dòng tương ứng với số lượng lớn hơn 0 là còn hàng thì chúng ta có thể viết hàm VLOOKUP kết hợp với hàm IF như sau: Kết hợp hàm VLOOKUP và hàm IF trong Excel Công thức trên tra cứu số lượng của sản phẩm Nho, sau đó so sánh kết quả này nếu lớn hơn 0 thì hàm IF sẽ trả về kết quả là Còn hàng, nếu không kết quả sẽ là Hết hàng Xem thêm:Sử dụnghàm vlookup có điều kiệntrong Excel Ví dụ 4.2: Trong tình hình thị trường có biến động, chúng ta sẽ cần có mức thiết lập tồn kho an toàn khác nhau tuỳ theo từng thời điểm, giả sử trong thời điểm đầu năm, mức tồn kho an toàn của chúng ta được lưu trong ô G1 là 15 đơn vị sản phẩm. Chúng ta muốn kiểm tra sản phẩm Táo của chúng ta có trạng thái tồn kho như thế nào, bạn có thể sử dụng công thức như trong hình minh hoạ. Kiểm tra tồn kho an toàn với VLOOKUP và hàm IF Ví dụ 4.3: Chúng ta có 2 danh sách: danh sách hàng hoá trong kho và danh sách hàng đủ tiêu chuẩn xuất khẩu, vậy làm sao để so sánh 2 danh sách này với nhau và đánh dấu mặt hàng nào trong kho đủ tiêu chuẩn xuất khẩu? Chúng ta có thể kết hợp hàm VLOOKUP với hàm IF và hàm ISNA như trong hình minh hoạ như sau Kết hợp hàm VLOOKUP, IF, ISNA Sử dụng kết hợp hàm VLOOKUP và hàm IF để tính toán theo điều kiệnVí dụ 4: Giả sử bạn quản lý 1 đội nhân viên bán hàng, cơ cấu hoa hồng cho nhân viên bán hàng của bạn rất đơn giản: nếu doanh thu đạt được > 200 thì hoa hồng là 10% của doanh thu, nếu doanh thu đạt được <= 200 thì hoa hồng là 5% của doanh thu đó. Bài toán này có thể được giải quyết bằng cách lồng ghép hàm IF và hàm VLOOKUP như trong hình dưới đây Lồng ghép hàm IF và VLOOKUP để tính hoa hồng Xử lý lỗi #NA khi VLOOKUP không tìm thấy giá trị trong bảngTrong trường hợp giá trị cần tìm kiếm không thể được tìm thấy khi sử dụng hàm VLOOKUP thì chúng ta sẽ nhận lại kết quả là lỗi #N/A. Để đưa ra một thông báo có ý nghĩa hơn cho người dùng, chúng ta có cách kết hợp hàm VLOOKUP và hàm IFNA thể xử lý lỗi #N/A bằng cách đưa ra giá trị thay thế như sau. =IFNA(value, value_if_na)
Cách kết hợp hàm vlookup và ifna * Kết luận Trên đây là 4 ví dụ thường gặp nhất trong việc kết hợp hàm IF với hàm VLOOKUP. Các bạn hãy ghi nhớ để sử dụng phù hợp trong từng hoàn cảnh. Để có thể sử dụng tốt việckế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 Xem thêm: CÁCH KẾT HỢP HÀM VLOOKUP VỚI CÂU LỆNH IF IF ISNA VLOOKUP · · · Bài viết liên quan Hàm IF nhiều điều kiện trong Excel Nếu tiết kiệm và đầu tư bây giờ, thì sau bao năm bạn có thể nghỉ hưu 4 cách tốt nhất để xử lý khi quên mật khẩu tài liệu Excel, Word Tuyển tập những bài viết về hàm Vlookup chi tiết, dễ hiểu CÁCH DÙNG HÀM LEN TRONG EXCEL ĐỂ ĐẾM SỐ KÝ TỰ TRONG MỘT Ô Cải thiện kỹ năng Excel Ngay Siêu ưu đãi mùa dịch Covid Khóa học liên quanKhóa học Excel từ cơ bản đến chuyên gia dành cho người đi làm Chi tiết khóa học Khóa học Trọn bộ Power Query nền tảng cho công việc báo cáo dữ liệu Chi tiết khóa học Hàm IF nhiều điều kiện trong Excel Hàm IF nhiều điều kiện trong Excel Nếu tiết kiệm và đầu tư bây giờ, thì sau bao năm bạn có thể nghỉ hưu Nếu tiết kiệm và đầu tư bây giờ, thì sau bao năm bạn có thể nghỉ hưu 4 cách tốt nhất để xử lý khi quên mật khẩu tài liệu Excel, Word 4 cách tốt nhất để xử lý khi quên mật khẩu tài liệu Excel, Word Tuyển tập những bài viết về hàm Vlookup chi tiết, dễ hiểu Tuyển tập những bài viết về hàm Vlookup chi tiết, dễ hiểu CÁCH DÙNG HÀM LEN TRONG EXCEL ĐỂ ĐẾM SỐ KÝ TỰ TRONG MỘT Ô CÁCH DÙNG HÀM LEN TRONG EXCEL ĐỂ ĐẾM SỐ KÝ TỰ TRONG MỘT Ô Cải thiện kỹ năng Excel Ngay Siêu ưu đãi mùa dịch Covid Cải thiện kỹ năng Excel Ngay Siêu ưu đãi mùa dịch CovidKhóa học Excel từ cơ bản đến chuyên gia dành cho người đi làmKhóa học Trọn bộ Power Query nền tảng cho công việc báo cáo dữ liệu Đăng ký học qua Email Hộ kinh doanh Học Excel Online. Khóa học Excel Khóa học VBA Khóa học SQL Khóa học Google Apps Script Khóa học Power BI YouTube EXCELVBAPOWER BISQLGoogle Apps ScriptGoogle SheetsPower QueryPythonPower Platform © Học Excel Online. All rights reserved. Video liên quan |
