Магия Excel

Выделяем всю строку при наличии в столбце слова (буквы, текста) Тут на вебинаре прозвучал вопрос — как покрасить всю строку, если в одном из столбцов встречается какое-то слово (то есть не занимает ячейку целиком, просто знаком равно = сравнивать нельзя). Можно функцией ПОИСК или НАЙТИ. Первая работает без учета регистра, вторая — с. =НАЙТИ (что ищем; где ищем) На выходе — порядковый номер символа, позиция искомого значения в тексте для поиска. В тех случаях, когда они не находят искомое значение, возвращается ошибка. В случае условного форматирования при ошибках в формуле правило не сработает, а это то что нам нужно. Главное в условном форматировании — не запутаться со строками и столбцами. Формула в условном форматировании вводится один раз — при ее формировании учитывайте, что вы вводите формулу для левой верхней ячейки диапазона, а далее на все остальные ячейки она будет «протягиваться» (как если бы мы ввели формулу в B2 и далее скопировали и вставили во все остальные ячейки в трех столбцах). И ссылки на ячейки будут меняться, если они являются относительными. Смотрим на видео!

Ура! Теперь в Excel есть всплывающие подсказки, показывающие, чему равен фрагмент формулы. В Google Таблицах такое было давно. А в Excel можно было только вычислить фрагмент формулы нажатием F9, чтобы посмотреть, чему равна функция в конкретном случае или что скрывается за ссылкой. Но после нажатия F9 вычисленный фрагмент становится значением, и нужно выйти через Esc, чтобы он так и не остался фиксированным значением. А тут просто всплывающее значение. Можно посмотреть, чему равна: - одна из функций в формуле - сочетание функций, выражение внутри формулы - что хранится в той или иной ячейке - какой массив данных хранится в диапазоне или вычисляется функцией/частью формулы. Отключается и включается сочетанием Ctrl + Alt + P. Пока доступно только для участников программы Office Insider, ждем для всех!😺

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

Начиная с Excel 2016 можно вычислять минимальное и максимальное значение по условиям — например, максимальную сделку не вообще, а с определенным типом товара. Функции называются МАКСЕСЛИ / MAXIFS и МИНЕСЛИ / MINIFS. =МАКСЕСЛИ(максимальный_диапазон; диапазон_условия1; условие1; …) Максимальный диапазон — диапазон, в котором мы ищем максимальное число. В Google Таблицах эти функции тоже есть, названия у них там на английском при любом языке формул: MAXIFS, MINIFS.

Добавляем к дате день недели и выделяем выходные Допустим, мы с вами хотим видеть в каждой дате день недели - не "01.01.2023", как по умолчанию, а "01.01.2023 Вс". Для этого заходим в формат ячеек (Ctrl + 1) и добавляем к формату "ДДД" (DDD). Это краткое обозначение дня недели ("Вс"). Для полного ("Воскресенье") понадобится код "ДДДД" (DDDD). Ну а чтобы выделить цветом выходные (или другие дни) - воспользуемся условным форматированием (Conditional Formatting). Зададим правило с формулой, а в ней будем использовать функцию ДЕНЬНЕД / WEEKDAY. Она возвращает порядковый номер дня недели. Чтобы нумерация была привычной для нас с вами, добавьте второй аргумент, равный двойке: =ДЕНЬНЕД (ячейка с первой датой в диапазоне; 2) Тогда понедельнику будет соответствовать единица (иначе - воскресенью), вторнику - двойка и так далее. И остается добавить условие - день недели у нас должен быть больше 5 (то есть 6 или 7, суббота или воскресенье), чтобы ячейка заливалась цветом. Все показываем на видео!

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

Изменяем числа или даты в пару кликов в Excel: специальная вставка Допустим, нам нужно изменить все числа в диапазоне (сделать отрицательными, то есть умножить на минус один, или вычесть налог, то есть умножить на (1-ставка налога)); изменить все даты (прибавить неделю, например). В Excel это можно сделать без формул, с помощью специальной вставки. Показываем в коротком (3 минуты) видео, как. Сочетание клавиш для вызова окна специальной вставки: WIndows: Ctrl + Alt + V Mac: ⌘ + ⌃ + V

Немного Excel-экзотики: меняем цвет линий сетки на листе. Вдруг вам надоели серые линии? Или хочется ввести в легкий ступор коллегу, которая (-ый) подумает, что ваш отчет немного заколдован и Excel выглядит как-то не так, как должен бы? Параметры - Дополнительно - Цвет линий сетки Options - Advanced - Gridline color В этом разделе ("Параметры отображения листа", Display options for this worksheet) и выше в разделе "Параметры отображения книги" (Display options for this workbook) есть и более практичные вещи. Например, если вы получили от кого-то книгу, где нет ярлыков листов, это не значит, что лист там один (даже если лист один, но ярлыки отображаются - его ярлык видно), стоит заглянуть сюда - тут есть соответствующий флажок.

Задача от подписчицы: поиск по двум критериям (двум оценкам в матрице компетенций) По двум оценкам поведенческих и профессиональных компетенций нужно выставить комплексную оценку. То есть если Лемур получает оценку профессиональных компетенций A и поведенческих — C, то его комплексная оценка — B. Тут нам поможет функция ИНДЕКС / INDEX. Она работает и с двумерными массивами. Если аргумент — это диапазон (или таблица), то можно извлекать элемент по номерам строки и столбца. А эти самые номера мы можем искать с помощью функции ПОИСКПОЗ / MATCH. В общем виде структура формулы будет такой: =ИНДЕКС(диапазон ;ПОИСКПОЗ для поиска номера строки;ПОИСКПОЗ для поиска номера столбца)) Более подробно: =ИНДЕКС(диапазон ;ПОИСКПОЗ(значение, которое ищем в столбце;столбец для поиска;0);ПОИСКПОЗ(значение, которое ищем в строке; строка для поиска;0))

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

Редактируем скрытую ячейку, не раскрываястроки/столбцы Вам нужно изменить значение ячейки, не раскрывая строку/столбец с ней (чтобы потом не скрывать снова)? Есть два способа: 1 Ввести адрес ячейки в поле "Имя" (слева от строки формул); 2 Ввести его в окне "Переход" (вызывается клавишей F5) После любого из двух действий можно редактировать значение в строке формул (или просто его увидеть - иногда нужно именно это). Смотрим на видео! P.S. А вот в Google Таблицах такая магия не сработает, увы 😿