Computer >> Hướng Dẫn Máy Tính >  >> Phần Mềm >> Office

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

Thường cần liệt kê giá trị dựa trên tiêu chí từ danh sách. Ví dụ: Kế hoạch task – Người phụ trách. Cần liệt kê tất cả task của một người. Excel cung cấp nhiều cách populate list dựa trên giá trị ô. Bài viết trình bày 6 cách.

Tải Workbook Thực Hành

6 Cách Populate List Dựa Trên Giá Trị Ô Trong Excel

1. AutoFill List Dựa Trên Giá Trị Ô (INDEX-MATCH)

Dữ liệu: Project_Number_Name_Serial. Nhập Project Name → ra danh sách Worker.

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

Bước 1: Ô D17: =IFERROR(INDEX($B$3:$D$11,ROW(B2:D11),MATCH($C$16,$B$3:$D$3,0)),"") → Enter.

Giải thích: MATCH tìm cột Project → ROW đếm hàng → INDEX lấy tên worker → IFERROR xử lý lỗi.

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

Bước 2-3: Chọn Project từ drop-down → hiện tất cả worker.

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel 6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

2. Populate Rows Theo Giá Trị Ô Với FILTER (Excel 365)

Ngược lại: Nhập Worker Name → ra Project Name.

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

Bước 1: Ô G6: =FILTER(B4:B16, G5=C4:C16).

Giải thích: FILTER(lấy cột B, điều kiện G5= cột C).

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

Bước 2: Nhập tên ở G5 → Enter → tự động liệt kê project.

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

3. Chặn Thay Đổi Drop-Down Cấp 1 (Dependent Validation)

Danh sách Food Items theo Food Type. Muốn: Chọn Type → Items chỉ hiện món thuộc Type đó. Không cho đổi Type sau khi đã chọn Item (tránh mismatch).

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel 6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

Bước 1: Chọn ô Items → Data Validation.

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

Bước 2: Source: =IF(B4="",Foods, INDIRECT("FakeRange")).

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

Bước 3: Cảnh báo → Yes.

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

Bước 4-5: Chọn Type → chọn Item → không thể đổi Type (Items bị khóa).

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel 6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel 6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

4. Tạo Unique List Theo Tiêu Chí (UNIQUE + FILTER)

Dữ liệu có trùng → chỉ lấy unique.

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

Bước 1: Ô G6: =UNIQUE(FILTER(B4:B22,C4:C22=G5)).

Giải thích: FILTER lọc tất cả match (kể trùng) → UNIQUE loại trùng.

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

Bước 2: Nhập Name ở G5 → Enter.

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

5. Trích Xuất Toàn Bộ Hàng Thỏa Mãn Tiêu Chí (Công Thức Mảng)

Dữ liệu sản phẩm: ID, Brand, Model, Unit Price. Lọc hàng theo Brand (H5, H7).

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

Bước 1: Ô B19: =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)) → Ctrl+Shift+Enter → copy toàn bảng.

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

Bước 2: Nhập Brand ở H5, H7 → Enter.

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

6. Tạo Dependent Drop-Down List (Data Validation + INDIRECT)

3 danh sách món ăn theo loại. Tạo drop-down phụ thuộc.

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

Bước 1: Data Validation cho cột Food Types → Allow: List → Source: chọn header các list.

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel 6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

Bước 2: Drop-down Food Types xuất hiện.

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

Bước 3: Chọn toàn dữ liệu → Formulas → Create from Selection.

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

Bước 4: Popup → chọn Top row → OK (tạo Named Range cho từng loại).

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

Bước 5: Ô D14 → Data Validation → Allow: List → Source: =INDIRECT(B14) → OK.

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

Bước 6: Cảnh báo → Yes.

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

Bước 7-8: Chọn Food Type → Drop-down Items tự lọc đúng loại.

6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel 6 Phương Pháp Chứng Minh Tạo Danh Sách Từ Giá Trị Ô Trong Excel

Lưu Ý & Lỗi Thường Gặp

LỗiNguyên Nhân
#VALUE! trong FILTERKích thước include argument không khớp array argument.
#N/A!Không tìm thấy dữ liệu. Dùng IFERROR bắt lỗi.
Đặt tên ListKhông được chứa dấu cách. Dùng dấu gạch dưới "_".

Kết Luận

6 cách populate list dựa trên giá trị ô: INDEX-MATCH, FILTER, chặn thay đổi drop-down, UNIQUE+FILTER, công thức mảng trích hàng, Dependent Drop-down (INDIRECT). Có cách khác? Hãy chia sẻ!

Đọc Thêm

  • Chuyển Dữ Liệu Giữa Worksheet Tự Động
  • Auto Populate Từ Worksheet Khác
  • Fill Down Đến Hàng Cuối Có Dữ Liệu (3 Cách)