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

В видео рассмотрен пример обработки "грязных" данных с помощью инструментов редактора Power Query и формул языка М.

Множество других приемов разбираем в курсе "Power Query: с Нуля до Pro". Присоединяйтесь...

👉 ЗАПИСАТЬСЯ НА КУРС

Маленький секрет клавиши Ctrl при вводе формул в Excel.

👉 Пошаговые видеокурсы по разным аспектам работы в Microsoft Excel и Word

☕️ Угостить автора чашечкой кофе :)

Быстро найти последнее значение в столбце поможет простая формула с функциями ИНДЕКС и СЧЕТЗ.

👉 Пошаговые видеокурсы по разным аспектам работы в Microsoft Excel и Word

☕️ Угостить автора чашечкой кофе :)

Если с помощью формулы нужно подтянуть последнее значение в столбце, в котором также могут быть и пустые ячейки, то можно задействовать функцию ПРОСТМОТР и простой трюк.

👉 Пошаговые видеокурсы по разным аспектам работы в Microsoft Excel и Word

☕️ Угостить автора чашечкой кофе :)

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

Вполне рабочий прием, но довольно трудоемкий. В новых версиях Excel есть функция ОБЪЕДИНИТЬ (TEXTJOIN). Она делает то же самое, но гораздо проще.

👉 Пошаговые видеокурсы по разным аспектам работы в Microsoft Excel и Word

☕️ Угостить автора чашечкой кофе :)

⚙️ Power Query — автоматизация рутины в Excel без формул и макросов

Если вы часто объединяете таблицы, чистите данные вручную или каждый месяц заново собираете отчёты, то приглашаю вас на курс, который поможет автоматизировать все эти задачи - БЕЗ формул, БЕЗ макросов и БЕЗ ошибок.

📘 Курс состоит из четырёх модулей:

1. Основы Power Query
Научитесь работать с редактором запросов и подготавливать данные без рутины.

2. Преобразование данных
Сможете очищать и структурировать любые данные в несколько кликов.

3. Расширенные возможности
Научитесь строить динамичные отчёты и автоматизировать повторяющиеся задачи.

4. Основы языка M
Сможете «заглянуть под капот» Power Query и адаптировать его под свои задачи.

🧩 Форматы участия:

🎓 ПОГРУЖЕНИЕ
Основы Power Query в семи уроках
Стоимость : БЕСПЛАТНО

🚀 СТАРТ
Самостоятельное прохождение
Стоимость : 4 990 ₽

🎯 ПРАКТИКА
С проверкой и поддержкой
Стоимость : 9 990 ₽

На тарифе ПРАКТИКА после успешной сдачи финального экзамена вы получите сертификат о прохождении курса.

💡Главная задача курса — освободить вас от рутины и сделать так, чтобы Power Query стал вашим основным инструментом для работы с данными.

Оцените, нужен ли вам Power Query, пройдя бесплатное ПОГРУЖЕНИЕ.

👉 ЗАПИСАТЬСЯ НА КУРС

Power Query — инструмент, который полностью меняет подход к работе в Excel. Он позволяет забыть о сложных формулах, ручном копировании и бесконечных исправлениях.

Основные преимущества Power Query и наглядный пример его использования представлены в видео.

Архив с файлами из видео

Записаться на курс, можно нажав на эту ссылку.

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

Архив с файлами из видео

Записаться на курс, можно нажав на эту ссылку.

Канал в MAX

Благодарю всех, принявших участие в опросе.

Вижу, что есть потребность в дублировании контента, поэтому завел канал в Макс. Неожиданно бурная реакция на вполне безобидный опрос заставляет меня повторить:

Никого НЕ ПРИНУЖДАЮ туда переходить! Основной площадкой по-прежнему остается Телеграм!!

Но если вам удобнее использовать Max, то вот ссыкла на канал-дублер - https://max.ru/andreysukhov

Также возобновил репост видео на Рутуб

В Excel 365 есть функция УРЕЗДИАПАЗОН (TRIMRANGE) и ее "облегченный" аналог - оператор точка.

Если используете динамические диапазоны, то стоит знать об их существовании.

Файл-пример

YouTube | Рутуб | MAX

👉 Пошаговые видеокурсы по разным аспектам работы в Microsoft Excel и Word

В видео рассмотрим, как создать свою пользовательскую функцию в Excel без VBA с помощью функции LAMBDA.

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

Файл-пример

YouTube | Рутуб | MAX

👉 Пошаговые видеокурсы по разным аспектам работы в Microsoft Excel и Word

В этом видео разберем функцию LET, которая появилась в Excel 2021.

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

Файл-пример

YouTube | Рутуб | MAX

👉 Пошаговые видеокурсы по разным аспектам работы в Microsoft Excel и Word

📁 Форматы файлов Excel: что использовать и чем они отличаются

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

Итак, самые важные форматы, о которых стоит знать:

.xlsx — стандартная книга Excel
• Самый распространённый формат.
• Безопасный, не содержит макросов.
• Оптимален в 90% случаев.
Для обмена данными чаще всего используется именно этот формат.

.xls — старый формат (до Excel 2007)
Лимиты: 65 536 строк, 256 столбцов.
Не поддерживает современные функции и большинство инструментов Excel.
Сейчас используется только для совместимости со старым ПО.

.xlsm — книга Excel с макросами
Такой же .xlsx, но с поддержкой VBA, форм управления, пользовательских функций и т. д.
Если в книге есть макросы, то следует использовать именно этот формат. При сохранении в .xlsx макросы будут удалены.

.xlsb — бинарная книга Excel
Продвинутый формат для тяжёлых файлов.
Открывается быстрее, весит меньше, не ломается при больших массивах данных.
Рекомендуется для:
• больших таблиц (200k+ строк),
• Power Query,
• Power Pivot,
• моделей и отчётов,
• файлов 20–200 МБ.
Макросы поддерживает.

.xltx и .xltm — шаблоны Excel
.xltx — шаблон без макросов
.xltm — шаблон с макросами
Используются, если нужно один раз настроить книгу и создавать на её основе новые (чистые) копии.
Отлично подходит для отчётов, ежемесячных шаблонов, коммерческих предложений.

.xlam и .xla — надстройки Excel
Формат для подключаемых модулей и пользовательских функций. Используется при создании надстроек.

.csv, .txt — текстовые форматы
Хранят только данные, без формул и форматирования.
Используются для экспорта и обмена между системами.
Оптимально для загрузки/выгрузки в 1С, ERP, CRM и другие системы.

👉 Пошаговые видеокурсы по разным аспектам работы в Microsoft Excel и Word

☕️ Угостить автора чашечкой кофе 🙂

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

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

Разберем их на примере Excel 365.

YouTube | Рутуб | MAX

👉 Пошаговые видеокурсы по разным аспектам работы в Microsoft Excel и Word

🚫 Почему не стоит объединять ячейки в Excel

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

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

❌ Сортировка и фильтры перестают работать

При объединении «ломается» структура листа и в результате пропадает возможность пользоваться сортировкой и фильтрацией в диапазоне, включающем в себя объединенную ячейку.

❌ Нельзя использовать объединенные ячейки в «умных» таблицах

Умные таблицы - это идеальная структура для Excel и именно поэтому в ней невозможно использовать объединение ячеек.

❌ Формулы и расчеты

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

❌ Макросы на VBA

Макросы, в которых не вложен алгоритм обработки объединенных ячеек, могут работать некорректно.

❌ Проблемы в Power Query

Power Query также чувствителен к объединению и его поведение в этом случае сложно предугадать.

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

☕️ Угостить автора чашечкой кофе

Если нужно получить список всех файлов находящихся в какой-то папке на диске компьютера, то проще всего это можно сделать с помощью Power Query.

YouTube | Рутуб | MAX

👉 Пошаговые видеокурсы по разным аспектам работы в Microsoft Excel и Word

💡 Незаменимая клавиша F2

Думаю, каждый сталкивался с такой ситуацией.

В диалоговом окне нужно отредактировать ссылку на диапазон. Ставим курсор в поле и нажимаем стрелку влево — но вместо текстового курсора начинает двигаться табличный курсор по листу (1, см. рис).

Решение очень простое — клавиша F2.

Она переключает поле между двумя режимами:

- режим выбора диапазона — Excel выделяет ячейки;

- режим редактирования текста — стрелки двигают текстовый курсор внутри поля.

Текущий режим отображается слева в строке состояния (2, см. рис).

Работает во всех диалоговых окнах Excel, где есть поле со ссылкой на диапазон (Создать имя, Проверка данных, Условное форматирование и т.д.)

☕️ Угостить автора чашечкой кофе :)

Расчет возраста по дате рождения - довольно частая задача, решаемая с помощью Эксель.

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

👉 Курс Power Query

YouTube | Рутуб | MAX

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

Решить задачу можно разными способами.

Рассмотрим три:

1. Универсальная формула
2. Динамические массивы
3. Power Query

📊 Файл с примером

Подробно инструменты Power Query разбираем на курсе. Присоединяйтесь: 🚀 Power Query с Нуля

YouTube | Рутуб | MAX

📌 Почему ЕСЛИОШИБКА — плохая практика в расчетах

Функция ЕСЛИОШИБКА выглядит как универсальное решение: есть ошибка — подставили 0 — получили «красивый» отчет.
Но подвох в том, что она скрывает причину проблемы, а не решает ее.

Простейший пример: =ЕСЛИОШИБКА(A1/B1;0)

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

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

Когда ЕСЛИОШИБКА все же уместна:
• в финальном отчете или дашборде;
• когда ошибка осознанно допустима.

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

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

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

Рассмотрим 5 ПРИЕМОВ, которые реально ускоряют работу и помогают избежать глупых ошибок.

📊 Файл-пример

🚀 Пошаговые видеокурсы по Microsoft Excel

YouTube | Рутуб | MAX