![Excel - 检查一个主单元格内是否存在多个单元格的内容](https://linux22.com/image/1551765/Excel%20-%20%E6%A3%80%E6%9F%A5%E4%B8%80%E4%B8%AA%E4%B8%BB%E5%8D%95%E5%85%83%E6%A0%BC%E5%86%85%E6%98%AF%E5%90%A6%E5%AD%98%E5%9C%A8%E5%A4%9A%E4%B8%AA%E5%8D%95%E5%85%83%E6%A0%BC%E7%9A%84%E5%86%85%E5%AE%B9.png)
我有一份 Excel 电子表格。A 列中有一些单词。在某些情况下,一个单元格中有多个单词,以小数点(句点)分隔;例如,university.of.california
或school.house
。第一个点之后的任何内容(包括点本身)都应被忽略;例如,university.of.california
应被视为只是university
。
B 至 R 列中有三字母组合,即每组三个字母。但这些列中也有空白单元格。
我想检查 B 列至 R 列中的三字母组合是否出现在同一行 A 列的(第一个)单词中。例如,如果某行的 AF 列包含
university.of.california
、cal
、rev
、sit
和,uni
则uny
该行应计为 2,因为uni
和sit
出现在 内university
。
cal
不算数是因为california
位于句点之后,
rev
不算数是因为ver
顺序错误,不算数是因为、 和字母uny
未 出现u
n
y
一起在university
。
我希望每行的 U 列指示该行 B 到 R 列中与 A 列中第一个单词匹配的三字母组合的数量。我该怎么做?
并且在 T 列中使用哪个公式,因此如果 U 等于或高于在该行中找到的 1 个匹配项,则为 TRUE(绿色),如果 U 在该行中为 0,则为 FALSE(不着色)?
这是一个示例数据集。如上所述,A 列至 R 列包含将提供的输入数据。T 列和 U 列包含我希望 Excel 根据该输入创建的结果。在此示例中,单元格 T6 为真,因为“ice”存在于第一个点之前,U6 为 1,因为这是第一个点之前的唯一匹配,而“hou”和“col”仅存在于第一个点之后,因此被忽略(红色)。在照片中,黄色是需要考虑的正确匹配。
+---+--------------------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+---+-------+---+
| | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U |
+---+--------------------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+---+-------+---+
| 1 | university | abd | ity | sfd | fgh | tyu | kjg | sdf | jhg | bnm | hjk | | | | | | | | | TRUE | 1 |
| 2 | school | bnm | sdf | hoo | tyu | kjg | sdf | jhg | ool | hjk | sdf | fgh | tyu | kjg | sch | jhg | bnm | hjk | | TRUE | 3 |
| 3 | college | sdf | fgh | tyu | kjg | sdf | jhg | bnm | sdf | fgh | tyu | kjg | sdf | jhg | bnm | hjk | | | | FALSE | 0 |
| 4 | home | ome | fgh | tyu | kjg | sdf | jhg | | | | | | | | | | | | | TRUE | 1 |
| 5 | nice.colored.house | hou | col | ice | | | | | | | | | | | | | | | | TRUE | 1 |
| 6 | super.market | etr | etr | sdf | fsd | sdf | iuj | mar | ket | | | | | | | | | | | FALSE | 0 |
+---+--------------------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+ +-------+---+
以下是相同的数据(可能包括转录错误),但使用了颜色编码来表示照明,如上所述:
如果可能,公式应该不区分大小写。例如,ooL
和OOL
应该算作匹配school
。
答案1
如果没有辅助细胞,这是非常困难的,甚至是不可能的。很多辅助细胞。幸运的是,这很容易做到和很多辅助细胞。
我的解决方案要求每个实际单元格都有一个辅助单元格,直到第 列 R
。您可以将它们放在 同一行的第 列 中。或者,您可以将它们放在第 11 行到第 16 行或第 101 行到第 106 行的第 列 中。 AA
我 选择 将它们放在不同工作表上的并行单元格中;这有利于以后的扩展。AR
A
R
笔记: 如果您希望稍后能够对数据进行排序,请将辅助单元格放在与主数据相同的工作表上,放在相同的行但(显然)不同的列中(例如,AA
通过 AR
)。
在Sheet2!A1
,输入
=IFERROR(LEFT(Sheet1!A1,SEARCH(".",Sheet1!A1)-1), Sheet1!A1)
Sheet1!A1
这将提取第一个句点(小数点)的值(如果有)。具体来说,它会搜索第一个.
Sheet1!A1
如果找到一个,它会用它来LEFT()
提取它之前的文本;否则,它就取整个值。
在Sheet2!B1
,输入
=IF(AND(Sheet1!B1<>"",NOT(ISERROR(SEARCH(Sheet1!B1, $A1)))), 1, 0)
检查是否Sheet1!B1
不为空,以及是否出现在(小数点前的Sheet2!A1
部分)。如果是,则计算结果为 1;否则计算结果为 0。Sheet1!A1
选择Sheet2!B1
并拖动/填充到右侧,到列 R
。然后选择单元格 A1:R1
并向下拖动/填充到第 6 行。结果如下:
现在剩下的就简单了。在 中Sheet1!U1
,输入
=SUM(Sheet2!B1:R1)
计算第 1 行的匹配项。在 中Sheet1!T1
输入
=U1>0
选择单元格 T1:U1
并向下拖动/填充至第 6 行。就完成了:
如果您想要为单元格着色,可以使用条件格式轻松实现。如果您想要对数据进行排序,并且已将辅助单元格与实际数据放在同一行,则同时选择实际数据和辅助单元格(即 A1:AR6
),然后对整个块进行排序。