利用设置关键字提取第二个工作表值 [英] Use set of keywords to extract values from second worksheet

问题描述

我会尽力解释我现在面临最好的问题，我可以。

I'll try to explain the problem I'm facing best as I can.

一个有一组包含提取出来作为一个Excel文件的多个副本的数据。在这个数据是钥匙，我想用从另一个工作簿中筛选出相关的数据。

A have a set of data that contains multiple duplicates extracted as an excel file. Within this data are "keys" that I want to use to filter out relevant data from another workbook.

我开始从关键字列表中删除重复，我想我得到这个工作的一种令人满意的。然后，我尝试提取并使用以下数组公式计算从值的最小值：

I start by removing duplicates from the list of keywords and I think I got this working kind of satisfactory. I then try to extract and calculate the minimum from the values using the following array formula:

=MIN(VLOOKUP(Blad1!D2:D8,Blad2!A3:D9,2))

不过，由于预期这不起作用。该值返回从目标范围的最低值，但似乎忽略了所提供的关键字。相反，它可以简单地认为的整个范围的最小值。

However, this doesn't work as expected. The value returns the minimum value from the target range, but seems to ignore the provided keywords. Instead it simply finds the minimum value of the entire range.

我是远从专业的，当涉及到Excel，以便就如何实现这一可能以更有效的方式来完成的任何建议都欢迎。

I am far from a professional when it comes to excel so any suggestions on how this could be done in a more efficient way are welcome.

下面是一个样本文档

推荐答案

这些数组公式应该是你所需要的。

These array formulas should be what you need.

'MINIF in F2,
=MIN(IF(COUNTIF($D$2:$D$8, Blad2!$A$2:$A$9&""), Blad2!$B$2:$B$9))
'MAXIF in G2
=MAX(IF(COUNTIF($D$2:$D$8, Blad2!$A$2:$A$9&""), Blad2!$C$2:$C$9))
'AVERAGEIF¹ in H2
=AVERAGE(IF(COUNTIF($D$2:$D$8, Blad2!$A$2:$A$9&""), Blad2!$D$2:$D$9))

数组公式需要用<大骨节病>控制 + <大骨节病>移 + <大骨节病>Enter↵敲定。
尝试更加紧密地降低全列引用到的范围重新presenting实际数据的范围。数组公式嚼起来计算周期对数所以它是很好的做法，缩小范围，引用到最低限度。

Array formulas need to be finalized with Ctrl+Shift+Enter↵. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum.

的结果是15，35和23.6。

The results are 15, 35 and 23.6.

¹Note，这是不天然<一href=\"https://support.office.com/en-us/article/AVERAGEIF-function-ff3c70f9-5afe-4d3e-87d4-717187f98c20\"相对=nofollow> AVERAGEIF功能或<一个href=\"https://support.office.com/en-us/article/AVERAGEIFS-function-48910C45-1FC0-4389-A028-F7C5C3001690\"相对=nofollow> AVERAGEIFS功能但数组公式。这种方法被选择，由于大量的标准

¹Note that this is NOT the native AVERAGEIF function or AVERAGEIFS function but an array formula. This approach was chosen due to the large number of criteria.

这篇关于利用设置关键字提取第二个工作表值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！