Магия Excel

Продолжим исследовать новый дивный мир Excel, где больше нет правила "одна ячейка — одно значение" Посмотрим на вложенные массивы в ячейке: сначала с помощью новых (но уже не сверхновых — это теперь предыдущая экселевская революция) лямбда-формул сформируем в одной ячейке таблицу с таблицами, где для каждого продукта будет своя табличка И будем ВПР-ить — сначала нужную таблицу по названию продукта, а потом внутри найденной таблицы будем искать нужную подкатегорию 🤯 И все это из одной ячейки... Это и другие нюансы новых массивов в ячейках — в видео по ссылке, там же файл: https://sponsr.ru/excel_magic/191086/113_Massivy_v_yacheikah/ Вся эта красота пока доступна только в бета-канале обновлений и мало с чем работает (сводные таблицы, Power Query — хотя все это отчасти напоминает PQ с таблицами/списками внутри таблиц и т.д., не правда ли? проверка данных, диаграммы, условное форматирование, VBA...). Так что знакомимся на перспективу, так сказать)

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

Условное форматирование в ячейках со списками Мы с вами уже немного про сверхновые списки говорили (тут) — продолжим разговор. Списки пока мало с чем работают из стандартных инструментов Excel: — сводные таблицы — диаграммы — проверка данных — Power Query — Найти и заменить — Условное форматирование Но вот в случае с последним мы найдем решение — превратим внутри формулы список в массив. Конечно, на очень больших объемах данных это станет безобразием, но в целом работать будет. Смотрите в коротком видео со звуком — выделяем строки, в которых внутри ячейки в списке среднее значение чисел выше определенного. Видео на Kinescope

Все аргументы текстом прямо в формуле Введите функцию и нажмите Ctrl + Shift + A Все аргументы функции появятся прямо в формуле — текстом. Понятно, что после этого функция работать не будет, пока вы не замените эти "заглушки" на значения правильных типов. Но если вам так проще воспринимать аргументы и работать с функцией — почему нет?

Делим текст на отдельные символы формулой. Варианты для разных версий Excel Excel 365 =РЕГИЗВЛЕЧЬ(ячейка;".";1) Извлекаем с помощью РЕГИЗВЛЕЧЬ / REGEXEXTRACT любой символ (точка в регулярках = любой символ). Чтобы получить не только первое совпадение, а все символы, задаем третий аргумент, равный единице. Иначе извлекается только первый символ. Excel 2021 =ПСТР(ячейка;ПОСЛЕД(;ДЛСТР(ячейка));1) Формируем последовательность чисел функцией ПОСЛЕД / SEQUENCE — от единицы до числа символов в тексте (ДЛСТР / LEN выдаст число знаков). И извлекаем функцией ПСТР / MID символы. Эта функция возвращает один или несколько (у нас один — это задано в третьем аргументе) символов с заданной (во втором аргументе) позиции. А у нас позиций будет много: сформированная последовательность чисел от 1 до числа знаков. То есть мы вытащим первый, второй и так далее до последнего символа из ячейки. Любая версия =ПСТР($A$1;СТОЛБЕЦ()-1;1) Здесь нужно будет формулу протянуть, это не формула массива, а отдельная для каждого символа. Функция СТОЛБЕЦ / COLUMN возвращает номер столбца. Так как у меня символы в примере извлекаются в столбце B и далее, я вычитаю единицу из номера столбца. Чтобы начать с первого символа.

Имя умной таблицы при создании Не только модные и мощные вещи вроде массивов с вложенными массивами в ячейках представили в Excel в эти дни. Но появилась и такая простая и удобная вещь — спустя пару десятилетий после появления "умных" таблиц — как возможность сразу при создании задавать им имя 🔥А не лезть потом во вкладку "Конструктор таблиц". Создание умной таблицы, напомню: 1 Ctrl + T 2 Ctrl + L 3 Вставка — Таблица 4 Главная — Форматировать как таблицу Традиционно ждем, когда красота будет доступна всем в 365 и в будущих коробочных версиях. P.S. Вот тут ранее я писал про то, что можно добавить поле с именем умной таблицы на панель быстрого доступа, если вы часто эти самые таблицы создаете

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

С огромным удовольствием читаю новую книгу Николая Павлова И опять восхищаюсь его умением объяснять — даже ради этого стоит его книги почитать! Ну а это единственная книга на русском про функции DAX, если говорить про российских авторов. Конечно, есть переводная мощь Феррари и Руссо, но на русском есть второе издание, а в оригинале уже третье, сильно обновленное. И она сложнее. А Николаю удается объяснять непростые вещи максимально понятно, насколько это возможно. Браво!

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

Если без Excel и Google Таблиц не проходит и дня, — вы наверняка знаете, сколько времени в них теряется. В 2026 году до 80% рутины уже давно делает ИИ. По сути, вы получаете бесплатного ассистента внутри таблицы, который выдаёт формулы любой сложности, находит аномалии и делает выводы. Чтобы вернуть себе до 10 часов в неделю, отдайте часть своей работы ИИ — на курсе «Нейросети для Excel и Google Таблиц» от Академии Эдюсон. Эффект будет уже с первого урока: вы получите набор готовых инструментов, чек-листов и шаблонов для быстрого внедрения ИИ в свои задачи. Через пару недель вы сможете: — Работать с самыми популярными ИИ-сервисами для работы с таблицами. — Сокращать рутину за счёт автоматизации и шаблонов. — Быстро собирать и анализировать любую информацию, чтобы в ней было легко ориентироваться. — Визуализировать данные — понятно для коллег и руководства. — Создавать ИИ-агентов для повторяющихся задач: отчётов, поиска ошибок, визуализации. А также: → 365 дней поддержки личного куратора; → удостоверение о повышении квалификации; → бессрочный доступ к материалам и обновлениям. Оставьте заявку с промокодом НЕЙРОЭКСЕЛЬ — получите повышенную скидку 65%.

Обновление в Excel: массив в одной ячейке Штуковина, которая постепенно будет доезжать до пользователей 365 — в одной ячейке может быть целый массив. Теперь, например, можно использовать ФИЛЬТР (как в видео) в умной таблице — раньше в такой ситуации были бы сплошные ошибки #ПЕРЕНОС, т.к. в умных таблицах не предполагалось применять формулы, которые будут возвращать массивы. Достаточно взять формулу в фигурные скобки. Но это не Ctrl + Shift + Enter, не старые формулы массива, в случае с которыми формула выглядит так: {=Формула} Чтобы получить массив в ячейке, нужно вручную ввести скобки так: ={Формула} Видео — 5 мин со звуком, можно посмотреть на Kinescope (доступно в России) Video is too big

Друзья, привет! Сегодня вашему вниманию один из более чем 70 видеоуроков моего курса по подписке на Sponsr — в нем разбираем задачу из жизни (помогал коллегам с похожей таблицей), в которой будем использовать функции из разных категорий. Задача — вытащить из текста упоминаемый ID, найти его в таблице и дать гиперссылку на ячейку / строку с ним. Посмотреть (это видео открыто без подписки) можно здесь, там же ссылка на гуглотаблицу с примером: https://sponsr.ru/excel_magic/159087/Zadacha_nahodim_metriku_v_tablice_i_formiruem_ssylku_na_yacheiku__stroku_s_nei

Forwarded From Николай Павлов (Планета Excel) За последние пару-тройку лет в Excel 365 появилось много мелких и не очень нововведений (не считая новых функций листа). Большинство из них не тянут на отдельное видео, так что я решил рассказать про них оптом 😁 ✔️ Фокусировка на ячейке ✔️ Копирование итогов из строки состояния ✔️ Улучшенный выпадающий список ✔️ Отображение сразу нескольких скрытых листов ✔️ Панель навигации ✔️ Улучшенный тёмный режим ✔️ Проверка производительности ✔️ Устаревшие значения ✔️ Контрастные цвета ✔️ Поддержка эмодзи и юникода ✔️ Распознавание текста с изображений ✔️ Вставка картинок в ячейку ✔️ Инсайты от ИИ Понятно, что в текущих российских реалиях наличие установленной на компьютере последней версии Microsoft Office 365 по подписке - это роскошь и есть далеко не у всех. Но то, что появляется сначала эксклюзивом только в подписке Excel 365, спустя какое-то время Microsoft обычно добавляет и в регулярные версии Excel, так что надежда есть 😉 Например, некоторые из описанных фич уже есть в Excel 2024. ▶️ Смотреть видео и читать статью у меня на сайте (без VPN) https://www.planetaexcel.ru/techniques/11/72537/ ▶️ Смотреть видео на YouTube https://youtu.be/uZW4ICrRnFY Video is too big

Горячие клавиши по понедельникам 🔥 Ctrl с минусом: удаление. Удаление чего? Если у вас выделены столбцы или строки полностью, они и будут удаляться без лишних вопросов! Если ячейка или диапазон — то Excel уточнит, хотите ли вы удалить ячейки, строки или столбцы. В умной таблице будет удаляться строка, если выделена одна ячейка, и столбец, если несколько в одном столбце. В сводной таблице выделенные элементы (в области строк или столбцов) будут скрыты (отфильтрованы). Ну а если мы добавим Shift, то будем удалять границы ячеек.

Видео: разбор формулы, которая вычисляет серию игр без ничьих по каждой команде. Новые функции Excel во всей  красе Маэстро Михаил Музыкин поделился со мной формулой, которую он выдал в качестве решения на одном из форумов по Excel Задача в том, чтобы вычислить, сколько матчей к данному моменту у каждой команды было без ничейных результатов подряд. Тут во всей красе новые и сверхновые функции, а я их обожаю, так что решил записать видео с разбором формулы по шагам, надеюсь, это поможет новичкам понять силу функций и лучше с ними познакомиться! Видео на Kinescope Ссылка на тему на форуме Канал Михаила

Как избежать вставки ссылок в диалоговых окнах Вот редактируете вы какую-то формулу или диапазон в окне условного форматирования или в диспетчере имен Excel. И нажимаете стрелку влево или вправо на клавиатуре, чтобы... переместить курсор. И в этот момент Excel вставляет ссылки на ячейки. А-а-а-а-а! Как от этой гадости избавиться? Нажать F2. И тогда стрелки будут перемещать курсор. При вводе формул в ячейках это тоже работает. Опознать режим можно по надписи в левом нижнем углу (в строке состояния) — если там "Правка" (Edit), то можно смело нажимать на стрелки :)

У вас есть умная таблица. Это здорово! Ее, кстати, можно создать из диапазона сочетаниями клавиш Ctrl + T и Ctrl + L. А в умной таблице у вас есть строка итогов. Это тоже здорово. И для нее есть сочетание клавиш: Ctrl + Shift + T. И добавляет, и убирает строку итогов. Но вот проблема: без строки итогов можно просто "дописывать" данные к таблице — если вводить что-то в первой пустой строке под таблицей, новые строки автоматически станут ее частью. Но строка итогов всегда в самом низу таблицы, и если вводить данные под ней — ничего не произойдет, размеры таблицы не изменятся. Выход? Перемещаемся в последнюю строку с данными (не в строку итогов) и в последний столбец. Нажимаем Tab. Это самый быстрый способ добавить строку! Очень короткое видео без звука с демонстрацией.

У вас есть объект графического слоя — рисунок, фигура (как на скриншотах), диаграмма, срез умной / сводной таблицы и т.п. Если тянуть левой кнопкой мыши за маркеры (углы) объекта, его размеры будут меняться. Добавляем Alt — и объект будет выравниваться по сетке (границам ячеек). Очень удобно, когда несколько диаграмм, например, нужно аккуратно расположить Shift — и пропорции будут сохраняться. Ctrl + Shift — пропорции будут сохраняться, а центр объекта будет оставаться на месте, то есть он будет увеличиваться "во все стороны", а не в сторону выбранного маркера, за который вы тянете. Просто Ctrl — с сохранением расположения центра, но с изменением пропорций. Можно пользовать не только в Excel — но и в Google Презентациях, к примеру!