Основные проблемы всей отчетности
На работе имеется проблема - это отчетность.
Каждый отчет валидольный т.к. даже открыть файл - это проблема.
Исодные данные:
- Файл xlsx весом 80 000 КБ (изменение на xlsb не решает проблемы т.к. не позволяет подключаться с помощью PowerQuery к текущему файлу)
- более 30 страниц, с таблицами до 2 500 строк и по 200 столбцов
- переполненый диспетчер имен (удалил более 80 000 имен)
- пользовательские форматы ячеек (было несколько сотен форматов)
Ключи
- В качестве ключей по которым связаны большинство таблиц были номера договоров (без даты). В процессе проверок все-таки случилось так, что появились кредиты с одинаковыми номерами у разных контрагентов. В дальнейшем выяснилось, что и номер договора тоже не уникален для нашей организации (теперь люблю документы с номером б/н). В последствии оказалось, что и номер документа с датой тоже использовать сразу не получиться т.к. между номером и датой может отсутствовать необязательное "от" и количество пробелов ничем не ограниченного в любом месте имени договора и программными средствами базы данных это тоже не ограниченно никак, а к этому прибавьте всевозможные непечатные символы (переносы сток, неразрывные пробелы ТАБ и т.д.)
- Номер договора не самое крайнее левое поле. Поле с ключами, обычно закрепляется и если оно крайнее левое, то работать значительно удобнее.
Кормлю бухгалтерию ошибками, чтобы привели все соответствие. Прошел уже год и мы только завершаем эту работу.
Помните, я им не начальник и приказать я ничего не могу.
ИНН
- Exsel пытается постоянно подсказать человеку и немного скорректировать информацию, чтобы было проще. В результате все ИНН хранятся в числовом формате и первые нули обрезаются. Написал макрос, который исправляет ИНН и переводит ячейку в текстовый формат.
- Некоторые данные - ИНН, ОГРН и т.д. оказались неверными т.к. иногда данные протягивали за нижний угол.
Источник информации
- Ранее вся информация присылалась из бухгалтерии в xl, без ИНН, при этом имеется несколько компаний, которые имели схожее или очень схожее название, отличное в регистре буквы в написании.
Это все копировалось на вновь созданный лист в самом отчете, что увеличивало количество листов. Кратно кварталам плюс 1. - Далее с помощью сложных комбинаций простых формул из номера с датой договора извлекался номер договора, по которому с каждого квартала формулой ВПР вся информация заносилась на нудные листы.
- На неустановленном компьютере были установлены дополнительные расширения и настройки. От курса валют, до собственных шрифтов и форматов ячеек, связей с другими книгами. К этому добавилось порядка 80 000 недействительных имен в диспетчере имен (он просто не открывался средствами xl и все пришлось искать в архивах xl руками, чтоб еще ничего лишнего не удалить).
Для решения большей части проблеме пришлось полностью переработать отчеты в учетной системе и добавить недостающие поля. Далее запросами Power Query унифицировать различные выгрузки. Это позволило автоматизировать заливку данных, убрать человеческий фактор, сократить количество листов с данными в 4 раза.
Вес файла и время открытия
- Вес файла достиг 80 000 КБ, а время открытия достигало 40 минут.
- На самом деле на скорость открытия в данном случае больше влияет количество формул, которые ссылаются на предыдущие вычисления на десятках таблицах, больших размеров. Сейчас вес нового файла менее 4 000 КБ, а время открытия порядка 10 минут.
Форматы ячеек.
Не могу установить причины, но при попытке привести все ячейки к одному шрифту и формату ячеек приводили к ошибке, которая не давала сохранять файл без возможности, что-то сделать, удалить диапазон с такими ячейками можно, но ошибка при этом возникает такая же.
В качестве решения данной проблемы удалил все стили книги и скопировал форматы ячеек из сохраненной книги. Затем привел все к единообразию вернув формат к стилю ячеек, который требовался изначально. Все, в книге нет лишних форматов стилей, но при прохождении всех этапов согласования готового отчета они вновь появляются. Кто-то из сотрудников намерено или по незнанию добавляет данные в диспетчер имен и дорматы ячеек.
Разобрал файл XL и докопался до автора стилей ячеек. ФИО уже установлены, осталось только понять работают ли они сейчас и кто еще пользуется настройками, которые создали эти негодяи.
ВПР
ВПР - это одна из самых быстрых, но ненадежных функций.
ВПР - не может позиционировать себя по горизонтали. Если добавить столбик в середину таблицы, как временно, так и на постоянной основе, то формула перестает работать корректно, а обнаружить это не всегда возможно.
Учитывая все это я перевожу все таблицы с данными в динамические таблицы, а так же использую связку ИНДЕКС ПОИСКПОЗ, или СУММЕСЛИМН. При работе в динамической таблице это сильно упрощает жизнь.
Нумерация полей.
В подавляющем большинстве отчетов в шапке таблицы содержится номер столбца, а чуть выше описание. Это очень крутая штука и ей нужно уметь пользоваться.
Если взять все данные из всех таблиц в файле выстроить в одну большую таблицу, удалить абсолютно одинаковые поля (столбцы), а затем пронумеровать, то эту сквозную нумерацию можно использовать в дальнейшем для подстановки данных между таблицами.
Создав один справочник полей, мы избежим дублирования полей.
При использовании формулы ИНДЕКС ПОИСКПОЗ, чтобы подтянуть данные из другого столбца, потребуется просто прописать его номер и никогда эта нумерация, случайно уже не измениться, а каждый пользователь будет однозначно понимать, что здесь должно быть.
Ручное исправление формул
Представьте себе отчет на несколько тысяч строк и пору сотен столбцов в каждой ячейке которого есть формула. Все хорошо, но кто-то изменил одну ячейку т.к. в прошлом квартале это было исключительный случай и его нужно было нивелировать.
Для решения этой проблемы необходимо создать таблицы корректировок, которые позволяют точечно исправлять данные с учетом временных периодов, а исправления прошлого квартала уже не влияют на текущий.
Почему так происходит
Бизнес-аналитик
В компании под 100 человек офиса не просто нет должности, но и человека выполняющего этот функционал.
Фрагментарность работы
При заполнении отчета, каждый отдел заполняет свой блок информации в меру своего понимания и знаний, в результате часть информации, либо дублируется, либо не вносится, а иногда правится руками, чтоб сошлось.
Отсутствие понятных инструкций
Как показала практика, все правила формирования выгрузок отчета, должны быть сохранены в виде скринов и сформированы в инструкцию (презентацию). Письменные инструкции, как правило, бесполезны т.к. визуальные образы цельные. На скринах разу видны все фильтры, настройки.
Далее в каждом отчете должен быть паспорт отчета и небольшие рекомендации по заполнению этого отчета. Это все критично. Особенно при смене коллектива, болезнях.
Новый вопрос для собеседования
После сдачи квартальной отчетности у меня появился еще один вопрос к потенциальным работодателям - какого размера должностная инструкция?
Список всех моих статей в оглавлении.