Kiến thức về chức năng, ví dụ. Chức năng vpr hoạt động như thế nào?

Chắc chắn là nhiều người dùng đang hoạt động Trình chỉnh sửa bảng tính Excel định kỳ phải xử lý các tình huống cần thay thế các giá trị từ bảng này sang bảng khác. Hãy tưởng tượng, một sản phẩm nào đó sẽ đến kho của bạn. Chúng tôi có sẵn hai tệp: một tệp có danh sách tên hàng hóa đã nhận, tệp thứ hai là bảng giá của chính sản phẩm này. Mở bảng giá, chúng ta thấy trong đó có nhiều món hơn và chúng không được sắp xếp theo thứ tự như trong file danh sách món. Không chắc ai trong chúng ta sẽ thích ý tưởng kiểm tra cả hai tệp và chuyển giá từ tài liệu này sang tài liệu khác theo cách thủ công. Tất nhiên, khi chúng ta đang nói về 5–10 vị trí, việc nhập dữ liệu cơ học là hoàn toàn có thể, nhưng phải làm gì nếu số lượng mục vượt quá 1000? Trong trường hợp này, Excel và hàm VLOOKUP kỳ diệu của nó (hoặc vlookup, nếu chúng ta đang nói về phiên bản tiếng Anh của chương trình) sẽ giúp chúng ta giải quyết công việc đơn điệu.

Vì vậy, khi bắt đầu công việc chuyển đổi dữ liệu từ bảng này sang bảng khác, sẽ là thích hợp để thực hiện Đánh giá ngắn Hàm VLOOKUP. Như bạn có thể đã hiểu, vlookup cho phép bạn chuyển dữ liệu từ bảng này sang bảng khác, từ đó tự động điền vào các ô mà chúng ta cần. Để hàm VLOOKUP hoạt động chính xác, hãy chú ý đến sự hiện diện của các ô đã hợp nhất trong tiêu đề bảng của bạn. Nếu có, bạn sẽ cần phải chia chúng ra.

Giả sử chúng ta cần điền vào “Bảng đặt hàng” với dữ liệu từ “Bảng giá”

Vì vậy, chúng ta phải đối mặt với nhiệm vụ chuyển giá của các hàng hóa hiện có vào một bảng có tên của chúng và tính tổng giá thành của từng sản phẩm. Để làm điều này, chúng ta phải thực hiện thuật toán sau:

Để bắt đầu, hãy cho bảng tính Excel theo hình thức bạn cần. Thêm hai cột có tên “Giá” và “Chi phí” vào ma trận dữ liệu đã chuẩn bị. Chọn định dạng tiền tệ cho các ô trong phạm vi của các cột mới được tạo.
Bây giờ hãy kích hoạt ô đầu tiên trong khối “Giá” và gọi “Trình hướng dẫn hàm”. Điều này có thể được thực hiện bằng cách nhấp vào nút “fx” nằm ở phía trước thanh công thức hoặc bằng cách giữ tổ hợp phím “Shift+F3”. Trong hộp thoại mở ra, hãy tìm danh mục “Liên kết và Mảng”. Ở đây chúng ta không quan tâm đến bất cứ điều gì khác ngoài hàm VLOOKUP. Chọn nó và nhấn OK. Nhân tiện, cần phải nói rằng hàm VLOOKUP có thể được gọi thông qua tab “Công thức”, trong danh sách thả xuống cũng có danh mục “Liên kết và Mảng”.
Sau khi kích hoạt VLOOKUP, một cửa sổ sẽ mở ra trước mặt bạn với danh sách các đối số cho hàm bạn đã chọn. Trong trường “Giá trị tìm kiếm”, bạn sẽ cần nhập phạm vi dữ liệu có trong cột đầu tiên của bảng với danh sách hàng hóa đã nhận và số lượng của chúng. Nghĩa là, bạn cần cho Excel biết chính xác những gì nó cần tìm trong bảng thứ hai và chuyển sang bảng đầu tiên.
Sau khi đối số đầu tiên được chỉ định, bạn có thể chuyển sang đối số thứ hai. Trong trường hợp của chúng tôi, đối số thứ hai là một bảng có bảng giá. Đặt con trỏ chuột vào trường đối số và di chuyển đến trang có danh sách giá. Chọn thủ công một phạm vi với các ô nằm trong khu vực cột có tên sản phẩm và giá của chúng. Cho Excel biết giá trị nào bạn muốn hàm VLOOKUP khớp.
Để Excel không bị nhầm lẫn và tham khảo được dữ liệu bạn cần, điều quan trọng là phải sửa liên kết được cung cấp cho nó. Để thực hiện việc này, hãy đánh dấu các giá trị cần thiết trong trường “Bảng” và nhấn F4. Nếu mọi thứ được thực hiện chính xác, dấu $ sẽ xuất hiện trên màn hình.
Bây giờ chúng ta đi đến trường đối số “Số trang” và đặt nó thành giá trị “2”. Khối này chứa tất cả dữ liệu cần được gửi đến bảng tính của chúng tôi và do đó, điều quan trọng là phải gán giá trị sai cho “Chế độ xem khoảng thời gian” (đặt vị trí thành “FALSE”). Điều này là cần thiết để hàm VLOOKUP chỉ hoạt động với giá trị chính xác và không làm tròn chúng.

Bây giờ tất cả các hành động cần thiết đã được hoàn thành, tất cả những gì chúng ta phải làm là xác nhận chúng bằng cách nhấp vào nút “OK”. Ngay khi dữ liệu ở ô đầu tiên thay đổi, chúng ta sẽ cần áp dụng hàm VLOOKUP cho mọi thứ tài liệu excel. Để thực hiện việc này, chỉ cần nhân VLOOKUP trên toàn bộ cột “Giá”. Điều này có thể được thực hiện bằng cách kéo góc dưới bên phải của ô có giá trị đã thay đổi xuống cuối cột. Nếu mọi thứ diễn ra suôn sẻ và dữ liệu thay đổi theo cách chúng tôi cần, chúng tôi có thể bắt đầu tính tổng chi phí hàng hóa của mình. Để thực hiện hành động này, chúng ta cần tìm sản phẩm của hai cột - “Số lượng” và “Giá cả”. Bởi vì Excel chứa mọi thứ công thức toán học, phép tính có thể được cung cấp cho “Thanh công thức” bằng cách sử dụng biểu tượng “fx” đã quen thuộc với chúng ta.

Tâm điểm

Có vẻ như mọi thứ đã sẵn sàng và VLOOKUP đã hoàn thành nhiệm vụ của chúng tôi, nhưng thực tế không phải vậy. Thực tế là trong cột “Giá”, hàm VLOOKUP vẫn hoạt động, bằng chứng cho điều này là việc hiển thị hàm VLOOKUP trên thanh công thức. Tức là cả hai bảng của chúng tôi vẫn được kết nối với nhau. Sự song song như vậy có thể dẫn đến thực tế là khi dữ liệu trong bảng có bảng giá thay đổi, thông tin có trong tệp làm việc của chúng ta với danh sách hàng hóa cũng sẽ thay đổi.

Tốt hơn hết bạn nên tránh tình trạng này bằng cách chia hai bảng ra. Để thực hiện việc này, chúng ta cần chọn các ô nằm trong phạm vi của cột “Giá” và nhấp chuột phải vào cột đó. Trong cửa sổ mở ra, chọn và kích hoạt tùy chọn “Sao chép”. Sau đó, không xóa vùng chọn khỏi vùng ô đã chọn, hãy nhấn lại nút bên phải chuột và chọn tùy chọn "Dán đặc biệt".

Kích hoạt tùy chọn này sẽ mở một hộp thoại trên màn hình của bạn, trong đó bạn sẽ cần chọn hộp bên cạnh danh mục “Giá trị”. Xác nhận hành động của bạn bằng cách nhấp vào nút “OK”.

Chúng tôi quay lại thanh công thức của mình và kiểm tra tình trạng còn hàng trong cột “Giá” chức năng hoạt động VLOOKUP. Nếu thay cho công thức bạn thấy đơn giản giá trị số, có nghĩa là mọi thứ đều ổn và chức năng VLOOKUP bị tắt. Tức là mối liên hệ giữa hai Tệp Excel bị rách và không có nguy cơ thay đổi ngoài kế hoạch hoặc xóa dữ liệu đính kèm trên bảng với bảng giá. Giờ đây, bạn có thể sử dụng tài liệu bảng tính một cách an toàn và không phải lo lắng về điều gì sẽ xảy ra nếu “Bảng giá” bị đóng hoặc chuyển đến vị trí khác.

Làm thế nào để so sánh hai bảng trong Excel?

Bằng cách sử dụng hàm VLOOKUP, bạn có thể so sánh một số giá trị khác nhau chỉ trong vài giây, chẳng hạn như so sánh giá của một sản phẩm hiện tại đã thay đổi như thế nào. Để thực hiện việc này, bạn cần đăng ký VLOOKUP trong cột trống và tham chiếu hàm tới các giá trị đã thay đổi trong bảng khác. Tốt nhất là cột " Giá mới" sẽ nằm ngay sau cột "Giá". Giải pháp này sẽ cho phép bạn thực hiện những thay đổi về giá rõ ràng hơn để so sánh.

Khả năng làm việc với nhiều điều kiện

Một ưu điểm chắc chắn khác của hàm VLOOKUP là khả năng hoạt động với một số thông số cụ thể cho sản phẩm của bạn. Để tìm một sản phẩm dựa trên hai đặc điểm trở lên, bạn phải:

Tạo hai (hoặc, nếu cần, nhiều hơn) điều kiện tìm kiếm.
Thêm một cột mới, trong quá trình hoạt động của chức năng, tất cả các cột khác mà sản phẩm được tìm kiếm sẽ được thêm vào.
Trong cột kết quả, sử dụng thuật toán được mô tả ở trên, chúng ta nhập công thức của hàm VLOOKUP đã quen thuộc với chúng ta.

Tóm lại, điều đáng nói là sự hỗ trợ của Excel cho chức năng như VLOOKUP giúp đơn giản hóa rất nhiều việc làm việc với thông tin dạng bảng. Đừng ngại sử dụng VLOOKUP khi làm việc với một số lượng lớn data, vì dù được thiết kế thế nào thì nguyên lý hoạt động của chức năng vẫn luôn giống nhau. Tất cả những gì bạn cần làm là xác định chính xác các đối số của nó.

Hàm VLOOKUP trong bảng sẽ giúp bạn tìm giá trị trong bảng. Ví dụ Excelđược mô tả dưới đây trong bài viết.

Trong khi làm việc với chương trình, người dùng thường cần phải tìm kiếm nhanh thông tin trong một bảng và chuyển nó sang một đối tượng trang tính khác.

Hiểu nguyên tắc công việc VPR sẽ đơn giản hóa đáng kể công việc của bạn và giúp bạn hoàn thành công việc nhanh hơn.

Nội dung:

VLOOKUP (Tra cứu dọc) là một tên hàm khác có thể tìm thấy trong phiên bản tiếng Anh của bộ xử lý bảng tính. Bản thân chữ viết tắt VPR có nghĩa là “xem theo chiều dọc”. Việc phân tích dữ liệu và tìm kiếm dữ liệu trong bảng được thực hiện bằng cách liệt kê dần dần các phần tử từ hàng này sang hàng khác trong mỗi cột.

Ngoài ra Excel còn có chức năng ngược lại là HLOOKUP hoặc HLOOKUP - xem ngang. Sự khác biệt duy nhất trong hoạt động của các tùy chọn là GLOOK thực hiện tìm kiếm trong bảng bằng cách liệt kê các cột chứ không phải hàng. Thông thường, người dùng thích hàm VLOOKUP hơn vì hầu hết các bảng đều có nhiều hàng hơn cột.

Cú pháp VLOOKUP trông như thế nào?

Sử dụng tài liệu đã được tạo hoặc mở một tài liệu mới tờ giấy trắng;
Nhấp vào nút “Công thức”, như thể hiện trong hình bên dưới;
Trong thanh tìm kiếm, nhập “VLOOKUP” hoặc “VLOOKUP” tùy theo ngôn ngữ chương trình;
Thiết lập một danh mục "Danh sách đầy đủ";
Bấm vào "Tìm".

Khi tìm kiếm một công thức, bạn sẽ thấy nó trong danh sách. Bằng cách nhấp vào một phần tử, công thức của nó sẽ được hiển thị ở cuối màn hình. Tên của hàm được chỉ định bên ngoài dấu ngoặc và các tham số của nó nằm trong dấu ngoặc. Bên trong mỗi công thức tham số riêngđược viết ở góc<>cái ghim. Hình thức chung Mô tả cho VLOOKUP trông như thế này:

Hình 3 - danh sách các tham số

Chúng ta hãy xem xét kỹ hơn từng giá trị được mô tả trong ngoặc:

<ЧТО>- yếu tố đầu tiên. Thay vào đó, bạn cần nhập chính xác giá trị muốn tìm vào bảng. Bạn cũng có thể nhập địa chỉ ô vào bảng;
<НОМЕР_СТОЛБЦА>- ở đây bạn cần in số cột trong đó dữ liệu sẽ được sắp xếp.
<ГДЕ>- ở đây người dùng xác định số lượng ô, chỉ định kích thước của chúng trong biểu mẫu mảng hai chiều dữ liệu. Cột đầu tiên là phần tử “CÁI GÌ”;
<ОТСОРТИРОВАНО>- Phần tử này của hàm VLOOKUP có nhiệm vụ sắp xếp cột đầu tiên theo thứ tự tăng dần (cột đầu tiên dành cho “WHERE”). Kết quả của việc sắp xếp thành công, giá trị trở thành đúng (một). Nếu có bất kỳ sự không chính xác hoặc lỗi nào xảy ra khi nhập tham số, giá trị sắp xếp sai (không) sẽ xuất hiện. Điều đáng chú ý là trong nhiệm vụ VLOOKUP<ОТСОРТИРОВАНО>có thể được bỏ qua, trong trường hợp đó giá trị mặc định của nó được chấp nhận là đúng.

Cách VLOOKUP hoạt động. Ví dụ hữu ích

Để hiểu rõ hơn về nguyên lý hoạt động của VLOOKUP chúng ta chuyển sang xem xét ví dụ cụ thể. Hãy lấy bảng đơn giản nhất với hai cột. Hãy để nó cho biết mã và tên của sản phẩm.

Sau khi điền vào bảng, nhấp vào ô trống và kết quả VLOOKUP. Bấm vào tab Công thức và chọn VLOOKUP. Sau đó nhập mọi thứ thông số bắt buộc vào cửa sổ như trong Hình 3. Xác nhận hành động. Ô sẽ hiển thị kết quả của lệnh.

Hình 4 – ví dụ về tìm kiếm trong một bảng đơn giản

Trong hình trên, các ô màu biểu thị giá trị của sản phẩm. Nếu bạn không nhập giá trị để sắp xếp, hàm sẽ tự động coi giá trị đó là một. Tiếp theo, chương trình “nghĩ” rằng các phần tử của cột đầu tiên trong bảng của bạn theo thứ tự tăng dần từ trên xuống dưới. Nhờ đó, quy trình tìm kiếm sẽ chỉ dừng lại khi đạt đến một dòng có giá trị, số lượng của dòng đó đã cao hơn đối tượng tìm kiếm.

Hãy xem một ví dụ khác về việc sử dụng một hàm thường gặp trong công việc thực sự với bảng giá và bảng tên sản phẩm. Trong trường hợp người dùng gõ phần tử cuối cùng trong ngoặc bằng 0, : Tùy chọn này kiểm tra cột đầu tiên trong phạm vi mảng đã cho. Việc tìm kiếm sẽ tự động dừng ngay khi tìm thấy thông số trùng khớp giữa thông số “CÁI GÌ” và tên sản phẩm.

Nếu bảng không có mã định danh bạn đã nhập cho tên sản phẩm, tìm kiếm VLOOKUP sẽ trả về N/A, nghĩa là không có mục nào cho số đã cho.

Hình 5 – ví dụ thứ hai cho VLOOKUP

Khi nào nên sử dụng VLOOKUP?

Hai tùy chọn sử dụng VLOOKUP được mô tả ở trên.

Biến thể đầu tiên VLOOKUP phù hợp với trường hợp sau:

Khi cần chia các giá trị của đối tượng bộ xử lý bảng cho các phạm vi của nó;
Đối với những bảng trong đó tham số WHERE có thể chứa nhiều giá trị giống nhau. Trong trường hợp này, công thức sẽ chỉ trả về giá trị ở dòng cuối cùng của mảng;
Khi nào cần tìm kiếm các giá trị Hơn nữa, có thể được chứa trong cột đầu tiên. Vì vậy bạn sẽ tìm thấy dòng cuối cùng bảng gần như ngay lập tức.

Cách viết đầu tiên VLOOKUP không thể tìm thấy một phần tử trừ khi tìm thấy giá trị nhỏ hơn hoặc bằng giá trị tìm kiếm . Chỉ "N/A" sẽ được trả về trong ô kết quả.

Tùy chọn thứ hai cho VLOOKUP (chỉ định "0" để sắp xếp) được sử dụng cho bàn lớn, tại đó họ gặp nhau cùng tên cho một số ô. VLOOKUP giúp bạn dễ dàng thao tác với dữ liệu vì nó trả về hàng đầu tiên được tìm thấy.

TRONG đời thực Tùy chọn này được sử dụng khi bạn cần tìm kiếm trong một phạm vi nhất định - nó không nhất thiết phải tương ứng với toàn bộ kích thước bảng. Trong các đối tượng trang tính có các loại giá trị khác nhau, VLOOKUP giúp tìm chuỗi văn bản.

Hình 6 – ví dụ về tìm kiếm giá trị văn bản

VLOOKUP hữu ích khi bạn cần xóa nhiều khoảng trống thừa. Hàm này nhanh chóng tìm thấy tất cả các tên có dấu cách và bạn có thể xóa chúng nhanh chóng. Ví dụ:

Hình 7 – VLOOKUP khi xóa khoảng trắng

Khi làm việc với tài liệu bảng tính, có thể có cấu trúc dữ liệu khá khác nhau, người dùng thường phải đối mặt với nhu cầu xác định dữ liệu từ bảng này sang bảng khác. Nói cách khác, cần tìm dữ liệu trong một bảng (ví dụ bảng giá) và chuyển sang bảng khác (sản phẩm bán cho Thời kỳ nhất định thời gian, giá và tên được chỉ định chính xác trong bảng đầu tiên). Để không khiến bạn, độc giả thân mến, rơi vào sự nhầm lẫn đặc biệt giữa từ ngữ và nước sốt ngụ ngôn, chúng ta hãy xem xét ví dụ thực tế Hàm vlookup hoạt động như thế nào trong excel.

Hôm nay chúng ta sẽ xem xét:

Giới thiệu: Cú pháp

Chức năng này có bốn tham số:

“CÁI GÌ” là một giá trị hiếm khi được sử dụng để chỉ ra đối tượng tìm kiếm hoặc một tham chiếu cụ thể đến một ô có giá trị được tìm kiếm. Tham số sau có thể dễ dàng được coi là tham số được sử dụng nhiều nhất khi làm việc với hàm VLOOKUP.

“WHERE” là liên kết đến một phạm vi ô (mảng hai chiều), trong cột đầu tiên sẽ tìm kiếm giá trị của tham số “WHAT”.

“SỐ CỘT” - số cột trong phạm vi mà giá trị sẽ được trả về;

"SẮP XẾP" - rất tham số quan trọng, vì tính đúng đắn của điều kiện đã chọn: “1-TRUE” - “2-FALSE” sẽ phụ thuộc vào kết quả cuối cùng hoạt động của hàm VLOOKUP được áp dụng (dữ liệu được lấy mẫu liên quan đến câu hỏi: cột đầu tiên của dải ô có được sắp xếp theo thứ tự tăng dần không?<ГДЕ>). Điều đáng chú ý là nếu bạn bỏ qua quá trình thiết lập giá trị mong muốn, tham số sẽ tự động chấp nhận điều kiện “1-TRUE”.

Vì vậy, chúng tôi hy vọng chúng tôi đã giải quyết được vấn đề này. Bây giờ chúng ta hãy chuyển sang phần thực tế của câu chuyện, có thể nói là phần được mong đợi nhất.

Một ví dụ đơn giản: chúng tôi kết hợp dữ liệu từ hai bảng

Vì vậy, đây là một ví dụ cổ điển: bảng “Các mặt hàng đã bán”, trong đó bạn cần thay thế các giá trị từ bảng “Bảng giá”.

Chúng tôi đứng ở ô “D6”.

Gọi cửa sổ dịch vụ của bảng điều khiển “fx” bằng cách nhấn phím tương ứng và trong cửa sổ trình hướng dẫn chức năng nhất định, hãy kích hoạt hộp kiểm “Danh mục”.

Chọn mục “Liên kết và mảng”.

Trong hộp chọn hàm, đặt giá trị thành “VLOOKUP”.

Nhấp vào nút "OK" và đi đến bước tiếp theo- nhập các đối số của hàm này.

sử dụng nút trái chuột, nhấp vào ô đầu tiên trong danh sách tên của bạn; trong ví dụ của chúng tôi, hành động này được gán để kích hoạt ô “B6”. Vì vậy, mục “Giá trị được tìm kiếm” tương ứng với giá trị “B6”.

Trong hộp kiểm thứ hai “Bảng”, chúng tôi chỉ ra đối số mà chúng tôi đang tìm kiếm, nghĩa là chúng tôi cho biết chính xác các giá trị mà chúng tôi cần sẽ đến từ đâu: Giữ nút chuột trái và chọn toàn bộ giá danh sách. Hay đúng hơn, phần chính của nó là dữ liệu, tránh làm nổi bật tên cột và tất nhiên là tiêu đề.

Bây giờ, bạn cần chuyển liên kết đến bảng, có thể nói, thành một liên kết tuyệt đối - chọn đối số từ ví dụ “G6:I10” và nhấn phím “F4”.

Kết quả là, chúng tôi thấy rằng liên kết trước đó đã thay đổi: các ký hiệu ban đầu hiện được bao quanh bởi các ký hiệu đô la “$G$6:$I$10”, đây là điều chúng tôi muốn đạt được.

Trường thứ ba của cửa sổ dịch vụ “Số cột” yêu cầu chỉ định số hai (2), vì từ cột thứ hai của bảng đầu tiên, các giá trị cần phải tương quan với dữ liệu trong “tên” bảng đầu tiên .

Và cuối cùng, tham số thứ tư mà chúng ta cần chỉ định là “không” trong cột “Xem khoảng thời gian”. Vì giá trị “1” tương ứng với các tham số số của dữ liệu, nên trong trường hợp của chúng tôi, chúng tôi sử dụng tìm kiếm đối tượng mong muốn, có thể nói, trong hình thức văn bản, vì vậy sự lựa chọn của chúng tôi là hiển nhiên - “không”.

Chà, kết quả của các thao tác của chúng tôi là giá trị xuất hiện trong cột “Giá” của bảng “Các mặt hàng đã bán” đầu tiên - số “10”, tương ứng với giá trị được chỉ định từ bảng thứ hai.

Bây giờ tất cả những gì còn lại là nhập công thức nhân giá với số lượng và tận hưởng kết quả cuối cùng.

Trong ô “E6” chúng ta đặt dấu bằng.

Di chuyển điểm đánh dấu đến vị trí “C6”.

Tiếp theo, nhấn dấu nhân.

Đi đến ô “D6” và nhấn phím “Enter”.

Tất cả những gì chúng ta cần làm để trình soạn thảo Excel hiển thị kết quả cuối cùng của hành động của chúng ta là sao chép công thức bằng cách kéo hai cột cuối cùng (vùng có dữ liệu) từ trên xuống dưới - các giá trị hiện tại sẽ xuất hiện theo các thao tác được thực hiện.

Chỉ vậy thôi - những tính toán chính xác dành cho bạn, bạn đọc thân mến!

Hàm VLOOKUP trong Excel sẽ giúp bạn tìm giá trị trong bảng, ví dụ được mô tả bên dưới trong bài viết.

Trong khi làm việc với chương trình, người dùng thường có nhu cầu tìm kiếm nhanh thông tin trong một bảng và chuyển sang đối tượng trang tính khác.

Hiểu cách hoạt động của VLOOKUP sẽ đơn giản hóa rất nhiều làm việc trong Excel và sẽ giúp bạn hoàn thành nhiệm vụ nhanh hơn.

Cú pháp VLOOKUP trông như thế nào?

Cú pháp của hàm trong Excel là một tập hợp các tham số mà nó có thể được gọi và đặt. Cách ghi tương tự như cách ghi hàm toán học. Nhìn quan điểm đúng tùy chọn có thể được mở bằng cách mở bộ xử lý bảng :

Sử dụng tài liệu đã được tạo hoặc mở một trang trống mới;
Nhấp vào nút “Công thức”, như thể hiện trong hình bên dưới;
Trong thanh tìm kiếm, nhập “VLOOKUP” hoặc “VLOOKUP” tùy theo ngôn ngữ chương trình;
Thiết lập một danh mục "Danh sách đầy đủ";
Bấm vào "Tìm".

Hình 2 – tìm kiếm công thức trong Excel

Khi tìm kiếm một công thức, bạn sẽ thấy nó trong danh sách. Bằng cách nhấp vào một phần tử, công thức của nó sẽ được hiển thị ở cuối màn hình. Tên của hàm được chỉ định bên ngoài dấu ngoặc và các tham số của nó nằm trong dấu ngoặc. Bên trong công thức, mỗi tham số riêng lẻ được viết bằng góc<>cái ghim. Mô tả chung cho VLOOKUP trông như thế này:

Hình 3 - danh sách các tham số

Chúng ta hãy xem xét kỹ hơn từng giá trị được mô tả trong ngoặc:

<ЧТО>- yếu tố đầu tiên. Thay vào đó, bạn cần nhập chính xác giá trị muốn tìm vào bảng. Bạn cũng có thể nhập địa chỉ ô vào bảng;
<НОМЕР_СТОЛБЦА>- ở đây bạn cần in số cột trong đó dữ liệu sẽ được sắp xếp.
<ГДЕ>- ở đây người dùng xác định số lượng ô, chỉ định kích thước của chúng dưới dạng mảng dữ liệu hai chiều. Cột đầu tiên là phần tử “CÁI GÌ”;
<ОТСОРТИРОВАНО>- Phần tử này của hàm VLOOKUP có nhiệm vụ sắp xếp cột đầu tiên theo thứ tự tăng dần (cột đầu tiên dành cho “WHERE”). Kết quả của việc sắp xếp thành công, giá trị trở thành đúng (một). Nếu có bất kỳ sự không chính xác hoặc lỗi nào xảy ra khi nhập tham số, giá trị sắp xếp sai (không) sẽ xuất hiện. Điều đáng chú ý là trong nhiệm vụ VLOOKUP<ОТСОРТИРОВАНО>có thể được bỏ qua, trong trường hợp đó giá trị mặc định của nó được chấp nhận là đúng.

Cách VLOOKUP hoạt động. Ví dụ hữu ích

Để hiểu rõ hơn cách hoạt động của VLOOKUP, hãy chuyển sang xem xét các ví dụ cụ thể. Hãy lấy một bảng đơn giản có hai cột. Hãy để nó cho biết mã và tên của sản phẩm.

Sau khi điền vào bảng, bấm vào ô trống và hãy viết công thức vào đó và kết quả của hàm VLOOKUP. Nhấp vào tab Công thức và chọn VLOOKUP. Sau đó nhập tất cả các tham số cần thiết vào cửa sổ như Hình 3. Xác nhận hành động. Ô sẽ hiển thị kết quả của lệnh.

Hình 4 – ví dụ về tìm kiếm trong một bảng đơn giản

Hãy xem xét một ví dụ khác về cách sử dụng hàm, thường gặp khi làm việc thực tế với bảng giá và bảng tên sản phẩm. Trong trường hợp người dùng gõ phần tử cuối cùng trong ngoặc đơn bằng 0, Excel hoạt động như thế này:: Tùy chọn này kiểm tra cột đầu tiên trong phạm vi mảng đã cho. Việc tìm kiếm sẽ tự động dừng ngay khi tìm thấy thông số trùng khớp giữa thông số “CÁI GÌ” và tên sản phẩm.

Nếu bảng không có mã định danh bạn đã nhập cho tên sản phẩm, tìm kiếm VLOOKUP sẽ trả về N/A, nghĩa là không có mục nào cho số đã cho.

Hình 5 – ví dụ thứ hai cho VLOOKUP

Khi nào nên sử dụng VLOOKUP?

Hai tùy chọn sử dụng VLOOKUP được mô tả ở trên.

Biến thể đầu tiên VLOOKUP thích hợp cho các trường hợp sau:

Khi cần chia các giá trị của đối tượng bộ xử lý bảng cho các phạm vi của nó;
Đối với những bảng trong đó tham số WHERE có thể chứa nhiều giá trị giống nhau. Trong trường hợp này, công thức sẽ chỉ trả về giá trị ở dòng cuối cùng của mảng;
Khi bạn cần tìm các giá trị lớn hơn những gì có thể chứa trong cột đầu tiên. Bằng cách này, bạn sẽ tìm thấy hàng cuối cùng của bảng gần như ngay lập tức.

Tùy chọn thứ hai cho VLOOKUP (biểu thị “0” để sắp xếp) được sử dụng cho các bảng lớn trong đó các tên giống nhau xuất hiện cho một số ô. VLOOKUP giúp bạn dễ dàng thao tác với dữ liệu vì nó trả về hàng đầu tiên được tìm thấy.

Trong thực tế, tùy chọn này được sử dụng khi bạn cần tìm kiếm trong một phạm vi nhất định - nó không nhất thiết phải tương ứng với toàn bộ kích thước bảng. Trong các đối tượng trang tính chứa các loại giá trị khác nhau, VLOOKUP giúp bạn tìm thấy các chuỗi văn bản.

Hình 6 – ví dụ về tìm kiếm giá trị văn bản

VLOOKUP rất hữu ích khi bạn cần loại bỏ nhiều khoảng trắng thừa. Hàm này nhanh chóng tìm thấy tất cả các tên có dấu cách và bạn có thể xóa chúng nhanh chóng. Ví dụ:

Hình 7 – VLOOKUP khi xóa khoảng trắng

Hiệu suấtVLOOKUP

Hầu hết người dùng không muốn nhập tham số<СОРТИРОВКА> trong khi làm việc với hàm. Tất nhiên, việc nhập số 0 sẽ dễ dàng hơn nhưng việc bỏ qua toán tử sẽ làm chậm quá trình tìm kiếm. Khi làm việc với lượng lớn dữ liệu, Excel có thể quá chậm. Trên các thiết bị cũ hơn, bộ xử lý bảng đôi khi còn bị treo do tìm kiếm VLOOKUP quá chậm.

Nếu có hàng nghìn công thức trên một trang tài liệu của bạn, tốt hơn hết bạn nên sắp xếp cột đầu tiên. Điều này cho phép bạn tăng Tổng hiệu suất tìm kiếm lên tới 400%-500%.

Sự khác biệt về tốc độ thực thi các loại khác nhau VLOOKUP là bất kỳ chương trình máy tính Sẽ dễ dàng hơn nhiều khi làm việc với dữ liệu đã được sắp xếp bằng cách thực hiện tìm kiếm nhị phân. Loại hàm đầu tiên sử dụng tìm kiếm nhị phân, nhưng loại thứ hai thì không, vì việc tối ưu hóa phương pháp chỉ định hàm này vẫn còn thiếu.

Chức năng đồng phục công sở hàm VLOOKUP Excel cho phép bạn tìm dữ liệu được yêu cầu trong bảng đã chỉ định và trong cột đã chỉ định và trả về kết quả là dữ liệu đó. Chức năng này có thể rất hữu ích khi so sánh dữ liệu từ các bảng khác nhau hoặc khi kết hợp thông tin thành một mảng duy nhất để phân tích sâu hơn.

VLOOKUP ( VLOOKUP V. phiên bản tiếng Anh) đã giải mã, giống như xem theo chiều dọc. Chức năng là một trong phổ biến nhất trong Excel. Ví dụ: nó cho phép dễ dàng tìm và so sánh dữ liệu điện thoại của một người hoặc tổ chức từ bảng tra cứu theo tên của người đó. Bạn có thể so sánh giá hàng hóa theo tên của chúng. Những khả năng này và nhiều khả năng khác sẽ được hàm VLOOKUP cung cấp cho bạn. Nó khá đơn giản để sử dụng.

Sử dụng chức năng

Chúng ta hãy xem cấu trúc VLOOKUP và những đối số mà nó sử dụng. Giống như bất kỳ hàm nào khác trong Excel, nó bắt đầu bằng dấu bằng(=). Hơn nữa Tên các hàm và đối số được đặt trong dấu ngoặc đơn.

VLOOKUP chứa 4 đối số.

Hàm tìm kiếm giá trị mong muốn trong ngoài cùng bên trái cột và trả về giá trị trong cùng một dòng từ cột được chỉ định.

Thứ hai - chỉ định chính xác bảng (hoặc phạm vi ô) trong đó tìm kiếm này sẽ được thực hiện.

Phải bao gồm một cột cho câu trả lời, nó nằm ở bên phải cột có giá trị ban đầu.

Đối số cuối cùng– xem theo khoảng thời gian, có thể có 2 giá trị: 0 – NÓI DỐI, 1 — ĐÚNG VẬY. chịu trách nhiệm cho tìm kiếm chính xác(khớp khi nhìn từ trên xuống dưới). Nếu không tìm thấy gì, lỗi N/A (không có dữ liệu) sẽ được trả về. 1 – xấp xỉ.

Để hiểu rõ hơn cách thức hoạt động của hàm, hãy xem xét ví dụ về cách sử dụng VLOOKUP.

Ví dụ về việc sử dụng

Ví dụ đơn giản đầu tiên - có 2 bảng. TRONG mộtđược chỉ ra Các mặt hàng và số nhận dạng (ID) của họ. TRONG thứ hai, bằng cách sử dụng bộ lọc theo ID, chúng tôi muốn có được Tên sản phẩm.

Sau dấu bằng nhập VLOOKUP, sau đó Đi vào Và FXđể nhập các đối số.

Các đối số cũng có thể được nhập vào dòng thích hợp, cách nhau bằng dấu chấm phẩy.

Các giá trị được tìm kiếm sẽ là các giá trị mà chúng ta sẽ tìm kiếm các kết quả trùng khớp. TRONG trong trường hợp này Cái này ô E1.

Vì thứ hai chúng tôi nhấn mạnh lập luận phạm vi bảng.

Hoàn toàn cần thiết sửa chữa(chỉ định ký hiệu đô la hoặc phím F4 cho toàn bộ phạm vi) và thực hiện liên kết tuyệt đối để phạm vi không bị “trượt”, vì trong trường hợp này tên bảng không được chỉ định.

- thứ gì đó cần được trả lại. Ví dụ này yêu cầu trả về cột 2 ( Sản phẩm). Vì tìm kiếm chính xácĐối số thứ 4 – .

Sau khi nhập tất cả các giá trị, nhấn nút ĐƯỢC RỒI.

Bây giờ, khi thay đổi số ID trong bộ lọc, nó sẽ thay đổi tên Các mặt hàng.

Bây giờ hãy nhìn một vi dụ khac.

Bây giờ bạn cần lấy lô cho mỗi tên hàng hóa theo tiêu chí Số lượng.

Ví dụ, đối với bé nhỏ số lượng lô phải từ 100 đến 200, trung bình – 200-300 vân vân.

Giá trị mong muốn trong trường hợp này sẽ là Số lượng, Chọn bảng tiêu chí phạm vi – Lô hàng(sửa F4). Cột số 2, xem tua nhanh thời gian trong trường hợp này phải là 1 (sẽ cho phép bạn nhận được giá trị nhỏ hơn gần nhất với giá trị mong muốn).

Như chúng ta có thể thấy trong bảng kết quả về số lượng, ví dụ: 110, lô được phát hành bởi Melkaya (100 nhỏ gần nhất), v.v. Việc sắp xếp các tiêu chí phải từ nhiều nhất đến ít nhất, nếu không VLOOKUP sẽ không hoạt động.