☰

Microsoft Excel 公式使用表格对每行数据进行分类

tag-icon • tag-icon microsoft-excel worksheet-function
Microsoft Excel 公式使用表格对每行数据进行分类

我在 Excel 文档中尝试比较两个工作表。工作表 1 的 B 列中有一个区号列表。工作表 2 的 A 至 D 列中有一个所有区号列表，每列的第一行标题为“东部、中部、山区、太平洋”，后面是每个区域的所有区号。

我要做的是根据 Sheet 2 中相应的时区对 Sheet 1 中 C 列中的每个区号进行分类。

我该怎么做呢？

这是我所拥有的基本表格的屏幕截图。

第 1 页：
第 1 页

第 2 页：
第 2 页

答案1

以下是使用与现在完全相同的数据布局执行此操作的方法。此公式适用于C2Sheet1：

=IF(ISNA(MATCH(B2,Sheet2!$A$1:$A$40,0)),IF(ISNA(MATCH(B2,Sheet2!$B$1:$B$40,0)),IF(ISNA(MATCH(B2,Sheet2!$C$1:$C$40,0)),IF(ISNA(MATCH(B2,Sheet2!$D$1:$D$40,0)),"Not Found","Pacific"),"Mountain"),"Central"),"Eastern")

Sheet2!$A$1:$A$40虽然不好看，但确实管用。如果 Sheet2 上的数据超过第 40 行，则可能需要调整对 Sheet2 的四个绝对引用（例如）。

最终公式由四次 MATCH 函数的使用组成，每次仅搜索一列区号。

以下是我建立公式的方法。

我使用了 MATCH 函数。MATCH 只能在单个列中搜索值。首先，我创建了四个单独的 MATCH 公式。每个公式仅搜索其中一个时区列。例如，要在“东部”列中搜索在单元格中找到的值（区号）B2：

=MATCH(B2,Sheet2!$A$1:$A$40,0)

假设B2包含201，这将返回，2因为 201 是该列中的第二项。但如果B2包含205这将返回#N/A。我们通过用 ISNA 公式包装公式来捕获它：

=IF(ISNA(MATCH(B2,Sheet2!$A$1:$A$40,0)),"no_next_formula","Eastern")

ISNA 测试 MATCH 公式是否返回#N/A。如果是/真，则返回“no_next_formula”。如果否/假，则返回“Eastern”，因为 MATCH 在东部区号列中找到了我们的区号。

我们可以创建四个这样的公式，适当更改 Sheet2 上引用的范围以搜索其他三个区号。示例工作表可能如下所示：

在此处输入图片描述

现在，为了将它们组合成上面显示的单个公式，我复制了 F 列中的整个公式（减去符号）并替换了E 列公式中的=文本。"no_next_formula"

F2 中的公式：

=IF(ISNA(MATCH(B2,Sheet2!$D$1:$D$40,0)),"Not Found","Pacific")

E2 中的公式（之前）：

=IF(ISNA(MATCH(B2,Sheet2!$C$1:$C$40,0)),"no_next_formula","Mountain")

E2 中的公式（插入 F2 中的公式后）：

=IF(ISNA(MATCH(B2,Sheet2!$C$1:$C$40,0)),IF(ISNA(MATCH(B2,Sheet2!$D$1:$D$40,0)),"Not Found","Pacific"),"Mountain")

然后我重复这些步骤，复制现在的公式以替换公式中的E2文本。完成公式的替换后，我们就得到了上面显示的完整公式。"no_next_formula"D2C2

答案2

如果你确定你的数据格式正确（具体来说，在 Sheet 2 中没有区号出现超过一次），那么你可以使用

=SUM(COLUMN(Sheet2!A$2:D$99)*(Sheet2!A$2:D$99=B2))

查找出现区号（在 中）的列号（在 Sheet 2 中B2）。（将其替换99为 Sheet 2 中最后一行的编号，其中有区号。）这是数组公式；您必须在输入（或粘贴）后输入++ Ctrl。 这将创建一个虚拟数组，其中每个单元格包含列号乘以单元格的值是否等于 的真值，如下所示：ShiftEnterB2

1*(Sheet2!A2=Sheet1!B2)   2*(Sheet2!B2=Sheet1!B2)   3*(Sheet2!C2=Sheet1!B2)   4*(Sheet2!D2=Sheet1!B2)
1*(Sheet2!A3=Sheet1!B2)   2*(Sheet2!B3=Sheet1!B2)   3*(Sheet2!C3=Sheet1!B2)   4*(Sheet2!D3=Sheet1!B2)
1*(Sheet2!A4=Sheet1!B2)   2*(Sheet2!B4=Sheet1!B2)   3*(Sheet2!C4=Sheet1!B2)   4*(Sheet2!D4=Sheet1!B2)
            ︙                        ︙                        ︙                        ︙

例如，如果Sheet1!B2是 303，则变为

1*(201=303)               2*(205=303)               3*(208=303)               4*(206=303)
1*(202=303)               2*(210=303)               3*(303=303)               4*(209=303)
1*(203=303)               2*(214=303)               3*(307=303)               4*(213=303)
      ︙                        ︙                        ︙                        ︙

IE，

1*假 2*假 3*假 4*假
1*假 2*假 3*真的                    4*错误
1*假 2*假 3*假 4*假
   ︙ ︙ ︙ ︙

由于 TRUE 为 1，FALSE 为 0，因此简化为

   0 0 0 0
   0 0 →   3   ← 0
   0 0 0 0
︙ ︙ ︙ ︙ ︙

当然，其中的SUM是 3，这是 303 所在的列的编号。

如果您确定工作表 1 中的每个区号列B 都存在于工作表 2 中，那么您只需将列号插入函数即可获取时区名称INDEX：

=INDEX(Sheet2!A$1:D$1, 1, SUM(COLUMN(Sheet2!A$2:D$99)*(Sheet2!A$2:D$99=B2)))

（记得使用Ctrl++ ）但如果 Sheet 1 中的区号在 Sheet 2 中不存在，则每个单元Shift格的 均为 FALSE，因此虚拟数组全为零， 为0， 可能会返回一些误导性的内容。要处理这种情况，请设置为EnterSheet2!whatever=B2SUMINDEXZ2

=SUM(COLUMN(Sheet2!A$2:D$99)*(Sheet2!A$2:D$99=B2))

（记得使用Ctrl++ Shift）Enter并设置C2为

=IF(Z2=0, "Not Found", INDEX(Sheet2!A$1:D$1, 1, Z2))

相关内容