Макрос: создаем по отдельному файлу для каждого продукта/города/клиента (для каждого уникального значения в столбце)

Итак, вы хотите быстро получить отдельные файлы с данными по каждому значению в том или ином столбце. Забирайте этот макрос, добавляйте его в личную книгу макросов, добавляйте кнопку на панель быстрого доступа и теперь вы можете в любом файле выбрать заголовок любой таблицы/диапазона, нажать эту кнопку и произойдет следующее:

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.
И нажимаете стрелку влево или вправо на клавиатуре, чтобы... переместить курсор.
И в этот момент Excel вставляет ссылки на ячейки. А-а-а-а-а!

Как от этой гадости избавиться? Нажать F2.
И тогда стрелки будут перемещать курсор. При вводе формул в ячейках это тоже работает.

Опознать режим можно по надписи в левом нижнем углу (в строке состояния) — если там "Правка" (Edit), то можно смело нажимать на стрелки :)

Задача: генерируем коды вида «АБВ-00001» для переноса в Word и печати наклеек

Вашему вниманию новое видео на Sponsr.ru — оно бесплатное и открыто для всех, а не только для подписчиков. Это разбор небольшой задачки: как генерировать коды, в которых есть текстовая часть и идущие подряд числа.

Решение — и банальными и простыми формулами и новыми формулами версий 2021-2024 для задаваемого числа столбцов и строк. Попутно применяем пользовательский формат и функцию ТЕКСТ / TEXT.

Присылайте свои задачи в личные сообщения или по почте renat@shagabutdinov.ru. Без личных/коммерческих данных. Можно заменить на несколько строк со случайными. Если будет интересная задача — разберем ее в таком формате!

Сводные таблицы Excel: 10 приемов

10 сводно-табличных заклинаний под одной (виртуальной) обложкой — с видео или скриншотами:
— Удаление источника данных сводной
— Чередование строк в сводной
— Превращаем сводную в формулы
— Число уникальных элементов
— Группировка дат: анализируем сезонность
— И другое!

https://shagabutdinov.ru/tpost/mry5t8o211-svodnie-tablitsi-excel-10-priemov

Провожу серию вебинаров для крупной компании (ретейл, электроника) по Excel

И вот после последнего на данный момент вебинара прислали обратную связь с такими показателями!

А вот несколько отзывов сотрудников текстом:

Спасибо большое спикеру за интересную и доходчивую подачу материала.

Спасибо за умение доступно донести материал.

Спасибо большое за мастер-класс, было познавательно и полезно! Спасибо за понятное изложение материала

Мастер-класс шикарный и для новичков и для тех, кто подзабыл, и тех, кто уже многое знает!

Хотите организовать вебинар по Excel в своей компании — присылайте телеграмму (@r_shagabutdinov) или письмо: renat@shagabutdinov.ru!

Рассылка "Магия таблиц"

Совсем скоро подписчикам придет восьмой выпуск рассылки! Там будут новости (в частности, в Office в целом и в Excel подвезли новый режим 🌓), несколько табличных лайфхаков и всякое жизненное (про путешествия и бег).

Подписаться можно тут:
✉️https://shagabutdinov.ru/#subnoscription

А пока — вот предыдущие выпуски:
Первый. Новости и немного про графической слой Excel и про срезы — один из типов объектов, живущих на нем.

Второй. про ссылки на умные таблицы в Google Spreadsheets, линейчатую диаграмму для визуализации план-факта или чего-то подобного и немного личного — про путешествие на край света🥝.

Третий. Макрос для создания Word’овских документов по шаблону и лайфхаки для навигации по листам Excel. А также книжные итоги года.

Четвертый. Пара новостей об изменениях в Excel, секретный секрет про очень скрытые листы и пара слов про поездку в Оман.

Пятый. про новую функцию УРЕЗДИАПАЗОН, старую функцию ИНДЕКС (но про ее применение, которое может стать новостью для многих из вас) и про парочку нетабличных статей

Шестой. пачка лайфхаков из новой (для меня и, думаю, для вас, но не для автора 😊) книги Билла Джелена, про запуск нового формата — обучение по подписке и немного стоицизма

Седьмой. Про новые видео по табличным формулам, диаграммно-гистограмные приемы и про крутую книгу о силе оптимизма 😊

B2:ИНДЕКС(...) — ссылка на диапазон динамических размеров

Функция ИНДЕКС / INDEX весьма разносторонне развита. Умеет она в числе прочего возвращать ссылку на ячейку вместо ее содержимого. Если поставить ее после двоеточия. Например, так:

A1:ИНДЕКС(...)

В примере первая ячейка диапазона для расчета среднего — это B2 (то есть январь в каждом столбце), а последняя возвращается ИНДЕКСом — исходя из числа в ячейке A16.

=СРЗНАЧ(B2:ИНДЕКС(B2:B12;$A$16))

Теперь можно менять число в ячейке A16 и получать обновленный результат.

Почему не СМЕЩ / OFFSET, которая тоже может возвращать диапазон переменного размера? Ее тоже можно использовать в таких ситуациях. Но учитывайте, что она волатильная, в отличие от ИНДЕКСа. То есть пересчитывается при любом изменении в книге, а не при изменении ячеек, которые на нее влияют.

Ух какой отзыв на Озоне пришел на "Магию таблиц", не могу не поделиться! Сама книга почти закончилась, готовится третий тираж, но еще кое-где есть в наличии.

И на Озоне, и на WB число оценок перевалило за 200 со средней оценкой 5 и 4.9 соответственно🔥Спасибо всем читателям!

Эту книгу я бы подарил себе самому пятнадцать лет назад, если бы мог хлопнуть дверцей "Делориана" и метнуться в 2010-й на пару минут.
Работаю в EXCEL ежедневно и привык думать, что "знаю и умею" быстро и правильно.
В мою жизнь ворвались сводные таблицы, подсветив неожиданную истину: я НЕ умею работать в EXCEL.
Всегда кто-то и что-то подсказывал из-за плеча.
Так неправильно.
Пришло время вгрызться в базовые знания, поданные "от простого к сложному", системно, с иллюстрациями и примерами.
Я не "технарь", а человек "за компьютером". EXCEL и табличное мышление неизбежны.
Если вы как покупатель этой книги спросили бы моего совета по работе с книгой, я бы ответил:
Это - учебник, а не книга для послеобеденного чтения.
Закладывайте по два часа в день на эту книгу, имейте план усвоения материалов книги и открывайте эту книгу, только сидя перед компьютером с рабочим файлом EXCEL.
Сразу заводите EXCEL-конспект пройденных материалов.

Не бойтесь "нудятины". По зёрнышку. По шагу, по шажочку.
Не нужно изучать "впрок". Просто накапливайте базовые знания, лексику и EXCEL улыбнётся вам. Как и возможности быстрой работы с данными, их вариациями подачи, чтения, интерпретации.
Говорю это как стопроцентный "гуманитарий", которому жизнь и работа всё уже объяснили в деталях.
Если EXCEL неизбежен для вас, то эта книга - отличный старт.

Видеоурок: Функции для разделения текста: TEXTSPLIT и другие

Друзья, хочу поделиться с вами одним из уроков своего курса "Магия новых функций Excel" — про TEXTSPLIT и другие функции для разделения текстовых строк и извлечения фрагментов.

Полезного просмотра!

Весь курс можно найти здесь:
https://shagabutdinov.ru/magic-excel

Диаграммы в Excel: 11 приемов

— Быстрая вставка диаграммы
— 4 способа добавления новых данных
— Фильтрация данных на диаграмме в разных версиях Excel
— Изображения вместо столбиков
— Выделяем отдельные точки данных
— Выравниваем диаграммы
— Серая тема для черно-белой печати диаграмм
— Фоновое выделение периода на диаграмме
— И другие приемы!

Функция LET

В ситуациях, когда в формуле приходится использовать какой-то промежуточный результат много раз, пригодится новая функция LET. Она есть в Excel 2021/2024/365 и Google Таблицах.

Синтаксис функции: несколько пар аргументов, в которых вы задаете в первом аргументе переменную, а во втором — выражение для нее. И затем вычисление с использованием этих переменных.

Что она дает:
— легче воспринимать формулу (одно и то же выражение не повторяется много раз целиком)
— скорость
— экзотика: можно использовать, чтобы добавлять комментарии к формуле 😸

https://shagabutdinov.ru/tpost/47brblc2e1-novaya-funktsiya-excel-i-google-tablits

Видео. Извлекаем элементы из даты: месяц и год, номер недели, день недели номером и текстом, квартал

Друзья, привет! Вашему вниманию новое видео моего курса по подписке "Магия табличных формул".

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

Это видео открыто и бесплатно для всех — смотрите по ссылке.

Подписаться можно по ссылке — каждую неделю минимум одно новое видео, доступны и все старые. С файлами-примерами.

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

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

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

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

Старая и иногда добрая функция ПРОСМОТР / LOOKUP

Не ПРОСМОТРX / XLOOKUP, которая появилась в Excel 2021 и Google Таблицах. А функция без икса, которая есть во всех версиях. Для базовой задачи по поиску текстового значения в другой таблице подходит плохо, так как ее алгоритм поиска предполагает сортировку исходного диапазона по алфавиту, что сложно поддерживать (так что в старых версиях лучше ВПР / VLOOKUP с последним аргументом, равным нулю; а в новых и Google — ПРОСМОТРX)

Но зато с ПРОСМОТРом можно некоторые фокусы вытворять: например, находить последнее значение в столбце или вести нечеткий текстовый поиск (находить определенное слово в ячейке, а не точное соответствие)

Эти приемы и общая информация про функцию в статье:
https://shagabutdinov.ru/excel-lookup/

--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!

ОченьСкрытый Лист

Листы в Excel бывают видимыми (таким должен быть как минимум один лист в книге), скрытыми и очень скрытыми.

Просто скрыть можно в Excel (правая кнопка по ярлыку — скрыть)

А ОченьСкрыть — только в редакторе VBA.
Нажимаем Alt+F11, выбираем лист в Project Explorer, меняем свойство Visible на xlSheetVeryHidden

Теперь лист нельзя будет увидеть и показать в интерфейсе Excel. Но пользователь, знающий о таком свойстве, сможет его изменить в VBA. Еще такой лист будет показан, если пользователь запустит макрос, показывающий все скрытые листы.

И при подключении к файлу в Power Query будут видны все листы — и скрытые, и очень скрытые.
_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥

Найти и заменить: меняем форматы, а не значения

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

И вам нужно их все переформатировать по другому образцу. Допустим, без полужирного начертания.

Вызываем окно "Найти и заменить" — Ctrl + H

Выбираем справа Формат — Выбрать формат из ячейки
Напротив поля "Найти" выбираем образец, какие ячейки будем менять
А напротив "Заменить на" — выбираем образец, как они должны выглядеть

Нажимаем "Заменить все". Готово!

--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!

Перемещаем столбец

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

Ну а если надо переместить столбец, зажимаем клавишу Shift, тащим — и он просто перемещается. Уже без предупреждений :)

Тренинг "Табличные формулы для продолжающих" 45801 24 мая-25 мая

Вот в таком компьютерном классе пройдет интенсив по формулам ! Камерная обстановка и насыщенная интересная практика.

5 мест уже забронировано, так что осталось только 5 входных билетов с книгой "Магия таблиц" в подарок. Сегодня последний день, когда можно забронировать участие со скидкой 3 000 рублей.

Присоединяйтесь! Если есть любые вопросы и сомнения - пишите:
renat@shagabutdinov.ru

Оставить заявку можно по этому же адресу или на странице тренинга:
https://shagabutdinov.ru/formulas-offline

Там же программа.
В любом случае я немного поспрашиваю вас по поводу рабочих задач и вашего уровня перед бронированием места, чтобы понять, подойдет ли вам обучение.