6 потрясающих трюков Excel

Сводные таблицы

Пожалуй, это один из самых мощных аналитических инструментов в арсенале Excel. Сводные таблицы позволят Вам быстро агрегировать огромные объемы данных и динамически менять аналитические разрезы. И все это чудо - без единой формулы. Сводные таблицы - незаменимый помощник при анализе данных и составлении отчетов.

Как воспользоваться:

  1. Выбрать любую ячейку в диапазоне исходных данных.
  2. Найти на ленте вкладку "Вставка", а на ней - кнопку "Сводная таблица" и щелкнуть по ней.
  3. Проверить, что указанный в диалоговом окне диапазон совпадает с диапазоном исходных данных, и нажать "ОК".

Готово! Сводная таблица создана, правда, выглядит пока не лучшим образом. У Вас на экране сейчас должно быть что-то наподобие этого:

Пустая сводная таблица

Пустая сводная таблица и панель "Поля сводной таблицы"

И теперь, исходя из целей анализа, Вам необходимо разместить поля данных (перечислены списком в панели "Поля сводной таблицы") в каких-либо областях сводной таблицы - это можно сделать простым перетаскиванием поля в соответствующий квадрат в нижней части панели или установкой пометки в чек-боксе слева от названия поля.

Дополнительные материалы:

Flash Fill

Тут даже говорить нечего, лучше просто посмотрите видео ниже или прочитайте исходную статью:

Эта фишка пригодится при наведении порядка в записях, полученных из баз данных, и их подготовке к дальнейшему анализу. Работает в Excel 2013 и выше

Подбор параметра

Зачастую Вы уже знаете ответ, некоторую цифру, которую нужно отобразить в отчетах или использовать в вычислениях. Однако Вам нужно узнать, какие входные данные позволят получить этот ответ. Например, Вы хотите узнать, под какой процент нужно положить деньги в банк, чтобы через 10 лет получить миллион долларов. Надстройка "Подбор параметра" как раз и позволяет найти неизвестные параметры в подобных уравнениях, а также помогает при прогнозировании или определении целевых параметров.

Как воспользоваться:

  1. На вкладке "Данные" щелкнуть по кнопке Анализ "что если" и выбрать в открывшемся меню пункт "Подбор параметра".
  2. В открывшемся окне необходимо указать в поле "Установить в ячейке:" ссылку на ячейку с формулой, которую нужно рассчитать; в поле "Значение:" - финальное, известное Вам значение, которое нужно получить в результате расчета формулы; в поле "Изменяя значение ячейки" - ссылку на ячейку с изменяемым параметром формулы.
Данные -> Анализ "что если" -> Подбор параметра

Запуск надстройки "Подбор параметра"

Условное форматирование

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

Как применить:

  1. Выделить требуемый диапазон данных.
  2. На вкладке "Главная" щелкнуть по кнопке Условное форматирование и выбрать нужный Вам формат. Например, чтобы выделить все ячейки, значение которых превышает 100, выберите показанный на картинке пункт и введите число 100 в появившемся диалоговом окне. Перед тем, как нажать ОК Вы также сможете указать формат ячейки и шрифта, которые будут применены к удовлетворяющим условию данным.
Главная -> Условное форматирование -> Правила выделения ячеек

Включение условного форматирования

ИНДЕКС() + ПОИСКПОЗ()

Наверняка Вы знаете функцию ВПР(), позволяющую извлекать из таблицы данные из нужной Вам строки. Она, конечно, хороша, проста в применении но имеет ряд серьезных недостатков. А вот пара функций ИНДЕКС() + ПОИСКПОЗ() этих недостатков лишена и зачастую работает гораздо эффективнее.

Чтобы научиться пользоваться этой связкой, прочитайте следующие статьи:

График-водопад

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

График waterfall-chart позволяет наглядно отобразить вклад каждого элемента в общую картину

Пример графика waterfall

В Excel 2016 такой вид графика является встроенным и создается парой кликов, а вот обладателям предыдущих версий придется воспользоваться этой инструкцией.