Bạn đã bao giờ muốn bắt buộc người dùng chỉ nhập giá trị từ một danh sách theo thứ tự trong Google Sheets không? Đừng lo, chúng ta có thể sử dụng tính năng xác minh dữ liệu để làm điều đó.
Vấn đề của chúng ta
Chúng ta muốn đảm bảo rằng người dùng chỉ có thể nhập dữ liệu từ một danh sách cụ thể trong Google Sheets và các giá trị này phải giữ nguyên thứ tự trong danh sách đó. Ngoài ra, các giá trị có thể được lặp lại nhiều lần.
Sử dụng tính năng “List from range”
Để đáp ứng các yêu cầu trên, chúng ta có thể sử dụng tính năng “List from range” trong xác minh dữ liệu. Như thế nào?
Vào bài viết này, chúng ta sẽ tìm hiểu một mẹo trực quan và dễ hiểu về cách thực hiện điều này bước từng bước.
Ví dụ
Hãy xem một ví dụ về phương pháp xác minh dữ liệu như trên.
Trong Google Sheets, danh sách của chúng ta nằm trong ô D2:D11 và chứa tên các 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à ví dụ. Đó có thể là tên sản phẩm, tên sách, tên quốc gia, mã sản phẩm, số chuyến bay, mã trạm, hoặc bất kỳ danh sách nào khác.
Chúng ta muốn ngăn người dùng chỉ nhập tên tệp vào phạm vi A2:A từ danh sách D2:D11 và theo đúng thứ tự tên tệp trong danh sách D2:D11. Tuy nhiên, người dùng có quyền nhập tên tệp nhiều lần mà vẫn tuân thủ điều kiện trên.
Nếu bạn nhìn vào cột “dữ liệu đầu vào sai”, bạn sẽ hiểu tại sao các giá trị đó là sai. “File 4” đứng trước “File 3”, vi phạm yêu cầu.
Chúng ta sẽ sử dụng hai ô trợ giúp chứa hai công thức duy nhất. Sau đó, thay vì sử dụng danh sách ban đầu D2:D11, chúng ta sẽ tạo danh sách thả xuống từ hai ô trợ giúp đó.
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
Hãy sử dụng ô F2 để tạo hai giá trị cần thiết để tạo danh sách thả xuống. Công thức 1 sẽ được đưa vào ô F2.
Đây là công thức 1:
=ifna(indirect(ArrayFormula("A"&MATCH(2,1/(A:A<>""),1))), D2)
Giải thích công thức trên:
Phần ArrayFormula(“A”&MATCH(2,1/(A:A<>””),1)) sẽ trả về ID ô cuối cùng không trống (nếu có) trong cột A.
Sau đó, hàm Indirect sẽ sử dụng ID đó để trả về giá trị từ ô cuối cùng không trống (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 đó, hàm IFNA sẽ trả về giá trị từ ô D2, đó là giá trị đầu tiên trong danh sách.
Cùng xem hình ảnh bên dưới để hiểu rõ hơn về những gì xảy ra trong ô F2.
Công thức 2 – “Giá trị tiếp theo” trong danh sách
Công thức thứ hai này sẽ trả về giá trị tiếp theo nằm ngay bên dưới kết quả của công thức thứ nhất. Hãy viết công thức này vào ô F3.
=iferror(if(counta(A:A)=0,"",index(D2:D11,match(F2,D2:D11,0)+1)))
Giải thích công thức trê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 trong IF là hàm COUNTA như sau:
if(counta(A:A)=0
Nếu số lượng giá trị trong cột A là 0, thì IF sẽ trả về giá trị trống “”, ngược lại, IF sẽ thực thi phần này của công thức – 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 giá trị từ danh sách theo thứ tự trong Google Sheets.
Tạo danh sách từ phạm vi
Làm theo các bước cuối cùng sau đây:
- Chọn A2:A20 hoặc phạm vi mà bạn muốn danh sách thả xuống xuất hiện.
- Nhấp vào menu Dữ liệu > Xác minh dữ liệu và làm theo cài đặt “List from range” như hình ảnh 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 thành công!
Sample_Sheet_11920
Để biết thêm thông tin hữu ích về Google Sheets và các tính năng khác, hãy truy cập Crawlan.com.
