А все ведь знают, что в разных текстовых редакторах, в браузере можно удалить весь текст от курсора и до конца слова сочетанием 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 с примером формулы для поиска по двум оценкам.

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

По двум оценкам поведенческих и профессиональных компетенций нужно выставить комплексную оценку.
То есть если Лемур получает оценку профессиональных компетенций 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 нет) можно ссылаться на пачку листов или использовать символ подстановки в названиях листов (правда, только один раз 🤷🏻‍♂️).