Excel 2010: оптимізуємо розмір таблиці
 | Розпочну з цитати з одного з листів наших читачів, яка звучить так: «Шановні співробітники «Б & K»! Як і більшість моїх колег, я користуюся програмою MS Excel. Нещодавно, створюючи резервні копії документів, я помітив, що деякі файли стали займати дуже багато місця. Тобто первісно обсяг їх був невеликий, а з часом чомусь виріс у декілька разів! Підкажіть, що стало причиною такого збільшення, і як повернути розмір таблиці MS Excel в нормальний стан?..» Що можна сказати з цього приводу? Сучасний комп’ютер, звісно, не той, що десять років тому. Обсяг пам’яті, ємність жорсткого диска, швидкодія — прогрес у цьому плані не може не вражати. Проте, як би там не було, усі ці ресурси потрібно використовувати з розумом. І в першу чергу стежити за тим, що являють собою ваші дані, та чи немає в них нічого зайвого. Особливо це важливо для бухгалтера, який зберігає величезну кількість документів у форматі MS Excel. Як правильно працювати з такими документами, як запобігти необґрунтованому збільшенню їх обсягу, поговоримо в цій статті. Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства |
Причини збільшення обсягу файлів криються у внутрішньому форматі зображення документів MS Excel. Сам по собі цей формат є загадковим. Докладно його ми, звісно, не розглядатимемо — не бухгалтерське це завдання. А от на практичних прийомах скорочення файлів в MS Excel я пропоную зупинитися детальніше. Здебільшого такі прийоми дозволяють не лише зменшити обсяг простору, що займають документи Excel, а й прискорити їх обробку. А це — важливий фактор при роботі з великими базами даних, які в бухгалтерській практиці зустрічаються буквально на кожному кроці. Розпочнемо безпосередньо з електронної таблиці.
Оптимізуємо робочий лист
Отже, зараз наше завдання — розібратися, які елементи робочого листа MS Excel можуть збільшити розмір файлу і що можна зробити в такій ситуації. Спершу попрацюємо з документом, де є тільки дані та розрахункові формули. Я вибрав як приклад базу, фрагмент якої показано на рис. 1. У ній рівно 5000 записів, надано документ у форматі Excel 2003 з розширенням «*.xls». База містить сім полів, займає близько 367 Кбайт, і цю цифру я пропоную запам’ятати. Тепер робимо так.
1. Стаємо в кінці таблиці. Натискуємо комбінацію «Ctrl+↑» (переходимо в кінець робочого листа). У стандартному форматі MS Excel це рядок з номером 65536.
2.У будь-якій комірці цього рядка змінюємо формат. Наприклад, виділяємо комірку жовтим фоном.
3. Зберігаємо документ та дивимося на зміст диска. Розмір файлу збільшився, тепер він становить 445Кбайт. Здавалося б, небагато. Проте, приріст склав близько 21%.
4. Видаляємо форматування і знову зберігаємо файл. Обсяг документа не змінився, — він залишився більше початкового.
Важливо! При збереженні файлу Excel запам’ятовує не лише реально заповнені комірки, а й раніше використовуваний діапазон. При очищенні комірок Excel не виключає їх із цього діапазону та запам’ятовує у файлі більше даних, ніж є в документі насправді.
Вирішити цю проблему дуже просто.
Порада Видаліть усі непотрібні (у тому числі — порожні!) рядки та стовпці, розташовані праворуч і знизу від таблиці, та перезбережіть документ. Таблиця стане компактніше.
Зверніть увагу: складне, багатоколірне форматування, умовне форматування помітно збільшують розмір файлу. Щоб файл займав менше місця, залиште в ньому тільки найнеобхідніше. Зайві формати видаліть. В Excel 2010 для цього зробіть так:
1. Виділіть блок комірок.
2. На стрічці «Главная» (рис. 2) натисніть кнопку «Очистить».
3. Із запропонованого меню виберіть потрібний варіант «Очистить форматы» (рис. 2). У програмі Excel 2003 для цієї мети можна скористатися меню «Правка → Очистить → Формат».
Ставимо значення
замість формул
Другим важливим фактором скорочення документа є заміна формул їх значеннями. Зрозуміло, такий прийом можливий не завжди. Проте, у реальній роботі часто трапляються ситуації, коли в якийсь момент формули в таблиці вже не потрібні. Проте вони залишаються, зберігаються разом із документом. І не лише займають зайвий простір, а й уповільнюють обробку таблиці. Щоб позбавитися від непотрібних формул, зробіть так:
1. Виділіть ділянку таблиці, де можна видалити формули.
2. Скопіюйте фрагмент в буфер обміну (комбінація «Ctrl+C»).
3. Не знімаючи виділення, клацніть по значку випадного списку іконки «Вставить» головного меню програми. Відкриється меню, як наведено на рис. 3.
4. Виберіть варіант «Специальная вставка…». Відкриється вікно «Специальная вставка», як наведено на рис. 4. В Excel 2003 вікно спеціальної вставки можна відкрити за командою «Правка → Специальная вставка)».
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 автоматично відключить цей параметр для всіх таблиць, що залишилися.
І ще. Отримати доступ до параметра «Создавать таблицу на основе данных, находящихся:» можна тільки при використанні Майстра зведених таблиць. В Excel 2003 — це основний інструмент для створення зведених звітів. Але в Excel 2010 це не так. Вставити зведену таблицю можна по-різному. Наприклад, через меню «Вставка» (іконка «Сводная таблица»). А от окремої кнопки для виклику Майстра тут немає, її потрібно додати на стрічку вручну. Найпростіше це зробити так:
1. Клацніть по панелі швидкого доступу правою кнопкою миші.
2. Із контекстного меню виберіть пункт «Настройка панели быстрого доступа…» (рис. 8). Відкриється вікно «Параметры Excel», як наведено на рис. 9.
3. Перемикач «Выбрать команды из:» поставте в положення «Все команды».
4. У списку команд підсвітіть елемент «Мастер сводных таблиц и диаграмм».
5. Натисніть кнопку «Добавить>>» (рис. 9).
6. У вікні «Параметры Excel» натисніть «ОК».
На панелі швидкого доступу з’явиться іконка для виклику Майстра зведених таблиць.
Картинки
Наявність графічних зображень у робочій книзі відчутно збільшує розмір документа. Особливо, коли зображень чимало і вони надані з високим розділенням. Наприклад, після звичайного сканування документа може виявитися, що рисунок записано з розділенням 300 dpi (300 крапок на дюйм). Фотоапарат або камера сучасного мобільного телефону здатна створювати зображення набагато більших розмірів. Проте що найголовніше — в офісній роботі це абсолютно ні до чого. Тому варто стиснути рисунки в документі, зменшивши розділення до 150, а то й до 96 dpi. На екрані (та і при звичайному друкуванні) втрати в якості будуть непомітні, а файл стане набагато менше. Щоб стиснути рисунки у програмі Excel 2010, зробіть так:
1. Відкрийте документ, перейдіть на лист, де є рисунки.
2. Клацанням миші виділіть будь-який рисунок. У головному меню програми з’явиться додатковий пункт «Робота з рисунками», а стрічка набере вигляду, як наведено на рис. 10.
3. У групі «Изменение» клацніть по кнопці «Сжать рисунки». Відкриється вікно «Сжатие рисунков», як наведено на рис. 11.
4. Перемикач «Качество вывода:» поставте в положення «Экран (150 пикселей на дюйм):…» або «Электронная почта (96 пикселей на дюйм):…» — залежно від бажаного ступеня стиснення.
5. Якщо в документі є декілька рисунків, відключіть прапорець «Применить только к этому рисунку».
6. Уключіть прапорець «Удалить обрезанные области рисунков». У цьому випадку всі частини зображення, які не видно на екрані, буде видалено.
7. У вікні «Сжатие рисунков» натисніть «ОК».
Важливо! У програмі Excel 2003 кнопка стиснення рисунків розміщена на панелі інструментів «Настройка изображения». Ця панель активізується після вибору будь-якого рисунка в робочій книзі.
Прибираємо іменовані діапазони
Що стосується використання іменованих діапазонів, я б сказав так. З одного боку — це дуже зручний засіб для написання формул. Імена роблять формули простіше і зрозуміліше. Але, як і слід було очікувати, велика кількість іменованих діапазонів призводить до зростання обсягу файлу. Особливо, якщо кордони діапазону динамічні, тобто визначаються за допомогою формул. У будь-якому разі варто уважно переглянути список діапазонів та відмовитися від тих, які не відіграють ключової ролі у вашому конкретному документі. Для коригування списку діапазонів зробіть так:
1. Відкрийте документ, натисніть комбінацію «Ctrl+F3». Відкриється вікно «Диспетчер имен».
2. У цьому вікні виберіть елементи для видалення та натисніть кнопку «Удалить».
Відключаємо журнал змін
У певних ситуаціях документи MS Excel оголошують з параметрами загального доступу. Нагадаю, що в MS Excel 2010 управління загальним доступом знаходиться на вкладці «Рецензирование», іконка «Доступ к книге». У програмі MS Excel 2003 такий доступ можна відкрити через меню «Сервис → Доступ к книге».
Сама по собі така можливість дуже корисна, оскільки в цьому випадку коригувати та переглядати документ можуть декілька осіб одночасно. Водночас загальний доступ може стати причиною істотного збільшення обсягів електронних таблиць. Річ у тім, що для файлів із загальним доступом Excel пам’ятає всю історію змін документа — хто, коли і як коригував комірки робочої книги. За умовчанням ці відомості зберігаються протягом 30 днів. Тому при активній роботі з таблицею її обсяг може швидко збільшуватися. У моїй практиці, наприклад, була таблиця обсягом близько 8 Мбайт, яка за кілька тижнів виросла до розміру майже 240 Мбайт!
Порада Не використовуйте загальний доступ до документа без нагальної на те необхідності.
Якщо загальний доступ все-таки потрібний і скасувати його не можна, скоротіть кількість днів зберігання даних у журналі з історією змін. Щоб розібратися з параметрами загального доступу, зробіть так.
1. Увійдіть до меню «Рецензирование», клацніть по іконці «Доступ к книге» (у Excel 2003 це меню «Сервис → Доступ к книге»).
2. У вікні «Управление доступом к файлу» відключіть прапорець «Разрешать изменять файл нескольким пользователям одновременно…» (рис. 12). Тим самим ви відключите загальний доступ до документа. Журнал змін Excel не вестиме. Обсяг документа стане менший.
Щоб обмежити кількість записів у журналі змін, зробіть так.
1. Через меню «Рецензирование» відкрийте вікно «Управление доступом к файлу» (в Excel 2003 — це «Сервис → Доступ к книге»).
2. Перейдіть на закладку «Подробнее» та зменшіть кількість днів зберігання історії змін (рис. 12).
Видаляємо макроси
та форми
Ще один спосіб скоротити розмір робочої книги — видалити з неї непотрібні(!) макроси та форми. Виникає запитання: звідки вони можуть взятися? Причин чимало. Найпоширеніша — створення робочої книги з наявного шаблона або документа, де вже були ці самі макроси та форми. Оскільки для вашої роботи вони абсолютно ні до чого, я пропоную позбутися такого «подарунка» без жалю. Тим більше, що будь-який макрос — це додаткове джерело для вірусних атак. Щоб видалити зайві макроси та форми, зробіть так:
1. Знаходячись на робочому листі, натисніть «Alt+F11». Відкриється редактор Visual Basic.
2. У головному меню виберіть команду «View → Project Explorer». Відкриється вікно «Project Explorer» з переліком усіх модулів та форм, які є в робочій книзі.
3. Клацніть кнопкою на модулі або формі, яку збираєтеся видалити.
4. Із контекстного меню виберіть «Remove».
5. У вікні з пропозицією експортувати текст модуля в окремий файл виберіть «No».
У програмі Excel 2010 є простіший спосіб видалення макросів та форм. Зробіть так.
1. Відкрийте документ.
2. У меню «Файл» виберіть команду «Сохранить как…». Відкриється вікно Провідника.
3. У цьому вікні клацніть по параметру «тип файла:» та вкажіть варіант «Книга Excel (*xlsx)» (документ без підтримки макросів).
4. Збережіть документ. Усе! Макроси та форми робочої книги «помруть» автоматично.
Як альтернативний варіант — можна відключити макроси при завантаженні файлу та заново зберегти документ.
Застосовуємо нові
формати MS Excel 2010
Цей спосіб оптимізації робочої книги актуальний тільки для користувачів MS Excel 2007(2010). Річ у тім, що в цих програмах з’явилися нові формати для збереження файлів, які дозволяють стиснути документ. Я б звернув увагу на три таких формати:
— «*.xlsx» — стандартний спосіб зберігання документів в Excel 2007(2010). Файли в цьому форматі надані в запакованому вигляді. Їх розмір порівняно з тими ж документами в Excel 2003 може скоротитися у 5 — 7 разів;
— «*.xlsm» — розширений варіант формату «*.xlsx» з можливістю збереження макросів;
— «*.xlsb» — двійковий спосіб зображення документа. Цей формат забезпечує мінімальний розмір файлу при максимальній швидкості доступу до даних. Єдиний недолік формату «*.xlsb» — його несумісність з іншими додатками. Хоча, якщо подумати, а чи завжди потрібна ця сумісність? Отже, сміливіше використовуйте перелічені формати таблиць у своїй роботі, і обсяг документів на вашому комп’ютері скоротиться в рази.
На сьогодні все. Успішної роботи! Чекаю ваших листів, пропозицій та зауважень на bk@id.factor.ua , nictomkar@rambler.ru або на форумі редакції.
