Tất cả bí mật của hàm VLOOKUP trong Excel để tìm dữ liệu trong bảng và trích xuất nó vào một bảng khác

2024 Tác giả: Malcolm Clapton | [email protected]. Sửa đổi lần cuối: 2023-12-17 04:15

Sau khi đọc bài viết, bạn sẽ không chỉ học cách tìm dữ liệu trong bảng tính Excel và trích xuất nó sang một bảng tính khác, mà còn cả các kỹ thuật có thể được sử dụng kết hợp với hàm Vlookup.

Khi làm việc trong Excel, rất thường xuyên có nhu cầu tìm dữ liệu trong một bảng và trích xuất nó vào một bảng khác. Nếu bạn vẫn chưa biết cách thực hiện điều này, thì sau khi đọc bài viết này, bạn sẽ không chỉ học cách thực hiện việc này mà còn tìm hiểu xem trong những điều kiện nào bạn có thể khai thác hiệu suất tối đa của hệ thống. Hầu hết các kỹ thuật rất hiệu quả nên được sử dụng cùng với hàm VLOOKUP đều được xem xét.

Ngay cả khi bạn đã sử dụng hàm VLOOKUP trong nhiều năm, thì khả năng cao là bài viết này sẽ hữu ích với bạn và không khiến bạn thờ ơ. Ví dụ, là một chuyên gia CNTT, và sau đó là một người đứng đầu trong lĩnh vực CNTT, tôi đã sử dụng hàm VLOOKUP được 15 năm, nhưng tôi chỉ xoay sở được với tất cả các sắc thái bây giờ, khi tôi bắt đầu dạy Excel cho mọi người một cách chuyên nghiệp.

VLOOKUP là chữ viết tắt của vthẳng đứng NS điều tra. Tương tự như vậy, VLOOKUP - LOOKUP theo chiều dọc. Chính cái tên của hàm gợi ý cho chúng ta rằng nó tìm kiếm trong các hàng của bảng (theo chiều dọc - lặp qua các hàng và sửa cột), chứ không phải trong các cột (theo chiều ngang - lặp qua các cột và sửa hàng). Cần lưu ý rằng hàm VLOOKUP có một người chị - một chú vịt con xấu xí sẽ không bao giờ trở thành thiên nga - đây là hàm HLOOKUP. HLOOKUP, trái ngược với VLOOKUP, thực hiện tìm kiếm theo chiều ngang, nhưng khái niệm Excel (và thực sự là khái niệm tổ chức dữ liệu) ngụ ý rằng bảng của bạn có ít cột hơn và nhiều hàng hơn. Đó là lý do tại sao chúng ta cần tìm kiếm theo hàng thường xuyên hơn nhiều lần so với cột. Nếu bạn sử dụng hàm HLO quá thường xuyên trong Excel, thì có khả năng là bạn đã không hiểu điều gì đó trong cuộc sống này.

Cú pháp

Hàm VLOOKUP có bốn tham số:

= VLOOKUP (;; [;]), ở đây:

- giá trị mong muốn (hiếm khi) hoặc tham chiếu đến ô chứa giá trị mong muốn (đại đa số các trường hợp);

- tham chiếu đến một dải ô (mảng hai chiều), trong cột FIRST (!) mà giá trị tham số sẽ được tìm kiếm;

- số cột trong phạm vi mà từ đó giá trị sẽ được trả về;

- đây là một tham số rất quan trọng trả lời câu hỏi liệu cột đầu tiên của phạm vi có được sắp xếp theo thứ tự tăng dần hay không. Nếu mảng được sắp xếp, chúng tôi chỉ định giá trị TRUE hoặc 1, ngược lại - FALSE hoặc 0. Nếu tham số này bị bỏ qua, nó được mặc định là 1.

Tôi cá rằng nhiều người trong số những người biết hàm Vlookup là không ổn định, sau khi đọc mô tả của tham số thứ tư, họ có thể cảm thấy khó chịu, vì họ đã quen nhìn thấy nó ở dạng hơi khác: thường họ đang nói về một kết quả khớp chính xác khi tìm kiếm (FALSE hoặc 0) hoặc quét phạm vi (TRUE hoặc 1).

Bây giờ bạn cần phải căng thẳng và đọc đoạn tiếp theo nhiều lần cho đến khi bạn cảm nhận được ý nghĩa của những gì đã nói ở phần cuối. Mỗi từ đều quan trọng ở đó. Ví dụ sẽ giúp bạn tìm ra nó.

Công thức VLOOKUP hoạt động chính xác như thế nào?

• Loại công thức I. Nếu tham số cuối cùng bị bỏ qua hoặc được chỉ định bằng 1, thì hàm VLOOKUP giả định rằng cột đầu tiên được sắp xếp theo thứ tự tăng dần, vì vậy tìm kiếm dừng lại ở hàng ngay trước dòng chứa giá trị lớn hơn giá trị mong muốn … Nếu không tìm thấy chuỗi nào như vậy, hàng cuối cùng của dải ô sẽ được trả về.

  

• Công thức II. Nếu tham số cuối cùng được chỉ định là 0, thì hàm VLOOKUP sẽ quét tuần tự cột đầu tiên của mảng và ngay lập tức dừng tìm kiếm khi tìm thấy kết quả khớp chính xác đầu tiên với tham số, nếu không, mã lỗi # N / A (# N / A) Được trả lại.

  

Công thức quy trình làm việc

VPR loại I

VPR loại II

Tràng hoa cho công thức dạng I

1. Công thức có thể được sử dụng để phân phối giá trị trên các phạm vi.
2. Nếu cột đầu tiên chứa các giá trị trùng lặp và được sắp xếp chính xác, thì cột cuối cùng của các hàng có giá trị trùng lặp sẽ được trả về.
3. Nếu bạn tìm kiếm một giá trị rõ ràng là lớn hơn cột đầu tiên có thể chứa, thì bạn có thể dễ dàng tìm thấy hàng cuối cùng của bảng, hàng này có thể khá có giá trị.
4. Chế độ xem này sẽ chỉ trả về lỗi # N / A nếu nó không tìm thấy giá trị nhỏ hơn hoặc bằng giá trị mong muốn.
5. Khá khó hiểu là công thức trả về các giá trị sai nếu mảng của bạn không được sắp xếp.

Tràng hoa cho công thức loại II

Nếu giá trị mong muốn xuất hiện nhiều lần trong cột đầu tiên của mảng, thì công thức sẽ chọn hàng đầu tiên để truy xuất dữ liệu tiếp theo.

Hiệu suất VLOOKUP

Bạn đã đạt đến cao trào của bài viết. Có vẻ như, sự khác biệt là gì nếu tôi chỉ định không hoặc một làm tham số cuối cùng? Về cơ bản, tất nhiên, mọi người đều chỉ ra số 0, vì điều này khá thực tế: bạn không cần phải lo lắng về việc sắp xếp cột đầu tiên của mảng, bạn có thể ngay lập tức xem giá trị đã được tìm thấy hay chưa. Nhưng nếu bạn có hàng nghìn công thức VLOOKUP trên trang tính của mình, bạn sẽ nhận thấy rằng hàm VLOOKUP II chạy chậm. Đồng thời, mọi người thường bắt đầu nghĩ:

• Tôi cần một máy tính mạnh hơn;
• Tôi cần một công thức nhanh hơn, chẳng hạn như nhiều người biết về INDEX + MATCH, công thức này được cho là nhanh hơn 5-10%.

Và ít ai nghĩ rằng ngay sau khi bạn bắt đầu sử dụng VLOOKUP loại I và đảm bảo rằng cột đầu tiên được sắp xếp theo bất kỳ phương tiện nào, tốc độ của VLOOKUP sẽ tăng lên 57 lần. Tôi đang viết bằng chữ - LẦN THỨ BẢY! Không phải 57%, mà là 5.700%. Tôi đã kiểm tra thực tế này khá đáng tin cậy.

Bí mật của công việc nhanh như vậy nằm ở chỗ một thuật toán tìm kiếm cực kỳ hiệu quả có thể được áp dụng trên một mảng đã sắp xếp, được gọi là tìm kiếm nhị phân (phương pháp chia đôi, phương pháp phân đôi). Vì vậy, VLOOKUP loại I áp dụng nó, và VLOOKUP loại II tìm kiếm mà không có bất kỳ tối ưu hóa nào. Điều này cũng đúng với hàm MATCH, bao gồm một tham số tương tự và hàm LOOKUP, chỉ hoạt động trên các mảng được sắp xếp và được đưa vào Excel để tương thích với Lotus 1-2-3.

Nhược điểm của công thức

Nhược điểm của hàm VLOOKUP là rõ ràng: thứ nhất, nó chỉ tìm kiếm trong cột đầu tiên của mảng được chỉ định và thứ hai, chỉ ở bên phải của cột này. Và như bạn hiểu, có thể xảy ra trường hợp cột chứa thông tin cần thiết sẽ ở bên trái cột mà chúng ta sẽ xem xét. Sự kết hợp công thức INDEX + MATCH đã được đề cập không có nhược điểm này, khiến nó trở thành giải pháp linh hoạt nhất để trích xuất dữ liệu từ các bảng so với hàm VLOOKUP (VLOOKUP).

Một số khía cạnh của việc áp dụng công thức trong cuộc sống thực

Tìm kiếm phạm vi

Một minh họa cổ điển về tìm kiếm theo phạm vi là nhiệm vụ xác định mức chiết khấu theo kích thước đơn hàng.

Tìm kiếm chuỗi văn bản

Tất nhiên, hàm VLOOKUP không chỉ tìm kiếm số mà còn tìm kiếm văn bản. Cần lưu ý rằng công thức không phân biệt trường hợp của các ký tự. Nếu bạn sử dụng ký tự đại diện, bạn có thể tổ chức tìm kiếm mờ. Có hai ký tự đại diện: "?" - thay thế bất kỳ một ký tự nào trong chuỗi văn bản, "*" - thay thế bất kỳ số ký tự nào.

Không gian chiến đấu

Câu hỏi thường được đặt ra là làm thế nào để giải quyết vấn đề về khoảng trắng thừa khi tìm kiếm. Nếu bảng tra cứu vẫn có thể bị xóa chúng, thì tham số đầu tiên của công thức VLOOKUP không phải lúc nào cũng tùy thuộc vào bạn. Do đó, nếu có nguy cơ làm tắc các ô có khoảng trống thừa, thì bạn có thể sử dụng hàm TRIM để xóa nó.

Định dạng dữ liệu khác nhau

Nếu tham số đầu tiên của hàm Vlookup tham chiếu đến một ô có chứa một số, nhưng được lưu trữ trong ô dưới dạng văn bản và cột đầu tiên của mảng chứa các số ở định dạng đúng, thì tìm kiếm sẽ không thành công. Tình huống ngược lại cũng có thể xảy ra. Vấn đề có thể được giải quyết dễ dàng bằng cách dịch tham số 1 sang định dạng yêu cầu:

= VLOOKUP (−− D7; Sản phẩm! $ A $ 2: $ C $ 5; 3; 0) - nếu D7 chứa văn bản và bảng chứa số;

= VLOOKUP (D7 & ""); Sản phẩm! $ A $ 2: $ C $ 5; 3; 0) - và ngược lại.

Nhân tiện, bạn có thể dịch văn bản thành một số theo nhiều cách cùng một lúc, hãy chọn:

• Phủ định kép -D7.
• Nhân với một D7 * 1.
• Phép cộng bằng không D7 + 0.
• Nâng lên lũy thừa đầu tiên D7 ^ 1.

Việc chuyển đổi một số thành văn bản được thực hiện thông qua việc nối với một chuỗi trống, điều này buộc Excel phải chuyển đổi kiểu dữ liệu.

Cách ngăn chặn # N / A

Điều này rất thuận tiện để thực hiện với hàm IFERROR.

Ví dụ: = IFERROR (VLOOKUP (D7; Sản phẩm! $ A $ 2: $ C $ 5; 3; 0); "").

Nếu hàm VLOOKUP trả về mã lỗi # N / A, thì hàm IFERROR sẽ chặn nó và thay thế tham số 2 (trong trường hợp này là một chuỗi trống) và nếu không có lỗi nào xảy ra, thì hàm này sẽ giả sử rằng nó hoàn toàn không tồn tại, nhưng chỉ có hàm VLOOKUP trả về kết quả bình thường.

Mảng

Thường thì họ quên đặt tham chiếu của mảng là tuyệt đối và khi kéo dài mảng sẽ "trôi". Hãy nhớ sử dụng $ A $ 2: $ C $ 5 thay vì A2: C5.

Bạn nên đặt mảng tham chiếu trên một trang tính riêng của sổ làm việc. Nó không bị kẹt dưới chân, và sẽ an toàn hơn.

Một ý tưởng tốt hơn nữa sẽ là khai báo mảng này dưới dạng một phạm vi được đặt tên.

Nhiều người dùng, khi chỉ định một mảng, sử dụng cấu trúc như A: C, chỉ định toàn bộ các cột. Cách tiếp cận này có quyền tồn tại, vì bạn không phải theo dõi thực tế rằng mảng của bạn bao gồm tất cả các chuỗi bắt buộc. Nếu bạn thêm hàng vào trang tính với mảng ban đầu, thì phạm vi được chỉ định là A: C không cần được điều chỉnh. Tất nhiên, cấu trúc cú pháp này buộc Excel phải làm nhiều việc hơn một chút so với việc chỉ định chính xác phạm vi, nhưng chi phí này có thể bị bỏ qua. Chúng ta đang nói về phần trăm giây.

Vâng, trên bờ vực của thiên tài - để sắp xếp các mảng theo hình thức.

Sử dụng hàm COLUMN để chỉ định cột cần trích xuất

Nếu bảng mà bạn đang truy xuất dữ liệu bằng hàm VLOOKUP có cấu trúc giống như bảng tra cứu, nhưng chỉ chứa ít hàng hơn, thì bạn có thể sử dụng hàm COLUMN () trong hàm VLOOKUP để tự động tính toán số cột được truy xuất. Trong trường hợp này, tất cả các công thức VLOOKUP sẽ giống nhau (được điều chỉnh cho tham số đầu tiên, tham số này sẽ tự động thay đổi)! Lưu ý rằng tham số đầu tiên có tọa độ cột tuyệt đối.

Tạo khóa tổng hợp với & "|" &

Nếu cần thiết phải tìm kiếm theo nhiều cột cùng một lúc, thì cần phải tạo một khóa tổng hợp cho việc tìm kiếm. Nếu giá trị trả về không phải là văn bản (như trường hợp của trường "Mã") mà là số, thì công thức SUMIFS thuận tiện hơn sẽ phù hợp cho điều này và khóa cột tổng hợp sẽ không bắt buộc.

Đây là bài viết đầu tiên của tôi cho một Lifehacker. Nếu bạn thích nó, tôi mời bạn ghé thăm, và cũng rất vui khi đọc các bình luận về bí quyết sử dụng hàm VLOOKUP của bạn và những thứ tương tự. Cảm ơn.:)