Ô

Áp dụng xác thực dữ liệu đối với ô

Áp dụng kiểm soát dữ liệu đối với ô

Bạn có thể sử dụng tính nă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 ô. Một trong những mục đích sử dụng phổ biến nhất của tính năng xác thực dữ liệu là để tạo 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.

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

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

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

  2. Chọn dữ liệu >xác thực dữ liệu.

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

  3. Trên tab thiết đặt , bên dưới cho phép, hãy chọn một tùy chọn:

    • Toàn bộ số -để hạn chế ô chỉ chấp nhận toàn bộ số.

    • 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ế ô này 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 – đối với công thức tùy chỉnh.

  4.  Bên dưới dữ liệu, hãy chọn một điều kiện:

    • ư

    • không phải giữa

    • bằng

    • không bằng

    • lớn hơn

    • nhỏ hơn

    • lớn hơn hoặc bằng

    • nhỏ hơn hoặc bằng

  5. Trên tab thiết đặt , bên dưới cho phép, hãy chọn một tùy chọn:

  6. Đặt các giá trị khác bắt buộc, dựa trên những gì bạn đã chọn để cho phépdữ liệu. Ví dụ, nếu bạn chọn giữa, sau đó chọn tối thiểu:tối đa: các giá trị cho (các) ô.

  7. Chọn hộp kiểm bỏ qua hộp kiểm trống nếu bạn muốn bỏ qua dấu cách trống.

  8. Nếu bạn muốn thêm tiêu đề và thông báo cho quy tắc của mình, hãy chọn tab thông báo nhập , rồi nhập tiêu đề và thông báo nhập.

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

  10. Chọn OK.

    Bây giờ, nếu người dùng cố nhập một giá trị không hợp lệ, một cửa sổ bật lên sẽ xuất hiện với thư đó, "giá trị này không khớp với các hạn chế xác thực dữ liệu cho ô này."

Nếu bạn đang tạo một trang tính yêu cầu người dùng nhập dữ liệu, bạn có thể muốn hạn chế mục nhập vào một phạm vi nhất định của ngày hoặc số điện thoại, hoặc đảm bảo rằng chỉ số nguyên dương được nhập vào. Excel có thể hạn chế nhập dữ liệu vào các ô 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ế 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 , hãy bấm xác thực dữ liệu > xác thực 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 và điền các tiêu chí và giá trị hạn chế.

    Lưu ý: Các hộp nơi bạn nhập các giá trị hạn chế sẽ được gắn nhãn dựa trên dữ liệu và hạn chế các 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 sẽ có thể nhập giá trị hạn chế trong hộp tối thiểu và tối đa có nhãn ngày bắt đầungày kết thúc.

Lời nhắc người dùng cho các mục nhập hợp lệ

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

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

  2. Trên tab dữ liệu , hãy bấm xác thực dữ liệu > xác thực 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 nhập , hãy 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ị một thông báo giải thích lỗi đó.

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

  2. Trên tab dữ liệu , hãy bấm xác thực dữ liệu > xác thực 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 đề , hãy nhập tiêu đề cho thư của bạn.

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

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

    Để

    Trên Kiểu menu bật lên, 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 dữ liệu không hợp lệ và yêu cầu họ chọn hoặc không để cho biết họ muốn tiếp tục

    Cảnh báo

    Cảnh báo người dùng dữ liệu không hợp lệ, nhưng cho phép họ tiến hành sau khi bỏ lỡ thông báo cảnh báo

    Quan trọng

Hạn chế 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 , bên dưới công cụ, hãy bấm xácthực.

    Tab Dữ liệu, nhóm Công cụ

    Lưu ý: Nếu lệnh xác thực không sẵn dùng, có thể trang tính đã được bảo vệ hoặc sổ làm việc đang đượ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 menu bật lên cho phép , hãy chọn loại dữ liệu mà bạn muốn cho phép.

  4. Trên menu bật lên dữ liệu , hãy chọn loại tiêu chí hạn chế bạn muốn, rồi nhập giá trị hạn chế.

    Lưu ý: Các hộp nơi bạn nhập các giá trị hạn chế sẽ được gắn nhãn dựa trên dữ liệu và hạn chế các 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 sẽ có thể nhập giá trị hạn chế trong hộp tối thiểu và tối đa có nhãn ngày bắt đầungày kết thúc.

Lời nhắc người dùng cho các mục nhập hợp lệ

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

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

  2. Trên tab dữ liệu , bên dưới công cụ, hãy bấm xácthực.

    Tab Dữ liệu, nhóm Công cụ

    Lưu ý: Nếu lệnh xác thực không sẵn dùng, có thể trang tính đã được bảo vệ hoặc sổ làm việc đang đượ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 nhập , hãy 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ị một thông báo giải thích lỗi đó.

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

  2. Trên tab dữ liệu , bên dưới công cụ, hãy bấm xácthực.

    Tab Dữ liệu, nhóm Công cụ

    Lưu ý: Nếu lệnh xác thực không sẵn dùng, có thể trang tính đã được bảo vệ hoặc sổ làm việc đang đượ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 đề , hãy nhập tiêu đề cho thư của bạn.

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

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

    Để

    Trên Kiểu menu bật lên, 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 dữ liệu không hợp lệ và yêu cầu họ chọn hoặc không để cho biết họ muốn tiếp tục

    Cảnh báo

    Cảnh báo người dùng dữ liệu không hợp lệ, nhưng cho phép họ tiến hành sau khi bỏ lỡ 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 phần này là để thêm bất kỳ kiểu kiểm soát dữ liệu nào. Các bước 3-7 là đặc biệt để 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, hãy bấm Kiểm soát dữ liệu.

  3. Trên tab Thiết đặt, trong hộp Cho phép, hãy chọn Danh sách.

  4. Trong hộp Nguồn, hãy 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. Hãy đả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. Để chỉ rõ 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. Hãy kiểm tra kiểm soát dữ liệu để đảm bảo nó 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 ô 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ó chứa xác thực mà bạn muốn xóa, rồi đi đến dữ liệu > xác thực dữ liệu và trong hộp thoại xác thực dữ liệu, nhấn nút xóa tất cả , sau đó bấm 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. Hãy làm theo các 1-2 bước ở trên.

  2. Từ danh sách Cho phép, hãy 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 các 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, trong 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. Hãy làm theo các 1-2 bước ở trên.

  2. Trong hộp Cho phép, hãy 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 các 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ế mà 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 số thập phân, ví dụ như . 2, sau đó 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 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. Hãy làm theo các 1-2 bước ở trên.

  2. Trong hộp Cho phép, hãy 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 từ ngày hôm nay, chọn giữa trong hộp Dữ liệu, hãy nhập =TODAY() vào hộp Ngày bắt đầu và 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. Hãy làm theo các 1-2 bước ở trên.

  2. Trong hộp Cho phép, hãy 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ụ: chẳng hạn như 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. Hãy làm theo các 1-2 bước ở trên.

  2. Trong ô Cho phép, hãy 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ố lượng 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 vào 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 trong 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. Hãy làm theo các 1-2 bước ở trên.

  2. Trong hộp Cho phép hãy chọn kiểu dữ liệu mà 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 một hoặc nhiều ô dưới ô Dữ liệu, hãy bấm vào ô bạn muốn dùng để xác định các giá trị được phép.

    Ví dụ: để chỉ cho phép các mục nhập cho 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 ho phép >Số nguyên, Dữ liệu, nhỏ hơn hoặc bằng và 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 thực hiện trong Excel 2016; Tuy nhiên, chức năng 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).

= VÀ (TRÁI (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. Nghĩa là phần A2)=1 sẽ thay đổi thành A3)=1, A4)=1 v.v.

Để biết thêm chi tiết

Đả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 @

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

Bạn luôn có thể yêu cầu một chuyên gia trong Cộng đồng Kỹ thuật Excel, tìm hỗ trợ trong Cộng đồng Giải đáp hay đề xuất tính năng hoặc cải tiến mới ở Excel User Voice.

Xem thêm

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

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

Loại bỏ danh sách thả xuống

Lưu ý:  Trang này được dịch tự động nên có thể chứa các lỗi về ngữ pháp hoặc nội dung không chính xác. Mục đích của chúng tôi là khiến nội dung này trở nên hữu ích với bạn. Cho chúng tôi biết thông tin này có hữu ích hay không? Dưới đây là bài viết bằng tiếng Anh để bạn tham khảo..​

Phát triển kỹ năng Office của bạn
Khám phá nội dung đào tạo
Sở hữu tính năng mới đầu tiên
Tham gia Người dùng nội bộ Office

Thông tin này có hữu ích không?

Cảm ơn phản hồi của bạn!

Cảm ơn bạn đã phản hồi! Để trợ giúp tốt hơn, có lẽ chúng tôi sẽ kết nối bạn với một trong những nhân viên hỗ trợ Office của chúng tôi.

×