Cách sử dụng công thức tra cứu trong xác thực dữ liệu trong Excel
Câu hỏi gốc: –
Điều tôi muốn làm nghe có vẻ đơn giản nhưng tôi chưa tìm ra giải pháp.
Nếu tên / loại là “cáp”, tôi muốn một danh sách thả xuống cho “att” chỉ cho phép các giá trị code1.
Nếu tên / loại là “htrc”, tôi muốn một danh sách thả xuống cho “att” chỉ cho phép các giá trị code2.
Những gì tôi đã thử là:
-
xác định phạm vi được đặt tên gọi là “mã” cho $ A: $ A trên sheet1 – xác thực dữ liệu cho B2 trên sheet2 và sử dụng công thức:
IF ($ A2 = “cable”; INDIRECT (mã = “code1”); INDIRECT (mã = “code2”))
Khi tôi đang nhập, tôi nhận được thông báo cho biết “Nguồn hiện đánh giá là có lỗi. Bạn có muốn tiếp tục không?” và menu thả xuống trống…
Cách đăng ký
Chúng tôi tạo danh sách thả xuống và chúng tôi sử dụng “OFFSET”, “MATCH”, “VLOOKUP” và “COUNTIF” để xác thực dữ liệu.
Làm theo các bước sau: – Chọn ô B2 trong sheet 2.
-
Chuyển đến tab Dữ liệu> Xác thực dữ liệu> Cài đặt> Danh sách.
-
Nhập công thức: – = OFFSET (mã, MATCH (VLOOKUP (G6, Tên khóa, 2, 0), mã, 0) -1, 1, COUNTIF (mã, VLOOKUP (G6, Tên khóa, 2, 0)), 1 )
-
Nhấp vào ok.
-
Sao chép xác thực Dữ liệu vào dán trong phạm vi.
Tình huống:
Làm thế nào để giải quyết vấn đề?
Ở đây chúng ta có một nhiệm vụ chỉ cho phép các mục không có dấu chấm câu. Vì vậy, chúng ta cần tập trung vào một danh sách các dấu câu để kiểm tra.
Việc sử dụng các chức năng và một công cụ sẽ giúp chúng ta giải quyết vấn đề.
Công thức được sử dụng trong công cụ Xác thực dữ liệu. Công thức kiểm tra điều kiện và trả về True hoặc False. Ở đây True có nghĩa là dấu chấm câu không tồn tại, vì vậy công cụ chấp nhận giá trị.
Công thức chung:
Giải thích:
-
Hàm FIND trả về chỉ số xuất hiện của bất kỳ dấu câu nào trong ô kiểm tra.
-
Hàm COUNT đếm các số và đối sánh kết quả với 0.
Ví dụ:
Tất cả những điều này có thể khó hiểu. Vì vậy, hãy kiểm tra công thức này bằng cách chạy nó trên ví dụ được hiển thị bên dưới. Ở đây chúng tôi sẽ áp dụng xác thực dữ liệu trên ô để hạn chế người dùng nhập bất kỳ giá trị nào có dấu chấm câu. Vì vậy, dưới đây là công thức để sử dụng trong công cụ xác thực dữ liệu.
Sử dụng công thức:
$: đóng băng tham chiếu $ E $ 4: $ E $ 10: mảng tất cả các dấu câu C4: ô cần kiểm tra
Ở đây chúng ta có dấu chấm câu trong mảng E4: E10 và để kiểm tra là ô C4.
Bây giờ ở đây công thức được sử dụng cho ô C4. Sao chép công thức sang các ô còn lại khác bằng phím tắt Ctrl + D. Bây giờ chúng ta sẽ nhập các giá trị vào ô để kiểm tra.
Bây giờ chúng ta sử dụng giá trị với dấu chấm câu.
Như bạn có thể thấy, giá trị không được Excel chấp nhận và xuất hiện lỗi “Giá trị này không khớp với các hạn chế xác thực dữ liệu được xác định cho ô này”. Bạn cũng có thể tùy chỉnh thông báo lỗi và thông báo cảnh báo để cho người dùng biết những giá trị nào bị cấm. Chỉnh sửa các giá trị như hình dưới đây.
Như bạn có thể thấy, có một biển cảnh báo “Không có dấu chấm câu”. Bây giờ hãy chỉnh sửa thông báo lỗi.
Như bạn có thể thấy, bạn nhận được thông báo thận trọng và cảnh báo theo yêu cầu. Thông báo cảnh báo Lỗi này sẽ được hiển thị bất cứ khi nào một giá trị dấu câu được sử dụng.
Bây giờ điền vào cơ sở dữ liệu của bạn mà không do dự.
Như bạn có thể thấy ở đây là tất cả các giá trị không có dấu chấm câu.
Dưới đây là tất cả các lưu ý quan sát liên quan đến việc sử dụng công thức.
Ghi chú:
-
Áp dụng công cụ xác thực dữ liệu để hạn chế người dùng nhập giá trị không hợp lệ không thỏa mãn điều kiện.
-
Công cụ xác thực dữ liệu có thể hoạt động với cả số và văn bản.
-
Hàm COUNT và FIND, cả hai đều trả về một giá trị số.
-
Các toán tử như bằng (=), nhỏ hơn bằng (⇐), lớn hơn (>) hoặc không bằng (<>) chỉ có thể được thực hiện trong hàm được áp dụng với số.
Hy vọng bài viết này về Cách sử dụng công thức Tra cứu trong Xác thực Dữ liệu trong Excel là giải thích. Tìm thêm các bài viết về chức năng Ngày & Giờ tại đây.
Nếu bạn thích blog của chúng tôi, hãy chia sẻ nó với bạn bè của bạn trên Facebook. Và bạn cũng có thể theo dõi chúng tôi trên Twitter và Facebook. Chúng tôi rất muốn nghe ý kiến của bạn, hãy cho chúng tôi biết cách chúng tôi có thể cải thiện, bổ sung hoặc đổi mới công việc của mình và làm cho nó tốt hơn cho bạn. Viết thư cho chúng tôi tại [email protected]
Bài viết liên quan:
Hộp công thức trong xác thực dữ liệu cho phép bạn chọn loại hạn chế bắt buộc.