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

Для начала работы нужно активировать дополнительный пакет анализа

Первое, с чего нужно начать - установить надстройку. Весь процесс рассмотрим на примере версии Microsoft Excel 2010. Делается это следующим образом. Перейдите на вкладку «Файл» и нажмите «Параметры», затем выберите раздел «Надстройки». Далее, отыщите «Надстройки Excel» и кликните по кнопке «Перейти». В открывшемся окне доступных надстроек отметьте пункт «Пакет анализа» и подтвердите выбор, нажав «ОК». В случае, если необходимого пункта нет в списке, вам придётся найти его вручную, воспользовавшись кнопкой «Обзор».

Так как вам ещё могут пригодиться функции Visual Basic, желательно также установить «Пакет анализа VBA». Делается это аналогичным образом, разница только в том, что вам придётся выбрать другую надстройку из списка. Если вы точно знаете, что Visual Basic вам не нужен, то можно ничего больше не загружать.

Процесс установки для версии Excel 2013 точно такой же. Для версии программы 2007, разница только в том, что вместо меню «Файл» необходимо нажать кнопку Microsoft Office, далее следуйте по пунктам, как описано для Эксель 2010. Также перед тем как начать загрузку, убедитесь, что на вашем компьютере установлена последняя версия NET Framework.

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

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

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

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

Инструменты анализа Excel

Одним из самых привлекательных анализов данных является «Что-если». Он находится: «Данные»-«Работа с данными»-«Что-если».

Средства анализа «Что-если»:

1. «Подбор параметра». Применяется, когда пользователю известен результат формулы, но неизвестны входные данные для этого результата.
2. «Таблица данных». Используется в ситуациях, когда нужно показать в виде таблицы влияние переменных значений на формулы.
3. «Диспетчер сценариев». Применяется для формирования, изменения и сохранения разных наборов входных данных и итогов вычислений по группе формул.
4. «Поиск решения». Это надстройка программы Excel. Помогает найти наилучшее решение определенной задачи.

Практический пример использования «Что-если» для поиска оптимальных скидок по таблице данных .

Другие инструменты для анализа данных:

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



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

Чтобы упростить просмотр, обработку и обобщение данных, в Excel применяются сводные таблицы.

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

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

Составить отчет можно с помощью «Сводной таблицы».

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

Анализ «Что-если» в Excel: «Таблица данных»

Мощное средство анализа данных. Рассмотрим организацию информации с помощью инструмента «Что-если» - «Таблица данных».

Важные условия:

• данные должны находиться в одном столбце или одной строке;
• формула ссылается на одну входную ячейку.

Процедура создания «Таблицы данных»:

Анализ предприятия в Excel: примеры

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

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

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

Примечание: Чтобы включить функцию Visual Basic для приложений (VBA) для пакета анализа, вы можете загрузить надстройку " Пакет анализа - VBA " таким же образом, как и при загрузке пакета анализа. В диалоговом окне Доступные надстройки установите флажок Пакет анализа - VBA .

Чтобы загрузить пакет анализа в Excel для Mac, выполните указанные ниже действия.

В меню Сервис выберите пункт надстройки Excel .

В окне Доступные надстройки установите флажок Пакет анализа , а затем нажмите кнопку ОК .

1. Если надстройка Пакет анализа отсутствует в списке поля Доступные надстройки , нажмите кнопку Обзор , чтобы найти ее.

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

   Выйдите из приложения Excel и перезапустите его.

   Теперь на вкладке Данные доступна команда Анализ данных .

Я не могу найти пакет анализа в Excel для Mac 2011

Существуют несколько сторонних надстроек, которые предоставляют функции пакета анализа для Excel 2011.

Вариант 1. Скачайте статистическое программное обеспечение надстройки КСЛСТАТ для Mac и используйте его в Excel 2011. КСЛСТАТ содержит более 200 основных и расширенных статистических средств, включающих все функции пакета анализа.

Выберите версию КСЛСТАТ, соответствующую операционной системе Mac OS, и загрузите ее.

Откройте файл Excel, содержащий данные, и щелкните значок КСЛСТАТ, чтобы открыть панель инструментов КСЛСТАТ.

В течение 30 дней вы получите доступ ко всем функциям КСЛСТАТ. По истечении 30 дней вы сможете использовать бесплатную версию, включающую функции пакета анализа, или заказать одно из более полных решений КСЛСТАТ.

Вариант 2. Скачайте Статплус: Mac LE бесплатно из Аналистсофт, а затем используйте Статплус: Mac LE с Excel 2011.

Вы можете использовать Статплус: Mac LE для выполнения многих функций, которые ранее были доступны в пакетах анализа, таких как регрессия, гистограммы, анализ вариации (Двухфакторный дисперсионный обработки) и t-тесты.

Перейдите на веб-сайт аналистсофт и следуйте инструкциям на странице загрузки.

После загрузки и установки Статплус: Mac LE откройте книгу, содержащую данные, которые нужно проанализировать.

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

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

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

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

Как работать

Исходные данные могут быть любыми: данные по продажам, отгрузкам, доставкам и так далее.

1. Откройте файл с таблицей, данные которой надо проанализировать.
2. Перейдите на вкладку «Вставка» → «Таблица» → «Сводная таблица» (для macOS на вкладке «Данные» в группе «Анализ»).
3. Должно появиться диалоговое окно «Создание сводной таблицы».
4. Настройте отображение данных, которые есть у вас в таблице.

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

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

Можно её детализировать, например, по странам. Переносим «Страны».

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

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

Полезное дополнение. Координаты нигде прописывать не нужно - достаточно лишь корректно указать географическое название в таблице.

Как работать

1. Откройте файл с таблицей, данные которой нужно визуализировать. Например, с информацией по разным городам и странам.
2. Подготовьте данные для отображения на карте: «Главная» → «Форматировать как таблицу».
3. Выделите диапазон данных для анализа.
4. На вкладке «Вставка» есть кнопка 3D-карта.

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

Также достаточно информативной является круговая диаграмма по годам. Размер круга задаётся суммой.

3. Лист прогнозов

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

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

Как работать

1. Откройте таблицу с данными за период и соответствующими ему показателями, например, от года.
2. Выделите два ряда данных.
3. На вкладке «Данные» в группе нажмите кнопку «Лист прогноза».
4. В окне «Создание листа прогноза» выберите график или гистограмму для визуального представления прогноза.
5. Выберите дату окончания прогноза.

В примере ниже у нас есть данные за 2011, 2012 и 2013 годы. Важно указывать не числа, а именно временные периоды (то есть не 5 марта 2013 года, а март 2013-го).

Для прогноза на 2014 год вам потребуются два ряда данных: даты и соответствующие им значения показателей. Выделяем оба ряда данных.

На вкладке «Данные» в группе «Прогноз» нажимаем на «Лист прогноза». В появившемся окне «Создание листа прогноза» выбираем формат представления прогноза - график или гистограмму. В поле «Завершение прогноза» выбираем дату окончания, а затем нажимаем кнопку «Создать». Оранжевая линия - это и есть прогноз.

4. Быстрый анализ

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

Полезное дополнение. Мгновенно можно создавать различные типы диаграмм или спарклайны (микрографики прямо в ячейке).

Как работать

1. Откройте таблицу с данными для анализа.
2. Выделите нужный для анализа диапазон.
3. При выделении диапазона внизу всегда появляется кнопка «Быстрый анализ». Она сразу предлагает совершить с данными несколько возможных действий. Например, найти итоги. Мы можем узнать суммы, они проставляются внизу.

В быстром анализе также есть несколько вариантов форматирования. Посмотреть, какие значения больше, а какие меньше, можно в самих ячейках гистограммы.

Также можно проставить в ячейках разноцветные значки: зелёные - наибольшие значения, красные - наименьшие.

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

ЗАДАНИЕ № 1

Статистический анализ данных в программе MS Excel

Цель работы : научиться обрабатывать статистические данные с помощью встроенных функций MS Excel ; изучить возможности Пакета анализа и его инструменты: «Генерация случайных чисел» , «Гистограмма» , «Описательная статистика» на примере обработки измерений скорости движения.

В соответствии с методическими указаниями к лабораторной работе «Измерение скорости движения автомобилей» (по дисциплине «Изыскание и проектирование автомобильных дорог») обработать экспериментальные данные измерений методами математической статистики в программе Excel. Для чего:

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

Максимальное значение скорости движения Vмакс; - среднее значение скорости движения Vср;

Стандартное отклонение S;

Стандартное отклонение среднего Sср;

Коэффициент Стьюдента (для определения доверительного интервала) t; - доверительный интервал для Р = 0.95.

2. Получить статистические характеристики, используя инструмент « Описательная статистика » из дополнительного пакета «Анализ данных ».

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

4. Построить кумулятивную кривую (кривую накопленной частости).

5. Построить теоретическую кривую распределения скорости движения.

Для получения достаточного количества исходных данных (результатов измерений скорости) использовать имитационный эксперимент с помощью инструмента «Генерация случайных чисел » дополнения «Анализ данных ».

При выполнении п.п. 3 и 4 подобрать интервал скоростей («карман» – в терминологии Excel), позволяющий получить наиболее симметричную гистограмму, демонстрирующую нормальный закон распределения.

Образец выполнения приведен в прилагаемом файле ОсновыПК1-Студент.xls.

Методические указания

Предположим, что мы проделали серию из 10 опытов, измеряя некоторую величину Х. Таблица 1. Примерный вид листа «Обработка эксперимента»

Записи в колонках D и Е – это подсказки, которые помогут разобраться с тем, какие характеристики мы будем рассчитывать. Колонка F у Вас должна быть пока пустой, в нее будут помещены наши формулы.

Обработку результатов начнем с расчета числа опытов n .

Для определения числа значений используется специальная функция, которая называется СЧЕТ . Для ввода формулы с функциями используется Мастер функций , который запускается командой «Вставка функции» через меню «Вставка» – «Функция» или кнопкой на панели инструментов с обозначением f x .

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

Первый шаг работы (рисунок 1) служит для выбора нужной функции.

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

Список функций упорядочен по алфавиту, что позволяет без труда найти нужную нам функцию СЧЕТ («Подсчитывает количество чисел в списке аргументов»).

Выделив щелчком эту функцию, нажимаем кнопку Ok и переходим к шагу 2.

Второй шаг (рисунок 2) служит для задания аргументов функции.

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

В ячейке F7 c помощью функции СРЗНАЧ рассчитывается среднее значение выборки, в ячейке F8 – стандартное отклонение выборки, с помощью функции СТАНДОТКЛОН. .

Аргументами этих функций служит все тот же диапазон ячеек.

Для расчета доверительного интервала необходимо определить коэффициент Стьюдента. Он зависит от вероятности ошибки (при обычно задаваемой надежности 95% вероятность ошибки составляет 5%), и от числа степеней свободы n-1 ).

Для нахождения коэффициента Стьюдента используется статистическая функция Excel СТЬЮДРАСПОБР (“Стьюдента распределение обратное“). Особенностью этой функции является то, что первый аргумент, число 5% (или 0,05) вводится в соответствующее окно с клавиатуры. Для второго указываем адрес ячейки, где находится значение n , затем дописываем в окне “-1”. Получаем запись “F6-1 ”.

Для нахождения доверительного интервала используется обычная формула умножения. Конечно, вместо букв там должны стоять адреса ячеек, где находятся коэффициент Стьюдента и стандартное отклонение среднего. Как правило, значение доверительного интервала округляется до одной значащей цифры, такой же порядок окружения должен быть и у среднего. Поэтому окончательный результат можно записать так: с 95%-ной надежностью Х = 14,80±0,05 . В заключение посчитаем относительную ошибку определения Х: = ДИ / Х ср (формула: “=F11/F7 ”). Значение относительной ошибки обычно выражают в процентах, у нас 0,3%.

Для выполнения заданий 2 и 3 используется надстройка «Пакет анализа» (из меню Сервис  .Анализ данных  Гистограмма).

Для установки надстройки вызвать меню Сервис  Надстройки и из предлагаемого списка доступных к установке надстроек выбрать «Пакет анализа» (см. Установка надстроек

Excel на компьютере.doc).