Bạn đang xem bài viết Hàm Vlookup Với Một Số Ví Dụ Cơ Bản Và Nâng Cao 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.
Hàm VLOOKUP với một số ví dụ cơ bản và nâng cao trong ExcelTrong Excel, hàm VLOOKUP là một hàm mạnh mẽ đối với hầu hết người dùng Excel, được sử dụng để tìm kiếm giá trị ở ngoài cùng bên trái của phạm vi dữ liệu và trả về giá trị khớp trong cùng một hàng từ cột bạn đã chỉ định như ảnh chụp màn hình bên dưới . Hướng dẫn này nói về cách sử dụng hàm VLOOKUP với một số ví dụ cơ bản và nâng cao trong Excel.
Giới thiệu hàm VLOOKUP – Cú pháp và Đối sốCú pháp của hàm VLOOKUP: =VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) đối số:
Lookup_value: Giá trị mà bạn muốn tìm kiếm. Nó phải nằm trong cột đầu tiên của phạm vi table_array.
Table_array: Phạm vi dữ liệu hoặc bảng nơi định vị cột giá trị tra cứu và cột giá trị kết quả.
Col_index_num: Số cột mà từ đó giá trị phù hợp sẽ được trả về. Nó bắt đầu bằng 1 từ cột ngoài cùng bên trái trong mảng bảng.
Range_lookup: Giá trị logic xác định xem hàm VLOOKUP này sẽ trả về kết quả khớp chính xác hay kết quả gần đúng.
Đối sánh gần đúng – XNUMX – 1 / ĐÚNG: Nếu không tìm thấy kết hợp chính xác, công thức sẽ tìm kiếm kết hợp gần nhất – giá trị lớn nhất nhỏ hơn giá trị tra cứu. Trong trường hợp này, bạn nên sắp xếp cột tra cứu theo thứ tự tăng dần.= VLOOKUP (lookup_value, table_array, col_index, TRUE)= VLOOKUP (lookup_value, table_array, col_index, 1)
Kết hợp chuẩn xác – XNUMX – 0 / SAI: Điều này được sử dụng để tìm kiếm một giá trị chính xác bằng giá trị tra cứu. Nếu không tìm thấy kết quả khớp chính xác, giá trị lỗi # N / A sẽ được trả về.= VLOOKUP (lookup_value, table_array, col_index, FALSE)= VLOOKUP (lookup_value, table_array, col_index, 0)
Ghi chú:
1. Hàm Vlookup chỉ tìm kiếm giá trị từ trái sang phải.
2. Nếu có nhiều giá trị khớp dựa trên giá trị tra cứu, thì chỉ giá trị khớp đầu tiên sẽ được trả về bằng cách sử dụng hàm Vlookup.
3. Nó sẽ trả về giá trị lỗi # N / A nếu không tìm thấy giá trị tra cứu.
Các ví dụ cơ bản về VLOOKUP 1. So khớp chính xác Vlookup và so khớp gần đúng VlookupThông thường, nếu bạn đang tìm kiếm kết hợp chính xác với hàm Vlookup, bạn chỉ cần sử dụng FALSE trong đối số cuối cùng.
Ví dụ, để nhận được điểm Toán tương ứng dựa trên số ID cụ thể, vui lòng thực hiện như sau:
=VLOOKUP(F2,$A$2:$D$7,3,FALSE)
2. Và sau đó, kéo chốt điền xuống các ô bạn muốn điền vào công thức này và bạn sẽ nhận được kết quả như mong muốn. Xem ảnh chụp màn hình:
Ghi chú:
1. Trong công thức trên, F2 là giá trị mà bạn muốn trả về giá trị phù hợp của nó, A2: D7 là mảng bảng, số 3 là số cột mà giá trị phù hợp của bạn được trả về và KHÔNG ĐÚNG đề cập đến sự phù hợp chính xác.
2. Nếu giá trị tiêu chí của bạn không được tìm thấy trong phạm vi dữ liệu, giá trị lỗi # N / A sẽ được hiển thị.
Đối sánh gần đúng hữu ích cho việc tìm kiếm giá trị giữa các phạm vi dữ liệu. Nếu không tìm thấy kết quả khớp chính xác, thì Vlookup gần đúng sẽ trả về giá trị lớn nhất nhỏ hơn giá trị tra cứu.
Ví dụ: nếu bạn có dữ liệu phạm vi sau đây, các đơn hàng được chỉ định không có trong cột Đơn hàng, làm thế nào để nhận được Giảm giá gần nhất của nó trong cột B?
1. Nhập công thức sau vào một ô mà bạn muốn đặt kết quả:
=VLOOKUP(D2,$A$2:$B$9,2,TRUE)
2. Sau đó, kéo chốt điền xuống các ô để áp dụng công thức này và bạn sẽ nhận được các kết quả phù hợp gần đúng dựa trên các giá trị đã cho, xem ảnh chụp màn hình:
Ghi chú:
1. Trong công thức trên, D2 là giá trị mà bạn muốn trả về thông tin tương đối của nó, A2: B9 là phạm vi dữ liệu, số 2 cho biết số cột mà giá trị phù hợp của bạn được trả lại và TRUE đề cập đến kết quả phù hợp gần đúng.
2. Đối sánh gần đúng sẽ trả về giá trị lớn nhất nhỏ hơn giá trị tra cứu cụ thể của bạn.
3. Để sử dụng hàm Vlookup để nhận giá trị khớp gần đúng, bạn phải sắp xếp cột ngoài cùng bên trái của dải dữ liệu theo thứ tự tăng dần, nếu không sẽ trả về kết quả sai.
2. Làm một Vlookup phân biệt chữ hoa chữ thường trong ExcelTheo mặc định, hàm Vlookup thực hiện tra cứu không phân biệt chữ hoa chữ thường có nghĩa là nó xử lý các ký tự viết thường và viết hoa là giống hệt nhau. Đôi khi, bạn có thể cần phải thực hiện tra cứu phân biệt chữ hoa chữ thường trong Excel, các hàm Chỉ mục, Khớp và Chính xác hoặc các hàm Tra cứu và Chính xác có thể giúp bạn.
Ví dụ: tôi có dải dữ liệu sau mà cột ID chứa chuỗi văn bản với chữ hoa hoặc chữ thường, bây giờ, tôi muốn trả về điểm Toán tương ứng của số ID đã cho.
=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0)) Ghi chú:
1. Trong công thức trên, A2: A10 là cột chứa các giá trị cụ thể mà bạn muốn tra cứu, F2 là giá trị tra cứu, C2: C10 là cột nơi kết quả sẽ được trả về.
2. Nếu tìm thấy nhiều kết quả phù hợp, công thức này sẽ luôn trả về kết quả phù hợp đầu tiên.
=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)
2. Sau đó, kéo chốt điền xuống các ô mà bạn muốn sao chép công thức này và bạn sẽ nhận được các giá trị phù hợp có phân biệt chữ hoa chữ thường như ảnh chụp màn hình bên dưới:
Ghi chú:
1. Trong công thức trên, A2: A10 là cột chứa các giá trị cụ thể mà bạn muốn tra cứu, F2 là giá trị tra cứu, C2: C10 là cột nơi kết quả sẽ được trả về.
2. Nếu tìm thấy nhiều kết quả phù hợp, công thức này sẽ luôn trả về kết quả phù hợp cuối cùng.
3. Giá trị Vlookup từ phải sang trái trong ExcelNhấp để biết chi tiết từng bước về nhiệm vụ này…
4. Vlookup giá trị khớp thứ hai, thứ n hoặc cuối cùng trong ExcelThông thường, nếu có nhiều giá trị khớp được tìm thấy khi sử dụng hàm Vlookup, chỉ bản ghi khớp đầu tiên sẽ được trả về. Trong phần này, tôi sẽ nói về cách lấy giá trị khớp thứ hai, thứ n hoặc cuối cùng với hàm Vlookup.
Giả sử bạn có một danh sách tên trong cột A, khóa đào tạo mà họ đã mua trong cột B và bây giờ, bạn đang tìm kiếm khóa đào tạo thứ 2 hoặc thứ n mà khách hàng đã mua. Xem ảnh chụp màn hình:
1. Để nhận giá trị khớp thứ hai hoặc thứ n dựa trên tiêu chí đã cho, vui lòng áp dụng công thức mảng sau vào ô trống:
=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2)) Lưu ý:
Trong công thức này, A2: A14 là phạm vi có tất cả các giá trị để tra cứu, B2: B14 là phạm vi của các giá trị phù hợp mà bạn muốn trả về, E2 là giá trị tra cứu và số cuối cùng 2 cho biết giá trị khớp thứ hai mà bạn muốn lấy, nếu bạn muốn muốn trả về giá trị khớp thứ ba, bạn chỉ cần thay đổi nó thành 3 khi bạn cần.
Nếu bạn muốn vlookup và trả về giá trị khớp cuối cùng như hình ảnh chụp màn hình bên dưới, điều này Vlookup và trả lại giá trị phù hợp cuối cùng hướng dẫn có thể giúp bạn nhận được giá trị phù hợp cuối cùng trong chi tiết.
5. Vlookup so khớp các giá trị giữa hai giá trị hoặc ngày nhất định1. Đầu tiên, bảng gốc của bạn phải là một dải dữ liệu được sắp xếp. Và sau đó, sao chép hoặc nhập công thức sau vào một ô trống:
Ghi chú:
1. Trong công thức trên, A2: A6 là phạm vi các giá trị nhỏ hơn và B2: B6 là phạm vi các số lớn hơn trong phạm vi dữ liệu của bạn, E2 là giá trị đã cho mà bạn muốn nhận giá trị tương ứng của nó, C2: C6 là dữ liệu cột mà bạn muốn trích xuất.
Nếu bạn đang đau đầu với công thức trên, thì đây, tôi sẽ giới thiệu một công cụ dễ dàng – Kutools cho Excel, Với khả NHÌN giữa hai giá trị tính năng, bạn có thể trả lại mục tương ứng dựa trên giá trị hoặc ngày cụ thể giữa hai giá trị hoặc ngày tháng mà không cần nhớ bất kỳ công thức nào. Nhấp để tải xuống Kutools cho Excel ngay bây giờ!
6. Sử dụng các ký tự đại diện cho các kết quả phù hợp từng phần trong hàm VlookupTrong Excel, các ký tự đại diện có thể được sử dụng trong hàm Vlookup, hàm này giúp thực hiện đối sánh một phần giá trị tra cứu. Ví dụ: bạn có thể sử dụng Vlookup để trả về giá trị phù hợp từ một bảng dựa trên một phần của giá trị tra cứu.
Giả sử, tôi có một loạt dữ liệu như ảnh chụp màn hình bên dưới, bây giờ, tôi muốn trích xuất điểm số dựa trên tên (không phải tên đầy đủ). Làm cách nào để giải quyết công việc này trong Excel?
1. Chức năng Vlookup bình thường không hoạt động chính xác, bạn cần kết hợp văn bản hoặc tham chiếu ô với ký tự đại diện, vui lòng sao chép hoặc nhập công thức sau vào ô trống:
Ghi chú:
1. Trong công thức trên, E2 & “*” là giá trị tra cứu, giá trị trong E2 và * ký tự đại diện (“*” cho biết bất kỳ một ký tự nào hoặc bất kỳ ký tự nào), A2: C11 là phạm vi tra cứu, số 3 cột chứa giá trị trả về.
2. Vlookup khi sử dụng ký tự đại diện, bạn phải đặt chế độ đối sánh chính xác với FALSE hoặc 0 cho đối số cuối cùng trong hàm Vlookup.
Lời khuyên:
1. Tìm và trả về các giá trị phù hợp kết thúc bằng một giá trị cụ thể, vui lòng áp dụng công thức này: =VLOOKUP(“*”&E2, $A$2:$C$11, 3, FALSE)
2. Để tra cứu và trả về giá trị đã so khớp dựa trên một phần của chuỗi văn bản, cho dù văn bản được chỉ định ở phía trước, phía sau hay ở giữa chuỗi văn bản, bạn chỉ cần nối hai ký tự * xung quanh tham chiếu ô hoặc văn bản. Vui lòng làm với công thức này: =VLOOKUP(“*”&D2&”*”, $A$2:$B$11, 2, FALSE)
7. Giá trị Vlookup từ một trang tính khácThông thường, bạn có thể phải làm việc với nhiều trang tính, hàm Vlookup có thể được sử dụng để tra cứu dữ liệu từ một trang tính khác giống như trên một trang tính.
=VLOOKUP(A2,’Data sheet’!$A$2:$C$15,3,0)
2. Sau đó, kéo chốt điều khiển điền xuống các ô mà bạn muốn áp dụng công thức này và bạn sẽ nhận được kết quả tương ứng như bạn cần, xem ảnh chụp màn hình:
Lưu ý: Trong công thức trên:
A2 đại diện cho giá trị tra cứu;
Bảng dữliệu là tên của trang tính mà bạn muốn tra cứu dữ liệu, (Nếu tên trang tính chứa các ký tự khoảng trắng hoặc dấu chấm câu, bạn nên đặt dấu ngoặc kép xung quanh tên trang tính, nếu không, bạn có thể sử dụng trực tiếp tên trang tính như = VLOOKUP (A2, Biểu dữ liệu! $ A $ 2: $ C $ 15,3,0));
A2: C15 là phạm vi dữ liệu trong Bảng dữ liệu mà chúng tôi đang tìm kiếm dữ liệu;
con số 3 là số cột chứa dữ liệu phù hợp mà bạn muốn trả về.
8. Giá trị Vlookup từ sổ làm việc khácPhần này sẽ nói về việc tra cứu và trả về các giá trị phù hợp từ một sổ làm việc khác bằng cách sử dụng hàm Vlookup.
1. Để truy xuất chi phí tương đối từ một sổ làm việc khác, trước tiên, hãy mở cả hai sổ làm việc bạn muốn sử dụng, sau đó áp dụng công thức sau vào một ô mà bạn muốn đặt kết quả:
=VLOOKUP(B2,'[Product list.xlsx]Sheet1′!$A$2:$B$6,2,0)
2. Sau đó, kéo và sao chép công thức này vào các ô khác mà bạn cần, xem ảnh chụp màn hình:
Ghi chú:
1. Trong công thức trên: B2 đại diện cho giá trị tra cứu; [Danh sách sản phẩm.xlsx] Trang tính 1 là tên của sổ làm việc và trang tính mà bạn muốn tra cứu dữ liệu, (Tham chiếu đến sổ làm việc được đặt trong dấu ngoặc vuông và toàn bộ sổ làm việc + trang tính được đặt trong dấu nháy đơn); A2: B6 là phạm vi dữ liệu trong trang tính của sổ làm việc khác mà chúng tôi đang tìm kiếm dữ liệu; con số 2 là số cột chứa dữ liệu phù hợp mà bạn muốn trả về.
2. Nếu sổ làm việc tra cứu bị đóng, đường dẫn tệp đầy đủ cho sổ làm việc tra cứu sẽ được hiển thị trong công thức như ảnh chụp màn hình sau:
9. Vlookup và trả về văn bản trống hoặc văn bản cụ thể thay vì giá trị lỗi 0 hoặc # N / AThông thường, khi bạn áp dụng hàm vlookup để trả về giá trị tương ứng, nếu ô phù hợp của bạn trống, nó sẽ trả về 0 và nếu không tìm thấy giá trị phù hợp của bạn, bạn sẽ gặp lỗi giá trị # N / A như ảnh chụp màn hình bên dưới. Thay vì hiển thị giá trị 0 hoặc # N / A với ô trống hoặc giá trị khác mà bạn thích, điều này Vlookup để trả lại giá trị trống hoặc giá trị cụ thể thay vì 0 hoặc không có hướng dẫn có thể giúp bạn từng bước một.
Ví dụ về VLOOKUP nâng cao 1. Tra cứu hai chiều với chức năng Vlookup (Vlookup trong hàng và cột)Đôi khi, bạn có thể cần thực hiện tra cứu 2 chiều, nghĩa là Vlookup cả hàng và cột cùng một lúc. Giả sử, nếu bạn có phạm vi dữ liệu sau và bây giờ, bạn có thể cần nhận giá trị cho một sản phẩm cụ thể trong một quý cụ thể. Phần này sẽ giới thiệu một số công thức để xử lý công việc này trong Excel.
Trong Excel, bạn có thể sử dụng kết hợp các hàm Vlookup và MATCH để tra cứu hai chiều, vui lòng áp dụng công thức sau vào một ô trống, rồi nhấn Đi vào phím để nhận kết quả.
=VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)
Lưu ý: Trong công thức trên:
H1: giá trị tra cứu trong cột mà bạn muốn lấy giá trị tương ứng dựa vào đó;
A2: E6: phạm vi dữ liệu bao gồm tiêu đề hàng;
H2: giá trị tra cứu trong hàng mà bạn muốn lấy giá trị tương ứng dựa trên;
A1: E1: các ô của tiêu đề cột.
=INDEX($B$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $B$1:$E$1, 0))
Lưu ý: Trong công thức trên:
B2: E6: phạm vi dữ liệu để trả về mục phù hợp từ;
H1: giá trị tra cứu trong cột mà bạn muốn lấy giá trị tương ứng dựa vào đó;
A2: A6: tiêu đề hàng chứa sản phẩm bạn muốn tìm.
H2: giá trị tra cứu trong hàng mà bạn muốn lấy giá trị tương ứng dựa trên;
B1: E1: tiêu đề cột chứa phần tư bạn muốn tìm.
2. Giá trị khớp Vlookup dựa trên hai tiêu chí trở lênBạn có thể dễ dàng tra cứu giá trị phù hợp dựa trên một tiêu chí, nhưng nếu bạn có hai tiêu chí trở lên, bạn có thể làm gì? Các hàm LOOKUP hoặc MATCH và INDEX trong Excel có thể giúp bạn giải quyết công việc này một cách nhanh chóng và dễ dàng.
Ví dụ, tôi có bảng dữ liệu bên dưới, để trả lại giá phù hợp dựa trên sản phẩm và kích thước cụ thể, các công thức sau có thể giúp bạn.
=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12)) Ghi chú:
1. Trong công thức trên: A2: A12 = G1: nghĩa là tìm kiếm các tiêu chí của G1 trong phạm vi A2: A12; B2: B12 = G2: nghĩa là tìm kiếm các tiêu chí của G2 trong phạm vi B2: B12; D2: D12: phạm vi mà bạn muốn trả về giá trị tương ứng.
2. Nếu bạn có nhiều hơn hai tiêu chí, bạn chỉ cần nối các tiêu chí khác vào công thức, chẳng hạn như: =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))
Sự kết hợp của chỉ mục và chức năng đối sánh cũng có thể được sử dụng để trả về giá trị phù hợp dựa trên nhiều tiêu chí. Vui lòng sao chép hoặc nhập công thức sau:
=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))
Sau đó, nhấn tổ hợp phím Ctrl + Shift + Enter để nhận giá trị tương đối khi bạn cần. Xem ảnh chụp màn hình:
Ghi chú:
1. Trong công thức trên: A2: A12 = G1: nghĩa là tìm kiếm các tiêu chí của G1 trong phạm vi A2: A12; B2: B12 = G2: nghĩa là tìm kiếm các tiêu chí của G2 trong phạm vi B2: B12; D2: D12: phạm vi mà bạn muốn trả về giá trị tương ứng.
2. Nếu bạn có nhiều hơn hai tiêu chí, bạn chỉ cần nối các tiêu chí mới vào công thức, chẳng hạn như: =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))
3. Vlookup để trả về nhiều giá trị phù hợp với một hoặc nhiều điều kiệnTrong Excel, hàm Vlookup tìm kiếm một giá trị và chỉ trả về giá trị khớp đầu tiên nếu có nhiều giá trị tương ứng được tìm thấy. Đôi khi, bạn có thể muốn trả về tất cả các giá trị tương ứng trong một hàng, trong một cột hoặc trong một ô. Phần này sẽ nói về cách trả về nhiều giá trị phù hợp với một hoặc nhiều điều kiện trong sổ làm việc.
Vlookup tất cả các giá trị phù hợp dựa trên một điều kiện theo chiều ngang:
Để Vlookup và trả về tất cả các giá trị phù hợp dựa trên một giá trị cụ thể theo chiều ngang, công thức chung là:
=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) – m, “”), COLUMN() – n)), “”)
Chú thích: m là số hàng của ô đầu tiên trong phạm vi trả về trừ đi 1. n là số cột của ô công thức đầu tiên trừ đi 1.
=IFERROR(INDEX($C$2:$C$20, SMALL(IF($F1=$A$2:$A$20, ROW($C$2:$C$20)-1,””), COLUMN()-5)),””)
2. Và sau đó, chọn ô công thức đầu tiên và kéo chốt điền sang các ô bên phải cho đến khi ô trống được hiển thị và tất cả các mục tương ứng đã được trích xuất, xem ảnh chụp màn hình:
Lời khuyên:
Nếu có các giá trị phù hợp trùng lặp trong danh sách trả về, để bỏ qua các giá trị trùng lặp, vui lòng sử dụng công thức này, sau đó nhấn Đi vào để nhận được kết quả đầu tiên: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),””)
Tiếp tục nhập công thức này: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),””) vào một ô bên cạnh kết quả đầu tiên, rồi nhấn Ctrl + Shift + Enter các phím với nhau để nhận kết quả thứ hai, sau đó kéo công thức này sang các ô bên phải để nhận tất cả các giá trị phù hợp khác cho đến khi ô trống hiển thị, xem ảnh chụp màn hình:
Vlookup tất cả các giá trị phù hợp dựa trên hai hoặc nhiều điều kiện theo chiều ngang:
Để Vlookup và trả về tất cả các giá trị phù hợp dựa trên các giá trị cụ thể hơn theo chiều ngang, công thức chung là:
=IFERROR(INDEX(return_range, SMALL(IF(1 = ((–(lookup_value1=lookup_range1)) * (–(lookup_value2 = lookup_range2))), ROW(return_range) – m, “”), COLUMN() – n)),””)
Chú thích: m là số hàng của ô đầu tiên trong phạm vi trả về trừ đi 1. n là số cột của ô công thức đầu tiên trừ đi 1.
1. Áp dụng công thức sau vào một ô trống mà bạn muốn xuất kết quả:
=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((–($F1=$A$2:$A$20)) * (–($F2=$B$2:$B$20))), ROW($C$2:$C$20)-1,””), COLUMN()-5)),””)
2. Sau đó, chọn ô công thức và kéo chốt điền sang các ô bên phải cho đến khi ô trống hiển thị và tất cả các giá trị phù hợp dựa trên tiêu chí cụ thể đã được trả lại, xem ảnh chụp màn hình:
Chú thích: Để có thêm tiêu chí, bạn chỉ cần kết hợp lookup_value và lookup_range vào công thức, chẳng hạn như: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((–(lookup_value1=lookup_range1)) * (–(lookup_value2 = lookup_range2) * (–(lookup_value3 =lookup_range3)))), ROW(return_range) – m, “”), COLUMN() – n)),””) .
Vlookup tất cả các giá trị phù hợp dựa trên một điều kiện theo chiều dọc:
Để Vlookup và trả về tất cả các giá trị phù hợp dựa trên một giá trị cụ thể theo chiều dọc, công thức chung là:
=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,””), ROW() – n )),””)
Chú thích: m là số hàng của ô đầu tiên trong phạm vi trả về trừ đi 1. n là số hàng của ô công thức đầu tiên trừ đi 1.
=IFERROR(INDEX($C$2:$C$20, SMALL(IF(E$2=$A$2:$A$20, ROW($C$2:$C$20)-1,””), ROW()-1)),””)
2. Sau đó, chọn ô công thức đầu tiên và kéo chốt điền xuống các ô khác cho đến khi ô trống được hiển thị và tất cả các mục tương ứng đã được liệt kê trong một cột, xem ảnh chụp màn hình:
Sau đó nhấn Ctrl + Shift + Enter các phím với nhau để nhận giá trị phù hợp đầu tiên, sau đó kéo ô công thức này xuống các ô khác cho đến khi ô trống hiển thị và bạn sẽ nhận được kết quả như mong muốn:
Vlookup tất cả các giá trị phù hợp dựa trên hai hoặc nhiều điều kiện theo chiều dọc:
Để Vlookup và trả về tất cả các giá trị phù hợp dựa trên các giá trị cụ thể hơn theo chiều dọc, công thức chung là:
=IFERROR(INDEX(return_range, SMALL(IF(1=((–(lookup_value1=lookup_range1)) * ( –(lookup_value2=lookup_range2))), ROW(return_range)-m,””), ROW()-n)),””)
Chú thích: m là số hàng của ô đầu tiên trong phạm vi trả về trừ đi 1. n là số hàng của ô công thức đầu tiên trừ đi 1.
=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((–($E$2=$A$2:$A$20)) * (–($F$2=$B$2:$B$20))), ROW($C$2:$C$20)-1,””), ROW()-1)),””)
2. Sau đó kéo ô công thức xuống các ô khác cho đến khi ô trống được hiển thị, xem ảnh chụp màn hình:
Chú thích: Để có thêm tiêu chí, bạn chỉ cần kết hợp lookup_value và lookup_range vào công thức, chẳng hạn như: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((–(lookup_value1=lookup_range1)) * (–(lookup_value2 = lookup_range2) * (–(lookup_value3 =lookup_range3)))), ROW(return_range) – m, “”), ROW() – n)),””) .
Nếu bạn muốn Vlookup và trả về nhiều giá trị đã so khớp vào một ô duy nhất với dấu phân cách được chỉ định, chức năng mới của TEXTJOIN có thể giúp bạn giải quyết công việc này một cách nhanh chóng và dễ dàng.
Vlookup tất cả các giá trị phù hợp dựa trên một điều kiện vào một ô duy nhất:
=TEXTJOIN(“,”,TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,””)) Lời khuyên:
Để bỏ qua các bản sao trong các giá trị phù hợp được trả về, vui lòng sử dụng công thức sau: =TEXTJOIN(“,”, TRUE, IF(IFERROR(MATCH($C$2:$C$20, IF(F1=$A$2:$A$20, $C$2:$C$20, “”), 0),””)=MATCH(ROW($C$2:$C$20), ROW($C$2:$C$20)), $C$2:$C$20, “”))
Vlookup tất cả các giá trị phù hợp dựa trên hai hoặc nhiều điều kiện vào một ô duy nhất:
=TEXTJOIN(“,”,TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,””)) Ghi chú:
1. Hàm TEXTJOIN chỉ khả dụng trong Excel 2023 và Office 365.
4. Vlookup để trả về toàn bộ hoặc toàn bộ hàng của một ô phù hợpTrong phần này, tôi sẽ nói về cách lấy toàn bộ hàng của một giá trị phù hợp bằng cách sử dụng hàm Vlookup.
=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)
2. Sau đó, kéo ô công thức sang bên phải cho đến khi dữ liệu của toàn bộ hàng được hiển thị, xem ảnh chụp màn hình:
Lời khuyên:
Nếu tìm thấy nhiều hàng dựa trên giá trị phù hợp, để trả về tất cả các hàng tương ứng, vui lòng áp dụng công thức này: =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),””),ROW()-1)),””) , và sau đó nhấn Ctrl + Shift + Enter các phím với nhau để nhận được kết quả đầu tiên, sau đó kéo chốt điền sang phải các ô, xem ảnh chụp màn hình:
5. Thực hiện nhiều hàm Vlookup (Vlookup lồng nhau) trong ExcelCông thức chung cho hàm Vlookup lồng nhau là:
=IFERROR(VLOOKUP(lookup_value,table1,col,0),IFERROR(VLOOKUP(lookup_value,table2,col,0),VLOOKUP(lookup_value,table3,col,0))) Lưu ý:
lookup_value: giá trị bạn đang tìm kiếm;
Bảng1, Bảng2, Bảng3, …: bảng chứa giá trị tra cứu và giá trị trả về;
col: số cột trong bảng mà bạn muốn trả về giá trị phù hợp.
0: Điều này được sử dụng cho một kết hợp chính xác.
1. Vui lòng áp dụng công thức sau vào một ô trống mà bạn muốn đặt kết quả:
Ghi chú:
1. Trong công thức trên, J3 là giá trị bạn đang tìm kiếm; A3: B7, D3: E7, G3: H7 là các phạm vi bảng trong đó giá trị tra cứu và giá trị trả về tồn tại; Con số 2 là số cột trong phạm vi để trả về giá trị phù hợp.
2. Nếu không tìm thấy giá trị tra cứu, giá trị lỗi sẽ được hiển thị, để thay thế lỗi bằng một văn bản có thể đọc được, vui lòng sử dụng công thức sau: =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),”can’t find”)))
6. Vlookup để kiểm tra xem giá trị có tồn tại hay không dựa trên dữ liệu danh sách trong một cột khácChức năng Vlookup cũng có thể giúp bạn kiểm tra xem các giá trị có tồn tại hay không dựa trên một danh sách khác, ví dụ: nếu bạn muốn tìm tên trong cột C và chỉ cần trả về Có hoặc Không nếu tên được tìm thấy hay không trong cột A như ảnh chụp màn hình bên dưới cho xem.
1. Vui lòng áp dụng công thức sau vào một ô trống:
=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), “No”, “Yes”)
2. Sau đó, kéo chốt điền xuống các ô mà bạn muốn điền vào công thức này và bạn sẽ nhận được kết quả như mong muốn, xem ảnh chụp màn hình:
7. Vlookup và tổng hợp tất cả các giá trị phù hợp trong các hàng hoặc cộtNếu bạn làm việc với dữ liệu số, đôi khi, khi trích xuất các giá trị phù hợp từ bảng, bạn cũng có thể cần tính tổng các số trong một số cột hoặc hàng. Phần này sẽ giới thiệu một số công thức để hoàn thành công việc này trong Excel.
Giả sử, bạn có một danh sách sản phẩm với doanh số bán hàng trong vài tháng như ảnh chụp màn hình bên dưới, bây giờ, bạn cần tổng hợp tất cả các đơn đặt hàng trong tất cả các tháng dựa trên các sản phẩm đã cho.
Vlookup và tính tổng các giá trị phù hợp đầu tiên trong một hàng:
=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))
2. Sau đó, kéo chốt điền xuống để sao chép công thức này sang các ô khác mà bạn cần và tất cả các giá trị trong một hàng của giá trị khớp đầu tiên đã được cộng lại với nhau, xem ảnh chụp màn hình:
Vlookup và tính tổng tất cả các giá trị phù hợp trong nhiều hàng:
Công thức trên chỉ có thể tính tổng các giá trị trong một hàng cho giá trị phù hợp đầu tiên. Nếu bạn muốn tính tổng tất cả các kết quả phù hợp trong nhiều hàng, vui lòng sử dụng công thức sau, sau đó kéo chốt điền xuống các ô bạn muốn áp dụng công thức này và bạn sẽ nhận được kết quả mong muốn, xem ảnh chụp màn hình:
=SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)
Vlookup và tính tổng các giá trị phù hợp đầu tiên trong một cột:
Nếu bạn muốn tính tổng giá trị cho các tháng cụ thể như trong ảnh chụp màn hình bên dưới.
=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))
Vlookup và tính tổng tất cả các giá trị phù hợp trong nhiều cột:
Để Vlookup và tính tổng tất cả các giá trị phù hợp trong nhiều cột, bạn nên sử dụng công thức sau:
=SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))
Có thể các công thức trên khó nhớ đối với bạn, trong trường hợp này, tôi sẽ giới thiệu một tính năng tiện dụng – Tra cứu và tính tổng of Kutools cho Excel, với tính năng này, bạn có thể nhận được kết quả dễ dàng nhất có thể. Nhấp để tải xuống Kutools cho Excel ngay bây giờ!
Ví dụ, nếu bạn muốn tính tổng các giá trị khi bạn cần khớp cả cột và hàng để có được tổng giá trị của sản phẩm Áo len trong tháng XNUMX như ảnh chụp màn hình bên dưới.
Vui lòng áp dụng công thức sau vào một ô, sau đó nhấn phím Enter để nhận kết quả, xem ảnh chụp màn hình:
=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))
Chú thích: Trong công thức trên: B2: F9 là phạm vi dữ liệu của các giá trị số mà bạn muốn tính tổng; B1: F1 istiêu đề cột chứa giá trị tra cứu mà bạn muốn tính tổng dựa trên; I2 là giá trị tra cứu trong tiêu đề cột bạn đang tìm kiếm; A2: A9 là tiêu đề hàng chứa giá trị tra cứu mà bạn muốn tính tổng dựa trên; H2 là giá trị tra cứu trong tiêu đề hàng bạn đang tìm kiếm.
8. Vlookup để hợp nhất hai bảng dựa trên một hoặc nhiều cột chínhTrong công việc hàng ngày, khi phân tích dữ liệu, bạn có thể cần tập hợp tất cả thông tin cần thiết vào một bảng duy nhất dựa trên một hoặc nhiều cột chính. Để giải quyết công việc này, chức năng Vlookup cũng có thể giúp bạn.
Ví dụ: bạn có hai bảng, bảng đầu tiên chứa dữ liệu về sản phẩm và tên, và bảng thứ hai chứa sản phẩm và đơn đặt hàng, bây giờ, bạn muốn kết hợp hai bảng này bằng cách khớp cột sản phẩm chung thành một bảng.
Để hợp nhất hai bảng thành một dựa trên một cột chính, vui lòng áp dụng công thức sau vào một ô trống mà bạn muốn lấy kết quả, sau đó kéo chốt điền xuống các ô mà bạn muốn áp dụng công thức này, bạn sẽ lấy bảng đã hợp nhất với cột thứ tự nối với dữ liệu bảng đầu tiên dựa trên dữ liệu cột chính.
=VLOOKUP($A2,$E$2:$F$8,2,FALSE)
Nếu dữ liệu chung của bạn ở phía bên phải và dữ liệu trả về ở cột bên trái trong bảng thứ hai, để hợp nhất cột thứ tự, thì hàm Vlookup không thể thực hiện công việc. Để tra cứu từ phải sang trái, bạn có thể sử dụng hàm INDEX và MATCH để thay thế cho hàm Vlookup.
=INDEX($E$2:$E$8, MATCH($A2, $F$2:$F$8, 0))
Nếu hai bảng bạn muốn nối có nhiều cột chính, để hợp nhất các bảng dựa trên các cột chung này, hàm INDEX và MATCH có thể giúp bạn.
Công thức chung để hợp nhất hai bảng dựa trên nhiều cột chính là:
2 Sau đó, chọn ô công thức đầu tiên và kéo chốt điền để sao chép công thức này sang các ô khác khi bạn cần:
Lời khuyên: Trong Excel 2023 và các phiên bản mới hơn, bạn cũng có thể sử dụng Truy vấn nguồn tính năng hợp nhất hai hoặc nhiều bảng thành một bảng dựa trên các cột chính. Vui lòng nhấp để biết chi tiết từng bước.
9. Vlookup so khớp các giá trị trên nhiều trang tính Các giá trị phù hợp với hàm VLOOKUP giữ nguyên định dạng ô 1. Vlookup để nhận định dạng ô (màu ô, màu phông chữ) cùng với giá trị tra cứuVui lòng thực hiện theo các bước sau để tra cứu và trả về giá trị tương ứng của nó cùng với định dạng ô:
1. Trong trang tính có chứa dữ liệu bạn muốn Vlookup, nhấp chuột phải vào tab trang tính và chọn Mã Chế độ xem từ menu ngữ cảnh. Xem ảnh chụp màn hình:
2. Trong mở Microsoft Visual Basic cho các ứng dụng , vui lòng sao chép mã VBA bên dưới vào cửa sổ Mã.
Mã VBA 1: Vlookup để nhận định dạng ô cùng với giá trị tra cứu
Sub Worksheet_Change(ByVal Target As Range) 'Updateby Extendoffice Dim I As Long Dim xKeys As Long Dim xDicStr As String On Error Resume Next Application.ScreenUpdating = False xKeys = UBound(xDic.Keys) For I = 0 To UBound(xDic.Keys) xDicStr = xDic.Items(I) Range(xDic.Keys(I)).Interior.Color = _ Range(xDic.Items(I)).Interior.Color Range(xDic.Keys(I)).Font.FontStyle = _ Range(xDic.Items(I)).Font.FontStyle Range(xDic.Keys(I)).Font.Size = _ Range(xDic.Items(I)).Font.Size Range(xDic.Keys(I)).Font.Color = _ Range(xDic.Items(I)).Font.Color Range(xDic.Keys(I)).Font.Name = _ Range(xDic.Items(I)).Font.Name Range(xDic.Keys(I)).Font.Underline = _ Range(xDic.Items(I)).Font.Underline Else Range(xDic.Keys(I)).Interior.Color = xlNone End If Next Set xDic = Nothing End If Application.ScreenUpdating = True End SubMã VBA 2: Vlookup để nhận định dạng ô cùng với giá trị tra cứu
Public xDic As New Dictionary Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long) Dim xFindCell As Range On Error Resume Next Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole) If xFindCell Is Nothing Then LookupKeepFormat = "" chúng tôi Application.Caller.Address, "" Else LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value chúng tôi Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address End If End Function5. Sau đó nhấn vào OK để đóng hộp thoại, sau đó lưu và đóng cửa sổ mã, bây giờ, hãy quay lại trang tính, rồi áp dụng công thức này: =LookupKeepFormat(E2,$A$1:$C$10,3) vào một ô trống mà bạn muốn xuất kết quả, rồi nhấn phím Enter. Xem ảnh chụp màn hình:
6. Sau đó, chọn ô kết quả đầu tiên và kéo chốt điền xuống để nhận tất cả kết quả cùng với định dạng của chúng. Xem ảnh chụp màn hình.
2. Giữ định dạng ngày từ giá trị trả về Vlookup=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),”mm/dd/yyyy”)
Chú thích: Trong công thức trên, E2 là giá trị giao diện, A2: C9 là phạm vi tra cứu, số 3 là số cột bạn muốn giá trị được trả về, mm/dd/yyy là định dạng ngày bạn muốn giữ lại.
3. Vlookup và trả về giá trị khớp với nhận xét ôBạn đã bao giờ thử dùng Vlookup để trả về không chỉ dữ liệu ô phù hợp mà còn cả nhận xét ô cũng như trong Excel như ảnh chụp màn hình sau đây không? Để giải quyết nhiệm vụ này, Hàm do Người dùng Xác định bên dưới có thể giúp bạn.
1. Giữ ALT + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.
Mã VBA: Vlookup và trả về giá trị khớp với nhận xét ô:
Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant 'Updateby Extendoffice Application.Volatile Dim xRet As Variant 'could be an error Dim xCell As Range xRet = Application.Match(LookVal, FTable.Columns(1), FType) If IsError(xRet) Then VlookupComment = "Not Found" Else Set xCell = FTable.Columns(FColumn).Cells(1)(xRet) VlookupComment = xCell.Value With Application.Caller If Not .Comment Is Nothing Then .Comment.Delete End If If Not xCell.Comment Is Nothing Then .AddComment xCell.Comment.Text End If End With End If End Function 4. Xử lý văn bản và số thực trong VlookupVí dụ: tôi có một dải dữ liệu, số ID trong bảng gốc là định dạng số, trong ô tra cứu được lưu trữ dưới dạng văn bản, khi áp dụng chức năng Vlookup thông thường, kết quả lỗi # N / A được hiển thị như ảnh chụp màn hình bên dưới cho xem. Trong trường hợp này, làm thế nào bạn có thể nhận được thông tin chính xác nếu số tra cứu và số gốc trong bảng có định dạng dữ liệu khác nhau?
Để xử lý văn bản và số thực trong hàm Vlookup, vui lòng áp dụng công thức sau vào một ô trống, sau đó kéo chốt điền xuống để sao chép công thức này và bạn sẽ nhận được kết quả chính xác như ảnh chụp màn hình bên dưới:
=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0)) Ghi chú:
1. Trong công thức trên, D2 là giá trị tra cứu bạn muốn trả về giá trị tương ứng của nó, A2: B8 là bảng dữ liệu bạn muốn sử dụng, số 2 là số cột chứa giá trị phù hợp mà bạn muốn trả về.
2. Công thức này cũng hoạt động tốt nếu bạn không chắc chắn nơi bạn có số và nơi bạn có văn bản.
Tải xuống các tệp mẫu VLOOKUP
Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
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 2023-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 Toàn Tập Về Hàm Vlookup Trong Excel Và Các Ví Dụ Nâng Cao Hay Gặp
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 EXCELCú 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 EXCELTô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ÁCTrong 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 VLOOKUPGiả 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 VLOOKUPCũ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 ĐỐIVà 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 EXCELCó 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 EXCELKhi 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
Sử Dụng Các Hàm Cơ Bản Trong Excel Và Ví Dụ
Home »
Sử dụng các hàm cơ bản trong Excel và ví dụ
Sử dụng các hàm cơ bản trong Excel và ví dụ
Save
Saved
Removed
1
Deal Score
0
Deal Score
0
5
/
5
(
1
vote
)
Nếu bạn chưa có Excel trong máy tính thì hãy download các phiên bản Excel được mình trình bày trong bài viết này.
1. Hàm Sum: Hàm tính tổng
Hàm Sum là hàm tính đầu tiên mà người học cần phải biết trong Excel. Hàm này sẽ giúp bạn tính tổng các giá trị mà bạn đã chọn từ các cột hoặc các hàng hay tính tổng trong một phạm vi nào đó.
Công thức: =SUM (number 1, number 2;…)
Ví dụ:
=SUM (B2: G2) – Một sự lựa chọn đơn giản để tính tổng các giá trị của một hàng.
=SUM (A2: A8) – Một sự lựa chọn đơn giản để tính tổng các giá trị của một cột.
=SUM (A2: A7, A9, A12: A15) – Một sự lựa chọn để tính tổng các giá trị từ phạm vi A2 đến A7, bỏ qua A8, thêm A9, bỏ qua A10 và A11, sau đó thêm từ A12 đến A15.
=SUM (A2: A8) / 20 – Tính tổng các giá trị trong phạm vi từ A12 đến A15 sau đó chia tổng đó cho 20.
2. Hàm Average: Hàm tính trung bình cộng các số
Hàm Average là hàm giúp bạn tính giá trị trung bình của các số được chọn trong một hàng, cột hoặc trong một phạm vi theo cách đơn giản và dễ dàng nhất, chẳng hạn như tính cổ tức trung bình của một nhóm cổ đông nhất định.
Công thức: =AVERAGE (number 1, number 2,…)
Ví dụ:
=AVERAGE (B2: B11) – Tính giá trị trung bình của các số trong khoảng từ B2 đến B11, nó tương tự với SUM (B2: B11)/10.
3. Hàm Min/Max: Hàm tìm giá trị lớn nhất, giá trị nhỏ nhất
Hàm MIN và hàm MAX giúp ta tìm được giá trị nhỏ nhất hoặc giá trị lớn nhất trong một phạm vi giá trị được chọn.
Công thức: =MIN (number 1, number 2,…)
=MAX (number 1, number 2,…)
Ví dụ:
=MIN (B2: C11) – Tìm giá trị nhỏ nhất trong phạm vi từ B2 đến C11.
=MAX (B2: C11) – Tương tự như vậy, hàm này yêu cầu tìm giá trị lớn nhất trong phạm vi từ B2 đến C11.
4. Hàm If: Hàm điều kiện
Hàm IF thường được sử dụng khi bạn muốn sắp xếp dữ liệu của mình theo một điều kiện nhất định nào đó. Khi sử dụng hàm này, bạn có thể lồng ghép cả các hàm trong Excel khác ở bên trong.
Công thức: =IF ( logic_test, [value_if_true], [value_if_false] )
Ví dụ:
=IF (C2
5. Hàm Trim: Hàm giúp loại bỏ các khoảng trống
Hàm TRIM là hàm giúp đảm bảo loại bỏ các khoảng trống trong các dữ liệu mà bạn nhập vào để khi thực hiện các thao tác khác không bị trả về các giá trị lỗi. Không giống như các hàm trong Excel với các chức năng khác có thể hoạt động trên một phạm vi các ô, hàm TRIM chỉ có thể hoạt động trên một ô duy nhất.
Công thức: =TRIM (text)
Ví dụ:
=TRIM(A2) – Loại bỏ khoảng trống trong giá trị của ô A2.
6. Hàm Count/CountA: Hàm đếm dữ liệu/ Đếm ô chứa dữ liệu
Hàm COUNT là hàm dùng để đếm tất cả các ô trong một phạm vi nhất định chỉ chứa các giá trị số.
Công thức: =COUNT ( value1, [value2],…)
=COUNT (A : A) – Đếm tất cả các giá trị bằng số trong cột A. Tuy nhiên, để đếm hàng, thì bạn phải điều chỉnh địa chỉ bên trong công thức thì hàm mới có thể được thực hiện
=COUNT (A1: C1) – Bây giờ công thức đã có thể đếm hàng.
Hàm COUNTA cũng giống như hàm COUNT, COUNTA đếm tất cả các ô có dữ liệu. Tuy nhiên, không giống với hàm COUNT chỉ đếm các giá trị số, hàm COUNTA có thể đếm giá trị nhiều loại như ngày, thời gian, văn bản…
Công thức: = COUNTA ( value1, [value2],…)
Ví dụ:
=COUNTA (C2: C13) – Đếm các hàng từ 2 đến 13 trong cột C bất kể giá trị nào. Tuy nhiên, giống như hàm COUNT, bạn không thể sử dụng cùng một công thức để đếm hàng. Bạn phải điều chỉnh lựa chọn bên trong dấu ngoặc, ví dụ =COUNTA (C2: H2) sẽ đếm các cột từ C đến H.
7. Hàm Left : hàm lấy kí tự ở bên trái
Trong các hàm excel, hàm left là hàm xử lý chuỗi, sử dụng hàm left nhằm mục đích lấy ra các kí tự ở bên trái chuỗi kí tự
Cú pháp của hàm left: =LEFT( text,n )
( Trong công thức trên text là chuỗi kí tự, n là số kí tự cần cắt ra từ chuỗi kí tự và n có giá trị mặc định là 1 )
Ví dụ minh họa: Yêu cầu bạn là phải lấy được 4 kí tự bên trái trong cột họ và tên của học sinh thì ta phải sử dụng hàm left với cú pháp như sau :
=LEFT( B2,4 )
8. Hàm Right : Hàm lấy kí tự ở bên phải
Trái ngược với hàm left, hàm right được sử dụng nhằm mục đích lấy ra các kí tự ở bên phải chuỗi kí tự và đây cũng là một trong những hàm excel hay được sử dụng nhất
Cú pháp của hàm Right : =RIGHT( text,n )
( Trong công thức trên text là chuỗi kí tự, n là số kí tự cần cắt ra từ chuỗi kí tự và n có giá trị mặc định là 1 )
Ví dụ minh họa: Yêu cầu bạn là phải lấy được 4 kí tự bên phải trong cột họ và tên của học sinh thì ta phải sử dụng hàm left với cú pháp như sau :
=RIGHT( B2,4 )
9. Hàm Sumif : Hàm dùng để tính tổng các ô có điều kiện
Trong các hàm excel thì hàm sumif được sử dụng để tính tổng các vị trí các ô. Không giống như hàm sum, hàm sumif cũng là tính tổng nhưng phải thõa mãn điều kiện, yêu cầu đề ra
Cú pháp hàm sumif=SUMIF(Range, Criteria, Sum_Range) Range
( Trong đó Range là vị trí của điều kiện, criteria là điều kiện còn sum_range là vị trí cần tính tổng )
Lưu ý: Trong trường hợp vị trí phạm vi của điều kiện trùng với vị trí phạm vi cần tính tổng thì cú pháp của nó không như cũa nữa mà sẽ đơn giản hơn =SUMIF( Phạm vi điều kiện, Điều kiện ).
Ví dụ minh họa : Đề bài yêu cầu bạn tính tổng số điểm của các bạn nam trong lớp thì bạn nhập cú pháp như sau
=SUMIF(C2:C11,”nam”,D2:D11)
10. Hàm year, month, day : ngày, tháng, năm
Đây là 3 hàm excel có đặc điểm là chỉ về thời gian : day có nghĩa là ngày, month có nghĩa là tháng, year có nghĩa là năm
Cú pháp chung của cả 3 hàm này là =YEAR(today()), =MONTHtoday()), =DAY(today()). Mục đích chính mà mọi người hay sử dụng là nhằm để tách lấy 1 trong 3 yếu tố ngày tháng năm từ một chuỗi số liệu thống kê trên excel.
– Với biểu thức ngày hoặc tháng hoặc năm ở đây là một giá trị số chỉ giá trị ngày tháng năm.
– Ví dụ về hàm: Nếu hôm nay là ngày 22/09/2019, thì khi bạn gõ =DAY(TODAY()) → 22; =MONTH(TODAY()) → 9; =YEAR(TODAY()) → 2023.
11. Hàm Networkdays : hàm tính số ngày làm việc
Đây là một hàm excel dùng để tính thời gian, tiêu biểu là bạn làm công việc này trong bao lâu, bạn đã sử dụng cái này trong bao lâu, gặp lại bạn cũ trong bao lâu,…vv
Networkday là một hàm rất thuận tiện để làm việc đó
Cú pháp hàm networkday : =NETWORKDAY ( Number1, Number2 )
Ví dụ minh họa : Để tính số ngày bạn đã làm việc cho công ty A từ 10/11/2016 đến 01/07/2019 bạn gõ như sau:
=NETWORKDAY( C2, D2 )
Và kết quả nhận lại được là 585 ngày.
12. Hàm And: hàm logic
Hàm and được biết đến như là một hàm logic cơ bản trong phần mềm Microsoft Excel, đây là hàm thường để sử dụng với hàm IF để xét nhiều logic cùng lúc.
Kết quả trả về là TRUE nếu tất cả các đối số là TRUE
Cú pháp hàm And : =AND (Logical 1 , Logical 2, ….)
Trong đó các đối số Logical 1, Logical 2 là các biểu thức điều kiện
Kết quả hàm sẽ trả về cho bạn giá trị TRUE ( 1 ) nếu tất cả các đối số của nó là đúng, trả về cho bạn kết quả có giá trị FALSE ( 0 ) nếu thấy trong đó có một hay nhiều đối số là sai.
13. Hàm OR : hàm lý luận
Hàm OR là hàm lý luận tương tự như hàm and, hàm OR này được sử dụng kết hợp với các hàm khác như SUMIF, HLOOKUP, VLOOKUP, COUNTIF, SUMIF,… mà hàm này không dùng độc lập.
Cú pháp hàm OR : =OR( D1, D2… )
( Trong đó các đối số D1, D2 là các biểu thức điều kiện )
Sau khi thực hiện hàm sẽ trả về cho bạn giá trị TRUE ( 1 ) nếu bất cứ đối số của nó là đúng, và ngược lại nó sẽ trả về cho bạn kết quả có giá trị FALSE ( 0 ) nếu thấy trong đó có một hay nhiều đối số là sai.
14. Hàm choose: chọn lựa
Hàm choose sẽ trả về kết quả từ danh sách bạn chỉ định, dựa trên các vị trí được yêu cầu
Ví dụ trên cho thấy hàm CHOOSE trả về giá trị BOAT ở vị trí thứ 3.
Tổng kết các hàm cơ bản trong excel
Các bạn đã được tìm hiểu các hàm cơ bản trong Excel và cách sử dụng của từng hàm. Hi vọng qua bài viết này các bạn sẽ nắm chắc được kiến thức của các hàm cơ bản trong Excel và sử dụng chúng một cách linh hoạt và tiện lợi.
Cập nhật thông tin chi tiết về Hàm Vlookup Với Một Số Ví Dụ Cơ Bản Và Nâng Cao 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!