Trong MS Excel, đôi khi chúng ta có thể cần xác định hoặc tìm các giá trị dựa trên một số tiêu chí hoặc điều kiện từ danh sách. Giả sử chúng ta có một kế hoạch nhiệm vụ với tên của người tương ứng, người giám sát từng nhiệm vụ. Và bây giờ chúng ta cần liệt kê tên của tất cả các nhiệm vụ theo hàng dựa trên người đã cho. Bằng cách này, Excel cung cấp nhiều cách khác nhau để điền danh sách dựa trên giá trị ô. Trong bài viết này, chúng ta sẽ xem cách chúng ta có thể điền danh sách dựa trên giá trị ô trong Excel.
Tải xuống Sách bài tập Thực hành
6 cách điền danh sách dựa trên giá trị ô trong Excel
1. Tự động điền danh sách dựa trên giá trị ô
Hãy liệt kê dự án với tên của các nhân viên dự án. Trong mỗi dự án, tên của nhân viên được chỉ định ở định dạng này “ Project_Number_Name_Serial ”. Vì vậy, nhiệm vụ của chúng tôi là tìm tất cả tên của nhân viên sử dụng dự án.
Bước 1: Nhập công thức sau vào ô D17 và nhấn Enter
=IFERROR(INDEX($B$3:$D$11,ROW(B2:D11),MATCH($C$16,$B$3:$D$3,0)),"") Giải thích công thức
- Đây MATCH ($ C $ 16, $ B $ 3:$ D $ 3,0) phần này khớp với Tên dự án đã nhập với Tập dữ liệu và nó chỉ được coi là khớp chính xác.
- ROW (B2:D11) đang đếm số hàng của tập dữ liệu.
- CHỈ SỐ ($ B $ 3:$ D $ 11, ROW (B2:D11), TRẬN ĐẤU ($ C $ 16, $ B $ 3:$ D $ 3,0)) phần này của công thức là tìm tên công nhân dự án phù hợp. Nếu dữ liệu không được tìm thấy trong tập dữ liệu đã cho thì nó sẽ bị lỗi #NA.
- Cuối cùng, IFERROR là để xử lý bất kỳ loại lỗi nào.
Bước 2 :Bây giờ từ danh sách thả xuống, hãy chọn bất kỳ tên dự án nào
Bước 3: Tất cả tên của công nhân sẽ được hiển thị
2. Điền các hàng dựa trên giá trị ô cụ thể với công thức
Hãy xem cách chúng ta có thể tìm kiếm tên công nhân theo một cách tiếp cận khác. Trong tập dữ liệu, một người có thể được chỉ định cho nhiều dự án. Bây giờ nhiệm vụ của chúng ta là tìm ra tên dự án bằng cách sử dụng tên của công nhân. Ở đây, tập dữ liệu chính sẽ như thế này:
Bước 1: Nhập công thức sau vào ô G6 và nhấn ENTER chìa khóa
=FILTER(B4:B16, G5=C4:C16) Giải thích công thức
- Trong FILTER hàm, B4:B16 đây là phạm vi mà chúng tôi sẽ trích xuất dữ liệu.
- G5 trong ô này sẽ cung cấp tên đầu vào và sẽ so sánh với phạm vi tên C4:C16
- Để khám phá thêm về chức năng FILTER, bạn có thể xem liên kết này
Bước 2: Bây giờ, hãy nhập bất kỳ tên nào vào ô G5 và nhấn Enter
3. Chặn các thay đổi trong trình đơn thả xuống đầu tiên
Giả sử chúng tôi có nhiều danh sách các mặt hàng thực phẩm khác nhau. Mỗi danh sách khác với những danh sách khác và mặt hàng thực phẩm cụ thể phải nằm trong danh sách hợp lệ
Trong một bảng tính khác, chúng tôi sẽ chọn các mặt hàng thực phẩm theo loại của chúng.
Mối quan tâm của chúng tôi là nếu chúng tôi chọn bất kỳ loại thực phẩm nào trong cột B thì trong cột C (Mặt hàng) sẽ chỉ có các mặt hàng nằm trong danh sách hoặc loại thực phẩm đó.
Bước 1: Chọn các ô Mặt hàng thực phẩm và mở Xác thực dữ liệu
Bước 2: Sau đó trong Nguồn viết công thức sau
=IF(B4="",Foods, INDIRECT("FakeRange"))
Bước 3: Một cảnh báo sẽ bật lên. Nhấp vào nút Có nút
Bước 4: Bây giờ hãy chọn Loại thực phẩm và sau đó chọn các mặt hàng
Bước 5: Khi bạn đã nhập Loại thực phẩm và Mặt hàng, bạn không thể thay đổi bất kỳ mặt hàng thực phẩm nào. Vì vậy, không có khả năng kết hợp lỗi
4. Tạo một danh sách duy nhất trong Excel dựa trên Tiêu chí
Về việc tìm kiếm danh sách giá trị duy nhất, Excel cung cấp nhiều cách khác nhau. Hãy coi cùng một tập dữ liệu như phương pháp 2 với các giá trị trùng lặp. Bây giờ mục tiêu của chúng tôi là tìm ra danh sách duy nhất bằng cách sử dụng các công thức.
Bước 1: Nhập công thức sau vào ô G6
=UNIQUE(FILTER(B4:B22,C4:C22=G5))
- BỘ LỌC (B4:B22, C4:C22 =G5) chức năng này giống như phương pháp 2. Nó trích xuất tất cả các tên phù hợp từ tập dữ liệu. Nếu có bất kỳ kết quả trùng lặp nào, thì FILTER hàm cũng đếm chúng.
- Để xóa các giá trị trùng lặp được trả về bởi FILTER , chúng tôi đã sử dụng hàm UNIQUE hàm số. Hàm này sẽ loại bỏ tất cả các giá trị trùng lặp khỏi dữ liệu đã khớp. Để khám phá thêm về chức năng này, bạn có thể truy cập liên kết này
Bước 2: Bây giờ, hãy nhập bất kỳ Tên nào trong ô G5 và nhấn Enter
5. Trích xuất tất cả các hàng từ danh sách đáp ứng tiêu chí trong một cột bằng cách sử dụng công thức mảng
Hãy có tập dữ liệu sản phẩm với ID của chúng , Thương hiệu , Mô hình và Đơn giá . Bây giờ, nhiệm vụ của chúng ta là tìm ra những hàng nơi Tên thương hiệu sẽ được khớp với Tên thương hiệu đã nhập của chúng ta trong ô H5 và H7 .
Bước 1 :Nhập công thức sau vào ô B19 và nhấn CTRL + SHIFT + ENTER và sao chép công thức trong toàn bộ bảng.
=INDEX($B$4:$E$15, SMALL(IF(COUNTIF($H$5:$H$7,$C$4:$C$15), MATCH(ROW($B$4:$E$15), ROW($B$4:$E$15)), ""), ROWS(B19:$B$19)), COLUMNS($B$3:B3
Bước 2: Bây giờ, hãy nhập tên vào các ô H5 và H7 và nhấn Enter
6. Tạo danh sách thả xuống phụ thuộc trong Excel
Trong MS Excel, danh sách thả xuống là một tính năng hữu ích khi chúng tôi thực hiện biểu mẫu nhập dữ liệu hoặc Bảng điều khiển Excel.
Nó hiển thị danh sách các mục dưới dạng menu thả xuống trong một ô và người dùng có thể chọn từ menu thả xuống. Điều này có thể hữu ích khi bạn có danh sách tên, sản phẩm hoặc khu vực mà chúng tôi thường cần nhập vào một nhóm ô.
Giả sử chúng ta có ba danh sách thực phẩm khác nhau, bây giờ chúng ta sẽ tạo Danh sách thả xuống phụ thuộc trong Excel bằng cách sử dụng các danh sách đó.
Bước 1: Mở Xác thực dữ liệu tùy chọn
Bước 2: Trong Xác thực dữ liệu cửa sổ, chọn Cho phép dưới dạng Danh sách và Chọn Nguồn như bên dưới
Bước 3: Bạn sẽ tìm thấy một Trình đơn thả xuống Liệt kê trong cột Loại thực phẩm
Bước 4: Bây giờ chọn tất cả các tập dữ liệu và nhấp vào Tạo từ lựa chọn trong tùy chọn Công thức tab
Bước 5: Một cửa sổ bật lên sẽ xuất hiện. Nhấp vào Hàng trên cùng rồi nhấn nút Ok nút
Bước 6: Bây giờ chuyển đến ô D14 và mở Xác thực dữ liệu . Đảm bảo rằng Cho phép được đặt là Danh sách và sau đó viết công thức sau trong Nguồn . Cuối cùng, nhấn OK nút
=INDIRECT(B14)
Bước 7: Một cảnh báo sẽ bật lên. Nhấn vào nút Có nút
Bước 8: Bây giờ, hãy chọn bất kỳ Loại Thực phẩm nào từ danh sách Thả xuống đầu tiên và tìm danh sách Các mặt hàng liên kết trong danh sách Thả xuống khác
Bước 8: Đầu ra cuối cùng sẽ như thế này:
Những điều cần nhớ
| Các lỗi thường gặp | Khi chúng hiển thị |
|---|---|
| # GIÁ TRỊ! Lỗi trong FILTER | Đối số bao gồm phải có kích thước tương thích với đối số mảng, nếu không, FILTER sẽ trả về #VALUE! |
| # NA! Lỗi | Nếu công thức không tìm thấy bất kỳ dữ liệu nào từ tập dữ liệu, thì nó sẽ trả về lỗi này. Chúng tôi cần sự trợ giúp của IFERROR để xử lý lỗi này. |
| Đặt tên trong danh sách | Về mặt đặt tên của danh sách, chúng ta không thể sử dụng bất kỳ khoảng trắng nào. Nếu có khoảng trắng trong tên thì chúng ta có thể sử dụng “ _ ”. |
Kết luận
Đây là một số cách để điền danh sách dựa trên giá trị ô trong Excel. Tôi đã hiển thị tất cả các phương pháp với các ví dụ tương ứng của chúng nhưng có thể có nhiều lần lặp khác. Tôi cũng đã thảo luận về các nguyên tắc cơ bản của các chức năng được sử dụng. Nếu bạn có bất kỳ phương pháp nào khác để đạt được điều này, vui lòng chia sẻ với chúng tôi.
Bài đọc thêm
- Tự động chuyển dữ liệu từ một trang tính Excel sang một trang tính khác
- Cách tự động điền từ một trang tính khác trong Excel
- Cách điền dữ liệu xuống hàng cuối cùng trong Excel (3 phương pháp nhanh)