Mẹo Google Sheets · 14 phút đọc
Muốn tạo danh sách thả xuống trong Google Sheets, bạn chọn các ô, vào Chèn › Trình đơn thả xuống, gõ các lựa chọn (hoặc trỏ tới một dải ô có sẵn danh sách) rồi bấm Xong. Bài này đi qua hai cách tạo, cách tô màu từng lựa chọn, cách làm thả xuống phụ thuộc 2 cấp, chặn nhập sai, chép cho cả cột, sửa lỗi "dữ liệu không hợp lệ" và những gì thay đổi khi mở file bằng Excel.
Danh sách thả xuống (dropdown) buộc người nhập chọn từ các giá trị có sẵn thay vì tự gõ. Lợi ích thấy ngay khi đếm: cột Trạng thái có 6 việc gồm 2 Đang làm, 3 Chưa bắt đầu, 1 Hoàn thành. =COUNTIF(C2:C7; "Đang làm") ra 2. Nhưng chỉ cần một người gõ "Đang lam" (thiếu dấu) hay "đang làm " (thừa dấu cách), công thức ra 1 và bạn không biết mình đang thiếu số.
Tên menu trong bài theo giao diện tiếng Việt của Google Trang tính. Ba đường đều mở cùng một bảng thiết lập: Chèn › Trình đơn thả xuống, chuột phải vào ô › Trình đơn thả xuống, hoặc Dữ liệu › Xác thực dữ liệu › Thêm quy tắc.
Hợp với danh sách ngắn, ít khi đổi: Trạng thái, Ưu tiên, Có/Không, Nam/Nữ.
Thử lại: chọn Đang làm ở 2 ô, Chưa bắt đầu ở 3 ô, Hoàn thành ở 1 ô. =COUNTIF(C2:C300; "Đang làm") ra 2, =COUNTIF(C2:C300; "Chưa bắt đầu") ra 3. Thử gõ tay "Đang lam" vào một ô trống: Google từ chối và hiện thông báo, ô giữ nguyên giá trị cũ.
Danh sách dài hoặc hay đổi — tên nhân viên, dự án, sản phẩm — nên để riêng ở một trang (thường đặt tên Cài đặt hoặc DS) rồi cho dropdown đọc từ đó. Thêm người mới chỉ cần gõ thêm một dòng ở trang danh sách, mọi dropdown tự có.
Dải mở $B$2:$B (không ghi dòng cuối) giúp người thêm vào dòng 20, dòng 50 vẫn hiện trong danh sách; ô trống bị bỏ qua. Tên trang có dấu cách hoặc có dấu tiếng Việt thì đặt trong nháy đơn như trên.
Muốn dropdown lấy danh sách không trùng từ chính dữ liệu đang có (ví dụ tên khách đã nhập ở trang Đơn hàng), tạo một cột phụ rồi trỏ dropdown vào cột đó:
=SORT(UNIQUE(FILTER('Đơn hàng'!B2:B; 'Đơn hàng'!B2:B<>"")))Thử lại: cột Khách có Minh Anh, Quán Gió, Minh Anh, Bảy Hiền, Quán Gió. FILTER bỏ ô trống, UNIQUE còn 3 tên, SORT xếp theo chữ cái: Bảy Hiền, Minh Anh, Quán Gió.
Trong bảng quy tắc, mở Tuỳ chọn nâng cao để chọn kiểu hiển thị:
| Kiểu hiển thị | Trông thế nào | Hợp khi |
|---|---|---|
| Khối | Giá trị nằm trong viên bo tròn có màu (chip) | Cột trạng thái, ưu tiên cần nhìn lướt |
| Mũi tên | Chữ thường, có mũi tên ở góc ô | Bảng in ra, bảng nhiều cột |
| Văn bản thuần | Trông như ô bình thường, vẫn kiểm giá trị | Muốn gọn, chỉ cần chặn nhập sai |
Màu của từng mục chọn ngay cạnh mục đó, cả với kiểu lấy từ dải ô. Lưu ý từ trợ giúp của Google: khi bạn xoá một mục có màu khỏi dải nguồn, mục đó vẫn còn trong phần tiêu chí nhưng không sửa được nữa — vào quy tắc dọn lại cho gọn.
Cho phép có nhiều lựa chọn (chỉ bật được với kiểu Khối) cho một ô chứa nhiều giá trị, ví dụ một việc gắn cả Marketing và Thiết kế. Ô sẽ lưu thành chữ có dấu phẩy: "Marketing, Thiết kế". Đếm việc có Marketing thì dùng ký tự đại diện:
=COUNTIF(E2:E300; "*Marketing*")Thử lại: E2 = "Marketing, Thiết kế", E3 = "Marketing", E4 = "Thiết kế". COUNTIF ra 2. Trên ứng dụng điện thoại không chọn được nhiều giá trị, và Excel không có tính năng này (xem phần cuối).
Thả xuống phụ thuộc: chọn Nhóm hàng ở cột A thì cột B chỉ hiện mặt hàng của nhóm đó. Chọn Trà thì B chỉ còn Ô long, Lài, Sen. Google Sheets không có nút làm sẵn; cách chắc chạy là dùng FILTER đổ danh sách con ra một hàng phụ, rồi cho dropdown của cột B đọc hàng phụ đó.
Chuẩn bị trang DS có 2 cột dạng danh sách dài: A là Nhóm, B là Mặt hàng (Cà phê – Arabica, Cà phê – Robusta, Cà phê – Culi, Trà – Ô long, Trà – Lài, Trà – Sen, Mật ong – Hoa cà phê, Mật ong – Hoa nhãn). Cách xếp này dễ thêm hơn mỗi nhóm một cột.
=IFERROR(TRANSPOSE(FILTER(DS!$B$2:$B$50; DS!$A$2:$A$50=A2)); "")Thử lại: A2 = Trà. FILTER tìm trong DS!A2:A9 các dòng bằng Trà (dòng 5, 6, 7) và trả về Ô long, Lài, Sen; TRANSPOSE đặt chúng vào H2, I2, J2. Dropdown B2 đọc H2:Z2, bỏ qua ô trống, nên chỉ hiện 3 lựa chọn. Đổi A2 thành Mật ong: hàng phụ thành Hoa cà phê, Hoa nhãn.
Chỉ cần một ô phụ thuộc (ví dụ ô chọn ở đầu một trang báo cáo) thì không cần xoay ngang: đặt =FILTER(DS!$B$2:$B$50; DS!$A$2:$A$50=A2) ở H2 cho nó tràn xuống, dropdown dùng dải $H$2:$H$20.
Cách INDIRECT là cách quen thuộc trên Excel: mỗi nhóm một vùng có tên (Data › Define Name), tên vùng trùng với giá trị cấp 1, và nguồn của dropdown cấp 2 là =INDIRECT($A2). Tên vùng không được có dấu cách, nên nhóm "Mật ong" phải đặt tên Mật_ong và dùng =INDIRECT(SUBSTITUTE($A2; " "; "_")). Trên Google Sheets, ô dải nguồn của dropdown được thiết kế để nhận một dải ô; nếu file của bạn không nhận công thức INDIRECT ở đó, dùng cách FILTER ở trên.
Đổi cấp 1 thì cấp 2 không tự xoá
Đã chọn Trà – Lài rồi đổi A2 sang Cà phê, B2 vẫn giữ chữ Lài và bị đánh dấu không hợp lệ. Thêm cột kiểm: =IF(B2=""; ""; IF(COUNTIFS(DS!$A$2:$A$50; A2; DS!$B$2:$B$50; B2)=0; "Sai cặp"; "")). Với A2 = Cà phê, B2 = Lài: không dòng nào thoả cả hai nên ra "Sai cặp".
Mặc định Google từ chối giá trị không có trong danh sách. Trong Tuỳ chọn nâng cao, nếu chọn Hiện cảnh báo, giá trị lạ vẫn được nhập nhưng ô có dấu cảnh báo ở góc — hợp khi danh sách chỉ là gợi ý (ví dụ ghi chú tự do). Cột dùng để đếm, lọc, tính tiền thì giữ chế độ từ chối.
Dropdown không chặn được mọi thứ: người dán đè cả ô từ chỗ khác có thể mang theo hoặc làm mất quy tắc của ô đích. Với bảng nhiều người sửa, khoá các vùng không cho gõ — xem cách khoá ô công thức trong Google Sheets — và dặn mọi người dán bằng Ctrl+Shift+V (chỉ dán giá trị) để giữ nguyên quy tắc của cột.
Chép dropdown cho cả cột, từ chắc chắn nhất đến nhanh nhất:
Mỗi lần chép-dán là có thể sinh thêm một quy tắc mới. Mở Dữ liệu › Xác thực dữ liệu thỉnh thoảng để gộp các quy tắc trùng, nếu không lần sau thêm mục mới bạn phải sửa nhiều chỗ.
Ô có dấu đỏ ở góc và báo giá trị phải là một mục trong danh sách nghĩa là giá trị đang có không khớp chính xác mục nào. Các nguyên nhân hay gặp:
| Tình huống | Vì sao bị báo | Cách sửa |
|---|---|---|
| Dữ liệu cũ nhập trước khi có dropdown | "Đang lam", "dang lam" không khớp "Đang làm" | Lọc cột, sửa hàng loạt bằng Tìm và thay thế (Ctrl+H) |
| Đổi tên một mục trong danh sách gốc | Ô đã chọn tên cũ không tự đổi theo | Tìm và thay thế tên cũ thành tên mới trên cả cột |
| Khoảng trắng thừa trong danh sách gốc | "Lan " khác "Lan" | Dữ liệu › Dọn dẹp dữ liệu › Cắt bớt khoảng trắng, hoặc TRIM |
| Đổi cấp 1 của dropdown phụ thuộc | Giá trị cấp 2 không thuộc nhóm mới | Chọn lại cấp 2; thêm cột kiểm "Sai cặp" |
| Mã số dạng số và dạng chữ | Danh sách có "01" (chữ), ô có 1 (số) | Định dạng cả hai về văn bản thuần |
| Dải nguồn bị trượt sau khi chép | Quy tắc ở dòng dưới trỏ sang vùng khác | Mở quy tắc kiểm tra, khoá $ cho phần cố định |
Để đếm có bao nhiêu ô sai mà không phải dò bằng mắt, thêm một cột kiểm cạnh cột dropdown:
=IF(C2=""; ""; IF(COUNTIF('Cài đặt'!$B$2:$B$20; C2)=0; "Không hợp lệ"; ""))Thử lại: Cài đặt!B2:B5 = Chưa bắt đầu, Đang làm, Bị chặn, Hoàn thành. C2 = "Đang làm" → COUNTIF ra 1 → để trống. C3 = "Đang lam" → COUNTIF ra 0 → "Không hợp lệ". Lưu ý COUNTIF không phân biệt hoa thường, nên "đang làm" viết thường không bị bắt ở cột này dù dropdown vẫn báo; dùng =SUMPRODUCT(EXACT('Cài đặt'!$B$2:$B$20; C2)*1)=0 nếu cần phân biệt.
Nếu cột còn là ngày tháng hay số dán từ phần mềm khác, kiểm tra thêm kiểu dữ liệu — xem sửa lỗi ngày tháng trong Google Sheets.
Tải file về .xlsx rồi mở bằng Excel, dropdown vẫn là danh sách chọn (Excel gọi là Data Validation, kiểu List), nhưng không phải thứ gì cũng giữ được:
| Trên Google Sheets | Khi mở bằng Excel |
|---|---|
| Danh sách gõ tay | Vẫn chọn được. Excel giới hạn danh sách gõ tay khoảng 255 ký tự; danh sách dài nên để trong dải ô |
| Danh sách từ dải ô | Vẫn chọn được. Dải mở như $B$2:$B nên đổi thành dải có dòng cuối, ví dụ $B$2:$B$200 |
| Kiểu Khối, màu từng mục | Excel hiện mũi tên khi chọn ô; màu riêng từng mục không có sẵn, phải tô bằng định dạng có điều kiện |
| Cho phép nhiều lựa chọn | Excel không có. Ô chứa "Marketing, Thiết kế" bị coi là không khớp danh sách |
| Phụ thuộc bằng FILTER + hàng phụ | Chạy trên Excel 2021 và Microsoft 365 (có FILTER); Excel 2019 trở về trước báo lỗi |
| Phụ thuộc bằng INDIRECT + vùng có tên | Chạy trên mọi bản Excel |
Những khác biệt khác giữa hai bên (hàm, Apps Script, chia sẻ) có ở bài Google Sheets hay Excel. Khi đã có cột chọn từ danh sách chuẩn, làm báo cáo đếm, cộng theo từng giá trị bằng hàm QUERY sẽ không bị lọt dòng vì gõ sai.
Danh sách gõ tay: Dữ liệu › Xác thực dữ liệu › bấm vào quy tắc › Thêm một mục khác. Danh sách từ dải ô: chỉ cần gõ thêm vào dải nguồn; nếu dải là dải mở như $B$2:$B thì mục mới hiện ngay.
Chọn các ô, mở Dữ liệu › Xác thực dữ liệu và xoá quy tắc. Giá trị đang có trong ô vẫn còn, chỉ mất mũi tên và việc kiểm tra.
Không. Thứ tự là thứ tự bạn gõ hoặc thứ tự trong dải nguồn. Muốn theo chữ cái thì sắp xếp dải nguồn, hoặc tạo cột phụ bằng SORT(UNIQUE(...)) như trong bài.
Có, trong ứng dụng Google Sheets bấm vào ô là hiện danh sách. Riêng chế độ chọn nhiều giá trị thì điện thoại không chọn được nhiều mục.
Dải nguồn phải nằm trong cùng file. Muốn dùng danh sách ở file khác, kéo nó về một trang phụ bằng IMPORTRANGE rồi cho dropdown đọc trang phụ đó.
Mẫu khác liên quan: Quản lý Lead & Bán hàng PRO.
Google Sheets hay Excel: so chi phí, làm chung, dùng trên điện thoại, khi mất mạng, giới hạn dữ liệu, Apps Script với VBA và các công thức khác nhau.
Cách dùng hàm FILTER trong Google Sheets: lọc 1 hay nhiều điều kiện AND/OR, theo ngày, chữ chứa, kết hợp SORT, UNIQUE và sửa lỗi không tìm thấy kết quả.
Cách dùng hàm ARRAYFORMULA trong Google Sheets: một công thức cho cả cột, bỏ dòng trống bằng IF, kết hợp VLOOKUP, XLOOKUP, SUMIF, thay AND/OR, sửa lỗi #REF!.
Cách dùng hàm SUMIFS và COUNTIFS trong Google Sheets: cú pháp, nhiều điều kiện, lọc theo tháng bằng DATE, ký tự đại diện, sửa lỗi ra 0, so với SUMIF.
Xem thêm mẹo và video hướng dẫn trên kênh YouTube HiTaVa, hoặc đọc các bài khác trong chủ đề Mẹo Google Sheets.
Cần hỏi thêm?Zalo 0935077673 (Hải Vân)
