如何在同一列中找到最接近的值

发布于 2025-02-07 03:37:52 字数 806 浏览 0 评论 0原文

我是使用电源查询的新手，并开始使用PQ重建我在Excel中可以做的任何事情。我想在同一列中找到壁橱值，然后从另一个表返回相应的值。

我希望这是一个视觉效果，可以清楚地表明我想做什么。例如，我有2个表，壁橱值为29为30，因此它将从其他表中返回30的相应值，即C.

在Excel中，我将使用以下任何一种方式来执行任何方法工作。

=INDEX(L172:M174,MATCH(MIN(ABS(L172:L174-M176)),ABS(L172:L174-N171),0),2)

=VLOOKUP(INDEX(L172:L174,MATCH(MIN(ABS(L172:L174-M176)),ABS(L172:L174-N171),0)),L172:M174,2,FALSE)

在PQ中，我只弄清楚如何使用以下功能从其他表中查找值：

#"TABLE 2"[Category]{List.PositionOf(#"TABLE 2"[Value],Number.abs(xxx))}

但是我很难接近返回的最小绝对数量部分。我尝试使用table.transformcolumns或list.transform，但无论哪种方式都会给我带来错误。

提前致谢！

原文

I am a newbie using Power Query, and started using PQ to rebuild whatever I can do in Excel.
I wanted to find the closet value within the same column and then return the corresponding value from the other table.

Here's a visual that I hope it makes clear what I wanted to do. For example, I have 2 tables, the closet value to 29 is 30 so it will return the 30's corresponding value from other table, which is C.

In Excel I will use either way below to do the work.

=INDEX(L172:M174,MATCH(MIN(ABS(L172:L174-M176)),ABS(L172:L174-N171),0),2)

=VLOOKUP(INDEX(L172:L174,MATCH(MIN(ABS(L172:L174-M176)),ABS(L172:L174-N171),0)),L172:M174,2,FALSE)

In PQ, I only figured out how to look up the value from other table using the following function:

#"TABLE 2"[Category]{List.PositionOf(#"TABLE 2"[Value],Number.abs(xxx))}

But I am having a hard time approaching the returning the minimum absolute number part. I tried to use table.transformcolumns or list.transform, but either way gets me errors.

Thanks in advance!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

兮颜 2025-02-14 03:37:52

假设Table1和Table2被加载到PowerQuery中，则其最高部分创建了一个函数，该函数在Table1中找到最接近的值，然后从Table2中拉出行。最后几行使用该功能在输入列FindClosest上找到与每个行的最接近值

let
FC = (TestValue)=> let
// add an index to Table1 so we later can determine matching row
Source = Table.AddIndexColumn(Table1, "Index", 0, 1, Int64.Type),
//subtract the TestValue from each row in Table1, and take absolute number as result    
#"Added Custom" = Table.AddColumn(Source, "Diff", each Number.Abs([Column1]-TestValue)),
//sort results so that first row is closest match, first index in a close tie
#"Sorted Rows" = Table.Sort(#"Added Custom",{{"Diff", Order.Ascending},{"Index", Order.Ascending}}),
// pull the index number from the first row, which will be row we want to grab in Table2
Index = #"Sorted Rows"{0}[Index],
// pull the value from that row number in Table2, from column: Column1
Table2value=Table2{Index}[Column1]
in Table2value,

Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Findclosest", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each FC([Findclosest]))
in #"Added Custom"

src =“ https://i.sstatic.net/4ukzl.jpg” alt =“在此处输入图像说明”>

Assuming Table1 and Table2 are loaded into PowerQuery, the top part of this creates a function that finds the closest value in Table1, then pull row from Table2. The last few rows use that function on the input column Findclosest to find the closest value to each and every row

let
FC = (TestValue)=> let
// add an index to Table1 so we later can determine matching row
Source = Table.AddIndexColumn(Table1, "Index", 0, 1, Int64.Type),
//subtract the TestValue from each row in Table1, and take absolute number as result    
#"Added Custom" = Table.AddColumn(Source, "Diff", each Number.Abs([Column1]-TestValue)),
//sort results so that first row is closest match, first index in a close tie
#"Sorted Rows" = Table.Sort(#"Added Custom",{{"Diff", Order.Ascending},{"Index", Order.Ascending}}),
// pull the index number from the first row, which will be row we want to grab in Table2
Index = #"Sorted Rows"{0}[Index],
// pull the value from that row number in Table2, from column: Column1
Table2value=Table2{Index}[Column1]
in Table2value,

Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Findclosest", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each FC([Findclosest]))
in #"Added Custom"