Меняем регистр в 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.

Отображаем только значки в ячейках - без чисел

В условном форматировании в Excel есть графические объекты, в том числе наборы значков (Icon Sets). Это 3, 4 или 5 значков: по умолчанию все числа в выделенном диапазоне делятся на 3, 4 или 5 равных частей и для каждой из них применяется соответствующий значок.

А если у нас, например, оценки по пятибалльной шкале, можно отображать только значки, без самих цифр!

Для этого нужно залезть в настройки правила условного форматирования и включить флажок "Показывать только значок" (Show Icon Only).
В настройки можно попасть:
- если сразу выбрать в списке наборов значков не готовый вариант, а "Другие правила" (More Rules) - тогда вы попадете в диалоговое окно с настройками
- через "Управление правилами" (Manage Rules) в Условном форматировании, как на видео.

Визуализируем выполнение плана с помощью гистограмм

В условном форматировании помимо значков есть и гистограммы (в английском языковом пакете называются более корректно — Data Bars, все-таки это горизонтальные столбики, а гистограммой мы привыкли называть вертикальные).

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

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

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

Будущее уже наступило, просто оно еще неравномерно распределено.

Лемур считает, что Уильям Гибсон говорил про разные версии Excel. Вот, например, появилась такая удобная и такая долгожданная вещь - фильтрация в выпадающих списках в проверке данных. Но доступна не всем :(

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

Увы, пока это удовольствие доступно только в Microsoft 365 и пока только тем, кто получает самые свежие обновления через бета-канал по программе Office Insider (подробнее тут). На самом деле, опция была анонсирована еще в январе. Но до сих пор эта ожидаемая многими (судя по комментариям в Tech Community Microsoft) опция до обычных пользователей не доехала. Будем ждать, когда это обновление будет доступно в Microsoft 365 у всех!

Ну а если хочется бесплатно и прямо сейчас... То эта красота была и есть в Google Таблицах!

Продолжим разговор про выпадающие списки. Хотя поиск в них пока доступен меньшинству, сами выпадающие списки (проверка данных) есть во всех версиях Excel.
И зачастую мы создаем их на основе списка (товаров/фамилий/городов и т.д.), который меняется (дополняется).
Как сделать, чтобы новые значения попадали в выпадающий список автоматически? И чтобы сами списки автоматически появлялись в новых строках вашей таблицы?

Решения могут быть разные в зависимости от того, на разных ли листах данные.

Данные на том же листе - превращаем справочник в Таблицу и ссылаемся на него.
Данные на другом листе и у вас Excel 2010+ - просто ссылаемся на другой лист.
Данные на другом листе и будут пополняться новыми и/или у вас Excel 2007 - ссылаемся через функцию ДВССЫЛ / INDIRECT или через именованный диапазон.

Обсуждаем все нюансы в видео:
https://youtu.be/Mf9fMmWPUCw