Excel 2018 оптимізуємо розмір таблиці, сайт для бухгалтерів бюджетних установ

Почну з цитати з листа одного нашого Новомосковсктеля, яка звучить так: «Шановні співробітники« Б K »! Як і більшість моїх колег, я користуюся програмою MS Excel. Нещодавно, створюючи резервні копії документів, я помітив, що деякі файли стали займати дуже багато місця. Тобто спочатку їх обсяг був невеликий, а згодом чомусь виріс у декілька разів! Підкажіть, що стало причиною такого збільшення і як повернути розмір таблиці MS Excel в нормальний стан? Що можна сказати з цього приводу? Сучасний комп'ютер, звичайно, не той, що десять років тому. Обсяг пам'яті, місткість жорсткого диска, швидкодія - прогрес в цьому плані не може не вражати. Але як би там не було, всі ці ресурси потрібно використовувати з розумом. І в першу чергу - стежити за тим, що являють собою ваші дані, і чи немає в них нічого зайвого. Особливо це важливо для бухгалтера, який зберігає величезну кількість документів в форматі MS Excel. Як правильно працювати з такими документами, як попередити необґрунтоване збільшення їх обсягу - ми розглянемо в цій статті.

Причини збільшення обсягу файлів криються у внутрішньому форматі подання документів MS Excel. Сам по собі цей формат загадковий. Детально розглядати його ми, звичайно ж, не будемо - не бухгалтерська це завдання. А ось на практичних прийомах скорочення файлів в MS Excel я пропоную зупинитися докладніше. У більшості випадків такі прийоми дозволяють не тільки зменшити обсяг займаного документами Excel простору, але і прискорити їх обробку. А це - важливий фактор при роботі з великими базами даних, які в бухгалтерській практиці зустрічаються буквально на кожному кроці. Почнемо безпосередньо з електронної таблиці.

Оптимізуємо робочий лист

1. Стаємо в кінці таблиці. Натискаємо комбінацію «Ctrl + ↓» (переходимо в кінець робочого листа). У стандартному форматі MS Excel це рядок з номером 65536.

2. У будь-якому осередку цього рядка змінюємо формат. Наприклад, виділяємо клітинку жовтим фоном.

3. Зберігаємо документ і дивимося на зміст диска. Розмір файлу збільшився, тепер він становить 445 Кбайт. Здавалося б, небагато. Проте, приріст склав близько 21%.

4. Видаляємо форматування і знову зберігаємо файл. Обсяг документа не змінився - він як і раніше залишився більше вихідного.

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

Вирішити цю проблему дуже просто.

Рада Видаліть всі непотрібні (в тому числі - порожні!) Рядки і стовпці, розташовані праворуч і знизу від таблиці, і перезберегти документ. Таблиця стане компактніше.

1. Виділіть блок осередків.

2. На стрічці «Головна» (рис. 2) натисніть кнопку «Очистити».

ставимо значення
замість формул

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

1. Виділіть ділянку таблиці, де можна видалити формули.

2. Скопіюйте фрагмент в буфер обміну (комбінація «Ctrl + C»).

3. Не знімаючи виділення, клацніть на значку списку іконки «Вставити» головного меню програми. Відкриється меню, як на рис. 3.

5. У цьому вікні виберіть варіант «Вставка» в положення «Значення».

6. Натисніть «ОК».

У складних таблицях може заплутатися навіть навчений досвідом бухгалтер. Не кажучи вже про ситуації, коли документ розробляє одна людина, а користуватися ним будуть інші. Тому хорошим стилем роботи в Excel є додавання приміток до неочевидним фрагментами документа. І тут, як то кажуть, все добре в міру. Зайві примітки теж займають місце, без них таблиця буде компактніше. Щоб видалити примітки, робимо так:

1. Відкриваємо документ, знаходимо осередок з приміткою.

2. Клацаємо на цій комірці правою кнопкою мишки, відкриється контекстне меню, як на рис. 5.

3. З цього меню вибираємо «Видалити примітку».

зменшуємо розмір
зведених таблиць

Зведені таблиці - одна з найпоширеніших причин збільшення розмірів файлу в MS Excel. Тут ситуація така. При створенні зведеної таблиці Excel формує спеціальну базу даних (так званий «внутрішній кеш», або «кеш зведеної таблиці») і зберігає її разом з документом. По суті, внутрішній кеш - це заготовка для формування підсумкових звітів самої різної структури. У ній зберігається інформація про доступні полях у вихідній базі, можливі зв'язки між ними, проміжні підсумки і інші обчислювані елементи. Основний плюс роботи з кешем - це висока продуктивність. При коригуванні структури зведеного звіту Excel робить всю роботу з нуля - більшу частину інформації він бере з кешу в готовому вигляді. Але це не все. У бухгалтерській практиці часто формують кілька зведених таблиць по одним і тим же вихідним даним. У такій ситуації передбачена можливість використовувати для кожної нової таблиці один і той же кеш. Це дуже зручно. Наприклад, при відновленні зведеної таблиці Excel автоматично оновить всі звіти, побудовані на одному кеші. Крім того, використання спільної робочої бази дозволяє істотно скоротити обсяг документа.

Однак робота з кешем має і свої недоліки. Справа в тому, що розмір кеша може перевищувати обсяг самого звіту, причому в кілька разів! А це призводить до різкого збільшення документа. У той же час, потужність сучасного комп'ютера дозволяє перерахувати зведений звіт з декількох десятків тисяч записів за лічені секунди. Так що виграш від використання кеша в плані продуктивності відносно невеликий. Тому в багатьох випадках має сенс взагалі відмовитися від роботи з кешем, і тим самим скоротити розмір документа. Подивимося, що потрібно зробити, щоб не зберігати кеш в зведеній таблиці. Робимо так:

1. Відкриваємо документ, стаємо на будь-який елемент всередині зведеного звіту.

2. Клацаємо правою кнопкою мишки, відкриється контекстне меню, як на рис. 6.

3. У цьому меню вибираємо пункт «Параметри таблиці ...». Відкриється вікно налаштувань, як на рис. 7.

4. У ньому переходимо на закладку «Дані» і відключаємо прапорець «Зберігати вихідні дані разом з файлом».

5. Натискаємо «ОК».

Все, тепер в документі буде зберігатися тільки зведений звіт. Пару слів про ефективність цього методу. Для бази даних, зображеної на рис. 1, я побудував зведений звіт щодо обсягів реалізації товарів по датам. Тобто в звіті фігурували три поля: дата реалізації, найменування товару та сума. Документ разом зі зведеною таблицею зайняв 1489 Кбайт. Після того як я перебудував звіт і видалив з нього кеш, документ скоротився до 982 Кбайт. В даному випадку тільки на одній зведеній таблиці ми заощадили близько 35% від загального обсягу документа. Непоганий результат.

Говорячи про оптимізацію зведених звітів, хочу звернути увагу на такий момент. Важливу роль в цьому питанні відіграє те, як була вказана область вихідних даних для побудови звіту. Наприклад, в базі на рис. 1 я можу точно вказати частину робочого листа, по якій буде формуватися зведена таблиця. Наприклад, це може бути блок «A1: G5001» (нагадаю, що в базі я залишив 5000 записів, плюс один рядок на заголовок). Але в цьому випадку при додаванні записів в основну базу вони не будуть потрапляти в зведений звіт. І це - явний мінус. З іншого боку, в такому підході є і позитивний момент: зведена таблиця буде займати небагато місця. На практиці найчастіше надходять інакше - вказують в якості вихідних даних область колонок. Наприклад, для бази на рис. 1 це можуть бути колонки «A: G». Так надходять для того, щоб при додаванні нових рядків у таблицю вони автоматично потрапили до зведеного звіту. В цьому випадку зведена таблиця займе набагато більше місця.

І останній момент щодо зведених звітів. Я хотів би повернутися до питання, коли в одній робочій книзі є кілька зведених таблиць і всі вони побудовані за одним набору вихідних даних. В цьому випадку ви можете вказати, що зведені таблиці (крім першої) будуються на основі вже існуючого кеша. Для цього на першому етапі роботи з Майстром таблиць потрібно встановити перемикач «Створювати таблицю на основі даних, що знаходяться:» в положення «в інший зведеної таблиці або зведеної діаграми» (рис. 7). І тоді MS Excel створить кеш тільки для однієї таблиці, а інші примірники зведених звітів будуть просто використовувати цей кеш. При великій кількості таблиць економія місця може бути воістину колосальною.

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

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

1. Клацніть по панелі швидкого доступу правою кнопкою мишки.

2. З контекстного меню виберіть пункт «Налаштування панелі швидкого доступу ...» (рис. 8). Відкриється вікно «Параметри Excel», як на рис. 9.

3. Перемикач «Вибрати команди з:» поставте в положення «Усі команди».

4. У списку команд підсвітите елемент «Майстер зведених таблиць і діаграм».

5. Натисніть кнопку «Додати >>» (рис. 9).

6. У вікні «Параметри Excel» натисніть «ОК».

На панелі швидкого доступу з'явиться іконка для виклику Майстра зведених таблиць.

1. Відкрийте документ, перейдіть на лист, де є малюнки.

2. Клацанням мишки виділіть будь-який малюнок. У головному меню програми з'явиться додатковий пункт «Робота з малюнками», а стрічка набуде вигляду, як на рис. 10.

3. У групі «Зміна» клацніть на кнопці «Стиснути малюнки». Відкриється вікно «Стиснення малюнків», як на рис. 11.

5. Якщо в документі є кілька малюнків, вимкніть прапорець «Застосувати тільки до цього малюнку».

6. Увімкніть прапорець «Видалити обрізані області малюнків». В цьому випадку всі частини зображення, які не видно на екрані, будуть видалені.

7. У вікні «Стиснення малюнків» натисніть «ОК».

Прибираємо іменовані діапазони

1. Відкрийте документ, натисніть комбінацію «Ctrl + F3». Відкриється вікно «Диспетчер імен».

2. У цьому вікні виберіть елементи для видалення і натисніть кнопку «Видалити».

відключаємо журнал
змін

Сама по собі така можливість дуже корисна, тому що в даному випадку коригувати і переглядати документ можуть декілька людей одночасно. У той же час загальний доступ може стати причиною істотного збільшення обсягів електронних таблиць. Справа в тому, що для файлів із загальним доступом Excel пам'ятає всю історію змін документа: хто, коли і як коригував осередки робочої книги. За замовчуванням ці відомості зберігаються протягом 30 днів. Тому при активній роботі з таблицею її обсяг може швидко збільшуватися. У моїй практиці, наприклад, була таблиця обсягом близько 8 Мбайт, яка за пару тижнів виросла до розміру близько 240 Мбайт!

Рада Не використовуйте загальний доступ до документа без явної на те необхідності.

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

2. У вікні "Керування доступом до файлу» вимкніть прапорець «Дозволити змінювати файл декільком користувачам одночасно ...» (рис. 12). Тим самим ви вимкніть загальний доступ до документа. Журнал змін Excel вести не буде. Обсяг документа стане менше.

Щоб обмежити кількість записів в журналі змін, зробіть так:

2. Перейдіть на закладку «Детальніше» та зменшите кількість днів зберігання історії змін (рис. 12).

Видаляємо макроси і форми

Ще один спосіб скоротити розмір робочої книги - видалити з неї непотрібні (!) Макроси і форми. Виникає питання: звідки вони можуть узятися? Причин багато. Найпоширеніша - створення робочої книги з наявного шаблону або документа, де вже були ці самі макроси і форми. Оскільки для вашої роботи вони абсолютно ні до чого, я пропоную розлучитися з таким «подарунком» без жалю. Тим більше що будь-який макрос - це додаткове джерело для вірусних атак. Щоб видалити зайві макроси і форми, зробіть так:

2. У головному меню виберіть команду «View → Project Explorer». Відкриється вікно «Project Explorer» з переліком всіх модулів і форм, які є в робочій книзі.

3. Клацніть кнопкою на модулі або формі, яку збираєтеся видалити.

4. З контекстного меню виберіть «Remove».

5. У вікні з пропозицією експортувати текст модуля в окремий файл виберіть «No».

1. Відкрийте документ.

2. У меню «Файл» виберіть команду «Зберегти як ...». Відкриється вікно Провідника.

3. У цьому вікні клацніть на параметрі «тип файлу:» і вкажіть варіант «Книга Excel (* xlsx)» (документ без підтримки макросів).

4. Збережіть документ. Усе! Макроси і форми робочої книги «помруть» автоматично.

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

- «* .xlsm» - розширений варіант формату «* .xlsx» з можливістю збереження макросів;

- «* .xlsb» - двійковий спосіб представлення документа. Цей формат забезпечує мінімальний розмір файлу при максимальній швидкості доступу до даних. Єдиний недолік формату «* .xlsb» - його несумісність з іншими додатками. Хоча, якщо подумати, а чи завжди потрібна ця сумісність? Так що сміливіше використовуйте перераховані формати таблиць в своїй роботі, і обсяг документів на вашому комп'ютері скоротиться в рази.

Зберегти собi НА сторiнка