2026-05 Автор: Кубик. Цикл матеріалів "Непаперова армія".

Електронний журнал. Частина 5. Оптимізація ЕЖООС для великого підрозділу.

В цій статті буде надано технічні рекомендаці, які допоможуть оптимізувати роботу вашого ЕЖООС, зменшити розмір файлу і пришвидшити його роботу. Рекомендації можна використовувати незалежно від розміру підрозділу, але практичний ефект вони матимуть тільки на великих розмірах таблиць.

Визначимось, що мається на увазі під "великим розміром".

1. Великою будемо вважати таблицю або аркуш в якому кількість рядків більше 10 000.
2. ЕЖООС в якому кількістсь рядків на аркуші ООС більше 3 000, також будемо вважати великим.

Якщо у вас менша кількість даних, ця інформація буде вам корисна більше для розвитку, ніж для практичної оптимізації наданого зразка ЕЖООС Кубик. На менших розмірах все має працювати швидко і не вимагати додаткових дій.

Мета оптимізації і вимірювання результату

Оптимізувати ми будемо два основних параметра це швидкість обрахунку всіх формул в журналі і розмір файлу. Як визначити розмір файлу це зрозуміло, зафіксуйте початкове значення, а для визначення швидкості підрахунку формул можна використати такий макрос. Перед запуском макросу залиште тільки той файл швидкість підрахунку якого треба порахувати.

Sub MeasureCalculationTime()
    Dim startTime As Double
    Dim endTime As Double
    ' Вимкнути події та екранне оновлення
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    startTime = Timer
    Application.CalculateFullRebuild
    endTime = Timer
    MsgBox "Час перерахунку: " & Round(endTime - startTime, 2) & " секунд"

    ' Відновлення стану
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Коли потрібно щось робити:

Швидкість повного перерахунку
менше 7-8 секунд нічого не робити;
Більше 8-15 секунд треба думати про оптимізацію;
Більше 15 секунд треба оптимізовувати.

Розмір файлу:
до 3 мб на 1000 рядків аркушу ООС, нічого не робити;
3-5 МБ треба задуматись;
більше 5 мб треба оптимізувати

Скоріш за все показники будуть пов'зані, єдине якщо у вас навіть невеликий підрозділ і кількість рядків ООС 1000-3000 через деякий час, може виникнути потреба зменшити розмір файлу, навіть при приємних показниках швидкості.

Дослідження

Перш ніж щось оптимізувати треба зрозуміти, що саме викликає найбільше навантаження і займає розмір.

Ще раз про безпеку даних

Вести електронний облік особового складу потрібно із дотриманням вимог безпеки і конфіденційності даних. Пам'ятайте: будь-який список особового складу, штатний розклад і кількісні показники підрозділу - це інформація для службового користування (ДСК), а в деяких випадках може взагалі мати гриф "Таємно"!

Ні в якому разі не надавайте інформацію з ЕЖООС, скріни екранів з реальними даними чи копії файлів.
Ні служба підтримки ні автор, ніколи не запитають у вас таку інформацію, щоб щось виправити або подивитись. У випадку будь-яких підозрілих запитів повідомте через форму зворотнього зв'язку і зверніться до відділу, який відповідає за кібербезпеку у вас в підрозділі.

Пошук помилок початкового заповнення

Якщо кількість не збігається з даними вашого поточного обліку, треба шукати причини. Розглянемо основні етапи перевірки, щоб виявити причини розбіжності.

1. Штатна чисельність має зійтися в першу чергу. Якщо не збігається, перевірте аркуші Посади і ШПО. Можливі причини:

• На аркуші Посади вказане звання, яке відсутні в таблиці звань (колонки O-R аркушу Налаштування), або вказане скорочено, або з помилкою. Знайти зручно за допомогою фільтру в категорії посади на аркуші Посади, там же потрібно і виправити.
• Якійсь підрозділ пропущено при підготовці аркушу Статистика, або неправильно зроблено підсумовування. Перевіряйте кількісні показники штату по всіх підрозділах і категоріях посад.

2. Якщо зі штатною чисельністю все добре, але не збігається списочна чисельність. Можливі причини:

• Аналогічна проблема із назвою звання. Перевірте колонку "Категорія військовослужбовця", чи не з'явилося значення "Не знайдено", яке підсвічується малиновим кольором.
• Перевірте принцип, як ви рахуєте списочну чисельність. В наведеному прикладі списочна чисельність рахується по категорії військовослужбовця, а не по категорії посади.

3. Не збігається кількість військовослужбовців в наявності або по якійсь категорії відсутніх. Перевірте налаштування статусів (колонки E-K аркушу Налаштування), зверніть увагу, що наявність\відсутність рахується по колонці "Статус група", колонка "На забезпеченні", в наданому прикладі ніяк не впливає на підрахунки. Перечитайте ще раз частину 2 розділ Статуси. Якщо не розберетеся - задавайте питання в групу в Signal.

4. Не збігається кількість особового складу в розпорядженні. Перевірте список на аркуші ООС, встановить фільтр по колонці 240 - останній тип наказу "В розпорядження". Має залишитися актуальний список військовослужбовців, які перебувають у розпорядженні. Аналогічний результат має давати фільтр по колонці 430 - де поточна посада не заповнена. Якщо список не вірний, перевірте, чи внесли інформацію на аркуш Переміщення і перевели цих військовослужбовців в розпорядження.

Можливі проблеми зі швидкістю роботи і особливості використання смарт-таблиць

Для роботи наведеного зразка ЕЖООС потрібен Excel версії 2021 або вище.

Для орієнтиру розміру файлу будемо використовувати показник кількості особового складу на аркуші ООС, далі під терміном кількість ОС буде матися на увазі саме цей показник.

1. При кількості ОС до 10 000, для нормальної роботи достатньо стандартного вживаного офісного ноутбука вартістю в 15-20 тис. грн. Орієнтовний "нормальний" розмір файлу ЕЖООС у форматі XLSX 2-4 Мб на кожну тисячу рядків аркушу ООС. Не зважаючи на те що формат XLSB важить менше і має працювати швидше і краще, були випадки підвисання і не відновлення данних в цьому форматі, тому цей формат використовувати не рекомендовано.

2. Приклад містить велику кількість формул, при кількості ОС більше 5000, може знадобитися відключати автоматичний підрахунок формул, особливо при інтенсивному введенні даних та обов'язково при копіюванні для початкового заповнення.

3. При кількості ОС до 2000 тисяч, ніякі додаткові оптимізації наданого зразку не потрібні, при кількості до 5000 скоріш за все також. При кількості ОС більше 5000 вам знадобляться деякі не складні оптимізації, як то скорочення історії аркушу ООС, або часткове переведення формул в значення. Якщо у вас є потреба в допомозі, або додаткова консультація звертайтесь в формі зворотнього зв'язку або задавайте питання в групу в Signal.

4. Особливістю роботи Смарт таблиць, навіть при відносно невеликій кількості даних є зависання при вставлення з буферу обміну діапазонів, якщо у вихідному файлі стоїть фільтр і рядки йдуть не один за одним. В такому випадку копіювати в ЕЖООС треба через проміжний файл: спочатку вставляти дані в проміжний файл, а потім щоб фільтру не було, з нього вже в смарт-таблицю ЕЖООС. В такому випадку операція буде виконуватися за секунди навіть на тисячі рядків. Якщо вам треба копіювати більше 5 000 рядків, і операція копіювання починає займати більше 30 секунд, копіюйте частинами по 1-2 тисячі рядків і вставлення буде проходити майже миттєво.

5. Також довго буде виконуватись або може призвести до зависання операція з видалення навіть невеликої кількості рядків в таблиці, якщо вони йдуть не підряд. Така операція виконується при відкритті табелю на новий місяць, що описано в другій частині. Обійти це можна наступним чином: перш ніж видаляти такі рядки, спочатку потрібно їх виділити кольором, очистити значення, потім відсортувати за кольором, щоб всі вони опинилися в кінці і вже потім видалити рядки. Цю операцію треба робити при відключенному автоматичному перерахунку формул.

6. Закриття файла відбувається відносно довго, це пов'язано з тим що в файлі багато формул і в Excel формується велике дерево зв'язків, яке знаходиться в пам'яті. А при закритті відбувається очищення кешу, і чомусь ця операція відбувається занадто довго. При кількості ОС більше 10000 це може займати декілька хвилин, ця поведінка є коректною і оптимізувати це на поточному етапі ніяк не вдалось.

Якщо ви плануєте виконувати потенційно довгу операцію або просто робити нетипові маніпуляції, перед тим як їх робити, зробіть копію файлу. Також для запобігання втрати даних регулярно робіть щоденну копію файлу на незалежний носій.

Читайте далі: Формування офіційної версії ЕЖООС згідно вимог 687 наказу МОУ.

Чи вдалося вам, дотримуючись інструкцій, зробити початкове заповнення? Так 👍 Ні 👎

Використання і підтримка ЕЖООС від Кубик безкоштовна для СОУ.

Для отримання шаблону надішліть запит на лінію підтримки в формі зворотнього зв'язку, посилання для скачування буде відправлено вам на електронну пошту протягом робочого дня.

Також посилання можно отримати в автоматичному режимі, якщо зробити донат "На Розвиток "Української непаперової". Посилання буде доступно одразу.

Інформація щодо новин і виходу оновлень шаблону на телеграм-каналі.
Підтримка і обговорення в групі Signal за посиланням.