Реклама на портале

Финансовые функции в Excel. Финансовые функции в Excel Основные средства анализа данных

Подборка финансового анализа в таблицах excel предприятия от различных авторов.

Таблицы Excel Попова А.А. позволят провести финансовый анализl: рассчитать деловую активность, платежеспособность, рентабельность, финансовую устойчивость, агрегированный баланс, провести анализ структуры активов баланса, коэффициентный и динамический анализ на основе 1 и 2 формы бухгалтерской отчетности предприятия.
Скачать финансовый анализ в excel от Попова

Таблицы Excel финансового анализа предприятия Зайковского В.Э. (директора по экономике и финансам ОАО «Томского завода измерительной аппаратуры») позволяют на основе 1 и 2 форм внешней бухгалтерской отчетности произвести расчет банкротства предприятия по модели Альтмана, Таффлера и Лиса, оценить финансовое состояние предприятия по показателям ликвидности, финансовой устойчивости, состоянию основных средств, оборачиваемости активов, рентабельности. Помимо этого находят связь между неплатежеспособностью предприятия с задолженностью государства перед ним. Имеются графики изменения активов и пассивов предприятия во времени.
Скачать финансовый анализ в excel от Зайковского

Таблицы Excel для проведения финансового анализа от Малахова В.И. позволяют рассчитать баланс в процентной форме, оценку эффективности управления, оценку финансовой (рыночной) устойчивости, оценку ликвидности и платежеспособности, оценку рентабельности, деловой активности, положения предприятия на РЦБ, модель Альтмана. Строятся диаграммы актива баланса, динамики выручки, динамика валовой и чистой прибыли, динамика задолженности.
Скачать финансовый анализ в excel от Малахова

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

Таблицы excel Салова А.Н., Маслова В.Г. позволит провести спектр - балльного анализа финансового состояния. Спектр балльный метод является наиболее надежным методом финансово-экономического анализа. Его суть заключается в проведении анализа финансовых коэффициентов путем сравнения полученных значений с нормативными величинами, используется при этом система «разнесения» этих значений по зонам удаленности от оптимального уровня. Анализ финансовых коэффициентов производится путем сравнения полученных значений с рекомендуемыми нормативными величинами, играющими роль пороговых нормативов. Чем удаленнее значение коэффициентов от нормативного уровня, тем ниже степень финансового благополучия и выше риск попадания в категорию несостоятельных предприятий.

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

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

С помощью полученных знаний вы легко сможете самостоятельно автоматизировать свою рутинную работу (сверить две выписки, сформировать график платежей, произвести сверку актов, загрузить курсы валют с сайта Центрального Банка РФ, сделать консолидированный ОДДС (Cash-flowstatement) по выпискам нескольких компаний холдинга и многое другое).

Тренинг носит практическую направленность, поэтому для участия в нём слушателям необходимо принести ноутбук с установленным Microsoft Office не ниже 2010 версии (для Windows) с возможностью создания макросов.

Программа обучения

Практический курс «Программирование в Excel для финансистов» состоит из двух блоков:

Базовый блок

Данный блок предлагает освоить (освежить) базовые знания в области программирования на языке VisualBasic (forExcel).

1) Интерфейс редактора Visual Basic

  • Окно редактора VBA Безопасность макросов, рекомендации по выбору уровней безопасности
  • Функциональные окна и их назначение. Работа в редакторе

2) Простая запись макроса

  • Просмотр и корректировка программного кода, созданного автоматически
  • Оптимизация кода
  • Создание и отладка процедуры Sub
  • Ввод программного кода в «ручном режиме», варианты запуска макросов. Упрощенный ввод программного кода в ручном режиме

3) Основы программирования в Visual Basic (изучение на основе редактора VB для MSExcel)

  • Использование служебного блока WITH … END WITH
  • Объявление переменных «Область жизни» переменной (локальные и глобальные переменные)
  • Типы переменных Возможности использования сложных (объектных) переменных
  • Управляющие операторы языка Visual Basic
  • Циклы в Visual Basic
  • Встроенные функции VB for Office
  • Основные функции обработки данных разных типов. Преобразование типов данных
  • Создание пользовательских функций
  • Отличие от процедур. Различные варианты передачи параметров (переменных) в функцию Практическая отработка навыков разработки функций

4) Отладчик программ

  • Возможные ошибки при написании программ. Ошибки первого уровня (синтаксические), второго уровня (логические) и Runtime-ошибки
  • Возможности по использованию отладчика для поиска ошибок в программах

5) Заключительная часть

  • Основы объектной модели MSExcel
  • Основные свойства и методы объекта Range Варианты использования. Практическое применение для обработки данных
  • Объект Selection. Практическое использование объекта

По итогам обучения на базовом блоке слушатели смогут:

  • Самостоятельно записывать макросы, вносить в них правки, оптимизировать код макросов
  • Писать процедуры в «ручном режиме», запускать их в различных режимах
  • Создавать пользовательские функции для автоматизации обработки данных в MSExcel
  • Использовать переменные и понимать особенности применения различных типов переменных
  • Разбираться в различиях объявления переменных
  • Выявлять ошибки в программах, используя отладчик редактора Visual Basiс

Продвинутый блок

Данный блок предлагает освоить сложные навыки знания в области создания программ на языке Visual Basic (for Excel).

1) Объектно-ориентированное программирование (изучение на основе редактора VB для MSExcel)

  • Понятие объекта, классов, событий и методов

2) Объектная модель MSExcel

  • объект Workbook (коллекция WorkBooks)
  • объект Worksheet (коллекция WorkSheets)
  • объект Range - основы использования

3) Свойства, методы и возможности использования. Работа со справочной системой Microsoft (MSDN )

4) Перехват событий в среде VB for Office

5) UserForm и элементы управления для использования на формах пользователя:

  • TextBox (текстовое поле)
  • Label (надпись, метка)
  • ListBox (список) и ComboBox (поле со списком)
  • CheckBox (флажок) и OptionButton (переключатель)
  • CommandButton (кнопка)

второстепенные:

  • TabStrip (набор вкладок)
  • MultiPage (набор страниц)
  • ToggleButton (выключатель)
  • ScrollBar (полоса прокрутки)
  • SpinButton (счетчик)
  • Image (рисунок)

6) Диалог открытия файлов FileDialog

  • Синтаксис, варианты использования

7) Организация доступа к файловой системе компьютера

8) Организация взаимодействия между приложениями. Управление MSWord из программ MSExcel

9) Практические работы

  • Практическая работа 1. Разработка функции «Курс ЦБ РФ»
  • Практическая работа 2. Разработка программы «Сверка выписок»
  • Практическая работа 3. Разработка программы «Формирование консолидированного ОДДС»

11) Итоговое тестирование

  • По итогам обучения слушатели проходят тестирование на усвоение пройденного материала.
  • При успешной сдаче итогового теста слушателям выдается Удостоверение о повышении квалификации.

По итогам обучения на продвинутом блоке слушатели смогут:

  • Понимать и использовать особенности Объектно-Ориентированного программирования в MSExcel
  • Создавать программы (1) для автоматической обработки данных, используя объектную модель MSExcel
  • Уметь создавать и использовать в программах формы пользователя
  • Работать из программ c файловой системой компьютера (с файлами и директориями)
  • Управлять приложениями MSOffice из создаваемых программ
  • Подгружать данные через Интернет и автоматизировать данную загрузку
  • Обрабатывать данные из нескольких файлов

Расписание

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

Время занятий: с 09:30 до 17:00 по субботам

  • 16 ноября
  • 23 ноября
  • 30 ноября
  • 7 декабря
  • 19 декабря (четверг) с 18.45-22.00 Защита итоговой работы

Полная стоимость: 28 900 руб.

Освоить Excel легко! Если вы придерживаетесь противоположного мнения, вам не попадалось классное пособие для изучения программы.

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

Итак, вот мой выстраданный перечень из 10 лучших книг по Excel:

1. Джон Уокенбах “Microsoft Excel 2013. Библия пользователя”

2. Джон Уокенбах “Формулы в Microsoft Excel 2013”

Знаете, о чём я пожалел после покупки этой книги? О том, что она не попала в мои руки намного раньше. Это же настоящий кладезь мудрости!

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

Под конец станете таким крутым спецом, что сможете создавать пользовательские функции в VBA самостоятельно. Берите и изучайте “Формулы в Microsoft Excel 2013” от корки до корки. Она того стоит!

3. Джон Уокенбах “Excel 2013. Профессиональное программирование на VBA”

4. Билл Джелен и Майкл Александер “Сводные таблицы в Microsoft Excel”

Кому не хочется поднять производительность работы? В разы сократить затраты времени на занудную отчётность? Почти мгновенно оценивать и анализировать данные? А как насчёт урезать длинный запутанный отчёт до лаконичного и понятного? Сложно? Ничуть! Со сводными таблицами в Microsoft Excel все эти фокусы – проще пареной репы.

Если вам частенько приходится иметь дело со сложной отчётностью, труд Билла Джелена и Майкла Александера – must have в вашей библиотеке.

5. Куртис Фрай “Microsoft Excel 2013. Шаг за шагом”

6. Грег Харвей “Microsoft Excel 2013 для чайников”

7. Конрад Карлберг “Бизнес анализ с использованием Excel”

Что может быть скучнее, чем тягомотина с тоннами отчётов? Сидеть и анализировать ситуацию или разбираться с деловыми задачами приходится часами. Да бросьте! Вы серьёзно не в курсе, что всё это можно с легкостью делать в Excel?

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

Вы ведь не собираетесь сидеть на месте, как пресловутый камень, который вода обходит стороной? Нет? Тогда берите “Бизнес анализ с использованием Excel”, учитесь и развивайтесь!

8. Шимон Беннинг “Основы финансов с примерами в Excel”

Любопытный факт: почти все авторы пособий по финансам в своих книгах пренебрегают Excel. И очень зря. Ведь сейчас большинство компаний выполняют расчёты именно в этой программе. Шимон Беннинг заметил эту оплошность и выпустил “Основы финансов с примерами в Excel”.

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

Я считаю, что финансы нужно изучать в контексте работы с Excel. Вот почему рекомендую пособие “Основы финансов с примерами в Excel”, как один из лучших учебников. Труд Шимона Беннинга пригодится и студентам и профи.

Изучать Excel можно вечно. Когда-то я думал, что мой запас знаний о программе тянет на вагон и небольшую кошёлку. Оказалось, что знал я далеко не всё. В Excel есть масса хитростей. О том, как ими пользоваться, и расскажет Джон Уокенбах.

Книга содержит такое количество подсказок, советов, трюков, хитростей и приёмчиков, что удержать их все в голове порой невозможно. Вместе с тем каждый из них стоит взять на заметку, чтобы сократить время работы над той или иной задачей. Либо улучшить качество этой самой работы. Так что для меня книга “Excel 2013. Трюки и советы Джона Уокенбаха” стала настольной. Чего и вам желаю!

10. Николай Павлов “Microsoft Excel. Готовые решения – бери и пользуйся!”

Отличное пособие для новичков и специалистов в Excel с готовыми решениями.

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

Пособиями, которые я рекомендую в этой статье, пользуюсь до сих пор. Все они принесли огромную пользу мне и обязательно принесут её вам. Безусловно, лучше обзавестись бумажными экземплярами, чтобы делать пометки и оставлять закладки. Так удобней! Вот увидите, эти книги ускорят вашу работу с Excel и вдохнут в неё новую жизнь. Если вы так не считаете, с удовольствием поспорю с вами в комментариях! =)

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

Функция ПЛТ

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

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

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

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

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

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

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

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

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

Обширный функционал 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 автоматически распознает их как часть таблицы. В дальнейшем это облегчит обновление сводных таблиц с учетом новой информации.

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

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

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

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

Для любых предложений по сайту: [email protected]