Переход по листам с помощью функции  ГИПЕРССЫЛКА()  и дополнительных функции ПОДСТАВИТЬ() ЯЧЕЙКА() ДВССЫЛ()  СТРОКА() ИНДЕКС() ПОИСКПОЗ()
Переход по листам с помощью функции ГИПЕРССЫЛКА() и дополнительных функции ПОДСТАВИТЬ() ЯЧЕЙКА() ДВССЫЛ() СТРОКА() ИНДЕКС() ПОИСКПОЗ()

 

Разбор только Формулы №2

=ГИПЕРССЫЛКА(ПОДСТАВИТЬ(ЯЧЕЙКА(«адрес»;ДВССЫЛ(«Лист»&(СТРОКА()-3)&»!A1″));»‘»;»»);СТРОКА()-3)

Задача:

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

Используемые функции:

ГИПЕРССЫЛКА(), ПОДСТАВИТЬ(), ЯЧЕЙКА(), ДВССЫЛ(), СТРОКА(), ИНДЕКС(), ПОИСКПОЗ(),

Решение :

В первую очередь нам надо сформировать правильный адрес для гиперссылки.

ГИПЕРССЫЛКА(адрес;[имя])

адрес гиперссылки будет получен с помощью функции ЯЧЕЙКА(«тип сведений» ; «ссылка») и тип сведений выбираем «адрес» а для формирования  ссылки нам понадобится функция ДВССЫЛ() с значением такого вида «Лист»&(СТРОКА()-2)&»!A1″

объединяем текст «Лист» с формулой СТРОКА() которая определяет номер строки собственно ячейки, дальше объединяем текст  «!A1» и получаем ссылку на ячейку.

Будет Ошибка!! Если присмотрится в ссылке есть одинарная кавычка после Лист4 которую надо убрать  для получения корректного адреса.

убрать одинарную кавычку нам поможет функция ПОДСТАВИТЬ()

ПОДСТАВИТЬ(текст в котором ищем: найти символ: заменить символ на пусто)

получается что то на подобии:

ПОДСТАВИТЬ(ЯЧЕЙКА(«адрес»;ДВССЫЛ(«Лист»&(СТРОКА()-2)&»!A1″));»‘»;»»)

и задача практически решена.

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

Получится вот так:

=ГИПЕРССЫЛКА(ПОДСТАВИТЬ(ЯЧЕЙКА(«адрес»;ДВССЫЛ(«Лист»&(СТРОКА()-3)&»!A1″));»‘»;»»);«Имя гиперссылки»)

Так же можно добавить и другие варианты наименования ссылки.

используя функцию СТРОКА() получаем номер строки из которого надо вычесть количество строк до нужный диапазон.

=ГИПЕРССЫЛКА(ПОДСТАВИТЬ(ЯЧЕЙКА(«адрес»;ДВССЫЛ(«Лист»&(СТРОКА()-2)&»!A1″));»‘»;»»);СТРОКА()-2)

Скачайте файл с образцом решения для разбора.

 

 

 

 

Проверить списки на не достающие позиции с помощью функций ЕСЛИ, СЧЕТЕСЛИ, СТРОКА, ИНДЕКС и НАИМЕНЬШИЙ
Проверить списки на не достающие позиции с помощью функций ЕСЛИ, СЧЕТЕСЛИ, СТРОКА, ИНДЕКС и НАИМЕНЬШИЙ
=ЕСЛИ(СЧЁТЕСЛИ($C$5:$C$10;D5)=0;СТРОКА(D5);»есть в списке 1″)
 
=ИНДЕКС($D$5:$D$10;НАИМЕНЬШИЙ($E$5:$E$10;G5)-4)

Задача: Найти недостающие записи при сравнении двух списков. 

Для решения задачи будут использоваться функции:

ЕСЛИ(), СЧЕТЕСЛИ(), СТРОКА(), ИНДЕКС() и НАИМЕНЬШИЙ()

Логика решения:

В первую очередь определяем количество не совпадений в столбцах C и D  используя функцию СЧЕТЕСЛИ() все что не найдено будет с значением 0.

=СЧЁТЕСЛИ($C$5:$C$10;D5

все не найденные позиции как раз нам и нужны

Второе действие надо определить строку отсутствующей (то-есть с 0) записи в списке 

Для этого используем логическую функцию ЕСЛИ() и функцию  СТРОКА()

Логическое выражение  

СЧЁТЕСЛИ($C$5:$C$10;D5)=0

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

=СТРОКА(D7)

 

да и если ЛОЖЬ тогда пишем «есть в списке 1» 

Полностью формула выглядит так

=ЕСЛИ(СЧЁТЕСЛИ($C$5:$C$10;D5)=0;СТРОКА(D5);»есть в списке 1″)

Для формирования нужного нам списка отсутствующих элементов мы будем использовать замечательные функции НАИМЕНЬШИЙ() и 

ИНДЕКС()

Так как у нас есть номера строк или цифры в столбике E то мы можем использовать диапазон $E$5:$E$10 таблички или списка найти номер строки наименьшего числа с индексу из столбца G (номер строки определяется от первой строки)

G5- это номер индекса который нам надо вывести то есть от первого до последнего нужного элемента 1 до 10.

(таким образом  можно создавать ТОП продаж или ещё что то интересное) 

да и надо обязательна вычесть количество строк (4) до первой строки списка для  правильного подсчета.

НАИМЕНЬШИЙ($E$5:$E$10;G5)-4

После определения строки нужной нам позиции мы используем функцию ИНДЕКС() для вывода не номера строки а наименование не достающего элемента.

Указываем диапазон $D$5:$D$10 для поиска и номер строки НАИМЕНЬШИЙ($E$5:$E$10;G5)-4  элемента для формирования нового списка недостающих элементов.

Полностью формула выглядит так:

=ИНДЕКС($D$5:$D$10;НАИМЕНЬШИЙ($E$5:$E$10;G5)-4)

 

Вы можете скачать файл с готовым решением.

 

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

=ВЫБОР(СЛУЧМЕЖДУ(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″)

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