Новое видео: Урок 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 -)
И всего делов!

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

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