我们原来的工资系统在一行单元格(例如 A1:AE1)中报告每月的每一天,即 1 到 31。在每一天的下面,都会报告一个空单元格或一个两位数。空单元格表示员工当天根本没有上班。由于我们是连续上两个班次,所以系统会报告两位数。没有空格隔开的两位数作为一个整体没有意义;每个数字必须单独解释。每个数字都限制为 0 到 5;可以是 22、33、50、32 或 40。0 到 5 的范围用于编纂小时工资率。2 表示 14 美元/小时,而 3 表示 16 美元/小时。它是每个工资率(0 到 5)的所有实例的总和,我想统计但一直没能统计出来。这样做的目的是找出员工以一定的小时工资率工作了多少班次(而不是天数)。这些记录将在 AE 列的右侧报告。
COUNTIF(A1:AE1,"2") 计数 2 for {..., 50, 23, 32, ...} -- 所需计数
COUNTIF(A1:AE1,"2“)对 {..., 22, 23, 32, ...} 计算结果为 3 -- 希望得到 4
我曾尝试修改 SUMPRODUCT,但并不太成功。
下图(新手无法发帖)显示,2013 年 11 月 14 日至 18 日期间,小时工资 2 出现了 2 次,小时工资 3 出现了 8 次,即 {23, 33, 33, 33, 23}。虽然使用的 COUNTIF 公式也报告了 2 次小时工资 2,但只报告了 5 次小时工资 3。
答案1
尝试使用SUBSTITUTE
在单个单元格内进行计数,然后将其相加SUMPRODUCT
:
=SUMPRODUCT((LEN(A1:AE1)-LEN(SUBSTITUTE(A1:AE1,"2",""))))
以上是统计2
。你可以将 2 改为 3 来统计 s 3
:
=SUMPRODUCT((LEN(A1:AE1)-LEN(SUBSTITUTE(A1:AE1,"3",""))))
^
LEN(A1:AE1)-LEN(SUBSTITUTE(A1:AE1,"3",""))
3
给出每个特定单元格中每个单元格的数量。
逻辑是从单元格的长度中减去没有 3 的单元格的长度。
答案2
创建一个 UDF(用户定义函数),并使用它来进行计数。不要在单元格中输入 =COUNTIF(A1:AE1,"2"),而要输入 =TALLYPAYRATES(A1:AE1,"2")
按查看/宏/查看宏
在宏名称中,输入 TALLYPAYRATES,然后按创建。
将这两行替换为以下内容:
Function TALLYPAYRATES(rng As Range, sRate As String) As Long
Dim nOccurs As Long
Dim aCell As Range
Dim sCellRate As String
nOccurs = 0
For Each aCell In rng
sCellRate = Format(aCell, "00")
If Left(sCellRate, 1) = sRate Then
nOccurs = nOccurs + 1
End If
If Right(sCellRate, 1) = sRate Then
nOccurs = nOccurs + 1
End If
Next aCell
TALLYPAYRATES = nOccurs
End Function
然后在某些单元格中添加类似以下内容
=TALLYPAYRATES(A1:F1,“2”)
=TALLYPAYRATES(A1:F1,"3")
以下数据 ------- 给出了这些结果
22 33 50 32 40 计数 3 3
50 23 32 00 00 计数 2 2
22 23 32 00 00 计数 4 2
23 33 33 33 23 计数 2 8
答案3
另一种方法如下:
=COUNT(IF(VALUE(MID($A$1:$AE$1,SEQUENCE(2),1)) = COLUMN()-COLUMN($AF$1:$AF$1), VALUE(MID($A$1:$AE$1,SEQUENCE(2),1)), "a") )
它将每个单元格分成两个数组元素,并将它们全部组成一个完整数组。然后将它们设为值,而不是文本。然后IF()
检查每个数组元素的值,并将文本字符放置在任何不等于该值的元素的位置。COUNT()
然后计算剩余的数值,忽略那些文本字符元素。
计算结果是什么COLUMN()-COLUMN($AF$1:$AF$1)
?它从 0 开始,随着您复制并粘贴到五个单元格中,它上升到 5,以使其处于范围内AF:AK
。
(无论出于什么原因,我目前无法将其作为Spill
公式,因此需要复制和粘贴。)
关键要素是COUNT()
价值观和文本之间的区别。
LET()
它看起来又长又乱,但人们可以通过或(如果愿意或不可用的话)命名范围来修饰这个反对意见。
(当然,在 2014 年它就需要 CSE 条目,并且SEQUENCE()
需要替换,可能用数组常量({0,1,2,3,4,5}
)替换。这个答案不仅是为了提出一种不同的方式,而且还要更多地利用 2021 年后的功能。
在未来一年半的某个时间段内,随着我们大多数人最终可以使用新的文本/数组公式,这将变得轻而易举。但在此之前……对于旧版安装……)