Полезные формулы excel для контроля финансов

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

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

Информацию о полезных горячих клавишах Вы найдет в этой статье. О специализированных статистических функциях в Excel также читайте тут.

Наиболее популярные финансовые функции Excel

1. БС

(англ. FV) – возвращает будущую стоимость инвестиций при условиях постоянной процентной ставки, периодических постоянных платежей или единого общего платежа (в виде начальной инвестиции, определяемой аргументом «пс»): = БС(ставка;кпер;плт;[пс];[тип]), где:

«ставка» – процентная ставка за период (можно использовать ставку простого процента в случае с депозитами / вкладами): например, если ставка 6% годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 6%/12, при ежеквартальных выплатах аргумент «ставка» будет равен 6%/4;
«кпер» – общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент «кпер» будет равен 5*12;
«плт» – постоянная выплата за каждый период (выплаты – отрицательные значения, поступления – положительные значения): например, если ежемесячный платеж по кредиту составляет 10 000 руб., то аргумент «плт» будет равен -10 000.
«пс» – приведенная стоимость или первоначальная (инвестированная или вложенная) сумма (если аргумент опущен, предполагается значение 0 и необходимо обязательно указать аргумент «плт»),
«тип» – срок выплаты в начале (1) или в конце периода (0) (если аргумент «тип» опущен, предполагается значение 0, т.е. в конце периода).

2. БЗРАСПИС

(англ. FVSCHEDULE) – возвращает будущую стоимость инвестиций после начисления ряда сложных процентов (с переменной процентной ставкой, подойдет для вкладов с капитализацией процентов): =БЗРАСПИС(первичное;план), где:

«первичное» – стоимость инвестиции на текущий момент,
«план» – массив применяемых процентных ставок.

3. ПС

(англ. PV) – возвращает приведенную (текущую) стоимость инвестиции или займа (на основе постоянной процентной ставки): =ПС(ставка; кпер; плт; [бс]; [тип]), где:

«ставка» – процентная ставка за период (можно использовать ставку простого процента в случае с депозитами / вкладами): например, если ставка 6% годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 6%/12, при ежеквартальных выплатах аргумент «ставка» будет равен 6%/4;
«кпер» – общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент «кпер» будет равен 5*12;
«плт» – постоянная выплата за каждый период (выплаты – отрицательные значения, поступления – положительные значения): например, если ежемесячный платеж по кредиту составляет 10 000 руб., то аргумент «плт» будет равен -10 000.
«бс» – будущая стоимость или желаемый остаток средств после последнего платежа (если аргумент опущен, предполагается значение 0 и необходимо обязательно указать аргумент «плт»),
«тип» – срок выплаты в начале (1) или в конце периода (0) (если аргумент «тип» опущен, предполагается значение 0, т.е. в конце периода).

4. ЧПС

(англ. NPV) – возвращает чистую приведенную или дисконтированную стоимость инвестиции при условии серии периодических денежных потоков и с использованием ставки дисконтирования: =ЧПС(ставка; значение1; [значение2],…), где:

«ставка» – ставка дисконтирования за один период;
«значение1, значение2,…» – предполагаемые выплаты и поступления (должны быть равномерно распределены во времени, при этом выплаты должны осуществляться в конце каждого периода).

5. ЧИСТНЗ

(англ. XNPV) – возвращает чистую приведенную стоимость для денежных потоков, не обязательно являющихся периодическими: =ЧИСТНЗ(ставка;значения;даты), где:

«ставка» – ставка дисконтирования за один период;
«значение1, значение2,…» – предполагаемые выплаты и поступления (денежные потоки, соответствующие графику платежей, приведенному в аргументе “даты”. Если первое значение является затратами или выплатой, оно должно быть отрицательным. Все последующие выплаты дисконтируются на основе 365-дневного года. Ряд значений должен содержать по крайней мере одно положительное и одно отрицательное значение);
«даты» – график дат платежей, который соответствует платежам для денежных потоков.

6. ПЛТ

(англ. PMT) – возвращает сумму периодического платежа с постоянным процентом и постоянной суммой платежа (подходит для расчета платежей по аннуитету): =ПЛТ(ставка; кпер; пс; [бс]; [тип]), где:

«ставка» – процентная ставка за период (можно использовать ставку простого процента в случае с депозитами / вкладами): например, если ставка 6% годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 6%/12, при ежеквартальных выплатах аргумент «ставка» будет равен 6%/4;
«кпер» – общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент «кпер» будет равен 5*12;
«пс» – приведенная стоимость или первоначальная (инвестированная или вложенная) сумма (если аргумент опущен, предполагается значение 0),
«бс» – будущая стоимость или желаемый остаток средств после последнего платежа (если аргумент опущен, предполагается значение 0),
«тип» – срок выплаты в начале (1) или в конце периода (0) (если аргумент «тип» опущен, предполагается значение 0, т.е. в конце периода).

7. ПРПЛТ

(англ. IPMT) – возвращает сумму процентных платежей за указанный период только в том случае, если платежи в каждом периоде осуществляются равными частями: =ПРПЛТ(ставка;период;кпер;пс;[бс];[тип]), где:

«ставка» – процентная ставка за период (можно использовать ставку простого процента в случае с депозитами / вкладами): например, если ставка 6% годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 6%/12, при ежеквартальных выплатах аргумент «ставка» будет равен 6%/4;
«период» – период, для которого требуется найти платежи по процентам (число в интервале от 1 до аргумента “кпер”);
«кпер» – общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент «кпер» будет равен 5*12;
«пс» – приведенная стоимость или первоначальная (инвестированная или вложенная) сумма (если аргумент опущен, предполагается значение 0),
«бс» – будущая стоимость или желаемый остаток средств после последнего платежа (если аргумент опущен, предполагается значение 0),
«тип» – срок выплаты в начале (1) или в конце периода (0) (если аргумент «тип» опущен, предполагается значение 0, т.е. в конце периода).

8. СТАВКА

(англ. RATE) – возвращает ставку процентов по аннуитету за один период: =СТАВКА(кпер; плт; пс; [бс]; [тип]; [прогноз]), где:

«кпер» – общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент «кпер» будет равен 5*12;
«плт» – постоянная выплата за каждый период (выплаты – отрицательные значения, поступления – положительные значения): например, если ежемесячный платеж по кредиту составляет 10 000 руб., то аргумент «плт» будет равен -10 000;
«пс» – приведенная стоимость или первоначальная (инвестированная или вложенная) сумма (если аргумент опущен, предполагается значение 0);
«бс» – будущая стоимость или желаемый остаток средств после последнего платежа (если аргумент опущен, предполагается значение 0, а аргумент «пс» является обязательным);
«тип» – срок выплаты в начале (1) или в конце периода (0) (если аргумент «тип» опущен, предполагается значение 0, т.е. в конце периода);
«прогноз» – предполагаемая величина ставки (если аргумент “прогноз” опущен, предполагается значение 10%).

9. ЭФФЕКТ

(англ. EFFECT) – возвращает фактическую (или эффективную) годовую процентную ставку, если заданы номинальная годовая процентная ставка и количество периодов в году, за которые начисляются сложные проценты: =ЭФФЕКТ(номинальная_ставка;кол_пер), где:

«номинальная_ставка» — номинальная процентная ставка;
«кол_пер» – количество периодов в году, за которые начисляются сложные проценты.

10. ДОХОД

(англ. YIELD) – возвращает доходность ценных бумаг (облигаций), по которым производятся периодические выплаты процентов: =ДОХОД(дата_согл; дата_вступл_в_силу; ставка; цена; погашение, частота; [базис]), где:

«дата_согл» — дата расчета за ценные бумаги (дата продажи ценных бумаг покупателю, более поздняя, чем дата выпуска);
«дата_вступл_в_силу» — срок погашения ценных бумаг (момент, когда истекает срок действия ценных бумаг);
«ставка» — годовая процентная ставка для купонов по ценным бумагам;
«цена» — цена ценных бумаг на 100 рублей номинальной стоимости;
«погашение» — выкупная стоимость ценных бумаг на 100 рублей номинальной стоимости;
«частота» — кол-во выплат по купонам за год (для ежегодных – 1, для полугодовых — 2, для ежеквартальных — 4);
«базис» — используемый способ вычисления дня (если 0 или опущен, то используется американский (NASD) 30/360).

11. ВСД

(англ. IRR) – возвращает внутреннюю ставку доходности для потоков денежных средств (для платежей (отрицательные величины) и доходов (положительные величины), которые имеют место в следующие друг за другом и одинаковые по продолжительности периоды): =ВСД(значения; [предположения]), где:

«значения» – массив или ссылка на ячейки, содержащие ряд денежных выплат (отрицательные значения) и поступлений (положительные значения), происходящих в регулярные периоды времени (по крайней мере одна положительная и одна отрицательная величина);
«предположение» — величина, предположительно близкая к результату ВСД ( в большинстве случаев нет необходимости задавать аргумент “предположение”. Если он опущен, предполагается значение 10%).

12. МВСД

(англ. MIRR) – возвращает модифицированную внутреннюю ставку доходности, учитывая процент от реинвестирования средств (при котором положительные и отрицательные денежные потоки имеют разные значения ставки): =МВСД(значения;ставка_финанс;ставка_реинвест), где:

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

Дополнительные финансовые функции Excel

13. ИНОРМА (англ. INTRATE) – возвращает процентную ставку для полностью инвестированных ценных бумаг: =ИНОРМА(дата_согл;дата_вступл_в_силу;инвестиция;погашение;[базис]).

14. ЧИСТВНДОХ (англ. XIRR) – возвращает внутреннюю норму прибыли для графика поступлений денежных средств, не обязательно носящих периодический характер: =ЧИСТВНДОХ(значения;даты[;предположение]).

15. ДОХОДСКИДКА (англ. YIELDDISC) – возвращает годовой доход по ценным бумагам, на которые сделана скидка (например, по казначейским векселям): =ДОХОДСКИДКА(дата_согл;дата_вступл_в_силу;цена;погашение;[базис]).

16. ДОХОДПОГАШ (англ. YIELDMAT) – возвращает годовой доход по ценным бумагам, проценты по которым выплачиваются в срок погашения: =ДОХОДПОГАШ(дата_согл;дата_вступл_в_силу;дата_выпуска;ставка;цена;[базис]).

17. СКИДКА (англ. DISC) – возвращает норму скидки для ценных бумаг: =СКИДКА(дата_согл;дата_вступл_в_силу;цена;погашение;[базис]).

18. ЦЕНА (англ. PRICE) – возвращает цену за 100 рублей номинальной стоимости ценных бумаг, по которым производится периодическая выплата процентов: =ЦЕНА(дата_согл;дата_вступл_в_силу;ставка;доход;погашение,частота;[базис]).

19. АСЧ (англ. SYD) – возвращает величину амортизации актива за данный период, рассчитанную по сумме чисел лет срока полезного использования: =АСЧ(нач_стоимость;ост_стоимость;время_эксплуатации;период).

20. ЦЕНАКЧЕК (англ. TBILLPRICE) – возвращает цену за 100 рублей номинальной стоимости для казначейского векселя: =ЦЕНАКЧЕК(дата_согл;дата_вступл_в_силу;скидка).

4,572 total views, 2 views today

Следите за нашими обновлениями:

Источник

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

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

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

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

ПРОСТЫЕ ФУНКЦИИ И ФОРМУЛЫ MS EXCEL

Начнем изучение с наиболее простых функций из раздела «Мастер функций» MS Excel.

Функция «СУММ»

Данная функция помогает суммировать значения нескольких ячеек. Рассмотрим пример использования этой функции (рис. 1).

A	B	C	D	E	F	G	H
3	№ п/п	Наименование			Ед. изм.	Стоимость ед. изм., руб.	Расход	Сумма, руб.
4	1	2			3	4	5	6
5	1	Материал № 1			шт.	50,00	2,00	100,00
6	2	Материал № 2			кг	101,54	0,50	50,77
7	3	Материал № 3			кг	120,00	4,00	480,00
8	4	Материал № 4			м	150,00	1,50	225,00
9	5	Материал № 5			л	200,00	0,75	150,00
10	n
11	Итого							1006,00

Рис. 1. Пример использования функции «СУММ»

Необходимо посчитать стоимость материальных расходов, затраченных на единицу выпущенной продукции, если известна стоимость закупки единицы измерения и фактический расход каждого вида материала на изготовление единицы продукции (графы 4 и 5 таблицы, представленной на рис. 1). Итог по каждой позиции материала выведен в графе 6 путем перемножения фактического расхода на стоимость закупки.

«Итого» рассчитывают сложением всех подытогов по каждой позиции материала. Для этого используют функцию «СУММ» и выделяют диапазон ячеек с необходимыми значениями данных (в нашем случае — графа 6, которой в MS Excel соответствует столбец «Н»). Тогда формула приобретет следующий вид:

= СУММ(H5:H9), где H5:H9 — диапазон данных по графе 6 от материала № 1 до материала № 5.

Когда пользователю нужно рассчитать сумму значений ячеек или применить иную функцию, но при этом получить результат расчетов с округлением (например, без копеек), применяют функции «ОКРУГЛ», «ОКРУГЛВВЕРХ» и «ОКРУГЛВНИЗ». Как правило, эти функции не используют как самостоятельные, чаще их применяют в комплексе с другими функциями (например, с «СУММ»). В нашем случае по материалу № 2 сумма составляет 50,77 руб. (графа 6). Составим формулу для расчета итоговой суммы с учетом округления:

=ОКРУГЛ(СУММ(H5:H9);0), где «0» — число разрядов для округления.

Справочная информация о форматировании ячеек:

1. Чтобы установить количество знаков после запятой, нужно кликнуть правой кнопкой мыши по необходимой ячейке и выбрать «Формат ячеек», где определяется категория формата: числовой, текстовый, процентный, дата и др. (в нашем случае для граф 4–6 нужен числовой формат), а затем устанавливается количество десятичных знаков (для рассматриваемого примера — 2).

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

2. Чтобы применить конкретный формат одной ячейки к другим ячейкам, используют функцию «Формат по образцу», представленную на вкладке «Главная» основного меню.

3. Для выравнивания информации в ячейке можно обратиться к «Формату ячеек» и во всплывающем диалоговом окне выбрать «Выравнивание» или воспользоваться одноименной функцией во вкладке «Главная» основного меню (рис. 2). Данная функция позволяет определить направление (ориентацию) текста, его расположение в ячейке. При выборе «перенос по словам» текст ячейки не будет выходить за ее пределы.

Функция «СУММЕСЛИ»

Функция также предназначена для суммирования значений ячеек. Отличительная особенность — назначение конкретного условия (критерия) отбора. Для определения условия используются символы («˃», «<» и др.) и непосредственно текстовые значения ячеек. Рассмотрим пример применения функции «СУММЕСЛИ» (рис. 3).

В таблице с исходными данными, приведенной на рис. 3, отображены расходы предприятия по двум обособленным подразделениям (ОП) — г. Москва и г. Липецк. Суммарные расходы по этим подразделениям составляют 4924 руб. Необходимо рассчитать расходы каждого подразделения. Для этого воспользуемся функцией «СУММЕСЛИ», формула которой имеет следующий вид:

=СУММЕСЛИ(диапазон;критерий;[диапазон суммирования]).

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

Для рассматриваемого примера (см. рис. 3) формулы приобретут следующий вид:

г. Москва:

=СУММЕСЛИ(F17:F24;»Москва»;E17:E24) = 2750 руб.;

г. Липецк:

=СУММЕСЛИ(F17:F24;»Липецк»;E17:E24) = 2174 руб.

Рассмотрим работу формулы на примере обособленного подразделения в г. Москва:

первый диапазон ячеек (F17:F24) — это столбец для отбора, где представлены наименования подразделений; критерий отбора в данном случае — Москва;
второй диапазон (E17:E24) — столбец с суммами расходов, из которых программа выберет те, которые имеют отношение только к критерию отбора, и просуммирует их.

Как отмечено ранее, диапазон суммирования (в формуле указан в квадратных скобках) не является обязательным к заполнению. Например, на основании исходных данных, представленных в таблице на рис. 3, необходимо посчитать сумму расходов в размере 1000 руб. Тогда формула приобретет следующий вид:

=СУММЕСЛИ(E17:E24;1000) = 3000 руб.

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

Функции «ЕСЛИ» и «СЧЕТЕСЛИ»

Данные функции используют при установлении определенных условий или критериев.

Функция «СЧЕТЕСЛИ» предназначена для расчета количества ячеек по заданному критерию в формуле и имеет следующий вид:

=СЧЕТЕСЛИ(диапазон;критерий).

Функция «ЕСЛИ» позволяет сравнивать значения и в зависимости от результата выводить итог при верном или неверном сравнении. Формула выглядит следующим образом:

=ЕСЛИ(лог_выражение;[значение_если_истина];[значение_если_ложь]).

Рассмотрим пример применения данных функций (рис. 4).

Для рассматриваемого примера необходимо определить, опаздывал ли сотрудник Иванов И. И. на работу, при условии, что рабочий день согласно трудовому распорядку предприятия начинается в 9 утра. Для этого в графе «Примечание» нужно установить факт наличия опозданий. С этой целью применяем формулу:

=ЕСЛИ(G40>F40;»опоздание»;»-«), где необходимым условием к выполнению является превышение значения ячеек «G» (время фактического зафиксированного прибытия работника) над значением ячеек «F» (нормативное время прибытия).

Если неравенство выполняется, функция «ЕСЛИ» установит в ячейках «Н» — «опоздание»; если неравенство не выполняется, будет установлен прочерк, который показывает, что факт нарушения трудовой дисциплины не выявлен.

Для определения количества опозданий воспользуемся функцией «СЧЕТЕСЛИ»:

=СЧЕТЕСЛИ(H40:H47;»опоздание») = 2, где функция отбирает ячейки в диапазоне H40:H47 со значением «опоздание» и выводит их количество. В нашем случае Иванов И. И. опоздал на работу дважды, что и посчитала указанная функция.

Дополнительно отметим еще несколько функций с критериями: «ЕСЛИОШИБКА», «СЧЕТЕСЛИМН» и «СЧЕТЗ».

«ЕСЛИОШИБКА» возвращает значение, если вычисление по формуле выдает ошибку, в противном случае — возвращает результат формулы:

=ЕСЛИОШИБКА(значение;значение_если_ошибка).

«СЧЕТЕСЛИМН» — функция, похожая на «СЧЕТЕСЛИ», единственное отличие заключается в возможности применения нескольких критериев. Если бы в рассматриваемом примере (рис. 4) не провели предварительный отбор по конкретному сотруднику и по графе 2 встречалось бы несколько сотрудников, то для определения количества опозданий для каждого сотрудника в отдельности нужно было применять функцию «СЧЕТЕСЛИМН».

«СЧЕТЗ» — наиболее простая функция среди рассмотренных, которая рассчитывает количество непустых ячеек в заданном для анализа диапазоне.

Функции «МИН» и «МАКС»

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

На основании исходных данных таблицы, представленной на рис. 4, определим максимальное и минимальное время прибытия на работу сотрудника Иванова И. И.:

=МИН(G40:G47) = 8:24:11;

=МАКС(G40:G47) = 9:02:14.

Функция «ЧИСТРАБДНИ»

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

=ЧИСТРАБДНИ(нач_дата;кон_дата;[праздники]), где начальная и конечная дата являются обязательными условиями для заполнения, а праздники заполняются при необходимости.

Рассмотрим пример определения количества рабочих дней за период на основании таблицы, представленной на рис. 5.

Определим количество рабочих дней за период с 01.07.2018 по 31.07.2018. Известно, что в указанном месяце не было нерабочих праздничных дней. Тогда формула расчета будет иметь следующий вид:

=ЧИСТРАБДНИ(B63;C63) = 22 рабочих дня.

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

=ЧИСТРАБДНИ(B64;C64;C66) = 20 рабочих дней.

Функция «СРЗНАЧ»

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

Рассчитаем на основании исходных данных таблицы, отраженной на рис. 4, среднее время прибытия сотрудника на работу. Формула будет иметь следующий вид:

=СРЗНАЧ(G40:G47) = 8:50:13.

Часто функцию «СРЗНАЧ» используют для расчета среднего уровня заработной платы. Рассмотрим соответствующий пример с числовыми данными (рис. 6).

Таблица на рис. 6 содержит сведения о зарплате каждого сотрудника. Нужно рассчитать средний уровень зарплаты среди представленных сотрудников:

=СРЗНАЧ(D77:D82) = 55 222,39 руб.

Данная формула рассчитала среднеарифметическое по диапазону ячеек с суммами заработных плат. Аналогичный результат получим, разделив итоговую сумму (331 334,34 руб.) на количество сотрудников (6 чел.).

АНАЛИЗ ДАННЫХ С ПОМОЩЬЮ ИНСТРУМЕНТА «СВОДНЫЕ ТАБЛИЦЫ»

В Microsoft Excel можно найти разные инструменты для анализа данных, однако широкое распространение получил инструмент формирования сводных таблиц, который необходим для обобщения и консолидации баз данных. Под базой данных понимают как таблицу из любого файла MS Excel, так и базу данных из внешнего носителя информации (например, 1С).

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

Для построения сводной таблицы при помощи MS Excel нужно определить исходную таблицу или базу данных. Далеко не каждая таблица может подойти для построения сводной таблицы, поэтому настоятельно рекомендуем учитывать основные требования, предъявляемые к исходной базе данных:

в заголовках столбцов (шапке) исходной таблицы не должно быть объединенных ячеек и столбцов без наименования или с одинаковыми наименованиями;
в таблице исходной базы данных не должно быть пустых строк (пустые ячейки допустимы). В противном случае MS Excel по умолчанию воспримет это концом таблицы, и все данные, находящиеся после пустой строки, не попадут в сформированную сводную таблицу;
должны отсутствовать объединенные ячейки внутри таблицы, при их наличии консолидация данных невозможна.

Пример использования инструмента «Сводные таблицы»

Рассмотрим пример использования инструмента MS Excel «Сводные таблицы» на основании исходных данных, приведенных в табл. 1 .

Таблица 1. Исходные данные для применения инструмента MS Excel «Сводные таблицы»
Наименование подразделения	Квартал	Месяц	Выручка, тыс. руб.
ОП Москва	1	Январь	30 157
ОП С.-Петербург	1	Январь	25 400
ОП Саратов	1	Январь	22 100
ОП Курск	1	Январь	18 100
ОП Москва	1	Февраль	33 500
…

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

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

Этап 1. Форматирование исходного диапазона данных для придания анализируемому объему данных значения таблицы (необязательный пункт). Это возможно с помощью функции «Форматировать как таблицу» командной строки «Главная». В данном разделе MS Excel предлагает разные стили оформления, из которых можно выбрать любой понравившийся, так как для построения сводных таблиц стиль не имеет никакого значения.

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

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

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

А. Н. Дубоносова, заместитель управляющего директора по экономике и финансам

Источник