Все о канализации и водоснабжении

Все о канализации и водоснабжении

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

где в числителе стоит большее значение выборочной дисперсии, а в знаменателе — меньшее. Для вывода о достоверности различий между выборками используют ОСНОВНОЙ ПРИНЦИП проверки статистических гипотез. Критические точки для
содержатся в таблице. Нулевую гипотезу отвергают, если фактически установленная величина
превзойдет или окажется равной критическому (стандартному) значению
этой величины для принятого уровня значимости и числа степеней свободы k 1 = n большая -1 ; k 2 = n меньшая -1 .

П р и м е р: при изучении влияния некоторого препарата на скорость проростания семян было установлено, что в экспериментальной партии семян и контроле средняя скорость проростания одинакова, но есть разница в дисперсиях.
=1250,
=417. Объемы выборок одинаковы и равны 20.

=2,12. Следовательно, нулевая гипотеза отвергается.

Особенности использования формулы ВПР в Excel

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

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

2. Номер столбца, указываемый в третьем поле «Номер_столбца» при использовании Мастера функций, должен отсчитываться, начиная с самого критерия.

3. Функция ВПР из диапазона с искомыми данными выдает первое сверху значение. Это значит, что, если во второй таблице, откуда мы пытаемся «подтянуть» некоторые данные, присутствуют несколько ячеек с одинаковым критерием, то в рамках выделенного диапазона ВПР захватит первое сверху значение. Об этом следует помнить. К примеру, если мы хотим к цене товара подтянуть количество из другой таблицы, а там этот товар встречается несколько раз (в нескольких строках), то к цене подтянется первое сверху количество.

4. Последний параметр формулы, который 0 (нуль), ставить нужно обязательно. Иначе формула может криво работать.

5. После использования ВПР саму формулу лучше сразу удалить, оставив только полученные значения. Делается это очень просто. Выделяем диапазон с полученными значениями, нажимаем «копировать» и на это же место с помощью специальной вставки вставляем значения. Если таблицы находятся в разных книгах Excel, то очень удобно разорвать внешние связи (оставив вместо них только значения) с помощью специальной команды, которая находится по пути Данные → Изменить связи.

Разорвать связи

После вызова функции разрывания внешних связей появится диалоговое окно, где нужно нажать кнопку «Разорвать связь» и затем «Закрыть».

Диалоговое окно разрыва внешних связей

Это позволит удалить сразу все внешние ссылки.

Расчет величины показателя корреляции в Excel

Пример 3. Используя данные 23 предприятий о: X — цена на товар А, тыс. руб.; Y — прибыль торгового предприятия, млн. руб, производится изучение их зависимости. Оценка регрессионной модели дала следующее: ∑(yi-yx) 2 = 50000; ∑(yi-yср) 2 = 130000. Какой показатель корреляции можно определить по этим данным? Рассчитайте величину показателя корреляции и, используя критерий Фишера, сделайте вывод о качестве модели регрессии.

Определим F крит из выражения:

F расч = R 2 /23*(1-R 2)

где R – коэффициент детерминации, равный 0,67.

Таким образом, расчетное значение F расч = 46.

Для определения F крит используем распределение Фишера (см. рисунок 3).

Рисунок 3 – Пример расчетов.

Таким образом, полученная оценка уравнения регрессии надежна.

На данном примере рассмотрим, как оценивается надежность полученного уравнение регрессии. Этот же тест используется для проверки гипотезы о том, что коэффициенты регрессии одновременно равны нулю, a=0 , b=0 . Другими словами, суть расчетов — ответить на вопрос: можно ли его использовать для дальнейшего анализа и прогнозов?

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

Итак, целью анализа является получение некоторой оценки, с помощью которой можно было бы утверждать, что при некотором уровне α полученное уравнение регрессии — статистически надежно. Для этого используется коэффициент детерминации R 2 .
Проверка значимости модели регрессии проводится с использованием F-критерия Фишера, расчетное значение которого находится как отношение дисперсии исходного ряда наблюдений изучаемого показателя и несмещенной оценки дисперсии остаточной последовательности для данной модели.
Если расчетное значение с k 1 =(m) и k 2 =(n-m-1) степенями свободы больше табличного при заданном уровне значимости, то модель считается значимой.

где m – число факторов в модели.
Оценка статистической значимости парной линейной регрессии производится по следующему алгоритму:
1. Выдвигается нулевая гипотеза о том, что уравнение в целом статистически незначимо: H 0: R 2 =0 на уровне значимости α.
2. Далее определяют фактическое значение F-критерия:

где m=1 для парной регрессии.
3. Табличное значение определяется по таблицам распределения Фишера для заданного уровня значимости, принимая во внимание, что число степеней свободы для общей суммы квадратов (большей дисперсии) равно 1 и число степеней свободы остаточной суммы квадратов (меньшей дисперсии) при линейной регрессии равно n-2 (или через функцию Excel FРАСПОБР(вероятность;1;n-2)).
F табл — это максимально возможное значение критерия под влиянием случайных факторов при данных степенях свободы и уровне значимости α. Уровень значимости α — вероятность отвергнуть правильную гипотезу при условии, что она верна. Обычно α принимается равной 0,05 или 0,01.
4. Если фактическое значение F-критерия меньше табличного, то говорят, что нет основания отклонять нулевую гипотезу.
В противном случае, нулевая гипотеза отклоняется и с вероятностью (1-α) принимается альтернативная гипотеза о статистической значимости уравнения в целом.
Табличное значение критерия со степенями свободы k 1 =1 и k 2 =48, F табл = 4

Выводы : Поскольку фактическое значение F > F табл, то коэффициент детерминации статистически значим (найденная оценка уравнения регрессии статистически надежна ) .

Проверка статистической значимости регрессии по функции FРАСПОБР

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

Для проверки значимости уравнения в целом выдвинем гипотезу Н 0 о статистической незначимости коэффициента детерминации и противоположную ей гипотезу Н 1 о статистической значимости коэффициента детерминации:

Проверим гипотезы с помощью F-критерия Фишера. Показатели приведены в таблице 2.

Таблица 2 – Исходные данные

Для этого используем в пакете Excel функцию:

  • α – вероятность, связанная с данным распределением;
  • p и n – числитель и знаменатель степеней свободы, соответственно.

Зная, что α = 0,05, p = 2 и n = 53, получаем следующее значение для F крит (см. рисунок 2).

Рисунок 2 – Пример расчетов.

Таким образом можно сказать, что F расч > F крит. В итоге принимается гипотеза Н 1 о статистической значимости коэффициента детерминации.

Лучшие функции Excel, используемые для решения экономических задач

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

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

Для удобства восприятия материала сгруппируем эти функции в три блока:

1. Функционал расчетных формул в Excel.

2. Функционал Excel для обработки табличных данных.

3. Инструменты Excel для моделирования и анализа экономических данных.

ФУНКЦИОНАЛ РАСЧЕТНЫХ ФОРМУЛ В EXCEL

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

Пакет встроенных расчетных формул включает в себя десятки наименований, но самыми востребованными в работе экономистов являются следующие формулы: ЕСЛИ, СУММЕСЛИ и СУММЕСЛИМН, ВПР и ГПР, СУММПРОИЗВ, СЧЕТЕСЛИ.

Решение экономической задачи с помощью формулы ЕСЛИ

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

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

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

где логическое выражение — данные, которые нужно проверить/сравнить (числовые или текстовые значения в ячейках);

значение_если_истина — результат, который появится в расчетной ячейке, если значение будет верным;

значение_если_ложь — результат, который появится в расчетной ячейке при неверном значении.

Задача № 1. Предприятие реализует три номенклатурные группы продукции: лимонад, минеральная вода и пиво. С 01.09.2020 запланировано установить скидку в размере 15 % на пиво.

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

Для решения задачи создаем в таблице третий столбец и прописываем в первой ячейке номенклатуры формулу: =ЕСЛИ(C4="пиво";15%;0).

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

В данном примере показано использование формулы ЕСЛИ для обработки текстовых значений в исходных данных.

Решение экономической задачи с помощью формулы СУММЕСЛИ

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

Задача № 2. На основе ведомости начисления заработной платы сотрудникам магазина нужно определить общую сумму зарплаты продавцов.

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

Для решения задачи добавим внизу таблицы еще одну строку «Всего продавцы». В ее ячейке под суммой зарплаты, начисленной сотрудникам магазина, пропишем следующую формулу:=СУММЕСЛИ(C4:C13;"продавец";D4:D13).

Таким образом мы задали условие, при котором табличный редактор обращается к столбцу с наименованием должностей (столбец С), выбирает в нем значение «Продавец» и суммирует данные ячеек с начисленной заработной платой из столбца D в привязке к этой должности.

Результат решения задачи — в табл. 2.

Решение экономической задачи с помощью формул ВПР и ГПР

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

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

Формулы прописывают в общем виде следующим образом:

=ВПР(искомое значение, которое требуется найти; таблица и диапазон ячеек для выборки данных; номер столбца, из которого будут подставлены данные; [интервал просмотра данных]);

=ГПР(искомое значение, которое требуется найти; таблица и диапазон ячеек для выборки данных; номер строки, из которой будут подставлены данные; [интервал просмотра данных]).

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

Задача № 3. У экономиста есть данные в виде таблицы Excel о реализации продукции за сентябрь в натуральном измерении (декалитрах) и данные о реализации продукции в сумме (рублях) в другой таблице Excel. Экономисту нужно предоставить руководству отчет о реализации продукции с тремя параметрами:

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

Для решения этой задачи с помощью формулы ВПР нужно последовательно выполнить следующие действия.

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

Шаг 2. В первой ячейке столбца с данными о продажах в рублях прописываем расчетную формулу: =ВПР(B4:B13;Табл.4!B4:D13;3;ЛОЖЬ).

Пояснения к формуле:

В4:В13 — диапазон поиска значений по номенклатуре продукции в создаваемом отчете;

Табл.4!B4:D13 — диапазон ячеек, где будет производиться поиск, с наименованием таблицы, в которой будет организован поиск;

3 — номер столбца, по которому нужно выбрать данные;

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

Шаг 3. Продлеваем формулу первой ячейки до конца списка номенклатуры в создаваемом нами отчете.

Читайте также:  Формула ПРЕДСКАЗ для прогноза продаж с учетом сезонности в Excel

Шаг 4. В первой ячейке столбца с данными о цене реализации единицы продукции прописываем простую формулу деления значения ячейки столбца с суммой продаж на значение ячейки столбца с объемом продаж (=E4/D4).

Шаг 5. Продлим формулу с расчетом цены реализации до конца списка номенклатуры в создаваемом нами отчете.

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

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

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

Решение экономической задачи с помощью формулы СУММПРОИЗВ

Формула СУММПРОИЗВ позволяет экономистам справиться практически с любой экономической задачей, для решения которой нужно работать с несколькими массивами данных. Она обладает всеми возможностями рассмотренных выше формул, умеет суммировать произведения данных из списка до 255 источников (массивов).

Задача № 4. Есть реестр продаж различной номенклатуры продукции за сентябрь 2020 г. Нужно рассчитать из общего реестра данные о суммах реализации по основным номенклатурным группам продукции.

Чтобы выполнить задачу, добавим внизу реестра три новые строки с указанием групп продукции и пропишем в ячейке с будущими данными о продажах первой группы (пиво) следующую формулу: =СУММПРОИЗВ(((C4:C13=C16)*D4:D13)). Здесь указано, что в ячейке должно быть выполнено суммирование произведений значений диапазона ячеек столбца с наименованием групп продукции (C4:C13) с условием отбора наименования группы «Пиво» (С16) на значения ячеек столбца с суммами продаж (D4:D13).

Далее копируем эту формулу на оставшиеся две ячейки, заменив в них условия отбора на группу «Лимонад» (С17) и группу «Минеральная вода» (С18).

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

Решение экономической задачи с помощью формулы СЧЕТЕСЛИ

Формула СЧЕТЕСЛИ используется не так широко, как предыдущие, но она выручает экономистов, если нужно минимизировать ошибки при работе с таблицами Excel. Эта формула удобна для проверки корректности вводимых данных и установке различного рода запретов, что особенно важно, если с данными работает несколько пользователей.

Задача № 5. Экономисту поручили провести корректировку справочника номенклатуры ТМЦ в учетной базе данных компании. Справочник долгое время не проверяли, данные в него вносили порядка 10 человек, поэтому появилось много некорректных и дублирующих наименований.

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

Экономист может решить эту проблему с помощью формулы СЧЕТЕСЛИ. Нужно выполнить следующие действия:

  • выбираем диапазон ячеек, куда будут вноситься наименования номенклатуры (В5:В3005);
  • в меню редактора выбираем путь: ДанныеПроверка данных;
  • в появившемся диалоговом окне выбираем вкладку Параметры и указываем в выпадающем списке Типданных вариант Другой;
  • в строке Формула указываем: =СЧЕТЕСЛИ($В$5:$В$3005;В5)<=1;
  • в диалоговом окне на вкладке Сообщение об ошибке вводим текст сообщения и нажимаем кнопку «ОК».

Если кто-либо из сотрудников будет пытаться ввести в указанный диапазон ячеек наименование ТМЦ, которое уже есть в диапазоне, у него это не получится. Excel выдаст сообщение в таком виде (рис. 1).

ФУНКЦИОНАЛ EXCEL ДЛЯ ОБРАБОТКИ ТАБЛИЧНЫХ ДАННЫХ

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

Решение экономической задачи с применением функции сортировки данных

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

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

Сортировку данных выполнить просто:

  • выделяем курсором столбцы таблицы;
  • заходим в меню редактора: Данные → Сортировка;
  • выбираем нужные параметры сортировки и получаем новый вид табличных данных.

Задача № 6. Экономист должен подготовить отчет о заработной плате, начисленной сотрудникам магазина, с последовательностью от самой высокой до самой низкой зарплаты.

Для решения этой задачи берем табл. 2 в качестве исходных данных. Выделяем в ней диапазон ячеек с показателями начисления зарплат (B4:D13).

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

Нажимаем кнопку «ОК», и табл. 2 преобразуется в новую табл. 5, где в первой строке идут данные о зарплате директора в 50 000 руб., в последней — данные о зарплате грузчика в 18 000 руб.

Решение экономической задачи с использованием функционала Автофильтр

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

В табличном редакторе Excel есть два вида фильтров:

  • автофильтр — используют для фильтрации данных по простым критериям;
  • расширенный фильтр — применяют при фильтрации данных по нескольким заданным параметрам.

Автофильтр работает следующим образом:

  • выделяем курсором диапазон таблицы, данные которого собираемся отфильтровать;
  • заходим в меню редактора: Данные → Фильтр → Автофильтр;
  • выбираем в таблице появившиеся значения автофильтра и получаем отфильтрованные данные.

Задача № 7. Из общих данных о реализации продукции за сентябрь 2020 г. (см. табл. 4) нужно выделить суммы продаж только по группе лимонадов.

Для решения этой задачи выделяем в таблице ячейки с данными по реализации продукции. Устанавливаем автофильтр из меню: Данные Фильтр Автофильтр. В появившемся меню столбца с группой продукции выбираем значение «Лимонад». В итоге в табл. 6 автоматически остаются значения продаж лимонадов, а данные по группам «Пиво» и «Минеральная вода» скрываются.

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

Чтобы организовать «Диапазон условий», следует выполнить следующие действия:

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

Строка копий заголовков вместе с условиями отбора образуют «Диапазон условий».

Порядок работы с функционалом консолидации данных

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

Консолидация работает только с идентичными таблицами Excel, поэтому для успеха все объединяемые таблицы должны отвечать следующим требованиям:

  • макеты всех консолидируемых таблиц одинаковые;
  • названия столбцов во всех консолидируемых таблицах идентичные;
  • в консолидируемых таблицах нет пустых столбцов и строк.

Работа с функционалом консолидации включает ряд последовательных действий:

1) открываем файлы со всеми таблицами, из которых собираемся консолидировать данные;

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

3) в меню Excel открываем вкладки: Данные → Работа с данными → Консолидация;

4) в открывшемся диалоговом окне выбираем функцию консолидации (как правило, это «сумма», потому что нам требуется суммировать значения нескольких таблиц);

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

6) если требуется автоматическое обновление данных консолидированной таблицы при изменении данных исходных таблиц, ставим галочку напротив «Создавать связи с исходными данными»;

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

Решение экономической задачи с использованием функционала сводной таблицы для создания нового отчета

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

Для создания сводной таблицы нужно зайти в меню Excel и вызвать Мастера сводных таблиц. В моей версии редактора это выполняется через Вставка → Сводная таблица, в некоторых версиях нужно выбрать Данные → Сводная таблица.

В появившемся диалоговом окне формируем параметры будущей таблицы:

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

Задача № 8. Экономисту нужно создать отчет на основе реестра данных о реализации продукции за сентябрь 2020 г. В отчете должно быть два уровня группировки данных. На первом уровне нужно вывести итоги по группам продукции, на втором уровне — по ее номенклатурным позициям. Чтобы решить эту задачу, вызываем Мастера сводных таблиц. Указываем, что данные берем из ячеек табл. 4, а отчет будем размещать на новом листе книги Excel. В конструкторе отчета указываем, что в первой колонке отчета будут показатели групп и номенклатурных единиц продукции, во второй — данные о суммах реализации. После этого даем команду создать сводную таблицу. Результат — в табл. 7.

ИНСТРУМЕНТЫ EXCEL ДЛЯ МОДЕЛИРОВАНИЯ И АНАЛИЗА ЭКОНОМИЧЕСКИХ ДАННЫХ

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

Решение экономической задачи с помощью надстройки «Поиск решения»

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

Такими экономическими задачами могут быть:

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

Функция поиска решения является дополнительной надстройкой, поэтому в стандартном меню Excel мы ее не найдем. Чтобы использовать в своей работе функцию «Поиск решения», экономисту нужно сделать следующее:

  • в меню Excel выбрать путь: ФайлПараметрыНадстройки;
  • в появившемся списке надстроек выбрать «Поиск решения» и активировать эту надстройку;
  • вернуться в меню Excel и выбрать: ДанныеПоиск решения.

Задача № 9. Туристической компании необходимо организовать доставку 45 туристов в четыре гостиницы города с трех пунктов прибытия при минимально возможной сумме затрат. Для решения задачи составляем таблицу с исходными данными:

1. Количество прибывающих с каждого пункта — железнодорожный вокзал, аэропорт и автовокзал (ячейки Н6:Н8).

2. Количество забронированных для туристов мест в каждой из четырех гостиниц (ячейки D9:G9).

3. Стоимость доставки одного туриста с каждого пункта прибытия до каждой гостиницы размещения (диапазон ячеек D6:G8).

Исходные данные, размещенные таким образом, показаны в табл. 8.1.

Далее приступаем к подготовке поиска решения.

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

2. Выбираем на листе ячейку для расчета искомой функции минимизации затрат (J4) и прописываем в ячейке расчетную формулу: =СУММПРОИЗВ(D6:G8;D15:G17).

3. Заходим в меню Excel, вызываем диалоговое окно надстройки «Поиск решения» и указываем там требуемые параметры и ограничения (рис. 2):

  • оптимизировать целевую функцию — ячейка J4;
  • цель оптимизации — до минимума;
  • изменения ячейки переменных — диапазон ячеек второй таблицы D15:G17;
  • ограничения поиска решения:

– в диапазоне ячеек второй таблицы D15:G17 должны быть только целые значения (D15:G17=целое);

– значения диапазона ячеек второй таблицы D15:G17 должны быть только положительными (D15:G17>=0);

– количество мест для туристов в каждой гостинице таблицы для поиска решения должно быть равно количеству мест в исходной таблице (D18:G18 = D9:G9);

Читайте также:  Пример формулы для добавления символов в строке ячейки Excel

– количество туристов, прибывающих с каждого пункта, в таблице для поиска решения должно быть равно количеству туристов в исходной таблице (Н15:Н17 = Н6:Н8).

Далее даем команду найти решение, и надстройка рассчитывает нам результат оптимальной доставки туристов (табл. 8.2).

При такой схеме доставки целевое значение общей суммы расходов действительно минимальное и составляет 1750 руб.

Пакет расширенного анализа данных

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

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

Специализированные надстройки для финансово-экономической работы

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

Не будем останавливаться на таких надстройках, как Power Query, Power Pivot, Power Quick, так как они в большей степени используются в целях бизнес-аналитики, чем для решения экономических задач.

Есть и другие надстройки к Excel, которые могут облегчить работу специалистов финансово-экономических служб. Интерес представляют две бесплатные надстройки — «Финансист» и PowerFin.

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

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

Надстройка PowerFin будет полезна прежде всего экономистам, которые работают с кредитами и инвестициями. Она без проблем устанавливается в меню надстроек Excel и имеет следующие функции:

Расчет величины показателя корреляции в Excel

Пример 3. Используя данные 23 предприятий о: X — цена на товар А, тыс. руб.; Y — прибыль торгового предприятия, млн. руб, производится изучение их зависимости. Оценка регрессионной модели дала следующее: ∑(yi-yx) 2 = 50000; ∑(yi-yср) 2 = 130000. Какой показатель корреляции можно определить по этим данным? Рассчитайте величину показателя корреляции и, используя критерий Фишера, сделайте вывод о качестве модели регрессии.

Определим F крит из выражения:

F расч = R 2 /23*(1-R 2)

где R – коэффициент детерминации, равный 0,67.

Таким образом, расчетное значение F расч = 46.

Для определения F крит используем распределение Фишера (см. рисунок 3).

Рисунок 3 – Пример расчетов.

Таким образом, полученная оценка уравнения регрессии надежна.

1. Таблица значений F-критерия Фишера для уровня значимости α = 0.05

1 2 3 4 5 6 8 12 24
1 161,45 199,50 215,72 224,57 230,17 233,97 238,89 243,91 249,04 254,32
2 18,51 19,00 19,16 19,25 19,30 19,33 19,37 19,41 19,45 19,50
3 10,13 9,55 9,28 9,12 9,01 8,94 8,84 8,74 8,64 8,53
4 7,71 6,94 6,59 6,39 6,26 6,16 6,04 5,91 5,77 5,63
5 6,61 5,79 5,41 5, 19 5,05 4,95 4,82 4,68 4,53 4,36
6 5,99 5,14 4,76 4,53 4,39 4,28 4,15 4,00 3,84 3,67
7 5,59 4,74 4,35 4,12 3,97 3,87 3,73 3,57 3,41 3,23
8 5,32 4,46 4,07 3,84 3,69 3,58 3,44 3,28 3,12 2,93
9 5,12 4,26 3,86 3,63 3,48 3,37 3,23 3,07 2,90 2,71
10 4,96 4,10 3,71 3,48 3,33 3,22 3,07 2,91 2,74 2,54
11 4,84 3,98 3,59 3,36 3, 20 2,95 2,79 2,61 2,40

Когда m=1, выбираем 1 столбец.

k 2 =n-m=7-1=6 — т.е.6-я строка — берем табличное значение Фишера

F табл =5.99, у ср. = итого: 7

Влияние х на у — умеренное и отрицательное

ŷ — модельное значение.

F расч. = 28,648: 1 = 0,92
200,50: 5

А = 1/7 * 398,15 * 100% = 8,1% F табл

Нарушается данная модель, поэтому данное уравнение статистически не значимо.

Так как расчетное значение меньше табличного — незначимая модель.

1 Σ (y — ŷ) *100%
N y

A= 1/7*0,563494* 100% = 8,04991% 8,0%

Считаем, что модель точная, если средняя ошибка аппроксимации менее 10%.

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

Модель у = а * х b — степенная функция

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

log у = log a + b log x

Y=C+b*X -линейная модель.

С = 1,7605 — (- 0,298) * 1,7370 = 2,278

Возврат к исходной модели

Ŷ=10 с *x b =10 2.278 *x -0.298

№п/п У X Y X Y*X У I (y-ŷ) /yI
1 68,80 45,10 1,8376 1,6542 3,039758 2,736378 60,9614643 0,113932
2 61, 20 59,00 1,7868 1,7709 3,164244 3,136087 56,2711901 0,080536
3 59,90 57, 20 1,7774 1,7574 3,123603 3,088455 56,7931534 0,051867
4 56,70 61,80 1,7536 1,7910 3,140698 3, 207681 55,4990353 0,021181
5 55,00 58,80 1,7404 1,7694 3,079464 3,130776 56,3281590 0,024148
6 54,30 47, 20 1,7348 1,6739 2,903882 2,801941 60,1402577 0,107555
7 49,30 55, 20 1,6928 1,7419 2,948688 3,034216 57,3987130 0,164274
Итого 405, 20 384,30 12,3234 12,1587 21,40034 21,13553 403,391973 0,563493
Средняя 57,88571 54,90 1,760486 1,736957 3,057191 3,019362 57,62742 0,080499

Входим в EXCEL через «Пуск»-программы. Заносим данные в таблицу. В «Сервис» — «Анализ данных» — «Регрессия» — ОК

Если в меню «Сервис» отсутствует строка «Анализ данных», то ее необходимо установить через «Сервис» — «Настройки» — «Пакет анализа данных»

Прогнозирование спроса на продукцию предприятия. Использование в MS Excel функции «Тенденция»

A — спрос на товар. B — время, дни

№ п/п A
1 11 1
2 14 2
3 13 3
4 15 4
5 17 5
6 17,9
7 18,4 7

Шаг 1. Подготовка исходных данных

Шаг 2. Продлеваем временную ось, ставим на 6,7 вперед; имеем право прогнозировать на 1/3 от данных.

Шаг 3. Выделим диапазон A6: A7 под будущий прогноз.

Шаг 4. Вставка функция

Вставка диаграмма нестандартны гладкие графики

диапазон у готово.

Если каждое последующее значение нашего временной оси будет отличаться не на несколько процентов, а в несколько раз, тогда нужно использовать не функцию «Тенденция», а функцию «Рост».

1. Елисеева «Эконометрика»

2. Елисеева «Практикум по эконометрике»

3. Карлсберг «Excel для цели анализа»


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

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

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

Возвращает значение, обратное (правостороннему) F-распределению вероятностей. Если p = FРАСП(x;. ), то FРАСПОБР(p;. ) = x.

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

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

Чтобы узнать больше о новых функциях, см. статьи Функция F.ОБР и Функция F.ОБР.ПХ .

Поиск в нескольких столбцах или базе Excel с INDIRECT (ДВССЫЛ)

В примере (снимок № 6) имеется 3 таблицы. Диапазоны данных в каждой из них имеют названия «Финансы», «Производство» и «Продажи».

В этом примере вам нужно найти сотрудника из отдела продаж с идентификатором 3. Название диапазона вводится в C19, идентификатор – в E19. Ячейка B22 связана с C19, и B22 (идентификатор) – это Lookup_value. Выражение с ДВССЫЛ находится в таблице – INDIRECT воспринимает содержимое C19 как имя диапазона.

Далее указываете номер колонки в матрице, откуда функция должна извлекать сведения и отображать их в ячейке при помощи VLOOKUP. В Range_lookup введите 0, потому что здесь требуется только точное пересечение.

Excel INDIRECT

ВПР – одно из самых полезных и важных средств поиска в Microsoft Excel. Он обычно используется для выполнения запросов к огромным листам с кучей информации, когда ручная работа может занять слишком много времени. Буква «V» в VLOOKUP означает «вертикальный», поэтому ее также иногда называют формулой вертикального отбора.

Особенности функции вертикального поиска в Excel

  • Регистр в запросах не учитывается.
  • В строке «Порядок сортировки» значение по умолчанию равно 1 (TRUE-ПРАВДА). Поэтому лучше не пропускать этот аргумент, если вам нужны только точные совпадения.
  • В первом поле (там, где вы пишете запрос) допускается использование подстановочных знаков.

Функция ВПР в Ексель

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

Корреляционная зависимость. Коэффициент корреляции и его свойства. Уравнения регрессии.

ЗАДАЧА корреляционного анализа сводится к:

Установлению направления и формы связи между признаками;

Измерению ее тесноты.

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

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

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

По виду корреляционного поля можно судить о наличии или отсутствии связи и ее типе.



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

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

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

Показателем тесноты связи является коэффициент корреляции . Эмпирический коэффициент корреляции определяется выражением:

Коэффициент корреляции лежит в пределах от -1 до1 и характеризует степень близости между величинамиx иy . Если:

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

Уравнение прямой регрессии х нау в общем случае выглядит как

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

Критерий Фишера позволяет сравнивать величины выбороч­ных дисперсий двух независимых выборок. Для вычисления F эмп нуж­но найти отношение дисперсий двух выборок, причем так, что­бы большая по величине дисперсия находилась бы в числителе, а меньшая – в знаменателе. Формула вычисления критерия Фи­шера такова:

где — дисперсии первой и второй выборки соответственно.

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

Чис­ло степеней свободы определяется также просто:

k 1 =n l — 1 для первой выборки (т.е. для той выборки, величина дисперсии которой больше) и k 2 = n 2 — 1 для второй выборки.

В Приложе­нии 1 критические значения критерия Фишера находятся по величинам k 1 (верхняя строчка таблицы) и k 2 (левый столбец таблицы).

Если t эмп >t крит, то нулевая гипотеза принимается, в противном случае принимается альтернативная.

Пример 3. В двух третьих классах проводилось тестирование умственного развития по тесту ТУРМШ десяти учащихся. Полученные значения величин средних достоверно не различались, однако психолога интересует вопрос — есть ли различия в степени однородности показателей умственного развития между классами.

Решение. Для критерия Фишера необходимо сравнить дис­персии тестовых оценок в обоих классах. Резуль­таты тестирования представлены в таблице:

Рассчитав дисперсии для переменных X и Y, получаем:

s x 2 =572,83; s y 2 =174,04

Тогда по формуле (8) для расчета по F критерию Фишера находим:

Результаты решения данной задачи с применяемыми функциями в пакете Excel приведены на рисунке 1.

Рисунок 1 – Пример расчетов.

Читайте также:  Примеры решений распределения с помощью функции ГАУСС в Excel
№ п/п Наименование показателя Формула расчета
1 Коэффициент корреляции =КОРРЕЛ(B2:B7;C2:C7)
2 Расчетное значение t-критерия tp =ABS(C8)/КОРЕНЬ(1-СТЕПЕНЬ(C8;2))*КОРЕНЬ(6-2)
3 Табличное значение t-критерия trh =СТЬЮДРАСПОБР(0,05;4)
4 Табличное значение стандартного нормального распределения zy =НОРМСТОБР((0,95+1)/2)
5 Значение преобразования Фишера z’ =ФИШЕР(C8)
6 Левая интервальная оценка для z =C12-C11*КОРЕНЬ(1/(6-3))
7 Правая интервальная оценка для z =C12+C11*КОРЕНЬ(1/(6-3))
8 Левая интервальная оценка для rxy =ФИШЕРОБР(C13)
9 Правая интервальная оценка для rxy =ФИШЕРОБР(C14)
10 Стандартное отклонение для rxy =КОРЕНЬ((1-C8^2)/4)

Таким образом, с вероятностью 0,95 линейный коэффициент корреляции заключен в интервале от (–0,386) до (–0,990) со стандартной ошибкой 0,205.

Топ-5 функций Excel, которые сэкономят ваше время

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

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

1. ЕСЛИ

  • – относится к логическим функциям и позволяет делать расчеты для одного или нескольких условий.
  • Например, условие выплаты премии: если процент выполнения KPI больше 80%, то сотрудник получает премию в размере 10%, если меньше или равно 80%, то 0.
  • Функция Если может дополняться другими функциями, например:
  • СУММЕСЛИ – расчет суммы в зависимости от условий
  • СРЗНАЧЕСЛИ – расчет среднего в зависимости от условий
  • СЧЁТЕСЛИ – подсчет элементов в зависимости от условий.

Также есть еще функции СУММЕСЛИМН, СРЗНАЧЕСЛИМН и СЧЁТЕСЛИМН. Это функции применяются, если у нас есть несколько условий.

  1. Например, нужно посчитать сумму премий, выплаченных сотрудникам в четырех регионах с рейтингом результативности ниже 3.
  2. В решении этой задачи поможет функция СУММЕСЛИМН, так как у нас несколько условий: 1 – регион, 2 – Рейтинг результативности.

2. СУММПРОИЗВ

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

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

3. ВПР

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

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

  1. Например, нам нужно из таблицы 2 подтянуть процент целевой премии в таблицу 1 по табельному номеру.
  2. С помощью функции ВПР это можно сделать за 5 секунд.

4. ГПР

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

5. Сочетание функций ИНДЕКС+ПОИСКПОЗ

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

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

Мы хотим сделать предложение по повышению зарплаты, опираясь на данные матрицы.

Для этого мы используем 2 функции ПОИСКПОЗ для поиска по горизонтали и по вертикали для поиска значений. А функция ИНДЕКС ищет значение на их пересечении.

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

Описание работы функции ФИШЕР в Excel

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

  • не является числом. В такой ситуации функция ФИШЕР осуществит возвращение значения ошибки #ЗНАЧ!;
  • имеет значение либо меньше -1, либо больше 1. В данном случае функция ФИШЕР возвратит значение ошибки #ЧИСЛО!.

Уравнение, которое используется для математического описания функции ФИШЕР, имеет вид:

Рассмотрим применение данной функции на 3-x конкретных примерах.

ТОЧНЫЙ КРИТЕРИЙ ФИШЕРА

Рональд Фишер

Рональд Фишер

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

1. История разработки критерия

Впервые критерий был предложен Рональдом Фишером в его книге «Проектирование экспериментов». Это произошло в 1935 году. Сам Фишер утверждал, что на эту мысль его натолкнула Муриэль Бристоль. В начале 1920-х годов Рональд, Муриэль и Уильям Роуч находились в Англии на опытной сельскохозяйственной станции. Муриэль утверждала, что может определить, в какой последовательности наливали в ее чашку чай и молоко. На тот момент проверить правильность ее высказывания не представлялось возможным.

Это дало толчок идее Фишера о «нуль гипотезе». Целью стала не попытка доказать, что Муриэль может определить разницу между по-разному приготовленными чашками чая. Решено было опровергнуть гипотезу, что выбор женщина делает наугад. Было определено, что нуль-гипотезу нельзя ни доказать, ни обосновать. Зато ее можно опровергнуть во время экспериментов.

Было приготовлено 8 чашек. В первые четыре налито молоко сначала, в другие четыре – чай. Чашки были помешаны. Бристоль предложили опробовать чай на вкус и разделить чашки по методу приготовления чая. В результате должно было получиться две группы. История говорит, что эксперимент прошел удачно.

Благодаря тесту Фишера вероятность того, что Бристоль действует интуитивно, была уменьшена до 0.01428. То есть, верно определить чашку можно было в одном случае из 70. Но все же нет возможности свести к нулю шансы того, что мадам определяет случайно. Даже если увеличивать число чашек.

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

2. Для чего используется точный критерий Фишера?

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

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

3. В каких случаях можно использовать точный критерий Фишера?

  1. Сравниваемые переменные должны быть измерены в номинальной шкале и иметь только два значения, например, артериальное давление в норме или повышено, исход благоприятный или неблагоприятный, послеоперационные осложнения есть или нет.
  2. Критерий подходит для сравнения очень малых выборок: точный критерий Фишера может применяться для анализа четырехпольных таблиц в случае значений ожидаемого явления менее 10, что является ограничением для применения критерия хи-квадрат Пирсона.
  3. Точный критерий Фишера бывает односторонним и двусторонним. При одностороннем варианте точно известно, куда отклонится один из показателей. Например, во время исследования сравнивают, сколько пациентов выздоровело по сравнению с группой контроля. Предполагают, что терапия не может ухудшить состояние пациентов, а только либо вылечить, либо нет.
    Двусторонний тест является предпочтительным, так как оценивает различия частот по двум направлениям. То есть оценивается верятность как большей, так и меньшей частоты явления в экспериментальной группе по сравнению с контрольной группой.

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

4. Как рассчитать точный критерий Фишера?

Допустим, изучается зависимость частоты рождения детей с врожденными пороками развития (ВПР) от курения матери во время беременности. Для этого выбраны две группы беременных женщин, одна из которых — экспериментальная, состоящая из 80 женщин, куривших в первом триместре беременности, а вторая — группа сравнения, включающая 90 женщин, ведущих здоровый образ жизни на протяжении всей беременности. Число случаев ВПР плода в экспериментальной группе составило 10, в группе сравнения — 2.

Вначале составляем четырехпольную таблицу сопряженности:

Исход есть (Наличие ВПР) Исхода нет (Отсутствие ВПР) Всего
Фактор риска есть (Курящие) A = 10 B = 70 (A + B) = 80
Фактор риска отсутствует (Некурящие) C = 2 D = 88 (C + D) = 90
Всего (A + C) = 12 (B + D) = 158 (A + B + C + D) = 170

Точный критерий Фишера рассчитывается по следующей формуле:

где N — общее число исследуемых в двух группах; ! — факториал, представляющий собой произведение числа на последовательность чисел, каждое из которых меньше предыдущего на 1 (например, 4! = 4 · 3 · 2 · 1)

В результате вычислений находим, что P = 0,0137.

5. Как интерпретировать значение точного критерия Фишера?

Достоинством метода является соответствие полученного критерия точному значению уровня значимости p. То есть, полученное в нашем примере значение 0,0137 и есть уровень значимости различий сравниваемых групп по частоте развития ВПР плода. Необходимо лишь сопоставить данное число с критическим уровнем значимости, обычно принимаемым в медицинских исследованиях за 0,05.

  • Если значение точного критерия Фишера больше критического, принимается нулевая гипотеза и делается вывод об отсутствии статистически значимых различий частоты исхода в зависимости от наличия фактора риска.
  • Если значение точного критерия Фишера меньше критического, принимается альтернативная гипотеза и делается вывод о наличии статистически значимых различий частоты исхода в зависимости от воздействия фактора риска.

В нашем примере P < 0,05, в связи с чем делаем вывод о наличии прямой взаимосвязи курения и вероятности развития ВПР плода. Частота возникновения врожденной патологии у детей курящих женщин статистически значимо выше, чем у некурящих.

Примечания Remarks

Если x не является числом, то Прогноз возвращает #VALUE! If x is nonnumeric, Forecast returns the #VALUE! значение ошибки. error value.

Если известные_значения_y и известные_значения_x пусты или содержат различное количество точек данных, то Прогноз возвращает значение ошибки #N/a. If known_y’s and known_x’s are empty or contain a different number of data points, Forecast returns the #N/A error value.

Если дисперсия известные_значения_x равно нулю, то Прогноз возвращает #DIV/0! If the variance of known_x’s equals zero, Forecast returns the #DIV/0! значение ошибки. error value.

Уравнение для ПРЕДСКАЗ имеет значение + BX, где The equation for FORECAST is a+bx, where и and где x и y — пример среднего значения (известные_значения_x) и СРЗНАЧ (известные_значения_y). and where x and y are the sample means AVERAGE(known_x’s) and AVERAGE(known_y’s).

Поддержка и обратная связь Support and feedback

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Have questions or feedback about Office VBA or this documentation? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь. Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Критерии Стьюдента

Для оценки статистической значимости модели по параметрам рассчитывают t-критерии Стьюдента.

Оценка значимости модели с помощью критерия Стьюдента проводится путем сравнения их значений с величиной случайной ошибки:

t-критерии Стьюдента

Случайные ошибки коэффициентов линейной регрессии и коэффициента корреляции определяются по формулам:

Случайные ошибки коэффициентов линейной регрессии

Сравнивая фактическое и табличное значения t-статистики и принимается или отвергается гипотеза о значимости модели по параметрам.

Зависимость между критерием Фишера и значением t-статистики Стьюдента определяется так

Зависимость между критерием Фишера и значением t-статистики Стьюдента

Как и в случае с оценкой значимости уравнения модели в целом, модель считается ненадежной если tтабл > tфакт

Дисперсионный анализ

Пример . По совокупности 25 предприятий торговли изучается зависимость между признаками: X — цена на товар А, тыс. руб.; Y — прибыль торгового предприятия, млн. руб. При оценке регрессионной модели были получены следующие промежуточные результаты: ∑(y i -y x) 2 = 46000; ∑(y i -y ср) 2 = 138000. Какой показатель корреляции можно определить по этим данным? Рассчитайте величину этого показателя, на основе этого результата и с помощью F-критерия Фишера сделайте вывод о качестве модели регрессии.
Решение. По этим данным можно определить эмпирическое корреляционное отношение : , где ∑(y ср -y x) 2 = ∑(y i -y ср) 2 — ∑(y i -y x) 2 = 138000 — 46000 = 92 000.
η 2 = 92 000/138000 = 0.67, η = 0.816 (0.7 Fтабл, то найденная оценка уравнения регрессии статистически надежна.

Вопрос: Какую статистику используют для проверки значимости модели регрессии?
Ответ: Для значимости всей модели в целом используют F-статистику (критерий Фишера).

Ссылка на основную публикацию