Trích lọc dữ liệu và tạo báo cáo động trong Microsoft Excel - Nguyễn Duy Tuân
Tóm tắt Trích lọc dữ liệu và tạo báo cáo động trong Microsoft Excel - Nguyễn Duy Tuân: ..."SELECT * FROM KHO") Việc đặt tên - NAME đem lại hiệu quả sử dụng rất cao trong các công thức Excel đồng thời đảm bảo cho A-Tools biết đó là khu vực bảng. Với A-Tools, tên của một vùng KHO đƣợc coi nhƣ tên của một bảng (table) trong CSDL Excel. Cách tạo tên cho vùng dữ liệu Ngƣời dùng làm ...et Email: sales@bluesofts.net or tuanktcdcn@yahoo.com Theo cách làm công thức Excel thông thƣờng, muốn lấy tên phải dùng VLOOKUP tìm mã hàng trong DMVLSPHH rồi copy cho tất cả các dòng còn lại. Với Add-in A-Tools chỉ cần kết nối với sổ danh mục nhƣ trên rồi SELECT thông tin cần lấy. Chỉ một ...ue) Trong môi trƣờng lập trình (VBE), viết thủ tục "GetValue" trong một module theo cấu trúc sau Function GetValue(ByVal DataArray, ByVal Row As Integer, ByVal Column As Integer, ByVal Value As Variant) 'Có thể đổi tên hàm 'GetValue = NewValue End Function Ví dụ: Tại cột 2 của mản...
_source. ValueFalse:Là giá trị nếu kết quả của search_condition là sai (False), ValueFalse có thể là tên cột trong table_source. Func: Tên hàm dùng để tính (SUM, COUNT, MIN, MAX, AVG) Ví dụ 42: Lập sổ chi tiết nhập-xuất tồn hàng hóa. Mã hàng nằm ở ô C10. Giá trị tồn đầu ở F13. “Tồn” = Tồn trƣớc + Nhập – Xuất + Tại ô B6 đặt công thức ="SELECT so_ct, ngay_ct, FIELDIF(loai_phieu='N',Slg,0), FIELDIF(loai_phieu='X',Slg,0) , GetOnce(Cell(F13))+data(PrevRow,Col)+data(Row,2)-data(Row,3) AS [Tồn] Add-in A-Tools Trích lọc dữ liệu và làm báo cáo động trong Microsoft Excel Công ty Cổ phần BLUESOFTS| Nguyễn Duy Tuân – Tel: 04.379.17200 Mobil: 0904210337 57/69 Website: www.bluesofts.net Email: sales@bluesofts.net or tuanktcdcn@yahoo.com FROM kho WHERE ma_vlsphh=RANGE(C10)'" + Tại ô B14 (ô đầu tiên của vùng công thức) nhập công thức BS_SQL với chuỗi SQL lấy ở B11, sử dụng tham số chèn dòng, không hiện dòng tiêu đề của bảng kết quả vì dòng 12 đã tự nhập rồi. =bs_sql(B6,"HR=NO;INSERT=YES") + Kết quả Ví dụ 43: Lập sổ tổng hợp nhập-xuất tồn hàng hóa. + Tại ô B6 đặt công thức SELECT DMVLSPHH.MA_VLSPHH,DMVLSPHH.Ten, DMVLSPHH.Dvi, FIELDIF(loai_phieu='N',SLG,0,SUM) as [N], FIELDIF(loai_phieu='X',SLG,0,SUM) as [X], N-X as [Tồn] FROM DMVLSPHH LEFT JOIN KHO ON DMVLSPHH.MA_VLSPHH = KHO.MA_VLSPHH GROUP BY DMVLSPHH.MA_VLSPHH, DMVLSPHH.Ten, DMVLSPHH.Dvi + Kết quả Add-in A-Tools Trích lọc dữ liệu và làm báo cáo động trong Microsoft Excel Công ty Cổ phần BLUESOFTS| Nguyễn Duy Tuân – Tel: 04.379.17200 Mobil: 0904210337 58/69 Website: www.bluesofts.net Email: sales@bluesofts.net or tuanktcdcn@yahoo.com Ta có thể dùng hàm IIF thay cho FIELDIF SELECT DMVLSPHH.MA_VLSPHH, DMVLSPHH.Ten, DMVLSPHH.Dvi, SUM(IIF(loai_phieu='N',SLG,0) as [N], SUM(IIF(loai_phieu='X',SLG,0) as [X], N-X as [Tồn] FROM DMVLSPHH LEFT JOIN KHO ON DMVLSPHH.MA_VLSPHH = KHO.MA_VLSPHH GROUP BY DMVLSPHH.MA_VLSPHH, DMVLSPHH.Ten, DMVLSPHH.Dvi Hàm VTC: Tạo báo các dạng Cross Tab Cấu trúc: VTC(ColumnName,Func,ColumnCalc,[FormatDate]) ColumnName: Tên cột mà các giá trị của nó làm tiêu thức, điều kiện để tính Func: Tên hàm dùng để tính (SUM, COUNT, MIN, MAX, AVG) ColumnCalc:Tên cột đƣợc tính giá trị theo hàm khai báo ở Func, cột này phải là kiểu số. FormatDate: Là định dạng ngày. Có thể bỏ qua tham số này, nếu kiểu của ColumnName không phải là kiểu Date, hoặc là kiểu Date nhƣng dữ liệu đang ở dạng mm/dd/yy. Nếu ColumnName là kiểu Date mà dữ liệu nhập không phải ở dạng mm/dd/yy thì FormatDate phải là mm/dd/yy. Hàm VTC sẽ liệt kê giá trị duy nhất trong cột ColumnName thành các cột. Trong mỗi cột tạo ra đƣợc thống kê giá trị ở cột ColumnCalc theo một trong các hàm SUM, COUNT, MIN, MAX, AVG. Ví dụ 44: Lập sổ tổng hợp số lƣợng hàng xuất cho các khách hàng. Gồm các cột MA_VLSPHH, các cột còn lại là tổng số lƣợng hàng xuất cho từng mã khách hàng. Add-in A-Tools Trích lọc dữ liệu và làm báo cáo động trong Microsoft Excel Công ty Cổ phần BLUESOFTS| Nguyễn Duy Tuân – Tel: 04.379.17200 Mobil: 0904210337 59/69 Website: www.bluesofts.net Email: sales@bluesofts.net or tuanktcdcn@yahoo.com + Tại ô B14 đặt công thức SELECT MA_VLSPHH, VTC(MA_KH, SUM, SLG) FROM KHO WHERE LOAI_PHIEU='X' GROUP BY MA_VLSPHH B15 đặt công thức sử dụng SQL ở B14 =bs_sql($B$14,"INSERT=YES") + Kết quả nhƣ dƣới đây VTC(MA_KH, SUM, SLG) : sẽ liệt kê các mã khách hàng duy nhất thành các cột, trong mỗi cột đƣợc tính tổng số lƣợng mỗi mặt hàng. GROUP BY MA_VLSPHH : A-Tools sẽ thống kê sô liệu và nhóm theo mỗi mặt hàng. Ví dụ 45: Lập sổ tổng hợp số lƣợng hàng xuất cho các khách hàng. Gồm các cột MA_VLSPHHSTT, Mã hàng, Tên hàng, ĐVT, Tổng xuất, Tên các khách hàng với số lƣợng xuất mỗi mặt hàng tƣơng ứng. + Tại ô A1 đặt công thức SELECT RECNO() AS STT,K.MA_VLSPHH AS [Mã], HH.TEN AS [Tên], HH.Dvi AS [ĐVT], SUM(K.SLG) AS [Tổng xuất], VTC(KH.TEN,SUM,K.SLG) FROM KHO K, DMKH KH, DMVLSPHH HH WHERE K.MA_KH=KH.MA_KH AND K.MA_VLSPHH=HH.MA_VLSPHH AND K.LOAI_PHIEU='X' GROUP BY K.MA_VLSPHH, HH.TEN, HH.Dvi A2 đặt công thức sử dụng SQL ở A1 =bs_sql(A1,"INSERT=YES") + Kết quả nhƣ dƣới đây Add-in A-Tools Trích lọc dữ liệu và làm báo cáo động trong Microsoft Excel Công ty Cổ phần BLUESOFTS| Nguyễn Duy Tuân – Tel: 04.379.17200 Mobil: 0904210337 60/69 Website: www.bluesofts.net Email: sales@bluesofts.net or tuanktcdcn@yahoo.com Ví dụ 46: Lập bảng tổng hợp số lƣợng các hàng hoá xuất kho theo từng ngày. Dữ liệu đƣợc lấy từ sổ KHO. + Tại ô A1 đặt công thức SELECT MA_VLSPHH,SUM(SLG) AS [TỔNG XUẤT], VTC(NGAY_CT,SUM,SLG,MM/DD/YY) FROM KHO WHERE LOAI_PHIEU='X' GROUP BY MA_VLSPHH Hàm SQL: Cập nhật nguồn dữ liệu có hàm BS_SQL Cấu trúc: SQL(table_source[, update]) table_source: là tên của bảng(table) hay tên của một bảng đƣợc truy vấn bới hàm BS_SQL. update: Nếu là TRUE bảng table_source sẽ luôn đƣợc cập nhật lại khi công thức BS_SQL gọi nó thực hiện. Là FALSE (ngầm định) hoặc bỏ trống thì hàm BS_SQL chỉ cập nhật bảng table_source nếu nó đang ở trạng thái #N/A (chƣa cập nhật). Bạn nên dùng tham số với giá trị theo ngầm định. Hàm SQL cần sử dụng khi công thức table2 dựa vào nguồn dữ liệu của table1, table1 đƣợc tạo bởi hàm BS_SQL. Add-in A-Tools Trích lọc dữ liệu và làm báo cáo động trong Microsoft Excel Công ty Cổ phần BLUESOFTS| Nguyễn Duy Tuân – Tel: 04.379.17200 Mobil: 0904210337 61/69 Website: www.bluesofts.net Email: sales@bluesofts.net or tuanktcdcn@yahoo.com Ví dụ 47: Cập nhật dữ liệu nguồn trƣớc khi truy vấn dữ liệu. + Giả xử ta lập công thức lấy toàn bộ dữ liệu trong sổ kho của mã hàng HH001, bảng kết quả đƣợc đặt tên là DLHH1. Công thức là: =bs_sql("SELECT * FROM KHO WHERE MA_VLSPHH='HH001' ", "NAME=DLHH1") NAME=DLHH1: A-Tools sẽ tạo vùng dữ liệu A9:K19 với NAME là “DLHH1”. + Cần lấy tất cả dữ liệu từ bảng DLHH1 tạo ra ở trên với phiếu nhập Công thức là: =bs_sql("SELECT * FROM SQL(DLHH1) WHERE LOAI_PHIEU='N' ") Phương pháp tạo báo cáo động trong Excel với hàm BS_SQL Phƣơng pháp làm báo cáo của Excel là phải làm NHIỀU công thức gộp lại rồi copy. Phƣơng thức làm báo của Add-in A-Tools là chỉ cần làm MỘT công thức có hàm BS_SQL tạo ra một báo cáo. Vì một vài lý do khác nữa mà báo cáo tạo bởi hàm BS_SQL trong Add-in A-Tools chạy nhanh và linh hoạt hơn cách làm Excel thông thƣờng. Để học và nắm đƣợc cách làm ta giả thiết cần lập sổ chi tiết vật tƣ hàng hóa. Ví dụ 48: Lập sổ chi tiết vật tƣ hàng hóa. Sổ này sẽ lọc ra các dòng dữ liệu liên quan đến loại phiếu đƣợc chọn (LOAI_PHIEU) và của mã hàng đƣợc chọn (MA_VLSPHH). Mở tập tin có dữ liệu ví dụ “Examble.xls” có bảng dữ liệu nguồn KHO. Việc thiết kế đƣợc thực hiện theo các bƣớc sau: Add-in A-Tools Trích lọc dữ liệu và làm báo cáo động trong Microsoft Excel Công ty Cổ phần BLUESOFTS| Nguyễn Duy Tuân – Tel: 04.379.17200 Mobil: 0904210337 62/69 Website: www.bluesofts.net Email: sales@bluesofts.net or tuanktcdcn@yahoo.com Bước 1: Thiết kế cấu trúc của báo cáo Tạo sheet “CTHH” trong tập tin “Examble.xls” để thiết kế cấu trúc báo cáo theo hình dƣới đây. Tạo mục chọn mã dạng Validation - List Trong báo cáo có 2 mục chọn: + “Loại phiếu” tại ô C4 cần chọn loại phiếu “N”, “X” từ danh sách: + “Mã VLSPHH” tại ô C5 cần chọn từ danh sách: Mục chọn nhƣ trên chứa danh sách các mã hàng hóa, vật tƣ phát sinh trong cột MA_VLSPHH của bảng KHO. Để nạp vào danh sách trên, đầu tiên cần tạo sheet mới (nếu chƣa có) và đặt tên “Mã duy nhất” trong file “Examble.xls”. Trong sheet này lập công thức BS_SQL để lấy ra danh sách mã hàng hóa, vật tƣ duy nhất, vùng kết quả đƣợc đặt tên là “MAHH” Công thức: =bs_sql("SELECT DISTINCT MA_VLSPHH FROM KHO", "HR=NO; NAME=MAHH") Add-in A-Tools Trích lọc dữ liệu và làm báo cáo động trong Microsoft Excel Công ty Cổ phần BLUESOFTS| Nguyễn Duy Tuân – Tel: 04.379.17200 Mobil: 0904210337 63/69 Website: www.bluesofts.net Email: sales@bluesofts.net or tuanktcdcn@yahoo.com Từ khóa DISTINCT để lấy danh sách duy nhất NAME=MAHH để A-Tools tạo tên (Define Name) vùng dữ liệu kết quả là MAHH. Quay về sheet “CTHH”, chọn ô C5 để tạo Validation – List. Hãy thực hiện đúng 6 bƣớc dƣới đây: Chọn ô C5->Chọn menu “Data”->Chọn “Data Validation”->Allow chọn “List”- >Source chọn “=MAHH”->Hoàn thành nhất [Ok]. Yêu cầu với khu vực chứa dữ liệu của báo cáo + Dòng tiêu đề nên nhập tay để thể hiện nội dung cụ thể. + Từ dòng thứ 2 của khu vực này (theo ví dụ này trong bảng tính Excel từ dòng 8) sẽ đặt công thức BS_SQL. Khu vực này phải để thừa ra ít nhất 2 dòng trống, nếu có định dạng dòng dữ liệu thì nên để 3 dòng trống. + Dòng cuối cùng của vùng dữ liệu, sau 2 hoặc 3 dòng trống đặt công thức Excel để tính tổng các cột dữ liệu. Theo ví dụ trên là dòng 10. Add-in A-Tools Trích lọc dữ liệu và làm báo cáo động trong Microsoft Excel Công ty Cổ phần BLUESOFTS| Nguyễn Duy Tuân – Tel: 04.379.17200 Mobil: 0904210337 64/69 Website: www.bluesofts.net Email: sales@bluesofts.net or tuanktcdcn@yahoo.com Bước 2: Lập công thức với hàm BS_SQL để lấy dữ liệu vào báo cáo Cấu trúc của hàm BS_SQL(SQL [,OPTIONS]). Nếu chuỗi khai báo SQL mà ngắn thì ta có thể đặt trực tiếp trong hàm. Nếu nó dài thì nên để nó nằm ở một ô khác ví dụ B6. Tại công thức BS_SQL thì đặt công thức và sử dụng B6 làm tham số. =BS_SQL(B6). Làm nhƣ vậy Excel sẽ không báo lỗi khi chuỗi tham số SQL quá dài (>128 ký tự). + Đặt công thức có chuỗi khai báo SQL tại ô B6 ="SELECT SO_CT, NGAY_CT, SLG, DON_GIA, THANH_TIEN FROM KHO WHERE LOAI_PHIEU = RANGE(C4) AND MA_VLSPHH =RANGE(C5)" Dùng hàm RANGE(C4), RANGE(C5) để khi thay đổi giá trị tại ô C4, C5 thì công thức đƣợc cập nhật lại. + Đặt công thức BS_SQL tại ô B8 =bs_sql(B6,"HR=NO;INSERT=YES") Giá trị chuỗi SQL ở ô B6 đƣợc đƣa vào tham số đầu tiên hàm BS_SQL HR=NO để bảng kết quả không có dòng tiêu đề. Vì dòng tiêu đề ta tự nhập tay. INSERT=YES để các dòng kết quả tự động co giãn khi thay đổi loại phiếu, mã hàng ở các ô C4, C5. Sau khi nhập xong công thức nhấn ENTER, dữ liệu lập tức trả về và điền vào cả bảng báo cáo. Dòng cuối tự tính tổng các cột SQL, THANH_TIEN. Nếu muốn sửa công thức phải đảm bảo các việc: Chọn đúng một ô trong vùng công -> sửa công thức-> Nhấn CTRL+SHIFT+ENTER để chạy vì đây là công thức mảng. Nếu không muốn thực hiện nhấn phím ESC để hủy việc sửa. Add-in A-Tools Trích lọc dữ liệu và làm báo cáo động trong Microsoft Excel Công ty Cổ phần BLUESOFTS| Nguyễn Duy Tuân – Tel: 04.379.17200 Mobil: 0904210337 65/69 Website: www.bluesofts.net Email: sales@bluesofts.net or tuanktcdcn@yahoo.com Video hướng dẫn làm báo cáo nhanh với SQL Builder trong Add-in A-Tools Các hàm CSDL: Tìm kiếm và thống kê có nhiều điều kiện Add-in A-Tools cung cấp bộ hàm tìm kiếm và thống kê có nhiều điều kiện, nguồn dữ liệu có thể lấy từ trong chính tập tin Excel hoặc từ các nguồn CSDL bên ngoài nhƣ hàm BS_SQL. Các hàm là: BS_DVLOOKUP, BS_DSUM, BS_DCOUNT, BS_DMIN, BS_DMAX, BS_DAVG, BS_DVAR. Để học tốt các hàm này bạn hãy mở tập tin sau để học và làm theo C:\A-Tools\HELP & DEMOS\Bai 3 Hàm CSDL (Database Functions).xls Hàm BS_DVLOOKUP: Tìm kiếm nhiều điều kiện Cấu trúc: BS_DLOOKUP(expr, table_source, search_condition[, options]) Hàm BS_DLOOKUP làm một hàm mảng. Hàm tìm kiếm theo nhiều điều kiện theo search_condition, dữ liệu đƣợc tìm có thể trong một hay nhiều bảng. Giá trị trả về có thể 1 hoặc cả mảng giá trị. expr: Biểu thức trả về (thƣờng là một cột/field trong table_source) nếu việc tìm kiếm thoả mãn điều kiện search_condition table_source: Một hay nhiều bảng dữ liệu nguồn (có quan hệ) chứa dữ liệu cần tìm và trả về, nó có thể là một Name trỏ tới một vùng dữ liệu. Nếu dữ liệu cần Add-in A-Tools Trích lọc dữ liệu và làm báo cáo động trong Microsoft Excel Công ty Cổ phần BLUESOFTS| Nguyễn Duy Tuân – Tel: 04.379.17200 Mobil: 0904210337 66/69 Website: www.bluesofts.net Email: sales@bluesofts.net or tuanktcdcn@yahoo.com tìm hay trả về có từ nhiều bảng thì table_source là nguồn dữ liệu đƣợc kết hợp từ nhiều table, cách kết hợp chúng giống nhƣ trong cú cú pháp sau từ khoá FROM của ngôn ngữ T-SQL. search_condition: Điều kiện để tìm kiếm, cách viết điều kiện giống nhƣ với cú pháp SQL sau từ khoá WHERE của ngôn ngữ T-SQL. Nếu table_source đƣợc kết hợp từ nhiều bảng/table thì search_condition phải chỉ ra biểu thức quan hệ. options: Tuỳ chọn cho hàm, cách dùng tham số này giống nhƣ trong hàm BS_SQL, các nhóm hàm này (BS_D*) thì chỉ sử dụng các khai báo tham số sau: + DBKEY = Mã DBKEY Mã DB KEY đƣợc tạo để kết nối với một CSDL bên ngoài. Xem hƣớng dẫn tạo DBKEY. + HAVING = search_condition search_condition là điều kiện có sử dụng các hàm thống kê nhƣ SUM, COUNT, AVG, MIN, MAX + GROUPBY = group_by_expression group_by_expression là danh sách các cột (field) cần nhóm dữ liệu. + ORDERBY = order_expression order_expression là danh sách các cột (field) cần đƣợc sắp xếp. Cần dùng với hàm BS_SQL, BS_TABLE, BS_DVLOOKUP + SERVERSOURCE = YES/NO Nếu SERVERSOURCE = YES thì dữ liệu đƣợc lấy từ máy chủ, NO hoặc không khai báo thì dữ liệu lấy tại máy đang chạy. Cách khai báo HAVING, GROUPBY, ORDERBY tƣơng tự nhƣ trong cấu trúc câu lệnh SQL sau các từ khoá HAVING, GROUP BY, ORDER BY Các tham số trong ngoặc vuông ([ ]) có thể bỏ qua. Các hàm BS_DSUM, BS_DCOUNT, BS_DMIN, BS_DMAX, BS_DAVG cũng có cấu trúc và cách dùng tƣơng tự hàm BS_DLOOKUP Ví dụ 49: Tìm một mã hàng mà trong bảng KHO có số lƣợng nhập kho là 10. Hƣớng giải quyết: chỉ cần tìm trên một sổ KHO vì tại đây có các thông tin để tìm và dữ liệu trả về. Công thức: =BS_DLOOKUP("TOP 1 MA_VLSPHH","KHO","LOAI_PHIEU = 'N' AND SLG = 10") TOP 1: để lấy một giá trị đầu tiên Ví dụ 50: Lấy ra các mã hàng có số lƣợng nhập > 5 =BS_DLOOKUP("MA_VLSPHH","KHO","LOAI_PHIEU = 'N' AND SLG > 5") Kết quả trả về một mảng giá trị. Muốn sửa lại công thức nhấn CTRL+SHIFT+ENTER Vẫn yêu cầu ví dụ trên bây giờ cần lấy ra tên hàng. Công thức là =BS_DLOOKUP("DMVLSPHH.TEN", "KHO INNER JOIN DMVLSPHH ON KHO.MA_VLSPHH = DMVLSPHH.MA_VLSPHH", "LOAI_PHIEU = 'N' AND SLG >5") Add-in A-Tools Trích lọc dữ liệu và làm báo cáo động trong Microsoft Excel Công ty Cổ phần BLUESOFTS| Nguyễn Duy Tuân – Tel: 04.379.17200 Mobil: 0904210337 67/69 Website: www.bluesofts.net Email: sales@bluesofts.net or tuanktcdcn@yahoo.com Nguồn dữ liệu table_source là hai bảng KHO và DMVLSPHH có quan hệ “KHO INNER JOIN DMVLSPHH ON KHO.MA_VLSPHH = DMVLSPHH.MA_VLSPHH” Vẫn theo ví dụ trên nhƣng dữ liệu tìm và lấy ở tập tin Access “Examble.mdb” và đã kết nối tới DBKEY là “MDB. Công thức nhƣ sau. =BS_DLOOKUP("DMVLSPHH.TEN", "KHO INNER JOIN DMVLSPHH ON KHO.MA_VLSPHH = DMVLSPHH.MA_VLSPHH", "LOAI_PHIEU = 'N' AND SLG >5", “DBKEY=MDB”) Hàm BS_DSUM: Tính tổng với nhiều điều kiện Cấu trúc: BS_DSUM(expr, table_source , [search_condition] , [options]) Hàm BS_DSUM tính tổng theo nhiều điều kiện, dữ liệu làm điều kiện có thể từ một hoặc nhiều bảngtrong CSDL. Cách thức dùng giống nhƣ hàm BS_DVLOOKUP. Ví dụ 51: Tính tổng số lƣợng nhập kho mà mã hàng là HH001 Công thức: =BS_DSUM("SLG","KHO","LOAI_PHIEU='N' AND MA_VLSPHH='HH001'") Hàm BS_DCOUNT: Đếm giá trị với nhiều điều kiện Hàm này có cấu trúc và cách dùng tƣơng tự hàm BS_DSUM Hàm BS_DMIN: Tính giá trị nhỏ nhất với nhiều điều kiện Hàm này có cấu trúc và cách dùng tƣơng tự hàm BS_DSUM Hàm BS_DMAX: Tính giá trị lớn nhất với nhiều điều kiện Hàm này có cấu trúc và cách dùng tƣơng tự hàm BS_DSUM Hàm BS_DAVG: Tính trung bình với nhiều điều kiện Hàm này có cấu trúc và cách dùng tƣơng tự hàm BS_DSUM Hàm BS_DVAR: Tính độ lệch chuẩn với nhiều điều kiện Hàm này có cấu trúc và cách dùng tƣơng tự hàm BS_DSUM Hàm BS_DFUNC: Tính nhiều điều kiện theo tham số là tên hàm Cấu trúc: BS_DFUNC(FuncName, expr, table_source, [search_condition], [options]) FuncName: Là một chuỗi chỉ ra tên hàm dùng để tính, nhận một trong các tên hàm SUM, COUNT, AVG, MIN, MAX, VAR Ví dụ 52: Tính tổng số lƣợng nhập kho mà mã hàng là HH001 Công thức: =BS_DSUM("SLG","KHO","LOAI_PHIEU='N' AND MA_VLSPHH='HH001'") Hoặc: =BS_DFUNC(“SUM”,"SLG","KHO" , "LOAI_PHIEU='N' AND MA_VLSPHH='HH001' ") Hàm BS_TABLE: Trích lọc dữ liệu - Cách làm khác của BS_SQL Cấu trúc: BS_TABLE(select_list, table_source , [search_condition] , [options]) Hàm BS_TABLE là hàm mảng, ý nghĩa tƣơng tự nhƣ hàm BS_SQL, các tham số của hàm BS_TABLE tƣơng ứng với các thành phần của khai báo SQL nhƣ: SELECT, FROM, WHERE,... Add-in A-Tools Trích lọc dữ liệu và làm báo cáo động trong Microsoft Excel Công ty Cổ phần BLUESOFTS| Nguyễn Duy Tuân – Tel: 04.379.17200 Mobil: 0904210337 68/69 Website: www.bluesofts.net Email: sales@bluesofts.net or tuanktcdcn@yahoo.com Các tham số trong ngoặc vuông ([ ]) có thể bỏ qua. select_list: là danh sách các cột đƣợc trả về trong bảng kết quả. select_list chính là khai báo sau từ khoá SELECT của câu lệnh SQL. table_source: Bảng dữ liệu nguồn chứa các thông tin cần trích lọc, nó có thể là một Name trỏ tới một vùng dữ liệu. Tham số này chính là khai báo sau từ khoá FROM của câu lệnh SQL. search_condition: điều kiện để trích lọc. Tham số này chính là khai báo sau từ khoá WHERE của câu lệnh SQL. options: Cách dùng tƣơng tự nhƣ hàm BS_SQL. Sử dụng hàm BS_SQL hay hàm BS_TABLE đều cho ra một kết quả. Hàm BS_TABLE phù hợp cho bài toán cũng nhƣ cách dùng đơn giản. Ví dụ 53: Lập một danh sách gồm các cột: MA_VLSPHH, SLG từ sổ KHO mà loại phiếu là loại nhập (LOAI_PHIEU='N'). Các cách làm với BS_SQL: =BS_SQL("SELECT MA_VLSPHH, SLG FROM KHO WHERE LOAI_PHIEU = 'N' ") Các cách làm với BS_TABLE: = BS_TABLE("MA_VLSPHH, SLG" , "KHO" , "LOAI_PHIEU = 'N' ") Ví dụ 54: Lập một danh sách tổng hợp SLG nhập gồm các cột: MA_VLSPHH, SLG từ sổ KHO mà loại phiếu là loại nhập (LOAI_PHIEU='N'), sắp xếp cột SLG tăng dần. Các cách làm với BS_SQL: =BS_SQL("SELECT MA_VLSPHH, SUM(SLG) FROM KHO WHERE LOAI_PHIEU = 'N' GROUP BY MA_VLSPHH ORDER BY 2 ") Các cách làm với BS_TABLE: =BS_TABLE("MA_VLSPHH, SUM(SLG)" , "KHO" , "LOAI_PHIEU = 'N' ", "GROUPBY = MA_VLSPHH; ORDERBY = 2 ") GROUPBY , ORDERBY là viết liền (không có dấu cách), cách viết không giống từ khóa trong select GROUP BY, ORDER BY (có dấu cách). Add-in A-Tools Trích lọc dữ liệu và làm báo cáo động trong Microsoft Excel Công ty Cổ phần BLUESOFTS| Nguyễn Duy Tuân – Tel: 04.379.17200 Mobil: 0904210337 69/69 Website: www.bluesofts.net Email: sales@bluesofts.net or tuanktcdcn@yahoo.com Kết quả là: Chức năng trích lọc dữ liệu và làm báo cáo động trong Excel của Add-in A- Tools rất mạnh. Nó thực hiện trích lọc với tốc độ nhanh, chính xác, móc nối dữ liệu giữa các bảng với nhau, kết nối nhiều CSDL, cho phép liên kết từ máy tính này sang máy tính khác (mô hình client-server). Bạn học hết tất cả các hàm trên sẽ làm chủ đƣợc trong công việc xử lý dữ liệu Excel của mình. Trong bộ cài Add-in A-Tools cung cấp nhiều tập tin Excel có các ví dụ về lập bạn hãy mở và làm theo sẽ rõ hơn. Thƣ mục lƣu dữ liệu ví dụ: C:\A-Tools\HELP & DEMOS\ Toàn bộ dữ liệu và các ví dụ trong tài liệu này sử dụng bốn tập tin dƣới đây: Bai 1 - Huong dan su dung ham BS_SQL Bai 2 - Cac ham ho tro nhom ham Database Functions.xls Bai 3 Hàm CSDL (Database Functions).xls Bai 4 - Huong dan tao bao cao.xls Tệp “SoKeToanSQL.xls” chứa hệ thống các sổ kế toán đƣợc thiết kế bằng hàm BS_SQL của A-Tools chạy rất nhanh và linh hoạt đây là ví dụ tốt để bạn học và làm các sổ sách cho mình một cách chuyên nghiệp. Thông tin chi tiết mới nhất về Add-in A-Tools mời các bạn xem tại đây: Xem video hướng dẫn Add-in A-Tools Hƣớng dẫn kết nối dữ liệu Excel qua mạng: Tools_Network/A-Tools_Network.html Xử lý lỗi khi Add-in A-Tools không khởi động cùng Microsoft Excel
File đính kèm:
- trich_loc_du_lieu_va_tao_bao_cao_dong_trong_microsoft_excel.pdf