☰

如何在一行中为不同的文本单元格分配一个数字，然后对该行求和

tag-icon • tag-icon microsoft-excel worksheet-function
如何在一行中为不同的文本单元格分配一个数字，然后对该行求和

我设置了一个电子表格来监控办公室的缺勤情况。列表示每月 1 至 31 天，行表示月份/员工

        Mon Tue Wed Thu Fri

一月 1 2 3 4 Jeanne
Jan
Ben
Ellie
Alice
Stef
Victoria
二月
珍妮
·简
·本·
埃利
·爱丽丝·
斯蒂夫
·维多利亚

缺勤情况列为 AL、AL.5、SL 和 SL.5 = 年假、半天年假、病假和半天病假

我希望能够添加年假和病假的总计列，并创建一个公式来计算每行的总天数和半天数。

我无法让任何公式将 AL.5 和 SL.5 识别为 .5 的值。我尝试添加条件表，但无法使公式正常工作。我目前正在使用 excel 2013。

答案1

这有点像是花招，但假设您的四种休假类型代码是固定的，它应该可以满足您的目的：

Excel 工作表截图

单元格 H2 中的文本是我在单元格 F2 中输入的公式，然后复制到范围 F2:G4 中。第一个 COUNTIF 计算相应“AL”或“SL”代码的出现次数，第二个 COUNTIF 计算“AL.5”或“SL.5”代码的出现次数。我只是将第二个计数乘以 0.5 即可得到您要查找的加权和。

答案2

我将为此使用 Power Query 插件。Power Query 可以从 Excel 表格导入数据。我将使用 Unpivot 命令将每个单元格转换为一行，并使用单个值列保存所有缺席值（例如 AL、SL.5）。然后，我将对该列使用 Split 命令将前 2 个字符（AL、SL）与 .5 值分开。我将将包含前 2 个字符的列重命名为 Absence 类型，并将新列重命名为 Days。然后，我将使用 Replace 命令将 Days 列中的任何空白值替换为 1。然后，我将 Days 列数据类型转换为十进制数。

此时，您可以使用 Group By 命令来汇总数据并计算天数列的总和。我可能只会将查询以详细形式传递到 Excel 数据模型中，然后在其上构建数据透视表。这将为您提供更强大的分析能力，因为您可以自由地求和、过滤、切片和切块等。

相关内容