Собесов
16 ноября 2026 г.

Excel для аналитика: необходимые навыки и практические задачи

Что аналитику нужно знать в Excel: структура таблиц, СУММЕСЛИМН, поиск, сводные таблицы и Power Query. Учебный набор данных, расчёты и проверки результата.

Excel помогает быстро проверить выгрузку, посчитать показатели и передать отчёт человеку, который не работает с кодом. Хороший файл устроен понятно: видно, откуда взялись данные, как получены числа и как их проверить.

Для начала не нужно знать сотни функций. Разберитесь с обычными таблицами, суммой по условиям, поиском значений и сводными таблицами. Затем добавьте Power Query для повторяющихся действий.

Начните с порядка в данных

Одна строка должна означать что-то одно: например, заказ. Один столбец — хранить один вид данных: сумму, дату или город. Не вставляйте итоги посреди списка заказов.

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

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

Данные для упражнений

Заголовки поместите в первую строку, заказы — в строки 2–6.

A: order_id B: city C: status D: order_date E: amount
1 Москва paid 01.10.2026 1 000
2 Москва paid 02.10.2026 500
3 Санкт-Петербург paid 02.10.2026 800
4 Москва cancelled 03.10.2026 900
5 Санкт-Петербург paid 03.10.2026 1 200

Данные учебные. Все заказы вместе — 4 400 ₽. Только оплаченные — 3 500 ₽. Эти числа пригодятся для проверки.

СУММЕСЛИМН: складываем только нужные строки

Посчитаем оплаченные заказы Москвы.

=СУММЕСЛИМН(E2:E6;B2:B6;"Москва";C2:C6;"paid")

Ответ — 1 500. Формула смотрит на город и статус, а складывает значения из столбца суммы. В английской версии Excel функция называется SUMIFS. Разделитель аргументов зависит от настроек. Описание есть в справке Microsoft.

Сверьте два подходящих заказа вручную. Затем замените Москву на Санкт-Петербург: должно получиться 2 000.

Для числа заказов с теми же условиями нужна СЧЁТЕСЛИМН (COUNTIFS). Средний оплаченный заказ Москвы — 1500 / 2 = 750. Делить на все три московских заказа нельзя: один из них отменён.

Как копировать формулы

Когда копируете формулу, ссылки обычно сдвигаются. Знак $ помогает закрепить строку или столбец.

Например, в отчёте по городам условие G2 должно изменяться на G3. А исходные диапазоны $E$2:$E$6 и $B$2:$B$6 — оставаться на месте. Подумайте, что должно меняться, и только потом расставляйте знаки $.

После копирования проверьте формулу в начале, середине и конце списка.

Поиск по справочнику

Допустим, к каждому городу нужно добавить регион из другой таблицы. Сначала убедитесь, что город встречается в справочнике один раз.

Если одному городу соответствуют две разные записи, функция поиска может вернуть одну из них и скрыть проблему. Сначала разберитесь, какая запись правильная.

Также не заменяйте автоматически всё ненайденное нулями. «Не нашли значение» и «значение равно нулю» — разные ответы. И проверьте версию Excel у человека, которому отправляете файл: новые функции есть не везде.

Сводная таблица: быстро собираем отчёт

Поставьте города в строки, сумму заказов — в значения, статус — в фильтр. Выберите paid. Получится Москва — 1 500, Санкт-Петербург — 2 000, всего — 3 500.

Добавили данные? Убедитесь, что новые строки попали в источник, и обновите сводную. Иначе она продолжит показывать старый ответ.

Осторожнее со средними. Общий средний чек нужно считать по всем подходящим заказам. В нашем примере это 3500 / 4 = 875. Если в городах разное число заказов, просто усреднить их средние чеки будет неверно.

Когда нужен Power Query

Если каждый месяц вы одинаково чистите и соединяете файлы, эти шаги можно один раз настроить в Power Query. Потом повторять их при обновлении данных.

Проверьте, работает ли настройка со вторым файлом. Затем добавьте пропуск или измените название столбца. Посмотрите, заметна ли ошибка. Возможности зависят от версии и платформы Excel; их можно уточнить в документации Microsoft.

Что проверить перед отправкой

Сверьте общий итог с исходной таблицей. Проверьте фильтры, новые строки и ошибки формул. Подпишите период и единицы: рубли, заказы, люди или проценты. Объясните, как обновить файл.

Для практики добавьте возврат, заказ без города и повтор номера заказа. Сначала решите, что они означают, затем меняйте расчёт. Эта привычка пригодится и в SQL, и в Python.

Другие статьи