Такие замечательные функции, как ВПР, ПОИСКПОЗ+ИНДЕКС, ГПР позволяют по известному критерию найти только одно значение из списка: самое первое при просмотре сверху вниз для вертикальных таблиц или самое первое при просмотре слева направо при просмотре слева направо. А что делать, если нужно получить не одно, а все значения. Например, выбрать для каждой компании объекты, которые ей подчиняются и не с помощью фильтрации, а именно по формулам. Найдем ответ и на эту задачу!

Решение этой задачи будет с использованием формулы массива, поэтому необходимо заранее выделить предполагаемый диапазон ячеек (можно с запасом) и написать формулу, завершив её формулой массива, т.е. нажав клавиши Ctrl+Shift+Enter:

Сперва в ответ получаем значения из таблицы, а когда они заканчиваются, то сообщение об ошибке. Подобное, как и всегда, можно убрать с применением функции ЕСЛИОШИБКА.

Разберем формулу «по косточкам»:

• $C$2:$C$16 — диапазон ячеек-результата, т.е. объекты.

• НАИМЕНЬШИЙ(Массив;K) — возвращает k-е наименьшее значение в множестве данных:
  Массив определяется по формуле: ЕСЛИ(D2:D16=$F$2;СТРОКА(D2:D16)-СТРОКА($D$1);»»), т.е. проверяется в ячейках D2:D16 соответствие выбранной компании (критерий) в ячейке F2, а затем определяются позиции (номера по порядку) в выделенной области, но так как диапазон выделен без заголовков (с ячейки D2), то необходимо скорректировать на строку-заголовок, т.к. СТРОКА($D$1), а в случае если совпадение критерия не найдено, то позиция не определяется — «».
  K — позиция в диапазоне данных.

Окончательно формула будет выглядеть:

В этом случае, порядок расположения объектов следует сверху вниз. Если требуется обратный порядок, т.е. снизу вверх, то функцию НАИМЕНЬШИЙ надо заменить на НАИБОЛЬШИЙ:

В случае, если ответ необходимо получать данные то одного столбца, то другого, то необходимо расширить массив в функции ИНДЕКС и определить номер столбца с помощью функции ПОИСКПОЗ:

Просматривая функции категории Дата и время, можно увидеть различные функции, чтобы определить день, месяц или год. Однако, среди стандартного перечня нет функции, позволяющей определить номер квартала по дате. Раз нет — сделаем сами! Для этого необходима обычная дата.

Первый способ — используем функции МЕСЯЦ, ЕСЛИ, И.

Определяем значение месяца с помощью функции МЕСЯЦ, а затем в функции ЕСЛИ, описываем 4 условия, каждое из которых отвечает за принадлежность того или иного месяца к определенному кварталу:

Второй способ — использование функций МЕСЯЦ, ЦЕЛОЕ.

Т.к. квартал — это каждые 3 последующих месяца в году, то можно с помощью функции МЕСЯЦ определить значение месяца, а затем к текущему значению месяца прибавить значение 2. Если полученную сумму разделить на 3 (количество месяцев в квартале), то целая часть числа будет соответствовать значению квартала:

Или, собрав всё в одну формулу, получаем простое решение:

Исходные данные для расчета могут быть не только в одной рабочей книге, но и в разных книгах. Чтобы связать книги Excel, необходимо предварительно открыть все связываемые книги, а затем при создании формулы перейти в нужную книгу Excel, открыть нужный лист, выделить ячейку или диапазон ячеек, ввести знак следующей операции или завершить формулу клавишей Enter.

Изначально в формуле будет прописан адрес ячейки следующего вида:
[Курсы валют.xlsx]ЦБ’!$B$3,
где в квадратных скобках указывается название файла —  в приведенном примере ссылки — Курсы валют.xlsx, затем название листа ЦБ и адрес ячейки — $B$3. По умолчанию, ссылка на ячейку другого файла абсолютная, однако при необходимости ее можно сделать и относительной и смешанной.

При открытии файла (если другие книги-источники будут закрыты) на экране появляется ПРЕДУПРЕЖДЕНИЕ СИСТЕМЫ БЕЗОПАСНОСТИ [SECURITY WARNING], которое сообщает, что автоматическое обновление ссылок отключено. Если нажать Включить содержимое [Enable Content], то результаты расчетов будут обновлены.

Вид ссылки немного изменяется — в него добавляется полный путь расположения файла, например:
‘D:\Материалы EXCEL Level 2\[Курсы валют.xlsx]ЦБ’!$B$3.

Для редактирования связи с внешним источником на вкладке Данные [Data], в группе Подключения [Connections], выбрать Изменить связи [Edit Links]

В окне Изменение связей [Edit Links] выбрать нужное действие:

• Обновить [Update Values] – обновление выделенного источника. Состояние Неизвестно свидетельствует о том, что в данном окне обновление данных из источников не производилось. После выполнения команды Обновить состояние изменяется на OK.
• Изменить [Change source] – изменение внешнего источника в случае смены имени файла или перемещения источника в другую папку. Следует помнить, что структура книги изменяться не должна, т.к. внутри выбираемого файла название листа и адрес нужной ячейки должны совпадать с изначально заданными в формуле.
• Открыть [Open Source] – открытие файла-источника.
• Разорвать связь [Break Link] — разорвать связь с внешним источником. Формулы в ячейках заменяются значениями, которые получены в ячейках на данный момент. Команду отменить нельзя, поэтому следует создать копию файла, чтобы при необходимости остался исходный файл с формулами для последующих обновлений.
• Запрос на обновление связей [Startup Prompt] – возможность настроить вариант обновления при открытии файла:
  — Пользователь указывает, нужно ли задавать вопрос [Let users choose to display the alert or not]]
  — Не задавать вопрос и не обновлять связи [Don’t display the alert and don’t update automatic links]
  — Не задавать вопрос и обновлять связи [Don’t display the alert and update links]