一、前言
财务领域的你，是否常常感觉自己深陷Excel的“泥沼”——每月、每周甚至每日，都在重复着从不同系统导出数据、手动复制粘贴、繁琐核对、调整格式的循环？这些操作不仅耗时耗力，更容易在不经意间引入错误。更重要的是，当大量精力被这些低价值的事务性工作占据，我们没有时间去进行真正有价值的数据分析与业务洞察！
二、目标
今天我们将使用Excel内置功能构建一套近乎全自动化的智能台账管理系统；读完本文，你将收获的不仅仅是几个Excel技巧，更是一套全新的数据处理思维和工作方法，让你能够从数据获取、数据清洗、关联数据、数据呈现的数据全生命周期来构建出一套高效的数据处理方案，从而把精力从事务性工作中解放出来！
- 高效获取多源数据： 从企业微信共享表格，金蝶云星空、OA系统等各类业务系统获取所需数据。
- 实现数据自动化清洗与转换： 彻底告别繁琐的手动整理。
- 构建动态、关联的数据模型： 轻松整合分析来自不同模块的数据。
- 制作交互式、可视化的台账报告： 让数据自己“说话”，辅助决策。
三、方案
1、数据获取—手工数据获取
手工数据收集过程中最重要的是权限控制，我们经常有一个需求：就是做一个大的Execel工作簿，分很多sheet表，每个人填写每个表的数据；但是我们需要各自只看见自己的表格，不能看别人的数据，然后如果是每个周每月都要填写这个表的数据，我们希望前面已经添加的数据填写人不能修改，只能添后面行or列的数据！
标准的微软Excel共享无法实现这个功能；但是企业微信的共享表格可以，为什么要用企微，因为企业微信在控制权限的同时，方便分发给各个联系人，如果你是用企业微信办公的，会非常的方便！
效果图如下：通过选择文档，新建表格，然后某张sheet表中数据只能被小张查看，注意小张sheet表前面有一个锁；
具体操作如下：
1.右键新建表格-选择保护工作表-选择其他成员不可查看，同时点击“指定成员单独设置权限”，这里设置谁可以查看此表格；
2.上述设置只能实现：“每个人只编辑和查看自己的sheet表”，如果你是需要设置“能编辑这个表的人”需要让他无法编辑上月or历史已经提交的数据，只能查看那如何设置呢，需要做三步：第一步：找到菜单栏的保护按钮点击，如下图：第二步是选择所选范围（A1:C12）进行设置为可查看，第三步是选择所选范围（D1:D12）进行设置为可编辑，如下图：设置完后，选择的人员可对A1:C12数据查看不能编辑，同时可对D1:D12数据编辑；当然没有在保护范围的其他数据，比如：E列及后、13行及后续行是所有人都可以编辑和查看的；所以我们设置时一般设置为A1:C200为可查看，D1:Z200可编辑。
获取数据完成后，右上角有个按键三杠可以导出xlsx文件，可以通过本地的Excel打开编辑及后续使用！
2、数据获取—系统(金蝶云星空)数据获取
金蝶云星空的数据获取用M语言获取，也就是通过空白的查询中编写M语言代码：以下代码的通用性是可以取金蝶云星空的所有单据内容数据，不是报表数据，报表数据的API接口是另一个；代码的内容不在此讲解；你复制代码（下方下载）放入powerQurey的高级编辑器中，然后在图形页面设置几个参数，当然最简单的方法是你也可以下载我的Excel文件填写如下图的相关参数，获取你也可以到Power Query查询页面中直接copy查询和参数的文件夹到你自己新建的excel中使用！
下载案例后，打开power query查询页面，输入相关参数，如下图框红的部分：
3、数据清洗
数据清洗是比较简单的，也是比较自定义的，在Power Query查询页面的菜单中，你可以尝试任何操作来规范你的数据，比如：重名称列，删除行，自定义新增列等等这些操作的过程都会被记忆，下次数据还会按照这个规则继续进行清洗，每一个清洗的步骤都会记录在右侧菜单栏中，你可以清楚的看到你做了哪些步骤的清洗过程；你也可以直接定位到具体的那一个清洗步骤，数据就会呈现那个特定步骤时的相关数据；总之清洗可以按步骤被记录；
4、关联数据
当你的数据清洗完成后，左上有一个关闭并上载的按钮，注意你需要点击下面的三角，选择关闭并上载至，然后会有一个对话框出现，选择表or仅创建链接，将此数据添加到数据模型勾选上；加入数据模型就是为了取做数据关联；
加入数据模型后，在Excel中需要打开power povit的菜单显示栏（1.通过开发工具菜单-com加载项或者2.文件-选项-加载项-下方管理选择com加载项打开power povit的菜单栏），然后将表和表直接用线连接起来，这个连接线的两段是代表关联的字段；同时至少有一方是唯一值，这样构建出一个“一对多”的关系；如下图：
5、数据呈现
连接表和表之间联系就像vlookup建立了一个表和表的索引关系一样，它是永久存在的；此时，我们需要建立一些“度量值”的东西来做数据呈现，“度量值”不很好理解，你暂时可以理解为你需要呈现的数据的列；比如你需要新建一个求和的金额，那么就建立一个金额的度量值；如图，在下方建立度量值：
最后，在power povit的编辑页面的主页，找到创建数据透视图or数据表来构建你需要呈现的数据，在选择数据时可以选择你建立的度量值；数据透视表就可以刷新了，刷新数据是直接从金蝶系统来获取数据的；最后，以上整个Excel自动化台账管理方案的大概纲要就结束了，过程中可能有很多自定义的步骤没有详细解决，我这里提供的是一个可以用于解决Excel中数据获取，清洗，管理，呈现以及后续持续更新的一个方案；这个方案中只用到了企业微信+微软的Excel，其优点是不设计其他复杂工具；微软Excel的安装有问题可以用office tool工具解决，不懂的可以扣我！
四、总结
构建以上方案就像修一条自来水管道一样，你想一下如果你家里没有自来水，需要每天去打水，会有多麻烦，虽然每天只用半小时，但是每天都要去（就想某些工作一样每天都要做）；而建立一个自来水管道需要花费1天时间，但是后续你只要打开水龙头就有水了；这个方案的就想构建一个自来水管道一样，数据就是水；构建好了刷新马上就可以在Excel中看到了；
最后，整个步骤是：企业微信可以收集手工数据，Excel的可以收集系统数据（金蝶，OA，网页等等）；最后手工数据导入Excel中，数据进行清洗，清洗后的数据与数据构建关联性（连线），最后，创建需要呈现的数据度量值，通过数据透视表or图呈现你需要的数据；后续数据透视表直接刷新就可以实现数据的滚动更新；其实这里每一个点展开来讲都很复杂，我这只用了一个简单案例讲解各功能，告诉大家有一条这样的路，把最主要的关键路“连接金蝶系统”给大家打通了，供大家参考下载使用；至于其他功能没有展开讲解。大家有问题可以下方留言，看到我会回复！如果你觉得这篇文章对你有帮助，欢迎分享给更多有需要的财务同行！
发表回复