Зачетная работа по Excel

Зачетная работа по Excel

Вариант 6

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

Задание 1

Создайте показанную ниже таблицу на чистом рабочем листе Excel.

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

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

33
12
14						1,3
21
20
	7	13	15	17	11	6	12	19

Примечание. В одной из ячеек мелким шрифтом показан «правильный» для этой ячейки результат (для самопроверки).

Задание 2

Возможный способ расчета дисперсии по сгруппированным статистическим данным.

Во втором столбце показанной ниже таблицы приведены xi – наблюдаемые значения признака для элементов статистической совокупности, а в третьем столбце fi – частота повторения признака в i‑ой группе (столько раз наблюдалось значение xi в статистической совокупности). (В статистике такое представление данных называют вариационным рядом; число i нумерует группы с одинаковым значением признака). При таком представлении данных для расчета среднего значения и дисперсии признака нельзя использовать стандартные функции Excel СРЗНАЧ и ДИСП.

Скопируйте показанную ниже таблицу на чистый рабочий лист Excel.

i	xi	fi	(xi - xcр)2
1	2,25	1
2	2,5	2
3	2,75	1
4	3	39
5	3,25	98
6	3,5	222
7	3,75	249
8	4	234
9	4,25	189
10	4,5	183
11	4,75	78
12	5	33

1) В какой-то произвольной ячейке рабочего листа (вне таблицы!) рассчитайте xcp – среднее значение признака. Среднее необходимо рассчитывать как среднее арифметическое взвешенное:

(Суммы в этой формуле – по всем значениям i). Для расчёта суммы, стоящей в числителе, используйте стандартную функцию СУММПРОИЗВ.

2) В ячейках четвёртого столбца таблицы создайте формулы, рассчитывающие значения (xi ‑ xcр)2 . Обязательное условие: формула вводится с клавиатуры только в одну ячейку (в любую!), а в остальные ячейки – копируется!

3) В какой-то произвольной ячейке рабочего листа (вне таблицы!) рассчитайте σ2 – дисперсию признака. Дисперсию необходимо рассчитывать как взвешенную:

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

4) Используя стандартную функцию КОРЕНЬ, в какой-то произвольной ячейке рабочего листа (вне таблицы!) рассчитайте среднее квадратическое отклонение:

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

Задание 3

1) Используя таблицу из Задания 2, постройте обычную гистограмму, на которой по вертикальной оси должны отображаться значения частот fi , а по горизонтальной – значения xi .

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

Задание 4

Создайте показанную ниже таблицу на чистом рабочем листе Excel.

i	xi	fi	ni	Ni
1	2,25	1
2	2,5	2
3	2,75	1
4	3	39
5	3,25	98
6	3,5	222
7	3,75	249
8	4	234
9	4,25	189
10	4,5	183
11	4,75	78
12	5	33

1) В пустых ячейках четвёртого столбца таблицы создайте формулы, рассчитывающие значения ni:

Обязательное условие: формула вводится с клавиатуры только в одну ячейку (в любую!), а в остальные ячейки – копируется!

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

Обязательное условие: формула вводится с клавиатуры только в одну ячейку (в любую!), а в остальные ячейки – копируется!

При создании этой формулы воспользуйтесь стандартными функциями ПИ(), EXP и КОРЕНЬ, а значения величин σ2 и xcp возьмите те, которые получены в Задании 2 (скопируйте соответствующие ячейки с рабочего листа Задания 2 на данный рабочий лист, копируя только значения, а не формулы).

3) Постройте диаграмму типа «точечная», на которой отобразите зависимости Ni и ni от xi. (Значения xi должны быть по горизонтальной оси, а Ni и ni по вертикальной).

4) Отформатируйте построенную диаграмму в чёрно-белом стиле. Зависимость n(x) должна отображаться на диаграмме без линии (только точками - маркерами; тип маркера выберите любой), а зависимость N(x) только линией (без маркеров!).