Cách sử dụng Data Validation để tạo drop - down List và kiểm tra dữ liệu nhập trong Excel - How to use Data validation

Data validation không những giúp chúng ta quy định được kiểu dữ liệu nhập để tránh được những sai sót mà nó còn có thể sử dụng kết hợp với các hàm Indirect hay IF, . . . để đem lại hiệu quả trong nhiều bảng dữ liệu phức tạp.

Trong phần này, chúng ta cùng tìm hiểu về cách tạo drop - down List có giá trị phụ thuộc một List khác với Indirect trong Data validation qua một ví dụ cụ thể.

Lưu ý: để làm theo hướng dẫn các bạn có thể tải file dữ liệu mẫu TẠI ĐÂY

Cho bảng dữ liệu như sau:

Yêu cầu 1: Điền tên công ty chỉ được chọn 1 trong 5 công ty đã cho

Để ý là với các yêu cầu có liên quan tới việc ràng buộc dữ liệu này phụ thuộc vào dữ liệu kia (dạng danh sách phụ thuộc) hay quy định nhập theo kiểu dữ liệu nào đó, thì chúng ta nghĩ ngay tới Data validation.

- Chúng ta sẽ tạo List trong Data Validation

Bước 1: Quét chọn vùng địa chỉ của "Công ty SX" từ B11:B15 và đặt chuột vào vùng Name box đặt tên rồi nhấn Enter như hình minh họa

Bước 2: Quét chọn vùng địa chỉ C3:C8 chọn thẻ Data chọn Data Validation, thẻ Settings chọn và nhập như hình minh họa dưới đây

Đến đây chúng ta đã thực hiện xong việc tạo List, nhấn chuột vào ô C3 và chọn tên xe từ nút button

Yêu cầu 2: Sản phẩm chỉ chọn trong những sản phẩm tương ứng với công ty SX

Bước 1: Quét vùng dữ liệu cột tên xe Toyota D12:D15 đặt tên trong vùng Name box như hướng dẫn ở hình sau

Bước 2: Tiếp tục thực hiện quét chọn cột tên xe của HONDA, BMWG, AUDI, FORD và đặt tên tương tự trên

Bước 3: Sau khi đặt tên vùng cho các cột tên xe, chúng ta quét chọn vùng địa chỉ D3:D8 chọn thẻ Data thực hiện như hướng dẫn ở hình sau:

Đến đây đã thực hiện xong yêu cầu 2.

Ở bước này ta có sử dụng thêm hàm INDIRECT hàm này có công dụng trả về tham chiếu từ chuỗi dữ liệu (cụ thể trong ví dụ nó trả về vùng địa chỉ của chuỗi trong ô C3 tức là vùng địa chỉ ta đặt là toyota)

Yêu cầu 3: Nhập cột số lượng là số nguyên dương

Thực hiện như sau:

- Quét vùng dữ liệu E3:E8 chọn thẻ Data, Data Validation, Thẻ Settings, mục Allow chọn Whole Number, mục Data chọn Greater than, mục Minimum nhập 0 như hình sau:

Sau khi thực hiện các bước trên có thể nhấn OK và bắt đầu nhập số liệu, nếu bạn muốn thông báo khi nhập liệu sai hãy khoan nhấn OK và làm theo các thao tác sau:

và tiến hành nhập số liệu.

Yêu cầu 4: Tính đơn giá

- Chúng ta đã quen với hàm vlookup để dò tìm theo cột trong 1 bảng dữ liệu, với bài toán này chúng ta có 5 bảng tên xe, như vậy chúng ta cần kết hợp hàm Indirect, như đã biết indirect trả về dữ liệu vùng tham chiếu, nên chúng ta thực hiện như sau:

Bước 1: Đặt tên cho vùng tham chiếu, Quét chọn vùng Tên xe và Giá đặt tên vùng dữ liệu như hình minh họa dưới (đặt toyota1 vì chúng ta đã đặt toyota cho cột tên xe ở yêu cầu trên)

Bước 2: Đặt tên vùng tương tự cho bảng giá xe HONDA, BMWG, AUDI, FORD là (honda1, bmwg1,audi1,ford1)

Bước 3: Nhập công thức như hình minh họa sau:

- Giải thích, hàm indirect là đối số thứ 2 trong hàm vlookup, hàm trả về bảng tham chiếu

Yêu cầu 5: Tính Thành tiền

- Thành tiền = Số lượng * Đơn giá

Như vậy, yêu cầu đã được hoàn thành.

Để quản lý các tên vùng dữ liệu (xóa, thêm, sửa. . .) các bạn có thể nhấn Ctr + F3 (hoặc vào thẻ Fomulas chọn Name Manager) hộp thoại quản lý tên vùng hiển thị như hình sau:

Có 1 lưu ý nhỏ các bạn cần nhớ, việc đặt tên vùng tránh trùng với 1 trong các ô địa chỉ có trong excel, trong bài này hãng bmw tôi để thêm chữ g là: bmwg để khi đặt tên vùng là bmwg và bmwg1 không bị trùng với cột bmw1 của excel.

Các bạn có thể xem  hướng dẫn chi tiết qua video - how to use Data validation in Excel