Excel-用于根据条件创建查找值数组的公式 [英] Excel - Formula to create an array of lookup values based on a criteria
问题描述
我一直在寻找这个看似简单的问题的解决方案,但到目前为止却失败了.我在Excel中有这样的表格:
I was looking for the solution for this apparently easy problem, but failed so far. I have a table in Excel like this:
Column 1 Column 2
apples 1
apples 2
bananas 5
apples 3
bananas 4
我需要一个公式,该公式根据第1列的条件返回第2列中的值的数组.因此,如果我选择"apples",则我的输出数组应为{1,2,3}.该输出应兼容在标准SUM(SUMIFS())公式中使用.
What I need is a formula that returns an array of the values in column 2, based on the criteria on column 1. So if I choose "apples", my output array should be {1,2,3}. That output should be compatible for use inside of a standard SUM(SUMIFS()) formula.
在此先感谢您的见解
关于,安德烈(André)
Regards, André
输出数组中元素的顺序不重要
The order of the elements in the output array is unimportant
推荐答案
我假设您不想要这样的东西
I assume you don't want something like this
= IF(A2:A6 ="apples",B2:B6,")
对于您的示例,该示例将返回您的值以及诸如此类的空字符串"
For your example that will return your values along with "null strings" like this
{1; 2;"; 3;"}
您可以使用此版本以升序返回值
You could use this version to return the values in ascending order
= SMALL(IF(A2:A6 ="apples",B2:B6),ROW(INDIRECT("1:"& COUNTIF(A2:A6,"apples")))))
那会给你
{1; 2; 3}
如果在另一个公式中使用它,则需要使用 CTRL + SHIFT + ENTER
If you use that in another formula you'll need to "array enter" with CTRL+SHIFT+ENTER
这篇关于Excel-用于根据条件创建查找值数组的公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!