嵌套多单元阵列公式

嵌套多单元阵列公式

我的数据位于前两个表(A1:B6D1: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,"")))}

相关内容