Анпивот (Unpivot) — отмена свертывания, преобразование двумерной таблицы в плоскую, пригодную для построения сводной таблицы.

Эту операцию можно проделать в Power Query:
— Данные — "Из таблицы/диапазона" (выделяем предварительно исходную двумерную таблицу)
— Выделяем в редакторе Power Query те столбцы, которые должны остаться в новой таблице — у нас это "Компания".
— Преобразование — Отменить свертывание других столбцов (Transform — Unpivot Other Columns)

Альтернатива — формулой, если у вас Microsoft 365 с волшебными новыми функциями. Про формулу напишем отдельно!

А вот формула-монстр для отмены свертывания (анпивот) "в режиме реального времени". Но только с новыми функциями 365.

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

=ГСТОЛБИК(ПОСТОЛБЦ(ЕСНД(РАЗВЕРНУТЬ(первый столбец;;СЧЁТЗ(заголовки столбцов, для которых отменяется свертывание));первый столбец));
ПОСТОЛБЦ(ЕСНД(РАЗВЕРНУТЬ(заголовки столбцов;СЧЁТЗ(первый столбец - для подсчета числа значений в нем));заголовки столбцов));
ПОСТОЛБЦ(данные столбцов))

Тут на верхнем уровне три вертикальных массива, которые склеиваются в одну таблицу функцией ГСТОЛБИК / HSTACK.

Последний аргумент с функцией ПОСТОЛБЦ / TOCOL — это просто данные (суммы в нашем случае), которые превращаются из двумерного массива в один столбик за счет этой самой TOCOL.

Первые два аргумента — это столбцы с компаниями и с заголовками. Здесь работает функция EXPAND / РАЗВЕРНУТЬ: она добавляет к массиву с названиями компаний два лишних пустых столбца, заполненных ошибкой Н/Д. Которую мы заменяем на исходные данные с помощью ЕСНД / IFNA. Иначе говоря, мы клонируем каждое название компании три раза (по числу столбцов с данными), потом превращаем в одномерный массив с помощью ПОСТОЛБЦ / TOCOL.

В отдельном сообщении выше файл с формулой с пошаговым разбором отмены свертывания — забирайте, изучайте и пользуйтесь!

Самый компактный вариант для визуализации прогресса в Excel — гистограмма (условное форматирование)

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

Сделать такой простенький прогресс-бар можно так:
— Сослаться на фактическое значение в отдельной ячейке, куда мы вставим гистограмму;
— Вставить гистограмму в эту ячейку по вкусу;
— Задать в настройках правила условного форматирования максимальное значение как план/цель — тогда гистограмма будет шириной во всю ячейку, когда факт будет равняться плану. Иначе — будет показываться % выполнения;
— Можно включить опцию "Показывать только столбец", фактическое значение нам здесь не нужно — оно и так есть в исходной ячейке. А тут пусть будет только наш прогресс-бар.

Отличия по строкам
Вы хотите быстро выделить цветом ячейки, в которых план отличается от факта (один столбец от другого — в общем случае)?

1 Выделяем столбцы (можно быстро быстро выделить их сочетанием Ctrl + Shift + стрелка вниз)
2 Ctrl + G —> Выделить (Special)
3 Отличия по строкам (Row differences)
4 Красим выделенные ячейки нужным цветом. Готово!

Смотрим на GIF (без звука)

Видео для новичков: абсолютные и относительные ссылки в Excel

Друзья, вашему вниманию видео по фундаментальной для понимания табличных формул (ссылки работают +- одинаково и в Excel, и в Google Таблицах, и в других офисных пакетах) теме:
— Абсолютные и относительные ссылки
— Смешанные ссылки
— Стиль ссылок R1C1
— Режим "Показать формулы"
— Бонус: почему объединенные ячейки могут портить жизнь при работе с формулами

8 минут со звуком:
https://youtu.be/jCgDpHkmE0s

Объединяем умные таблицы в одну: формулы и Power Query

В видео разбираем такую задачу: собрать данные из нескольких умных таблиц.

Если у вас Microsoft 365, то можно наслаждаться новыми формулами и использовать функцию ВСТОЛБИК / VSTACK. Так же в видео разбираем, как с ее помощью в сочетании с функцией ФИЛЬТР / FILTER фильтровать данные "в режиме реального времени" и добавить к результату фильтрации заголовки.

Если версии 2010 и новее, то можно с помощью Power Query объединить таблицы в один запрос и далее анализировать данные вместе с помощью сводной таблицы или просто выгрузить на лист как одну таблицу.

Добавляем гистограммы в сводной таблице отдельным столбцом

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

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

Хотите скрыть все объекты на графическом слое Excel? Нажмите Ctrl + 6.

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

Чтобы все это дело временно убрать, нажмите Ctrl + 6. А еще одно нажатие этого сочетания вернет все на место.

Спасибо каналу "Финансовый Гиппогриф" за этот лайфхак 😺

И еще один совет по работе с объектами, которые находятся "над ячейками" (на графическом слое).

Если вы будете их перемещать или менять их размеры с нажатой клавишей Alt, то они будут выравниваться по границам ячеек, а не произвольно.
Этот прием очень помогает аккуратно выравнивать диаграммы 😸

Автосумма: одним движением суммы по всем столбцам/месяцам.

Сочетание клавиш Alt + = позволяет получить сумму быстро, не вводя руками функцию СУММ / SUM.
Если выделить ячейку под столбцом с числами и нажать Alt + =, то получим сумму по этому столбцу (одну функцию СУММ).
Уточняем: речь про "просто Alt", то есть левый Alt. Правый Alt заменяет сочетание Ctrl+Alt и в сочетании с плюсом-минусом будет менять масштаб листа.

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

Нужно напечатать много строк, которые точно не влезут на одну страницу? Закрепляем строки, чтобы заголовки были на каждой странице при печати!

Разметка страницы — Печатать заголовки — Сквозные строки
Page Layout — Print Titles — Rows to repeat at top

И вводим / выделяем строки, которые нужно выводить на каждой странице при печати. Со столбцами, разумеется, работает аналогично 😺

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

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

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

Добавляем к дате день недели и выделяем выходные

Допустим, мы с вами хотим видеть в каждой дате день недели — не "01.01.2024", как по умолчанию, а "01.01.2024 Пн".

Для этого заходим в формат ячеек (Ctrl + 1) и добавляем к формату "ДДД" (DDD). Это краткое обозначение дня недели ("Пн"). Для полного ("Понедельник") понадобится код "ДДДД" (DDDD).

Ну а чтобы выделить цветом выходные (или другие дни) — воспользуемся условным форматированием (Conditional Formatting).
Зададим правило с формулой, а в ней будем использовать функцию ДЕНЬНЕД / WEEKDAY.
Она возвращает порядковый номер дня недели. Чтобы нумерация была привычной для нас с вами, добавьте второй аргумент, равный двойке:

=ДЕНЬНЕД (ячейка с первой датой в диапазоне; 2)

Тогда понедельнику будет соответствовать единица (иначе неделя будет начинаться с воскресенья, если пропустить второй аргумент функции), вторнику — двойка и так далее.

И остается добавить условие — день недели у нас должен быть больше 5 (то есть 6 или 7, суббота или воскресенье), чтобы ячейка заливалась цветом.

Режим перехода в конец (End mode)

Нажмите клавишу End — и в строке состояния появится надпись "Режим перехода в конец" (End mode)

Это значит, что теперь при нажатии на стрелку на клавиатуре вы переместитесь в конец текущей области (в направлении стрелки). Если активная ячейка пустая или пограничная (крайняя) в диапазоне — то вы переместитесь к следующей непустой ячейке или краю листа.

Заполняем промежуточные шаги с помощью прогрессии

Допустим, вы знаете первое значение и то, к которому нужно прийти. Выделите весь диапазон от первого значения до последнего и вызовите инструмент "Прогрессия" (Series):
Главная — Заполнить (кнопка со стрелкой вниз в правой части ленты) — Прогрессия

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

Средневзвешенная цена

Если у нас есть данные по продажам товаров с разными ценами, мы не можем считать среднюю цену просто функцией СРЗНАЧ / AVERAGE. Ведь мы не будем учитывать число проданных товаров, и, как в примере, средняя цена будет некорректная.

= (650 + 1400 + 2200) / 3 = 1417.

Но мы продали совсем мало товаров за 2200 и за 1400! Фактическая средняя цена должна быть ближе к 650.

Поэтому правильно вычислить сумму продаж в деньгах и потом поделить на все штуки.

Сумму продаж в деньгах можно вычислить одной функций СУММПРОИЗВ / SUMPRODUCT — она перемножает числа из нескольких диапазонов и потом суммирует результаты. Останется только разделить результат на сумму проданных штук. И получится 752 — это уже похоже на правду!

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

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

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

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

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

Нужно выделить все формулы?

Нажимаем Ctrl+G

В открывшемся окне "Переход" нажимаем "Выделить" (Special)

Далее — "Формулы" (Formulas)

Готово. Можно теперь покрасить ячейки с формулами, если хочется.