Ô

Áp dụng xác thực dữ liệu cho ô

Sử dụng xác thực dữ liệu để hạn chế loại dữ liệu hoặc giá trị mà người dùng nhập vào một ô, chẳng hạn như danh sách thả xuống.

Trình duyệt của bạn không hỗ trợ video. Hãy cài đặt Microsoft Silverlight, Adobe Flash Player hoặc Internet Explorer 9.

Chọn (các) ô bạn muốn tạo quy tắc.

  1. Chọn Dữ liệu >lệ Dữ liệu.

    Xác nhận tính hợp lệ Dữ liệu

  2. Trên tab Cài đặt , trong Cho phép, chọn một tùy chọn:

    • Số Nguyên - để hạn chế ô chỉ chấp nhận số nguyên.

    • Thập phân - để hạn chế ô chỉ chấp nhận số thập phân.

    • Danh sách - để chọn dữ liệu từ danh sách thả xuống.

    • Ngày - để hạn chế ô chỉ chấp nhận ngày.

    • Thời gian - để hạn chế ô chỉ chấp nhận thời gian.

    • Độ dài Văn bản - để hạn chế độ dài của văn bản.

    • Tùy chỉnh – cho công thức tùy chỉnh.

  3. Trong Dữ liệu, chọn một điều kiện.

  4. Đặt các giá trị bắt buộc khác dựa trên giá trị bạn đã chọn cho Cho phép Dữ liệu.

  5. Chọn tab Thông báo Nhập và tùy chỉnh thông báo mà người dùng sẽ thấy khi nhập dữ liệu.

  6. Chọn hộp kiểm Hiển thị thông báo nhập khi ô được chọn để hiển thị thông báo khi người dùng chọn hoặc di chuột qua (các) ô đã chọn.

  7. Chọn tab Cảnh báo Lỗi để tùy chỉnh thông báo lỗi và chọn Kiểu.

  8. Chọn OK.

    Bây giờ, nếu người dùng cố nhập giá trị không hợp lệ, Cảnh báo Lỗi sẽ xuất hiện cùng với thông báo tùy chỉnh của bạn.

Tải xuống ví dụ của chúng tôi

Tải xuống sổ làm việc ví dụ với tất cả các ví dụ về xác thực dữ liệu trong bài viết này

Nếu bạn đang tạo một trang tính yêu cầu người dùng phải nhập dữ liệu, bạn có thể muốn hạn chế mục nhập trong một phạm vi ngày hoặc số nhất định hoặc đảm bảo chỉ nhập số nguyên dương. Excel có thể hạn chế mục nhập dữ liệu ở một số ô nhất định bằng cách sử dụng xác thực dữ liệu, nhắc người dùng nhập dữ liệu hợp lệ khi một ô được chọn và hiển thị thông báo lỗi khi người dùng nhập dữ liệu không hợp lệ.

Hạn chế mục nhập dữ liệu

  1. Chọn các ô mà bạn muốn hạn chế nhập dữ liệu.

  2. Trên tab Dữ liệu , chọn Xác thực Dữ liệu > lệ Dữ liệu.

    Lưu ý: Nếu lệnh xác thực không sẵn dùng, trang tính có thể được bảo vệ hoặc sổ làm việc có thể được chia sẻ. Bạn không thể thay đổi cài đặt xác nhận tính hợp lệ dữ liệu nếu sổ làm việc được chia sẻ hoặc trang tính được bảo vệ. Để biết thêm thông tin về việc bảo vệ sổ làm việc, hãy xem Bảo vệ sổ làm việc.

  3. Trong hộp Cho phép, hãy chọn loại dữ liệu bạn muốn cho phép, rồi điền các tiêu chí và giá trị giới hạn.

    Lưu ý: Các hộp nơi bạn nhập giá trị giới hạn được dán nhãn dựa trên dữ liệu và giới hạn tiêu chí mà bạn đã chọn. Ví dụ: nếu bạn chọn Ngày làm kiểu dữ liệu của mình, bạn có thể nhập giới hạn giá trị vào các hộp giá trị tối thiểu và tối đa có nhãn Ngày Bắt đầu và Ngày Kết thúc.

Nhắc người dùng về các mục nhập hợp lệ

Khi người dùng chọn ô có yêu cầu nhập dữ liệu, bạn có thể hiển thị thông báo giải thích dữ liệu nào hợp lệ.

  1. Chọn các ô mà bạn muốn nhắc người dùng nhập dữ liệu hợp lệ.

  2. Trên tab Dữ liệu , chọn Xác thực Dữ liệu > lệ Dữ liệu.

    Lưu ý: Nếu lệnh xác thực không sẵn dùng, trang tính có thể được bảo vệ hoặc sổ làm việc có thể được chia sẻ. Bạn không thể thay đổi cài đặt xác nhận tính hợp lệ dữ liệu nếu sổ làm việc được chia sẻ hoặc trang tính được bảo vệ. Để biết thêm thông tin về việc bảo vệ sổ làm việc, hãy xem Bảo vệ sổ làm việc.

  3. Trên tab Thông báo Nhập , chọn hộp kiểm Hiển thị thông báo nhập khi ô được chọn.

  4. Trong hộp Tiêu đề , hãy nhập tiêu đề cho thư của bạn.

  5. Trong hộp Thông báo đầu vào, nhập thông báo bạn muốn hiển thị.

Hiển thị thông báo lỗi khi nhập dữ liệu không hợp lệ

Nếu bạn có các hạn chế dữ liệu tại chỗ và người dùng nhập dữ liệu không hợp lệ vào một ô, bạn có thể hiển thị thông báo giải thích lỗi.

  1. Chọn ô mà bạn muốn hiển thị thông báo lỗi.

  2. Trên tab Dữ liệu , bấm vào Xác thực Dữ liệu > lệ Dữ liệu .

    Lưu ý: Nếu lệnh xác thực không sẵn dùng, trang tính có thể được bảo vệ hoặc sổ làm việc có thể được chia sẻ. Bạn không thể thay đổi cài đặt xác nhận tính hợp lệ dữ liệu nếu sổ làm việc được chia sẻ hoặc trang tính được bảo vệ. Để biết thêm thông tin về việc bảo vệ sổ làm việc, hãy xem Bảo vệ sổ làm việc .

  3. Trên tab Cảnh báo Lỗi , trong hộp Tiêu đề , nhập tiêu đề cho thư của bạn.

  4. Trong hộp Thông báo lỗi , nhập thông báo bạn muốn hiển thị nếu dữ liệu không hợp lệ được nhập.

  5. Thực hiện một trong những thao tác sau:

    Để

    Trên menu bật lên Kiểu, chọn

    Yêu cầu người dùng khắc phục lỗi trước khi tiếp tục

    Ngừng

    Cảnh báo người dùng rằng dữ liệu không hợp lệ và yêu cầu họ chọn Có hoặc Không để cho biết họ có muốn tiếp tục không

    Cảnh báo

    Cảnh báo người dùng rằng dữ liệu không hợp lệ nhưng cho phép họ tiếp tục sau khi bỏ qua thông báo cảnh báo

    Quan trọng

Thêm xác thực dữ liệu vào một ô hoặc dải ô

Lưu ý: Hai bước đầu tiên trong mục này là để thêm bất kỳ loại xác thực dữ liệu nào. Các bước 3-7 dành riêng cho việc tạo danh sách thả xuống.

  1. Chọn một hoặc nhiều ô để xác thực.

  2. Trên tab Dữ liệu , trong nhóm Công cụ Dữ liệu , chọn Xác thực Dữ liệu.

  3. Trên tab Cài đặt, trong hộp Cho phép , chọn Danh sách.

  4. Trong hộp Nguồn, nhập giá trị danh sách của bạn, được phân tách bởi dấu phẩy. Ví dụ: nhập Thấp,Trung bình,Cao.

  5. Đảm bảo đã chọn hộp kiểm Thả xuống trong ô. Nếu không, bạn sẽ không thấy mũi tên thả xuống bên cạnh ô.

  6. Để xác định cách bạn muốn xử lý các giá trị trống (null), hãy chọn hoặc bỏ chọn hộp kiểm Bỏ qua giá trị trống.

  7. Kiểm tra xác thực dữ liệu để đảm bảo rằng tính năng này đang hoạt động đúng. Thử nhập cả dữ liệu hợp lệ và không hợp lệ vào ô để đảm bảo rằng thiết đặt của bạn đang hoạt động như dự kiến và các thông báo xuất hiện khi bạn mong đợi.

Lưu ý: 

  • Sau khi bạn tạo danh sách thả xuống, hãy đảm bảo danh sách hoạt động theo cách bạn muốn. Ví dụ: bạn có thể muốn kiểm tra xem liệu ô có đủ rộng để hiển thị tất cả các mục nhập của bạn hay không.

  • Loại bỏ xác thực dữ liệu - Chọn ô hoặc các ô có chứa xác thực mà bạn muốn xóa, đi đến Xác thực Dữ liệu > và trong hộp thoại xác thực dữ liệu, nhấn Xóa Tất cả, rồi chọn OK.

Bảng sau đây liệt kê các kiểu kiểm soát dữ liệu khác và cho bạn biết cách thêm nó vào trang tính.

Để thực hiện điều này:

Hãy làm theo các bước sau:

Giới hạn mục nhập dữ liệu ở số nguyên nằm trong giới hạn.

  1. Làm theo các bước 1-2 ở trên.

  2. Từ danh sách Cho phép, chọn Số nguyên.

  3. Trong hộp Dữ liệu, hãy chọn kiểu hạn chế bạn muốn. Ví dụ: để đặt giới hạn trên và giới hạn dưới, hãy chọn giữa.

  4. Nhập giá trị tối thiểu, tối đa hoặc cụ thể để cho phép.

    Bạn cũng có thể nhập công thức trả về giá trị số.

    Ví dụ, giả sử bạn đang xác thực dữ liệu ở ô F1. Để đặt giới hạn khấu trừ tối thiểu bằng hai lần số con trong ô đó, hãy chọn lớn hơn hoặc bằng trong hộp Dữ liệu và nhập công thức, =2*F1 , vào hộp Tối thiểu .

Giới hạn mục nhập dữ liệu ở số thập phân nằm trong giới hạn.

  1. Làm theo các bước 1-2 ở trên.

  2. Trong hộp Cho phép, chọn Thập phân.

  3. Trong hộp Dữ liệu, hãy chọn kiểu hạn chế bạn muốn. Ví dụ: để đặt giới hạn trên và giới hạn dưới, hãy chọn giữa.

  4. Nhập giá trị tối thiểu, tối đa hoặc cụ thể để cho phép.

    Bạn cũng có thể nhập công thức trả về giá trị số. Ví dụ, để đặt giới hạn tối đa cho tiền hoa hồng và tiền thưởng là 6% tiền lương của nhân viên bán hàng trong ô E1, hãy chọn nhỏ hơn hoặc bằng trong hộp Dữ liệu và nhập công thức, =E1*6% , vào hộp Tối đa.

    Lưu ý: Để cho phép người dùng nhập tỷ lệ phần trăm, ví dụ 20%, hãy chọn Thập phân trong hộp Cho phép, chọn kiểu hạn chế bạn muốn trong hộp Dữ liệu, nhập giá trị tối thiểu, tối đa hoặc cụ thể dưới dạng thập phân, ví dụ: 0,2 , rồi hiển thị ô xác thực dữ liệu dưới dạng tỷ lệ phần trăm bằng cách chọn ô đó và bấm Kiểu Phần trăm Ảnh nút trong nhóm Số trên tab Trang đầu.

Giới hạn mục nhập dữ liệu ở một ngày trong phạm vi ngày.

  1. Làm theo các bước 1-2 ở trên.

  2. Trong hộp Cho phép , chọn Ngày .

  3. Trong hộp Dữ liệu, hãy chọn kiểu hạn chế bạn muốn. Ví dụ: để cho phép các ngày sau một ngày nhất định, hãy chọn lớn hơn.

  4. Nhập bắt đầu, ngày kết thúc hoặc ngày cụ thể để cho phép.

    Bạn cũng có thể nhập công thức trả về ngày. Ví dụ: để đặt khung thời gian giữa ngày hôm nay và 3 ngày kể từ ngày hôm nay, hãy chọn giữa trong hộp Dữ liệu, nhập =TODAY() vào hộp Ngày bắt đầu, rồi nhập =TODAY()+3 vào hộp Ngày kết thúc.

Giới hạn mục nhập dữ liệu ở thời gian trong một khung thời gian.

  1. Làm theo các bước 1-2 ở trên.

  2. Trong hộp Cho phép , chọn Thời gian.

  3. Trong hộp Dữ liệu, hãy chọn kiểu hạn chế bạn muốn. Ví dụ: để cho phép các thời gian trước một thời điểm nhất định trong ngày, hãy chọn nhỏ hơn.

  4. Nhập thời gian bắt đầu, thời gian kết thúc hoặc thời gian cụ thể để cho phép. Nếu bạn muốn nhập thời gian cụ thể, hãy dùng định dạng thời gian hh:mm.

    Ví dụ: giả sử bạn thiết lập ô E2 với thời gian bắt đầu (8:00 SA) và ô F2 với thời gian kết thúc (5:00 CH) và bạn muốn giới hạn thời gian họp giữa những thời điểm đó, rồi chọn giữa trong hộp Dữ liệu, nhập =E2 vào hộp Thời gian bắt đầu, rồi nhập =F2 vào hộp Thời gian kết thúc.

Giới hạn mục nhập dữ liệu văn bản ở độ dài được chỉ định.

  1. Làm theo các bước 1-2 ở trên.

  2. Trong hộp Cho phép , chọn Độ dài Văn bản.

  3. Trong hộp Dữ liệu, hãy chọn kiểu hạn chế bạn muốn. Ví dụ: để cho phép tối đa một số ký tự nhất định, hãy chọn nhỏ hơn hoặc bằng.

  4. Trong trường hợp này, chúng tôi muốn giới hạn mục nhập ở 25 ký tự, vì vậy hãy chọn nhỏ hơn hoặc bằng trong hộp Dữ liệu và nhập 25 vào hộp Tối đa.

Tính toán những gì được phép dựa trên nội dung của một ô khác.

  1. Làm theo các bước 1-2 ở trên.

  2. Trong hộp Cho phép, hãy chọn kiểu dữ liệu bạn muốn.

  3. Trong hộp Dữ liệu, hãy chọn kiểu hạn chế bạn muốn.

  4. Trong hộp hoặc các hộp bên dưới hộp Dữ liệu, hãy chọn ô bạn muốn sử dụng để chỉ định những ô được phép.

    Ví dụ: để chỉ cho phép các mục nhập cho một tài khoản nếu kết quả sẽ không vượt quá ngân sách trong ô E1, hãy chọn Cho phép > Số nguyên , Dữ liệu, nhỏ hơn hoặc bằng và Số >Tối đa = =E1.

Lưu ý: 

  • Các ví dụ sau sử dụng tùy chọn Tùy chỉnh nơi bạn viết công thức để đặt điều kiện. Bạn không cần phải lo lắng về việc hộp Dữ liệu hiển thị bất kỳ nội dung nào vì hộp đã bị vô hiệu hóa với tùy chọn Tùy chỉnh.

  • Ảnh chụp màn hình trong bài viết này được chụp ở Excel 2016; nhưng chức năng là giống nhau trong Excel dành cho web.

Để đảm bảo rằng

Hãy nhập công thức này

Ô có chứa một ID sản phẩm (C2) luôn bắt đầu bằng tiền tố chuẩn của "ID-" và ít nhất có 10 ký tự (lớn hơn 9).

=AND(LEFT(C2,3)="ID-",LEN(C2)>9)

Ví dụ 6: Công thức trong xác thực dữ liệu

Ô có chứa tên sản phẩm (D2) chỉ chứa văn bản.

=ISTEXT(D2)

Ví dụ 2: Công thức trong xác thực dữ liệu

Ô có chứa sinh nhật của người đó (B6) phải lớn hơn số năm đặt trong ô B4.

=IF(B6<=(TODAY()-(365*B4)),TRUE,FALSE)

Ví dụ xác thực dữ liệu để giới hạn một mục nhập ở một độ tuổi tối thiểu

Mọi dữ liệu trong dải ô A2:A10 đều chứa dữ liệu duy nhất.

=COUNTIF($A$2:$A$10,A2)=1

Ví dụ 4: Công thức trong xác thực dữ liệu

Lưu ý: Bạn phải nhập công thức xác thực dữ liệu cho ô A2 trước tiên, sau đó sao chép ô A2 sang A3:A10 sao cho tham đối thứ hai tới COUNTIF sẽ khớp với ô hiện tại. Đó là phần A2)=1 sẽ thay đổi thành A3)=1, A4)=1 , v.v.

Đảm bảo rằng mục nhập địa chỉ email trong ô B4 có chứa ký hiệu @.

=ISNUMBER(FIND("@",B4))

Ví dụ xác thực dữ liệu đảm bảo rằng địa chỉ email có chứa ký hiệu @

Mẹo: Nếu bạn là chủ sở hữu doanh nghiệp nhỏ đang tìm kiếm thêm thông tin về cách thiết lập Microsoft 365, hãy truy cập Trợ giúp & tìm hiểu về doanh nghiệp nhỏ.

Bạn muốn xem thêm?

Tạo danh sách thả xuống

Thêm hoặc loại bỏ các mục trong danh sách thả xuống

Xem thêm về xác thực dữ liệu

Bạn cần thêm trợ giúp?

Bạn muốn xem các tùy chọn khác?

Khám phá các lợi ích của gói đăng ký, xem qua các khóa đào tạo, tìm hiểu cách bảo mật thiết bị của bạn và hơn thế nữa.

Cộng đồng giúp bạn đặt và trả lời các câu hỏi, cung cấp phản hồi và lắng nghe ý kiến từ các chuyên gia có kiến thức phong phú.