☰

如何计算单元格列中的不同值/字符？

tag-icon • tag-icon microsoft-excel-2007 search
如何计算单元格列中的不同值/字符？

我在一个单元格中保存了客户的全名和地址，例如；
Mr Smith, 1 High Street, London NW12 1AB

我的电子表格中有数百个姓名、地址和邮政编码，我需要按姓氏、街道名称和邮政编码进行搜索和筛选。

我正在尝试识别和计算每条街道和邮政编码中的客户数量。例如，我们在 SE12 中有多少客户，我们在 NW12 1AB 中有多少客户？我认为，我的问题是在每个单元格中搜索三种不同的信息。

我还需要在两个电子表格中识别相同的信息。例如，我们在两个电子表格中有多少个史密斯先生。两个电子表格中有多少个位于 High Street 或相同邮政编码的客户。

我不是 Excel 专家，如果您使用非常简单的语言，我不会生气。

答案1

Text to Columns正如 Math 所指出的，如果您将数据转换为单独的列，您可能会拥有更大的灵活性。使用选项卡中的命令可以轻松实现这一点Data，将数据类型设置为Delimited并将分隔符设置为Comma。

话虽如此，按街道或邮政编码获取客户数量，并按特定街道、邮政编码或姓氏筛选客户而不将数据转换为列并不困难。

假设您的数据位于 Sheet1 中的 A2:A500 范围内，并且位于 Sheet2 中的相同范围内，并且两个工作表都位于同一工作簿中。

要计算 Sheet1 中邮政编码为“NW12 1AB”的客户数量，请使用以下公式：

=COUNTIF(Sheet1!$A$2:$A$500, "*NW12 1AB*")

要计算 Sheet1 和 Sheet2 中“Smith”的数量，请使用：

=COUNTIF(Sheet1!$A$2:$A$500,"*Smith*") + COUNTIF(Sheet2!$A$2:$A$500,"*Smith*")

要计算邮政编码“NW12 1AB”中的“Grimes”的数量，请使用：

=COUNTIFS(Sheet1!$A$2:$A$500,"*Grimes*", Sheet1!$A$2:$A$500,"*NW12 1AB*")

要计算 Sheet2 中名为“Salton”或住在“High”街的客户数量，请使用：

=COUNTIF(Sheet2!$A$2:$A$500,"*Salton*") + COUNTIF(Sheet2!$A$2:$A$500,"*High Street*") -
 COUNTIFS(Sheet2!$A$2:$A$500,"*Salton*", Sheet2!$A$2:$A$500,"*High Street*")

要过滤 Sheet1 以获取居住在邮政编码为“NW12 1AB”的 Smiths，

  • 突出显示数据范围 A2:A500 并在功能区选项卡上选择Sort & Filter--> ，FilterHome

  • 从列顶部的下拉菜单中选择Text Filter--> 。Custom Filter

  • 然后将输入框设置为Contains“Smith”和Contains“NW12 1AB”。

使用此方法您只能设置两个条件。

如果您想要过滤列表并且有两个以上的条件（或 And 和 Or 条件的组合），请突出显示数据范围并按下Advanced数据Sort & Filter选项卡的部分。

系统将提示您输入列表范围和条件范围。

由于Advanced过滤器会创建过滤列表的副本，因此系统还会提示您想要将新的过滤列表写入何处。

以下是根据条件范围筛选所有居住在“High Street”的 Smith 列表的示例：

在此处输入图片描述

请注意，使用“*Smith*”作为姓氏 Smith 的模式可能不是最佳选择：正如一位评论者指出的那样，它将匹配像 Smithson（或像 Smith-Jones）这样的姓氏和像 Smithsonian Way 这样的街道地址。

更好的模式是“* Smith, *, *, *”。即使这样，对于名称“Donald Smith, Jr.”也可能产生误报。（该名称还会在使用逗号作为分隔符的文本到列转换中造成问题。）

事实上，Excel 只能识别两个通配符“*”和“？”，而没有正则表达式那样的语义，这意味着需要检查此类匹配的结果是否存在特殊情况。

答案2

如果您不想篡改现有数据，可以使用 Excel 的字符串公式将这三个字段提取到辅助列中。假设客户的全名和地址在单元格中A1，请设置

  • B1–=FIND(",", A1)
  • C1–=TRIM(LEFT(A1, B1-1))
  • D1–=FIND(",", A1, B1+1)
  • E1–=TRIM(MID(A1, B1+1, D1-B1-1))
  • F1–=TRIM(RIGHT(A1, LEN(A1)-D1))

然后，对于您提供的示例数据，您将获得以下结果：

  • B1–9
  • C1–Mr Smith
  • D1–24
  • E1–1 High Street
  • F1–London NW12 1AB

该TRIM()函数会删除字符串开头和结尾的空格。如果没有该函数，如果您的原始数据在逗号后有空格，那么E1和F1开头也会有空格，这可能会影响后续分析。  TRIM()还会将多个空格合并为单个空格；例如，如果您的原始数据为Mr   Smith，那么B1仍然是Mr Smith。

如果某些A1值的逗号少于或多于两个，则可以使用更复杂的解决方案来处理。

相关内容