Данные дня выполнения задания

Статистические признаки	Тип поля данных	Перечень значений статистических признаков
N п/п	Целое число	Порядковый номер регистрации занести автозаполнением с 1 до 40
Поступило	Дата	Любые 40 дат в пределах заданного диапазона в хронологическом порядке
Ф.И.О. (наименование юридического лица), привлеченного к административной ответственности	Текст	Любые Ф.И.О. и (или) названия организаций
Статус лица	Список	Физическое лицо, должностное лицо, юридическое лицо
Статья КоАП РФ	Список	5 любых статей из КоАП РФ, подведомственные суду
Судья	Список	5 любых фамилий судей районного суда
Рассмотрено	Дата	С учетом даты поступления
Результат рассмотрения	Список	Назначено административное наказание, прекращено, передано по подведомственности, возвращено
Вид основного наказания	Список	Виды основного наказания по КоАП РФ (не менее 5)
Срок ареста	целое число
Сумма штрафа (наложено), руб.	"Денежный" в рублях
Вид дополнительного наказания	Список	Виды дополнительных наказаний по КоАП РФ
Исполнено	Дата	С учетом даты рассмотрения
Взыскано, руб.	"Денежный" в рублях
Справочник		Перечень значений статистических признаков, тип поля данных у которых - "список"

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

3. Открыть новый файл в приложении MS Excel и сохранить файл, указав в имени файла свою фамилию. Ввести на листе название документа первичного учета "Журнал учета дел об административных правонарушениях за 2009 год", имя "Лист 1" переименовать в БД-журнал.

4. Ввести последовательно название статистических признаков из таблицы 1 как названия столбцов. Они должны быть введены последовательно в ячейки без пропуска столбцов.

5. Формируем последний столбец журнала - Справочник. В ячейки столбца (сверху вниз) последовательно вносим наименования статистических признаков, тип поля данных у которых - "список", и под ними перечень возможных значений этих признаков, взятые из табл. 1. Для статистического признака "Статья КоАП РФ" определить составы правонарушений, подведомственных суду и подсудные районным судам, задав соответствующие условия поиска по кодексу в доступной справочно-правовой системе. Отобрать пять составов, внести их в "Справочник" (только номера статей). Определить для каждой указанной статьи КоАП РФ допустимые виды наказаний, размеры штрафов или сроков административного ареста. Виды административных наказаний для статистических признаков "Вид основного наказания", "Вид дополнительного наказания" указываем по КоАП РФ, воспользовавшись текстом кодекса в любой из установленных справочно-правовых систем (Гарант, Кодекс). Список административных наказаний должен содержать последовательно наказания, являющиеся основными и дополнительными.

В результате столбец "Справочник" будет иметь примерно такой вид (рис. 91):

Справочник

Статус лица

Физическое лицо

Должностное лицо

Юридическое лицо

Статья КоАП РФ

Ст. 7.1

...

Ст. 12.26

Судья

Иванов

Петров

...

и т.д.

Рис. 91. Вид столбца "Справочник" "Журнала учета дел об административных правонарушениях за 2009 год"

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

Для создания логического условия используется функция ЕСЛИ. Выделяем первую пустую ячейку столбца Вид основного наказания, где в строке должны вводиться значения. На панели инструментов нажимаем кнопку , далее в категории выбрать логические и в них выбираем функцию ЕСЛИ из списка функций. Появляется окно ввода условий. В строку окна ввода Лог_выражение ввести логическое условие в виде: {адрес первой ячейки столбца "Результат рассмотрения"} = "Назначено административное наказание"; в строке окна ввода если истина - записать "выберите из списка", а в строке окна ввода если ложь указать "нет", ОК.

Пример (рис. 92).

Тогда, если в ячейке столбца "Результат рассмотрения" значение статистического признака будет определено как "Назначено административное наказание", то в соответствующей строке столбца "Вид основного наказания" появится запись выберите из списка, а если другой результат, то значение нет.

При установке логического условия в столбец "Срок ареста" в строку окна ввода Лог_выражение ввести логическое условие в виде: {адрес первой ячейки столбца "Вид основного наказания"} = "Административный арест"; в строке окна ввода если истина - записать "укажите количество суток", а в строке окна ввода если ложь указать "нет", ОК.

При установке логического условия в столбец "Сумма штрафа" в строку окна ввода Лог_выражение ввести логическое условие в виде: {адрес первой ячейки столбца "Вид основного наказания"}= "Штраф"; в строке окна ввода если истина - записать "укажите сумму", а в строке окна ввода если ложь указать "нет", ОК.

Внимание! Текст значения признака, вносимый в строку окна ввода Лог_выражение, должен полностью совпадать с его текстом в "Справочнике".

После ввода логических формул в первые ячейки указанных столбцов копируем форматы на все ячейки соответствующего столбца (внесенные формулы протягиваются "автозаполнением" на 40 записей).

7. Формируем возможности выбора из списка допустимых значений статистических признаков в столбцах журнала, где определен тип поля данных список. Выделяем первую ячейку под названием статистического признака соответствующего столбца (например, под признаком Статус лица). В меню выбираем Данные - Проверка - Тип данных - Список. Запрашивается диапазон источника данных. В столбце Справочник выделяем диапазон ячеек - все возможные значения соответствующего статистического признака и подтверждаем (кнопка ОК). После этого на выделенной ячейке появится значок выпадающего списка, а при его нажатии можно просмотреть список допустимых значений признака.

Пример.

Созданный формат ячейки скопируйте на весь столбец (на 40 записей).

Аналогично установите форматы для столбцов "Статья КоАП РФ" "Судья", "Результат рассмотрения", "Вид основного наказания", "Вид дополнительного наказания".

Устанавливаем проверку на значения дат в столбцах "Поступило", "Рассмотрено" в пределах года. В меню Данные - Проверка - Тип данных - Дата имеется возможность задать диапазон допустимых значений (например, в пределах 2009 г.). Это обеспечивает корректность первичных данных, так как значения дат, не входящие в заданный диапазон, занести будет нельзя.

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

9. Заполняем "Журнал учета дел об административных правонарушениях за 2009 год" данными (вносим 40 различных записей по делам). В столбцах, в которых тип данных определен как "список", выбираем необходимое значение признака из списка. В столбцах "Статья КоАП РФ" "Судья", "Результат рассмотрения" значения признаков выбираем случайным образом из списка, но так, чтобы в "Журнале учета дел об административных правонарушениях за 2009 год" равномерно присутствовали все значения признаков. Вносимые данные должны быть корректными - соответствовать КоАП РФ.

Примечание. Порядковые номера заносим "автозаполнением" (выделить диапазон двух значений и протянуть вниз за правый нижний угол). "Автозаполнением" можно заносить даты поступления и рассмотрения.

Замечание. Для удобства заполнения большой таблицы, чтобы всегда видеть наименования столбцов (шапку таблицы) и номера дел (первый столбец) необходимо закрепить область шапки наименований столбцов и столбец "N п/п" (перетащить на полосах прокрутки, а затем Окно - Закрепить области).

Второй способ закрепления областей: выделить ячейку, которая должна граничить с закрепляемыми областями сверху и слева и далее Окно - Закрепить области.

10. Формируем сводную таблицу.

Выделить диапазон ячеек вместе с наименованиями столбцов без Справочника. Столбец Справочник можно скрыть. В меню брать Данные - Сводная таблица (рис. 94).

Примечание. Диаграмма формируется одновременно автоматически (отметить в окне "Мастер сводных таблиц и диаграмм" "сводная диаграмма (со сводной таблицей)"). Размещать таблицу можно как на существующем листе, так и на отдельном листе Excel. Лучше сводную таблицу помещать на отдельном листе (рис. 95).

Далее работать с мастером подсказок и дойти пошагово до выбора Макета.

Макет таблицы является статистической программой сводки. В Макете выберите, какой показатель будет формировать строки, какой - столбцы, по какому показателю обсчитываются данные. Наименования показателей перетаскиваются в таблицу мышкой (рис. 95).

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

Результаты рассмотрения дел по судьям. Показатель "Судья" перетащить в строки, а "Результат рассмотрения" в столбцы. В поле "Данные" перетащить показатель "Рассмотрено", так как таблица будет формироваться по делам, имеющим дату рассмотрения. После того как сводная таблица сформирована, перетащить неиспользуемые в диаграмме и таблице показатели к заголовку (где имеется запись "перетащите сюда элементы данных"). Сформируйте диаграмму.

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

Виды наказания по статьям КоАП. В Макете в строки перетаскиваем "Статьи КоАП", в столбцы - "Вид основного наказания". В поле "Данные" помещается показатель "Рассмотрено". Сформировать диаграмму.

Суммы штрафов, наложенные по статьям КоАП. Столбец будет один, поэтому в Макете в столбцы показатель помещать не надо. В строках - показатель "Статья КоАП". Показатель "Сумма штрафов" помещаем в "Данные". В отличие от предыдущих таблиц в "Данных" надо выбрать тип операции "Сумма значений", а не "Количество значений" (выбирается двойным щелчком левой клавишей мыши по метке "Количество значений"). Сформировать диаграмму.

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

11. Внести коррективы в журнал (например, изменить результаты рассмотрения - прекратить дело там, где был штраф). Обновить данные в диаграмме и сводной таблице (значок восклицательного знака на панели меню "Сводные таблицы").

12. Перейти на лист с базой данных. Выделить любую ячейку в таблице. Изменить форму представления базы данных учета дел об административных правонарушениях на карточную (в меню Данные - Форма). Осуществить поиск (используя кнопку "Критерии") по номеру записи.

Компьютерный практикум по теме "Обобщенные показатели статистики"

Цель: Освоить приемы расчета показателей относительных величин и анализа данных с их использованием.

Задачи:

1. Произвести расчеты относительных показателей структуры и координации категорий осужденных по степени тяжести совершенных преступлений (табл. 31). При расчете отношения координации между категориями осужденных по степени тяжести совершенных ими преступлений за базу сравнения взять осужденных за особо тяжкие преступления.

2. Построить круговые (секторные) диаграммы, характеризующие распределение осужденных по тяжести совершенных ими преступлений в 2003 и 2007 гг.

3. Построить столбиковые диаграммы, характеризующие соотношение осужденных по тяжести совершенных ими преступлений в 2003 и 2007 гг.

4. Рассчитать коэффициент преступности и коэффициент судимости по федеральным округам и в целом по России (данные за 2001/2008 г. в табл. 32).

5. Построить диаграммы, характеризующие соотношение коэффициентов преступности и судимости в 2001 и 2008 гг. по федеральным округам в сравнении с Российской Федерацией в целом.

6. Рассчитать индекс судимости с учетом использования данных о судимости за преступления различных категорий тяжести (табл. 33).

Исходные статистические данные для выполнения заданий (табл. 31, 32).

Таблица 31

Тяжесть совершенных преступлений	Осуждено в 2003 г.	Осуждено в 2007 г.
Небольшой тяжести	191 618	295 801
Средней тяжести		310 841
Тяжкие	258 358	254 188
Особо тяжкие	76 806	55 649
Всего	Рассчитать сумму	Рассчитать сумму

Таблица 32

	Численность населения в возрасте до 13 лет (2001/2008 г.)	Всего численность населения (2001/2008 г.)	Осуждено лиц (2001/2008 г.)	Зарегистрировано преступлений (2001/2008 г.)
Центральный федеральный округ	5 098 667 / 4 355 462	738 075 / 150 741	255 302 / 182 221	602 343 / 692 377
Северо-западный федеральный округ	2 034 026 / 1 656 722	14 371 672 / 13 501 038	118 810 / 74 596	318 715 / 282 196
Южный федеральный округ	3 922 002 / 3 637 003	21 523 064 / 22 835 216	159 090 / 124 134	337 610 / 334 780
Приволжский федеральный округ	5 179 517 / 4 106 078	31 839 534 / 30 241 581	272 981 / 210 852	603 262 / 694 063
Сибирский федеральный округ	3 480 583 / 2 894 594	20 675 135 / 19 553 461	229 895 / 167 766	499 882 / 553 160
Уральский федеральный округ	2034977 / 1758572	12 564 607 / 12 240 382	127 829 / 97517	322 661 / 333 876
Дальневосточный федеральный округ	1 204 075 / 968 852	7 107 012 / 6 486 419	79 951 / 57 453	176 608 / 193 200
Российская Федерация	Рассчитать	Рассчитать	Рассчитать	Рассчитать

Таблица 33

Тяжесть совершенных преступлений	Осуждено в I полугодии 2003 года	Осуждено в I полугодии 2004 года	Веса тяжести преступлений
Небольшой тяжести	91 191	97 588
Средней тяжести	122 321	146 764
Тяжкие	114 700	117 923
Особо тяжкие	38 265	26 805
Всего	Рассчитать сумму	Рассчитать сумму