Tổng hợp cách sử dụng Data Validation cơ bản và nâng cao

Posted on by adminChuyên mục:Kiến thức Internet
Bước 3 : Định dạng những list thành bảng và đặt tên những vùng tài liệuData validation có điều kiện kèm theo ( Tạo list thả xuống phụ thuộc vào có giá trị phụ thuộc vào vào 1 list khác )Trong một hệ quản trị cơ sở tài liệu, tài liệu từ người dùng nhập vào cần phải được trấn áp để bảo vệ tính thống nhất của tài liệu. Ví dụ khi nhập giới tính thì nên số lượng giới hạn hai giá trị là Nam hoặc Nữ. Nếu bạn không trấn áp thì người dùng sẽ nhập một giá trị “ bậy bạ ”, gây ra thực trạng tài liệu không như nhau. Chính vì thế Data validation là một công cụ giúp xử lý yếu tố này .

Bài viết này Cole sẽ chia sẻ cho các bạn hiểu Data validation là gì? Cách Sử dụng Data validation? Cài đặt Data validation trong excel như thế nào? Tìm hiểu Data validation nâng cao, đặc biệt là Data validation có điều kiện  

I. Data validation là gì?

Data Validation là 1 công cụ được sử dụng rất nhiều trong Excel, chức năng của Data validation là giúp bạn kiểm soát được định dạng dữ liệu nhập vào, giúp bạn cố định những dữ liệu ở 1 ô và người sử dụng chỉ có thể nhập được các giá trị đã cố định sẵn ở ô đó mà không thể nhập các dữ liệu hay giá trị khác vào.

Đây là 1 công cụ tiện nghi giúp mọi người khi nhập liệu vào mạng lưới hệ thống tài liệu luôn bảo vệ cùng 1 kiểu nhập tài liệu chuẩn xác, thống nhất giá trị .

II. Cách dùng Data validation 

Tại phần này, Cole.vn sẽ hướng dẫn bạn cách sử dụng data validation trong excel cơ bản , và có thể sử dụng data validation trong excel 2010 đến data validation trong excel 2013,…

  • Bước 1: Bạn Chọn ô (cell) hoặc range trên bảng của excel
  • Bước 2: Chọn Data » Data Tools » Data Validation.

Hướng dẫn sử dụng công cụ excel validation

Excel sẽ hiển thị hộp thoại xác nhận tài liệu có 3 setup tab : Cài đặt ( Settings ), nhập thông tin ( Input Message ) và cảnh báo nhắc nhở lỗi ( Error Alert ) .

  • Bước 3: Cài đặt data validation 

Tab Settings (Cài đặt) là nơi bạn nhập các tiêu chí định dạng dữ liệu. Có một số tiêu chí định dạng tích hợp sẵn với các tùy chọn khác nhau: Any value, Whole Number, Decimal, list, Date, Time, Text length, Custom.

cài đặt data validation

Data validation options

Có 8 định dạng dữ liệu có sẵn trong Data Validation mà người dùng có thể lựa chọn để định dạng dữ liệu nhập vào

  1. Any Value ( Mọi giá trị ) – Bất cứ dạng tài liệu nào đều đồng ý. Việc chọn tùy chọn này sẽ xóa mọi xác nhận tài liệu hiện có. Nếu trước đó ô ( cell ) đã được đặtInput Message, thông tin sẽ vẫn hiển thị khi ô ( cell ) đó được thao tác ngay cả khi chọn Any Value .
  1. Whole Number– Chỉ số nhập số nguyên. Nếu nhập số thập phân, chuỗi, … sẽ bị báo lỗi. Chức năng này hữu dụng khi tài liệu nhập là tuổi, số lượng loại sản phẩm, số mẫu sản phẩm, điểm thi, …
    • Ở mục này bạn hoàn toàn có thể nhập những số dương, số âm và cả số 0 .
    • Phân loại định dạng trong excelBạn cũng hoàn toàn có thể không được cho phép bỏ trống ô bằng cách chọn ô Ignore Blank .

Khi chọn Whole number, công dụng Data Open được cho phép khống chế khoanh vùng phạm vi giá trị nhập .

    • Between: giá trị nằm ở giữa khoảng chừng nhỏ nhất ( Minimum ) và lớn nhất ( Maximum ) .
    • Not between: giá trị nằm ở ngoài khoảng chừng nhỏ nhất ( Minimum ) và lớn nhất ( Maximum ) .
    • Equal to: bằng 1 số nào đó bạn nhập vào .
    • Not equal to: không bằng số mà bạn nhập vào .
    • Greater than: lớn hơn số mà bạn nhập vào .
    • Less than: nhỏ hơn số mà bạn nhập vào .
    • Greater than or equal to: lớn hơn hoặc bằng số mà bạn nhập vào .
    • Less than or equal to: nhỏ hơn hoặc bằng số mà bạn nhập vàoPhạm vi giá trị
  1. Decimal– Chức năng này chỉ cho phép nhập liệu là số nguyên hoặc số thập phân. Cách triển khai tương tự như so với Whole number. Sử dụng kiểu giá trị Decimal được cho phép tránh được những sai sót do lao lý về số thập phân Quốc tế và Nước Ta ( dấu chấm và phẩy ) .
    • Ở đây bạn hoàn toàn có thể viết những số có dấu phẩy như 1,2 ; 2,5 ; 3,5, hoặc số nguyên như 1, 2, 3 …
    • Bạn cũng hoàn toàn có thể không được cho phép bỏ trống ô bằng cách chọn ô Ignore Blank .

Ở mục này bạn cũng hoàn toàn có thể thiết lập những dạng như sau :

    • Between: giá trị nằm ở giữa khoảng chừng nhỏ nhất ( Minimum ) và lớn nhất ( Maximum ) .
    • Not between: giá trị nằm ở ngoài khoảng chừng nhỏ nhất ( Minimum ) và lớn nhất ( Maximum ) .
    • Equal to: bằng 1 số nào đó bạn nhập vào .
    • Not equal to: không bằng số mà bạn nhập vào .
    • Greater than: lớn hơn số mà bạn nhập vào .
    • Less than: nhỏ hơn số mà bạn nhập vào .
    • Greater than or equal to: lớn hơn hoặc bằng số mà bạn nhập vào .
    • Less than or equal to: nhỏ hơn hoặc bằng số mà bạn nhập vào
  1. List– Chức năng này chỉ được cho phép chọn từ một list những mục có sẵn. Bạn sẽ tạo list thả xuống với định dạng tài liệu này .

                   Bạn có thể tham khảo bài viết về cách tạo Excel data validation list ở phía dưới

  1. Date– Chỉ nhập định dạng ngày tháng. Ví dụ, bạn hoàn toàn có thể nhu yếu nhập ngày giữa khoảng chừng thời hạn 1/1/2018 và 31/12/2021, hoặc là nhập ngày sau 1/7/2018 .
    • Với định dạng Date thì bạn hoàn toàn có thể chọn những tài liệu ngày tháng từ bảng tính hoặc hoàn toàn có thể tự điền vào .
    • Bạn cũng hoàn toàn có thể không được cho phép bỏ trống ô bằng cách chọn ô Ignore Blank .

Ở mục này bạn cũng hoàn toàn có thể thiết lập những dạng như sau :

    • Between: giá trị nằm ở giữa khoảng chừng nhỏ nhất ( Minimum ) và lớn nhất ( Maximum ) .
    • Not between: giá trị nằm ở ngoài khoảng chừng nhỏ nhất ( Minimum ) và lớn nhất ( Maximum ) .
    • Equal to: bằng 1 số nào đó bạn nhập vào .
    • Not equal to: không bằng số mà bạn nhập vào .
    • Greater than: lớn hơn số mà bạn nhập vào .
    • Less than: nhỏ hơn số mà bạn nhập vào .
    • Greater than or equal to: lớn hơn hoặc bằng số mà bạn nhập vào .
    • Less than or equal to: nhỏ hơn hoặc bằng số mà bạn nhập vào
  1. Time– Chỉ nhập thời hạn. Ví dụ : bạn hoàn toàn có thể chỉ định rằng tài liệu đã nhập phải muộn hơn 12 : 00 p. m .

Ở mục này bạn cũng hoàn toàn có thể thiết lập những dạng như sau :

    • Between: giá trị nằm ở giữa khoảng chừng nhỏ nhất ( Minimum ) và lớn nhất ( Maximum ) .
    • Not between: giá trị nằm ở ngoài khoảng chừng nhỏ nhất ( Minimum ) và lớn nhất ( Maximum ) .
    • Equal to: bằng 1 số nào đó bạn nhập vào .
    • Not equal to: không bằng số mà bạn nhập vào .
    • Greater than: lớn hơn số mà bạn nhập vào .
    • Less than: nhỏ hơn số mà bạn nhập vào .
    • Greater than or equal to: lớn hơn hoặc bằng số mà bạn nhập vào .
    • Less than or equal to: nhỏ hơn hoặc bằng số mà bạn nhập vào

7. Text length (Độ dài dữ liệu) – Độ dài của dữ liệu (số lượng ký tự) bị giới hạn

    • Với định dạng Text Length thì bạn hoàn toàn có thể số lượng giới hạn cho độ dài chuỗi ký tự sẽ được nhập vào ô .
    • Bạn cũng hoàn toàn có thể không được cho phép bỏ trống ô bằng cách chọn ô Ignore Blank .

Ở mục này bạn cũng hoàn toàn có thể thiết lập những dạng như sau :

    • Between: giá trị nằm ở giữa khoảng chừng nhỏ nhất ( Minimum ) và lớn nhất ( Maximum ) .
    • Not between: giá trị nằm ở ngoài khoảng chừng nhỏ nhất ( Minimum ) và lớn nhất ( Maximum ) .
    • Equal to: bằng 1 số nào đó bạn nhập vào .
    • Not equal to: không bằng số mà bạn nhập vào .
    • Greater than: lớn hơn số mà bạn nhập vào .
    • Less than: nhỏ hơn số mà bạn nhập vào .
    • Greater than or equal to: lớn hơn hoặc bằng số mà bạn nhập vào .
    • Less than or equal to: nhỏ hơn hoặc bằng số mà bạn nhập vào
  1. Custom– Tùy chọn định dạng tài liệu. Bạn hoàn toàn có thể viết công thức của riêng mình để pháp luật tài liệu nguồn vào .
    • Ở định dạng Custom thì bạn hoàn toàn có thể nhập vào công thức cho ô đã chọn .
    • Bạn cũng hoàn toàn có thể không được cho phép bỏ trống ô bằng cách chọn ô Ignore Blank .

Ở mục này bạn cũng hoàn toàn có thể thiết lập những dạng như sau :

    • Between: giá trị nằm ở giữa khoảng chừng nhỏ nhất ( Minimum ) và lớn nhất ( Maximum ) .
    • Not between: giá trị nằm ở ngoài khoảng chừng nhỏ nhất ( Minimum ) và lớn nhất ( Maximum ) .
    • Equal to: bằng 1 số nào đó bạn nhập vào .
    • Not equal to: không bằng số mà bạn nhập vào .
    • Greater than: lớn hơn số mà bạn nhập vào .
    • Less than: nhỏ hơn số mà bạn nhập vào .
    • Greater than or equal to: lớn hơn hoặc bằng số mà bạn nhập vào .
    • Less than or equal to: nhỏ hơn hoặc bằng số mà bạn nhập vào

  • Bước 4: Nhập thông báo cho data validation 

Tab Input Message (Nhập thông báo) là nơi bạn nhập lời nhắn cho 1 ô (cell) hoặc range có đặt chế độ data validation khi người nhập liệu thao tác với ô đó, lời nhắn sẽ hiện ra nhằm nhắc nhở họ nhập đúng kiểu dữ liệu.

  • Bước 5: Cài đặt cảnh báo lỗi

Tab Error Alert (Cảnh báo lỗi) có chức năng kiểm soát dữ liệu được nhập vào có đúng định dạng đã quy định hay không. Có 3 loại cảnh báo lỗi: Stop (dừng), Warning (Cảnh báo) và Information (Thông báo). Mục Title (Tiêu đề): bạn nhập tiêu đề và mục Error message (Thông báo lỗi): bạn nhập nội dung thông báo lỗi.

Người nhập liệu khác khi nhập sai kiểu tài liệu lao lý sẽ nhìn thấy thông tin như thế này :

>> Chuyển đổi số, số hóa doanh nghiệp, các nhà quản lý phải làm gì để theo kịp?

3 loại cảnh báo nhắc nhở Stop, Warning và Information thao tác khác nhau được diễn đạt ở bảng dưới đây :

Loại cảnh báo Thao tác

Stop

 Ngăn người dùng nhập tài liệu không hợp trong ô ( cell ) pháp luật. Người dùng phải thử lại để nhập tài liệu hợp lệ .

Cửa sổ của Stop có 2 lựa chọn: Retry (thử lại) hoặc Cancel (hủy).

Warning

 Cảnh báo tài liệu mà người dùng nhập vào không hợp lệ nhưng không ngăn việc nhập tài liệu, người dùng vẫn hoàn toàn có thể liên tục thao tác nhập liệu .

Cửa sổ của Warning có 3 lựa chọn: Yes (nhận dữ liệu không hợp lệ), No (chỉnh sửa lại dữ liệu không hợp lệ) và Cancel (xóa dữ liệu không hợp lệ).

Information

 Thông báo tài liệu người dùng nhập vào không hợp lệ. Thông báo này cũng không ngăn việc nhập tài liệu, người dùng vấn hoàn toàn có thể liên tục thao tác nhập liệu .

Cửa sổ của Information có 2 lựa chọn: Ok (nhận dữ liệu không hợp lệ) và Cancel (xóa dữ liệu không hợp lệ).

  • Các cài đặt khác

  1. Apply these changes to other cells with the same settings – thao tác này sẽ update định dạng tài liệu cho những ô ( cell ) khác có cùng thiết lập với ô ( cell ) đang được chỉnh sửa .
  2. Cách bỏ định dạng Data Validation trong Excel

Chọn ô đã được định dạng Data Validation > Nhấn chọn thẻ Data > Ở mục Data Tools > Chọn Data Validation > Nhấn vào Clear All > Nhấn OK.

III. Cách tạo Excel Data validation list

  1. Tạo danh sách thả xuống thủ công

  • Bước 2 : Trong hộp thoại Data Validation, ở mụcSettingschọn định dạngList ( list )
  • Bước 3 : Sau khi chọn định dạngList, mục Source Open, bạn nhập tài liệu ví dụ như Yes và No như trong hình và cách nhau bằng dấu phẩy .

  1. Tạo danh sách thả xuống từ dữ liệu trong ô

Bạn có 1 list trong cột như sau :

Sau đây là những bước để tạo list thả xuống từ cột tài liệu trên

  • Bước 1 : Chọn 1 ô ( cell ) ở chỗ khác mà bạn muốn tạo list thả xuống
  • Bước 2 : Nhấp chuột vàoData ->chọn Data Validationtrên thanh công cụ->Trong hộp thoạiData Validationở mục Settings chọnList
  • Bước 3 : Ở mục source nhập= $ A $ 2 : USD A $ 6 ,hoặc nhấp chuột vào vùngSource sau đó đưa chuột sang cột tài liệu và kéo thả chuột vùng tài liệu mà bạn muốn tạo list .

Vậy là bạn đã nhập 1 list thả xuống ở ô C2

Lưu ý: Nếu bạn muốn tạo danh sách thả xuống trong nhiều ô cùng một lúc, hãy chọn tất cả các ô mà bạn muốn tạo rồi làm theo các bước trên. Đảm bảo rằng các tham chiếu ô là tuyệt đối (chẳng hạn như $A$2) và không tương đối (chẳng hạn như A2 hoặc A$2 hoặc $A2).

  1. Thao danh sách thả xuống bằng công thức

Ngoài việc chọn từ các ô và nhập dữ liệu theo cách thủ công, bạn cũng có thể sử dụng công thức và nhập trong hộp Source để tạo danh sách thả xuống Excel.

Bất kỳ công thức nào trả về list giá trị đều hoàn toàn có thể được sử dụng để tạo list thả xuống trong Excel .
Bạn có 1 list trong cột như sau :

Đây là công thức của hàm OFFSET để tạo list thả xuống :
= OFFSET ( reference, rows, cols, [ height ], [ width ] )
Công thức trên cần có năm đối số, trong đó tham chiếu là A2 ( điểm khởi đầu của list ). rows / cols được chỉ định bằng 0 vì không muốn bù trừ ô tham chiếu. Chiều cao được chỉ định là 5 vì có năm mục trong list .
= OFFSET ( $ A $ 2,0,0,5 )

Bây giờ, khi bạn sử dụng công thức này, nó sẽ trả về một mảng có danh sách năm loại trái cây trong A2:A6. Lưu ý rằng nếu bạn nhập công thức vào một ô, hãy chọn nó và nhấn F9, bạn sẽ thấy rằng nó trả về một miền tên trái cây.

IV. Data validation nâng cao

  1. Data validation có điều kiện (Tạo danh sách thả xuống phụ thuộc có giá trị phụ thuộc vào 1 list khác)

Data Validation có điều kiện (conditional data validation) có nghĩa là hạn chế các lựa chọn trong danh sách thả xuống của 1 ô tùy thuộc vào giá trị trong một ô khác (hoặc trong một danh sách thả xuống khác). Ở đây, chúng ta sẽ tìm hiểu cách tạo danh sách thả xuống có điều kiện trong Data validation.

  • Bước 1: Tạo worksheet

Bạn hoàn toàn có thể tạo 1 workbook gồm 2 worksheets : 1 worksheet nhập liệu ( trang nhập liệu ), 1 worksheet tạo list ( trang list )

  • Bước 2: Thiết lập các danh sách

Bạn nhập 3 danh sách gồm 3 mục Produce, Fruit và Vegetable vào trang danh sách. Trong đó, Produce là mục chính với 2 mục nhỏ là  Fruit và Vegetable

LƯU Ý:  Fruit và Vegetable được lấy làm tên mục trong Excel và chỉ chấp nhận 1 từ, mục có hai từ không được chấp nhận. Nếu bạn cần sử dụng các mục có chứa nhiều từ trong danh sách chính, hãy xem hướng dẫn ở cuối bài

  • Bước 3: Định dạng các danh sách thành bảng và đặt tên các vùng dữ liệu

Tiếp theo, bạn định dạng từng list dưới dạng Bảng Excel. Điều này làm cho list của bạn trở thành list động – kích cỡ list sẽ tự động hóa kiểm soát và điều chỉnh nếu bạn thêm hoặc xóa những mục, do đó list thả xuống của bạn sẽ hiển thị hàng loạt list .
Cách triển khai :

        1. Chọn vùng tài liệu
        2. Trong thẻHome
        3. ChọnFormat as table 
        4. Tích vào“My table has headers” trong hộp thoại => NhấnOK

Sau đó, bạn đặt tên cho những vùng tài liệu như sau :

        1. Chọn vùng tài liệu trừ ô heading
        2. Nhấp chuột vào ô tên bảng ở góc trên cùng bên trái dưới để đặt tên cho bảng ( chỉ đồng ý 1 từ )
        3. Nhấn phím Enter để hoàn tất

  • Bước 4: Tạo drop down list trong danh sách chính

+ Đầu tiên, bạn sẽ nhập các tiêu đề vào bảng nhập dữ liệu và thiết lập bảng

        1. Trên trang nhập liệu, nhập những tiêu đề trong B2 : C2 –Product Type vàItem
        2. Chọn ô B2 và trên thẻHome, bấmFormat as Table
        3. Nhấp vào một trong những Table Styles
        4. Tích vàoMy table has headerstrong hộp thoại => Bấm OK

+ Tạo menu đổ xuống trong danh sách chính

Tiếp theo bạn nhập danh mục chính trong cột Produce Type

        1. Trên trang nhập liệu, chọn ô B3
        2. Trên thanh công cụ chọn thẻData=> ChọnData Validation.
        3. Trong hộp thoạiData Validation đặt định dạngList
        4. Trong hộpSource, nhập “ = tên mục := produce
        5. NhấnOk để kết thúc

+ Tạo menu đổ xuống cho danh sách phụ thuộc

Tiếp theo, bạn sẽ tạo một danh sách chứa giá trị phụ thuộc được thêm vào cột Item. Nó sẽ cho ra dải dữ liệu của mục Fruit hoặc là mục Vegetable tùy vào giá trị bạn chọn ở cột Produce là gì.

Khác với mục Produce, ở mục Item bạn sẽ dùng hàm INDIRECT trong data validation để tạo  danh sách thả xuống 

          1. Trên trang nhập liệu, chọn ô C3
          2. Trên thanh công cụ chọn thẻData=> ChọnData Validation.
          3. Trong hộp thoạiData Validation đặt định dạngList
          4. Trong hộpSource, nhập công thức “

            = INDIRECT ( B3 )”   

            (Trong ví dụ này, ô C3 đang được thao tác, vì vậy công thức sẽ liên hệ đến ô đầu tiên trong cột Produce Type tức là ô B3)

          5. Bấm OK.

Lưu ý : Nếu ô B3 trông, bạn sẽ nhìn thấy thông tin như sau :

  • Bước 5: Kiểm tra danh sách thả xuống

Sau khi đã tạo xong list nhờ vào theo hướng dẫn như trên, bạn hoàn toàn có thể kiểm tra xem list của mình có đúng hay không bằng những bước sau :

      1. Nhấp chuột vào ô B3, Excel sẽ hiển thị list có 2 mục Fruit hoặc Vegetable. Bạn hoàn toàn có thể chọn 1 trong 2 mục trên .
      2. Nhấn chuột vào mũi tên ở ô C3 mụcItem, để xem list tương ứng .
      3. Danh sách thả xuống sẽ hiển thị những giá trị tương quan tới những mục ởProduce, ví dụ : Mục Fruit sẽ cho ra list những giá trị như Banana, Lemon, …

Tiếp theo đó bạn cũng có thể tạo thêm danh sách như vậy ở các ô phía dưới khi nhấn phím Tab

  1. Tạo danh sách thả xuống có giá trị phụ thuộc với đề mục gồm 2 chữ

Trong một workbook, bạn hoàn toàn có thể cần có những mục gồm hai từ trong list thả xuống xác nhận tài liệu Excel tiên phong. Ví dụ : những lựa chọn của bạn là “ Red Fruit ”, “ Green Fruit ” và “ Yellow Fruit ”

      • Tạo range và list thả xuống như được miêu tả ở trên .
      • Tạo list phụ, sử dụng tên gồm một từ, ví dụ : RedFruit, GreenFruit, YellowFruit
      • Đối với menu thả xuống thứ hai, hãy chọn Cho phép : Danh sách và sử dụng công thức xóa khoảng chừng trắng khỏi tên. Ví dụ :

 =INDIRECT(SUBSTITUTE(A2,” “,””))

Hy vọng bài viết về data validation của Cole đã giúp các bạn biết thêm về công cụ data validation cũng như cách sử dụng data validation nâng cao. Chúc các bạn áp dụng thành công trong công việc của mình.

>> 9 buổi học Power Bi từ cơ bản đến nâng cao với phương pháp học thực tiễn ứng dụng giúp các bạn dễ dàng theo học mà không cảm thấy phức tạp hay khó hiểu. Tìm hiểu thêm về lộ trình khóa học Power BI hoặc điền form để được hỗ trợ tư vấn

admin