excel - 在随机 5x5 数组中查找值的单元格引用

我有一个 5x5 数组，在随机位置带有字母 AY。我需要找到特定字母在数组中的位置。我能够使用搜索、匹配和 isnumber 获得一个 1x25 矩阵，该矩阵在所需值的位置有一个 1，但我不知道如何从数组中提取该位置。

{a, b, c, d, e; f、g、h、i、j；k、l、m、n、o；p、q、r、s、t；u, v, w, x, y}

如果我正在寻找“d”将返回

{0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}

如何提取“d”位于原始数组的第四位或[1,4]？在原始问题中，字符没有任何特定顺序。它们是完全随机的。

我在 Excel 电子表格中执行此操作，因此试图找到一个有效的公式而不是 VBA 代码。isnumber 围绕搜索获得 1x25 矩阵

标签： excelexcel-formula

假设：

AGGREGATE 是我在这里选择的功能。它将对公式 14 和 15 执行类似数组的操作。它可以设置为忽略错误，这将是我们的优势，我们可以选择要从结果列表中提取结果的位置。AGREGATE 采用以下形式：

AGGREGATE(Formula #, Ignore Option #, range/array, parameter)

公式 15 将结果列表从小到大排序。公式 14 从最大到最小。如果所有条目都是唯一的，则使用两者中的哪一个都没有关系。
选项 6 将忽略范围/数组内的所有错误
对于范围/数组部分，构建所有电子表格行/列号的列表。为行构建一次公式，然后重复它并切换到列。将行号或列号除以 TRUE/FALSE 结果。在这种情况下，范围/数组是否等于您的搜索词。通过数学运算（并非所有函数）发送 TRUE/FALSE 时，TRUE 变为 1，FALSE 变为 0。除以 0 将产生错误。只有您的搜索词所在的行将除以 1，而行号保持不变。由于选项 6 会忽略所有错误，因此您将看到包含您的搜索词的行号列表。只要您的单元格都是唯一的，这意味着您将只有 1 个行号。
公式 14 和 15 的参数部分是您希望返回的排序结果的位置。在这种情况下，需要第一次出现，所以它应该是 1。

把这些信息放在一起告诉我们，我们的 AGGREGATE 公式应该是这样的：

FOR ROW
AGGREGATE(15,6,ROW($C$2:$G$6)/($C$2:$G$6=$A$2),1)

FOR COLUMN
AGGREGATE(15,6,COLUMN($C$2:$G$6)/($C$2:$G$6=$A$2),1)

现在这将告诉您您所在的电子表格列和行。如果您的矩阵/数组从 A1 开始，则无需进一步做任何事情。但是，此解决方案假定您的矩阵从 C2 开始。这意味着您的矩阵行号和列号与电子表格的行号和列号不匹配。需要进行细微的调整。只需减去矩阵的起始位置并加 1。这种调整将使公式看起来像：

FOR ROW
AGGREGATE(15,6,ROW($C$2:$G$6)/($C$2:$G$6=$A$2),1)-ROW($C$2)+1

FOR COLUMN
AGGREGATE(15,6,COLUMN($C$2:$G$6)/($C$2:$G$6=$A$2),1)-COLUMN($C$2)+1