Computer >> Máy Tính >  >> Phần mềm >> Office

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

Xác thực dữ liệu là một tính năng quan trọng trong Excel. Trong bài viết này, chúng ta sẽ xem cách xác thực dữ liệu Excel dựa trên một ô khác được tạo ra như thế nào. Xác thực dữ liệu làm cho danh sách trở nên sáng tạo và thân thiện với người dùng hơn. Thay vì có dữ liệu trong các ô khác nhau của một cột, bạn có tùy chọn để chọn bất kỳ dữ liệu nào dựa trên danh sách trong một ô. Ở đây trong bài viết này, chúng ta sẽ thấy quá trình tạo danh sách phụ thuộc bằng cách sử dụng Xác thực dữ liệu Excel. Chúng ta cũng sẽ thấy quá trình hạn chế nhập dữ liệu trong một loạt ô có xác thực dữ liệu.

Xác thực dữ liệu trong Excel là gì?

Xác thực dữ liệu là một tính năng của Excel mà qua đó bạn có thể tạo các quy tắc mà bạn muốn nhập loại dữ liệu nào vào ô. Vì vậy, về cơ bản, nó cho phép bạn áp dụng bất kỳ quy tắc nào trong khi nhập bất kỳ dữ liệu nào. Có rất nhiều quy tắc xác nhận khác nhau. Ví dụ:bạn chỉ có thể cho phép các giá trị số hoặc văn bản trong một ô thông qua xác thực dữ liệu hoặc có thể cho phép các giá trị số trong một phạm vi cụ thể. Xác thực dữ liệu có thể hạn chế ngày và giờ nằm ​​ngoài phạm vi đã cho. Nó giúp chúng ta kiểm tra độ chính xác và chất lượng của dữ liệu trước khi sử dụng. Xác thực dữ liệu cung cấp một số kiểm tra để đảm bảo tính nhất quán của dữ liệu đầu vào hoặc dữ liệu được lưu trữ.

Cách xác thực dữ liệu trong Excel

Để xác thực dữ liệu trong Excel, bạn cần xác định các quy tắc xác thực dữ liệu. Sau đó, nếu bạn nhập bất kỳ dữ liệu nào, quá trình xác thực dữ liệu sẽ hoạt động trên đó. Nếu dữ liệu đáp ứng các quy tắc xác thực dữ liệu, nó sẽ đưa dữ liệu vào ô. Nếu không, nó sẽ không hiển thị thông báo lỗi.

Đầu tiên, hãy lấy một tập dữ liệu bao gồm thẻ sinh viên, tên sinh viên và tuổi. Chúng tôi muốn xác thực dữ liệu trong đó độ tuổi phải nhỏ hơn 18.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

Sau đó, chọn ô D11 . Sau đó, đi tới Dữ liệu trên dải băng. Sau đó, chọn tùy chọn thả xuống Xác thực dữ liệu từ Công cụ dữ liệu nhóm.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

Do đó, Xác thực dữ liệu hộp thoại sẽ xuất hiện. Từ đó, chọn Cài đặt chuyển hướng. Sau đó, chọn Số nguyên từ Cho phép tiết diện. Sau đó, hãy chọn mục Bỏ qua khoảng trống quyền mua. Tiếp theo, chọn nút Ít hơn tùy chọn từ Ngày . Sau đó, đặt Tối đa giá trị là 18 . Cuối cùng, nhấp vào OK .

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

Tiếp theo, nếu chúng tôi viết 20 là tuổi, nó sẽ hiển thị lỗi vì nó vượt quá giới hạn tối đa của chúng tôi trong việc xác thực dữ liệu. Đó là những gì chúng tôi nhận được từ xác thực dữ liệu.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

4 Ví dụ Thích hợp để Thực hiện Xác thực Dữ liệu Dựa trên Một Ô khác trong Excel

Để sử dụng xác thực dữ liệu dựa trên một ô khác trong Excel, chúng tôi đã tìm thấy 4 ví dụ khác nhau mà qua đó bạn có thể hình dung rõ ràng. Trong bài viết này, chúng tôi sẽ sử dụng hàm INDIRECT và phạm vi được đặt tên để sử dụng xác thực dữ liệu. Chúng tôi cũng sẽ sử dụng tham chiếu ô và cách hạn chế mục nhập giá trị để xác thực dữ liệu. Tất cả các phương pháp này đều khá dễ sử dụng. Để hiểu rõ những điều này, hãy làm theo đúng phương pháp.

1. Áp dụng hàm INDIRECT

Phương pháp đầu tiên của chúng tôi dựa trên việc sử dụng hàm INDIRECT . Trong phương pháp này, chúng tôi muốn sử dụng INDIRECT này trong hộp thoại xác thực dữ liệu. Chức năng này giúp chúng ta thay đổi tùy chọn sổ xuống theo một ô nhất định. Chúng tôi lấy một tập dữ liệu bao gồm hai mục và các loại khác nhau của chúng.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

Để hiểu rõ phương pháp, hãy làm theo các bước

Các bước

  • Đầu tiên, hãy chuyển đổi cả ba cột thành các bảng khác nhau.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Sau đó, chọn phạm vi ô B5 đến B6 .
  • Do đó, Thiết kế bảng tab sẽ xuất hiện.
  • Đi tới Thiết kế bảng trên dải băng.
  • Sau đó, thay đổi Tên Bảng từ Thuộc tính nhóm.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Sau đó, chọn phạm vi ô D5 tới D9 .
  • Thay đổi Tên Bảng từ Thuộc tính nhóm.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Cuối cùng, chọn phạm vi ô F5 đến F9 .
  • Sau đó, thay đổi Tên bảng từ nhóm Thuộc tính giống như cách trước.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Sau đó, chuyển đến Công thức trên dải băng.
  • Chọn Xác định tên từ Xác định tên nhóm.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Sau đó, Tên mới hộp thoại sẽ xuất hiện.
  • Đặt tên.
  • Trong phần Tham khảo, hãy viết ra những điều sau.
=Items[Item]

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Nhấp vào OK .
  • Sau đó, tạo hai cột mới mà chúng tôi muốn thêm xác thực dữ liệu.
  • Sau đó, chọn ô H5 .

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Sau đó, chuyển đến Dữ liệu trên dải băng.
  • Sau đó, chọn tùy chọn thả xuống Xác thực dữ liệu từ Công cụ dữ liệu nhóm.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Do đó, Xác thực dữ liệu hộp thoại sẽ xuất hiện.
  • Đầu tiên, chọn Cài đặt ở trên cùng.
  • Sau đó, chọn Danh sách từ Cho phép
  • Sau đó, chọn hộp Bỏ qua chỗ trống và trình đơn thả xuống trong ô tùy chọn.
  • Sau đó, ghi lại những điều sau trong Nguồn phần.
=Item
  • Cuối cùng, nhấp vào OK .

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Do đó, bạn sẽ nhận được tùy chọn thả xuống sau đây, nơi bạn có thể chọn kem hoặc nước trái cây.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • chọn ô I5 .
  • Sau đó, chuyển đến Dữ liệu trên dải băng.
  • Sau đó, chọn tùy chọn thả xuống Xác thực dữ liệu từ Công cụ dữ liệu nhóm.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Do đó, Xác thực dữ liệu hộp thoại sẽ xuất hiện.
  • Đầu tiên, chọn Cài đặt ở trên cùng.
  • Sau đó, chọn Danh sách từ Cho phép phần.
  • Sau đó, chọn hộp Bỏ qua chỗ trống và trình đơn thả xuống trong ô tùy chọn.
  • Sau đó, ghi lại những điều sau trong Nguồn phần.
=INDIRECT(H5)
  • Cuối cùng, nhấp vào OK .

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Do đó, bạn sẽ nhận được tùy chọn thả xuống sau đây, nơi bạn có thể chọn bất kỳ hương vị nào. Ở đây, chúng ta có được hương vị kem sau đây.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Bây giờ, nếu chúng tôi chọn nước trái cây từ danh sách mặt hàng, hương vị sẽ thay đổi theo.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

2. Sử dụng Dải ô được Đặt tên

Phương pháp thứ hai của chúng tôi dựa trên việc sử dụng phạm vi được đặt tên. Trong phương pháp này, bạn có thể áp dụng tên cho phạm vi trong Bảng. Sau đó, sử dụng tên bảng này trong hộp thoại xác thực dữ liệu. Chúng tôi lấy một tập dữ liệu bao gồm trang phục, màu sắc và kích cỡ.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

Để hiểu phương pháp, hãy làm theo các bước.

Các bước

  • Đầu tiên, hãy tạo một bảng bằng tập dữ liệu.
  • Để thực hiện việc này, hãy chọn phạm vi ô B4 tới D9 .

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Sau đó, chuyển đến Chèn trên dải băng.
  • Chọn Bảng từ Bảng nhóm.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Kết quả là chúng ta sẽ nhận được kết quả sau, Hãy xem ảnh chụp màn hình.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Tiếp theo, chuyển đến Công thức trên dải băng.
  • Chọn Xác định tên từ Xác định tên nhóm.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Sau đó, Tên mới hộp thoại sẽ xuất hiện.
  • Đặt tên.
  • Trong phần Tham khảo, hãy viết ra những điều sau.
=Table1[Dress]
  • Sau đó, nhấp vào OK .

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Sau đó, chọn lại Xác định tên từ Xác định tên nhóm.
  • Sau đó, Tên mới hộp thoại sẽ xuất hiện.
  • Đặt tên.
  • Trong phần Tham khảo, hãy viết ra những điều sau.
=Table1[Color]
  • Sau đó, nhấp vào OK .

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Cũng thực hiện quy trình tương tự đối với kích thước.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Bây giờ, hãy tạo ba cột mới.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Sau đó, chọn F5 .
  • Sau đó, chuyển đến Dữ liệu trên dải băng.
  • Sau đó, chọn Xác thực dữ liệu tùy chọn thả xuống từ Công cụ dữ liệu nhóm.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Do đó, Xác thực dữ liệu hộp thoại sẽ xuất hiện.
  • Đầu tiên, chọn Cài đặt ở trên cùng.
  • Sau đó, chọn Danh sách từ Cho phép
  • Sau đó, chọn hộp Bỏ qua chỗ trống và trình đơn thả xuống trong ô tùy chọn.
  • Sau đó, hãy ghi lại những điều sau trong phần Nguồn.
=Dress
  • Cuối cùng, nhấp vào OK .

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Do đó, chúng tôi sẽ nhận được các tùy chọn thả xuống sau đây cho trang phục.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Sau đó, chọn G5 .
  • Sau đó, chuyển đến Dữ liệu trên dải băng.
  • Sau đó, chọn Xác thực dữ liệu tùy chọn thả xuống từ Công cụ dữ liệu nhóm.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Do đó, Xác thực dữ liệu hộp thoại sẽ xuất hiện.
  • Đầu tiên, chọn Cài đặt ở trên cùng.
  • Sau đó, chọn Danh sách từ Cho phép phần.
  • Sau đó, chọn hộp Bỏ qua chỗ trống và trình đơn thả xuống trong ô tùy chọn.
  • Sau đó, hãy ghi lại những điều sau trong phần Nguồn.
=Color
  • Cuối cùng, nhấp vào OK .

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Do đó, chúng tôi sẽ nhận được các tùy chọn thả xuống sau cho màu sắc

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Sau đó, chọn H5 .
  • Sau đó, chuyển đến Dữ liệu trên dải băng.
  • Sau đó, chọn tùy chọn thả xuống Xác thực dữ liệu từ Công cụ dữ liệu nhóm.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Do đó, Xác thực dữ liệu hộp thoại sẽ xuất hiện.
  • Đầu tiên, chọn Cài đặt ở trên cùng.
  • Sau đó, chọn Danh sách từ Cho phép phần.
  • Sau đó, chọn hộp Bỏ qua chỗ trống và trình đơn thả xuống trong ô tùy chọn.
  • Sau đó, hãy ghi lại những điều sau trong phần Nguồn.
=Size
  • Cuối cùng, nhấp vào OK .

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Do đó, chúng tôi sẽ nhận được các tùy chọn thả xuống sau đây cho kích thước.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

3. Áp dụng tham chiếu ô trong xác thực dữ liệu

Phương pháp thứ ba của chúng tôi dựa trên việc sử dụng tham chiếu ô trực tiếp trong xác thực dữ liệu. Trong phương pháp này, chúng tôi muốn sử dụng tham chiếu ô trong hộp thoại xác thực dữ liệu. Do đó, nó sẽ cung cấp cho chúng tôi một tùy chọn thả xuống. Ở đây, chúng tôi lấy một tập dữ liệu bao gồm các tiểu bang và số tiền bán hàng của chúng.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

Để hiểu phương pháp, hãy làm theo các bước.

Các bước

  • Đầu tiên, hãy tạo hai ô mới bao gồm trạng thái và số tiền bán hàng.
  • Sau đó, chọn ô F4 .

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Sau đó, chuyển đến Dữ liệu trên dải băng.
  • Sau đó, chọn tùy chọn thả xuống Xác thực dữ liệu từ Công cụ dữ liệu nhóm.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Do đó, Xác thực dữ liệu hộp thoại sẽ xuất hiện.
  • Đầu tiên, chọn Cài đặt ở trên cùng.
  • Sau đó, chọn Danh sách từ Cho phép phần.
  • Sau đó, chọn hộp Bỏ qua chỗ trống và trình đơn thả xuống trong ô tùy chọn.
  • Sau đó, chọn phạm vi ô B5 đến B12 .
  • Cuối cùng, nhấp vào OK .

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Do đó, bạn sẽ nhận được một tùy chọn thả xuống, nơi bạn có thể chọn bất kỳ trạng thái nào.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Chúng tôi muốn nhận được số lượng bán hàng ở trạng thái tương ứng.
  • Để thực hiện việc này, hãy chọn ô F5 .
  • Sau đó, viết ra công thức sau bằng hàm Vlookup .
=VLOOKUP(F4,$B$5:$C$12,2,0)

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Nhấp vào Enter để áp dụng công thức.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Sau đó, nếu bạn thay đổi trạng thái từ tùy chọn thả xuống, số tiền bán hàng sẽ tự động thay đổi. Xem ảnh chụp màn hình.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

4. Hạn chế mục nhập giá trị với xác thực dữ liệu

Phương pháp cuối cùng của chúng tôi dựa trên cách hạn chế nhập giá trị bằng xác thực dữ liệu. Trong phương pháp này, chúng tôi muốn sử dụng xác thực dữ liệu và áp dụng một số quy tắc mà thông qua đó, việc nhập dữ liệu bị hạn chế. Nếu bạn nhập bất kỳ dữ liệu nào trong phạm vi đã cho, nó sẽ cho phép chúng tôi đưa nó vào ô, Ngược lại, nó sẽ hiển thị lỗi. Chúng tôi lấy một tập dữ liệu bao gồm ID đơn đặt hàng, mặt hàng, ngày đặt hàng và số lượng.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

Các bước

  • Trong phương pháp này, chúng tôi muốn giới hạn ngày đặt hàng từ ngày 1 tháng 1 năm 2021 đến ngày 5 tháng 5 năm 2022. Bên ngoài phạm vi này sẽ hiển thị lỗi.
  • Để thực hiện việc này, hãy chọn ô D10 .
  • chuyển đến Dữ liệu trên dải băng.
  • Sau đó, chọn Xác thực dữ liệu tùy chọn thả xuống từ Công cụ dữ liệu nhóm.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Do đó, Xác thực dữ liệu hộp thoại sẽ xuất hiện.
  • Đầu tiên, chọn Cài đặt ở trên cùng.
  • Sau đó, chọn Ngày từ Cho phép phần.
  • Sau đó, chọn hộp Bỏ qua chỗ trống tùy chọn.
  • Chọn giữa từ tùy chọn Ngày phần.
  • Sau đó, đặt ngày bắt đầu và ngày kết thúc.
  • Cuối cùng, nhấp vào OK .

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Bây giờ, nếu chúng ta đặt ngày vào ô D10 nằm ngoài phạm vi, nó sẽ hiển thị cho chúng ta một lỗi. Xem ảnh chụp màn hình.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

Cách xác thực dữ liệu dựa trên ô liền kề trong Excel

Chúng tôi có thể xác thực dữ liệu dựa trên một ô liền kề. Ví dụ:bạn xác định một văn bản nhất định trong ô liền kề, Bây giờ, nếu bạn đặt văn bản đó vào xác thực dữ liệu và xác định rằng không có cách nào để viết trên cột tiếp theo cho đến khi điều kiện đáp ứng. Bạn có thể dễ dàng làm điều đó trong ô liền kề. Chúng tôi lấy một tập dữ liệu bao gồm một số bài kiểm tra, ý kiến ​​và lý do. Chúng tôi muốn viết điều gì đó trong cột lý do nếu ý kiến ​​về kỳ thi là khó.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

Để hiểu quy trình, hãy làm theo các bước.

Các bước

  • Đầu tiên, chọn phạm vi ô D5 tới D9 .

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Sau đó, chuyển đến Dữ liệu trên dải băng.
  • Sau đó, chọn Xác thực dữ liệu tùy chọn thả xuống từ Công cụ dữ liệu nhóm.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Do đó, Xác thực dữ liệu hộp thoại sẽ xuất hiện.
  • Đầu tiên, chọn Cài đặt ở trên cùng.
  • Sau đó, chọn Tùy chỉnh từ Cho phép phần.
  • Sau đó, hãy viết lại công thức sau trong Công thức phần.
=$C5="Hard"
  • Cuối cùng, nhấp vào OK .

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

  • Sau đó, bạn có thể thêm mô tả trong các cột lý do khi giá trị ô liền kề là Khó .
  • Tuy nhiên, nếu chúng tôi cố gắng thêm mô tả khi giá trị ô liền kề khác nhau, thì nó sẽ hiển thị lỗi cho chúng tôi.

Xác thực dữ liệu dựa trên một ô khác trong Excel (4 Ví dụ)

Kết luận

Trong bài viết này, chúng ta đã biết cách tạo danh sách bằng cách sử dụng Xác thực dữ liệu Excel. Chúng tôi đã tạo một danh sách phụ thuộc bằng cách xác thực dữ liệu Excel dựa trên một ô khác, nơi chúng tôi đã sử dụng INDIRECT hàm số. Chúng tôi đã thấy cách nhập dữ liệu có thể bị hạn chế bằng cách sử dụng xác thực dữ liệu dựa trên một ô khác. Bài viết này có thể hữu ích cho nhiều hoạt động thống kê. Hy vọng bạn sẽ thích bài viết này. Hãy cứ ổn và bình luận bên dưới nếu bạn gặp bất kỳ khó khăn nào liên quan đến bài viết này. Đừng quên truy cập Exceldemy của chúng tôi trang.

Các bài viết liên quan

  • Chỉ xác thực dữ liệu Excel bằng chữ và số (Sử dụng Công thức Tùy chỉnh)