Hiểu thêm về Validation

[handung107]

Kiểm tra việc nhập dữ liệu bằng Data Validation

Ở trên, chúng ta chỉ mới nói về chức năng tạo những DS của Validation, tuy vậy, Validation còn một chức năng rất quan trọng, mà các bạn ít để ý đến, đó là giúp chúng ta kiểm tra việc nhập liệu, thông báo cho chúng ta biết nếu chúng ta nhập dữ liệu không đúng.
Bạn nghĩ sao, nếu như bạn nhập một mã hàng không có tên trong DS các mặt hàng, và Validation sẽ thông báo cho bạn ?
Hoặc nếu khi bạn đặt một mã hàng mới cho một mặt hàng mới mà bị trùng lặp với cái đã có ?
Sau đây là một số thí dụ về sử dụng chức năng này của Validation : Trong thẻ Setting hộp Allow, bạn chọn Custom, hộp Data, bạn nhập công thức, xem như ta bắt đầu từ Cell A1
1/Không cho phép nhập có khoảng cách :
=LEN(A1)=LEN(SUBSTITUTE(A1," ","")) Hay : =ISERROR(FIND(" ",A1))
2/Không cho phép nhập có khoảng cách ở đầu hay cuối dữ liệu :
=LEN(A1)=LEN(TRIM(A1))
3/Không cho nhập toàn chữ hoa :
=NOT(EXACT(A1,UPPER(A1)))
4/Cho phép nhập vào Cell C1 dữ liệu tìm thấy trong DS A1:A5 :
=NOT(ISERROR(MATCH(C1,$A$1:$A$5,0)))
5/Cho phép lựa chọn 2 trường hợp :
=OR(A1=1,A1=2)
6/Cho phép nhập giá trị hoặc trong khoảng 1 đến 500, hoặc trong khoảng 700 đến 799 :
OR(AND(A1>=1,A1<=500),AND(A1>=700,A1<=799))
7/Cho phép nhập giá trị duy nhất trong một cột :
=COUNTIF($A$1:A1,A1)=1
8/Cho phép nhập giá trị duy nhất vào một dãy :
=COUNTIF($A$1:$A$10,"="&A1)=1 Hay =COUNTIF(tablename,A1)=1 (nếu dãy A1:A10 được đặt tên)
9/Cho phép nhập số liệu đến khi tổng của chúng =500
=SUM($A$1:$A$10)<=500
10/Không cho phép nhập vào ngày cuối tuần :
=AND(WEEKDAY(A1)<>1,WEEKDAY(A1)<>7)
11/Giới hạn nhập 25 ký tự :
=LEN(A1)<=25
Trên đây chỉ là một vài thí dụ nhỏ, còn nhiều ý tưởng phong phú hơn, xin nhường cho các bạn tự khám phá

 

[handung107]

Bây giờ, việc thực hiện kiểm tra dữ liệu đã xong, chúng ta có thể soạn những câu thông báo bằng cách nhờ thẻ Error Alert.
Bạn phải chọn dấu kiểm tại Show Error Alert after invalid data is entered
Có 3 kiểu tại hộp Style :
1/Stop : ngưng ngay khi dữ liệu nhập được kiểm tra là không hợp lệ
2/Warning : cảnh cáo, nhắc nhở
3/Information : chỉ là câu thông báo, nếu ta phớt lờ, dữ liệu vẫn có thể được nhập vào
Tại hộp Title bạn hãy đặt cho nó cái tựa .Td : Chú ý nhập trùng dữ liệu chẳng hạn
Tại Error Message, bạn hãy soạn thông điệp nhắc nhở, td : Dữ liệu bạn vừa nhập đã có rồi. Hãy chọn mã MH khác
Bây giờ, bạn có thể yên tâm khi nhập các dữ liệu, vì đã có những lời cảnh báo, nhắc nhở bạn nếu bạn vô ý nhập sai

 

[hieu1563]

Tôi rất tiếc không gởi file hay hình để minh hoạ cho các bạn dễ hiểu, nhưng tôi nghĩ loạt bài về Validation này rất hay, nên muốn chia xẻ với các bạn. Hôm nay, tôi giới thiệu với các bạn một kiểu Validation nữa.
Giả sử tại cột A, bạn có 1 danh sách từ A2:A21 gồm các mã KH, tuy vậy, bạn chỉ muốn trong danh sách DropDowns của Validation xuất hiện những Mã KH nào được bạn lựa chọn. Bạn hãy làm như sau, để tiện theo dõi, các bạn hãy mở Excel ra và cùng làm nhé :
1- Chọn Sheet 1 và đổi tên Sheet thành DM. Nhập Mã KH vào cột A, từ A2 : A21
2- Tại B2:B21, bạn đặt các CheckBox. Bạn vào View/Toolbars/Forms. Trên thanh công cụ Forms, bạn chọn Checkbox. Bạn lần lượt đặt các checkbox vào từng Cell B2 đến B21. Nhấp chuột phải vào các Checkbox và chọn Format Control/Control/Value, chọn Unchecked, tại Cell link, bạn nhập B2 cho checkbox đặt tại B2. Tương tự cho đến Cell B21.
3- Tại D2 bạn nhập công thức : =IF(B2=TRUE,ROW(),"")
4- Tại C2 bạn nhập công thức :[=IF(ISNUMBER(SMALL(IF($D$2:$D$21="","",ROW($D$2:$D$21)),ROW(1:1))),INDIRECT("A"&SMALL(IF($D$2:$D$21="","",ROW($D$2:$D$21)),ROW(1:1))),"")]. Đây là công thức mảng nên bạn phải kết thúc bằng Ctrl+Shift+Enter. Để giải thích công thức này bạn hãy xem lại các bài về công thức mảng với các hàm Indirect, Small và Row. Bạn kéo rê công thức từ C2, D2 đến C21, D21
5-Bạn vào Insert/Name/Define để đặt tên cho mảng MaKH. Tại hộp Refers to Bạn nhập công thức sau : =OFFSET(DM!$C$2,0,0,COUNTA(DM!$C$2:$C$21)-COUNTBLANK(DM!$C$2:$C$21),1)
6-Bây giờ bạn có thể đặt Validation vào bất kỳ một dãy nào trong cùng 1 Sheet hay ở những Sheet khác trong Workbook. Bạn chỉ cần chọn dãy đó và vào Data/Validation, trong phần Source bạn nhập =MaKH.
7- Bạn hãy thử lại kết quả của mình bằng cách chọn ở hộp Checkbox. Chỉ những mã KH nào được chọn (có kết quả là True), mới có tên trong danh sách trong Validation.
Hy vọng các bạn sẽ cảm thấy thích trước những tính năng của Validation

Cách này hơi phức tạp và khó hiểu. Em nghĩ chỉ cần dùng những hàm như match; index; max . . . là có thể làm được rồi.
Thân!

 

[hieu1563]

Những bài viết của Chị Handung107 thể hiện sự hiểu biết sâu sắc của Chị về Validation. Tuy nhiên để mọi người dễ theo dõi thì chị nên ở mỗi đầu bài chị nêu ra một bài toán (mục đích là để làm gì . . ), và lời giải chính là bài viết. Như thế những người đi sau như tụi Em mới hiểu hết (hy vọng là vậy) ý của chị và tiếp thu nhanh hơn.
Mấy ý kiến mạo muội.
Thân!

 

[hieu1563]

Bạn sửa lại đoạn mã trên như sau rivate Sub Worksheet_Change(ByVal Target As Range)
Worksheets("TenSheet").Range("Tenday").Sort Key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub
(Cột A là cột muốn sắp xếp, hàng 2 là hàng bắt đầu nhập liệu, hàng 1 là tiêu đề).
Đoạn mã này có 1 khuyết điểm là khi ta vừa nhập liệu vào cột A xong là dữ liệu bắt đầu sort liền, những Cell ở cột liên quan sẽ trống. Tôi đã muốn sửa lại sao cho nhập toàn bộ dữ liệu vào các cột xong, mới bắt đầu sự sắp xếp, nhưng chưa sửa được. Bạn nào có cao kiến gì, xin góp ý.

Em nghĩ nên dùng cái này :
Private Sub Worksheet_Deactivate()
Worksheets("TenSheet").Range("Tenday").Sort Key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Chỉ khi nào Sheet này không được chọn nữa thì hành động SORT mới xảy ra (Cho người nhập dễ thấy), Như vậy thì sẽ đơn giản hơn.
Thân!

 

[hieu1563]

WhoamI à, những yêu cầu của em là những mặt hạn chế của Validation, mà một mình nó không thể giải quyết được. Nó phải kết hợp với VBA, hay thêm một vài công cụ khác nữa, mới thể hiện hết cái ưu điểm và khắc phục nhược điểm của nó.
Đối với yêu cầu 2, chị gởi File Validation 3, trong đó minh hoạ phần nội dung ngày 17/12/2004 của bài này, em xem kỹ lại nhé.
Và chị giới thiệu thêm Validation kết hợp với ComboBox. Em hãy tìm hiểu ở những Source Code, và đặc biệt ở hộp Name, cũng như phần Data/ Validation.
Mấy hôm nay máy tính của chị bị nhiễm con Trojan, số File bị hư hỏng khá nhiều, nên không biết thế nào, nếu File không tốt, các em hãy cho biết để chị gởi lại

Chị có thể gửi cho Em File này được không ạ
Em cảm ơn chị nhiều
hieu1563@gmail.com

 

[LekhuongDuy]

Tôi có dùng Validation để chọn mã số phiếu thu - chi. Khi in phiếu T-C, thì tôi chọn từng một mã phiếu T-C để in rất mất thởi gian. Xin các Anh, chị hướng dẫn cách để in hàng loạt tất cả phiếu T-C trong danh sách validation chỉ bằng một lệnh in. Xin cám ơn!

 

[hieu hoc]

Cho em hoi la excel anh chi dang dung la 97 - 2003 phai khong a, em dung excel 2007 khong co cac phan giong anh chi huong dan, em bo tay luon

 

[Aokiji]

Ðề: Hiểu thêm về Validation

Cho em hỏi data validation dùng đễ làm gì? (công dụng của nó) chứ k cần giải thích hàm loằng ngoằng gì đâu ạ...tks

 

[SA_DQ]

Ðề: Hiểu thêm về Validation

Cho em hỏi data validation dùng đễ làm gì? (công dụng của nó) chứ k cần giải thích hàm loằng ngoằng gì đâu ạ...tks

Ngắn gọn nhất ư?

Dùng để chọn hay nhập dữ liệu từ 1 danh sách cho nhanh!