В связи с недавним выходом новинки, посвященной языку 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 — Куслейка

Новая функция REGEXEXTRACT в Excel: извлекаем электронную почту, даты и другие фрагменты из текста

Весной 2024 года в Excel были анонсированы функции для работы с регулярными выражениями. Ранее они были доступны в Google Spreadsheets. Разбираем несколько примеров. Извлекаем:
- вес из длинного названия товара
- все адреса электропочты из ячейки с текстом
- даты в разных форматах
- текст в скобках.

https://www.youtube.com/watch?v=xylfF5lS3WY

Если у вас есть формула, возвращающая динамический массив (то есть результат может быть разных размеров; допустим, функция FILTER / ФИЛЬТР будет возвращать разное количество строк в разное время, если в исходном диапазоне будут добавляться строки или меняться текущие), результат работы такой формулы можно отправить в Power Query.

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

Смотрим на коротком видео (без звука).

Выделяем любую ячейку с результатом работы формулы:
Данные — Из таблицы/диапазона
Data — From Table/Range

Или просто кликайте правой кнопкой на любую ячейку формулы и в контекстном меню выберите "Получить данные из таблицы/диапазона" (Get Data from Table/Range...)

Про динамические массивы можно подробнее узнать в следующем видео:
https://www.tg-me.com/lemur_excel/95

Статья для тех, кто работает в Р7-Офис. Но и пользователям Excel будет полезно пробежаться и вспомнить про принципы работы формул массива :)

В российском офисном пакете Р7-Офис в таблицах есть некоторые функции, появившиеся в Excel только в 2021 версии вместе с динамическими массивами. При этом формулы массива в Р7 работают как “старые” формулы массива (возможно, это когда-нибудь изменится). Так что новыми функциями пользоваться не так удобно, как в новом Excel, но зато они в принципе есть, в отличие от Excel 2019, допустим 🙂

В статье разбираем:
— Как вводятся и работают старые и новые формулы массива в Excel
— Какие новые функции появились в Excel 2021/365 благодаря динамическим массивам Excel
— И разбираем, как работать с новыми функциями в Р7, где принципы работы формул массива старые, несмотря на наличие новых функций :)

https://shagabutdinov.ru/r7array/

Парочка горячих клавиш для тех, кто работает с Power Query

Многие из вас знают, что сочетание клавиш Alt — F11 открывает редактор Visual Basic (макросы).

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

Это будет влиять на масштаб всего, кроме ленты инструментов.

Ищем данные в разных таблицах с помощью ВПР / VLOOKUP и ДВССЫЛ / INDIRECT

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

У разных подразделений разная шкала оценки — например, где-то третий разряд присваивается с 60 лет, а где-то с 50.
Как быть?
Если бы задача была с одной таблицей, то все просто решается функцией ВПР / VLOOKUP: ищем возраст сотрудника в таблице, получаем разряд из второго столбца. Последний (четвертый аргумент) ВПР не трогаем, т.к. по умолчанию у этой функции интервальный просмотр, то есть поиск ближайшего наименьшего числа, а именно это нам и нужно в данном случае.

=ВПР(возраст сотрудника; таблица с возрастами и разрядами; 2)

Но у нас таблица не одна! Во втором аргументе ВПР могут быть разные таблицы, в зависимости от должности.
Поступим так:
— превратим таблицы для каждого отдела в "умные" таблицы (Форматировать как таблицу / Format as Table или Ctrl + T или Ctrl + L)
— назовем каждую по имени отдела
— теперь можно ссылаться на таблицы по имени. Нам надо получить название отдела по сотруднику (найти должность в списке "должность-отдел" и подтянуть отдел) — это и будет название нужной таблицы. Чтобы название таблицы из текста стало ссылкой, мы засунем всю конструкцию в ДВССЫЛ / INDIRECT — функцию, превращающую текст в ссылку.

В общем виде будет так:

=ВПР(возраст сотрудника; ДВССЫЛ(формула для определения названия нужной таблицы); 2)

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

Интерфейс Excel: приемы и горячие клавиши для ускорения работы

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

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

Кстати, эти знания пригодятся вам и для настройки других приложений MS Office.

https://www.youtube.com/watch?v=hRGpkN787Yw

Как я готовлю и провожу корпоративное обучение по Excel и Google Таблицам

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

А подробнее — в статье. Обычно в "комплект" обучения входят не только собственно вебинары (для которых нужно подготовить и файлы с примерами, часто на основе рабочих файлов заказчика), но и слайды, ответы на вопросы, чат обучения, домашние работы с проверкой — так что одними уроками дело не ограничивается.
Если захотите организовать обучение для своей команды — пишите на почту ([email protected]) или в телеграм @r_shagabutdinov, буду рад помочь!

https://shagabutdinov.ru/sheets-excel-training/

Флажки (checkboxes) есть и в Excel, и в Google Таблицах. Это переключатели, которые могут в быть в одном из двух положений — вкл/выкл и не зависят при этом друг от друга (в отличие от радиокнопок).

В Google Таблицах флажки в ячейках появились несколько лет назад, в Excel — в 2024 году только в Microsoft 365. Но в старых версиях флажки тоже были, только они не привязаны к ячейкам и поэтому не так удобны (каждый флажок вставляется отдельно, а сразу в целый диапазон ячеек добавить флажки нельзя).

Как их можно использовать?

— Чек-лист: поставили галочку, пункт списка закрасился/зачеркнулся.
— Столбец с неким условием: даем ли скидку, едет ли сотрудник на корпоратив и так далее. Затем этот столбец используем в расчетах (формулах). Или формируем выборку по этому столбцу: два отдельных списка (со скидкой и без, едут и не едут).
— Задаем параметры, используем флажки в интерактивных отчетах: сортируем ли данные, выводим ли их по месяцам или дням, какие столбцы забираем из исходника и так далее.
— Выбираем, какие ряды (регионы/филиалы/показатели и что угодно) отображать в диаграмме.
— И многое другое, на что хватит фантазии :)

Рассматриваем некоторые сценарии в статье:
https://shagabutdinov.ru/checkboxes-excel-sheets/

Или смотрим видео:
https://youtu.be/12k5sVL5iu4

План-факт через комбинированную диаграмму

Вот такая диаграмма для сравнения двух показателей (план и факт, производство и продажи, ...). Как ее построить?

— Тип диаграммы в целом - комбинированная, тип каждого ряда данных — гистограмма. Один из рядов данных — на вспомогательную ось, сама ось удалена (так как она не отличается по значениям от основной) — это все можно настроить, нажав "Изменить тип диаграммы" на ленте или в контекстном меню.

— Делаем разные значения бокового зазора у обеих гистограмм, чтобы столбики отличались по ширине. Заливку у обеих делаем прозрачной (в примере 40%). Это настраивается в панели "Формат", выделяем столбики и нажимаем Ctrl+1.

— Подписи забираем из ячеек в столбце D. Там формула (просто темп прироста, факт делим на план и вычитаем единицу) и пользовательский формат:

+0%* 🔥;-0%* 👎

(смайлики выберите по вкусу)

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

P.S. Файл с диаграммой прикреплен в отдельном сообщении выше — забирайте!

Выпадающие списки в Excel с автоматическим добавлением новых значений

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

Решения могут быть разные в зависимости от того, на разных ли листах данные.

Данные на том же листе — превращаем справочник в Таблицу и ссылаемся на него.
Данные на другом листе и у вас Excel 2010+ — просто ссылаемся на другой лист.
Данные на другом листе и будут пополняться новыми и/или у вас Excel 2007 — ссылаемся через функцию ДВССЫЛ / INDIRECT или через именованный диапазон.

Обсуждаем все нюансы в видео:
https://youtu.be/Mf9fMmWPUCw

Возрастно-половая пирамида в Excel через условное форматирование (гистограммы)

Алгоритм такой:
1 Создаем два отдельных правила
выделяем по очереди данные по женщинам и мужчинам и —> Главная — Условное форматирование — Гистограммы

Заходим в параметры каждого правила здесь:
Главная — Условное форматирование — Управление правилами — выделяем правило — Изменить правило

И:

2.1 У левого столбца (Женщины в примере) в параметрах меняем направление на "Справа налево"

2.2 У обоих задаем одинаковый максимум, чтобы масштаб тоже был одинаковый. С той же целью обязательно делаем ширины столбцов с гистограммами одинаковыми.

Еще один трюк с гистограммами: вставляем сверху текст

Как?

1 Сначала берем нужные данные. Они могут уже быть в ячейках, как у нас в столбце B, но если вы хотите оставить числа отдельно, то в другом столбце сошлитесь на данные:
=B2
(для первой строки)

2 Далее строим гистограмму. Условное форматирование — Гистограммы — выбираем цвет по вкусу.

3 Эта гистограмма будет отображаться вместе с данными (числами). Уберем числа и оставим только гистограмму:
Условное форматирование — Управление правилами — выбираем нашу гистограмму — Изменить правило — Показывать только столбец

4 Осталось скопировать нужный текст ( у нас это A2:A12), вставить в диапазон с гистограммами (можно в первую ячейку) и далее нажать на смарт-тэг "Параметры вставки" справа внизу (см скриншот) и выбрать "Связанный рисунок".

Астрологи объявили неделю гистограмм!

Итак, на этот раз мы будем применять их не ко всем значениям, а к верхним N.
Например, к топ-5 регионов.

Для этого сначала вставим гистограммы (Главная — Условное форматирование — Гистограммы) и далее создадим правило условного форматирования, которое будет применять никакое форматирование к последним значениям (в нашем случае последним 6, потому что всего регионов 11, а мы хотим оставить топ-5)

Главная — Условное форматирование — Правила отбора первых и последних значений — Последние 10 элементов - меняем 10 на 6 или другое нужное число — выбираем "пользовательский формат" и в нем не меняем ничего, то есть оставляем форматирование пустым.

Идем в Управление правилами условного форматирования и там:
1 Убеждаемся, что у правила с пустым форматированием верхний приоритет (оно идет первым в списке — если это не так, поменяйте порядок с помощью стрелок справа)
2 Включаем флажок "Остановить, если истина" у этого правила.

Так-так-так, смотрите, какие функции подвезли в Excel!

То, что было доступно в Google Таблицах около 10 000 лет, появилось и в Excel — пока только у инсайдеров, но в любом случае рано или поздно функции доедут до всех подписчиков 365.

Это TRANSLATE и DETECTLANGUAGE. На русском — ПЕРЕВОД и ОПРЕДЕЛИТЬЯЗЫК.

В Google Таблицах эти функции называются GOOGLETRANSLATE и DETECTLANGUAGE.
Вот пример применения функций в Гугл — определяем язык и в зависимости от этого формулой выдаем ссылку на российский или американский книжный магазин.

Объединение ячеек: почему это не очень хорошо и чем заменить с тем же визуальным эффектом

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

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