Магия Excel

Сводные таблицы Excel: 10 приемов 10 сводно-табличных заклинаний под одной (виртуальной) обложкой — с видео или скриншотами: — Удаление источника данных сводной — Чередование строк в сводной — Превращаем сводную в формулы — Число уникальных элементов — Группировка дат: анализируем сезонность — И другое! https://shagabutdinov.ru/tpost/mry5t8o211-svodnie-tablitsi-excel-10-priemov

Задача: генерируем коды вида «АБВ-00001» для переноса в Word и печати наклеек Вашему вниманию новое видео на Sponsr.ru — оно бесплатное и открыто для всех, а не только для подписчиков. Это разбор небольшой задачки: как генерировать коды, в которых есть текстовая часть и идущие подряд числа. Решение — и банальными и простыми формулами и новыми формулами версий 2021-2024 для задаваемого числа столбцов и строк. Попутно применяем пользовательский формат и функцию ТЕКСТ / TEXT. Присылайте свои задачи в личные сообщения или по почте [email protected]. Без личных/коммерческих данных. Можно заменить на несколько строк со случайными. Если будет интересная задача — разберем ее в таком формате!

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

Подробное руководство по функции FILTER / ФИЛЬТР — вашему вниманию — Синтаксис функции. Как задаются условия в Excel и Google Spreadsheets. И-ИЛИ в условиях — Условия на даты, текст, фрагменты текста, флажки — Условия с функциями (например, данные только за понедельники) — Фильтрация по списку — ФИЛЬТРация с СОРТировкой — Добавляем к результату фильтрации заголовки — Фильтруем не все столбцы — Фильтруем горизонтальные диапазоны — FILTER в качестве аргументов других функций 🔗Google Таблица с примерами из статьи 📗Книга Excel с примерами из статьи https://shagabutdinov.ru/blog/tpost/ko1p8i5rt1-funktsiya-filter-v-google-spreadsheets-i _ _ _ Мини-курс "Магия новых функций Excel. Революция в табличных формулах"🔥

Макрос: создаем по отдельному файлу для каждого продукта/города/клиента (для каждого уникального значения в столбце) Итак, вы хотите быстро получить отдельные файлы с данными по каждому значению в том или ином столбце. Забирайте этот макрос, добавляйте его в личную книгу макросов, добавляйте кнопку на панель быстрого доступа и теперь вы можете в любом файле выбрать заголовок любой таблицы/диапазона, нажать эту кнопку и произойдет следующее: 1 В папке с вашей книгой Excel будет создана папка с заголовком ("Продукт", если у вас была активна ячейка с таким заголовком перед вызовом макроса) 2 В этой новой папке будет созданы книги для каждого значения из столбца — по одной на значение. В каждой книге будет данные только по одному этому значению (в случае с продуктом — по одной книге с данными по каждому продукту). Как добавить макрос в личную книгу макросов, чтобы он был доступен при работе с любыми файлами Excel — читайте здесь. Сам макрос в соседнем сообщении (сохраняйте файл с макросом, заходите Alt+F11 в редактор макросов, добавляйте файл в личную книгу макросов PERSONAL.xlsb — для этого выберите Import File в контекстном меню по правой кнопке мыши) В очень коротком видео со звуком показываю пример, как именно происходит магия.

Код макроса

Задача: посчитать стоимость (то есть перемножить цену и количество) с условием (то есть не по всем подряд строкам) Если бы просто перемножить два столбца — цена и остатки — то все просто. Берем функцию СУММПРОИЗВ / SUMPRODUCT — она перемножает значения из нескольких массивов, а потом суммирует полученные произведения: =СУММПРОИЗВ(Прайс[Цена];Прайс[Остатки]) Но нам нужно не все подряд, а, допустим, только строки, в которых встречается определенный бренд — например, Orijen. Тогда добавим третий аргумент (массив) в функцию. С помощью функции НАЙТИ / FIND будем определять, есть ли искомый бренд в столбце "Название". Если функция выдаст ошибку (проверим это с помощью ЕОШИБКА / ISERROR), значит, бренда нет, а нам нужно, чтобы ошибки не было — так что мы будем превращать ИСТИНА (=ошибка есть, название не найдено) в ЛОЖЬ и наоборот. Таким образом, следующая конструкция выдаст ИСТИНА там, где искомое слово найдено: НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название]))) Но это будет массив из логических значений ИСТИНА и ЛОЖЬ, и мы превратим его в единицы и нули, умножив на -1 дважды: --НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название]))) Получится, что в нужных нам строках будут единицы, а в ненужных нули, и вся конструкция в целом вернет нам сумму произведений цены и количества только из нужных строк: =СУММПРОИЗВ(Прайс[Цена];Прайс[Остатки];--НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название])))) --- 💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!

Гистограммы — простой и очень полезный инструмент для визуализации. Вашему вниманию статья про них: — Как работают гистограммы. Как их вставлять и что лучше с ними не делать — Меняем минимум и максимум у гистограмм — Задаем мин/макс формулой — Убираем числа, показывая только гистограммы — Применяем гистограммы в сводной, в том числе только к одному уровню https://shagabutdinov.ru/tpost/7cfjpyxsj1-gistogrammi-v-excel-prostoi-instrument-v

Функция ЛИСТ / SHEET Возвращает она порядковый номер (индекс) листа. И этот номер может меняться. Он зависит от положения листа — они нумеруются от 1 до N, где N — количество листов в книге. Скрытые листы считаются. Функция без аргументов будет возвращать номер листа, на котором находится: =ЛИСТ() С аргументом (ссылкой) будет возвращать номер листа, на который ссылка: =ЛИСТ(Лист2!A1) Если лист переместить, то его номер меняется. Соответственно, можно придумать формулу с проверкой. Например, такую, которая будет сигнализировать об ошибке, если лист с оглавлением передвинуть вправо (как на видео): =ЕСЛИ(ЛИСТ()>1; "Ошибка!Переместите лист в начало книги";"Оглавление") -- 💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!

Вывести все имена и соответствующие диапазоны на лист Вот как можно сформировать табличку (диапазон) со списком всех или некоторых имен и их диапазонов: Вкладка "Формулы" — Определенные имена — Использовать в формуле — Вставить имена Formulas — Defined Names — Use in Formula — Paste Names А зачем может пригодиться? Если вы применили имя в формуле, а потом удалили это имя (это можно сделать в диспетчере имен, Ctrl + F3), формула будет возвращать ошибку, хотя само имя в формуле останется. То есть если у вас было =Выручка*Налог, то так оно и останется, "Налог" не будет заменен на ту ячейку, которая названа этим именем. А если вывести куда-то список имен, то сможете посмотреть, какой диапазон каким именем был назван. Можно ли отключить такое поведение? Чтобы при удалении имени оно исчезало из формулы, заменялось на диапазон? Можно. Для конкретного листа: Файл — Параметры — Дополнительно — Параметры совместимости с Lotus 1-2-3 — Преобразовывать формулы в формат Excel при вводе -- 💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!

Переключение дэшборда между днями и неделями — с помощью функции SEQUENCE Итак, вы хотите создать простой дэшборд, в котором будете агрегировать данные по неделям или дням. И при этом хотите легко переключать режим «недели / дни» (или изменение любого другого параметра), не залезая в формулы. Статья и пример в Google Таблицах. Но в новом Excel такое тоже можно реализовать — и флажки, и функция SEQUENCE / ПОСЛЕД в наличии! https://shagabutdinov.ru/blog/tpost/jbrryezom1-pereklyuchenie-deshborda-mezhdu-dnyami-i -- 💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!

Ставили цели на этот год? Обратите внимание, что 13% года уже прошли 😈 Как это можно вычислить и визуализировать? Используем функцию ДОЛЯГОДА / YEARFRAC. У нее два обязательных аргумента — две даты. Если нужна универсальная формула, можно вычислять первую дату текущего года и текущую дату — такая формула всегда будет возвращать долю прошедших в текущем году дней. =ДОЛЯГОДА(ДАТА(ГОД(СЕГОДНЯ());1;1);СЕГОДНЯ()) Самая лаконичная визуализация прогресса — гистограмма условного форматирования. Просто копируем формулу во вторую ячейку (или ссылаемся на эту ячейку), вставляем гистограмму (Главная — Условное форматирование — Гистограммы), меняем минимум и максимум на ноль и единицу. Можно добавить заливку ячейки другим цветом, как в примере. -- 💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!