Выполнить работу в Microsoft Excel

Задание 4: Анализ статистики прокурорских проверок и построение интерактивной панели мониторинга в Excel

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

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

Создайте таблицу со следующими данными:

Задание: заполните таблицу данными как минимум за 2 квартала (6 месяцев).

Требования к выполнению:

1. Расчетные показатели:

Добавьте в таблицу два расчетных столбца:

«Эффективность проверок, %» (формула: = (Выявлено нарушений / Количество проверок) * 100).

«Коэффициент следственного реагирования» (формула: = (Возбуждено дел / Выявлено нарушений) * 100).

2. Сводная таблица и срез:

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

К данной сводной таблице добавьте интерактивный срез по полю «Тип проверки».

3. Построение диаграмм для дашборда:

На новом листе создайте панель мониторинга.

Разместите на нем:

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

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

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

Интерактивность:

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

Пошаговая инструкция к заданию:

Этап 1: Подготовка и форматирование исходной таблицы

1. Создайте таблицу с данными:

Откройте новый файл MS Excel.

На Листе 1 создайте таблицу с следующими заголовками столбцов:

A: Подразделение

B: Тип проверки

C: Месяц

D: Количество проверок

E: Выявлено нарушений

F: Возбуждено дел

Заполните таблицу данными как минимум за 6 месяцев для 2-3 подразделений и 2-3 типов проверок. Пример данных можно взять из формулировки задания.

2. Преобразуйте диапазон в «Умную таблицу»:

Выделите любую ячейку в вашей таблице.

Перейдите на вкладку «Главная» -> «Форматировать как таблицу» и выберите любой понравившийся стиль.

В диалоговом окне убедитесь, что галочка «Таблица с заголовками» стоит, и нажмите «ОК».

Зачем это нужно: «Умная таблица» автоматически расширяется при добавлении новых данных, и формулы в добавленных столбцах автоматически копируются на все строки.

3. Добавьте расчетные столбцы:

В столбце G (следующий после «Возбуждено дел») создайте заголовок Эффективность проверок, %.

В первой ячейке этого столбца (например, G2) введите формулу:
=[@[Выявлено нарушений]]/[@[Количество проверок]]*100

Нажмите Enter. Формула автоматически применится ко всем строкам таблицы.

В столбце H создайте заголовок Коэффициент следственного реагирования.

В первой ячейке (H2) введите формулу:
=ЕСЛИ([@[Выявлено нарушений]]>0; ([@[Возбуждено дел]]/[@[Выявлено нарушений]])*100; 0)

Зачем это нужно: Функция ЕСЛИ предотвращает ошибку деления на ноль, если нарушений не выявлено.

4. Присвойте таблице имя (опционально, но полезно):

Выделите таблицу. Появится вкладка «Конструктор таблиц».

В группе «Свойства» вы увидите поле «Имя таблицы». Задайте понятное имя, например, ДанныеПроверок.

Этап 2: Создание сводной таблицы и срезов

1. Создайте сводную таблицу:

Выделите любую ячейку в вашей «умной таблице».

Перейдите на вкладку «Вставка» -> «Сводная таблица».

В диалоговом окне убедитесь, что выбран правильный диапазон вашей таблицы, и нажмите «ОК». Будет создан новый лист для сводной таблицы.

2. Настройте сводную таблицу:

В области «Поля сводной таблицы» справа:

Перетащите поле Подразделение в область «Строки».

Перетащите поле Количество проверок в область «Значения». Убедитесь, что для него выбрана функция «СУММ».

Перетащите поле Эффективность проверок, % в область «Значения». Щелкните по нему и в параметрах поля выберите «Параметры полей значений...». Установите вычисление по «Среднему». Нажмите «ОК».

3. Добавьте срезы (интерактивные фильтры):

Выделите сводную таблицу.

Перейдите на вкладку «Анализ сводной таблицы» (или «Конструктор») -> «Вставить срез».

В диалоговом окне выберите поля Тип проверки, Подразделение и Месяц. Нажмите «ОК».

На листе появятся три среза. Вы можете настроить их размер и расположение.

Этап 3: Построение диаграмм для панели мониторинга

1. Создайте новый лист для дашборда:

Создайте новый лист и назовите его «Панель мониторинга».

2. Гистограмма с группировкой (Проверки и нарушения по месяцам):

Вернитесь на лист со сводной таблицей.

Создайте новую сводную таблицу на этом же листе (чтобы она была связана с теми же срезами).

Настройте ее:

Строки: Месяц

Значения: Количество проверок (СУММ) и Выявлено нарушений (СУММ).

Выделите эту сводную таблицу и перейдите «Анализ сводной таблицы» -> «Сводная диаграмма».

Выберите тип «Гистограмма с группировкой». Нажмите «ОК».

Скопируйте готовую диаграмму и вставьте ее на лист «Панель мониторинга».

3. Круговая диаграмма (Распределение по типам проверок):

Аналогично, создайте третью сводную таблицу:

Строки: Тип проверки

Значения: Количество проверок (СУММ).

На ее основе постройте «Круговую диаграмму» и перенесите на дашборд.

4. Линейчатая диаграмма (Рейтинг подразделений по коэффициенту реагирования):

Создайте четвертую сводную таблицу:

Строки: Подразделение

Значения: Коэффициент следственного реагирования (Среднее).

На ее основе постройте «Линейчатую диаграмму» и перенесите на дашборд.

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

Этап 4: Настройка интерактивности и финальное оформление

1. Свяжите срезы со всеми диаграммами:

Это самый важный шаг. Щелкните правой кнопкой мыши по любому срезу (например, по «Тип проверки»).

Выберите пункт «Связь с отчетом...».

В появившемся диалоговом окне установите галочки напротив ВСЕХ созданных вами сводных таблиц (и, соответственно, их диаграмм). Нажмите «ОК».

Повторите эту процедуру для всех срезов (Подразделение и Месяц).

2. Проверка интерактивности:

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

3. Финальное оформление дашборда:

Расположите диаграммы и срезы на листе логично и компактно.

Добавьте заголовки к диаграммам для большей ясности (выделите диаграмму -> вкладка «Конструктор» -> «Добавить элемент диаграммы» -> «Названия диаграмм»).

Подпишите сам лист «Панель мониторинга статистики проверок».