Бесплатный мини-курс по новым функциям Excel на Stepik

Друзья, записал крошечный курс (5 тем — небольшое текстовое описание функций + видео-уроки) по новым функциям Excel.

— Функция ПРОСМОТРX / XLOOKUP — замена легендарной ВПР / VLOOKUP
— Динамические массивы Excel — новые правила работы с массивами в Excel и появившиеся благодаря ним функции
— Новые функции для работы с массивами — как ВСТОЛБИК / VSTACK, позволяющая объединять массивы в один или ВЫБОРСТРОК / CHOOSEROWS, с которой можно извлечь отдельные строки из массива
— Функция LAMBDA — с ней можно создавать собственные функции или обрабатывать циклично каждую строку или столбец массива (и многое другое).

Можно посмотреть уроки абсолютно бесплатно на Stepik — по этой ссылке:
https://stepik.org/course/182713

Буду признателен, если поделитесь ссылкой с коллегами, а после прослушивания напишете в комментариях на платформе или здесь, как вам уроки!

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

Функция ЯЧЕЙКА / CELL может выдавать разную информацию: например, полное имя файла (книги) вместе с листом. Для этого ее первый и единственный обязательный аргумент должен быть равен "имяфайла" ("filename").

А дальше — дело техники — вытаскиваем только имя листа текстовыми функциями.
В новом Excel совсем удобно: ТЕКСТПОСЛЕ / TEXTAFTER вытащит все, что после квадратной скобки.

=ТЕКСТПОСЛЕ(ЯЧЕЙКА("имяфайла");"]")

В старых версиях Excel воспользуемся комбинацией функций:
НАЙТИ / FIND подскажет, на какой позиции находится скобка, ДЛСТР / LEN — сколько в имени вообще символов — исходя из этого поймем, какая длина названия листа — в нашем случае 5 символов — именно столько извлечем с конца текстовой строки с помощью функции ПРАВСИМВ / RIGHT.

=ПРАВСИМВ(ЯЧЕЙКА("имяфайла");ДЛСТР(...)-НАЙТИ("]";...))

Изменяем стандартную диаграмму

Построить диаграмму в Excel можно очень быстро — практически одной лапой, а точнее, сочетанием клавиш Alt + F1.
Эта комбинация вызывает вставку стандартной гистограммы (столбиков).

Что если вы хотите строить диаграмму другого вида? Например, не обычную гистограмму, а с накоплением (когда отдельные составляющие выстраиваются в один общий столбец), с таблицей данных (значения внизу диаграммы в таблице), с осью в тысячах или еще с чем-то?

Настройте диаграмму как вам хочется. После этого:
1 Щелкаем правой кнопкой и нажимаем "Сохранить как шаблон..." (Save as Template...)

2 В появившемся окне придумываем название, под которым шаблон диаграммы будет сохранен в файловой системе

3 Нажимаем на ленте инструментов во вкладке "Конструктор диаграмм" (Design) кнопку "Изменить диаграмму" (Change Chart Type)

4 Заходим в папку "Шаблоны" (Templates). Во-первых, мы уже можем пользоваться этим шаблоном отсюда и при вставке новых диаграмм! Но нам остается финальный штрих, чтобы именно эта диаграмма строилась по сочетанию клавиш Alt + F1.

5 Щелкаем по шаблону правой кнопкой мыши и нажимаем "Сделать стандартной" (Set as Default Chart).

P.S. Если хотите, чтобы стандартной были вообще не столбики, а другой тип — допустим, круговая (пирог, pie chart) — щелкните на этот тип в окне "Изменение типа диаграммы" и нажмите туда же — "Сделать стандартной".

Хочу изучить конкретную тему в рамках 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 — Пульс, Эскобар

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 — Куслейка

Подробный обзор, где больше книг — по постоянному адресу:
https://teletype.in/@renat_shagabutdinov/excellent_books

Вычисляем период в днях/месяцах/годах: функция РАЗНДАТ / DATEDIF

Если вам нужно вычислить разницу между двумя датами не в днях (для чего достаточно вычесть из одной даты другую или воспользоваться функцией ДНИ / DAYS), а в месяцах или годах (например, возраст) — пользуйтесь функцией РАЗНДАТ / DATEDIF. В Excel при ее вводе не будут отображаться всплывающая подсказка с аргументами, Excel не предложит ее дописать, но не обращайте на это внимания — она работает во всех версиях. И в Google Таблицах тоже!

 =РАЗНДАТ(дата_начала; дата_окончания; единица измерения)

Первые два аргумента — даты начала и окончания периода. Они могут быть указаны прямо в формуле в кавычках либо в виде ссылок на ячейки с датами, а также быть заданными функцией СЕГОДНЯ / TODAY.

Единица измерения задается в кавычках. Есть следующие возможные варианты:

"d" — число дней (такой параметр не имеет особого смысла, так как для этой задачи подойдет и функция ДНИ / DAYS, и просто вычитание);
"m" — число полных месяцев в периоде;
"y" — число полных лет в периоде;
"md" — разница в днях без учета месяца и года (например, между 01.01.2021 и 15.06.2022 — 14 дней);
"ym" — разница в месяцах без учета дня и года (например, между 01.01.2021 и 15.06.2022 — 5 месяцев);
"yd" — разница в днях без учета года (например, между 01.01.2021 и 15.06.2022 —165 дней).

Видео про функцию ПРОСМОТРX / XLOOKUP

Это чудо для поиска (объединения таблиц) появилось в Excel 2021 и в Google Таблицах. И лишено некоторых минусов функции ВПР — легендарной функции, чего уж там!

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

Но зато ВПР умеет работать с символами подстановки (* и ?) по умолчанию, а ПРОСМОТРX — нет, нужно задавать специальный аргумент для этого.

Вот видео про эту функцию:
https://youtu.be/4wigZhde7jY

Это первое видео бесплатного открытого мини-курса на Stepik про новые функции Excel, заглядывайте на огонек:
https://stepik.org/course/182713/

Вот такая табличка: сравнение старого и нового Excel и Google Таблиц по доступности новых функций.

Это один из многих слайдов практикума "Новые функции", который пройдет 7, 14 и 20 ноября.
На самих занятиях слайды смотреть не будем — это дополнительный материал, а во время вебинаров будет много практики и ответы на вопросы.

Практиковаться можно будет и в Google Таблицах (если нет подписки 365), и в Excel!

Присоединяйтесь!
https://www.mann-ivanov-ferber.ru/courses/practicum-excel/

Лемур принес вам скидочку 35% — по промокоду LEMUREC, действующему до 7 ноября.

А тут, напоминаем, подробное сравнение Excel и Google Таблиц по всем нюансам.

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

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

2 Двойной щелчок по заголовку поля ("Сумма по полю...")

3 Щелчок по полю в редакторе сводной таблицы — "Параметры полей значений"

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

Удаляем пустые строки

Выделяем диапазон, в котором нужно удалить пустые ячейки (в видео - все данные на листе с помощью Ctrl+Shift+End).

Для этого нужен инструмент "Найти и выделить" (на ленте на вкладке "Главная", Home — Go To) — выбираем там "Выделить группу ячеек" и в появившемся диалоговом окне — "Пустые ячейки" (Blanks).
Еще можно нажать F5 или Ctrl + G и в появившемся окне нажать "Выделить".

После этого остается нажать Ctrl + - (Ctrl и минус) — это удаление ячеек/строк/столбцов. И выбрать "строку".

P.S. Если у вас пустые ячейки только в одном столбце, и нужно удалить строки с такими ячейками — выделите один столбец, а не всю таблицу, а далее алгоритм такой же.

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

Ctrl + Backspace - очень удобное сочетание клавиш для возвращения к активной ячейке

Макрос для сравнения двух файлов (книг Excel)

Удаляем строки с пустыми ячейками в одном из столбцов

Макрос: создаем оглавление в книге

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

Поиск по двум критериям

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

Видеоурок: "старые" и новые формулы массивов

Функция СУММЕСЛИМН / SUMIFS: сумма по условиям

Собираем данные с разных листов в Excel и Google Таблицах (список листов - динамический)

"Протягивание формул" (двойной щелчок и сочетания клавиш)

Объединяем умные таблицы в одну: формулы и Power Query

Сравнение списков (Видео)

Нарастающий итог: закрепляем только первую ячейку в диапазоне

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

1. Ссылаемся на первую ячейку, эфчетырим ее (то есть нажимаем F4, чтобы сделать ссылку абсолютной, "закрепить")

2. Вводим двоеточие и ту же самую ячейку, но уже оставляем относительной. Получается диапазон с началом и концов в одной ячейке, но конец не закреплен - так что при протягивании/копировании формулы будет меняться.
=СУММ($B$2:B2)

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

Хотите видеть какую-то книгу Excel всегда наверху в списке последних файлов?

На стартовом экране (Backstage) наводите курсор на нужный файл — справа появится кнопка (которая выглядит как... кнопка) "Закрепить" (Pin). Нажимайте и книга будет всегда наверху.
Точно так же можно открепить обратно.

Хотите, чтобы стартовый экран не показывался при включении Excel (или другого приложения Office)?
Заходите в Параметры — Общие — отключайте "Показывать начальный экран при запуске этого приложения"

Функция ТЕКСТ / TEXT: превращаем число в текстовое значение в заданном числовом формате

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

Для чего нужна?
Допустим, вы хотите "склеить" в одну текстовую строку текст и число.
Чтобы получить в таблице надпись вида "По состоянию на: 30.06.23" или "Сумма продаж: 20 500". То есть текст из фиксированной части и какого-то вычисления/функции, как-то суммы чисел или текущей даты.

Проблема в том, что если сделать это "в лоб" без функции ТЕКСТ / TEXT, форматирование потеряется. Число будет без разделителей разрядов, со всеми знаками после запятой; дата будет в виде числа ("По состоянию на: 44742") — потому что вот так даты хранятся в Excel и Таблицах.

И функция ТЕКСТ позволяет это исправить — укажите нужный формат во втором аргументе, как если бы вводили его в окне "Формат ячеек" (Ctrl + 1).

Итак, для даты в нашем примере нужна будет такая формула:

="По состоянию на: " & ТЕКСТ (дата; "ДД.ММ.ГГ")

Подробнее про пользовательские числовые форматы можно посмотреть в видео — оно на основе Google Таблиц, но все работает практически идентично.

Как разделить текст по нескольким разделителям?

Например, по косой черте и дефису, как в примере.

В новой версии Excel можно воспользоваться функцией ТЕКСТРАЗД / TEXTSPLIT.
А чтобы она работала с несколькими разделителями, отправим их в массив:

{"первый разделитель"; "второй"; ... }

Если бы мы сделали так (не массив, а одна текстовая строка) — то оба символа считались бы одним разделителем:

"/-"

А в Google Таблицах есть функция SPLIT, работающая похожим образом. Но там массив указывать не надо. Задайте третий аргумент как ноль, если хотите, чтобы все символы считались одним разделителем, и единицей, если каждый должен считаться отдельным (это вариант по умолчанию, так что можно просто ограничиться двумя аргументами). Для нашей задачи:

=SPLIT(A2; "/-")

или
=SPLIT(A2; "/-"; 1)

Выводим формулой список всех рабочих дней — от заданной до сегодняшней (так в примере; но можно и наоборот — как вам нужно)

Для этого:
1 вычислим число рабочих дней в периоде (функция ЧИСТРАБДНИ / NETWORKDAYS)

2 Засунем это число в функцию ПОСЛЕД / SEQUENCE и получим последовательность чисел от 1 до числа рабочих дней в периоде

3 отправим эту последовательность в функцию РАБДЕНЬ / WORKDAY — она возвращает дату, которая наступит по прошествии N рабочих дней от заданной. В нашем случае она выдаст много дат, по одной для каждого числа полученной на прошлом шаге последовательности.

Формула такая:

=РАБДЕНЬ(начальная дата-1;ПОСЛЕД(ЧИСТРАБДНИ(начальная дата ;конечная дата)))

На скриншоте конечная дата задается функцией СЕГОДНЯ / TODAY — так что список будет обновляться каждый день (кроме выходных 😉)

P.S. G.S. В Google Sheets тоже будет работать, только не забудьте нажать Ctrl+Shift+Enter, чтобы добавилась функция ArrayFormula.

А как быть в старых версиях Excel (до 2019 включительно), где функции ПОСЛЕД / SEQUENCE нет?

Там можно воспользоваться прогрессией (Series). Ищите инструмент на вкладке "Главная" в коллекции команд "Заполнить" (стрелка вниз).
Увы, это будет статичная история, а не динамическая, как в случае с формулами... но тоже неплохо!

Тем временем большая часть тиража книги "Магия таблиц" уже распродана за 4 месяца — более 2100 экземпляров из 2500, в издательстве осталось совсем чуть-чуть, и в магазинах тоже — в Лабиринте книга и вовсе кончилась, например.

Так что если планируете сделать полезные и табличные подарки коллегам/друзьям на Новый год или по другим поводам, поторопитесь!
Книгу можно заказать тут:
На сайте издательства (там же электрическая книга)
Book24
Лабиринт (потрачено)
Озон
Wildberries
Читай-город
Буквоед

За это время прибавилось отзывов! На Озоне 50 оценок с рейтингом 4.92, на WIldberries 22 оценки — 4.8.

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

Ну и несколько других (просто копируем из магазинов как есть):
— Настоящий справочник, очень пригодился в работе
— Отличная книга!
— Отличная полезная книга
— Книга бесценна по своему содержанию.
— уровень знания excel неплохой, знания математики забыты. люблю, когда все объясняют. для меня книга очень полезная. автору, продавцу и озону спасибо ❤️
— содержание: описан полезный функционал
— Купил книгу так как до этого проходил два курса от автора. Очень доволен, пользуюсь в том числе как справочником-шпаргалкой, если что-то подзабылось. Настоятельно рекомендую.
— Книга-мечта! Отлично дополняет курс "Магия Excel"! Полностью книгу не читала, но обращаюсь, когда нужно быстро найти функцию или освежить в памяти комбинацию клавиш для определенных действий!

Новинка: флажки в Excel

Лучше поздно, чем никогда 😺 В Excel — пока только ранним пташкам, получающим обновления первыми — доступны флажки в ячейках. Как в Google Таблицах, где они появились уже давно.

Флажки — переключатели, меняющие значения с ИСТИНА / TRUE на ЛОЖЬ / FALSE и наоборот. Их можно использовать для чек-листов, списков, ссылаться на них в формулах (включили флажок — начисляем скидку в этой строке с помощью функции IF / ЕСЛИ, например) и в условном форматировании (поставили флажок — зачеркнули или покрасили цветом строку)

В коротком видео смотрим:
— на новые флажки и как их использовать в формулах и условном форматировании
— на старые флажки Excel — элементы управления формы (они размещаются не в ячейках, а на отдельном слое, и каждый нужно вручную связывать с ячейкой)
— на флажки в Google Таблицах

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

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

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

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

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

+0%* 🔥;-0%* 👎

(смайлики выберите по вкусу; чтобы зайти в окно настройки формата, нажмите Ctrl+1)

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

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