PowerQuery系列（13）-⾃定义函数
本篇博客介绍 Power Query ⾃定义函数的技巧，在 PQ 中计算个税。
以⼯资类所得应交个税为例，最新的个税起征点为 5000 并按下表的级次进⾏缴税（假设没有其它扣除项）。
对照税率表，我们先看看⼿⼯如何计算。⽐如 xxx 的薪资为 8900，没有其它扣除，那么应缴纳的个税为：
1: 应纳税所得额 = 8900 - 5000 = 3900
2： 3900 对应级数为 1，应纳税额 = 3900 * 10% - 210 = 180
接下来介绍在 PQ 中如何通过⾃定义函数来计算。有下⾯两个表：税率表（TaxRate）he员⼯薪资表（salaries），要计算张三和李四应交个税⾦额。假设我们⽤⾃定义函数的⽅式。本⽂将给出三种⽅法。
将 Excel ⼯作表中的两个数据源通过 Ctrl + T 转换成表，加载到 PQ 中。
函数的语法
PQ 函数的语法⽰例：
黑上(x, y) => x + y
⽅法1：在⾼级编辑器中⼿写代码。新建⼀个空查询，改名为 GetIncomeTax，进⼊⾼级查询，在⾼级
查询中输⼊下⾯的代码：
(taxable) =>
if taxable <=0 then 0
else if taxable <= 3000 then taxable * 0.03
else if taxable <= 12000 then taxable * 0.1 - 210
else if taxable <= 25000 then taxable * 0.2 - 1410
else if taxable <= 35000 then taxable * 0.25 - 2660
else if taxable <= 55000 then taxable * 0.3 - 4410
else if taxable <= 80000 then taxable *0.35 - 7160
else taxable * 0.45 - 15160
点击完成按钮，回到查询编辑器界⾯，这个函数就完成了。以上代码跟其他编程语⾔差不多，思路⽐较直观。可以通过上⾯代码熟悉 M语⾔if then else 的语法。
函数调⽤
选中 salaries 查询，右键，点击【复制】，将 salaries 查询表复制⼀个名为 IncomeTaxMethod1 的新查询，添加⼀个⾃定义列， 计算应纳税所得额 (⽤ Taxable 表⽰）：
再增加⼀个计算列，调⽤⾃定义函数 GetIncomeTax 计算个税 ：
点击完成按钮回到查询编辑器界⾯，第⼀种⽅法定义函数和调⽤函数完成。
对应的 M 语⾔脚本：
let
源 = Excel.CurrentWorkbook(){[Name="salaries"]}[Content],
ChangedTypes = Table.TransformColumnTypes(源,{{"Name", type text}, {"Salary", Int64.Type}}),
AddedTaxableCol = Table.AddColumn(
ChangedTypes,我爱你 韩语
"Taxable",
each [Salary] - 5000),
Result = Table.AddColumn(
初三英语教学计划AddedTaxableCol, "IncomeTax", each GetIncomeTax([Taxable]))
in
Result
查询转函数
⽅法2：⽅法 2 的个税计算⽅法来⾃于我见过的 Excel 中最简洁的计算公式：
=ROUND(MAX((B4-5000)*{3;10;20;25;30;35;45}%-{0;210;1410;2660;4410;7160;15160},0),2)
公式中只有⼀个参数，但这个公式有点晦涩，先解释⼀下。这个公式⽤到了 Excel 的数组。第⼀个数组是税率， 为⽅便表述称为
taxrate_array，第⼆个数组是速算扣除数，为表述⽅便称为 deduction_array:
taxrate_array: {3;10;20;25;30;35;45}
deduction_array: {0;210;1410;2660;4410;7160;15160}
所以，天枰座女生和什么星座配
(D2-5000)*{3;10;20;25;30;35;45}%-{0;210;1410;2660;4410;7160;15160}
表⽰是将 (salary-5000）后的值 （taxable）与 taxrate_array 数组的每⼀个元素进⾏计算，再减去 deduction_array 数组的对应值，结果组成⼀个新数组。然后再从这个结果数组中取最⼤值，四舍五⼊保留两位⼩数。⽤伪代码表述更加清晰：
taxable = salary - 5000
高圆圆简历个人资料简介incomeTax = {taxable*0.03-0; taxable*0.1-210; taxable*0.2-1410; ...}
incomeTax = Max({incomeTax})
IncomeTax = Round(IncomeTax, 2)
这个算法是怎么想出来的我们不去管它，主要讲解在 PQ 中⽤同样算法实现的步骤。
选中 TaxrateTable 查询，右键，点击【引⽤】菜单，得到⼀个新的查询，将查询改名为 GetTax。后⾯基于这个查询编写计算个税的函数。
根据刚才的 Excel 公式，上图中 Level 字段、TaxableFrom 字段和 TaxableTo 字段是⽆关字段，可以删除。可以⽤
Table.RemoveColumn 函数删除，也可以使⽤ Table.SelectColumns 函数保留需要的列。本次使⽤Table.SelectColumns 函数。在⾼级编辑器或公式栏中操作都可以：
SelectedCols = Table.SelectColumns(Source, {"Rate", "Deduction"})
这样选择了相关的两列，查询编辑器界⾯如下：
函数需要参数，⽐如计算个税需要应纳税所得额。为了⽅便，我先⽤⼀个特定值，后⾯再替换。进⼊⾼级编辑器，⽬前的代码如下：
let
Source = TaxRatesTable,
SelectedCols = Table.SelectColumns(Source, {"Rate", "Deduction"})
in
SelectedCols
⽐如⽤刚才的应纳税所得额 3900，将代码变更为：
let
taxable = 3900,
Source = TaxRatesTable,
SelectedCols = Table.SelectColumns(Source, {"Rate", "Deduction"})
in
SelectedCols
注意下图我的变更是插⼊了⼀个变量：
然后增加⼀个⾃定义列 Trial (表⽰试算）：
节约用水的故事此时查询编辑器界⾯如下：
对应的 M 语⾔代码如下：
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论