Chọn từ thả xuống và kéo dữ liệu từ trang tính khác nhau trong Microsoft Excel
Trong bài viết này, chúng ta sẽ tìm hiểu Cách sử dụng hàm … trong Excel.
Danh sách thả xuống trong Xác thực dữ liệu là gì?
Xác thực dữ liệu là một tính năng của Excel 2016 có mục đích hạn chế những gì người dùng có thể nhập vào một ô. Điều cần thiết là phải tạo danh sách thả xuống hoặc hộp tổ hợp có chứa các tùy chọn được xác định trước để hạn chế lỗi người dùng và cho phép nhập dữ liệu nhất quán hơn.
Giả sử bạn là một giáo viên. Bạn đã chuẩn bị điểm danh của học sinh trong một sổ làm việc. Điểm danh của mỗi tháng nằm trong một trang tính khác nhau của tháng đó.
Trong trang tính tổng thể, bạn muốn đặt một hàm VLOOKUP để kiểm tra xem học sinh đó có mặt hay vắng mặt trong tháng đó. Sẽ rất đơn giản nếu dữ liệu của bạn nằm trên cùng một trang tính, nhưng không phải vậy. Nó nằm trong các trang tính khác nhau. Nhưng điều đó không có nghĩa là chúng ta không thể lấy dữ liệu từ một trang tính khác trong excel. Chúng tôi có thể và sẽ.
Trong tháng, bạn đã tạo một danh sách thả xuống trong ô C1. Nó chứa một danh sách các tháng. Bây giờ bạn muốn hiển thị vắng mặt hoặc có mặt dựa trên tháng đã chọn trong Ô C1. Chúng ta hãy chỉ xem công thức chung trước.
Công thức chung cho hàm VLOOKUP từ nhiều trang tính:
|
=VLOOKUP(lookupValue,INDIRECT(“”&cell that contains name of month&”!range”),col_index_no,0) |
Đối với ví dụ này, tôi tham dự các trang tính “Jan”, “Feb” và “Mar” trong cùng một phạm vi A2: C11.
Bây giờ tôi đã chuẩn bị một trang tính tổng thể.
Trong ô C4, đặt công thức này và kéo nó xuống.
|
=VLOOKUP(B4,INDIRECT(“”&$C$1&”!B2:C11″),2,0) |
Bây giờ, bất cứ khi nào bạn thay đổi tên của tháng trong ô C1, excel sẽ lấy giá trị từ một trang tính khác (từ trang tính của tháng đó, nếu nó tồn tại).
Giải thích
Chúng tôi đã sử dụng hàm Gián tiếp trong Excel để tìm nạp giá trị từ một trang tính khác.
INDIRECT thay đổi văn bản thành tham chiếu. Chúng tôi đã sử dụng INDIRECT để tham chiếu các trang tính khác trong excel.
Ví dụ: nếu bạn viết INDIRECT (“sheet2: A2”) trong a1 trên sheet1. Nó sẽ kéo giá trị từ sheet2! A2 trong sheet1: A1. Nếu bạn viết = VLOOKUP (“abc”, INDIRECT (“sheet2! A2: B100”), 2,0) bất kỳ trang tính nào, hàm VLOOKUP sẽ tìm kiếm “abc” trong phạm vi A2: B100 trên trang tính2.
INDIRECT (“” & $ C $ 1 & “! B2: C11”): Ở đây chúng tôi muốn tên trang tính thay đổi, đó là lý do tại sao chúng tôi viết nó như thế này. Nếu Ô C1 chứa “Jan”, nó sẽ dịch sang INDIRECT (“Jan! B2: C11”), sau đó sẽ dịch sang Jan! B2: C11 cho mảng bảng VLOOKUP. Nếu C1 có Feb, nó sẽ dịch sang INDIRECT (“Feb! B2: C11”), v.v.
Sau đó, VLOOKUP đã thực hiện công việc của mình.
VLOOKUP (B4, INDIRECT (“” & $ C $ 1 & “! B2: C11”), 2,0): bây giờ kể từ khi Indirect cung cấp mảng bảng, hàm VLOOKUP chỉ cần kéo dữ liệu từ phạm vi đó một cách dễ dàng.
Trong bài này, chúng ta sẽ tìm hiểu cách chỉnh sửa danh sách sổ xuống trong excel. Để thực hiện việc này, chúng tôi sẽ sử dụng Trình quản lý tên và Xác thực dữ liệu. Hãy hiểu điều này bằng cách lấy một ví dụ.
Chúng tôi có một số danh sách ở đây như hình dưới đây.
Đầu tiên, chúng ta cần tạo một danh sách thả xuống cho Danh mục chính và sau đó chúng ta sẽ chuyển sang Danh mục phụ.
Chọn Công thức> Trình quản lý Tên trong Tên đã xác định HOẶC sử dụng phím tắt Ctrl F3 để mở Trình quản lý tên, nơi chúng tôi sẽ giữ danh sách của mảng với tên của chúng để chúng tôi có thể gọi chúng bằng tên đó bất cứ khi nào cần thiết.
Nhấp vào Mới để tạo. Tại đây Tên sẽ là Tháng và trong tùy chọn Tham khảo nhập danh sách dưới Tháng như hình bên dưới.
Chúng ta sẽ làm tương tự trong Week_Days và nó sẽ hiển thị như
Sau đó nhấp vào Xác thực dữ liệu dưới thanh Dữ liệu. Tùy chọn chọn danh sách là Cho phép và chọn các ô cho tên danh mục chính, trong trường hợp này là ô B2 và C2 là “Tháng” và “Ngày trong tuần”
Như chúng ta có thể thấy một danh sách thả xuống được tạo yêu cầu người dùng chọn từ tùy chọn đã cho.
Bây giờ hãy chọn ô trong Danh mục con và chỉ cần viết công thức trong Xác thực dữ liệu và nhấp vào OK.
Công thức:
|
=INDIRECT(E4) |
Kết quả hiển thị như thế này
Nếu tôi không muốn Ngày và Ngày trong tuần. Thay vào đó, tôi muốn Trái cây_Name và rau_Name. Chúng tôi chỉ cần chỉnh sửa danh sách Trình quản lý tên của mình.
Nhấn Ctrl + F3 để mở Trình quản lý tên và xóa danh sách đã được chèn và thêm danh sách mới, tức là Tên_Rau và Rau_công.
Bây giờ chọn ô trong Sub_category như thể hiện trong ảnh chụp nhanh bên dưới.
Thay vì ô Month và Week_Days, chúng ta sẽ sử dụng Fruits_Name và Vegetables_Name trong Data Validation và nhấp vào OK
Như bạn có thể thấy danh sách mới được thêm vào đây.
Đây là cách chúng ta có thể chỉnh sửa trong danh sách thả xuống và thay đổi lựa chọn danh sách.
Hy vọng bài viết này về cách Chọn Từ Thả xuống và Kéo Dữ liệu Từ Các Trang tính khác nhau trong Microsoft Excel là giải thích. Tìm thêm các bài viết về danh sách thả xuống và các công cụ Excel liên quan tại đây. Nếu bạn thích blog của chúng tôi, hãy chia sẻ nó với bạn bè của bạn trên Facebook. Và bạn cũng có thể theo dõi chúng tôi trên Twitter và Facebook. Chúng tôi rất muốn nghe ý kiến của bạn, hãy cho chúng tôi biết cách chúng tôi có thể cải thiện, bổ sung hoặc đổi mới công việc của mình và làm cho nó tốt hơn cho bạn. Viết thư cho chúng tôi tại [email protected].