Корреляционно-регрессионный анализ в excel: инструкция выполнения

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

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

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

1. Перейти на вкладку «Вставка» и щелкнуть по кнопке «Таблица».
2. Откроется диалоговое окно «Создание таблицы».
3. Указать диапазон данных (если они уже внесены) или предполагаемый диапазон (в какие ячейки будет помещена таблица). Установить флажок напротив «Таблица с заголовками». Нажать Enter.

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

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

1. Активизируем любую из ячеек диапазона данных. Щелкаем кнопку «Сводная таблица» («Вставка» – «Таблицы» – «Сводная таблица»).
2. В диалоговом окне прописываем диапазон и место, куда поместить сводный отчет (новый лист).
3. Открывается «Мастер сводных таблиц». Левая часть листа – изображение отчета, правая часть – инструменты создания сводного отчета.
4. Выбираем необходимые поля из списка. Определяемся со значениями для названий строк и столбцов. В левой части листа будет «строиться» отчет.

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

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

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

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

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

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

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

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

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

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

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

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

Как работать

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

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

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

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

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

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

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

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

Как работать

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

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

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

Прочие инструменты

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

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

Жми «Нравится» и получай только лучшие посты в Facebook ↓

Надстройка «Анализ данных» в Экселе

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

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

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

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

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

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

• Дисперсионный. Вы можете выбрать из предложенных вариантов в списке (однофакторный, двухфакторный с повторениями, двухфакторный без повторений). Всё зависит от количества факторов и выборок.
• Корреляционный. Позволяет построить корреляционную матрицу. Такой подход даёт возможность определить, связаны ли большие значения одной группы данных с большими значениями другой группы. Или проделать то же самое для маленьких значений. Это называется отрицательной корреляцией.
• Ковариационный. Используется в случаях, когда необходимо посчитать функцию «КОВАРИАЦИЯ.Г». Также такой тип анализа позволяет определить, ассоциированы ли группы данных по величине.
• Фурье. Применяется, когда необходимо решить задачу в линейных системах либо проанализировать периодические данные.
• Гистограмма. Очень удобно использовать для решения задач типа: распределить значение успеваемости студентов в группе.
• Скользящее среднее. Применяется, когда нужно рассчитать значения, находящиеся в прогнозируемом периоде, основываясь на среднем значении переменной.
• Генерация случайных чисел. Заполняет указанный диапазон случайными числами.
• Ранг и перцентиль. Нужен, чтобы вывести таблицу с порядковым и центральным рангами.
• Регрессия. Позволяет подобрать график набора наблюдений, применяя метод наименьших квадратов.
• Выборка. Применяется в случаях, когда нужно создать выборку из генеральной совокупности, в качестве которой выступает входной диапазон.
• Т-тест. Даёт возможность проверить на равенство значения по каждой выборке. Существует несколько разновидностей этого инструмента. Выбирайте тот вариант, который больше подходит для решения текущей задачи.
• Z-тест. Этот инструмент нужен, чтобы проверять гипотезу о неразличии между средними одной и другой генеральных совокупностей относительно одно- и двусторонней гипотез.

Пример решения транспортной задачи в Excel

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

Условия задачи

Имеем 5 поставщиков и 6 покупателей. Объёмы производства этих поставщиков составляют 48, 65, 51, 61, 53 единиц. Потребность покупателей: 43, 47, 42, 46, 41, 59 единиц. Таким образом, общий объем предложения равен величине спроса, то есть, мы имеем дело с закрытой транспортной задачей.

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

Решение задачи

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

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

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

Открывается «Мастер функций». В списке, который предлагает он, нам следует отыскать функцию СУММПРОИЗВ. Выделяем её и жмем на кнопку «OK».

Открывается окно ввода аргументов функции СУММПРОИЗВ. В качестве первого аргумента внесем диапазон ячеек матрицы затрат. Для этого достаточно выделить курсором данные ячейки. Вторым аргументом выступит диапазон ячеек таблицы, которая была приготовлена для расчетов. Затем, жмем на кнопку «OK».

Кликаем по ячейке, которая расположена слева от верхней левой ячейки таблицы для расчетов. Как и в прошлый раз вызываем Мастер функций, открываем в нём аргументы функции СУММ. Кликнув по полю первого аргумента, выделяем весь верхний ряд ячеек таблицы для расчетов. После того, как их координаты занесены в соответствующее поле, кликаем по кнопке «OK».

Становимся в нижний правый угол ячейки с функцией СУММ. Появляется маркер заполнения. Жмем на левую кнопку мыши и тянем маркер заполнения вниз до конца таблицы для расчета. Таким образом мы скопировали формулу.

Кликаем по ячейке размещенной сверху от верхней левой ячейки таблицы для расчетов. Как и в предыдущий раз вызываем функцию СУММ, но на этот раз в качестве аргумента используем первый столбец таблицы для расчетов. Жмем на кнопку «OK».

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

Переходим во вкладку «Данные». Там в блоке инструментов «Анализ» кликаем по кнопке «Поиск решения».

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

Запускается окно добавления ограничения. Прежде всего, нам нужно добавить условие того, что сумма данных в строках таблицы для расчетов должна быть равна сумме данных в строках таблицы с условием. В поле «Ссылка на ячейки» указываем диапазон суммы в строках таблицы расчетов. Затем выставляем знак равно (=). В поле «Ограничение» указываем диапазон сумм в строках таблицы с условием. После этого, жмем на кнопку «OK».

Аналогичным образом добавляем условие, что столбцы двух таблиц должны быть равны между собой. Добавляем ограничение, что сумма диапазона всех ячеек в таблице для расчета должна быть большей или равной 0, а также условие, что она должна быть целым числом. Общий вид ограничений должен быть таким, как представлен на изображении ниже. Обязательно проследите, чтобы около пункта «Сделать переменные без ограничений неотрицательными» стояла галочка, а методом решения был выбран «Поиск решения нелинейных задач методом ОПГ». После того, как все настройки указаны, жмем на кнопку «Найти решение».

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

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

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

Строим диаграмму рассеяния (корреляционное поле) и график линии регрессии.

4.1.
Находим минимальный и максимальный элемент выборки X это 18-й и 15-й элементы соответственно, x min = 22.10000 и x max = 26.60000.

4.2.
Находим минимальный и максимальный элемент выборки Y это 2-й и 18-й элементы соответственно, y min = 29.40000 и y max = 31.60000.

4.3.
На оси абсцисс выбираем начальную точку чуть левее точки x 18
= 22.10000, и такой масштаб, чтобы на оси
поместилась точка x 15
= 26.60000 и отчетливо различались остальные точки.

4.4.
На оси ординат выбираем начальную точку чуть левее точки y 2
= 29.40000, и такой масштаб, чтобы на оси
поместилась точка y 18
= 31.60000 и отчетливо различались остальные точки.

4.5.
На оси абсцисс размещаем значения x k
, а на оси ординат значения y k
.

4.6.
Наносим точки (x 1
, y 1
),
(x 2
, y 2
),…,(x 26
, y 26
)
на координатную плоскость. Получаем диаграмму рассеяния (корреляционное поле), изображенное на рисунке ниже.

4.7.
Начертим линию регрессии.

Для этого найдем две различные точки с координатами (x r1 , y r1) и (x r2 , y r2)
удовлетворяющие уравнению (3.6), нанесем их на координатную плоскость и проведем через них прямую. В качестве абсциссы первой точки возьмем значение x min = 22.10000. Подставим значение x min в уравнение (3.6),
получим ординату первой точки. Таким образом имеем точку с координатами (22.10000, 31.96127). Аналогичным образом получим координаты второй точки, положив в качестве абсциссы значение x max = 26.60000.
Вторая точка будет: (26.60000, 30.15970).

Линия регрессии показана на рисунке ниже красным цветом

Обратите внимание, что линия регрессии всегда проходит через точку средних значений величин Х и Y, т.е. с координатами (M x , M y)

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

Начнём с того, что такое коэффициент корреляции вообще. Он показывает степень взаимосвязи между двумя элементами и всегда находится в диапазоне от -1 (сильная обратная взаимосвязь) до 1 (сильная прямая взаимосвязь). Если коэффициент равен 0, это говорит о том, что взаимосвязь между значениями отсутствует.

Теперь, разобравшись с теорией, перейдём к практике. Чтобы найти взаимосвязь между переменными и у, воспользуйтесь встроенной функцией Microsoft Excel «КОРРЕЛ». Для этого нажмите на кнопку мастера функций (она расположена рядом с полем для формул). В открывшемся окне выберите из списка функций «КОРРЕЛ». После этого задайте диапазон в полях «Массив1» и «Массив2». Например, для «Массив1» выделите значения у, а для «Массив2» выделите значения х. В итоге вы получите рассчитанный программой коэффициент корреляции.

Следующий способ будет актуален для студентов, от которых требуют найти зависимость по заданной формуле. Прежде всего, нужно знать средние значения переменных x и y. Для этого выделите значения переменной и воспользуйтесь функцией «СРЗНАЧ». Далее необходимо вычислить разницу между каждым x и x ср, и y ср. В выбранных ячейках напишите формулы x-x, y-. Не забудьте закрепить ячейки со средними значениями. Затем растяните формулу вниз, чтобы она применилась и к остальным числам.

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

Теперь перейдём к знаменателю. Посчитанные разности нужно возвести в квадрат. Для этого в отдельной колонке введите формулы: (x-x ср) 2 и (y-y ср) 2 . Затем растяните формулы на весь диапазон. После, при помощи кнопки «Автосумма», найдите сумму по всем колонкам (для x и для y). Осталось перемножить найденные суммы и извлечь из них квадратный корень. Последний шаг — поделите числитель на знаменатель. Полученный результат и будет искомым коэффициентом корреляции.

Как найти «анализ данных» в экселе 2003?

​ В окне Список​​ Means);​​ команду Параметры, а​ входит в состав​ несколько таблиц с​Добавление промежуточных итогов в​ или возрастанию.​Создание сводной диаграммы​ можно взглянуть под​ данных. Только добавление​ предварительного просмотра, а​ которая включает функции​Пакет анализа​установите флажок​ с помощью подходящей​​ предлагает функция​«Надстройки»​​ надстроек установите флажок​Двухвыборочный t-тест с одинаковыми​ затем — категорию​ Office профессиональный плюс,​ данными. Во второй​ сводную таблицу​​Фильтрация данных в сводной​Чтобы провести наглядную презентацию,​ разным углом. Excel​ данных в нескольких​ затем выберите подходящий.​​ надстройки «Пакет анализа»,​, а затем нажмите​Пакет анализа​ статистической или инженерной​«Анализ данных»​(предпоследний в списке​ рядом с элементом​​ дисперсиями (t-Test: Two-Sample​ Надстройки.​ позволяет создавать интерактивные​ его части описывается​Промежуточные итоги в сводных​ таблице​​ создайте сводную диаграмму​ поможет вам приступить​ таблицах в Excel,​Примечание:​ или заказать одно​ кнопку​, а затем нажмите​ макрофункции, а результат​. Среди них можно​ в левой части​ Пакет анализа VBA.​ Assuming Equal Variances);​в списке Управление (внизу​

​ диаграммы и другие​

• Анализ что если эксель
• Проверка данных эксель
• Работа с массивами данных эксель
• Таблица данных в эксель
• Экспорт данных из эксель в эксель
• Как в эксель данные из строки перенести в столбцы
• Как в эксель построить график по данным таблицы
• База данных в эксель
• Завис эксель как сохранить данные
• Анализ чувствительности в excel пример таблица данных
• Как из ворда в эксель перенести данные
• Как перевести таблицу из ворда в эксель без потери данных

Функция ЕСЛИ в Excel (общие сведения)

Любая программа пусть это даже небольшая, обязательно содержит последовательность действий, которая называется алгоритмом. Выглядеть он может так:

1. Проверить всю колонку А на предмет наличия четных чисел.
2. Если четное число обнаружено, сложить такие-то значения.
3. Если четное число не обнаружено, то выдать надпись «не обнаружено».
4. Проверить получившееся число, является ли оно четным. 
5. Если да, то сложить его со всеми четными числами, отобранными в пункте 1.

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

Синтаксис функции ЕСЛИ с одним условием

Любая функция в Ecxel выполняется с помощью формулы. Шаблон, по которому нужно передавать функции данные, называется синтаксисом. В случае с оператором ЕСЛИ, формула будет в таком формате.

=ЕСЛИ (логическое_выражение;значение_если_истина;значение_если_ложь)

Давайте рассмотрим синтаксис более подробно:

1. Логическое выражение. Это непосредственно условие, соответствие или несоответствие которому проверяет Excel. Проверяться могут как числовые, так и текстовая информация.
2. Значение_если_истина. Результат, который отобразится в ячейке в случае, если проверяемые данные соответствуют заданному критерию.
3. Значение_если_ложь. Результат, который выводится в ячейку, если проверяемые данные не соответствуют условию.

Вот пример для наглядности.

1

Здесь функция осуществляет сравнение ячейки А1 с числом 20. Это первый пункт синтаксиса. Если содержимое оказывается больше этого значения, в ячейку, где формула была прописана, выводится значение «больше 20». Если же этому условию ситуация не соответствует – «меньше или равно 20».

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

2

Поскольку нам нужно проверить текст, а не число, в первом аргументе указывается B2=”зач.”.

Синтаксис функции ЕСЛИ с несколькими условиями

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

Чтобы было более наглядно, вот синтаксис.

=ЕСЛИ(логическое_выражение;значение_если_истина;ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь))

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

Вот пример.

3

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

4

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

3D-карты

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

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

Как работать

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

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

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