ВЫБОР () и СЛУЧМЕЖДУ () Выбор группы случайным образом

=ВЫБОР(СЛУЧМЕЖДУ(1;4);»№1″;»№2″;»№3″;»№4″)

Разбить сотрудников на разные группы случайным образом можно с помощью формул ВЫБОР() и СЛУЧМЕЖДУ().

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

=ВЫБОР(СЛУЧМЕЖДУ(1;4);»№1″;»№2″;»№3″;»№4″)

При копировании строки вниз с формулой , информация будут генерироваться случайным образом с определенной нумерацией  группы (№1,№2,№3,№4) для каждого участника или сотрудника в списке.

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

Функция СЛУЧМЕЖДУ () генерирует случайные числа между двумя числами. A функция ВЫБОР () принимает число в качестве первого аргумента, и использует это число для выбора элемента из последующих аргументов по номеру случайно выбранному функцией СЛУЧМЕЖДУ ().

Так, в этой формуле, СЛУЧМЕЖДУ () генерирует число между 1 и 4, это число используется, чтобы выбрать группу из 4 следующих значений: «№1″,»№2″,»№3″,»№4».

 

Знайте, что СЛУЧМЕЖДУ () пересчитывается всякий раз, когда вносится любое изменение в книгу, или даже при открытии рабочей книги. Как только у вас есть набор случайных заданий, вам может потребоваться скопировать и вставить формулы в качестве значения, чтобы предотвратить дальнейшие изменения.

 

ПСТР() и ПОИСК () Извлечь текст между скобками

=ПСТР(Текст; Поиск_первой_позиции(«(«:Текст)+1,Поиск_второй _позиции(«)»;Тектс)-Поиск_первой_позиции(«(«;Тектс)-1)

Чтобы извлечь текст между скобками, вы можете использовать формулу, основанную на функции ПСТР () и  функции ПОИСК ().

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

=ПСТР(D7;ПОИСК(«(«;D7)+1;ПОИСК(«)»;D7)-ПОИСК(«(«;D7)-1)

Основой этой формулы является функция ПСТР () , которая извлекает определенное количество символов из текста, начиная с определенного места.

Найти «Поиск_первой_позиции»  с какой позиции начинать извлечение текста, мы используем это выражение:

ПОИСК(«(«;D7)+1

Начало позиции:  14+1=15

Функция ПОИСК () находит позицию скобку в тексте, а затем уберем саму скобку с первой позиции прибавив единицу.

Для нахождения «Поиск_второй _позиции» второй скобки, мы используем это выражение:

ПОИСК(«)»;D7)-1

Начало второй позиции: 18-1=17

Функция ПОИСК () находит позицию скобки в тексте, а затем уберем саму скобку с последней позиции отняв единицу.

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

ПОИСК(«)»;D7)-ПОИСК(«(«;D7)-1)

Вторая позиция, отнять первую, получим количество символов для извлечения

17 — 14 =3      2,4

И соберем всю формулу как в ячейке E7:

=ПСТР(D7;ПОИСК(«(«;D7)+1;ПОИСК(«)»;D7)-1-ПОИСК(«(«;D7))

=ПСТР(Текст; позиция первая скобка; позиция Вторая скобка)

=ПСТР(Текст;14+1;17-1-14)

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

=ПСТР(D7;ПОИСК(«(«;D7)+1;ПОИСК(«)»;D7)-1-ПОИСК(«(«;D7))+0

РАЗНДАТ () и  ЕСЛИ () найти количество месяцев между датами
РАЗНДАТ () и ЕСЛИ () найти количество месяцев между датами

=РАЗНДАТ(Нач_дата,Конеч_дата,»M»)

Найти количество месяцев между двумя датами в виде целого числа, можно использовать функцию РАЗНДАТ.

В показанном примере, формула в E6 является:

=РАЗНДАТ(C6;D6;»M»)

Обратите внимание, что РАЗНДАТ автоматически округляет вниз. 

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

РАЗНДАТ принимает 3 аргумента: Дата начала, конечная_дата, и третий аргумент. В данном случае, мы ищем количество месяцев, поэтому мы указываем «M»  в третьем аргументе.

РАЗНДАТ автоматически вычисляет и возвращает число месяцев, округленное вниз.

Ближайший месяц

РАЗНДАТ округление по умолчанию. Если вы хотите вычеслить целого количество месяцев, вы можете откорректировать формулу F7:

=РАЗНДАТ(C7;D7+30;»M»)

да и если даты смешаны и разница получается отрицательное то тогда выходит ошибка ЧИСЛО можно исправить откорректировав формулу введя в формулу функцию ЕСЛИ добавив логику если больше то делай так если не больше тогда по-другому …..

=ЕСЛИ(C7>D7;РАЗНДАТ(D7;C7+30;»m»);РАЗНДАТ(C7;D7+30;»m»))

БДСУММ () Найти сумму по таблице с условиями
БДСУММ () Найти сумму по таблице с условиями

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

=БДСУММ(1 Таблица для поиска; 2 Наименование столбца суммирования; 3 Таблица с условиями)

Для суммирования данных с большим количеством условиям можно воспользоваться функцией баз данных формула в ячейке N11: =БДСУММ(D5:H15;H5;K5:O9)

(аргумент номер 1) Диапазон должен включать в себя наименованиями полей D5:H15 , (аргумент номер 3) а также диапазон K5:O9 таблицы с условиями для поиска должен быть выбрана с наименованиями столбцов идентичными названиям из таблицы поиска , в аргументе 2 указывается наименование столбца суммирования.

Свод по нескольким таблицам с разных листов
Свод по нескольким таблицам с разных листов

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

=СУММ(ТАБ1:ТАБ5!F6)

Собрать данные из одинаковых таблиц расположенных в одной книге и последовательно расположенных группы листов. В формуле используется диапазон листов ТАБ1:ТАБ5, ячейка для суммирования F6. (трехмерная ссылка)

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


СУММЕСЛИМН () узнаем выручку по городам и менеджерам за период

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

=СУММЕСЛИМН(Диапазон суммирования ; Диапазон условия1 ;условие1;Диапазон условия2;условие2;…n)

Объяснение

Узнать Выручку по городу или менеджеру за период очень просто. Используем функцию СУММЕСЛИМН() с условиями для поиска, формула в M8:

=СУММЕСЛИМН($F$7:$F$32;$D$7:$D$32;J8)

Как работает функция СУММЕСЛИМН () с несколькими условиями:

В формуле записываем первый диапазон «$F$7:$F$32» с суммами затем указываем второй диапазон «$D$7:$D$32» с данными в которых мы будем искать наш ГОРОД и само условие ГОРОД «J8» по которому будет вестись отбор сумм в данном случае используется только одно условие но аналогично можно добавить столько условий сколько Вам надо.

Условиями могут быть различные комбинации цифр , текста, логических символов объединённых конкатенацией & так же не забываем звёздочку «*» — любые символы и «?» — один символ:

ищем все что начинается с Мо «Мо*» или заканчивается на а «*а»

Например:

Больше или равно «>=»&L9

Больше чем — «>»&L9

Меньше чем — «>»&L9

ссылка на ячейку — О8

указать точную дату — «01.10.2020»

и так далее….

СУММПРОИЗВ () + ОСТАТ () сумма и количество четных и нечетных чисел в диапазоне 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}

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

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

РАБДЕНЬ.МЕЖД () для определенных дней по оплате
РАБДЕНЬ.МЕЖД () для определенных дней по оплате

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

=формула РАБДЕНЬ.МЕЖД(нач.дата;кол.дней;»вых»;»праздн»)

Задача: найти все предыдущие и последующие Понедельники и Среды без выходных за период.

Объяснение:

Найти предыдущий рабочий день перед выходными можно вычислив его прибавив к отчетной дате один день » C8+1 ;» а в аргументе количество дней отнять «-1» в результате дата попавшая на простой рабочий день не меняется, но если дата попала на выходной или праздник смещается на пятницу.

=РАБДЕНЬ.МЕЖД(C8+1;-1)

а теперь нам осталось задать ВЫХОДНЫЕ в виде комбинации нулей и единиц соответствующие дням недели в таком виде «0101111»

1 — выходной;

0 — рабочий;

Получилась формула:

=РАБДЕНЬ.МЕЖД(C8+1;-1;»0101111″)

Таким образом мы нашли все предыдущие даты выходным с фильтром платежные дни только Понедельник и Среда.

Последующие дни после выходных можно найти похожей формулой.

=РАБДЕНЬ.МЕЖД(C8-1;1;»0101111″)

Но в этот раз отнимаем один день от отчетной даты а в аргументе наоборот прибавляем день, таким образом смещение идет на Понедельник.

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

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

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

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

Объяснение

Используя функцию РАБДЕНЬ найдем даты предшествующие и последующие выходным, применив во втором аргументе минус и плюс единицу также добавив и отняв от начальной даты по единицы для определения попал-ли этот день на выходной , формула в 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; в результате мы получаем всегда рабочий день или предыдущий рабочий день если выпадает выходной.