Магия Excel

Задачка: отфильтровать данные по списку товаров. Слева большая таблица (допустим, несколько тысяч или сотен тысяч строк - не столь важно). Справа - список товаров, по которым мы хотим ее отфильтровать, то есть получить выборку только с остатками этих товаров в разных городах и на разных складах. Как бы вы решили эту задачу?

Скидка на курс "Магия Excel" и видеоурок про объединение и разделение текстовых строк В МИФе идет черная пятница со скидками на книги и курсы, и мы с Лемуром решили не стоять в сторонке - попросили у коллег скидку на наш курс. Ловите промокод на 40% до конца декабря: LEMUR Сам курс тут. Уроки по 10-20 минут, с пометками, стрелочками, масштабированием и всем, что нужно для лучшего восприятия. Все функции и команды и на русском, и на английском. Все ключевые темы и функции внутри. https://www.mann-ivanov-ferber.ru/courses/magicexcel/ Посмотрите один из 55 уроков "Объединяем и разделяем текст", чтобы лучше понять, что внутри (и задавайте любые вопросы в комментариях): https://www.youtube.com/watch?v=YwCO1EcIKO0

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

А вот и сам макрос — код ниже, но проще будет загрузить прикрепленный файл с модулем и его импортировать, как в видео. Sub Оглавление() Dim Contents As Worksheet Set Contents = ActiveWorkbook.Worksheets.Add(before:=ActiveWorkbook.Worksheets(1)) Contents.Range("A1") = "Оглавление" Contents.Name = "Оглавление" For i = 2 To ActiveWorkbook.Worksheets.Count Contents.Hyperlinks.Add Anchor:=Cells(i, 1), Address:="", SubAddress:="'" & ActiveWorkbook.Worksheets(i).Name & "'!A1", TextToDisplay:=ActiveWorkbook.Worksheets(i).Name Next i Contents.Columns(1).EntireColumn.AutoFit End Sub

Создаем оглавление на отдельном листе с помощью гиперссылок Ранее мы писали про окно "Переход к листу" — удобно для навигации. Но было ценное замечание от читателя: там нет поиска. Как можно сделать навигацию еще более удобной в случае большого количества листов: создать оглавление с ссылками на каждый лист. Вставить ссылку на место в документе можно с помощью сочетания Ctrl + K (как и в других приложениях, например, здесь в Телеграме). Далее выбираем слева "Связать с" — "Место в документе". Вводим отображаемый в ячейке текст ссылки и адрес ячейки, внизу в списке выбираем лист. И получаем ссылку для перехода на соответствующее место в документе. Но если листов много, вручную проделывать это для каждого листа будет мучительно, поэтому нам пригодится макрос, который формирует оглавление на отдельном листе автоматически.

Автосумма: одним движением суммы по всем столбцам/месяцам. Сочетание клавиш Alt + = позволяет получить сумму быстро, не вводя руками функцию СУММ / SUM. Если выделить ячейку под столбцом с числами и нажать Alt + =, то получим сумму по этому столбцу (одну функцию СУММ). Уточняем: речь про "просто Alt", то есть левый Alt. Правый Alt заменяет сочетание Ctrl+Alt и в сочетании с плюсом-минусом будет менять масштаб листа. А если - как в видео - выделить диапазон из нескольких столбцов и строк вместе с пустой строкой под ним и столбцом справа, то мы получим суммы по каждому столбцу и строке (и итоговую справа внизу).

Навигация по листам в книге Excel В книге много листов? Щелкните правой кнопкой мыши на стрелки в левом нижнем углу. Откроется список всех листов. Там смотреть удобнее, чем просто по ярлыкам. А к следующему и предыдущему листу можно переходить с помощью сочетаний клавиш Ctrl + PgDn и Ctrl+PgUp.

А это книга Excel с примерами формул для получения месяца из даты. Те же варианты с учетом специфики Excel - порядковый номер с нулем и без, короткое и длинное текстовое название и ваши (любые) варианты названий.

У вас есть дата, а вы хотите номера/названия месяцев в отдельном столбце (допустим, для сводной, для отчетов, для фильтрации) - 7 вариантов Самый простой вариант - функция MONTH / МЕСЯЦ. Это число, порядковый номер (3 для марта, 11 для ноября). С помощью функции TEXT / ТЕКСТ можно получить также вариант с нулем для коротких номеров (03 для марта, но 11 для ноября). С помощью нее же - текстовые варианты (мар. и марта для российских региональных настроек). Если "марта" вам не нравится и вы хотите использовать абсолютно любые варианты, то можно брать их из диапазона с помощью ИНДЕКСа. Или из виртуального массива внутри формулы с помощью того же ИНДЕКСа или ВПР или ВЫБОРа. По ссылке - семь с половиной вариантов, включая формулу массива, выдающую ваши названия месяцев для всего столбца (без вспомогательного диапазона; названия внутри формулы). Таблица с примерами формул

Удаляем пустые строки Выделяем диапазон, в котором нужно удалить пустые ячейки (в видео - все данные на листе с помощью Ctrl+Shift+End). Для этого нужен инструмент "Найти и выделить" (на ленте на вкладке "Главная") - выбираем там "Выделить группу ячеек" и в появившемся диалоговом окне - "Пустые ячейки". После этого остается нажать Ctrl + - (Ctrl и минус) - это удаление ячеек/строк/столбцов. И выбрать "строку". P.S. Если у вас пустые ячейки только в одном столбце, и нужно удалить строки с такими ячейками - выделите один столбец, а не всю таблицу, а далее алгоритм такой же.

Есть клавиши, которые в Excel (да и не только) выполняют одну и ту же задачу в разном контексте. За счет этого их проще запоминать. Shift позволяет выделять сразу несколько объектов/символов от активного до того, на который щелкнете. Это работает во многих ситуациях: - При выделении диапазонов - щелкните на любую ячейку с зажатой Shift и выделится весь диапазон от активной до той, на которую щелкнули. - Группируем листы в книге Excel - с Ctrl можно выделять по одному, а вот с Shift'ом - сразу от текущего до любого (нужно кликнуть на последний группируемый лист с зажатой клавишей Shift) - В срезах можно выделять сразу несколько элементов с Shift'ом - Фрагмент формулы при ее редактировании - И не только в Excel - в текстовых редакторах и браузере можно выделять текст, в Проводнике - файлы и папки

Срезы - удобные и наглядные фильтры, которые находятся на графическом слое листа Excel (то есть "плавают" поверх ячеек), появились в Excel 2010 и доступны как в "Таблицах" (Tables, их еще называют "умными таблицами"), так и в сводных таблицах (Pivot Tables). Разбираем в небольшой статье, как их вставлять и как привязать срез сразу к нескольким сводным таблицам! https://teletype.in/@renat_shagabutdinov/excel_slicer