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

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

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

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

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

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

Оптимізувати ми будемо два параметра: швидкість перерахунку всіх формул і розмір файлу. Як визначити розмір файлу зрозуміло - просто зафіксуйте його початкове значення. А для визначення швидкості перерахунку формул в Excel можна використати наведений нижче макрос. Перед запуском макросу залиште відкритим тільки один файл Excel, швидкість перерахунку якого треба визначити. Зробіть замір швидкості перерахунку 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 МБ на 1 000 рядків аркушу ООС - нічого не робити;
3-5 МБ на 1 000 рядків аркушу ООС - треба аналізувати, дивитись на показник швидкості і загальний розмір файлу;
більше 5 МБ на 1 000 рядків аркушу ООС - треба оптимізовувати.

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

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

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

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

Для кожного аркуша по черзі виконайте такі дії:

• Виділіть всю таблицю, скопіюйте її в буфер обміну і вставте з буфера як значення.
• Збережіть файл і оцініть, як змінилися розмір файлу та швидкість перерахунку після заміни формул на значення.
  Якщо розмір файлу зменшився більш ніж на 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.
З видаленням інформації за попередні періоди.
Якщо розмір таблиці історії статусів стає занадто великим і оптимізації способом недостатньо, залишається лише скоротити історію статусів.
Визначте, за який період потрібно зберегти історію в архівній копії і прибрати з поточного файлу.
Для коректної роботи ЕЖООС необхідно, як мінімум, зберегти записи, які впливають на формування табеля, тобто записи починаючи з початку поточного місяця.

Для прикладу наведу кроки, як видалити записи за період до 01.01.2026.

1. Встановіть фільтр в стовпці 455 "Дата, по яку факт", вибравши значення менше 01.01.2026.
2. Переконайтеся, що серед відібраних записів немає жодного, який впливає на поточний стан фільтру в стовпці 491 "Останній запис" - всі рядки мають бути порожні.
3. Виділіть усі відібрані записи та повністю очистіть їх від інформації, не видаляючи самі рядки.
4. Візьміть будь-який стовпець в якому не має формули, наприклад, 253 "Дата по яку план", і залийте його кольором, який ніде не використовується.
5. Зніміть фільтр і відсортуйте таблицю за стовпцем 253 за кольором, щоб всі очищені записи опинилися внизу.
6. Видаліть записи, які позначені вибраним кольором у стовпці 253.
   Цей спосіб доцільно використовувати, коли кількість записів, що видаляються, перевищує 10 000. На меншій кількості оптимізація не матиме суттєвого впливу ні на швидкість ні на розмір файлу. У цьому випадку достатньо скористатися першим способом оптимізації.

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

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

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

Оптимізація аркушу переміщення без втрати інформації за попередні періоди.
Для цього необхідно виконати наступні кроки:

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

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

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

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

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

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

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

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

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

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

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

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

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

Оптимізація аркушів Посади, Підрозділи та ШПО

У великих підрозділах з часом аркуш Посади також може ставати відносно "важким", наприклад, після чисельних організаційно-штатних змін та\або переходу на новий штати. Загалом, незважаючи на велику кількість даних, цей аркуш обчислюється досить швидко і зазвичай не має суттєвого впливу на загальну швидкодію ЕЖООС.
За потреби можна відсортувати всі скорочені посади вниз та скинути формули для них. Також гарною практикою є використання копії файлу, у якій аркуш Посади залишається динамічним (із формулами), а до основного робочого файлу переноситься вже таблиці Посади та\або Підрозділи без формул. Такий підхід є простим і цілком ефективним.
Аналогічно на аркуші ШПО можна скинути формули в стовпцях, які відповідають за зв'язок з аркушем Посади: 220,221, 2,3,5, 210,214 та, насамперед, 771. Дані в цих стовпцях змінюються лише тоді, коли вносяться зміни до штату, але перед тим як це робити зробіть заміри, чи воно того варто.

Використання скороченої версії ЕЖООС

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

Ще декілька особливостей використання

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

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

3. Закриття файлу може займати тривалий час через очищення внутрішнього кешу Excel. Для файлів розміром понад 20 МБ процес закриття триває декілька хвилин. Така поведінка є коректною і не підлягає оптимізації. Щоб не чекати завершення закриття файлу, можна спочатку зберегти зміни, а потім завершити процес Excel через Диспетчер задач. Перед цим обов'язково переконайтеся, що всі інші відкриті файли Excel такж збережені або закриті, інакше їхні незбережені зміни буде втрачено, а при повторному відкритті почнеться діалог відновлення, що завжди не є добре.

4. Якщо один із файлів у Excel завис або тривалий час виконує операцію, а вам потрібно відкрити новий файл, не обов'язково чекати завершення поточного процесу. Можна запустити паралельно ще один процес Excel і відкрити потрібний файл в ньому. Для запуску нового процесу використовуйте ключ /x під час запуску програми. Для зручності можна створити окремий ярлик і вказати в ньому команду на кшталт наведеної нижче:
   "C:\Program Files\......\EXCEL.EXE" /x

Чи була Вам корисна інформація?

Так 👍 Ні 👎 Зворотній зв'язок 👋

Підтримати проект "Українська непаперова".

Попередні матеріали і рекомендації по веденню ЕЖООС від Кубик:

video_libraryВідеоогляд наданого приклада:

Частина 1. Електронний журнал обліку особового складу від Кубик.

Частина 2. Опис і особливості роботи аркушів Переміщення, Статуси і Табель

Версія ЕЖООС від Кубик 2.0. Довідник підрозділи і автоматичне формування повних назв посад.

Частина 3. Початкове заповнення даних і перехід на ЕЖООС від Кубик

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

Версія ЕЖООС від Кубик 3.0. Розрахунок додаткової нагороди і довідка про проходження служби.

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

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

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

Посилання будуть доступні користувачам DELTA або власникам корпоративних облікових записів Сил Оборони України.

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