Основы компьютерной грамотности. Базовый учебный курс

Подождите немного. Документ загружается.

Модуль 3. Работа с электронными таблицами

270

Дополнительные

вычисления

Рис. 3.95. Диалоговое окно сводной таблицы

с областью

Дополнительные вычисления

Рис. 3.96. Сводная таблица с дополнительными вычислениями

Учебники Цена 1997−1998 1999−2000 Общий итог

Ïî ëèòåðàòóðå 115 0,00% 10,09% 4,65%

130 0,00% 24,66% 11,36%

140 33,91% 0,00% 18,29%

Итог 33,91% 34,75% 34,30%

Ïî ìàòåìàòèêå 80 19,16% 8,07% 14,05%

90 0,00% 19,06% 8,78%

Итог 19,16% 27,13% 22,83%

Ïî ìàòåìàòèêå 90 26,82% 0,00% 14.46%

Итог 26,82% 0,00% 14,46%

Ïî ôèçèêå 150 8,62% 38,12% 22,21%

170 11,49% 0,00% 6,20%

Итог 20,11% 38,12% 28,41%

Îáùèé èòîã 100,00% 100,00% 100,00%

Сумма по полю Количество Год издания

modul_3_2.qxd 1/11/07 1:50 PM Page 270

3.6.6. Вопросы и задания

1. В каких сферах деятельности вы встречались с информационными

системами?

2. Какие существуют информационные системы?

3. Что такое база данных?

4. Что такое реляционная база данных?

5. Какого типа могут быть поля в реляционной базе данных?

6. Что такое запись в реляционной базе данных?

7. Как называется база данных, созданная в MS Excel?

8. Чем отличается выборка данных, сделанная с помощью

Формы,

от выборки, сделанной с помощью

Фильтра?

9. Для чего нужны сводные таблицы?

3.6. Базы данных в электронных таблицах

271

modul_3_2.qxd 1/11/07 1:50 PM Page 271

3.7. Решение задач на формирование простых

баз данных в MS Excel и запросов к ним

Рассмотрим решение некоторых задач, предусматривающих формиро-

вание простых баз данных и запросов к ним.

Задача № 1. Составьте список поступающих в Пермский университет, в

котором будут приведены следующие данные о каждом абитуриенте: фами-

лия, имя, отчество, год рождения, место рождения, номер паспорта, место

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

ловек.

• Найдите записи об иногородних абитуриентах (например, живущих

не в областном центре).

• Найдите записи об абитуриентах 19 лет и старше.

• Составьте новый список, в который войдут абитуриенты, набравшие

не менее 13 баллов.

• Составьте сводную таблицу, в которой будет подсчитано количество

абитуриентов, проживающих в разных населенных пунктах.

• Составьте сводную таблицу, в которой будет вычислен средний балл

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

Решение

Создавая требуемый список, сначала сформируем заголовок (имена по-

лей базы данных), затем через меню Данные → Форма заполним его запися-

ми (рис. 3.97).

Модуль 3. Работа с электронными таблицами

272

Рис. 3.97. Создание списка записей

modul_3_2.qxd 1/11/07 1:50 PM Page 272

Для выявления иногородних абитуриентов зададим Критерий Место жи-

тельства не равно (<>) Пермь (рис. 3.98). С помощью кнопки Далее мож-

но просмотреть все найденные записи.

Выберем абитуриентов 19 лет и старше. Например, для 2005 года это

абитуриенты, рожденные в 1986 году и ранее. (Для простоты изложения мы

не учитываем месяц и день рождения). Сформируем критерий: Год

рождения <=1986 (рис. 3.99).

С помощью кнопки Далее можно просмотреть искомые записи. Для со-

здания списка абитуриентов, набравших менее 13 баллов, используем меню

Данные → Фильтр → Автофильтр. В списке Количество баллов выберем строку

Условие и зададим его больше или равным 13 (рис. 3.100, 3.101).

3.7. Решение задач на формирование простых

баз данных в MS Excel и запросов к ним

273

Рис. 3.98. Выявление иногородних

абитуриентов

Рис. 3.99. Выявление абитуриентов

19 лет и старше

Рис. 3.100. Задание фильтра

Рис. 3.101. Задание условий фильтра

modul_3_2.qxd 1/11/07 1:51 PM Page 273

Получим требуемый список (рис. 3.102).

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

(в меню Данные уберем флажок Автофильтр).

Далее выберем меню Данные → Сводная таблица. На Шаге 3 работы Мас-

тера создадим Макет (рис. 3.103) следующим образом: в поле Строка помес-

тим Место жительства, а в поле Данные → Количество по полю Год Рожде-

ния (для того чтобы заменить функцию Сумма на Количество, следует дваж-

ды щелкнуть по кнопке Год рождения, помещенной в поле Данные, и в спи-

ске операций выбрать Количество).

Получим требуемую сводную таблицу (рис. 3.104).

Сводная таблица со средним баллом для абитуриентов, проживающих в

разных населенных пунктах, строится аналогично (рис. 3.105).

Модуль 3. Работа с электронными таблицами

274

Рис. 3.102. Список абитуриентов, набравших 13 и более баллов

Рис. 3.103. Создание макета сводной таблицы

modul_3_2.qxd 1/11/07 1:51 PM Page 274

3.7.1. Вопросы и задания

1. Составьте список сотрудников школы, в котором будут указаны фа-

милия, имя, год рождения, год поступления на работу, заработная пла-

та каждого из них. Список содержит семь человек. Найдите записи с

фамилиями сотрудников, начинающимися на букву

А. Найдите за-

писи, содержащие сведения о сотрудниках, чья заработная плата не

превышает 2000 рублей. Составьте новый список, в который войдут

сотрудники старше 60 лет со стажем работы в данной школе не более

5 лет. Составьте сводную таблицу, в которой будет просуммирована

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

2. Составьте список успеваемости семи учеников, в котором будут ука-

заны

фамилия, имя, год рождения каждого из них, а также сумма на-

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

учеников начинаются на букву К. Найдите записи, содержащие дан-

ные об учениках старше 16 лет. Составьте список, в который войдут

три лучших ученика.

3. Составьте список для семи спортсменов, в котором будут указаны

фамилия, имя, год рождения каждого, а также количество баллов, на-

бранных в многоборье. Найдите записи, содержащие данные о

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

содержащие данные о спортсменах не старше 16 лет. Составьте спи-

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

4. Придумайте и решите подобную задачу из области своей профессио-

нальной деятельности.

3.7. Решение задач на формирование простых

баз данных в MS Excel и запросов к ним

275

Рис. 3.104. Сводная таблица с итогом

Количество по Году рождения

Рис. 3.105. Сводная таблица

с итогом Среднее по Количеству баллов

modul_3_2.qxd 1/11/07 1:51 PM Page 275

3.8. Статистическая обработка результатов

наблюдений и измерений

3.8.1. Задачи статистической обработки

данных в образовании

Для того чтобы спрогнозировать ситуацию в будущем, необходимо тща-

тельно проанализировать множество фактов за прошедший период. Этим

занимается статистика – экономическая, медицинская и т.д.

Статистика играет большую роль и в сфере образования. Важными по-

казателями здесь являются количество учащихся, обеспеченность педаго-

гическими кадрами, учебной литературой, техническими средствами обу-

чения и т.д.

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

математической статистики, позволяющий выявить зависимость между

различными данными, проверить характер этой зависимости, то есть с не-

которой степенью вероятности подобрать описывающую ее функцию, по-

строить прогноз.

Например, если говорить о сфере образования, то может быть полезно

знать, какие факторы и в какой мере определяют образованность населе-

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

эти показатели определить более формально, то есть заменить на некие ко-

личественные характеристики, например, в отдельных случаях это может

быть доля людей с высшим (средним специальным) образованием в общем

количестве населения или в определенной возрастной группе.

3.8.2. Использование MS Excel для статистической

обработки данных

3.8.2.1. Вычисление коэффициента корреляции

Раздел математической статистики, который исследует зависимости ме-

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

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

чений их величины, а также меру такой зависимости.

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

характере зависимости между этими величинами. Чаще всего допускают

наличие линейной зависимости. В этом случае мера зависимости называет-

ся коэффициентом корреляции. Коэффициент корреляции изменяется в ди-

апазоне от –1 до +1. Если он близок к 1, то имеет место сильная прямая за-

висимость, если к 0, то слабая. Если коэффициент положителен, то возрас-

Модуль 3. Работа с электронными таблицами

276

modul_3_2.qxd 1/11/07 1:52 PM Page 276

тание одной величины приводит к возрастанию другой. Если коэффициент

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

Определить коэффициент корреляции можно с помощью MS Excel. Для

этого в группе статистических функций имеется функция КОРРЕЛ. Рас-

смотрим на примере, как ею воспользоваться.

Исследуем два фактора: обеспеченность компьютерами и успеваемость

учеников. Для этого потребуются статистические данные по ряду образова-

тельных учреждений (рис. 3.106).

Вызовем Мастер функций (меню Вставка → Функция) и в категории

Статистические найдем КОРРЕЛ (рис. 3.107).

3.8. Статистическая обработка результатов наблюдений и измерений

277

10 3,98

25 4,01

19 4,34

78 4,41

45 3,94

32 3,62

90 4,6

21 4,24

34 4,36

45 3,99

67 4,5

Îáåñïå÷åííîñòü êîìïüþòåðàìè Óñïåâàåìîñòü

Рис. 3.106. Статистические данные

Рис. 3.107. Выбор функции КОРРЕЛ

modul_3_2.qxd 1/11/07 1:52 PM Page 277

Укажем два диапазона значений (рис. 3.108), характеризующих два фак-

тора, – обеспеченность компьютерами (Массив1 – А2:А12) и успеваемость

(Массив2 – В2:В12).

Получим коэффициент корреляции, равный 0,572465336 (рис. 3.109).

Следовательно, существует прямая зависимость успеваемости от наличия

компьютеров, но не очень сильная.

Можно определять зависимость таких величин, как уровень образова-

ния (измеренный, например, в годах обучения) и уровень дохода населе-

ния; количество часов, затрачиваемых на выполнение домашних заданий,

и средняя оценка; уровень образования и занимаемая должность (здесь

Модуль 3. Работа с электронными таблицами

278

Рис. 3.108. Определение диапазонов исследуемых факторов

10 3,98

25 4,01

19 4,34

78 4,41

45 3,94

32 3,62

90 4,6

21 4,24

34 4,36

45 3,99

67 4,5

Îáåñïå÷åííîñòü êîìïüþòåðàìè Óñïåâàåìîñòü

Рис. 3.109. Коэффициент корреляции успеваемости

и обеспеченности компьютерами

Êîýôôèöèåíò

êîððåëÿöèè 0,572465336

modul_3_2.qxd 1/11/07 1:53 PM Page 278

должна быть определена некая условная шкала) и т.д. Во всех этих случаях

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

ческих данных.

3.8.2.2. Нахождение параметров приближения по методу

наименьших квадратов

Допустим, мы выяснили, что два фактора взаимозависимы. Следующий

шаг в исследованиях – попытка выяснить характер этой зависимости. Дру-

гими словами, нужно подобрать функцию зависимости одного фактора от

другого. Вид такой функции неизвестен, и ее следует искать методом под-

бора по статистическим данным.

Если мы имеем два массива статистических данных, характеризующих

поведение двух факторов, то с помощью MS Excel можем представить их

зависимость на диаграмме.

Посмотрим, как это будет выглядеть для примера из п. 3.8.2.1. Нужно

выделить ряды данных, вызвать Мастер диаграмм и задать Вид диаграммы

(точечная диаграмма). В результате получим диаграмму (рис. 3.110).

По расположению точек можно попробовать догадаться, какая зависи-

мость имеет место в данном примере. Полученную таким образом функ-

цию называют регрессионной моделью.

Итак, как же подобрать регрессионную модель? Видимо, это можно осу-

ществить в два этапа:

1) подобрать вид функции;

2) вычислить параметры функции.

Первый этап не имеет строгого решения. Здесь лучшие советчики –

опыт и интуиция исследователя. Чаще всего выбор происходит между ли-

нейной, квадратичной, логарифмической, экспоненциальной или степен-

ной функциями.

3.8. Статистическая обработка результатов наблюдений и измерений

279

Рис. 3.110. Зависимость успеваемости от наличия компьютеров

modul_3_2.qxd 1/11/07 1:53 PM Page 279