Бизнесхак на каждый день. Экономьте время, деньги и силы

Используйте «Умные таблицы» Excel

Казалось бы, любой набор данных в Excel – это таблица. Во всяком случае, мы воспринимаем это именно так. Но для Excel это просто диапазон, который можно форматировать как таблицу нажатием кнопки на ленте инструментов, что делает работу ощутимо удобнее.

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

Что изменится?

Во-первых, теперь формулы станут читаться легче, в них будут отображаться названия столбцов, а не адреса ячеек:

Во-вторых, при добавлении новых данных формулы будут протягиваться автоматически.

В-третьих, у таблицы можно добавить строку итогов – и в любой момент увидеть статистику, например среднюю цену или общую стоимость. Для этого надо выделить любую ячейку таблицы и отметить в разделе «Конструктор» пункт «Строка итогов».

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

Сценарный анализ в excel – 1. Таблицы данных

В старых версиях Excel этот инструмент назывался не «Таблицы данных», а «Таблицы подстановки». Суть же не изменилась.

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

Рассмотрим этот инструмент на примере. Допустим, у нас есть простая модель расчета прибыли от продаж (серым обозначены входящие параметры, а в белых ячейках – расчетные показатели):

Если мы хотим посмотреть влияние изменения одного параметра (например, проанализировать влияние изменения объема производства) на несколько расчетных показателей, нужно поставить в соседние ячейки одной строки ссылки на ячейки с расчетными показателями, а в столбец перечислить разные сценарии по входному параметру:

В этом примере в строке стоят ссылки на ячейки с выручкой, себестоимостью и прибылью от продаж. В столбце – разные варианты по объему производства. После того как данные готовы, необходимо выделить всю таблицу (в данном случае с ячейки «Количество товаров» и до правого нижнего угла), на ленте инструментов выбрать:

Данные → Анализ «Что если» → Таблица данных

и в появившемся диалоговом окне в пункте «Подставлять данные по строкам» (то есть наши варианты по количеству производимых товаров) поставить ссылку на ячейку с количеством товаров в нашей модели – в примере это ячейка B3.

После этого в таблице будут отображены разные сценарии изменения выручки, себестоимости и прибыли от продаж при шести вариантах объема производства:

Вы наверняка обратили внимание, что в диалоговом окне был и пункт «Подставлять значения по столбцам», который остался незаполненным. Есть возможность делать таблицы данных с двумя входящими параметрами – для этого и нужны оба пункта. Тогда конечный параметр будет только один, а не три (как в примере) или более.

Допустим, вы хотите рассмотреть разные сценарии по прибыли от продаж при изменении удельной себестоимости и объема производства. Объем производства оставим в столбце, а в строке приведем разные варианты по удельной себестоимости. В левую верхнюю ячейку заготовки поставим ссылку на ячейку с конечным показателем – прибылью от продаж:

Выделяем таблицу и снова вызываем инструмент «Таблица данных». Но теперь в диалоговом окне мы ставим ссылки на две ячейки исходной модели – с удельной себестоимостью и объемом производства:

И получаем результат:

Сценарный анализ – 2. Подбор параметра

Еще один полезный встроенный инструмент Excel для проведения анализа «Что если» – «Подбор параметра». Его можно найти там же, где и таблицы данных:

Данные → Анализ «Что если» → Подбор параметра.

Подбор параметра позволяет получить ответ на вопрос:

Каким должен быть входящий параметр, чтобы получить заданный результат?

Или, если рассматривать пример – модель из предыдущего раздела:

Какой должна быть себестоимость единицы товара (при прочих равных), чтобы получить прибыль, равную 58 000 рублей?

Вызовем инструмент «Подбор параметра», чтобы получить ответ:

В первом пункте мы указываем ссылку на ячейку с целевым показателем – в данном случае она была активна и подставилась автоматически (B9). Во втором пункте диалогового окна нужно указать желаемое целевое значение – мы хотим прибыль от продаж на уровне 58 тысяч. Изменять мы будем параметр в ячейке B5 – себестоимость единицы.

Нажимаем ОК и получаем результат. Можно сохранить его в таблице или вернуть исходные значения.

Как построить простой прогноз в Excel