Как сохранить значения в excel без формул
Обновлено: 04.07.2024
Главная Предложения и вопросы Надстройки Данные без формул
Данные без формул
Надстройка может быть полезна при работе со сложными файлами Excel, в которых содержатся макросы, пользовательские функции на VBA, формулы со ссылками на другие рабочие книги и пр. Трудности возникают при попытке открыть такие файлы на других компьютерах, где, например, не активированы макросы. Другая проблема - размер файла. Если требуется только просмотр данных, то совсем не обязательно пересылать мегабайты информации с формулами и скрытыми вычислениями. Обычно в этом случае готовится отдельный файл с копией данных со значениями и форматами, но без формул. Частичный перенос информации диапазонами ячеек изменяет настройки печати страницы, надежнее переносить данные в виде полных копий рабочих листов. Надстройка datasnapshot.xla позволяет автоматизировать этот процесс.
Оставьте видимыми только нужные листы с информацией, затем запустите основную процедуру надстройки через нажатие Ctrl+w (Ctrl+ц).
После подтверждения запуска на всех не скрытых листах исходного файла во всех ячейках формулы заменяются на значения, а сводные таблицы преобразуются в обычные. Также удаляются элементы управления Forms (кнопки, списки, флажки), другие графические элементы сохраняются. Затем эти листы переносятся в новую рабочую книгу. Исходный файл закрывается без сохранения изменений.
В файле-отчете сохраняется все форматирование. Формулы остаются только на уровне условного форматирования. Может изменится внешний вид, если эти формулы ссылались на скрытые листы исходного файла (это теоретически возможно).
Файл надстройки datasnapshot.xla совместим со всеми версиями Excel. Устанавливается стандартными средствами Excel:
Как уже отмечалось, надстройка резервирует сочетание клавиш Ctrl+w (Ctrl+ц). При этом стандартная функциональность этого сочетания клавиш (закрытие активной книги) отключается.
Если надстройка активна, то при нажатии Ctrl+w (Ctrl+ц) в активном файле появится следующее предупреждение:
Будьте внимательны, если исходный файл не сохранен, то лучше нажать "Нет" и сохранить данные.
По нажатию кнопки "Да" запустится процесс создания нового файла Excel, в котором будут только значения из текущего файла, будут удалены все ссылки, формулы, элементы управления, макросы и пр. В результате получится файл небольшого объема, который может использоваться для печати и пересылке по электронной почте.
Удалить формулы в Excel можно множеством способов, каждый из которых сильно зависит от поставленной задачи. Как правило, при этом требуется сохранить в ячейках какие-то значения, вариантов два:
- либо это значения, вычисленные этими формулами
- либо сам текст формул
Помимо вопроса о том, что оставить в ячейках после удаления формул, могут быть и другие вводные, такие как:
- где удалять формулы:
- в диапазоне,
- всем листе
- или всей книге
Эта статья даст ответы на все такие вопросы.
Удалить формулы и сохранить данные
Наиболее распространенная задача — когда нужно оставить в ячейке результат формулы, а саму формулу — удалить. В понимании Excel результат формулы является значением ячейки.
Так вот, чтобы перевести формулы в значения, нужно:
- Выделить нужный диапазон ячеек (или одну ячейку, всю строку, столбец или весь лист)
- Скопировать выделенное
- Вставить процедурой вставки «как значения»
Можно сделать это клавишами мыши или клавиатуры, суть процедуры от этого не меняется.
С помощью мыши
Не самый быстрый, т.к. работает только одна рука, но самый удобный и понятный способ. Выделяем, копируем, и в контекстном меню, вызываемом нажатием правой кнопки мыши по выделенному диапазону, нужно выбрать вот эту команду (выделена красным):
Команда «вставить как значения» в командном меню вставки в Excel![Команда "вставить как значения" в командном меню вставки в Excel]()
С помощью клавиатуры (сочетания клавиш)
Когда копирование делается в соседний столбец или в другую книгу, я предпочитаю этот способ, хоть он и экономит лишь секунды.
- выделяем диапазон
- мышью, если это столбец, строка или лист
- сочетаниями Ctrl+Shift+стрелки или Shift+стрелки, если ячейки или диапазоны ячеек
- копируем сочетанием Ctrl+C
- перемещаемся стрелками к диапазону, куда нужно вставить данные и/или Ctrl+Tab для перехода в другую книгу
- вызываем контекстное меню клавишей на клавиатуре, иногда ее нет, но обычно на клавиатурах она есть, рядом с правым ALT
- Стрелками перемещаемся к команде (Вниз-вниз-вправо)
- Клавиша Enter
Может показаться, что тут целых 6 шагов и это долго, но попробуйте приловчиться и сравнить работу на больших диапазонах ячеек — мышью будет точно дольше.
В 2 клика с надстройкой !SEMTools
Быструю замену формул на значения в выделенном диапазоне без необходимости копирования и особой вставки я реализовал в своей надстройке для Excel !SEMTools.
Для тех, кто не готов разбираться в VBA, после установки !SEMTools запуск процедуры доступен в 2 клика из соответствующего меню в группе «УДАЛИТЬ«. Функционал относится к бесплатным возможностям !SEMTools (работают без ограничений в любой версии).
Удалить формулы и сохранить значения в Excel с !SEMTools
В видимых ячейках, пропуская скрытые фильтром
Вот здесь при использовании стандартных решений уже могут возникнуть проблемы. Процедура вставки «как значения» даст сбой и повредит данные, если в копируемом диапазоне есть отфильтрованные строки.
Он отработает на отлично, как и та же процедура надстройки. В синтаксисе кода присутствует проверка ячеек на предмет того, скрыты ли они.
Удаляем формулы только в отфильтрованных строках, пропуская скрытые
Удалить формулы на листе / во всех листах
Когда нужно быстро избавиться от всех формул на листе или во всей книге, также могут помочь соответствующие простенькие процедуры, написанные на VBA:
Ими можно воспользоваться без внедрения в свои файлы, подключив надстройку !SEMTools.
Удаляем формулы со всего листа с !SEMTools
Перевод формул в их текст (без сохранения результирующих значений)
Иногда требуется для демонстрационных целей преобразовать ячейки, оставив текст формул. В таких случаях полезной будет процедура замены.
![Перевести формулы в текст]()
Выделяем диапазон формул, жмем Ctrl+H («эйч», англ.), чтобы запустить диалоговое окно «Найти и заменить«, и в соответствующих окошках вбиваем значения.
![Формулы как текст]()
Апостроф в результате замены не будет виден и они будут выглядеть идентично их написанию, но восприниматься формулы будут как текст.
Установка, настройка и работа в пакете Microsoft Office.
Администратор раздела: Megaloman (Мастер-Эксперт)
Как проще всего сохранить 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]
Black Cloud, а как бы я макрос запустил из одной для другой?)
Андреенков Владимир
Почему из одной для другой? К, примеру, сначала, делаете, копию листа внутри книги оригинала, записываете, макрос, удаляете лишний лист, сохраняете, теперь в оригинале книги есть макрос, и он будет в каждой его копии, т.е. когда Вы делаете Сохранить как. сохраняете под другим именем, то макрос в новом файле тоже будет. Поэтому Ваш вопрос непонятен.
=====
Правильно заданный вопрос должен содержать ответ!Александр Романов [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 написал. Просто немного переделав макрос созданный екселем. Не думаю что этот способ можно считать более сложным чем Спец. вставка.При удалении формулы также удаляется ее результат. Если вы не хотите удалять значение, можно удалить только формулу.
Удаление формулы
Вы выберите ячейку или диапазон ячеек, содержащих формулу.
Нажмите клавишу DELETE.
Удаление формулы с сохранением результатов
Для этого нужно скопировать формулу, а затем вкопировать ее в ту же ячейку с помощью параметра Вировать значения.
Выберем ячейку или диапазон ячеек, которые содержат формулу.
Если формула является формулой массива, необходимо сначала выбрать все ячейки в диапазоне ячеек, которые содержат формулу массива:Щелкните ячейку в формуле массива.
На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.
Выберите текущий массив.
На вкладке Главная в группе Буфер обмена нажмите кнопку Копировать .
На вкладке Главная в группе Буфер обмена щелкните стрелку под кнопкой , а затем выберите вкладки Значения.
Удаление формулы массива
Чтобы удалить формулу массива, выберите все ячейки в диапазоне ячеек, которые содержат формулу массива. Для этого:
Щелкните ячейку в формуле массива.
На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.
Выберите текущий массив.
Нажмите клавишу DELETE.
Удаление формулы с сохранением результатов
Выберем ячейку или диапазон ячеек, которые содержат формулу.
Щелкните Главная > Копировать (или нажмите CTRL+C).
![Команда "Копировать" в группе "Буфер обмена"]()
Щелкните главная > под кнопкой В >в таблице.
![Команда "Вставить" с параметром "Вставить значения" в Excel Web App]()
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Читайте также:
