excel online - excel vlookup



truy Lượt Xem:64

Hướng dẫn tạo một Vlookup Offset trong excel

Trong trường hợp bạn có một bảng chứa cùng một ID cho nhiều - hoặc ô trống. Chúng tôi khuyên bạn nên sắp xếp lại dữ liệu của mình
 
Để thực hiện sửa đổi này, chúng ta sẽ sử dụng 3 hàm, INDEX, MATCH và OFFSET.
 
Chúng tôi có một bảng (cột A: D) với danh sách bán hàng cho mỗi sản phẩm.
 
Chúng tôi muốn tự động sắp xếp lại các giá trị của chúng tôi trong 2 bảng khác nhau. Vì vậy, chúng ta sẽ tạo các công thức giữa 2 bảng final và bảng ban đầu.
 
Hướng dẫn tạo một Vlookup Offset trong excel
Nhìn! Có một vấn đề BIIIIG. Trong cột A, nhiều ngày bị thiếu.
 
Ngay cả khi chúng tôi sao chép ngày cho các ô trống, chúng tôi vẫn gặp sự cố. Chúng tôi không thể chỉ ra dữ liệu cho các sản phẩm B, C và D.

VLOOKUP hoặc INDEX

Ý tưởng là để tìm vị trí của các ngày (ID của chúng tôi).
 
Sau đó, chúng ta sẽ đọc giá trị tiếp theo bằng cách dịch chuyển 1, 2 hoặc 3 hàng. Hàm VLOOKUP không thuận tiện ở đây. VLOOKUP là hoàn hảo để lấy các giá trị trên cùng một hàng nhưng không thực hiện bù trừ.
 
Do đó, chúng ta phải sử dụng  hàm INDEX để xây dựng nghiên cứu của chúng ta vì hàm INDEX trả về một dữ liệu trong một phạm vi.
 
Đối với công thức trong G2, chúng tôi sẽ viết công thức sau để trả lại số lượng mặt hàng đã bán vào tháng 1 năm 2014 cho sản phẩm A.
 
= INDEX ($ A $ 2: $ D $ 49, MATCH ($ F2, $ A $ 2: $ A $ 49,0), 3)
 
Công thức có thể được hiểu như sau
 
Chúng tôi tập trung vào dữ liệu A2: D49 (dữ liệu không có tiêu đề )
 
Sau đó, chúng ta tìm hàng tương ứng với ngày chúng ta quan tâm ( hàm MATCH ).
 
Để kết thúc, chúng tôi chỉ ra giá trị 3 (chỉ mục cột để trả về)
 
huong-dan-tao-mot-vlookup-offset-trong-excel2

Trích xuất doanh thu

Để trả lại doanh thu cho sản phẩm A, chúng tôi chỉ phải thay đổi giá trị của cột và thay thế nó bằng 4
 
= INDEX ($ A $ 2: $ D $ 49, MATCH ($ F2, $ A $ 2: $ A $ 49,0), 4 )
 
Trích xuất doanh thu

Tạo bù đắp

Bởi vì hàm INDEX trả về một phạm vi dữ liệu (và không phải là một giá trị như VLOOKUP), chúng tôi sẽ bao gồm trong 2 công thức trước đó hàm OFFSET.
 
= OFFSET (tham chiếu, số hàng, số cột)
 
Hàm OFFSET trả về một dữ liệu dựa trên tham chiếu của một ô pivot.
 
Trong ví dụ của chúng tôi, nếu chúng ta muốn trả về số lượng sản phẩm B, chúng ta cần phải dịch chuyển từ một ô xuống so với tìm kiếm trước đó. Công thức cho sản phẩm B là:
 
= OFFSET (INDEX ($ A $ 2: $ D $ 49, MATCH ($ F2, $ A $ 2: $ A $ 49,0), 3), 1,0 )
 
Hàm OFFSET
 
Và cứ thế cho các tế bào khác. Nếu bạn bấm đúp vào một trong các ô bên dưới, bạn sẽ hiển thị các công thức trong các ô tương ứng
 

Tags:
Bình luận

Bình luận

Các bài viết mới

Các tin cũ hơn