Darbe.ru

Быт техника Дарби
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Массив интервалов в excel

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Как построить график с нормальным распределением в Excel

95-0-Нормальное распределение в Excel лого

Так как я часто имею дело с большим количеством данных, у меня время от времени возникает необходимость генерировать массивы значений для проверки моделей в Excel. К примеру, если я хочу увидеть распределение веса продукта с определенным стандартным отклонением, потребуются некоторые усилия, чтобы привести результат работы формулы СЛУЧМЕЖДУ() в нормальный вид. Дело в том, что формула СЛУЧМЕЖДУ() выдает числа с единым распределением, т.е. любое число с одинаковой долей вероятности может оказаться как у нижней, так и у верхней границы запрашиваемого диапазона. Такое положение дел не соответствует действительности, так как вероятность возникновения продукта уменьшается по мере отклонения от целевого значения. Т.е. если я произвожу продукт весом 100 грамм, вероятность, что я произведу 97-ми или 103-граммовый продукт меньше, чем 100 грамм. Вес большей части произведенной продукции будет сосредоточен рядом с целевым значением. Такое распределение называется нормальным. Если построить график, где по оси Y отложить вес продукта, а по оси X – количество произведенного продукта, график будет иметь колоколообразный вид, где наивысшая точка будет соответствовать целевому значению.

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

Характеристики нормального распределения

Непрерывная случайная переменная, которая подчиняется нормальному распределению вероятностей, обладает некоторыми особыми свойствами. Предположим, что вся производимая продукция подчиняется нормальному распределению со средним значением 100 грамм и стандартным отклонением 3 грамма. Распределение вероятностей для такой случайной переменной представлено на рисунке.

95-1-Нормальное распределение в Excel

Из этого рисунка мы можем сделать следующие наблюдения относительно нормального распределения — оно имеет форму колокола и симметрично относительно среднего значения.

Стандартное отклонение имеет немаловажную роль в форме изгиба. Если посмотреть на предыдущий рисунок, то можно заметить, что практически все измерения веса продукта попадают в интервал от 95 до 105 граммов. Давайте рассмотрим следующий рисунок, на котором представлено нормальное распределение с той же средней – 100 грамм, но со стандартным отклонением всего 1,5 грамма

95-2-Нормальное распределение в Excel

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

Небольшое значение стандартного отклонения выражается в более «тощей и высокой кривой, плотно прижимающейся к среднему значению. Чем больше стандартное, тем «толще», ниже и растянутее получается кривая.

Создание массива с нормальным распределением

Итак, чтобы сгенерировать массив данных с нормальным распределением, нам понадобится функция НОРМ.ОБР() – это обратная функция от НОРМ.РАСП(), которая возвращает нормально распределенную переменную для заданной вероятности для определенного среднего значения и стандартного отклонения. Синтаксис формулы выглядит следующим образом:

=НОРМ.ОБР(вероятность; среднее_значение; стандартное_отклонение)

Другими словами, я прошу Excel посчитать, какая переменная будет находится в вероятностном промежутке от 0 до 1. И так как вероятность возникновения продукта с весом в 100 грамм максимальная и будет уменьшаться по мере отдаления от этого значения, то формула будет выдавать значения близких к 100 чаще, чем остальных.

Давайте попробуем разобрать на примере. Выстроим график распределения вероятностей от 0 до 1 с шагом 0,01 для среднего значения равным 100 и стандартным отклонением 1,5.

95-3-демонстрация функции НОРМОБР

Как видим из графика точки максимально сконцентрированы у переменной 100 и вероятности 0,5.

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

=НОРМ.ОБР(СЛЧИС(); среднее_значение; стандартное_отклонение)

Создадим массив данных для нашего примера со средним значением 100 грамм и стандартным отклонением 1,5 грамма и протянем нашу формулу вниз.

95-4-массив данных с нормальным распределением

Теперь, когда массив данных готов, мы можем выстроить график с нормальным распределением.

Построение графика нормального распределения

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

95-5-данные для нормального распределения

Далее строим таблицу с категориями. Нижняя граница (B11) равняется округленному вниз ближайшему кратному числу. Остальные категории увеличиваются на значение шага. Формула в ячейке B12 и последующих будет выглядеть:

В столбце X будет производится подсчет количества переменных в заданном промежутке. Для этого воспользуемся формулой ЧАСТОТА(), которая имеет два аргумента: массив данных и массив интервалов. Выглядеть формула будет следующим образом =ЧАСТОТА(Data!A1:A175;B11:B20). Также стоит отметить, что в таком варианте данная функция будет работать как формула массива, поэтому по окончании ввода необходимо нажать сочетание клавиш Ctrl+Shift+Enter.

95-6-таблица для нормального распределения

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

95-7-график нормального распределения в excel

Осталось отформатировать диаграмму и наш график с нормальным распределением готов.

95-8-график с нормальным распределением

Итак, мы познакомились с вами с нормальным распределением, узнали, что Excel позволяет генерировать массив данных с помощью формулы НОРМ.ОБР() для определенного среднего значения и стандартного отклонения и научились приводить данный массив в графический вид.

Вам также могут быть интересны следующие статьи

13 комментариев

Ренат, добрый день.
Все несколько проще:
Данные->Анализ данных->Генерация случайных чисел (Распределение=Нормальное)
+
Данные->Анализ данных->Гистограмма->Галка на «вывод графика» («Карманы» можно даже не задавать)

Расчет среднего значения в таблице Excel

Во время работы с числовыми данными в Эксель нередко перед пользователями встает такая задача, как подсчет среднего значения. Математически данное действие выполняется путем деления суммы всех чисел на их количество. А как это сделать в Excel? Давайте разбираться.

Читайте так же:
Использование символа в excel

Информация в строке состояния

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

Выделенные ячейки строки в Эксель

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

Среднее значение по выделенным ячейкам в строке состояния Эксель

Расчет среднего значения

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

Использование арифметического выражения

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

  1. Встаем в нужную ячейку, ставим знак “равно” и пишем арифметическое выражение по следующем принципу:
    =(Число1+Число2+Число3. )/Количество_слагаемых .
    Примечание: в качестве числа может быть указано как конкретное числовое значение, так и ссылка на ячейку. В нашем случае, давайте попробуем посчитать среднее значение чисел в ячейках B2,C2,D2 и E2.
    Конечный вид формулы следующий: =(B2+E2+D2+E2)/4 .Арифметические выражение для расчета среднего значения в Excel
  2. Когда все готово, жмем Enter, чтобы получить результат.Расчет среднего значения в Эксель с помощью формулы

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

Инструменты на ленте

Данный метод основан на использовании специального инструмента на ленте программы. Вот как это работает:

  1. Выделяем диапазон ячеек с числовыми данными, для которых мы хотим определить среднее значение.Выделение ячеек столбца в Excel
  2. Переходим во вкладку “Главная” (если находимся не в ней). В разделе инструментов “Редактирование” находим значок “Автосумма” и щелкаем по небольшой стрелке вниз рядом с ним. В раскрывшемся перечне кликаем по варианту “Среднее”.Расчет среднего значения ячеек столбца Excel
  3. Сразу же под выделенным диапазоном отобразится результат, который и является средним значением по всем отмеченным ячейкам.Среднее значение ячеек столбца Excel

Примечание: Если вместо вертикального выделения (столбца целиком или его части) будет выполнено горизонтальное выделение, то результат отобразится не под областью выделения, а справа от нее.

Среднее значение по ячейкам строки Excel

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

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

Расчет среднего значения по нескольким столбцами в Excel

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

Средние значения по всем столбцам в таблице Excel

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

Альтернативный способ использования “Среднее” на ленте:

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

Использование функции СРЗНАЧ

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

  1. Встаем в ячейку, куда планируем выводить результат. Кликаем по значку “Вставить функци” (fx) слева от строки формул.Вставка функции в ячейку таблицы Эксель
  2. В открывшемся окне Мастера функций выбираем категорию “Статистические”, в предлагаемом перечне кликаем по строке “СРЗНАЧ”, после чего нажимаем OK.Выбор оператора СРЗНАЧ в мастере функций Excel
  3. На экране отобразится окно с аргументами функции (их максимальное количество – 255). Указываем в качестве значения аргумента “Число1” координаты нужного диапазона. Сделать это можно вручную, напечатав с клавиатуры адреса ячеек. Либо можно сначала кликнуть внутри поля для ввода информации и затем с помощью зажатой левой кнопки мыши выделить требуемый диапазон в таблице. При необходимости (если нужно отметить ячейки и диапазоны ячеек в другом месте таблицы) переходим к заполнению аргумента “Число2” и т.д. По готовности щелкаем OK.Заполнение аргументов функции СРЗНАЧ в Экселе
  4. Получаем результат в выбранной ячейке.Среднее значение в Excel с помощью функции СРЗНАЧ
  5. Среднее значение не всегда может быть “красивым” за счет большого количества знаков после запятой. Если нам такая детализация не нужна, ее всегда можно настроить. Для этого правой кнопкой мыши щелкаем по результирующей ячейке. В открывшемся контекстном меню выбираем пункт “Формат ячеек”.Переход к формату ячейки Excel
  6. Находясь во вкладке “Число” выбираем формат “Числовой” и с правой стороны окна указываем количество десятичных знаков после запятой. В большинстве случаев, двух цифр более, чем достаточно. Также при работе с большими числами можно поставить галочку “Разделитель групп разрядов”. После внесение изменений жмем кнопку OK.Настройка количества знаков после запятой в формате ячеек Excel
  7. Все готово. Теперь результат выглядит намного привлекательнее.Среднее значение в Эксель с помощью функции СРЗНАЧ

Инструменты во вкладке “Формулы”

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

  1. Встаем в ячейку, в которой планируем выполнить расчеты. Переключаемся во вкладку “Формулы”. В разделе инструментов “Библиотека функций” щелкаем по значку “Другие функции”, в раскрывшемся перечне выбираем группу “Статистические”, затем – “СРЗНАЧ”.Выбор функции СРЗНАЧ во вкладке Формулы в Excel
  2. Откроется уже знакомое окно аргументов выбранной функци. Заполняем данные и жмем кнопку OK.Заполнение аргументов функции СРЗНАЧ в Экселе

Ввод функции в ячейку вручную

Как и все остальные функции, формулу СРЗНАЧ с нужными аргументами можно сразу же прописать в нужной ячейке.

В общем, синтаксис функции СРЗНАЧ выглядит так:

В качестве аргументов могут выступать как ссылки на отельные ячейки (диапазоны ячеек), так и конкретные числовые значения.

Просто встаем в нужную ячейку и, поставив знак “равно”, пишем формулу, перечислив аргументы через символ “точка с запятой”. Вот, как это выглядит со ссылками на ячейки в нашем случае. Допустим, мы решили включить в подсчет всю первую строку и только три значения из второй:

Формула функции СРЗНАЧ в Excel

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

Безусловно, такой метод нельзя назвать удобным, но иногда, при небольшом объеме данных, и он вполне может использоваться.

Определение среднего значения по условию

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

Допустим, нам нужно посчитать среднее значение только по положительным числам, т.е. тем, которые больше нуля. В этом случае, нас выручит функция СРЗНАЧЕСЛИ.

  1. Встаем в результирующую ячейку и жмем кнопку “Вставить функцию” (fx) слева от строки формул.Вставка функции в ячейку таблицы Эксель
  2. В Мастере функций выбираем категорию “Статистические”, кликаем по оператору “СРЗНАЧЕСЛИ” и жмем ОК.Выбор оператора СРЗНАЧЕСЛИ в Мастере функций Excel
  3. Откроются аргументы функции, после заполнения которых кликаем OK:
    • в значении аргумента “Диапазон” указываем (вручную или выделив с помощью левой кнопки мыши в самой таблице) требуемую область ячеек;
    • в значении аргумента “Условие”, соответственно, задаем наше условие попадания ячеек из отмеченного диапазона в общий расчет. В нашем случае, это выражение “>0”. Вместо конкретного числа, в случае необходимости, в условии можно указать адрес ячейки, содержащей числовое значение.
    • поле аргумента “Диапазон_усреднения” можно оставить пустим, так как его обязательное заполнение требуется только при работе с текстовыми данными.Заполнение аргументов функции СРЗНАЧЕСЛИ в Экселе
  4. Среднее значение с учетом заданного нами условия отбора ячеек отобразилось в выдранной ячейке.Среднее значение с помощью функции СРЗНАЧЕСЛИ в Excel

Заключение

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

Массив интервалов в excel

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

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

Microsoft Excel предлагает большое число функций, облегчающих статистическую обработку данных эксперимента.

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

Мастер функций (повторение)

Вспомните основные приемы работы с Мастером функций.

Запуск: кнопкой вставки функции . (или командой меню Вставка-Функция).

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

Задание аргументов функции. Аргументами могут служить непосредственно данные или адреса ячеек, диапазонов ячеек, содержащих необходимые данные. Как правило, все исходные данные для функции должны быть на листе и вводят (указывая мышкой) адреса ячеек, содержащих эти значения. Обратите внимание, что окно аргументов можно перемещать, если оно заслоняет нужную часть экрана. Кроме того, рядом с полем для ввода есть маленькая кнопка с красной стрелочкой. При щелчке по ней окно аргументов сворачивается до узкой полоски, новый щелчок опять развернет окно аргументов. Помогает работе с мастером функций подсказка под полем для ввода аргументов, в которой разъясняется их смысл и возможные значения. Когда аргументы введены, в окне появляется значение функции, что тоже помогает избежать ошибок. Заканчивается работа с мастером функций нажатием кнопки «Ok» или клавиши «Enter».

=> Особенности Office-2007.
Для работы с функциями можно использовать вкладку Формулы, где представлена библиотека функций, разбитая на категории. Можно прийти к вставке функций и через значок Автосумма, выбрав там команду Другие функции.
Еще один способ — значок fx, рядом со строкой формул.

Счет значений

Для получения количества числовых ячеек в интервалах или массивах ячеек используется функция СЧЁТ. Использовать эту функцию удобно, особенно при большом числе данных, при их отбраковке.

Синтаксис: СЧЁТ(значение1; значение2; . ) где
Значение1, значение2, . — это числа, адреса ячеек и диапазонов (до 30 аргументов). Пустые ячейки, логические значения, тексты и значения ошибок в массиве или ссылке игнорируются.

Функцию СЧЁТ можно вызвать через список функций значка АВТОСУММА (команда Число).

Среднее значение

Функция СРЗНАЧ возвращает среднее арифметическое аргументов.

Синтаксис СРЗНАЧ(значение1; значение2; . ), где
— значение1, значение2, . — это числа, адреса ячеек и диапазонов (до 30 аргументов), для которых вычисляется среднее. Пустые ячейки, логические значения, тексты и значения ошибок игнорируются; однако ячейки, которые содержат нулевые значения, учитываются.

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

Синтаксис: УРЕЗСРЕДНЕЕ(массив;доля), где
— массив — это массив или интервал усредняемых значений. — доля — это доля точек данных, исключаемых из вычислений. Например, если доля = 0,2, то из множества данных, содержащих 25 точек исключаются 4 точки (25*0,2= 5, но из соображений симметрии функция исключит 2 точки с наибольшими значениями и 2 точки с наименьшими значениями). Если доля 1, то функция возвращает ошибку #ЧИСЛО!.

Разброс точек. Дисперсия и стандартное отклонение

Разброс экспериментальных данных, оценивается дисперсией по выборке, которое определяется функцией ДИСП или стандартным отклонением (функция СТАНДОТКЛОН)

Синтаксис: ДИСП(число1;число2; . ), или СТАНДОТКЛОН(число1; число2; . )
где число1, число2, . — это до 30 числовых аргументов, соответствующих выборке из генеральной совокупности. Логические значения, такие как ИСТИНА или ЛОЖЬ, а также текст игнорируются.

ДИСП и СТАНДОТКЛОН используют формулы:

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

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

Критерий Стьюдента, доверительный интервал

Для определения доверительного интервала, в который с заданной надежностью попадает измеряемая величина, используется критерий Стюдента t(a;f). Он был предложен в 1908 году англиским математиком и химиком Госсетом, который опубликовал их в работе под псевдонимом Student (студент). Для нахождения критерия Стюдента используется функция СТЬЮДРАСПОБР(a;f) .

Синтаксис: СТЬЮДРАСПОБР(альфа;степень_свободы), где
— альфа — это вероятность ошибки, используемая для вычисления уровня надежности (Р). Уровень надежности определяется как Р = 1 — a, или, другими словами, a равная 0,05 означает 95% уровень надежности.
— степень_свободы — это число степеней свободы, характеризующее распределение. Для среднего значения степень свободы f = n-1.

Функция СТЬЮДРАСПОБР использует метод итераций для вычисления результата. Если СТЬЮДРАСПОБР не сходится после 100 итераций, то функция возвращает значение ошибки #Н/Д (не достижимо).

Доверительный интервал среднего рассчитывается по формуле ДИ = ± sср*t(a;f) где sср — стандартное отклонение среднего.
Окончательно получаем X = Xср ± sср*t(a;f)

Если число измерений велико, можно использовать доверительный интервал для среднего генеральной совокупности, который находится с помощью функции ДОВЕРИТ, однако эта функция дает заниженные результаты при небольшом числе измерений.
Синтаксис: ДОВЕРИТ(альфа; станд_откл; n)

Линейная регрессия

Коэффициенты линейной регрессии Y = mX+b и их ошибки вычисляются по довольно сложным формулам (рассмотренным на прошлом занятии). Excel предлагает ряд функций, облегчающих эти расчеты.

Коэффициент m (наклон) линии, описываемой уравнением Y = mX+b может быть получен при помощи статистической функции НАКЛОН.

Коэффициент b, показывающий отрезок, отсекаемый линией Y = mX+b на оси Y, может быть определен с помощью функции ОТРЕЗОК.

Коэффициент корреляции Пирсона R, который отражает степень линейной зависимости между двумя множествами данных, и его квадрат (коэффициент детерминации) может быть получен с помощью функций ПИРСОН и КВПИРСОН.
Если R 2 равен 1, то имеет место полная корреляция с линейной моделью, т.е. нет различия между фактическим и оценочным значениями Y. В противоположном случае, если коэффициент равен 0, то уравнение линейной регрессии не применимо для предсказания значений Y.

Синтаксис:
НАКЛОН(известные_значения_y; известные_значения_x)
ОТРЕЗОК (известные_значения_y; известные_значения_x)
КВПИРСОН (известные_значения_y; известные_значения_x)

Полная статистика линейной регрессии. Формулы массива

  • Начинать ввод следует с выделения массива ячеек, куда будут выводиться данные. Для функции ЛИНЕЙН это прямоугольник N*5, где N — число коэффициентов в уравнении.
  • Заканчивать ввод функции следует сочетанием клавиш Ctrl-Shift-Enter (или, удерживая нажатыми клавиши Ctrl-Shift щелкнуть мышкой Ok).

Синтаксис: ЛИНЕЙН(извест_знач_y; извест_знач_x; конст; статистика)
Константа определяет, нужно ли рассчитывать отрезок b, отсекаемый линией на оси OY. Если из теоретических представлений известно, что линия проходит через начало координат и отрезок b, отсекаемый ею на оси OY равен 0, то вводится значение константы ЛОЖЬ (или 0). В противном случае вводится значение ИСТИНА (1), и отрезок b будет вычислен из массива данных.
Еще одно логическое значение Стат указывает, требуется ли вернуть полную статистику по регрессии (ИСТИНА) или нет (ЛОЖЬ) В результате правильного ввода функции в выделенных ячейках получаем таблицу данных (пример в таблице 1):

В ячейке Е7 находится коэффициент m (наклон) линии, в ячейке F7 — коэффициент b (отрезок);
В ячейках E8 и F8 приведены стандартные значения ошибок для коэффициентов m и b. (sm, sb) Стандартные значения ошибок могут быть использованы для оценки значимости коэффициентов регрессии.
В ячейке Е9 — квадрат коэффициента корреляции Пирсона (R 2 ) В ячейке F9 — cтандартная ошибка для оценки Y (sy).
В ячейке Е10 выводится F-статистика, используемая для определения адекватности модели. В ячейке F10 представлено число степеней свободы (f). Число степеней свободы используются для нахождения F-критических значений в статистической таблице. Оно находится по формуле f = n — (k + 1), где k — это число независимых переменных, а n — число точек данных. В нашем случае f = 10 — (1 + 1) = 8
В ячейках Е11 и F11 представлены, соответственно, регрессионная сумма квадратов и остаточная сумма квадратов.

Адекватность модели

Для определения уровня надежности модели нужно сравнить значения специального F(a;f1;f2) и F-статистики, возвращаемой функцией ЛИНЕЙН. Для определения используется функция FРАСПОБР(a;f1; f2), где
a вероятность ошибочного вывода (обычно применяется значение равное 0,05), а f1 и f2 — число степеней свободы, причем f1 = k = 1, где k — число независимых переменных в уравнении модели, а f2 равно числу степеней свободы, данное в таблице результатов функции ЛИНЕЙН.
Если наблюдаемое F >> F(a;f1;f2), то полученное регрессионное уравнение адекватно.

Графики: построение

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

  1. Выделите массивы ячеек, содержащих значения Х и Y. (Несмежные области выделяют, используя клавишу Ctrl).
  2. Щелкните на значке Мастера диаграмм панели инструментов. На первом этапе его работы определите тип диаграммы (Точечная) и ее вид только точки, точки и линии, только линии и т.п.
  3. На следующем этапе можно примерно увидеть будущий график. Если нужно, то на вкладке Ряд можно удалить или добавить ряды данных зависящих от Х.
  4. Следующий этап позволяет ввести различные надписи, определить необходимость и расположение легенды, линий сетки.
  5. На последнем этапе определяем, будет ли для графика создан отдельный Лист и нажимаем кнопку Готово
=> Особенности Office-2007.
— В Excel-2007 нет мастера диаграмм. Первый этап выполняется с вкладки Вставка. При этом на листе сразу появляется выбранный тип диаграммы.

Редактирование графика

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

Прежде всего, можно пермещать график, изменять размеры графика, как и любого графического объекта. Для этого его надо выделить и растянуть/уменьшить до требуемой величины.

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

=> Особенности Office-2007.
При щелчке на диаграмме появляются три дополнительные вкладки Ленты, предназначенные для работы с диаграммами. На вкладке Конструктор можно изменить тип диаграммы, определить исходные данные, если они не были выделены предварительно, выбрать макет, стиль и размещение диаграммы. Вкладка Макет позволяет проводить более детальную работу с элементами диаграммы (Легенда, Название, Подписи, Сетка, Оси и т.д.) Вкладка Формат предоставляет дополнительные возможности форматирования объектов диаграммы.

Линия тренда и дополнительные данные

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

На вкладках этой команды выбираем тип тренда (линейный) и его параметры. В частности, отмечаем Выводить уравнение, Выводить коэффициент R^2.

Открыв через контекстное меню точки окно «Параметры рядов данных«, на график также можно нанести планки погрешностей, используя значение ошибок в определении величины Y. В качестве величины ошибки может быть использовано значение ДИ рассчитанное для одной из точек или cтандартная ошибка для оценки Y (sy), найденая функцией ЛИНЕЙН.

=> Особенности Office-2007.
Линии тренда и планки погрешностей задаются на вкладке Макет, в разделе Анализ.

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

Встроенные функции. Статистический анализ. Работа с математическими и статистическими функциями

MS EXCEL обеспечивает 10 разных категорий функций: математические/тригонометрические, инженерные, логические, текстовые, статистические, функции категории дата/время, функции для работы с базами данных/списками, финансовые, информационные и функции категории ссылки/массивы.

Программа EXCEL содержит более 400 встроенных функций, которые можно выбрать с помощью Мастера функций.

Формулы, содержащие функции, можно вводить непосредственно в ячейку, в строку формул или создавать с помощью Мастера функций

Для вызова Мастера функций необходимо выбрать команду Вставить функцию fx из меню Формулы или нажать на панели инструментов формула кнопку

После её нажатия появится окно Мастера функций (рис. 3.1 рис. 3.1).

Запуск Мастера функций

В открывшемся диалоговом окне выберите категорию и имя функции, а затем в полях с соответствующими подсказками введите аргументы (рис. 3.2 рис. 3.2). После нажатия кнопки ОК, готовая функция появится в строке формул

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

Диалоговое окно Аргументы функции СРЗНАЧ (A1:A10)

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

  1. числовые константы, например, функция ПРОИЗВЕД(2;3) вычисляет произведение чисел 2 и 3, т.е. 2•3.
  2. ссылки на ячейки и блоки ячеек (функция ПРОИЗВЕД (А1;С1:СЗ) вычисляет произведение содержимого ячеек А1,С1,С2 и С3, т.е. А1•С1•С2•СЗ.
  3. текстовые константы (заключенные в кавычки).
  4. логические значения.
  5. массивы.
  6. имена ссылок, например, если ячейке А10 присвоить имя СУММА –последовательность команд Формулы Присвоить имя. – рис. 3.3 рис. 3.3), а блоку ячеек В10:Е10 – имя ИТОГИ, то допустима следующая запись: =СУММ(СУММА;ИТОГИ).
  7. смешанные аргументы, например, =СРЗНАЧ (Группа;АЗ;5*3)

Присвоение имени ячейке или блоку ячеек

Пример 1. Вычислить значения функции

Y=e x *sin(x) для $-1geq x leq 1$$Delta x=0.1$

  1. Заполним столбец А значениями аргумента функции. Чтобы не вводить их вручную, применим следующий прием. Введите в ячейку А1 начальное значения аргумента (-1). Во вкладке Главная> Редактирование выберите кнопку Заполнить, затем Прогрессия и в открывшемся диалоговом окне укажите предельное значение (1), шаг(0,2) и направление По столбцам (рис. 3.4 рис. 3.4). После нажатия кнопки ОК в столбце А будут введены все значения аргумента

Автозаполнение ячеек

  1. В ячейку В1 введите формулу =exp(А1)*sin(A1). Размножьте эту формулу на остальные ячейки столбца B , ухватив левой мышью маркер заполнения (черный квадратик в правом нижнем углу рамки выделенной ячейки B1 ) и протащив маркер до конца изменения аргумента. В итоге будут вычислены соответствующие значения функции.
Логические функции

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

Так, функция ЕСЛИ выполняет проверку условия, задаваемого первым аргументом логич_выр:

=ЕСЛИ(логич_выр; знач_да; знач_нет) и возвращает знач_да, если условие выполнено (ИСТИНА), и знач_нет, в противном случае (ЛОЖЬ).

Если значение в ячейке А6<10, то функция вернет результат 5, а иначе – 10.

Если значение в ячейке B4>80, то в ячейке с приведенной формулой будет записано «Сданы», иначе – «Не сданы».

Если сумма значений в столбце А1:А10 больше 0, то вычислится сумма значений в столбце В1:В10, в противном случае результат – 0.

Дополнительные логические функции

позволяют создавать сложные условия, например:

Если суммы и в столбце А1:А10 и в столбце В1:В10 положительны, то вычислить суму значений в ячейках А1:В10, иначе – 0.

Статистические функции

MS EXCEL предоставляет широкие возможности для анализа статистических данных. Для решения простых задач можно использовать встроенные функции. Рассмотрим некоторые из них.

  1. Вычисление среднего арифметического последовательности чисел:

=СРЗНАЧ (числа).

  1. Нахождение максимального (минимального) значения:
  1. Вычисление медианы (числа, являющегося серединой множества):

=МЕДИАНА(числа).

  1. Вычисление моды (наиболее часто встречающегося значения в множестве):

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

  1. Дисперсия:
  1. Стандартное отклонение:

=СТАНДОТКЛОН( числа).

Статистический анализ с помощью Пакета анализа

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

Чтобы воспользоваться инструментами анализа, выполните следующие действия.

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

Инструмент Описательная статистика

  1. Выберите из списка название нужного инструмента анализа и нажмите кнопку ОК.

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

Инструмент Описательная статистика формирует таблицу статистических данных, ускоряя и упрощая этот процесс по сравнению с использованием формул 1- 6 (рис. 3.6 рис. 3.6).

Обработка столбца В инструментом Описательная статистика

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

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

Пример 2. Пусть дана таблица с данными о температуре воздуха в Краснодаре летом 2014г. Интервал изменения температуры от 18 до 38 градуса по Цельсию (его можно определить с помощью функций МАКС() и МИН()).

  1. Разобьем этот интервал на подинтервалы – карманы шириной, например, 2 градуса по Цельсию (ширина карманов не обязательно должна быть равной).
  2. Воспользуемся командой Заполнить из меню Главная в группе Редактирование для быстрого заполнения столбца карманов (значения в столбце будут изменятся от 18 до 38 градусов по Цельсию с шагом 2 градуса).
  3. Выполним команду Анализ данных из меню Данные. В открывшемся диалоговом окне зададим входной интервал (это ячейки с данными о температуре), интервал карманов, выходной интервал (надо указать только верхнюю, левую ячейку для вывода результатов) и установим флажок Вывод графика.
  4. После нажатия кнопки ОК на экран будет выведена гистограмма, а рядом со столбцом карманов появится столбец частот, показывающий, сколько дней летом в Краснодаре имели температуру, попадающую в каждый интервал.

ЗАДАНИЕ

Каждый вариант состоит из двух заданий. Для выполнения первого задания необходимо:

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

Исходными данными для второго задания являются варианты заданий к лабораторной работе № 1. Необходимо:

Массивы.

Для начала следует понимать, что такое массив и какие массивы бывают.

Массивом называют группу данных объединенных (сгруппированных) в одну структуру (группу)

В «Excel» массивы подразделяют на три типа в зависимости от структуры расположения данных в таблице:

Горизонтальный одномерный (линейный) массив – массив, в котором данные расположены горизонтально в одну строку.

Вертикальный одномерный (линейный) массив – массив, в котором данные расположены вертикально в один столбец.

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

Формулы (функции) массивов.

Для работы с массивами в «Эксель» предусмотрены специальные формулы – формулы массивов.

Формулы массивов в свою очередь классифицируются на формулы, которые выводят (рассчитывают) единичный результат, и формулы которые рассчитывают и выдают результат в виде массива (матрицы).

Для расчета данных в массивах могут применяться и обычные функции. При нажатии клавиш Ctrl + Shift + Enter обычная формула выделяется фигурными скобками и становится формулой массива.

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

Смета на проведение работ в Excel.
Вид работКоличествоТрудозатраты на единицуСтоимость одного чел часа
Прокладка кабеля, м250,5123
Укладка тротуарной плитки, м²450,125244
Покраска металлических поверхностей, м²40,2233
Монтаж дверного замка, шт.120,840
Итого:3480,4

Чтобы получить полную стоимость работ следует перемножить количество, стоимость и трудозатраты на единицы для каждого вида работ, а потом сложить затраты на каждый вид работ. Сделать это можно в несколько действий по порядку, а можно написать одну единственную функцию массива: и нажать сочетание клавиш «Ctrl + Shift + Enter», чтобы «Excel» распознал формулу массив.

Функция формула массива

Рассмотрим функцию массива ТРАНСП(). Эта функция полностью относится к функциям массива и производить транспонирование выделенного массива, то есть меняет местами столбцы и строки (переворачивает таблицу). Чтобы использовать данную функцию следует:

Функция формула массива трансп

  • Выделить диапазон, в который планируете транспонировать таблицу (если в исходной таблице четыре столбца и шесть строк, то выделяем шесть столбцов и четыре строки);
  • В строке функций пишем =ТРАНСП();
  • В скобках указать массив, который вы хотите транспонировать (перевернуть) и нажать клавишу «ENTER».

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector