Размер. Тегло. Сила на звука. Няма значение как ще го наречем. Когато даден файл е твърде тежък, той става твърде голям, това е проблем. Не можем да го изпратим по имейл, зареждането и запазването отнема много време. Това е особено досадно, когато правите резервни копия, тъй като отнема няколко секунди, когато мозъкът ни е мислил за производителност.

Историята

Онзи ден приятелят ми Quique ми каза, че е получил файл с прекалено голям размер. Когато неговият създател го генерира по негово време, той заема по-малко от 1 Mb, но сега заема почти 50 Mb. Какво се случи? Коментирайки по телефона, ние се насочваме към някои често заподозрени:

  • Има ли данни в модела на данните?
  • Има ли много фигури (например празни текстови полета) на листовете?

И как се прави това? Започваме с кирка и лопата.

Информация за модела на данните

Моделът на данни е информация, която може да е във файла, но не е на нито един лист. Той е отзад и за да видите тази информация, трябва да се свържете чрез динамични таблици или да работите директно в модела чрез мениджъра на модела на данни.

Понякога тези данни остават незабелязани, тъй като мениджърът на модела на данни е достъпен само от версията на Excel от 2013 г. Следователно, в предишни версии може да не е лесно намиране.

Въпреки че моделът на данни е много ефективен, той може да съхранява много информация, така че може да заема много място, генерирайки много големи файлове. За да разбера дали в модела на данни има информация, ми хрумва, че можем да направим две неща:

1. Поставете обобщена таблица. При вмъкване се създава диалогов прозорец, който, ако съществува, ви позволява да изберете модела на данни като източник на таблицата. Ако опцията за модел на данни не може да бъде проверена, това е, защото няма нищо.

excel
Вмъкване на обобщена таблица

2. Директен достъп до администратора на модела на данни. Това може да стане във версията за 2016 г. чрез раздела Данни, в групата „Инструменти за данни“. В предишни версии чрез раздела Power Pivot, който в някои версии е добавка, която трябва да бъде инсталирана.

Раздел Power Pivot Раздел Данни

В нашия случай файлът няма модел на данни, затова отидохме при следващия заподозрян в списъка.

Скрити (или видими) форми

Понякога откривам файл, който работи много бавно, с прекалено голям размер, който заема много мегабайта. След много търсене установих, че на един лист буквално има хиляди празни текстови полета. Но това ... откъде знаеш?

Е, има няколко начина. Единият е „на око“. Обичайната икона на мишката в електронна таблица е кръст. Ако имаме текстово поле и поставим курсора върху него, това, което ще получим, е икона, която прилича на главна буква I (като тази в Word). По-добре да го видим на снимка.

Това обикновено виждате в Excel:

Обичайна икона в Excel

Ето какво да видите дали има „натрапници“:

Икона, когато има текстово поле

По същия начин, ако има фигура или други неща, иконата ще бъде стрелка. Ако с алтернативния курсор щракнем, тогава нашият приятел става видим:

Видимо текстово поле

Ако имаме по-нова версия на Excel от 2007 г. (имаме го, нали?), Тогава можем да направим нашите обекти видими по друг начин: чрез панела за избор. Това може да бъде намерено във версията за 2016 г. под „Оформление на страницата“ на лентата в панела „организиране“. В предишните версии трябва да изберете обект, а бутонът за активиране на панела за избор е в раздела "Инструменти за рисуване" или "Графични инструменти". Панелът се появява вдясно и там имаме списък с всички обекти, които са на листа.

Ако има нещо, то ще бъде там.

Панел за избор - прозорец за избор на Excel 2016 във версии преди 2016 г.

Както можете да видите, с няколко copy-paste генерирах повече от 33 000 копия на същото празно текстово поле. Генерирането на кадри е експоненциално, така че бързо достига прекомерни суми, ако не го осъзнаем. Проблемът сега е да ги изтриете всички. Бихте ли дали ключа за изтриване 33 000 пъти? Аз не. За това знам VBA. Бих сложил код горе-долу по следния начин:

По този начин елиминирам всички фигури (текстови полета, но също и изображения, фигури и т.н.), които са на листа.

Това обаче не беше проблемът и в досието на нашия приятел. Панелът за избор беше празен, затова продължих да търся.

Условни формати, клетъчни формати, цветове ...

Листът имаше много формати от всякакъв вид. Условни, фонове на клетки, модели, цветове на фона и текста ... Опитах се да модифицирам това, защото нещо трябваше да бъде. Просто избрах целия лист, не сложих цвят на фона, линии, шаблони, приложих и запазих ... И размерът на файла беше практически същият. Това не беше така.

И се чудех как можете да разберете какво точно заема дисковото пространство ... И разбира се, имаше решение.

Вижте вътрешностите на .xlsx файл

Откакто Microsoft прие стандарта .xlsx, той преобразува Excel файлове в xml структурирани файлове. Това означава, че може да се чете по много начини и че много видове програми ще разберат структурата. Любопитното е, че собственият вносител на xml на Excel ни дава грешка. И защо е това?

Просто, защото Excel не запазва директно файла в xml формат, но също така го компресира в zip формат, така че да заема по-малко място. И така ... Ами ако се опитаме да сменим разширението на файла и да го отворим с компресор?

Предишни стъпки за промяна на разширението на файла

Предполагам, че всички знаем как да променим разширението на файл, но нека кажем как да го направим така или иначе. Ако вече знаете как да го направите, можете да преминете към следващото заглавие без проблеми.

Първото нещо е да отворите прозорец на Windows Explorer. Това може да стане бързо, като натиснете клавишите Windows + E.

Комбинация за отваряне на Windows Explorer

Прозорецът на Windows Explorer се отваря в „Моят компютър“. Там избираме панела „Преглед“ и вдясно от цялото имаме бутона Опции, където кликваме върху „Промяна на папката и опциите за търсене“.

Променете опциите на папката

Отваря се прозорец, в който избираме раздела „Преглед“ и след това превъртаме надолу, докато намерим „Скриване на разширенията на файлове за известни типове файлове“. Трябва да оставим отметката в квадратчето и да щракнем върху OK.

Показване на разширението на файла.

Сега променяме разширението на файла

След като това стане, отиваме в нашия .xlsx файл и го преименуваме. Заменяме .xlsx с .zip и приемаме всички съобщения на Windows.

Променете разширението на файла.

Както можете да видите, първо направих копие, за да запазя оригинала .xlsx (добре, в нашия случай това беше .xlsm, защото имаше макроси). И сега можем да отворим новия .zip файл във всеки компресор. В моя случай имам инсталиран WinRar, но всеки, който отвори .zip, е валиден, дори самият Windows Explorer.

Когато го отворите, ще видите структура на папка и файл. Оказва се, че нашият .xlsx файл всъщност представлява куп компресирани XML файлове и папки. Можем да навигираме в папките, за да видим какво заема място ... В нашия конкретен случай въведох папката „xl“ и вътре в папката „работни листове“. Сортирайки по размер, вече знаем кой лист е виновникът:

Преглед на Excel файл чрез WinRar.

И там ги имаме! Нашите виновници са листове 5 и 4 от нашия файл.

Разбиране към кой лист се отнася ...

Това, което се случва, е, че в нашия файл листовете не се наричат ​​„лист5“ или „лист4“. Те имат имена като „Корица“, „Индекс“, „Основни данни“ ...

Първият подход е да се обърнем към нашия редактор на VBA, тъй като знаем, че в панела на проекта те поставят имената ни в скоби, но отвън те все още се наричат ​​„Лист 1“, „Лист 2“ и т.н.

Структура на .xlsx файла, видяна в панела Project на VBE.

Има малко цензура, тъй като файлът не е мой и нямам изрично разрешение да го използвам. Запазил съм само родовите имена, защото това е доста добре разбрано. Имената в скоби са това, което виждаме в разделите на нашите листове. И „Лист 1“, „Лист 2“ и т.н. е вътрешното име на листата. Всъщност това е свойство, наречено "кодово име", което ни позволява известна лекота да работим с листовете. Но това не е името, на което нашата номенклатура съответства в zip файла. Ще се опитам да го изясня малко.

В кода можем да се позовем на лист по три начина: по името му, по кодовото му име и по неговия индекс. Бихме го направили така:

В този случай индексът е 1, а кодовото име е Sheet1. Но те не трябва да съвпадат. Всъщност в моя файл листът, наречен „Корица“, има индекс 2 и кодовото име „Sheet5“.

Е, номерът зад „лист“ в имената на файловете на нашия zip е индексът на всеки лист.

Решаване на случая: намаляване на размера на файла

И накрая, знаейки на кой лист трябва да действаме, и след като изхвърлих останалите заподозрени, просто отидох до последния ред с данни, избрах целия следващ ред и натиснах Ctrl + Shift + ↓, за да избера всички клетки до края от файла. След това щракнете с десния бутон и изтрийте. По същия начин отидох до последната колона с данни и направих същото, като избрах следващата колона и след това Ctrl + Shift + →, за да избера всички колони до края. Отново щракнете с десния бутон и изтрийте.

Изтриване на клетки.

Току-що направих това на двата листа, които изглежда имаха проблеми с размера в нашия zip файл. И листовете бяха избрани с помощта на VBA с командата, която използваше индекса от предишния раздел. След спасяването магията беше направена.

Тежи по-малко ...

Какво се беше случило?

Както вече обсъждах по-рано, в листовете се прилагаха много формати. Засенчени, райета, цветове ...

Изглежда, че когато клетката е празна и неформатирана, Excel я игнорира. Той не запазва никаква информация за него, защото е празен и има стандартните свойства. Но когато променим формата, Excel трябва да запише тази информация във файла. Ако премахнем форматирането отново, за Excel това вече е модифицирана клетка и продължава да я запазва, освен ако не го изтрием напълно.

При подготовката на листа производителят вероятно е избрал цели колони и редове за форматиране. Може би просто щракнете двукратно в долната част на страницата, без да осъзнавате, че не се нуждаете от толкова информация, съхранявана във файла. Повтаряйки това със стотици колони, теглото на файла се умножава.

Така че, ако сте стигнали толкова далеч, мога да ви кажа само да не прецветявате листите на Excel, ако не искате да се озовете с 40 мегабайта файл, без да знаете защо.