☰

嵌套多单元阵列公式

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 array
嵌套多单元阵列公式

我的数据位于前两个表（A1:B6和D1:F6）中：

嵌套多单元格数组公式

我预期的最终结果是I1:I4中的每个对应值都是 on H1:H4。

例如，您取“A”的值，从中找到每个对应的代码B1:B6（即“code1”、“code2”和“code3”），然后查找中的最新日期E1:E5（对应于上一步中的代码）并给出与中的值的日期相对应的结果D1:D5。

我陷入了查找最新日期的步骤。我尝试了这些：

{=IF($F$1:$F$5=IF($A$1:$A$6=$H$1,$B$1:$B$6),$E$1:$E$5)}
{=IF($F$1:$F$5={IF($A$1:$A$6=$H$1,$B$1:$B$6)},$E$1:$E$5)}

有什么指导吗？我可以添加其他列，但问题是多数组结果，据我所知，这些结果无法存储在一个单元格中。

答案1

我不是 Excel 专家！但是我意识到当传递给 MAX 的是一个绝对数组而不是从 IF 返回的数组时，公式在 MAX 中工作得很好。我猜这是因为两个数组的结果长度不匹配，返回 #N/A 表示超出，这导致整个公式为 #N/A。请参见下面的屏幕截图。

在此处输入图片描述

这促使我在 VBA 中创建一个非常基本的 UDF，它仅返回所需的数组。此 UDF 中没有太多验证。确保只向其传递单列引用，并且预期的返回值存在于右侧非常相邻的列中。例如，如果您传递 A1:A4，它将检查 B1:B4 中的值。

按ALT+F11访问 VBA 编辑器，插入-->模块并将以下代码粘贴到其中。

Public Function RetArray(r1 As Range, a As String) As Variant

Dim i
i = 0
Dim myarray()

For Each cell In r1
    If cell.Value = a Then
        i = i + 1
    End If
Next cell
ReDim myarray(i)
Dim j
j = 0
For Each cell In r1
    If cell.Value = a Then
       myarray(j) = cell.Offset(0, 1).Value
       j = j + 1
    End If

Next cell
RetArray = myarray
End Function

我们将在最终解决方案中使用此 UDF。例如，要获取 B 列的数组，其中 A 是 H1，请使用以下公式=RetArray(A1:A6,H1)

下一个棘手的部分是将这个返回的值映射到 E 列的精确位置并从 D 中获取值。

首先在 C 中创建一个辅助列，它是 D 和 E 的连接

C1 中的公式是=E1&F1并将其拖到下面的目标单元格。确保您的日期在 Excel 中采用有效的日期格式，而不是文本格式，否则此解决方案将不起作用。

您的表格位于 H1:H4。

现在在 I1 中输入以下公式。

=INDEX($D$1:$D$9,MIN(IF($C$1:$C$9=MAX(IF($F$1:$F$9=RetArray($A$1:$A$8,H1),$E$1:$E$9,0))&RetArray($A$1:$A$8,H1),ROW($C$1:$C$9),99^99)))

按CTRL+ SHIFT+ENTER创建数组公式并将其向下拖动到表格的整个长度。

不过，这个解决方案还没有经过彻底的测试。只有当你从第 1 行开始数据时，它才会有效，否则引用会出错，除非你使用前一行引用来操作相同的数据。请尝试一下并恢复。也请检查其他用户的更智能的解决方案。

在此处输入图片描述

更新

我意识到，虽然常量数组确实有效而单元格引用无效（这导致我创建了一个返回数组的 UDF），但使用 TRANSPOSE 函数实际上似乎在内部创建了某种常量数组。因此，只需将 UDF 替换为包裹在 IF 中的 TRANSPOSE，解决方案便可运行，无需任何 VBA 代码和任何辅助列。

请尝试一下并检查是否适合您。

I1 及以下的数组公式是

=INDEX($D$1:$D$9,MIN(IF($E$1:$E$9&$F$1:$F$9=MAX(IF($F$1:$F$9=TRANSPOSE(IF($A$1:$A$8=H1,$B$1:$B$8)),$E$1:$E$9,0))&TRANSPOSE(IF($A$1:$A$8=H1,$B$1:$B$8)),ROW($C$1:$C$9),99^99)))

在此处输入图片描述

答案2

经过一整天的思考，结果如下：

结果

创建了 4 个辅助列，每个列都有单元格数组公式。

单元格C1向下：

{=MAX(IF(B1=$K$1:$K$5,$J$1:$J$5))}

单元格D1向下：

{=IFERROR(INDEX($I$1:$I$5,MATCH(1,(B1=$K$1:$K$5)*(C1=$J$1:$J$5),0)),"")}

单元格E1向下：

{=MAX(IF(A1=$A$1:$A$6,$C$1:$C$6))}

单元格F1向下：

{=INDEX($B$1:$B$6,MATCH(1,(A1=$A$1:$A$6)*($C$1:$C$6=E1),0))}

答案是G1向下的：

{=INDEX($D$1:$D$6,MATCH(1,($B$1:$B$6=F2)*($C$1:$C$6=E2),0))}

将其放在一栏中会很好，但是哦，好吧... :)

答案3

朋友，现在是我的。

基本上查询，就是找到最新（最近）日期对于 Code1、Code2 和 Code3，如果它在 ColA 中具有相应的匹配值 A，（检查原始查询帖子）。

这意味着必须尝试两者。因为 Code3 出现了两次，第一次是 A，第二次是 B。

正如我所写，最新的就是最近的，MAX 函数无法使用，MIN 查找最新值。

查看屏幕截图，，

在此处输入图片描述

公式是，

=IF(D423:D428="A",IF(E423:E428 =({"代码1","代码","代码3"}),MIN(F423:F428)))

甚至非数组公式也解决了这个问题。

注意：您可以使用数据范围重新设计公式。

我已发布经过我测试的解决方案，如有不同，请发表评论。

答案4

经过一些修改，我找到了解决方案最近日期仅在所需区域内，使用过滤数据标准是 A 和 Code1、Code2 或 Code3。

在此处输入图片描述

{=MAX(IF(D423:D428="A",IF(E423:E428 ={"代码1","代码","代码3"},F423:F428,"")))}

相关内容