Информатика

Рис. 16. Исходный список

Задание 2. Сортировка списка

1.Скопируйте список на два других рабочих листа этой же книги.

2.Переименуйте Лист 2 в Сортировка по фамилиям. Для сортировки

–упорядочения данных по алфавиту или в порядке возрастания (убывания) числовых значений – щелкните мышью по любой ячейке списка, выберите меню Данные – Сортировка, проверьте установку переключателя в группе

Идентифицировать столбцы по (подписям). В поле Сортировать по

выберите в раскрывающемся списке Группа, в поле Затем по – Фамилия, В последнюю очередь по – Имя. Все три соответствующие переключателя установите в положение по возрастанию. После щелчка ОК будет проведена сортировка списка по возрастанию номеров групп, при совпадении номеров групп – по алфавиту фамилий, при совпадении фамилий – по именам.

3.Переименуйте Лист 3 с другой копией списка в Сортировка по баллу. Опробуйте здесь различные варианты сортировки: по датам, по одному и нескольким экзаменам. Последнюю сортировку выполните по баллам и группам (по убыванию баллов).

21

PDF создан испытательной версией pdfFactory Pro www.pdffactory.com

Задание 3. Поиск с помощью формы

Поиск нужных данных из списка может быть произведен с помощью формы Данные – Форма – Критерии. В соответствующие поля вводятся условия поиска (критерии): для поиска по точному соответствию вводится нужная запись, при поиске по близкому соответствию используются подстановочные символы * (любая последовательность символов) или ? (один неизвестный символ), при поиске по числовому сравнению используются операторы отношения <, <=, > и т.п. Если указаны критерии поиска для нескольких полей, то будут искаться записи, удовлетворяющие всем условиям одновременно.

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

– с именами, Юлия, Светлана;

– с фамилиями, начинающимися на А, К;

– родившихся до 1987 года (введите в поле Дата <1/1/87 – без пробелов!);

– со средним баллом, большим 4.

2.Сколько записей удовлетворяют каждому из условий?

3.Опробуйте поиск по нескольким критериям одновременно, например, фамилия заканчивается на ов (*ов), при этом средний балл больше

3,5.

Задание 4. Автофильтрация данных

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

1.Для автофильтрации щелкните мышью по одной из ячеек списка и воспользуйтесь меню Данные – Фильтр – Автофильтр.

2.Выберите в раскрывающемся списке поля Группа значение, соответствующее Вашей группе: все остальные данные будут скрыты. На рисунке 17 пример выбора группы 264.

22

PDF создан испытательной версией pdfFactory Pro www.pdffactory.com

Рис. 17. Автофильтр. Выбор студентов группы 264

3.Для восстановления всех данных в списке поля Группа выберите Все

или меню Данные – Фильтр – Отобразить все.

4.Можно фильтровать список несколько раз. Снова выберите в значение, соответствующее Вашей группе, в списке поля Математика выберите 5 – будут выведены данные о студентах Вашей группы, получившие отличные оценки за первый экзамен (см. рис. 18).

Рис. 18. Результат применения фильтра

5.Восстановите список.

6.Для задания более сложных условий отбора используется пункт Условие (см. рис. 19). В раскрывающемся списке поля Балл выберите Условие; для вывода данных о студентах со средним баллом от 3,5 до 5 в диалоговом окне введите в верхней строке слева – больше (выбирается из раскрывающегося списка), в правой – 3,5, проверьте установку переключателя И/ИЛИ на И, в нижней строке слева выберите из списка

меньше или равно, справа – 5, щелкните ОК.

23

PDF создан испытательной версией pdfFactory Pro www.pdffactory.com

Рис. 19. Работа с пунктом «Условие» пользовательского автофильтра

7.Восстановите список.

8.Самостоятельно:

–Выберите в Вашей группе фамилии, начинающиеся на К и оканчивающиеся на ов, родившихся в промежутке, например, от 1/01/86 до 1/07/87 (вначале отбор по группе, затем – по фамилиям: равно К*ов, в последнюю очередь – по датам).

–Выберите фамилии студентов, в которые входит буква а, среди них – со средним баллом от 4 до 5.

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

9. Для отмены режима автофильтрации вновь выберите Данные – Фильтр – Автофильтр.

Задание 5. Расширенная фильтрация

Для работы с расширенным фильтром вначале просмотрите справку по этому вопросу.

Найдите студентов группы 264 сдавших экзамен по Математике на «5».

1.Скопируйте строку имен полей в область рабочего листа ниже списка на строку 25.

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

– в столбце «Группа» (ячейка В26) – 264;

– в столбце «Математика» (ячейка F26) – 5.

3.Меню Данные – Фильтр – Расширенный фильтр (см. рис. 20):

24

PDF создан испытательной версией pdfFactory Pro www.pdffactory.com

–В диалоговом окне укажите место обработки – Скопировать результат в другое место.

–Исходный диапазон – выделите таблицу с заголовками столбцов

B2:I22.

–Диапазон условий – укажите диапазон критериев находящийся внизу таблицы B25:I26.

–Укажите месторасположения результата B28:I29.

–ОК.

4. Последние два задания, выполненные с помощью автофильтра, выполните с использованием расширенного фильтра. Сравните результат.

Рис. 20. Диалоговое окно «Расширенный фильтр»

Лабораторная работа 7. Ведение списка «Договоры»

Цель работы – применение навыков создания и работы со списками, сортировки данных, фильтрации.

1.Создайте список учета договоров и внесите в него сведения о 17 договорах (см. табл. 2).

2.Установите форматы:

–денежный (в рублях) для столбца «сумма»;

–процентный для поля «процент штрафа».

3. Создайте под списком 4 строки с показателями: Итого, Средняя сумма, Максимум и Минимум. Вычислите значения.

25

PDF создан испытательной версией pdfFactory Pro www.pdffactory.com

Таблица 2

Ведение списка договоры

4. Вычислите значения столбца «срыв» по формуле:

«фактическая дата» – «срок исполнения»

Для столбца «срыв» задайте числовой формат.

5.Вычислите значения столбца «штраф» по формуле: сумма*процент штрафа*срыв для договоров, выполненных позже срока исполнения (Срыв > 0).

6.Вычислите значения столбца «итог» по формуле:

«сумма» – «штраф»

7.Проведите сортировку данных в списке:

– по регионам;

– по регионам и по заказчику.

8.Отберите данные по региону «Юг».

9.Отберите договоры, оканчивающиеся в марте 2003 года.

26

PDF создан испытательной версией pdfFactory Pro www.pdffactory.com

10.Отберите договоры, не выполненные в срок исполнителем ООО

«Лира».

11.Отберите договоры по региону «Север», сумма которых превышает

50 000 рублей.

12.Вычислите общую сумму договоров по каждому региону.

13.Скройте детальные данные, оставив только итоги, а затем снова отобразите детальные данные.

14.Вычислите средний срыв по каждому исполнителю.

Лабораторная работа 8. Анализ данных криминологических исследований

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

1.Создайте таблицу (см. табл. 3), содержащую статистические данные

околичестве зарегистрированных преступлений за период с 2001 г. по 2005

г.

2.Переименуйте лист в база преступлений.

3.Проведите форматирование ячеек таблицы, согласно следующим установкам:

– установите режим переноса по словам в шапке таблицы и столбце преступлений;

27

PDF создан испытательной версией pdfFactory Pro www.pdffactory.com

–объедините ячейки в шапке таблицы;

–установите запивку ячеек шапки таблицы серым цветом;

–оформите данные в ячейках шрифтом Times New Roman, размер шрифта ячеек таблицы задайте 12 пт.

–выровняйте данные в ячейках.

4.Рассчитайте итоговые показатели по годам.

5.Рассчитайте показатель структуры преступности для каждого года, поместив их в столбец «в % от общего числа». Формат ячеек в этих столбцах Процентный.

6.Добавьте в таблицу последним по счету столбец «Средний показатель за 2001-2005 гг.». Для каждого вида преступлений рассчитайте

данный показатель как среднее арифметическое абсолютных показателей за все годы.

7.Для каждого года добавьте столбец «Абсолютное отклонение от среднего». Рассчитайте абсолютное отклонение годового уровня определенного вида преступлений от среднего. Расчет производите следующим образом: если годовой уровень преступлений меньше среднего за 2001-2005 гг., отклонение определяется как разность среднего и годового уровней, в противном случае – как разность годового уровня и среднего за период.

8.Постройте круговую диаграмму на отдельном листе, иллюстрирующую структуру преступности в 2001 г. Диаграмму разместите на отдельном листе.

9.Для преступлений против личности (убийств, умышленного причинения вреда здоровью и изнасилований) постройте на отдельном листе книги «преступления против личности» таблицу и соответствующий ей график, характеризующий изменение количества преступлений данных видов за 2001-2005 гг.

10.Для исходной таблицы, расположенной на листе «база преступлений», с помощью автофильтра:

– выведите только итоговые данные;

– выведите данные тех видов преступлений, средний показатель которых за период 2001-2005 гг. находился в интервале от 10 000 до 100 000.

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

– количество преступлений за 2002 г. не попадало в интервал от 10 000

до 40 000;

– количество преступлений против личности в 2001 г. не превысило

50 000, а в 2002 г. превысило 10 000;

28

PDF создан испытательной версией pdfFactory Pro www.pdffactory.com

– либо количество преступлений против личности в 2001 г. превысило 50 000, либо количество преступлений против личности в 2002 г. не превысило 10 000.

12. Для исходной таблицы, расположенной на листе «база преступлений», с помощью сортировки упорядочите виды преступлений по возрастанию среднего показателя за 2001-2005 гг.

Лабораторная работа 9. Статистика. Интервалы, частоты, кривые, гистограммы. Вычисление средних, дисперсии, моды и медианы

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

Цель работы – получение навыков обработки статистических данным средствами Excel.

Задание 1. Нахождение интервалов, частот для налоговых неуплат

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

1. Сумму налоговых неуплат запишите ячейки A2:D10. Данные приведены в таблице 4:

Таблица 4

Исходные данные

2.Количество зарегистрированных неуплат (N), запишите в ячейку А12. Для определения используйте функцию =СЧЕТ(A2:D10).

3.Вычислите в ячейке D12 хmax.

4.Вычислите в ячейке D13 хmin.

29

PDF создан испытательной версией pdfFactory Pro www.pdffactory.com

5. В ячейку D14 запишите формулу, определяющую величину интервала:

i = xmax − xmin , 1+ 3,32LgN

В итоге получите i=12,49.

6.Запишите массив интервалов в ячейки F2:F9 с шагом i=12:

– В ячейке F2 запишите минимальное значение неуплаты.

– В ячейку F3 введите =F2+12.

– Протяните формулу до ячейки F9 включительно.

7.Далее определите частоту значений в каждом интервале (рис. 21):

– Выделите ячейки H2:H9 для записи выделенных частот.

– В ячейку H2 запишите формулу определения частот: Функция –

категория Статистические – Частота.

– В окне диалога укажите массив данных и двоичный массив. Однако

не следует нажимать ОК, необходимо нажать комбинацию клавиш: Ctrl+Shift+Enter.

Рис. 21. Ввод аргументов функции ЧАСТОТА

– В столбце Н отобразится результат.

30

PDF создан испытательной версией pdfFactory Pro www.pdffactory.com