Excel + Power Query для аналитика 2026: 12 формул, M-язык, как не остаться там навсегда

Q: Стоит ли учить VBA?

В 2026 — нет. VBA — legacy. Если нужна автоматизация — Python + openpyxl / xlwings, либо Power Query M.

Q: Power Query или dbt?

Power Query — для локальных трансформаций (один аналитик). dbt — для командной работы (несколько аналитиков на одной модели). Junior — Power Query, Middle/Senior — dbt. См. dbt macros. ---

Excel — самый недооценённый инструмент аналитика 2026. С Power Query он умеет: подключаться к API, базам данных, web-страницам; делать ETL; merge таблиц; преобразования. Многие junior-аналитики делают Excel-only работы первые 1-2 года. Это нормально — но только если ты планируешь переход на SQL/Python.

Этот гайд — что выучить в Excel чтобы быть полезным сейчас, и как не остаться там навсегда.

Что Excel делает хорошо

Задача	Excel — winner?
Quick exploration (5-30 минут)	✅ Да
Презентация с цифрами / графиками	✅ Да
Адhoc-расчёт «на коленке»	✅ Да
Очистка CSV до 100k строк	✅ Через Power Query
Подключение к API + автообновление	✅ Power Query
ETL на 1-10M строк	⚠️ Power Query, но медленно
Production reporting	❌ Нет, used BI tools
Совместная работа 5+ человек	❌ Нет, используют BI
Версионирование / git	❌ Нет

12 must-know Excel-формул для аналитика

Шаг 1: Lookup-функции (топ-3)

#### XLOOKUP (Excel 365+)

Современная замена VLOOKUP / INDEX-MATCH.

=XLOOKUP(A2; products[id]; products[name]; "Не найдено")

Параметры: что ищем, где ищем (массив-источник), что возвращаем, default. Поддерживает search-from-end, wildcard.

#### INDEX + MATCH (универсально)

=INDEX(products[name]; MATCH(A2; products[id]; 0))

Работает в любой версии Excel. Быстрее VLOOKUP, поддерживает поиск влево.

#### VLOOKUP (legacy, но все умеют)

=VLOOKUP(A2; products; 2; FALSE)

Поиск только справа, медленнее. Лучше XLOOKUP / INDEX-MATCH.

Шаг 2: Aggregation на условиях

#### SUMIFS / COUNTIFS / AVERAGEIFS

=SUMIFS(orders[amount]; orders[region]; "Москва"; orders[date]; ">="&DATE(2026;1;1))

«Сумма amount где region=Москва и date≥1.1.2026». Условий — до 127.

#### COUNTIF

=COUNTIF(users[status]; "active")

Подсчёт по одному условию.

Шаг 3: Дата-функции

#### EOMONTH / EDATE

=EOMONTH(A2; 0)  // конец месяца
=EDATE(A2; 12)   // через 12 месяцев

#### NETWORKDAYS / WORKDAY

=NETWORKDAYS(A2; B2)         // рабочих дней
=NETWORKDAYS.INTL(A2; B2; 11; holidays)  // с праздниками РФ

Шаг 4: Текстовые функции

#### TEXTSPLIT / TEXTJOIN (Excel 365+)

=TEXTSPLIT(A2; ";")  // разбить по ; в массив
=TEXTJOIN("; "; TRUE; B2:B10)  // собрать с разделителем

#### TRIM / CLEAN / SUBSTITUTE

=TRIM(A2)                          // убрать лишние пробелы
=SUBSTITUTE(A2; ","; ".")          // заменить символ

Шаг 5: Динамические массивы (Excel 365)

#### FILTER / SORT / UNIQUE

=UNIQUE(orders[region])                            // уникальные значения
=SORT(FILTER(orders; orders[amount]>1000); 2; -1)  // фильтр + сортировка

Excel выдаёт массив-результат в соседние ячейки. Powerful, не VBA.

Power Query — ETL без программирования

Power Query (PQ) — встроенный ETL в Excel/Power BI. M-язык под капотом, но 90% операций — drag-and-drop через UI.

Шаг 1: подключиться к источнику

Data → Get Data → выбрать тип:

File (CSV, Excel, JSON, XML)
Database (PostgreSQL, MySQL, SQL Server)
Web (HTML / JSON API)
Other (PDF, Folder, Azure)

Шаг 2: типичные операции

Операция	UI шаг
Удалить пустые строки	Remove Rows → Remove Blank Rows
Переименовать колонки	Right-click → Rename
Cast типов	Right-click → Change Type
Split column	Transform → Split Column
Merge tables (JOIN)	Home → Merge Queries (LEFT/RIGHT/INNER/OUTER)
Pivot/Unpivot	Transform → Pivot Column
Group by + aggregation	Transform → Group By

Все шаги записываются в M-скрипт. Можешь его редактировать вручную в Advanced Editor.

M-script пример

let
    Source = Csv.Document(File.Contents("C:\data\orders.csv"), [Delimiter=","]),
    PromoteHeaders = Table.PromoteHeaders(Source),
    ChangeType = Table.TransformColumnTypes(PromoteHeaders, {{"date", type date}, {"amount", type number}}),
    Filtered = Table.SelectRows(ChangeType, each [amount] > 0),
    Grouped = Table.Group(Filtered, {"region"}, {{"total_amount", each List.Sum([amount]), type number}})
in
    Grouped

Это М-эквивалент SQL:

SELECT region, SUM(amount) AS total_amount
FROM orders
WHERE amount > 0
GROUP BY region;

Шаг 3: Pivot-таблицы

Pivot — главная сила Excel для аналитика.

Поле UI	SQL-эквивалент
Rows	GROUP BY первая колонка
Columns	PIVOT / CASE WHEN
Values	SUM/COUNT/AVG aggregation
Filters	WHERE
Slicers / Timeline	WHERE интерактивный

Pivot в Excel — это interactive GROUP BY с UI. Незаменим для quick analysis.

Шаг 4: ОТЛИЧИЯ от SQL/Python — где Excel ломается

Memory limit

Excel grid: 1,048,576 строк × 16,384 колонок
Power Query: грузит больше, но при загрузке в Excel — те же limits

Реальность: на 500k+ строк Excel начинает тормозить. На 1M+ — лагает.

Версионирование

Excel-файл с формулами нельзя ревьюить через git. Изменения исчезают. Это главная причина перехода на SQL/Python.

Аудит изменений

Кто и когда поменял формулу? В Excel не отследить. В SQL/dbt — git blame.

Шаринг с другими

Прислал коллеге xlsx → он открыл, поломал, прислал обратно. Excel-боль. В BI: один dashboard, все видят latest.

Шаг 5: как не остаться в Excel навсегда

Excel — first 6-12 месяцев junior-аналитика. Дальше — переход.

Месяц	Что делать
1-3	Excel + Power Query mastery
3-6	Параллельно SQL (тренажёр + pet-проект)
6-9	Заменить Excel-задачи на SQL-запросы
9-12	Python для аналитики (pandas)
12+	BI tool (DataLens / Power BI / Tableau) — заменяет Excel для презентаций

Не пропускай Excel на старте — на старте он самый эффективный инструмент. Но через год — должен быть твой 20% инструмент, а не 80%.

Подводные камни

Грабли 1: «всё уже в Excel, зачем учить SQL»

Реальность: junior с только Excel — потолок 100-180K. С SQL + Excel — 150-250K. С SQL + Python + BI — 200-350K. Подробнее зарплата аналитика 2026.

Грабли 2: Power Query вместо нормального ETL

Power Query слабый на 1M+ rows. Лучше переключаться на SQL/dbt раньше. См. dbt для аналитика.

Грабли 3: формулы как «макароны»

Длинные формулы =IF(IF(IF(...))) непонимаемы. Refactor: либо разбить на промежуточные ячейки, либо переписать в SQL CASE WHEN.

Грабли 4: Excel «вшит» в бизнес-процессы

Если CFO работает только в Excel → не борись, работай с Excel, но результаты считай в SQL/BI и экспортируй в Excel. Лучшая из обоих миров.

Частые вопросы

Стоит ли учить VBA?

В 2026 — нет. VBA — legacy. Если нужна автоматизация — Python + openpyxl / xlwings, либо Power Query M.

Excel или Google Sheets?

Excel мощнее (Power Query, dynamic arrays). Google Sheets лучше для совместной работы. Junior — Excel master, потом по обстоятельствам.

Какие версии Excel нужны?

Excel 365 (online или desktop) — самый функциональный (XLOOKUP, TEXTSPLIT, FILTER). Excel 2019 — minimum для production. Excel 2016 — legacy, многого нет.

Я хочу делать SQL — что начинать?

Купи Karpov SQL курс, проходи SQL-тренажёр (5 задач бесплатно), читай гайд от нуля до Junior.

Power Query или dbt?

Power Query — для локальных трансформаций (один аналитик). dbt — для командной работы (несколько аналитиков на одной модели). Junior — Power Query, Middle/Senior — dbt. См. dbt macros.

Что дальше

Если хочешь практику — попробуй SQL-тренажёр с автопроверкой (5 задач бесплатно). После Excel/Power Query SQL становится понятным быстро — синтаксис похож на M-язык.

Готов к собеседованиям? AI-интервью тренирует ответы на реальных вопросах из бесплатного пула. В Pro — безлимит мок-собесов + 491 SQL-задача + 612 тестовых заданий + 50+ блог-постов.

Смежные посты

Сравнить Free и Pro → (1999₽/мес, экономит часы рутины)

Источники

Microsoft Docs: «Power Query M reference» (learn.microsoft.com)
Habr 2026: Power Query best practices
Karpov SQL курс (karpov.courses)
ExcelGuru: «From Excel to SQL» (excelguru.ca)
Yandex Practicum: «Аналитик с нуля» курс