Bạn đã từng sử dụng công cụ xác thực dữ liệu trong Google Sheets chưa? Đây là một trong những tính năng cần phải tìm hiểu trong Google Sheets.
Công cụ này cho phép người dùng tạo danh sách thả xuống trong ô và cũng là một cách hiệu quả để xác thực các mục nhập thủ công.
Với công cụ xác thực dữ liệu, bạn có thể kiểm soát các loại giá trị được nhập vào trong một ô hoặc dải ô.
Dưới đây là một số ví dụ về công cụ xác thực dữ liệu tốt nhất trong Google Sheets:
Tạo danh sách thả xuống trong Google Sheets
Danh sách thả xuống là tính năng xác thực dữ liệu phổ biến nhất trong Google Sheets. Hãy bắt đầu với nó.
Cách tạo danh sách thả xuống trong Google Sheets
Dưới đây là một số ví dụ về công cụ xác thực dữ liệu trong Google Sheets liên quan đến danh sách thả xuống. Từ đó, bạn có thể tìm hiểu cách tạo danh sách thả xuống trong Google Sheets và tính hữu ích của nó.
Có hai cách để tạo danh sách thả xuống trong Google Sheets:
- Danh sách thả xuống: Để thêm mục vào danh sách thả xuống thủ công.
- Danh sách thả xuống (từ một phạm vi): Để thêm mục vào danh sách thả xuống từ một phạm vi.
Danh sách thả xuống: Để thêm mục vào danh sách thả xuống thủ công
Hãy chèn một danh sách thả xuống vào ô C1 chứa ba tên: “Ben,” “Johanna,” và “Jay.” Dưới đây là hướng dẫn từng bước để chèn danh sách thả xuống đơn giản vào Google Sheets.
Bước:
- Di chuyển đến ô mà bạn muốn tạo danh sách thả xuống, ví dụ: C1.
- Di chuyển đến menu Dữ liệu và chọn Xác thực dữ liệu.
- Nhấp vào nút “+Thêm quy tắc”.
- Chọn “Thả xuống” là Tiêu chí.
- Nhập các mục của danh sách (ví dụ: “Ben,” “Johanna,” và “Jay”) mà bạn muốn hiển thị trong danh sách thả xuống.
- Nếu bạn muốn, bạn có thể chọn màu sắc riêng cho từng mục.
- Nhấp vào “Hoàn tất.”
Bạn có thể sử dụng nút “Thêm mục khác” để thêm nhiều mục khác và biểu tượng thùng rác để xóa các mục không mong muốn.
Tùy chọn tiên tiến:
Bạn có thể chọn “Hiển thị cảnh báo” nhưng vẫn cho phép nhập. Một tam giác màu đỏ sẽ hiển thị trong ô C1.
Hoặc bạn có thể từ chối việc nhập và hiển thị “Văn bản giúp đỡ cho ô được chọn,” tức là ô C1.
Có ba kiểu hiển thị:
- Chip: – Hiển thị kiểu Chip giúp mở menu bằng cách nhấp vào mũi tên trong ô.
- Mũi tên: – Mở menu bằng cách nhấp vào nó (Mũi tên).
- Chữ thuần: – Nhấp đúp vào menu để mở nó.
Danh sách thả xuống (từ một phạm vi): Để thêm mục vào danh sách thả xuống từ một phạm vi
Đây là phương pháp phổ biến nhất để chèn danh sách thả xuống vào Google Sheets.
Trong phương pháp này, bạn phải nhập các mục của danh sách vào một cột, hàng hoặc phạm vi ô và trỏ xác thực dữ liệu vào các mục đó để thêm chúng vào danh sách thả xuống. Bạn có thể thực hiện như sau.
Ví dụ, hãy nhập các tên “Ben,” “Johanna,” và “Jay” vào các ô F1, F2 và F3, tương ứng.
Tiếp theo, làm theo các bước trên (vui lòng cuộn lên để xem) 1 đến 3. Ở bước thứ tư, chọn Thả xuống (từ một phạm vi).
Nhập phạm vi F1:F3 vào trường được cung cấp và nhấp vào “Hoàn tất.”
Nếu bạn muốn bổ sung thêm tên trong tương lai, bạn có thể nhập F1:F10 thay vì F1:F3. Tất cả các thiết lập khác đều giống nhau.
Cách sử dụng danh sách thả xuống trong thực tế trong Google Sheets
Tôi đang sử dụng danh sách thả xuống trong ô trong các trường hợp dưới đây.
Danh sách thả xuống để điều khiển biểu đồ trong Google Sheets
Đây là một trong những ví dụ về công cụ xác thực dữ liệu tốt nhất trong Google Sheets. Xem cách điều khiển dữ liệu nguồn của biểu đồ một cách linh hoạt.
Bạn có thể thấy danh sách thả xuống trong ô N1 điều khiển dữ liệu của biểu đồ cột.
Nếu bạn muốn kiểm soát biểu đồ của mình (dữ liệu nguồn) trong Google Sheets như trên, bạn có thể làm theo hướng dẫn này – Cách lấy phạm vi động trong biểu đồ trong Google Sheets.
Cách tạo danh sách thả xuống trên nhiều Sheets trong Google Sheets
Liệu tôi có thể sao chép danh sách thả xuống qua các tab trang trong Google Sheets không?
Nếu bạn muốn cùng một danh sách thả xuống trên nhiều trang tính, hãy sao chép và dán nó.
Không cần tạo cùng một danh sách thả xuống khi bạn muốn sử dụng nó trong nhiều ô, bất kể trong Trang tính hoặc Trang tính khác trong cùng một tệp.
Điều này áp dụng cho cả Danh sách thả xuống và Danh sách thả xuống (từ một phạm vi).
Cách tạo danh sách thả xuống có điều kiện trong Google Sheets
Để tạo danh sách thả xuống có điều kiện trong Google Sheets, bạn có thể sử dụng các hàm Lọc hoặc Truy vấn.
Danh sách thả xuống có điều kiện là gì?
Đôi khi, bạn có thể muốn danh sách thả xuống theo một điều kiện. Tốt hơn, tôi sẽ giải thích nó qua một ví dụ.
Ví dụ về Danh sách thả xuống có điều kiện:
Giả sử, ví dụ, bạn muốn tạo một danh sách thả xuống bằng cách sử dụng xác thực dữ liệu từ phạm vi D2:D. Trong đó, danh sách là từ phạm vi D2:D. Bạn không muốn tất cả các mục trong phạm vi này xuất hiện trong danh sách thả xuống.
Bạn chỉ muốn xuất hiện các mục trong danh sách nếu giá trị tương ứng của chúng trong phạm vi E2:E là “Yes.”
Dưới đây là các bước liên quan.
Bước để tạo danh sách thả xuống có điều kiện trong Google Sheets:
Tôi sẽ sử dụng cùng một ví dụ dữ liệu ở trên cho ví dụ của mình.
Áp dụng công thức Lọc dưới đây vào bất kỳ cột trống nào khác. Ở đây tôi sẽ chèn công thức này vào ô H2.
=filter(D2:D,E2:E=”Yes”)
Bây giờ bạn có thể nhập H2:H thay vì D2:D trong trường dưới Danh sách thả xuống (từ một phạm vi) trong xác thực dữ liệu trong Google Sheets.
Dưới đây là một hướng dẫn khác, phù hợp với ví dụ trên, là một phần bổ sung cho các ví dụ về xác thực dữ liệu tốt nhất trong Google Sheets của tôi – Giá trị phân biệt trong danh sách thả xuống trong Google Sheets.
Nó cũng giải thích xác thực dữ liệu có điều kiện trong Google Sheets nhưng từ một góc nhìn khác.
Danh sách thả xuống phụ thuộc vào danh sách thả xuống khác
Ban đầu, tôi nghĩ tôi không nên bao gồm mẹo này trong bài viết này, đầy đủ các ví dụ xác thực dữ liệu tốt nhất trong Google Sheets.
Không có giải pháp linh hoạt tích hợp sẵn để tạo danh sách thả xuống phụ thuộc trong Google Sheets ngoại trừ việc sử dụng Apps Script.
Nhưng, theo một cách giới hạn, bạn có thể tạo một danh sách thả xuống phụ thuộc trong Google Sheets.
Ví dụ về Danh sách thả xuống phụ thuộc vào dữ liệu khác:
Trong ô B2, bạn có thể thấy một danh sách thả xuống. Trong ô C2, có một danh sách thả xuống phụ thuộc vào danh sách thả xuống đầu tiên.
Điều đó có nghĩa là khi bạn chọn “Fruits” trong danh sách thả xuống đầu tiên, danh sách thả xuống thứ hai chỉ liệt kê các mục trái cây.
Nếu lựa chọn trong danh sách thả xuống đầu tiên là “Vegetables,” danh sách thứ hai sẽ chỉ liệt kê các loại rau.
Nghĩa là danh sách thả xuống đầu tiên chứa các danh mục và thứ hai, các mục nằm trong mỗi danh mục.
Bước để tạo danh sách thả xuống phụ thuộc vào danh sách thả xuống khác trong Google Sheets:
Tôi nghĩ rằng ở đây tôi nên minh hoạ quy trình trước tiên.
Bước:
- Nhập một số tên hoa quả vào phạm vi E2:E. Tiêu đề trong E2 phải là “Fruits.”
- Tương tự, nhập một số tên rau vào phạm vi F2:F. Ô F2 phải chứa tiêu đề “Vegetables.”
- Bạn nên nhập E2:F2 trong trường dưới Danh sách thả xuống (từ một phạm vi). Tôi đã giải thích các bước để tạo danh sách thả xuống trong Google Sheets. Vui lòng cuộn lên và xem.
Chèn công thức dưới đây vào ô H2 và sử dụng kết quả của nó trong phạm vi từ H2:H làm phạm vi của thả xuống thứ hai (thả xuống trong ô C2).
=transpose(filter($E$3:$F,$E$2:$F$2=B2))
Đó là tất cả những gì bạn cần làm để tạo danh sách thả xuống phụ thuộc vào danh sách thả xuống trong Google Sheets.
Dưới đây là một hướng dẫn chi tiết hơn về chủ đề này – Danh sách thả xuống phụ thuộc đa hàng động trong danh sách thả xuống trong Google Sheets.
Ngoài ra, để biết các hàm tôi đã sử dụng, vui lòng kiểm tra hướng dẫn này – Hướng dẫn về Các hàm Google Sheets.
Các ví dụ về xác thực dữ liệu trong Google Sheets sử dụng hộp kiểm (tick box)
Làm thế nào để chèn hộp kiểm xác thực dữ liệu trong Google Sheets?
Xác thực dữ liệu hỗ trợ Hộp kiểm. Bạn có thể chèn hộp kiểm theo hai cách.
Hãy truy cập vào menu Chèn và nhấp vào Hộp kiểm hoặc truy cập vào Dữ liệu> Xác thực dữ liệu> Thêm quy tắc> Tiêu chí> Hộp kiểm.
Tôi thích phương pháp trước mặc dù phương pháp sau cung cấp một số tùy chọn hồ sơ hộp kiểm tiên tiến.
Hãy xem chúng là gì?
Hiển thị cảnh báo hoặc từ chối nhập với văn bản giúp đỡ tương tự như danh sách thả xuống.
Hộp kiểm có tính tương tác, điều đó có nghĩa bạn có thể nhấp vào nó để chọn hoặc bỏ chọn hộp kiểm.
Khi bạn chọn một hộp kiểm, giá trị trong ô đó sẽ là TRUE, nếu không là FALSE. Thay vì TRUE hoặc FALSE, bạn có thể đặt bất kỳ giá trị ô tùy chỉnh nào.
Tài nguyên bổ sung liên quan đến hộp kiểm trong Google Sheets:
- Chọn / Bỏ chọn hộp kiểm dựa trên giá trị của ô khác trong Google Sheets.
- 10 mẹo và thủ thuật hay nhất về hộp kiểm trong Google Sheets.
- Thay đổi màu sắc của hộp kiểm khi chuyển đổi trong Google Sheets.
- Đánh dấu: Khóa và mở khóa ô sử dụng hộp kiểm trong Google Sheets.
- Gán giá trị cho hộp kiểm và tổng cộng trong Google Sheets.
Hạn chế hoặc áp dụng ràng buộc giá trị ô thành số, ngày hoặc văn bản bằng Xác thực dữ liệu
Ở đầu bài viết này, tôi đã đề cập đến mục đích của xác thực dữ liệu trong các ứng dụng bảng tính. Đó là đảm bảo chất lượng dữ liệu bằng cách áp dụng logic xác thực vào các ô.
Tôi đã cung cấp cho bạn một số ví dụ có thể là những ví dụ xác thực dữ liệu tốt nhất có sẵn trong Google Sheets.
Không nghi ngờ gì, tất cả đều liên quan đến danh sách thả xuống và hộp kiểm.
Một số thiết lập xác thực dữ liệu đơn giản có thể đảm bảo chất lượng dữ liệu tối đa với công sức tối thiểu trong Google Sheets. Dưới đây là một số ví dụ.
Xác thực dữ liệu trong Google Sheets để hạn chế hoặc áp dụng ràng buộc cho việc nhập số
Bạn có thể dễ dàng thiết lập một số ràng buộc cho việc nhập số vào một ô hoặc phạm vi ô.
Hãy xem cách giới hạn hoặc áp dụng một số ràng buộc cho việc nhập số vào một ô hoặc phạm vi ô đã chọn.
Giả sử, ví dụ, công ty của bạn có một số ràng buộc được đặt cho việc phê duyệt giờ làm thêm (OT). Tối thiểu là 1 giờ và Tối đa là 4 giờ một ngày.
Vì vậy, trong quá trình nhập dữ liệu làm thêm hàng ngày trong Google Sheets, bạn muốn đảm bảo rằng Sheets chỉ cho phép nhập các số >=1 và <=4 trong phạm vi đã định.
Dưới đây là cách thực hiện điều đó.
Hãy chọn ô hoặc phạm vi ô, ví dụ: A1:A10, nơi bạn muốn áp dụng hoặc gán một số ràng buộc nhập liệu liên quan đến số.
Tiếp theo, đi đến menu Dữ liệu> Xác thực dữ liệu> Thêm quy tắc.
Dưới Tiêu chí, chọn “Nằm giữa” và nhập các số 1 và 4 như hình dưới đây.
Dưới đây là tất cả các tiêu chí liên quan đến số trong xác thực dữ liệu trong Google Sheets.
- Lớn hơn.
- Lớn hơn hoặc bằng.
- Nhỏ hơn.
- Nhỏ hơn hoặc bằng.
- Bằng.
- Là không bằng.
- Nằm giữa.
- Không nằm giữa.
Xác thực dữ liệu trong Google Sheets để hạn chế việc nhập ngày
Đặt một số ràng buộc trong ô hoặc dải ô về nhập ngày. Điều này đảm bảo rằng chúng ta chỉ nhập hoặc chọn các ngày được định dạng đúng hoặc nhập các ngày nằm trong phạm vi đã cho.
Để đặt, hãy chọn Tiêu chí “Là một ngày hợp lệ.”
Các Tiêu chí khác: Ngày là, Ngày trước, Ngày trước hoặc trong ngày, Ngày sau, Ngày sau hoặc trong ngày, Ngày nằm giữa, và Ngày không nằm giữa.
Hạn chế việc nhập văn bản trong Google Sheets bằng Xác thực dữ liệu
Bạn có thể muốn chỉ chấp nhận ID email hợp lệ trong một ô có tiêu đề “Email” trong cơ sở dữ liệu nhân viên. Sau đó áp dụng Tiêu chí “Đoạn văn bản hợp lệ” như hình dưới đây.
Đây là một cách khác để đảm bảo chất lượng dữ liệu trong Bảng tính.
Các Tiêu chí văn bản khác: Văn bản chứa, Văn bản không chứa, Văn bản giống đúng, và Văn bản hợp lệ URL là các tùy chọn tiêu đề văn bản khác.
Hãy nhớ rằng việc xác thực dữ liệu trên Google Sheets có thể nâng cao chất lượng dữ liệu và giảm các lỗi nhập liệu. Hơn nữa, nó cung cấp một cách thuận tiện và linh hoạt để kiểm soát và định dạng dữ liệu của bạn.
Các ví dụ về xác thực dữ liệu tuỳ chỉnh trong Google Sheets dựa trên Công thức
Với tiêu chí “Công thức tùy chỉnh là” trong xác thực dữ liệu, chúng ta có thể cải thiện khả năng làm sạch dữ liệu trên Google Sheets.
Trong Google Sheets, giống như các công thức tùy chỉnh trong định dạng có điều kiện, các tham chiếu ô tuyệt đối và tương đối đóng một vai trò quan trọng khi sử dụng công thức tùy chỉnh trong xác thực dữ liệu.
Vai trò của tham chiếu ô tương đối và tuyệt đối trong xác thực dữ liệu
Tương đối và Tuyệt đối là hai loại tham chiếu ô trong Google Sheets. Chúng khác nhau như thế nào? Tôi sẽ giải thích ngắn gọn.
Tham chiếu ô Tương đối:
Khi bạn sao chép một công thức sang ô khác, các tham chiếu ô trong công thức cũng di chuyển nếu chúng là tương đối.
Ví dụ: =C1
Tham chiếu ô Tuyệt đối:
Chúng ta có thể kiểm soát hành vi của các tham chiếu ô bằng cách sử dụng dấu Dollar.
Dấu Dollar có vai trò quan trọng trong các tham chiếu ô tuyệt đối.
Nếu bạn sử dụng dấu Dollar với tham chiếu ô, khi bạn sao chép, tham chiếu ô đó vẫn không đổi theo cách nhất định. Xem các ví dụ.
Ví dụ:
=$C$1: Dòng và cột luôn không thay đổi.
=$C1: Dòng thay đổi.
=C$1: Cột thay đổi.
Mục tiêu cuối cùng của tôi với hướng dẫn Google Sheets này là cung cấp cho bạn một số ví dụ về xác thực dữ liệu tốt nhất trong Google Sheets. Vì vậy, chúng ta bắt đầu thôi.
Tôi sẽ sử dụng cả các tham chiếu ô tương đối và tuyệt đối trong các ví dụ xác thực dữ liệu tuỳ chỉnh dưới đây.
Xác thực dữ liệu có điều kiện trong Google Sheets bằng Công thức tùy chỉnh
Hãy bắt đầu bằng một ví dụ cơ bản. Hãy xem cách sử dụng công thức tùy chỉnh trong xác thực dữ liệu trong Google Sheets.
Cho phép nhập dữ liệu trong một phạm vi (A1:A5) nếu cột kế bên (B1:B5) chứa “Yes”:
Ở đây tôi áp dụng một công thức tùy chỉnh cho phạm vi A1:A5.
A1 sẽ được xác thực nếu B1 là “Yes,” A2 sẽ được xác thực nếu B2 là “Yes,” và cứ tiếp tục như vậy.
Bước:
- Chọn phạm vi A1:A5.
- Đi đến menu Dữ liệu> Xác thực dữ liệu> Thêm quy tắc.
- Chọn “Công thức tùy chỉnh là” dưới Tiêu chí.
- Nhập công thức bên dưới.
=B1=”Yes”
Công thức trên là một ví dụ về tham chiếu ô tương đối trong xác thực dữ liệu trong Google Sheets.
Hãy thử nghiệm nó trên Bảng của bạn để hiểu cách nó hoạt động.
Dưới đây là một số ví dụ khác về xác thực dữ liệu tuỳ chỉnh dựa trên công thức tùy chỉnh trong Google Sheets.
Xác thực dữ liệu để hạn chế hoặc cho phép chỉ nhập số lẻ hoặc chẵn trong một phạm vi:
Làm thế nào để chỉ cho phép nhập số lẻ trong một phạm vi?
Tôi đang áp dụng quy tắc này cho phạm vi A1:B10. Vì vậy, hãy nhập A1:B10 trong trường “Áp dụng vào phạm vi” trong bảng “Quy tắc xác thực dữ liệu”.
=isodd(A1)
Làm thế nào để chỉ cho phép nhập số chẵn trong một phạm vi?
=iseven(A1)
Cho phép số lẻ hoặc số chẵn trong một phạm vi theo điều kiện:
Tôi muốn chỉ cho phép số chẵn trong phạm vi A1:A5 nếu phạm vi B1 chứa TRUE.
Để đặt quy tắc đó, hãy chọn phạm vi A1:A5 và sử dụng công thức tùy chỉnh dưới đây trong xác thực dữ liệu.
=and(iseven(A1),$B$1=TRUE)
Thay ISEVEN bằng ISODD để chỉ cho phép số lẻ khi B1 = TRUE.
Hãy nhớ rằng xác thực dữ liệu để hạn chế việc nhập trùng lặp trong Google Sheets có thể cải thiện chất lượng dữ liệu và giảm lỗi nhập liệu. Ngoài ra, nó cung cấp một cách tiện lợi và linh hoạt để kiểm soát và định dạng dữ liệu của bạn.
Chúng ta đã tới cuối bài viết về các ví dụ xác thực dữ liệu tốt nhất trong Google Sheets. Hãy chia sẻ ý kiến của bạn trong phần bình luận. Chúc bạn may mắn!
