Общее количество экземпляров однозначных чисел с двузначными числами с одной и той же ячейкой.

Question

Наша унаследованная система начисления заработной платы сообщает каждый день месяца, т. Е. От 1 до 31, по одному ряду ячеек, например, A1:AE1. Ниже каждый день сообщается о пустой ячейке или двузначном числе. Пустая ячейка означает, что сотрудник не был на работе в тот день. Поскольку мы работаем в две смены, система выдает двузначные числа. Двухзначное число, не разделенное пробелом, не имеет значения в целом; каждая цифра должна интерпретироваться отдельно. Каждая цифра ограничена числами от 0 до 5; и может выглядеть как 22, 33, 50, 32 или 40. Диапазон от 0 до 5 используется для кодирования ставок почасовой оплаты. 2 означает 14 долларов в час, а 3 означает 16 долларов в час. Это сумма всех экземпляров каждой ставки заработной платы, от 0 до 5, которую я пытаюсь подсчитать, но не смог. Смысл этого состоит в том, чтобы узнать, сколько смен, а не дней, сотрудник работал с определенной почасовой ставкой. Эти подсчеты будут сообщены справа от столбца AE.

COUNTIF(A1:AE1, " 2 ") подсчитывает 2 за {..., 50, 23, 32, ...} - желаемое количество

COUNTIF(A1:AE1, " 2 ") подсчитывает 3 за {..., 22, 23, 32, ...} - в надежде получить 4

Я пытался поработать с SUMPRODUCT, но не очень успешно.

На изображении ниже (запрещено публиковать, потому что я новичок), есть 2 случая почасовой ставки 2 и 8 случаев почасовой ставки 3 в период с 14 по 18 ноября 2013 года, то есть {23, 33, 33, 33, 23}. Хотя используемая формула COUNTIF также сообщает о 2 случаях почасовой ставки 2, она сообщает только о 5 случаях почасовой ставки 3.

Answer 1

Попробуйте использовать SUBSTITUTE для подсчета в отдельных ячейках, а затем суммируйте их с помощью SUMPRODUCT:

=SUMPRODUCT((LEN(A1:AE1)-LEN(SUBSTITUTE(A1:AE1,"2",""))))

Выше стоит сосчитать 2 . Вы можете изменить 2 на 3, чтобы считать 3 с:

=SUMPRODUCT((LEN(A1:AE1)-LEN(SUBSTITUTE(A1:AE1,"3",""))))
                                                ^

LEN(A1:AE1)-LEN(SUBSTITUTE(A1:AE1,"3","")) дает номер каждого 3 в каждой конкретной ячейке.

Логика вычитает длину ячейки без 3 из длины ячейки.

Answer 2

Создайте пользовательскую функцию (UDF) и используйте ее для подсчета. Вместо того, чтобы помещать = COUNTIF (A1:AE1, "2") в ячейку, вместо этого ставьте = TALLYPAYRATES (A1:AE1, "2")

Нажмите Просмотр / Макросы / ViewMacros

В поле «Имя макроса» введите 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(А1:F1, "2")

= TALLYPAYRATES(А1: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