Как уменьшить размер файла и ускорить его в Excel

Подробный разбор всех проблем, отрицательно влияющих на размер и скорость работы файлов Excel. Подробная статья www.planetaexcel.ru/techniques...
Заходите в гости ↓↓↓
Twitter / planeta_excel
Facebook / planetaexcel
Instagram / planetaexcel
Мои книги planetaexcel.ru/books/
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru/plex/

[@yaraslav2906]

Как и большинство слушателей Ваших "видео-курсов" не устаю благодарить за очень информативные,содержательные и профессиональные уроки.Николай,думаю, не будет преувеличением сказать,что Вы задаете уровень уроков по Excel на канале.

[@planetaexcel]

+Yara Slav Спасибо! :)

[@yurii_svetlov]

1. Рабочая область листа. 2. Печатные разрывы страниц. 3. Избыточное условное форматирование. 4. Лишние стили. 5. Внешние ссылки и формулы. 6. Именованные диапазоны. 7. Кэш сводных таблиц. 8. Подключение к внешним базам данных. 9. Изображения и автофигуры. 10. Ненужные макросы и формы. 11. Скрытые и суперскрытые листы. 12. Новые форматы файлов.

[@y_lesia5836]

Только первый шаг с рабочей областью позволил уменьшить файл с 23Мб до 5Мб. А сколько еще непознанного нами...)) Огромное спасибо за Ваши уроки!

[@user-tu3xr7do9b]

А у меня с 22 МБ до 2,9 МБ😁

[@user-zh8lz7tw8k]

Огромное спасибо! Из файла 157 мб сделал 150кб путем удаления строк 😂👍 даже мысли не было что это так влияет.

[@user-xv3oj5ze9j]

Здорово! Я с вашей помощью уменьшила файл со 128 до 53 Мбайт!!! Спасибо, умный человек!

[@user-di4wn3zo8h]

Дополнение из практики по удалению именованных диапазонов в диспетчере имен (13:02). Если имен в книге больше 65 тысяч (я видел это своими глазами), то диспетчер имён не откроется, реакции на нажатие не будет. Предположительно это вызвано превышением ограничений, на которые рассчитан Excel, от чего диспетчер или весь Excel целиком намертво зависают. По этой же причине файл может повисать на 10-15 минут при открытии, если в excel настоено доверие к этому файлу. При попытке открытия программа будет проверять доступ к каждой из тысяч ссылок, и отвиснет только когда проверит их все. Решения из интернета по удалению этих имён и ссылок тоже не сработают, потому что макросы будут тоже рушить эксель при попытке обратиться к списку имён. В этом случае нужно использовать макрос, удаляющий по одному имени за цикл, не вызывая при этом весь список. Это ужасно долгий процесс, желательно оставить макрос работать на ночь. Отдельно сообщу, что в excel существуют скрытые имена, которые не видно в диспетчере имён, и они тоже плодятся и раздувают файл так же как открытые - переносятся из внешних программ, копируются с других книг и так далее. Они же кстати часто являются причиной того, что связь с другим файлом разорвать не удаётся. Как работать с ними это отдельная тема, потому что там есть и системные имена, которые трогать не нужно.

[@decore1036]

"системные имена, которые трогать не нужно" - это ссылки/имена shape'ов? Если нет, то что это? Я много раз удалял все скрытые имена без какого-то видимого эффекта.

[@val_k]

Николай, огромное спасибо за Ваше видео. Всё очень доступно!

[@user-xp1ok3oo7p]

Огромное спасибо за Ваши уроки!

[@user-wj9ih4vj4x]

Огромное Вам спасибо Николай!!!

[@evgeniyelfimov5995]

Спасибо, друг, за работу. Ты крут !!!

[@Shurr83]

Супер! 26 минут полезной информации!

[@olgalo__artist]

Очень информативно и полезно! Кое-что не знала! За ваши видео я бы миллион лайков поставила 👍

[@aleksn7304]

Детальный разбор вопросов ваше всё. Спасибо.

[@user-ek9wy9mm2y]

Огромное спасибо! Прочитал вашу статью с этим видео на ПланетеExcel и нажатием нескольких клавиш уменьшил рабочий файл с 130 Мб до 400 Кб !!! Сохранение файла теперь "летает". Причина размера была в лишних ячейках (использую не больше 600 строк, а таблицы копировались вплоть до миллион-с-чем-то строки).

[@pulse194]

всегда полезно и интересно вас слушать...

[@baramasha]

Николай, спасибо за советы, как всегда очень полезно!

[@planetaexcel]

+Maria Baranova Спасибо, Мария!

[@wovan84]

Ваше видио стало первым в плейлисте Работа👍🏻

[@gaddar78]

Спасибо! Очень полезное видео!

[@pulse194]

Николай, спасибо большое

[@dinylatk4134]

Благодарю! Очень полезные советы!!!

[@vladimirshmakov3041]

Николай , спасибо.

[@yurd563]

Большое спасибо за видеурок!

[@BackstreetDV]

Отличное видео. Спасибо

[@romanmakhalov2326]

Огромное спасибо, коллега. Как кстати!

[@atash_singer]

Очень полезны совет спасибо вам

[@user-jw7cq2nb7d]

спасибо огромное, очень помогли!

[@Albertus99]

Огромное спасибо вам!!!

[@TheSitdikov]

Спасибо вам огромное. Я ваш должник, впрочем как и многие люди которым вы помогли.

[@user-tq6gs8yt4x]

подписался в благодарность)

[@lisoso8441]

спасибо.буду использовать.

[@BrawlStars-qh6wf]

спасибо большое,мне помогло очень

[@user-em8lb7qz3e]

Спасибо )

[@user-kj9ti4bk9c]

Молодец!!!! Спасибо!!!!!!

[@KeiTaiho]

9:50 - не всегда это работает. Иногда такая связь появляется при копировании ячейки из другой книги, где была включена проверка данных. При этом создается связь с книгой, которая не рвется через стандартный функционал, не рвется через манипуляции с архивом, как предлагают на форумах, ни отменой проверки данных в текущей книге - вообще никак, кроме как полное удаление ячейки и внесение данных из другой книги иным способом. Идеальный способ - вставлять как значения данные в новую книгу из имеющейся. Если же нужно сохранять форматирование, то нужно быть очень аккуратным с ячейками, где включена проверка данных. Надеюсь, кому-нибудь поможет этот комментарий, т.к. нигде больше в интернете я не нашел такой информации.

[@user-bs3dc5ip6t]

Супер!

[@user-my5zr7vg7z]

Николай, скажите есть ли возможность удалить пустые строки в таблице где есть объединенные ячейки?

[@MrQuantum18]

Красавец!

[@pautovajulia4646]

Спасибо! помог сразу первый способ из 89 Мб осталось 17 Кб

[@saniyaibrayeva1078]

Спасибо за информацию, в режиме чтения пункт 1 не получается. подскажите как решить?

[@svetlanakomarova5415]

спасибо

[@user-kg7vx6kb3z]

Добрый день.Вопрос по лишним ссылкам.Кнопка "разорвать связь" работает только для ссылок в ячейках, если же были использованы внешние книги при проверке данных (например при установке выбора в ячейке из списка). Как быстро удалить эти ссылки, оставив нужные проверки? Убрать все проверки я знаю как.

[@HAMeJleON]

надстройка inquier идет в комплекте только в pro plus версии от 2013 и выше, так что мало она у кого есть, редко можно увидеть людей покупающих office, именно в полной версии всего лишь от 12 тыс рублей за 1 лицензию...

[@eduardmart1237]

У меня проблема - нужно используя данные с одной таблички делать рейтинговый отчет в другой таблице "формат таблицы утвержден приказом" и это приходится делать довольно часто для разных типов таблиц. Сейчас реализуют это как я подозреваю костылем, indirect и ячейка с названием листа донора данных. Но этот метод довольно медленный. Какие еще есть возможности по автоматизации подставление данных в таблицу. Из других? Формат данных в таблице доноре разный. Менять название листа не получается, exel меняет полностью все ссылки на ячейку.

[@robertvardanyan7101]

Thanks

[@sbn4862]

Здраствуйте. У меня подключени на 12 месяц по отдельностью в каждом подключение минимум 1500 строк и обеднено на один обшую запрос с Повер Квери. Создал сводную через общую запрос не загружая как таблица в Эксел. Тормазит так что немогу даже перезагружат комп. Процессор core i5 8ой поколения. Вопрос как можна ускорят процес в таком случи. Естли М коды чтобы както сжат или чтота как Table.Buffer без потерии возможности обновления(Извините за плохой руски язик)

[@user-qp1zv5nx8f]

К моему сожалению, ни один из способов не даёт быстрейшего результата. Не потому что варианты не рабочие, скорее всего в моём случае это несколько разных причин в один момент, из-за чего в процессе устранения этих проблем excel "вешается". Устранять приходится радикальным способом: копирование всего массива исходника в новый файл через вставку значения и затем аккуратное форматирование не всего листа а только рабочей области данных. Других способов пока не знаю. Но всё равно, Николай, благодарю Вас за Ваш труд и помощь.

[@Octieve_San]

спасибо большое!1!!

[@TheArhiArhus]

Чуть-чуть не по теме. Но очень назрел вопрос. При работе Экселя он загружает процессор на 12-13 процентов и виснес. Все галочки где нужно поставлены. Есть в принципе какой-то способ решения данной проблемы?

[@yk1560]

Есть файл с анализом чувствительности внутри. Из-за АЧ сильно виснет. Можно ли ускорить работу этого файла?

[@Shutar_S]

Xlsb в конце как вишенка на торте) Спасибо!

[@Karfogenius]

Нуу, нафик макрос, чтобы объекты удалить? Одну картинку выделить потом Ctrl+A и все скопом удалить. Или, если не видно ничего Gtrl+G и точку на выделить объекты и тоже самое.

[@russlikkruss4088]

Здравствуйте, воспользовался модулем inquire, а размер файла стал наоборот только больше

[@user-es5hf7qo3q]

Можно ли ускорить файл с 6-ступенчатой группировкой, сохранив её?

[@mim2990]

Ролики - высший класс! Подскажи пожалуйста (не могу найти): У меня есть таблица с формулами импорта надстройки блумберг. в одной ячейке формула и она грузит данные вниз сколько нужно. Когда таких ячеек в строке набирается больше 100, то таблица и так стает очень громоздкой. Но, самое главное, что я не могу эту таблицу превратить в умную таблицу. Потому что после обновления данных блумберга, формула дублируется в каждую ячейку таблицы. Приходится клонировать таблицу и создавать умную уже с готовой таблицы, а при обновлении вручную копировать данные с исходной таблицы. Но в экселе хранится ведь две совершенно одинаковые таблицы. Как можно запретить экселю в умной таблицы клонировать строки с формулами? Спасибо

[@romanpavlenko4391]

спасибо , столкнулся с такой проблемой что мемогу больше закидывать картинкив коменттарии после около 30 раз (даже когда паррочку стер , всеравно картинки записывать не даёт ) , в офисе есть ограничения ? офис оригинальный home & buiseness 2016

[@user-pe8rw6hq8z]

Я пытался применить ваш совет по выделению пустых ячеек и удалению их, но ничего не получилось. Выскочила надпись: "Данная команда неприменима для перекрывающихся диапазонов" Что делать?

[@alekseyermakov3911]

Очень юзабельно!

[@user-gr9np1th9t]

Есть ли у вас обучение с начала?

[@user-hm3rt9rr8u]

Здорово, спасибо! Почему я Вас раньше не нашла? Картинки убрала другим способом: Файл/Поиск проблем/Инспектор документов - после проверки в самом конце списка выводится количество невидимых элементов. Удалить! и, оп-па - картинок нет!

[@FrauLIV]

В формате. xlsb нельзя создавать запросы в Power Query, они зависают, а так же, как сказал Николай, обращаться к внешним источникам.

[@Sig4childrens]

Заметил за собой, что уже не в 1 раз лезу в описание под видео, чтобы посмотреть тайм-коды видео. Т.е. временные отсечки для разбора того или иного способа. Так же, не хватает мини-help по применяемым в видео функциям (просто перечень). Т.к. в мобильном ютубе нельзя копировать текст комментариев, иногда приходится делать скрины экрана, чтобы не потерять мудрую мысль/совет/ответ на интересуюший вопрос. Понимаю, что меня навряд ли услышит многоуважаемый Николай, но все же...

[@honky-tonk1060]

можна делать копии коментариев в мобильном ютубе, но с помощью программы NewPipe (есть на Гитхабе), но только описание видео, и основные комментарии (ветку с ответами скопировать не получится, поэтому или скринами как Вы ответы в ветках, или копированием в браузере, но в браузере на мобильнике неудобно, потому что не хватает точности управления как с мышкой на компьютере, да и бывают глюки при выделении: выделяеш вниз, а подглючивание в самыйпоследний момент захватывает все, что выше нужного коммента).

[@honky-tonk1060]

Если открыть в браузере и развернуть ветки ответов с комментариями - то можна сохранить как html-страницу (то есть все как видете так и отобразится, и при этом даже интернет не нужен - специально проверил, все в порядке, - ранее обычно с текстами так поступал). Сохраненные страницы html на мобильнике будут в папке Download (браузер по усмотрению - Chrome, Opera (красная), и iron browser умеют сохранить и прочитать html-cтраницы).

[@tusim_365]

Здравствуйте, у меня в таблице эксель миллион строк и они не удаляются. Есть какой-то способ. Думаю они тормозят файл

[@Selinger1000]

Inquire, к сожалению, существует только в про версиях.

[@user-bh3lg3et5n]

помогите пожалуйста! Возможно ли такое, что файл весит всего 6,9Мбайт но очень сильно тормозит? Есть разметки страниц, есть форматирование таблицы, есть именованные диапазоны, есть рисунки. Но сам файл весит очень маленькое. Если я пришлю файл, то сможете ли ВЫ поставить "диагноз" изза чего больше всего тормозит.

[@asderasder9308]

Здравствуйте! У меня такая проблема, файл сохраняется по 30 минут, как это можно исправить? Жёсткий ссд диск

[@cyklonification]

👍 👍👍👍💙!!

[@aibabrat]

Добрый день! Уважаемый автор Николай, вопрос у меня 600 000 запись в одном листе называется LOG. Когда открывает Excel файл ждём почти 20 минут. Вот по этому мне нужно быстро открыть и работать. Об этом вы можете мне помогать?

[@irynaburkatska3910]

Используйте в работе не excel, а языки программирования. Например, R или Python?

[@aibabrat]

@@irynaburkatska3910 Это было 4 г. назад. Теперь мы перешли на Access

[@user-sl7sr2yl5j]

Фомат "двоичные данные" не позволяет работать с "Повер Квери". Это ограничения эксел?

[@planetaexcel]

К сожалению, да.

[@EvgenyTitkov]

Проблема файлов xlsb в том что, например, подключением к ним и при работе сними с помощью power query, вы получаете жуткие тормоза.

[@user-mq6dd4yk5n]

Подскажите, пожалуйста, как быть, если из-за размера файла я его даже открыть не могу? Можно ли как-то облегчить его, не открывая?

[@planetaexcel]

Вот это вряд ли. Если только переименовывать из xlsx в zip, открывать потом как архив и чистить вручную. Но так легко поломать вообще всё :(

[@sir.Geronis]

А что делать когда drawing1.xml разростается до огромных размеров? У меня он весит 229МБ, а сами картинки 6,7МБ. Удалять не вариант, с удалением файла, удаляются картинки.

[@planetaexcel]

Может разрешение картинок очень большое?

[@sir.Geronis]

​@@planetaexcel Нет. Картинки занимают всего 6,7МБ., это около 112 фотографий. Я потом удалил файл drawing1.xml, заново залил все фотографии в файл. И файл drawing1.xml стал весить 8.2МБ. Вместо 229МБ. И файл стал летать. Получается, файл drawing1.xml был замусорен. И почистить его нет ни какой возможности, кроме как удалением. При этом, если файл drawing1.xml удалить, а картинки нет, то при открытии, они будут удалены из файла автоматически. Пришлось заново картинки заливать. И это проблема!

[@ElviRussia]

16:42 Картинки

[@igorstrunkin1070]

Если как у меня в программке астрологической (для прогностики, аналогов в мире нет и близко даже) на базе Эксельки порядка 10 млн. ячеек занято лишь на 1 -м листе и в большинстве стоят в среднем 4-х этажные формулы, то она, конечно, не может удалить все ячейки под ней, как у автора видео. Тогда опускаюсь до границы ползунка вниз и по паре сотен пустых ячеек удаляю, идя так вверх до границ расчётов. Периодически сохраняю промежуточный результат. Пустые строки возникли от того, что пришлось урезать точность орбисов аспектов до 1 градуса, а было в 2 раза точнее - но Экселька уже не тянула, лагала, пришлось удалить часть в серединке и тупо перетаскивать массивные блоки, дабы сделать компактнее Лист. Удивило, что не даёт Эксель 2010 (только 32 бит -ная может считать ШЭ - Швейцарские эфемериды для любого астероида) это толком сделать. Многократные попытки давали сбои после которых программа рушилась напрочь, благо постоянно сохранял под новыми именами. Но это - только с большими массивами данных (мой файл 235 Мб и сократить не получается уже - это вам не "купи-продай" табличка; на пике доводил до 310 Мб - но с таким количеством расчётов невозможно работать было - отказы внезапные Эксельки там обычное дело). Я смог сделать так, что расчёт итоговой диаграммы при 21 методике для 18 пар планет может занимать 2 - 20 сек, но сохраняет результат она порядка 2 мин... Странно то, что убрав порядка 1200 строк меж расчётами как описал выше - программка не уменьшилась в размере, а ... увеличилась! Плюс удалил 25% расчётных ячеек с длинными формулами - но тоже 5 Мб всего убавилось. Удаление строк меж расчётами, в принципе, было возможно и это проще, но нарушались формулы многие, и я не рискнул. Хотя и перетаскиванием то же самое после было, пришлось корректировать. Правильно сразу начисто писать, но когда не знаешь толком ещё что конструируешь - так не выйдет. А переписать заново труд полугодовой или то, что свыше года уж дополняю - тоже нереально... В общем - искусственно созданная компактность не принесла ожидаемого результата, но зарезала былые возможности возрата к расширению точности в таблицах. Столбцы в конце Листа - не хочет удалять (в отличие от строк, эти удалось). Потому логичнее идти вправо больше, чем вниз для расчётов. Цветность шрифта необходима для различения расчётов - и она практически, не влияет на размер файла.

[@irynaburkatska3910]

Скажите, вы забиваете микроскопом гвозди? Или пытаетесь паять кабели паяльной лампой?😂😂 Каждой работе - свой инструмент! Какой к черту excel для обработки Big data?😂😂 Советую попробовать R. То, что excel делает за час, он сделает за секунду.

[@igorstrunkin1070]

​@@irynaburkatska3910 Было б кому отвечать...Смотрю все шибко умные стали на словах, - программисты! - а по факту - ноль; пшик сотворить только и могут - типа примитивного детского калькулятора, это уже верх их достижений; а ещё ж и хамить умеют! Иначе давно уж были бы программы достойные по астропрогностике.

[@user-gr9np1th9t]

Курсы ?

[@user-cp1wk5jd7u]

как раз очень нужно было мне это видео. т.к. есть отчет в excel виде. Весит оно у меня очень много почему то ( порядка 17 Мб) Решил проверить солько у меня ненужных строк путем нажатия кнопок Ctrl+End. в итоге получилочь 1000000 строк а рабочих всего 200-250 строк Пытался их удалить но копм просто зависает от такого количества удаления. Что делать?

[@planetaexcel]

+Алексей Башмаков Попробуйте скопировать свои 200 строк на новый лист, а старый - удалите.

[@Rice8520]

Попробуйте также перед удалением отключить автоматическое вычисление.9

[@BombaDaday]

здравствуйте у меня файл весит 20+ мб, хотя 3 месяца назад я создал его и было 700+ кб, за 3 месяца друг умудрилась расширить файл до 20+ мб)) хотя в нем нет ничего такого, я про макросы или про формул, простейший файл, но весит как слон, помогите уменьшить размер. И да мой друг очень любит функцию Merge & Center, по моему это и делает файлы такими необоснованно большими, что посоветуете?

[@planetaexcel]

Вы из этого видео всё попробовали?

[@BombaDaday]

@@planetaexcel в этом видео уже не помню что говорится))) но смотрел пробовал, но не все проблемы в видео касаются именно моей проблеме, там не говорится, это точно, о 20 с лишним мб файле))) понимаете какой скачок из 700кб сразу в 20+мб и это очень странно, в видео говорится о сложных формулах но в моем файле нету ниче серьезного, формулы только суммы и максимум автокопирование одной строки или ячейки и все, не сложный файл но весит как нормальный аудиофайл с расширением flaac

[@excluzive.]

Как из таблицы в 200 тысяч строк выцепить дубликаты по одному из полей, с наименьшим количеством сопутствующей боли? За видео спасибо.

[@Olega88]

+eXclusive Отличный вопрос, тожу жду ответ.

[@user-pi3oq2vd1d]

тоже интересует эта функция

[@alexsemenov2572]

счетесли попробуйте

[@kselnaga7303]

первый пример, я все ломал голову как от него избавится, постоянно с ним сталкиваюсь, а все оказалось просто))

[@TheSnos15]

часто люди, не обременяющие себя интеллектуальной работой, не удаляют форматирование, например, а просто убирают значение, записывают пустую строку или пробел(ы) вместо удаления значения, рамки не убирают, а делают их не видимого глазу цвета и так далее. когда начинаешь рассказывать как этого избегать, отмахиваются или даже гавкают :) кстати, много какое форматирование как раз наоборот надо применять к столбцу/строке/листу, а не имеющимся данным. именно так файл будет меньше и быстрее, а новые данные уже будут оформлены как и старые, то есть не надо будет что-то там "копировать по формату", в чём рукожопы снова облажаются рано. ну и любой человек рано или поздно. в общем, автор опять вроде и о нужных вещах поговорил, но сам не до конца разбирается и не рассказывает о причинах, о том как избежать и как избавиться не хирургически

[@user-cl1um1wo9m]

А вот макросы все же лучше сохранить на всякий случай, вдруг что лишнее удалишь в утомленном ритме)

[@1iuh]

0:59 Файл всего 9 Мб. У меня файлы и по 80 Мб вырастают. Просто ужас и висяк. Как так - сам не понимаю. Формат xlsb вместо xlsx уменьшит объём почти вдвое.

[@user-sv4qc2pz2f]

Проблема в удалении пустых ячеек. Пишет: недостаточно ресурсов. Выберите меньше данных или закройте другие приложения. Что делать?

[@planetaexcel]

Боюсь, что нужен компьютер помощнее :)

[@alexanderr.539]

Удаляйте частями. Можно еще временно(!) отключить функцию автоматического пересчета формул в настройках файла. После очистки и пересохранения включите обратно.

[@nikitakita4]

Дополнительный совет. Удалите правила условного форматирования.

[@decore1036]

странно, но ничего не сказано ни про скрытые имена, ни про скрытые objects/shapes...

[@key_geee]

Убрала пересчет сводной таблицы и файл с 10707 кв стал 507 кв :)))

[@user-qn5by5iv7u]

2022

[@Pedrochenko]

Эх, зачем суперскрытые листы палите ))

[@user-mw9sd2mg4u]

Excel 2019 не хочет удалять все строки 🤥

[@user-jm1np9vk2y]

Не удаляет строки....

[@alexandrengineer7972]

НЕТ У МЕНЯ НИ ВУКЛАДКИ РАЗРАБОТЧИКА, НИ ВКЛАДКИ ИНКВАРИ... 2016 XL

[@user-uh4nr8dc4c]

проотимммсмс

[@denisbeloglazov392]

Мозг!

[@user-fs4fp8ve9w]

увы, не помогло

[@user-gz3vy4mq2n]

Маджик!