Ai lười quá hoặc không có thời gian đọc - xem video. Chi tiết và sắc thái có trong văn bản dưới đây.
Công thức của vấn đề
Vì vậy, chúng tôi có hai bảng - đặt bàn и danh sách giá:
Nhiệm vụ là thay thế giá từ bảng giá thành bảng đơn hàng tự động, tập trung vào tên sản phẩm để sau này tính giá thành.
Dung dịch
Trong bộ hàm Excel, dưới thể loại Tham chiếu và mảng (Tra cứu và tham khảo) có một chức năng VPR (Vlookup).Hàm này tìm kiếm một giá trị nhất định (trong ví dụ của chúng tôi, đây là từ “Apples”) trong cột ngoài cùng bên trái của bảng được chỉ định (bảng giá) di chuyển từ trên xuống dưới và sau khi tìm thấy nó, sẽ hiển thị nội dung của ô liền kề (23 rúp). Về mặt vật lý, hoạt động của chức năng này có thể được biểu diễn Vì vậy:
Để dễ dàng sử dụng thêm chức năng, hãy thực hiện một việc cùng một lúc - đặt tên riêng cho phạm vi ô trong bảng giá. Để thực hiện việc này, hãy chọn tất cả các ô của bảng giá ngoại trừ “tiêu đề” (G3: H19), chọn từ menu Chèn - Tên - Chỉ định (Chèn - Tên - Xác định) hoặc báo chí CTRL + F3 và nhập bất kỳ tên nào (không có dấu cách) như Giá cả… Bây giờ, trong tương lai, bạn có thể sử dụng tên này để liên kết với bảng giá.
Bây giờ chúng ta sử dụng hàm VPR… Chọn ô nơi nó sẽ được nhập (D3) và mở tab Công thức - Chèn hàm (Công thức - Chèn hàm)… Trong danh mục Tham chiếu và mảng (Tra cứu và Tham khảo) tìm chức năng VPR (Vlookup) và báo chí OK… Một cửa sổ để nhập các đối số cho hàm sẽ xuất hiện:
Lần lượt chúng tôi điền:
- Giá trị mong muốn (Giá trị tra cứu) - tên sản phẩm mà chức năng cần tìm ở cột ngoài cùng bên trái của bảng giá. Trong trường hợp của chúng tôi, từ "Táo" từ ô B3.
- Bàn (Mảng bảng) - một bảng mà từ đó các giá trị mong muốn uXNUMXbuXNUMX được lấy, tức là bảng giá của chúng tôi. Để tham khảo, chúng tôi sử dụng tên riêng của chúng tôi "Giá" đã cung cấp trước đó. Nếu bạn không đặt tên, bạn chỉ có thể chọn bảng, nhưng đừng quên nhấn nút F4để ghim liên kết bằng ký hiệu đô la, bởi vì nếu không, nó sẽ trượt xuống khi sao chép công thức của chúng ta xuống phần còn lại của các ô trong cột D3: D30.
- cột_số (Số chỉ mục cột) - số sê-ri (không phải chữ cái!) Của cột trong bảng giá mà từ đó chúng ta sẽ lấy các giá trị của giá. Cột đầu tiên của bảng giá có tên được đánh số 1, do đó chúng ta cần giá từ cột được đánh số 2.
- khoảng_lần_năm (Tra cứu phạm vi) - chỉ có thể nhập hai giá trị vào trường này: FALSE hoặc TRUE:
- Nếu một giá trị được nhập 0 or nói dối (SAI), thì trên thực tế, điều này có nghĩa là chỉ cho phép tìm kiếm kết hợp chuẩn xác, tức là nếu hàm không tìm thấy mặt hàng không chuẩn được chỉ định trong bảng đơn hàng trong bảng giá (ví dụ: nếu nhập "Dừa"), nó sẽ tạo ra lỗi # N / A (không có dữ liệu).
- Nếu một giá trị được nhập 1 or TRUE (THẬT), thì điều này có nghĩa là bạn không cho phép tìm kiếm chính xác, nhưng trận đấu gần đúng, tức là trong trường hợp “dừa”, hàm sẽ cố gắng tìm một sản phẩm có tên gần giống với “dừa” nhất có thể và trả về giá cho tên này. Trong hầu hết các trường hợp, sự thay thế gần đúng như vậy có thể đánh lừa người dùng bằng cách thay thế giá trị của sản phẩm sai thực tế có ở đó! Vì vậy, đối với hầu hết các vấn đề kinh doanh thực tế, tốt nhất là không nên cho phép tìm kiếm gần đúng. Ngoại lệ là khi chúng tôi đang tìm kiếm số chứ không phải văn bản - ví dụ: khi tính Chiết khấu theo từng bước.
Mọi điều! Nó vẫn còn để nhấn OK và sao chép hàm đã nhập vào toàn bộ cột.
Lỗi # N / A và sự triệt tiêu của chúng
Chức năng VPR (Vlookup) trả về lỗi # N / A (# N / A) nếu một:
- Đã bật tìm kiếm chính xác (đối số Chế độ xem khoảng thời gian = 0) và tên mong muốn không có trong Bàn.
- Tìm kiếm thô bao gồm (Chế độ xem khoảng thời gian = 1), Nhưng Bàn, trong đó tìm kiếm đang diễn ra không được sắp xếp theo thứ tự tăng dần của tên.
- Định dạng của ô chứa giá trị bắt buộc của tên (ví dụ: B3 trong trường hợp của chúng tôi) và định dạng của các ô của cột đầu tiên (F3: F19) của bảng là khác nhau (ví dụ: số và văn bản ). Trường hợp này đặc biệt điển hình khi sử dụng mã số (số tài khoản, số nhận dạng, ngày tháng, v.v.) thay vì tên văn bản. Trong trường hợp này, bạn có thể sử dụng các chức năng Ч и TEXT để chuyển đổi các định dạng dữ liệu. Nó sẽ trông giống như thế này:
= VLOOKUP (TEXT (B3), giá, 0)
Bạn có thể đọc thêm về điều này ở đây.
- Hàm không thể tìm thấy giá trị cần thiết vì mã chứa khoảng trắng hoặc các ký tự không in được (ngắt dòng, v.v.). Trong trường hợp này, bạn có thể sử dụng các hàm văn bản TRIM (CẮT) и IN(LAU DỌN) để loại bỏ chúng:
= VLOOKUP (TRIMSPACES (CLEAN (B3)), giá, 0)
= VLOOKUP (TRIM (CLEAN (B3)); giá; 0)
Để ngăn chặn thông báo lỗi # N / A (# N / A) trong trường hợp hàm không thể tìm thấy kết quả khớp chính xác, bạn có thể sử dụng hàm SỐ PHIẾU (SẾ PHẠM)… Vì vậy, ví dụ: cấu trúc này chặn bất kỳ lỗi nào do VLOOKUP tạo ra và thay thế chúng bằng các số không:
= IFERROR (VLOOKUP (B3, giá, 2, 0), 0)
= IFERROR (VLOOKUP (B3; giá; 2; 0); 0)
PS
Nếu bạn cần trích xuất không phải một giá trị mà toàn bộ tập hợp cùng một lúc (nếu có nhiều giá trị khác nhau), thì bạn sẽ phải sửa đổi với công thức mảng. hoặc sử dụng tính năng XLOOKUP mới từ Office 365.
- Một phiên bản cải tiến của hàm VLOOKUP (VLOOKUP 2).
- Tính nhanh chiết khấu theo bước (phạm vi) bằng cách sử dụng hàm VLOOKUP.
- Cách tạo “VLOOKUP bên trái” bằng hàm INDEX và MATCH
- Cách sử dụng hàm Vlookup để điền vào biểu mẫu với dữ liệu từ danh sách
- Cách lấy ra không phải giá trị đầu tiên mà là tất cả các giá trị từ bảng cùng một lúc
- Các hàm VLOOKUP2 và VLOOKUP3 từ tiện ích bổ sung PLEX