Отчёт по обращениям граждан: сводка по темам и срокам в Excel
Раз в месяц или в квартал вышестоящая организация просит отчёт по обращениям граждан: сколько поступило, по каким темам, кто отвечал, сколько ответов дано с нарушением срока. Журнал при этом ведётся исправно, а на сам отчёт уходит полдня ручного подсчёта. Разберём, как построить периодический отчёт так, чтобы он собирался из журнала за несколько минут и сразу показывал узкие места.
Отчёт по обращениям граждан: что в него обычно включают
Единой формы отчёта, одинаковой для всех, нет: структуру почти всегда задаёт вышестоящий орган или внутренний регламент. Поэтому первым делом уточните, какие показатели от вас ждут, и сверьтесь с действующими требованиями. Закон об обращениях граждан (59-ФЗ) задаёт общие правила рассмотрения, а конкретные сроки и формы отчётности зависят от вида обращения и вашей организации.
Чаще всего в периодический отчёт входят следующие блоки:
- Общее количество обращений за период и динамика к прошлому периоду.
- Разбивка по видам: письменные, устные на личном приёме, электронные, из других органов по компетенции.
- Разбивка по темам: жилищные вопросы, благоустройство, транспорт, социальная поддержка, трудовые вопросы и так далее.
- Результат рассмотрения: разъяснено, поддержано, не поддержано, направлено по компетенции.
- Соблюдение сроков: сколько ответов дано в срок, сколько с нарушением, сколько ещё на исполнении.
- Нагрузка на исполнителей: сколько обращений у каждого отдела или сотрудника.
- Повторные и коллективные обращения, если по ним нужна отдельная отметка.
Главное правило: отчёт должен собираться из журнала автоматически. Если для каждой цифры приходится вручную листать строки, ошибки неизбежны.
Как должен быть устроен журнал, чтобы отчёт собирался сам
Качество отчёта определяется качеством журнала. Типичная проблема: тема записана в свободной форме, и «Жилищный вопрос», «ЖКХ», «жилищное» превращаются в три разные категории. Чтобы этого не было, заведите справочники и пользуйтесь выпадающими списками.
Минимальный набор колонок, который позволяет получить любой из перечисленных отчётов:
- Регистрационный номер и дата регистрации.
- Вид обращения (письменное, электронное, личный приём).
- Тема из справочника.
- Исполнитель или подразделение.
- Срок рассмотрения (рассчитывается формулой от даты и вида обращения).
- Дата фактического ответа.
- Результат рассмотрения.
- Отметка о продлении срока, если оно применялось.
- Статус: на исполнении, исполнено в срок, исполнено с нарушением срока.
Статус лучше не вводить руками, а вычислять формулой. Например, если дата ответа пустая, а срок уже прошёл, строка получает статус «Просрочено». Если дата ответа есть и она не позже срока, статус «В срок». Так вы исключаете ситуацию, когда исполнитель «забыл» поменять статус.
Сводка по темам и исполнителям: пошагово в Excel
Допустим, за квартал в журнале 312 обращений. Нужно получить сводку по темам и по исполнителям. Самый надёжный способ без макросов: функции СЧЁТЕСЛИМН и СУММЕСЛИМН плюс сводная таблица.
Шаг 1. Приведите данные в порядок. Превратите диапазон журнала в «умную» таблицу (Ctrl+T). Тогда формулы и сводные будут подхватывать новые строки сами.
Шаг 2. Добавьте вычисляемые колонки. «Срок», «Статус», «Дней на рассмотрение» (дата ответа минус дата регистрации). Месяц регистрации тоже вынесите в отдельную колонку: так проще строить помесячную динамику.
Шаг 3. Постройте сводную таблицу по темам. В строки поставьте «Тема», в значения «Количество по полю Номер». Добавьте второй показатель: количество строк со статусом «Просрочено». Отсортируйте по убыванию.
Шаг 4. Постройте сводную по исполнителям. В строки «Исполнитель», в столбцы «Статус», в значения количество. Сразу видно, у кого больше всего просрочек.
Шаг 5. Добавьте процент. Доля просроченных = просроченные / всего по строке. Именно процент, а не абсолютное число, показывает реальную картину: у сотрудника с 5 просрочками из 10 дело хуже, чем у сотрудника с 6 из 80.
Пример вымышленных данных для квартала:
- Всего обращений: 312.
- Жилищные вопросы: 94 (30 %), благоустройство: 71 (23 %), социальная поддержка: 58 (19 %), транспорт: 40 (13 %), прочее: 49 (15 %).
- Исполнено в срок: 281, с нарушением срока: 17, на исполнении: 14.
- Доля нарушений: 17 / 298 завершённых, то есть около 5,7 %.
Такая картина уже говорит о многом: почти треть обращений приходится на одну тему, значит, есть смысл подготовить типовые разъяснения или вынести проблему на уровень руководства.
Как находить узкие места по данным отчёта
Отчёт нужен не только для отправки наверх, но и для управления. Вот что стоит смотреть каждый период.
Среднее время рассмотрения по темам
Посчитайте среднее значение колонки «Дней на рассмотрение» в разрезе тем. Если по жилищным вопросам в среднем 26 дней при допустимых 30, а по остальным 12 дней, значит, в этой теме обращения регулярно «упираются» в срок, и риск просрочки высокий.
Доля продлений
Если срок продлевается часто, это сигнал: либо обращения действительно сложные и требуют запросов в другие органы, либо исполнители откладывают работу. Сопоставьте долю продлений с темой и исполнителем.
Обращения «на грани»
Отдельным списком выводите обращения, до срока по которым осталось 5 дней и меньше, а ответа ещё нет. Это рабочий инструмент на каждую неделю, а не только на конец периода. Подсветите их условным форматированием.
Перекос нагрузки
В госоргане, школе или администрации нагрузка часто распределяется неравномерно: один специалист закрывает половину обращений. Сводка по исполнителям покажет это в цифрах и поможет аргументированно перераспределить работу.
Повторные обращения
Если один и тот же заявитель обращается по одному вопросу второй и третий раз, значит, первый ответ не решил проблему. Колонка «Повторное: да/нет» и простая сводка по ней помогут увидеть темы, где ответы формальные.
Типичные ошибки при подготовке отчёта
- Разные написания одной темы. Решение: справочник и выпадающий список вместо ручного ввода.
- Срок введён вручную. Один сотрудник считает календарные дни, другой рабочие. Решение: единая формула и понятное правило в шапке таблицы, согласованное с юристом вашей организации.
- Статус не обновляется. Решение: вычисляемый статус.
- Отчёт собирается из разных файлов. Решение: один журнал на организацию с фильтром по подразделению.
- Нет контроля данных. Дата ответа раньше даты регистрации, пустая тема, дубль номера. Решение: проверка данных и отдельный лист с контролем ошибок.
- Персональные данные в отчёте. Для вышестоящей организации обычно нужны обобщённые цифры. Не выгружайте ФИО и адреса заявителей туда, где они не нужны.
Чек-лист перед отправкой отчёта
- Период в шапке указан верно, даты границ совпадают с запросом.
- Сумма по темам равна общему числу обращений.
- Сумма «в срок», «с нарушением» и «на исполнении» тоже равна общему числу.
- Нет строк без темы, исполнителя или даты регистрации.
- Просроченные обращения проверены вручную, причины записаны.
- Цифры сверены с предыдущим отчётом: резкие скачки объяснены.
- Из файла удалены лишние персональные данные.
Где взять готовую таблицу
Если не хочется собирать формулы и сводные с нуля, возьмите Журнал обращений в Excel. Он платный, стоит 390 ₽. Внутри журнал на 500 строк, сроки по типам обращений, учёт продлений, сводка по темам и исполнителям, списки просроченных и «горящих» обращений и отчёт по месяцам. Справочники и формулы уже настроены, вам остаётся вносить обращения.
Если вам нужно сверять выгрузки из разных систем, пригодится бесплатная Сверка двух списков в Excel. А если журнал входящих документов в вашей организации ведётся отдельно, посмотрите Журнал входящих и исходящих документов. Подборка решений для бюджетных учреждений и органов власти собрана на странице для госструктур.
Нужно что-то ещё или доработать таблицу под ваш регламент и форму отчёта? Закажите проект.
