主页 / computer / 问题 / 1742220
Accepted
luchonacho
Asked: 2022-09-14 12:47:44 +0800 CST

使用非数值数据查找矩阵

  • 772

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

在此处输入图像描述

我想做一个查找，以便将“标题”值放在一个单元格中，然后返回内容。所以，我在黄色单元格中放了一封信并返回内容，如下所示：

在此处输入图像描述

它本质上是一个 vlookup 和 hlookup 一起，或者在矩阵而不是向量中搜索。问题是我找不到对非数字数据执行此操作的方法。许多公式需要对数据进行排序，但在我的情况下它没有意义。数据本身有一个逻辑顺序（当然不是在显示的模因示例中）。

任何想法我怎样才能做到这一点？自然，将行堆叠在一起（A、B、C、D、E、F、G、H、I）然后进行 vlookup 可以解决问题，但数据必须采用这种格式。

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

microsoft-excel libreoffice-calc

4 个回答

  1. Best Answer

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

    表格的长度不必总是相同的。如果需要，它可以变化。

    请参阅下面的屏幕截图。参考其中显示的数据。

    在此处输入图像描述

    辅助细胞 I4

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

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

    在 Helper 单元格 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 中工作。请检查这是否适合您。

    • 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”，否则你会遇到问题。

    • 2

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

    进一步细化，将搜索框转换为使用带有下拉框的数据验证，因此它只提取有效的标题。

    • 1

  4. 间接和匹配

    假设：

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

    目标：

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

    示例：
    使用 "value" (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) |
     +---------------+----------------------+
    • -1

相关问题