Khi thao tác trong Excel, nhiều lúc bạn sẽ cần nhập dữ liệu, sau đó đưa dữ liệu vào một bảng, hay còn gọi là tạo FormData. FormData được hiểu là thao tác tạo dữ liệu nhập trên một Form đã định sẵn, lúc này dữ liệu nhập vào Form sẽ được lưu ở một nơi nào đó. Bài viết hôm nay sẽ hướng dẫn bạn cách tạo form nhập dữ liệu trong Excel.
Phương pháp tạo FormData rất dễ thực hiện, hầu như không phải viết code, với những đặc điểm nổi bật:
+Viết code rất ít, chỉ có công thức và công thức quen thuộc.
+ Áp dụng được cho tất cả mọi loại bảng dữ liệu của bạn.
+ Tái sử dụng đượcc nhiều lần.
Nội dung bài viết
- 1 1. Tạo FormData ban đầu.
- 2 2. Tạo một bảng setting.
- 3 3. Thêm code và Edit code vba.
- 4 4. Thiết lâp bảng setting.
1. Tạo FormData ban đầu.
Đầu tiên, bạn thực hiện tạo một Form nhập liệu. Trong bài viết này, ta tạo cho nó giống với một cửa sổ Window. Form của bạn sẽ ở dạng tùy thích, có thể là dạng bảng hay dạng các ô phân tán. Tiếp đó thực hiện thiết kế bảng dữ liệu, nơi dữ liệu của Form sẽ được lưu. Form ở trong ví dụ này có 5 tham số, bảng dữ liệu sẽ có 8 cột.
2. Tạo một bảng setting.
Mỗi một FormData sẽ cần có một bảng setting, chứa những công thức, nơi bạn sẽ dành nhiều thời gian nhất để xây dựng. Nó gồm có hai dòng, số cột sẽ nhiều hơn 1 cột so với số cột trong bảng dữ liệu. Trong ví dụ bảng dữ liệu có 8 cột, vậy nó sẽ là 9 cột, chúng ta sẽ đặt tên bảng này là “BangSetting”.
Xem thêm: Cách làm danh mục bảng trong Word đơn giản, nhanh chóng
3. Thêm code và Edit code vba.
+ Bạn tiến hành tải module mFormData về máy, và import nó vào trong VBA.
+ Tiếp đó thực hiện chèn thêm một module mới.
+ Ứng với một FormData, bạn thao tác tạo một Sub Vba như trên hình. Bạn có thể thực hiện copy cho nhanh, chúng chỉ khác nhau ở “BangSetting”, và đó là tên của bảng setting mà liên kết với FormData của bạn.
+ Sau đó, bạn gắn macro cho nút bấm “Thêm” vào sub này.
4. Thiết lâp bảng setting.
Thực hiện tạo liên kết bảng dữ liệu với bảng setting:
+Tại ô đầu tiên của bảng setting, bạn thao tác gõ công thức =XXX, với XXX là địa chỉ của hàng dữ liệu cuối cùng của bảng dữ liệu.
Tạo thông báo:
+ Những ô của hàng thứ 2 tại bảng setting nhận các công thức dùng để kiểm tra dữ liệu. Những công thức này bạn sử dụng để kiểm tra tính hợp lệ của dữ liệu trong Form. “” có nghĩa là ok.
Tạo liên kết dữ liệu:
+ Khi tiến hành nhấn nút “Thêm”, giá trị công thức của ô thứ [1,x] bảng setting sẽ được ghi vào trong một hàng mới của bảng dữ liệu tại vị trí [*,x-1], x>1.
Cơ chế hoạt động:
+ Mỗi khi bạn nhấn nút “Thêm”, thì Excel kiểm tra giá trị của các ô nằm ở hàng 2 của bảng setting. Nếu như có một ô có nội dung khác rỗng, thì một thông báo sẽ xuất hiện để hỏi bạn có tiếp tục lưu dữ liệu này nữa hay không? Tiếp đó nó sẽ thêm những hàng mới vào trong bảng dữ liệu, và tiến hành copy các giá trị tương ứng ở bảng setting vào các dòng mới này. Tạo ra hành động lưu dữ liệu. Chúc bạn thực hiện thành công!
Cập nhật mới nhất ngày 01 tháng 07 năm 2022: Nếu các bạn đã từng sử dụng Access để xây dựng 1 CSDL [Cơ sở dữ liệu] hẳn các bạn đã từng sử dụng Form trong Access để nhập liệu nhanh và giao diện nhập liệu trực quan. Tương tự như vậy, ta cũng sẽ có cách để nhập liệu trong Excel tương tự với Access so với cách truyền thống nhập cho từng ô. Bài viết sẽ hướng dẫn các bạn tạo Form trong Excel để nhập liệu tự động đơn giản nhất.
NỘI DUNG
- Cách tạo các điều kiện ban đầu cho Form nhập liệu
- Video hướng dẫn chi tiết
- Cách tạo các liên kết giữa các sheet để nhập liệu
- Cách sử dụng Form vừa tạo
Cách tạo các điều kiện ban đầu cho Form nhập liệu
Đầu tiên các bạn hãy vào link sau để tải file Excel về thực hành:
- Tải File code VBA FORM
- Tải File Code VBA
Video hướng dẫn chi tiết
Để bắt đầu thực hành các bạn mở file Excel vừa tải về nó sẽ có 3 sheet cụ thể như sau:
Chú ý:
+] Nếu các bạn tạo mới thì nhớ lưu file với đuôi là .xlsb còn nếu lưu file bình thường với đuôi .xls thì sẽ mất code VBA [các bạn sẽ phải code lại].
+] Vào menu File -> Options -> Customize Ribbon -> Customize the Ribbon -> Developer -> OK để mở chế độ VBA.
Tiếp theo, các bạn vào sheet danh sách. Ở ô C1 và C2 các bạn gộp vào [click vào Merge & Center để gộp] và gõ "số thí sinh", còn ô D1 và D2 gộp lại và gõ =COUNTA[B:B]-1 vì dùng để đếm số thí sinh, B:B là chọn hết cột B.
Các bạn mở lại sheet Form và kẻ khung viền cho bảng. Tiếp theo các bạn sẽ gõ hàm =ISBLANK[C4] ở ô E4 để kiểm tra xem ô C4 đã nhập gì chưa. Hàm trả về TRUE nếu chưa nhập dữ liệu, FALSE nếu đã nhập dữ liệu rồi.
Tiếp tục:
+] trong ô E5, gõ =LEN[C5]= 10 chữ số.
+] trong ô E6, gõ =ISNUMBER[C6]=FALSE tạo điều kiện nhập năm sinh với giá trị TRUE khi chưa nhập.
+] trong ô E7, gõ =OR[ISERROR[FIND["@",C7]],ISERROR[FIND[".",C7]],ISERROR[FIND[" ",C7]]=FALSE], tuỳ khá dài nhưng nó tạo điều kiện là nhập vào email ở ô C7 không có dấu cách trong tên email, phải có "@" và ".".
+] để tạo list trong mục Trình độ, các bạn click vào ô C8 - trong menu Data -> Data Validation. Trong mục Allow -> List, còn trong mục Source, các bạn vào sheet dữ liệu trong cột Trình độ chọn hết dữ liệu từ B4 đến B7.
+] các mục Kinh nghiệm, Vị trí ứng tuyển các bạn làm tương tự. Có mũi tên như trong hình tức đã làm đúng.
+] còn mục Kỹ năng, trong ô C11 các bạn vào menu Developer -> Insert -> Checkbox [Form Control]. Rồi các bạn vẽ ra 1 Checkbox và nhấn Ctrl+D để có 3 Checkbox và sửa tên như hình dưới [click phải chuột vào Checkbox -> Edit Text].
+] còn mục Giới tính thì các bạn vào menu Developer -> Insert -> Option Button [Form Control]. Làm tương tụ như Kỹ năng.
Sau khi thực hiện xong các bước trên, ta cần list Trình độ ra. Các bạn gõ các mục Excel, English, Word lần lượt vào các ô H1 I1 J1 và trở lại mục Kỹ năng click phải chuột vào Checkbox Excel và chọn Format Control. Rồi thực hiện các bước như 3 hình dưới.
Khi các bạn click vào ô Excel thì khi click ô Control thì hiện TRUE, không click hiện FALSE.
Trong ô H3, các bạn gõ =COUNTIF[H2,TRUE] và làm tương tự như các ô English - Word. Nó sẽ trả về 1 nếu ô phía trên là TRUE và ngược lại. Hàm này đổi về 1 và 0 của TRUE và FALSE để dễ code VBA.
Đối với mục Giới tính, các bạn chọn 1 ô [ô G8 chẳng hạn] rồi gõ "Giới tính" và click chuột phải vào Checkbox Nam -> Format Control. Trong ô Cell link chọn ô C12 [có chứa Checkbox]. Nam = 1 và Nữ = 2.
Trong ô H8, các bạn gõ =IF[C12=1,"Nam","Nữ"] , bước này dùng để gán Nam - Nữ vào sheet Danh Sách.
Ngoài ra, các thí sinh trong File Excel không thể có email giống nhau được. Trong ô F7, gõ =COUNTIF['Danh Sách'IE:E,C7], trong đó cụm 'Danh Sách'IE:E là cột Địa chỉ email trong sheet Danh Sách.
Cách tạo các liên kết giữa các sheet để nhập liệu
Trong mục này, các bạn tạo 1 Shape làm nút Cập nhật dữ liệu [vào menu Insert -> Shapes] vào sheet Danh Sach.
Tiếp theo,các bạn nhấn Alt+F11 để mở VBA lên và tạo Module1 trong sheet1 [Form] và gõ các đoạn code sau cho chính xác:
Sau khi gõ code VBA xong ra màn hình chính Excel, click phải chuột vào nút Cập nhật dữ liệu và chọn Assign Macro -> nhập liệu -> OK.
Để thay các giá trị số 1 và 0 trong Trình Độ, các bạn bôi đen nguyên cụm cột Trình Độ rồi vào menu Home -> Conditional Formatting. Hộp thoại New Formatting Rule, trong Format Style -> Icon Sets và cài đặt giống trong hình dưới: