Как сохранить файл excel без ссылок
Мы ВКонтакте
JS: 2.14.23
CSS: 4.9.13
jQuery: 3.6.0
DataForLocalStorage: 2022-05-11 16:41:29-standard
jQuery
jQuery UI
Bootstrap
Font Awesome
Megaloman (Мастер-Эксперт)
Пакет MSOffice
Программы и игры Компьютеры Устранение неисправностей
Установка, настройка и работа в пакете Microsoft Office.
Как проще всего сохранить xlsx без формул? (Excel 2007+)
Кроме специальной вставки.
Здравствуйте, Александр Романов [Alexo]!
Если нужно сохранить именно в xlsx - то некоторые варианты, которые ранее были в специальной вставке, в Excel2007 вынесены и в меню. В том числе вставка значений.
К сожалению не могу вставить скриншот, но я думаю это не сложно. Как Вы можете сделать.
Вы выделяете нужный фрагмент листа таблицы, копируете его. Затем на закладке меню "Главная" самая левая большая иконка вставки. Кликаете мышкой не на саму иконку, а на надпись "Вставить" и в подменю выбираете "Вставить значения". При этом на листе формулы заменяются на их значения. Файл сохраняете и все.
Если же не нужен именно xlsx - тогда можно посмотреть в сторону например PDF. Т.е. выбираете "Сохранить как"-->"PDF или XPX".
babylon_5
Посетитель
21.11.2009, 13:11
К сожалению эта та же самая спец. вставка, но в немного более удобном виде.
Хотелось бы иметь возможность редактирования (отсюда PDF не подходит).
Андреенков Владимир
Самый простой вариант создать в книге новый Лист, выделить все на исходном листе (Ctrl+A), копировать (Ctrl+C), на новом листе в левом углу правой кнопкой, Специальная вставка, выбрать Значения, Ок, потом еще раз в левом углу правой кнопкой, Специальная вставка, выбрать Форматы, Ок.
Александр Романов [Alexo]
Андреенков Владимир
Александр Романов [Alexo]
Я просто думал может я чего не знаю.
Сейчас приходиться сохранять исходный файл потом спец. вставкой все копировать в новую книгу.
Да, почти единственным вариантом остается копирование и вставка значений.
Еще принципиально другой путь - написать небольшой макрос, который будет проходить по диапазону ячеек и заменять формулы на значения.
Цитата: Александр Романов [Alexo]Сейчас приходиться сохранять исходный файл потом спец. вставкой все копировать в новую книгу.
А зачем так? Если нужен другой файл со значениями, то проще: "Сохранить как", а потом "Копировать"-"Вставить значения". Намного проще (IMHO).
Александр Романов [Alexo]
Андреенков Владимир
Правильно говорит babylon_5, сначала Сохранить как. деалаем копию, а потом, записываем макрос, выделить все, копировать, специальная вставка значения. специальная вставка формулы. Это позволит потом только нажимать горячие кнопки запуска макроса, что намного быстрее и удобнее.
Андреенков Владимир
Александр Романов [Alexo]:
Я надеюсь Вы поняли, что выше описаный способ подразумевают открытой только одну книгу не надо копировать с другой + процесс ускоряется с помощью макроса, единственно, что вам предется делать, это резервная копия исходного файла
Александр Романов [Alexo]
Андреенков Владимир
Почему из одной для другой? К, примеру, сначала, делаете, копию листа внутри книги оригинала, записываете, макрос, удаляете лишний лист, сохраняете, теперь в оригинале книги есть макрос, и он будет в каждой его копии, т.е. когда Вы делаете Сохранить как. сохраняете под другим именем, то макрос в новом файле тоже будет. Поэтому Ваш вопрос непонятен.
Александр Романов [Alexo]
записываем макрос, выделить все, копировать, специальная вставка значения.
выше описаный способ подразумевают открытой только одну книгу не надо копировать с другой
а как бы я макрос запустил из одной для другой?)
Вопрос был про то как я макросом скопирую данные из другой книги? фактически смаил в конце означал, что то типа зачем это уточнение если такое в прицепе невозможно?
Со всей ответственностью могу сказать:
а) сделать макросом резервную копию можно;
б) макрос из одной книги может работать с другой (в т.ч. макросом можно сначала и создать эту другую книгу, или выбрать ее в диалоге "Открыть файл", или просто использовать заданное имя файла для открытия);
в) макросом можно читать значения в одной книге/листе и записывать их в другую книгу/лист;
г) макросом можно устанавливать любое оформление ячеек/областей;
и т.д. и т.п. тут ограничений практически нет. Можно даже в реестре макросом поковыряться. :)
Андреенков Владимир
Вопрос был про то как я макросом скопирую данные из другой книги? фактически смаил в конце означал, что то типа зачем это уточнение если такое в прицепе невозможно?
Вы по-моему не поняли, проведите эксперимент, откройте книгу, выделить все, копировать, теперь на этом же листе Специальная вставка значение, Специальная вставка форматы, все. Я не понимаю зачем что-то копровать из другой книги? Надо просто сделать копию оригинала и рабоать с ним, ведь вопрос стоял в том как избавиться от формул, это решение, просто данные не надо переносить из другой книги и можно ускорить процесс избавления от формул за счет использования макроса.
Андреенков Владимир
babylon_5:
Целиком поддерживаю, просто я смотрю человек не сильно разбирается в макросах, не стал нагружать как работать и с другими книгами с помощью них, тем более без этого можноо обойтись, все решается в пределах одной книги достаточно сделать копию оригинала.
Александр Романов [Alexo]
Я не понимаю зачем что-то копровать из другой книги?
вот и я не понимаю зачем вы об этом упомянули.
babylon_5, как же макрос работает с книгами если заранее не известно как будет называться книга из которой надо будет скопировать содержимое ячеек?
Андреенков Владимир
Вопрос был про то как я макросом скопирую данные из другой книги?
а теперь Вы спрашиваете зачем я упомянул о копировании из другой книги, но ведь Вы сами об этом спрашивали!? А я только спросил зачем копировать из другой книги, если можно работать с одной книгой (копией оригинала).
Цитата: Александр Романов [Alexo]как же макрос работает с книгами если заранее не известно как будет называться книга из которой надо будет скопировать содержимое ячеек?
б) макрос из одной книги может работать с другой (в т.ч. макросом можно сначала и создать эту другую книгу, или выбрать ее в диалоге "Открыть файл", или просто использовать заданное имя файла для открытия);
Но, если что еще раз структурно:
а) содержимое ячеек может копироваться из текущей книги в книгу с заранее заданным именем;
б) книга может быть выбрана в диалоге (есть, есть в VBA команды выбора файла (давайте сменим термин, уж очень далеко ушло развитие макроязыка со времен MS Office 6 и теперь это уже мощный язык программирования Visual Basic for Application (VBA)));
в) есть еще вариант создать книгу, заполнить ее данными и записать (с заданным именем или с запросом имени - возможны оба варианта). но это уже для случая не "из которой надо будет скопировать" а "в которую".
А само имя, собственно, аккуратненько в переменную поместим и до поры-до времени будем сохранять.
И вообще, по-моему мы уже пошли по кругу.
Не буду спорить с ТопикСтартером, и не буду портить его картину мира. Не может ему макрос работать с другими книгами - значит в его мире это не возможно. Тем более, что есть глубокие сложности с именами этих книг.
В моем мире макросы прекрасно соглашались работать с другими книгами когда мне это от них требовалось. Ну, значит мой мир именно таков. Мне видимо проще.
Андреенков Владимир
сначала, делаете, копию листа внутри книги оригинала, записываете, макрос,
выделить все, копировать, теперь на этом же листе Специальная вставка значение, Специальная вставка форматы, все.
удаляете лишний лист, сохраняете, теперь в оригинале книги есть макрос
Для того чтобы получить книгу без формул открываете оригинал, Сохранить как. сохраняете под другим именем, запускаем макрос, получаем книгу без формул.
Андреенков Владимир
Black Cloud , Бакалавр
Да ну, усложнение. Если уж макрос - так он сам и сделает новую книгу (копию текущей. ), и например, символ какой к имени исходной книги для новой добавит. Если уж пользовать макрос - зачем лишние телодвижения? Пусть он и работает. А нам останется только его запускать.
Андреенков Владимир
Александр Романов [Alexo]
Что ж из вас никто в ответе то макрос не написал? А только писали про то что я и так уже знаю, а?)
А то так получается что вы про макросы и сами не знали пока не погуглили.
Андреенков Владимир
Александр Романов [Alexo]:
Для того чтобы написать макрос так как я сказал, необходимо выполнить: Вид-Макросы-Запись макроса, указать имя макроса, выбрать "горячую клавишу", ок (ecxel 2007). Выполнить необходимые действия: выделить все, копировать, теперь на этом же листе Специальная вставка значение, Специальная вставка форматы. После получения нужного результата: Вид-Макросы-Остановить запись. Теперь макрос будет запускаться "горячей клавишей", как видите никаких дополнительных знаний не требуется и смысла писать Вам текст этого макроса тоже нет, т.к. Вы сами в состоянии его создать, так как Вам нужно. Если хотите еще и с помощью макроса делать копию документа, то Вам придется изучать язык макросов, для начала справки офиса будет достаточно.
Александр Романов [Alexo]
Вопрос был не в этом.
И как писать макросы я знаю. Про макросы я сразу и не подумал, так как думал что есть возможность сохранить книгу просто без формул.
Никто из вас кстати тоже про макросы не подумал, так как все сразу начали писать про спец. вставку о которой я упомянул в вопросе.
Андреенков Владимир
Что ж из вас никто в ответе то макрос не написал?
Непонятно тогда зачем Вам нужен макрос, если сами умеете?
В результате описанного мною способа все, что Вам нужно сделать, это нажать Сохранить как. и нажать "горячую клавишу" запуска макроса. Если Вы считаете, что это будет все равно недостаточно быстрое выполнение Вашей задачи, то извините.
Цитата: Александр Романов [Alexo]вы про макросы и сами не знали пока не погуглили.
Цитата: Александр Романов [Alexo]Как проще всего сохранить xlsx без формул?
Какой вопрос задали, такой ответ и получили. Мы тут мысли читать не умеем (простите засранцев). Что спрашивают - на то и отвечаем.
Вот именно, не в этом. И макросы - это совсем не самое простое. Я потому и упомянул про них в мини-форуме, что это не в вопрос (кстати просто по приколу, а за это зацепились - оказалось очень даже в тему ).
Вот если бы Вы, сэр, в вопросе написали бы хотя бы как в комментарии:
Цитата: Александр Романов [Alexo]Я просто думал может я чего не знаю.
Сейчас приходиться сохранять исходный файл потом спец. вставкой все копировать в новую книгу.
Т.е. про спец.вставку знаю, хочу узнать какие пути еще вообще есть. Вы бы получили совсем другие ответы.
А так: вы просили как попроще в 2007м это сделать, я Вам и написал где ткнуть чтобы попроще. Да, это корнями где-то из спец.вставки, но сильно проще.
Вот где-то так.
2 Black Cloud , Бакалавр
ТС сам писать макросы не умеет. Он только "знает как". А это, как говорят в Одессе, две большие разницы.
Ну, так, чтобы было понятно даже мне (а не только ТС-у): знать как заниматься сексом и заниматься им - это вот. тут тоже есть в ньюансах различие.
Все. В этом вопросе я больше не пишу, потому как флудэрство уже пошло, а это не есть гуд. И карму не улучшит. Воооотт.
Александр Романов [Alexo]
Ответ всего один и он не ваш. я не имею ввиду форум.
И макрос я имел ввиду полный макрос, т.е. исходник VBA скрипта который сам выделит все, скопирует, создаст новую книгу и туда все запишет. Вобщем полный он и в Африке полный.
ТС сам писать макросы не умеет. Он только "знает как". А это, как говорят в Одессе, две большие разницы.
Ну, так, чтобы было понятно даже мне (а не только ТС-у): знать как заниматься сексом и заниматься им - это вот. тут тоже есть в ньюансах различие.
Хех, как низко же вы пали.
Цитата: Александр Романов [Alexo]И макрос я имел ввиду полный макрос, т.е. исходник VBA скрипта который сам выделит все, скопирует, создаст новую книгу и туда все запишет. Вобщем полный он и в Африке полный.
Тут есть два ответа. И оба Вам не понравятся.
1. Ну, тут про ключи от квартиры. (надеюсь ясно)
2. Полный макрос - лЁгко. Правда, поскольку вот по такой информации:
Как проще всего сохранить xlsx без формул? (Excel 2007+)
Кроме специальной вставки.
его написать сложновато, я предлагаю Вам такой вариант:
- стучитесь ко мне в почту/аську/скайп
- я Вам дам номер WM-кошелька
- с Вас 10WMZ и подробно что, с какого листа, в каком диапазоне ячеек, в каком виде перекинуть в новую книгу или из старой книги. а еще лучше образец файла
- с меня макрос, который (если захотите) спросит из какой книги будем брать данные, перенесет все, спросит имя книги под которым сохранять новую (или сформирует это имя по Вашим правилам) и сделает это усё как в лучших домах лондОна и пАрижа. А за +10WMZ он обретет лист с настройками, где Вы сможетеоперативно переключать способы открытия/записи и менять префикс/суффикс имени нового файла.
Вобщем будет полный как в Африке.
Хоть уже лет шесть не писал под Ёксель, но ради такого случая. грех не вспомнить. Да и не проблема.
Засим разрешите откланяться.
Александр Романов [Alexo]
Все. В этом вопросе я больше не пишу, потому как флудэрство уже пошло, а это не есть гуд. И карму не улучшит. Воооотт.
его написать сложновато, я предлагаю Вам такой вариант:
Я попозже сам напишу, как время найдется. Заодно и гляну насколько это сложно.
Александр Романов [Alexo]
Вот, очень простой скрипт. (Тут есть еще над чем работать)
Без знаний VB/VBA написал. Просто немного переделав макрос созданный екселем. Не думаю что этот способ можно считать более сложным чем Спец. вставка.
Копирую столбец из одной книги (Книга1) в другую(Книга2). При этом формулы в столбце в Книге2 имеют вид, например,
[Книга2]А1+В2
Как убрать ссылку [книга2], чтобы формула выглядела как и в источнике, т.е. А1+В2
Копирую столбец из одной книги (Книга1) в другую(Книга2). При этом формулы в столбце в Книге2 имеют вид, например,
[Книга2]А1+В2
Как убрать ссылку [книга2], чтобы формула выглядела как и в источнике, т.е. А1+В2 kissi80
light26,
вид в источнике (Книга1), например, в ячейке Y20, на листе с названием Итоги:
=коммунальщик!Z20
где коммунальщик - название листа в этой книге.
вид в другой книге, куда копируется столбец(!)
='[Книга1.xlsx]коммунальщик'!Z20
Так вот надо , чтобы названия книги, т.е. что бы квадратных скобок и внутренностей их НЕ БЫЛО!
light26,
вид в источнике (Книга1), например, в ячейке Y20, на листе с названием Итоги:
=коммунальщик!Z20
где коммунальщик - название листа в этой книге.
вид в другой книге, куда копируется столбец(!)
='[Книга1.xlsx]коммунальщик'!Z20
Так вот надо , чтобы названия книги, т.е. что бы квадратных скобок и внутренностей их НЕ БЫЛО! kissi80
Так вот надо , чтобы названия книги, т.е. что бы квадратных скобок и внутренностей их НЕ БЫЛО! Автор - kissi80
Дата добавления - 18.11.2011 в 11:29
Как вариант, правда с неким дополнительным действом:
после вставки жмем Ctrl+H, в "Найти" - "[Книга1]", в "Заменить на" - ничего, в "Область поиска" - "формулы". И жмем "Заменить все".
Как вариант, правда с неким дополнительным действом:
после вставки жмем Ctrl+H, в "Найти" - "[Книга1]", в "Заменить на" - ничего, в "Область поиска" - "формулы". И жмем "Заменить все". _Boroda_
_Boroda_,
это да, это я уже нарыл, спасибо.
однако хотелось бы что-нить попроще в смысле макрос какой-нибудь, т.е. кнопку нажал и все сделалось, без дополнительных действов, поскольку это не я буду делать, а пользователи, со всеми вытекающими. (((
_Boroda_,
это да, это я уже нарыл, спасибо.
однако хотелось бы что-нить попроще в смысле макрос какой-нибудь, т.е. кнопку нажал и все сделалось, без дополнительных действов, поскольку это не я буду делать, а пользователи, со всеми вытекающими. ((( kissi80
[/vba]
Такой макрос. Можно положить в книгу макросов (Персонал), можно использовать только в этой книге. Можно повесить на кнопку. Второй файл должен быть открыт. Судя по первому посту, так оно и есть.
[/vba]
Такой макрос. Можно положить в книгу макросов (Персонал), можно использовать только в этой книге. Можно повесить на кнопку. Второй файл должен быть открыт. Судя по первому посту, так оно и есть. _Boroda_
[/vba]
Такой макрос. Можно положить в книгу макросов (Персонал), можно использовать только в этой книге. Можно повесить на кнопку. Второй файл должен быть открыт. Судя по первому посту, так оно и есть. Автор - _Boroda_
Дата добавления - 18.11.2011 в 16:07
Если подобных переносов много, замены становятся очень трудоемкими.
Тогда лучше не заниматься заменами, а в конце открыть окно связей (в XL2003 Правка >> Связи) и пересоединить связи на новую книгу
Для "свежих" переносов срабатывает на 100%, но при одном условии - новая книга должна быть сохранена до пересоединения
Если подобных переносов много, замены становятся очень трудоемкими.
Тогда лучше не заниматься заменами, а в конце открыть окно связей (в XL2003 Правка >> Связи) и пересоединить связи на новую книгу
Для "свежих" переносов срабатывает на 100%, но при одном условии - новая книга должна быть сохранена до пересоединения v__step
С уважением, Владимир
v__step, в том-то все и дело, что не 100 %.
Поэтому и надо было механизм, который одинаково работает.
Со временем проблем пока нет, всегда можно выкроить, да и сейчас доделывается система, которая будет на SQL, т.е. тут от Excel отойдем
v__step, в том-то все и дело, что не 100 %.
Поэтому и надо было механизм, который одинаково работает.
Со временем проблем пока нет, всегда можно выкроить, да и сейчас доделывается система, которая будет на SQL, т.е. тут от Excel отойдем kissi80
Был бы очень благодарен, если бы Вы привели живой пример такого сбоя
Для меня это важно, т.к. я пользуюсь пересоединением связей не только вручную (очень часто), но и программно в Чистилке (выложил эту утилиту в разделе "Готовые решения" в сыром виде), и пока что не замечал сбоев
Уже засомневался - только что проверил для разрушенной связи - сработало
Был бы очень благодарен, если бы Вы привели живой пример такого сбоя
Для меня это важно, т.к. я пользуюсь пересоединением связей не только вручную (очень часто), но и программно в Чистилке (выложил эту утилиту в разделе "Готовые решения" в сыром виде), и пока что не замечал сбоев
Уже засомневался - только что проверил для разрушенной связи - сработало
С уважением, Владимир
Был бы очень благодарен, если бы Вы привели живой пример такого сбоя
Для меня это важно, т.к. я пользуюсь пересоединением связей не только вручную (очень часто), но и программно в Чистилке (выложил эту утилиту в разделе "Готовые решения" в сыром виде), и пока что не замечал сбоев
Уже засомневался - только что проверил для разрушенной связи - сработало
v__step, Не, не приведу. )))
Я написал, что не 100%, поскольку при нескольких попытках (в разное время) слетали другие связи. Дело в том, что эти конкретные книги сейчас очень запутаны и тяжеловесны (напримр, 150 листов с перекрестными ссылками. ). Поэтому мы сейчас все переводим на SQL, но пока не перешли, дорабатывать-то надо.
Наверное все дело действительно в некорректных связях, которые накопились и мешают, но разбираться нет ни времени ни желания.
v__step, Не, не приведу. )))
Я написал, что не 100%, поскольку при нескольких попытках (в разное время) слетали другие связи. Дело в том, что эти конкретные книги сейчас очень запутаны и тяжеловесны (напримр, 150 листов с перекрестными ссылками. ). Поэтому мы сейчас все переводим на SQL, но пока не перешли, дорабатывать-то надо.
Наверное все дело действительно в некорректных связях, которые накопились и мешают, но разбираться нет ни времени ни желания. kissi80
С уважением, Владимир
Предположим, что у нас есть вот такая несложная таблица, в которой подсчитываются суммы по каждому месяцу в двух городах, а затем итог переводится в евро по курсу из желтой ячейки J2.
Проблема в том, что если скопировать диапазон D2:D8 с формулами куда-нибудь в другое место на лист, то Microsoft Excel автоматически скорректирует ссылки в этих формулах, сдвинув их на новое место и перестав считать:
Задача: скопировать диапазон с формулами так, чтобы формулы не изменились и остались теми же самыми, сохранив результаты расчета.
Способ 1. Абсолютные ссылки
Как можно заметить по предыдущей картинке, Excel сдвигает только относительные ссылки. Абсолютная (со знаками $) ссылка на желтую ячейку $J$2 не сместилась. Поэтому для точного копирования формул можно временно перевести все ссылки во всех формулах в абсолютные. Нужно будет выделить каждую формулу в строке формул и нажать клавишу F4:
Способ 2. Временная деактивация формул
- Выделяем диапазон с формулами (в нашем примере D2:D8)
- Жмем Ctrl+H на клавиатуре или на вкладке Главная - Найти и выделить - Заменить (Home - Find&Select - Replace)
Способ 3. Копирование через Блокнот
Этот способ существенно быстрее и проще.
Нажмите сочетание клавиш Ctrl+Ё или кнопку Показать формулы на вкладке Формулы (Formulas - Show formulas) , чтобы включить режим проверки формул - в ячейках вместо результатов начнут отображаться формулы, по которым они посчитаны:
Скопируйте наш диапазон D2:D8 и вставьте его в стандартный Блокнот:
Теперь выделите все вставленное (Ctrl+A), скопируйте в буфер еще раз (Ctrl+C) и вставьте на лист в нужное вам место:
Осталось только отжать кнопку Показать формулы (Show Formulas) , чтобы вернуть Excel в обычный режим.
Примечание: этот способ иногда дает сбой на сложных таблицах с объединенными ячейками, но в подавляющем большинстве случаев - работает отлично.
Способ 4. Макрос
Если подобное копирование формул без сдвига ссылок вам приходится делать часто, то имеет смысл использовать для этого макрос. Нажмите сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , вставьте новый модуль через меню Insert - Module и скопируйте туда текст вот такого макроса:
Для запуска макроса можно воспользоваться кнопкой Макросы на вкладке Разработчик (Developer - Macros) или сочетанием клавиш Alt+F8. После запуска макрос попросит вас выделить диапазон с исходными формулами и диапазон вставки и произведет точное копирование формул автоматически:
Если в один прекрасный момент вы осознаете, что ваш основной рабочий файл в Excel разбух до нескольких десятков мегабайт и во время открытия файла можно смело успеть налить себе кофе, то попробуйте пробежаться по описанным ниже пунктам - возможно один или несколько из них укоротят вашего "переростка" до вменяемых размеров и разгонят его "тормоза" :)
Проблема 1. Используемый диапазон листа больше, чем нужно
Если ваша таблица занимает 5 на 5 ячеек, то это отнюдь не означает, что Excel запоминает при сохранении этого файла только 25 ячеек с данными. Если вы в прошлом использовали какие-либо ячейки на этом листе, то они автоматически включаются в используемый диапазон (так называемый Used Range), который и запоминается при сохранении книги. Проблема в том, что при очистке используемых ячеек Excel далеко не всегда автоматически исключает их из используемого диапазона, т.е. начинает запоминать в файле больше данных, чем реально имеется.
Проверить это просто – нажмите на клавиатуре сочетание клавиш Ctrl+End и посмотрите куда переместится активная ячейка. Если она прыгнет на фактическую последнюю ячейку с данными на листе – отлично. А если вдруг ускачет сильно правее и/или ниже "в пустоту" – дело плохо: все эти ненужные пустые ячейки Excel тоже запоминает внутри файла.
Лечится это, тем не менее, достаточно легко:
- Выделите первую пустую строку под вашей таблицей
- Нажмите сочетание клавиш Ctrl+Shift+стрелка вниз – выделятся все пустые строки до конца листа.
- Удалите их, нажав на клавиатуре Ctrl+знак минус или выбрав на вкладке Главная – Удалить – Удалить строки с листа (Home – Delete – Delete rows) .
- Повторите то же самое со столбцами.
- Повторите все вышеописанные процедуры на каждом листе, где при нажатии на Ctrl+End активная ячейка перемещается не на фактическую последнюю ячейку с данными а "в пустоту" ниже и/или правее.
- Сохраните файл (обязательно, иначе изменения не вступят в силу!)
Если в вашей книге очень много таких листов, то проще, наверное, использовать короткий макрос.
Проблема 2. Используется старый формат XLS вместо новых XLSX, XLSM и XLSB
Много лет и версий подряд еще с начала девяностых в Excel был один формат файлов - XLS. Это, конечно, убирало проблемы совместимости, но, сам по себе, этот формат давно устарел и имел много неприятных недостатков (большой размер, непрозрачность внутренней структуры данных, легкую повреждаемость и т.д.)
Начиная с верии Excel 2007 Microsoft ввела новые форматы сохранения файлов, использование которых заметно облегчает жизнь и - ваши файлы:
- XLSX - по сути является зазипованным XML. Размер файлов в таком формате по сравнению с Excel 2003 меньше, в среднем, в 5-7 раз.
- XLSM - то же самое, но с поддержкой макросов.
- XLSB - двоичный формат, т.е. по сути - что-то вроде скомпилированного XML. Обычно в 1.5-2 раза меньше, чем XLSX. Единственный минус: нет совместимости с другими приложениями кроме Excel, но зато размер - минимален.
Проблема 3. Избыточное форматирование
Сложное многоцветное форматирование, само-собой, негативно отражается на размере вашего файла. А условное форматирование еще и ощутимо замедляет его работу, т.к. заставляет Excel пересчитывать условия и обновлять форматирование при каждом чихе.
Оставьте только самое необходимое, не изощряйтесь. Особенно в тех таблицах, которые кроме вас никто не видит. Для удаления только форматов (без потери содержимого!) выделите ячейки и выберите в выпадающем списке Очистить - Очистить форматы (Clear - Clear Formats) на вкладке Главная (Home) :
Особенно "загружают" файл отформатированные целиком строки и столбцы. Т.к. размер листа в последних версиях Excel сильно увеличен (>1 млн. строк и >16 тыс. столбцов), то для запоминания и обрабоки подобного форматирования нужно много ресурсов. В Excel 2013-2016, кстати, появилась надстройка Inquire, которая содержит инструмент для быстрого избавления от подобных излишеств - кнопку Удалить избыточное форматирование (Clean Excess Cell Formatting) :
Она мгновенно удаляет все излишнее форматирование до конца листа, оставляя его только внутри ваших таблиц и никак не повреждая ваши данные. Причем может это сделать даже для всех листов книги сразу.
Если вы не видите у себя в интерфейсе вкладку Inquire, то ее необходимо подключить на вкладке Разработчик - Надстройки COM (Developer - COM Addins) .
Проблема 4. Ненужные макросы и формы на VBA
Большие макросы на Visual Basic и особенно пользовательские формы с внедренной графикой могут весьма заметно утяжелять вашу книгу. Для удаления:
- нажмите Alt+F11, чтобы войти в редактор Visual Basic
- найдите окно Project Explorer’а (если его не видно, то выберите в меню View - Project Explorer)
- удалите все модули и все формы (правой кнопкой мыши - Remove - дальше в окне с вопросом о экспорте перед удалением - No):
Также код может содержаться в модулях листов - проверьте их тоже. Также можно просто сохранить файл в формате XLSX без поддержки макросов - все макросы и формы умрут автоматически. Также можно воспользоваться инструментом Очистить книгу от макросов из надстройки PLEX.
Проблема 5. Именованные диапазоны
Если в вашем файле используются именованные диапазоны (особенно с формулами, динамические или получаемые при фильтрации), то имеет смысл от них отказаться в пользу экономии размера книги. Посмотреть список имеющихся диапазонов можно нажав Ctrl+F3 или открыв окно Диспетчера имен (Name Manager) на вкладке Формулы (Formulas) :
Также вычищайте именованные диапазоны с ошибками (их можно быстро отобрать с помощью кнопки Фильтр в правом верхнем углу этого окна) - они вам точно не пригодятся.
Проблема 6. Фотографии высокого разрешения и невидимые автофигуры
Если речь идет о фотографиях, добавленных в книгу (особенно когда их много, например в каталоге продукции), то они, само-собой, увеличивают размер файла. Советую сжимать их, уменьшая разрешение до 96-150 точек на дюйм. На экране по качеству это совершенно не чувствуется, а размер файла уменьшает в разы. Для сжатия воспользуйтесь кнопкой Сжать рисунки (Compress pictures) на вкладке Формат (Format) :
Кроме видимых картинок на листе могут содержаться и невидимые изображения (рисунки, фотографии, автофигуры). Чтобы увидеть их, выделите любую картинку и на вкладке Формат (Format) нажмите кнопку Область выделения (Selection Pane) .
Для удаления вообще всех графических объектов на текущем листе можно использовать простой макрос:
Проблема 7. Исходные данные сводных таблиц
По-умолчанию Excel сохраняет данные для расчета сводной таблицы (pivot cache) внутри файла. Можно отказаться от этой возможности, заметно сократив размер файла, но увеличив время на обновление сводной при следующем открытии книги. Щелкните правой кнопкой мыши по сводной таблице и выберите команду Свойства таблицы (Pivot Table Properties) - вкладка Данные (Data) - снять флажок Сохранять исходные данные вместе с файлом (Save source data with file):
Если у вас несколько сводных таблиц на основе одного диапазона данных, то сократить размер файла здорово помогает метод, когда все сводные таблицы после первой строятся на основе уже созданного для первой таблицы кэша. В Excel 2000-2003 это делается выбором переключателя на первом шаге Мастера сводных таблиц при построении:
В Excel 2007-2016 кнопку Мастера сводных таблиц нужно добавлять на панель вручную - на ленте такой команды нет. Для этого щелкните по панели быстрого доступа правой кнопкой мыши и выберите Настройка панели быстрого доступа (Customize Quick Access Toolbar) и затем найдите в полном списке команд кнопку Мастер сводных таблиц (PivotTable and PivotChart Wizard) :
Проблема 8. Журнал изменений (логи) в файле с общим доступом
Если в вашем файле включен общий доступ на вкладке Рецензирование - Доступ к книге (Review - Share Workbook) , то внутри вашего файла Excel на специальном скрытом листе начинает сохраняться вся история изменений документа: кто, когда и как менял ячейки всех листов. По умолчанию, такой журнал сохраняет данные изменений за последние 30 дней, т.е. при активной работе с файлом, может запросто занимать несколько мегабайт.
Мораль: не используйте общий доступ без необходимости или сократите количество дней хранения данных журнала, используя вторую вкладку Подробнее (Advanced) в окне Доступ к книге. Там можно найти параметр Хранить журнал изменений в течение N дней (Keep change history for N days) или совсем отключить его:
Проблема 9. Много мусорных стилей
Про эту пакость я уже подробно писал ранее в статье о том, как победить ошибку "Слишком много форматов ячеек". Суть, если кратко, в том, что если вы разворачиваете на вкладке Главная список Стили ячеек (Home - Cell Styles) и видите там очень много непонятных и ненужных стилей, то это плохо - и для размера вашего файла Excel и для его быстродействия.
Удалить ненужные стили можно с помощью макроса или готовой команды из надстройки PLEX.
Проблема 10. Много примечаний
Примечания к ячейкам, конечно, не самый вредный момент из всех перечисленных. Но некоторые файлы могут содержать большое количество текста или даже картинок в примечаниях к ячейкам. Если примечания не содержат полезной для вас информации, то их можно легко удалить с помощью команды на вкладке Главная - Очистить - Очистить примечания (Home - Clear - Clear Comments) .
Например, вам нужно скопировать формулы в выбранном диапазоне из текущей книги в другую книгу без ссылки, как легко решить эту проблему в Excel? Здесь мы поделимся с вами тремя методами.
- Копирование формул из одной книги в другую без ссылки, изменяя формулы (6 ступени)
- Скопируйте формулы из одной книги в другую без ссылки, заменив формулы на текст (3 ступени)
- Копирование формул из одной книги в другую без ссылки с помощью Exact Copy (3 ступени)
- Копирование формул из одной книги в другую без ссылки с помощью автоматического текста
Копирование формул из одной книги в другую без ссылки, изменяя формулы
Чтобы ссылки на формулы не менялись во время копирования, мы можем немного изменить формулы, а затем скопировать. Вы можете сделать следующее:
1. Выберите диапазон, в который вы будете копировать формулы, и в нашем случае выберите Диапазон H1: H6, а затем нажмите Главная > Найти и выбрать > Замените. См. Снимок экрана ниже:
Внимание: Вы также можете открыть диалоговое окно «Найти и заменить», нажав кнопку Ctrl + H ключи одновременно.
2. В открывшемся диалоговом окне «Найти и заменить» введите = в Найти то, что поле, введите пробел в Заменить поле, а затем щелкните Заменить все кнопку.
Теперь появляется диалоговое окно Microsoft Excel, в котором указывается, сколько замен было выполнено. Просто нажмите на OK кнопку, чтобы закрыть его. И закройте диалоговое окно «Найти и заменить».
3. Продолжайте выбирать диапазон, копируйте их и вставляйте в целевую книгу.
4. Продолжайте выбирать вставленные формулы и откройте диалоговое окно «Найти и заменить», нажав Главная > Найти и выбрать > Замените.
5. В диалоговом окне «Найти и заменить» введите пробел в поле Найти то, что поле, введите = в Заменить поле, а затем щелкните Заменить все кнопку.
6. Закройте всплывающее диалоговое окно Microsoft Excel и диалоговое окно «Найти и заменить». Теперь вы увидите, что все формулы из исходной книги точно скопированы в целевую книгу. См. Снимки экрана ниже:
Заметки:
(1) Этот метод требует открытия как книги с формулами, из которых вы будете копировать, так и целевой книги, в которую вы будете вставлять.
(2) Эти методы изменят формулы в исходной книге. Вы можете восстановить формулы в исходной книге, выбрав их и повторив шаги 6 и 7 выше.
Легко объединяйте несколько листов / книг в один лист / книгу
Объединение десятков листов из разных книг в один может оказаться утомительным занятием. Но с Kutools for Excel's Объединить (рабочие листы и рабочие тетради) утилиту, вы можете сделать это всего за несколько кликов! Полнофункциональная бесплатная 30-дневная пробная версия!
Скопируйте формулы из одной книги в другую без ссылки, заменив формулы на текст
На самом деле, мы можем быстро преобразовать формулу в текст с помощью Kutools for Excel's Преобразовать формулу в текст функция одним щелчком мыши. А затем скопируйте текст формулы в другую книгу и, наконец, преобразуйте текст формулы в реальную формулу с помощью Kutools for Excel's Преобразовать текст в формулу функцию.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
1. Выберите ячейки формулы, которые вы скопируете, и нажмите Кутулс > Content > Преобразовать формулу в текст. Смотрите скриншот ниже:
2. Теперь выбранные формулы преобразованы в текст. Скопируйте их, а затем вставьте в целевую книгу.
3. Продолжайте выделять вставленный текст, а затем щелкните Кутулс > Content > Преобразовать текст в формулу для преобразования вставленного текста в формулу.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Копирование формул из одной книги в другую без ссылки с помощью Exact Copy
Этот метод представит Точная копия полезности Kutools for Excel, который может помочь вам с легкостью скопировать несколько формул в новую книгу.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
1. Выберите диапазон, в который вы будете копировать формулы. В нашем случае мы выбираем Range H1: H6, а затем щелкаем Кутулс > Точная копия. См. Снимок экрана:
2. В первом открывшемся диалоговом окне «Копирование точной формулы» нажмите кнопку OK кнопку.
3. Теперь откроется второе диалоговое окно «Копирование точной формулы», перейдите в целевую книгу, выберите пустую ячейку и щелкните значок OK кнопка. Смотрите скриншот выше.
Ноты:
(1) Если вы не можете переключиться на целевую книгу, введите адрес назначения, например [Book1] Sheet1! $ H $ 2 в приведенное выше диалоговое окно. (Book1 - имя целевой книги, Sheet1 - имя целевого рабочего листа, $ H $ 2 - конечная ячейка);
(2) Если вы установили вкладку Office (Получите бесплатную пробную версию), вы можете легко переключиться на целевую книгу, щелкнув вкладку.
(3) Этот метод требует открытия как книги с формулами, из которых вы будете копировать, так и целевой книги, в которую вы будете вставлять.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Копирование формул из одной книги в другую без ссылки с помощью автоматического текста
Иногда вам может потребоваться просто скопировать и сохранить сложную формулу в текущей книге и вставить ее в другие книги в будущем. Kutools for ExcelАвтора Авто текст Утилита позволяет копировать формулу как автоматический ввод текста и повторно использовать ее в других книгах одним щелчком мыши.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
1. Щелкните ячейку, в которую вы скопируете формулу, а затем выберите формулу в строке формул. См. Снимок экрана ниже:
2. В области навигации щелкните в крайнем левом углу панели навигации, чтобы переключиться на панель автоматического текста, щелкните, чтобы выбрать Формулы группу, а затем щелкните Добавить кнопка на вершине. См. Снимок экрана ниже:
3. В открывшемся диалоговом окне «Новый автотекст» введите имя для ввода автоматического текста формулы и щелкните значок Добавить кнопка. Смотрите скриншот выше.
4. Откройте целевую книгу или переключитесь на нее, выберите ячейку, а затем щелкните автоматический текст формулы, формула будет сразу вставлена в выбранную ячейку.
Автоматический текст формулы можно повторно использовать в любое время в любой книге одним щелчком мыши.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Демонстрация: копирование формул из одной книги в другую без ссылки
Одновременно копируйте и вставляйте ширину столбца и высоту строки только между диапазонами / листами в Excel
Если вы установили произвольную высоту строки и ширину столбца для диапазона, как вы могли бы быстро применить высоту строки и ширину столбца этого диапазона к другим диапазонам / листам в Excel? Kutools для Excel Копировать диапазоны Утилита поможет вам сделать это легко! Полнофункциональная бесплатная 30-дневная пробная версия!
Читайте также: