СУММПРОИЗВ () подсчет разных наборов продуктаов

СУММПРОИЗВ () подсчет разных наборов продуктаов

Общая формула

=Функция СУММПРОИЗВ(Стоимость,—(диапазон=»х»))

Объяснение

Для подсчета стоимости разных наборов продуктов используем критерий «X» , чтобы включить или исключить продукты из набора, используйте функцию СУММПРОИЗВ как в ячейки E14:

=Функция СУММПРОИЗВ($D$7:$D$12;—(E$7:E$12=»X»))

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

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

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

$D$7:$D$12

Обратите внимание на диапазон он является абсолютным, чтобы предотвратить изменения, так как формула копируется вправо. В этот массиве следующие значения:

{120;470;70;20;500;230}

Второй массив создается с помощью такого выражения:

—(E$7:E$12=»X»)

Результат E$7:E$12=»X» — это массив истинных и ложных логических значений вроде этого:

{ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА}

Двойной минус (—) преобразует эти истинные ложные значения в 1-ы и 0-и:

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

Так, в формуле содержится два массива вот что получилось:

=Функция СУММПРОИЗВ ({120;470;70;20;500;230},{1;0;1;0;0;1})

Затем функция СУММПРОИЗВ перемножает соответствующие элементы массивов и суммирует все произведения:

=Функция СУММПРОИЗВ ({120;0;70;0;0;230})

и возвращает сумму произведений, 420 .

Фактически, второй массив действует как фильтр для значений в первом массиве.

СУММПРОИЗВ () подсчет разных наборов продуктов

РАБДЕНЬ () определить предыдущий день и следующий рабочий день до и после выходных
РАБДЕНЬ () определить предыдущий день и следующий рабочий день до и после выходных

Общая формула

=РАБДЕНЬ(Нач. Дата; Количество дней)

Функция РАБДЕНЬ возвращает дату рабочего дня исключая выходные и праздники. Функцию РАБДЕНЬ удобно использовать для вычисления дат платежей, ожидаемых дат доставки или количества фактически отработанных дней.

Объяснение

Используя функцию РАБДЕНЬ найдем даты предшествующие и последующие выходным, применив во втором аргументе минус и плюс единицу также добавив и отняв от начальной даты по единицы для определения попал-ли этот день на выходной , формула в D7 и E7 :

Прибавим к начальной дате единицу а аргумент поставим -1-н день от начальной даты, таким образом функция РАБДЕНЬ вернет предыдущий день ПЯТНИЦУ.

«D7» =РАБДЕНЬ(C7+1;-1)

Отняв от начальной даты единицу а в аргументе указать 1-цу к начальной дате, таким образом функция РАБДЕНЬ вернет следующий день ПОНЕДЕЛЬНИК.


«E7» =РАБДЕНЬ(C7-1;1)

аргумент 1 возвращает следующий день.

А если вычисляемая дата не попадает на Субботу и Воскресение тогда происходит взаимозачет и выходит изначальная дата.

Определить предыдущий рабочий день используя ЕСЛИ и ДЕНЬНЕД
Определить предыдущий рабочий день используя ЕСЛИ и ДЕНЬНЕД

Общая формула

=ЕСЛИ(ДЕНЬНЕД(C7;2)=6;C7-1;ЕСЛИ(ДЕНЬНЕД(C7;2)=7;C7-2;C7))

Предыдущий рабочий день ищем с помощью функции
ДЕНЬНЕД и ЕСЛИ. ДЕНЬНЕД определяет что за день недели у нас на определенную дату а затем используем ЕСЛИ для исключения Субботы и Воскресения, эти дни недели имеют свои индексы 6 и 7 в списке дней недели. Так же можно указать свою собственную нумерацию недели выбрав другой режим нумерации недели вторым критерием в функции ДЕНЬНЕД .

В показанном примере, формула в D7:

=ЕСЛИ(ДЕНЬНЕД(C7;2)=6;C7-1;ЕСЛИ(ДЕНЬНЕД(C7;2)=7;C7-2;C7))

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

Функция ДЕНЬНЕД показывает какой день недели выбран за дату
ДЕНЬНЕД(C7;2) и если дата выпадает на субботуЕСЛИ(ДЕНЬНЕД(C7;2)=6 или на воскресенье ЕСЛИ(ДЕНЬНЕД(C7;2)=7 тогда от исходной даты отнимаем 1-н день C7-1; или 2-ва два дня C7-2; в результате мы получаем всегда рабочий день или предыдущий рабочий день если выпадает выходной.

ДАТА у СЕГОДНЯ () склеить или ОБЪЕДИНИТЬ() с ТЕКСТ ом

Общая формула

=D7&ТЕКСТ(C7;»ГГГГ.ММ.ДД»)

=ОБЪЕДИНИТЬ(» «;1;E7:E10)

Объяснение

Для объединения (склеивания) используется символ конкатенации «&» а так же можно использовать функцию ОБЪЕДИНИТЬ(Разделитель; 0 или 1; ячейки или диапазон для склеивания, массив) , формула в E7:

=D7&ТЕКСТ(C7;»ГГГГ.ММ.ДД»)

Как работает конкатенация:

Пользоваться склеиванием или конкатенацией очень просто, можно объединять ячейки, диапазоны подставив символ «&» конкатенации в формулу между объединяемыми ячейками или диапазонами столбцов или строк.

Так же есть функция ОБЪЕДИНИТЬ (), расширяет возможности склеивания «&», позволяет установить любой разделитель и игнорировать пробелы или нет в тексте.

Основная формула:

=ОБЪЕДИНИТЬ(» ! «;1;D11;ТЕКСТ(C11;»ГГГГ.ММММ.ДДДД»))

Как работает:

В Функции ОБЪЕДИНИТЬ() указываем разделитель, в кавычках указываем нужный символ являющимся разделителем можно и не указывать тогда все будет объединено без разделения. Указав второй критерий «ИСТИНА» то формула будет игнорировать пустые ячейки далее указываем ячейки с текстом которые нам надо объединить(или массив или диапазон)

Получить Значение по ИНДЕКС у из массива констант внутри формулы
Получить Значение по ИНДЕКС у из массива констант внутри формулы

Основная формула:

=ИНДЕКС({«пн»:»вт»:»ср»:»чт»:»пт»:»сб»:»вс»};C7)

Возвращает значение элемента в массиве, выбранном с помощью индекса строки.

Ищем что находится по третьему индексу в массиве.

Массив с ЕСЛИ и МАКС

Общая формула

=МАКС(ЕСЛИ($C$6:$C$17=F8;$D$6:$D$17))

Объяснение

Для нахождения максимальной суммы продажи по имени продавца можно использовать формулу массива, которая использует совместно функцию МАКС и ЕСЛИ. В показанном примере, формула в G8:

=МАКС(ЕСЛИ($C$6:$C$17=F8;$D$6:$D$17))

Примечание: это формула массива и должно вводиться с помощью Control + Shift + ввод.

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

Функция МАКС не имеет встроенного способа использования критерия поиска. Для этого применяем функцию ЕСЛИ и укажем критерий по диапазону с именами продавцов.


ЕСЛИ($C$6:$C$17=F8;$D$6:$D$17)

Это выражение сравнивает каждое значение в  диапазоне $C$6:$C$17  по отношению к F8 («Даниил»). В результате получается массив истинных и ложных значений вроде этого:

{ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ; ИСТИНА;ЛОЖЬ;ЛОЖЬ; }

Каждое истинное значение соответствует имени «Даниил» . Функция проверяет значения истина, ложь и в результате возвращает соответствующее «истина» значения из диапазона $D$6:$D$17. Конечный результат, это

{354;ЛОЖЬ; ЛОЖЬ;2348;ЛОЖЬ; ЛОЖЬ; 43476;ЛОЖЬ; ЛОЖЬ; 235;ЛОЖЬ; ЛОЖЬ;}

Обратите внимание что значения других имен ложные. Этот массив возвращает «МАКС» максимальное число по критерию «Даниил» , который автоматически игнорирует ложные значения  и возвращает максимальное число, 43476.

Примечание: Формулы должны быть вписаны с помощью Ctrl + Shift + ввод.

Дополнительные критерии

Чтобы применить более одного критерия, можно вложить другой, если в первом случае, если:

{=МАКС(если(условие1,если(условие2 данные)))}

Такая конструкция работает : МИН, СУММ, делением, произведением и с другими арифметическими операциями и формулами.

Массив с ЕСЛИ и МЕДИАНА

Массив с ЕСЛИ и МЕДИАНА

Общая формула

{=МЕДИАНА(ЕСЛИ($C$6:$C$17=F8;$D$6:$D$17))}

Объяснение

Для расчета медианы на основе одного или нескольких критериев можно использовать формулу массива, которая использует функции МЕДИАНА и ЕСЛИ. В показанном примере, формула в G8:

{=МЕДИАНА(ЕСЛИ($C$6:$C$17=F8;$D$6:$D$17))}

Примечание: формула массива должна вводиться с помощью Control + Shift + ввод.

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

Функция медиана не имеет встроенного способа использования критерия. Стандартная функция  возвращать медиану (среднее) число в этом диапазоне.

Для использования критерия, мы используем функции ЕСЛИ внутри медианный.

ЕСЛИ($C$6:$C$17=F8;$D$6:$D$17)

Это выражение сравнивает каждое значение в  диапазоне $C$6:$C$17  по отношению к F8 («А»). В результате получается массив истинных и ложных значений вроде этого:

{ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;}

Каждое истинное значение соответствует значению А. Функция  сравнивает  и возвращает  соответствующие «ИСТИНА» значения из диапазона $D$6:$D$17. Конечный результат, это

{35;ЛОЖЬ;78;ЛОЖЬ;867;ЛОЖЬ;434;ЛОЖЬ;98;ЛОЖЬ;12;ЛОЖЬ}

Обратите внимание что значения группы Б сейчас ложные. Этот массив возвращает МЕДИАНУ по критерию А , который автоматически игнорирует ложные значения  и возвращает среднее значение, 88.

Примечание: Формулы должны быть вписаны с помощью Control + Shift + ввод.

Дополнительные критерии

Чтобы применить более одного критерия, можно вложить другой, если в первом случае, если:

{=Медиана(если(условие1,если(условие2 данные)))}

Массив с ЕСЛИ и МЕДИАНА EXCEL DEVSAP

Таблица умножения массив excel

Общая формула

={C6:C15*D5:M5}

Ctrl +Shift + Enter

Этот способ позволяет перемножить значение столбца и строки одной формулой используя массив.

Как это работает:

Предварительно выделяете диапазон ячеек в котором будет отображаться результат в нашем случае это D6:M15 . Оставив выделенный диапазон пишем формулу диапазон C6:C15 столбца умножить на диапазон D5:M5 строки и для корректной работы используем комбинацию клавиш ввода
Ctrl +Shift + Enter

Посещаемости с СУММЕСЛИ_Devsap_Excel

Посещаемости с СУММЕСЛИ

Общая формула

=СЧЁТЕСЛИ(C6:I6;»Вых»)

Один из способов отслеживать посещаемость написать простую формулы на основе функции СЧЕТЕСЛИ. В показанном примере, формула, на J6:

=СЧЁТЕСЛИ(C6:I6;»Вых»)

Эта формула использует СЧЕТЕСЛИ с критериями «Вых» при совпадении значений в ячейки  и критерия подсчитывает количество совпадений а вторая формула подсчитывает пустые ячейки:

=СЧЁТЕСЛИ(C6:I6;»Вых»)  — поиск «Вых»

Подсчет пустых клеток на листе используется СЧЕТЕСЛИ  :

=СЧЁТЕСЛИ(C6:I6;»») —  поиск «пустых»

Посещаемости с СУММЕСЛИ devsap

Топ СЧЁТЕСЛИ РАНГ

Топ СЧЁТЕСЛИ РАНГ в EXCEL

Топ СЧЁТЕСЛИ РАНГ в EXCEL

Общая формула

=РАНГ(C5;$C$5:$C$11)+СЧЁТЕСЛИ($C$5:C5;C5)-1

Для сортировки данных в диапазоне, который содержит только числовые значения, вы можете использовать функции РАНГ и СЧЕТЕСЛИ. В показанном примере, формула в F5:

=РАНГ(C5;$C$5:$C$11)+СЧЁТЕСЛИ($C$5:C5;C5)-1

где «Рейтинг» — это диапазон С5:С12

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

Ядром этой формулы является функцией ранжирования, которая используется для создания ранга , где наибольшему значению присваивается первое место «1»:

=РАНГ(C5;$C$5:$C$11)

Здесь, ранг использует  диапазон Рейтинг (С5:С12) для удобства. По умолчанию, значение будет присваиваться от 1 к большему, 2 на вторую, и так далее. Это прекрасно работает до тех пор, пока числовые значения являются уникальными. Однако, для обработки числовых значений, которые содержат повторяющиеся значения,  нужно использовать функцию СЧЕТЕСЛИ, чтобы сохранить последовательность нумерации. Это делается путем добавления возвращаемое звание:

=СЧЁТЕСЛИ($C$5:C5;C5)-1

Обратите внимание на ссылку , в которой меняется диапазон $C$5:C5 , поскольку формула копируется вниз в таблице.  Выражение будет возвращать ноль 0 для каждого числового значения, пока не встретится дубликат. Выражение возвращает 1  если встречается первый дубликат , в третьем случае, он будет возвращать 2, и так далее.

Как только формула посчитает, данные могут быть отсортированы по столбцу помощником.

ФАЙЛ Топ_СЧЁТЕСЛИ _РАНГ_в_EXCEL_devsap