同一单元格中两位数和个位数的实例总数。

同一单元格中两位数和个位数的实例总数。

我们原来的工资系统在一行单元格(例如 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 年后的功能。

在未来一年半的某个时间段内,随着我们大多数人最终可以使用新的文本/数组公式,这将变得轻而易举。但在此之前……对于旧版安装……)

相关内容