Обучение Excel: от основ до продвинутых инструментов

Интерфейс Excel и основы работы с таблицами

Excel — это программа для работы с табличными данными: вы вводите значения, организуете их в строки и столбцы, оформляете, а затем анализируете с помощью формул, фильтров, диаграмм и других инструментов.

В этой статье вы разберёте интерфейс Excel и освоите базовые действия с листами, ячейками, диапазонами и умными таблицами (Excel Table). Это фундамент, на который будут опираться формулы, сводные таблицы и продвинутые инструменты в следующих темах.

!Схема основных элементов окна Excel

Из чего состоит файл Excel

В Excel данные хранятся в файле, который называется книга.

Книга — файл Excel (например, Отчёт.xlsx).

Лист — отдельная вкладка внутри книги (например, Лист1, Продажи, Справочники).

Ячейка — пересечение столбца и строки (например, B3).

Диапазон — группа ячеек (например, A1:D10).

Важно: обычно книга содержит несколько листов, а анализ данных часто удобнее строить, когда каждый лист выполняет свою роль (ввод данных, справочники, отчёты).

Основные элементы интерфейса

Лента

Лента — это верхняя панель с вкладками команд.

Главная — ввод, копирование, форматирование, стили, базовые операции.

Вставка — таблицы, диаграммы, изображения, сводные таблицы.

Формулы — функции, управление именами, аудит формул.

Данные — сортировка, фильтры, импорт, проверка данных.

Вид — режимы отображения, закрепление областей, масштаб.

Совет: если вы не находите команду, используйте поиск команд (в современных версиях Microsoft 365 он расположен сверху и позволяет искать по названию функции).

Панель быстрого доступа

Панель обычно находится слева вверху и содержит кнопки вроде Сохранить, Отменить, Повторить. Её можно настроить под себя.

Поле имени и строка формул

Поле имени показывает адрес активной ячейки (например, C7) или имя диапазона.

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

Подробно об этих элементах: Строка формул в Excel (Microsoft Support)

Рабочая область листа

Центральная часть — сетка из столбцов и строк.

Столбцы обозначаются буквами: A, B, C…

Строки обозначаются числами: 1, 2, 3…

Активная ячейка — текущая выделенная ячейка, куда вводятся данные.

Строка состояния, масштаб и виды

Внизу находится строка состояния.

Она показывает подсказки и итоги по выделению (например, сумму выделенных чисел).

Справа обычно есть масштаб (ползунок) и кнопки режимов отображения.

Навигация и выделение: то, что экономит время

Быстрая навигация по листу

Клик мышью по ячейке — переход.

Стрелки на клавиатуре — перемещение по одной ячейке.

Ctrl + стрелки — прыжок к краю текущего блока данных.

Как выделять диапазоны

Перетаскивание мышью — выделение прямоугольного диапазона.

Shift + стрелки — расширение выделения.

Ctrl + клик по ячейкам — выбор нескольких несмежных областей.

Почему это важно: большинство действий в Excel (форматирование, сортировка, построение диаграмм) применяются к выделенному диапазону.

Ввод и редактирование данных

Как вводить данные

Выделите ячейку.

Введите текст или число.

Нажмите Enter (обычно переход вниз) или Tab (переход вправо).

В одной ячейке может быть:

Текст (например, название товара)

Число (например, 1250)

Дата/время (например, 06.02.2026)

Формула (начинается со знака =)

Редактирование содержимого

Двойной клик по ячейке — редактирование прямо в ячейке.

F2 — редактирование без мыши.

Редактирование через строку формул — удобно для длинных значений.

Перенос строки внутри ячейки

Если нужно сделать новую строку в той же ячейке:

Windows: Alt + Enter

Копирование, вставка и автозаполнение

Копирование и вставка

Ctrl + C — копировать

Ctrl + V — вставить

Ctrl + X — вырезать

Excel вставляет не только значения, но и формат. Если результат выглядит неожиданно, используйте варианты вставки (например, только значения).

Маркер заполнения

В правом нижнем углу выделенной ячейки/диапазона есть маленький квадратик — маркер заполнения.

Что он умеет:

Копировать значение или формулу вниз/вправо.

Продолжать последовательности (1, 2, 3…; Пн, Вт…; месяцы).

Полезно помнить: последовательность определяется по образцу. Часто достаточно задать два первых значения (например, 10 и 20), выделить их и протянуть — Excel продолжит шаг.

Основы форматирования таблиц

Форматирование не меняет смысл данных, но делает таблицу читаемой.

Самые частые операции на вкладке «Главная»

Выбор шрифта и размера.

Выравнивание по левому/центру/правому краю.

Форматы чисел: число, валюта, процент, дата.

Границы и заливка.

Почему важен формат числа

Одна и та же запись может отображаться по-разному в зависимости от формата.

Например:

0,25 в формате Процент будет показано как 25%.

Дата может отображаться как 06.02.2026 или 6 февраля 2026.

Подробно о числовых форматах: Форматы чисел в Excel (Microsoft Support)

Превращаем диапазон в «умную таблицу»

Обычный диапазон — это просто прямоугольник ячеек. Таблица Excel (часто говорят умная таблица) — специальный объект, который добавляет удобства: автозаголовки, фильтры, автоматическое расширение при вводе новых строк, структурированные ссылки.

Как создать таблицу

Подготовьте данные в виде прямоугольника с заголовками столбцов (например: Дата, Товар, Количество, Сумма).

Выделите любую ячейку внутри диапазона.

Откройте вкладку Вставка → Таблица.

Проверьте, что диапазон указан верно, и включена опция Таблица с заголовками.

После создания:

В заголовках появятся кнопки фильтра.

Таблица получит стиль.

При вводе данных под таблицей она будет расширяться.

Официальная справка: Создание и форматирование таблицы (Microsoft Support)

Частые ошибки новичков и как их избежать

Смешивание типов данных в одном столбце: например, числа и текст вроде — или нет данных. Лучше использовать пустую ячейку или отдельный столбец с признаком.

«Шапка» в несколько строк: для фильтров, сортировки и таблиц заголовки должны быть в одной строке.

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

Данные без структуры: держите правило: один столбец — один смысл (дата отдельно, сумма отдельно, количество отдельно).

Что дальше по курсу

Дальше вы будете опираться на навыки из этой статьи, чтобы:

работать с формулами и ссылками на ячейки

использовать сортировку и фильтры для анализа

строить отчёты и визуализации

Если интерфейс и «механика» работы с листом доведены до автоматизма, дальнейшие темы проходят заметно легче.

Формулы и функции: базовые приёмы расчётов

Excel становится по-настоящему полезным, когда вы переходите от простого ввода данных (лист, ячейки, диапазоны и умные таблицы из предыдущей темы) к вычислениям. В этой статье вы научитесь создавать формулы, использовать функции, понимать ссылки на ячейки и избегать типичных ошибок.

Что такое формула и что такое функция

Формула — выражение, которое Excel вычисляет и показывает результат в ячейке.

Функция — готовая «команда вычисления» внутри формулы (например, сумма или среднее).

Правило: любая формула в Excel начинается со знака =.

Примеры:

=B2*C2 — формула умножения.

=SUM(B2:B10) — формула с функцией суммы.

Официальная справка Microsoft: Обзор формул в Excel

Арифметика в формулах: операторы и приоритет

Основные операторы

| Что делаем | Оператор | Пример | |---|---:|---| | Сложение | + | =A1+B1 | | Вычитание | - | =A1-B1 | | Умножение | | =A1B1 | | Деление | / | =A1/B1 | | Степень | ^ | =A1^2 | | Склейка текста | & | =A1&" "&B1 |

Приоритет действий и скобки

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

Пример:

=A1+B1C1 — сначала B1C1, потом прибавление.

=(A1+B1)*C1 — сначала сумма, затем умножение.

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

Ссылки на ячейки: относительные, абсолютные и смешанные

Когда вы копируете формулу вниз или вправо, Excel обычно подстраивает ссылки. Это удобно, но иногда мешает — поэтому важно понимать виды ссылок.

Относительная ссылка

Пример: A1

При копировании формулы ссылка изменится относительно нового положения.

Сценарий: если в D2 формула =B2C2, то при копировании в D3 она станет =B3C3.

Абсолютная ссылка

Пример: 1

При копировании не меняется ни столбец, ни строка.

Это нужно, когда есть один фиксированный параметр, например курс валюты в F1, а расчёты идут по многим строкам: =D2*1.

Смешанная ссылка

Пример: 1 (фиксирована строка 1, столбец меняется)

Часто используется в расчётах по таблицам, где нужно закрепить «шапку» или «первый столбец».

!Визуальная подсказка, как ведут себя относительные и абсолютные ссылки при копировании формулы

Как быстро поставить знаки F1 → $F1 → F1.

Ввод функций: синтаксис и аргументы

Функции записываются как имя и круглые скобки с аргументами.

Пример: =SUM(A2:A10)

Здесь SUM — имя функции, A2:A10 — её аргумент (диапазон).

Подсказки при вводе:

Excel показывает список функций и помогает заполнять аргументы.

Аргументы обычно разделяются символом, который зависит от региональных настроек (часто ;). Поэтому вы можете увидеть =SUM(A2;A3) вместо =SUM(A2,A3).

Базовые функции для повседневных расчётов

SUM, AVERAGE, MIN, MAX

=SUM(C2:C100) — сумма.

=AVERAGE(C2:C100) — среднее.

=MIN(C2:C100) — минимум.

=MAX(C2:C100) — максимум.

Официальная справка:

Функция SUM

Функция AVERAGE

COUNT и COUNTA

=COUNT(A2:A100) — считает ячейки с числами.

=COUNTA(A2:A100) — считает непустые ячейки (включая текст).

Это важно, потому что «сколько строк заполнено» и «сколько чисел для расчёта» — разные вопросы.

IF: простая логика

Функция IF возвращает одно значение, если условие истинно, и другое — если ложно.

Пример: премия 10%, если продажи больше 100000:

=IF(D2>100000;D2*0,1;0)

Здесь:

D2>100000 — условие.

D2*0,1 — результат, если условие выполняется.

0 — результат, если условие не выполняется.

Справка: Функция IF

ROUND: округление для денег и отчётов

=ROUND(E2;2) — округляет число в E2 до 2 знаков после запятой.

Полезно помнить: формат отображения (например, «2 знака») не всегда означает округление самого значения, а ROUND округляет именно результат.

Справка: Функция ROUND

TODAY и NOW: текущая дата и время

=TODAY() — текущая дата.

=NOW() — текущие дата и время.

Эти функции пересчитываются автоматически при открытии файла или пересчёте книги.

Справка: Функция TODAY

Формулы в умной таблице: структурированные ссылки

В прошлой теме вы создавали умные таблицы (Excel Table). Их ключевое преимущество в расчётах — структурированные ссылки: формулы читаются как «сложить столбец Сумма», а не «сложить диапазон D2:D999».

Примеры:

=SUM(Продажи[Сумма]) — сумма по столбцу Сумма в таблице Продажи.

=[@Цена]*[@Количество] — расчёт в текущей строке таблицы.

Плюсы структурированных ссылок:

Диапазоны не нужно расширять вручную при добавлении новых строк.

Формулы становятся понятнее.

Таблица сама протягивает формулы на новые строки.

Справка: Использование структурированных ссылок в таблицах Excel

Типичные ошибки в формулах и как их распознавать

Ошибки в Excel обычно показываются специальными кодами.

| Ошибка | Что означает | Частая причина | |---|---|---| | #DIV/0! | Деление на ноль | Делите на пустую ячейку или 0 | | #VALUE! | Неверный тип данных | Число записано как текст, смешаны типы | | #NAME? | Неизвестное имя | Опечатка в названии функции или имени диапазона | | #REF! | Неверная ссылка | Удалили строку/столбец, на который ссылалась формула | | #N/A | Нет значения | Часто при поиске, когда совпадение не найдено |

Если ошибка ожидаема (например, деление при пустом знаменателе), используйте IFERROR.

Пример: =IFERROR(A2/B2;0) — если деление вызывает ошибку, показать 0.

Справка: Функция IFERROR

Практические привычки, которые ускоряют работу

Проверяйте формулу в строке формул и обращайте внимание, куда ведут ссылки.

Используйте маркер заполнения, но помните про тип ссылок (относительная или абсолютная).

Держите один смысл в одном столбце (числа отдельно, текст отдельно), иначе функции будут давать неожиданные результаты.

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

Что дальше по курсу

Теперь у вас есть база вычислений: формулы, функции и ссылки. Следующий шаг — научиться быстро находить закономерности в данных: сортировать, фильтровать, готовить данные к анализу и строить отчёты. Эти навыки опираются на корректные расчёты, поэтому важно довести базовые приёмы формул до уверенного уровня.

Работа с данными: сортировка, фильтры, проверка и очистка

После того как вы освоили интерфейс Excel, научились вводить данные и делать расчёты формулами и функциями, возникает следующий практический вопрос: а можно ли доверять данным и удобно ли их анализировать? Чаще всего проблемы в отчётах появляются не из-за «сложных формул», а из-за беспорядка в данных: лишние пробелы, текст вместо чисел, дубликаты, разные написания одного и того же значения.

В этой статье вы разберёте четыре навыка, которые превращают «сырые» таблицы в удобный набор данных для расчётов и анализа:

сортировка

фильтры

проверка данных (ограничение ввода)

очистка и приведение к единому виду

!Схема показывает, что подготовка данных идёт до расчётов и отчётов

Почему подготовка данных критична для формул и отчётов

Формулы из предыдущей темы работают корректно только тогда, когда данные однотипны и предсказуемы.

Примеры типичных «ломающих» ситуаций:

числа в столбце записаны как текст (например, "1000"), и SUM даёт неожиданный результат

в одном столбце встречаются значения "Москва", "москва", "Москва " (с пробелом) и это «разные» значения для фильтров и сводных

в таблице есть дубликаты строк, из-за чего итоги завышены

Практическое правило: сначала привести данные в порядок, потом считать и строить отчёты.

Сортировка данных

Сортировка — это упорядочивание строк по одному или нескольким столбцам. Она помогает быстро увидеть:

самые большие и самые маленькие значения

«хвосты» (аномальные записи)

логический порядок дат, статусов, категорий

Быстрая сортировка по одному столбцу

Кликните любую ячейку в столбце, по которому хотите сортировать.

На вкладке Данные выберите сортировку А→Я / Я→А (для текста) или По возрастанию / По убыванию (для чисел и дат).

Если ваши данные оформлены как умная таблица (Excel Table) из первой темы, сортировка и фильтры работают особенно надёжно: Excel точно понимает границы таблицы и заголовки.

Многоуровневая сортировка

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

Сценарий: отсортировать продажи по городу, а внутри города — по сумме (убывание).

Вкладка Данные → Сортировка.

Уровень 1: Город (А→Я).

Уровень 2: Сумма (по убыванию).

Важные нюансы сортировки

Если Excel предлагает «расширить выделение», обычно это правильно: строки должны перемещаться целиком, чтобы не «разъехались» столбцы.

Заголовки должны быть в одной строке, без объединённых ячеек.

Пустые строки внутри диапазона часто мешают: Excel воспринимает данные как несколько отдельных блоков.

Смешение типов (числа и текст в одном столбце) приводит к странному порядку сортировки.

Фильтры: показываем только нужное

Фильтр скрывает строки, которые не подходят под условия, но не удаляет их. Это основной инструмент, когда нужно быстро ответить на вопрос «покажи только…».

Включение фильтра

Для умной таблицы фильтры включены автоматически в заголовках.

Для обычного диапазона: выделите строку заголовков и включите Данные → Фильтр.

Типовые фильтры

по значениям (поставить галочки у нужных)

текстовые фильтры (содержит, начинается с)

числовые фильтры (больше, меньше, между)

фильтры по датам (за период, по месяцам)

Практический приём: в списке значений фильтра используйте строку поиска — это ускоряет работу в больших таблицах.

Фильтрация в связке с расчётами

Фильтр помогает проверять формулы:

отфильтруйте один город или один менеджер

проверьте, что суммы и логика IF ведут себя ожидаемо

найдите строки, где появился #VALUE! или #DIV/0!

Важно: стандартные функции вроде SUM считают и скрытые строки тоже. Если вам нужно суммировать только видимые строки, обычно используют специальные инструменты (к ним вы вернётесь позже, когда будете изучать более продвинутый анализ).

Удаление дублей и поиск повторов

Дубликаты — частая причина завышенных итогов. Они появляются после копирования, объединения файлов, повторного экспорта данных.

Быстро удалить дубликаты

Кликните внутри таблицы.

Вкладка Данные → Удалить дубликаты.

Выберите столбцы, по которым определяется уникальность строки.

Ключевой момент: уникальность почти всегда определяется несколькими столбцами. Например, строка продаж может быть уникальна по комбинации Дата + Документ + Товар.

Визуально подсветить повторы

Если вы не готовы удалять сразу, можно сначала подсветить повторяющиеся значения:

вкладка Главная → Условное форматирование → правила выделения ячеек → повторяющиеся значения

Это удобно как этап проверки перед удалением.

Проверка данных: ограничиваем неправильный ввод

Проверка данных — это правила, которые не дают пользователю (или вам через месяц) ввести «плохие» значения. Это особенно важно для таблиц, которые заполняются вручную.

Что можно ограничить

список допустимых значений (выпадающий список)

диапазон чисел (например, количество от 1 до 1000)

даты (например, не раньше 01.01.2025)

длину текста (например, код ровно 10 символов)

Пример: выпадающий список для статуса

Сценарий: столбец Статус должен содержать только Новый, В работе, Закрыт.

Подготовьте список статусов в отдельном диапазоне (лучше на листе Справочники).

Выделите столбец Статус.

Вкладка Данные → Проверка данных.

Тип: Список, источник: диапазон со статусами.

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

Подсказка ввода и сообщение об ошибке

В окне проверки данных можно настроить:

сообщение для ввода (подсказка, что ожидается)

сообщение об ошибке (что делать, если введено неверно)

Это превращает таблицу в простой и понятный инструмент ввода.

Очистка данных: приводим к единому виду

Очистка — это набор действий, которые делают данные «ровными»: без лишних пробелов, с одинаковыми форматами, с корректными типами.

Лишние пробелы и невидимые символы

Частый случай: "Москва" и "Москва " выглядят одинаково, но для Excel это разные значения.

Что делать:

используйте функцию TRIM (убирает лишние пробелы внутри и по краям)

если данные пришли из внешней системы и содержат «мусорные» символы, иногда помогает CLEAN

Лучше применять очистку в отдельном вспомогательном столбце, а потом при необходимости заменить исходные значения через вставку только значения.

Числа, которые стали текстом

Признаки:

числа выравниваются по левому краю (часто, но не всегда)

формулы дают #VALUE!

SUM не увеличивается, хотя вы добавили «число»

Частые способы исправления:

если Excel показывает предупреждение, выберите Преобразовать в число

используйте инструмент Данные → Текст по столбцам (часто «лечит» тип данных даже без реального разделения)

примените функцию VALUE, если данные нужно привести формулой

Приведение дат к корректному виду

Даты должны быть датами, а не текстом. Иначе фильтры «по месяцам» и сортировка по времени работают неправильно.

Что помогает:

единый формат ввода (через проверку данных)

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

Разделение текста на части

Если в одном столбце хранится несколько смыслов (например, "Иванов И.И." или "Москва, Тверская"), дальше анализировать неудобно.

Используйте:

Данные → Текст по столбцам (разделение по разделителю)

Мгновенное заполнение (Flash Fill), когда Excel может угадать шаблон по вашим примерам

Мини-чеклист перед анализом и формулами

Убедитесь, что у таблицы одна строка заголовков и нет объединённых ячеек.

Проверьте типы данных: числа — числа, даты — даты.

Уберите лишние пробелы в ключевых текстовых столбцах.

Найдите и обработайте дубликаты.

Настройте проверку данных для столбцов, которые будут заполняться вручную.

Только после этого переходите к расчётам формулами и построению отчётов.

Что дальше по курсу

После сортировки, фильтров и очистки вы получаете самое ценное: надёжную таблицу, которой можно доверять. На такой базе формулы работают предсказуемо, а отчёты не «плывут» из-за случайных опечаток.

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

Сводные таблицы и сводные диаграммы для анализа

Сводные таблицы (PivotTable) — один из самых мощных инструментов Excel для анализа данных без сложных формул. Они позволяют быстро превращать «сырую» таблицу с тысячами строк в понятный отчёт: суммы по городам, количество заказов по менеджерам, динамика продаж по месяцам.

Эта тема логично продолжает предыдущие:

из статьи про интерфейс и умные таблицы вы берёте правильную структуру данных и удобство расширения диапазона

из статьи про формулы — понимание чисел, дат и форматов (чтобы итоги считались корректно)

из статьи про сортировку, фильтры и очистку — подготовку данных (без неё сводные часто дают «странные» результаты)

!Схема показывает логику сборки сводной таблицы перетаскиванием полей

Когда сводные таблицы действительно нужны

Сводная таблица подходит, когда у вас есть список операций (много строк однотипной структуры), и вы хотите быстро ответить на вопросы вида:

сколько было продаж по каждому городу

какая сумма продаж по каждому менеджеру и месяцу

какие 10 товаров приносят больше всего выручки

сколько заказов в каждом статусе

Если данные уже выглядят как «готовый отчёт» (много объединённых ячеек, шапка в несколько строк, отдельные блоки), сначала их нужно привести к табличному виду.

Базовые понятия: из чего состоит сводная

Сводная таблица строится из полей исходной таблицы:

поля измерений — по ним вы «разрезаете» данные (Город, Менеджер, Товар, Статус, Дата)

поля показателей — то, что вы агрегируете (Сумма, Количество, Маржа)

Главная идея сводной: Excel группирует строки по выбранным измерениям и считает итог по показателям.

Подготовка данных перед построением сводной

Сводная таблица «любит» аккуратный источник данных. Используйте чеклист из предыдущей темы и добавьте два правила:

одна строка = одна запись (одна продажа/операция/заказ)

один столбец = один смысл (дата отдельно, сумма отдельно, статус отдельно)

Особенно важно:

заголовки должны быть в одной строке, без пустых названий столбцов

внутри диапазона не должно быть полностью пустых строк и столбцов

числа должны быть числами, даты — датами (а не текстом)

Почему лучше начинать с умной таблицы

Если оформить данные как умную таблицу (Excel Table), сводная станет надёжнее:

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

заголовки фиксированы и понятны

проще выбирать диапазон при создании отчёта

Как создать сводную таблицу

Кликните любую ячейку внутри вашей исходной таблицы (лучше умной таблицы).

Откройте Вставка → Сводная таблица.

Проверьте диапазон источника.

Выберите, куда разместить отчёт: на новый лист (обычно удобнее) или на текущий.

Нажмите ОК.

Официальная инструкция: Создание сводной таблицы для анализа данных листа

Панель полей: куда что перетаскивать

После создания появится список полей и четыре зоны размещения. Их проще запомнить по смыслу.

| Зона | Что делает | Пример результата | |---|---|---| | Строки | Формирует строки отчёта (группы) | Города списком вниз | | Столбцы | Формирует «разворот» по столбцам | Месяцы в заголовках | | Значения | Считает итоги по числам | Сумма продаж, количество | | Фильтры | Фильтрует весь отчёт целиком | Показать только 2026 год |

Практический принцип: сначала соберите простую сводную Строки + Значения, и только потом усложняйте.

Первый отчёт: сумма продаж по городам

Допустим, у вас есть столбцы: Дата, Город, Менеджер, Товар, Количество, Сумма.

Соберите отчёт:

Перетащите Город в Строки.

Перетащите Сумма в Значения.

Excel создаст список городов и посчитает итог.

Почему иногда получается «Количество», а не «Сумма»

Если поле в Значения воспринимается как текст (например, в сумме есть пробелы, символы или часть значений текстом), Excel может автоматически выбрать не Сумма, а Количество.

Что сделать:

вернуться к источнику и привести столбец к числовому типу (см. приёмы очистки из предыдущей статьи)

в сводной открыть настройки поля значений и поменять тип расчёта

Настройки поля значений: что именно считать

В зоне Значения важны две вещи:

функция агрегации (сумма, количество, среднее, максимум и т.д.)

формат числа (деньги, целые, проценты)

Типовые варианты:

Сумма — для выручки, затрат, количества (если количество хранится числом)

Количество — чтобы посчитать число строк/заказов

Среднее — средний чек, средняя скидка

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

Несколько измерений: отчёт в разрезах

Сводная раскрывается, когда вы добавляете второй и третий разрез.

Примеры конструкций:

Город в Строки, Менеджер тоже в Строки (вторым уровнем), Сумма в Значения

Город в Строки, Месяц (из даты) в Столбцы, Сумма в Значения

Товар в Строки, Сумма в Значения, а Статус в Фильтры

Если отчёт стал «слишком широким», чаще всего виновата зона Столбцы: она быстро раздувает число колонок.

Группировка дат: месяцы, кварталы, годы

Если в источнике есть корректные даты, сводная умеет группировать их.

Сценарий: нужно увидеть продажи по месяцам.

Перетащите Дата в Строки или Столбцы.

Кликните по любой дате в сводной.

Выберите группировку и отметьте, например, Месяцы и Годы.

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

Обновление сводной: почему цифры «не меняются»

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

Хорошая практика:

хранить источник как умную таблицу (тогда новые строки попадут в источник диапазона)

после обновления данных выполнять обновление сводной таблицы

Если у вас несколько сводных отчётов на разных листах, обновляйте их согласованно, чтобы в отчётах не было «разных версий правды».

Сводная диаграмма: визуализация, которая связана с отчётом

Сводная диаграмма — это диаграмма, построенная на базе сводной таблицы. Её ключевое отличие от обычной диаграммы:

она «живёт» вместе со сводной: меняете разрезы и фильтры — диаграмма меняется тоже

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

Как создать сводную диаграмму

Кликните внутри сводной таблицы.

Откройте Вставка → выберите диаграмму (или команду вставки диаграммы для сводной).

Выберите тип: столбчатая — для сравнения категорий, линейная — для динамики по времени.

Практический совет: для динамики почти всегда выигрывают линейные диаграммы, а для рейтингов (топ товаров/городов) — столбчатые.

Срезы и шкала времени: фильтрация «в один клик»

Фильтры в шапке сводной удобны, но для отчётов и презентаций часто лучше подходят срезы.

срез — панель кнопок, которая фильтрует сводную по выбранному полю (например, по Городу или Менеджеру)

шкала времени — специальный фильтр по датам (месяцы, кварталы, годы)

Их преимущество: пользователь не открывает выпадающие списки, а видит активные фильтры сразу.

Хорошая практика для отчётного листа:

сверху разместить 2–4 среза по ключевым полям

рядом разместить 1–2 сводные диаграммы

ниже — сводные таблицы с детализацией

Типичные ошибки и как их избегать

Ошибка про некорректное имя поля: обычно в источнике есть пустой заголовок столбца или объединённые ячейки в строке заголовков. Решение: один ряд заголовков, все поля названы.

Вместо суммы считается количество: в числовом столбце есть текст или «числа как текст». Решение: очистка и приведение типов в источнике.

Сводная не видит новые строки: источник — обычный диапазон, который не расширился. Решение: перевести источник в умную таблицу.

Отчёт выглядит «рваным»: в тексте есть лишние пробелы, разный регистр, разные написания. Решение: чистка (например, TRIM) и стандартизация значений до сводной.

Мини-чеклист перед тем, как отправлять отчёт

источник оформлен как умная таблица и содержит только один блок данных

заголовки заполнены и находятся в одной строке

типы данных корректны (даты — даты, числа — числа)

сводные обновлены после последнего изменения данных

значения отформатированы (валюта, разряды)

фильтры и срезы понятны (видно, что именно сейчас выбрано)

Что дальше

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

Визуализация и оформление отчётов: диаграммы и условное форматирование

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

структурировать данные и работать с умными таблицами

считать показатели формулами

готовить данные (фильтры, очистка)

собирать итоги в сводных таблицах и сводных диаграммах

Теперь добавим слой, который превращает «таблицу с числами» в управляемый отчёт: диаграммы и условное форматирование.

!Пример листа отчёта: таблица + диаграмма + условное форматирование

Что делает визуализация «правильной» в Excel

Цель визуализации — ускорить ответы на вопросы, которые вы уже умеете считать:

где больше всего продаж

что растёт или падает во времени

где отклонения и аномалии

какие значения требуют внимания

Практические принципы:

Один график — один вопрос

Данные должны быть подготовлены: даты — датами, числа — числами, без дублей и «Москва/Москва » (это из темы про очистку)

Лучше меньше цветов, но одинаковый смысл: например, красный всегда про проблему, зелёный — про норму

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

Диаграммы: как выбрать тип под задачу

Диаграмма в Excel строится по диапазону (или по умной таблице) и отображает взаимосвязь данных.

Ключевые элементы диаграммы:

Ось — шкала (например, сумма по вертикали)

Ряд данных — набор значений, который рисуется (например, «Выручка»)

Категории — подписи (например, города или месяцы)

Легенда — расшифровка рядов

Подписи данных — числа прямо на графике

Официальная справка: Создание диаграммы от начала до конца

Быстрая таблица выбора диаграммы

| Задача | Лучший тип диаграммы | Почему | |---|---|---| | Сравнить категории (города, товары) | Столбчатая | Хорошо видно «кто больше/меньше» | | Показать динамику по времени | Линейная | Лучше всего показывает тренд | | Показать структуру в 100% | Нормированная (100%) столбчатая | Удобно сравнивать доли | | Найти выбросы и разброс | Точечная | Видно, как точки «разлетаются» |

Важно: круговая диаграмма часто используется «по привычке», но для сравнения многих категорий обычно хуже столбчатой.

Как построить диаграмму правильно (на обычной таблице и на умной)

Диаграмма по диапазону

Проверьте, что в данных есть заголовки столбцов.

Выделите диапазон с данными.

Вкладка Вставка → выберите тип диаграммы.

Проверьте, что Excel правильно понял, где категории и где значения.

Диаграмма по умной таблице

Если ваш источник оформлен как умная таблица:

диаграмма проще «держится» за правильные столбцы

при добавлении новых строк диаграмма часто расширяется автоматически

Практический совет: если отчёт обновляется регулярно, почти всегда выгодно держать источник в формате умной таблицы.

Частые настройки, которые делают диаграмму «отчётной», а не «учебной»

Заголовок и подписи

добавьте понятный заголовок: не «Сумма», а «Выручка по городам, ₽»

включайте подписи данных, когда важны точные значения (например, топ-5 товаров)

форматируйте числа (разряды, валюта) так же, как в таблице

Справка: Добавление или удаление названий диаграмм, осей или подписей данных

Оси и масштаб

Неправильный масштаб может визуально «преувеличить» изменения.

проверяйте, где начинается ось

одинаковые графики сравнивайте с одинаковыми шкалами

Цвет и акцент

один цвет для основного ряда

второй цвет только для выделения важного (например, план/факт или выбранный сегмент)

избегайте пёстрых палитр без смысла

Сортировка источника

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

отсортировать источник по убыванию показателя

показывать топ (например, топ-10), а не «всё на свете»

Это напрямую опирается на навыки сортировки и фильтрации из предыдущей темы.

Сводные диаграммы в отчётах

Сводная диаграмма удобна, когда у вас уже есть сводная таблица:

меняете разрезы в сводной — меняется диаграмма

легко делать интерактивные отчёты с фильтрами

Если вы готовите отчёт для руководителя, часто лучше:

собрать сводную таблицу как «двигатель»

на её основе построить сводную диаграмму

добавить удобные фильтры (например, по году или региону)

Справка: Создание сводной диаграммы

Условное форматирование: подсветка смысла прямо в таблице

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

Типовые задачи:

подсветить просрочки и отклонения

показать «тепловую карту» по значениям

выделить топ/анти-топ

отметить дубликаты или пустые значения

Справка: Применение условного форматирования

Основные виды правил

#### Правила выделения ячеек

Подходят для простых условий:

больше/меньше

между

равно

содержит текст

Пример: выделить красным суммы меньше 0 или маржу меньше 5%.

#### Полосы данных, цветовые шкалы, наборы значков

Полосы данных показывают «длину» значения прямо в ячейке

Цветовые шкалы создают эффект тепловой карты

Значки (стрелки, светофор) полезны для статусов

Практический совет: эти варианты хорошо работают на больших таблицах, но могут мешать чтению, если включить их «везде и сразу».

!Примеры трёх популярных видов условного форматирования

Правила на основе формулы: самый гибкий вариант

Иногда готовых правил недостаточно. Тогда используется правило типа «Использовать формулу для определения форматируемых ячеек».

Примеры сценариев:

выделять всю строку заказа, если статус равен Просрочен

выделять сделки, где скидка выше допустимой

подсвечивать строки, где поле Сумма пустое, а Количество заполнено

Ключевой момент: формула должна возвращать TRUE/FALSE (истина/ложь). Если условие истинно — формат применяется.

Практический приём для строк таблицы:

закрепляйте столбцы знаком $, чтобы правило работало на весь диапазон строк (это опирается на тему про абсолютные и смешанные ссылки)

Справка: Использование формулы в условном форматировании

Управление правилами: порядок, диапазон и «почему всё сломалось»

Если условного форматирования много, важно уметь им управлять.

Что проверять в первую очередь:

Диапазон применения: формат может примениться только к части столбца или «уехать» на соседние столбцы

Порядок правил: иногда правило сверху перекрывает правило снизу

Копирование форматов: при вставке данных можно случайно скопировать и правила

Инструмент для контроля:

вкладка Главная → Условное форматирование → Управление правилами

Полезная практика: держать 3–7 понятных правил вместо 20 мелких, которые никто не обслуживает.

Оформление отчётного листа: чтобы читать было легко

Что стоит стандартизировать

единые форматы чисел (валюта, проценты, разряды)

одинаковые шрифты и размеры

одинаковые цвета для одинакового смысла

аккуратные заголовки и подписи (без «Сумма2» и «Итого3»)

Типовая компоновка отчёта на одном листе

сверху: фильтры/период/контекст

ниже: 1–2 диаграммы, отвечающие на ключевые вопросы

ниже: таблица детализации с условным форматированием

Печать и экспорт

Если отчёт нужно печатать или отправлять PDF, обычно важны:

ориентация страницы

поля

повтор строк заголовков

Справка: Настройка листа для печати

Типичные ошибки в диаграммах и условном форматировании

Диаграмма построена по «грязным» данным: лишние пробелы или текст вместо чисел дают неверные группы и итоги

Слишком много элементов: легенда на 15 серий, подписи на каждом столбце и яркие цвета одновременно

Неправильный тип диаграммы: динамику по времени показывают столбцами без смысла, а сравнение категорий делают кругом

Условное форматирование не на весь диапазон: часть строк без подсветки из-за неправильного диапазона применения

Правила конфликтуют: порядок правил не контролируется

Связь с курсом и что дальше

Визуализация не заменяет расчёты и подготовку данных, а делает их результат понятным.

умные таблицы помогают диаграммам и правилам форматирования устойчиво расширяться

формулы дают корректные показатели для сравнения

очистка данных делает группировки и фильтры «чистыми»

сводные таблицы часто становятся источником интерактивных диаграмм

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

Продвинутые функции: поиск, массивы, работа с текстом и датами

После базовых формул, подготовки данных и сводных таблиц обычно появляется следующий запрос: как быстро соединять таблицы, строить «умные» выборки и приводить данные к нужному виду без ручной правки. В этой статье вы разберёте продвинутый набор функций, который чаще всего нужен в реальной работе:

поиск и подстановка значений между таблицами

динамические массивы (формулы, которые возвращают сразу диапазон)

работа с текстом (очистка, извлечение частей, объединение)

работа с датами (месяцы, границы периода, разница между датами)

Эти инструменты особенно хорошо раскрываются, когда исходные данные оформлены как умные таблицы и очищены (темы про структуру данных и подготовку данных), а результаты используются в отчётах и визуализации.

!Схема показывает идею VLOOKUP/XLOOKUP: подстановка из справочника по ключу

Поиск и подстановка: как «склеивать» таблицы по ключу

Что такое ключ и почему без него всё ломается

Ключ — это поле, по которому вы однозначно находите запись в справочнике.

Примеры ключей:

артикул товара

табельный номер сотрудника

номер заказа

Практическое правило: сначала добейтесь чистоты ключа (лишние пробелы, разные регистры, ошибки ввода), и только потом настраивайте поиск. Это напрямую опирается на приёмы очистки из темы про подготовку данных.

XLOOKUP: современная функция поиска

Функция XLOOKUP умеет искать значение в одном столбце и возвращать соответствующее значение из другого столбца.

Типовой сценарий: по коду товара подтянуть из справочника Название и Цена.

Пример логики:

что ищем — код товара из строки заказа

где ищем — столбец Код товара в справочнике

что возвращаем — столбец Цена или Название из справочника

Плюсы XLOOKUP по сравнению с классическим VLOOKUP:

можно искать и влево, и вправо

проще управлять поведением при «не найдено»

можно задавать тип совпадения

Официальная справка: Функция XLOOKUP

VLOOKUP: классика, которую всё ещё встречают

VLOOKUP (в русской локализации часто ВПР) всё ещё часто встречается в файлах и шаблонах.

Ограничения VLOOKUP, которые важно помнить:

поиск идёт только по первому столбцу выбранного диапазона

вернуть можно только столбцы правее этого первого столбца

часто «ломается», если внутри диапазона перемещают/вставляют столбцы (потому что используется номер столбца)

Официальная справка: Функция VLOOKUP

INDEX + MATCH: надёжная связка для сложных случаев

Связка INDEX + MATCH полезна, когда:

нужно контролировать, по какому столбцу ищем, и из какого возвращаем

важна устойчивость к перестановке столбцов

вы работаете в версии Excel без XLOOKUP

Идея:

MATCH находит позицию ключа

INDEX возвращает значение из нужного столбца по найденной позиции

Официальная справка:

Функция INDEX

Функция MATCH

Как обрабатывать ситуацию «не найдено»

В поисковых формулах очень важно заранее решить, что делать, если ключа нет в справочнике.

Типовые варианты:

показывать пусто

показывать текст Нет в справочнике

показывать 0 (редко полезно, но иногда нужно для расчётов)

Если вы используете формулы, которые могут давать ошибки, используйте IFERROR как последний слой защиты.

Справка: Функция IFERROR

Динамические массивы: формулы, которые возвращают диапазон

Динамические массивы — это формулы, которые могут вернуть сразу несколько значений и «разлиться» на соседние ячейки. Это особенно удобно для построения автоматических списков для отчётов и контроля качества данных.

Важно: эти функции доступны в современных версиях Excel (в первую очередь Microsoft 365). В более старых версиях часть примеров может быть недоступна.

!Схема объясняет, как работает разлив динамических массивов и почему появляется #SPILL!

FILTER: отобрать строки по условию

FILTER возвращает только те строки диапазона, которые удовлетворяют условию.

Сценарии:

показать только заказы выбранного менеджера

вывести только строки, где сумма больше порога

построить витрину данных для диаграммы

Справка: Функция FILTER

SORT и SORTBY: сортировка формулой

SORT сортирует диапазон и возвращает отсортированную «копию».

SORTBY позволяет сортировать один диапазон по другому (например, вывести товары, отсортированные по выручке).

Справка:

Функция SORT

Функция SORTBY

UNIQUE: список уникальных значений

UNIQUE помогает получить список уникальных значений из столбца.

Практические применения:

список уникальных городов для выпадающего списка

список менеджеров для фильтра отчёта

быстрый контроль «грязных» значений (например, увидеть варианты написания одного города)

Справка: Функция UNIQUE

SEQUENCE: генерация последовательностей

SEQUENCE создаёт последовательность чисел (часто используется как «техническая» функция для построения календарей, нумерации или тестовых данных).

Справка: Функция SEQUENCE

LET: сделать формулу читабельнее и быстрее

LET позволяет задать имена для промежуточных вычислений внутри формулы.

Это полезно, когда:

формула длинная

один и тот же фрагмент повторяется несколько раз

вы хотите уменьшить риск ошибки и упростить поддержку

Справка: Функция LET

Работа с текстом: очистка, извлечение, объединение

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

Очистка текста: TRIM, CLEAN, SUBSTITUTE

TRIM удаляет лишние пробелы (в начале, в конце и повторяющиеся внутри).

CLEAN убирает непечатаемые символы, которые часто приходят из внешних систем.

SUBSTITUTE заменяет один фрагмент текста на другой.

Справка:

Функция TRIM

Функция CLEAN

Функция SUBSTITUTE

Практический приём: часто используют связку «сначала CLEAN, потом TRIM», чтобы привести ключи к единому виду перед XLOOKUP.

Извлечение частей строки: LEFT, RIGHT, MID

Эти функции помогают «достать» часть текста по позиции:

LEFT — слева

RIGHT — справа

MID — из середины

Справка:

Функция LEFT

Функция RIGHT

Функция MID

Чтобы извлечение было осмысленным, часто сначала находят позицию разделителя.

Поиск внутри текста: FIND и SEARCH

FIND ищет текст с учётом регистра.

SEARCH ищет без учёта регистра.

Справка:

Функция FIND

Функция SEARCH

Пример задачи: в строке вида Город - Магазин найти позицию дефиса, а затем LEFT/MID разделить на части.

Объединение текста: CONCAT и TEXTJOIN

CONCAT склеивает фрагменты текста.

TEXTJOIN склеивает диапазон с заданным разделителем и умеет игнорировать пустые.

Справка:

Функция CONCAT

Функция TEXTJOIN

TEXTJOIN полезен, например, чтобы собрать строку комментария из нескольких полей или аккуратно объединить элементы списка.

Работа с датами: периоды, границы месяца, разница

Даты в Excel — это основа отчётности «по месяцам» и «за период». Главное условие: дата должна быть датой, а не текстом (это вы проверяете на этапе подготовки данных).

Базовые функции даты

TODAY возвращает текущую дату.

NOW возвращает текущие дату и время.

YEAR, MONTH, DAY извлекают год, месяц и день.

DATE собирает дату из года, месяца и дня.

Справка:

Функция TODAY

Функция NOW

Функции YEAR, MONTH, DAY

Функция DATE

EOMONTH и EDATE: управление месяцами

EOMONTH возвращает последний день месяца со сдвигом на нужное число месяцев.

EDATE сдвигает дату на нужное число месяцев и возвращает дату.

Применения:

быстро получить границы месяца для отчёта

посчитать дату «плюс 3 месяца»

построить календарный ряд периодов

Справка:

Функция EOMONTH

Функция EDATE

Разница между датами: DATEDIF

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

Важно: функция поддерживается Excel, но исторически считается «особой» (в некоторых версиях она не отображается в списках подсказок). Тем не менее, она документирована Microsoft.

Справка: Функция DATEDIF

Практические комбинации, которые часто нужны в работе

Поиск + очистка

Типовая задача: ключи пришли с пробелами или «мусорными» символами, и поиск не находит совпадение.

Подход:

создать очищенный ключ через CLEAN и TRIM

использовать этот ключ в XLOOKUP

UNIQUE + SORT + FILTER для «витрины» отчёта

Типовая задача: получить список уникальных клиентов за выбранный период и отсортировать по выручке.

Подход:

FILTER отбирает строки периода

UNIQUE оставляет уникальные значения

SORT или SORTBY формирует порядок

Даты + сводные таблицы

Если вы добавляете в источник столбец Месяц отчёта (например, через EOMONTH или через извлечение YEAR/MONTH), то:

сводные таблицы группируют данные стабильнее

диаграммы по времени читаются проще

Частые ошибки и как их быстро диагностировать

| Симптом | Частая причина | Что сделать | |---|---|---| | XLOOKUP не находит, хотя «визуально одинаково» | лишние пробелы, непечатаемые символы | применить TRIM и CLEAN к ключам | | вместо ожидаемого списка формула даёт #SPILL! | рядом заняты ячейки, мешают «разливу» | очистить/освободить диапазон разлива | | даты сортируются «как текст» | дата импортирована строкой | привести тип данных, проверить формат и источник | | поиск возвращает «не то значение» | ключ не уникален в справочнике | обеспечить уникальность ключа или менять логику |

Как это связывается с предыдущими темами курса

Из темы про умные таблицы вы берёте структурированный источник, который расширяется без правок диапазонов.

Из темы про формулы — правильные ссылки и контроль ошибок через IFERROR.

Из темы про подготовку данных — чистые ключи и корректные типы (числа и даты).

Из темы про сводные таблицы и визуализацию — готовые разрезы и показатели, которые теперь можно собирать более гибко с помощью массивов и поиска.

Следующий логичный шаг после освоения этих функций — собирать устойчивые отчётные модели, где справочники, факты и расчёты обновляются быстро и без ручной доработки.

Автоматизация: Power Query, основы макросов и запись действий

Рано или поздно в Excel появляется типичная боль: одни и те же действия приходится повторять каждую неделю. Вы снова копируете данные из отчёта, чистите пробелы, приводите даты, объединяете таблицы, пересобираете сводные и обновляете диаграммы.

В предыдущих темах курса вы научились:

структурировать данные и использовать умные таблицы

считать показатели формулами

очищать и проверять данные

строить сводные таблицы и визуализации

применять продвинутые функции (поиск, массивы, текст, даты)

Следующий логичный шаг — автоматизировать повторяющиеся процессы. В Excel для этого чаще всего используют два инструмента:

Power Query — автоматизация загрузки, объединения и подготовки данных (ETL-подход: извлечь, преобразовать, загрузить)

Макросы (VBA) и запись действий — автоматизация повторяемых действий интерфейса (форматирование, экспорт, копирование, подготовка листов отчёта)

!Общая картина: Power Query готовит данные, а макросы автоматизируют действия на листах

Как выбрать инструмент: Power Query или макрос

Power Query и макросы решают разные задачи. Удобно запомнить правило:

Power Query — когда вы готовите данные (импорт, очистка, объединение, типы, справочники)

Макросы — когда вы делаете действия (копировать, вставить, форматировать, создать лист, выгрузить PDF)

Короткая таблица выбора

| Ситуация | Лучше выбрать | Почему | |---|---|---| | Каждый месяц приходит новый CSV, нужно привести столбцы и типы | Power Query | Настроили один раз, дальше только обновляете | | Нужно объединить 30 файлов из папки в одну таблицу | Power Query | Это типовая задача «Собрать из папки» | | Нужно подтянуть атрибуты из справочника по ключу | Power Query или формулы | Power Query удобен на этапе подготовки, формулы — в расчётной модели | | Нужно одним кликом оформить отчётный лист и сохранить в PDF | Макрос | Это последовательность действий интерфейса | | Нужно нажатием кнопки обновить данные и сводные | Power Query + (опционально) макрос | Power Query обновляет запросы, макрос может запускать «обновить всё» и финальные шаги |

Power Query: что это и чем он отличается от формул

Power Query — встроенный инструмент Excel для загрузки и преобразования данных. Вы выполняете шаги подготовки данных в отдельном редакторе, а Excel сохраняет их как последовательность преобразований.

Ключевые отличия от «ручной чистки» на листе:

преобразования можно повторять при каждом обновлении

источник может быть внешним (файлы, папка, база, веб)

вы работаете с шагами (их можно переупорядочить, отключить, исправить)

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

Справка Microsoft: Power Query для Excel: справка и обучение

Базовый рабочий процесс Power Query

Где находится Power Query

В Excel (особенно Microsoft 365) Power Query обычно находится на вкладке Данные в группе Получение и преобразование данных.

Типовой цикл «настроил один раз — обновляю всегда»

Подключиться к источнику (CSV, Excel, папка, таблица на листе)

Открыть редактор Power Query

Выполнить преобразования (очистка, типы, разбиение, объединение)

Загрузить результат в Excel (таблица или модель данных)

В дальнейшем нажимать Обновить

Практические преобразования в Power Query, которые заменяют ручную работу

Ниже — наиболее частые операции, которые напрямую связаны с темами курса про очистку данных, сводные и отчёты.

Типы данных: основа правильных итогов

Power Query умеет явно задавать тип столбца: число, дата, текст, логический.

Почему это важно:

сводные таблицы перестают «считать количество вместо суммы»

группировка дат становится корректной

фильтры и сортировка работают предсказуемо

Практический совет: на раннем этапе всегда проверьте типы ключевых колонок (дата, сумма, количество, ключи справочников).

Удаление лишнего и выбор нужного

Самый простой способ ускорить модель:

удалить ненужные столбцы

оставить только поля, которые реально участвуют в анализе

Это снижает размер файла и уменьшает количество ошибок.

Разделение и объединение столбцов

Power Query удобно применяют, когда «в одном столбце несколько смыслов», например:

Город, улица нужно разделить

Код-версия нужно разнести по отдельным полям

Это соответствует принципу из ранних тем: один столбец — один смысл.

Фильтрация строк и контроль качества

В Power Query можно:

исключать строки с пустыми ключами

оставлять только нужный период

убирать «мусорные» строки (итоги, шапки, комментарии), которые часто попадаются в выгрузках

Удаление дублей

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

Важно: заранее определить, по каким колонкам строка считается дублем (например, Дата + Номер заказа + Товар).

Объединение таблиц: Merge (аналог XLOOKUP на этапе подготовки)

Power Query умеет соединять таблицы по ключу (операция объединения, часто называется Merge).

Сценарий:

есть таблица продаж (факт)

есть справочник товаров (название, категория)

нужно добавить категорию в продажи по ключу Код товара

Это функционально похоже на XLOOKUP, но делается на этапе подготовки данных, а не расчёта на листе.

Склейка файлов из папки

Одна из самых сильных сторон Power Query — сбор данных из множества файлов одинакового формата.

Типовая задача:

в папке каждый день появляется новый CSV

структура колонок одинакова

нужно собрать всё в одну таблицу и обновлять одной кнопкой

Результат: вы добавляете новый файл в папку, нажимаете Обновить, и таблица расширяется без ручной работы.

!Как выглядит логика Power Query: запрос и список шагов преобразования

Загрузка результатов: таблица или модель данных

После преобразований Power Query предлагает варианты загрузки.

Загрузка в таблицу на лист

Подходит, если:

вы хотите использовать результат как обычную умную таблицу

дальше планируете формулы, условное форматирование, диаграммы

Загрузка в модель данных

Подходит, если:

данных много

планируется несколько связанных таблиц (факт + справочники)

вы строите сводные на базе модели (это часто даёт больше возможностей)

Обновление: как встроить Power Query в отчётный процесс

Важная идея: Power Query не «правит» исходные файлы — он строит результат заново при обновлении.

Хорошая практика:

хранить исходники отдельно и не редактировать их вручную

в Excel держать лист Raw (необязательно видимый пользователю), а отчёты — на отдельных листах

обновлять данные через Данные → Обновить всё

Справка Microsoft: Обновление внешнего подключения данных в Excel

Основы макросов: что это и когда они полезны

Макрос — это автоматизация действий в Excel с помощью VBA (Visual Basic for Applications). На старте чаще всего используют запись макроса, чтобы Excel сам сгенерировал код по вашим действиям.

Важно понимать границу ответственности:

Power Query отвечает за данные

макросы чаще отвечают за операции на листах и сценарии «нажми кнопку — получи результат»

Примеры задач, где макросы особенно уместны

создать новый лист отчёта по шаблону

очистить старые выгрузки, вставить новые значения, обновить сводные

проставить форматирование и ширины столбцов

выгрузить отчёт в PDF и сохранить в папку

сделать кнопку «Обновить отчёт» для пользователя

Безопасность макросов: что важно знать

Макросы могут выполнять действия на компьютере пользователя, поэтому Office защищает файлы с макросами.

Практические правила:

доверяйте макросам только из понятных источников

используйте форматы файлов с макросами осознанно (обычно *.xlsm)

не включайте макросы в файле, происхождение которого неизвестно

Справка Microsoft: Включение или отключение макросов в файлах Office

Запись макроса: самый быстрый вход

Как записать макрос

Откройте вкладку Разработчик (если её нет, включите в настройках ленты)

Нажмите Записать макрос

Задайте имя макроса (без пробелов)

Выберите место хранения (часто: текущая книга)

Выполните действия в Excel

Нажмите Остановить запись

Справка Microsoft: Запись макроса

Абсолютные и относительные ссылки при записи

При записи макроса Excel может «запоминать» действия по-разному:

абсолютно (перейти в конкретную ячейку B2)

относительно (сделать шаг от текущей позиции)

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

Где смотреть и как понимать записанный VBA-код

Записанный макрос можно открыть в редакторе VBA и увидеть команды. Даже если вы не планируете программировать, полезно уметь распознать три вещи:

какой лист и диапазон затрагивается

какие операции выполняются (копирование, вставка, формат)

есть ли «жёсткие» ссылки, которые сломаются при смене структуры

Справка Microsoft: Знакомство с Visual Basic для приложений

Типовые ограничения записи макросов

Запись макроса удобна, но важно трезво оценивать ограничения:

не все действия записываются «красиво» и надёжно

записанный код часто содержит лишние переходы и выборы диапазонов

при изменении структуры листа (вставили столбец, переименовали лист) макрос может перестать работать

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

Сценарий «сильной связки»: Power Query + сводные + макрокнопка

Один из самых рабочих паттернов в Excel-отчётности:

Power Query забирает и чистит данные

результат загружается в таблицу

сводные и диаграммы строятся на базе этой таблицы

макрос по кнопке делает «обновить всё» и приводит отчёт к финальному виду

Преимущество: вы отделяете данные от оформления и получаете отчёт, который обновляется предсказуемо.

Практические рекомендации по архитектуре файла

Чтобы автоматизация не превратилась в хрупкий набор «магии», придерживайтесь простых правил:

держите исходники отдельно, не редактируйте их вручную

в книге разделяйте листы: Данные (загрузка), Справочники, Отчёт

давайте понятные имена запросам и таблицам

фиксируйте ключи и типы данных (это напрямую связано с темами про подготовку данных)

избегайте ручных правок внутри выгруженной таблицы Power Query (они исчезнут после обновления)

Что дальше

После этой темы у вас появляется «профессиональная связка»:

Power Query готовит данные устойчиво

формулы и продвинутые функции считают показатели

сводные и диаграммы визуализируют результат

макросы упрощают запуск сценария и оформление

На практике именно автоматизация превращает Excel из «табличного редактора» в инструмент регулярной отчётности: меньше ручной работы, меньше ошибок, быстрее обновление.