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.