Excel Nâng Cao - Kiểm soát dữ liệu nhập với Data Validation trong Excel

Trong bài viết này, tôi giới thiệu tới các bạn cách sử dụng Data Validation để tạo List và Kiểm tra dữ liệu nhập (quy định dữ liệu nhập) trong bảng tính Excel. Để các bạn có thể nắm bắt được cách sử dụng chúng ta sẽ áp dụng ngay vào ví dụ cụ thể.

Ví dụ chúng ta cần lập bảng thống kê như hì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

- Chúng ta sẽ tạo List với chức năng 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ừ 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ình minh họa 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ình minh họa

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

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ấn OK

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