Входной интервал содержит нечисловые данные excel что делать

Входной интервал содержит нечисловые данные что делать?

Работа с инструментом «Регрессия» в Microsoft Excel

Открыв рабочую книгу и введя в нее исходные данные для построения уравнения регрессии, вызываем надстройку «Регрессия»: Данные — Анализ данных — Регрессия.

Рис. П. 1. Задание парной регрессии

Ставим «галочку» в окно «Метки», если в отчете Microsoft Excel требуется знать, к какой из объясняющих переменных относятся результирующие данные.

Рис. П.2. Задание множественной регрессии

«Уровень надежности». По умолчанию программа строит уравнение регрессии для доверительной вероятности (уровень надежности) 0,95. Если требуется другая величина, ставим «галочку» в окно «Уровень надежности» и в окно, помеченное символом «%», вводим требуемую величину уровня надежности десятичной дробью.

«Параметры вывода». Указываем, куда вывести результаты регрессионного анализа: на этом листе, как указано на обоих рисунках, на другой рабочий лист или в новую рабочую книгу.

«Остатки». Выбираем то, что требуется исследователю, и ставим «галочку». Можно одновременно пометить несколько окон. Подробная информация дана в справке но инструменту «Регрессия».

Заполнив диалоговое окно «Регрессия», нажимаем кнопку ОК. Программа выводит отчет «Вывод итогов» в виде трех таблиц (рис. П.З, приведено для двух объясняющих переменных).

Приведем описание таблиц (первых двух — в табл. П1.1 и П1.2 соответственно, третьей — в текстовом виде).

Описание первой таблицы

Наименование в отчете

Коэффициент множественной корреляции, индекс корреляции

Коэффициент детерминации, R 2

Скорректированный К 2

Наименование в отчете

Среднее квадратическое отклонение от модели

Рис. П.З. Результаты работы программы

Описание третьей таблицы

Стандартная ошибка коэффициента регрессии 5^, 5_Л). S_bm.

P-значение — это значение уровней значимости, соответствующее вычисленным ^статистикам коэффициентов.

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

Описание второй таблицы

df — число степеней свободы

SS — сумма квадратов

MS = SS/df — дисперсия на одну степень свободы

Использование Пакета анализа EXCEL для построения простой линейной регрессионной модели

history 26 января 2019 г.

Проведем простой регрессионный анализ с помощью надстройки MS EXCEL Пакет анализа .

В данной статье решены следующие задачи:

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

В результате вычислений будет заполнен указанный Выходной интервал.

Тот же результат можно получить с помощью формул (см. файл примера лист Надстройка, столбцы I:T ):

Отчет, сформированный надстройкой, состоит из следующих разделов:

Раздел «Регрессионная статистика»:

Раздел « Дисперсионный анализ »:

Регрессия входной интервал содержит нечисловые данные

трюки • приёмы • решения

При импортировании данных из других источников вы, возможно, уже успели обнаружить, что Excel иногда некорректно импортирует значения. В частности, он может принять ваши числа за текст. И тогда, например, при суммировании диапазона значений формула СУММ возвращает 0 — хотя диапазон, по всей видимости, содержит числовые значения.

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

Excel ничего не добавит к значениям, но в процессе укажет этим ячейкам иметь фактические значения.

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

Метки. Если входной интервал не включает меток, снимите флажок Метки. Excel генерирует соответствующие метки данных для выходной таблицы (Строка 1, Строка 2, или Столбец 1, Столбец 2 и т.д.)

Выходные данные

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

Новый рабочий лист. Этот параметр вставляет новый лист в рабочую книгу, где располагается текущий рабочий лист, и вставляет результаты в ячейку А1 нового листа. Используйте поле ввода рядом с параметром для задания имени нового листа.

Новая рабочая книга. Этот параметр создает новую рабочую книгу, добавляет новый рабочий лист и вставляет результаты в ячейку А1 нового листа.

Генерация случайных чисел

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

Для генерации случайных величин необходимо иметь возможность получать последовательность равномерно распределенных случайных чисел, т.е. чисел, которые ведут себя как независимые реализации или выборки случайной величины R, равномерно распределенной на единичном интервале [0,1]. Такие числа получают с помощью генераторов случайных чисел.

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

Не нашли то, что искали? Воспользуйтесь поиском:

Лучшие изречения: Для студента самое главное не сдать экзамен, а вовремя вспомнить про него. 10663 — | 7824 — или читать все.

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

Функция СРЗНАЧ вычисляет среднее арифметическое значение. Она игнорирует пустые, логические и текстовые ячейки и может использоваться вместо длинных формул. Например, для вычисления среднего значения данных в диапазоне ячеек В4:В15 можно использовать формулу:

Очевидно, что проще ввести = СРЗНАЧ(B4:B15).

Функция МЕДИАНА вычисляет медиану множества числовых значений.

Функция МОДА определяет значение, которое чаще других встречается во множестве чисел.

Функция МАКС вычисляет наибольшее значение в диапазоне.

Функция МИН вычисляет наименьшее значение в диапазоне.

Функция СЧЕТ определяет количество ячеек в заданном диапазоне, которые содержат числа, в том числе, даты и формулы, возвращающие числа.

Функции ДИСП и СТАНДОТКЛОН определяют дисперсию и стандартное отклонение чисел, в предположении что они образуют выборку.

Функции ДИСПР и СТАНДОТКЛОНП определяют дисперсию и стандартное отклонение для генеральной совокупности.

Функция НАКЛОН вычисляет коэффициент наклона линии линейной регрессии.

Функция ОТРЕЗОК вычисляет отрезок, отсекаемый на оси линией линейной регрессии.

Функция ПРЕДСКАЗ вычисляет теоретические значения y по линии линейной регрессии.

Чтобы получить доступ к инструментам Пакета анализа необходимо:

· выполнить команду Сервис/Анализ данных;

· для использования инструмента анализа, выбрать его имя в списке и нажать кнопку ОК;

· заполнить открывшееся диалоговое окно (в большинстве случаев это означает задание входного диапазона с данными, которые вы собираетесь анализировать, указание верхней левой ячейки выходного диапазона, в который должны быть помещены результаты, и выбор нужных параметров. Группирование: установить переключатель в положение По столбцам или По строкам в зависимости от расположения данных во входном диапазоне. Установить переключатель в положение Метки в первой строке, если первая строка во входном диапазоне содержит названия столбцов или установить переключатель в положение Метки в первом столбце, если названия строк находятся в первом столбце входного диапазона. Если входной диапазон не содержит меток, то необходимые заголовки в выходном диапазоне будут созданы автоматически).

Если надстройка Анализ данных отсутствует, то ее можно подключить с помощью команды Сервис/Надстройки/Пакет анализа VBA ( Analysis ToolPak VBA ).

Инструмент Описательная статистика предлагает таблицу основных статистических характеристик для одного или нескольких множеств входных значений ( Рис. 7.1 ):

Корреляция используется для количественной оценки взаимосвязи двух наборов данных, представленных в безразмерном виде. Корреляционный анализ дает возможность установить, ассоциированы ли наборы данных по величине, то есть, большие значения из одного набора данных связаны с большими значениями другого набора (положительная корреляция), или, наоборот, малые значения одного набора связаны с большими значениями другого (отрицательная корреляция), или данные двух диапазонов никак не связаны (корреляция близка к нулю). В диалоговом окне Корреляция ( REF _Ref12174106 h * MERGEFORMAT Рис. 7.2 ) указывается Входной интервал – ссылка на диапазон, содержащий анализируемые данные. Ссылка должна состоять как минимум из двух смежных диапазонов данных, организованных в виде столбцов или строк.

Регрессия используется для подбора графика линии регрессии. Параметры диалогового окна Регрессия ( Рис. 7.3 ):

Входной интервал Y – ссылка на диапазон анализируемых зависимых данных (диапазон должен состоять из одного столбца). Входной интервал X – ссылка на диапазон независимых данных, подлежащих анализу. Уровень надежности – установить флажок, чтобы включить в выходной диапазон дополнительный уровень. В соответствующее поле ввести уровень надежности, который будет использован дополнительно к уровню 95%, применяемому по умолчанию. Константа-ноль – установить флажок, чтобы линия регрессии прошла через начало координат. Остатки – установить флажок, чтобы включить остатки в выходной диапазон. Стандартизированные остатки – установить флажок, чтобы включить стандартизированные остатки в выходной диапазон. График остатков – установить флажок, чтобы построить диаграмму остатков для каждой независимой переменной. График подбора – установить флажок, чтобы построить диаграммы наблюдаемых и предсказанных значений для каждой независимой переменной. График нормальной вероятности – установить флажок, чтобы построить диаграмму нормальной вероятности.

Статистический анализ в excel Назначение и возможности пакета анализа

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

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

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

Экспоненциальное сглаживание предназначается для предсказания значения на основе прогноза для предыдущего периода, скорректированного с учетом погрешностей в этом прогнозе. Использует константу сглаживания, по величине которой определяет, насколько сильно влияют на прогнозы погрешности в предыдущем прогнозе. Для константы сглаживания наиболее подходящими являются значения от 0,2 до 0,3. Эти значения показывают, что ошибка текущего прогноза установлена на уровне от 20 до 30 процентов ошибки предыдущего прогноза. Более высокие значения константы ускоряют отклик, но могут привести к непредсказуемым выбросам. Низкие значения константы могут привести к сдвигу аргумента для предсказанных значений.

Линейный регрессионный анализ заключается в подборе графика для набора наблюдений с помощью метода наименьших квадратов. Регрессия используется для анализа воздействия на отдельную зависимую переменную значений одной или более независимых переменных. Мы рассмотрим, как влиял на курс ЕВРО по отношению к рублю курс доллара США в июле 1999 года.

Установка пакета анализа.

Если в Microsoft Excel в меню Сервисотсутствует командаАнализ данных, то необходимо установить статистический пакет анализа данных.

Чтобы установить пакет анализа данных

ВменюСервисвыберите командуНадстройки. Если в списке надстроек нет пакета анализа данных, нажмите кнопкуОбзори укажите диск, папку и имя файла для надстройки пакет анализа, Analys32.xll (как правило, папка LibraryAnalysis) или запустите программу Setup, чтобы установить эту надстройку.

Установите флажок Пакет анализа,выберите кнопкуOK.

Вызов пакета анализа

Чтобы запустить пакет анализа:

В меню Сервисвыберите командуАнализ данных.

В списке Инструменты анализавыберите нужную строку.

Корреляция

При выборе строки Корреляцияв диалоговом запросеАнализ данныхпоявляется следующее окно.

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

Группирование. Установите переключатель в положениеПо столбцамилиПо строкамв зависимости от расположения данных во входном диапазоне.

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

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

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

Новая книга. Установите переключатель, чтобы открыть новую книгу и вставить результаты анализа в ячейку A1 на первом листе в этой книге.

Смотри лист Корреляция в примере.

Вернитесь в текущий документ через Панель задач

результате программа сформирует таблицу с коэффициентами корреляции между выбранными совокупностями.

Регрессионный анализ. Построение статических однофакторных моделей

Практическая работа 1. Регрессионный анализ. Построение статических однофакторных моделей.

Содержательная постановка задачи. Имеется статистическая информация по центральному федеральному округу, которая представлена в таблице1:

Таблица 1. Число гостиниц и число ночевок в гостиницах

Наименование субъекта Федерации

Число гостиниц и аналогичных средств размещения, ед.

Число ночевок в гостиницах и аналогичных средствах размещения, тыс. ночевок

* — на базе данных Федеральной службы государственной статистики.

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

Часть I.

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

Пример. В исходной таблице произведем сортировку по столбцу С (число гостиниц и аналогичных средств размещения) по возрастанию числа гостиниц.

Рис.1. Сортировка по возрастанию числа гостиниц

Отсортированная таблица представлена на рисунке 2:

Рис.2. Отсортированная таблица по возрастанию числа гостиниц

По отсортированным данным, используя мастер диаграмм, построим точечную диаграмму (диапазон ячеек С1:D19) (Рис.3).

Рис. 3. Диаграмма по отсортированной таблице

После построения диаграммы, вызовем контекстовое меню, щелкнув правой кнопкой мыши по одной из точек диаграммы, и выберем в нем команду Добавить линию тренда…(Рис. 4):

Рис. 4. Вкладка Параметры линии тренда

Во вкладке Параметры линии тренда выберем Линейная и отметим флаги показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^2). Чем R2 ближе к 1, тем удачнее регрессионная модель. На диаграмме появляется линия тренда (Рис. 5).

Рис.5. Диаграмма с линейной линией тренда

Чаще всего выбор производится среди следующих функций:

у = ах + b — линейная функция;

у = ах2 + bх + с — квадратичная (полиномиальная) функция;

у = аln(х) + b — логарифмическая функция;

у = аеbх — экспоненциальная функция;

у = ахb — степенная функция.

Отобразим на диаграмме все возможные тренды (Рис. 6.).

Рис. 6. Диаграмма с построенными линиями тренда

Часть II.

Требуется: рассчитать основные характеристики случайных величин.

Для расчета основных характеристик случайных величин используются следующие функции: СРЗНАЧ() – возвращает среднее арифметическое своих аргументов, КОРЕНЬ() – возвращает значение квадратного корня, а также ДИСП() и КОРРЕЛ().

ДИСП() — Оценивает дисперсию по выборке (Рис.7).

Число1, число2. — от 1 до 255 числовых аргументов, соответствующих выборке из генеральной совокупности.

Рис. 7. Аргументы функции, оценивающей дисперсию по выборке — Дисп()

КОРРЕЛ() – возвращает коэффициент корреляции между интервалами ячеек «массив1» и «массив2». Коэффициент корреляции используется для определения взаимосвязи между двумя свойствами. Например, можно установить зависимость между средней температурой в помещении и использованием кондиционера (Рис. 8).

Синтаксис функции: КОРРЕЛ(массив1;массив2).

Массив1 — это интервал ячеек со значениями, Массив2 — второй интервал ячеек со значениями.

Рис.8. Аргументы функции, возвращающей коэффициент корреляции Коррел()

Получим следующие результаты (Рис. 9):

Рис. 9. Результаты расчетов с использованием математических функций

Можно сделать вывод о том, что линейная зависимость между числом гостиниц и аналогичных средств размещения (ряд X) и числом ночевок в гостиницах и аналогичных средствах размещения (ряд Y) существует, т. к. коэффициент корреляции равен 0,93729 и .

Коэффициент корреляции значим, т. к. расчетный критерий Стъюдента больше табличного критерия: 10,7566 > 2.1190.

Рассчитаем коэффициент корреляции для исходных данных с помощью функции Корреляция пакета Анализ данных.

Вызвать окно Анализ данных можно с помощью команды Анализ данных меню Данные (Рис. 10).

Рис. 10. Анализ данных

Пакет Корреляция позволяет определить коэффициенты корреляции для n-го количества рядов данных. Выбор команды Корреляция вызывает окно Корреляция (Рис. 11).

Рис. 11. Окно Корреляция

Это окно содержит две панели Входные данные и Параметры вывода. Окно Входной интервал: предназначено для ссылки на диапазон, содержащий анализируемые данные. Эта ссылка должна состоять не менее чем из двух смежных диапазонов данных, расположенных по строкам или столбцам. Флаги Группирование: зависят от расположения данных в диапазоне. Флаг Метки в первой строке (Метки в первом столбце) устанавливается в том случае, если входной интервал включал название диапазонов. Если название диапазонов были включены в интервал, а данный флаг не выставлен, после нажатия кнопки Ок, Excel выдаст сообщение об ошибке «Входной интервал содержит нечисловые данные». Если входной диапазон не содержит меток, то необходимые заголовки в выходном диапазоне будут созданы автоматически.

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

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

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

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

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

Заполняем все необходимые поля окна Корреляция (Рис. 12).

Входной интервал – это данные, по которым необходимо провести корреляционный анализ, в данном случае это исходные данные по числу гостиниц и аналогичных средств размещения и числу ночевок в гостиницах и аналогичных средствах размещения (С2:D19). Строка 1 также указана во входном интервале, но в ней содержатся заголовки столбцов, поэтому ставим флаг Метки в первой строке.

В выходном интервале ставим Новый рабочий лист, в котором будут вынесены результаты расчета.

Рис. 12. Расчет коэффициента корреляции

Полученные данные абсолютно идентичны коэффициентам полученным с помощью функции КОРРЕЛ() (Рис. 13).

Рис. 13. Результаты расчета коэффициента корреляции

Пакет Описательная статистика предназначен для расчета основных статистических показателей. Окно Описательная статистика (Рис. 14) содержит:

Рис.14. Описательная статистика

панель Входные данные, аналогичную панели в окне Корреляция; панель Параметры вывода содержит указание на выходной интервал, аналогичный окну Корреляция; флаг Итоговая статистика обеспечивает вывод в выходной интервал среднего, стандартную ошибку (среднего), медиану, мода, стандартное отклонение, дисперсию выборки, эксцесс, асимметричность, интервал, минимум, максимум, сумму и количество значений; флаг Уровень надежности, установка которого выводит в выходной интервал строку для уровня надежности. Значение, введенное в поле, соответствует требуемому уровню надежности; флаг К-тый наименьший и К-тый наибольший, установка которых выводит в выходной интервал строки для k-го наибольшего и k-го наименьшего значения для каждого диапазона данных. В соответствующем окне необходимо ввести число k. Если k равно 1, эта строка будет содержать минимум или максимум из набора данных.

Далее вызовем функцию Описательная статистика из пакета Анализ данных (Рис. 15).

Рис. 15. Описательная статистика пакета Анализ данных

Выставив все необходимые флаги, нажимаем кнопку Ок, и получаем таблицу описательных статистик (Рис. 16).

Рис. 16. Таблица описательных статистик

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

Варианты заданий к практической работе 1.

Содержательная постановка задачи. Исходные данные:

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

Вариант 2. Имеется статистическая информация по Северо-Западному федеральному округу*, которая представлена в таблице:

Число гостиниц и аналогичных средств размещения

Число ночевок в гостиницах и аналогичных средствах размещения, тыс.

Источник