Архив рубрики: Excel

СУММПРОИЗВ () + ОСТАТ () сумма и количество четных и нечетных чисел в диапазоне

=СУММПРОИЗВ(—(ОСТАТ(диапазон чисел;2)=1))

Подсчет ячеек, содержащих числа

Если вам нужно подсчитать ячейки, содержащие только нечетные числа, вы можете использовать формулу, основанную на функция СУММПРОИЗВ вместе с функцией ОСТАТ.

В примере, показанном формула в ячейке G7:

=СУММПРОИЗВ(D7:D18;(—(ОСТАТ(D7:D18;2)=0)))

Эта формула возвращает значение 7,так как есть 7 нечетных чисел в диапазоне D7:D18.

Как эта формула работает

Функция СУММПРОИЗВ напрямую работает с массивами.

В Функции СУММПРОИЗВ довольно легко можно отфильтровать нужные нам нечетные числа используя второй массив в виде критерия или фильтра определяющего четные и нечетные числа, затем перевести полученные значения истины и ложь в единицы и нули (—двойным минусом)  затем перемножить массивы  и просуммировать. 

В этом случае, мы определяем нечетное число, с помощью функции ОСТАТ ():

—(ОСТАТ(D7:D18;2)=0))

ОСТАТ () возвращает остаток после деления. В данном случае делитель равен 2, поэтому ОСТАТ () будет возвращать остаток 1 на любое нечетное целое число, и остаток нулю для четных чисел.

Функции СУММПРОИЗВ , перемножит два массива 

первый массив состоит из ряда чисел;

{(12,113,12434,15,9,87,6,7,56,4,3,1)}

второй массив состоит из истины и ложь которые мы переведем в единицы и нули с помощью двойного минуса или двойное отрицание «—«:

—{(ложь,истина,ложь,истина,истина,истина,ложь,истина,истина)}

вот что у нас получилось

{(0,1,0,1,1,1,0,1,0,1,1)}

Далее функция СУММПРОИЗВ  () перемножает массивы получая массив с рядом чисел которые соответствует нечетным в виде:

{(0,113,0,15,9,87,0,7,0,0,3,1)}

Последнее действие функции СУММПРОИЗВ  () суммирует все числа массива и выводит результат : 235

А количество можно посчитав преобразовав первый массив в нули и единицы с помощью функции ЕЧИСЛО ()  как в формуле:

=СУММПРОИЗВ(—(ЕЧИСЛО(D7:D18));(—(ОСТАТ(D7:D18;2)=0)))

Функция ЕЧИСЛО () определяет является ли текст в ячейки числом или нет в результате выдает 0 или 1 .

Получаем простой массив в виде единиц и нулей

{1;1;1;1;1;1;1;1;1;1;1;1}

который аналогично как в предыдущем примере перемножается  со вторым массивом и получаем сумму конечного массива то есть количество нечетных чисел.

СУММПРОИЗВ () + ОСТАТ () сумма и количество четных и нечетных чисел в диапазоне