Практическая работа 5 ms excel статистические функции решение

Обновлено: 21.01.2025

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

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

Рассмотрим работу с использованием массивов на следующем примере. Требуется определить прибыль для каждого года деятельности отеля, представленного в таблице 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 — множество значений y, которые уже известны для соотношения y = mx + b.
Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная.
Если массив известные_значения_y имеет одну строку, то каждая строка массива известные_значения_x интерпретируется как отдельная переменная.
Известные_значения_x — необязательное множество значений x, которые уже известны для соотношения y = mx + b .
Массив известные_значения_x может содержать одно или несколько множеств переменных. Если используется только одна переменная, то известные_значения_y и известные_значения_x могут иметь любую форму, при условии, что они имеют одинаковую размерность.
Если используется более одной переменной, то известные_значения_y должны быть вектором (то есть интервалом высотой в одну строку или шириной в один столбец).
Если известные_значения_x опущены, то предполагается, что это массив такого же размера, как и известные_значения_y .
Новые_значения_x — новые значения x, для которых ТЕНДЕНЦИЯ возвращает соответствующие значения y. Новые_значения_x должны содержать столбец (или строку) для каждой независимой переменной, как и известные_значения_x . Таким образом, если известные_значения_y — это один столбец, то известные_значения_x и новые_значения_x должны иметь такое же количество столбцов. Если известные_значения_y — это одна строка, то известные_значения_x и новые_значения_x должны иметь такое же количество строк.
Если новые_значения_x опущены, то предполагается, что они совпадают с известные_значения_x .
Если опущены оба массива известные_значения_x и новые_значения_x , то предполагается, что это массив такого же размера, что и известные_значения_y .
Конст — логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.
Если конст имеет значение ИСТИНА или опущено, то b вычисляется обычным образом.
Если конст имеет значение ЛОЖЬ, то b полагается равным 0, и значения m подбираются таким образом, чтобы выполнялось соотношение y = mx .

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

Известные_значения_y — это множество значений y , которые уже известны в соотношении y = b*m x . Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная. Если массив известные_значения_y имеет одну строку, то каждая строка массива известные_значения_x интерпретируется как отдельная переменная.
Известные_значения_x — это необязательное множество значений x , которые уже известны для соотношения y=b*m x . Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная. Массив известные_значения_x может содержать одно или несколько множеств переменных. Если используется только одна переменная, то известные_значения_y и известные_значения_x могут иметь любую форму, при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то известные_значения_y должны быть вектором (то есть интервалом высотой в одну строку или шириной в один столбец). Если известные_значения_x опущены, то предполагается, что это массив такого же размера, как и известные_значения_y .
Новые_значения_x — это новые значения x , для которых РОСТ возвращает соответствующие значения y . Новые_значения_x должны содержать столбец (или строку) для каждой независимой переменной, как и известные_значения_x . Таким образом, если известные_значения_y — это один столбец, то известные_значения_x и новые_значения_x должны иметь такое же количество столбцов. Если известные_значения_y — это одна строка, то известные_значения_x и новые_значения_x должны иметь такое же количество строк. Если аргумент новые_значения_x опущен, то предполагается, что он совпадает с аргументом известные_значения_x. Если оба аргумента известные_значения_x и новые_значения_x опущены, то предполагается, что это массив такого же размера, как и известные_значения_y .
Конст — это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 1. Если конст имеет значение ИСТИНА или опущено, то b вычисляется обычным образом. Если конст имеет значение ЛОЖЬ, то b полагается равным 1, а значения m подбираются так, чтобы y = m x .

ПРЕДСКАЗ(x, известные_значения_y, известные_значения_x ) – вычисляет или предсказывает будущее значение по существующим значениям. Предсказываемое значение — это значение y, соответствующее заданному значению x. Значения x и y известны; новое значение предсказывается с использованием линейной регрессии. Эту функцию можно использовать для прогнозирования будущих продаж, потребностей в оборудовании или тенденций потребления.

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

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

Использовать встроенные статистических функции Excel 2003 для расчетов.

Задание 1. Рассчитать количество прожитых дней.

Технология работы:

Запустить приложение Excel 2003.

В ячейку A1 ввести дату своего рождения (число, месяц, год – 20.12.81). Зафиксируйте ввод данных.

Просмотреть различные форматы представления даты (Формат – Формат ячейки – Число – Числовые форматы - Дата) . Перевести дату в тип ЧЧ.ММ.ГГГГ. Пример, 14.03.2001

Рассмотрите несколько типов форматов даты в ячейке А1.

В ячейку A2 ввести сегодняшнюю дату.

В ячейке A3 вычислить количество прожитых дней по формуле =A2-A1. Результат может оказаться представленным в виде даты, тогда его следует перевести в числовой тип. (Формат – Формат ячейки – Число – Числовые форматы – Числовой – число знаков после запятой – 0).

Задание 2. Возраст учащихся. По заданному списку учащихся и даты их рождения. Определить, кто родился раньше (позже), определить кто самый старший (младший).

Технология работы:

Получите от преподавателя файл Возраст. По локальной сети: Откройте папку Мое сетевое окружение–Соседние компьютеры–Great– Электронные_таблицы, найдите файл Возраст. Скопируйте его любым известным вам способом.

Откройте свою папку (с номером вашей группы\класса). Вставьте в нее скопированный ранее файл.

Рассчитаем возраст учащихся. Чтобы рассчитать возраст необходимо с помощью функции СЕГОДНЯ выделить сегодняшнюю текущую дату из нее вычитается дата рождения учащегося, далее из получившейся даты с помощью функции ГОД выделяется из даты лишь год. Из полученного числа вычтем 1900 – века и получим возраст учащегося. В ячейку D3 записать формулу =ГОД(СЕГОДНЯ()-С3)-1900. Результат может оказаться представленным в виде даты, тогда его следует перевести в числовой тип. (Формат – Формат ячейки – Число – Числовые форматы – Числовой – число знаков после запятой – 0).

Определим самый ранний день рождения. В ячейку C22 записать формулу =МИН(C3:C21);

Определим самого младшего учащегося. В ячейку D22 записать формулу =МИН(D3:D21);

Определим самый поздний день рождения. В ячейку C23 записать формулу =МАКС(C3:C21);

Определим самого старшего учащегося. В ячейку D23 записать формулу =МАКС(D3:D21).

Самостоятельная работа:

Задача. Произведите необходимые расчеты роста учеников в разных единицах измерения.

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

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

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

=ЦЕЛОЕ((СЕГОДНЯ()-E4)/365,25)

Прокомментируем ее. Из сегодняшней даты вычитается дата рождения ученика. Таким образом, получаем полное число дней, прошедших с рождения ученика. Разделив это количество на 365,25 (реальное количество дней в году, 0,25 дня для обычного года компенсируется високосным годом), получаем полное количество лет ученика; наконец, выделив целую часть, — возраст ученика.
Является ли девочка отличницей, определяется формулой (на примере ячейки H4):

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

=СУММЕСЛИ(F4:F15;"м";D4:D15)/СЧЁТЕСЛИ(F4:F15;"м")

Функция СУММЕСЛИ позволяет просуммировать значения только в тех ячейках диапазона, которые отвечают заданному критерию (в нашем случае ребенок является мальчиком). Функция СЧЁТЕСЛИ подсчитывает количество значений, удовлетворяющих заданному критерию. Таким образом и получаем требуемое.
Для подсчета доли отличниц среди всех девочек отнесем количество девочек-отличниц к общему количеству девочек (здесь и воспользуемся набором значений из одной из вспомогательных колонок):

=СУММ(H4:H15)/СЧЁТЕСЛИ(F4:F15;"ж")

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

=ABS(СУММЕСЛИ(G4:G15;15;D4:D15)/СЧЁТЕСЛИ(G4:G15;15)-
СУММЕСЛИ(G4:G15;16;D4:D15)/СЧЁТЕСЛИ(G4:G15;16))

Обратите внимание на то, что формат данных в ячейках G18:G20 – числовой, два знака после запятой. Таким образом, задача полностью решена. На рисунке представлены результаты решения для заданного набора данных.

Самостоятельная работа:

С использованием электронной таблицы произвести обработку данных помощью статистических функций.
1. Даны сведения об учащихся класса, включающие оценки в течение одного месяца. Подсчитайте количество пятерок, четверок, двоек и троек, найдите средний балл каждого ученика и средний балл всей группы. Создайте диаграмму, иллюстрирующую процентное соотношение оценок в группе.
2. Четверо друзей путешествуют на трех видах транспорта: поезде, самолете и пароходе. Николай проплыл 150 км на пароходе, проехал 140 км на поезде и пролетел 1100 км на самолете. Василий проплыл на пароходе 200 км, проехал на поезде 220 км и пролетел на самолете 1160 км. Анатолий пролетел на самолете 1200 км, проехал поездом 110 км и проплыл на пароходе 125 км. Мария проехала на поезде 130 км, пролетела на самолете 1500 км и проплыла на пароходе 160 км.
Построить на основе вышеперечисленных данных электронную таблицу.

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

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

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

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

Определить среднее количество километров по всем видам транспорта.

3. Создайте таблицу “Озера Европы”, используя следующие данные по площади (кв. км) и наибольшей глубине (м): Ладожское 17 700 и 225; Онежское 9510 и 110; Каспийское море 371 000 и 995; Венерн 5550 и 100; Чудское с Псковским 3560 и 14; Балатон 591 и 11; Женевское 581 и 310; Веттерн 1900 и 119; Боденское 538 и 252; Меларен 1140 и 64. Определите самое большое и самое маленькое по площади озеро, самое глубокое и самое мелкое озеро.
4. Создайте таблицу “Реки Европы”, используя следующие данные длины (км) и площади бассейна (тыс. кв. км): Волга 3688 и 1350; Дунай 2850 и 817; Рейн 1330 и 224; Эльба 1150 и 148; Висла 1090 и 198; Луара 1020 и 120; Урал 2530 и 220; Дон 1870 и 422; Сена 780 и 79; Темза 340 и 15. Определите самую длинную и самую короткую реку, подсчитайте суммарную площадь бассейнов рек, среднюю протяженность рек европейской части России.
5. В банке производится учет своевременности выплат кредитов, выданных нескольким организациям. Известна сумма кредита и сумма, уже выплаченная организацией. Для должников установлены штрафные санкции: если фирма выплатила кредит более чем на 70 процентов, то штраф составит 10 процентов от суммы задолженности, в противном случае штраф составит 15 процентов. Посчитать штраф для каждой организации, средний штраф, общее количество денег, которые банк собирается получить дополнительно. Определить средний штраф бюджетных организаций.

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

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

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

Запустить Мастер функций можно тремя способами:

«Вставить функцию»

Затем нужно кликнуть по полю «Категория» и выбрать значение «Статистические».

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

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

Оператор МАКС предназначен для определения максимального числа из выборки. Он имеет следующий синтаксис:

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

По названию функции МИН понятно, что её задачи прямо противоположны предыдущей формуле – она ищет из множества чисел наименьшее и выводит его в заданную ячейку. Имеет такой синтаксис:

СРЗНАЧ

Функция СРЗНАЧ ищет число в указанном диапазоне, которое ближе всего находится к среднему арифметическому значению. Результат этого расчета выводится в отдельную ячейку, в которой и содержится формула. Шаблон у неё следующий:

СРЗНАЧЕСЛИ

Функция СРЗНАЧЕСЛИ имеет те же задачи, что и предыдущая, но в ней существует возможность задать дополнительное условие. Например, больше, меньше, не равно определенному числу. Оно задается в отдельном поле для аргумента. Кроме того, в качестве необязательного аргумента может быть добавлен диапазон усреднения. Синтаксис следующий:

МОДА.ОДН

Формула МОДА.ОДН выводит в ячейку то число из набора, которое встречается чаще всего. В старых версиях Эксель существовала функция МОДА, но в более поздних она была разбита на две: МОДА.ОДН (для отдельных чисел) и МОДА.НСК(для массивов). Впрочем, старый вариант тоже остался в отдельной группе, в которой собраны элементы из прошлых версий программы для обеспечения совместимости документов.

МЕДИАНА

Оператор МЕДИАНА определяет среднее значение в диапазоне чисел. То есть, устанавливает не среднее арифметическое, а просто среднюю величину между наибольшим и наименьшим числом области значений. Синтаксис выглядит так:

СТАНДОТКЛОН

Формула СТАНДОТКЛОН так же, как и МОДА является пережитком старых версий программы. Сейчас используются современные её подвиды – СТАНДОТКЛОН.В и СТАНДОТКЛОН.Г. Первая из них предназначена для вычисления стандартного отклонения выборки, а вторая – генеральной совокупности. Данные функции используются также для расчета среднего квадратичного отклонения. Синтаксис их следующий:

НАИБОЛЬШИЙ

Данный оператор показывает в выбранной ячейке указанное в порядке убывания число из совокупности. То есть, если мы имеем совокупность 12,97,89,65, а аргументом позиции укажем 3, то функция в ячейку вернет третье по величине число. В данном случае, это 65. Синтаксис оператора такой:

НАИМЕНЬШИЙ

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

РАНГ.СР

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Данная практическая работа предназначена для изучения технологии поиска решения для задач оптимизации (минимизация, максимизации).

Содержимое разработки

Практическая работа №2

Тема: Подготовка и применение электронных таблиц при решении задач оптимизации (на примере функции «Поиск решения» MS Excel

Цель занятия Изучение технологии поиска решения для задач оптимизации (минимизация, максимизации).

Задание12.1.Используя режим подбора параметра (Сервис /Подбор параметра), определить штатное расписание фирмы. Известно, что в штате фирмы состоят: 6 курьеров, 8 младших менеджеров, 10 менеджеров, 3 заведующих отделом, 1 главный бухгалтер, 1 программист, 1 системный аналитик, 1 генеральный директор. Общий месячный фонд зарплаты составляет 100 000р. Определить, какими должны быть оклады сотрудников.

Каждый оклад является линейной функцией от оклада курьера, а именно:

зарплата = Ai*x + Bi,

где х - оклад курьера; Ai и Вi - коэффициенты, показывающие:

Ai - во сколько раз превышается значение х; Bi - на сколько превышается значение х. Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel.

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

Штатное расписание фирмы

Зарплата сотрудника

Количество сотрудников

Суммарная зарплата

Фонд заработной платы

3. Выделите отдельную ячейку D3 для зарплаты курьера (переменная «х») и все расчеты задайте с учетом этого. В ячейку D3 временно введите произвольное число.

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

= B6*$D$3 + С6

Далее скопируйте формулу из ячейки D6 вниз по столбцу автокопированием.

В столбце F задайте формулу расчета заработной платы всех работающих в данной должности. =D6*E6.

Далее скопируйте формулу из ячейки F6 вниз по столбцу автокопированием.

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

5. Произведите подбор зарплат сотрудников фирмы для суммарной заработной платы, 100 000 р. Для этого в меню Сервис активизируйте команду Подбор параметра.

В поле Установить в ячейке появившегося окна введите ссылку на ячейку F14,

содержащую формулу расчета фонда заработной платы; в поле Значение наберите искомый результат 100 000; в поле Изменяя значение ячейки введите ссылку на изменяемую ячейку D3,в которой находится значение зарплаты курьера, и щелкните по кнопке ОК. Произойдет обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100000 р.

Присвойте рабочему листу имя «Штатное расписание 1». Сохраните созданную книгу под именем «Штатное расписание» в своей папке.

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

Порядок работы

Запустите редактор электронных таблиц Microsoft Excel и откройте ранее созданный файл «Штатное расписание».

Читайте также: