Дано: есть данные за несколько лет с выручкой (или чем-то еще) по дням.

Задача: посмотреть на сезонность, какой месяц "лучше", какой "хуже". На сезонность — то есть на январь за все годы, на февраль за все годы, и так далее.

Для этой задачи извлечем из чемоданчика всемогущий мультитул — сводную таблицу. По умолчанию в сводной даты группируются по годам-кварталам-месяцам, то есть мы смотрим на данные в рамках каждого года. А нам нужно убрать этот верхний уровень, смотреть только на уровень месяцев (или кварталов, если вам нужно сезонность на этом уровне). Для этого группируем данные сами - только по месяцам.
Это можно сделать на ленте: Анализ сводной таблицы — Группировка по полю
или в контекстном меню — щелкаем по полю с датами в сводной правой кнопкой мыши и нажимаем "Группировать" (или нажимаем Г на клавиатуре)

После группировки можно посмотреть на сумму показателя по месяцам, а можно на среднее значение. Еще раз уточняем: теперь это данные за все январи в периоде , то есть мы не смотрим на динамику во времени, а смотрим на сезонность! Если нам нужна динамика от месяца к месяцу, то нужна группировка и по годам, и по месяцам, как было изначально при построении сводной (в большинстве версий Excel поле с датами само группируется в таком формате при его переносе в область строк)

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

Неужели это свершилось!

Теперь в Word и Excel можно, как в Google Документах/Таблицах, вставлять текст и значения ячеек без форматирования (опция уже какое-то время выкатывается у тех, кто получает обновления, то есть у подписчиков Microsoft 365).

Сочетание клавиш такое же, как в сервисах Google - Ctrl + Shift + V.

Очень удобно: не нужно лезть в контекстное меню и там выбирать "Вставить только значения" или вызывать окно специальной вставки (Ctrl + Alt + V) и там выбирать "Значения" и нажимать ОК.

В Excel можно таким образом превратить формулы в значения в пару нажатий: скопировали Ctrl+C и вставили как значения Ctrl+Shift+V.

Отображаем только значки в ячейках - без чисел

В условном форматировании в Excel есть графические объекты, в том числе наборы значков (Icon Sets). Это 3, 4 или 5 значков: по умолчанию все числа в выделенном диапазоне делятся на 3, 4 или 5 равных частей и для каждой из них применяется соответствующий значок.

А если у нас, например, оценки по пятибалльной шкале, можно отображать только значки, без самих цифр!

Для этого нужно залезть в настройки правила условного форматирования и включить флажок "Показывать только значок" (Show Icon Only).
В настройки можно попасть:
- если сразу выбрать в списке наборов значков не готовый вариант, а "Другие правила" (More Rules) - тогда вы попадете в диалоговое окно с настройками
- через "Управление правилами" (Manage Rules) в Условном форматировании, как на видео.

Визуализируем выполнение плана с помощью гистограмм

В условном форматировании помимо значков есть и гистограммы (в английском языковом пакете называются более корректно — Data Bars, все-таки это горизонтальные столбики, а гистограммой мы привыкли называть вертикальные).

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

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

После вставки гистограммы можно включить опцию "Показывать только столбец" (Show Bar Only) в настройках правила, чтобы числа в ячейках не отображались и не "накладывались" на гистограммы. Так будет наряднее, и при этом формулы (и числа, которые они возвращают) никуда не денутся, просто не будут отображаться — а значит, при изменении данных гистограммы будут показывать актуальную картинку.

Будущее уже наступило, просто оно еще неравномерно распределено.

Лемур считает, что Уильям Гибсон говорил про разные версии Excel. Вот, например, появилась такая удобная и такая долгожданная вещь - фильтрация в выпадающих списках в проверке данных. Но доступна не всем :(

Это очень удобно, когда мы настраиваем выпадающие списки с большим количеством значений. Например, когда у нас есть прайс-лист, а там сотни товаров. И раньше нужно было листать длинный-длинный список значений, а сейчас можно ввести ключевое слово (название бренда, например), и список сразу отфильтруется, останутся только подходящие значения.

Увы, пока это удовольствие доступно только в Microsoft 365 и пока только тем, кто получает самые свежие обновления через бета-канал по программе Office Insider (подробнее тут). На самом деле, опция была анонсирована еще в январе. Но до сих пор эта ожидаемая многими (судя по комментариям в Tech Community Microsoft) опция до обычных пользователей не доехала. Будем ждать, когда это обновление будет доступно в Microsoft 365 у всех!

Ну а если хочется бесплатно и прямо сейчас... То эта красота была и есть в Google Таблицах!

Продолжим разговор про выпадающие списки. Хотя поиск в них пока доступен меньшинству, сами выпадающие списки (проверка данных) есть во всех версиях Excel.
И зачастую мы создаем их на основе списка (товаров/фамилий/городов и т.д.), который меняется (дополняется).
Как сделать, чтобы новые значения попадали в выпадающий список автоматически? И чтобы сами списки автоматически появлялись в новых строках вашей таблицы?

Решения могут быть разные в зависимости от того, на разных ли листах данные.

Данные на том же листе - превращаем справочник в Таблицу и ссылаемся на него.
Данные на другом листе и у вас Excel 2010+ - просто ссылаемся на другой лист.
Данные на другом листе и будут пополняться новыми и/или у вас Excel 2007 - ссылаемся через функцию ДВССЫЛ / INDIRECT или через именованный диапазон.

Обсуждаем все нюансы в видео:
https://youtu.be/Mf9fMmWPUCw

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

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

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

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