Nguyên nhân k sổ được danh sách trong datavalidation

Trong một hệ quản trị cơ sở dữ liệu, dữ liệu từ người dùng nhập vào cần phải được kiểm soát để đảm bảo tính thống nhất của dữ liệu. Ví dụ khi nhập giới tính thì nên giới hạn hai giá trị là Nam hoặc Nữ. Nếu bạn không kiểm soát thì người dùng sẽ nhập một giá trị “bậy bạ”, gây ra tình trạng dữ liệu không đồng nhất. Chính vì vậy Data validation là một công cụ giúp giải quyết vấn đề 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 lợi giúp mọi người khi nhập liệu vào hệ thống dữ liệu luôn đảm bảo cùng 1 kiểu nhập dữ 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.

Excel sẽ hiển thị hộp thoại xác thực dữ liệu có 3 cài đặt tab: Cài đặt (Settings), nhập thông báo (Input Message) và cảnh báo 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.

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 dữ liệu nào đều chấp nhận. Việc chọn tùy chọn này sẽ xóa mọi xác thực dữ liệu hiện có. Nếu trước đó ô (cell) đã được đặt Input Message, thông báo 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 ích khi dữ liệu nhập là tuổi, số lượng mặt hàng, số sản phẩm, điểm thi,… 

• • Ở mục này bạn có thể nhập các số dương, số âm và cả số 0.
  • Bạn cũng có thể không cho phép bỏ trống ô bằng cách chọn ô Ignore Blank.

Khi chọn Whole number, chức năng Data xuất hiện cho phép khống chế phạm vi giá trị nhập.

• • Between: giá trị nằm ở giữa khoảng nhỏ nhất (Minimum) và lớn nhất (Maximum).
  • Not between: giá trị nằm ở ngoài khoả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. 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 thực hiện tương tự đối với Whole number. Sử dụng kiểu giá trị Decimal cho phép tránh được những sai sót do quy định về số thập phân Quốc tế và Việt Nam (dấu chấm và phẩy). 

• • Ở đây bạ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 có thể không cho phép bỏ trống ô bằng cách chọn ô Ignore Blank.

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

• • Between: giá trị nằm ở giữa khoảng nhỏ nhất (Minimum) và lớn nhất (Maximum).
  • Not between: giá trị nằm ở ngoài khoả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ỉ cho phép chọn từ một danh sách các mục có sẵn. Bạn sẽ tạo danh sách thả xuống với định dạng dữ 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 có thể yêu cầu nhập ngày giữa khoảng thời gian 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 có thể chọn các dữ liệu ngày tháng từ bảng tính hoặc có thể tự điền vào.
  • Bạn cũng có thể không cho phép bỏ trống ô bằng cách chọn ô Ignore Blank.

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

• • Between: giá trị nằm ở giữa khoảng nhỏ nhất (Minimum) và lớn nhất (Maximum).
  • Not between: giá trị nằm ở ngoài khoả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 gian. Ví dụ: bạn có thể chỉ định rằng dữ liệu đã nhập phải muộn hơn 12:00 p.m.

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

• • Between: giá trị nằm ở giữa khoảng nhỏ nhất (Minimum) và lớn nhất (Maximum).
  • Not between: giá trị nằm ở ngoài khoả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 có thể giới hạn cho độ dài chuỗi ký tự sẽ được nhập vào ô.
  • Bạn cũng có thể không cho phép bỏ trống ô bằng cách chọn ô Ignore Blank.

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

• • Between: giá trị nằm ở giữa khoảng nhỏ nhất (Minimum) và lớn nhất (Maximum).
  • Not between: giá trị nằm ở ngoài khoả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 dữ liệu. Bạn có thể viết công thức của riêng mình để quy định dữ liệu đầu vào.

• • Ở định dạng Custom thì bạn có thể nhập vào công thức cho ô đã chọn.
  • Bạn cũng có thể không cho phép bỏ trống ô bằng cách chọn ô Ignore Blank.

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

• • Between: giá trị nằm ở giữa khoảng nhỏ nhất (Minimum) và lớn nhất (Maximum).
  • Not between: giá trị nằm ở ngoài khoả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 dữ liệu qui định sẽ nhìn thấy thông báo 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 Stop, Warning và Information thao tác khác nhau được mô tả ở bảng dưới đây:

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

1. Apply these changes to other cells with the same settings – thao tác này sẽ cập nhật định dạng dữ liệu cho các ô (cell) khác có cùng cài đặt 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 1: Nhấp chuột vào Data -> chọn Data Validation trên thanh công cụ

• Bước 2: Trong hộp thoại Data Validation, ở mục Settings chọn định dạng List (danh sách)

• Bước 3: Sau khi chọn định dạng List, mục Source xuất hiện, bạn nhập dữ 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 danh sách trong cột như sau:

Sau đây là các bước để tạo danh sách thả xuống từ cột dữ liệu trên

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

Vậy là bạn đã nhập 1 danh sách 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ề danh sách giá trị đều có thể được sử dụng để tạo danh sách thả xuống trong Excel.

Bạn có 1 danh sách trong cột như sau:

Đây là công thức của hàm OFFSET để tạo danh sách 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 bắt đầu của danh sách). 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 danh sách.

=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 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 danh sách (trang danh sách)

• 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 danh sách dưới dạng Bảng Excel. Điều này làm cho danh sách của bạn trở thành danh sách động – kích thước danh sách sẽ tự động điều chỉnh nếu bạn thêm hoặc xóa các mục, do đó danh sách thả xuống của bạn sẽ hiển thị toàn bộ danh sách.

Cách thực hiện: 

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

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

1. 1. 1. 1. Chọn vùng dữ 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ỉ chấp nhận 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. 1. 1. 1. Trên trang nhập liệu, nhập các tiêu đề trong B2: C2 – Product Type và Item
         2. Chọn ô B2 và trên thẻ Home, bấm Format as Table
         3. Nhấp vào một trong các Table Styles
         4. Tích vào My table has headers trong 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. 1. 1. 1. Trên trang nhập liệu, chọn ô B3
         2. Trên thanh công cụ chọn thẻ Data => Chọn Data Validation.
         3. Trong hộp thoại Data Validation đặt định dạng List
         4. Trong hộp Source, nhập “=tên mục: =produce
         5. Nhấn Ok để 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ọn Data Validation.
        3. Trong hộp thoại Data Validation đặt định dạng List
        4. Trong hộp Source, 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 báo như sau:

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

Sau khi đã tạo xong danh sách phụ thuộc theo hướng dẫn như trên, bạn có thể kiểm tra xem danh sách của mình có đúng hay không bằng các bước sau:

1. 1. 1. Nhấp chuột vào ô B3, Excel sẽ hiển thị danh sách có 2 mục Fruit hoặc Vegetable. Bạ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ục Item, để xem danh sách tương ứng.
      3. Danh sách thả xuống sẽ hiển thị các giá trị liên quan tới các mục ở Produce, ví dụ: Mục Fruit sẽ cho ra danh sách các 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 có thể cần có các mục gồm hai từ trong danh sách thả xuống xác thực dữ liệu Excel đầu tiên. Ví dụ: các lựa chọn của bạn là “Red Fruit”, “Green Fruit” và “Yellow Fruit”

• • • Tạo range và danh sách thả xuống như được mô tả ở trên.
    • Tạo danh sách 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 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 BIhoặc điền form để được hỗ trợ tư vấn