Tạo Drop List Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Cách Tìm Tên Trên Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Mở Đầu

Trong môi trường làm việc hiện đại, Excel không chỉ là một công cụ tính toán mà còn là một nền tảng mạnh mẽ để quản lý dữ liệu, tự động hoá quy trình và tạo ra các biểu mẫu tương tác. Một trong những tính năng phổ biến và hữu ích nhất của Excel là Drop List (hay còn gọi là danh sách thả xuống, dropdown list). Drop List giúp người dùng:

• Giảm thiểu lỗi nhập liệu bằng cách giới hạn các lựa chọn hợp lệ.
• Tiết kiệm thời gian khi người dùng không cần phải gõ toàn bộ nội dung.
• Cải thiện tính nhất quán và chuẩn hoá dữ liệu trong toàn bộ bảng tính.
• Tăng tính trực quan cho các biểu mẫu và báo cáo.

Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết, từ các bước cơ bản nhất cho người mới bắt đầu, đến các kỹ thuật nâng cao, bao gồm việc tạo danh sách động, sử dụng công thức, và tích hợp với các tính năng khác như Data Validation, Table, và VBA. Bạn sẽ có thể áp dụng ngay vào công việc hàng ngày, nâng cao hiệu suất và chất lượng dữ liệu của mình.

Có thể bạn quan tâm: Cách Tìm Kiếm Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Kiến Thức Cơ Bản Về Drop List Trong Excel

1.1 Drop List là gì?

Drop List là một công cụ kiểm tra dữ liệu (Data Validation) trong Excel, cho phép người dùng chọn một giá trị từ một danh sách các mục đã được định trước. Khi người dùng nhấp vào ô, một mũi tên sẽ xuất hiện, cho phép họ mở danh sách và chọn một mục.

1.2 Khi nào nên sử dụng Drop List?

• Nhập dữ liệu chuẩn: Khi có một tập hợp các giá trị cố định (ví dụ: các khu vực, phòng ban, trạng thái đơn hàng).
• Giảm lỗi nhập: Tránh nhập sai chính tả, nhập số ngoài phạm vi cho phép.
• Tạo biểu mẫu: Khi xây dựng các mẫu đơn, phiếu khảo sát, hoặc báo cáo.
• Tự động hoá: Kết hợp với các công thức và macro để tự động cập nhật dữ liệu.

1.3 Các thành phần chính

Có thể bạn quan tâm: Cách Ghim Hàng Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

2. Tạo Drop List Cơ Bản Bằng Data Validation

2.1 Bước 1: Chuẩn bị danh sách nguồn

1. Mở một sheet mới (hoặc một sheet hiện có) và nhập các mục vào một cột, ví dụ: A2:A6.
   A2: Hà Nội A3: Hồ Chí Minh A4: Đà Nẵng A5: Hải Phòng A6: Cần Thơ

2. Lưu ý: Đảm bảo không có ô trống trong dải dữ liệu, nếu có, các mục trống sẽ xuất hiện trong Drop List.

2.2 Bước 2: Đặt tên cho dải dữ liệu (tùy chọn)

• Chọn dải A2:A6.
• Vào Formulas → Define Name (hoặc Formulas → Name Manager).
• Đặt tên, ví dụ: DanhSachThanhPho.
• Nhấn OK.

Việc đặt tên giúp quản lý dễ dàng hơn, đặc biệt khi danh sách được sử dụng ở nhiều vị trí.

2.3 Bước 3: Áp dụng Data Validation cho ô mục tiêu

1. Chọn ô (hoặc dải ô) muốn gắn Drop List, ví dụ B2.
2. Vào Data → Data Validation.
3. Trong hộp thoại Data Validation:
4. Allow: Chọn List.
5. Source:
   • Nếu không đặt tên: nhập =$A$2:$A$6.
   • Nếu đã đặt tên: nhập =DanhSachThanhPho.
6. In‑cell dropdown: Đảm bảo ô này được tick.
7. Nhấn OK.

Bây giờ, khi bạn click vào ô B2, sẽ xuất hiện mũi tên và danh sách các thành phố để chọn.

2.4 Bước 4: Thêm thông báo và lỗi (tùy chọn)

• Input Message: Thông báo hiển thị khi người dùng chọn ô. Ví dụ: “Vui lòng chọn thành phố”.
• Error Alert: Thông báo khi người dùng nhập dữ liệu không có trong danh sách. Chọn kiểu Stop, Warning, hoặc Information tùy nhu cầu.

3. Tạo Drop List Động (Dynamic Drop List)

Khi danh sách nguồn thay đổi (thêm, bớt mục), việc cập nhật dải ô trong Data Validation mỗi lần là không thực tế. Drop List động sẽ tự cập nhật tự động dựa trên nội dung thay đổi.

3.1 Sử dụng Table (Bảng)

1. Chuyển dải nguồn thành Table:
2. Chọn dải A2:A6.
3. Vào Insert → Table (hoặc Ctrl + T).
4. Đánh dấu “My table has headers” nếu có tiêu đề.

5. Đặt tên cho Table, ví dụ: tblThanhPho.

6. Xác định cột dữ liệu:

7. Nếu Table có tiêu đề “Thành phố” trong cột A, công thức để lấy toàn bộ cột là:
   =tblThanhPhoThành phố

8. Áp dụng Data Validation:

9. Chọn ô mục tiêu.
10. Vào Data → Data Validation → List.
11. Trong Source, nhập:
    =tblThanhPhoThành phố
12. Nhấn OK.

Khi bạn thêm một thành phố mới vào cuối Table, Drop List sẽ tự động hiển thị mục mới mà không cần chỉnh sửa Data Validation.

3.2 Sử dụng hàm OFFSET và COUNTA

Nếu không muốn dùng Table, bạn có thể dùng công thức OFFSET để tạo dải động.

=OFFSET($A$2,0,0,COUNTA($A$2:$A$100),1)

Giải thích:

• OFFSET($A$2,0,0,…) bắt đầu từ A2.
• COUNTA($A$2:$A$100) đếm số ô không rỗng trong phạm vi A2:A100, quyết định chiều cao của dải.
• Khi bạn nhập thêm giá trị trong cột A, COUNTA sẽ tăng và dải sẽ mở rộng.

Áp dụng:

• Vào Data Validation → List.
• Nhập công thức trên vào Source (không cần dấu = ở đầu trong một số phiên bản Excel; nếu cần, viết =OFFSET(...)).

Lưu ý: Hàm OFFSET là hàm volatile, có thể làm chậm file khi có rất nhiều công thức. Đối với file lớn, ưu tiên dùng Table.

3.3 Sử dụng hàm FILTER (Excel 365/2026)

Nếu bạn đang dùng Excel 365 hoặc 2026, có thể dùng hàm FILTER để lọc danh sách dựa trên tiêu chí.

=FILTER($A$2:$A$100,$A$2:$A$100<>"")

Công thức này trả về một mảng các giá trị không rỗng và có thể dùng làm nguồn cho Drop List.

Để sử dụng công thức mảng trong Data Validation, bạn cần đặt công thức vào một named range (đặt tên) và dùng tên đó làm nguồn.

4. Drop List Phụ Thuộc (Dependent Drop List)

Trong nhiều trường hợp, bạn cần hai danh sách thả xuống: danh sách thứ hai phụ thuộc vào lựa chọn của danh sách thứ nhất. Ví dụ: chọn Quốc gia, sau đó Tỉnh/Thành phố tương ứng.

4.1 Chuẩn bị dữ liệu

Cách tổ chức:

• Cột A: Danh sách quốc gia (A2:A4).
• Các cột tiếp theo: Mỗi cột chứa các tỉnh/thành phố tương ứng với quốc gia ở hàng 2.

4.2 Đặt tên cho các dải phụ thuộc

1. Đặt tên cho dải quốc gia: chọn A2:A4 → Define Name → QuocGia.
2. Đặt tên cho từng dải tỉnh/thành phố:
3. Chọn B2:B4 → Define Name → VietNam.
4. Chọn C2:C4 → Define Name → My.
5. Chọn D2:D4 → Define Name → NhatBan.

Tên phải không chứa khoảng trắng và phải giống với giá trị trong danh sách quốc gia (không dấu, không khoảng trắng). Để tự động chuyển đổi, bạn có thể dùng hàm SUBSTITUTE và UPPER trong tên hàm (sẽ được trình bày trong phần VBA).

4.3 Tạo Drop List cho Quốc gia

• Áp dụng Data Validation cho ô E2 (Quốc gia) với Source = =QuocGia.

4.4 Tạo Drop List phụ thuộc cho Tỉnh/Thành phố

Có thể bạn quan tâm: Không Mở Được File Excel: Nguyên Nhân, Giải Pháp Và Cách Phòng Ngừa

1. Chọn ô F2 (Tỉnh/Thành phố).
2. Vào Data → Data Validation → List.
3. Trong Source, nhập công thức:
   excel =INDIRECT(SUBSTITUTE(E2," ",""))
4. INDIRECT trả về tham chiếu dựa trên chuỗi.
5. SUBSTITUTE(E2," ","") loại bỏ khoảng trắng trong tên quốc gia (nếu có) để khớp với tên đã đặt.

Khi bạn chọn “Việt Nam” ở E2, công thức sẽ trở thành =VietNam và hiển thị danh sách các thành phố tương ứng.

4.5 Xử lý lỗi khi không có giá trị

Nếu ô E2 chưa được chọn, công thức INDIRECT sẽ trả lỗi #REF!. Để tránh, bạn có thể:

• Cách 1: Đặt Error Alert trong Data Validation, chọn Ignore blank.
• Cách 2: Sử dụng công thức mảng trong Named Range:
• Tạo một Named Range DanhSachThanhPho với công thức:
  excel =IF(E2="","",INDIRECT(SUBSTITUTE(E2," ","")))
• Dùng =DanhSachThanhPho làm nguồn cho ô F2.

5. Kết Hợp Drop List Với Các Hàm Khác

5.1 Sử dụng VLOOKUP/HLOOKUP với Drop List

Giả sử bạn có bảng giá sản phẩm và muốn tự động hiển thị giá khi người dùng chọn sản phẩm từ Drop List.

Bước thực hiện:

1. Tạo Drop List cho Mã SP (ô E2) dựa trên cột A.
2. Trong ô F2, nhập công thức:
   excel =VLOOKUP(E2,$A$2:$C$4,3,FALSE)
   Khi người dùng chọn P002, ô F2 sẽ hiển thị 15,000.

5.2 Sử dụng INDEX-MATCH (thay thế VLOOKUP)

Với dữ liệu lớn hoặc khi cột tìm kiếm không nằm ở cột đầu tiên, dùng INDEX-MATCH:

=INDEX($C$2:$C$100, MATCH(E2, $A$2:$A$100, 0))

5.3 Tự động tính tổng dựa trên lựa chọn Drop List

Ví dụ: Bạn có bảng chi phí theo loại, và muốn tính tổng chi phí cho loại được chọn.

• Drop List cho Loại ở ô D2.
• Công thức tổng ở E2:
  excel =SUMIF($A$2:$A$5, D2, $B$2:$B$5)

6. Tạo Drop List Bằng VBA (Macro)

Khi cần tạo hàng loạt Drop List, hoặc muốn tự động hoá quá trình dựa trên các tiêu chí phức tạp, VBA là một công cụ mạnh mẽ.

6.1 Mở Trình VBA

• Nhấn Alt + F11 để mở Visual Basic for Applications.
• Vào Insert → Module để tạo một module mới.

6.2 Mã VBA tạo Drop List cho một dải ô

Sub TaoDropListCoBan() Dim rngSource As Range Dim rngTarget As Range ' Định nghĩa dải nguồn (cột A2:A6) Set rngSource = ThisWorkbook.Worksheets("Sheet1").Range("A2:A6") ' Định nghĩa dải ô mục tiêu (cột B2:B20) Set rngTarget = ThisWorkbook.Worksheets("Sheet1").Range("B2:B20") With rngTarget.Validation .Delete ' Xóa validation cũ nếu có .Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, _ Formula1:="=" & rngSource.Address .IgnoreBlank = True .InCellDropdown = True .ShowInput = True .InputTitle = "Chọn giá trị" .InputMessage = "Vui lòng chọn một mục từ danh sách" .ShowError = True .ErrorTitle = "Giá trị không hợp lệ" .ErrorMessage = "Bạn đã nhập một giá trị không có trong danh sách." End With
End Sub

Giải thích:

• rngSource là dải chứa danh sách.
• rngTarget là dải ô sẽ nhận Drop List.
• .Add tạo validation kiểu List, Formula1 chứa địa chỉ dải nguồn.

6.3 Tạo Drop List Động Với VBA

Nếu danh sách nguồn thay đổi thường xuyên, bạn có thể tạo một named range động bằng VBA và áp dụng:

Sub TaoDropListDong() Dim ws As Worksheet Dim lastRow As Long Dim rngSource As Range Dim rngTarget As Range Set ws = ThisWorkbook.Worksheets("Sheet1") ' Xác định dòng cuối cùng của dải nguồn trong cột A lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row Set rngSource = ws.Range("A2:A" & lastRow) ' Đặt tên cho dải nguồn (dynamic named range) ThisWorkbook.Names.Add Name:="DanhSachDong", RefersTo:=rngSource ' Áp dụng Drop List cho cột B (B2:B100) Set rngTarget = ws.Range("B2:B100") With rngTarget.Validation .Delete .Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, _ Formula1:="=DanhSachDong" .InCellDropdown = True End With
End Sub

Khi bạn chạy macro này, bất kỳ thay đổi nào thêm vào cột A sẽ tự động được phản ánh trong Drop List ở cột B.

6.4 Drop List Phụ Thuộc Bằng VBA

Sub TaoDropListPhuThuoc() Dim ws As Worksheet Dim rngCountry As Range, rngCity As Range Dim lastRowCountry As Long, lastRowCity As Long Set ws = ThisWorkbook.Worksheets("Sheet1") ' Định nghĩa danh sách quốc gia lastRowCountry = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row Set rngCountry = ws.Range("A2:A" & lastRowCountry) ThisWorkbook.Names.Add Name:="QuocGia", RefersTo:=rngCountry ' Định nghĩa danh sách thành phố cho mỗi quốc gia ' Giả sử dữ liệu bắt đầu ở cột B, C, D tương ứng Dim col As Long For col = 2 To 4 ' B đến D lastRowCity = ws.Cells(ws.Rows.Count, col).End(xlUp).Row Set rngCity = ws.Range(ws.Cells(2, col), ws.Cells(lastRowCity, col)) ' Lấy tên quốc gia từ hàng 1 (tiêu đề) Dim countryName As String countryName = Replace(ws.Cells(1, col).Value, " ", "") ThisWorkbook.Names.Add Name:=countryName, RefersTo:=rngCity Next col ' Áp dụng Drop List cho ô E2 (quốc gia) With ws.Range("E2").Validation .Delete .Add Type:=xlValidateList, Formula1:="=QuocGia" .InCellDropdown = True End With ' Áp dụng Drop List phụ thuộc cho ô F2 (thành phố) With ws.Range("F2").Validation .Delete .Add Type:=xlValidateList, Formula1:="=INDIRECT(SUBSTITUTE(E2,"" "",""""))" .InCellDropdown = True End With
End Sub

Macro trên tự động tạo named ranges cho mỗi quốc gia dựa trên tiêu đề cột và thiết lập Drop List phụ thuộc.

7. Các Mẹo Và Thủ Thuật Nâng Cao

7.1 Giới Hạn Số Lượng Mục Trong Drop List

Excel cho phép tối đa 32,767 ký tự trong trường Source. Nếu danh sách quá dài, bạn có thể:

• Sử dụng Table: Giới hạn số mục thực tế.
• Sử dụng Combo Box (ActiveX hoặc Form Control): Cho phép nhiều mục hơn và có khả năng tìm kiếm.

7.2 Thêm Tính Năng Tìm Kiếm (Searchable Drop List)

Excel không có tính năng tìm kiếm trong Data Validation mặc định, nhưng bạn có thể:

• Sử dụng Combo Box (ActiveX): Thiết lập MatchEntry = fmMatchEntryComplete để hỗ trợ tìm kiếm.
• Sử dụng Data Validation kết hợp với hàm FILTER (Excel 365):
• Tạo ô nhập tạm thời (G2) để người dùng gõ một phần từ khóa.
• Dùng công thức:
  excel =FILTER($A$2:$A$100, ISNUMBER(SEARCH(G2, $A$2:$A$100)))
• Đặt kết quả làm nguồn cho Drop List.

7.3 Định Dạng Điều Kiện Khi Chọn Drop List

Bạn có thể thay đổi màu nền hoặc màu chữ dựa trên giá trị được chọn:

1. Chọn dải ô có Drop List.
2. Vào Home → Conditional Formatting → New Rule.
3. Chọn Use a formula to determine which cells to format.
4. Nhập công thức, ví dụ:
   excel =$B2="Hà Nội"
5. Đặt định dạng (màu nền xanh, chữ trắng, …) và OK.

7.4 Bảo Vệ Drop List Khi Chia Sẻ File

• Khi chia sẻ file, người nhận có thể vô tình xóa Data Validation. Để tránh:
• Bảo vệ Worksheet: Review → Protect Sheet, chọn Allow: Select unlocked cells và Format cells, Edit objects nếu cần.
• Đánh dấu các ô chứa Drop List là Locked (mặc định) để khi bảo vệ sheet, các ô này không thể thay đổi.

7.5 Đồng Bộ Drop List Giữa Nhiều Sheet

Nếu bạn có nhiều sheet cùng sử dụng cùng một danh sách:

• Đặt Named Range trong Workbook (không giới hạn sheet) và sử dụng trong mọi Data Validation.
• Hoặc đặt danh sách nguồn trong một Sheet riêng (ví dụ: “DataLists”) và tham chiếu tới đó.

7.6 Sử Dụng Power Query Để Tạo Danh Sách Động

Khi dữ liệu nguồn nằm trong cơ sở dữ liệu hoặc file CSV lớn, bạn có thể:

1. Import dữ liệu bằng Power Query (Data → Get Data).
2. Tạo bảng trong Power Query, lọc bỏ các giá trị trùng và rỗng.
3. Load kết quả vào một Table trong Excel.
4. Sử dụng Table này làm nguồn cho Drop List – mỗi lần dữ liệu được refresh, danh sách sẽ tự động cập nhật.

8. Các Lỗi Thông Thường Khi Sử Dụng Drop List Và Cách Khắc Phục

9. Ví Dụ Thực Tế Áp Dụng Drop List Trong Các Ngành Nghề

9.1 Quản Lý Dự Án

• Drop List cho trạng thái công việc: Chưa bắt đầu, Đang thực hiện, Hoàn thành, Bị hoãn.
• Khi trạng thái thay đổi, công thức Conditional Formatting tự động tô màu.

9.2 Kế Toán & Tài Chính

• Drop List cho loại chứng từ: Hóa đơn, Phiếu chi, Phiếu thu.
• Kết hợp với VLOOKUP để tự động điền mã chứng từ, ngày, và tài khoản kế toán tương ứng.

9.3 Nhân Sự

• Drop List cho phòng ban, vị trí, cấp bậc.
• Khi chọn phòng ban, danh sách vị trí được lọc tự động (phụ thuộc).

9.4 Bán Lẻ & Kho

• Drop List cho mã sản phẩm, nhà cung cấp, khu vực lưu kho.
• Khi nhập mã sản phẩm, công thức INDEX-MATCH trả về giá bán, tồn kho, và tự động tính tổng tiền.

9.5 Giáo Dục

• Drop List cho lớp học, môn học, giáo viên phụ trách.
• Khi chọn lớp, danh sách môn học được lọc, và khi chọn môn, danh sách giáo viên hiển thị.

10. Tổng Kết

Drop List trong Excel là một công cụ cực kỳ mạnh mẽ, giúp đảm bảo chất lượng dữ liệu, tăng tốc độ nhập liệu, và tạo ra các biểu mẫu chuyên nghiệp. Bài viết đã cung cấp:

1. Kiến thức cơ bản: cách tạo danh sách thả xuống đơn giản bằng Data Validation.
2. Kỹ thuật động: sử dụng Table, OFFSET, FILTER để danh sách tự cập nhật.
3. Drop List phụ thuộc: xây dựng danh sách cấp 2 dựa trên lựa chọn của danh sách cấp 1.
4. Kết hợp công thức: VLOOKUP, INDEX-MATCH, SUMIF để tự động tính toán.
5. Tự động hoá bằng VBA: tạo, cập nhật và quản lý Drop List qua macro.
6. Mẹo nâng cao: tìm kiếm, định dạng có điều kiện, bảo vệ, đồng bộ giữa các sheet.
7. Xử lý lỗi và ví dụ thực tế trong các ngành nghề khác nhau.

Với kiến thức này, bạn đã sẵn sàng triển khai Drop List trong mọi dự án Excel, từ những bảng tính cá nhân cho tới các hệ thống quản lý doanh nghiệp phức tạp. Hãy thử áp dụng ngay hôm nay, và khám phá cách mà một danh sách thả xuống đơn giản có thể biến đổi cách làm việc của bạn một cách đáng kể. Chúc bạn thành công!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.