使用Excel查询ERP数据库数据（Ms Query）

fuyou1355

之前有网友分享了如何使用excel2007的数据连接功能（数据—自其它来源—来自sql server）连接ERP数据库，从而取得需要的数据。但是该功能也有明显不足，即不能设置复杂的查询条件（也可能是本人学艺不精）。今天我将给大家陆续分享一些使用excel数据连接功能（microsoft query）制作自动查询表的案例，在分享的同时也希望能一同探讨我遇见的问题（本人专业财务）！

案例一：

背景：
我司是一家集团型公司（共有十余家子公司），集团使用的是用友U8 10.1，现集团财务总账岗位提出需求，希望能从ERP中快捷查询科目余额表，用于出具各子公司的财务报表（子公司财务较弱）。

分析：

思路一：

总账科目余额表数据存储在ERP数据库GL_Accsum表中，因此可以通过使用Excel导入外部数据的功能取该表数据，但此表要求相关月份的凭证都已审核、记账，否则不能正确取数。且该表本身不包含累计数，需要通过其他方式汇总累计金额。

思路二：

总账科目余额表数据存储在ERP数据库GL_Accsum表，凭证明细数据存储在ERP数据库GL_Accvouch表中，会计科目档案存储在ERP数据库Code表中。

查询GL_Accvouch表时，不管该凭证是否审核、记账都可以正常查询，且正常情况下本月的凭证可能还未审核、未记账，但上月的凭证一般来说都已审核、记账了，因此基于此种情况可以通过取GL_Accsum表中的“期初余额”，汇总GL_Accvouch表中的“本期借方金额”、“本期贷方金额”、“累计借方金额”、“累计贷方金额”，然后将这些数据都查询、汇总到Code表中，最后通过“期初余额+本期增加额—本期减少额=期末余额”的等式及会计科目的性质判断“期末余额方向”和“期末余额”。

第一种思路有一定的局限性，这里不多描述，我采用思路二做了相关附件，其涉及Excel表中的“Microsoft Query”功能（我这里不能登录服务器桌面，只能使用该方式），有关代码如下：

一、凭证明细表——GL_Accsum代码：

SELECT GL_accvouch.i_id AS '凭证ID', GL_accvouch.iyear AS '会计年度', GL_accvouch.iYPeriod AS '会计年月', GL_accvouch.ino_id AS '凭证号', GL_accvouch.cbill AS '制单人', GL_accvouch.ccheck AS '审核人', GL_accvouch.cbook AS '记账人', GL_accvouch.cdigest AS '摘要', GL_accvouch.ccode AS '科目编码', GL_accvouch.cexch_name AS '币种名称', GL_accvouch.md AS '本币借方金额', GL_accvouch.mc AS '本币贷方金额', GL_accvouch.md_f AS '外币借方金额', GL_accvouch.mc_f AS '外币贷方金额', GL_accvouch.nfrat AS '汇率', GL_accvouch.csettle AS '结算方式', GL_accvouch.cn_id AS '票据号', GL_accvouch.cdept_id AS '部门编码', GL_accvouch.cperson_id AS '职员编码', GL_accvouch.ccus_id AS '客户编码', GL_accvouch.csup_id AS '供应商编码', GL_accvouch.citem_id AS '项目编码'

FROM UFDATA_001_2015.dbo.GL_accvouch GL_accvouch

WHERE (GL_accvouch.iyear=?) AND (GL_accvouch.iYPeriod<>201600)

二、科目余额表——GL_Accsum代码：

SELECT DISTINCT GL_accsum.ccode AS '科目代码', code.ccode_name AS '科目名称', GL_accsum.iYPeriod AS '会计年月', GL_accsum.cbegind_c AS '期初金额方向', Sum(isnull(GL_accsum.mb,0)) AS '期初余额', Sum(isnull(GL_accsum.md,0)) AS '借方发生额', Sum(isnull(GL_accsum.mc,0)) AS '贷方发生额', GL_accsum.cendd_c AS '期末金额方向', Sum(isnull(GL_accsum.me,0)) AS '期末余额'

FROM UFDATA_001_2015.dbo.code code, UFDATA_001_2015.dbo.GL_accsum GL_accsum

WHERE (GL_accsum.iYPeriod In ('201601','201602','201603','201604','201605','201606','201607','201608','201609','201610','201611','201612')) AND (code.iyear=?) AND (code.ccode=GL_accsum.ccode)

GROUP BY GL_accsum.ccode, code.ccode_name, GL_accsum.iYPeriod, GL_accsum.cbegind_c, GL_accsum.cendd_c

三、会计科目档案表——Code代码：

SELECT DISTINCT code.ccode AS '科目代码', code.ccode_name AS '科目名', code.bend AS '是否末级', code.igrade AS '科目级次'

FROM UFDATA_001_2015.dbo.code code

WHERE (code.iyear=?)

ORDER BY code.ccode, code.ccode_name

查询完上述信息后，我在Excel中设置相应的查询公式、判断公式，最终组合成附件表格。该表解决了凭证未审核、未记账状态下不登录ERP时查询“会计科目发生额及余额”的需求，方便财务报表的出具！！

fuyou1355

案例二：
为解决财务不能随时掌握“采购未开票”数据，同时减少当期因供应商未及时开票造成的资金压力，我做了一份“采购未开票自动刷新表”，希望对各位的日常工作有所帮助！！

模板作用：
1、        财务、采购能够随时查看ERP中已入库、但未开票的明细数据；
2、        财务、采购能据此掌握ERP中长期挂账（暂估部分）的供应商信息，为定期清理往来账目提供依据；
3、        财务能及时掌握、调节供应商可开票数，减少资金压力；
4、        财务、采购能以此数据为基础，制作各类分析资料；

使用方法：见附件视频。

模板思路：
ERP中的采购入库单列表数据分别存储在“rdrecord 01”表（采购入库单主表）和“rdrecords01” 表（采购入库单子表）中，供应商信息存储在“Vendor”表中；
在“rdrecords01” 表中有“入库数量”和“累计开票数量”，两者之差为“未开票数量”，乘以对应的单价后可以计算出“未开票金额”、“未开票税额”、“未开票价税合计”等数据；
在Excel中查询和计算出需要的数据后，可以通过“动态数据透视表”功能自动汇总相应的信息，完成自动查询、汇总的操作。

相关代码：
SELECT RdRecord01.cBusType AS '业务类型', RdRecord01.cPTCode AS '采购类型编码', RdRecord01.cCode AS '入库单号', RdRecord01.dDate AS '单据日期', RdRecord01.cOrderCode AS '采购订单号', RdRecord01.cSource AS '单据来源', RdRecord01.cARVCode AS '采购到货单号', RdRecord01.dARVDate AS '到货日期', RdRecord01.cDepCode AS '部门编码', RdRecord01.cVenCode AS '供应商编码', Vendor.cVenAbbName AS '供应商简称', RdRecord01.cMemo AS '备注', RdRecord01.cWhCode AS '仓库编码', RdRecord01.cMaker AS '制单人', RdRecord01.cPersonCode AS '业务员编码', RdRecord01.cVenPUOMProtocol AS '收付款协议编码', rdrecords01.cInvCode AS '存货编码', rdrecords01.cBatch AS '批号', rdrecords01.iQuantity AS '数量', rdrecords01.iSumBillQuantity AS '累计开票数量', rdrecords01.iUnitCost AS '本币无税单价', RdRecord01.iExchRate AS '汇率', rdrecords01.iTaxRate AS '税率', rdrecords01.iPrice AS '本币无税金额', rdrecords01.iTaxPrice AS '本币税额', rdrecords01.iSum AS '本币价税合计'
FROM UFDATA_001_2015.dbo.RdRecord01 RdRecord01, UFDATA_001_2015.dbo.rdrecords01 rdrecords01, UFDATA_001_2015.dbo.Vendor Vendor
WHERE rdrecords01.ID = RdRecord01.ID AND Vendor.cVenCode = RdRecord01.cVenCode AND ((RdRecord01.dDate>=?))

fuyou1355

一朵耶菊花 发表于 2016-12-23 14:58
求视频

简化视频：http://pan.baidu.com/s/1boXvL9t，提取码：mgqn
案例视频：http://pan.baidu.com/s/1bptGypL，提取码：gq5c

fuyou1355

以个人的经验来看，只取ERP的数据是不够的，还需要和excel中使用公式、列表等功能结合才能满足实际业务需求！

fuyou1355

zhuyangzhao0602 发表于 2016-12-13 12:46
希望可以发下视频

需要视频可留下qq邮箱！

HNYDB

帖子虽然好长时间了，不过还是想要视频和表格，谢谢！  872342713@qq.com

fuyou1355

可惜论坛不能上传视频，不然可以分享更过的资料给大家！

wu道

挺好的东西

zhuyangzhao0602

希望可以发下视频

open1991

感谢楼主分享

hujvjv

好东西啊谢谢

zhuyangzhao0602

求视频2853229361@qq.com

fuyou1355

zhuyangzhao0602 发表于 2016-12-14 13:56
求视频

已发视频！

zhuyangzhao0602

谢谢！！！！

xxy1314521

好东东呀——谢谢楼主

qsxcdw

新人 一切都在学习中