Использование электронных таблиц Excel 2000 для вычисления выборочных характеристик данных

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

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

Характеристика пакета Excel

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

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

Использование специальных функций

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

Функция СРЗНАЧ вычисляет среднее арифметическое из нескольких массивов (аргументов) чисел. Аргументы число1, число2,... — это от 1 до 30 массивов для которых вычисляется среднее.

Функция МЕДИАНА позволяет получать медиану заданной выборки. Медиана - это элемент выборки, число элементов выборки со значениями больше которого и меньше которого равно.

Функция МОДА вычисляет наиболее часто встречающееся значение в выборке.

Функция ДИСП позволяет оценить дисперсию по выборочным данным.

Функция СТАНДОТКЛОН вычисляет стандартное отклонение.

Функция ЭКСЦЕСС вычисляет оценку эксцесса по выборочным данным.

Функция СКОС позволяет оценить асимметрию выборочного распределения.

Функция КВАРТИЛЬ вычисляет квартили распределения. Функция имеет формат КВАРТИЛЬ (массив, значение), где массив – интервал ячеек, содержащих значения СВ; значение определяет какая квартиль должна быть найдена (0 – минимальное значение, 1 – нижняя квартиль, 2 – медиана, 3 – верхняя квартиль, 4 – максимальное значение распределения).

Задание 1. Провести статистический анализ методом описательной статистики доходов населения в регионе 1 и регионе 2.

		сумма
		среднее
	1,11	дисперсия
154,95	1,05	станд. отклонение
		квартили
		квартили
		медиана
		мода
	-2,57	эксцесс
3,16		скос(ассиметрия)

Задание 2. Наблюдение посещаемости четырех внеклассных мероприятий в экспериментальном (20 человек) и контрольном (30 человек) классах дали значения (соответственно): 18, 20, 20, 18 и 15, 23, 10, 28. Требуется найти среднее значение, стандартное отклонение, медиану и квартили этих данных.

Задание 3. Найти среднее значение, медиану, стандартное отклонение и квартили результатов бега на дистанцию 100 м у группы студентов (с): 12,8; 13,2; 13,0; 12,9; 13,5; 13,1.

Задание 4. Определите верхнюю и нижнюю квартиль, выборочную асимметрию и эксцесс для данных измерений роста групп студенток: 164, 160, 157, 166, 162, 160, 161, 159, 160, 163, 170, 171.

Задание 5. Найти наиболее популярный туристический маршрут из четырех реализуемых фирмой, если за неделю последовательно были реализованы следующие маршруты: 1, 3, 3, 2, 1, 1, 4, 4, 2, 4, 1, 3, 2, 4, 1, 4, 4, 3, 1, 2, 3, 4, 1, 1, 3.

Использование инструмента Пакет анализа

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

Для установки пакета Анализ данных в Excel сделайте следующее:

- в меню Сервис выберите команду Надстройки;

- в появившемся списке установите флажок Пакет анализа.

Для использования статистического пакета анализа данных необходимо:

· указать курсором мыши на пункт меню Сервис и щелкнуть левой кнопкой мыши;

· в раскрывающемся списке выбрать команду Анализ данных (если команда Анализ данных отсутствует в меню Сервис, то необходимо установить в Excel пакет анализа данных);

· выбрать строку Описательная статистика и нажать кнопку ОK

· в появившемся диалоговом окне указать входной интервал, то есть ввести ссылки на ячейки, содержащие анализируемые данные;

· указать выходной интервал, то есть ввести ссылку на ячейку, в которую будут выведены результаты анализа;

· в разделе Группирование переключатель установить в положение по столбцам или по строкам;

· установить флажок в поле Итоговая статистика и нажать ОК.

Задание 6. В рабочей зоне производились замеры концентрации вредного вещества. Получен ряд значений (в мг./м³): 12, 16, 15, 14, 10, 20, 16, 14, 18, 14, 15, 17, 23, 16. Необходимо определить основные выборочные характеристики.

 

4. Технология создание интерактивного теста, на примере MS EXCEL.

Этап

1. Запустите программу MS Excel.

2. Выполните команду Сервис – Макрос – Безопасность. В открывшемся диалоговом окне Безопасность во вкладке Уровень безопасности установите Средняя.

3. В ячейку A1 введите название теста Проверка знаний по математике.

4. В ячейку B3 введите запись Вариант 1.

5. В ячейку D3 введите запись ФИО, а в ячейку D4 – Класс.

Этап

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

6. Введите в ячейку E4 списки классов, которые будут проходить тестирование. Встаньте в ячейку Е4.

1) Выбирается меню Данные. 2) В ниспадающем меню выбирается команда Проверка.
	3) В диалоговом окне выбирается тип данных - Список
4) В окне Источник перечисляются варианты ответов через точку с запятой.

 

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

7. В строке 6 оформите заголовки столбцов теста.

8. В ячейки В7:В16 введите вопросы, а в ячейки С7:С16 введите ответы в виде списка с выборочными четырьмя ответами, среди которых один правильный.

 

 

9. Лист 1 переименуйте Тест. (ПКМ на ярлычке листа)

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

10. Выполните команду Сервис – Макрос – Начать запись. Дайте имя макросу Очистка. Выделите все поля с ответами и нажмите клавишу delete. Также удалите фамилию ученика и класс.

11. Выполните команду Сервис – Макрос – Остановить запись.

12. Выполните команду Вид – Панели инструментов – Формы.

13. Найдите инструмент Кнопка, активизируйте его (щелкните на нем) и нарисуйте кнопку на листе.

14. Назначьте ей макрос Очистка.

15. Переименуйте кнопку.

16. Оформите тест, используя Формат – Ячейки.

Введите любые ответы, а также заполните ФИО и Класс.

Сохраните тест.

Этап

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

1. Переименуйте Лист2 в Результат.

2. В строки А2 и А3 введите записи ФИО и Класс соответственно.

3. Скопируйте с первого листа номера вопросов и сами вопросы в столбцы А6:А16 и В6:В16.

4. Введите остальные заголовки таблицы, согласно рисунку (Ваш ответ, Результат, Верный ответ).

5. Создадим первый макрос – Ваш ответ.

6. Перед созданием макросов на втором листе курсор на листе ответов устанавливайте в какую-нибудь пустую ячейку, где нет записей, например, для нашего примера F9.

7. Выполните команду Сервис – Макрос – Начать запись. Дайте имя макросу Ваш_ответ.

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

9. Установите курсор в ячейку В2, нажмите знак «=», перейдите на лист вопросов и щелкните мышью в ячейку Е4 (Петров Вася) и нажмите клавишу «Enter».

10. Аналогично введите класс.

11. Таким же образом в листе ответов введите в ячейку С7 ответ с листа вопросов.

12. Скопируйте остальные варианты ответов: установите курсор в ячейку С7 и подведите его в правый нижний угол этой ячейки. Когда курсор примет вид «+», протяните вниз до ячейки С16.

13. Остановите макрос.

14. Нарисуйте кнопку и назначьте ей макрос Ваш ответ.

15. Далее оформляем столбец Результат. Для этого используем логическую функцию «если».

16. Создайте второй макрос – Результат. Для этого, на листе ответов установите курсор в ячейку D7.

17. Выполните команду Вставка – Функция (или кнопка f_x рядом со строкой формул). Выберите в категории Логические функцию Если.

18. Заполните поля согласно Рисунку. Текстовые ответы необходимо заключать в кавычки.

19. Аналогичным образом заполните ячейки D8:D16. Причем верными ответами являются: D8=“равносторонний”; D9=“тупоугольный”; D10=“биссектриса”; D11=“катет”; D12=“медиана”; D13=“подобные”; D14=“высота”; D15=“равнобедренный”; D16=“разносторонний”.

20. Остановите макрос. Нарисуйте кнопку и назначьте ей макрос Результат.

21. Далее оформляем столбец Верный ответ.

22. Создайте третий макрос – назовите его Ответ1. Установите курсор в ячейку Е7. Введите в ячейки E7:E16 верные ответы к вопросам.

23. Остановите макрос.

24. Нарисуйте кнопку Верный ответ и назначьте ей макрос Ответ1.

25. Далее оформляем столбец Оценка. Для этого используем статистическую функцию «счетесли».

26. В строки В18 и В19 введите соответственно записи Количество верных ответов, Количество неверных ответов.

27. Создайте четвертый макрос – назовите его Оценка.

28. Установите курсор в ячейку С18. Выполните команду Вставка – Функция (или кнопка fx рядом со строкой формул). Выберите в категории Статистические функцию Счетесли.

29. Выделите на листе ответов диапазон D7:D16.

30. В строке критерий введите запись «верно» и нажмите кнопку ОК.

31. Аналогичным образом введите количество неверных ответов. Только в строке критерий введите запись «неверно».

32. Для выставления оценки используем функцию «если». Критерии оценивания:

Кол-во верных ответов	Оценка
9-10
7-8
5-6
>4

33. Установите курсор в ячейку С21. Выполните команду Вставка – Функция (или кнопка fx рядом со строкой формул). Выберите в категории Логические функцию Если.

34. После открытия окна Аргументы функции щелкните мышью в ячейку С18. Ее адрес появится в строке Лог_выражение. Далее введите записи согласно Рисунку.

35. Установите курсор в строку Значение_если_ложь и нажмите на кнопку ЕСЛИ (рядом со строкой формул) для создания следующего вложения функции Если.

36. При открытии окна Аргументы функций нужно ввести следующие записи

37. Установите курсор в строку Значение_если_ложь и нажмите на кнопку ЕСЛИ (рядом со строкой формул) для создания следующего вложения функции Если.

38. При открытии окна Аргументы функций нужно ввести следующие записи

39. Остановите макрос.

40. Нарисуйте кнопку и назначьте ей макрос Оценка.

41. Создайте кнопку Очистка для удаления данных в столбцах Ваш ответ, Результат, Верный ответ (по аналогии с кнопкой Очистка на листе Тест)

42. Теперь нам нужно задать переход с листа Тест на лист Результаты. Перейдите на лист Тест.

43. На панели Рисование выберите раздел стрелки и подберите подходящую фигуру.

44. Нарисуйте стрелку.

45. Сделайте на ней надпись Далее, используя .

46. Выделите стрелку.

47. Зайдите в меню Вставка – Гиперссылка. Установите настройки как на рисунке.

48. Перейдите на лист Тест.

49. Встаньте в ячейку Е3. Зайдите Данные – Проверка.

50. Осталось защитить наши листы. Выделите на листе Тест ячейки Е3:Е4 и С7:С16 (используйте для этого клавишу CTRL).

51. Зайдите Формат – Ячейки, на закладке Защита уберите галочку Защищаемая ячейка.

52. Зайдите Сервис – Защита – Защитить лист. Введите любой пароль. Теперь без вашего разрешения изменения будут невозможны.

53. Сохраните файл.

 

Контрольная работа

Каждое задание должно быть выполнено на своем листе.

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

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

- оформите три таблицы: Меню, Заказ, Счет;

- в таблице Меню должен быть перечень блюд и стоимость одной порции;

- в таблице Заказ - номера столов, названия блюд и количество заказанных порций для каждого стола;

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

Все таблицы должны быть красиво оформлены.

Меню			Заказ
Блюдо	Цена			Количество порций
Борщ				Борщ	Бифштекс	Салат	...
Бифштекс	18,5		Стол 1
Салат	5,8		Стол 2
...	...		Стол 3

 

Счет
№ стола	Сумма к оплате	Расчет посетителя	Сдача
Стол 1
Стол 2
Стол 3
...
Всего по счету:

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

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

Задание 2: Облигации приобретены 06.09.05 по курсу 89 и имеют купонный доход в размер 9%, который выплачивается с периодичностью один раз в полугодие. Предполагаемая дата погашения облигации – 10.09.09 по курсу 100. Определить годовую ставку помещения. Базис расчета принять равным 1.

С помощью инструмента Подбор параметра определить значение аргумента при условии годовой ставки помещения, равной 15%.

Задание 3: При помощи инструмента Таблица подстановки проанализировать влияние на годовую ставку помещения курса покупки облигации (79, 84, 89, 94, 99, 104, 109) и купонной ставки (5%. 10%, 15%, 20%, 25%, 30%, 35%, 40%).

Задание 4: Построить выборочные функции распределения (относительные и накопленные частоты) для роста в см. 20 студентов: 101, 109, 170, 108, 101, 109, 102, 101, 170, 108, 104, 107, 109, 101, 170, 101, 101, 180, 102, 106.

 

ЛАБОРАТОРНАЯ РАБОТА №6. Технология работы с базами данных, на примере MS ACCESS. Создание базы данных, состоящей из двух таблиц

Цели работы:

· познакомиться с основными понятиями базы данных;

· научиться создавать таблицу базы данных в режиме Конструктор;

· освоить переход из режима Конструктор в режим Таблицы;

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

· познакомиться с простой сортировкой значений таблицы.

Задание 1. Создайте новую базу данных о студентах «Компьютерной школы».

· Вызовите программу MS Access. Для этого выполните последовательно: Пуск ® Все программы ® Microsoft Access. Перед вами откроется окно системы управления базами данных, в котором справа появится меню (рис.1).

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

· Перед вами появится окно базы данных (рис.2).

Задание 2. Изготовьте структуру таблицы с информацией о студентах «Компьютерной школы».

Вы создаете таблицу, поэтому выберите объект Таблицы.

 

Задание 3. Создайте таблицу Группы.

· Выберите объект Таблица, если находитесь в другом окне.

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

· Переименуйте Поле 1. Для этого поставьте курсор в любую ячейку столбца Поля1. Выполните команду Формат ® Переименовать столбец. Ячейка имени столбца окажется выделенной. Введите название поля Учебная группа и нажмите клавишу [Enter ].

· Аналогично переименуйте Поле 2. Введите название поля Преподаватель. Должно получиться как на Рис.3.

· Сохраните таблицу и именем Группы, щелкнув по кнопке - сохранить. На вопрос о создании ключевого поля ответьте – Нет.

· Перейдите в режим Конструктор, щелкнув по кнопке и посмотрите как заданы поля.

· Исправьте данные: Сделайте поле Учебная группа Ключевым, поместив курсор на имя этого поля и щелкнув по кнопке - Ключевое поле. Тип данных этого поля задайте Числовым.

· Закройте таблицу Группы, на запрос о сохранении ответьте Да.

Задание 4. Создайте таблицу Список.

· Дважды щелкните по меню Создание таблицы путем ввода данных.

· По аналогии с предыдущим заданием переименуйте поля таблицы по следующей схеме:

вместо	Поле 1	название	Код
вместо	Поле 2	название	Фамилия
вместо	Поле 3	название	Имя
вместо	Поле 4	название	Отчество
вместо	Поле 5	название	Год рождения
вместо	Поле 6	название	Школа
вместо	Поле 7	название	Класс
вместо	Поле 8	название	Учебная группа

· Сохраните таблицу с именем Список. На вопрос о создании ключевого поля ответьте отрицательно.

 

· Перейдите в режим Конструктор и посмотрите, как заданы поля. Исправьте тип данных полей в соответствии с Рисунком.

· Обратите внимание, что в поле Учебная группа имеет место подстановка (значения этого поля не надо набирать вручную, они будет подставляться из списка, содержащегося в таблице Группы).

Рис.3.

Подстановку производим по следующей схеме: поставьте курсор на поле Учебная группа; выберите закладку Подстановка: тип элемента управления – Поле со списком; источник строк – Группы (выбирайте из ниспадающего меню), как показано внизу Рисунка.

 

 

· Сохраните изменения.

· Закройте таблицу.

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