В Эксель почти всегда есть несколько решений одной и той же задачи.

Так зависимый выпадающий список можно создать с помощью функции ПРОСМОТРХ.

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

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

Самый простой способ объединить несколько файлов буквально за пару минут.

Если есть желание освоить множество аналогичных приёмов по преобразованию данных, приходите на курс по Power Query.

👉 Power Query: с Нуля до Pro

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

Более подробно с нечетким соответствием при объединении данных в Power Query мы разберемся в курсе Power Query: с Нуля до Pro.

👉 Присоединяйтесь

Если вам приходилось иметь дело с грязными данными, то, безусловно, знаете, как сложно все это привести к нормальному виду, для того чтобы можно было задействовать другие инструменты 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, где есть поле со ссылкой на диапазон (Создать имя, Проверка данных, Условное форматирование и т.д.)

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