将数组公式的文本结果转换为可用格式 [英] Convert an Array Formula's Text Results into a Usable Format

问题描述

当数组公式的结果是数字时，我发现通常很容易找到合适的方法将数组折叠为单个结果.但是，当数组公式的结果是文本时，我发现很难以提供单一所需结果的方式操作公式.简而言之，是否有一种方法可以操作我忽略的一系列文本结果?请参阅此问题的底部以了解最终无效的公式，并请求解决方案.>

*Edit - 再次阅读本文后，我可以交替地将我的问题总结为:有没有办法从公式数组结果"中访问多个文本元素，而无需单独选择(例如:使用 INDEX)?

使用数组公式的示例，其中结果数组是数值

(1) 示例1: 假设A 列第1-500 行是xyz123 格式的产品ID 列表，B 列第1-500 行显示该产品的总销售额.如果我想找到销售额最高的产品的销售额，其中 ID 的最后 3 位数字高于 400，我可以使用像这样的数组公式(用 CTRL + SHIFT + ENTER 确认，而不仅仅是 ENTER):

=MAX(IF(VALUE(RIGHT(A1:A500,3))>400,B1:B500,""))

(2) 示例 2 现在假设 B 列包含产品名称，而不是 Sales.我现在只想返回与产品 ID 的最后 3 位数字大于 400 的条件匹配的名字.这可以按如下方式完成:

=INDEX(B1:B500,MIN(IF(VALUE(RIGHT(A1:A500,3))>400,ROW(A1:A500),"")))

这里，我做了一点操作，使公式 [IF(RIGHT(A1:A500,3...]) 的实际数组部分返回一个值结果 [单元格 A1:A500 的 ROW3 位数字高于 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))>400,ROW($A$1:$A$500)，)，排()))

数组公式不起作用的示例，其中结果数组是文本值

现在假设我想获取示例 3 中的结果，并对它们执行一些文本操作.例如，假设我想将它们全部连接成一个文本字符串.以下不起作用，因为 concatenate 不会将这样的结果数组作为可接受的参数.

=CONCATENATE((IF(VALUE(RIGHT($A$1:$A$500,3))>400,ROW($B$1:$B$500),"")))

所以问题是:有谁知道如何让最后一个公式起作用?或者，如何让一个公式工作，它接受一组文本结果，并将其转换为可用范围"[因此它可以插入上面的 Concatenate]，或者可以立即使用文本参数进行操作 [例如 mid、搜索、替代等]?现在我能看到的唯一方法是使用上面的示例 3，然后进一步说，例如 Concatenate(C1,C2,C3...C10).

解决方案

如前所述，没有本机函数可以在单个单元格中执行您想要的操作.如果您绝对不能使用 VBA，那么您可以使用辅助列(如果愿意，可以隐藏该列)，然后让您想要结果的单元格只显示辅助列的最后一个单元格.

示例:

生产名称类型苹果果实西兰花蔬菜胡萝卜蔬菜橙色水果

假设您想要一个单元格来显示所有 Fruit 结果.您可以使用另一列来承载此公式.稍后您将隐藏该列，因此让我们使用其中一个，例如 Z 列.我们还希望能够轻松更改您要查找的内容，因此我们将条件放在单元格 D2 中.在单元格 Z2 中并向下复制，您将使用以下公式:

=IF(B2=$D$2,IF(Z1="",A2,Z1&", "&A2),IF(Z1="","",Z1))

结果如下:

Produce Name Type Search For(其他列直到你到达Z)苹果 水果 水果 苹果西兰花蔬菜苹果胡萝卜蔬菜苹果橙子 水果 苹果, 橙子

然后在您想要结果单元格的任何地方，我们会说 D3，只需使用此公式从您的辅助列中获取最后一个结果，然后隐藏辅助列.

=Z5

结果如下:

Produce Name Type Search For苹果 水果 水果西兰花蔬菜苹果，橙胡萝卜蔬菜橙色水果

您可以使用动态命名范围而不是简单的 =Z5 来确保您始终获得辅助列中的最后一个单元格，以便您的数据可以增长或缩小，并且您仍然可以得到正确的结果.但是现在您可以将单元格 D2 的内容从 Fruit 更改为 Vegetable，现在结果单元格将显示 Broccoli, Carrot.希望这样的事情可以适应您的需求.

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屋！