Входной интервал содержит нечисловые данные excel
Обновлено: 06.07.2024
Одним из самых распространенных методов, применяемых в статистике для изучения данных, является корреляционный анализ, с помощью которого можно определить влияние одной величины на другую. Давайте разберемся, каким образом данный анализ можно выполнить в Экселе.
Назначение корреляционного анализа
Корреляционный анализ позволяет найти зависимость одного показателя от другого, и в случае ее обнаружения – вычислить коэффициент корреляции (степень взаимосвязи), который может принимать значения от -1 до +1:
-
если коэффициент отрицательный – зависимость обратная, т.е. увеличение одной величины приводит к уменьшению второй и наоборот.
Сила зависимости определяется по модулю коэффициента корреляции. Чем больше значение, тем сильнее изменение одной величины влияет на другую. Исходя из этого, при нулевом коэффициенте можно утверждать, что взаимосвязь отсутствует.
Выполняем корреляционный анализ
Для изучения и лучшего понимания корреляционного анализа, давайте попробуем его выполнить для таблицы ниже.
Здесь указаны данные по среднесуточной температуре и средней влажности по месяцам года. Наша задача – выяснить, существует ли связь между этими параметрами и, если да, то насколько сильная.
Метод 1: применяем функцию КОРРЕЛ
В Excel предусмотрена специальная функция, позволяющая сделать корреляционный анализ – КОРРЕЛ. Ее синтаксис выглядит следующим образом:
Порядок действий при работе с данным инструментом следующий:
Метод 2: используем “Пакет анализа”
Альтернативным способом выполнения корреляционного анализа является использование “Пакета анализа”, который предварительно нужно включить. Для этого:
Все готово, “Пакет анализа” активирован. Теперь можно перейти к выполнению нашей основной задачи:
Заключение
Таким образом, выполнение корреляционного анализа в Excel – достаточно автоматизированная и простая в освоении процедура. Все что нужно знать – где найти и как настроить необходимый инструмент, а в случае с “Пакетом решения”, как его активировать, если до этого он уже не был включен в параметрах программы.
При использовании функции ЛИНЕЙН на листе в Microsoft Excel результаты статистического вывода могут возвращать неверные значения. Средство регрессия в окне "пакет анализа" может также возвращать неверные значения.
Причина
Результат, возвращаемый функцией ЛИНЕЙН, может быть неправильным, если выполняется одно или несколько из указанных ниже условий.
Диапазон значений x перекрывает диапазон значений y.
Количество строк в диапазоне входных данных меньше числа столбцов в общем диапазоне (x-value + y-Value).
Вы задаете нулевую константу (для третьего аргумента функции ЛИНЕЙН установите значение истина).
Обходное решение
Случай 1: диапазоны x-value и y перекрываются
Если диапазоны x-value и y перекрываются, функция ЛИНЕЙН возвращает неверные значения во всех ячейках результата. Нормальная статистическая вероятность запрещает значения в диапазонах x и y для перекрытия (повторяющиеся друг друга). Не перекрывают диапазоны x и y при ссылке на ячейки в формуле.Примечание. Средство регрессия предупреждает об этой проблеме и не продолжает работу. Вы можете использовать средство регрессия вместо функции ЛИНЕЙН. В Microsoft Office Excel 2007 вы можете найти инструмент регрессия, щелкнув анализ данных в группе анализ на вкладке данные . В Microsoft Office Excel 2003 и более ранних версиях Excel можно найти инструмент регрессия, выбрав пункт анализ данных в меню Сервис .
Случай 2: количество строк меньше числа столбцов x-Columns.
Статистические функции не действительны, так как количество строк должно быть меньше числа столбцов x (переменных). Количество строк данных должно быть больше количества столбцов данных (столбцов x и y).
Случай 3: указывается нулевая константа
Не указывайте нулевые константы (b = 0) в функции.
Дополнительная информация
Средство регрессия входит в пакет анализа. Пакет анализа — это программа надстройки Excel. Оно доступно при установке Microsoft Office или Excel. Прежде чем использовать средство регрессия в Excel, вы должны загрузить анализ ToolPak.To в Excel 2007, выполнив указанные ниже действия.
Выберите пункт надстройки, а затем в поле Управление выберите пункт надстройки Excel .
В окне Доступные надстройки установите флажок Пакет анализа , а затем нажмите кнопку ОК.Примечание. Если в списке Доступные надстройки не указан Пакет анализа , нажмите кнопку Обзор , чтобы найти его.
Чтобы сделать это в Excel 2003 и более ранних версиях Excel, выполните указанные ниже действия.
В меню Сервисвыберите пунктнадстройки.
В диалоговом окне надстройки выберите Пакет анализаи нажмите кнопку ОК,Обратите внимание на то, что Пакет анализа не указан в поле Доступные надстройки, нажмите кнопку Обзор , чтобы найти его.
Ссылки
Статистические вычисления на цифровом компьютере. Уильям J. Hemmerle. Blaisdell компания публикации: 1967. Глава 3, "вычисления с несколькими регрессиями" и раздел 3.2.1, "теория для предварительной регрессии".
Рис. П. 1. Задание парной регрессии
Ставим «галочку» в окно «Метки», если в отчете Microsoft Excel требуется знать, к какой из объясняющих переменных относятся результирующие данные.
Если исследователю не требуется константа Ь0, то ставим «галочку» в окно «Константа — ноль». Линия регрессии пройдет через начало координат.
Рис. П.2. Задание множественной регрессии
«Уровень надежности». По умолчанию программа строит уравнение регрессии для доверительной вероятности (уровень надежности) 0,95. Если требуется другая величина, ставим «галочку» в окно «Уровень надежности» и в окно, помеченное символом «%», вводим требуемую величину уровня надежности десятичной дробью.
«Параметры вывода». Указываем, куда вывести результаты регрессионного анализа: на этом листе, как указано на обоих рисунках, на другой рабочий лист или в новую рабочую книгу.
«Остатки». Выбираем то, что требуется исследователю, и ставим «галочку». Можно одновременно пометить несколько окон. Подробная информация дана в справке но инструменту «Регрессия».
Заполнив диалоговое окно «Регрессия», нажимаем кнопку ОК. Программа выводит отчет «Вывод итогов» в виде трех таблиц (рис. П.З, приведено для двух объясняющих переменных).
Приведем описание таблиц (первых двух — в табл. П1.1 и П1.2 соответственно, третьей - в текстовом виде).
Таблица П1.1
Описание первой таблицы
Наименование в отчете
Множественный R
Коэффициент множественной корреляции, индекс корреляции
Коэффициент детерминации, R 2
Скорректированный К 2
Наименование в отчете
Среднее квадратическое отклонение от модели
Рис. П.З. Результаты работы программы
Описание третьей таблицы
Данные первой строки относятся к коэффициенту уравнения регрессии Ь0, данные второй строки — к коэффициенту Ьи третьей — к Ь2 и далее до коэффициента Ьт, но числу объясняющих переменных в уравнении.
Метки, если поставлена галочка в окно «Метки». У-пересечение для коэффициента />0, далее но всем объясняющим переменным.
Значения коэффициентов уравнения регрессии Ь0, Ьь . Ьт.
Стандартная ошибка коэффициента регрессии 5^, 5Л). Sbm.
Статистическая значимость коэффициента регрессии (^-статистика) для а = 0,05 tw . tbm.
P-значение — это значение уровней значимости, соответствующее вычисленным ^статистикам коэффициентов.
Нижние 95% и Верхние 95% — это нижние и верхние границы 95%-ных доверительных интервалов для коэффициентов уравнения регрессии. Если в окно «Уровень надежности» не вводилось другое значение доверительной вероятности, то последние два столбца дублируют предыдущие два столбца. Если в окно «Уровень надежности» было введено другое значение доверительной вероятности у, то последние два столбца содержат значения соответственно нижней и верхней границы у-процентных доверительных интервалов.
Найти в матрице количество отрицательных чисел, а числа, входящие в диапазон [0..10], умножить на 3
Произвести следующую обработку 10 целых чисел: это у меня получилось. Но не могу понять как.
Массив: Найти диапазон значений по модулю и след матрицы.
Составить программу, которая будет генерировать случайные целые числа в интервале и заполнять ими.
Задано натуральное число , диапазон значений слово. Найти сумму квадратов цифр данного числа
Задано натуральное число , диапазон значений слово. Найти сумму квадратов цифр данного числа.
Заменить в матрице значение элементов третьей строки на полусумму значений всех элементов
Напишите программу формирования массива размером 5X5 с помощью датчика случайных чисел . Замените в.
Спасибо, за корректное изменение. А по теме не подскажите или ник пользователя, кто может помочь?
Добавлено через 27 минут
Заранее благодарю Вас! я вложу файл "задание.rar" (там 1 экселевский файл), отличающийся от предыдущего, на листе "интервалы", я разместил 3 необходимых интервала.
переделайте пожалуйста. Подскажите пожалуйста, а если мне необходимо заменить интервалы удаленные друг от друга, например: [0.92;0.96] и [1.05;1.08]? Как указать обе границы правую и левую.
Заранее благодарю!
Почитайте справку по функции ЕСЛИ. Все достаточно просто.
Если на листе «Начальная матрица» в ячейке А1 находится значение меньшее или равное 0,92, то значение функции равно 3, иначе, если на листе «Начальная матрица» в ячейке А1 находится значение меньшее или равное 1,03, то значение функции равно 1, иначе значение функции равно 2.
Если вам нужно задать другие интервалы, то нужно переделать функцию в ячейке А1 на листе «Полученная матрица» в соответствии с новыми интервалами и протянуть ее по всему диапазону.
Корреляционный анализ – популярный метод статистического исследования, который используется для выявления степени зависимости одного показателя от другого. В Microsoft Excel имеется специальный инструмент, предназначенный для выполнения этого типа анализа. Давайте выясним, как пользоваться данной функцией.
Суть корреляционного анализа
Предназначение корреляционного анализа сводится к выявлению наличия зависимости между различными факторами. То есть, определяется, влияет ли уменьшение или увеличение одного показателя на изменение другого.
Если зависимость установлена, то определяется коэффициент корреляции. В отличие от регрессионного анализа, это единственный показатель, который рассчитывает данный метод статистического исследования. Коэффициент корреляции варьируется в диапазоне от +1 до -1. При наличии положительной корреляции увеличение одного показателя способствует увеличению второго. При отрицательной корреляции увеличение одного показателя влечет за собой уменьшение другого. Чем больше модуль коэффициента корреляции, тем заметнее изменение одного показателя отражается на изменении второго. При коэффициенте равном 0 зависимость между ними отсутствует полностью.
Расчет коэффициента корреляции
Теперь давайте попробуем посчитать коэффициент корреляции на конкретном примере. Имеем таблицу, в которой помесячно расписана в отдельных колонках затрата на рекламу и величина продаж. Нам предстоит выяснить степень зависимости количества продаж от суммы денежных средств, которая была потрачена на рекламу.
Способ 1: определение корреляции через Мастер функций
Одним из способов, с помощью которого можно провести корреляционный анализ, является использование функции КОРРЕЛ. Сама функция имеет общий вид КОРРЕЛ(массив1;массив2).
-
Выделяем ячейку, в которой должен выводиться результат расчета. Кликаем по кнопке «Вставить функцию», которая размещается слева от строки формул.
В поле «Массив2» нужно внести координаты второго столбца. У нас это затраты на рекламу. Точно так же, как и в предыдущем случае, заносим данные в поле.
Как видим, коэффициент корреляции в виде числа появляется в заранее выбранной нами ячейке. В данном случае он равен 0,97, что является очень высоким признаком зависимости одной величины от другой.
Способ 2: вычисление корреляции с помощью пакета анализа
Кроме того, корреляцию можно вычислить с помощью одного из инструментов, который представлен в пакете анализа. Но прежде нам нужно этот инструмент активировать.
-
Переходим во вкладку «Файл».
Параметр «Группирование» оставляем без изменений – «По столбцам», так как у нас группы данных разбиты именно на два столбца. Если бы они были разбиты построчно, то тогда следовало бы переставить переключатель в позицию «По строкам».
В параметрах вывода по умолчанию установлен пункт «Новый рабочий лист», то есть, данные будут выводиться на другом листе. Можно изменить место, переставив переключатель. Это может быть текущий лист (тогда вы должны будете указать координаты ячеек вывода информации) или новая рабочая книга (файл).
Так как место вывода результатов анализа было оставлено по умолчанию, мы перемещаемся на новый лист. Как видим, тут указан коэффициент корреляции. Естественно, он тот же, что и при использовании первого способа – 0,97. Это объясняется тем, что оба варианта выполняют одни и те же вычисления, просто произвести их можно разными способами.
Как видим, приложение Эксель предлагает сразу два способа корреляционного анализа. Результат вычислений, если вы все сделаете правильно, будет полностью идентичным. Но, каждый пользователь может выбрать более удобный для него вариант осуществления расчета.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Читайте также: