Финансовые функции в Excel. Полезные формулы Excel для контроля финансов Вы научитесь применять Excel для решения профессиональных задач

Или . Чтобы воспользоваться автоматической установкой Вам потребуются права администратора. Если прав администратора нет - ничего страшного, ручная установка не вызовет проблем. Инструкция по установке ниже:

Для тех, у кого есть права администратора

1. Скачайте надстройку
2. Запустите разархивированный файл (Установка и удаление надстройки) из папки «Автоматическая установка»
3. Готово

Для тех, кто не хочет лишний раз беспокоить сисадмина:)

1. Скачайте надстройку
2. Разархивируйте сохраненный файл
3. Закройте все приложения MS Office (Word, Excel, Outlook и др.), предварительно сохранив открытые файлы
4. Запустите разархивированный файл Excel «Установка и удаление надстройки» из папки «Ручная установка»
5. Разрешите запуск макросов при запуске файла и кликните по кнопке «Установить надстройку Финансист»
6. Перезагрузите Excel (закройте и заново откройте)
7. Готово

Если не получилось, можно действовать так:

Как обновить надстройку

Автоматическое обновление. Для тех, у кого есть права администратора на компьютере

1. Скачайте надстройку
2. Разархивируйте сохраненный файл в новую папку
3. Закройте все приложения MS Office (Word, Excel, Outlook и др.), предварительно сохранив открытые файлы
4. Запустите разархивированный файл «Установка и удаление надстройки» (из папки «Автоматическая установка»)
   1. в открывшемся окне выбираем опцию «Удалить надстройку» -> кнопка «Выполнить»
   2. запускаем этот же файл («Установка и удаление надстройки») повторно, опция «Добавить надстройку» -> кнопка «Выполнить»
5. Перезагрузите Excel (закройте и заново откройте)
6. Готово

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

1. Скачайте надстройку
2. Разархивируйте сохраненный файл
3. Закройте все приложения MS Office (Word, Excel, Outlook и др.), предварительно сохранив открытые файлы
4. Из папки «Ручная установка» запустите разархивированный файл Excel «Установка и удаление надстройки»
5. Разрешите запуск макросов при запуске файла и кликните по кнопке «Удалить надстройку Финансист» (файл удалит старую версию)
6. Кликните по кнопке «Установить надстройку Финансист»
7. Перезагрузите Excel (закройте и заново откройте)
8. Готово

Ответы на вопросы

Что такое надстройка «Финансист»? Надстройка «Финансист» - это программа, добавляющая в Excel дополнительные команды и функции, которые будут полезны в работе финансовому директору и другим сотрудникам финансовой службы. В стандартной версии Excel такого функционала нет, либо он запрятан так далеко, что использовать его не слишком удобно.

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

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

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

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

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

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

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

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

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

	№ п/п	Наименование			Ед. изм.	Стоимость ед. изм., руб.	Расход	Сумма, руб.
		Материал № 1
		Материал № 2
		Материал № 3
		Материал № 4
		Материал № 5
	Итого

Рис. 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.

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

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

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

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

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

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

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

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

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

Материал публикуется частично. Полностью его можно прочитать в журнале

Модульный курс Excel предназначен для углубленного изучения табличного редактора из пакета Microsoft Office с нуля. Он состоит из двух блоков - курса для начинающих и продвинутого курса. Если у вас совсем нет опыта применения MS Excel, но вам предстоит активно использовать его функции в профессиональной деятельности, вам стоит выбрать именно этот курс, построенный по принципу «от простого к сложному».

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

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

Модульный курс MS Excel будет полезен

• Слушателям, не имеющим опыта применения Excel;
• Слушателям с опытом применения Excel, желающим повысить эффективность работы с приложением, систематизировать знания и навыки;
• Студентам и аспирантам ВУЗов;
• Начинающим бухгалтерам, экономистам и финансистам;
• Менеджерам и офисным сотрудникам любого звена;
• Операторам и аналитикам big data;
• Социологам, маркетологам, специалистам по статистике и обработке данных;
• Исследователям и научным работникам;
• SEO и IT-специалистам.

Обучение на модульном курсе Excel поможет вам освоить

• Интерфейс, панели и инструменты Excel;
• Основные термины, понятия и операции;
• Базы, листы, списки и книги Excel;
• Инструменты для ввода, обработки, поиска, фильтрации, форматирования и группировки данных по выбранным параметрам;
• Сводные таблицы, графики и диаграммы;
• Спектр средств для анализа больших объемов данных;
• Методы вычисления, прогнозирования и аналитики;
• Функции: логические, математические, статистические, текстовые, функции массивов;
• Формулы и макросы.

Вы научитесь применять Excel для решения профессиональных задач

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

Популярные темы Excel на базовом курсе

Помимо перечисленных выше тем рассматриваются функции ВПР и ГПР (вертикальный и горизонтальный поиск результата), эти функции необходимы в подавляющем числе случаев при работе с массивами данных в excel. Практически все курсы эксель в Москве дают темы впр сводных таблиц уже на базовом курсе, хотя сложность данной темы уже довольно высокая. При изучении excel используется интерактивная программа обучения, что дает возможность любому учащемуся принимать деятельное и активное участие в процессе обучения. Практика по excel проходит непосредственно в ходе учебного процесса. Каждый учащийся работает на индивидуальном компьютере, выполняя задания, предлагаемые преподавателем. Весь учебный процесс в программе отображается на интерактивной доске, что позволяет быстро и удобно освоить все вычислительные алгоритмы и инструменты оформления программы. Все практические примеры, разбираемые на занятиях и практически выполняемые, взяты из реальной жизни и максимально соответствуют тем задачам, которые приходится выполнять в повседневной жизни большинству специалистов.

Запишитесь на модульный курс Excel в Москве прямо сейчас и получите ценные навыки и серьезное преимущество при трудоустройстве уже через 10 занятий!

Учебная программа курса

Базовый уровень

Занятие 1

Табличный процессор MS Excel . Настройка окна программы

Табличный процессор Microsoft Excel. Назначение программы. Интерфейс программы Настройка интерфейса: панели быстрого доступа, ленты, области заголовков столбцов. Масштаб.

Ввод и редактирование информации.

Ввод информации.

Редактирование данных(замена, удаление, добавление, исправление ошибок).

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

Работа с листами (вставка, перемещение, копирование, удаление).

Изменение структуры таблицы.

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

Объединение ячеек.

Форматирование ячеек:

Работа со шрифтами, выравнивание содержимого ячеек, поворот текста, настойка отступов.

Копирование формата по образцу. Очистка формата.

Создание границ для таблицы(использование кнопки границ, вкладки граница и рисование отдельных границ).

Способы создание заливки для ячеек.

Занятие 2

Создание числовых последовательностей.

Создание списков.

Ограничение на ввод данных.

Накладывание условия на ввод данных.

Формирование сообщения ошибки при вводе данных.

Формирование сообщения на ввод данных.

Работа с форматами данных.

Типы данных и форматы Microsoft Excel: текст, числа, даты, время, денежный формат. Их настройка.

Проблемы в использовании форматов.

Пользовательский формат.

Создание пользовательского формата для текста, дат и времени.

Организация вычислений в Excel.

Ввод формулы в ячейку. Копирование формул с использованием автозаполнения.

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

Влияющие и зависимые ячейки.

Подготовка документа к печати.

Настройка параметров страницы.

Создание колонтитулов

Установка сквозных строк и столбцов.

Предпечатная подготовка документа. Использование предварительного просмотра.

Печать выделенной области. Задание области печати.

Занятие 3

Функции.

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

Способы работы с функциями. Использование справки. Ввод функций при помощи Мастер функций. Категории функций.

Логические функции: ЕСЛИ,И,ИЛИ,ЕСЛИОШИБКА. Вложенные логические функции.

Математические и статические функции: СУММЕСЛИ, СЧЕТЕСЛИ, СРЗНАЧЕСЛИ

Текстовые функции СЦЕПИТЬ, ЛЕВСИМВ, прописн, строчн, ПРОПНАЧ.

Функции массивов: ГПР, ВПР,ТРАНСП.

Решение практических задач с использованием функций.

Копирование результатов вычислений и формул при помощи специальной вставки.

Обзор возможных ошибок в формулах.

Занятие 4

Диаграммы. Графика в документах

Создание диаграмм. Мастер диаграмм.

Настройка свойств диаграммы.

Построение различных типов диаграмм.

Печать диаграммы.

Вставка графических элементов. Вставка рисунка.

Объекты SmartArt.

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

Понятие условного форматирования.

Создание условного форматирования(сравнение со значением, с результатом формулы или функции.

Копирование условного форматирования.

Редактирование и удаление условного форматирования.

Автоформат.

Занятие 5

Работа с базами данных.

Понятие базы данных.

Сортировка.

Удаление дубликатов.

Промежуточные итоги.

Группировка, создание структуры.

Скрытие и отображение данных.

Закрепление строк и столбцов при просмотре.

Разделение листа на части при просмотре.

Консолидация данных.

Применение фильтров. Отбор данных посредством фильтрации.

Расширенный фильтр. Создание условий для расширенного фильтра.

Поиск и замена.

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

Создание сводных таблиц.

Изменение форматов у сводных таблиц.

Настройка сводных таблиц.

Редактирование сводной таблицы.

Обновление данных сводной таблицы.

Занятие 6

Примечания (создание, редактирование, удаление).

Создание гиперссылок.

Защита информации.

Установка пароля на открытие книги.

Защита листа на ввод данных.

Разрешение работать с отдельными ячейками и запрет работы на остальных ячейках.

Продвинутый уровень

Занятие 1

Понятие массива данных.

Работа с массивами данных.

Создание формул с массивами. Особенности редактирования формул.

Использование функций в массивах.

Поиск данных по нескольким критериям.

Списки .

Создание списков на основе постоянных данных.

Создание зависимых списков.

Занятие 2

Основные средства анализа данных

Таблица подстановки с одним или двумя параметрами.

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

Сценарии. Анализ при помощи сценариев.

Поиск решения.

Консолидация данных из разных файлов.

Сложные диаграммы.

Спарклайны (инфоргаммы)

Комбинированные диаграммы(несколько диаграмм на одном бланке диаграммы)

Занятие 3

Сводные таблицы и диаграммы.

Создание сводной таблицы из нескольких файлов. Создание формул в сводных таблицах.

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

Группировка данных в сводных таблицах.

Использование сводных таблиц для анализа данных.

Использование срезов.

Создание сводной диаграммы.

Занятие 4

Методы прогнозирования данных.

Функция ПРЕДСКАЗ.

Функция РОСТ.

Функция ТЕНДЕНЦИЯ.

Учет сезонности.

Построение линии тренда. Выбор типа линии тренда.

Скользящее среднее.

Макросы

Сохранение макроса в персональной книге макросов и удаление макроса из этой книги.

Копирование макроса в другую книгу.

Варианты запуска макросов (назначение макроса кнопке, горячие клавиши)

Редактирование и удаление макросов.

Сохранение файла с макросом.

Настройка безопасности макроса.

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

Курс «Excel для финансиста» поможет Вам:

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

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

Обучение проводит сертифицированный преподаватель Microsoft.

ПРОГРАММА КУРСА:

1. Финансовые возможности MS Excel:

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

2. Статистические возможности MS Excel:

• МАКС, МИН, СРЗНАЧ, СРЗНАЧА, МЕДИАНА
• функция ПРЕДСКАЗ - вычисляет или предсказывает будущее значение по существующим значениям. Эту функцию можно использовать для прогнозирования будущих продаж, потребностей в оборудовании или тенденций потребления.
• функция ДОВЕРИТ - возвращает доверительный интервал для среднего генеральной совокупности с нормальным распределением.
• функция РОСТ - рассчитывает прогнозируемый экспоненциальный рост на основе имеющихся данных.
• функция ТЕНДЕЦИЯ - возвращает значения в соответствии с линейным трендом.
• функции СТАНДОТКЛОНА, СТАНДОТКЛОНА,В и СТАНДОТКЛОН.Г- оценивает стандартное отклонение по заданным параметрам.

3. Финансовое моделирование и инвестиционный анализ с помощью MS Excel

• Анализ ЧТО-ЕСЛИ (диспетчер сценариев, таблица данных, подбор параметров)
• Надстройка Solver (для решения многопараметрических задач)
• Построение прогнозов
• Построение диаграммы ПЛАН_ФАКТ
• Расчеты кредитов

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

Продолжительность: 16 ак.ч

Даты проведения семинара в Москве в 2018 году

Внимание! В расписании возможны изменения. Просим уточнять информацию по электронной почте [email protected]

В стоимость включено:

Набор для записей

Пособие

Кофе-брейк

Обед

Сертификат

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

Функция ПЛТ

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

ПЛТ(ставка;кпер;пс;бс;тип)

• Ставка - процентная ставка по ссуде.
• Кпер - общее число выплат по ссуде.
• Пс - приведённая к текущему моменту стоимость, или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой.
• Бс - требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент «бс» опущен, то он полагается равным 0 (нулю), т. е. для займа, например, значение «бс» равно 0.

Функция СТАВКА

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

СТАВКА(кпер;плт;пс;бс;тип;прогноз)

• Кпер - общее число периодов платежей для ежегодного платежа.
• Плт - выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно аргумент «плт» состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если он опущен, аргумент «пс» является обязательным.
• Пс - приведённая (текущая) стоимость, т. е. общая сумма, которая на данный момент равноценна ряду будущих платежей.
• Бс (необязательный аргумент) - значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты. Если аргумент «бс» опущен, предполагается, что он равен 0 (например, будущая стоимость для займа равна 0).
• Тип (необязательный аргумент) - число 0 (нуль), если платить нужно в конце периода, или 1, если платить нужно в начале периода.
• Прогноз (необязательный аргумент) - предполагаемая величина ставки. Если аргумент «прогноз» опущен, предполагается, что его значение равно 10%. Если функция СТАВКА не сходится, попробуйте изменить значение аргумента «прогноз». Функция СТАВКА обычно сходится, если значение этого аргумента находится между 0 и 1.

Функция ЭФФЕКТ

Возвращает эффективную (фактическую) годовую процентную ставку, если заданы номинальная годовая процентная ставка и количество периодов в году, за которые начисляются сложные проценты. Полное описание функции