Á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 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. Xem qua video này để nắm được tổng quan ngắn gọn về tính năng xác thực dữ liệu.

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

Bạn có thể tải xuống sổ làm việc ví dụ với tất cả các ví dụ về tính năng xác thực dữ liệu, bạn sẽ tìm thấy trong bài viết này. Bạn có thể theo dõi hoặc tạo các tình huống xác thực dữ liệu của riêng mình.

Tải xuống các ví dụ về tính năng xác thực dữ liệu Excel

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

Ghi chú: Ba 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 4-8 dành riêng cho việc tạo một 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 vào Xác thực dữ liệu.

    Xác thực Dữ liệu nằm ở tab Dữ liệu, nhóm Công cụ Dữ liệu
  3. Trên tab Thiết đặt, trong hộp Cho phép, hãy chọn Danh sách.

    Tab thiết đặt trong hộp thoại Xác thực Dữ liệu
  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ụ:

    1. Để giới hạn câu trả lời thành hai lựa chọn (chẳng hạn: "Bạn có con không?"), hãy nhập Có,Không.

    2. Để giới hạn danh tiếng về chất lượng của nhà cung cấp ở ba mức xếp hạng, hãy nhập Thấp,Trung bình,Cao.

      Ghi chú: Thông thường, các bước sau đây chỉ được đề xuất cho các mục danh sách không thể thay đổi. Nếu bạn có một danh sách có thể thay đổi hoặc nếu bạn cần thêm hoặc loại bỏ các mục theo thời gian thì bạn nên làm theo bước Thực hành Tốt nhất dưới đây.

      Thực hành Tốt nhất: Bạn cũng có thể tạo các mục nhập danh sách bằng cách tham chiếu đến một dải ô ở đâu đó trong sổ làm việc. Cách hiệu quả nhất là tạo danh sách của bạn, sau đó định dạng dưới dạng Bảng Excel (từ tab Trang đầu, chọn Kiểu > Định dạng dưới dạng Bảng > chọn Kiểu Bảng phù hợp nhất với bạn). Tiếp theo, hãy chọn Phạm vi Nội dung Dữ liệu, là phần trong bảng chỉ có danh sách của bạn, không phải Tiêu đề bảng (trong trường hợp này là Phòng ban) và đặt tên có ý nghĩa trong Hộp Tên bên trên cột A.

      Nhập tên có ý nghĩa cho danh sách trong hộp tên

    Bây giờ, thay vì nhập các giá trị danh sách của bạn vào hộp Nguồn xác thực dữ liệu, bạn thêm tên mà bạn vừa xác định, theo sau dấu Bằng (=).

    Đứng trước tên bảng với dấu =

    Điều tốt nhất về cách sử dụng bảng là khi bạn thêm hoặc loại bỏ các mục khỏi danh sách của mình, danh sách xác thực dữ liệu của bạn sẽ tự động cập nhật.

    Ghi chú: Tốt nhất bạn nên đưa danh sách của mình vào một trang tính riêng biệt (ẩn nếu cần) sao cho không ai có thể chỉnh sửa chúng.

  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 ô đó.

    Danh sách thả xuống trong ô xuất hiệ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.

    Ghi chú: Nếu giá trị được phép của bạn dựa trên một phạm vi ô có tên đã xác định và không có một ô trống ở bất kỳ đâu trong dải ô đó, việc chọn hộp kiểm Bỏ qua giá trị trống sẽ cho phép giá trị bất kỳ được nhập vào ô được xác thực. Điều này cũng đúng đối với bất kỳ ô nào được tham chiếu bởi các công thức xác thực: nếu ô được tham chiếu bất kỳ là ô trống, việc chọn hộp kiểm Bỏ qua giá trị trống sẽ cho phép giá trị bất kỳ cần được nhập vào ô được xác thực.

  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.

Ghi chú: 

  • 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 không.

  • Nếu danh sách mục nhập của danh sách thả xuống nằm trên trang tính khác và bạn muốn ngăn không cho người dùng nhìn thấy hoặc thay đổi nó, hãy cân nhắc việc ẩn và bảo vệ trang tính đó. Để biết thêm thông tin về cách bảo vệ trang tính, hãy xem Khóa các ô để bảo vệ chú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, rồi đi tới 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 vào nút Xóa Tất cả, rồi nhấ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-3 trong mục Thêm xác thực dữ liệu vào một ô hoặc dải ô ở 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.

    Hộp thoại tiêu chí xác thực

    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. Làm theo các bước 1-3 trong mục Thêm xác thực dữ liệu vào một ô hoặc dải ô ở 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.

    Ghi chú: Để cho phép người dùng nhập tỷ lệ phần trăm, ví dụ như 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ạng thập phân, ví dụ như 0,2, rồi hiển thị ô kiểm soát dữ liệu ở 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 chủ.

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-3 trong mục Thêm xác thực dữ liệu vào một ô hoặc dải ô ở 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.

    Thiết đặt tiêu chí xác thực để giới hạn mục nhập ngày ở một khung thời gian cụ thể

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-3 trong mục Thêm xác thực dữ liệu vào một ô hoặc dải ô ở 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.

    Thiết đặt xác thực để giới hạn mục nhập thời gian trong một khung thời gian

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-3 trong mục Thêm xác thực dữ liệu vào một ô hoặc dải ô ở 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 ở 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.

    Ví dụ Xác thực dữ liệu với độ dài văn bản giới hạn

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-3 trong mục Thêm xác thực dữ liệu vào một ô hoặc một dải ô ở trên. Trong hộp Cho phép hãy chọn kiểu dữ liệu mà bạn muốn.

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

  3. 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.

    Thiết đặt xác thực để tính toán dựa trên nội dung một ô khác

Ghi chú: 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.

Để đả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

Ghi chú: 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 @.

=ISUMBER(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 @

  • Tại sao lệnh Xác thực Dữ liệu không được bật trên dải băng? Lệnh có thể không sẵn dùng vì:

    • Bảng Microsoft Excel có thể được liên kết đến site SharePoint Bạn không thể thêm xác thực dữ liệu tới bảng Excel được liên kết đến site SharePoint. Để thêm xác thực dữ liệu, bạn phải hủy liên kết bảng Excel hoặc chuyển bảng Excel thành một dải ô.

    • Có thể hiện tại bạn đang nhập dữ liệu Lệnh Xác thực Dữ liệu không được bật trên tab Dữ liệu trong khi bạn đang nhập dữ liệu vào một ô. Để kết thúc nhập dữ liệu, nhấn ENTER hoặc ESC.

    • Trang tính có thể được bảo vệ hoặc chia sẻ Bạn không thể thay đổi thiết đặt xác thực dữ liệu nếu sổ làm việc của bạn được chia sẻ hoặc bảo vệ. Để tìm hiểu cách để ngừng chia sẻ hoặc bảo vệ sổ làm việc, hãy xem mục Bảo vệ sổ làm việc.

  • Tôi có thể thay đổi cỡ phông không? Không, cỡ phông được cố định. Cách duy nhất để thay đổi kích cỡ hiển thị là điều chỉnh thu phóng màn hình của bạn ở góc dưới bên phải của cửa sổ Excel. Tuy nhiên, bạn có thể sử dụng một Hộp Tổ hợp ActiveX. Hãy xem Thêm hộp danh sách hoặc hộp tổ hợp vào một trang tính.

  • Có bất kỳ cách nào để xác thực dữ liệu tự động điền hoặc tự động chọn khi tôi nhập không? Không, nhưng nếu bạn sử dụng Hộp Tổ hợp ActiveX bạn sẽ thực hiện được chức năng đó.

  • Tôi có thể thực hiện đa lựa chọn trong danh sách xác thực dữ liệu không? Không, trừ khi bạn sử dụng Tổ hợp ActiveX hoặc Hộp Danh sách.

  • Tôi có thể chọn một mục trong danh sách xác thực dữ liệu và chuyển sang danh sách khác không? Có! Điều này được gọi là Xác thực Dữ liệu Phụ thuộc. Để biết thêm thông tin, hãy xem Tạo Danh sách Thả Xuống Phụ thuộc.

  • Làm thế nào tôi có thể loại bỏ tất cả xác thực dữ liệu trên một trang tính? Bạn có thể sử dụng hộp thoại Đi tới > Đặc biệt. Trên tab Trang đầu > Chỉnh sửa > Tìm & Chọn (hoặc nhấn F5 hoặc Ctrl+G trên bàn phím), rồi Đặc biệt > Xác thực dữ liệu và chọn Tất cả (để tìm tất cả các ô có xác thực dữ liệu) hoặc Giống nhau (để tìm các ô khớp với các thiết đặt xác thực dữ liệu cụ thể).

    Đi Tới hộp thoại Đặc biệt

    Bên cạnh hộp thoại gọi xác thực dữ liệu (tab Dữ liệu > Xác thực Dữ liệu), hãy nhấn nút Xóa Tất cả, rồi nhấn OK.

  • Tôi có thể bắt buộc ai đó tạo một mục nhập trong một (các) ô có xác thực dữ liệu không? Không, nhưng bạn có thể sử dụng VBA (Visual Basic for Applications) để kiểm tra xem ai đó có tạo một mục nhập trong các trường hợp nhất định, chẳng hạn như trước khi họ Lưu hoặc Đóng Sổ làm việc không. Nếu họ chưa lựa chọn, bạn có thể hủy sự kiện và không cho phép họ tiếp tục cho đến khi lựa chọn được thực hiện.

  • Làm thế nào tôi có thể tô màu các ô dựa trên vùng chọn danh sách xác thực dữ liệu? Bạn có thể sử dụng Định dạng Có điều kiện. Trong trường hợp này bạn muốn sử dụng tùy chọn Chỉ định dạng các ô chứa.

    Chỉ định dạng các ô có tùy chọn
  • Làm thế nào để tôi có thể xác thực địa chỉ email? Bạn có thể sử dụng phương pháp Tùy chỉnh > Công thức và kiểm tra xem ký hiệu @ có tồn tại trong mục nhập không. Trong trường hợp này, công thức được sử dụng là =ISNUMBER(FIND(“@”,D2)). Hàm FIND tìm kiếm các ký hiệu @ và nếu tìm thấy hàm sẽ trả về vị trí số của mình trong chuỗi văn bản và cho phép các mục nhập. Nếu không tìm thấy thì hàm FIND sẽ trả về lỗi và ngăn mục nhập.

Bạn có câu hỏi về một hàm cụ thể?

Đăng câu hỏi vào diễn đàn cộng đồng Excel

Giúp chúng tôi cải thiện Excel

Bạn có gợi ý về cách chúng tôi có thể cải thiện phiên bản Excel tiếp theo không? Nếu có, hãy kiểm tra các chủ đề tại Tiếng nói Người dùng Excel.

Xem thêm

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

Video: Tạo và quản lý danh sách thả xuống

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

Phát triển các kỹ năng 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.

×