Анализ данных Excel

Анализ данных Excel

Использование надстройки «Пакет анализа», поможет упростить расчеты при проведении статистического или инженерного анали за.

Надстройка Пакет анализа ( Analysis ToolPak ) доступна из вкладки Данные , группа Анализ . Кнопка для вызова диалогового окна называется Анализ данных .

Если кнопка не отображается в указанной группе, то необходимо сначала включить надстройку (ниже дано пояснение для EXCEL 2010/2007):

  • на вкладке Файл выберите команду Параметры , а затем — категорию Надстройки .
  • в списке Управление (внизу окна) выберите пункт Надстройки Excel и нажмите кнопку Перейти .
  • в окне Доступные надстройки установите флажок Пакет анализа и нажмите кнопку ОК.

СОВЕТ : Если пункт Пакет анализа отсутствует в списке Доступные надстройки , нажмите кнопку Обзор , чтобы найти надстройку. Файл надстройки FUNCRES.xlam обычно хранится в папке MS OFFICE, например C :\ Program Files \ Microsoft Office \ Office 14\ Library \ Analysis или его можно скачать с сайта MS.

После нажатия кнопки Анализ данных будет выведено диалоговое окно надстройки Пакет анализа .

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

  • Однофакторный дисперсионный анализ (ANOVA: single factor);
  • Двухфакторный дисперсионный анализ с повторениями (ANOVA: two factor with replication);
  • Двухфакторный дисперсионный анализ без повторений (ANOVA: two factor without replication);
  • Корреляция (Correlation) ;
  • Ковариация (Covariance) ;
  • Описательная статистика (Descriptive Statistics) ;
  • Экспоненциальное сглаживание (Exponential Smoothing);
  • Двухвыборочный F-тест для дисперсии (F-test Two Sample for Variances) ;
  • Анализ Фурье (Fourier Analysis);
  • Гистограмма (Histogram);
  • Скользящее среднее (Moving average);
  • Генерация случайных чисел (Random Number Generation) ;
  • Ранг и Персентиль (Rank and Percentile) ;
  • Регрессия (Regression) — простая регрессия; для множественной регрессии см. ;
  • Выборка (Sampling) ;
  • Парный двухвыборочный t-тест для средних (t-Test: Paired Two Sample for Means) ;
  • Двухвыборочный t-тест с одинаковыми дисперсиями (t-Test: Two-Sample Assuming Equal Variances) ;
  • Двухвыборочный t-тест с различными дисперсиями (t-Test: Two-Sample Assuming Unequal Variances) ;
  • Двухвыборочный z-тест для средних (z-Test: Two Sample for Means) .

Функции табличного редактора Excel, позволяющие формировать данные для анализа результатов работы компании

Средства Excel для визуализации данных бизнес-анализа

Понятие бизнес-аналитики достаточно обширно и нередко трактуется по-разному.

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

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

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

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

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

Кроме того, пользователь может сам легко перенастроить существующие или разработать новые отчеты в Excel, не привлекая специалистов по программированию.

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

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

Работают со сводными таблицами из вкладки меню функции «Анализ» (рис. 1). На этой вкладке также настраиваются параметры сводной таблицы и источники данных (откуда берется информация).

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

Обратите внимание!

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

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

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

К сведению

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

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

Для наглядности приведем еще пример локального применения функции ВПР при решении задачи построения оперативного бизнес-отчета из собственной практики.

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

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

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

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

Рассмотрим основные показатели хозяйственной деятельности предприятия (таблица 1).

Таблица 1 – Показатели финансово-экономической деятельности предприятия в 2009-2010 гг.

Примечание – Источник: собственная разработка на основе данных предприятия.

Данные таблицы 1 позволяют проследить следующие тенденции в развитии предприятия. Так, по итогам 2010 г. предприятием получена выручка от реализации продукции, работ, услуг в объеме 136483 млн р., что стало ниже показателя предыдущего года на 109110 млн р., или на 44,4%. Ухудшение результата производственно-сбытовой деятельности предприятия в рассматриваемом периоде было обусловлено резким снижением спроса на продукцию предприятия со стороны основных потребителей, финансовое положение которых в 2010 г. сильно ухудшилось в связи с влиянием последствий кризисных явлений в экономике Республики Беларусь.

С отрицательной стороны характеризуется рост налоговой нагрузки на выручку, которая в рассматриваемый период возросла на 0,723 процентных пункта, с 14,248% в 2009 г. до 14,970% в 2010 г.

Ухудшение произошло также в связи с ростом уровня себестоимости реализованной продукции на 4,140 процентных пункта, с 77,714% в 2009 г. до 81,854% в 2010 г., а также уровня расходов на реализацию – на 0,113 процентных пункта, с 0,171% в 2009 г. до 0,284% в 2010 г.

В совокупности вышеуказанные факторы оказали отрицательное влияние на формирование прибыли от реализации продукции предприятия, объем которой по результатам 2010 г. составил 3947 млн р. и снизился по отношению к результату предыдущего года на 15374 млн р., или на 79,4%.

В результате произошло снижение показателей рентабельности продаж и реализованной продукции на 4,975 и 6,590 процентных пункта соответственно.

С учетом результатов операционной и внереализационной деятельности предприятие в 2010 г. сформировало прибыль отчетного периода в размере 2049 млн р., что стало ниже показателя предыдущего года на 14095 млн р., или на 87,3%.

Размер чистой прибыли в 2010 г., оставшейся в распоряжении предприятия, составил 3 млн р. против 10183 млн р. в 2009 г.

В рассматриваемый период предприятие проводило политику снижения численности работников. Так, за 2009-2010 г. среднесписочная численность работников предприятия снизилась на 70 чел. и составила 1615 чел.

Недостаточная загруженность производства в 2010 г. не позволила повысить уровень производительности труда работников предприятия, который снизился в рассматриваемый период на 61,2 млн р., или на 42,0%. В то же время доведенные «сверху» показатели по росту среднемесячной заработной платы в промышленности обусловили значительный ее рост по предприятию в 2010 г. – 27,2%. В результате сложилась негативная тенденция опережения темпов роста заработной платы (127,2%) над темпами роста производительности труда (58,0%), что характеризует крайне неэффективное использование трудовых ресурсов на предприятии.

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

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

Юлия Перминова

Тренер Учебного центра Softline с 2008 года.

Другие типы анализа «что если”

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

  • Диспетчер сценариев позволяет подставлять значения сразу в несколько ячеек (до 32). Вы можете создать несколько сценариев, а затем сравнить их, не изменяя значений вручную. В следующем примере мы используем сценарии, чтобы сравнить несколько различных мест для проведения мероприятия.
  • Таблицы данных позволяют взять одну из двух переменных в формуле и заменить ее любым количеством значений, а полученные результаты свести в таблицу. Этот инструмент обладает широчайшими возможностями, поскольку выводит сразу множество результатов, в отличие от Диспетчера сценариев или Подбора параметра. В следующем примере видно 24 возможных результата по ежемесячным платежам за кредит:

Оцените качество статьи. Нам важно ваше мнение:


Добавить комментарий

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