Магия Excel

Как я готовлю и провожу корпоративное обучение по Excel и Google Таблицам Решили с Лемуром немного поделиться внутренней кухней и рассказать, как мы готовим и проводим корпоративное обучение. Если вкратце, то Лемур готовит файлы с примерами, которые кажутся ему забавными, а потом сутками спит, ну а я доделываю все остальное. А подробнее — в статье. Обычно в "комплект" обучения входят не только собственно вебинары (для которых нужно подготовить и файлы с примерами, часто на основе рабочих файлов заказчика), но и слайды, ответы на вопросы, чат обучения, домашние работы с проверкой — так что одними уроками дело не ограничивается. Если захотите организовать обучение для своей команды — пишите на почту ([email protected]) или в телеграм @r_shagabutdinov, буду рад помочь! https://shagabutdinov.ru/sheets-excel-training/

Извлекаем из таблицы строки с самой большой и маленькой сделкой (наименьшим и наибольшим числом) С новыми функциями это получается просто: сначала сортируем таблицу по сделкам (по аналогии можно сортировать по датам, тогда вы сможете взять самую старую и новую строки) с помощью SORT / СОРТ: =СОРТ(таблица; номер столбца, по которому сортируем) Если нужно по убыванию, то задаем третий аргумент, равный -1 Ну а далее, не выводя на лист отсортированный результат, сразу отправляем его внутрь функции CHOOSEROWS / ВЫБОРСТРОК — и берем первую (1) и последнюю (-1) строки. =ВЫБОРСТРОК(СОРТ(таблица; номер столбца для сортировки);1;-1) В общем виде ВЫБОРСТРОК имеет такой синтаксис: =ВЫБОРСТРОК(диапазон / массив; номер строки, которую извлекаем ; [еще номер строки]; ...) То есть можем извлечь и одну строку, и несколько — перечисляем столько номеров, сколько нужно. А если нужны все нечетные строки, например, с 1 по 100, можно использовать функцию ПОСЛЕД / SEQUENCE, чтобы не вводить столько чисел вручную: =ВЫБОРСТРОК(диапазон; ПОСЛЕД(50;;1;2))

Интерфейс Excel: приемы и горячие клавиши для ускорения работы Настраиваем интерфейс Excel: — закрепляем и скрываем ленту инструментов — вызываем команды с помощью клавиш — "создаем" собственные сочетания клавиш для команд — добавляем на панель быстрого доступа любые инструменты и команды — даже те, которых нет на ленте. Лемур уверен: хоть что-то из этого видео вы раньше не знали :) Например, что можно настраивать панель быстрого доступа для отдельных файлов или добавлять туда любые команды, даже те, что с ленты инструментов не добавляются или добавляются только вместе со всей своей коллекцией. Пишите в комментариях, что оказалось наиболее полезным! Кстати, эти знания пригодятся вам и для настройки других приложений MS Office. https://www.youtube.com/watch?v=hRGpkN787Yw

Ищем данные в разных таблицах с помощью ВПР / VLOOKUP и ДВССЫЛ / INDIRECT Вот такая задача от подписчика: есть сотрудники разных специальностей (должностей), и в зависимости от отдела (или другого параметра) нам нужно искать их разряд в разных таблицах. У разных подразделений разная шкала оценки — например, где-то третий разряд присваивается с 60 лет, а где-то с 50. Как быть? Если бы задача была с одной таблицей, то все просто решается функцией ВПР / VLOOKUP: ищем возраст сотрудника в таблице, получаем разряд из второго столбца. Последний (четвертый аргумент) ВПР не трогаем, т.к. по умолчанию у этой функции интервальный просмотр, то есть поиск ближайшего наименьшего числа, а именно это нам и нужно в данном случае. =ВПР(возраст сотрудника; таблица с возрастами и разрядами; 2) Но у нас таблица не одна! Во втором аргументе ВПР могут быть разные таблицы, в зависимости от должности. Поступим так: — превратим таблицы для каждого отдела в "умные" таблицы (Форматировать как таблицу / Format as Table или Ctrl + T или Ctrl + L) — назовем каждую по имени отдела — теперь можно ссылаться на таблицы по имени. Нам надо получить название отдела по сотруднику (найти должность в списке "должность-отдел" и подтянуть отдел) — это и будет название нужной таблицы. Чтобы название таблицы из текста стало ссылкой, мы засунем всю конструкцию в ДВССЫЛ / INDIRECT — функцию, превращающую текст в ссылку. В общем виде будет так: =ВПР(возраст сотрудника; ДВССЫЛ(формула для определения названия нужной таблицы); 2) Разбор задачи — в видео, а в соседнем посте файл (книга Excel) с формулой. Эту идею можно использовать в любой подобной задаче, когда нужно искать значение в нескольких диапазонах, а не в одном.

Количество листов в строке состояния В этой самой строке вообще много полезного можно отобразить — кликните по ней правой кнопкой 🐭 и убедитесь. Например, если выбрать "Номер листа" (Sheet Number), то вы будете видеть общее количество листов и порядковый номер активного.

Парочка горячих клавиш для тех, кто работает с Power Query Многие из вас знают, что сочетание клавиш Alt — F11 открывает редактор Visual Basic (макросы). А есть еще одно, очень похожее — Alt — F12 — для открытия другого редактора, Power Query. Внутри самого Power Query можно менять масштаб с помощью сочетаний: Ctrl — Shift — + (плюс) Ctrl — Shift — - (минус) Это будет влиять на масштаб всего, кроме ленты инструментов.

Статья для тех, кто работает в Р7-Офис. Но и пользователям Excel будет полезно пробежаться и вспомнить про принципы работы формул массива :) В российском офисном пакете Р7-Офис в таблицах есть некоторые функции, появившиеся в Excel только в 2021 версии вместе с динамическими массивами. При этом формулы массива в Р7 работают как “старые” формулы массива (возможно, это когда-нибудь изменится). Так что новыми функциями пользоваться не так удобно, как в новом Excel, но зато они в принципе есть, в отличие от Excel 2019, допустим 🙂 В статье разбираем: — Как вводятся и работают старые и новые формулы массива в Excel — Какие новые функции появились в Excel 2021/365 благодаря динамическим массивам Excel — И разбираем, как работать с новыми функциями в Р7, где принципы работы формул массива старые, несмотря на наличие новых функций :) https://shagabutdinov.ru/r7array/

Если у вас есть формула, возвращающая динамический массив (то есть результат может быть разных размеров; допустим, функция FILTER / ФИЛЬТР будет возвращать разное количество строк в разное время, если в исходном диапазоне будут добавляться строки или меняться текущие), результат работы такой формулы можно отправить в Power Query. И в запрос будут попадать новые данные, которые вернет формула в будущем, то есть это не будет статичная таблица с результатом на момент загрузки данных в Power Query. Смотрим на коротком видео (без звука). Выделяем любую ячейку с результатом работы формулы: Данные — Из таблицы/диапазона Data — From Table/Range Или просто кликайте правой кнопкой на любую ячейку формулы и в контекстном меню выберите "Получить данные из таблицы/диапазона" (Get Data from Table/Range...) Про динамические массивы можно подробнее узнать в следующем видео: https://t.me/lemur_excel/95

Новая функция REGEXEXTRACT в Excel: извлекаем электронную почту, даты и другие фрагменты из текста Весной 2024 года в Excel были анонсированы функции для работы с регулярными выражениями. Ранее они были доступны в Google Spreadsheets. Разбираем несколько примеров. Извлекаем: - вес из длинного названия товара - все адреса электропочты из ячейки с текстом - даты в разных форматах - текст в скобках. https://www.youtube.com/watch?v=xylfF5lS3WY

В связи с недавним выходом новинки, посвященной языку M (он используется в Power Query в Excel и Power BI) время обновить обзор основных книг по каждой эксельной теме 😸 Итак, если вы хотите изучить конкретную тему в рамках Excel, вот по одной книге на каждую. Excel в целом Microsoft Excel Inside Out (Office 2021 and Microsoft 365) На русском: Excel 2019. Библия пользователя — Куслейка, Александер Макросы Microsoft Excel VBA and Macros — Bill Jelen На русском: Excel 2016. Профессиональное программирование на VBA — Александер, Куслейка (не пугайтесь версии 2016 — макросы не меняются десятилетиями) Сводные таблицы Сводные таблицы в Microsoft Excel 2021 и Microsoft 365 — Джелен Power Query Скульптор данных в Excel с Power Query — Николай Павлов или / и Приручи данные с помощью Power Query в Excel и Power Bi — Пульс, Эскобар Язык M The Definitive Guide to Power Query (M): Mastering complex data transformation with Power Query (книга для глубокого погружения именно в язык M, то есть ее лучше читать с опытом работы в интерфейсе Power Query и при желании решать там нестандартные задачи и писать код самостоятельно) Power Pivot и язык формул DAX (который используется и в Power BI / других решениях Microsoft) Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel — Руссо, Феррари Очень глубоко и основательно про DAX: Подробное руководство по DAX: бизнес-аналитика с Microsoft Power BI, SQL Server Analysis Services и Excel — Руссо, Феррари Для первого ознакомления с Power Pivot можно начать с глав в книге Джелена про сводные Формулы в целом С новыми формулами (LAMBDA, новые массивы), от начального до продвинутого уровня: главы про формулы в Microsoft Excel Inside Out. С новыми формулами посложнее: Advanced Excel Formulas: Unleashing Brilliance with Excel Formulas На русском с новыми формулами: главы про формулы у меня в "Магии таблиц" На русском до 2019 включительно от начального до продвинутого: главы про формулы в Excel 2019. Библия пользователя На русском до 2019 включительно посложнее: Мастер формул — Николай Павлов Старые формулы массива (до 2019 включительно) Ctrl+Shift+Enter Mastering Excel Array Formulas: Do the Impossible with Excel Formulas Thanks to Array Formula Magic — Girvin На русском: Мастер формул — Николай Павлов Новые формулы массива (динамические массивы) Up Up and Array!: Dynamic Array Formulas for Excel 365 and Beyond На русском: немного есть у меня в "Магии таблиц" Визуализация Визуализация данных при помощи дашбордов и отчетов в Excel — Куслейка

Закрепление верхней строки в Excel в один клик: добавляем команды на панель быстрого доступа Панель быстрого доступа (Quick Access Toolbar, QAT) — простейший инструмент для настройки интерфейса "под себя". Туда можно добавить любую команду — как с ленты, так и из списка вообще всех команд и инструментов Excel. Даже если какое-то действие нельзя добавить напрямую из ленты (потому что оно там находится в выпадающем списке, в коллекции — как, например, закрепление верхней строки находится в коллекции "Закрепление областей"; или потому что действия вообще нет на ленте) — его все равно можно добавить через параметры Excel, чтобы во всех книгах у вас всегда был доступ к нужной команде в один клик. В мини-статье разбираем, как это сделать — как раз на примере закрепления верхней строки.