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

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

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

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

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

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

Оптимізувати ми будемо два параметра: швидкість обрахунку всіх формул і розмір файлу. Як визначити розмір файлу зрозуміло, просто зафіксуйте початкове значення, а для визначення швидкості підрахунку формул можна використати наступний макрос. Перед запуском макросу залиште відкритим тільки один файл ЕЖООС швидкість підрахунку якого треба визначити. Зробить замір швидкості перерахунку 5-7 разів, відкиньте найбільші і найменші значення.

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 МБ на 1000 рядків аркушу ООС, треба аналізувати, залежить від швидкості роботи і загального розміру файлу;
більше 5 мб на 1000 рядків аркушу ООС, треба оптимізувати.

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

Пошук вузького місця

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

Перш ніж оптимізувати треба зрозуміти, який саме аркуш викликає найбільше навантаження і скільки він важить.
Зробить копію файлу і всі дослідження робить на копії, а тільки коли отримаєте результат якій вам потрібен переносьте оптимізацію на робочий файл.

По черзі для кожного аркушу зробить наступне, виділить всю таблицю, скопіюйте в буфер обміну і вставьте з буферу як значення.
Збережіть файл і оцінить зміну розміру файлу і швидкість перерахунку файлу після заміни формул на значення.
Якщо показник розміру змінився більше ніж на 1-2МБ або час розрахунку зменшився більше ніж на 2-3 секунди, аркуш можна пробувати оптимізувати.

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

Оптимізація аркушу Статуси.

Особливістю аркушу Статуси є те, що він розростається найбільш швидко, і якщо ви ведете ЕЖООС рік, то його розмір навіть для не дуже великого підрозділу може бути доволі суттєвим. Є 2 способу оптимізації цього аркушу:
Спосіб 1.
Без втрати інформацї за попередні періоди.
Для цього вам необхідно виконати наступні кроки:

1. Відсортувати аркуш таким чином, щоб усі записи, які визначають поточний статус опинились знизу. Починаючи з версії 2.6 стовбці 490 та 491 записи які визначають поточний статус підсвічються зеленим кольором, тому вам достатньо відсортувати таблицю по одній із цих колонок по кольору, таким чином, щоб зелений колір був внизу.
2. Виділити усі стовбчики в яких є формули для "історичних" записів і замінити формули на значення. Після цього автозаливка жовтим має зникнути, файл зменшитись в розмірі і почати перераховуватись швидше.
3. Для додаткової оптимізації можна замінити також формулу пошуку індекса в стовбці 778 аркушу ООС з MATCH на XMATCH, щоб шукати у зворотньому порядку.

   =IF([@20]="";"";IFERROR(MATCH([@20];тСтатусиІсторія[491];0);кНеЗнайдено)) 
   замінити на
   =IF([@20]="";"";IFERROR(XMATCH([@20];тСтатусиІсторія[491];0;-1);кНеЗнайдено))

   В теорії оптимізація формули теж має дати якийсь невеликий сукупний ефект, але в своїх тестах я різницю не помітив, а вот пункти 1 і 2 впливають суттєво.

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

1. Встановить фільтр в колонці 455 дата по яку факт, значенням менше 01.01.2026.
2. Переконайтесь, що не має записів які впливають на поточний стан фільтр по колонці 491 Останній запис, всі рядки мають бути пусті.
3. Виділить усі відібрані записи і повністю очистить їх від інформації, не видаляючи сам рядок.
4. Візьмить будь яку колонку наприклад 9 ПІБ і залійте її кольором, який не використовується.
5. Зніміть фільтр і відсортуйте таблицю по колонці 9 ПІБ по кольору, щоб всі очищені записи попали донизу.
6. Видаліть записи які відмічені кольором в колонці 9 ПІБ.
   Цей спосіб є смисл використовувати коли кількість видаляємих записів буде більше 10000, на меншій кількості оптимізація не дасть ніякого суттєвого впливу, а ні на швидкість, а ні на розмір файлу і достатньо використовувати перший метод.

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

Оптимізація аркушу Переміщення.

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

1. Відсортувати аркуш, щоб усі записи, які визначають поточну посаду опинились знизу. З версії 2.6 стовбці 490 та 491 підсвічються зеленим кольором для актуальних записів, відсортуйте таблицю по одній із цих колонок по кольору, таким чином, щоб зелений колір був внизу.
2. Виділити усі стовбчики в яких є формули для записів, які не визначають поточну посаду скопіюйте в буфер і вставьте як значення.
3. Для додаткової оптимізації можна замінити також формулу пошуку індекса в стовбці 779 аркушу ООС з MATCH на XMATCH, щоб шукати у зворотньому порядку по аналогії з аркушом Статуси.

Особливістю роботи після скидання формул в аркушу посади є те, що рядки пов'язані між собою і у випадку, якщо робляться зміни в попердньому періоді виправлення помилок, або у зв'язку з скасуванням пунктів наказів, треба бути уважним і дивитись на попередні записи по людині яка коригується і за потреби коригувати їх вручну або відновлювати формулу в стовбці і потім повторити кроки 1-2.
Якщо процедура скидання формул виконується невперше, перед тим, як виконувати її, треба спочатку відновити формули для всього стовбця, а потім виконувати кроки 1-2. Також є смисл не скидати формули за якийсь період, наприклад останні 3-6 місяців, навіть якщо вже є наступні переміщення, це не вплине суттєво на швидкість роботи і розмір, але дозволить за потреби робити коригування в автоматичному режимі.

Оптимізація аркушу ООС

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

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

Наведу приклад, як відібрати і перенести історію статусів по виключеним військовослужбовцям з аркушу Статуси.

1. Встановить фільтр по колонці 777 Індекс ООС, всі значення які менше 0. Це і будуть військовослужбовці які звільнені.
2. Виділити ПІБ кольором, і відсортувати по кольору вгору(якщо записи будуть залишатися на аркуші), або вниз якщо записи будуть видалятися з поточної таблиці і переноситись в архів.
3. Перенести записи в архівний файл, або зняти формули і заливку.

З аркушем переміщення кроки аналогічні.
Оптимізація буде мати ефект, якщо видалятися буде велика кількість записів 1000+ тому цей крок оптимізації є смисл робити 1-2 рази на рік, коли накопичилась суттєва кількість виключених військовослужбовців і оптимізація попередніх кроків не дає достатнього результату.

Оптимізація аркушу Статистика

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

Напримклад формула без контролю перерахунку:
=COUNTIFS(тШПО[220];ПідрозділиБЧС&"*";тШПО[210];C$5;тШПО[214];кТак)

може бути замінена на формулу з контролем перерахунку:
=IF($A$1=кТак;COUNTIFS(тШПО[220];ПідрозділиБЧС&"*";тШПО[210];C$5;тШПО[214];кТак);0)

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

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

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

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 за посиланням.