我有一張桌子：
A B C D E
Identifier Reference DK NO SE
1 DK-NO 5 20 30 40
2 DK-SE 15 20 30 40
3 DK-NO-SE 20 20 30 40
現在，我想要做的是計算列“識別符號”中標識的值與“參考”中的值之間的平均差異，即第一個值是：AVERAGE(C1-B1;D1-B1) = AVERAGE (15;25) = 20, 第二行是 AVERAGE(C2-B2;E2-B2) = AVERAGE(5,15) = 10 第三行 AVERAGE(C3-B3;D3-B3;E3-B3;) = AVERAGE( 0;10;20) = 10，依此類推。
最好是可用于電源查詢的解決方案。
uj5u.com熱心網友回復：
以下是使用 Power Query 執行此操作的方法。
您使用 List.Accumulate 來收集相關值；然后將它們平均并減去參考值。
請閱讀代碼注釋并遵循應用步驟
let
Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
//set the data types
colTypes = List.Zip({Table.ColumnNames(Source), {Text.Type} & List.Repeat({Int64.Type},Table.ColumnCount(Source)-1)}),
#"Changed Type" = Table.TransformColumnTypes(Source, colTypes),
//add Index column to identify the relevant (row)
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
//calculate the averages using List.Accumulate to gather the factors
#"Added Custom" = Table.AddColumn(#"Added Index", "Average Diffs", each let
cols = Text.Split([Identifier],"-"),
vals = List.Accumulate(cols,
{},
(state,current)=> state & { Record.Field(#"Added Index"{[Index]},current)})
in
List.Average(vals) - [Reference]),
//Delete the Index Column
result = Table.RemoveColumns(#"Added Custom",{"Index"})
in
result
如果您有 Windows Excel，XMATCH您也可以在添加到同一表格的列中使用此公式：（請注意，您確實需要在公式的某些部分參考表格名稱）
=AVERAGE(INDEX(Table47[@],, XMATCH(FILTERXML("<t><s>" & SUBSTITUTE([@Identifier],"-","</s><s>") & "</s></t>","//s"),Table47[#Headers])))-[@Reference]
uj5u.com熱心網友回復：
您可以使用此公式 =AVERAGE(INDIRECT(ADDRESS(ROW(B3),MATCH(LEFT(B3,2),$2:$2,0)))-C3,INDIRECT(ADDRESS(ROW(B3),MATCH(RIGHT(RIGHT( B3,2),$2:$2,0)))-C3)
uj5u.com熱心網友回復：
您擁有 PowerQuery 解決方案 (@Ron Rosenfeld)。
對于電子表格公式，您可以使用 LET() 創建 1 和 0 的掩碼，并使用它來過濾列并創建平均值
例如對于單元格 F2，這個公式：
=LET(mask,IF(ISERROR(FIND(C$1:E$1,$A2)),0,1),(SUMPRODUCT(mask,C2:E2)/SUM(mask))-$B2)
應該允許您添加額外的列和新的 2 字符代碼。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/382709.html