excel表格_excel函数公式大全_execl从入门到精通
你辛辛苦苦做出的Excel数据模型同事不会用？参数表格可以拯救你2024-03-05 14:53:00
需求场景
假设你用微软的Excel做了一个牛叉到没朋友的报告模板，有各种复杂的计算。这个模板使用的N个原始文件是从某个系统导出的。如果这个模板只供你一个人使用，毫无问题；当你需要把这个模板提交给其他伙伴使用时，他们就需要自己从系统中导出N个原始文件然后修改模板的PowerQuery代码，这将是一场你无法想象的灾难。怎么避免这样的灾难发生？
避免灾难的方法是：尽可能将模板代码封装，只让其他伙伴导出系统文件放到某个特定文件夹，然后把模板文件丢到原始文件所在的文件夹。然后打开模板文件刷新即可。
这就是参数表格发挥作用的地方。
不知道“Parameter Table”确切翻译是什么，我直译为“参数表格”。参数表格的意思是：PowerQuery的参数可以根据用户输入来实现动态化。
首先在Excel中创建参数表格并进行初始化
这个表格只有两列：参数和值。因为这整个思路是我抄的英文资源的，所以我偷懒照搬英文列名：“Parameter”和“Value”。然后用插入表格或套用表格的方式，将区域转化成表格，记得将表格命名为“Parameters”【图1】。注意左上角的“表名称”。Value列就是需要用户输入的地方。
现在进行参数表格初始化：由于需要用的数据源有多个且放在了一个固定的文件夹内（假设叫"原始数据"），因此，在Parameter列下面添加一个”FolderPath"。Value的值里填入下面的公式：
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)&"原始数据"然后把参数表格引入到PowerQuery
选择“从表格”创建然后新建一个查询，这一步是创建包含参数值的查询，便于后面从这个查询里引用参数值。创建过程比较简单，一路默认和确定即可，不再截图演示。因为是从已经命名的表格创建的，所以查询名自动继承了表格名字“Parameters”。创建引用参数表格值的函数
现在要创建一个函数，因为只有函数才会拥有动态变化的值。直接点击“新建查询-从其他源-空白查询”，创建一个空白查询，点击“高级编辑器”，删除里边的所有代码，把下面代码粘贴进去然后保存，并把该查询命名为“fnGetParameter”。
//括号和等号加右箭头是函数的标志，括号里的是函数的参数，或者叫变量。如果把第一行拿掉，其实就是一个完整的查询。加上第一行就把这个查询封装成为一个函数了。(ParameterName as text) =>let//获取刚才创建的Parameters表格ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],//这一步其实是让参数表格的行可以无限扩充，不再局限于本例中的三行。注意在“in”前的都是一条语句，并且有缩进，我这里偷懒没缩进。整条语句蛮抽象，我自己也还没完全弄懂其内部的运作原理，直接照搬了。ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),Value=if Table.IsEmpty(ParamRow)=truethen nullelse Record.Field(ParamRow{0},"Value")inValue
现在可以创建你牛叉的数据模型了
接下来创建你的数据模型，无需考虑原始文件的引用路径是否硬编码。进行各种复杂的Shaping也好，Calculating也好，然后加载到数据模型，。根据需要添加计算列、计算字段，创建数据透视表、数据透视图乃至PowerView。十八般武艺样样上吧。具体步骤不赘述。
把你模型中原始文件的引用路径全部用引用参数表格函数来代替
报告创建好之后，你同事能够轻松使用你的牛逼报告模板的关键一步来了：将上一步写死的源文件位置，替换成参数表格里的值。类似于下面这样的：
源 = Excel.Workbook(File.Contents(fnGetParameter("FolderPath")), null, true),
其中的FolderPath就是你同事电脑上保存源文件的路径。但不需要你同事自己写路径，模型文件会自动获取，这就是它牛叉的地方。
保存该Excel文件。
你同事不费吹灰之力就可以使用你的牛叉模板
同事拿到这个Excel数据模型文件后，放到ta想放的位置。在同一个文件夹下新建一个叫“原始数据”的空文件夹，把系统中的原始数据文件都丢到这个叫“原始数据”的文件夹中。
然后打开模板文件，点击“刷新”，大概几秒到几分钟就获取到最新的数据了（取决于数据量大小和模型的复杂程度），你模型中的数据透视表、图表及其他各种丰富多彩的可视化结果都自动更新好了。怎么样，心动了吗？要不要立即试试看？
标签: excel模型建立