Магия Excel

В функции 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

Функция РАЗВЕРНУТЬ / EXPAND Что она делает? Увеличивает размеры массива. Все "дополнительные" значения (то есть дополнительные строки и/или столбцы, то, чего нет в исходном массиве, который задается в первом аргументе функции) будут ошибками #Н/Д (#N/A). Но их можно заменить на какое-то значение — указав его в четвертом аргументе. Вот пример, как мы используем эту функцию, чтобы при сборе топ-N сделок из разных таблиц формировать дополнительный столбец, в котором будет имя каждой таблицы. В предыдущем варианте — по ссылке — мы использовали для этого другую функцию MAKEARRAY.

Видео: объединение таблиц по 2 условиям 8 минут со звуком В видео разбираем, как объединять таблицы по двум условиям: со вспомогательным столбцов и без. В деле функции ВПР / VLOOKUP, ПРОСМОТРX / XLOOKUP и СУММЕСЛИМН / SUMIFS. Файл с примером по ссылке. А другие бесплатные видеоуроки (и этот тоже) можно найти по ссылке: https://shagabutdinov.ru/video

Одной формулой собираем ТОП-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

Новое видео: Урок 18. Функция LET: упрощаем и ускоряем вычисления Покупали когда-либо курс "Магия новых функций Excel"? Тогда скорее в личный кабинет — там вас ждет новый, уже восемнадцатый, урок. Про функцию LET. Совсем скоро — и другие уроки! Про все нюансы функции ПРОСМОТРX / XLOOKUP и ФИЛЬТРацию с регулярными выражениями заодно. https://shagabutdinov.ru/magic-excel P.S. А по этой ссылке найдете ряд бесплатных видеоуроков по Excel и Google Таблицам. И этот список тоже скоро пополнится.

Не устаем рассказывать про одно из наших любимых сочетаний Ctrl+Backspace — это возвращение к активной ячейке. Удобно при работе с формулами, расширенным фильтром, другими окнами. Например. Многие из вас знают одно из любимых Лемуром сочетаний клавиш Ctrl + Shift + стрелки (⌘ + ⇧ + стрелки). Оно позволяет (если ловкости лап хватит все это нажать одновременно) выделить диапазон до последней заполненной ячейки в направлении стрелки. В том числе при вводе формулы — можно выделить диапазон в аргументе функции, как на видео. Но в результате мы можем переместиться довольно далеко от формулы. И чтобы вернуться к текущей ячейке (в которую мы эту самую формулу вводим, не заканчивая при этом ввод формулы), пригодится сочетание — Ctrl + Backspace (⌃ + Delete).

Сводные таблицы Google Spreadsheets— новый курс Это 20 видео с исходными и готовыми файлами с примерами, в которых разбираются все-все нюансы сводных таблиц Google Spreadsheets и дополнительные смежные темы тоже: — Что вообще сводные могут и не могут, какие данные подходят (и как подготовить неподходящие!), как обновляются в Excel и Google (изменение старых данных и новые строки) — Настройка отчетов: вычисления, строки и столбцы, фильтры и срезы, числовые форматы, условное форматирование и спарклайны — Рассчитываемые поля: точно разберетесь, как ссылаться на другие данные из них, использовать условия с визуализацией выполнения и когда рассчитываемые поля не подходят — Сводные без сводных — с помощью формул на LAMBDA. В том числе с текстом в области значений — Сводная из нескольких источников — Функция GETPIVOTDATA — Визуализация: спарклайны одной формулой, в том числе только для промежуточных итогов (одного уровня), спарклайны, меняющие цвет по условию, тепловая карта без чисел, визуализация план-факта спарклайнами и другие примеры — Отмена свертывания (unpivot) https://shagabutdinov.ru/pivot_google

Заставляем функцию обрабатывать массив, даже если она этого не хочет😸 Некоторые функции — например, КОНМЕСЯЦА / EOMONTH (возвращающая последнюю дату месяца, отстоящего от даты на заданное число месяцев) — не хотят выдавать результат в виде массива. А возвращают в такой ситуации ошибку — как в столбце B в примере, когда мы дали функции сразу много дат, чтобы и результат получить в виде массива дат. Но если добавить к ссылке на диапазон знак "+" (плюс), то магия случится! 🔥 Так не работает: =КОНМЕСЯЦА(A2:A16;1) А так работает: =КОНМЕСЯЦА(+A2:A16;1) Почему? Из-за плюса сначала вычисляется выражение с этим самым плюсом — и диапазон A2:A16 становится массивом значений. А с этим уже функция справляется. Это все — про Excel 2021 / 2024 / 365. Ну а в целом про динамические массивы можно посмотреть в этом видео: https://t.me/lemur_excel/95

Функция ОКРУГЛТ / MROUND: округляем числа и время с нужной точностью Вот такая замечательная функция: округляет число (это первый аргумент) с нужной точностью (второй аргумент – число или ссылка на ячейку с числом). Есть, допустим, у вас цены, а вы хотите их «причесать» - с точностью 50: =ОКРУГЛТ(число; точность) Можно округлять и время. Это ведь тоже число в Excel и Google Таблицах – число от нуля до единицы. Например, 0,5 – половина дня, то есть 12:00:00. Соответственно, если мы хотим округлить времена с точностью, допустим, 10 минут, можно поступить так: =ОКРУГЛТ(A2;10/60/24) То есть посчитать, какую долю от суток занимают 10 минут. Или так: =ОКРУГЛТ(A2;"00:10:00") --- 💫Магия новых функций Excel. Революция в табличных формулах: от SORT и FILTER до GROUPBY и LAMBDA

Добавляем гистограммы в сводной таблице отдельным столбцом Друзья, вашему вниманию видео со звуком на пару минут — разбираем, как в сводной добавить отдельный столбец с гистограммами. Если вы хотите, чтобы визуализация была не "поверх ячеек" с данными, а отдельным столбиком, и чтобы это было частью сводной (то есть отражала актуальные данные в случае обновления исходника и соответственно сводной) — это способ для вас. В двух словах: мы добавляем еще один столбец с теми же суммами, применяем к нему условное форматирование (это могут быть не только гистограммы, но и значки / цветовая шкала) и потом в настройках правила условного форматирования включаем опцию "Показывать только столбец" (Show Bar Only).

Новый урок в курсе "Магия новых функций Excel": Флажки в Excel и Google Таблицах Друзья, если вы когда-либо приобретали курс "Магия новых функций Excel", заглядывайте в личный кабинет — там вас ждет новый урок про флажки: — "новые" флажки (checkboxes) Excel — условное форматирование и флажки — ссылки на флажки в формулах — флажки и диаграммы — "старые" флажки Excel (элементы управления) — флажки в Google Таблицах https://shagabutdinov.ru/magic-excel

Был столбец с пунктами типа 10.1, 10.2, 10.3...10.10, 10.11, формат текстовый... Поменял формат на числовой, после чего часть цифр превратилась в пятизначные типа 45430, 44438, часть в даты. Как известно, для оптимиста стакан наполовину полон, а для Excel — первое февраля. И вот тут как раз такой случай, нам нужна нумерация такого вида, которую Excel воспринимает как даты. Чтобы ввести это как текст, ставьте апостроф в начале, который превратит значение в текстовое (в ячейке он отображаться не будет — на скриншоте это третья строка): '10.1 А если нужно уже имеющиеся данные превратить в порядковые пункты, можно использовать такую формулу: =ДЕНЬ(ячейка) & "." & МЕСЯЦ(ячейка) За счет того, что мы склеиваем несколько значений в одну текстовую строку (с помощью амперсанда), результат будет текстом.