Nhập liệu nâng cao với nguồn dữ liệu bên ngoài - DataSource trong Add-in A-Tools v10.1 (1 người xem)

[Nguyễn Duy Tuân]

Bài viết này tôi xin chia sẻ về tính năng mới cũng như cách ứng dụng chức năng Nhập Liệu Nâng Cao của Add-in A-Tools trong việc thiết kế file Excel để nhập liệu nhanh.
Với Add-in A-Tools v10 trở về trước nguồn dữ liệu cho nhập liệu nâng cao là Excel Range - là vùng dữ liệu của file Excel đang mở.
Từ Add-in A-Tools v10.1 chúng tôi cung cấp giải pháp liên kết dữ liệu bên ngoài cùng với các hỗ trợ đa dạng hơn cho công việc nhập liệu trên Excel nhanh. Đây là thiết lập DataSource.

DataSource cho phép người dùng liên kết các nguồn dữ liệu như sau:

+ Vùng trên bảng tính Excel trong workbook đang làm việc hoặc một workbook khác.
+ Vùng trên bảng tính Excel phía máy chủ (A-Tools Server).
+ Lấy bằng SQL với CSDL bất kỳ bên ngoài (như SQL Server, MySQL, Oracle, Access, Excel,...)
+ Lấy bằng SQL với CSDL bất kỳ bên ngoài phía máy chủ (A-Tools Server)
+ Từ Google Sheets (trên Google Drive).
+ Từ Excel Online (trên OneDrive).
+ Từ các tập tin hoặc Web/URL có dữ liệu định dạng CSV, JSON, TXT từ máy local hoặc máy chủ (A-Tools Server).
+ Từ Web/Url chứa tag TABLE.

Video hướng dẫn:

Bài viết gốc tại đây.

Nội dung tiếp theo tôi sẽ trình bày chi tiết ứng dụng hơn về giải pháp này...

 

[viethung78]

Kỹ thuật của anh Tuân rất tốt. Tuy nhiên, cách làm này chưa thực sự hiệu quả vì phải kết nối realtime tới danh mục, trong khi dữ liệu danh mục lại ít thay đổi. Việc lựa chọn danh sách thông qua danh mục đã được đồng bộ sẽ đơn giản và ổn định hơn.
Ngoài ra, Microsoft Office 2024 và Microsoft 365 hiện đã hỗ trợ tính năng search list trong Data Validation, nên việc bổ sung add-in để nhập liệu theo cách này không còn quá cần thiết. Ưu tiên sử dụng tính năng built-in vẫn sẽ tối ưu hơn về độ tin cậy và khả năng bảo trì.

 

[Nguyễn Duy Tuân]

Kỹ thuật của anh Tuân rất tốt. Tuy nhiên, cách làm này chưa thực sự hiệu quả vì phải kết nối realtime tới danh mục, trong khi dữ liệu danh mục lại ít thay đổi. Việc lựa chọn danh sách thông qua danh mục đã được đồng bộ sẽ đơn giản và ổn định hơn.
Ngoài ra, Microsoft Office 2024 và Microsoft 365 hiện đã hỗ trợ tính năng search list trong Data Validation, nên việc bổ sung add-in để nhập liệu theo cách này không còn quá cần thiết. Ưu tiên sử dụng tính năng built-in vẫn sẽ tối ưu hơn về độ tin cậy và khả năng bảo trì.

Cảm ơn bạn đã góp ý. Mình trả lời từng ý của bạn như sau:

"cách làm này chưa thực sự hiệu quả vì phải kết nối realtime tới danh mục, trong khi dữ liệu danh mục lại ít thay đổi. Việc lựa chọn danh sách thông qua danh mục đã được đồng bộ sẽ đơn giản và ổn định hơn."

Tính năng nhập liệu nâng cao khi kết nối đến nguồn dữ liệu là Excel nó chỉ cập nhật lại khi phát hiện có thay đổi ở vùng dữ liệu nguồn. Việc load dữ liệu tôi ưu nhất nên rất nhanh.

"Ngoài ra, Microsoft Office 2024 và Microsoft 365 hiện đã hỗ trợ tính năng search list trong Data Validation, nên việc bổ sung add-in để nhập liệu theo cách này không còn quá cần thiết. Ưu tiên sử dụng tính năng built-in vẫn sẽ tối ưu hơn về độ tin cậy và khả năng bảo trì."

Tính năng Validation List của Office 365, 2024 đã hỗ trợ tìm và lọc nhưng chỉ hỗ trợ tìm trên MỘT CỘT thì có thể dùng tính năng đã có của Excel.
Thực tế người dùng Excel khi tra cứu thông tin họ phải tìm trên NHIỀU CỘT. Ví dụ thông tin cần nhập là MÃ nhưng họ phải tìm tên, mã số thuế, số điện thoại,... khi chọn thì lấy MÃ hoặc lấy các thông tin khác đi cùng. Đây là nhu cầu phổ biến đấy.

​

Tính năng Nhập liệu nâng cao ngoài việc hỗ trợ tìm kiếm còn hỗ trợ các tính năng khác như in liên tục....

​

Tính năng mà chức năng Nhập liệu nâng cao của Add-in A-Tools cung cấp đáp ứng gần như giống các phần mềm chuyên nghiệp khi nhập liệu cần tra tìm thông tin trên nhiều cột. Nếu người dùng có vài trăm hay vài ngàn mã hàng thì họ rất khó nhớ mã - Nếu chỉ tìm trên một cột như Excel 365, 2024. Việc tìm trên nhiều cột là yêu cầu chung của người dùng phần mềm. Tính năng này của A-Tools có từ phiên bản v7.0 - năm 2016 (đã 10 năm). Các phần mềm xây dựng trên Excel với A-Tools thì 100% khách hàng yêu cầu phải có tính năng "Nhập liệu nâng cao" dù Excel 365 đã hỗ trợ tìm và lọc nhưng chỉ một cột. Còn các tính năng khác mà mình đã nói và sẽ nói thêm trong chủ đề này này.

Tóm lại là nhu cầu chỉ đơn giản tìm trên một cột thì dùng Validation của Excel. Nếu yêu cầu nhập liệu như phần mềm, cần nhiều tính năng hơn thì "Nhập liệu nâng cao" của Add-in A-Tools hay các công cụ tương đương khác mới đáp ứng được.

(Nhập liệu nâng cao hiển thị ảnh)​

Giới thiệu thêm các bạn chương trình Excel - BanhangSQL ứng dụng tính năng Nhập liệu nâng cao và hàm BS_SQL.

(Chương trình này dùng nguồn dữ liệu bên trong cho nhập liệu nâng cao vì cả báo cáo và nguồn dữ liệu trong cùng một file. Phiên bản cho multi user tách CSDL một file, hệ thống báo cáo một file và ứng dụng "nguồn dữ liệu bên ngoài" - External Data Source được chia sẻ trong chủ đề này)​

 

[Nguyễn Duy Tuân]

Nếu các bạn muốn lập trình VBA tạo Nhập liệu nâng cao trên Userform, sử dụng class BSSearchEngine thì vào chủ để này xem:

Chủ đề 'Lập trình Userform-VBA tạo mục nhập có danh sách tìm kiếm với BSSearchEngine - Add-in A-Tools v10.1'

15/5/26

Nhập liệu nâng cao trong Add-in A-Tools cho phép tìm và lọc trên sheet. Từ phiên bản v10.1 cho phép người dùng lập trình với class BSSearchEngine để tạo cửa sổ tìm kiếm này với điều khiển - control trên Userform. Tốc độ nhanh dù hàng trăm ngàn dòng. BSSearchEngine cung cấp phong phú các thủ tục, thuộc tính, sự kiện để tùy biến giao diện và hành động.

Khi cài A-Tools bạn mở tập tin tại đường dẫn dưới đây để xem mã nguồn ví dụ đầy đủ:

(*) File mã nguồn ví dụ: "C:\A-Tools\HELP & DEMOS\A-Tools VBA Programming\BSSearchEngine - Create DropDown List on control\BSSearchEngine -...

• Nguyễn Duy Tuân
• Trả lời: 5
• Diễn đàn: BLUESOFTS: A-Excel, A-Tools

Bài đã được tự động gộp: 16/5/26

Thiết kế ứng dụng Excel cho nhiều người dùng chung hoặc khi file Excel lớn cần tách nhiều file

(Số liệu mang tính giả định sau khi tách file)​

Khi dữ liệu lớn bạn nên tách thành nhiều file để tối ưu hơn cho tính toán, việc tách file chúng ta chỉ phải mở phần dữ liệu cần ở chứ không phải mở tất cả.

Giả thiết file gốc của bạn là 20 MB gồm cả dữ liệu nguồn, báo cáo, form nhập liệu. Bạn nên tách như sau:
+ File 1: Data File (ví dụ 15 MB) file này chỉ lưu CSDL, các danh mục, sheet nhập phát sinh (chi tiết doanh thu, sổ nhật ký, thu chi,...)
+ File 2: : Report Files (ví dụ 3 MB) chứa các sheet làm báo cáo, tùy vào khối lượng các module, phạm vi quản lý bạn có thể tách thành nhiều file báo cáo. Ví dụ báo cáo liên quan xuất nhập tồn, báo cáo doanh thu, báo cáo công nợ, báo cáo lãi lỗ - tài chính,...
+ File 3: Form Files (ví dụ 2 MB) chứa các sheet, userform để nhập liệu (nếu có).

Trong tổng thể kích thước file chiếm phần lớn bởi dữ liệu nguồn - Data File. Việc tách ra giúp cho việc mở file sẽ nhẹ hơn, tôi xem báo cáo thì chỉ mở phần báo cáo thôi, không phải "cõng" theo khối dữ liệu nguồn 15 MB nữa. Add-in A-Tools với hàm BS_SQL, Nhập liệu nâng cao cho phép làm báo cáo và nhập liệu liên kết dữ liệu của file đang đóng.
Khi tách file việc phân quyền cho ai được mở cái gì, làm gì sẽ thuận lợi hơn nhiều khi chúng ta phối hợp làm việc nhóm.

Video dưới đây demo cách tạo nhập liệu nâng cao trên form nhập liệu, dữ liệu nguồn lấy từ máy tính khác trong mạng (Máy chủ - A-Tools Server)

 

[Nguyễn Duy Tuân]

HƯỚNG DẪN KẾT NỐI DỮ LIỆU EXCEL ĐANG MỞ - INTERNAL DATA SOURCE

Đây là giải pháp cũ của Add-in A-Tools từ cách đây 10 năm. Trước khi vào phần kết nối ngoài tôi lướt qua phần này để có tính hệ thống. Các bước đơn giản như sau:

+ Chọn vùng nhập liệu, nếu cần lấy nhiều cột thông tin thì chọn nhiều cột. Nếu các vùng nhận dữ liệu rời rạc nhau, không phải các cột liên tiếp thì chọn vùng đầu tiên, giữ CTRL chọn tiếp các vùng khác.

​

Màn hình thể hiện quá trình làm các bước như sau:

​

+ Ở mục "Dữ liệu nguồn"/"Source Range" nhấn vào biểu tượng chọn vùng, bạn có thể chọn vùng của file Excel bất kỳ đang mở.
Lưu ý nếu ở bước này bạn chọn vùng trên file Excel khác thì sau này nhập liệu bạn cũng phải mở file Excel đó lên - Đây chính là điểm khác biệt với kết nối ngoài - External Data Source.
+ Thực hiện ghép vùng nhập dữ liệu với cột nguồn dữ liệu ở phía bên phải màn hình thiết lập.
+ Mục "Cột nhận/Kiểm tra giá trị" hãy chọn cột nhận giá trị mang tính đại diện cho cả bản ghi (dòng chọn).
+ Check vào mục "Cột xxx chứa giá trị duy nhất" khi đó A-Tools sẽ tạo thêm Validation List cho vùng nhập liệu. Lợi ích là khi bạn chọn giá trị các cột khác sẽ nhận giá trị đi cùng giống như bạn phải dùng hàm VLOOKUP tìm và lấy giá trị trả về nhưng A-Tools không dùng hàm mà tự tìm và lấy ra giá trị cho bạn.
+ Check vào mục "Chỉ hoạt động trên cột xxx". Nếu chỉ kích hoạt cửa sổ tìm kiếm khi nhập liệu trên cột xxx thì ta check vào. Phiên bản v10.0 trở về trước không có mục này vì thế cửa sổ tìm kiếm được kích hoạt trên tất cả các vùng nhập liệu.
+ Các thiết lập khác như xác định cột hiển thị hình ảnh, các tùy chọn kiểm soát giá trị tìm được,... bạn tìm hiểu các tab còn lại.
Bấm nút "Lưu"/"Add" để thêm thiết lập.

Khi nhập liệu ta sẽ có màn hình tìm kiếm:

(Bạn có thể nhập nội dung tìm trên thanh công thức hoặc ô trên sheet đều tìm được như nhau. Nguồn dữ liệu dù hàng trăm ngàn dòng tốc độ nhập và tìm vẫn rất nhanh)​

Mẹo nhập nhiều dòng:

Nếu bạn muốn nhập một lúc nhiều dòng. Chọn nhiều dòng, nhập nội dung cần tìm, chọn các dòng trong dnh sách tìm được, nhấn ENTER lập tức các dòng chọn trong cửa sổ tìm kiếm được nhập vào các dòng bạn chọn.

Tùy chỉnh nội cửa sổ tìm và lọc:

+ Thay đổi vị trí hiển thị cột: dùng chuột nhấn và giữ tên cột rồi di chuyển.
+ Ẩn/hiện cột: nhấp chuột phải tick hoặc bỏ tick những cột muốn hiển thị hoặc ẩn.
+ Thay đổi kích thước cột: đặt chuột giữa hai cột nhấn và kéo trái, phải.
+ Sắp xếp: nhấn chuột vào tên cột mà bạn muốn sắp xếp, muốn hủy bỏ sắp xếp, nhấp chuột phải chọn "Clear Sort".

Các lệnh hỗ trợ khác

+ In liên tục: chọn các dòng trong cửa sổ tìm được, nhấp chuột phải chọn "in liên tục". Chức năng này rất cần khi bạn tạo báo cáo trên một sheet và lọc theo các điều kiện. Bạn hãy thiết lập ô điều kiện với Nhập Liệu Nâng cao. Khi chọn "In liên tục" A-Tools sẽ thay thế lần lượt giá trị vào điều kiện rồi thực hiện lệnh in sheet.
+ Cập nhật dữ liệu - Refresh: bạn dùng lệnh này nếu là kết nối dữ liệu bên ngoài, ví dụ kết nối tới SQL Server, MySQL, Google Sheets, Access, Excel file, JSON, CSV.... nếu cần cập nhật giá trị tại thời điểm thì hãy nhấn lệnh này.

HƯỚNG DẪN KẾT NỐI DỮ LIỆU BÊN NGOÀI - EXTERNAL DATA SOURCE (Từ Add-in A-Tools v10.1)

Hướng dẫn kết nối dữ liệu Excel trên máy chủ (A-Tools Server)

+ Bước 1: Bật máy chủ A-Tools. Một máy tính chứa dữ liệu nguồn bạn cài A-Tools sau đó vào menu "A-Tools" chọn "Start Server"/"Tạo máy chủ"

​

Các hàm lấy dữ liệu máy chủ BS_SQL, BS_JOIN, BS_DATA, BS_JSON, chức năng cùng làm việc với Excel qua mạng bạn phải khởi tạo máy chủ như trên.
Mở những tập tin Excel muốn chia sẻ trên máy chủ.

+ Bước 2: Thiết lập nhập liệu nâng cao phía máy khách

Chọn vùng nhập liệu, vào menu A-Tools chọn "Nhập liệu nâng cao", nhấn nút "External Source":

​

Thiết lập trên cửa sổ "DATA SOURCE":
+ Mục "Data Source Type" chọn "Excel Range"
+ Mục "Host Type" chọn "A-Tools Server". Nhập địa chier IP máy chủ, user, password rồi "Kết nối"

​

Sau khi kết nối thành công bạn sẽ có danh sách file Excel đang mở phía máy chủ, chọn file và nhập tên hoặc địa chỉ vùng

​

+ File Excel: chứa danh sách các tập tin Excel đang mở (Workbook) phía máy chủ. Bạn phải chọn một file cụ thể.
+ Range Addess: Bạn hãy chọn NAME của workbook (File Excel) là tốt nhất. Trường hợp nhập địa chỉ vùng hãy đảm bảo cấu trúc Tên_Sheets!Địa_chỉ_vùng. Ví dụ DMHH!A4:E100
Cuối cùng chọn "Ok"/"Đồng ý" bạn sẽ quay về màn hình thiết lập như phần đầu hướng dẫn.

Các Data Source dưới đây cho phép lấy dữ liệu tại máy đang chạy - Local và trên máy chủ (A-Tools Server)

+ Excel Range
+ SQL/Recordset
+ Data File (txt, csv, json)
+ Web - Url (tag Table)

Khi bạn cần xây dựng chương trình Excel cho nhiều người dùng chung trong mạng hãy lựa chọn "A-Tools Server". Dữ liệu nguồn lưu ở máy chủ, các máy khác nhập liệu liên kế đến máy chủ để tra cứu và nhận dữ liệu tìm được, làm báo cáo. File phía máy khách sẽ rất nhẹ và đảm bảo dữ liệu tập trung. Cách làm Excel cũng như A-Tools phiên bản cũ ta phải đồng bộ hết dữ liệu về local rôi mới thiết lập nhập liệu nâng cao, nhược điểm là file nặng vì phải cõng cả một đống dữ liệu làm nguồn, việc cập nhật mới khá mất thời gian.

Tính linh hoạt: dù bạn thiết lập lấy dữ liệu trên máy chủ nhưng nếu bạn chạy không kết nối máy chủ và bạn mở file Excel với trùng tên đã thiết lập nó sẽ tự lấy dữ liệu của file Excel đang mở (nếu không kết nối máy chủ). Cùng một thiết lập bạn vẫn làm việc lúc độc lập hoặc khi kết nối máy chủ.

Toàn bộ hướng dẫn Excel Range, lấy dữ liệu phía máy chủ có video tách riêng ở đây.

Bài viết tiếp theo tôi sẽ hướng dẫn kết nối dùng SQL - Đây là giải pháp liên kết tới dữ liệu các phần mềm khác mà bạn đang chạy.