Магия Excel

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

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

Хотите скрыть все объекты на графическом слое Excel? Нажмите Ctrl + 6. Это слой, по которому плавают диаграммы, срезы (умных таблиц и сводных) и временные шкалы (сводных таблиц), фигуры, изображения (но только те, что вставлены "над ячейками", а не "в ячейках"). Чтобы все это дело временно убрать, нажмите Ctrl + 6. А еще одно нажатие этого сочетания вернет все на место. Спасибо каналу "Финансовый Гиппогриф" за этот лайфхак 😺

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

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

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

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

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

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

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

Автозамена (AutoCorrect). Если вам часто нужно вводить какое-то словосочетание, адрес, имейл и так далее — придумайте для него короткое обозначение и добавьте в список автозамены в Параметрах. Параметры → Правописание → Параметры автозамены Options → Proofing → AutoCorrect Options Обратите внимание, что список автозамены один на все приложения Microsoft Office! Так что введенные вами варианты будут заменяться и в Outlook, Word и т.д.