☰

使用非数值数据查找矩阵

tag-icon • tag-icon microsoft-excel libreoffice-calc lookup
使用非数值数据查找矩阵

我有几个小表格（标题、内容）以矩阵形式组织，如以下示例：

在此处输入图片描述

我想进行查找，将“title”值放入单元格中，然后返回内容。因此，我在黄色单元格中放入一个字母并返回内容，如下所示：

在此处输入图片描述

它本质上是 vlookup 和 hlookup 的结合，或者在矩阵而不是向量中搜索。问题是我找不到对非数字数据执行此操作的方法。许多公式都要求对数据进行排序，但对我来说这没有意义。数据本身具有逻辑顺序（当然，在显示的 meme 示例中不是）。

有什么想法可以实现这一点吗？当然，将行堆叠在一起（A、B、C、D、E、F、G、H、I）然后进行 vlookup 可以解决问题，但是数据必须采用这种格式。

PD：需要适用于 Excel 和 Libreoffice 的解决方案。

答案1

我建议一种基于 INDEX 函数并使用两个辅助单元格的可能方法。唯一的假设是，如屏幕截图所示，两个上下相邻的表格之间有一个空白行。

表格的长度不必总是相同。可以根据需要变化。

参见下面的截图。参考其中显示的数据。

在此处输入图片描述

辅助细胞I4

=MIN(IF($G$3=$D$3:$F$20,ROW($D$3:$F$20),9^99))-ROW($C$2)

CTRL在旧版 Excel 中，您需要在公式栏中按+ SHIFT+将其设为数组公式ENTER。否则，公式可能无法正常工作。

同样，在辅助单元格 J4 中，数组公式为

=MIN(IF($G$3=$D$3:$F$20,COLUMN($D$3:$F$20),9^99))-COLUMN($C$2)

将搜索字符串放入 G3 中。

G4 中的公式是

=IFERROR(IF(G3="","",IF(ISBLANK(INDEX($D$3:$F$20,$I$4+ROW(A1),$J$4)),"",INDEX($D$3:$F$20,$I$4+ROW(A1),$J$4))),"")

将其向下拖动至整个范围的长度。

使用此动画 gif 查看其工作方式。

在此处输入图片描述

如果所有表格的大小相同，则无需辅助单元格的更简单的解决方案如下。

在 G4

=INDEX($D$3:$F$16,MIN(IF($G$3=$D$3:$F$16,ROW($D$3:$F$16),9^99))-ROW($C$2)+ROW(A1),MIN(IF($G$3=$D$3:$F$16,COLUMN($D$3:$F$16),9^99))-COLUMN($C$2))

CTRL按+ SHIFT+将其设为数组公式ENTER。公式将自动括在花括号中。

在此处输入图片描述

编辑1

我对 LibreOffice 没有太多经验，但看起来数组公式在 Excel 和 Calc 之间的工作方式可能不完全相同？

请参阅下面的解决方案，其中使用了 2 个辅助单元格。在我看来，它在 Excel 和 Calc 中都有效。现在您的表格具有固定且相同的长度，我会尽量保持简单。

在此处输入图片描述

辅助单元格 I4 数组公式 (CTRL + SHIFT + ENTER)

=MIN(IF($G$3=$D$3:$F$16,ROW($D$3:$F$16),9^99))-ROW($C$2)

辅助细胞 J4 阵列公式

=MIN(IF($G$3=$D$3:$F$16,COLUMN($D$3:$F$16),9^99))-COLUMN($C$2)

在 G4 中输入简单的 INDEX 公式

=INDEX($D$3:$F$16,$I$4+ROW(A1),$J$4)

将其向下拖动至表格的长度。

在我这边，此解决方案 xlsx 文件在 Excel 和 Calc 7.4 中均可运行。请检查它是否适合您。

答案2

假设您可以访问 O365 Excel，您实际上能按照您的建议堆叠行，但要堆叠在动态数组中，而不是辅助列中。如果您想使用 OFFSET 函数，则无法使用，因为 OF​​FSET 需要实际的单元格范围，并且无法与动态数组一起使用。您必须使用 INDEX。

这种方法不需要辅助单元或辅助列，一切都使用动态数组在内存中完成。

假设您的表格从 A1 开始，并且始终有 9 个表格，每个表格包含 5 个元素。

（此外，我经常使用 LET 和 alt-enter 换行符来帮助阐明我的公式。在这种情况下，LET 还消除了两次堆叠数组的需要。）

我们还假设搜索项“E”在 B25 中，并且此公式在 B26 中：

=LET(
foo,TOCOL(A1:C20,1,TRUE),
bar, MATCH(B25,foo,0)+1,
range, SEQUENCE(5,1,bar,1),
INDEX(foo,range)
)

如果您不想使用 LET，那么：

=INDEX(TOCOL(A1:C20,1,TRUE),SEQUENCE(5,1,MATCH(B25,TOCOL(A1:C20,1,TRUE),0)+1,1))

无论哪种情况，请当心，这对于您的示例来说“有点”是硬编码的，并且还假设表中的任何值都不完全是从“A”到“I”，否则您会遇到问题。

答案3

使用命名范围和INDIRECT()函数。将所有小表设置为命名范围，然后搜索框下的公式将是=INDIRECT(E2)（在我的示例中，E2 是搜索框的位置，如果您的 Excel 版本不溢出内容，则它可能不起作用）：在此处输入图片描述

进一步改进，将搜索框转换为使用带有下拉框的数据验证，以便它仅提取有效的标题。

答案4

间接匹配

假设：

  1. 每组数据（B3:E8、B10:E15、B17:E22）具有相同的维度。
  2. 列标题值是唯一的。

目标：

动态生成单元格坐标以用于 INDIRECT 函数来检索该单元格中的数据。

例子：
使用“值”（G3）=“H”，我想从 C18：C22 中检索值并将它们显示在 G4：G8 中。

例子

命名范围：

 lvl_1   B3:D3     // "A", "B", "C"
 lvl_2   B10:D10   // "D", "E", "F" //
 lvl_3   B17:D17   // "G", "H", "I" //
 value   G3        //  Search Value ("H" in example)

G4:G8 中的每个公式

=IFERROR(INDIRECT("R"&
    IFERROR(ROW(lvl_1)+ROW()-ROW(value)&"C"&MATCH(value,lvl_1,0)+MIN(COLUMN(lvl_1))-1,
    IFERROR(ROW(lvl_2)+ROW()-ROW(value)&"C"&MATCH(value,lvl_2,0)+MIN(COLUMN(lvl_2))-1,
           (ROW(lvl_3)+ROW()-ROW(value)&"C"&MATCH(value,lvl_3,0)+MIN(COLUMN(lvl_3))-1))),
    0),"-")

我的间接公式使用了 R1C1：

   FYI: My INDIRECT formula specified R1C1 notation.
   This was achieved using the "FALSE" (/"0") flag. A1 is
   the default notation if nothing specified.

   The following are all equivalent.
   ---------------------------------         
   =INDIRECT("R18C3",0)        // R1C1 
   =INDIRECT("R18C3",FALSE)    // R1C1 
   =INDIRECT("C18")            // A1 (Default)
   =INDIRECT("C18",1)          // A1
   =INDIRECT("C18",TRUE)       // A1

笔记

  1. 在矩阵表中插入额外的列不会破坏任何内容。
  2. 只要您对所有级别执行相同的操作（无论是否填充了数据），在数据表中添加额外的行也不会破坏任何内容。
  3. 公式可以使用辅助列/单元格和/或硬编码值和/或利用表格变得更小，但您最清楚如何使用它。
  4. 按照相同的格式添加其他级别。

示例：在单元格 G4 中求解
为了使公式更小并且更容易理解，我预先求解了一些值，我将把它们代入公式中。

 lvl_1 = B3:D3    = {"A","B","C"}
 lvl_2 = B10:D10  = {"D","E","F"}
 lvl_3 = B17:D17  = {"G","H","I"}
 value = G3       = "H"  //  Search Value
 
 ROW()       = ROW(G4)         = 4
 ROW(value)  = ROW(G3)         = 3
 ROW(lvl_1)  = ROW(B3:D3)      = 3
 ROW(lvl_2)  = ROW(B10:D10)    = 10
 ROW(lvl_3)  = ROW(B17:D17)    = 17

 COLUMN(lvl_1) = COLUMN(B3:D3) = {"2","3","4"}
 COLUMN(lvl_2) = COLUMN(B3:D3) = {"2","3","4"}
 COLUMN(lvl_3) = COLUMN(B3:D3) = {"2","3","4"}
 
 MIN(COLUMN(lvl_1)) = MIN({"2","3","4"}) = 2
 MIN(COLUMN(lvl_2)) = MIN({"2","3","4"}) = 2
 MIN(COLUMN(lvl_3)) = MIN({"2","3","4"}) = 2

所以

=IFERROR(INDIRECT("R"&
    IFERROR(ROW(lvl_1)+ROW()-ROW(value)&"C"&MATCH(value,lvl_1,0)+MIN(COLUMN(lvl_1))-1,
    IFERROR(ROW(lvl_2)+ROW()-ROW(value)&"C"&MATCH(value,lvl_2,0)+MIN(COLUMN(lvl_2))-1,
           (ROW(lvl_3)+ROW()-ROW(value)&"C"&MATCH(value,lvl_3,0)+MIN(COLUMN(lvl_3))-1))),
    0),"-")

变成

=IFERROR(INDIRECT("R"&
    IFERROR( 3+4-3&"C"&MATCH("H",{"A","B","C"},0)+2-1,
    IFERROR(10+4-3&"C"&MATCH("H",{"D","E","F"},0)+2-1,
            17+4-3&"C"&MATCH("H",{"G","H","I"},0)+2-1)),
    0),"-")

然后

=IFERROR(INDIRECT("R"&
    IFERROR(!ERROR,
    IFERROR(!ERROR,
            "18C3")),
    0),"-")

和

=IFERROR(INDIRECT("R18C3",0),"-")

或者

=INDIRECT("R18C3",0)

最后

="1-HHH"

扩展一下，上例中的 G4:G8 将解析为：

             F                  G            
     +---------------+----------------------+
 3   | Search Value: | ="H"                 |
     +---------------+----------------------+
 4   |               | =INDIRECT("R18C3",0) |
     +---------------+----------------------+
 5   |               | =INDIRECT("R19C3",0) |
     +---------------+----------------------+
 6   |               | =INDIRECT("R20C3",0) |
     +---------------+----------------------+
 7   |               | =INDIRECT("R21C3",0) |
     +---------------+----------------------+
 8   |               | =INDIRECT("R22C3",0) |
     +---------------+----------------------+

相关内容