Продвинутые функции и инструменты Excel — Power Query, формулы массива и Power Pivot

Введение: Почему Excel 2016 всё ещё актуален?

Даже без динамических массивов и новых функций Excel 365, версия 2016 остается мощным инструментом. В этой статье вы узнаете, как использовать Power Query, формулы массива и Power Pivot для автоматизации задач, анализа больших данных и создания профессиональных отчётов.

1. Power Query: Чистка и трансформация данных

Что это?

Power Query (в Excel 2016 называется «Получить и преобразовать данные») — инструмент для импорта, очистки и объединения данных из разных источников (Excel, CSV, веб-страницы, базы данных).

Как активировать?

1. Перейдите: Файл → Параметры → Надстройки.
2. В разделе Управление выберите COM-надстройки → Перейти.
3. Поставьте галочку Microsoft Power Query для Excel → OK.

Пример: Импорт и чистка данных из CSV

1. Импорт:
   Данные → Из текста/CSV → выберите файл.
   Нажмите Загруть → Преобразовать данные (откроется редактор Power Query).
2. Чистка данных:
   Удалите пустые строки: Главная → Удалить строки → Удалить пустые.
   Замените ошибки: Заменить значения → введите «н/д» → замените на 0.
   Разделите столбец «ФИО» на «Имя», «Фамилия»: Главная → Разделить столбец → По разделителю.
3. Объединение таблиц:
   Загрузите второй файл через Данные → Из таблицы/диапазона.
   Выберите Объединить запросы → Присоединить (аналог JOIN в SQL).
4. Сохраните и загрузите:
   Главная → Закрыть и загрузить → Закрыть и загрузить в... → Таблица.

Результат: Чистая таблица, которая обновляется при изменении исходников (правая кнопка → Обновить).

2. Формулы массива: Мощь без динамических массивов

Что это?

Формулы массива позволяют выполнять вычисления над диапазонами данных. Для их применения в Excel 2016 нужно нажимать Ctrl+Shift+Enter (появится { } вокруг формулы).

Примеры:

1. Поиск уникальных значений:{=ИНДЕКС(A2:A100; ПОИСКПОЗ(0; СЧЁТЕСЛИ($C$1:C1; A2:A100) + (A2:A100=""); 0)}
   
   Введите формулу в ячейку C2 → нажмите Ctrl+Shift+Enter → протяните вниз.
2. Сумма с условиями:{=СУММ((A2:A100="Москва") * (B2:B100>50000))}
   → Вернёт сумму продаж в Москве свыше 50 000.
3. Транспонирование таблицы:{=ТРАНСП(A1:D10)}

Ограничения:

• Формулы массива могут замедлить работу с большими данными.
• Не обновляются автоматически при изменении диапазонов (нужно пересчитывать через F9).

3. Power Pivot: Анализ миллионов строк

Что это?

Надстройка для работы с большими данными, создания связей между таблицами и сложных расчетов с помощью DAX (Data Analysis Expressions).

Как активировать?

1. Файл → Параметры → Надстройки.
2. В разделе Управление выберите COM-надстройки → Перейти.
3. Поставьте галочку Microsoft Power Pivot для Excel → OK.

Пример: Создание модели данных

1. Загрузка данных:
   Перейдите: Power Pivot → Управление.
   Нажмите Из других источников → выберите базу данных или файл Excel.
2. Создание связей:
   Перетащите поле «ID клиента» из таблицы «Заказы» на «ID» в таблице «Клиенты».
3. DAX-формулы:
   Добавьте меру для расчёта общей прибыли:
   Прибыль = SUM(Заказы[Сумма]) - SUM(Заказы[Себестоимость])
   
4. Сводная таблица:
   Вернитесь в Excel → Вставка → Сводная таблица → Использовать модель данных.
   → Анализируйте данные из связанных таблиц.

Сравнение с Excel 365

Практическое задание

1. Power Query:
   Скачайте CSV с курсом валют с сайта ЦБ РФ.
   Импортируйте в Excel и очистите данные (удалите пустые строки, измените формат даты).
2. Формулы массива:
   Создайте список уникальных регионов из столбца «Город» с помощью формулы массива.
3. Power Pivot:
   Свяжите таблицу «Продажи» с таблицей «Товары» по артикулу.
   Рассчитайте среднюю цену товара с помощью DAX: Средняя_цена = AVERAGE(Товары[Цена])
   

Частые ошибки и решения

1. Power Query не обновляется:
   Убедитесь, что исходные файлы не перемещены.
   Проверьте настройки запроса: Параметры запроса → Обновление.
2. Формулы массива не работают:
   Нажимайте Ctrl+Shift+Enter, а не Enter.
   Убедитесь, что диапазоны совпадают по размеру.
3. Power Pivot не видит данные:
   Перезагрузите модель данных через Power Pivot → Управление.

Заключение

Даже в Excel 2016 можно:

• Автоматизировать рутину через Power Query.
• Выполнять сложные расчеты с формулами массива.
• Анализировать Big Data через Power Pivot.

Что дальше? Во второй статье блока мы разберем, как создавать макросы без программирования и использовать пакет анализа для статистики.