Друзья, привет!
Сегодня вашему вниманию один из более чем 70 видеоуроков моего курса по подписке на Sponsr — в нем разбираем задачу из жизни (помогал коллегам с похожей таблицей), в которой будем использовать функции из разных категорий.

Задача — вытащить из текста упоминаемый ID, найти его в таблице и дать гиперссылку на ячейку / строку с ним.

Посмотреть (это видео открыто без подписки) можно здесь, там же ссылка на гуглотаблицу с примером:
https://sponsr.ru/excel_magic/159087/Zadacha_nahodim_metriku_v_tablice_i_formiruem_ssylku_na_yacheiku__stroku_s_nei

Обновление в Excel: массив в одной ячейке

Штуковина, которая постепенно будет доезжать до пользователей 365 — в одной ячейке может быть целый массив.

Теперь, например, можно использовать ФИЛЬТР (как в видео) в умной таблице — раньше в такой ситуации были бы сплошные ошибки #ПЕРЕНОС, т.к. в умных таблицах не предполагалось применять формулы, которые будут возвращать массивы.

Достаточно взять формулу в фигурные скобки. Но это не Ctrl + Shift + Enter, не старые формулы массива, в случае с которыми формула выглядит так:
{=Формула}

Чтобы получить массив в ячейке, нужно вручную ввести скобки так:
={Формула}

Видео — 5 мин со звуком, можно посмотреть на Kinescope (доступно в России)

Если без Excel и Google Таблиц не проходит и дня, — вы наверняка знаете, сколько времени в них теряется.

В 2026 году до 80% рутины уже давно делает ИИ. По сути, вы получаете бесплатного ассистента внутри таблицы, который выдаёт формулы любой сложности, находит аномалии и делает выводы.

Чтобы вернуть себе до 10 часов в неделю, отдайте часть своей работы ИИ — на курсе «Нейросети для Excel и Google Таблиц» от Академии Эдюсон.

Эффект будет уже с первого урока: вы получите набор готовых инструментов, чек-листов и шаблонов для быстрого внедрения ИИ в свои задачи.

Через пару недель вы сможете:
— Работать с самыми популярными ИИ-сервисами для работы с таблицами.
— Сокращать рутину за счёт автоматизации и шаблонов.
— Быстро собирать и анализировать любую информацию, чтобы в ней было легко ориентироваться.
— Визуализировать данные — понятно для коллег и руководства.
— Создавать ИИ-агентов для повторяющихся задач: отчётов, поиска ошибок, визуализации.

А также:
→ 365 дней поддержки личного куратора;
→ удостоверение о повышении квалификации;
→ бессрочный доступ к материалам и обновлениям.

Оставьте заявку с промокодом НЕЙРОЭКСЕЛЬ — получите повышенную скидку 65%.

Списки в ячейках Excel: первое знакомство

Продолжаем изучать новые опции и функции!

В этом видео (5 минут со звуком) смотрим на списки в ячейках Excel и функции HAS — три функции, которые позволяют определить, встречается ли в массиве конкретное значение (HAS), хотя бы какое-то из списка (HASANY), все из списка (HASALL)

Смотреть на Kinescope

Автоподбор ширины

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

Можно сделать так, чтобы менялся размер шрифта, чтобы числа в любом случае отображались в ячейках даже при небольшой ширине столбца. Выделяем ячейки — Ctrl + 1 — вкладка "Выравнивание" — "автоподбор ширины" (Shrink to fit)

Смотрим на видео без звука!

Имя умной таблицы при создании

Не только модные и мощные вещи вроде массивов с вложенными массивами в ячейках представили в Excel в эти дни.

Но появилась и такая простая и удобная вещь — спустя пару десятилетий после появления "умных" таблиц — как возможность сразу при создании задавать им имя 🔥А не лезть потом во вкладку "Конструктор таблиц".

Создание умной таблицы, напомню:
1 Ctrl + T
2 Ctrl + L
3 Вставка — Таблица
4 Главная — Форматировать как таблицу

Традиционно ждем, когда красота будет доступна всем в 365 и в будущих коробочных версиях.

P.S. Вот тут ранее я писал про то, что можно добавить поле с именем умной таблицы на панель быстрого доступа, если вы часто эти самые таблицы создаете

Делим текст на отдельные символы формулой. Варианты для разных версий Excel

Excel 365
=РЕГИЗВЛЕЧЬ(ячейка;".";1)
Извлекаем с помощью РЕГИЗВЛЕЧЬ / REGEXEXTRACT любой символ (точка в регулярках = любой символ). Чтобы получить не только первое совпадение, а все символы, задаем третий аргумент, равный единице. Иначе извлекается только первый символ.

Excel 2021
=ПСТР(ячейка;ПОСЛЕД(;ДЛСТР(ячейка));1)
Формируем последовательность чисел функцией ПОСЛЕД / SEQUENCE — от единицы до числа символов в тексте (ДЛСТР / LEN выдаст число знаков). И извлекаем функцией ПСТР / MID символы. Эта функция возвращает один или несколько (у нас один — это задано в третьем аргументе) символов с заданной (во втором аргументе) позиции. А у нас позиций будет много: сформированная последовательность чисел от 1 до числа знаков. То есть мы вытащим первый, второй и так далее до последнего символа из ячейки.

Любая версия
=ПСТР($A$1;СТОЛБЕЦ()-1;1)
Здесь нужно будет формулу протянуть, это не формула массива, а отдельная для каждого символа. Функция СТОЛБЕЦ / COLUMN возвращает номер столбца. Так как у меня символы в примере извлекаются в столбце B и далее, я вычитаю единицу из номера столбца. Чтобы начать с первого символа.

Все аргументы текстом прямо в формуле

Введите функцию и нажмите Ctrl + Shift + A

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

Но если вам так проще воспринимать аргументы и работать с функцией — почему нет?

Условное форматирование в ячейках со списками

Мы с вами уже немного про сверхновые списки говорили (тут) — продолжим разговор.

Списки пока мало с чем работают из стандартных инструментов Excel:
— сводные таблицы
— диаграммы
— проверка данных
— Power Query
— Найти и заменить
— Условное форматирование

Но вот в случае с последним мы найдем решение — превратим внутри формулы список в массив. Конечно, на очень больших объемах данных это станет безобразием, но в целом работать будет.
Смотрите в коротком видео со звуком — выделяем строки, в которых внутри ячейки в списке среднее значение чисел выше определенного.

Видео на Kinescope

Друзья, хочу напомнить, что еще пишу в личном канале: @shagabutdinov_r

Там:
— книги: что читаю и что советую, нон-фикшн о продуктивности и не только
— бег и бумажный журнал «Бегать просто», который я издаю
— путешествия (мини-отчеты из десятков стран), футбол и всё, что не влезает в таблицы
— список моих любимых книг

Здесь всё остаётся как было :) Буду рад видеть вас у себя на личном канале тоже.

Продолжим исследовать новый дивный мир Excel, где больше нет правила "одна ячейка — одно значение"

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

И будем ВПР-ить — сначала нужную таблицу по названию продукта, а потом внутри найденной таблицы будем искать нужную подкатегорию 🤯 И все это из одной ячейки...

Это и другие нюансы новых массивов в ячейках — в видео по ссылке, там же файл:
https://sponsr.ru/excel_magic/191086/113_Massivy_v_yacheikah/

Вся эта красота пока доступна только в бета-канале обновлений и мало с чем работает (сводные таблицы, Power Query — хотя все это отчасти напоминает PQ с таблицами/списками внутри таблиц и т.д., не правда ли? проверка данных, диаграммы, условное форматирование, VBA...). Так что знакомимся на перспективу, так сказать)