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

Вот какую новинку предложил 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/

Если мы установим фильтр, выберем определенные строки и после этого применим "Автосумму" (Alt + = или на ленте на вкладке "Формулы") — будет введена не функция СУММ / SUM, а ПРОМЕЖУТОЧНЫЕ.ИТОГИ / SUBTOTAL. Она же вводится в Таблицах (Создать таблицу - Ctrl + T) в строке итогов (Ctrl+Shift+T).

Эта функция позволяет производить вычисление только с видимыми строками.
У нее такой синтаксис:

=SUBTOTAL(номер функции ; диапазон ; [еще диапазон]; ...)

Номер функции определяет, какая операция будет производиться. Функций всего 11 - стандартный набор, который, например, есть и в вычислениях сводных таблиц Excel.

Каждая функция бывает в двух вариантах — коротком (9 или 11, например) и длинном из трех цифр (109 или 111).

Короткий вариант — подсчет всех видимых строк (отфильтрованных) и скрытых вручную (через скрытие или группировку) строк.
Длинный вариант — подсчет только отфильтрованных строк, без скрытых вручную.

Если внутри диапазона уже есть другие функции SUBTOTAL, такие вложенные подытоги не будут учитываться. То есть задвоения в таком случае не будет.

Совместная работа в Excel (в Microsoft 365)

Можно ли колдовать с данными вместе в режиме онлайн в Excel, как в Таблицах?

Можно, но нужна подписка Microsoft 365 🤯 И тогда доступны представления (sheet views, своего рода индивидуальные фильтры, как filter views в Таблицах), ветки комментариев, работа над книгой в браузере в Excel Online (но там функционал ограниченный, хоть и обновляется регулярно).

Чтобы работать над документом одновременно с другими пользователями, рабочую книгу Excel нужно хранить онлайн. Это предполагает облачное хранилище, а именно OneDrive, OneDrive for Business или SharePoint Online (в перспективе к списку может добавиться Dropbox).

Подробности - в этом фрагменте из книги "Магия таблиц" - ну а ее выход ожидается через несколько месяцев, а пока можно подписаться о уведомление о выходе тут:
https://www.mann-ivanov-ferber.ru/books/magiia-tablic/

PS А еще мы активно занимаемся обновлением нашей книги про Google Таблицы. Внутри обязательно будет материал и про новые формулы и про Google скрипты. Напишем вам, когда она будет доступна.

Автозамена (AutoCorrect). Если вам часто нужно вводить какое-то словосочетание, адрес, имейл и так далее — придумайте для него короткое обозначение и добавьте в список автозамены в Параметрах.
Параметры → Правописание → Параметры автозамены
Options → Proofing → AutoCorrect Options

Обратите внимание, что список автозамены один на все приложения Microsoft Office! Так что введенные вами варианты будут заменяться и в Outlook, Word и т.д.

Как выбрать половину значений из списка случайно?

Можно такой формулой:

=СЛЧИС()<0,5

Функция СЛЧИС / RAND возвращает случайное число от нуля до единицы. Так что в половине случаев оно будет меньше 0,5 - значит, такая формула примерно в половине случаев будет возвращать ИСТИНА / TRUE (так как сгенерированное число будет меньше 0,5), а в половине ЛОЖЬ / FALSE, и по этому признаку можно выбрать данные.

Формулы можно превратить в значения - если у вас Microsoft 365, то с помощью Ctrl+Shift+V, а во всех версиях - специальной вставкой (Ctrl+Alt+V).

Повторное применение фильтра

Вы поставили фильтр (Ctrl + Shift + L, кстати). Поменяли что-то в данных. И вот некоторые строки, в которых вы вносили изменения, уже фильтрации не соответствуют. Если добавились новые данные в конце таблицы — они тоже не отфильтруются автоматически. Как отобразить актуальные данные?
Не нужно отключать фильтр и настраивать снова.

Просто нажимайте Ctrl + Alt + L / . Или кнопку "Повторить" (Reapply) на ленте инструментов рядом с кнопкой фильтра (на вкладке "Данные" / Data).