excel - 如何将 sumif 与 len 和 if 语句一起使用

Question

客户希望在 Excel 中以一种简洁的方式（无辅助列）根据分配给的项目对另一张表上的表中的数据求和

他们将项目编码为“aa 1.1 sap”、“aa 1.1 ar”、“aa 1.2”、...、“aa 1.10”、“aa 1.11”等。只有 1.1 后面有附加字符，但它们希望将所有 1.1、1.2、1.10 等视为一个组。我创建了一个辅助列，只提取数字并在它们上使用 sumif，但他们不想要那个辅助列（非常令人沮丧，因为我可以将它隐藏在表格中）。我尝试了 sumif 与 len 和 if 语句的各种组合，但没有成功。他们也不想要数据透视表。只是一个简单的表格，上面有项目编号（1.1、1.2、1.15）和旁边的总数。我也遇到了 Excel 混淆 1.10 和 1.1 的问题，但我应该能够弄清楚这一点。

Name    Amount  Type
proj13  42  it 1.1 sap
proj14  4444    it 1.1 sap
proj15  566565  it 1.1 ar
proj16  4566    it 1.1 ar
proj8   0.1 it 1.10
proj9   2646    it 1.2
proj10  6546    it 1.2
proj11  46451   it 1.13
proj12  77556   it 1.15
proj13  42  it 1.15

因此，辅助列在 D 列中，公式为=IF(IF(LEN(C2)=7,MID(C2,4,4),MID(C2,4,3))="1.10",1.01,IF(LEN(C2)=7,MID(C2,4,4),MID(C2,4,3)))

（现在我通过将 1.10 转换为 1.01 来作弊）这将返回一个仅显示项目代码中数字的列

并且总数使用=SUMIF(D:D,"="&K2,B:B)其中 K 存储基于数字的值（1.1、1.2、1.3 等）

总数应等于：

1.1     575617
1.10    0.1
1.2     9192
1.13    46451
1.15    77598

我可以通过辅助列得到这个，但我不知道是谁将它全部放入一个单元格中的一个公式中。我试图避免使用 VBA 或宏，因为这也是他们不想要的。

标签： excelexcel-formula

Answer 1

您可以使用SUMIF和通配符。

对于 的特殊情况1.1，请使用空格 + 通配符：

=SUMIF(C:C,"*"&E2&" *",B:B)

对于所有其他情况：

=SUMIF(C:C,"*"&E3,B:B)