Навигация по листам в книге 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

А все ведь знают, что в разных текстовых редакторах, в браузере можно удалить весь текст от курсора и до конца слова сочетанием Ctrl + Delete, а последнее (до курсора) слово целиком — сочетанием Ctrl + Backspace?

А вот в самом Excel Ctrl + Delete — удаление текста / формулы в ячейке от курсора и до конца строки.

Контрольное значение: отслеживаем значения ячеек в любом месте

Если вам нужно всегда видеть, чему равно значение в какой-нибудь ячейке, даже если мы работаете на другом листе - добавьте эту ячейку в окно контрольного значения.

Лента инструментов:
Формулы - Окно контрольного значения
Formulas - Watch Window

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

F9: вычисляем фрагмент формулы

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

Это можно использовать, чтобы посмотреть, какой результат возвращает один из промежуточных этапов формулы, что мы получаем в ней на входе.
В таком случае важно не забыть после нажать Esc, чтобы выделенный фрагмент не остался в формуле статичным значением.

Но иногда превратить диапазон в значения - именно то, что нужно! F9 можно применять и так. Если выделить диапазон и нажать F9, то вместо ссылки в формуле будет массив из значений, то есть тот же самый диапазон, но уже "внутри формулы". И тогда ваша формула будет работать без вспомогательной таблицы. Если вы хотите избавиться от такой таблицы - то этот способ вам подойдет. Все показываем в видео!

P.S. Проговорим еще раз: так как после этой манипуляции все значения остаются только в формуле, что-то изменять придется в ней. Этот вариант подходит, если таблица не будет изменяться в будущем.

Обновляем книжно-табличный обзор (целиком он по ссылке).

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

Spice Up Your Sheet Life

100 гугло-табличных лайфхаков на самые разные темы - от одного из главных экспертов. Форматирование, функции, сводные, диаграммы, скрипты - тут всего понемногу. И есть действительно нетривиальные вещи.

Бен дарит книгу за подписку на рассылку. Рассылку отдельно рекомендуем, кстати!
Ссылка на PDF.

MrExcel 2022: Boosting Excel
Книга на Амазоне
Новинка от одного из лучших авторов в теме Excel.

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

Есть новшества Excel последних лет: LET, LAMBDA, ПРОСМОТРX, опции для совместной работы (как представления или история версий). Есть и Power Query и модель данных Power Pivot,
Много неочевидных вещей, как у всегда у автора - то, что зачастую не встретишь в других книгах.

Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables
Книга на Амазоне

Книга целиком посвящена "таблицам" Excel и их предшественнику - спискам. То есть тому инструменту, который в русскоязычной среде называют "умными таблицами", а в интерфейсе Excel - "таблицами" / tables.

Тут все детали про ссылки на таблицы, их взаимодействие, про изменение размеров таблиц, сортировку и фильтрацию, форматирование, использование как источник в простых сводных и Power Query и даже про работу с таблицами в VBA (макросах) и в Excel Online!

Перевод есть на сайте Сергея Багузина. Спасибо ему за эту работу! Сергей использовал в переводе свои скриншоты (!!) с русскоязычным интерфейсом.

Заполняем промежуточные шаги с помощью прогрессии

Допустим, вы знаете первое значение и то, к которому нужно прийти. Выделите весь диапазон от первого значения до последнего и вызовите инструмент "Прогрессия" (Series):
Главная - Заполнить (кнопка со стрелкой вниз в правой части ленты) - Прогрессия

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

Редактируем скрытую ячейку, не раскрывая строки/столбцы

Вам нужно изменить значение ячейки, не раскрывая строку/столбец с ней (чтобы потом не скрывать снова)? Есть два способа:

1 Ввести адрес ячейки в поле "Имя" (слева от строки формул);

2 Ввести его в окне "Переход" (вызывается клавишей F5)

После любого из двух действий можно редактировать значение в строке формул (или просто его увидеть - иногда нужно именно это). Смотрим на видео!

P.S. А вот в Google Таблицах такая магия не сработает, увы 😿

Книга Excel с примером формулы для поиска по двум оценкам.