Код макроса для удаления пустых листов

Макрос: удаляем пустые листы

На примере простой задачки (удалить все листы в текущей книге, на которых нет ни одного значения в ячейках) рассматриваем, как создать макрос в личной книге макросов и затем добавить на панель быстрого доступа, чтобы он всегда был под рукой.

Код макроса — выше в файле формата .bas — для импорта просто зайдите в редактор VBA (Alt + F11) и нажмите там Ctrl + M для импорта.

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

Друзья, наша с Лемуром книга тем временем стала №1 в жанре "Руководства по пользованию программами" в Лабиринте — в очень достойной компании😺 (особое внимание в этом топе обращаем на "Библию пользователя", хоть она и по 2019 Excel, книгу по визуализации Дика Куслейки и, конечно, книгу про сводные Билла Джелена — очень полезные вещи — про эти и другие книги можете почитать в обзоре)

Если уже прочитали, пожалуйста, поставьте оценку / оставьте отзыв в любом из магазинов, мы будем очень благодарны!

А купить можно тут:
На сайте издательства
Book24
Лабиринт
Озон
Литрес (электрическая)
Wildberries
И в оффлайне — например, в Библио-Глобусе или в регионах в Читай-городе, Буквоеде и других магазинах

Калькулятор в Excel... Ну, вообще-то строка формул (точнее, сами формулы, в любой ячейке) — вполне себе калькулятор, но если вы знаете толк в извращениях, вам нужны кнопочки M+, M-, CE и вы хотите вызывать калькулятор WIndows из Excel — почему нет 😺
(шутки шутками, но кнопок там намного больше, потому что есть разные режимы — инженерный, графики, вычисления с датами, преобразования)

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

Options — Quick Access Toolbar — All Commands — Calculator — Add.

Извлекаем из таблицы строки с самой большой и маленькой сделкой (наименьшим и наибольшим числом)

С новыми функциями это получается просто: сначала сортируем таблицу по сделкам (по аналогии можно сортировать по датам, тогда вы сможете взять самую старую и новую строки) с помощью SORT / СОРТ:

=СОРТ(таблица; номер столбца, по которому сортируем)

Если нужно по убыванию, то задаем третий аргумент, равный -1.

Ну а далее, не выводя на лист отсортированный результат, сразу отправляем его внутрь функции CHOOSEROWS / ВЫБОРСТРОК — и берем первую (1) и последнюю (-1) строки.
=ВЫБОРСТРОК(СОРТ(таблица; номер столбца для сортировки);1;-1)

В общем виде ВЫБОРСТРОК имеет такой синтаксис:
=ВЫБОРСТРОК(диапазон / массив; номер строки, которую извлекаем ; [еще номер строки]; ...)

То есть можем извлечь и одну строку, и несколько — перечисляем столько номеров, сколько нужно.
А если нужны все нечетные строки, например, с 1 по 100, можно использовать функцию ПОСЛЕД / SEQUENCE, чтобы не вводить столько чисел вручную:
=ВЫБОРСТРОК(диапазон; ПОСЛЕД(50;;1;2))

Мгновенное заполнение — один из самых простых и полезных инструментов Excel

Появилось мгновенное заполнение в Excel 2013. Этот инструмент позволяет преобразовать данные или извлечь какой-то фрагмент: достаточно задать 1-2 образца того, что надо получить из исходного столбца (или нескольких столбцов), и мгновенное заполнение заполнит весь столбец значениями, исходя из заданного вами паттерна (шаблона).

Это может быть не очевидным для тех, кто только знакомится с этим инструментом, но мгновенное заполнение анализирует всю строку, все смежные столбцы (то есть нужно вводить данные в любом соседнем столбце с данными, без перерыва в виде пустого столбца). И это значит, что можно обрабатывать (извлекать и объединять) данные из нескольких столбцов.

Курсы по Excel и Google Драйву

Друзья, несколько новостей по моим табличным курсам в МИФе

Во-первых, добавил новые видео и модули в эти курсы:
- В оба курса — модуль про функцию LAMBDA и вспомогательные функции BYROW/ BYCOL, SCAN и REDUCE, MAP и ISOMITTED (последняя в Excel). В случае с Гугл Драйвом — отдельное видео про именованные функции в Google Таблицах.
- Видео про ПРОСМОТРX / XLOOKUP в Google Таблицах — добавлено некоторое время назад, благо функция там появилась вскоре после Excel. В курсе про Excel эта функция и так, разумеется, была.
- Большое видео про Power Pivot (модель данных) в Магии Excel будет скоро.

Во-вторых, с 1 ноября цена на оба курса вырастет. Так что если думали про то, чтобы изучить основательно ту или иную тему или обучить свою команду/компанию (а на этих курсах у нас учатся сотрудники самых разных компаний, включая компанию из топ-10 крупнейших в России и национальный исследовательский университет) — самое время брать курс по старой цене с новыми материалами. Тем более несколько дней будет скидка — см ниже.

Немного фактов.
В каждом курсе есть исходные и заполненные файлы с примерами к каждому уроку, а в отдельных тарифах и подробные конспекты (суммарно как книга).

В Магии Excel 60+ уроков, в Гугл Драйве 90+.

Урокам можно ставить оценки. За все время у Гугл Драйва 600 оценок (из них 7 четверок и 2 тройки), у Магии Excel 245 оценок (тоже почти все пятерки: только 4 четверки, ни одной тройки).

Коллеги подкинули промокод на скидку, ловите:
LEMURY
35% до 5 октября.

https://www.mann-ivanov-ferber.ru/courses/magicexcel/
https://www.mann-ivanov-ferber.ru/courses/gdrive/

В очередной раз на корпоративном обучении выяснилось, что многие не знают про двойной щелчок для протягивания формул или значений (и это нормально и здорово: значит, те из вас, кто про это еще не слышал, на этом сэкономят немало времени)

Итак, если мы наводим курсор на правый нижний угол ячейки, и он превращается в черный крестик, можно щелкнуть дважды и формула скопируется до конца столбца (до последней строки с данными).

Это сработает и не только для формул, но и для последовательности чисел или дат (допустим, у вас 1 и 2 в первых двух ячейках — двойной щелчок продолжит ряд, как если бы вы использовали инструмент "Прогрессия" или тянули руками за уголок ячейки).

До Excel 2010 магия ломалась на пустых ячейках в соседнем столбце, но потом починили: если даже есть пропуски в столбце слева, формула протянется до конца.

Какие еще варианты?

Можно использовать Таблицы (Tables) — Ctrl+T или Ctrl+L и вперед — формулы в Таблицах автоматически копируются на все строки.

Еще можно использовать сочетание Ctrl + D для заполнения вниз. Но для этого придется сначала выделить все ячейки в столбце. То есть пойти в конец диапазона (Ctrl + End), потом выделить столбец до первой ячейки (Ctrl + Shift + ↑). Кстати, Ctrl + R — это заполнение вправо, тоже может пригодиться.

P.S. Google Таблицы сами предлагают протянуть формулу в таких ситуациях — просто можно нажать галочку или Ctrl + Enter. Двойной щелчок и Ctrl + D / Ctrl + R там тоже работают.

Подбор параметра (Goal Seek) находится в коллекции «Анализ “Что если”» (What-If Analysis) на вкладке ленты «Данные».

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

Если мы хотим идти от результата (допустим, маржинальная прибыль = 750 000) и понять, каким должен быть входящий параметр (один из — допустим, себестоимость при прочих равных) для желаемого результата, нам нужен подбор параметра.

Как извлечь из текстовой строки все символы, кроме первых N (например, первых двух)?

1. Чтобы вычислить, сколько символов нужно извлечь, смотрим на число символов в тексте (функция ДЛСТР / LEN) и вычитаем N. Так получим число знаков, которые нужно извлечь.

2. Ну а чтобы их извлечь, используем ПРАВСИМВ / RIGHT — эта функция извлекает из текста (первый аргумент) заданное во втором аргументе число символов. Если нужно было бы вырезать с начала — то ЛЕВСИМВ / LEFT.

Получается:
=ПРАВСИМВ(текст; ДЛСТР(текст) — N)

Альтернатива без формул — мгновенное заполнение. Вводим в первой строке то, что нужно извлечь — Enter — Ctrl+E — вуаля!

Горячие клавиши для быстрого перемещения и выделения в Excel🔥

Ctrl + PgDn/PgUp — следующий/предыдущий рабочий лист

Ctrl + Backspace — возвращаемся к активной ячейке

Ctrl + A — выделяем всю текущую область (диапазон)

Shift + пробел — выделяем всю строку (если активна "умная таблица" — то в пределах таблицы, иначе — в пределах всего листа)
Ctrl + пробел — выделяем весь столбец

Ctrl + стрелки — перемещаемся в конец диапазона (в направлении стрелки). Вместе с Shift — выделяем до конца диапазона.

Ctrl + End — перемещаемся в конец активной области на листе (в самые последние строку и столбец с данными)

P.S. Если делаете какое-то действие часто, на последнем шаге, когда кликаете на какую-то команду, остановитесь на секунду, наведите курсор на команду и посмотрите на подсказку — вполне вероятно, что там будет сочетание клавиш для нее.

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

Shift позволяет выделять сразу несколько объектов/символов от активного до того, на который щелкнете.

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

Ctrl + левая кнопка мыши: быстрое копирование листов или объектов

Нужно создать копию листа? Зажимаем Ctrl и тянем ярлык существующего листа мышкой. Получаем копию.

Это чудо работает не только с листами, но и с фигурами, например (см видео). Или с диаграммами.

И не только в Excel, но и в других приложениях. Например, в Power Point или Google Презентациях 🔥

Бесплатный мини-курс по новым функциям Excel на Stepik

Друзья, записал крошечный курс (5 тем — небольшое текстовое описание функций + видео-уроки) по новым функциям Excel.

— Функция ПРОСМОТРX / XLOOKUP — замена легендарной ВПР / VLOOKUP
— Динамические массивы Excel — новые правила работы с массивами в Excel и появившиеся благодаря ним функции
— Новые функции для работы с массивами — как ВСТОЛБИК / VSTACK, позволяющая объединять массивы в один или ВЫБОРСТРОК / CHOOSEROWS, с которой можно извлечь отдельные строки из массива
— Функция LAMBDA — с ней можно создавать собственные функции или обрабатывать циклично каждую строку или столбец массива (и многое другое).

Можно посмотреть уроки абсолютно бесплатно на Stepik — по этой ссылке:
https://stepik.org/course/182713

Буду признателен, если поделитесь ссылкой с коллегами, а после прослушивания напишете в комментариях на платформе или здесь, как вам уроки!

Получаем название листа формулой

Функция ЯЧЕЙКА / CELL может выдавать разную информацию: например, полное имя файла (книги) вместе с листом. Для этого ее первый и единственный обязательный аргумент должен быть равен "имяфайла" ("filename").

А дальше — дело техники — вытаскиваем только имя листа текстовыми функциями.
В новом Excel совсем удобно: ТЕКСТПОСЛЕ / TEXTAFTER вытащит все, что после квадратной скобки.

=ТЕКСТПОСЛЕ(ЯЧЕЙКА("имяфайла");"]")

В старых версиях Excel воспользуемся комбинацией функций:
НАЙТИ / FIND подскажет, на какой позиции находится скобка, ДЛСТР / LEN — сколько в имени вообще символов — исходя из этого поймем, какая длина названия листа — в нашем случае 5 символов — именно столько извлечем с конца текстовой строки с помощью функции ПРАВСИМВ / RIGHT.

=ПРАВСИМВ(ЯЧЕЙКА("имяфайла");ДЛСТР(...)-НАЙТИ("]";...))

Изменяем стандартную диаграмму

Построить диаграмму в Excel можно очень быстро — практически одной лапой, а точнее, сочетанием клавиш Alt + F1.
Эта комбинация вызывает вставку стандартной гистограммы (столбиков).

Что если вы хотите строить диаграмму другого вида? Например, не обычную гистограмму, а с накоплением (когда отдельные составляющие выстраиваются в один общий столбец), с таблицей данных (значения внизу диаграммы в таблице), с осью в тысячах или еще с чем-то?

Настройте диаграмму как вам хочется. После этого:
1 Щелкаем правой кнопкой и нажимаем "Сохранить как шаблон..." (Save as Template...)

2 В появившемся окне придумываем название, под которым шаблон диаграммы будет сохранен в файловой системе

3 Нажимаем на ленте инструментов во вкладке "Конструктор диаграмм" (Design) кнопку "Изменить диаграмму" (Change Chart Type)

4 Заходим в папку "Шаблоны" (Templates). Во-первых, мы уже можем пользоваться этим шаблоном отсюда и при вставке новых диаграмм! Но нам остается финальный штрих, чтобы именно эта диаграмма строилась по сочетанию клавиш Alt + F1.

5 Щелкаем по шаблону правой кнопкой мыши и нажимаем "Сделать стандартной" (Set as Default Chart).

P.S. Если хотите, чтобы стандартной были вообще не столбики, а другой тип — допустим, круговая (пирог, pie chart) — щелкните на этот тип в окне "Изменение типа диаграммы" и нажмите туда же — "Сделать стандартной".

Хочу изучить конкретную тему в рамках Excel. Какую одну книгу мне прочитать?

Excel в целом
Microsoft Excel Inside Out (Office 2021 and Microsoft 365)
На русском:
Excel 2019. Библия пользователя — Куслейка, Александер

Макросы
Microsoft Excel VBA and Macros — Bill Jelen
На русском: Excel 2016. Профессиональное программирование на VBA — Александер, Куслейка (не пугайтесь версии 2016 — макросы не меняются десятилетиями)

Сводные таблицы
Сводные таблицы в Microsoft Excel 2021 и Microsoft 365 — Джелен

Power Query
Скульптор данных в Excel с Power Query — Николай Павлов
или / и
Приручи данные с помощью Power Query в Excel и Power Bi — Пульс, Эскобар

Power Pivot и язык формул DAX (который используется и в Power BI / других решениях Microsoft)
Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel — Руссо, Феррари

Очень глубоко и основательно про DAX: Подробное руководство по DAX: бизнес-аналитика с Microsoft Power BI, SQL Server Analysis Services и Excel — Руссо, Феррари

Для первого ознакомления с Power Pivot можно начать с глав в книге Джелена про сводные

Формулы в целом
С новыми формулами (LAMBDA, новые массивы), от начального до продвинутого уровня: главы про формулы в Microsoft Excel Inside Out.
С новыми формулами посложнее: Advanced Excel Formulas: Unleashing Brilliance with Excel Formulas
На русском с новыми формулами: главы про формулы у меня в "Магии таблиц"
На русском до 2019 включительно от начального до продвинутого: главы про формулы в Excel 2019. Библия пользователя
На русском до 2019 включительно посложнее: Мастер формул — Николай Павлов

Старые формулы массива (до 2019 включительно)
Ctrl+Shift+Enter Mastering Excel Array Formulas: Do the Impossible with Excel Formulas Thanks to Array Formula Magic — Girvin
На русском: Мастер формул — Николай Павлов

Новые формулы массива (динамические массивы)
Up Up and Array!: Dynamic Array Formulas for Excel 365 and Beyond
На русском: немного есть у меня в "Магии таблиц"

Визуализация
Визуализация данных при помощи дашбордов и отчетов в Excel — Куслейка

Подробный обзор, где больше книг — по постоянному адресу:
https://teletype.in/@renat_shagabutdinov/excellent_books

Вычисляем период в днях/месяцах/годах: функция РАЗНДАТ / DATEDIF

Если вам нужно вычислить разницу между двумя датами не в днях (для чего достаточно вычесть из одной даты другую или воспользоваться функцией ДНИ / DAYS), а в месяцах или годах (например, возраст) — пользуйтесь функцией РАЗНДАТ / DATEDIF. В Excel при ее вводе не будут отображаться всплывающая подсказка с аргументами, Excel не предложит ее дописать, но не обращайте на это внимания — она работает во всех версиях. И в Google Таблицах тоже!

 =РАЗНДАТ(дата_начала; дата_окончания; единица измерения)

Первые два аргумента — даты начала и окончания периода. Они могут быть указаны прямо в формуле в кавычках либо в виде ссылок на ячейки с датами, а также быть заданными функцией СЕГОДНЯ / TODAY.

Единица измерения задается в кавычках. Есть следующие возможные варианты:

"d" — число дней (такой параметр не имеет особого смысла, так как для этой задачи подойдет и функция ДНИ / DAYS, и просто вычитание);
"m" — число полных месяцев в периоде;
"y" — число полных лет в периоде;
"md" — разница в днях без учета месяца и года (например, между 01.01.2021 и 15.06.2022 — 14 дней);
"ym" — разница в месяцах без учета дня и года (например, между 01.01.2021 и 15.06.2022 — 5 месяцев);
"yd" — разница в днях без учета года (например, между 01.01.2021 и 15.06.2022 —165 дней).

Видео про функцию ПРОСМОТРX / XLOOKUP

Это чудо для поиска (объединения таблиц) появилось в Excel 2021 и в Google Таблицах. И лишено некоторых минусов функции ВПР — легендарной функции, чего уж там!

— ВПР ищет только в первом столбце таблицы, а ПРОСМОТРX ссылается на отдельные столбцы (где ищем и откуда возвращаем данные) — ей все равно, какая структура данных;
— ПРОСМОТРX по умолчанию ищет текст (точное совпадение), а ВПР — ближайшее наименьшее число;
— В режиме поиска числа ПРОСМОТРX не требует сортировки данных и умеет искать и ближайшее наибольшее тоже;
— Есть отдельный аргумент для замены ошибок (когда ничего не найдено) на другое значение.

Но зато ВПР умеет работать с символами подстановки (* и ?) по умолчанию, а ПРОСМОТРX — нет, нужно задавать специальный аргумент для этого.

Вот видео про эту функцию:
https://youtu.be/4wigZhde7jY

Это первое видео бесплатного открытого мини-курса на Stepik про новые функции Excel, заглядывайте на огонек:
https://stepik.org/course/182713/

Вот такая табличка: сравнение старого и нового Excel и Google Таблиц по доступности новых функций.

Это один из многих слайдов практикума "Новые функции", который пройдет 7, 14 и 20 ноября.
На самих занятиях слайды смотреть не будем — это дополнительный материал, а во время вебинаров будет много практики и ответы на вопросы.

Практиковаться можно будет и в Google Таблицах (если нет подписки 365), и в Excel!

Присоединяйтесь!
https://www.mann-ivanov-ferber.ru/courses/practicum-excel/

Лемур принес вам скидочку 35% — по промокоду LEMUREC, действующему до 7 ноября.

А тут, напоминаем, подробное сравнение Excel и Google Таблиц по всем нюансам.