Формулы массива и анализ данных в Excel 2010/2013

МОДУЛЬ 1. ИСПОЛЬЗОВАНИЕ ФОРМУЛ МАССИВОВ

Как заставить обычные формулы работать как формулы массива для решения вот таких задач:

• защищать формулы без использования стандартных инструментов защиты
• использовать всего одну формулу для вывода результата в большом количестве ячеек (решаем проблемы смещения адресов ячеек без использования знака $)
• использовать всего одну ячейку для расчета вместо "толпы" столбцов с промежуточными вычислениями
• выполнить суммирование и другие расчеты в диапазоне с ошибками, не избавляясь от ошибочных значений
• выбрать первые несколько максимальных/минимальных значений (например, ТОП-10 лидеров продаж и пр.)

МОДУЛЬ 2. ПРИМЕНЕНИЕ ФУНКЦИЙ КАТЕГОРИИ «ССЫЛКИ И МАССИВЫ»

Использование функций из категории формул массивов как таковых для решения следующих задач:

•  как создать список для подстановки значений внутри функции, а не в ячейках
• поиск и подстановка значений по двум и более условиям: все возможности ПОИСКПОЗ и ИНДЕКС, именованные диапазоны и ДВССЫЛ
• поиск данных для нескольких одинаковых значений: ИНДЕКС и ПОИСКПОЗ, НАИМЕНЬШИЙ, СТРОКА и СТОЛБЕЦ
• изменение ориентации таблицы несколькими способами: с сохранением связей и без таковых
• суммировать данные из неизвестного (плавающего) диапазона ячеек, размеры которого зависят от искомых значений: ДВССЫЛ и ПОИСКПОЗ, АДРЕС (например, указанный интервал дат – начало и окончание проекта и т.п.)
• сбор данных с разных листов без использования ссылок на эти листы: ДВССЫЛ
• создание взаимосвязанных выпадающих списков: ДВССЫЛ и "умные" таблицы
• создание пользовательского имени формулы и его использование – например, для составления простого «интерактивного» графика: СМЕЩ
• суммировать данные из диапазона, соответствующего искомому значению: СМЕЩ
• циклические ссылки – зачем они нужны, как их найти и включить

Форматирование текста формул для облегчения чтения и редактирования: перенос по строкам, отступы, комментарии