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 – это не просто табличный редактор, но ещё и мощный инструмент для различных математических и статистических вычислений. В приложении имеется огромное число функций, предназначенных для этих задач. Правда, не все эти возможности по умолчанию активированы. Именно к таким скрытым функциям относится набор инструментов «Анализ данных» . Давайте выясним, как его можно включить.
Чтобы воспользоваться возможностями, которые предоставляет функция «Анализ данных» , нужно активировать группу инструментов «Пакет анализа» , выполнив определенные действия в настройках Microsoft Excel. Алгоритм этих действий практически одинаков для версий программы 2010, 2013 и 2016 года, и имеет лишь незначительные отличия у версии 2007 года.
Активация
- Перейдите во вкладку «Файл» . Если вы используете версию Microsoft Excel 2007, то вместо кнопки «Файл» нажмите значок Microsoft Office в верхнем левом углу окна.
- Кликаем по одному из пунктов, представленных в левой части открывшегося окна – «Параметры» .
- В открывшемся окне параметров Эксель переходим в подраздел «Надстройки» (предпоследний в списке в левой части экрана).
- В этом подразделе нас будет интересовать нижняя часть окна. Там представлен параметр «Управление» . Если в выпадающей форме, относящейся к нему, стоит значение отличное от «Надстройки Excel» , то нужно изменить его на указанное. Если же установлен именно этот пункт, то просто кликаем на кнопку «Перейти…» справа от него.
- Открывается небольшое окно доступных надстроек. Среди них нужно выбрать пункт «Пакет анализа» и поставить около него галочку. После этого, нажать на кнопку «OK» , расположенную в самом верху правой части окошка.
После выполнения этих действий указанная функция будет активирована, а её инструментарий доступен на ленте Excel.
Запуск функций группы «Анализ данных»
Теперь мы можем запустить любой из инструментов группы «Анализ данных» .
Работа в каждой функции имеет свой собственный алгоритм действий. Использование некоторых инструментов группы «Анализ данных» описаны в отдельных уроках.
Как видим, хотя блок инструментов «Пакет анализа» и не активирован по умолчанию, процесс его включения довольно прост. В то же время, без знания четкого алгоритма действий вряд ли у пользователя получится быстро активировать эту очень полезную статистическую функцию.
Юлия Перминова
Тренер Учебного центра Softline с 2008 года.
Базовый инструмент для работы с огромным количеством неструктурированных данных, из которых можно быстро сделать выводы и не возиться с фильтрацией и сортировкой вручную. Сводные таблицы можно создать с помощью нескольких действий и быстро настроить в зависимости от того, как именно вы хотите отобразить результаты.
Полезное дополнение. Вы также можете создавать сводные диаграммы на основе сводных таблиц, которые будут автоматически обновляться при их изменении. Это полезно, если вам, например, нужно регулярно создавать отчёты по одним и тем же параметрам.
Как работать
Исходные данные могут быть любыми: данные по продажам, отгрузкам, доставкам и так далее.
- Откройте файл с таблицей, данные которой надо проанализировать.
- Перейдите на вкладку «Вставка» → «Таблица» → «Сводная таблица» (для macOS на вкладке «Данные» в группе «Анализ»).
- Должно появиться диалоговое окно «Создание сводной таблицы».
- Настройте отображение данных, которые есть у вас в таблице.
Перед нами таблица с неструктурированными данными. Мы можем их систематизировать и настроить отображение тех данных, которые есть у нас в таблице. «Сумму заказов» отправляем в «Значения», а «Продавцов», «Дату продажи» - в «Строки». По данным разных продавцов за разные годы тут же посчитались суммы. При необходимости можно развернуть каждый год, квартал или месяц - получим более детальную информацию за конкретный период.
Набор опций будет зависеть от количества столбцов. Например, у нас пять столбцов. Их нужно просто правильно расположить и выбрать, что мы хотим показать. Скажем, сумму.
Можно её детализировать, например, по странам. Переносим «Страны».
Можно посмотреть результаты по продавцам. Меняем «Страну» на «Продавцов». По продавцам результаты будут такие.
Этот способ визуализации данных с географической привязкой позволяет анализировать данные, находить закономерности, имеющие региональное происхождение.
Полезное дополнение. Координаты нигде прописывать не нужно - достаточно лишь корректно указать географическое название в таблице.
Как работать
- Откройте файл с таблицей, данные которой нужно визуализировать. Например, с информацией по разным городам и странам.
- Подготовьте данные для отображения на карте: «Главная» → «Форматировать как таблицу».
- Выделите диапазон данных для анализа.
- На вкладке «Вставка» есть кнопка 3D-карта.
Точки на карте - это наши города. Но просто города нам не очень интересны - интересно увидеть информацию, привязанную к этим городам. Например, суммы, которые можно отобразить через высоту столбика. При наведении курсора на столбик показывается сумма.
Также достаточно информативной является круговая диаграмма по годам. Размер круга задаётся суммой.
3. Лист прогнозов
Зачастую в бизнес-процессах наблюдаются сезонные закономерности, которые необходимо учитывать при планировании. Лист прогноза - наиболее точный инструмент для прогнозирования в Excel, чем все функции, которые были до этого и есть сейчас. Его можно использовать для планирования деятельности коммерческих, финансовых, маркетинговых и других служб.
Полезное дополнение. Для расчёта прогноза потребуются данные за более ранние периоды. Точность прогнозирования зависит от количества данных по периодам - лучше не меньше, чем за год. Вам требуются одинаковые интервалы между точками данных (например, месяц или равное количество дней).
Как работать
- Откройте таблицу с данными за период и соответствующими ему показателями, например, от года.
- Выделите два ряда данных.
- На вкладке «Данные» в группе нажмите кнопку «Лист прогноза».
- В окне «Создание листа прогноза» выберите график или гистограмму для визуального представления прогноза.
- Выберите дату окончания прогноза.
В примере ниже у нас есть данные за 2011, 2012 и 2013 годы. Важно указывать не числа, а именно временные периоды (то есть не 5 марта 2013 года, а март 2013-го).
Для прогноза на 2014 год вам потребуются два ряда данных: даты и соответствующие им значения показателей. Выделяем оба ряда данных.
На вкладке «Данные» в группе «Прогноз» нажимаем на «Лист прогноза». В появившемся окне «Создание листа прогноза» выбираем формат представления прогноза - график или гистограмму. В поле «Завершение прогноза» выбираем дату окончания, а затем нажимаем кнопку «Создать». Оранжевая линия - это и есть прогноз.
4. Быстрый анализ
Эта функциональность, пожалуй, первый шаг к тому, что можно назвать бизнес-анализом. Приятно, что эта функциональность реализована наиболее дружественным по отношению к пользователю способом: желаемый результат достигается буквально в несколько кликов. Ничего не нужно считать, не надо записывать никаких формул. Достаточно выделить нужный диапазон и выбрать, какой результат вы хотите получить.
Полезное дополнение. Мгновенно можно создавать различные типы диаграмм или спарклайны (микрографики прямо в ячейке).
Как работать
- Откройте таблицу с данными для анализа.
- Выделите нужный для анализа диапазон.
- При выделении диапазона внизу всегда появляется кнопка «Быстрый анализ». Она сразу предлагает совершить с данными несколько возможных действий. Например, найти итоги. Мы можем узнать суммы, они проставляются внизу.
В быстром анализе также есть несколько вариантов форматирования. Посмотреть, какие значения больше, а какие меньше, можно в самих ячейках гистограммы.
Также можно проставить в ячейках разноцветные значки: зелёные - наибольшие значения, красные - наименьшие.
Надеемся, что эти приёмы помогут ускорить работу с анализом данных в Microsoft Excel и быстрее покорить вершины этого сложного, но такого полезного с точки зрения работы с цифрами приложения.
Microsoft Excel предлагает средства для анализа статистических данных. Такие встроенные функции, как СРЗНАЧ (AVERAGE), МЕДИАНА (MEDIAN) и МОДА (MODE), могут использоваться для проведения анализа данных. Если встроенных статистических функций недостаточно, необходимо обратиться к пакету Анализ данных .
Пакет Анализ данных, являющийся надстройкой, содержит коллекцию функций и инструментов, расширяющих встроенные аналитические возможности Excel. В частности, пакет Анализ данных можно использовать для создания гистограмм, ранжирования данных, извлечения случайных или периодических выборок из набора данных, проведения регрессионного анализа, получения основных статистических характеристик выборки, генерации случайных чисел с различным распределением, а также для обработки данных с помощью преобразования Фурье и других преобразований.
Пакет Анализ данных доступен при каждом запуске Excel. Функции пакета Анализ данных можно использовать точно так же, как и любые другие функции Excel, а чтобы получить к ним доступ, выполните описанные ниже действия:
1. Выберите в меню Сервис команду Анализ данных . При первом выборе этой команды Excel загружает файл с диска. Затем на экране появится окно диалога Анализ данных (рис. 2.19).
Рис. 2.19. Окно диалога Анализ данных
2. Чтобы использовать какой-либо из инструментов анализа, выберите его имя в списке и нажмите кнопку ОК.
3. Заполните открывшееся окно диалога. В большинстве случаев это означает задание входного диапазона с данными, которые вы собираетесь анализировать, задание выходного диапазона, куда должны быть помещены результаты, и выбор нужных параметров.
Если команда Анализ данных отсутствуетв меню Сервис или формула, содержащая функцию из пакета анализа, возвращает ошибочное значение #И MЯ?(# NAME?), выберите в меню Сервис команду Надстройки , затем Пакет анализа в списке надстроек, после чего нажмите кнопку ОК . Если Пакет анализа отсутствует в списке надстроек, вы должны установить его, запустив программу Setup.
При анализе данных часто возникает необходимость определения различных статистических характеристик или параметров распределения. С помощью Microsoft Excel можно анализировать распределение, используя несколько инструментов: встроенные статистические функции, функции для оценки разброса данных, инструмент Описательная статистика (Descriptive Statistics), который предоставляет удобные сводные таблицы основных параметров распределения, инструменты Гистограмма (Histogram), Ранг и персентиль (Rank and Percentile).
Встроенные статистические функции Microsoft Excel применяются при проведении статистического анализа данных. В данном разделе мы ограничимся обсуждением наиболее часто используемых статистических функций. Кроме них Excel также предлагает более сложные функции ЛИНЕЙН (LINEST), ЛГРФПРИБЛ (LOGEST), ТЕНДЕНЦИЯ (TREND) и РОСТ (GROWTH), которые работают с числовыми массивами.
Описательная статистика (Descriptive Statistics) позволяет создать таблицу основных статистических характеристик для одного или нескольких множеств входных значений. Выходной диапазон содержит таблицу со статистическими характеристиками для каждой переменной входного диапазона: среднее, стандартная ошибка, медиана, мода, стандартное отклонение и дисперсия выборки, коэффициент эксцесса, коэффициент асимметрии, размах, минимальное значение, максимальное значение, сумма, количество значений, k -е наибольшее и наименьшее значения (для любого заданного k ) и доверительный интервал для среднего.
Для использования Описательная статистика в меню Сервис выберите команду Анализ данных , затем в списке Инструменты анализа окна диалога Анализ данных выберите инструмент Описательная статистика и нажмите кнопку ОК . Появится окно диалога, показанное на рис. 2.20.
Рис. 2.20. Окно диалога Описательная статистика
Инструмент Описательная статистика требует задания входного диапазона, который может содержать одну или несколько переменных, и выходного диапазона. Вы должны также указать, как расположены переменные в столбцах или в строках. Установите флажок Метки в первой строке, если первая строка во входном диапазоне содержит названия столбцов. Excel использует эти метки для создания заголовков в выходной таблице.
Чтобы получить представленную выше таблицу статистических характеристик, установите флажки в области Параметры вывода .
Подобно другим инструментам пакета анализа, Описательная статистика создает таблицу констант. Если эта таблица вас не устраивает, можно получить большинство из перечисленных ниже статистических характеристик с помощью других инструментов пакета анализа или формул с использованием встроенных функций Excel.
Анализ данных с помощью диаграмм
В MS Excel имеется возможность графического представления данных в виде диаграммы. Диаграммы связаны с данными листа, на основе которых они были созданы, и изменяются каждый раз, когда изменяются данные на листе.
Диаграммы могут использовать данные несмежных ячеек. Диаграмма может также использовать данные сводной таблицы.
Можно создать либо внедренную диаграмму, либо лист диаграммы. Внедренная диаграмма – это объект, расположенный на листе и сохраняемый вместе с листом при сохранении книги. Внедренные диаграммы также связаны с данными и обновляются при изменении исходных данных. Лист диаграммы – лист книги, содержащий только диаграмму. Листы диаграммы связаны с данными таблиц и обновляются при изменении данных в таблице.
Для того чтобы построить диаграмму, выделите ячейки, содержащие данные, которые должны быть отражены на диаграмме; если необходимо, чтобы в диаграмме были отражены и названия строк или столбцов, выделите также содержащие их ячейки; нажмите кнопку Мастер диаграмм и следуйте инструкциям Мастера .
Для создания диаграмм из несмежных диапазонов нужно выделить первую группу ячеек, содержащих необходимые данные, удерживая клавишу CTRL, выделить необходимые дополнительные группы ячеек и нажать кнопку Мастер диаграмм.
Большая часть текстов диаграммы, например подписи делений оси категорий, имена рядов данных, текст легенды и подписи данных, связана с ячейками рабочего листа, используемого диаграммой. Если изменить текст этих элементов на диаграмме, они потеряют связь с ячейками листа. Чтобы сохранить связь, следует изменять текст этих элементов в исходных таблицах.
Ряд данных – группа связанных точек данных диаграммы, отображающая значение строк или столбцов листа. Каждый ряд данных отображается по-своему. На диаграмме может быть отображен один или несколько рядов данных. На круговой диаграмме отображается только один ряд данных.
Чтобы изменить текст легенды или имя ряда данных на листе, выберите ячейку, содержащую изменяемое имя ряда, введите новое имя и нажмите клавишу ENTER.
Чтобы изменить текст легенды или имя ряда данных на диаграмме, выберите нужную диаграмму, а затем выберите команду Диаграмма – Исходные данные . На вкладке Ряды выберите изменяемые имена рядов данных. В поле Имя укажите ячейку листа, которую следует использовать как легенду или имя ряда. Также можно просто ввести нужное имя. Если в поле Имя ввести имя, то текст легенды или имя ряда потеряют связь с ячейкой листа.
Чтобы изменить подписи значений на листе, необходимо выбрать ячейку, содержащую изменяемые данные, ввести новый текст или значение и нажать клавишу ENTER. Чтобы изменить подписи значений на диаграмме, надо один раз щелкнуть мышью изменяемую подпись, чтобы выбрать подписи для всего ряда, и щелкнуть еще раз, чтобы выбрать отдельную подпись значения. Ввести новый текст или значение и нажать клавишу ENTER. Если изменить текст подписи значений на диаграмме, то связь с ячейкой листа будет потеряна.
При большом диапазоне изменения значений для разных рядов данных в линейчатой диаграмме или при смещении типов данных (таких, как цена и объем) есть возможность отобразить один или несколько рядов данных на вспомогательной оси. Шкала этой оси соответствует значениям для соответствующих рядов:
– выберите ряды данных, которые нужно отобразить на вспомогательной оси, щелчком мыши;
– Формат – Ряды – вкладка Ось ;
– установите переключатель в положение По вспомогательной оси .
Для большинства плоских диаграмм можно изменить диаграммный тип ряда данных или диаграммы в целом. Для объемной диаграммы изменение типа диаграммы может повлечь за собой и изменение диаграммы в целом. Порядок преобразования рядов данных в конусную, цилиндрическую или пирамидальную диаграммы:
– выберите диаграмму, которую необходимо изменить, а также ряд данных на ней. Для изменения типа диаграммы в целом на самой диаграмме ничего не нажимайте;
– Диаграмма – Тип диаграммы – на вкладках Стандартные или Нестандартные выберите необходимый тип.
Для использования типов диаграмм конус, цилиндр или пирамида в объемной диаграмме или гистограмме выберите в поле Тип диаграммы в меню Стандартные пункт Цилиндр, Конус или Пирамида, а затем установите значок в поле Применить к .
Процедура изменения цветов, узора, ширины линии или типа рамки для маркеров данных, области диаграммы, области построения, сетки, осей и подписей делений на плоских и объемных диаграммах, линий тренда и планок погрешностей на плоских диаграммах, а также стенки и основания на объемных диаграммах:
– установить указатель на изменяемый элемент диаграммы и дважды нажать кнопку мыши;
– при необходимости выбрать вкладку Узор и указать нужные параметры.
Для указания эффекта заливки необходимо выбрать соответствующую команду, а затем указать нужные параметры на вкладках Градиентная, Текстура и Узор .
Работа с таблицами формата Список
Список – это упорядоченный набор данных, состоящий из строки заголовков (описания данных) и строк данных, которые могут быть числовыми и текстовыми.
Размер списка ограничен размерами одного рабочего листа, т.е. список может иметь не более 256 полей и не более 65 535 записей. Полями принято называть столбцы списка, а записями – строки.
– список обязательно должен содержать строку заголовков;
– в каждом столбце должна содержаться однотипная информация. Например, не следует смешивать в одном столбце даты и обычный текст;
– в списке не должно быть пустых строк;
– рекомендуется помещать список на отдельный лист. Но если все же на лист нужно поместить еще и другую информацию, следите, чтобы список от нее отделялся хотя бы одной пустой строкой и одним пустым столбцом. В противном случае вы рискуете приобрести, например, сотрудника с фамилией «Итого».
Для удобства работы с большими таблицами, воспользуйтесь командой Окно – Разделить . После того как на экране появятся разделительные линии, буксируйте их мышью таким образом, чтобы горизонтальная линия оказалась точно под строкой заголовков (от вертикальной линии можно отказаться, оттащив ее за пределы рабочего окна). Команда Окно – Закрепить области зафиксирует деление, и заголовки будут видны при прокручивании списка.
Excel обладает мощными средствами для работы со списками. Это:
– пополнение списка с помощью формы;
– фильтрация списка;
– сортировка списка;
– подведение промежуточных итогов;
– создание итоговой сводной таблицы на основе данных списка.
Для того чтобы воспользоваться любым из этих инструментов, нужно установить курсор на одну из ячеек списка.
При вводе данные можно добавлять непосредственно в ячейки, а можно воспользоваться специальной формой ввода (рис. 2.21).
Рис. 2.21. Форма ввода данных
Если вы выбрали первый способ, то используйте команду контекстного меню Выбрать из списка. Excel избавит вас от необходимости много раз набирать один и тот же текст.
Если вы решили прибегнуть к помощи формы ввода, поместите курсор в любое место списка и выберите команду Данные – Форма . На экране появится диалоговое окно, в котором будет отображено каждое поле списка. При этом поля, содержащие формулы, хотя и отображаются в форме ввода, их значения изменить нельзя.
Индикатор в правом верхнем углу формы показывает номер выбранной записи и общее число записей в форме.
Чтобы ввести новую запись, щелкните по кнопке Добавить . Форма очистится, и вы сможете ввести нужную информацию в соответствующие поля. После этого снова щелкните по кнопке Добавить , а если не хотите больше добавлять записи – по кнопке Закрыть .
Вновь введенные данные появятся в конце списка. Формулы, содержавшиеся в ячейках списка, автоматически будут распространены и на новую запись
Форму ввода можно использовать не только для ввода данных. Она позволяет просматривать существующие записи, редактировать их, удалять и выборочно отображать данные по определенному критерию.
Фильтрация списков
В Excel существует два типа фильтров: Автофильтр и Расширенный фильтр .
Перед тем как использовать Автофильтр , выделите любую ячейку списка. Затем выберите команду Данные – Фильтр – Автофильтр . При включении Автофильтра возле имен полей списка появятся кнопки со стрелками.
При щелчке по любой из этих кнопок раскрывается меню (рис. 2.22), содержащее команды и список значений данного поля. С помощью этого меню можно отобрать все записи с заданным значением поля.
Рис. 2.22. Вид меню, содержащего команды и список значений поля
Обратите внимание на цвет стрелок на кнопках Автофильтра : если Автофильтр включен, кнопки окрашиваются в синий цвет.
Чтобы отключить ранее заданный фильтр, в раскрывающемся меню кнопок Автофильтра следует выбрать команду Все.
Если задан сложный критерий, то придется отменять составляющие условия отбора по очереди. Иногда бывает проще отказаться от Автофильтра , выбрав команду Данные – Фильтр – Автофильтр , а потом установить Автофильтр снова.
Кроме команды Все , в раскрывающемся меню кнопок Автофильтра есть еще одна команда Первые 10 ..., которая используется для полей числового типа или дат. Эта команда покажет «горячую десятку» вашего списка.
Пусть необходимо узнать расходы за последние три дня. Щелкните по кнопке Автофильтра в столбце Дата , выберите в раскрываемся меню команду Первые 10 ..., в диалоговом окне сделайте установки, как на рис. 2.23.
Рис. 2.23. Диалоговое окно установки расходов за последние 3 дня
В окне Наложение условия по списку можно установить любое количество наибольших (или наименьших) элементов, которое хотите отобразить. Если вы хотите оставить процент записей (например, 10% наименьших значений), в третьем окне вместо Элементов списка установите % от количества элементов . При создании сложного условия отбора команда Первые 10 ... всегда применяется ко всему списку.
Иногда стандартных условий Автофильтра оказывается недостаточно. Для создания собственного Автофильтра необходимо:
– для выбранного поля (например, Менеджер ) из раскрывающегося меню кнопки Автофильтра выбрать команду (Условие …);
– в диалоговом окне Пользовательский автофильтр (рис. 2.24) задать условия отбора значений списка.
Рис. 2.24. Окно Пользовательский автофильтр
Если вы применяете Пользовательский автофильтр к текстовому полю, в качестве логической функции, связывающей условия, всегда выбирайте ИЛИ.
Для полей числового типа или дат используются следующие правила:
– И , когда интересует область между двумя числами или датами;
– ИЛИ , если интересует область вне интервала, заданного двумя числами или датами.
Расширенный фильтр
Часто для отбора нужной информации из списка бывает вполне достаточно Автофильтра или пользовательского фильтра. Однако для решения сложной задачи приходится прибегать к помощи расширенной фильтрации. Расширенный фильтр гораздо гибче Автофильтра , но чтобы воспользоваться им, придется выполнить подготовительные действия.
С помощью Расширенного фильтра (рис. 2.25) можно:
– определить более сложный критерий фильтрации;
– помещать результат отбора данных на другое место и даже на новый лист рабочей книги;
– устанавливать вычисляемый критерий отбора.
Рис. 2.25. Окно Расширенный фильтр
Чтобы воспользоваться Расширенным фильтром , необходимо задать диапазон критериев.
Диапазон критериев – область рабочего листа, в которой формируется условие (условия) отбора. Диапазон критериев должен состоять, по крайней мере, из двух строк, первая из которых содержит все или некоторые названия полей списка.
Удобнее всего отвести для диапазона критериев область над списком. Названия полей, не используемых при фильтрации, можно не помещать в диапазон критериев. Но если вы предполагаете, что в дальнейшем в зависимости от обстоятельств вам может понадобиться и другая информация из списка, скопируйте строку, содержащую названия полей списка, целиком.
Условия отбора следует вносить в пустые ячейки диапазона критериев. Условия отбора, расположенные в ячейках одной строки, соединяются оператором И . Условия, расположенные на разных строках, соединяются оператором ИЛИ . Диапазон критериев может состоять из любого количества строк.
Область ячеек, содержащих критерии, должна отделяться от списка, по крайней мере, одной пустой строкой.
Для того чтобы отключить Расширенный фильтр , используют команду Данные – Фильтр – Отобразить все.
При использовании вычисляемого критерия отбор производится «по несуществующему полю». При создании формул вычисляемых критериев всегда ссылайтесь на первую строку списка, а не на строку заголовков. Если в формулу будут подставляться значения вне списка, используют абсолютные ссылки.
Если отфильтрованный список должен быть помещен на другой лист рабочей книги, сначала переходят на этот лист и только потом обращаются к команде Данные – Фильтр – Расширенный фильтр.
Если вам нужно разработать сложные статистические или инженерные анализы, вы можете сэкономить этапы и время с помощью пакета анализа. Вы предоставляете данные и параметры для каждого анализа, и в этом средстве используются соответствующие статистические или инженерные функции для вычисления и отображения результатов в выходной таблице. Некоторые инструменты создают диаграммы в дополнение к выходным таблицам.
Функции анализа данных можно применять только на одном листе. Если анализ данных проводится в группе, состоящей из нескольких листов, то результаты будут выведены на первом листе, на остальных листах будут выведены пустые диапазоны, содержащие только форматы. Чтобы провести анализ данных на всех листах, повторите процедуру для каждого листа в отдельности.
Ниже описаны инструменты, включенные в пакет анализа. Для доступа к ним нажмите кнопкуАнализ данных в группе Анализ на вкладке Данные . Если команда Анализ данных недоступна, необходимо загрузить надстройку "Пакет анализа".
Примечание: Чтобы включить функцию Visual Basic для приложений (VBA) для пакета анализа, вы можете загрузить надстройку "пакет анализа - VBA" таким же образом, как и при загрузке пакета анализа. В диалоговом окне Доступные надстройки установите флажок Пакет анализа - VBA .
Дисперсионный анализ
Существует несколько видов дисперсионного анализа. Нужный вариант выбирается с учетом числа факторов и имеющихся выборок из генеральной совокупности.
Однофакторный дисперсионный анализ
Это средство выполняет простой анализ дисперсии для данных двух или более образцов. Анализ - это проверка гипотезы о том, что каждый образец выводится из того же основного распределения вероятности, что и для альтернативной гипотезы, для которой базовые распределения вероятностей не одинаковы. Если есть только два примера, вы можете использовать функцию на листе T . Проверка . В более чем двух выборках нет удобной обобщения с T . И может быть вызвана модель однофакторного дисперсионный проверки .
Двухфакторный дисперсионный анализ с повторениями
Этот инструмент анализа применяется, если данные можно систематизировать по двум параметрам. Например, в эксперименте по измерению высоты растений последние обрабатывали удобрениями от различных изготовителей (например, A, B, C) и содержали при различной температуре (например, низкой и высокой). Таким образом, для каждой из 6 возможных пар условий {удобрение, температура}, имеется одинаковый набор наблюдений за ростом растений. С помощью этого дисперсионного анализа можно проверить следующие гипотезы:
Извлечены ли данные о росте растений для различных марок удобрений из одной генеральной совокупности. Температура в этом анализе не учитывается.
Извлечены ли данные о росте растений для различных уровней температуры из одной генеральной совокупности. Марка удобрения в этом анализе не учитывается.
Извлечены ли шесть выборок, представляющих все пары значений {удобрение, температура}, используемые для оценки влияния различных марок удобрений (для первого пункта в списке) и уровней температуры (для второго пункта в списке), из одной генеральной совокупности. Альтернативная гипотеза предполагает, что влияние конкретных пар {удобрение, температура} превышает влияние отдельно удобрения и отдельно температуры.
Двухфакторный дисперсионный анализ без повторений
Этот инструмент анализа применяется, если данные можно систематизировать по двум параметрам, как в случае двухфакторного дисперсионного анализа с повторениями. Однако в таком анализе предполагается, что для каждой пары параметров есть только одно измерение (например, для каждой пары параметров {удобрение, температура} из предыдущего примера).
Корреляция
На листе КОРРЕЛ и Пирсон рассчитываются коэффициент корреляции между двумя переменными измерения, если измерения для каждой переменной отображаются для каждого из N субъектов. (Отсутствие наблюдения для какой-либо из тем приводит к тому, что эта тема пропускается в анализе.) Средство анализа корреляции особенно полезно, если для N тем используется более двух переменных измерения. Она предоставляет выходную таблицу, матрицу корреляции, которая показывает значение КОРРЕЛ (или Пирсона ), примененное к каждой возможной паре переменных измерения.
Коэффициент корреляции, например Ковариация, - это мера экстента, в котором одновременно различаются две переменные измерения. В отличие от ковариации коэффициент корреляции масштабируется таким образом, чтобы его значение не зависело от единиц, в которых выражаются две переменные измерения. (Например, если две переменные измерения являются весом и высотой, значение коэффициента корреляции не меняется, если вес конвертируется из килограммов в килограммы). Значение любого коэффициента корреляции должно находиться в диапазоне от-1 до + 1 включительно.
Корреляционный анализ дает возможность установить, ассоциированы ли наборы данных по величине, т. е. большие значения из одного набора данных связаны с большими значениями другого набора (положительная корреляция) или наоборот, малые значения одного набора связаны с большими значениями другого (отрицательная корреляция), или данные двух диапазонов никак не связаны (нулевая корреляция).
Ковариация
Вы можете использовать инструменты корреляции и ковариации в одном и том же параметре, если у вас есть N различных переменных измерения, которые потратили на набор отдельных пользователей. Средства корреляции и ковариации предоставляют выходную таблицу, матрицу, которая показывает коэффициент корреляции или ковариацию соответственно между каждой парой переменных измерения. Разница заключается в том, что коэффициенты корреляции масштабируются в зависимости от-1 и + 1 включительно. Соответствующие ковариации не масштабируются. Как коэффициент корреляции, так и ковариация - это величины экстентов, в которых две переменные различны друг от друга.
Инструмент Ковариация вычисляет значение функции КОвариация на листе. P для каждой пары переменных измерения. (Прямое использование ковариации. Функция P вместо средства Ковариация является разумной альтернативой, если есть только две переменные измерения, т. е. N = 2.) Запись по диагонали в выходной таблице инструмента ковариации в строке i - это Ковариация переменной измерения i-ой. Это всего лишь дисперсия Генеральной совокупности для этой переменной, вычисленная функцией на листе var . P .
Ковариационный анализ дает возможность установить, ассоциированы ли наборы данных по величине, то есть большие значения из одного набора данных связаны с большими значениями другого набора (положительная ковариация) или наоборот, малые значения одного набора связаны с большими значениями другого (отрицательная ковариация), или данные двух диапазонов никак не связаны (ковариация близка к нулю).
Описательная статистика
Инструмент анализа "Описательная статистика" применяется для создания одномерного статистического отчета, содержащего информацию о центральной тенденции и изменчивости входных данных.
Экспоненциальное сглаживание
Инструмент анализа "Экспоненциальное сглаживание" применяется для предсказания значения на основе прогноза для предыдущего периода, скорректированного с учетом погрешностей в этом прогнозе. При анализе используется константа сглаживания a , величина которой определяет степень влияния на прогнозы погрешностей в предыдущем прогнозе.
Примечание: Для константы сглаживания наиболее подходящими являются значения от 0,2 до 0,3. Эти значения показывают, что ошибка текущего прогноза установлена на уровне от 20 до 30 процентов ошибки предыдущего прогноза. Более высокие значения константы ускоряют отклик, но могут привести к непредсказуемым выбросам. Низкие значения константы могут привести к большим промежуткам между предсказанными значениями.
Двухвыборочный t-тест для дисперсии
Двухвыборочный F-тест применяется для сравнения дисперсий двух генеральных совокупностей.
Например, можно использовать F-тест по выборкам результатов заплыва для каждой из двух команд. Это средство предоставляет результаты сравнения нулевой гипотезы о том, что эти две выборки взяты из распределения с равными дисперсиями, с гипотезой, предполагающей, что дисперсии различны в базовом распределении.
С помощью этого инструмента вычисляется значение f F-статистики (или F-коэффициент). Значение f, близкое к 1, показывает, что дисперсии генеральной совокупности равны. В таблице результатов, если f < 1, "P(F <= f) одностороннее" дает возможность наблюдения значения F-статистики меньшего f при равных дисперсиях генеральной совокупности и F критическом одностороннем выдает критическое значение меньше 1 для выбранного уровня значимости "Альфа". Если f > 1, "P(F <= f) одностороннее" дает возможность наблюдения значения F-статистики большего f при равных дисперсиях генеральной совокупности и F критическом одностороннем дает критическое значение больше 1 для "Альфа".
Анализ Фурье
Инструмент "Анализ Фурье" применяется для решения задач в линейных системах и анализа периодических данных на основе метода быстрого преобразования Фурье (БПФ). Этот инструмент поддерживает также обратные преобразования, при этом инвертирование преобразованных данных возвращает исходные данные.
Гистограмма
Инструмент "Гистограмма" применяется для вычисления выборочных и интегральных частот попадания данных в указанные интервалы значений. При этом рассчитываются числа попаданий для заданного диапазона ячеек.
Например, можно получить распределение успеваемости по шкале оценок в группе из 20 студентов. Таблица гистограммы состоит из границ шкалы оценок и групп студентов, уровень успеваемости которых находится между самой нижней границей и текущей границей. Наиболее часто встречающийся уровень является модой диапазона данных.
Скользящее среднее
Инструмент анализа "Скользящее среднее" применяется для расчета значений в прогнозируемом периоде на основе среднего значения переменной для указанного числа предшествующих периодов. Скользящее среднее, в отличие от простого среднего для всей выборки, содержит сведения о тенденциях изменения данных. Этот метод может использоваться для прогноза сбыта, запасов и других тенденций. Расчет прогнозируемых значений выполняется по следующей формуле:
N - число предшествующих периодов, входящих в скользящее среднее;
A j - фактическое значение в момент времени j ;
F j - прогнозируемое значение в момент времени j .
Генерация случайных чисел
Инструмент "Генерация случайных чисел" применяется для заполнения диапазона случайными числами, извлеченными из одного или нескольких распределений. С помощью этой процедуры можно моделировать объекты, имеющие случайную природу, по известному распределению вероятностей. Например, можно использовать нормальное распределение для моделирования совокупности данных по росту людей или использовать распределение Бернулли для двух вероятных исходов, чтобы описать совокупность результатов бросания монеты.
Ранг и персентиль
Инструмент «ранжирование и персентиль» формирует таблицу, содержащую порядковый и процентный ранги для каждого значения в наборе данных. Вы можете проанализировать относительные значения в наборе данных. Это средство использует функции ранжирования на листе. EQ иПРОЦЕНТРАНГ. INC . Если вы хотите учитывать привязанные значения, используйте ранг. EQ , который обрабатывает привязанные значения в соответствии с одинаковым рангом или использует ранг. Функция AVG , возвращающая среднее значение ранга для привязанных значений.
Регрессия
Инструмент анализа "Регрессия" применяется для подбора графика для набора наблюдений с помощью метода наименьших квадратов. Регрессия используется для анализа воздействия на отдельную зависимую переменную значений одной или нескольких независимых переменных. Например, на спортивные качества атлета влияют несколько факторов, включая возраст, рост и вес. Можно вычислить степень влияния каждого из этих трех факторов по результатам выступления спортсмена, а затем использовать полученные данные для предсказания выступления другого спортсмена.
Средство регрессия использует функцию листа ЛИНЕЙН .
Инструмент анализа "Выборка" создает выборку из генеральной совокупности, рассматривая входной диапазон как генеральную совокупность. Если совокупность слишком велика для обработки или построения диаграммы, можно использовать представительную выборку. Кроме того, если предполагается периодичность входных данных, то можно создать выборку, содержащую значения только из отдельной части цикла. Например, если входной диапазон содержит данные для квартальных продаж, создание выборки с периодом 4 разместит в выходном диапазоне значения продаж из одного и того же квартала.
Двухвыборочный t-тест проверяет равенство средних значений генеральной совокупности по каждой выборке. Три вида этого теста допускают следующие условия: равные дисперсии генерального распределения, дисперсии генеральной совокупности не равны, а также представление двух выборок до и после наблюдения по одному и тому же субъекту.
Для всех трех средств, перечисленных ниже, значение t вычисляется и отображается как "t-статистика" в выводимой таблице. В зависимости от данных это значение t может быть отрицательным или неотрицательным. Если предположить, что средние генеральной совокупности равны, при t < 0 "P(T <= t) одностороннее" дает вероятность того, что наблюдаемое значение t-статистики будет более отрицательным, чем t. При t >=0 "P(T <= t) одностороннее" делает возможным наблюдение значения t-статистики, которое будет более положительным, чем t. "t критическое одностороннее" дает пороговое значение, так что вероятность наблюдения значения t-статистики большего или равного "t критическое одностороннее" равно "Альфа".
"P(T <= t) двустороннее" дает вероятность наблюдения значения t-статистики, по абсолютному значению большего, чем t. "P критическое двустороннее" выдает пороговое значение, так что значение вероятности наблюдения значения t- статистики, по абсолютному значению большего, чем "P критическое двустороннее", равно "Альфа".
Парный двухвыборочный t-тест для средних
Парный тест используется, когда имеется естественная парность наблюдений в выборках, например, когда генеральная совокупность тестируется дважды - до и после эксперимента. Этот инструмент анализа применяется для проверки гипотезы о различии средних для двух выборок данных. В нем не предполагается равенство дисперсий генеральных совокупностей, из которых выбраны данные.
Примечание: Одним из результатов теста является совокупная дисперсия (совокупная мера распределения данных вокруг среднего значения), вычисляемая по следующей формуле:
Двухвыборочный t-тест с одинаковыми дисперсиями
Это средство анализа выполняет двухвыборочный t-тест учащегося. В этой форме t-тест предполагается, что два набора данных получены из распространения с одинаковыми дисперсиями. Она называется гомоскедастическийм t-тестом. Вы можете использовать этот t-тест, чтобы определить, могут ли два примера быть получены из распределений с одинаковым заполнением.
Двухвыборочный t-тест с различными дисперсиями
Это средство анализа выполняет двухвыборочный t-тест учащегося. В этой форме t-тест предполагается, что два набора данных получены из распространения с неравными дисперсиями. Она называется гетероскедастическийм t-тестом. Как и в предыдущем случае с одинаковыми дисперсиями, вы можете использовать этот t-тест, чтобы определить, должны ли два примера поступать из распределения с одинаковым заполнением. Используйте этот тест, если в двух примерах есть отдельные темы. Используйте парный тест, описанный в приведенном ниже примере, когда есть один набор тем, а в двух образцах - измерения для каждой темы до и после обработки.
Для определения тестовой величины t используется следующая формула.
Следующая формула используется для вычисления степеней свободы, DF. Так как результат вычисления обычно не является целым числом, значение df округляется до ближайшего целого числа, чтобы получить критическое значение из t-таблицы. Функция листа Excel - T . Тест использует вычисляемое значение DF без округля, так как можно вычислить значение для T . Проверка с нецелочисленной DF. Из-за разных подходов к определению степеней свободы результаты в T . Тестирование и это средство t-тест будет отличаться в случае неравной вариации.
Двухвыборочный z-тест для средних - это Двухвыборочный z-тест для средних и известных отклонений. Это средство используется для проверки гипотезы на то, что в двух или двусторонних вариантах есть различия между двумя единицами заполнения. Если вариативность неизвестна, то функция листа Z . Вместо этого следует использовать проверку .
При использовании этого инструмента следует внимательно просматривать результат. "P(Z <= z) одностороннее" на самом деле есть P(Z >= ABS(z)), вероятность z-значения, удаленного от 0 в том же направлении, что и наблюдаемое z-значение при одинаковых средних значениях генеральной совокупности. "P(Z <= z) двустороннее" на самом деле есть P(Z >= ABS(z) или Z <= -ABS(z)), вероятность z-значения, удаленного от 0 в том же направлении, что и наблюдаемое z-значение при одинаковых средних значениях генеральной совокупности. Двусторонний результат является односторонним результатом, умноженным на 2. Инструмент "z-тест" можно также применять для гипотезы об определенном ненулевом значении разницы между двумя средними генеральных совокупностей. Например, этот тест можно использовать для определения разницы выступлений на соревнованиях двух автомобилей разных марок.
Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также (на английском языке).
