anh ơi cho em hỏi chút ah.
Em đang sử dụng Add-in a Tools bản mới nhất ngày 1.7. Dữ liệu của em phải xử lý đến hơn 100.000 dòng tuy nhiên khi em dùng BS_SQL("select * from [data$]") với dữ liệu < 65k dòng thì chạy ra data, lớn hơn 65K dòng thì báo VALUE, vậy có cách nào sửa lỗi này ko ah?
Các file của em xử lý thường rất lớn (từ trên 100k dòng trở lên) - em cảm ơn nhiều ah
Em kiểm tra phiên bản chắc chắn là mới nhé (9.1.1.1 ngày 01/7/2019)
Việc thứ hai là sau FROM [TÊN SHEET$]
Nếu đã làm đúng như trên mà vẫn bị em gửi file lên anh xem nhé.
Công thức của mình đúng nhưng mà nó chỉ áp dụng được cho nhỏ hơn 65k dòng (đến 65536), mà file của mình toàn lớn hơn 100k dòng lận (có những file 700k dòng) - giúp mình với
Em vừa đọc lại hết các hướng dẫn của Add-in A-tools từ các phiên bản trước, em thấy ở trang 22 có đề cập là với hàm BS_SQL thì chỉ giới hạn tối đa là 65.536 dòng
Ở phiên bản 8. anh có đề cập là Nhập liệu nâng cao, dữ liệu nguồn cho phép tối đa là 1.048.576 dòng
Vậy em hiểu là dữ liệu nguồn trong phần nhập liệu thì có thể lên tới 1 triệu dòng nhưng để lấy data ra báo cáo thì chỉ tối đa có 65k dòng đúng ko ah?
Vậy anh có cách nào để báo cáo của em vẫn lấy ra được phần nhập liệu cover hết tất cả dữ liệu đầu vào hay không ah? Em có nghĩ đến cách chia dữ liệu đầu vào thành các phần khác nhau để maximum 1 data là 65k dòng, mong anh cho em giải pháp toàn diện hơn
Em vừa đọc lại hết các hướng dẫn của Add-in A-tools từ các phiên bản trước, em thấy ở trang 22 có đề cập là với hàm BS_SQL thì chỉ giới hạn tối đa là 65.536 dòng
Ở phiên bản 8. anh có đề cập là Nhập liệu nâng cao, dữ liệu nguồn cho phép tối đa là 1.048.576 dòng
Vậy em hiểu là dữ liệu nguồn trong phần nhập liệu thì có thể lên tới 1 triệu dòng nhưng để lấy data ra báo cáo thì chỉ tối đa có 65k dòng đúng ko ah?
Vậy anh có cách nào để báo cáo của em vẫn lấy ra được phần nhập liệu cover hết tất cả dữ liệu đầu vào hay không ah? Em có nghĩ đến cách chia dữ liệu đầu vào thành các phần khác nhau để maximum 1 data là 65k dòng, mong anh cho em giải pháp toàn diện hơn
Chào bạn. Đúng là có sự cố với dòng phiên bản 9.0 hiển thì bị giới hạn 65536 dòng với hàm BS_SQL (không phải lúc nào cũng bị....). Ngoài bạn ra cũng đã có nhiều người liên hệ nói về việc này. Cảm ơn bạn. Bên mình đã nâng cấp bant Add-in A-Tools 9.1.1.2 khắc phục lỗi này. Bạn đọc thông tin nâng cấp và vào trang chủ tải bản mới nhất về chạy và báo lại kết quả mình nhé.
Add-in A-Tools v9.1.1.2: 01-07-2019 (Add-in A-Tools 2019)
+ Sửa: lỗi hàm BS_SQL chỉ hiển thị số dòng <=65536. Lỗi thường do máy đang mở nhiều file trong đó có file định dạng Excel 2003 (*.xls)
+ Sửa: tập tin ví dụ về lấy thông tin máy chủ cập nhật cách gọi lệnh theo version mơi.
(C:\A-Tools\HELP & DEMOS\A-Tools VBA Programming\Network\BSNetworkInfo.xls)
+ Thêm: bổ sung các thuộc tính để biết thêm thông tin phía máy chủ trong qua class BSNetwork (dùng trong VBA)
BSNetwork.ServerAToolsName - Biết tên phần mềm A-Tools phía máy chủ (ví dụ "Add-in A-Tools")
BSNetwork.ServerVersionStr - Biết tên phiên bản A-Tools phía máy chủ (ví dụ "v9.1.1.1")
BSNetwork.ServerVersionNum - Biết số hiệu version A-Tools phía máy chủ (ví dụ 6503)
BSNetwork.ServerExcelVersion - Biết tên version (number) của Excel đang chạy phía máy chủ
+ 10: Excel XP
+ 11: Excel 2003
+ 12: Excel 2007
+ 14: Excel 2010
+ 15: Excel 2013
+ 16: Excel 2016, 2019, 365
1- Em có nhiều sheet giống nhau, em làm theo các công thức combine sheet hoặc combine file (BS_Join) thì công thức là liệt kê các phần của các sheet vào liên tiếp với nhau; em không combine với hàm Group by giống như BS_SQL được: anh xem có cách nào em vừa gom data từ các sheet giống hệt nhau nhưng được group lại theo 1 nhóm nào đó hay không (file BS_Join) (vì anh thấy cái file nãy của em rất nặng, em còn các file data khác mà mỗi file 1 tháng cỡ 700k dòng), nếu nhóm lại với nhau chắc ko tải được ah
2- Với phần nhiều người cùng nhập liệu vào 1 file data: do việc có nhiều người nhập liệu, không tránh được sẽ có nhiều người làm sai, em dự định thiết kế nhiều file có form giống nhau, mỗi file có 1 người điền, người đó điền vào rồi thì sẽ được 1 người quản lý cấp trên xem và ấn nút phê duyệt, sau khi quản lý cấp trên xem xong thì file đó mới đến em, em xem xong thì em nhấn nút duyệt thì file đó sẽ được chạy vào file data tổng của em, em sẽ nhìn thấy được ô người lập, người duyệt đều có người sign off rồi thì em mới nhận file để đưa vào làm báo cáo -> anh coi có cách nào làm được điều đó trên excel không nhé (phân tầng cấp phê duyệt)
E cảm ơn anh nhiều
File đính kèm
Function BS_JOIN append data from multi sheet - Gộp dữ liệu từ nhiều sheet.xls
1- Em có nhiều sheet giống nhau, em làm theo các công thức combine sheet hoặc combine file (BS_Join) thì công thức là liệt kê các phần của các sheet vào liên tiếp với nhau; em không combine với hàm Group by giống như BS_SQL được: anh xem có cách nào em vừa gom data từ các sheet giống hệt nhau nhưng được group lại theo 1 nhóm nào đó hay không (file BS_Join) (vì anh thấy cái file nãy của em rất nặng, em còn các file data khác mà mỗi file 1 tháng cỡ 700k dòng), nếu nhóm lại với nhau chắc ko tải được ah
E cảm ơn anh nhiều
Phần này, bạn thử làm theo cách sao
1. Ở hàm BS_Join, bạn đặt name là DT_TONGHOP
2. Sau đó ở hàm BS_SQL bạn mới bắt đầu sử dụng thuộc tính GROUP BY
Mã:
SELECT DT_TONGHOP.MA_NB, DT_TONGHOP.KHO, DT_TONGHOP.MA_VLSPHH, DT_TONGHOP.SLG,
DT_TONGHOP.THANH_TIEN
FROM DT_TONGHOP
GROUP BY DT_TONGHOP.MA_NB, DT_TONGHOP.KHO, DT_TONGHOP.MA_VLSPHH, DT_TONGHOP.SLG,
DT_TONGHOP.THANH_TIEN
Tuy nhiên trong dữ liệu của bạn, mình thấy thiếu thông tin người bán vậy
Ý anh là đầu tiên vẫn gom hết về 1 file của BS join, sau đó mới dùng hàm khác để lấy thông tin = BS_SQL và GROUP BY đúng không ah?
Nhưng mà như này thì khối lượng dữ liệu của sheet DT_TONGHOP quá lớn (như em có nói mỗi sheet của em hơn 700k dòng -> nếu vậy em sẽ dùng BS_SQL và GROUP BY để lấy được thông tin trước, sau đó mới dùng BS_JOIN thì có lẽ khối lượng sẽ đỡ hơn nhiều
Trước khi trả lới các câu hỏi của bạn mình muốn biết bạn đã test lại vấn đền số dòng lấy ra > 65536 chưa? Cần nâng cấp 9.1.1.2 trước khi làm.
Các câu hỏi của bạn mình trả lời như sau:
1- Em có nhiều sheet giống nhau, em làm theo các công thức combine sheet hoặc combine file (BS_Join) thì công thức là liệt kê các phần của các sheet vào liên tiếp với nhau; em không combine với hàm Group by giống như BS_SQL được: anh xem có cách nào em vừa gom data từ các sheet giống hệt nhau nhưng được group lại theo 1 nhóm nào đó hay không (file BS_Join) (vì anh thấy cái file nãy của em rất nặng, em còn các file data khác mà mỗi file 1 tháng cỡ 700k dòng), nếu nhóm lại với nhau chắc ko tải được ah
Khi bạn dùng từ khóa GROUP BY để gộp thì phải có các nguyên tắc cần:
+ Dùng hàmthống kê như SUM, COUNT, AVG, MIN, MAX, VAR,... bao các cột cần tổng hợp
+ Các cột không nằm trong các hàm thống kê haowcj cần phân tách tiêu chí gộp thì cần đặt sau từ khóa GROUP BY
Ví dụ:
Của của bạn là
SELECT MA_NB, KHO, MA_VLSPHH, SLG, THANH_TIEN AS TT FROM DATA1 GROUP BY MA_VLSPHH
Công thức trên sai 2 lỗi: không có cột nào tổng hợp, không chỉ định đủ các cột sau từ khóa GROUP BY
Công thức đúng là:
SELECT MA_NB, KHO, MA_VLSPHH, SUM(SLG) AS SLG, SUM(THANH_TIEN) AS TT FROM DATA1 GROUP BY MA_NB, KHO, MA_VLSPHH
(Bạn xem file đính kèm)
2- Với phần nhiều người cùng nhập liệu vào 1 file data: do việc có nhiều người nhập liệu, không tránh được sẽ có nhiều người làm sai, em dự định thiết kế nhiều file có form giống nhau, mỗi file có 1 người điền, người đó điền vào rồi thì sẽ được 1 người quản lý cấp trên xem và ấn nút phê duyệt, sau khi quản lý cấp trên xem xong thì file đó mới đến em, em xem xong thì em nhấn nút duyệt thì file đó sẽ được chạy vào file data tổng của em, em sẽ nhìn thấy được ô người lập, người duyệt đều có người sign off rồi thì em mới nhận file để đưa vào làm báo cáo -> anh coi có cách nào làm được điều đó trên excel không nhé (phân tầng cấp phê duyệt)
Phê duyệt dữ liệu trước khi gộp là bài toán hay. Có ít nhất 2 cách để làm. 1. Cách 1: Tự tạo hai cột phụ để phê duyệt 2 lớp (cấp). Cách làm này không cần tính nang phê duyệt của A-Tools.
- Từ sheet nguồn, bạn tạo 2 cột phụ "Kiểm tra", "Phê duyệt". Khi bạn phân quyền người dùng thì chỉ định vùng nhập liệu mà nó không bao hai cột phụ kia, khi đó user không thể tự ý vào được.
- Tạo vùng ảo trên sheet đó. Chọn sheet cần tạo vùng ảo, bạn bấm "Tạo vùng và địa chỉ...", dùng chuột chọn vùng được phép nhập liệu.
- Sang màn hình phân quyền cho nhóm. Hãy chỉ định nhóm nào được mở vùng ảo này.
Như vậy một sheet bạn tạo được nhiều vùng ảo với các thuộc tính hiển thị và nhập liệu khác nhau, bạn phân quyền cho nhóm nào được mở vùng ảo này.
Trình tự làm:
1. Vào quản trị máy chủ: tạo vùng ảo từ menu
(Tạo vùng ảo từ menu)
2. Chọn vùng để nhập liệu. Những vùng không chọn sẽ không được nhập liệu.
Vùng ảo cho phép đặt các thuộc tính ẩn cột, dòng, lưu trạng thái phê duyệt,... không làm thay đổi sheet nguồn về cấu trúc. Dữ liệu được đồng bộ với sheet nguồn.
(Chọn vùng cho phép nhập liệu cho vùng ảo)
(Sau khi tạo được vùng ảo)
3. Phần quyền cho nhóm user được mở vùng ảo mà không mở sheet gốc, khi đó user sẽ chị sự thiết lập của vùng ảo như chỉ nhìn thấy một số dòng, cột, khu vực không được nhập liệu, các quyền thiết lập từ máy chủ....
2. Cách 2: Dùng tính năng phê duyệt của máy chủ
Về cơ bản quy trình thiết lập giống "Cách 1" nhưng cần chỉ định vùng lưu user & time, vùng phê duyệt cho vùng ảo.
Gộp dữ liệu với file đã phê duyệt
Khi vùng dữ liệu đã ghi nhận trạng thái ở vùng "Kiểm tra", "Phê duyệt" thì nó chính là trường dữ liệu để làm điều kiện WHERE
SELECT * FROM DATA WHERE [Phê Duyệt]=1
Bài đã được tự động gộp:
Khi vùng ảo được mở phía máy khách. User sẽ nhìn thấy nó như thế này.
Phần phân cấp với group nhóm thì em sẽ xem chiều nay
Còn phần test dữ liệu < 65536 thì em vẫn đang không làm được với excel 2007, em đang thử dùng sang máy khác với excel 2013 xem có được ko, có gì em sẽ fb tiếp ah
Em đã test với excel 2013 và 2016 thì phải từ version 2016 trở đi mới áp dụng được việc chạy dữ liệu trên 65536 dòng nhé.
Lưu ý: khi dùng với các công thức của Add-in A-Tools thì thay tên trường dữ liệu bằng tên sheet
Ví dụ: bình thường mọi người sẽ đặt name cho vùng dữ liệu trong file là Data=data$A$1:$G$5000 đối với tên sheet là Data -> công thức sẽ là
BS_SQL("SELECT * FROM DATA")
Nếu chạy cho dữ liệu lớn hơn 65536 thì việc đặt tên vùng dữ liệu sẽ không áp dụng được, lúc này sẽ phải change công thức thành
BS_SQL("SELECT * FROM [DATA$]")
Áp dụng tương tự cho tất cả các công thức khác của Add-in A-tools.
Chào Anh Nguyễn Duy Tuân!
Em mới cài Add-In A-tools và tự học cách sử dụng qua các bài học kèm theo phần mềm và có một vướng mắc như sau ạ!
Em lập dữ liệu và chạy thử hàm BS_SQL khi công thức hoạt động e tiến hành định dạng và thêm hàng tổng ở dưới cùng, tuy nhiên khi em thay đổi điều kiện thì bảng biểu không tự động co dãn theo sự thay đổi của hàm! Dữ liệu lấy về đè mất cả hàng Tổng chứ không phải chèn thêm hàng vào anh ạ!
Em đã thử lập file excel mới, đã thử sử dụng hàm đơn giản hơn để thử nhưng vẫn bị vướng như trên, em sử dụng Excel 2016 bản 32 bit
Em có up file đính kèm mong anh giúp đỡ ạ!
Chào Anh Nguyễn Duy Tuân!
Em mới cài Add-In A-tools và tự học cách sử dụng qua các bài học kèm theo phần mềm và có một vướng mắc như sau ạ!
Em lập dữ liệu và chạy thử hàm BS_SQL khi công thức hoạt động e tiến hành định dạng và thêm hàng tổng ở dưới cùng, tuy nhiên khi em thay đổi điều kiện thì bảng biểu không tự động co dãn theo sự thay đổi của hàm! Dữ liệu lấy về đè mất cả hàng Tổng chứ không phải chèn thêm hàng vào anh ạ!
Em đã thử lập file excel mới, đã thử sử dụng hàm đơn giản hơn để thử nhưng vẫn bị vướng như trên, em sử dụng Excel 2016 bản 32 bit
Em có up file đính kèm mong anh giúp đỡ ạ!
Công thức của bạn bị sai tham số. Cũ của bạn là:
=bs_Sql(A5 , LEFT(B3&B2&D2,0),A6)
Lỗi bạn dùng hàm LEFT thành tham số thứ 2 (tham số thứ hai phải là Options: INSERT=YES)
Công thức chỉ cần sửa lại là:
=bs_Sql(A5 & LEFT(B3&B2&D2,0),A6)
Em chào anh Tuân! Cảm ơn anh đã trả lời câu hỏi lần trước của em về việc sử dụng hàm LEFT
Anh cho e hỏi thêm chút ạ! Khi em lồng hàm tạo sự kiện [ONDBLCLICK=GOTO] vào hàm BS_SQL có sử dụng DBKEY hoặc hàm BS_JOIN thì hàm GOTO không đưa mình đến nguồn dữ liệu được ạ anh!
Em đã thử truy xuất dữ liệu như sau:
Truy xuất từ hàm BS_SQL trên cùng một work book: hoạt động tốt
Truy xuất từ hàm BS_JOIN trên cùng một work book: không hoạt động
Truy xuất từ hàm BS_SQL có sử dụng DBKEY: không hoạt động
Liệu có cách nào để truy xuất được dữ liệu từ một work book khác hoặc từ hàm BS_JOIN không anh!
Và việc Update dữ liệu nữa ạ! Em có 3 file excel liên kết với nhau theo tứ tự File1 -> File2 -> File3
Ở File3 em muốn lấy dữ liệu của File2 thì e phải vào File2 để cập nhật công thức A-tools để File2 lấy dữ liệu của File1
Có cách nào để Update File2 từ File3 mà không cần phải mở File2 lên không ạ
Em chào anh Tuân! Cảm ơn anh đã trả lời câu hỏi lần trước của em về việc sử dụng hàm LEFT
Anh cho e hỏi thêm chút ạ! Khi em lồng hàm tạo sự kiện [ONDBLCLICK=GOTO] vào hàm BS_SQL có sử dụng DBKEY hoặc hàm BS_JOIN thì hàm GOTO không đưa mình đến nguồn dữ liệu được ạ anh!
Em đã thử truy xuất dữ liệu như sau:
Truy xuất từ hàm BS_SQL trên cùng một work book: hoạt động tốt
Truy xuất từ hàm BS_JOIN trên cùng một work book: không hoạt động
Truy xuất từ hàm BS_SQL có sử dụng DBKEY: không hoạt động
Liệu có cách nào để truy xuất được dữ liệu từ một work book khác hoặc từ hàm BS_JOIN không anh!
Và việc Update dữ liệu nữa ạ! Em có 3 file excel liên kết với nhau theo tứ tự File1 -> File2 -> File3
Ở File3 em muốn lấy dữ liệu của File2 thì e phải vào File2 để cập nhật công thức A-tools để File2 lấy dữ liệu của File1
Có cách nào để Update File2 từ File3 mà không cần phải mở File2 lên không ạ
Việc này bắt buộc phải tuần tự em a. Vì file nguồn em làm công thức lấy data từ file khác. Cách khắc phục em có thể viết VBA để bấm nút tự mở và cập nhật.
Em chào anh Tuân! Cảm ơn anh đã trả lời câu hỏi lần trước của em về việc sử dụng hàm LEFT
Anh cho e hỏi thêm chút ạ! Khi em lồng hàm tạo sự kiện [ONDBLCLICK=GOTO] vào hàm BS_SQL có sử dụng DBKEY hoặc hàm BS_JOIN thì hàm GOTO không đưa mình đến nguồn dữ liệu được ạ anh!
Em đã thử truy xuất dữ liệu như sau:
Truy xuất từ hàm BS_SQL trên cùng một work book: hoạt động tốt
Truy xuất từ hàm BS_JOIN trên cùng một work book: không hoạt động
Truy xuất từ hàm BS_SQL có sử dụng DBKEY: không hoạt động
Liệu có cách nào để truy xuất được dữ liệu từ một work book khác hoặc từ hàm BS_JOIN không anh!
Các câu hỏi của bạn mình đã kiểm tra lại và phát hiện các lý do có thể bạn gặp lỗi. Bạn c;hú ý các vấn đề quy định của A-Tools khi sử dụng sự kiện ON*
Tất cả các sự kiện chạy cùng các hàm mảng của A-Tools (kể cả các hàm người dùng tự tạo bằng thư viện BSFormulaArray) đều cho phép chạy các sự kiện:
1. OnBeforeUpdate
2. OnAfterUpdate
3. OnBeforeExec
4. OnGetValue
5. OnDblClick (phải khai báo trực tiếp trong hàm)
6. OnSelectionChange (phải khai báo trực tiếp trong hàm)
(Các sự kiện từ 1->4 có thể khai báo trực tiếp trong hàm hoặc khai báo trên ô tham chiếu).
- Khi khai báo sự kiện hoặc thuộc tính cho các hàm A-Tools thì luôn có dấu ";" nằm cuối mỗi một thuộc tính. Ví dụ
=BS_SQL("SELECT...", "INSERT=YES; ONDBLCLICK=MyClick")
Khai báo trên bị sai vì thiếu dấu ";". Khai báo đúng như sau:
=BS_SQL("SELECT...", "INSERT=YES; ONDBLCLICK=MyClick;")
- Khi khai báo các sự kiện như "ONDBLCLICK=MyClick;" thì bắt buộc phải khai báo bên trong hàm chứ không được để trong ô bên ngoài rồi tham chiếu.
Cách làm như sau là sai:
Ô A4 chưa khai báo "INSERT=YES; ONDBLCLICK=MyClick;"
=BS_SQL("SELECT...", A4)
Khai báo trên có thuộc tính INSERT vẫn chạy nhưng các sự kiện ONDBLCLICK, ONSELECTIONCHANGE không chạy (quy định của A-Tools phải khai báo trực tiếp 2 sự kiện này)
Sửa lại như sau:
A4 bạn vẫn được phép khai báo các thuộc tính, ngoại trừ tên các sự kiện ONDBLCLICK, ONSELECTIONCHANGE
A4 khai báo: INSERT=YES;"
=BS_SQL("SELECT...", A4 & "ONDBLCLICK=MyClick;")
Lưu ý là tất cả các hàm mảng của A-Tools đều quy định như trên.
- Phiên bản A-Tools tải trên web trước 17h45 ngày 25/7/2019 hàm GOTO(...) được dùng kết hợp với các sự kiện gọi bị lỗi khi cột tìm kiếm là kiểu số (Number). Vậy bạn hãy tải phiên bản cập nhật Add-in A-Tools v9.1.1.3 ngày 25/7/2019 trên website http://bluesofts.net
@Nguyễn Duy Tuân A ơi cho e hỏi là e mua key pro có được mãi mãi ko? với lại khi máy e bị lỗi cài lại win có mất bản quyền pro của A_tools ko? E định mua 01 key dùng cho công việc nhưng đang băn khoăn 2 vấn đề trên @@
