Bạn muốn làm cách nào để bắt buộc người dùng nhập các giá trị từ một danh sách theo thứ tự trong Google Sheets?
Kiểm tra dữ liệu giúp chúng ta điều khiển cách dữ liệu được nhập vào một ô hoặc phạm vi ô. Sử dụng kiểm tra dữ liệu, đặc biệt là sử dụng danh sách từ phạm vi, chúng ta có thể đạt được yêu cầu trên.
Tức là chúng ta có thể đảm bảo rằng người dùng chỉ nhập dữ liệu từ danh sách cho trước trong Google Sheets và các giá trị này giữ nguyên thứ tự trong danh sách. Đồng thời, giá trị có thể lặp lại nhiều lần.
Để đáp ứng các yêu cầu trên, chúng ta có thể sử dụng tính năng kiểm tra dữ liệu ‘Danh sách từ phạm vi’ (thả xuống). Làm thế nào?
Trong bài hướng dẫn Google Sheets này, bạn có thể tìm hiểu cách thực hiện điều đó một cách tuyệt vời, từng bước một.
Ví dụ nhập các giá trị từ danh sách theo thứ tự trong Google Sheets
Dưới đây là một ví dụ về phương pháp kiểm tra dữ liệu thông thường như trên.
Trong Google Sheets, danh sách của tôi nằm trong ô D2:D11 và chứa tên tệp từ 1 đến 10 (hãy cuộn xuống và xem hình ảnh đầu tiên).
Danh sách có thể chứa bất kỳ giá trị nào, tên tệp chỉ là mục đích ví dụ. Nó có thể là tên sản phẩm, tên sách, tên quốc gia, mã hàng, số chuyến bay, mã trạm, hoặc bất kỳ danh sách nào.
Tôi muốn bắt buộc người dùng hoặc giới hạn chính mình chỉ nhập tên tệp trong phạm vi A2:A từ danh sách trong D2:D11 và theo thứ tự tên tệp trong danh sách D2:D11.
Tuy nhiên, lưu ý rằng người dùng có quyền nhập tên tệp nhiều lần, tuân thủ điều kiện trên.
Nếu bạn nhìn vào cột ‘dữ liệu nhập sai’, bạn có thể hiểu tại sao các dữ liệu đang nhập sai. “Tệp 4” xuất hiện trước “Tệp 3”, vi phạm yêu cầu trên.
Tôi không nghĩ về một trường hợp kiểm tra dữ liệu như vậy cho đến khi một trong số các độc giả của mình hỏi về điều này trong một trong các bài hướng dẫn liên quan đến kiểm tra dữ liệu ở đây – Các ví dụ kiểm tra dữ liệu tốt nhất trong Google Sheets.
Làm thế nào để thực hiện điều này bằng danh sách từ phạm vi?
Chúng ta sẽ sử dụng danh sách thả xuống aka ‘Danh sách từ phạm vi’ để bắt buộc người dùng nhập đúng giá trị (theo yêu cầu) vào ô A2:A.
Trong danh sách thả xuống đó, ở mỗi hàng, chỉ có hai giá trị có sẵn để chọn – một là giá trị từ phía trên ô đang hoạt động và giá trị khác là giá trị phía bên dưới (ngay bên dưới) giá trị trên danh sách D2:D11.
Để làm được điều này, chúng ta cần sử dụng hai ô trợ giúp chứa hai công thức duy nhất. Nhờ đó, chúng ta có thể sử dụng các ô trợ giúp đó thay cho danh sách gốc trong D2:D11 để tạo danh sách thả xuống.
Điều đó có nghĩa là trong ba bước, chúng ta có thể bắt buộc người dùng nhập các giá trị từ danh sách theo thứ tự trong Google Sheets. Dưới đây là ba bước đó dưới ba tiêu đề con.
Công thức 1 – Giá trị cuối cùng từ cột A hoặc Giá trị đầu tiên từ danh sách
Chúng ta sẽ sử dụng ô F2 và F3 để tạo hai giá trị cần thiết để tạo danh sách thả xuống. Trong hai ô đó, Công thức 1 của chúng ta sẽ được đặt trong ô F2.
Đây là công thức đó.
=ifna(indirect(ArrayFormula("A"&MATCH(2,1/(A:A<>" "),1))), D2)
Chúng ta cần hiểu công thức quan trọng này để nhập các giá trị từ danh sách theo thứ tự của chúng trong danh sách.
Ở đây, ArrayFormula(“A”&MATCH(2,1/(A:A<>” “),1)) sẽ trả về ID ô của ô không trống cuối cùng (nếu có) trong cột A.
Chúng ta sau đó sử dụng chính ID đó để trả về giá trị từ ô không trống cuối cùng (nếu có) (nếu có).
Nếu không có giá trị nào trong cột A, thì công thức sẽ trả về #N/A!. Trong trường hợp đó, IFNA sẽ trả về giá trị từ ô D2, đó là giá trị đầu tiên trong danh sách.
Hãy xem ảnh dưới đây để hiểu rõ hơn về điều gì xảy ra trong ô F2.
Công thức 2 – ‘Giá trị tiếp theo’ trong danh sách
Vai trò của công thức thứ hai này là trả về giá trị kế tiếp (phía bên dưới) giá trị kết quả của công thức đầu tiên từ danh sách. Hãy nhập công thức này vào ô F3.
=iferror(if(counta(A:A)=0, "", index(D2:D11, match(F2, D2:D11, 0)+1)))
Bạn có muốn lời giải thích cho công thức này không?
Dưới đây là lời giải thích cho bạn!
Hàm IF sẽ kiểm tra xem có giá trị nào trong cột A hay không. Biểu thức ở IF ở đây là hàm COUNTA như dưới đây.
if(counta(A:A)=0
Nếu số lượng giá trị trong cột A là 0, thì IF sẽ trả về một giá trị trống “”, ngược lại IF sẽ thực hiện phần công thức này – Index(D2:D11,match(F2,D2:D11,0)+1).
Bây giờ chúng ta đã có hai công thức – một trong ô F2 và một trong ô F3.
Đến lúc tạo danh sách thả xuống để nhập các giá trị từ danh sách theo thứ tự trong danh sách trong Google Sheets.
Hãy lưu ý một lần nữa rằng chúng tôi sẽ không sử dụng danh sách gốc để tạo danh sách thả xuống, thay vào đó, chúng tôi sẽ sử dụng phạm vi F2:F3.
Danh sách từ phạm vi để nhập các giá trị từ danh sách theo thứ tự trong danh sách
Hãy làm theo bước cuối cùng dưới đây.
- Chọn phạm vi A2:A20 hoặc phạm vi mà bạn muốn xuất hiện danh sách thả xuống.
- Sau đó, nhấp vào menu Dữ liệu > Kiểm tra dữ liệu và tuân theo cài đặt ‘Danh sách từ phạm vi’ dưới đây.
Bây giờ người dùng chỉ có thể nhập/chọn dữ liệu trong A2:A20 (hoặc phạm vi mà bạn đã tạo danh sách thả xuống) từ danh sách theo thứ tự trong Google Sheets.
Đó là tất cả. Chúc bạn vui vẻ!
+Sample_Sheet_11920
