☰

在 Excel 中使用多个条件对多列进行计数

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
在 Excel 中使用多个条件对多列进行计数

我的数据排列如下

    （C）     （四）     （英）     （F）     （G） 
4 高；平均 前高后低 低的 1.1、1.2 2.4、5.6
5 前高后低 低的 平均的 1.1.2， 1.1、1.3
6 平均；低 2.4、1.2.3 1.2、5.6
7 高的; 高的 平均的 1.1、1.2 3.4, 4.5

我需要计算“高”、“平均”和“低”在“1.1”前面出现的次数（对于所有行），然后对所有数字执行相同操作。将结果排列在类似这样的表格中（我已经对这个表格的标题进行了排序。需要帮助进行计数）。

高的 平均的 低的
 1.1 5 3 4
 1.2 ⋱
 1.3 ⋱
 2.4 ⋱
 ︙
 ︙

示例中的计数是手动完成的。实际表的尺寸为 200×8。我试过了countifs(C4:E7, "*High*", F4:G7, "1.2")。它给出了一个错误。如果我将两部分数据分别合并到一列中，它不会给出所需的结果。我无法使用，因为数据中也*1.2*有。1.2.3

再一次尝试：使用countif我已经创建此表的行：

（我） （日） （金） （左） （男）
1
2
3 高的 低的 平均的
4 2 2 1 1.1、1.2 2.4、5.6
5 1 2 1 1.1.2， 1.1、1.3
6 0 1 0 2.4、1.2.3 1.2、5.6
7 2 0 1 1.1、1.2 3.4, 4.5

然后给出公式sumif(L4:M7, "*1.2*", I4:I7)。它给出的计数6。我不明白这一点。如果使用，它会给出零。"1.2"理想情况下，我希望任何公式都将条件"1.2"作为目标表中的单元格引用，例如C12。

答案1

这太复杂了。我希望有人能找到一个好方法来简化我的答案。

首先，让我陈述一些假设。让我们将数据表描述为维度（n+米)×升 —n 一组中的列，后面跟着一组米 所有柱子都垂直延伸升 行。例如，问题中的表格是 (3+2)×4。第一行中的单元格n 列包含字符串列表（例如“High”、“Average”和“Low”，可能还有其他字符串），以“;␣”（其中“␣”表示空格）分隔。我们将这些字符串称为“单词”。其余单元格米 列包含字符串列表（例如“1.1”、“1.2”、“3.4”、“5.6”、“1.2.3”以及可能还有很多其他字符串），以“,␣”分隔。我们将这些字符串称为“数字”。

最右边永远不会有“高”、“平均”、“低”或任何其他词，或分号（';'）。米 列，最左边也不会有数字值或逗号（'，'）n 列。单元格可能包含单个值，并且可能以该列的适当分隔符结尾。单元格不会多次包含相同的值（例如，“1.1、1.2、1.1”），或者至少如果包含，则仅计算一次。

问题是如何计算同一行中给定单词和给定数字的所有对。一个值（即一个单元格）可能计入多对。例如，单词“High”和数字“1.1”仅在这一行中就一起出现了六 (6) 次：

（C） （四） （英） （F） （G）
  高的    高的; 世界   胡扯;高的    1.1、6、8      42，1.1   

因为我们将 C+F、C+G、D+F、D+G、E+F 和 E+G 算作六对不同的数字。然后我们需要将所有行的计数相加。

创建（重复数据删除）列表 全部单词和数字不是问题的一部分；OP 已经处理了这个问题。

COUNTIFS确实看起来是一个合适的工具。而且，由于单元格包含值列表，因此使用诸如 之类的模式是合适的*High*。

换句话说我的答案到Excel：在文本中查找文本而不查找更长的文本（超字符串）：

检查“1.0,1.1,1.2” 中却找不到“6.8,42，1.2.3”是一个常见问题。常见的解决方案是添加“，”（逗号）位于两个字符串的开头和结尾：“，1.2，“ （和 ”，1.0，“ 和 ”，1.1，) 出现在“，1.0,1.1,1.2，“ 但不是 ”，6.8,42,1.2.3，”。 在你的情况下，我们必须使用“， “（逗号+空格），因为这是您的分隔符。

在那个问题中，使用FIND()，可以就地进行前置和后置。在这种情况下，这似乎是不可能的。我的解决方案是复制数据，并添加分隔符。具体来说，我把它放到 Sheet2 上。对于从C4(on Sheet1) 开始的 (3+2)×4 表，输入 (on Sheet2)：

  • C4→="; " & Sheet1!C4 & ";"
  • D4→="; " & Sheet1!D4 & ";"
  • E4→="; " & Sheet1!E4 & ";"
  • F4→=", " & Sheet1!F4 & ","
  • G4→=", " & Sheet1!G4 & ","

并向下拖动/填充以覆盖所有数据（即全部 200 行）。请注意，根据您的数据，C和D使用 E分号，而F 和 G使用逗号。

现在转到您已有的结果矩阵（如问题中所示） - 我假设左上角是 H1- 然后输入 I2：

=COUNTIFS(Sheet2!$C$4:$C$7, "*; " & I$1 & ";*", Sheet2!$F$4:$F$7, "*, " & $H2 & ",*")
+COUNTIFS(Sheet2!$C$4:$C$7, "*; " & I$1 & ";*", Sheet2!$G$4:$G$7, "*, " & $H2 & ",*")
+COUNTIFS(Sheet2!$D$4:$D$7, "*; " & I$1 & ";*", Sheet2!$F$4:$F$7, "*, " & $H2 & ",*")
+COUNTIFS(Sheet2!$D$4:$D$7, "*; " & I$1 & ";*", Sheet2!$G$4:$G$7, "*, " & $H2 & ",*")
+COUNTIFS(Sheet2!$E$4:$E$7, "*; " & I$1 & ";*", Sheet2!$F$4:$F$7, "*, " & $H2 & ",*")
+COUNTIFS(Sheet2!$E$4:$E$7, "*; " & I$1 & ";*", Sheet2!$G$4:$G$7, "*, " & $H2 & ",*")

根据需要调整数据。（例如，将其替换 7 为数据表底部的行号（或一个非常大的数字）以允许扩展。）

我希望这个逻辑是显而易见的。第一行计算列 C 匹配的行数*;␣High;*——换句话说，单词列表包含High—— 和列中的数字列表 F包括1.1。然后我们检查其他五种组合（C+G、D+F、D+G、E+F 和 E+G）。

然后拖动/填充以覆盖所有单词（在行中 1）和所有数字（在列中 H）。

            结果

这看起来似乎需要输入很多内容，但还是可以接受的。首先输入

'+COUNTIFS(Sheet2!$C$4:$C$7, "*; " & I$1 & ";*", Sheet2!$F$4:$F$7, "*, " & $H2 & ",*")

替换F为第二组中的第一列（并按7上述方法替换）。然后制作米−1（额外）该公式片段的副本（从 开始 +），并适当调整第二列名称。例如，上面，我在第二行中将其更改F为。然后制作Gn−1（额外）份米行（同样从 开始 +）并适当调整第一列名称。例如，上面我在第三行和第四行中将 C其更改为，在最后两行中将其更改为。然后返回并将 更改为。DE'+=

答案2

Excel 不太擅长处理像您的示例这样的结构化数据。
如另一个答案中所述，可以编写一些复杂的公式，但如果您尝试将其应用于真实（更大）数据，它将极其复杂。

要高效工作，第一步就是转换数据。Excel 中可用的工具有 VBA 和 Power Query。

我将使用 Power Query 为您提供解决方案，因为它对我来说更加用户友好

  1. 最新版本的 Excel 已附带 Power Query。对于 Excel 2010，您需要单独安装它。
  2. 选择您的源数据并单击“来自表格/范围”（它将位于您的 powerQuery 选项卡中） 在此处输入图片描述
  3. 您需要执行以下转换：合并列、拆分列、替换值。您可以在下面找到完整代码，只需更改列名称即可。
    在此处输入图片描述
  4. 一旦数据准备好，您就可以加载它并构建具有所需输出的数据透视表。
    在此处输入图片描述
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"category 1", type text}, {"category 2", type text}, {"category 3", type text}, {"value 1", type text}, {"value 2", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"category 1", "category 2", "category 3"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Categories"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"value 1", "value 2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Values"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns1", {{"Categories", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Categories"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Categories", type text}}),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"Values", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Values"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Values", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type2"," ","",Replacer.ReplaceText,{"Values"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," ","",Replacer.ReplaceText,{"Categories"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value1", each ([Categories] <> "") and ([Values] <> ""))
in
    #"Filtered Rows"

相关内容