Новое видео: Урок 18. Функция LET: упрощаем и ускоряем вычисления

Покупали когда-либо курс "Магия новых функций Excel"?
Тогда скорее в личный кабинет — там вас ждет новый, уже восемнадцатый, урок. Про функцию LET.

Совсем скоро — и другие уроки! Про все нюансы функции ПРОСМОТРX / XLOOKUP и ФИЛЬТРацию с регулярными выражениями заодно.
https://shagabutdinov.ru/magic-excel

P.S. А по этой ссылке найдете ряд бесплатных видеоуроков по Excel и Google Таблицам. И этот список тоже скоро пополнится.

Одной формулой собираем ТОП-N сделок из всех умных таблиц в списке и добавляем название таблицы к каждой строке

Ух! Вот что могут (и очень, очень многое) новые формулы.

Что тут вообще происходит?

С помощью LET создаем функцию f от трех аргументов: имя таблицы x, сколько строк берем n, по какому столбцу сортируем cl. Имена переменных роли не играют - можете придумывать любые.

Сама функция f:
1 Сортирует таблицу (так как мы получаем из ячейки имя таблицы, это текст, его нужно сделать активной ссылкой через ДВССЫЛ / INDIRECT)
2 Ищет позицию нужного заголовка через ПОИСКПОЗX / XMATCH. Ссылку на массив заголовков получаем тоже через ДВССЫЛ, добавляя к названию таблицы "[#Заголовки]"
3 СОРТирует по нему (SORT)
4 Берет первые n строк (функция ВЗЯТЬ / TAKE)
5 Добавляет справа (горизонтально, функция ГСТОЛБИК / HSTACK) расклонированное n раз название таблицы (функция MAKEARRAY) — массив из n строк, 1 столбца, внутри функция, которая ничего не делает с этими номерами, а просто возвращает имя таблицы x

Ну а потом мы отправляем в REDUCE список таблиц. И собираем последовательно массив: начальным значением будут заголовки из первой таблицы. Далее с помощью ВСТОЛБИК / VSTACK объединяем таблицы (топ-N строк), возвращаемые нашей функцией f, одну под другой.

Зачем функция ЕСНД / IFNA? У нас нет заголовка для названий таблиц, массив заголовков в самих таблицах на одно значение меньше. И там будет ошибка #Н/Д. Ее и нужно заменить на желаемое название.

Нюанс с датами: формула будет возвращать их без форматирования. Так, как их видит Excel — просто в виде целых чисел. Можно форматировать сами ячейки с запасом, можно делать это через ВЫБОРСТОЛБЦ / CHOOSECOLS и функцию ТЕКСТ / TEXT, можно через условное форматирование, как на скриншоте и в примере — искать заголовки со словом "Дата" и применять к ним формат даты.

Файл с примером по ссылке

---
Хотите писать такие же формулы? Добро пожаловать:
💫Магия новых функций Excel. Революция в табличных формулах: от SORT и FILTER до GROUPBY и LAMBDA

Видео: объединение таблиц по 2 условиям

8 минут со звуком

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

В деле функции ВПР / VLOOKUP, ПРОСМОТРX / XLOOKUP и СУММЕСЛИМН / SUMIFS.

Файл с примером по ссылке.

А другие бесплатные видеоуроки (и этот тоже) можно найти по ссылке:
https://shagabutdinov.ru/video

Функция РАЗВЕРНУТЬ / EXPAND

Что она делает? Увеличивает размеры массива. Все "дополнительные" значения (то есть дополнительные строки и/или столбцы, то, чего нет в исходном массиве, который задается в первом аргументе функции) будут ошибками #Н/Д (#N/A).

Но их можно заменить на какое-то значение — указав его в четвертом аргументе.

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

В предыдущем варианте — по ссылке — мы использовали для этого другую функцию MAKEARRAY.

В функции LET можно задавать свои функции! Это, конечно, экзотика (потому что, как правило, если нам нужно применять одно и то же вычисление много раз, мы используем LAMBDA + MAP или другую вспомогательную функцию), но тем не менее.

Если после имени переменной последует не константа / выражение, а функция LAMBDA, то это будет имя функции, которую потом можно в LET вызывать. В следующем примере у нас простая функция, умножающая число, данное ей на входе, на 2:

=LET(f; LAMBDA (a;a*2); f(10) + f(5) )

Здесь f – название функции, а – название переменной, аргумента этой функции. Затем в последнем аргументе LET мы уже ее вызываем с конкретными значениями 10 и 5.

Про базовые сценарии применения LET читайте в статье:
https://shagabutdinov.ru/tpost/47brblc2e1-novaya-funktsiya-excel-i-google-tablits

VisiCalc и первые табличные функции

Сегодня немного истории, друзья.

Excel — это не первые электронные таблицы, да-да! Первым было приложение VisiCalc, созданная в 1979 году Дэном Бриклином и Бобом Фрэнкстоном.

"Визуальный калькулятор" — идея была в том, чтобы не менять все вводные для расчетов, как это приходится делать на обычном калькуляторе. И это свойство электронных таблиц — автоматическое обновление формул и сами формулы — до сих пор ключевое.

А что насчет формул? Сейчас в Excel более 500 функций, постоянно добавляются новые.
В VisiCalc в 1979 году их было чуть меньше — 21.
Многие вы узнаете:
@SUM, @NA, @ ERROR, @MAX, @MIN, @ AVERAGE, @ COUNT, @NPV, @ LOOKUP, @ABS, @INT, @EXP, @ LOG10, @LN, @PI, @SIN, @COS, @TAN, @ ASIN, @ ACOS, @ ATAN.

И никаких тебе LAMBDA и даже СУММЕСЛИМН 🤠

Если захотите секунд на 5 заглянуть в прошлое и найти связь Excel с VisiCalc — она есть не только в названиях функций.
Введите @ (а именно так начинались формулы в VisiCalc) и вводите название функции (например, СУММ) — и увидите, что собачка работает как знак "равно", вы вводите формулу.

Готовлю очередной вебинар для лектория крупной федеральной компании и тут в личные сообщения приходит такой отзыв — приятно! ❤️

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

В моем случае — и с возможностью приходить со своими рабочими задачами и вопросами между встреч.

Хотите организовать подобное у себя в компании? Добро пожаловать в личные сообщения (или оставьте заявку на сайте)

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

Если в рамках текущей сессии (после открытия Excel) вы использовали "Текст по столбцам" (Text to Columns) с разделителем, данные будут автоматически разделяться по этому символу при вставке (в примере это запятая, с тем же успехом мог бы быть пробел или другой символ).

Если вас такое поведение не устраивает, придется либо закрыть Excel после использования "Текста по столбцам", либо открыть этот инструмент еще раз, чтобы убрать в нем разделители на втором шаге.

На днях компания Microsoft анонсировала, что в следующих версиях Excel появится возможность (наконец-то!) автоматического обновления сводных таблиц. За это будет отвечать кнопка Автообновление (Auto Refresh) на вкладке Анализ сводной таблицы (PivotTable Analyze).

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

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

Суть способа в создании специального макроса, который будет:
1. Перехватывать событие изменения листа с исходными данными
2. Определять, находится ли изменённая ячейка в таблице, по которой построена сводная (ведь менять могут и произвольные ячейки за её пределами).
3. Обновлять сводную

В новом видео подробно разбираемся, как это сделать:
Читать статью и смотреть видеоурок https://www.planetaexcel.ru/techniques/8/58518/
Смотреть видео на YouTube https://youtu.be/Ytz-UgTbBFk

Формула не вычисляется, а отображается как текст! Почему?

1 вариант. В ячейке стоит апостроф `. Он делает значение текстовым. Удалите его. Ну а если, наоборот, надо временно отключить формулу, используйте апостроф.

2 вариант. Забыли добавить знак "равно" =.

3 вариант. Текстовый формат (или пользовательский формат @). Поменяйте на числовой или общий. Но этого будет недостаточно: надо будет зайти в ячейку (F2 или двойной клик или строка формул) и ввести формулу снова, нажав Enter.

4 вариант. Вы в режиме "Показать формулы" (Вкладка "Формулы"). Formulas — Show Formulas. Этот режим де-факто превращает лист в строку формул: вы видите формулы, а не результаты вычислений; и вы видите значения без форматирования, как они есть.

Табличные формулы: 20 приемов и советов для эффективной работы

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

Уверены, что даже опытные табличные формулье найдут для себя что-то новое. Пишите в комментариях, что для вас новое и актуальное!

https://shagabutdinov.ru/tpost/9ejyeuoy41-tablichnie-formuli-20-priemov-i-sovetov

Функции EXPAND / РАЗВЕРНУТЬ и MAKEARRAY + комплексный пример: собираем ТОП-N сделок из всех таблиц по списку одной формулой

Новый видеоурок в курсе "Магия новых функций Excel"
Если вы покупали этот курс, заглядывайте в личный кабинет — вас ждет новый урок про функцию EXPAND / РАЗВЕРНУТЬ и большой пример на многие функции из курса.
Также недавно был добавлен урок "Функция ПРОСМОТРX / XLOOKUP: все нюансы "новой ВПР" для объединения таблиц".

https://shagabutdinov.ru/magic-excel

Быстрое удаление столбцов/строк/ячеек

Ctrl и минус (Ctrl -)
И всего делов!

Если выделена вся строка листа или умной таблицы — она будет удалена без вопросов.
То же самое со столбцами.

Если выделена одна или несколько ячеек — появится диалоговое окно, как на скриншоте.

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

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

Нумеруем только видимые строки

Если мы хотим, чтобы при скрытии строк номера пунктов обновлялись автоматически (то есть нумеровались видимые в моменте строки, а не все, что есть на листе) — нужно использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ / SUBTOTAL.

Ибо она умеет обрабатывать только видимые (не скрытые) ячейки.

В нашем случае мы считаем значения, а не суммируем или что-то еще, то есть нужна функция СЧЁТЗ / COUNTA. Внутри SUBTOTAL ее код — 103 (у SUBTOTAL тип вычисления указывается в первом аргументе).

А что считаем? Для каждой строки мы считаем, сколько значений есть в видимых строках с начала таблицы (у нас это B2) до текущей строки.

Чтобы сформировать ссылку на диапазон от начала до текущей строки, сделаем так:

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;$B$2:B2)

$B$2:B2 — начало всегда в B2, а конец диапазона в строке с формулой. Такую ссылку не задать через F4, придется попотеть и ввести доллары вручную.

_ _ _
Ну а заодно напоминание про 🔥 клавиши:
Ctrl + 9 — скрыть строку
Ctrl + Shift + 9 — отобразить скрытые строки

Магия табличных формул: видеоуроки по подписке

Друзья, на Sponsr уже более 40 видеоуроков (10-30 минут) с исходными и готовыми файлами!

Здесь полный список всего, что уже есть:

Формулы
Вступительное видео. Книги по табличным формулам
1.1 Формулы, ссылки и имена
1.2. Ссылки на таблицы, листы и книги
1.3 Копирование и перенос формул. Скрытие формул
1.4 Операторы в формулах. Приоритет операторов
1.5 Функции и их аргументы. Автосумма. Волатильные функции
1.6 Ошибки в формулах
Задача: генерируем коды вида «АБВ-00001» для переноса в Word и печати наклеек
1.7 Формулы в Google Таблицах

Даты
2.1 Дата и время: основы. Текущие дата и время: вручную и функциями
2.2 Функция ДАТА / DATE. Формируем дату из элементов, создаем последовательность дат, «собираем» дату из текста
2.3 Извлекаем элементы из даты: месяц и год, номер недели, день недели номером и текстом, квартал
2.4 Функции для вычислений с рабочими днями. Производственный календарь
2.5 Время. Текущее время, извлечение часов, форматирование времени и продолжительности

Тексты
3.1 Объединение текста. Функция ТЕКСТ. Мгновенное заполнение
3.2 Замена текста: окно «Найти и заменить» и функции ПОДСТАВИТЬ, ЗАМЕНИТЬ
3.3 Извлекаем фрагменты из текста. Мгновенное заполнение, текст по столбцам и «старые» формулы
3.4 Разделяем текст формулами. Функции ТЕКСТРАЗД / TEXTSPLIT и SPLIT
3.5 Регулярные выражения

Логика и условия
4.1 Логические значения и флажки
4.2 Функции И (AND), ИЛИ (OR). Проектная диаграмма
4.3 Функции ЕСЛИМН / IFS, ПЕРЕКЛЮЧ / SWITCH, ВЫБОР / CHOOSE
4.4 Условное форматирование с формулами
4.5 Проверка данных с формулами
4.6 СУММЕСЛИМН / SUMIFS и компания
4.7 Элементы управления и СУММЕСЛИМН. Флажок и полоса прокрутки
4.8 Функция СУММПРОИЗВ / SUMPRODUCT

А впереди формулы массива, LAMBDA, Google Таблицы, пользовательские функции и многое другое.

Есть еще только 4 места со скидкой за 390 рублей в месяц. При подписке на год еще + скидка. А еще на Sponsr можно приходить в "личку" с табличными вопросами. Подписываться можно по ссылке:
https://sponsr.ru/excel_magic/

Автосумма: одним движением суммы по всем столбцам/месяцам.

Сочетание клавиш Alt + = позволяет получить сумму быстро, не вводя руками функцию СУММ / SUM.
Если выделить ячейку под столбцом с числами и нажать Alt + =, то получим сумму по этому столбцу (одну функцию СУММ).
Уточняем: речь про "просто Alt", то есть левый Alt. Правый Alt заменяет сочетание Ctrl+Alt и в сочетании с плюсом-минусом будет менять масштаб листа.

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

А это чужие онлайн-курсы по Excel / Таблицам, которые я рекомендую

Сам смотрел в разные годы, покупал за свой счет, никто из авторов не просил рекламировать :)

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

Xelplus
Курсы по всем темам от основ до Google Таблиц и Power Query, Python, Power Pivot.
Очень хороший профессиональный монтаж уроков, много дополнительных материалов. Недешево, но понятно, за что платишь.

Язык M — Михаил Музыкин (по подписке на Sponsr)
Высший полет мастерства и погружение в самые глубины языка M. Для тех, кто хочет освоить всю магию Power Query. Смотря иногда чисто для удовольствия, потому что не все может сразу пригодиться на практике (если у вас нет разнообразных и сложных задач в PQ), но настолько все интересно и с нюансами.
Даже на английском, думаю, непросто будет найти аналоги такого уровня.
Есть книга Power Query и язык M, хорошая, объемная и подробная, но местами напоминающая справку и не всегда там есть мостики с практикой.

Бен Коллинс — курсы по Google Таблицам
Есть бесплатные, в том числе по формулам и основам скриптов! Все курсы хороши, со всеми гуглотаблицами-примерами.

Ну а все хорошие книги по таблицам тут. Думаю, что учиться можно и нужно и на практике, и по книгам, и по курсам, кому-то больше подходит одно, кому-то другое, здорово, если получается комбинировать.

Вычисления с рабочими днями
Длительность: 5 мин

Функции РАБДЕНЬ, ЧИСТРАБДНИ, ЧИСТРАБДНИ.МЕЖД: считаем, какая дата наступит через N рабочих дней, в том числе с учетом праздников.

А еще считаем число рабочих дней (в том числе с нестандартной рабочей неделей) или определенных дней (допустим, сред) в периоде.

Этот и пара десятков других видеоуроков доступны по ссылке:
https://shagabutdinov.ru/video