Закрепление верхней строки в Excel в один клик: добавляем команды на панель быстрого доступа

Панель быстрого доступа (Quick Access Toolbar, QAT) — простейший инструмент для настройки интерфейса "под себя". Туда можно добавить любую команду — как с ленты, так и из списка вообще всех команд и инструментов Excel.

Даже если какое-то действие нельзя добавить напрямую из ленты (потому что оно там находится в выпадающем списке, в коллекции — как, например, закрепление верхней строки находится в коллекции "Закрепление областей"; или потому что действия вообще нет на ленте) — его все равно можно добавить через параметры Excel, чтобы во всех книгах у вас всегда был доступ к нужной команде в один клик. В мини-статье разбираем, как это сделать — как раз на примере закрепления верхней строки.

Добавляем к дате день недели и выделяем выходные

Допустим, мы с вами хотим видеть в каждой дате день недели - не "01.01.2023", как по умолчанию, а "01.01.2023 Вс".

Для этого заходим в формат ячеек (Ctrl + 1) и добавляем к формату "ДДД" (DDD). Это краткое обозначение дня недели ("Вс"). Для полного ("Воскресенье") понадобится код "ДДДД" (DDDD).

Ну а чтобы выделить цветом выходные (или другие дни) - воспользуемся условным форматированием (Conditional Formatting).
Зададим правило с формулой, а в ней будем использовать функцию ДЕНЬНЕД / WEEKDAY.
Она возвращает порядковый номер дня недели. Чтобы нумерация была привычной для нас с вами, добавьте второй аргумент, равный двойке:

=ДЕНЬНЕД (ячейка с первой датой в диапазоне; 2)

Тогда понедельнику будет соответствовать единица (иначе - воскресенью), вторнику - двойка и так далее.

И остается добавить условие - день недели у нас должен быть больше 5 (то есть 6 или 7, суббота или воскресенье), чтобы ячейка заливалась цветом.
Все показываем на видео!

Начиная с Excel 2016 можно вычислять минимальное и максимальное значение по условиям — например, максимальную сделку не вообще, а с определенным типом товара. Функции называются МАКСЕСЛИ / MAXIFS и МИНЕСЛИ / MINIFS.

=МАКСЕСЛИ(максимальный_диапазон; диапазон_условия1; условие1; …)

Максимальный диапазон — диапазон, в котором мы ищем максимальное число.

В Google Таблицах эти функции тоже есть, названия у них там на английском при любом языке формул: MAXIFS, MINIFS.

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

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

Также можно пробелами ставить отступы в формуле, если это поможет вам с восприятием формулы

На скриншоте формула с переносами строк, но без отступов. Каждая функция начинается с новой строки. На работу формулы это не влияет.

Ура! Теперь в Excel есть всплывающие подсказки, показывающие, чему равен фрагмент формулы.

В Google Таблицах такое было давно. А в Excel можно было только вычислить фрагмент формулы нажатием F9, чтобы посмотреть, чему равна функция в конкретном случае или что скрывается за ссылкой. Но после нажатия F9 вычисленный фрагмент становится значением, и нужно выйти через Esc, чтобы он так и не остался фиксированным значением.

А тут просто всплывающее значение. Можно посмотреть, чему равна:
- одна из функций в формуле
- сочетание функций, выражение внутри формулы
- что хранится в той или иной ячейке
- какой массив данных хранится в диапазоне или вычисляется функцией/частью формулы.

Отключается и включается сочетанием Ctrl + Alt + P.

Пока доступно только для участников программы Office Insider, ждем для всех!😺

Выделяем всю строку при наличии в столбце слова (буквы, текста)

Тут на вебинаре прозвучал вопрос — как покрасить всю строку, если в одном из столбцов встречается какое-то слово (то есть не занимает ячейку целиком, просто знаком равно = сравнивать нельзя).

Можно функцией ПОИСК или НАЙТИ. Первая работает без учета регистра, вторая — с.

=НАЙТИ (что ищем; где ищем)

На выходе — порядковый номер символа, позиция искомого значения в тексте для поиска.

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

Главное в условном форматировании — не запутаться со строками и столбцами. Формула в условном форматировании вводится один раз — при ее формировании учитывайте, что вы вводите формулу для левой верхней ячейки диапазона, а далее на все остальные ячейки она будет «протягиваться» (как если бы мы ввели формулу в B2 и далее скопировали и вставили во все остальные ячейки в трех столбцах). И ссылки на ячейки будут меняться, если они являются относительными.

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

Личная панель инструментов

Вот какую новинку предложил Excel с утра (вероятно, только для программы Office Insider пока — даже сказано, что "предварительная версия)

Личная панель инструментов: набор команд и инструментов под лентой.
А чем же была плоха обычная панель быстрого доступа? 😺Ведь ее тоже можно разместить под лентой и добавить любые инструменты.
Возможно, в случае с новой панелью Excel научится сам собирать те команды, которые вы используете чаще всего. Пока этого не случилось — там был стандартный набор.

Отличий от старой панели быстрого доступа тогда немного. В новом режиме лента никогда не "фиксируется" — она раскрывается только при щелчке на вкладку.

Внешний вид немного отличается, 2 варианта отображения команд в наличии — см. видео, есть опция "Показать метки команд". Панель раскрывается, то есть команд, вероятно, можно собрать больше, чем на панель быстрого доступа.

Нельзя (надеемся, пока) добавить отдельную команду не с ленты или из коллекции (как закрепление верхней строки). А на панель быстрого доступа можно любую команду Excel добавить — писали об этом выше. Может, в будущем под новую панель параметры дополнят ее настройками.

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

Доступ к командам на панели с клавиатуры (Alt + отображаемые цифры/буквы) есть, как был и к панели быстрого доступа.

Истина (True) где-то рядом

Сегодня пятничный и не совсем серьезный прием. Поделитесь, если вам пригодится в работе и придумаете, где это использовать.

Представим, что вам нужно проверять, какой язык у пользователя — русский или английский (любой другой).
Например, чтобы учесть региональные настройки (хотя они и язык в интерфейсе Excel независимы, это нужно иметь в виду), или чтобы поменять выводимые формулами тексты/обозначения, если в вашей компании говорят на нескольких языках. Ну или просто поздороваться с пользователем 😺

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

=ЕСЛИ(ЛЕВСИМВ(ИСТИНА)="И";"Добро пожаловать в таблицу!";"Hello world")

Чтобы формула обновлялась автоматически при открытии книги и любом действии в ней, можно добавить вместо ИСТИНЫ что-нибудь не менее истинное, но волатильное (то есть обновляющееся при любом изменении в книге). Допустим, функцию СЕГОДНЯ. Можно просто сравнивать сегодняшнюю дату с ней же самой, это всегда будет возвращать ИСТИНА.

=ЕСЛИ(ЛЕВСИМВ(СЕГОДНЯ()=СЕГОДНЯ())="И";"Добро пожаловать в таблицу!";"Hello world")

Протягиваем даты одной правой

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

P.s. Лемур заметил, что на гиф в конце опечатка 🙀. «Как обычно» —
это левой кнопкой мыши. Приносим извинения

Собираем данные с разных листов в Excel и Google Таблицах (список листов - динамический)

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

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

В заметке разбираем по шагам + вспоминаем, что в Excel в любых версиях (а в старых, увы, функции LAMBDA нет) можно ссылаться на пачку листов или использовать символ подстановки в названиях листов (правда, только один раз 🤷🏻‍♂️).

Тепловая карта (шкала) без чисел

В условном форматировании со значками можно отключать отображение данных (показывали здесь), как и в случае с гистограммами (здесь).
А в случае с цветовой шкалой нет опции "Показывать только шкалу".
Но это можно исправить, просто применив пользовательский формат, в котором все данные будут скрыты.
В пользовательских форматах можно задавать отдельные форматы для четырех типов данных - положительных чисел, отрицательных, нуля и текста. Задаются они через точку с запятой.
И если ввести такой формат:

;;;

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

Подробнее про пользовательские форматы - в этом видео (оно на основе Google Таблиц, но форматы работают одинаково и там, и в Excel за вычетом косметических отличий).

Функция ВЗЯТЬ / TAKE: суммируем последние N сделок

Эта функция извлекает заданное количество столбцов или строк из массива. В частном случае можно вытаскивать не только строки или столбцы, но и значения (если на входе задан одномерный массив).

Например, если нам нужны первые 10 значений в столбце таблицы, будет такая формула (в общем виде):

=ВЗЯТЬ(Таблица[Столбец]; 10)

А если нужны последние, а не первые? Прелесть в том, что функция умеет и так - просто укажите отрицательное количество строк в ее аргументе.

=ВЗЯТЬ(Таблица[Столбец]; -10)

Если нужна сумма последних 10, то добавим сверху функцию СУММ / SUM:

=СУММ(ВЗЯТЬ(Таблица[Столбец]; -10))

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

ВЗЯТЬ доступна только в Microsoft 365, увы.

Выделяем цветом формулы по какому-то признаку

Вы хотите выделить визуально "старые формулы массива" (из версий до 2019 включительно), или формулы, ссылающиеся на какой-то лист, или формулы с определенными функциями.

Получить текст формулы можно с помощью функции Ф.ТЕКСТ / FORMULATEXT. Искать в этом тексте какой-то признак можно с помощью функции НАЙТИ / FIND.
И если все это засунуть в условное форматирование, то мы получим возможность выделять визуально формулы, содержащие что-нибудь!

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

=НАЙТИ("{";Ф.ТЕКСТ(первая ячейка форматируемого диапазона))

Ссылки на лист с названием - по этому самому названию

=НАЙТИ("название листа";Ф.ТЕКСТ(первая ячейка ...))

Определенные функции - по их названию. Например, ПРОСМОТРX, которой нет в старых версиях:

=НАЙТИ("ПРОСМОТРX";Ф.ТЕКСТ(ячейка))

А вот выделить формулы со старой функцией ПРОСМОТР можно, добавив к "запросу" скобку - иначе будут выделяться формулы, где есть и ПРОСМОТР, и ПРОСМОТРX.
=НАЙТИ("ПРОСМОТР(";Ф.ТЕКСТ(ячейка))

Быстрая ссылка на другую книгу

Если вам нужно связать книги - сослаться на значение/диапазон из другой книги, можно сделать это довольно быстро следующим образом:
- Открыть обе книги на экране (Вид - Упорядочить, View - Arrange All)
- Правой кнопкой мыши потянуть за границу ячейки или диапазона, перетащить в новую книгу, выбрать в появившемся меню "Связать" (Link Here). Также можно скопировать ячейку или диапазон, вставить в другую книгу и только после этого выбрать вариант со связью, нажав на смарт-тег (справа снизу от ячейку, куда вставили). Можно вставить скопированный диапазон и через окно специальной вставки (Ctrl + Alt + V, Вставить связь / Paste Link), и через контекстное меню, вызываемое правой кнопкой мыши.

В данном случае мы просто создаем ссылку на ячейки (транслируем данные из источника). Если нужно данные из другой книги обрабатывать функциями в формуле, то сначала нужно будет ввести функцию, а потом ссылаться из нее на диапазоны в другой книге. Здесь тоже пригодится опция "Упорядочить".

Самый компактный вариант для визуализации прогресса в Excel - гистограмма (условное форматирование)

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

Сделать такой простенький прогресс-бар можно так:
- Сослаться на фактическое значение в отдельной ячейке, куда мы вставим гистограмму;
- Вставить гистограмму в эту ячейку по вкусу;
- Задать в настройках правила условного форматирования максимальное значение как план/цель - тогда гистограмма будет шириной во всю ячейку, когда факт будет равняться плану. Иначе - будет показываться % выполнения;
- Можно включить опцию "Показывать только столбец", фактическое значение нам здесь не нужно - оно и так есть в исходной ячейке. А тут пусть будет только наш прогресс-бар.

Выводим все даты текущего месяца формулой

Тут понадобится функция ПОСЛЕД / SEQUENCE, которая может вернуть последовательность чисел (ищите эту прелесть в Excel 2021, Microsoft 365, Google Таблицах). У нее задается число строк, столбцов, начальное значение и шаг.
Шаг - самое простое, у нас это единица, один день.
Число строк или столбцов будет равно единице - в зависимости от ориентации списка. Если вы хотите список в один столбец (как в примере на картинке), то столбец будет один, а число строк будет определяться числом дней в месяце.
Начало - первое число месяца.

Единицы добыть легко - их есть на клавиатуре, а вот как получить остальное?

Первое число месяца - это дата, у которой день = единица, месяц текущий (функция МЕСЯЦ / MONTH вернет нам месяц СЕГОДНЯшней даты), год текущий (функция ГОД / YEAR тоже от текущей даты, которую мы получим с помощью функции СЕГОДНЯ / TODAY).
Собрать дату из трех кусочков можно функцией ДАТА / DATE. Ее аргументы - номера года, месяца, дня:

ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1)

Последнее число месяца - это функция КОНМЕСЯЦА / EOMONTH. Первый аргумент - дата (мы зададим текущую). Второй - на сколько месяцев вперед (положительное число) или назад от нее отступить. То есть КОНМЕСЯЦА(СЕГОДНЯ();-1) - это последнее число предыдущего месяца. Нам нужен текущий, так что без отступов - второй аргумент равен нулю.

КОНМЕСЯЦА(СЕГОДНЯ();0)

Все вместе (для списка в один столбец):

=ПОСЛЕД(ДЕНЬ(КОНМЕСЯЦА(СЕГОДНЯ();0)); 1;
ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1); 1)

Нумерация с помощью формул

В этом видео рассматриваем несколько вариантов автоматической нумерации строк в диапазоне / таблице:
- с помощью функции СТРОКА / ROW, в том числе с учетом положения таблицы на листе (то есть при вставке новых строк над таблицей нумерация все равно не будет меняться, она останется в пределах таблицы
- с помощью функции ПОСЛЕД / SEQUENCE (живущей в Excel 2021 и Microsoft 365)
- формулой из функций ЕСЛИ / IF, ЕПУСТО / ISBLANK, СТРОКА / ROW и ДВССЫЛ / INDIRECT для нумерации с пропуском пустых строк.

Файл с формулами из видео прикрепляем отдельным сообщением!

План-факт через комбинированную диаграмму

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

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

- Делаем разные значения бокового зазора у обеих гистограмм, чтобы столбики отличались по ширине. Заливку у обеих делаем прозрачной (в примере 40%). Это настраивается в панели "Формат", выделяем столбики и нажимаем Ctrl+1.

- Подписи забираем из ячеек в столбце D. Там формула (просто темп прироста, факт делим на план и вычитаем единицу) и пользовательский формат:

+0%* 🔥;-0%* 👎

(смайлики выберите по вкусу)

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

P.S. Файл с диаграммой прикреплен в отдельном сообщении выше - забирайте!

P.P.S. Мы тут с Лемуром наконец книгу "Магия таблиц" дописали! Получилось почти 600 страниц в гугл-доке (скорее всего, в книге будет поменьше) и почти столько же скриншотов, а также 50+ файлов с примерами. На уведомление о выходе можно подписаться тут:
https://www.mann-ivanov-ferber.ru/books/magiia-tablic/