Практикум. 
Математика и информатика

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

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

го сэ О).

Таблица 5.8

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

Виды преступлений.
	абсолютный показатель.	в % от общего числа.	абсолютный показатель.	в % от общего числа.	абсолютный показатель.	в % от общего числа.	абсолютный показатель.	в % от общего числа.	абсолютный показатель.	в % от общего числа.
Убийства и покушения на убийства.			131 140.		32 618.		34 999.		32 285.
Умышленное причинение тяжкого вреда здоровью.	45 170.		47 669.		47 454.		53 576.		58 469.
Изнасилования и покушения на изнасилования.
Хулиганство.	131 082.		128 701.		132 858.		133 575.		133 187.
Разбой.	38 513.				38 550.		44 525.		47 052.
Кража.	1 143 364.		1 413 810.		1 297 696.		1 270 444.		926 815.
Г рабеж.	122 366.		138 973.				142 471.		167 267.
Присвоение или растрата.	44 399.		48 516.				41 557.		48 983.
Взяточничество.					74 208.		62 335.
Преступления, связанные с незаконным оборотом наркотиков.	190 127.		216 364.		243 617.		241 589.		189 576.
Иные преступления.	822 550.		920 268.		908 802.		933 876.		907 243.
Итого:

Раздел 5. Использование электронных таблиц…

• 2. Провести форматирование ячеек таблицы согласно следующим установкам:
  • • установить режим переноса по словам в шапке таблицы и столбце видов преступлений;
  • • объединишь ячейки в шапке таблице;
  • • установить заливку ячеек шапки таблицы серым цветом;
  • • оформить данные в ячейках шрифтом Times New Roman, размер шрифта для ячеек таблицы задайте 10 пт;
  • • провести выравнивание данных в ячейках.

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

• переименовать рабочий Лист1 книги, дав ему название «База преступлений «.

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

• 3. Провести вычисления в таблице.
• • Рассчитать итоговый показатель за 2005 г. (рис. 5.58) с использованием кнопки Автосумма Ц[].
• • Скопировать построенную формулу во все оставшиеся ячейки строки «Итого».
• • Рассчитать показатель структуры преступности, поместив их в столбец «в % от общего числа» (рис. 5.59).

Рис. 5.58. Итоговый показатель за 2005 г.

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

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

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

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

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

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

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

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

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

Указание. Вызвать Мастер диаграмм (кнопка Ш панели инструментов).

• 5. Для преступлений против личности (убийства, умышленное причинение вреда здоровью и изнасилования) построить на отдельном листе книги таблицу и соответствующий ей график, характеризующий изменение количества преступлений данных видов за 2005—2009 гг. (табл. 5.9):
  • • переименовать Лист2 книги, дав ему название «Преступления против личности"]
  • • создать шапку таблицы в одну строку, провести форматирование ячеек таблицы (выравнивание, заливка), формат ячеек шапки должен быть общим или текстовым;

Таблица 5.9

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

Виды преступлений.
Убийства и покушения на убийства.	34 999.	32 285.	31 630.	31 553.	30 849.
Умышленное причинение тяжкого вреда здоровью.	53 576.	58 469.	57 087.	57 352.	57 863.
Изнасилования и покушения на изнасилования.

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

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

• в ячейке листа «Преступления против личности»,.

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

• • переключиться на лист «База преступлений» и щелкнуть кнопкой мыши по ячейке, содержащей соответствующий вид преступления;
• • нажать кнопку /, в строке формул или клавишу Enter;
• • в таблице на листе «Преступления против личности» появиться ссылка на лист «База преступлений» (рис. 5.65);
• • заполнить остальные ячейки таблицы;
• • по данным таблицы листа «Преступления против личности» построить график, поместив его на этом же листе (рис. 5.66)
• 6. Для таблицы, расположенной на листе «База преступлений», с помощью автофильтра:
  • • вывести только итоговые данные.

Рис. 5.66. График «Преступления против личности»

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

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

Указание. Па вкладке Главного меню Данные —³? Сортировка и фильтр вызвать команду Фильтр. Щелкнуть стрелку? и из списка Фильтра столбца «Средний показатель за 2005—2009 гг.» выбрать параметр Числовые фильтры —? Настраиваемый фильтр. В появившемся диалоговом окне ввести необходимое условие (рис. 5.67).

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

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

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

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

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

• количество преступлений против личности в 2005 г. не превысило 50 000, а в 2006 г. превысило 10 000;

• • количество преступлений против личности в 2005 г. превысило 50 000 или количество преступлений в 2007 г. не превысило 10 000.
• 8. Для таблицы, расположенной на листе «База преступлений», с помощью сортировки упорядочить виды преступлений по возрастанию среднего показателя за 2005—2009 гг.

Указание. Выделить диапазон ячеек таблицы без шапки и Итоговых данных. На вкладке Главного меню Данные —? Сортировка и фильтр вызвать команду Сортировка, в появившемся окне Сортировка (рис. 5.71) выставить для сортировки столбец «Средний показатель за 2005—2009 гг.» (Столбец Q), уточнить: сортировать — по значениям, порядок сортировки — по возрастанию.

Задача 2. Индекс сезонной преступности Абсолютному большинству юридически значимых явлений свойственны сезонные колебания. Изучать сезонность юридически значимых явлений необходимо в интересах адекватной организации управленческой деятельности. Наиболее простой метод выявления и измерения сезонных колебаний — это расчет среднего уровня (среднего арифметического) изучаемых явлений по месяцам за год и сопоставления месячных данных со среднем уровнем. Это отношение уровней, выраженное в процентах, именуется индексом сезонности. Он рассчитывается по формуле где ИС — индекс сезонности; Ум — уровень по месяцам; Уср — средней уровень за год.

• 1. Создать таблицу, содержащую данные помесячной динамики преступности в России (табл. 5.10).
• 2. Рассчитать средней уровень преступности за год (Уср).
• 3. Определить индекс сезонности (ИС) для каждого месяца по предложенной выше формуле.

Таблица 5.10

Помесячная динамика преступности

Месяц.	Уровень преступности (Ум).	Индекс сезонности (ИС).
Январь.	202 013.
Февраль.
Март.	236 772.
Апрель.	217 325.
Май.	216 308.
Июнь.	224 342.
Июль.	210 395.
Август.	214 087.
Сентябрь.	227 999.
Октябрь.	232 266.
Ноябрь.	206 925.
Декабрь.	204 732.
Уср

4. Вывести только те записи, уровень преступности в которых превышает средний уровень преступности за год.

Указание. Использовать автофильтр. Результаты скопировать на другой лист, присвоив ему имя «Автофильтр».

5. Построить гистограмму, отражающую динамику индекса сезонности по месяцам, расположив ее на отдельном листе с именем «Динамика ИС».

Задача 3. Изучение возрастной преступной активности

В табл. 5.11 приведены данные, отражающие связь между возрастом и изменением процента лиц, совершивших преступление. По данным мировой, российской и региональной статистики наблюдается практически одна и та же тенденция распределения правонарушителей по возрасту: с начала возраста уголовной ответственности идет рост преступной активности, в 25—30 лет (с некоторыми колебаниями) ее уровень достигает максимума, а затем наступает постепенное снижение. В этом проявляется определенная закономерность. Обстоятельства, определяющие тип закономерности, изучаются на основе качественного (криминологического, уголовно-правового, гражданско-правового и т. д.) анализа сути явления. А выявление самой закономерности является задачей статистической обработки социально-правовых данных.

Таблица 5.11

Сведения о возрастной преступной активности

№.	Возраст лиц, лет.	Процент преступлений (/)).	Средний возраст в группе (Xj)
	14−15.		14,5.
	16−20.	И.	18,0.
	21−25.		23,0.
	26−30.		28,0.
	31−35.		33,0.
	36−40.		38,0.
	41−45.		43,0.
	46−50.		48,0.
	51−60.		55,5.
Среднее взвешенное (д;).
Дисперсия (а2).

Основными показателями анализа статистических данных являются математическое ожидание и дисперсия.

Математическое ожидание характеризует среднее взвешенное значение данных, вычисляемое по формуле.

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

1. Рассчитать основные статистические параметры: среднее взвешенное и дисперсию.

Указание. Можно воспользоваться встроенной функцией СУММПРОИЗВ

2. Построить график распределения процентов преступлений, но возрастным группам, поместив его на лист «Распределение».

Задача 4. Ведение списка «Договоры»

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

Примером такой задачи может служить задача учета и контроля выполнения договоров. Каждый договор описывается системой признаков: например, шифр, регион, исполнитель, срок исполнения, фактическая дата выполнения, сумма, процент штрафа и т. д. (табл. 5.12). Список может пополняться, могут изменяться и вычисляться значения признаков при наступлении некоторых событий, например срыв после выполнения договора.

1. Создать список учета договоров.

Указание. Открыть файл «Список» (D:Student Document) и сохранить его в личной папке с тем же именем.

• 2. Установить форматы:
  • • денежный (в руб.) для поля (столбца) Сумма;

со.

Шифр	Регион.	Исполнитель.	Срок исполнения.	Фактическая дата.	Сумма.	Срыв.	Процент штрафа.	Штраф.	Итог.
	Запад.	РУССТЕК.	01.01.2009.	03.01.2009.	10 000.		0,10.
	Восток.	РУНА ЛТД.	02.01.2009.	04.01.2009.	12 000.		0,20.
	Север	ЗАО МВ.	03.01.2009.	03.01.2009.	13 000.		0,15.
	Юг.	ООО ЛИРА.	04.03.2009.	04.03.2009.			0,01.
	Север	ЗАО МВ.	05.01.2009.	05.01.2009.	25 000.		0,03.
	Запад.	РУССТЕК.	05.02.2009.	06.02.2009.	12 000.		0,05.
	Юг.	ЗАО МВ.	06.01.2009.	07.01.2009.	13 000.		0,01.
	Восток.	РУНА ЛТД.	06.02.2009.	08.02.2009.	13 000.		0,01.
	Запад.	РУНА ЛТД.	07.01.2009.	09.01.2009.			0,02.
	Север	ЗАО МВ.	07.03.2009.	10.03.2009.			0,03.
	Юг.	ЗАО МВ.	08.01.2009.	11.01.2009.	85 000.		0,02.
	Юг.	ООО ЛИРА.	08.01.2009.	12.01.2009.	25 000.		0,01.
	Север	ЗАО МВ.	15.04.2009.	18.04.2009.	500 000.		0,03.
	Восток.	ООО ЛИРА.	07.01.2009.	10.01.2009.	250 000.		0,01.
	Запад.	ООО ЛИРА.	08.03.2009.	09.03.2009.			0,02.
	Север	РУССТЕК.	16.04.2009.	16.04.2009.	200 000.		0,01.
	Юг.	ООО ЛИРА.	09.01.2009.	21.01.2009.			0,02.
Итого.
Средняя сумма.
Максимум.
Минимум.

Раздел 5. Использование электронных таблиц…

• • числовой для поля Срыв;
• • процентный для поля Процент штрафа.
• 3. Вычислить показатели — Итого, Среднюю сумму, Максимум и Минимум по полю Сумма, вставив соответствующие формулы.
• 4. Вычислить срыв по формуле: Фактическая дата — Срок исполнения.

Указание. Для колонки Срыв задать числовой формат.

• 5. Вычислить штраф по формуле: Сумма*Срыв*Процент штрафа.
• 6. Вычислить итог по формуле Сумма — Штраф.
• 7. Переименовать Лист1 книги, дав ему название «Исходная база». Для выполнения пунктов 8—16 создать копии этого листа, дав им имена 8.1, 8.2,9, 10, 11, 12, 15.
• 8. Провести сортировку данных в списке:
• 1) по Регионам-,
• 2) по Регионам и Исполнителю.

Указание. Использовать команду Данные —* Сортировка.

9. Отобрать договоры, но региону «Юг».

Указание. Использовать Автофильтр.

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

Указание. Использовать Автофильтр.

11. Отобрать договоры, не выполненные в срок исполнителем «ООО ЛИРА».

Указание. Использовать Расширенный фильтр.

12. Отобрать договоры по региону «Север», сумма которых превышает 50 000 руб. или по региону «Юг», сумма которых не превышает 50 000 руб.

Указание. Использовать Расширенный фильтр.

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

Указание. Использовать команду Данные —*? Промежуточные Итоги, предварительно упорядочив список по региону.

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

Указание. Использовать кнопки работы с группами слева от таблицы.

• 15. Вычислить сумму и средний срыв по каждому исполнителю.
• 16. Скрыть детальные данные по 2-му уровню. Построить по данным полученной таблицы гистограмму с двумя осями

У: основной — для ряда Сумма и вспомогательной — для ряда Срыв, расположив ее на отдельном листе для диаграмм с названием «Анализ».

17. Сохранить таблицу в личной папке с именем Список.