Xem Nhiều 1/2023 #️ Hướng Dẫn Toàn Tập Về Hàm Vlookup Trong Excel Và Các Ví Dụ Nâng Cao Hay Gặp # Top 9 Trend | Trucbachconcert.com

Xem Nhiều 1/2023 # Hướng Dẫn Toàn Tập Về Hàm Vlookup Trong Excel Và Các Ví Dụ Nâng Cao Hay Gặp # Top 9 Trend

Cập nhật thông tin chi tiết về Hướng Dẫn Toàn Tập Về Hàm Vlookup Trong Excel Và Các Ví Dụ Nâng Cao Hay Gặp mới nhất trên website Trucbachconcert.com. 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.

Trong bài hướng dẫn về hàm VLOOKUP này, mình sẽ cố gắng dùng từ ngữ đơn giản để giải thích những điều cơ bản nhằm giúp quá trình học cho người mới bắt đầu trở nên dễ dàng nhất có thể. Chúng ta cũng sẽ khám phá vài công thức mẫu cho thấy cách dùng thông dụng nhất của hàm VLOOKUP trong Excel.

Vậy hàm VLOOKUP là gì? Để bắt đầu, nó là một hàm trong Excel. Nó dùng để làm gì? Nó tìm kiếm giá trị mà bạn định rõ và trả về giá trị tương ứng ở một cột khác. Nói một cách kỹ thuật hơn, hàm VLOOKUP tìm kiếm giá trị từ cột đầu tiên trong trong dải ô xác định, rồi trả về giá trị tương ứng trong cùng một hàng từ một ô khác.

Ở cách dùng thông dụng nhất, hàm VLOOKUP trong Excel tìm kiếm thông qua danh sách dữ liệu bạn tạo ra dựa trên ký hiệu nhận dạng đặc biệt rồi trả về cho bạn một mẩu thông tin tương ứng với ký hiệu nhận dạng đặc biệt đó.

Ký tự “V” trong hàm VLOOKUP là viết tắt của từ “vertical” (“thẳng đứng”). Ký tự đó được dùng để phân biệt hàm VLOOKUP và hàm HLOOKUP – đây là hàm tìm kiếm giá trị từ hàng trên cùng của mảng (H là viết tắt của từ “horizontal” (“nằm ngang”)).

Hàm VLOOKUP có sẵn trong tất cả phiên bản, gồm Excel 2013, Excel 2010, Excel 2007, Excel 2007, Excel XP và Excel 2000.

CÚ PHÁP HÀM VLOOKUP TRONG EXCEL

Cú pháp hàm VLOOKUP trong Excel có dạng như sau:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Như bạn thấy, hàm VLOOKUP trong Microsoft Excel có 4 thông số, hay câu lệnh. Ba thông số đầu tiên sẽ được trả về, thông số cuối cùng là tùy chọn.

lookup_value: giá trị tìm kiếm

Đây có thể hoặc là một giá trị (chữ số, ngày hay chuỗi ký tự) hoặc là một tham chiếu ô (tham chiếu ô chứa giá trị cần tìm), hoặc là một giá trị được trả về bởi một hàm Excel khác. Ví dụ:

Tìm kiếm chữ số: =VLOOKUP(40, A2:B15, 2) – công thức sẽ tìm kiếm số 40.

Tìm kiếm chuỗi ký tự: =VLLOKUP(“apples”, A2:B15, 2) – công thức sẽ tìm kiếm chữ “apples” (“táo”).

Tìm kiếm giá trị ở một ô khác: =VLOOKUP(C2, A2:B15, 2) – công thức sẽ tìm kiếm giá trị trong ô C2.

Lưu ý. Nếu giá trị trong hàm VLOOKUP là một chữ số và nó nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của mảng mà bạn đang tìm kiếm, thì hàm VLOOKUP sẽ trả về lỗi #N/A.

table_array: hai cột dữ liệu trở lên.

Hãy nhớ rằng, hàm VLOOKUP luôn luôn tìm kiếm giá trị từ cột đầu tiên của table_array. Table_array của bạn có thể chứa các giá trị khác nhau như chuỗi ký tự, ngày, chữ số, hay giá trị lôgic. Các giá trị không phân biệt dạng chữ, có nghĩa là ký tự viết hoa hay viết thường đều được xử lý như nhau.

Vì thế, công thức =VLOOKUP(40,A2:B15, 2) sẽ tìm kiếm số “40” từ ô A2 đến ô B15 bởi vì A là cột đầu tiên của table_array A2:B15. Hy vọng là mọi thứ đã trở nên dễ hiểu hơn. 🙂

col_index_num: số thứ tự cột trong table_array mà hàm VLOOKUP lấy giá trị ở hàng tương ứng để trả về.

Cột ở bên trái trong table_array xác định chính là 1, cột thứ hai là 2, cột thứ ba là 3, và vâng vâng.

À thì, giờ bạn có thể đọc hiểu toàn bộ công thức =VLOOKUP(40, A2:B15, 2). Công thức tìm kiếm số “40” từ ô A2 đến ô B15 và trả về giá trị tương ứng từ cột B (bởi vì B là cột thứ hai trong table_array xác định A2:B15).

range_lookup: xác định liệu bạn đang tìm kiếm sự phù hợp tuyệt đối (FALSE) hay sự phù hợp tương đối (TRUE hay được bỏ qua). Thông số cuối cùng tuy là tùy chọn nhưng rất quan trọng. Ở dưới bài hướng dẫn về hàm VLOOKUP này, tôi sẽ cung cấp vài ví dụ nhằm giải thích cách làm công thức trở nên phù hợp tuyệt đối hay tương đối một cách chính xác.

VÍ DỤ HÀM VLOOKUP TRONG EXCEL

Tôi hy vọng rằng bạn đã quen dần với hàm VLOOKUP. Bây giờ, hãy khám phá vài ví dụ sử dụng công thức VLOOKUP cho dữ liệu thực.

CÁCH DÙNG HÀM VLOOKUP TRONG EXCEL TỪ MỘT BẢNG TÍNH KHÁC

Trong thực tiễn, công thức VLOOKUP rất hiếm khi được dùng để tìm kiếm dữ liệu trong cùng một bảng tính. Thông thường, bạn sẽ phải tìm kiếm và kéo dữ liệu tương ứng về từ bảng tính khác.

Để dùng hàm VLOOKUP từ bảng tính Excel khác, bạn nên nhập tên bảng tính và một dấu cảm thán trong câu lệnh table_array trước dải ô, ví dụ =VLOOKUP(40, Sheet2!A2:B15, 2). Công thức này chỉ ra rằng dải ô tìm kiếm A2:B15 nằm ở Sheet2.

Tất nhiên, bạn không cần phải nhập tên bảng tính một cách thủ công. Chỉ cần bắt đầu gõ công thức và khi gõ đến câu lệnh table_array, hãy đổi sang bảng tính cần tìm và dùng chuột để chọn dải ô.

=VLOOKUP(“Product 1”, Prices!$A$2:$B$9, 2, FALSE)

Mẹo. Luôn luôn dùng tham chiếu ô tuyệt đối (có $) trong thông số table_array của công thức VLOOKUP là một ý tưởng tuyệt vời. Trong trường hợp này, dải ô cần tìm sẽ không thay đổi khi bạn sao chép công thức sang ô khác.

CÁCH DÙNG HÀM VLOOKUP TỪ MỘT SỔ LÀM VIỆC KHÁC

Để dùng hàm VLOOKUP giữa hai sổ làm việc Excel khác nhau, bạn nên đặt tên sổ làm việc trong dấu ngoặc vuông trước tên bảng tính.

=VLOOKUP(40, [Numbers.xlsx]Sheet2!A2:B15, 2)

Mở cả hai sổ làm việc. Bước này không bắt buộc, nhưng nó sẽ giúp việc lập công thức dễ dàng hơn bởi vì bạn sẽ không phải gõ tên sổ làm việc một cách thủ công. Bên cạnh đó, điều đó cũng sẽ bảo vệ công thức của bạn khỏi lỗi vô tình in sai.

Bắt đầu nhập công thức VLOOKUP, rồi đối với câu lệnh table_array, thì hãy đổi sang sổ làm việc khác rồi chọn dải ô cần tìm kiếm ở đó.

CÁCH SỬ DỤNG TÊN CHO DẢI Ô HAY BẢNG TRONG CÔNG THỨC VLOOKUP

Giả sử, nếu phải sử dụng cùng một dải ô cần tìm trong vài công thức VLOOKUP, thì bạn có thể tạo tên cho một dải ô rồi nhập trực tiếp tên của dải ô vào công thức thay vì phải nhập vùng cần tìm (câu lệnh table_array).

Để tạo tên cho dải ô, bạn chỉ cần chọn dải ô đó và gõ bất kỳ tên nào vào Name box (Hộp tên) – ở bên trái thanh Công thức.

=VLOOKUP(“Product 1”, Products, 2)

Hầu hết tên dải ô trong Excel đều được sử dụng cho toàn bộ sổ làm việc, nên bạn không cần định rõ tên của bảng tính trong câu lệnh table_array, dù cho dải ô bạn cần tìm nằm ở bảng tính khác. Nếu nó nằm ở sổ làm việc khác, thì bạn phải đặt tên sổ làm việc phía trước tên dải ô, ví dụ:

=VLOOKUP(“Product 1”, PriceList.xlsx!Products,2)

Những công thức như thế này này dễ hiểu hơn rất nhiều, đúng không? Bên cạnh đó, việc sử dụng tên cho dải ô có thể là một sự thay thế tuyệt vời cho tham chiếu ô tuyệt đối. Bởi vì tên dải ô không đổi khi sao chép công thức sang các ô khác, nên bạn có thể chắc rằng bạn sẽ luôn tìm đúng dải ô.

=VLOOKUP(“Product 1”, Table46[[Product]:[Price]],2)

hay thậm chí là có dạng như thế này: =VLOOKUP(“Product 1”, Table46,2)

VIỆC SỬ DỤNG KÝ TỰ ĐẠI DIỆN TRONG CÔNG THỨC VLOOKUP

Cũng như các công thức khác, bạn có thể sử dụng ký tự đại diện cho hàm VLOOKUP trong Excel:

Dấu chấm hỏi (?) để khớp với bất cứ ký tự riêng lẻ nào, và

Dấu sao (*) để khớp với bất kỳ dãy ký tự nào.

Việc sử dụng ký tự đại diện trong công thức VLOOKUP có thể rất hữu ích trong nhiều trường hợp:

Khi bạn không nhớ chính xác chuỗi ký tự bạn đang tìm kiếm.

Khi bạn muốn tìm một từ nào đó. Từ đó là một phần của nội dung ô. Hãy chú ý rằng hàm VLOOKUP tìm kiếm toàn bộ nội dung ô, như thể bạn đã chọn “Match the entire content” (“Khớp toàn bộ nội dung ô”) trong tương tác Tìm kiếm chuẩn của Excel.

Khi cột cần tìm có khoảng trống thụt đầu dòng hay cuối dòng. Nếu gặp trường hợp này, thì bạn có thể vắt óc cố tìm ra lý do tại sao công thức thông thường của bạn lại không được thực hiện.

VÍ DỤ 1. TÌM KIẾM CHUỖI KÝ TỰ BẮT ĐẦU HAY KẾT THÚC BẰNG CÁC KÝ TỰ NHẤT ĐỊNH

=VLOOKUP(“ack*”,$A$2:$C$11,1,FALSE)

=VLOOKUP(“ack*”,$A$2:$C$11,3,FALSE)

=VLOOKUP(“*man”,$A$2:$C$11,1,FALSE) – tìm tên kết thúc bằng “man”.

=VLOOKUP(“ad*son”,$A$2:$C$11,1,FALSE) – tìm tên bắt đầu bằng “ad” và kết thúc bằng “son”.

=VLOOKUP(“?????”,$A$2:$C$11,1,FALSE) – tìm họ có năm ký tự.

Lưu ý. Để công thức VLOOKUP có ký tự đại diện chạy chính xác, bạn phải luôn thêm FALSE như một thông số cuối cùng. Nếu dải ô cần tìm của bạn có hơn một mục nhập khớp với tiêu chuẩn đại diện, thì giá trị tìm thấy đầu tiên sẽ được trả về.

VÍ DỤ 2. CÔNG THỨC VLOOKUP CÓ KÝ TỰ ĐẠI DIỆN DỰA TRÊN GIÁ TRỊ Ô

Điều này có thể được giải quyết bằng cách dùng hàm VLOOKUP có dạng như sau:

=VLOOKUP(“*”&C1&”*”, $A$2:$B$12, 2, FALSE)

Công thức này tìm kiếm giá trị ở ô C1 thông qua một dải ô xác định và trả về giá trị tương ứng ở cột B. Xin hãy chú ý rằng chúng ta sử dụng dấu và (&) trước và sau một tham chiếu ô trong thông số đầu tiên nhằm nối chuỗi ký tự.

Như bạn có thể thấy trong ảnh chụp màn hình bên dưới, hàm VLOOKUP của tôi trả về “Jeremy Hill” bởi vì mã bản quyền của anh ấy có một nhóm ký tự nằm trong ô C1:

VIỆC SỬ DỤNG CÔNG THỨC VLOOKUP CÓ GIÁ TRỊ TÌM KIẾM CHÍNH XÁC VÀ GIÁ TRỊ TÌM KIẾM TƯƠNG ĐỐI

Và cuối cùng, hãy xem xét kỹ câu lệnh cuối cùng mà bạn nhập vào hàm VLOOKUP trong Excel – range_lookup. Vì đã được nói đến ở đầu bài hướng dẫn, câu lệnh này cực kỳ quan trọng bởi vì bạn có thể nhận được kết quả khác nhau tùy vào bạn nhập TRUE hay FALSE.

Đầu tiên, hãy xem “giá trị tìm kiếm chính xác” và “giá trị tìm kiếm tương đối” trong Microsoft Excel thật sự có nghĩa gì.

Nếu range_lookup được cài đặt là FALSE, thì công thức sẽ tìm chính xác giá trị, ví dụ, công thức sẽ tìm chính xác giá trị cần tìm khi bạn nhập giá trị đó làm thông số thứ nhất (lookup_value).

Nếu có hai hay nhiều hơn hai giá trị từ cột đầu tiên của table_array khớp với giá trị cần tìm, thì kết quả tìm thấy đầu tiên sẽ được trả về. Nếu không thể tìm thấy giá trị tìm kiếm chính xác, thì lỗi #N/A sẽ được trả về.

Ví dụ, nếu bạn sử dụng công thức =VLOOKUP(4, A2:B15,2,FALSE), nhưng dữ liệu của bạn không chứa giá trị 4 từ ô A2 đến cột A15, thì công thức sẽ trả về lỗi #N/A.

Nếu range_lookup được cài đặt thành TRUE hay được loại bỏ, thì công thức sẽ tìm sự phù hợp tương đối. Nói chính xác hơn, công thức VLOOKUP của bạn sẽ tìm kiếm sự phù hợp tuyệt đối đầu tiên và nếu sự phù hợp tuyệt đối không được tìm thấy, thì nó sẽ trả về sự phù hợp tương đối. Gía trị tìm kiếm tương đối là giá trị lớn nhất tiếp theo, chỉ nhỏ hơn lookup_value.

Lưu ý quan trọng! Nếu bạn nhập TRUE hay loại bỏ câu lệnh range_lookup, thì các giá trị ở cột đầu tiên của dải ô cần tìm phải được sắp xếp theo thứ tự tăng dần, đó là từ nhỏ nhất đến lớn nhất. Nếu không thì, hàm VLOOKUP trong Excel có thể sẽ không tìm được giá trị chính xác.

VÍ DỤ 1. CÁCH SỬ DỤNG HÀM VLOOKUP CÓ GIÁ TRỊ TÌM KIẾM CHÍNH XÁC TRONG EXCEL

Có thể bạn còn nhớ, để tìm kiếm sự phù hợp tuyệt đối, bạn phải đặt FALSE làm câu lệnh cuối cùng trong công thức VLOOKUP trong Excel.

Hãy chọn bảng “Animal speed” để làm ví dụ đầu tiên và tìm ra loài vật nào có thể chạy 80,5 km một giờ. Tôi tin chắc bạn sẽ chẳng gặp khó khăn gì với công thức này:

=VLOOKUP(50, $A$2:$B$15, 2, FALSE).

VÍ DỤ 2. SỬ DỤNG HÀM VLOOKUP CÓ GIÁ TRỊ CẦN TÌM TƯƠNG ĐỐI TRONG EXCEL

Khi sử dụng công thức VLOOKUP có giá trị tìm kiếm tương đối, đó là có range_lookup được cài đặt thành TRUE hay được bỏ đi, thì điều đầu tiên bạn cần làm đó là lọc cột đầu tiên trong dải ô cần tìm theo thứ tự tăng dần.

Điều này rất quan trọng bởi vì công thức VLOOKUP sẽ trả về giá trị lớn nhất tiếp theo cho giá trị cần tìm mà bạn đã định rõ rồi sẽ ngừng tìm kiếm. Nếu bạn không lọc dữ liệu một cách chính xác, thì cuối cùng bạn sẽ nhận được một kết quả lạ hay lỗi #N/A.

=VLOOKUP(69, $A$2:$B$15, 2, TRUE)

=VLOOKUP(69,$A$2:$B$15,2)

Như bạn có thể thấy, tôi đang cố tìm loài vật có tốc độ chạy gần 111 km một giờ nhất. Và đây là kết quả mà công thức VLOOKUP trả về:

Hy vọng rằng, những ví dụ này đã giúp việc sử dụng Excel dễ dàng hơn và hàm này không còn xa lạ với bạn nữa 🙂 Bây giờ, sẽ rất tuyệt để tổng kết những điều quan trọng mà bạn đã học nhằm giúp bạn nhớ các điểm mấu chốt tốt hơn.

HÀM VLOOKUP TRONG EXCEL – NHỮNG ĐIỀU CẦN NHỚ!

Hàm VLOOKUP trong Excel không thể tìm ở bên trái. Nó luôn luôn tìm giá trị nằm trong cột cận trái của dải ô cần tìm (table_array).

Trong công thức VLOOKUP, tất cả giá trị đều không phân biệt dạng chữ, có nghĩa là ký tự viết hoa và viết thường đều được xử lý như nhau.

Nếu giá trị cần tìm nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của dải ô cần tìm, thì hàm VLOOKUP sẽ trả về lỗi #N/A.

Nếu câu lệnh thứ ba (col_index_num) nhỏ hơn 1, thì công thức VLOOKUP sẽ trả về lỗi #VALUE!. Trong trường hợp, nó lớn hơn số cột trong dải ô cần tìm (table_array), thì công thức sẽ trả về lỗi #REF!.

Hãy sử dụng tham chiếu ô tuyệt đối trong câu lệnh table_array của công thức VLOOKUP để có đúng dải ô cần tìm khi xử lý công thức. Hãy cân nhắc việc sử dụng tên cho dải ô hay cho bảng trong Excel như một phương án thay thế.

Khi tìm kiếm sự phù hợp tương đối (range_lookup được cải đặt thành TRUE hay loại bỏ), hãy luôn lọc dữ liệu ở cột đầu tiên trong dải ô cần tìm theo thứ tự tăng dần.

Và cuối cùng, hãy nhớ tầm quan trọng của thông số cuối cùng. Sử dụng TRUE hay FALSE khi thích hợp và bạn sẽ không phải đau đầu nữa.

Để 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

Hàm If Nâng Cao, Cách Dùng Và Ví Dụ Về Hàm If Nâng Cao.

Hàm IF nâng cao là hàm giúp kiểm tra đối chiếu với nhiều nhiều điều kiện và trả về kết quả tương ứng. Bằng cách kết hợp nhiều hàm IF lồng nhau, hoặc kết hợp hàm IF với hàm AND, OR khiến cho hàm IF thông thường có thể kiểm tra đối chiếu với nhiều điều kiện.

Trong bài viết này, Học Excel Cơ Bản sẽ giúp bạn tìm hiểu về cú pháp và cách kết hợp hàm IF với những hàm khác như hàm AND, OR,… để kiểm tra nhiều điều kiện.

1.Chức năng của hàm IF nâng cao 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 nhiều điều kiện giúp ta tính toán, kiểm tra và đối chiếu nhiều điều kiện khác nhau và 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, các điều kiện xảy ra đồng thời bạn cần sử dụng thêm hàm AND trong biểu thức điều kiện. Cú pháp hàm AND là: AND(logical1, logical2,…)

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 nâng cao.

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: Hàm IF nâng cao.

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 nâng cao.

3.2. Hàm IF nâng cao kết hợp hàm AND.

Thông thường với những yêu cầu thực tế bạn cần thỏa mãn nhiều điều kiện cùng lúc. Chính vì vậy bạn cần kết hợp hàm AND để đảm bảo các yếu tố điều kiện đều được đảm bảo.

Cú pháp của hàm AND: AND(logical1, logical2,…)

Trong đó logical1 và logical2 là 2 mệnh đề logic.

Kết quả trả về của hàm AND:

TRUE: Khi tất cả các mệnh đề logic đều đúng.

FALSE: Khi có một mệnh đề logic bất kì trong hàm bị sai.

Chúng ta thường đặt hàm AND trong biểu thức logic của hàm IF để xét điều kiện cho hàm IF.

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, điều kiện xét tuyển là nếu học sinh đỗ phải đạt tổng điểm 3 môn lớn hơn hoặc bằng 18 điểm và không có môn nào đạt điểm 0.

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 ô 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 4: Hàm IF nâng cao.

Chú ý: Hàm AND chỉ chả về kết quả TRUE khi tất cả các biểu thực logic bên trong đều được thỏa mãn.

3.3. Hàm IF nâng cao 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 6: Hàm IF nâng cao.

3.4. Hàm IF nâng cao 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 7: Hàm IF nâng cao.

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 8: Hàm IF nâng cao.

3.5. Hàm IF lồng nhau.

=IF(C4<5,”Yếu”,IF(C4<6.5,”Trung Bình”,IF(C4<8,”Khá”,”Giỏi”)))

Với nhiều người, hàm IF lồng nhau có thể khó hiểu. Nhưng sẽ dễ hơn nếu bạn nhìn công thức hàm IF lồng nhau trên theo cách này:

= IF(C4<6.5,”Trung Bình”,

= IF(C4<8,”Khá”,”Giỏi”)))

Khi chúng ta lồng nhiều hàm IF, Excel sẽ ưu tiên hàm IF được viết trước, nếu hàm IF ở trước là đúng thì câu lệnh sẽ dừng những câu lệnh IF sau đó sẽ không được xét đến.

Sau khi nhập xong công thức cho ô D4, 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 10: Hàm IF nâng cao.

Từ ví dụ trên, có thể thấy điều kiện quan trọng nhất sẽ được viết đầu tiên. Vì Excel sẽ kiểm tra điều kiện theo thứ tự xuất hiện trong công thức, và ngay khi một điều kiện được đáp ứng, điều kiện sau đó sẽ không được đánh giá.

Lưu ý khi sử dụng hàm IF nâng cao 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ớ những điều cơ bản sau:

Trong Excel 2016-2007, bạn có thể kết hợp 64 điều kiện. Trong các phiên bản cũ hơn từ Excel 2003 về trước, bạn có thể dùng tối đa 7 điều kiện.

Luôn nghĩ tới thứ tự các điều kiện trong công thức hàm IF lồng nhau – nếu điều kiện đầu tiên đúng, những điều kiện sau sẽ không được kiểm tra.

Nếu công thức của bạn có nhiều hơn 5 chức năng hàm IF, các công thức sau đây có thể sẽ tối ưu hơn.

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

Hướng Dẫn Cách Dùng Hàm Vlookup Trong Excel Qua Các Ví Dụ Từ Cơ Bản Đến Nâng Cao

Chức năng của hàm VLOOKUP trong Excel

Hàm VLOOKUP trong Excel được sử dụng khi chúng ta cần tìm kiếm một giá trị tương ứng nào đó thông qua một giá trị dữ liệu từ một cột khác đã có sẵn trong bảng. Ví dụ: dùng hàm VLOOKUP để tham chiếu, phân loại học sinh thành các loại Giỏi, Khá, Trung bình, Yếu … thông qua giá trị điểm trung bình (đã có một bảng hoặc một vùng khác quy định giá trị điểm cho các phân loại Giỏi, Khá, Trung bình, …)

Việc phân loại này khá giống với khi ta sử dụng hàm IF. Tuy nhiên hàm IF trong trường hợp này có quá nhiều điều kiện để xét nên sẽ rất rắc rối. Việc áp dụng cách dùng VLOOKUP sẽ giúp bạn đơn giản hóa hơn rất nhiều.

Để học cách dùng hàm VLOOKUP hiệu quả, chúng ta sẽ bắt đầu với bước tìm hiểu công thức hàm VLOOKUP trong Excel như sau:

=VLOOKUP(Lookup_value, Table_array, Col_index_ num, Range_lookup)

Trong đó

Phân biệt hàm VLOOKUP và hàm 

HLOOKUP trong Excel

Cách sử dụng hàm VLOOKUP qua các ví dụ 

Ví dụ 1 về hàm VLOOKUP

Cách làm

Từ giá trị đối tượng, hàm sẽ nhập ô Thuế NK cho các mặt hàng dựa vào bảng QUY ĐỊNH THUẾ. Bạn chỉ cần nhập công thức ở ô G5 như sau:

=VLOOKUP(D5;$D$17:$F$20;2;1)*E5

Hàm VLOOKUP được sử dụng để tìm kiếm ra thuế nhập khẩu tại Bảng quy định thuế. Ở đây, vì bảng quy định thuế cho dưới dạng cột nên ta dùng hàm VLOOKUP trong Excel chứ không phải HLOOKUP. Cách dùng VLOOKUP cụ thể qua các tham số như sau:

D5: Giá trị là đối tượng cần tìm ở đây là các đối tượng từ 1,2,3,4 (Cột Đối tượng).

$D$17:$F$20: Bảng giới hạn dò tìm, chính là D17:F20 (Ấn F4 để Fix cố định giá trị để khi Copy công thức xuống các ô khác, giá trị này không thay đổi).

2: Thứ tự cột giá trị cần lấy.

1: Trường hợp này, ta lấy giá trị tương đối nên chọn là 1.

Ví dụ 2 về hàm VLOOKUP

Yêu cầu

1. Dựa vào MÃ HÀNG và dò tìm trong BẢNG THAM CHIẾU, điền TÊN HÀNG tương ứng.

2. Cột ĐƠN GIÁ thực hiện tương tự cột MÃ HÀNG

3. THÀNH TIỀN = SỐ LƯỢNG x ĐƠN GIÁ.

Hướng dẫn

Ta có, bảng tham chiếu được cho có dạng cột (cột MÃ HÀNG, cột TÊN HÀNG, cột ĐƠN GIÁ) nên ta thực hành theo cách dùng hàm VLOOKUP. Giá trị (từ ô B5:B10) dùng để dò tìm đối với cột đầu tiên của BẢNG THAM CHIẾU giống nhau nên giữ nguyên. Cột TÊN HÀNG là cột 2 trong BẢNG THAM CHIẾU. (Tương tự, cột ĐƠN GIÁ sẽ là cột 3).

Chúng ta sẽ sử dụng công thức hàm VLOOKUP trong Excel để điền cột TÊN HÀNG (C5) như sau:

=VLOOKUP(B5,$B$14:$D$16,2,1)

Trong đó, các tham số hàm VLOOKUP lần lượt là:

B5: Giá trị là đối tượng cần tìm ở đây là các đối tượng A, B hoặc C (Cột MÃ HÀNG).

$B$14:$D$16: Bảng giới hạn dò tìm, chính là B14:D16 (Ấn F4 để Fix cố định giá trị để khi Copy công thức xuống các ô khác, giá trị này không thay đổi).

2: Thứ tự cột giá trị cần lấy.

1: Trường hợp này, ta lấy giá trị tương đối nên chọn là 1.

Tại cột ĐƠN GIÁ, chúng ta áp dụng cách dùng VLOOKUP bằng công thức hàm như sau:

=VLOOKUP(B5,$B$14:$D$16,3,1)

Cuối cùng, tại Cột THÀNH TIỀN chúng ta không cần dùng đến hàm VLOOKUP hay bất kỳ hàm nào mà chỉ cần điền một công thức tính toán đơn giản:

=E5*D5

Kết luận

Đánh giá bài viết này

Các Ví Dụ Về Hàm Sum Trong Excel

Trong bài viết này Học Excel Online sẽ hướng dẫn các bạn cách tính tổng trong Excel bằng cách dùng tính năng AutoSum, và cách tạo công thức Sum để tính tổng các cột, hàng hoặc một dải ô đã được chọn. Ngoài ra, các bạn cũng sẽ học được cách tính tổng riêng những ô được hiển thị, tổng của các trang tính, đồng thời giúp bạn tìm hiểu thêm về những lý do khiến hàm Sum không hoạt động.

Cách tính tổng trong Excel dùng phép tính số học đơn giản

Nếu bạn cần tính nhanh tổng của một số ô, bạn có thể sử dụng Microsoft Excel như một chiếc máy tính mini. Chỉ cần dùng dấu (+) giống như trong phép toán số học thông thường. Ví dụ:

Tìm hiểu ngay: Địa chỉ học Excel tại Hà Nội

Cách sử dụng hàm Sum trong Excel

Excel SUM là một hàm toán học dùng để tính tổng. Cú pháp của hàm Sum như sau:

Sum (số thứ nhất, [số thứ 2] ,…) Đối số đầu tiên là bắt buộc, các cố khác là tùy chọn, bạn có thể áp dụng hàm Sum cho tối đa 225 số trong một công thức.

Trong công thức Sum của Excel, mỗi đối số có thể là một giá trị số dương hoặc số âm, một hàng hoặc một chuỗi các ô. Ví dụ như:

Hàm SUM trong Excel thực sự là lựa chọn hữu ích khi bạn cần cộng các giá trị nằm ở cách dải khác nhau hoặc tính tổng các giá trị số, các ô tham chiếu hay các dãy. Ví dụ: =SUM(A2:A4, A8:A9)

=SUM(A2:A6, A9, 10)

Ví dụ, bạn có thể kết hợp hàm SUM với đối số value_if_true thuộc hàm IF để cộng các giá trị trong cột B, C và D nếu tất cả các ô trong cùng hàng có cả ô chứa giá trị và ô trống:

Cách dùng AutoSum trong Excel:

Nếu bạn cần tính tổng một dãy các số hoặc tổng của vài hàng, cột liền kề thì có thể sử dụng công thức SUM được Microsoft Excel thiết kế sẵn.

Mẹo: Cách nhanh hơn để sử dụng AutoSum trong Excel là dùng phím tắt Alt + =. Bạn chỉ cần giữ phím Alt, nhấn phím Equal Sign, rồi Enter là hoàn thành việc chèn công thức Sum.

Ngoài tính tổng, bạn có thể sử dụng AutoSum để tự động nhập các hàm AVERAGE, COUNT, MAX, hay MIN.

Cách tính tổng một cột trong Excel

Để tính tổng các số liệu trong một cột cụ thể, bạn có thể sử dụng hàm SUM thông thường hoặc tính năng AutoSum. Ví dụ, để tính tổng các giá trị trong cột B, từ B2 đến B8, bạn nhập công thức SUM như sau:

Cách tính tổng toàn bộ cột với số hàng vô hạn không xác định được

Nếu cột mà bạn muốn tính tổng có số lượng dòng thay đổi (nghĩa là các ô mới có thể được thêm vào và những giá trị cõ sẵn có thể bị xóa đi), bạn có thể tính tổng toàn bộ cột bằng cách thêm tham chiếu cột, không xác định giới hạn trên và dưới. Ví dụ:

Tính tổng cột nhưng bỏ hàng tiêu đề hoặc một vài hàng đầu tiên

Kích thước bảng tính có giới hạn nhất định nên bạn có thể chỉ định giới hạn trên của công thức SUM dựa trên số hàng tối đa trong phiên bản Excel mà bạn dùng. Ví dụ: để tính tổng cột B mà không bao gồm hàng tiêu đề (loại trừ ô B1) bạn có thể sử dụng các công thức sau:

Trong Excel 2007, Excel 2010, Excel 2013 và Excel 2016:

= SUM (B2: B1048576)

Trong Excel 2003 và phiên bản thấp hơn:

= SUM (B2: B655366)

Cách tính tổng các hàng trong Excel

Tương tự như tính tổng một cột, bạn có thể tính tổng một hàng trong Excel bằng cách sử dụng hàm SUM, hoặc AutoSum để chèn công thức tính.

Ví dụ, để tính tổng các giá trị trong ô B2 đến D2, ta sử dụng công thức sau:

Cách tính tổng nhiều hàng trong Excel

= SUM (B2: D6) – tổng các giá trị từ hàng 2 đến hàng 6.

= SUM (B2: D3, B5: D6) – tổng các giá trị trong các hàng 2, 3, 5 và 6.

Cách tính tổng toàn bộ một hàng

Để tính tổng toàn bộ hàng với một số cột không xác định, ta cần cung cấp tham chiếu toàn bộ hàng cho công thức Excel Sum, ví dụ:

= SUM (2: 2) -SUM (A2: B2)

Sử dụng Excel Total Row để tính tổng dữ liệu trong một bảng:

Nếu dữ liệu của bạn được nằm trong một bảng tính của Excel, bạn có thể sử dụng tính năng Total Row để nhanh chóng tính tổng dữ liệu trong bảng và tổng số hiển thị trong hàng cuối cùng.

Một lợi thế lớn của việc sử dụng các bảng Excel là chúng tự động mở rộng để thêm được các hàng mới, do đó, bất kỳ dữ liệu mới nào bạn nhập vào trong một bảng sẽ được tự động thêm luôn vào công thức đã có.

Để chuyển đổi một dải ô bình thường thành một bảng, hãy nhấn chọn và ấn phím tắt Ctrl + T (hoặc nhấp vào Table trên tab Insert).

Cách thêm hàng total trong bảng Excel

Khi dữ liệu của bạn được sắp xếp trong một bảng, bạn có thể chèn thêm hàng total theo cách sau:

Khóa học lập trình VBA trong Excel

Cách tính tổng dữ liệu trong bảng

Khi tổng số hàng xuất hiện ở cuối bảng, Excel sẽ tự xác định cách tính toán dữ liệu trong bảng.

Nếu bạn muốn thực hiện một số phép tính khác, hãy chọn chức năng tương ứng từ danh sách thả xuống như Average, Count, Max, Min, …

Nếu hàng total tự động hiển thị tổng cho một cột mà bạn không cần dùng đến tổng đó thì mở danh sách thả xuống của cột đó và chọn None.

Chú thích. Khi sử dụng tính năng Excel Total Row để tính tổng một cột, tổng số Excel chỉ có giá trị trong hàng hiển thị bằng cách chèn hàm SUBTOTAL với đối số đầu tiên là 109.

Cách tính tổng các ô được (có thể nhìn thấy được) trong Excel

Đôi khi, để việc phân tích dữ liệu hiệu quả hơn, bạn cần phải lọc hoặc ẩn một số dữ liệu trong bảng tính. Một công thức tổng hợp thông thường sẽ không hoạt động trong trường hợp này bởi vì hàm Excel SUM sẽ cộng tất cả các giá trị trong phạm vi được chỉ định bao gồm các hàng ẩn (đã lọc ra).

Nếu bạn muốn tính tổng các ô hiển thị trong một danh sách được lọc, cách nhanh nhất là đưa chúng vào một bảng Excel, và sau đó bật tính năng Excel Total Row. Như đã trình bày trong ví dụ trước, chọn Sum ở hàng total của bảng rồi chèn chức năng SUBTOTAL bỏ qua các ô ẩn.

Một cách khác để tính tổng các ô đã lọc trong Excel là áp dụng AutoFilter cho dữ liệu của bạn theo cách thủ công bằng cách nhấp vào nút Filter trên tab Data. Và sau đó, tự viết một công thức Subtotal.

Hàm SUBTOTAL có cú pháp sau:

SUBTOTAL (function_num, ref1, [ref2], …) Trong đó, Function_num – một số từ 1 đến 11 hoặc từ 101 đến 111 dùng để xác định chức năng sẽ được dùng cho việc tính tổng phụ.

Bạn có thể tìm thấy danh sách đầy đủ các chức năng trên chúng tôi Bây giờ, chúng ta chỉ quan tâm đến chức năng SUM, được định nghĩa bởi các số 9 và 109. Cả hai số này loại trừ các hàng được lọc ra. Sự khác biệt là 9 bao gồm các ô ẩn thủ công (tức là bạn chuột phải rồi chọn Hide), trong khi đó 109 không bao gồm các ô đó.

Vì vậy, nếu bạn muốn tính tổng cho riêng những hàng được hiển thị, không tính những hàng đã bị ẩn thì có thể dụng 109 làm đối số cho công thức Subtotal của bạn.

Ref1, Ref2, … – ô hoặc dãy mà bạn muốn tính tổng phụ. Đối số Ref đầu tiên là bắt buộc, những đối số tiếp theo (lên đến 254) là tùy chọn. Trong ví dụ này, ta sẽ tính tổng các ô có thể nhìn thấy trong khoảng B2: B14 bằng cách sử dụng công thức sau:

Cách sử dụng running total (tính tổng lũy tích) trong Excel

Để tính tổng lũy tích trong Excel, bạn viết công thức SUM như bình thường và sử dụng thông minh các tham chiếu của các ô một các tuyệt đối và tương đối.

Ví dụ, muốn hiển thị sô lũy tích của các số trong cột B, ta nhập công thức sau và ô B2 rồi sao chép sang các ô khác:

Cách tính tổng cho nhiều trang tính trên cùng bảng tính

Nếu bạn có một vài bảng tính với bố cũng và dữ liệu tương tự nhau, bạn có thể cộng tổng các dữ liệu trong cùng một ô hoặc dải ô ở các trang tính khác nhau với cùng một công thức SUM.

Chúng ta có một công thức gọi là tham chiếu 3-D:

=SUM(Jan:Apr!B6)

=SUM(Jan:Apr!B2:B5)

Tính tổng có điều kiện trong Excel

Nếu công việc của bạn yêu cầu tính tổng các ô đáp ứng điều kiện nhất định nào đó thì bạn có thể sử dụng hàm SUMIF hoặc SUMIFS tương ứng.

Ví dụ: công thức SUMIF sau chỉ cộng tổng các giá trị trong cột B có trạng thái “Completed” (đã hoàn thành) trong cột C, ta có:

Excel SUM không hoạt động – lý do và giải pháp

Khi bạn tính tổng của một vài giá trị hoặc một số cột trong bảng tính Excel nhưng công thức SUM đơn giản không hoạt động thì có thể là do những lý do sau đây:

1. #Name error xuất hiện thay vì kết quả mong đợi

Đó là lỗi dễ sửa chữa nhất. 99 trên 100 trường hợp, lỗi #Name error chỉ ra rằng hàm SUM bị sai chính tả.

2. Một số con số không được cộng vào tổng chung

Một lý do phổ biến khác khiến một công thức Sum (hoặc Excel AutoSum) không hoạt động là các con số được định dạng dưới dạng các giá trị văn bản. Thoạt nhìn, chúng trông giống như các con số bình thường, nhưng Microsoft Excel nhận dạng chúng như chuỗi văn bản nên không đưa chúng vào tính toán.

3. Hàm Excel SUM trả về kết quả bằng 0

4. Công thức Excel Sum trả về một con số cao hơn dự kiến

Nếu công thức Sum của bạn sẽ trả về một số lớn hơn dự kiến, bạn cần nhớ rằng hàm SUM trong Excel sẽ thêm cả các ô ẩn nà ta không nhìn thấy được. Trong trường hợp này, bạn có thể sử dụng chức năng Subtotal để thay thế công thức SUM.

5. Công thức Excel SUM không cập nhật

Khi một công thức SUM trong Excel tiếp tục hiển thị tổng số cũ ngay cả sau khi bạn đã cập nhật các giá trị cần tính tổng mới, nhiều khả năng Calculation Mode được đặt thành Manual. Để khắc phục sự cố này, hãy chuyển đến tab Formulas, nhấp vào mũi tên thả xuống bên cạnh Calculate Options, rồi nhấn Automatic.

Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các công cụ khác của Excel.

Một số hàm cơ bản thường gặp như:

SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiện

COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiện

Các hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…

Các hàm dò tìm tham chiếu Index+Match, hàm SUMPRODUCT…

Một số công cụ hay sử dụng như:

Định dạng theo điều kiện với Conditional formatting

Thiết lập điều kiện nhập dữ liệu với Data Validation

Cách đặt Name và sử dụng Name trong công thức

Lập báo cáo với Pivot Table…

Rất nhiều kiến thức phải không nào? 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 từ cơ bản đến chuyên gia dành cho người đi làm

Bạn đang xem bài viết Hướng Dẫn Toàn Tập Về Hàm Vlookup Trong Excel Và Các Ví Dụ Nâng Cao Hay Gặp trên website Trucbachconcert.com. 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. Chúc bạn một ngày tốt lành!