匹配多个符合相同条件的结果。

huangapple go评论41阅读模式
英文:

Match several results with same conditions

问题

The aim of my workbook is to show result from a spreadsheet, after applying filters.

在我的工作簿中,目的是在应用筛选后显示电子表格的结果。

匹配多个符合相同条件的结果。

在这个示例中,
I get my spreadsheet in range A1:C13,

在此示例中,我获取了电子表格的范围A1:C13

in column E, thanks to the Yellow formula,
It shows every Done date by unsorted.

在列E中,由于黄色公式的帮助,
它显示了每个未排序的完成日期。

The problem is that I want to show relative Name in column F. But there is some equals dates for different names,

问题是我想在列F中显示相关的姓名。但是有一些相同日期对应不同的姓名,

My reflex where to do an Index Match, but it can't show when there is several results from the same conditions.

我的第一反应是使用索引匹配,但它无法在存在相同条件的多个结果时显示。

Any ideas ?

有任何想法吗?

英文:

The aim of my workbook is to show result from a spreadsheet, after applying filters.

匹配多个符合相同条件的结果。

In this example,
I get my spreadsheet in range A1:C13,

in column E, thanks to the Yellow formula,
It shows every Done date by unsorted.

The problem is that I want to show relative Name in column F. But there is some equals dates for different names,

My reflex where to do an Index Match, but it can't show when there is several results from the same conditions.

Any ideas ?

答案1

得分: 1

Change your formula to include the Name column in the output:

=将您的公式更改为在输出中包含"Name"列:

=SORT(FILTER(Table2[[Date]:[Name]],Table2[Statut]=E1),1,-1)

如果您的数据不紧邻在一起,且不希望在输出中显示中间列,那么我们可以使用相同的公式包裹在INDEX函数中,以返回数组的正确列:

=如果您的数据不是紧邻在一起,且不希望在输出中显示中间列,那么我们可以使用相同的公式包裹在INDEX函数中,以返回数组的正确列:

=INDEX(SORT(FILTER(Table2[[Date]:[Name]],Table2[Statut]=F1),1,-1),0,1)

=INDEX(SORT(FILTER(Table2[[Date]:[Name]],Table2[Statut]=F1),1,-1),0,3)

第一个公式中的末尾,1指的是日期范围中的相对列,第二个公式中的,3指的是名称范围中的相对列。

英文:

Change your formula to include the Name column in the output:

=SORT(FILTER(Table2[[Date]:[Name]],Table2[Statut]=E1),1,-1)

匹配多个符合相同条件的结果。


If your data is not right next to each other and you do not want the middle columns to show in your output then we use the same formula wrapped in INDEX to return the correct column of the array:

=INDEX(SORT(FILTER(Table2[[Date]:[Name]],Table2[Statut]=F1),1,-1),0,1)

And

=INDEX(SORT(FILTER(Table2[[Date]:[Name]],Table2[Statut]=F1),1,-1),0,3)

Where the ending ,1 in the first formula refers to the relative column in the referred to range of the date, and the ,3 in the second refers to the relative column in the referred to range of the Name

匹配多个符合相同条件的结果。

huangapple
  • 本文由 发表于 2020年1月6日 22:53:26
  • 转载请务必保留本文链接:https://go.coder-hub.com/59614199.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定