Data Validation Nâng cao

Thứ ba - 10/09/2013 11:56

Data Validation

Data Validation
Hôm nay tôi giới thiệu vài tuyệt chiêu của data validation mà dữ liệu nguồn được lấy ở một sheet khác
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.

 
Hàm Offset trong Data Validation

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

Data Validation

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

Tác giả bài viết: Minh Phú

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

Tổng số điểm của bài viết là: 40 trong 8 đánh giá

Xếp hạng: 5 - 8 phiếu bầu
Click để đánh giá bài viết

  Ý kiến bạn đọc

Mã bảo mật   
Thống kê
  • Đang truy cập17
  • Máy chủ tìm kiếm1
  • Khách viếng thăm16
  • Hôm nay7,632
  • Tháng hiện tại43,621
  • Tổng lượt truy cập1,564,473
Bạn đã không sử dụng Site, Bấm vào đây để duy trì trạng thái đăng nhập. Thời gian chờ: 60 giây