Лабораторная работа «Статистические функции Excel»
методическая разработка по теме

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

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

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

Выделим блок D2:D5. Начнем ввод формулы – наберем знак =. Выделим блок B2:B5, наберем знак минус -, выделим блок С2:С5. Ввод формул массива заканчивается комбинацией клавиш Ctrl+Shift+Enter. После нажатия такой комбинации во всех ячейках блока D2:D5 появится формула .

• Основные правила работы с формулами массива:

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

• Функции Excel, используемые для статистического анализа

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

В категорию статистических функций Excel входит около 80 функций, кроме того, значительное число функций статистического анализа входят в надстройку «Пакет анализа».

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

• МАКС(число1;число2; . ) — возвращает наибольшее значение из набора значений.
• Число1, число2. — от 1 до 30 чисел, среди которых требуется найти наибольшее.
• Можно задавать аргументы, которые являются числами, пустыми ячейками, логическими значениями или текстовыми представлениями чисел. Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, вызывают значения ошибок.
• Если аргумент является массивом или ссылкой, то в нем учитываются только числа. Пустые ячейки, логические значения или текст в массиве или ссылке игнорируются. Если логические значения или текст не должны игнорироваться, следует использовать функцию МАКСА . Если аргументы не содержат чисел, то функция МАКС возвращает 0 (ноль);
• МИН(число1;число2; . ) — возвращает наименьшее значение из набора значений, в остальном полностью аналогична функции ^ МАКС ;
• СРЗНАЧ(число1; число2; . ) — возвращает среднее (арифметическое) своих аргументов.
• Число1, число2, . — это от 1 до 30 аргументов, для которых вычисляется среднее.
• Аргументы должны быть либо числами, либо именами, массивами или ссылками, содержащими числа.
• Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако ячейки, которые содержат нулевые значения, учитываются ;

ТЕНДЕНЦИЯ ( известные_значения_y ; известные_значения_x; новые значения_x; конст) — возвращает значения в соответствии с линейным трендом, т.е. аппроксимирует прямой линией (по методу наименьших квадратов) массивы ”известные_значения_y” и “ известные_значения_x” . Возвращает значения y, в соответствии с этой прямой для заданного массива новые_значения_x .

РОСТ(известные_значения_y;известные_значения_x;новые_значения_x; конст) — возвращает значения y для последовательности новых значений x, задаваемых с помощью существующих x- и y-значений, т.е. функция рассчитывает прогнозируемый экспоненциальный рост на основании имеющихся данных.

• Функция ПРЕДСКАЗ имеет аргументы , указанные ниже.
• x — обязательный аргумент. Точка данных, для которой предсказывается значение.
• Известные_значения_y — обязательный аргумент. Зависимый массив или интервал данных.
• Известные_значения_x — обязательный аргумент. Независимый массив или интервал данных.
• Если x не является числом, функция ПРЕДСКАЗ возвращает значение ошибки #ЗНАЧ!.
• Если аргументы «известные_значения_y» и «известные_значения_x» пусты или количество точек данных в этих аргументах не совпадает, функция ПРЕДСКАЗ возвращает значение ошибки #Н/Д.
• Если дисперсия аргумента «известные_значения_x» равна 0, функция ПРЕДСКАЗ возвращает значение ошибки #ДЕЛ/0!.

• Замечания
• 1) Формулы, которые возвращают массивы, должны быть введены как формулы массива.

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

Для приведенных в таблице 2 данных о реализации гостиничных услуг сетью отелей «Европа » вычислить:

Мнение эксперта

Витальева Анжела, консультант по работе с офисными программами

Со всеми вопросами обращайтесь ко мне!

Задать вопрос эксперту

Описательная статистика Descriptive statistics — техника сбора и суммирования количественных данных, которая используется для превращения массы цифровых данных в форму, удобную для восприятия и обсуждения. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!

НОУ ИНТУИТ | Лекция | Основы анализа данных

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

Анализ данных в Microsoft Excel

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

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

Далее мы рассмотрим некоторые инструменты, включенные в Пакет анализа .

Мнение эксперта

Витальева Анжела, консультант по работе с офисными программами

Со всеми вопросами обращайтесь ко мне!

Задать вопрос эксперту

В доступной нам литературе, мы не нашли четкого алгоритма трехфакторного дис-персионного анализа для количественных признаков равномерного комплекса , но, поскольку необходимость в нем высока, разработали его сами, опираясь на алгоритмы Н. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!

Статистический анализ партии обработанных изделий в MS Excel | Статья в журнале «Молодой ученый»

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