Excel 2010: оптимізуємо розмір таблиці

Причини збільшення обсягу файлів криються у внутрішньому форматі подання документів 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 для цієї мети можна скористатися меню «Правка → Очистити → Формат».

У програмі Excel 2003 для цієї мети можна скористатися меню «Правка → Очистити → Формат»

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

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

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

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

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

4. Виберіть варіант «Спеціальна вставка ...». Відкриється вікно «Спеціальна вставка», як на рис. 4. В Excel 2003 вікно спеціальної вставки можна відкрити по команді «Правка → Спеціальна вставка».

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

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

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

видаляємо примітки

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

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

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

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.

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» - його несумісність з іншими додатками. Хоча, якщо подумати, а чи завжди потрібна ця сумісність? Так що сміливіше використовуйте перераховані формати таблиць в своїй роботі, і обсяг документів на вашому комп'ютері скоротиться в рази.

На сьогодні все. Успішної роботи! Чекаю на ваші листи, пропозицій і зауважень на [email protected] , [email protected] або на форумі редакції.

Помітілі помилку? Віділіть ее та натісніть Ctrl + Enter, щоб повідоміті нас про це

Виникає питання: звідки вони можуть узятися?
Хоча, якщо подумати, а чи завжди потрібна ця сумісність?
Помітілі помилку?