Теперь в ПРОСМОТРX / XLOOKUP есть поддержка регулярных выражений. Ее добавили в декабре 2024 и пока она доступна только подписчикам Microsoft 365.
Чтобы использовать регулярные выражения при поиске с помощью этой функции, нужно задать пятый аргумент – если этого не сделать, то по умолчанию будет точный поиск. Нам нужно 3 – соответствие регулярных выражений. Кстати, напомню, что вариант 2 – это возможность использовать символы подстановки * (любое, в том числе нулевое, количество любых знаков) и ? (один любой знак). Старая ВПР / VLOOKUP, кстати, умеет работать со звездочкой и знаком вопроса по умолчанию.
Вот пример с регуляркой в ПРОСМОТРX: ищем цену первого товара, в котором будет либо 3xUSB в названии, либо 4xUSB (символы в квадратных скобках в регулярках = один символ из набора).
Аналогично регулярки теперь поддерживаются и ПОИСКПОЗX / XMATCH.
___
А кто узнал об этом первым? А первым об этом узнали подписчики рассылки "Магия таблиц" 😸Присоединяйтесь:
https://shagabutdinov.ru/#subscription
Чтобы использовать регулярные выражения при поиске с помощью этой функции, нужно задать пятый аргумент – если этого не сделать, то по умолчанию будет точный поиск. Нам нужно 3 – соответствие регулярных выражений. Кстати, напомню, что вариант 2 – это возможность использовать символы подстановки * (любое, в том числе нулевое, количество любых знаков) и ? (один любой знак). Старая ВПР / VLOOKUP, кстати, умеет работать со звездочкой и знаком вопроса по умолчанию.
Вот пример с регуляркой в ПРОСМОТРX: ищем цену первого товара, в котором будет либо 3xUSB в названии, либо 4xUSB (символы в квадратных скобках в регулярках = один символ из набора).
Аналогично регулярки теперь поддерживаются и ПОИСКПОЗX / XMATCH.
___
А кто узнал об этом первым? А первым об этом узнали подписчики рассылки "Магия таблиц" 😸Присоединяйтесь:
https://shagabutdinov.ru/#subscription
Друзья, если вы покупали курс "Магия новых функций Excel", заглядывайте в личный кабинет!
Там вас ждет новый урок про функцию TRIMRANGE на 15 минут и файлы-примеры к нему.
В уроке дополнительно покрутим функции FILTER и UNIQUE, поговорим про то, как в старых версиях сделать именованный динамический диапазон с помощью ИНДЕКСа и, собственно, покрутим разные примеры с основным блюдом — TRIMRANGE / УРЕЗДИАПАЗОН
Если вы не покупали — теперь в курсе 15 уроков, и вот они:
1 / Старые и новые формулы. Новые типы ссылок — собачка (@) и решетка (#). Новая ошибка #SPILL. Старые функции в новых формулах. Новые формулы и старые инструменты Excel. Функция UNIQUE
2 / Функции SORT, SORTBY и FILTER
3 / Функция SEQUENCE: формируем последовательность из чисел или дат (или букв? 😉) одной формулой
4 / Функции для разделения текста: TEXTSPLIT и другие
5 / Функции для работы с регулярными выражениями
6 / Функции для перевода и определения языка
7 / Делаем плоское квадратным и наоборот: TOCOL и TOROW, WRAPROWS и WRAPCOLS
8 / Объединяем массивы/таблицы: функции VSTACK и HSTACK
9 / Извлекаем строки и столбцы: функции CHOOSECOLS и CHOOSEROWS, DROP и TAKE
10 / Функция LAMBDA и вспомогательная функция MAP
11 / Функция MAP: собираем данные с разных листов
12 / Функции BYROW и BYCOL: последовательно обрабатываем каждую строку (столбец) в диапазоне
13 / Функция REDUCE: применяем вычисление к каждому элементу и получаем накопленный итог и функция SCAN: рассчитываем нарастающие итоги с разной магией
14 / Функции PIVOTBY и GROUPBY — сводные таблицы… формулами!
15 / Функция TRIMRANGE: удаляем пустые строки и столбцы
https://shagabutdinov.ru/magic-excel
Там вас ждет новый урок про функцию TRIMRANGE на 15 минут и файлы-примеры к нему.
В уроке дополнительно покрутим функции FILTER и UNIQUE, поговорим про то, как в старых версиях сделать именованный динамический диапазон с помощью ИНДЕКСа и, собственно, покрутим разные примеры с основным блюдом — TRIMRANGE / УРЕЗДИАПАЗОН
Если вы не покупали — теперь в курсе 15 уроков, и вот они:
1 / Старые и новые формулы. Новые типы ссылок — собачка (@) и решетка (#). Новая ошибка #SPILL. Старые функции в новых формулах. Новые формулы и старые инструменты Excel. Функция UNIQUE
2 / Функции SORT, SORTBY и FILTER
3 / Функция SEQUENCE: формируем последовательность из чисел или дат (или букв? 😉) одной формулой
4 / Функции для разделения текста: TEXTSPLIT и другие
5 / Функции для работы с регулярными выражениями
6 / Функции для перевода и определения языка
7 / Делаем плоское квадратным и наоборот: TOCOL и TOROW, WRAPROWS и WRAPCOLS
8 / Объединяем массивы/таблицы: функции VSTACK и HSTACK
9 / Извлекаем строки и столбцы: функции CHOOSECOLS и CHOOSEROWS, DROP и TAKE
10 / Функция LAMBDA и вспомогательная функция MAP
11 / Функция MAP: собираем данные с разных листов
12 / Функции BYROW и BYCOL: последовательно обрабатываем каждую строку (столбец) в диапазоне
13 / Функция REDUCE: применяем вычисление к каждому элементу и получаем накопленный итог и функция SCAN: рассчитываем нарастающие итоги с разной магией
14 / Функции PIVOTBY и GROUPBY — сводные таблицы… формулами!
15 / Функция TRIMRANGE: удаляем пустые строки и столбцы
https://shagabutdinov.ru/magic-excel
shagabutdinov.ru
Ренат Шагабутдинов | Магия новых функций Excel
Для новичков и опытных пользователей. Создать интерактивный отчёт с выбором параметров. Сводные таблицы. Разделить текст на отдельные символы и многое другое. Доступ к курсу сразу после оплаты.
Два способа перемещения между листами Excel с помощью клавиатуры
Первый:
Сочетания клавиш Ctrl + PgUp (предыдущий лист, влево) и Ctrl + PgDn (следующий, вправо)
Второй:
Нажмите F6 (и отпустите). Выделится ярлык текущего листа.
После этого стрелками на клавиатуре выберите нужный вам лист и нажмите Enter.
_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥
Первый:
Сочетания клавиш Ctrl + PgUp (предыдущий лист, влево) и Ctrl + PgDn (следующий, вправо)
Второй:
Нажмите F6 (и отпустите). Выделится ярлык текущего листа.
После этого стрелками на клавиатуре выберите нужный вам лист и нажмите Enter.
_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥
shagabutdinov.ru
Ренат Шагабутдинов | Магия новых функций Excel
Для новичков и опытных пользователей. Создать интерактивный отчёт с выбором параметров. Сводные таблицы. Разделить текст на отдельные символы и многое другое. Доступ к курсу сразу после оплаты.
💥Магия табличных формул. Обучение по подписке
Друзья, рад анонсировать новый формат обучения — по подписке. Понимаю, что большие курсы давят своим объемом: когда тебе открываются сразу десятки уроков. Здесь все будет постепенно: каждую неделю минимум одно видео (можно настроить уведомления о новых видео по почте — удобно). И вот так плавно мы будем погружаться в тему табличных формул все глубже и глубже.
Быстро — это медленно без перерывов: через несколько месяцев вы будете на другом уровне. Эти месяцы пройдут в любом случае, а вот получится ли научиться новому — зависит только от вас :)
Какие особенности?
— На вас не давит большое количество видео, уроки появляются каждую неделю. На меня не давит длительность видео (ведь я не выдаю вам сразу 30 уроков по 15 минут, а по одному видео за раз), так что я более подробно, чем обычно, рассматриваю каждую тему с отступлениями вправо и влево и отсылками к другим темам и инструментам. Более глубокое погружение.
— Начнем с основ, но даже в простых темах будут нюансы, о которых, уверен, знали и не все опытные экселье. Постепенно дойдем и до многоэтажных формул, и до создания собственных (пользовательских) функций с помощью VBA и LAMBDA-функций.
— Видео свежие, то есть все обновления будут учтены.
— Подходит для всех версий Excel. Снимаю в интерфейсе Excel на русском, но буду озвучивать названия функций на английском. Так как говорить будем про формулы, то 95% информации и функций актуально и для Google Таблиц, многое и для российского Р7.
— Как всегда — хороший монтаж без шумов, пауз, с приближением экрана и стрелочками-кнопочками. Ко всем видео — исходные и готовые файлы. Можно (и нужно) повторять самостоятельно и смотреть готовые решения.
Подписаться можно тут:
https://sponsr.ru/excel_magic/
Три видео уже доступны подписчикам: обзор книг и сравнение формул/макросов/Power Query, урок про ссылки на ячейки, имена и стиль ссылок R1C1, урок про ссылки на умные таблицы, другие листы и книги.
Один из уроков в открытом доступе. Можно посмотреть и оценить формат.
Первые 100 подписчиков получают цену с хорошей скидкой ;) Такой цены больше не будет.
С любыми вопросами приходите в личные сообщения или на почту [email protected].
Друзья, рад анонсировать новый формат обучения — по подписке. Понимаю, что большие курсы давят своим объемом: когда тебе открываются сразу десятки уроков. Здесь все будет постепенно: каждую неделю минимум одно видео (можно настроить уведомления о новых видео по почте — удобно). И вот так плавно мы будем погружаться в тему табличных формул все глубже и глубже.
Быстро — это медленно без перерывов: через несколько месяцев вы будете на другом уровне. Эти месяцы пройдут в любом случае, а вот получится ли научиться новому — зависит только от вас :)
Какие особенности?
— На вас не давит большое количество видео, уроки появляются каждую неделю. На меня не давит длительность видео (ведь я не выдаю вам сразу 30 уроков по 15 минут, а по одному видео за раз), так что я более подробно, чем обычно, рассматриваю каждую тему с отступлениями вправо и влево и отсылками к другим темам и инструментам. Более глубокое погружение.
— Начнем с основ, но даже в простых темах будут нюансы, о которых, уверен, знали и не все опытные экселье. Постепенно дойдем и до многоэтажных формул, и до создания собственных (пользовательских) функций с помощью VBA и LAMBDA-функций.
— Видео свежие, то есть все обновления будут учтены.
— Подходит для всех версий Excel. Снимаю в интерфейсе Excel на русском, но буду озвучивать названия функций на английском. Так как говорить будем про формулы, то 95% информации и функций актуально и для Google Таблиц, многое и для российского Р7.
— Как всегда — хороший монтаж без шумов, пауз, с приближением экрана и стрелочками-кнопочками. Ко всем видео — исходные и готовые файлы. Можно (и нужно) повторять самостоятельно и смотреть готовые решения.
Подписаться можно тут:
https://sponsr.ru/excel_magic/
Три видео уже доступны подписчикам: обзор книг и сравнение формул/макросов/Power Query, урок про ссылки на ячейки, имена и стиль ссылок R1C1, урок про ссылки на умные таблицы, другие листы и книги.
Один из уроков в открытом доступе. Можно посмотреть и оценить формат.
Первые 100 подписчиков получают цену с хорошей скидкой ;) Такой цены больше не будет.
С любыми вопросами приходите в личные сообщения или на почту [email protected].
Sponsr
Магия табличных формул. От A1 до LAMBDA
Видеоуроки по формулам Excel (и не только): все нюансы и правила для новичков, новые функции, файлы с данными для практики и готовыми примерами
Не показывать нули в ячейках: разные варианты
На уровне всей книги или отдельного листа: заходим в Параметры Excel — Дополнительно — выбираем лист или книгу — Показывать нули в ячейках, которые содержат нулевые ячейки
(этот вариант на скриншоте)
На уровне диапазона: выделяем диапазон — Ctrl + 1 — (все форматы) — задаем пользовательский формат, в котором третий формат (для нуля) оставляем пустым, например:
(в пользовательских форматах через точку с запятой задаются форматы для положительных, отрицательных, нуля и текста; если нулевой формат не задан, то к нулям применяется первый формат для положительных чисел, а если задан явно, но пустым — то нули не будут отображаться)
На уровне поля сводной таблицы:
правая кнопка мыши по любому значению в области значений — Числовой формат (не "Формат ячейки", т.к. это форматирование конкретной ячейки, а вот "Числовой формат" — всего поля) — и там тоже задаем пользовательский формат ("все форматы").
--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!
На уровне всей книги или отдельного листа: заходим в Параметры Excel — Дополнительно — выбираем лист или книгу — Показывать нули в ячейках, которые содержат нулевые ячейки
(этот вариант на скриншоте)
На уровне диапазона: выделяем диапазон — Ctrl + 1 — (все форматы) — задаем пользовательский формат, в котором третий формат (для нуля) оставляем пустым, например:
0;-0;
(в пользовательских форматах через точку с запятой задаются форматы для положительных, отрицательных, нуля и текста; если нулевой формат не задан, то к нулям применяется первый формат для положительных чисел, а если задан явно, но пустым — то нули не будут отображаться)
На уровне поля сводной таблицы:
правая кнопка мыши по любому значению в области значений — Числовой формат (не "Формат ячейки", т.к. это форматирование конкретной ячейки, а вот "Числовой формат" — всего поля) — и там тоже задаем пользовательский формат ("все форматы").
--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!
Ставили цели на этот год? Обратите внимание, что 13% года уже прошли 😈
Как это можно вычислить и визуализировать?
Используем функцию ДОЛЯГОДА / YEARFRAC. У нее два обязательных аргумента — две даты.
Если нужна универсальная формула, можно вычислять первую дату текущего года и текущую дату — такая формула всегда будет возвращать долю прошедших в текущем году дней.
Самая лаконичная визуализация прогресса — гистограмма условного форматирования. Просто копируем формулу во вторую ячейку (или ссылаемся на эту ячейку), вставляем гистограмму (Главная — Условное форматирование — Гистограммы), меняем минимум и максимум на ноль и единицу. Можно добавить заливку ячейки другим цветом, как в примере.
--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!
Как это можно вычислить и визуализировать?
Используем функцию ДОЛЯГОДА / YEARFRAC. У нее два обязательных аргумента — две даты.
Если нужна универсальная формула, можно вычислять первую дату текущего года и текущую дату — такая формула всегда будет возвращать долю прошедших в текущем году дней.
=ДОЛЯГОДА(ДАТА(ГОД(СЕГОДНЯ());1;1);СЕГОДНЯ())
Самая лаконичная визуализация прогресса — гистограмма условного форматирования. Просто копируем формулу во вторую ячейку (или ссылаемся на эту ячейку), вставляем гистограмму (Главная — Условное форматирование — Гистограммы), меняем минимум и максимум на ноль и единицу. Можно добавить заливку ячейки другим цветом, как в примере.
--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!
Переключение дэшборда между днями и неделями — с помощью функции SEQUENCE
Итак, вы хотите создать простой дэшборд, в котором будете агрегировать данные по неделям или дням.
И при этом хотите легко переключать режим «недели / дни» (или изменение любого другого параметра), не залезая в формулы.
Статья и пример в Google Таблицах. Но в новом Excel такое тоже можно реализовать — и флажки, и функция SEQUENCE / ПОСЛЕД в наличии!
https://shagabutdinov.ru/blog/tpost/jbrryezom1-pereklyuchenie-deshborda-mezhdu-dnyami-i
--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!
Итак, вы хотите создать простой дэшборд, в котором будете агрегировать данные по неделям или дням.
И при этом хотите легко переключать режим «недели / дни» (или изменение любого другого параметра), не залезая в формулы.
Статья и пример в Google Таблицах. Но в новом Excel такое тоже можно реализовать — и флажки, и функция SEQUENCE / ПОСЛЕД в наличии!
https://shagabutdinov.ru/blog/tpost/jbrryezom1-pereklyuchenie-deshborda-mezhdu-dnyami-i
--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!
shagabutdinov.ru
Переключение дэшборда между днями и неделями в Google Таблицах — с помощью функции SEQUENCE
Как переключать режим “недели / дни" (или изменение любого другого параметра), не залезая в формулы
This media is not supported in your browser
VIEW IN TELEGRAM
Вывести все имена и соответствующие диапазоны на лист
Вот как можно сформировать табличку (диапазон) со списком всех или некоторых имен и их диапазонов:
Вкладка "Формулы" — Определенные имена — Использовать в формуле — Вставить имена
Formulas — Defined Names — Use in Formula — Paste Names
А зачем может пригодиться? Если вы применили имя в формуле, а потом удалили это имя (это можно сделать в диспетчере имен, Ctrl + F3), формула будет возвращать ошибку, хотя само имя в формуле останется.
То есть если у вас было
А если вывести куда-то список имен, то сможете посмотреть, какой диапазон каким именем был назван.
Можно ли отключить такое поведение? Чтобы при удалении имени оно исчезало из формулы, заменялось на диапазон?
Можно. Для конкретного листа:
Файл — Параметры — Дополнительно — Параметры совместимости с Lotus 1-2-3 — Преобразовывать формулы в формат Excel при вводе
--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!
Вот как можно сформировать табличку (диапазон) со списком всех или некоторых имен и их диапазонов:
Вкладка "Формулы" — Определенные имена — Использовать в формуле — Вставить имена
Formulas — Defined Names — Use in Formula — Paste Names
А зачем может пригодиться? Если вы применили имя в формуле, а потом удалили это имя (это можно сделать в диспетчере имен, Ctrl + F3), формула будет возвращать ошибку, хотя само имя в формуле останется.
То есть если у вас было
=Выручка*Налог, то так оно и останется, "Налог" не будет заменен на ту ячейку, которая названа этим именем.А если вывести куда-то список имен, то сможете посмотреть, какой диапазон каким именем был назван.
Можно ли отключить такое поведение? Чтобы при удалении имени оно исчезало из формулы, заменялось на диапазон?
Можно. Для конкретного листа:
Файл — Параметры — Дополнительно — Параметры совместимости с Lotus 1-2-3 — Преобразовывать формулы в формат Excel при вводе
--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!
This media is not supported in your browser
VIEW IN TELEGRAM
Функция ЛИСТ / SHEET
Возвращает она порядковый номер (индекс) листа.
И этот номер может меняться. Он зависит от положения листа — они нумеруются от 1 до N, где N — количество листов в книге. Скрытые листы считаются.
Функция без аргументов будет возвращать номер листа, на котором находится:
С аргументом (ссылкой) будет возвращать номер листа, на который ссылка:
Если лист переместить, то его номер меняется. Соответственно, можно придумать формулу с проверкой. Например, такую, которая будет сигнализировать об ошибке, если лист с оглавлением передвинуть вправо (как на видео):
--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!
Возвращает она порядковый номер (индекс) листа.
И этот номер может меняться. Он зависит от положения листа — они нумеруются от 1 до N, где N — количество листов в книге. Скрытые листы считаются.
Функция без аргументов будет возвращать номер листа, на котором находится:
=ЛИСТ()
С аргументом (ссылкой) будет возвращать номер листа, на который ссылка:
=ЛИСТ(Лист2!A1)
Если лист переместить, то его номер меняется. Соответственно, можно придумать формулу с проверкой. Например, такую, которая будет сигнализировать об ошибке, если лист с оглавлением передвинуть вправо (как на видео):
=ЕСЛИ(ЛИСТ()>1; "Ошибка!Переместите лист в начало книги";"Оглавление")
--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!
Гистограммы — простой и очень полезный инструмент для визуализации.
Вашему вниманию статья про них:
— Как работают гистограммы. Как их вставлять и что лучше с ними не делать
— Меняем минимум и максимум у гистограмм
— Задаем мин/макс формулой
— Убираем числа, показывая только гистограммы
— Применяем гистограммы в сводной, в том числе только к одному уровню
https://shagabutdinov.ru/tpost/7cfjpyxsj1-gistogrammi-v-excel-prostoi-instrument-v
Вашему вниманию статья про них:
— Как работают гистограммы. Как их вставлять и что лучше с ними не делать
— Меняем минимум и максимум у гистограмм
— Задаем мин/макс формулой
— Убираем числа, показывая только гистограммы
— Применяем гистограммы в сводной, в том числе только к одному уровню
https://shagabutdinov.ru/tpost/7cfjpyxsj1-gistogrammi-v-excel-prostoi-instrument-v
Задача: посчитать стоимость (то есть перемножить цену и количество) с условием (то есть не по всем подряд строкам)
Если бы просто перемножить два столбца — цена и остатки — то все просто. Берем функцию СУММПРОИЗВ / SUMPRODUCT — она перемножает значения из нескольких массивов, а потом суммирует полученные произведения:
Но нам нужно не все подряд, а, допустим, только строки, в которых встречается определенный бренд — например, Orijen.
Тогда добавим третий аргумент (массив) в функцию. С помощью функции НАЙТИ / FIND будем определять, есть ли искомый бренд в столбце "Название". Если функция выдаст ошибку (проверим это с помощью ЕОШИБКА / ISERROR), значит, бренда нет, а нам нужно, чтобы ошибки не было — так что мы будем превращать ИСТИНА (=ошибка есть, название не найдено) в ЛОЖЬ и наоборот. Таким образом, следующая конструкция выдаст ИСТИНА там, где искомое слово найдено:
Но это будет массив из логических значений ИСТИНА и ЛОЖЬ, и мы превратим его в единицы и нули, умножив на -1 дважды:
Получится, что в нужных нам строках будут единицы, а в ненужных нули, и вся конструкция в целом вернет нам сумму произведений цены и количества только из нужных строк:
---
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!
Если бы просто перемножить два столбца — цена и остатки — то все просто. Берем функцию СУММПРОИЗВ / SUMPRODUCT — она перемножает значения из нескольких массивов, а потом суммирует полученные произведения:
=СУММПРОИЗВ(Прайс[Цена];Прайс[Остатки])
Но нам нужно не все подряд, а, допустим, только строки, в которых встречается определенный бренд — например, Orijen.
Тогда добавим третий аргумент (массив) в функцию. С помощью функции НАЙТИ / FIND будем определять, есть ли искомый бренд в столбце "Название". Если функция выдаст ошибку (проверим это с помощью ЕОШИБКА / ISERROR), значит, бренда нет, а нам нужно, чтобы ошибки не было — так что мы будем превращать ИСТИНА (=ошибка есть, название не найдено) в ЛОЖЬ и наоборот. Таким образом, следующая конструкция выдаст ИСТИНА там, где искомое слово найдено:
НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название])))Но это будет массив из логических значений ИСТИНА и ЛОЖЬ, и мы превратим его в единицы и нули, умножив на -1 дважды:
--НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название])))Получится, что в нужных нам строках будут единицы, а в ненужных нули, и вся конструкция в целом вернет нам сумму произведений цены и количества только из нужных строк:
=СУММПРОИЗВ(Прайс[Цена];Прайс[Остатки];--НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название]))))
---
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!
This media is not supported in your browser
VIEW IN TELEGRAM
Макрос: создаем по отдельному файлу для каждого продукта/города/клиента (для каждого уникального значения в столбце)
Итак, вы хотите быстро получить отдельные файлы с данными по каждому значению в том или ином столбце. Забирайте этот макрос, добавляйте его в личную книгу макросов, добавляйте кнопку на панель быстрого доступа и теперь вы можете в любом файле выбрать заголовок любой таблицы/диапазона, нажать эту кнопку и произойдет следующее:
1 В папке с вашей книгой Excel будет создана папка с заголовком ("Продукт", если у вас была активна ячейка с таким заголовком перед вызовом макроса)
2 В этой новой папке будет созданы книги для каждого значения из столбца — по одной на значение. В каждой книге будет данные только по одному этому значению (в случае с продуктом — по одной книге с данными по каждому продукту).
Как добавить макрос в личную книгу макросов, чтобы он был доступен при работе с любыми файлами Excel — читайте здесь. Сам макрос в соседнем сообщении (сохраняйте файл с макросом, заходите Alt+F11 в редактор макросов, добавляйте файл в личную книгу макросов PERSONAL.xlsb — для этого выберите Import File в контекстном меню по правой кнопке мыши)
В очень коротком видео со звуком показываю пример, как именно происходит магия.
Итак, вы хотите быстро получить отдельные файлы с данными по каждому значению в том или ином столбце. Забирайте этот макрос, добавляйте его в личную книгу макросов, добавляйте кнопку на панель быстрого доступа и теперь вы можете в любом файле выбрать заголовок любой таблицы/диапазона, нажать эту кнопку и произойдет следующее:
1 В папке с вашей книгой Excel будет создана папка с заголовком ("Продукт", если у вас была активна ячейка с таким заголовком перед вызовом макроса)
2 В этой новой папке будет созданы книги для каждого значения из столбца — по одной на значение. В каждой книге будет данные только по одному этому значению (в случае с продуктом — по одной книге с данными по каждому продукту).
Как добавить макрос в личную книгу макросов, чтобы он был доступен при работе с любыми файлами Excel — читайте здесь. Сам макрос в соседнем сообщении (сохраняйте файл с макросом, заходите Alt+F11 в редактор макросов, добавляйте файл в личную книгу макросов PERSONAL.xlsb — для этого выберите Import File в контекстном меню по правой кнопке мыши)
В очень коротком видео со звуком показываю пример, как именно происходит магия.
Подробное руководство по функции FILTER / ФИЛЬТР — вашему вниманию
— Синтаксис функции. Как задаются условия в Excel и Google Spreadsheets. И-ИЛИ в условиях
— Условия на даты, текст, фрагменты текста, флажки
— Условия с функциями (например, данные только за понедельники)
— Фильтрация по списку
— ФИЛЬТРация с СОРТировкой
— Добавляем к результату фильтрации заголовки
— Фильтруем не все столбцы
— Фильтруем горизонтальные диапазоны
— FILTER в качестве аргументов других функций
🔗Google Таблица с примерами из статьи
📗Книга Excel с примерами из статьи
https://shagabutdinov.ru/blog/tpost/ko1p8i5rt1-funktsiya-filter-v-google-spreadsheets-i
_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥
— Синтаксис функции. Как задаются условия в Excel и Google Spreadsheets. И-ИЛИ в условиях
— Условия на даты, текст, фрагменты текста, флажки
— Условия с функциями (например, данные только за понедельники)
— Фильтрация по списку
— ФИЛЬТРация с СОРТировкой
— Добавляем к результату фильтрации заголовки
— Фильтруем не все столбцы
— Фильтруем горизонтальные диапазоны
— FILTER в качестве аргументов других функций
🔗Google Таблица с примерами из статьи
📗Книга Excel с примерами из статьи
https://shagabutdinov.ru/blog/tpost/ko1p8i5rt1-funktsiya-filter-v-google-spreadsheets-i
_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥
Как избежать вставки ссылок в диалоговых окнах
Вот редактируете вы какую-то формулу или диапазон в окне условного форматирования или в диспетчере имен Excel.
И нажимаете стрелку влево или вправо на клавиатуре, чтобы... переместить курсор.
И в этот момент Excel вставляет ссылки на ячейки. А-а-а-а-а!
Как от этой гадости избавиться? Нажать F2.
И тогда стрелки будут перемещать курсор. При вводе формул в ячейках это тоже работает.
Опознать режим можно по надписи в левом нижнем углу (в строке состояния) — если там "Правка" (Edit), то можно смело нажимать на стрелки :)
Вот редактируете вы какую-то формулу или диапазон в окне условного форматирования или в диспетчере имен Excel.
И нажимаете стрелку влево или вправо на клавиатуре, чтобы... переместить курсор.
И в этот момент Excel вставляет ссылки на ячейки. А-а-а-а-а!
Как от этой гадости избавиться? Нажать F2.
И тогда стрелки будут перемещать курсор. При вводе формул в ячейках это тоже работает.
Опознать режим можно по надписи в левом нижнем углу (в строке состояния) — если там "Правка" (Edit), то можно смело нажимать на стрелки :)
Задача: генерируем коды вида «АБВ-00001» для переноса в Word и печати наклеек
Вашему вниманию новое видео на Sponsr.ru — оно бесплатное и открыто для всех, а не только для подписчиков. Это разбор небольшой задачки: как генерировать коды, в которых есть текстовая часть и идущие подряд числа.
Решение — и банальными и простыми формулами и новыми формулами версий 2021-2024 для задаваемого числа столбцов и строк. Попутно применяем пользовательский формат и функцию ТЕКСТ / TEXT.
Присылайте свои задачи в личные сообщения или по почте [email protected]. Без личных/коммерческих данных. Можно заменить на несколько строк со случайными. Если будет интересная задача — разберем ее в таком формате!
Вашему вниманию новое видео на Sponsr.ru — оно бесплатное и открыто для всех, а не только для подписчиков. Это разбор небольшой задачки: как генерировать коды, в которых есть текстовая часть и идущие подряд числа.
Решение — и банальными и простыми формулами и новыми формулами версий 2021-2024 для задаваемого числа столбцов и строк. Попутно применяем пользовательский формат и функцию ТЕКСТ / TEXT.
Присылайте свои задачи в личные сообщения или по почте [email protected]. Без личных/коммерческих данных. Можно заменить на несколько строк со случайными. Если будет интересная задача — разберем ее в таком формате!
Sponsr
Задача: генерируем коды вида «АБВ-00001» для переноса в Word и печати наклеек | Магия табличных формул. От A1 до LAMBDA
Видеоуроки по формулам Excel (и не только): все нюансы и правила для новичков, новые функции, файлы с данными для практики и готовыми примерами
Сводные таблицы Excel: 10 приемов
10 сводно-табличных заклинаний под одной (виртуальной) обложкой — с видео или скриншотами:
— Удаление источника данных сводной
— Чередование строк в сводной
— Превращаем сводную в формулы
— Число уникальных элементов
— Группировка дат: анализируем сезонность
— И другое!
https://shagabutdinov.ru/tpost/mry5t8o211-svodnie-tablitsi-excel-10-priemov
10 сводно-табличных заклинаний под одной (виртуальной) обложкой — с видео или скриншотами:
— Удаление источника данных сводной
— Чередование строк в сводной
— Превращаем сводную в формулы
— Число уникальных элементов
— Группировка дат: анализируем сезонность
— И другое!
https://shagabutdinov.ru/tpost/mry5t8o211-svodnie-tablitsi-excel-10-priemov
Forwarded from Ренат Шагабутдинов из МИФа
Please open Telegram to view this post
VIEW IN TELEGRAM
Рассылка "Магия таблиц"
Совсем скоро подписчикам придет восьмой выпуск рассылки! Там будут новости (в частности, в Office в целом и в Excel подвезли новый режим 🌓), несколько табличных лайфхаков и всякое жизненное (про путешествия и бег).
Подписаться можно тут:
✉️https://shagabutdinov.ru/#subscription
А пока — вот предыдущие выпуски:
Первый. Новости и немного про графической слой Excel и про срезы — один из типов объектов, живущих на нем.
Второй. про ссылки на умные таблицы в Google Spreadsheets, линейчатую диаграмму для визуализации план-факта или чего-то подобного и немного личного — про путешествие на край света🥝.
Третий. Макрос для создания Word’овских документов по шаблону и лайфхаки для навигации по листам Excel. А также книжные итоги года.
Четвертый. Пара новостей об изменениях в Excel, секретный секрет про очень скрытые листы и пара слов про поездку в Оман.
Пятый. про новую функцию УРЕЗДИАПАЗОН, старую функцию ИНДЕКС (но про ее применение, которое может стать новостью для многих из вас) и про парочку нетабличных статей
Шестой. пачка лайфхаков из новой (для меня и, думаю, для вас, но не для автора 😊) книги Билла Джелена, про запуск нового формата — обучение по подписке и немного стоицизма
Седьмой. Про новые видео по табличным формулам, диаграммно-гистограмные приемы и про крутую книгу о силе оптимизма 😊
Совсем скоро подписчикам придет восьмой выпуск рассылки! Там будут новости (в частности, в Office в целом и в Excel подвезли новый режим 🌓), несколько табличных лайфхаков и всякое жизненное (про путешествия и бег).
Подписаться можно тут:
✉️https://shagabutdinov.ru/#subscription
А пока — вот предыдущие выпуски:
Первый. Новости и немного про графической слой Excel и про срезы — один из типов объектов, живущих на нем.
Второй. про ссылки на умные таблицы в Google Spreadsheets, линейчатую диаграмму для визуализации план-факта или чего-то подобного и немного личного — про путешествие на край света🥝.
Третий. Макрос для создания Word’овских документов по шаблону и лайфхаки для навигации по листам Excel. А также книжные итоги года.
Четвертый. Пара новостей об изменениях в Excel, секретный секрет про очень скрытые листы и пара слов про поездку в Оман.
Пятый. про новую функцию УРЕЗДИАПАЗОН, старую функцию ИНДЕКС (но про ее применение, которое может стать новостью для многих из вас) и про парочку нетабличных статей
Шестой. пачка лайфхаков из новой (для меня и, думаю, для вас, но не для автора 😊) книги Билла Джелена, про запуск нового формата — обучение по подписке и немного стоицизма
Седьмой. Про новые видео по табличным формулам, диаграммно-гистограмные приемы и про крутую книгу о силе оптимизма 😊
shagabutdinov.ru
Ренат Шагабутдинов | Консультирование и обучение по работе в Excel и Google Таблицах
Корпоративное и индивидуальное обучение по работе в Excel и Google Таблицах, полезные материалы, видеоуроки, статьи.
B2:ИНДЕКС(...) — ссылка на диапазон динамических размеров
Функция ИНДЕКС / INDEX весьма разносторонне развита. Умеет она в числе прочего возвращать ссылку на ячейку вместо ее содержимого. Если поставить ее после двоеточия. Например, так:
В примере первая ячейка диапазона для расчета среднего — это B2 (то есть январь в каждом столбце), а последняя возвращается ИНДЕКСом — исходя из числа в ячейке A16.
Теперь можно менять число в ячейке A16 и получать обновленный результат.
Почему не СМЕЩ / OFFSET, которая тоже может возвращать диапазон переменного размера? Ее тоже можно использовать в таких ситуациях. Но учитывайте, что она волатильная, в отличие от ИНДЕКСа. То есть пересчитывается при любом изменении в книге, а не при изменении ячеек, которые на нее влияют.
Функция ИНДЕКС / INDEX весьма разносторонне развита. Умеет она в числе прочего возвращать ссылку на ячейку вместо ее содержимого. Если поставить ее после двоеточия. Например, так:
A1:ИНДЕКС(...)
В примере первая ячейка диапазона для расчета среднего — это B2 (то есть январь в каждом столбце), а последняя возвращается ИНДЕКСом — исходя из числа в ячейке A16.
=СРЗНАЧ(B2:ИНДЕКС(B2:B12;$A$16))
Теперь можно менять число в ячейке A16 и получать обновленный результат.
Почему не СМЕЩ / OFFSET, которая тоже может возвращать диапазон переменного размера? Ее тоже можно использовать в таких ситуациях. Но учитывайте, что она волатильная, в отличие от ИНДЕКСа. То есть пересчитывается при любом изменении в книге, а не при изменении ячеек, которые на нее влияют.