分类：方法论
- 分析复杂的Power Query实现的Excel文件的逻辑
公司的财务顾问使用Power Query技术制作了几个非常复杂的Excel文件，用于业务数据分析。业务人员知道如何使用这些Excel，可无法读懂其中的逻辑细节，出现问题时也无法解决。考虑到未来这些逻辑都需要我做到系统中，那么这些其实就是第一手的需求资料，于是我决定，主动帮助业务人员分析这些Excel的逻辑。 一、学习 首先了解并学习Power Query是什么，能做什么，有什么特性和限制，并做一些实验和demo来加深理解。 我理解到，Power Query其实是一种表处理语言，从一张表计算出另一张表。非技术人员通过Excel软件中的图形化界面来编排计算过程，但背后其实就是代码（M语言代码）。 二、提取元素和关系 将每个Excel中的主要元素逐个提取出来，包括： Excel文件（工作簿） 工作表（Sheet） 表格（命名单元格区域） 单元格区域（非命名单元格区域） 数据透视表 数据透视表的切片器 Power Query的查询 Power Pivot的数据模型 这些就是要分析的问题对象。我将它们用不同的图形表示出来，画在一张PPT上。 然后分析它们之间的关系，包括： 元素之间的包含关系，例如Excel文件与工作表、查询，工作表与表格、数据透视表。 Excel文件的输入关系，即Excel文件从哪个Excel文件取数。 查询的输入关系，即查询从哪些Excel文件、表格、其他查询取数。 查询的输出关系，即查询的结果被哪个查询取数，或输出到哪个表格，或输出到哪个数据模型。 数据模型的关系，例如数据模型从哪个查询取数、数据模型间的连接关系。 将这些关系用连线或箭头表示出来，同样画在PPT上。 整理PPT中的元素和关系，就得到了整体结构图。 三、理解查询代码 整体上的计算过程是： 输入：从表格或Excel文件读取数据。 计算：Power Query执行计算。 输出：将计算结果输出到表格。 那么接下来要重点分析各Power Query查询的计算过程。 首先将每个查询的代码都提取出来，逐个放到文本文件中。然后逐个阅读这些代码文件，尝试理解，并写注释。 这个过程的主要困难在于，代码是最末端、最具体的执行步骤。一个宏观上的意图，可能会表现为多条代码。限于表处理语言的机制（只能表算表），计算过程的表达形式跟我们常用的编程语言很不一样，很简单的一个操作，Power Query经常不得不写得很绕、很晦涩。此外，步骤都是图形化编排出来的，没有有意义的命名。所以，基本上跟读反编译代码差不多，主要靠推理和猜测。这里我采用自底向上的思路，先用自然语言描述出每一个步骤做了什么，再联系上下文猜测它的宏观意图是什么，最后跟业务人员同步信息确认理解是否正确。 我对每个查询都进行了仔细分析，阅读并注释了每一行代码，画出了数据流转关系图，写出了对宏观意图的理解，写出了对主要计算过程的理解，写出了对计算结果的描述（形式和内容）。 四、报告与讲解 我将分析出来的所有成果，写成了一个报告文档。文档先总述Power Query的基础知识、各个Excel文件之间的关系，然后逐个Excel详细描述分析成果，包括Excel文件内的各结构图、每个查询的分析、每个数据透视表的分析等。 我将报告文档提交给了领导和相关业务同事，然后在领导的要求下，给领导和同事现场清楚地讲解了整个逻辑。 五、价值 后来的事实证明，我的工作是非常有价值的。 我清楚地理解了这里面的每一个细节，成为了最了解这一块业务的人之一（甚至某些方面还要超过业务人员），能参与进跟财务顾问的讨论，能在后续上系统时帮助产品经理纠正理解上的不足和偏差，能在开发时有更高的效率。
- 分析复杂的公式实现的Excel文件的逻辑
背景 公司有一个非常复杂的Excel文件，用于填报数据并做一些基本的分析。这个Excel文件设计得很精巧，充分利用了Excel的各种功能，通过颜色区分、基础数据区域、下拉选项、公式、条件样式等手段，实现了基础数据配置、数据填报、数据校验、统计分析等功能，几乎就是一个独立的业务系统了，设计者一定很厉害。 我们接到的需求是，将这个Excel的功能做上系统，毕竟线下使用不方便，数据也不通。开发时，有一个业务人员跟我们对接（非Excel设计者），我们做到哪一块，就问她这一块有什么逻辑，然后她去看公式，描述给我们。我发现，这样太低效了：① 她没有编程背景，自然语言描述经常不够准确；② 多个有互相联系的块，逐个描述，容易漏掉重要信息；③ 对于特别复杂的公式，她也看不太懂；④ 没有落到纸面，后面不清楚了还得重复问。我觉得，想把这个事情干好干扎实，对Excel中的逻辑进行系统、全面、细致的分析是必不可少的，这个事情，我来做。 分析过程 将每个Sheet导出为PDF，然后导入到我的iPad的GoodNotes软件中。 逐个Sheet，逐个单元格，阅读公式，理解并标注： 对于简单的单元格或公式，直接在单元格旁边手写标注，例如常量还是变量，输入形式是什么，从哪里取数，用伪代码重新表达逻辑等。 对于复杂的公式，先将其复制到VSCode中，加缩进格式化，使之更易读，然后再粘贴到GoodNotes中，一点一点地分析、标注计算过程，理解其意图。 至此，我已经充分理解了Excel中的所有逻辑，并得到了一份详细的分析手稿。限于开发周期，此时完成手稿就结束了。之后所有的逻辑改为由我来描述和检查，就这样，我们完成了第一版的开发。 进行第二版迭代时，我进一步将分析成果编写成了一个形式良好的文档，除了手稿中的内容（有不少变化），还考虑了目前已在系统上实现的功能模块划分、数据结构、填写控制等设计，使用更规范的描述方式（例如字段的多级命名、用颜色区分元素类型、更严谨的伪代码等），对每一个字段的逻辑进行了详细的描述。 至此，我们的软件迭代彻底从Excel文件脱离了出来，后续逻辑的更新只需要在这份文档的基础上迭代即可。 价值 对问题充分的理解，是解决问题的重要基础。我自己去理解了这些，后面做起事情来就踏实、从容很多了。 这也说明，事情没有一定之规，合适的方法要自己去找出来。