Cập nhật nội dung chi tiết về Sử Dụng Hàm If Kết Hợp Với Hàm Dò Tìm Vlookup, Hlookup mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.
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ách Sử Dụng Kết Hợp Hàm Vlookup Với Hàm Left
Việc kết hợp sử dụng các hàm với nhau trong Excel sẽ khiến cho công việc tính toán của bạn trở nên thuận lợi và tăng độ chính xác của phép tính lên rất nhiều. Vlookup là một hàm tra cứu dựa trên điều kiện cụ thể, hàm Left thì lại là một hàng lấy ký tự ở phía bên trái của một chuỗi ký tự. Khi ta kết hợp hai hàm Vlookup và Left này với nhau sẽ giúp cho hàm Vlookup thực hiện tra cứu một cách chính xác, nhanh chóng hơn.
HƯỚNG DẪN CÁCH SỬ DỤNG KẾT HỢP HÀM VLOOKUP VỚI HÀM LEFT Cú pháp và cách sử dụng của hàm Vlookup và hàm Left
Hàm Vlookup: Là hàm tra cứu và trả về kết quả theo hàng dọc.
– Cú pháp: =Vlookup(lookup_value, table_array, col_index_num, [range_lookup]
Trong đó:
+ lookup_value: Giá trị dùng để dò tìm
+ table_array: Vùng dữ liệu tra cứu
+ col_index_num: Thứ tự của cột cần lấy dữ liệu trên bảng giá trị dò.
+ range_lookup: Phạm vi tìm kiếm, TRUE tương đương với 1 (dò tìm tương đối), FALSE tương đương với 0 (dò tìm tuyệt đối).
Lưu ý: Đối với giá trị lookup_value ấn F4 3 lần, đối với giá trị table_array ấn F4 1 lần.
Ý nghĩa của việc sử dụng F4
– F4 (1 lần): để có giá trị tuyệt đối. Tuyệt đối được hiểu là cố định cột và cố định dòng ⇒ $cột$dòng
Ví dụ: $A$8 ⇒ cố định cột A và cố định dòng 8
– F4 (2 lần): để có giá trị tương đối cột và tuyệt đối dòng – Được hiểu là cố định dòng , không cố định cột ⇒ cột$dòng
Ví dụ: A$8 ⇒ cố định dòng 8, không cố định cột A
– F4 (3lần): để có giá trị tương đối dòng và tuyệt đối cột – Được hiểu là cố định cột, không cố định dòng ⇒ $cộtdòng
Hàm Left: Là hàm dùng để cắt lấy các ký tự bên trái của một chuỗi ký tự
+ Text: chuỗi ký tự cần cắt
+ n: số ký tự muốn cắt
Chúng ta có một bảng dữ liệu như hình dưới
Chúng ta sẽ sử dụng hàm LEFT để lấy Mã ngành từ cột Lớp.
Bước 1: Điền vào ô C6 công thức: =LEFT(B6,2). Ý nghĩa của công thức trên là cắt 2 ký tự ở ô B6
Bước 2: Sau đó ấn Enter. Kết quả hiện ra là 2 ký tự CN đã được cắt ra từ chuỗi ký tự CNTT1.
Bước 3: Các bạn kéo từ ô C6 xuống để các ô bên dưới tự động điền công thức và cho ra kết quả tương ứng.
Bước 4: Các bạn điền vào ô D6 công thức: =VLOOKUP($C6,$G$6:$H$10,2,0). $C6 là Giá trị dùng để tìm kiếm, ,$G$6:$H$10 là Vùng dữ liệu tra cứu, 2 là thứ tự cột cần lấy giá trị trong Vùng dữ liệu tra cứu, 0 là phạm vi tìm kiếm mang tính tuyệt đối.
Bước 5: Sau đó ấn Enter. Kết quả hiện ra là Tên ngành tương ứng với Mã Ngành. Các bạn lại kéo xuống để các ô bên dưới tự động điền công thức và cho ra kết quả tương ứng
Bước 6: Các bạn điền vào ô E6 công thức: =VLOOKUP(LEFT(B6,2),$G$6:$H$10,2,0). Chúng ta thay giá trị dùng để tìm kiếm từ $C6 thành LEFT(B6,2).
Bước 7: Sau đó ấn Enter và làm tương tự như bước 5.
Sửa Lỗi #N/A Trong Các Hàm Dò Tìm Vlookup Và Hlookup
Có 2 hàm dò tìm thường dùng là (dò theo cột dọc) và (dò theo hàng ngang).
Công thức tổng quát: = VLOOKUP(Lookup_value, Table_array, Col_index_ num, Range_lookup)
Giải thích:
Lookup_value: Giá trị cần dò tìm.
Table_array: Bảng giới hạn để dò tìm, bạn cần F4 để Fix cố định giá trị cho mục đích copy công thức tự động.
Col_index_num: Số thứ tự của cột lấy dữ liệu trong bảng cần dò tìm.
Range_lookup: Là giá trị Logic (TRUE=1, FALSE=0) quyết định so chính xác hay so tương đổi với bảng giới hạn. Nếu Range_lookup = 1 (TRUE): So tương đối.
Nếu Range_lookup = 0 (FALSE): So chính xác. Nếu bỏ qua đối này thì Excel hiểu là Range_lookup = 1
Tổng quan về lỗi #N/A.
N/A trong tiếng anh được viết tắt là Not Available = Không tồn tại, không có sẵn
Do đó lỗi này được hiểu là trong công thức bạn sử dụng có chứa nội dung không có sẵn trong vùng dữ liệu, dẫn tới không thể tính toán, hoàn thành công thức được.
Lỗi này thường xảy ra khi sử dụng các hàm dò tìm, tham chiếu. Đối tượng cần dò tìm, tham chiếu không có sẵn trong vùng cần tra cứu nên báo lỗi #N/A.
Đối với các hàm dò tìm như hoặc thì giá trị cần tra cứu phải nằm ở cột đầu tiên tính từ bên trái đối với VLOOKUP hoặc hàng đầu tiên tính từ trên xuống đối với HLOOKUP.
Ví dụ: Bài toán yêu cầu điền vào cột Thuế dựa vào bảng thuế của từng loại sản phẩm bên dưới. Nếu ta gõ công thức là: = VLOOKUP(B2,$B$11:$D$15,3,0) tức là dò tìm giá trị tra cứu ở ô B2 (ở đây là điện thoại) trong bảng tra cứu B11:D15. Tuy nhiên trong bảng B11:D15 thì cột đầu tiên là là cột STT do đó sẽ không thể dò tìm thấy giá trị cần tra cứu như ô B2 là “điện thoại”. Kết quả trả về là lỗi #N/A. Tương tự với các ô phía dưới.
Lỗi này tương tự như lỗi phía trên. Khi hàm dò tìm không thể tìm thấy giá trị cần tra cứu thì sẽ trả về lỗi #N/A.
Ví dụ: như ví dụ trên nhưng dò tìm giá trị “bao da” tương ứng với ô B9 mà trong bảng dò tìm phía dưới lại không có “bao da” do đó kết quả sẽ trả về là #N/A.
Nếu bạn chọn đối số [ange_lookup] là 1 tức là TRUE, khi đó nếu giá trị dò tìm nhỏ hơn giá trị nhỏ nhất trong bảng dò tìm thì kết quả sẽ trả về là #N/A.
Ví dụ: giá trị cần dò tìm ở ô A9 là 10 trong khi trong bảng dò tìm C11:D15 có giá trị nhỏ nhất ở cột cần dò tìm là 11. Như vậy kết quả trả về là #N/A.
Sử Dụng Hàm Vlookup Kết Hợp Hàm Sumif, Sum Trong Excel
Hai hàm SUMIF và VLOOKUP thì không còn xa lạ gì với người dùng Excel, nhất là 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 . Tuy nhiên với những bạn mới làm quen với Excel thì chắc cũng chưa biết việc kết hợp hai hàm trên sẽ là công cụ tuyệt vời đối với công việc của bạn. Vậy bạn đã biết cách sử dụng hàm vlookup kết hợp hàm sumif hay chưa ?
Bài viết sau đây sẽ cho bạn hiểu rõ các ví dụ công thức hiểu cách các hàm này hoạt động và cách sử dụng chúng cho dữ liệu thực tế. Hãy lưu ý rằng, đây là các ví dụ nâng cao – điều này có nghĩa là bạn đã quen thuộc với các quy tắc và cú pháp cơ bản của hàm VLOOKUP. Nếu không, bạn chắc chắn phải xem qua phần đầu tiên của bài hướng dẫn về hàm VLOOKUP cho người mới bắt đầu.
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
1.2 CÁCH SỬ DỤNG HÀM SUM, SUMIF TRONG EXCEL
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 đó
Cú pháp:
=SUM(num1,num2, ... num_n)Giống với hàm SUM, hàm SUMIF cũng là hàm tính tổng tuy nhiên trong một số trường hợp bạn bỏ qua một vài giá trị có trong dãy, lúc này hàm Sumif được sử dụng thay thế cho Sum. Với điều kiện được thêm vào dãy, hàm Sumif cho phép người sử dụng loại bỏ, bỏ qua các giá trị không thảo mãn điều kiện và tính tổng các giá trị thỏa mãn còn lại.
Cú pháp:
=SUMIF(Range,Criteria,Sum_range)
Range: Là địa chỉ dãy ô chứa dữ liệu.
Là giá trị điều kiện (có thể ở dạng biểu thức số, biểu thức kí tự, biểu thức logic) được đặt trong dấu nháy kép cùng kiểu dữ liệu với Range.
Sum_range: Là địa chỉ dãy ô cần tính tổng cần tính tổng.
2. HÀM VLOOKUP KẾT HỢP HÀM SUMIF, SUM
2.1 TÍNH TỔNG CỦA CÁC GIÁ TRỊ KẾT HỢP HÀM SUM – HÀM VLOOKUP TRONG EXCEL
Giả sử, bạn có danh sách các sản phẩm có đính doanh số trong vài tháng, một cột là một tháng.
2.2 TÍNH TỔNG CỦA CÁC GIÁ TRỊ CÓ ĐIỀU KIỆN KẾT HỢP HÀM SUMIF – HÀM VLOOKUP
Hàm SUMIF trong Excel thì tương tự với hàm SUM về mặt tính tổng các giá trị. Điểm khác biệt chính là hàm SUMIF tính tổng chỉ các giá trị đáp ứng tiêu chuẩn mà bạn đã định rõ.
Bảng Table 2 chứa nhiều mục có cùng số ID theo thứ tự ngẫu nhiên.
Bạn không thể thêm cột “Tên người bán” vào bảng Table 2.
CÔNG THỨC:
=SUMIF(E:E,VLOOKUP(I2,B4:C10,2,FALSE),F:F)
SumiF và Vlookup là tên hàm tính tổng và hàm tìm kiếm theo điều kiện.
E:E là vùng được lựa chọn để chứa các ô điều kiện
I2 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.
B2:C10 là thứ tự cột cần lấy dự liệu để dò gì cho giá trị I2 ở 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ã ID ở 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.
Chúng ta sẽ cần đổi cột đó về định dạng Currency là Tiền tệ, 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.
Ví dụ tôi sẽ nhập vào ô I4 Tên người bán: Emily với mã số ID là 01, kết quả doanh thu cần tìm kiếm vẫn cho ra kết quả chính xác.
Hàm tính toán SUMIF, hàm VLOOKUP tìm kiếm dữ liệu, 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. Và việc kết hợp 2 hàm lại sẽ giúp người dùng có thể tìm kiếm dữ liệu nhanh hơn, mà không cần tính toán thủ công cho dù đổi dữ liệu tìm kiếm đi chăng nữa. Ngoài ra còn rất nhiều hàm nâng cao khác như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Công cụ như Data validation, Pivot table, Power Query…
VIDEO HƯỚNG DẪN SỬ DỤNG HÀM SUM, HÀM SUMIF, HÀM SUMIFS:
Bạn đang đọc nội dung bài viết Sử Dụng Hàm If Kết Hợp Với Hàm Dò Tìm Vlookup, Hlookup trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết 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. Chúc bạn một ngày tốt lành!