Giả sử bạn có 1 danh sách từ A1:A120, trong đó bạn nhập các tiểu khoản của các tài khoản.
Td : Ở A1 là 111.1 - Tiền mặt VN
_____ A2 là 111.2 - Tiền mặt ngoại tệ....
_____ A4 là 112.1 - Tiền gởi VN ...
_____ A6 là 112.3 - Vàng bạc, kim loại quý...
Bây giờ bạn hãy đặt tên cho từng mảng theo các tài khoản từ 111.1 đến 111.3 là TK111, 112.1 đến 112.3 là TK112...cho đến hết.
Tại cột B1 : B50 , bạn nhập danh sách tên các tài khoản vừa đặt TK111, TK112, TK113, TK121..., nhập xong bạn đặt tên cho dãy này là TK.
Tôi thí dụ bạn muốn đặt một danh sách các tài khoản cho các Cell tại cột D, từ D2 đến D200 và kèm theo cột E đó là danh sách các tiểu khoản phụ thuộc các tài khoản tại cột D, td nếu ở D2 là TK111 thì E2 sẽ có 1 DS từ 111.1 - Tiền mặt VN đến 111.3 - Vàng bạc, kim loại quý.
Bạn sẽ làm như sau :
-Tại D2 bạn vào Data/Validation/Allow, chọn List, Source của bạn là =TK.
-Tại E2 bạn cũng vào Data/Validation/Allow/List, Source của bạn là =INDIRECT(D2) .
-Từ D2 và E2 bạn rê chuột đến D200 và E200 để áp dụng thuộc tính Validation này cho vùng chọn.
Phần 2:
Nếu ở cột B2 : B50 bạn muốn nhập DS các tài khoản có khoảng cách ở giữa cho nó đẹp một chút bạn làm như sau:
-Đặt tên cho dãy TK 111, TK 112..., cũng là TK
-Tại D2, đặt Validation / Source =TK, bạn sẽ được 1 List gồm TK 111, TK 112,....
-Tại E2, để hiển thị được danh sách phụ thuộc ấy bạn phải sửa lại đôi chút,(vì khi đặt tên tại NameBox cho vùng từ 111.1 đến 111.3 bạn chỉ có thể đặt tên là TK111..., không có khoảng cách). Bạn sửa như sau : Validation/Source =INDIRECT(SUBSTITUTE(D2," ",""))
Phần 3:
-Tại NameBox, bạn cũng không thể đặt tên vùng là một số, td 111 cho vùng từ 111.1 đến 111.3, nhưng bạn lại muốn tại cột D chỉ hiện ra danh sách 111, 112,113... không có chữ TK kèm theo.
-Bạn cũng đặt tên cho các vùng giống như ở phần 1 và tại cột B bạn nhập danh sách hiển thị cho cột D là 111, 112, 113...Cũng đặt tên cho dãy này là TK.
-Tại cột C, nếu ở B2 là 111, thì C2 là tên tương ứng đặt trong NameBox td TK111, nếu B3 là 112 thì C3 là TK112...Bạn đặt tên cho mảng B2:C50 là DMTK.
Như vậy khi đặt Source cho Validation tại D2 =TK, thì nó sẽ cho bạn List gồm 111, 112,....
-Tại E2 bạn sẽ đặt Source là : = INDIRECT(VLOOKUP(D2,DMTK,2,0)), bạn sẽ được List như ở các phần trên.
Phần 4:
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.
Phần 5:
Phần 5.1:
Chúng ta lại qua một dạng khác nữa của Validation. Cũng tại Sheet DM như tôi đã giới thiệu ở trên. Tại cột A1 bạn nhập tên cột là Mã KH, B1 là Tên KH. Từ A2:A21 nhập các Mã KH và B2:B21 nhập tên các KH. Đặt tên cho dãy B2:B21 là TenKH.
Chúng ta sẽ đặt Data/Validation cho dãy B2:B12 (côt 2), tại 1 Sheet khác được đặt tên là DS. Phần Source của Validation bạn nhập vào =TenKH
Bạn hãy Click chuột phải lên Sheet Tab tại Sheet DS, bạn chọn View Code (hoặc nhấn Alt+F11)để vào cửa sổ Microsoft Visual Basic. Bạn Copy đoạn mã sau và Paste vào cưa sổ VBA vừa được mở. Những phần được sửa lại cho phù hợp với dữ liệu của các bạn sẽ là :
- Target.Column = 2 vì đó là cột B.
- Worksheet và Range thay đổi tùy theo tên các bạn đặt cho Sheet nguồn và dãy.
-Taget.Value tại Worksheet DM và cột A, bắt đầu là Cell A1
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 2 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
Target.Value = Worksheets("DM").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("DM").Range("TenKH"), 0), 0)
Application.EnableEvents = True
End If
End Sub
Phần 5.2:
Chúng ta sẽ dùng ý tưởng trong phần 5.1 (trong Validation là DS Tên KH, nhưng khi Click vào Tên thì Mã KH lại hiển thị ). Bây giờ tôi muốn trong DS Validation có cả 2 cột : MaKH và Tên KH. Tôi sẽ dùng Source Code của phần 5.1 là :
Tại cột B1 : B50 , bạn nhập danh sách tên các tài khoản vừa đặt TK111, TK112, TK113, TK121..., nhập xong bạn đặt tên cho dãy này là TK.
Tôi thí dụ bạn muốn đặt một danh sách các tài khoản cho các Cell tại cột D, từ D2 đến D200 và kèm theo cột E đó là danh sách các tiểu khoản phụ thuộc các tài khoản tại cột D, td nếu ở D2 là TK111 thì E2 sẽ có 1 DS từ 111.1 - Tiền mặt VN đến 111.3 - Vàng bạc, kim loại quý.
Bạn sẽ làm như sau :
-Tại D2 bạn vào Data/Validation/Allow, chọn List, Source của bạn là =TK.
-Tại E2 bạn cũng vào Data/Validation/Allow/List, Source của bạn là =INDIRECT(D2) .
-Từ D2 và E2 bạn rê chuột đến D200 và E200 để áp dụng thuộc tính Validation này cho vùng chọn.
Phần 2:
Nếu ở cột B2 : B50 bạn muốn nhập DS các tài khoản có khoảng cách ở giữa cho nó đẹp một chút bạn làm như sau:
-Đặt tên cho dãy TK 111, TK 112..., cũng là TK
-Tại D2, đặt Validation / Source =TK, bạn sẽ được 1 List gồm TK 111, TK 112,....
-Tại E2, để hiển thị được danh sách phụ thuộc ấy bạn phải sửa lại đôi chút,(vì khi đặt tên tại NameBox cho vùng từ 111.1 đến 111.3 bạn chỉ có thể đặt tên là TK111..., không có khoảng cách). Bạn sửa như sau : Validation/Source =INDIRECT(SUBSTITUTE(D2," ",""))
Phần 3:
-Tại NameBox, bạn cũng không thể đặt tên vùng là một số, td 111 cho vùng từ 111.1 đến 111.3, nhưng bạn lại muốn tại cột D chỉ hiện ra danh sách 111, 112,113... không có chữ TK kèm theo.
-Bạn cũng đặt tên cho các vùng giống như ở phần 1 và tại cột B bạn nhập danh sách hiển thị cho cột D là 111, 112, 113...Cũng đặt tên cho dãy này là TK.
-Tại cột C, nếu ở B2 là 111, thì C2 là tên tương ứng đặt trong NameBox td TK111, nếu B3 là 112 thì C3 là TK112...Bạn đặt tên cho mảng B2:C50 là DMTK.
Như vậy khi đặt Source cho Validation tại D2 =TK, thì nó sẽ cho bạn List gồm 111, 112,....
-Tại E2 bạn sẽ đặt Source là : = INDIRECT(VLOOKUP(D2,DMTK,2,0)), bạn sẽ được List như ở các phần trên.
Phần 4:
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.
Phần 5:
Phần 5.1:
Chúng ta lại qua một dạng khác nữa của Validation. Cũng tại Sheet DM như tôi đã giới thiệu ở trên. Tại cột A1 bạn nhập tên cột là Mã KH, B1 là Tên KH. Từ A2:A21 nhập các Mã KH và B2:B21 nhập tên các KH. Đặt tên cho dãy B2:B21 là TenKH.
Chúng ta sẽ đặt Data/Validation cho dãy B2:B12 (côt 2), tại 1 Sheet khác được đặt tên là DS. Phần Source của Validation bạn nhập vào =TenKH
Bạn hãy Click chuột phải lên Sheet Tab tại Sheet DS, bạn chọn View Code (hoặc nhấn Alt+F11)để vào cửa sổ Microsoft Visual Basic. Bạn Copy đoạn mã sau và Paste vào cưa sổ VBA vừa được mở. Những phần được sửa lại cho phù hợp với dữ liệu của các bạn sẽ là :
- Target.Column = 2 vì đó là cột B.
- Worksheet và Range thay đổi tùy theo tên các bạn đặt cho Sheet nguồn và dãy.
-Taget.Value tại Worksheet DM và cột A, bắt đầu là Cell A1
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 2 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
Target.Value = Worksheets("DM").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("DM").Range("TenKH"), 0), 0)
Application.EnableEvents = True
End If
End Sub
Phần 5.2:
Chúng ta sẽ dùng ý tưởng trong phần 5.1 (trong Validation là DS Tên KH, nhưng khi Click vào Tên thì Mã KH lại hiển thị ). Bây giờ tôi muốn trong DS Validation có cả 2 cột : MaKH và Tên KH. Tôi sẽ dùng Source Code của phần 5.1 là :
Trích:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 2 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
Target.Value = Worksheets("DM").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("DM").Range("TenKH"), 0), 0)
Application.EnableEvents = True
End If
End Sub
-Target.Column = 2 (cột B) là cột nguồn sẽ được thể hiện trong DS của Validation. Như vậy, nếu ta dùng cột B để ghép dữ liệu của 2, 3 cột tuỳ ý chúng ta sẽ có DS trong Validation thể hiện cả 2, 3 cột chúng ta ghép lại.
-Tại Cell B2 ta chỉ cần đặt công thức =A2&" - "&C2&" - "D2 (Cột A là Mã KH, cột C là Tên KH, cột D là mã số thuế, hoặc bạn có thể lấy bất cứ thông tin tại bất cứ cột nào muốn ghép ) và kéo rê công thức xuống các Cell bên dưới. Đặt tên cho dãy B2:Bx là DS và sửa lại trong Range("TenKH") bằng "DS".
-Nếu bạn không thích ghép dữ liệu tại cột B, bạn có thể chọn bất cứ cột nào khác, và sửa lại Target.Column = số thứ tự của cột.
-Bây giờ bạn đã có 1 DS trong Validation thể hiện cả MaKH, TenKH..nhưng thể hiện trên Active Cell sẽ là cột A, nếu bạn muốn thay đổi là cột khác thì sửa trong Range("A1") là Range("C1") hay D1...tuỳ thích.
-DS Validation này sẽ cần chiều rộng của cột khá lớn, nếu Cell chỉ thể hiện Mã KH sẽ làm cho bảng DL của chúng ta không đẹp. Hẹn các bạn lần sau, chúng ta sẽ có cách khắc phục vấn đề này.
Phần 5.3:
Các bạn dùng Validation để biết nhiều thông tin chi tiết về một mã thì theo I làm như sau:
-Thông thường trong "danh mục" cột 1 là Mã, cột 2 là Tên, các cột còn lại để thể hiện những đặc điểm của mã.
-Bạn tạo thêm 2 cột ở cuối danh mục: cột 1 Mã, cột 2 là Thông tin. Ở cột mã bạn dùng tham chiếu =A2 để nhận mã gốc ở cột đầu tiên, cột Thông tin dùng =C2 & " " & D2 ....bạn muốn thông tin ở cột nào thì & với cột đó. Sau đó bạn dùng 2 cột cuối cùng này cho Validation.
-Để có được 2 hay nhiều cột trong Validation. Đầu tiên bạn đặt tên một cột VD là "Vung" sau đó thực Validation, sau khi xong bạn chọn lại vùng thành dạng 2 cột (VD E:F) đặt tên cũng là "Vung".
-Nút chọn của Validation thuộc họ "Combo Box". Trong bất cứ một nút chọn nào giống như vậy ở bất kỳ một phần mềm nào bạn dùng ALT+ Arrow down hoặc F4 là mở để chọn.
Nếu không dùng VBA I cũng chỉ làm được như vậy, bạn nào có phương án tốt xin đưa ra mọi người học hỏi.
Phần 6:
Phần 6.1: Dynamic Range
Tôi không biết nên dịch từ Dynamic Range thế nào cho sát nghĩa, nên đành sử dụng từ Dynamic Range vậy.
TD: tại Sheet DSach các bạn đặt tên cho dãy A1:A10 là DSach, nhưng khi dữ liệu của bạn thay đổi, kéo dài thêm đến A11, A12..., mỗi lần như vậy, bạn lại phải vào Insert/Name/Define, nếu không những dữ liệu nào có liên quan đến DSach sẽ không được cập nhật.
Vì vậy chúng ta sẽ dùng Dynamic Range (tạm dịch là DS động) cho dữ liệu tại cột A. Bạn hãy vào Insert/Name/Define.
- Tại hộp Name in Workbook, bạn đặt DSach.
- Tại hộp Refers to bạn gõ vào công thức : =OFFSET(DSach!$A$1,0,0,COUNTA(DSach!A:A),1).
Nếu bạn muốn danh sách này tự động sắp xếp theo thứ tự tăng dần, bạn hãy đặt source code sau vào cửa sổ VBA của Sheet DSach:
Trích:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 2 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
Target.Value = Worksheets("DM").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("DM").Range("TenKH"), 0), 0)
Application.EnableEvents = True
End If
End Sub
-Target.Column = 2 (cột B) là cột nguồn sẽ được thể hiện trong DS của Validation. Như vậy, nếu ta dùng cột B để ghép dữ liệu của 2, 3 cột tuỳ ý chúng ta sẽ có DS trong Validation thể hiện cả 2, 3 cột chúng ta ghép lại.
-Tại Cell B2 ta chỉ cần đặt công thức =A2&" - "&C2&" - "D2 (Cột A là Mã KH, cột C là Tên KH, cột D là mã số thuế, hoặc bạn có thể lấy bất cứ thông tin tại bất cứ cột nào muốn ghép ) và kéo rê công thức xuống các Cell bên dưới. Đặt tên cho dãy B2:Bx là DS và sửa lại trong Range("TenKH") bằng "DS".
-Nếu bạn không thích ghép dữ liệu tại cột B, bạn có thể chọn bất cứ cột nào khác, và sửa lại Target.Column = số thứ tự của cột.
-Bây giờ bạn đã có 1 DS trong Validation thể hiện cả MaKH, TenKH..nhưng thể hiện trên Active Cell sẽ là cột A, nếu bạn muốn thay đổi là cột khác thì sửa trong Range("A1") là Range("C1") hay D1...tuỳ thích.
-DS Validation này sẽ cần chiều rộng của cột khá lớn, nếu Cell chỉ thể hiện Mã KH sẽ làm cho bảng DL của chúng ta không đẹp. Hẹn các bạn lần sau, chúng ta sẽ có cách khắc phục vấn đề này.
Phần 5.3:
Các bạn dùng Validation để biết nhiều thông tin chi tiết về một mã thì theo I làm như sau:
-Thông thường trong "danh mục" cột 1 là Mã, cột 2 là Tên, các cột còn lại để thể hiện những đặc điểm của mã.
-Bạn tạo thêm 2 cột ở cuối danh mục: cột 1 Mã, cột 2 là Thông tin. Ở cột mã bạn dùng tham chiếu =A2 để nhận mã gốc ở cột đầu tiên, cột Thông tin dùng =C2 & " " & D2 ....bạn muốn thông tin ở cột nào thì & với cột đó. Sau đó bạn dùng 2 cột cuối cùng này cho Validation.
-Để có được 2 hay nhiều cột trong Validation. Đầu tiên bạn đặt tên một cột VD là "Vung" sau đó thực Validation, sau khi xong bạn chọn lại vùng thành dạng 2 cột (VD E:F) đặt tên cũng là "Vung".
-Nút chọn của Validation thuộc họ "Combo Box". Trong bất cứ một nút chọn nào giống như vậy ở bất kỳ một phần mềm nào bạn dùng ALT+ Arrow down hoặc F4 là mở để chọn.
Nếu không dùng VBA I cũng chỉ làm được như vậy, bạn nào có phương án tốt xin đưa ra mọi người học hỏi.
Phần 6:
Phần 6.1: Dynamic Range
Tôi không biết nên dịch từ Dynamic Range thế nào cho sát nghĩa, nên đành sử dụng từ Dynamic Range vậy.
TD: tại Sheet DSach các bạn đặt tên cho dãy A1:A10 là DSach, nhưng khi dữ liệu của bạn thay đổi, kéo dài thêm đến A11, A12..., mỗi lần như vậy, bạn lại phải vào Insert/Name/Define, nếu không những dữ liệu nào có liên quan đến DSach sẽ không được cập nhật.
Vì vậy chúng ta sẽ dùng Dynamic Range (tạm dịch là DS động) cho dữ liệu tại cột A. Bạn hãy vào Insert/Name/Define.
- Tại hộp Name in Workbook, bạn đặt DSach.
- Tại hộp Refers to bạn gõ vào công thức : =OFFSET(DSach!$A$1,0,0,COUNTA(DSach!A:A),1).
Nếu bạn muốn danh sách này tự động sắp xếp theo thứ tự tăng dần, bạn hãy đặt source code sau vào cửa sổ VBA của Sheet DSach:
Trích:
Private Sub Worksheet_Change(ByVal Target As Range)
Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub
Bây giờ bạn có thể thoải mái đặt Validation tại 1 cell bất kỳ trong cùng Sheet hay bất kỳ Sheet nào trong Workbook với Source cho Validation này là DSach.
Phần 6.2: hàm VLOOKUP trong Database động
Các bạn đã được giới thiệu về Dynamic Range, nghĩa là DS động với 1 cột dữ liệu, nhưng với Database có 4, 5 cột dữ liệu và dùng hàm VLOOKUP thì sao ?
Tôi giả sử chúng ta có DS động tại cột A như bài trên, nhưng A1 là tiêu đề cột.
Td : MaKH, từ A2 trở đi, chúng ta mới nhập dữ liệu.
Chúng ta sửa lại 1 chút trong hộp Refers to của Insert/Name/Define là : =OFFSET(DSach!$A$1,1,0,COUNTA(DSach!$A:$A)-1,1).
Database của chúng ta sẽ gồm cột B Tên KH, cột C Địa chỉ KH, cột D là Mã số thuế.
Chúng ta sẽ đặt tên cho Vùng gồm 4 cột này là Database.
Tại hộp Refers to bạn nhập công thức sau :
=OFFSET(DSach!$A$1,1,0,COUNTA(DSach!$A:$A)-1,COUNTA(DSach!$1:$1)).
Bây giờ, bạn đặt Validation để chọn MãKH, và dùng hàm VLOOKUP để lấy thông tin TênKH, Địa chỉ KH, Mã số thuế mà không lo phải đặt lại tên cho CSDL khi dữ liệu nhiều hơn số hàng mà chúng ta đã dùng để đặt tên cho mảng.
Phần 7: Bảo vệ dữ liệu trong Excel bằng Validation
Có một cách bảo vệ dữ liệu để người khác không được đụng vào mà chúng ta tận dụng khả năng có sẵn của Data / Validation.
- Chọn vùng dữ liệu bạn muốn bảo vệ.
- Chọn menu Data/ Validation.
- Trong thẻ Settings---> Allow là Text length, Data chọn between, Minimum---> bạn đánh giá trị chiều dài tối thiểu của chuỗi (ví dụ là 10000), ô Maximum---> nhập giá trị chiều dài tối đa của chuỗi (ví dụ 50000).
- Chọn tab Error Alert.
- Trong Style---> chọn Stop, ô Title--->bạn hãy nhập tiêu đề của ô thông báo (ví dụ CAM!), ô Error message--->bạn nhập nội dung của ô thông báo (ví dụ: Ban khong duoc thay doi du lieu cua nguoi khac).
- Chọn Check box : Show error alert after invalid data is entered.
Sau cùng, để xoá những vùng dữ liệu đã được đặt Validation-->bạn hãy chọn Clear All.
Phần 8: 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-->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
Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub
Bây giờ bạn có thể thoải mái đặt Validation tại 1 cell bất kỳ trong cùng Sheet hay bất kỳ Sheet nào trong Workbook với Source cho Validation này là DSach.
Phần 6.2: hàm VLOOKUP trong Database động
Các bạn đã được giới thiệu về Dynamic Range, nghĩa là DS động với 1 cột dữ liệu, nhưng với Database có 4, 5 cột dữ liệu và dùng hàm VLOOKUP thì sao ?
Tôi giả sử chúng ta có DS động tại cột A như bài trên, nhưng A1 là tiêu đề cột.
Td : MaKH, từ A2 trở đi, chúng ta mới nhập dữ liệu.
Chúng ta sửa lại 1 chút trong hộp Refers to của Insert/Name/Define là : =OFFSET(DSach!$A$1,1,0,COUNTA(DSach!$A:$A)-1,1).
Database của chúng ta sẽ gồm cột B Tên KH, cột C Địa chỉ KH, cột D là Mã số thuế.
Chúng ta sẽ đặt tên cho Vùng gồm 4 cột này là Database.
Tại hộp Refers to bạn nhập công thức sau :
=OFFSET(DSach!$A$1,1,0,COUNTA(DSach!$A:$A)-1,COUNTA(DSach!$1:$1)).
Bây giờ, bạn đặt Validation để chọn MãKH, và dùng hàm VLOOKUP để lấy thông tin TênKH, Địa chỉ KH, Mã số thuế mà không lo phải đặt lại tên cho CSDL khi dữ liệu nhiều hơn số hàng mà chúng ta đã dùng để đặt tên cho mảng.
Phần 7: Bảo vệ dữ liệu trong Excel bằng Validation
Có một cách bảo vệ dữ liệu để người khác không được đụng vào mà chúng ta tận dụng khả năng có sẵn của Data / Validation.
- Chọn vùng dữ liệu bạn muốn bảo vệ.
- Chọn menu Data/ Validation.
- Trong thẻ Settings---> Allow là Text length, Data chọn between, Minimum---> bạn đánh giá trị chiều dài tối thiểu của chuỗi (ví dụ là 10000), ô Maximum---> nhập giá trị chiều dài tối đa của chuỗi (ví dụ 50000).
- Chọn tab Error Alert.
- Trong Style---> chọn Stop, ô Title--->bạn hãy nhập tiêu đề của ô thông báo (ví dụ CAM!), ô Error message--->bạn nhập nội dung của ô thông báo (ví dụ: Ban khong duoc thay doi du lieu cua nguoi khac).
- Chọn Check box : Show error alert after invalid data is entered.
Sau cùng, để xoá những vùng dữ liệu đã được đặt Validation-->bạn hãy chọn Clear All.
Phần 8: 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-->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
Nguồn: Sưu tầm.
0 comments :
Post a Comment