„Помощ! Имам прекалено голям файл в Excel и не мога да работя с него. Моля те помагай!!!“ Не един и два пъти са ме търсили с подобна молба. И тук не говорим за файл с дани в 50 колони и 200 хиляди реда. Става въпрос за файл, в който на пръв поглед няма много данни, но той все пак е прекалено голям.
Нека да видим един пример
Файлът на долната картинка съдържа рецепта за традиционен български ошаф (много вкусен и полезен между другото, пробвайте рецептата).
Както виждате файлът съдържа 1 работен лист и малко текст на него. И въпреки това обемът му е ….. повече от 11 мегабайта!
И в повечето случаи, големият обем на файла е най-малкият проблем, който имаме. Самият файл се отваря много бавно, или когато опитаме да направим нещо просто като да копираме клетка забива често виждаме съобщение като това:
Да не говорим, че в много организации има ограничение на обема на файловете, които можем да изпратим по е-мейл.
Защо се случва всичко това? Възможни са буквално десетки причини, но в следващите редове ще ви покажа шест от тях, които по мои наблюдения се срещат най-често.
1. Краят е някъде на м…ного далеч
Като гледате картинката сигурно си мислите, че краят на работния лист е клетка К19, оцветена в жълто.
Нека проверим с помощта на CTR + End (тази клавишна комбинация избира последната клетка в работния лист)
Изведнъж се оказва, че последната клетка в работния лист е NAC500000. Съдейки по съдържанието, вероятно някой си прави шега, обхванат от коледно настроение. В практиката по-вероятно е, поради объркване или странна клавишна комбинация, да оставим някоя запетайка на много далечно място в работния лист.
„Чудо голямо!“ ще каже някой, но не е така. Това са почти 9500 колони и половин милион реда или точно 4,760,500,000 клетки. Дори и да са празни, Ексел трябва да запомни информация за тях. За това – след като се уверим, че тези клетки наистина не се използват, трябва да ги изтрием. И вижте – файлът вече е с почти 3 мегабайта по-малък!
Но разбира се, това все още е твърде много
2. Скрити шийтове
Те са много често срещана причина да имаме прекалено голям файл в Excel. Особено, когато това е някакъв стар отчет, създаден преди доста години. С времето към него са добавяни по различен повод работни листове, които вече не ни трябват. За по-бързо ги скриваме. Да не се виждат, пък ако ни потрябват да си ги имаме. И така малко по малко се натрупват и натежават. Затова – десен бутон върху работния лист и Unhide
Виждате, че действително имаме три скрити шийта. Знаете, че ако кликнем два пъти върху някой от тях, той ще се покаже. След това отново същата процедура за другите. В случая не е голяма болка, защото са само три. Но ако са повече, тогава вижте как може да ги покажете с малко усилия.
Нека да видим какво имаме в първия от тези скрити шийтове.
3. Ненужни стойности
В колона А до ред 250,000 са въведени единици.
Ако изтрием този шийт, ще видим, че тези 250,000 единици костват повече от мегабайт, което си е 10% от първоначалния обем на файла! Затова винаги трябва да се изтрива ненужната информация във всеки работен лист. Това включва също така текст, графики, ненужно форматирани клетки и т.н.
4. Скрити редове
По-интересен случай е вторият скрит шийт.
На пръв поглед му няма нищо, но обърнете внимание – има почти 500 хиляди скрити реда. Ако направим най-простото – да ги покажем, това смалява обема на файла с още над мегабайт. Отново – Ексел пази информация, че има 500,000 скрити реда, всеки с над 16,000 колони. Изводът – не скривайте много редове, ако не искате да имате много голям файл в Excel.
С колоните подобна щета почти не се усеща. Но все пак те са само 16 хиляди
5. Изображения
В третия скрит шийт някой е забравил няколко картинки
Картинките могат да бъдат голям консуматор на мегабайти. Затова е добре да ги изтриваме, но в случай че е необходимо да са там можем да направим следното:
Избираме произволно една от тях и отиваме на Picture Format > Compress Pictures
В това меню избираме най-високата възможна резолюция и махаме отметката от Apply only to this picture, за да може промяната, която ще направим да се приложи за всички изображения. След това даваме ОК и запазваме промените.
В случая печалбата не е особено голяма – шейсетина килобайта. Изглежда изображенията са били доре компресирани. Но понякога могат да бъдат дори десетки мегабайти. Този проблем (и съответно решение) може да имате и в Word, и най-вече PowerPoint.
6. Невалидни наименовани области (Named Ranges)
Това е една от най-неприятните причини за много голям файл в Excel поради факта, че за повечето хора е съвсем скрита. Обикновено човек се сеща да провери за скрити шийтове, за излишни картинки или стойности. Вече научихте, че дори скритите редове могат да увеличат обема на вашия файл драматично. Но за невалидни наименовани области кой би се сетил? Особено ако не ги е правил той. Всъщност те се откриват много лесно – просто натискаме CTRL + F3 и:
Установяваме, че нашият файл съдържа десетки, може би стотици такива наименовани области (вижте къде е курсорът горе вдясно и колко много място има до края долу). Много от тях са „счупени“ и показват грешка (#REF!). А други дори реферират към файлове в други папки и дори на друг компютър!
Ако искайте вярвайте, но във файла, който използвам за демонстрацията, има над 15,000 такива наименовани области. Няма лъжа. И става въпрос за съвсем реален файл, с който съм работил. И не, не съм ги броил на ръка, а съм използвал една от многобройните функционалности на Ексел, описана ето тук.
До момента не съм успял да намеря обяснения защо се получава този проблем с наименованите стойности. Предполагам е свързано с файлове, които са създадени отдавна, някои шийтове от тях са копирани в друг файл по някаква причина, после в трети, четвърти и т.н. Някои разширения за Ексел (add-ins) също създават множество наименовани области, за целите на изчисленията, които правят. Например вграденият Solver. Във всеки случай решението е изключително елементарно:
Просто отидете на най-горния ред и с клавишната комбинация SFIFT + CTRL + END маркирайте всички тези счупени имена и ги изтрийте с бутона Delete (става и с този от клавиатурата). Имайте предвид, че може да се наложи да почакате няколко секунди, минута може би. Зависи от броя на тези наименовани области
Резултатът е повече от впечатляващ – нашият файл вече е само 11 килобайта. Точно колкото и трябва да бъде с оглед на факта, че съдържа само една рецепта за вкусен ошав. Или с други думи – смалихме го около хиляда пъти!
***
Още малко идеи
Това бяха 6 от възможните причини, поради които може да имаме ненормално голям файл в Excel. По мои наблюдения те са най-често срещани, но ако дори и тяхното отстраняване не помогне може би проблемът се дължи на:
- Излишно много Conditional Formatting
- Прекалено много формули (особено VLOOKUP, INDIRECT, SUMIF, COUNTIF потребяват много памет)
- Пивот таблици
- Връзки към други файлове
- Форматът на файла – старият xls формат записва значително по-големи файлове от xlsx, а xlsb форматът има дори още по-голяма компресия от xlsx.