将数组公式的文本结果转换为可用格式 [英] Convert an Array Formula's Text Results into a Usable Format
问题描述
当数组公式的结果是数字时，我发现通常很容易找到合适的方法将数组折叠成单个结果。然而，当数组公式的结果是文本时，我发现难以以提供单个所需结果的方式来操作公式。简而言之，是否有一种操纵我忽略的文本结果数组的方法？请参阅这个问题的底部以获得最终所需的公式，而不需要解决方案。 p>
*编辑 - 再次阅读后，我可以交替地总结我的问题：有一种方法可以从公式数组结果访问多个文本元素，没有单独选择（例如：使用INDEX）？
Array公式工作的示例，其中结果数组是数值 / p>
（1）示例1：假设列A行1-500是产品ID的列表，格式为xyz123，列B行1-500显示该产品的总销售额。如果我想找到具有最高销售额的产品的销售额，其中ID的最后3位数大于400，我可以使用像这样的数组公式（用CTRL + SHIFT + ENTER确认，而不是按ENTER）：
= MAX（IF（VALUE（RIGHT（A1：A500,3））> 400，B1：B500，） ）
（2）示例2 现在假设列B包含产品名称，而不是销售。我现在想要简单地返回与产品ID的最后3位数字匹配的名称> 400。这可以如下进行：
= INDEX（B1：B500，MIN（IF（VALUE（RIGHT（A1：A500,3））> 400，ROW（A1：A500），）））
这里，我做了一点点操作，所以实际的Array部分公式[IF（RIGHT（A1：A500， 3 ...]返回值结果[cellA1：A500的ROWs，其中最后3位数高于400];因此，我可以使用MIN来显示仅匹配的第一个ROW＃，然后我可以使用该折叠结果在一个常规的INDEX函数中。
（3）示例3 有关最后的示例，请参阅此处关于类似问题的讨论[比我以下的总结示例，以与此问题不直接相关的方式]： https://stackoverflow.com/a/31325935 / 5090027
现在假设你想要一个所有产品名称，产品ID的最后3位数字> 400。据我所知，这不能在单个单元格中真正完成，它必须通过将每个单独的结果放在随后的单元上来完成。可以将以下公式放在C1中，并将其拖放到10行，然后显示前10个产品名称，产品ID的最后3位数字> 400。
= INDEX（$ B $ 1：$ B $ 500中，SMALL（IF（VALUE（RIGHT（$ A $ 1：$ A $ 500,3））GT; 400，ROW（$ A $ 1 $ A $ 500），），ROW（）））
数组公式不起作用，结果数组是文本值
现在假设我想在示例3中获取结果，并执行一些文本操作在他们。例如，假设我想将它们全部连接成单个文本字符串。以下不起作用，因为连接不会将这样的数组结果作为可接受的参数。
= CONCATENATE（（IF（VALUE（RIGHT（$ A $ 1：$ A $ 500,3））> 400，ROW （$ B $ 1：$ B $ 500），）））
所以问题是：有没有人知道如何让这个最后一个公式工作？或者，如何获取一个公式来处理文本结果的数组，并将其转换为可用范围[所以可以插入到上面的连接]，或者可以立即用文本参数来操作[例如中间，搜索，替代等]？现在唯一可以看到的方法是使用上面的例子3，然后再进一步说，例如，Concatenate（C1，C2，C3 ... C10）。
如前所述，没有本机功能可以在单个单元格中执行所需的操作。如果你绝对不能使用VBA，那么你可以使用一个帮助列（可以隐藏列，如果首选），然后让单元格在你想要的结果只是显示帮助列的最后一个单元格。
示例：
产品名称类型
苹果水果
西兰花蔬菜
胡萝卜蔬菜
橙色水果
说你想要一个单元格显示所有的水果结果。您可以使用另一列来托管此公式。你稍后会隐藏列，所以让我们一起使用一个列，比如列Z。我们也想很容易地改变你要找的东西，所以我们把条件放在单元格D2中。在单元格Z2中并复制下来，您将使用以下公式：
= IF（B2 = $ D $ 2，IF（Z1 = ，A2，Z1，&A2），IF（Z1 =，，Z1））
这将导致以下结果：
产生名称类型搜索（其他列，直到你Z）
苹果水果苹果
西兰花蔬菜苹果
胡萝卜蔬菜苹果
橙色水果苹果，橙色
然后在任何你想要的结果单元格的地方，我们会说D3，只需使用这个公式从帮助列中获取最后一个结果，然后隐藏帮助列。 p>
= Z5
哪些导致了这些下降：
产品名称类型搜索
苹果水果
西兰花蔬菜苹果，橙色
胡萝卜蔬菜
橙色水果
您可以使用动态命名范围而不是简单的 = Z5 ，以确保您总是在帮助列中获取最后一个单元格，以便您的数据可以增长或缩小，您仍然可以获得正确的结果。但现在，您可以将 Fruit 中的单元格D2的内容更改为 Vegetable ，现在结果单元格将显示西兰花，胡萝卜。希望这样可以适应你的需要。
When the results of an Array Formula are numbers, I find it generally easy to find an appropriate method to collapse the array into a single result. However when the results of an Array Formula are text, I find it difficult to manipulate the formula in a way which provides a single desired result. In short, is there a method of manipulating an Array of text results which I have overlooked? See the bottom of this question for the final desired formula which doesn't work, and request for solutions.
*Edit - after reading through this again, I can alternately summarize my question as: is there a way to access multiple text elements from a 'Formula Array result', without individually selecting (eg: with INDEX)?
Examples where Array Formulas work, where the Result Array is number values
(1) Example 1: Assume column A rows 1-500 is a list of product ID's in the format of xyz123, and column B rows 1-500 shows total sales for that product. If I want to find the sales for the product with the highest sales, where the last 3 digits of an ID are above 400, I could use an Array Formula like so (confirmed with CTRL + SHIFT + ENTER instead of just ENTER):
=MAX(IF(VALUE(RIGHT(A1:A500,3))>400,B1:B500,""))
(2) Example 2 Now assume that column B contains product names, instead of Sales. I now want to simply return the first name which matches criteria of the last 3 digits of the product ID being > 400. This could be done as follows:
=INDEX(B1:B500,MIN(IF(VALUE(RIGHT(A1:A500,3))>400,ROW(A1:A500),"")))
Here, I have done a little manipulation, so that the actual Array part of the formula [IF(RIGHT(A1:A500,3...] returns a value result [the ROWs of the cellsA1:A500 where the last 3 digits are above 400]; I can therefore use MIN to show only the first ROW # which matches, and then I can use that collapsed result in a regular INDEX function.
(3) Example 3 For a final example, see the discussion on a similar question here [Goes more in-depth than my summarized example below, in a way not directly relevant to this question]: https://stackoverflow.com/a/31325935/5090027
Assume now that you want a list of all product names, where the last 3 digits of the product ID >400. To my knowledge, this cannot really be done in a single Cell, it would have to be done by placing each individual result on a subsequent cell. The following formula could be placed, for example, in C1 and dragged down 10 rows, and would then show the first 10 product names with the product ID's having last 3 digits > 400.
=INDEX($B$1:$B$500,SMALL(IF(VALUE(RIGHT($A$1:$A$500,3))>400,ROW($A$1:$A$500),""),ROW()))
Example where Array Formulas will not work, where the result array is text values
Now assume that I want to take the results in Example 3, and perform some text manipulation on them. For example, assume I want to concatenate them all into a single string of text. The below doesn't work, because concatenate won't take an array of results like this as acceptable arguments.
=CONCATENATE((IF(VALUE(RIGHT($A$1:$A$500,3))>400,ROW($B$1:$B$500),"")))
So the question is: does anyone know how to get this last formula to work? Or, how to get a formula to work which takes an array of text results, and either converts it into a 'usable range' [so it can be plugged into Concatenate above], or can be manipulated with text arguments immediately [such as mid, search, substitute, etc.]? Right now the only method I can see would be using example 3 above, and then going further and saying, for example, Concatenate(C1,C2,C3...C10).
As stated previously, there is no native function which can do what you want in a single cell. If you absolutely cannot use VBA, then you could use a helper column (can hide the column if preferred) and then have the cell where you want the result simply show the last cell of the helper column.
Example:
Produce Name Type
Apple Fruit
Broccoli Vegetable
Carrot Vegetable
Orange Fruit
Say you want a single cell to show all Fruit results. You could use another column to host this formula. You will be hiding the column later, so let's use one out of the way, like column Z. We also want to easily be able to change what you're looking for, so we'll put the condition in cell D2. In cell Z2 and copied down, you would use this formula:
=IF(B2=$D$2,IF(Z1="",A2,Z1&", "&A2),IF(Z1="","",Z1))
That would result in the following:
Produce Name Type Search For (other columns until you get to Z)
Apple Fruit Fruit Apple
Broccoli Vegetable Apple
Carrot Vegetable Apple
Orange Fruit Apple, Orange
Then in wherever you want your result cell, we'll say D3, simply use this formula to get the last result from your helper column, and then hide the helper column.
=Z5
Which results in the following:
Produce Name Type Search For
Apple Fruit Fruit
Broccoli Vegetable Apple, Orange
Carrot Vegetable
Orange Fruit
You could use a dynamic named range instead of simply =Z5 to make sure you're always getting the last cell in your helper column so that your data can grow or shrink and you'll still get the correct result. But now you can change the contents of cell D2 from Fruit to be Vegetable and now the result cell will show Broccoli, Carrot. Hopefully something like this can be adapted to your needs.
这篇关于将数组公式的文本结果转换为可用格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
