☰

计算单元格中共享元素的数量

tag-icon • tag-icon libreoffice-calc
计算单元格中共享元素的数量

我正在努力获取一个计算公式来进行以下计算：

        A B D   G H A
A B C    2     1
A D E    2     1
A F G    1     2
B H I    1     1

就像示例中那样，我需要公式来比较两个单元格，并告诉我它们有多少个共同元素 - 无需我说明这两个单元格中有哪些元素，尽管它们始终是字母表中的字母，并且任何给定的字母都不会在同一个单元格中重复 -。为了说明起见，示例分别比较了 B1 与 A2、A3、A4 和 A5，以及 C1 与 A2、A3、A4 和 A5。

关于如何使用 LibreOffice 实现这一点，您有什么想法吗？谢谢！

答案1

如果第 1 行单元格中始终有 3 个字母，则可以使用：

=IF(ISNUMBER(SEARCH(MID(B$1;1;1);$A2));1;0) + IF(ISNUMBER(SEARCH(MID(B$1;2;1);$A2));1;0) + IF(ISNUMBER(SEARCH(MID(B$1;3;1);$A2));1;0)

在单元格 B2 中复制/粘贴到其他单元格（用于修复列/行的 $ 已经在公式中）

有 3 个 IF，每个字母对应一个。如果找到该字母，结果为数字，IF 将其替换为 1，如果没有找到，则替换为 0，并将 3 个 IF 的结果相加。

如果某一列中有更多字母，则仅向该列的公式添加一个 IF。

第 1 列中的字母数量可以改变，而公式不会发生任何变化。

如果第 1 行的字母之间有空格，如A B D' instead ofABD`，请将 MID 函数第 2 个参数中的 1、2 和 3 更改为 1、3 和 5。

结果

答案2

如果第 1 行的单元格中始终有三个字母，则可以使用：

=SUM(--ISNUMBER(SEARCH(MID(B$1,{1,2,3},1),$A2)))

这是 laurent 答案的渐进式演化。我做了一些优化。首先，

IF (Boolean_value, 1 , 0)

有点多余，因为 TRUE 的值为 1，FALSE 的值为 0。布尔值可以通过前缀轻松转换为等效整数--；缩写为- ( - (value) )，这会保留数值，但会将类型强制为整数。然后是{1,2,3}一个数组，使用SUM函数可以消除添加三个几乎相同的项的需要。

要处理第 1 行中长度不同的字符串，请使用

=SUM(--ISNUMBER(SEARCH(MID(B$1,ROW(INDIRECT("1:"&LEN(B$1))),1),$A2)))

这类似于第一个答案，只不过我们获取了列顶部字符串的长度，LEN(B$1)并将其连接到字符串“ 1:”，形成类似“ 1:3”的内容。这看起来像是行的范围，函数INDIRECT就是这样处理的，返回地址范围$1:$3。然后ROW()返回数组{1,2,3}。这是一个技巧，可以创建一个连续数字数组，其开头和结尾不是预先确定的。

Ctrl上面的公式必须用+ Shift+输入Enter。我不确定为什么必须这样，而第一个却不用。

您的示例/插图使字符串看起来像是嵌入了空格。如果这是真的（但您不想计算空格），则公式变为

=SUMPRODUCT(--(MID(B$1,ROW(INDIRECT("1:"&LEN(B$1))),1)<>" "),--ISNUMBER(SEARCH(MID(B$1,ROW(INDIRECT("1:"&LEN(B$1))),1),$A2)))

Ctrl也必须用+ Shift+输入Enter。

相关内容