Практическое занятие № 1 по темам «Статистическое наблюдение», «Сводка и группировка материалов статистического наблюдения»

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

Задачи

  • 1. Сформировать журнал первичного статистического учета дел об административных правонарушениях.
  • 2. Средствами Excel сформировать сводные статистические таблицы и сводные диаграммы по заданным программам сводки.

Для выполнения практической работы необходимо изучить темы «Статистическое наблюдение», «Сводка и группировка материалов статистического наблюдения» (гл. 2 и 3 учебника) и усвоить основные понятия: статистический признак, учетный показатель и его значения, структура статистической таблицы.

  • 1. До начала работы ознакомьтесь со структурой учетно-статистической карточки на дело об административном правонарушении, используемой в судах общей юрисдикции (форма № 7р в Инструкции по судебному делопроизводству в районном суде[1]). Форму найдите в любой из доступных СПС («КонсультантПлюс», «Гарант», «Кодекс»), Откройте учетностатистическую карточку в формате MS Word (в виде бланка). Создайте папку на компьютере со своей фамилией, скопируйте файл в нее.
  • 2. На интернет-портале ГАС РФ «Правосудие» (www.sudrf.ru) перейдите на сайт Судебного департамента (www.cdep.ru), в разделе «Судебная статистика» выберите подраздел «Данные судебной статистики», содержащий сводную статистическую информацию по всем судам общей юрисдикции по годам.

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

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

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

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

3. С интернет-портала ГАС РФ «Правосудие» (www.sudrf.ru) перейдите на сайт районного суда, юрисдикция которого распространяется на ваше место жительства (перейдите в раздел «Федеральные суды общей юрисдикции», по поиску найдите сайт интересующего районного суда). Сформируйте список дел об административных правонарушениях, находившихся в производстве суда и вступивших в законную силу в предшествующем году.

Рекомендации по формированию списка

Выберите раздел «Судебное делопроизводство», далее — «Поиск информации по делам», картотеку «Первая инстанция. Дела об административных правонарушениях».

Задайте поиск по датам вступления в законную силу за предшествующий год. Выделите данные на странице в таблице, скопируйте, вставьте в MS Excel. Скопируйте несколько страниц со списками дел и перенесите в MS Excel. Списки должны содержать не менее пяти различных составов правонарушений по статьям КоАП РФ и не менее трех различных результатов рассмотрения.

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

Исходные данные к заданию. Значимые для учета дел об административном правонарушении статистические признаки (учетные показатели), тип ноля данных и возможный перечень значений статистических признаков приведены в табл. 1.

Таблица 1

Статистический

признак

Тин поля данных

Перечень значений статистических признаков

№ п/п

Целое

число

Порядковый номер регистрации занести автозаполнением с 1 до 40

Поступило

Дата

Любые 40 дат в пределах заданного диапазона в хронологическом порядке

Окончание табл. 1

Статистический

признак

Тип поля данных

Перечень значений статистических признаков

Ф.И.О. (наименование юридического лица)

Текст

Любые Ф.И.О. и (или) названия организаций

Статус лица

Список

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

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

Список

Пять любых составов административных правонарушений по статьям Особенной части КоАП РФ, подведомственные судам и подсудные районному суду (обязательно хотя бы одна из выбранных статей Особенной части КоАП РФ должна в санкции предусматривать вид наказания — административный арест, приостановление деятельности, выдворение). В качестве примера используются статьи КоАП РФ с сайта районного суда

Судья

Список

Пять фамилий судей районного суда

Рассмотрено

Дата

С учетом даты поступления

Результат рассмотрения

Список

Назначено административное наказание, прекращено, передано но подведомственности, возвращено

Вид основного наказания

Список

Виды основного наказания по КоАП РФ заносятся в справочник «Виды административных наказаний»

Срок основного наказания

Целое

число

Указывается число, соответствующее размеру назначенного основного вида административного наказания

Сумма штрафа (наложено), руб.

«Денежный» в рублях

При вводе сведений по делам в журнал будет указываться сумма в рублях в случаях назначения штрафа

Вид дополнительного наказания

Список

Значения в справочнике «Виды административных наказаний». Виды дополнительных административных наказаний указываются в соответствии с КоАП РФ после значений основных административных наказаний

Срок дополнительного наказания

Целое

число

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

Справочники

Текст

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

Примечание. Статистические признаки определяют названия столбцов «Журнала учета дел об административных правонарушениях за ХХХХ год», который будет сформирован в MS Excel. Тип поля необходим для установ-

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

Порядок выполнения задания

1. Создание документа.

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

2. Создание полей БД «Журнал учета дел об административных правонарушениях».

Ввести последовательно название статистических признаков из табл. 1 как названия столбцов.

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

3. Создание справочников статистических признаков (учетных показателей).

Справа после последнего столбца созданной таблицы на листе MS Excel формируете раздел «Справочники», в котором будут находиться списки значений показателей, которые следует выбрать для конкретных записей по делам. В свободные ячейки (рекомендуется в одном столбце) последовательно вносите наименования статистических признаков (названия столбцов таблицы, тип поля данных у которых «список»). Под названием учетного показателя вносите перечень возможных значений этих учетных показателей (каждое значение в отдельной ячейке, без пропусков и объединений ячеек).

Возможные значения учетных показателей указаны в столбце «Перечень значений статистических признаков» в табл. 1.

В результате раздел «Справочники», где справочники размещены в одном столбце, будет иметь следующий вид (рис. 1).

Пример формирования раздела «Справочники»

Рис. 1. Пример формирования раздела «Справочники»

Для заполнения значения «Статья КоАП РФ» необходимо определить составы правонарушений, подведомственных судам и подсудных районным судам, задав соответствующие условия поиска по КоАП РФ в СПС (например, «Консультант Плюс»).

Рекомендации. Определите но положениям Общей части КоАП РФ признаки отнесения составов правонарушений к подсудности районных судов.

4. Формирование описания составов правонарушений по КоАП РФ.

Необходимо отобрать не менее пяти составов административных правонарушений в КоАП РФ. Отобранные статьи в санкциях должны содержать различные виды наказаний, в том числе: административный арест, административный штраф, административное приостановление деятельности и административное выдворение[2].

Отобранные составы административных правонарушений — часть и номер статьи КоАП РФ, диспозицию и санкции по ним — скопируйте из СПС и поместите в таблице на вспомогательном листе, назвав его «Статьи КоАП».

Под таблицей со сведениями о составах административных правонарушений создайте таблицу «Справочник составов КоАП РФ по субъектам административной ответственности».

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

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

Если состав правонарушения содержит в санкции дополнительные наказания для конкретных субъектов правонарушения, то такие наказания и их размеры также указываются в отдельных дополнительных столбцах таблицы.

Замечание. Под субъектом правонарушения «гражданин» в диспозициях статей КоАП РФ понимается физическое лицо, не имеющее статуса должностного лица или не являющееся предпринимателем без образования юридического лица, т.е. подпадающее в соответствии со справочником значений в понятие «иные физические лица».

Отобранные статьи Особенной части КоАП РФ внесите в справочник «Статьи КоАП РФ» (только номера и части статей КоАП РФ) в раздел «Справочники» на листе с БД.

Виды административных наказаний для статистических признаков в столбцах «Вид основного наказания», «Вид дополнительного наказания» указывайте в справочнике «Виды административных наказаний», воспользовавшись текстом КоАП РФ в любой доступной СПС («Консультант- Плюс», «Гарант», «Кодекс» и т.п.). В журнале будут использоваться значения видов административных наказаний из общего справочника, поэтому виды наказаний вносятся последовательно: только основные; виды наказаний, которые могут быть как основными, так и дополнительными; наказания, которые могут быть только дополнительными. Поскольку дополнительные административные наказания предусмотрены не во всех санкциях составов административных правонарушений, то в справочник «Видов административных наказаний» последней записью вносим значение «Не предусмотрено».

5. Формирование списков.

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

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

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

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

Аналогичный формат выбора из списка затем надо будет установить и на столбцы «Вид основного наказания» и «Вид дополнительного наказания». Однако предварительно необходимо установить на ячейки данных столбцов ЛУ (выполнить п. 5). После установления ЛУ наложите формат выбора из списка на данные два столбца.

Внимание! Нарушение указанного порядка действий приведет к невозможности сохранить ЛУ (если сначала создать выбор из списка и на этих же ячейках создавать ЛУ, то будет действовать проверка, что данные в ячейке должны соответствовать только значениям списка).

6. Постановка ЛУ.

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

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

Для создания ЛУ используется функция ЕСЛИ.

Выделяете первую пустую ячейку столбца «Вид основного наказания» (по строке с номером 1). На «панели инструментов» нажимаете кнопку fv далее в «категории» выбираете «логические», из списка функций выбираете функцию ЕСЛИ. Появляется окно ввода условий. В строку окна ввода «Лог_выражение» вводите ЛУ в виде: {адрес первой пустой ячейки столбца «Результат рассмотрения»} = «Назначено административное наказание»; в строке окна ввода «Если истина» — пишите «выберите из списка», а в строке окна ввода «Если ложь» укажите «нет», ОК.

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

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

Рекомендуется, чтобы не ошибиться с написанием логического выражения значения из справочника, не вносить в формулу точный текст значения справочника в логическое выражение, а ссылаться на ячейку, в которой это значение введено в справочнике. В записи логического выражения надо указать (кликнуть) на ячейку со значением в справочнике (рис. 2). Обратите внимание, что ячейка должна иметь признак постоянной (знаки $ должны быть перед наименованием строки и столбца, например, $Х$4), чтобы при протягивании — копировании формулы вниз по строкам ссылка на ячейку в ЛУ оставалась неизменной.

После сохранения созданного ЛУ проверьте корректность его выполнения. Если в ячейке столбца «Результат рассмотрения» значение статистического признака будет определено как «Назначено административное наказание», то в соответствующей строке столбца «Вид основного наказания» появится запись «выберите из списка», а если другой результат, то значение «нет». Текст значений истина или ложь можете изменить по своему усмотрению.

Пример логического выражения со ссылкой на ячейку со значением справочника

Рис. 2. Пример логического выражения со ссылкой на ячейку со значением справочника

После ввода Л У в первую пустую ячейку столбца «Вид основного наказания» копируем его на все 40 ячеек данного столбца (внесенные формулы протягиваются «автозаполнением» на 40 записей).

Далее аналогично записываем формулу ЛУ в столбец «Сумма штрафа». При установке ЛУ в первую пустую ячейку столбца «Сумма штрафа» в строку окна ввода «Лог_выражение» ввести ЛУ в виде: {адрес первой пустой ячейки столбца «Вид основного наказания»} = «Административный штраф»; в строке окна ввода «если истина» — запишите «укажите сумму», а в строке окна ввода «если ложь» укажите «нет», ОК. Внесенную в первую пустую ячейку столбца «Сумма штрафа» формулу протянуть «автозаполнением» на 40 записей.

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

Формула ЛУ в столбце «Срок основного наказания» должна предлагать указывать срок наказания при выборе одного из указанных видов наказаний. Для этого логическое выражение функции ЕСЛИ должно содержать внутри себя еще функцию ИЛИ (рис. 3).

При установке ЛУ в первую пустую ячейку столбца «Срок основного наказания» в строку окна ввода «Лог_выражение» введите Л У:

{адрес первой строки таблицы — пустой ячейки столбца «Вид основного наказания»} = ИЛИ («Административный арест»; «Обязательные работы»; «Лишение права»), в поле ввода «Значение_если_истина» запишите «укажите срок», а в поле ввода «Значение_если_ложь» укажите «нет», ОК.

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

1

Пример ввода в логическое выражение функции ЕСЛИ

Рис. 3. Пример ввода в логическое выражение функции ЕСЛИ

функции ИЛИ

Окно ввода проверки типа данных

Рис. 4. Окно ввода проверки типа данных: Дата

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

Сообщение об ошибке при вводе значений, не соответствующих

Рис. 5. Сообщение об ошибке при вводе значений, не соответствующих

условиям типа данных

8. Закрепление областей на листе MS Excel.

Рекомендуется закрепить область шапки таблицы (наименования столбцов) и столбец «№ п/п» (рис. 6), чтобы при движении вниз и вправо эти области были неподвижны. Для этого выделить крайнюю ячейку в левом верхнем углу под строкой с шапкой таблицы и столбцом «№ п/п». Выделенная ячейка «отделяет» подвижную область от закрепляемой неподвижной области. Далее выполнить команду в меню Вид — Закрепить области[3].

9. Заполнение журнала записями по делам.

Заполнить «Журнал учета дел об административных правонарушениях за ХХХХ год» данными (внесите 40 различных записей но делам). Порядковые номера занесите «автозаполнением» (выделите диапазон двух значений и протяните вниз за правый нижний угол). «Автозаполнением» можно также заполнить даты поступления и рассмотрения, исполнения.

Выбор ячейки, выделяющей закрепляемые области листа

Рис. 6. Выбор ячейки, выделяющей закрепляемые области листа

В столбцах, в которых тип данных определен как «список» («Статус лица», «Статья КоАП РФ», «Судья», «Результат рассмотрения», «Вид основного наказания», «Вид дополнительного наказания»), значения признаков выбирайте случайным образом из выпадающих списков[4], но так, чтобы в «Журнале учета дел об административных правонарушениях за ХХХХ год» равномерно присутствовали все значения признаков и вносимые данные каждой записи по делу по строке были корректными — соответствовали КоАП РФ. Сведения по делам вносите построчно, по каждому делу, сверяясь с выбранной по данному делу диспозицией и санкцией статьи КоАП РФ.

10. Формирование сводных таблиц (рис. 7).

Выделите диапазон ячеек вместе с наименованиями столбцов без раздела «Справочники». Столбец(-цы), содержащий раздел «Справочники», можно скрыть. В меню выбираете Вставка — Сводная таблица1 [5].

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

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

Замечание. Диаграмма формируется одновременно автоматически (отметить в окне «Мастер сводных таблиц и диаграмм» «сводная диаграмма (со сводной таблицей)»). Размещать сводную таблицу на отдельном листе Excel.

  • 11. Формирование сводных таблиц по различным макетам и сводных диаграмм к ним (рис. 8).
  • 11.1. Результаты рассмотрения дел по судьям.

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

Построение сводной таблицы

Рис. 7. Построение сводной таблицы

Построение сводной диаграммы

Рис. 8. Построение сводной диаграммы

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

11.2. Виды основных наказаний по статьям КоАП РФ.

В Макете в строки перетащите «Статьи КоАП РФ», в столбцы — «Вид основного наказания». В поле «Данные» поместите показатель «Рассмотрено». Сформируйте диаграмму. Не отображайте в сводном отчете и диаграмме записи, где отсутствует вид наказания (т.е. дела, по которым иной результат рассмотрения). Для этого со значений «пусто» снимите «галочку». Из списка полей не использованные в Макете учетные показатели перетащите в сводную таблицу для использования в качестве фильтров — Судья, Статус лица, Дополнительное наказание.

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

11.3. Суммы штрафов, наложенные по статьям КоАП РФ.

Столбец будет один, поэтому в макете в столбцы показатель помещать не надо, в строках — показатель «Статья КоАП РФ». Показатель «Сумма штрафов» поместите в «Данные». В отличие от предыдущих таблиц в «Данных» надо выбрать тип операции «Сумма» (рис. 9), а не «Количество» (выбирается двойным щелчком левой клавишей мыши по метке «Количество»).

Выбор операции для значений данных в сводной таблице

Рис. 9. Выбор операции для значений данных в сводной таблице

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

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

Внимание! Если сумма штрафов не считается, значит, формат значений в столбце «Сумма штрафа» нечисловой, необходимо проверить, чтобы в ячейках стояли только цифры и формат ячеек был выбран «Числовой».

11.4. Средние суммы штрафов, наложенные по статьям КоАП РФ.

Аналогично строим макет, но выбираем операцию «Среднее».

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

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

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

12. Изменение первичных данных (карточное представление) и корректировка первичных данных.

Вставьте в журнал представление данных в виде экранной формы (карточки надело — рис. 10). Меню — Данные — Форма. Войдите в «Критерии». Найдите дело № 38, измените результат рассмотрения и, соответственно, вид и размер административного наказания. Обновите данные в созданных сводных таблицах и диаграммах (выполните операцию «Обновить»).

Карточная форма представления записей в БД

Рис. 10. Карточная форма представления записей в БД

13. Детализация сводных данных.

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

Пример детализации статистических данных сводной таблицы

Рис. 11. Пример детализации статистических данных сводной таблицы

Настройте фильтры в журнале (Меню — Данные — Сортировка и фильтр — Фильтр).

Выполните аналогичный поиск с использованием фильтра в Журнале дел.

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

Дополнительные задания к практическому занятию № 1

Дополнительное задание 1

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

Задачи

  • 1. Создание шаблона статистического отчета.
  • 2. Формирование суммарных итоговых строк и граф.
  • 3. Создание логических выражений для формально-логического и логико-юридического контроля. Описание параметров условного форматирования.

Имеется макет статистической таблицы (табл. 2), по которой должны быть собраны статистические данные для анализа судебной практики о результатах рассмотрения судами представлений судебных приставов о замене штрафа иными видами наказаний в порядке исполнения приговоров за 2013 г.[6]

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

К табл. 2 имеется описание ФЛК:

1) гр. 4 = гр. 5 + гр. 10; 2) гр. 5 = сумма гр. 7—9; 3) гр.10 = сумма гр. 12—14; 4) гр.10 = сумма гр. 15—18; 5) гр. 3 меньше или равна гр. 2; 6) наличие значения сумм при наличии материалов (отказано); 7) наличие значения суммы при наличии материалов (отказано); 8) гр. 20 меньше или равна гр. 19; 9) гр. 22 меньше или равна гр. 21; 10) гр. 24 меньше или равна гр. 23; гр. 26 меньше или равна гр. 25.

Порядок выполнения задания

1. Создайте на листе MS Excel таблицу, соответствующую по содержанию табл. 2 (все 22 столбца подряд без переноса на новые строки). Поместите в дополнительные столбцы в конце таблицы описания Л У и создайте для них формулы тина ЕСЛИ-TO для проверки заполнения данных.

Рекомендации. Настройка контрольных логических соотношений осуществляется средствами условной функции ЕСЛИ. Необходимо выбрать ячейку, где будет отображаться результат проверки соотношения. Укажите логическое выражение, соблюдение которого будет контролироваться. Допустим, что в случае правильного результата никаких изменений в ячейке не будет — в значение «Истина» ставим пробел, а если контрольное соотношение не соблюдено, т.е. принимает значение «Ложь», то в ячейке должно появиться сообщение «Неверно». Примеры создания ЛУ даны на рис. 12, 13.

Результаты рассмотрения судами представлений судебных приставов о замене штрафа иными видами наказаний

Статья УК РФ

Поступило представлений в суд

Прекращено производство

Рассмотрено представлений (по существу)

Всего

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

Всего

Отказано в удовлетворении

На общую сумму

О замене штрафа в размере

до 1 млн

от 1

до 15 млн

свыше 15 млн

1

2

3

4

5

6

7

8

9

204

290

291

291.1

Статья УК РФ

Рассмотрено представлений (по существу)

Удовлетворено

На общую сумму

О замене штрафа в размере

Удовлетворено представлений с заменой на

до 1 млн

от 1

до 15 млн

свыше 15 млн

лишение

свободы

исправительные

работы

обязательные работы

иные виды наказания

10

11

12

13

14

15

16

17

18

204

290

291

291.1

Окончание табл. 2

Статья УК РФ

Рассмотрено в апелляции

Об отказе в замене штрафа на иной вид наказания

Об удовлетворении представления о замене штрафа

Всего

Из них отменено

Всего

Из них отменено

19

20

21

22

204

290

291

291.1

Экранная форма ввода логической формулы ЕСЛИ

Рис. 12. Экранная форма ввода логической формулы ЕСЛИ

Пример результата вычисления логической функцией ЕСЛИ

Рис. 13. Пример результата вычисления логической функцией ЕСЛИ

некорректного значения

Рекомендации по написанию формулы проверки заполнения связанных показателей «Количество» и «Сумма» (например, количество оштрафованных лиц и суммы штрафов, количество удовлетворенных требований и взысканные суммы).

В условиях типа:

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

= ЕСЛИ (ИЛИ (И (Е9 = 0; F9 = 0); И (Е9 > 0; F9 > 0));» «;» Неверно!»):

То есть, когда проверяемое значение соответствуют логическому выражению элемента ЕСЛИ, отображается индикатор в элементе ТО «ИСТИНА», а в случае несоответствия — индикатор «ЛОЖЬ».

Логическое выражение функции ЕСЛИ содержит логические функции ИЛИ, И:ИЛИ (И (Е9 = 0; F9 = 0); И (Е9 > 0; F9 > 0)

Синтаксис формулы имеет следующий смысл: «Значения ячеек Е9 = 0 и F9 = 0, или Е9 больше 0 и F9 больше или 0».

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

В нашем примере значения показателей не могут быть отрицательными, поэтому в формулах на проверку значений граф «Суммы», «Лица» указываем только больше 0.

  • 2. На новом листе MS Excel создайте журнал учета материалов, которые должны быть включены в статистический отчет. Журнал учета будет являться программой статистического наблюдения. По содержанию показателей определите необходимые реквизиты учета, чтобы по данным журнала можно было просчитать все требующиеся статистические показатели. Для полей, которые могут содержать ограниченный набор значений, сделайте справочники и предусмотрите возможность выбора из списка.
  • 3. Внесите в разработанный журнал учета семь произвольных записей, соответствующих учетным показателям. По учтенным в журнале данным заполните данные в созданной статистической табл. 2. Проверьте корректность внесенных данных по созданному ФЛК.

Дополнительное задание 2

Цель: формирование сводной статистической информации.

Задача: проведение сводки первичных отчетных форм средствами MS Excel.

Для свода будет использоваться статистическая таблица «Назначение наказания в виде штрафа по делам о взяточничестве»[7].

Порядок выполнения задания

  • 1. Создайте таблицу в MS Excel, задайте условия ФЛК и формата данных.
  • 2. Найдите шесть приговоров разных судов на сайтах районных судов вашего региона (или субъекта РФ но выбору преподавателя) на портале ГАС «Правосудие», соответствующие программе наблюдения. Заполните таблицы на основании данных приговоров, по каждому приговору — отдельную статистическую таблицу в разных файлах с именами названий судов.

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

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

В каждый лист указанным выше образом скопируйте бланк сведений с проверками.

  • 4. На бланке со сводным отчетом введите в верхнюю левую ячейку формы формулу суммы но всем предшествующим листам. Для этого надо выделить мышкой первую ячейку, в которой будет сводная сумма, нажать клавишу «=» затем нажать по очереди на такие же ячейки на всех предыдущих листах начиная с первого, при этом после выбора каждой ячейки (кроме ячейки на предпоследнем листе) нажимая «+». Когда вы дойдете до предпоследнего листа, вслед за которым находится «Сводная форма», необходимо нажать клавишу Enter. Для того чтобы формулы суммирования были прописаны во всех нужных ячейках, надо поднести мышку к правому нижнему углу первой ячейки, в которой уже прописана формула. При этом курсор примет форму крестика, надо потянуть за этот крестик вдоль столбца или вдоль строки (т.с. скопировать формат ячейки, содержащей формулу). Таким же образом скопируйте формулы во все строки и столбцы, куда должны быть внесены данные. Значение во всех ячейках при пустой базе первичных отчетов будет нулевым.
  • 5. Скопируйте па соответствующие листы содержание файлов с первичными отчетами. На листе «Сводный отчет» должны отобразиться данные суммирования. В случае ошибочных данных в ячейках, где настроены формулы проверки, отобразится сообщение «Неверно!».
  • 6. Создание формы для выборки заданных показателей (рис. 14). Операция детализации значений показателя сводного отчета — одна из наиболее часто встречающихся при анализе статистических данных.

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

Лист для выборки заданного показателя из отчетов

Рис. 14. Лист для выборки заданного показателя из отчетов

В ячейке, соответствующей значению по первому суду, вводим формулу. Запись в строке формул на листе MS Excel на рис. 14 имеет следующий смысл: обращаясь к соответствующему имени листа, указанному в ячейке

B4 (т.е. к коду организации), от указанной точки отсчета (ячейка пересечения номеров строк и граф) отсчитывается по порядку число по строкам и графам, указанное в ячейке, соответствующей «№ строки» и «№ графы». Значение в найденной ячейке отображается в столбце как результат выполнения формулы.

Последовательность создания формулы.

Выберите функцию СМЕЩ (смещения) (рис. 15). Указываем две ячейки, в которых будут указываться номера выбираемых строк и граф.

Экранная форма ввода параметров функции смещения

Рис. 15. Экранная форма ввода параметров функции смещения

Рекомендации. В функцию СМЕЩ вводится вложенная функция ДВССЫЛ, а внутрь нее — функция АДРЕС (рис. 16).

Экранная форма ввода параметров функции АДРЕС

Рис. 16. Экранная форма ввода параметров функции АДРЕС

В функции АДРЕС номер строки и номер столбца означают адрес ячейки, от которой будет осуществляться смещение. В Текст _ листа помещают адрес ячейки, в которой записано имя листа, с которого должны быть взяты значения.

Протяните формулу, проверьте выбранные значения в отчетах.

Выберите новый показатель в отчете, измените номер строки и графы выбранного показателя.

Выбранные данные в столбце должны соответствовать данным в соответствующей ячейке отчета.

Дополнительное задание 3

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

Задача: освоить прием проверки нарастающего итога.

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

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

Порядок выполнения задания

  • 1. Откройте два файла, содержащих сводные отчеты но одной форме за первое полугодие 2013 г. и за 2013 г. Файлы по указанию преподавателя по уголовным, гражданским делам или делам об административных правонарушениях копируем с сайта Судебного департамента (раздел «Судебная статистика», «Данные судебной статистики», сводные статистические отчеты о рассмотрении дел судами но первой инстанции № 1, 2, 1 -АН, во всех формах раздел 1 или сводные статистические отчеты о работе апелляционной инстанции — формы № 6, 7, раздел 1).
  • 2. Скопируйте сравниваемые разделы в один файл на отдельные листы. На последний новый лист скопируйте пустую статистическую таблицу раздела формы. Выделите цветной заливкой на листе с годовыми данными столбцы и ячейки, содержащие моментные показатели (остаток, число приостановленных дел и т.д., на которые не распространяется правило нарастающего итога).
  • 3. В верхней левой графоклетке статистической таблицы введите формулу вычитания значений предыдущего отчетного периода из последующего.

Рекомендации. Ввод формулы начинайте знаком «=», затем кликните на ячейку со значением показателя на листе с разделом последующего периода, минус «-» значение на листе предыдущего периода, ввод Enter. Затем протяните формулу на всю таблицу. Наличие отрицательных значений может свидетельствовать или о некорректных данных, или о корректировке отчетности за год. При наличии отрицательных значений ячейка изменит заливку на красную[8].

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

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

В меню «Формат» — выберите «Условное форматирование». Задайте условие изменения формата заливки ячейки на другой цвет, если значения меньше 0.

Измените условное форматирование на отрицательные значения на голубую заливку.

Дополнительное задание 4

Цель: формирование программы сводки по программе статистического наблюдения.

Задача: описать расчет статистических показателей по данным первичного учета — статистической карточки па подсудимого (СКП)[9].

Статистическая отчетность но судимости формируется на основании БД СКП.

Порядок выполнения задания

Опишите алгоритмы расчета показателей по графам, по строкам форм по судимости (например, формы 10.3 или 10.3.1, по нескольким строкам по указанию преподавателя или по заданному варианту). Условия формирования статистического показателя (графоклетки) по значениям СКП определяются совокупностью условий по строке и графе. Описание расчета показателей вставьте в бланк формы, добавив дополнительные графу и строку перед нумерацией статистической таблицы. Например, условие формирования строки «Ст. 204 ч. 1» будет: показатель 4.0 — основная статья, п. 4.6 Статья по судебному акту — 204 ч. 1. Условие по графе «Осуждено» — Дата вступления в силу судебного акта — с 01.01.2013 по 31.12.2013, п. 5.2 — Результат рассмотрения в отношении лица — «Осуждено».

  • [1] Приказ Судебного департамента от 29.04.2003 № 36 «Об утверждении Инструкциипо судебному делопроизводству в районном суде».
  • [2] Полное название вида административного наказания в соответствии со ст. 3.2 «Видыадминистративных наказаний» КоАП РФ — административное выдворение за пределы Российской Федерации иностранного гражданина или лица без гражданства.
  • [3] Для MS Excel 2003—2007 меню Окно — Закрепить области.
  • [4] Журнал может быть заполнен реальными выборочными данными на основе сведенийс сайта районного суда.
  • [5] Для MS Excel 2003—2007 меню Данные — Сводная таблица. Наименования показателей перетаскиваются в Макете в таблицу левой клавишей мыши.
  • [6] Обобщение судебной практики проводилось в 2013 г. областными и равными имсудами по поручению Верховного Суда РФ.
  • [7] В качестве образца используйте раздел 8 формы № 10.4.1 «Размеры штрафов осужденным за преступления коррупционной направленности», скачав утвержденный приказомбланк формы с сайта Судебного департамента, раздел Судебная статистика, нормативнаябаза.
  • [8] В шаблонах форм отчетности о деятельности судов, которые разрабатываются в Судебном департаменте для всех судов, настраивается условное форматирование числовых ячеек:при отрицательных значениях заливка ячейки ярко-красная.
  • [9] Бланк статистической карточки на подсудимого на 2015 г. утвержден приказом Судебного департамента от 29.12.2014 № 309. Приказ размещен на сайте Судебного департаментав разделе «Судебная статистика», подраздел «Нормативная база». URL: http://www.cdep.ru/index.php?id=78 (дата обращения: 24.11.2015).
 
Посмотреть оригинал
< Пред   СОДЕРЖАНИЕ   ОРИГИНАЛ     След >