Trong bài viết này, chúng ta sẽ học cách chọn từ danh sách thả xuống và kéo dữ liệu từ một trang tính khác trong Excel. Đôi khi chúng ta có tập dữ liệu lớn trong một trang. Nhưng chỉ cần một số dữ liệu cụ thể. Do đó, cần kéo dữ liệu từ trang đó sang trang khác. Ở đây, chúng ta sẽ thảo luận 4 phương pháp dễ dàng để chọn từ danh sách thả xuống và kéo dữ liệu từ trang tính khác. Trong các phương pháp này, chúng ta sẽ tạo danh sách thả xuống trước, sau đó dùng các hàm khác nhau để kéo dữ liệu.
Tải xuống Sổ thực hành
Tải sổ thực hành tại đây.
4 cách chọn từ danh sách thả xuống và kéo dữ liệu từ trang tính khác trong Excel
1. Dùng hàm VLOOKUP để chọn từ danh sách thả xuống và kéo dữ liệu từ trang tính khác
Phương pháp này dùng hàm VLOOKUP để chọn từ danh sách thả xuống và kéo dữ liệu từ trang tính khác. Hàm VLOOKUP tìm giá trị ở cột đầu tiên của bảng và trả về giá trị cùng hàng từ cột bạn chỉ định.
Giới thiệu tập dữ liệu
Ở đây, chúng ta có tập dữ liệu chứa doanh số ba tháng khác nhau của một số người bán hàng trong ba trang tính riêng biệt.
- Đây là doanh số Tháng 1 lưu trong trang tên Jan.
- Đây là doanh số Tháng 2 lưu trong trang tên Feb.
- Đây là doanh số Tháng 3 lưu trong trang tên Mar.
BƯỚC 1: Tạo menu thả xuống
Làm theo các bước dưới đây để tạo menu thả xuống.
- Trước tiên, chọn một trang khác và tạo cấu trúc tập dữ liệu. Chúng tôi tạo cấu trúc ở trang tên Hàm VLOOKUP. Viết Tên tháng và Tên trang ở Cột E.
- Thứ hai, để tạo danh sách thả xuống, chọn Ô E3.
- Thứ ba, vào tab Dữ liệu (Data) chọn Xác thực dữ liệu (Data Validation). Cửa sổ Xác thực dữ liệu mở ra.
- Chọn Danh sách (List) từ trường Cho phép (Allow) rồi chọn trường Nguồn (Source).
- Chọn các tùy chọn muốn thêm vào menu thả xuống. Chúng tôi chọn Ô E8 đến E10. Nhấn OK.
- Sau khi nhấn OK, menu thả xuống xuất hiện tại Ô E3. Bạn có thể chọn trang từ menu này.
BƯỚC 2: Kéo dữ liệu từ trang tính khác
Làm theo các bước dưới đây để kéo dữ liệu.
- Chọn Ô C5 và nhập công thức:
=VLOOKUP($B5,INDIRECT("'"&$E$3&"'!$B$5:$C$11"),2,FALSE)- Nhấn Enter.
Ở đây, chúng ta dùng hàm INDIRECT bên trong hàm VLOOKUP.
🔎 Cách công thức hoạt động:
⇒ INDIRECT("'"&$E$3&"'!$B$5:$C$11")
Chúng ta dùng hàm INDIRECT. Hàm này trả về tham chiếu được chỉ định bởi chuỗi văn bản. Nó có một đối số bắt buộc. Ở đây, đối số tham chiếu đến chuỗi văn bản lưu trong Ô E3. Chúng ta lưu tên trang trong Ô E3. Giả sử lưu Jan ở Ô E3. Thì nó trả về phạm vi B5:C11 của trang Jan.
⇒ VLOOKUP($B5,INDIRECT("'"&$E$3&"'!$B$5:$C$11"),2,FALSE)
Công thức này tìm giá trị ở Ô B5 trong mảng bảng của trang Jan. Số chỉ mục cột là 2 và tìm khớp chính xác nên dùng FALSE.
- Cuối cùng, dùng Fill Handle (Tay cài điền) để xem kết quả các ô còn lại.
- Nếu thay đổi Tên tháng bằng menu thả xuống, tập dữ liệu sẽ tự động cập nhật.
- Ta cũng thấy kết quả cập nhật cho Tháng 3 (March).
Đọc thêm: VLOOKUP với danh sách thả xuống trong Excel
2. Chọn từ danh sách thả xuống và kéo dữ liệu từ trang tính khác bằng hàm INDIRECT của Excel
Phương pháp 2 dùng hàm INDIRECT để chọn từ danh sách thả xuống và kéo dữ liệu từ trang tính khác. Dùng tập dữ liệu trước.
BƯỚC 1: Tạo menu thả xuống
Tạo menu thả xuống trước. Theo dõi các bước:
- Ban đầu, chọn trang khác và tạo cấu trúc tập dữ liệu giống trang trước, cũng viết Tên tháng và Tên trang ở Cột E.
- Vào tab Dữ liệu chọn Xác thực dữ liệu. Cửa sổ Xác thực dữ liệu mở ra.
- Chọn Danh sách từ Cho phép rồi chọn Nguồn.
- Chọn tùy chọn cho menu thả xuống. Chọn Ô E8 đến E10 (chứa tên trang). Nhấn OK.
- Cuối cùng, menu thả xuống xuất hiện tại Ô E3.
BƯỚC 2: Kéo dữ liệu từ trang tính khác
Đã tạo menu thả xuống. Bây giờ kéo dữ liệu.
Làm theo các bước:
- Chọn Ô C5 và nhập công thức:
=INDIRECT("'"&$E$3&"'!C6")
Dùng hàm INDIRECT. Hàm trả về tham chiếu do chuỗi văn bản chỉ định. Đối số tham chiếu đến chuỗi trong Ô E3 (tên trang). Giả sử Jan ở Ô E3, nó trả về Ô C6 của trang Jan.
- Nhấn Enter và kéo Fill Handle. Công thức được sao chép cho các ô còn lại.
- Chọn Ô C7 và sửa công thức thành C7 thay vì C6. Nhấn Enter.
- Chọn Ô C8, sửa thành C8. Nhấn Enter.
- Làm tương tự cho các ô còn lại. Kết quả như dưới.
- Thay đổi Tên tháng bằng menu thả xuống, dữ liệu tự động cập nhật.
- Kết quả cập nhật cho Tháng 3 (March).
Đọc thêm: Cách tạo danh sách thả xuống dựa trên công thức trong Excel (4 cách)
Bài đọc tương tự:
- Tạo danh sách thả xuống có thể tìm kiếm trong Excel (2 phương pháp)
- Cách tạo danh sách thả xuống Excel có màu sắc (2 cách)
- Tạo danh sách thả xuống Excel từ Bảng (5 ví dụ)
- Danh sách thả xuống Excel không hoạt động (8 vấn đề và giải pháp)
- Tự động cập nhật danh sách thả xuống trong Excel (3 cách)
3. Chọn từ danh sách thả xuống và trích xuất dữ liệu từ trang tính Excel khác với tùy chọn Xác thực dữ liệu
Phương pháp này dùng tùy chọn Xác thực dữ liệu (Data Validation) từ tab Dữ liệu (Data). Dùng tập dữ liệu mới: có ID, Tên, Giá của một số sản phẩm. Dùng tập dữ liệu này để tạo tập dữ liệu đầy đủ ở trang khác. Trích xuất dữ liệu từ tập dữ liệu Danh sách sản phẩm (Product List).
BƯỚC 1: Chèn menu thả xuống
- Tạo cấu trúc tập dữ liệu, chọn Ô B5.
- Chọn Xác thực dữ liệu từ tab Dữ liệu. Cửa sổ Xác thực dữ liệu mở ra.
- Chọn Danh sách từ Cho phép rồi click vào Nguồn.
- Chọn tùy chọn cho menu thả xuống. Chọn trang Danh sách sản phẩm rồi chọn Ô B5 đến... Nhấn OK.
- Menu thả xuống hiển thị Mã sản phẩm (Product ID) tại Ô B5.
- Làm tương tự cho Ô C5 (chọn Tên sản phẩm từ trang Danh sách sản phẩm).
- Đối với Ô D5, chọn Giá (Price) từ trang Danh sách sản phẩm.
- Cuối cùng, menu thả xuống xuất hiện ở các ô mong muốn của Hàng 5.
BƯỚC 2: Trích xuất dữ liệu từ trang tính khác
- Chọn các ô mong muốn của Hàng 4 & 5.
- Vào tab Chèn (Insert) chọn Bảng (Table).
- Cửa sổ Tạo Bảng hiện ra. Tích Bảng của tôi có tiêu đề nếu có. Nhấn OK.
- Sau khi nhấn OK, kết quả như dưới.
- Chọn Ô D5.
- Nhấn phím Tab. Menu thả xuống tự động thêm vào các ô mong muốn của Hàng 6. Bạn có thể điền dữ liệu cho các hàng bằng các menu này.
- Lặp lại quy trình để chèn menu thả xuống cho mọi hàng và trích xuất dữ liệu.
- Cuối cùng, đổi Định dạng số (Number Format) thành Tiền tệ (Currency) ở Cột D để trình bày đẹp.
Đọc thêm: Cách trích xuất dữ liệu dựa trên lựa chọn danh sách thả xuống trong Excel
4. Chọn từ danh sách thả xuống và trích xuất dữ liệu từ trang tính khác dùng hàm FILTER trong Excel
Phương pháp cuối dùng hàm FILTER để chọn từ danh sách thả xuống và trích xuất dữ liệu từ trang khác. Hàm FILTER lọc phạm vi hoặc mảng. Tập dữ liệu chứa ID, Tên, Số lượng, Giá sản phẩm.
BƯỚC 1: Chuyển tập dữ liệu thành Bảng
- Chọn bất kỳ ô nào của tập dữ liệu (ví dụ Ô B4).
- Vào tab Chèn chọn Bảng.
- Tích Bảng của tôi có tiêu đề trong hộp thoại Tạo Bảng và nhấn OK.
- Tập dữ liệu biến thành bảng như dưới.
- Vào tab Thiết kế Bảng (Table Design) đổi Tên Bảng. Đặt tên Product.
BƯỚC 2: Tạo danh sách duy nhất (Unique List)
- Đi đến trang mới, chọn ô bất kỳ (ví dụ Ô I4).
- Nhập công thức:
=UNIQUE(Product[Product Name])
- Nhấn Enter để thấy giá trị duy nhất của cột Tên sản phẩm.
Dùng hàm UNIQUE trả về giá trị duy nhất từ phạm vi/mảng. Công thức trả về giá trị duy nhất từ cột Tên sản phẩm của bảng Product.
BƯỚC 3: Thêm menu thả xuống
- Đi đến trang chứa danh sách giá trị duy nhất.
- Tạo tiêu đề bảng Product ở Hàng 4.
- Chọn Ô G5.
- Chọn Xác thực dữ liệu từ tab Dữ liệu. Cửa sổ Xác thực dữ liệu mở ra.
- Chọn Danh sách ở Cho phép rồi chọn Nguồn.
- Chọn tùy chọn cho menu thả xuống (tên sản phẩm). Chọn Ô I4 đến I6. Nhấn OK.
- Menu thả xuống xuất hiện tại Ô G5.
BƯỚC 4: Chèn dữ liệu từ trang tính khác
- Chọn Ô B5 và nhập công thức:
=FILTER(Product,Product[Product Name]=G5)
Dùng hàm FILTER lọc phạm vi. Tham số đầu là bảng Product. Tham số hai yêu cầu Tên sản phẩm khớp với Ô G5.
- Nhấn Enter để xem kết quả.
- Cuối cùng, thay đổi tên sản phẩm bằng menu thả xuống, tập dữ liệu tự động cập nhật.
Đọc thêm: Tạo bộ lọc Excel dùng danh sách thả xuống dựa trên giá trị ô
Lưu ý quan trọng
Có những điều cần nhớ khi chọn từ menu thả xuống và kéo dữ liệu từ trang khác:
- Ở Phương pháp 1, dùng dấu ngoặc kép cẩn thận khi nhập công thức. Cẩn thận với số chỉ mục cột trong VLOOKUP.
- Ở Phương pháp 2, công thức không tự cập nhật khi dùng Fill Handle. Sửa công thức từng hàng. Có thể dùng Phương pháp 1 thay thế.
- Phương pháp 3 chủ yếu dùng để tạo tập dữ liệu mới ở trang khác.
- Phương pháp 4 chỉ áp dụng cho Excel 365. Phiên bản cũ dùng các phương pháp trên.
Kết luận
Chúng tôi đã trình bày 4 cách dễ dàng chọn từ danh sách thả xuống và kéo dữ liệu từ trang tính khác trong Excel. Hy vọng giúp ích cho bạn. Tệp thực hành có ở đầu bài. Tải về và tập làm. Nếu có góp ý hoặc thắc mắc, hãy để lại bình luận.
Các bài viết liên quan
- Cách thêm mục vào danh sách thả xuống trong Excel (5 phương pháp)
- VBA chọn giá trị từ danh sách thả xuống trong Excel (2 phương pháp)
- Cách tạo danh sách thả xuống phụ thuộc có khoảng trắng trong Excel
- Giá trị duy nhất trong danh sách thả xuống với VBA trong Excel (Hướng dẫn hoàn chỉnh)
- Cách xóa trùng lặp khỏi danh sách thả xuống trong Excel (4 phương pháp)