Сыграть на Alt'е - доступ к командам на ленте с помощью клавиатуры

Хотя коты любят мышек, но колдовать в Excel быстрее получается при использовании клавиатуры - а освободившееся время можно посвятить охоте.
Лемур напоминает: нажатие Alt позволяет перемещаться по вкладкам и командам на ленте с помощью клавиш (указанных на вкладках и на командах) и стрелок (стрелками право-влево по вкладкам, вниз - чтобы зайти на вкладку, и Enter для выбора команды).

Alt - идем на нужную вкладку - выбираем нужную команду.

А еще у команд на панели быстрого доступа тоже есть обозначения.
А значит, это простой способ создать свое сочетание клавиш для абсолютно любой команды Excel, в том числе той, которой нет на ленте.
Подробнее о том, как их добавлять на панель быстрого доступа, мы писали тут: https://www.tg-me.com/google_sheets/1021

А когда команда на ней, остается нажать Alt + нужная цифра.

P.S. Магия Alt'а не работает на Маках 😿

Макрос: сравнение двух книг Excel

Когда-то давно я написал такой макрос по просьбе одного клиента, но с тех пор иногда меня спрашивают про такую задачу - сравнить две похожие книги Excel, определить, в каких ячейках значения отличаются.

Макрос простенький (и точно не является образцом красивого кода и идеалом макросостроения 😺) и работает только если в книгах одинаковое количество листов. То есть для сравнения совсем разных файлов не подойдет, но если вы хотите сравнить две версии или два похожих по структуре файла - самое то.

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

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

Ячейки с формулами подсвечиваются оранжевым. Так, на скриншоте видно, что сумма рассчитывается формулой только во второй книге, а в первой это значение.

Задавайте вопросы в комментариях!

Меняем регистр в Excel

Здесь можно использовать функции:
СТРОЧН / LOWER - нижний
ПРОПИСН / UPPER - ВЕРХНИЙ
ПРОПНАЧ / PROPER - Каждое С Заглавной

Жаль, нет функции для того, чтобы только первая буква всего текста была заглавной. Это можно исправить формулой из нескольких функций. Нужно соединить первую букву, сделав ее заглавной, и все остальные, сделав их строчными:

=ПРОПИСН(ЛЕВСИМВ(текст))&СТРОЧН(ПРАВСИМВ(текст;ДЛСТР(текст)-1))
=UPPER(LEFT(текст))&LOWER(RIGHT(текст;LEN(текст)-1))

Извлекаем первую букву (ЛЕВСИМВ), делаем ее заглавной (ПРОПИСН), прикрепляем (&) к этому все буквы справа (ПРАВСИМВ), кроме первой (длина всего текста - ДЛСТР - за вычетом единицы) и делаем строчными (СТРОЧН).

Меняем регистр в Word: Shift + F3

Лемур уверен: многие пользователи Ворда знают это сочетание клавиш, но вряд ли все. Так что стоит об этом напомнить!

Итак, Shift + F3 меняет регистр слова (на котором курсор) или выделенного фрагмента.

Верхний-Нижний-Каждое С Заглавной

Срезы - удобные и наглядные фильтры, которые находятся на графическом слое листа Excel (то есть "плавают" поверх ячеек), появились в Excel 2010 и доступны как в "Таблицах" (Tables, их еще называют "умными таблицами"), так и в сводных таблицах (Pivot Tables).

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

https://teletype.in/@renat_shagabutdinov/excel_slicer

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

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

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

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

Выделяем диапазон, в котором нужно удалить пустые ячейки (в видео - все данные на листе с помощью Ctrl+Shift+End).

Для этого нужен инструмент "Найти и выделить" (на ленте на вкладке "Главная") - выбираем там "Выделить группу ячеек" и в появившемся диалоговом окне - "Пустые ячейки".

После этого остается нажать Ctrl + - (Ctrl и минус) - это удаление ячеек/строк/столбцов. И выбрать "строку".

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

У вас есть дата, а вы хотите номера/названия месяцев в отдельном столбце (допустим, для сводной, для отчетов, для фильтрации) - 7 вариантов

Самый простой вариант - функция MONTH / МЕСЯЦ. Это число, порядковый номер (3 для марта, 11 для ноября).

С помощью функции TEXT / ТЕКСТ можно получить также вариант с нулем для коротких номеров (03 для марта, но 11 для ноября). С помощью нее же - текстовые варианты (мар. и марта для российских региональных настроек).

Если "марта" вам не нравится и вы хотите использовать абсолютно любые варианты, то можно брать их из диапазона с помощью ИНДЕКСа. Или из виртуального массива внутри формулы с помощью того же ИНДЕКСа или ВПР или ВЫБОРа.

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

Таблица с примерами формул

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

Навигация по листам в книге Excel

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

А к следующему и предыдущему листу можно переходить с помощью сочетаний клавиш Ctrl + PgDn и Ctrl+PgUp.

Автосумма: одним движением суммы по всем столбцам/месяцам.

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

А если - как в видео - выделить диапазон из нескольких столбцов и строк вместе с пустой строкой под ним и столбцом справа, то мы получим суммы по каждому столбцу и строке (и итоговую справа внизу).

Создаем оглавление на отдельном листе с помощью гиперссылок

Ранее мы писали про окно "Переход к листу" — удобно для навигации. Но было ценное замечание от читателя: там нет поиска.

Как можно сделать навигацию еще более удобной в случае большого количества листов: создать оглавление с ссылками на каждый лист.

Вставить ссылку на место в документе можно с помощью сочетания Ctrl + K (как и в других приложениях, например, здесь в Телеграме).

Далее выбираем слева "Связать с" — "Место в документе".
Вводим отображаемый в ячейке текст ссылки и адрес ячейки, внизу в списке выбираем лист.
И получаем ссылку для перехода на соответствующее место в документе.

Но если листов много, вручную проделывать это для каждого листа будет мучительно, поэтому нам пригодится макрос, который формирует оглавление на отдельном листе автоматически.

А вот и сам макрос — код ниже, но проще будет загрузить прикрепленный файл с модулем и его импортировать, как в видео.

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

Макрос: создаем оглавление автоматически

Следующий код создает новый лист с именем "Оглавление" и добавляет в первом столбце ссылки на все остальные листы в книге.

Его можно добавить в личную книгу макросов, чтобы он был доступен у вас во всех книгах Excel, а потом добавить на панель быстрого доступа, чтобы оглавление создавалось по нажатию кнопки.

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

Если у вас еще нет личной книги макросов, ее можно добавить, записав любой макрос — инструкция.

Скидка на курс "Магия Excel" и видеоурок про объединение и разделение текстовых строк

В МИФе идет черная пятница со скидками на книги и курсы, и мы с Лемуром решили не стоять в сторонке - попросили у коллег скидку на наш курс.

Ловите промокод на 40% до конца декабря: LEMUR

Сам курс тут. Уроки по 10-20 минут, с пометками, стрелочками, масштабированием и всем, что нужно для лучшего восприятия. Все функции и команды и на русском, и на английском. Все ключевые темы и функции внутри.
https://www.mann-ivanov-ferber.ru/courses/magicexcel/

Посмотрите один из 55 уроков "Объединяем и разделяем текст", чтобы лучше понять, что внутри (и задавайте любые вопросы в комментариях):
https://www.youtube.com/watch?v=YwCO1EcIKO0

Задачка: отфильтровать данные по списку товаров.

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

Как бы вы решили эту задачу?

Расширенный фильтр: решение задачи со списком товаров

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

Это расширенный фильтр. Условия для него задаются в ячейках: сверху заголовки столбцов из исходных данных, на которые накладываются условия, под ними - сами условия. Одна строка = одна комбинация условий, один фильтр.
В нашем случае условие на один столбец - "Товар". Скопируем заголовок из исходных данных, вставим над списком товаров.

После выделяем любую ячейку исходных данных и вызываем расширенный фильтр. Он прячется под надписью "Дополнительно" (Advanced) справа от кнопки обычного фильтра на вкладке ленты "Данные" (Data).

В появившемся диалоговом окне нужно выделить диапазон условий и нажать ОК.

Расширенный фильтр - мощный инструмент, он позволяет решать не только эту задачу. Де-факто это возможность сразу применять несколько фильтров одним движением, а также сразу вставлять выборку в другой диапазон, а не фильтровать на месте.

Кроме того, есть функции баз данных, которые работают с условиями расширенного фильтра! Они есть и в Excel, и в Google Таблицах (а сам расширенный фильтр только в Excel).

Подробнее про них и про правила записи условий тут:
https://teletype.in/@renat_shagabutdinov/4lVaI_Pj7

Дано: есть данные за несколько лет с выручкой (или чем-то еще) по дням.

Задача: посмотреть на сезонность, какой месяц "лучше", какой "хуже". На сезонность — то есть на январь за все годы, на февраль за все годы, и так далее.

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

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

Смотрим на видео!

Неужели это свершилось!

Теперь в Word и Excel можно, как в Google Документах/Таблицах, вставлять текст и значения ячеек без форматирования (опция уже какое-то время выкатывается у тех, кто получает обновления, то есть у подписчиков Microsoft 365).

Сочетание клавиш такое же, как в сервисах Google - Ctrl + Shift + V.

Очень удобно: не нужно лезть в контекстное меню и там выбирать "Вставить только значения" или вызывать окно специальной вставки (Ctrl + Alt + V) и там выбирать "Значения" и нажимать ОК.

В Excel можно таким образом превратить формулы в значения в пару нажатий: скопировали Ctrl+C и вставили как значения Ctrl+Shift+V.