Data validation theo danh sách ở sheet khác indirect

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:

> Xem video hướng dẫn và tải bài tập MẪU ở dưới

Video hướng dẫn [Video Guide]

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.

Các bạn tải file dữ liệu để thực hành TẠI ĐÂY 

Chúc các bạn học tập tốt.

Có thể bạn muốn xem (more videos) :

> Cách sử dụng hàm IF cơ bản

> Cách sử dụng hàm IF lồng nhau, kết hợp AND, OR

> Cách sử dụng hàm dò tìm Vlookup kết hợp IF, LEFT, RIGHT

> Cách sử dụng hàm dò tìm Hlookup kết hợp IF, LEFT, RIGHT

> Cách sử dụng hàm COUNTIT, COUNTA, COUNT, MIN, MAX, AVERAGE, SUM

2014-12-27T02:13:41-05:00 2014-12-27T02:13:41-05:00 https://aneedz.com/ms-excel/data-validation-nang-cao-434.html https://sg.cdnki.com/data-validation-theo-danh-sach-o-sheet-khac-indirect---aHR0cHM6Ly9hbmVlZHouY29tL3VwbG9hZHMvbmV3cy8yMDEzXzA5L2RhdGEtdmFsaWRhdGlvbi5wbmc=.webp

Data Validation Nâng cao.
Bạn đã biết cách dùng data validation ở bài học trước. Chắc hẳn bạn rất hạnh phúc vì điều đó.
Tuy nhiên những gì bạn làm được ở bài học trước chỉ là ứng dụng căn bản thôi. Hôm nay tôi giới thiệu vài tuyệt chiêu của data validation mà bạn chưa ngờ tới.
Cách sử dụng validation truyền thống chỉ lấy được data ở trong cùng một sheet hay còn gọi là một bản tính. Vậy nếu bạn muốn lấy data ở một sheet khác vì không muốn tạo thêm thì làm thế nào? Để tôi chỉ cho bạn nhé. 4 cách để làm việc này.

⇒Cách thứ nhất: Đơn giản là copy ô (cell) chứa Data Validation rồi dán vào sheet mà bạn muốn.

Giả sử sheet1 là sheet chứa dữ liệu nguồn (Data Source), là một bản có cấu trúc như sau
      A        B                  
1    STT   Mã linh kiện
2    1       A001
3    2       A002
4    3       A003
5    4       A004
6    5       A005
7    6       A006

Ta thấy dữ liệu cần dùng làm Data Validation là có từ ô B2 cho đến B7

Và ô chứa data validation là ô C3 chẳng hạn,
Bây giời bạn nhấp chuột vào ô C3 và copy. Sau đó di chuyển sang sheet thứ 2 (Sheet2) và dán (Paste) vào ô D3 của sheet2. Lúc này ô D3 cũng được validate rồi. bạn nhấp chuột vào ô đó nhưng không thấy danh sách đâu cả. Bây giờ làm sao đây? Bạn đừng lo lắng, bài học này dành cho bạn mà.

Thật ra khi bạn làm data validation ở sheet1 thì data source ở dạng địa chỉ tương đối và nó được hiểu như là tham chiếu nội bảng tính (Sheet). khi copy sang shee thứ 2 (Sheet2) thì địa chỉ của data source sẽ chuyển về sheet2!$B$2:$B$7, mà ô B2:B7 thì chưa có dữ liệu nên ô validation D3 bị trắng. Để lấy source cho nó thì bạn làm như sau: nhấp vào ô D3 (Của sheet2 vừa dán bên trên) rồi vào data validation. sau đó nhập thêm địa chỉ data source ở sheet1: ='sheet1'!$B$2:$B$7 sau đó nhấp ok.

Thế là xong. Cách này đơn giản quá nhỉ.

⇒Cách thứ 2: là dùng hàm indirect.
Nói là hàm cho nó oách chứ thật ra cách dùng này giống như bên trên vì thêm chữa indirect sau dấu = và địa chỉ bên trên lồng vào giữa ("sheet1'!$B$2:$B$7").

Vậy công thức đầy đủ trong ô Source là: =indirect('sheet1'!$B$2:$B$7).

⇒Cách thứ 3: Đó là dùng thẻ Defined Name (Đặt tên cho mảng dữ liệu).

Cách này rất hay ở chổ là mình có thể sử dụng bất cứ đâu trong trong workbook mà chỉ cần 1 lần define name duy nhất.
cách đặt tên bạn tham khảo tại bài viết ở link bên trên nhé.

Giả sử tôi đặt tên cho source là: Malinhkien='sheet1'!$B$2:$B$7. và lúc này trong ô Source ta có công thức sau: =Malinhkien. Kết quả thật là mỹ mãn. phải không nào?

⇒Cách thứ 4 Đây là cách do tôi nghĩ ra. đó là dùng hàm tham chiếu vlookup để đưa dữ liệu từ file nguồn về trong sheet đó.

Ví dụ ở bảng tính trên, ta có danh sách file nguồn được đánh số từ 1 cho đến 6, như vậy ở sheet mà bạn muốn có valiation bạn cũng đánh số 1 cho đến 6 rồi dùng hàm vlookup để tham chiếu giá trị của 1~6 là A001~ A006, tiếp theo là dùng data validation bình thường.

Bạn có thể lợi dụng tính chất tham chiếu tương đối trong excel để kết hợp giữa cách 1 và cách 4 lại với nhau. bằng cách đánh số 1~ 6 có cùng địa chỉ ô nhưng chỉ khác tên Sheet, lúc này thì copy ô data validation ở sheet1 rồi dán vào sheet2 thì dữ liệu cũng up-date bình thường.

⇒Cách thứ 5: Kết hợp với cách thứ 4 hoặc cách thứ 3 và thêm một  Hàm OffSet

Ví dụ sau khi dùng hàm Vlookup để lấy các ký tự A, B, C, từ sheet 2 sang sheet 1. Ta sử dụng hàm Offset để đưa dữ liệu nguồn vào Data Source như hình bên dưới. lúc này ta thấy cột Level có cả các số 0, do tôi sử dụng Hàm IFerror để loại bỏ kết quả #N/A do hàm không tìm thấy giá trị tham chiếu. Nếu bạn không muốn số 0 xuất hiện trong list, ta dùng Hàm CountA để đếm các ô có chứa Text tại ô O13=Counta(O:O)=5. sau đó ta dùng kết quả của hàm này để giới hạn chiều cao (tham số Height) cho Hàm Offset.

Theo kết quả của Hàm CountA là 5 ô có chứa chữ số. như vậy Hàm offset chỉ lấy giá trị của những ô N14:N16 như hình bên dưới.

Công thức tổng quát cho hàm offset trong data source như sau:
=OFFSET($N$14,0,0,$O$13)

⇒Cách thứ 6: Cách này hơi thủ công một chút, tuy nhiên, bạn không phải lo lắng dữ liệu ngồn bị mất hay bị xóa.

Các bước được thực hiện giống như trên, tuy nhiên tại box: Source ta không tham chiếu đến vùng dữ liệu nào, hoặc tên trong Defined Name, mà ta gõ trực tiếp dữ liệu vào luôn, và dùng dấu phẩy ( dấu ,) hoặc dấu chấm phẩy (;) để ngăn cách các dòng, tùy theo định dạng Separator và decimal trong excel.

Ví dụ: Nếu muốn danh sách xổ xuống là: A, B, C, D, E, F v,v thì trong ô Source ta cũng điền là A;B;C;D;E;F

Chúc các bạn thành công nhé

Chú ý: Việc đăng lại bài viết trên ở website hoặc các phương tiện truyền thông khác mà không ghi rõ nguồn http://aneedz.com là vi phạm bản quyền