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

Сегодня - две книги-сборника отдельных лайфхаков: одна по 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 с примером формулы для поиска по двум оценкам.

Задача от подписчицы: поиск по двум критериям (двум оценкам в матрице компетенций)

По двум оценкам поведенческих и профессиональных компетенций нужно выставить комплексную оценку.
То есть если Лемур получает оценку профессиональных компетенций A и поведенческих — C, то его комплексная оценка — B.

Тут нам поможет функция ИНДЕКС / INDEX. Она работает и с двумерными массивами. Если аргумент — это диапазон (или таблица), то можно извлекать элемент по номерам строки и столбца.
А эти самые номера мы можем искать с помощью функции ПОИСКПОЗ / MATCH.

В общем виде структура формулы будет такой:

=ИНДЕКС(диапазон ;ПОИСКПОЗ для поиска номера строки;ПОИСКПОЗ для поиска номера столбца))

Более подробно:

=ИНДЕКС(диапазон ;ПОИСКПОЗ(значение, которое ищем в столбце;столбец для поиска;0);ПОИСКПОЗ(значение, которое ищем в строке; строка для поиска;0))

Немного Excel-экзотики: меняем цвет линий сетки на листе.

Вдруг вам надоели серые линии? Или хочется ввести в легкий ступор коллегу, которая (-ый) подумает, что ваш отчет немного заколдован и Excel выглядит как-то не так, как должен бы?

Параметры - Дополнительно - Цвет линий сетки
Options - Advanced - Gridline color

В этом разделе ("Параметры отображения листа", Display options for this worksheet) и выше в разделе "Параметры отображения книги" (Display options for this workbook) есть и более практичные вещи. Например, если вы получили от кого-то книгу, где нет ярлыков листов, это не значит, что лист там один (даже если лист один, но ярлыки отображаются - его ярлык видно), стоит заглянуть сюда - тут есть соответствующий флажок.

Изменяем числа или даты в пару кликов в Excel: специальная вставка

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

В Excel это можно сделать без формул, с помощью специальной вставки. Показываем в коротком (3 минуты) видео, как.

Сочетание клавиш для вызова окна специальной вставки:
WIndows: Ctrl + Alt + V
Mac: ⌘ + ⌃ + V

Закрепление верхней строки в 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.
=НАЙТИ("ПРОСМОТР(";Ф.ТЕКСТ(ячейка))