Материал: Задача 1

Задача 1. Анализ данных криминологических исследований

1. Создать таблицу, содержащую статистические данные о количестве зарегистрированных преступлений за период с 2005 г. По 2009 г. (таблица 1.1). Верхняя левая клетка таблицы – а1.

Таблица 1.1

Зарегистрированные преступления за период с 2005 г. По 2009 г.

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

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

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

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

   • оформить данные в ячейках шрифтом Times New Roman, размер шрифта для ячеек таблицы задайте 10 пт;

   • провести выравнивание данных в ячейках;

Указание: форматирование выделенных ячеек таблицы осуществляется в режиме контекстного меню с помощью вкладки Формат ячеек (на вкладке Главная щелкнуть Формат > Формат ячеек.

• переименовать рабочий Лист1 книги, дав ему название «База_преступлений <фамилия студента>»;

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

3. Провести вычисления в таблице.

• Рассчитать итоговый показатель за 2005 г. с использованием кнопки Автосумма .

Рис. 3.1 (значения данных отличны от исходных)

• Скопировать построенную формулу во все оставшиеся ячейки строки «Итого».

• Рассчитать показатель структуры преступности, поместив их в столбец «в % от общего числа».

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

Рис. 3.2 (значения данных отличны от исходных)

Данный показатель выражается в процентах, поэтому для всех ячеек столбцов «в % от общего числа» необходимо установить процентный формат (Формат ячеек → Число → процентный)

Рис. 3.3

• Добавить в таблицу последним по счету столбец «Средний показатель за 2005-2009 гг.». Для каждого вида преступлений рассчитать данный показатель как среднее арифметическое «Абсолютных показателей» за все годы.

Указание: вызвать в строке формул Мастер функций , выбрать функцию СРЗНАЧ (Категория полный алфавитный перечень) с помощью мыши и клавиши CTRL последовательно выделить 5 ячеек.

Рис. 3.4 (значения данных отличны от исходных)

• Для каждого года добавить столбец «Абсолютное отклонение от среднего».

Рис. 3.5 (значения данных отличны от исходных)

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

Указание: вызвать Мастер функций , выбрать функцию ABS. При задании формулы обратите внимание на то, что ссылка на ячейку, содержащую средний показатель за 2005-2009 гг. должна быть абсолютной по столбцу для того, чтобы данную формулу можно было скопировать в остальные ячейки.

Рис. 3.6 (значения данных отличны от исходных)

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

Указание: Выберите данные для диаграммы.

На вкладке Вставка нажмите кнопку Рекомендуемые диаграммы.

На вкладке Рекомендуемые диаграммы выберите диаграмму для предварительного просмотра (вставить круговые или кольцевые диаграммы), объёмная круговая.

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

Рис. 4.1

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

Таблица 5.1

Преступления против личности

• Переименовать Лист2 книги (при необходимости создать Лист2), дав ему название «преступления против личности»;

• создать шапку таблицы в одну строку, провести форматирование ячеек таблицы (выравнивание, заливка), формат ячеек шапки должен быть общим или текстовым;

• скопировать из таблицы листа «база преступлений» названия видов преступлений против личности;

• данные о количестве преступлений задать в виде ссылок на соответствующие ячейки таблицы листа «база преступлений».

Указание: задание ссылок на ячейки выполнить следующим образом:

- в ячейке листа «преступления против личности», куда необходимо поместить «Абсолютный показатель» данного вида преступлений, поставить знак «=»;

- переключиться на лист «база преступлений» и щелкнуть кнопкой мыши по ячейке, содержащей соответствующей виду преступления;

- нажать кнопку в строке формул или клавишу Enter;

- в таблице на листе «преступления против личности» появится ссылка на лист «база преступлений».

Рис. 5.1

- заполнить остальные ячейки таблицы.

• по данным таблицы листа «преступления против личности» построить график, разместив его на этом же листе (Тип диаграммы – точечная с гладкими кривыми и маркерами. Наименования осей и название диаграммы ввести вручную.

Рис. 5.2

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

• вывести только итоговые данные.

Указание: на вкладке Главная нажать на пиктограмму Сортировка и фильтр вызвать команду Фильтр. Щелкнуть стрелку и из списка Фильтра столбца «Виды преступлений», выбрать параметр Итого:;

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

Указание: на вкладке Главная нажать на пиктограмму Сортировка и фильтр вызвать команду Фильтр. Щелкнуть стрелку и из списка Фильтра столбца «Средний показатель за 2005-2009 гг.», выбрать параметр Числовые фильтры→Настраиваемый фильтр. В появившемся диалоговом окне ввести необходимое условие:

Рис. 6.1

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

• количество преступлений в 2006 г. не попадало в интервал от 10 000 до 40 000;

Указание: сама таблица - исходный диапазон для отбора данных. Создать дополнительно диапазон условий, во главе которого должны быть заголовки столбцов таблицы (имена полей), ввести условия отбора, помещая их в разные строки (условие ИЛИ), как показано на рисунке. Между диапазонами должно быть не менее одной строки.

Рис. 7.1

Вызвать на вкладке Данные→Сортировка и фильтр команду Дополнительно. Установить переключатель Обработка в положение скопировать результаты в другое место. Заполнить с помощью вкладки и кнопки мыши поля адреса исходного диапазона и диапазон условий, включая имена полей. И указать начальный адрес диапазона, куда следует вывести результат. Нажать ОК.

Рис. 7.2

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

Рис. 7.3

• количество преступлений «Изнасилования и покушения на изнасилования» в 2006 г. не превысило 40 000 (второе условие не выполнено), но количество преступлений «Изнасилования и покушения на изнасилования» не превысило 10 000 (первое условие выполнено), поэтому строка «Изнасилования и покушения на изнасилования» выведена, как результат работы фильтра.

• количество преступлений «Умышленное причинение тяжкого вреда здоровью» в 2006 г. превысило 10 000 (первое условие не выполнено), но количество преступлений «Умышленное причинение тяжкого вреда здоровью» превысило 40 000 (второе условие выполнено), поэтому строка «Умышленное причинение тяжкого вреда здоровью» выведена, как результат работы фильтра.

• Количество преступлений «Убийства и покушение на убийство» в 2006 г. превысило 10 000 (первое условие не выполнено) и количество преступлений «Убийства и покушение на убийство» не превысило 40 000 (второе условие не выполнено). Так как ни одно из условий не выполнено, строка «Умышленное причинение тяжкого вреда здоровью» не выведена, как результат работы фильтра.