Как сохранить значения в Excel без формул

Обновлено: 20.11.2024

Содержимое ячейки, содержащей формулу, можно преобразовать так, чтобы вычисляемое значение заменяло формулу. Если вы хотите заморозить только часть формулы, вы можете заменить только ту часть, которую не хотите пересчитывать. Замена формулы ее результатом может быть полезна, если в книге много или сложные формулы и вы хотите повысить производительность за счет создания статических данных.

Вы можете преобразовывать формулы в их значения как для каждой ячейки, так и для всего диапазона сразу.

Важно! Обязательно изучите последствия замены формулы ее результатами, особенно если формулы ссылаются на другие ячейки, содержащие формулы. Рекомендуется сделать копию рабочей книги, прежде чем заменять формулу ее результатами.

В этой статье не рассматриваются параметры и методы расчета. Чтобы узнать, как включить или отключить автоматический пересчет для рабочего листа, см. статью Изменение пересчета формулы, итерации или точности.

Заменить формулы их расчетными значениями

При замене формул их значениями Excel навсегда удаляет формулы. Если вы случайно заменили формулу значением и хотите восстановить формулу, нажмите «Отменить» сразу после ввода или вставки значения.

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

Если формула представляет собой формулу массива, выберите диапазон, содержащий формулу массива.

Как выбрать диапазон, содержащий формулу массива

Нажмите ячейку в формуле массива.

На вкладке "Главная" в группе "Редактирование" нажмите "Найти и выбрать", а затем нажмите "Перейти".

Нажмите "Специальные".

Нажмите Текущий массив.

Нажмите "Копировать" .

Нажмите "Вставить" .

Нажмите стрелку рядом с параметром "Параметры вставки" и выберите "Только значения".

В следующем примере показана формула в ячейке D2, которая умножает ячейки A2, B2 и скидку, полученную из C2, для расчета суммы счета-фактуры для продажи. Чтобы скопировать фактическое значение вместо формулы из ячейки на другой лист или книгу, вы можете преобразовать формулу в своей ячейке в ее значение, выполнив следующие действия:

Нажмите F2, чтобы изменить ячейку.

Нажмите F9, а затем нажмите клавишу ВВОД.

После преобразования ячейки из формулы в значение значение отображается как 1932,322 в строке формул. Обратите внимание, что 1932,322 — это фактическое расчетное значение, а 1932,32 — это значение, отображаемое в ячейке в денежном формате.

Совет. Когда вы редактируете ячейку, содержащую формулу, вы можете нажать F9, чтобы навсегда заменить формулу ее вычисленным значением.

Заменить часть формулы ее вычисленным значением

Могут быть случаи, когда вы хотите заменить только часть формулы ее вычисленным значением. Например, вы хотите зафиксировать значение, которое используется в качестве первоначального взноса для автокредита. Этот первоначальный взнос был рассчитан на основе процента от годового дохода заемщика. В настоящее время эта сумма дохода не изменится, поэтому вы хотите зафиксировать авансовый платеж в формуле, которая вычисляет платеж на основе различных сумм кредита.

При замене части формулы ее значением эта часть формулы не может быть восстановлена.

Нажмите ячейку, содержащую формулу.

В строке формул выберите часть формулы, которую вы хотите заменить вычисленным значением. Когда вы выбираете часть формулы, которую хотите заменить, убедитесь, что вы включили весь операнд. Например, если вы выбираете функцию, вы должны выбрать полное имя функции, открывающую скобку, аргументы и закрывающую скобку.

Чтобы рассчитать выбранную часть, нажмите F9.

Чтобы заменить выбранную часть формулы ее вычисленным значением, нажмите клавишу ВВОД.

В Excel для Интернета результаты уже отображаются в ячейке книги, а формула отображается только в строке формул.

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

Лори Кауфман

Лори Кауфман
Писатель

Лори Кауфман – специалист по технологиям с 25-летним опытом работы. Она была старшим техническим писателем, работала программистом и даже управляла собственным бизнесом в нескольких местах. Подробнее.

Допустим, вы создали книгу Excel, которую нужно распространить, но вы не можете раскрыть формулы, которые использовали в этой книге. Мы покажем вам простой прием, позволяющий скопировать лист в другую книгу и быстро удалить формулы, чтобы отображались только результаты.

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

Чтобы создать рабочую книгу, из которой будут удалены формулы на листах, откройте исходную рабочую книгу Excel и выберите вкладку для рабочего листа, содержащего конфиденциальные формулы. Щелкните правой кнопкой мыши вкладку этого рабочего листа и выберите «Переместить или скопировать» во всплывающем меню.

В диалоговом окне «Переместить или скопировать» выберите «(новая книга)» в раскрывающемся списке «В книгу». Установите флажок «Создать копию», чтобы в поле стояла галочка. Это гарантирует, что лист будет скопирован в новую книгу, а не перемещен из исходной книги. Нажмите «ОК».

ПРИМЕЧАНИЕ. В раскрывающемся списке "В книгу" можно выбрать любые открытые книги.

Создается новая рабочая книга Excel, и скопированный рабочий лист вставляется в рабочую книгу вместе с именем вкладки рабочего листа. Вы можете сохранить эту книгу под другим именем, чтобы сохранить исходную книгу.

Выделите все ячейки на листе, который вы только что скопировали в эту книгу, нажав Ctrl+A или щелкнув квадрат в верхнем левом углу ячеек между буквами столбца и номерами строк.

Убедитесь, что вкладка "Главная" активна. В разделе «Буфер обмена» нажмите стрелку вниз на кнопке «Вставить», а затем нажмите кнопку «Значения» в разделе «Вставить значения» раскрывающегося меню, как показано на рисунке ниже.

Теперь рабочий лист содержит только результаты формул исходного рабочего листа, а не сами формулы.

Выполните эту процедуру для каждого листа в исходной книге, из которой вы хотите удалить формулы. Создав новую книгу, оставьте ее открытой, и вы сможете выбрать эту книгу в раскрывающемся списке «В книгу» в диалоговом окне «Переместить или скопировать».

› Как восстановить метки панели задач в Windows 11
›5 шрифтов, которые следует прекратить использовать (и лучшие альтернативы)
› Как установить Google Play Маркет в Windows 11
› Почему не было Windows 9?
› Что означает XD и как вы его используете?
› Почему прозрачные чехлы для телефонов желтеют?

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

Эту задачу довольно легко выполнить как с макросом, так и без него. Если вы хотите сделать это без макроса, выполните следующие действия:

Щелкните правой кнопкой мыши вкладку листа, который хотите скопировать. Excel отображает контекстное меню.
Выберите «Переместить» или «Копировать лист» в контекстном меню. Excel отображает диалоговое окно «Переместить или скопировать». (См. рис. 1.)

Рисунок 1. Диалоговое окно «Переместить или скопировать».

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

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

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию. Нажмите здесь, чтобы открыть эту специальную страницу в новой вкладке браузера.

Биография автора

На его счету более 50 научно-популярных книг и множество журнальных статей. Аллен Уайатт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене.

БОЛЬШЕ ОТ АЛЛЕНА

Начало нового поиска

Я много ищу в своих документах. Иногда поиски могут идти не совсем так, как я ожидал. Вот некоторые вещи, которые я .

Полезные всплывающие подсказки

Экранные подсказки могут быть полезны людям, читающим ваш документ на экране. Используя технику, описанную здесь, вы можете добавить .

Предложения Майкрософт относительно Word

Есть идея, как сделать Word лучше? Microsoft хочет услышать от вас и создала веб-сайт, чтобы запросить ваши .

Успешно запрограммируйте в Excel! Имя Джона Уокенбаха является синонимом мастерства в расшифровке сложных технических вопросов. В этом всеобъемлющем руководстве «Г-н Электронная таблица» показывает, как максимально использовать возможности Excel, используя профессиональные советы по разработке приложений для работы с электронными таблицами из его личной книжной полки. Ознакомьтесь с Excel 2013 Power Programming with VBA сегодня!

Дополнительные советы по Excel (лента)

Переход к определенному рабочему листу

Хотите быстро перемещаться с одного рабочего листа на другой? Вот как выполнить задание, когда у вас много файлов .

Переключение заголовков в закрепленной строке

Excel позволяет "замораживать" строки на листе. Что делать, если вы хотите, чтобы замороженные строки менялись при прокрутке .

Переход к алфавитным таблицам

У вас есть книга с большим количеством рабочих листов? Вот несколько удобных способов перехода к нужному листу в алфавитном порядке.

БЕСПЛАТНАЯ СЛУЖБА. Получайте подобные советы каждую неделю в ExcelTips, бесплатном информационном бюллетене по повышению эффективности. Введите свой адрес и нажмите "Подписаться".

Я попробовал макрос, как показано на рисунке. В результате скопированная вкладка в новой книге сохранила формулы, а исходная вкладка в исходном файле не содержала формул.

Range("B6").Копировать
[c8].PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Копировать из B6 в C8

Эдель,
Действуйте, как описано выше, после того как вы вставили значения, выполните Специальную вставку и выберите Форматы. Все форматирование возвращается как исходный лист.

Исходное форматирование полностью удалено, все еще ищут решение, которое убирает формулы, но оставляет форматирование.

Здравствуйте!
Я использую приведенный ниже код для сохранения даты с одного листа на другой, и он отлично работает. Все, что я хочу, это скопировать только ЗНАЧЕНИЯ вместо формул на другой лист. Есть предложения?
-------------------------------------------------------------------------- ---------------------
Sub Save()
Dim SourceRange As Range, DestRange As Range
Dim DestSheet As Worksheet, Lr Пока

С приложением
.ScreenUpdating = False
.EnableEvents = False
Конец с

'заполнить исходный лист и диапазон
Set SourceRange = Sheets("Invoice").Range("A1:J49")

'Заполните лист назначения и вызовите функцию LastRow
', чтобы найти последнюю строку,
Set DestSheet = Sheets("Сохраненные копии счетов-фактур")
Lr = DestSheet.Cells( Rows.Count, "A").End(xlUp).Row

'Используя информацию из функции LastRow, мы можем
'создать ячейку назначения и скопировать/вставить исходный диапазон
Set DestRange = DestSheet.Range("A" & Lr + 1)
SourceRange.Copy DestRange

С приложением
.ScreenUpdating = True
.EnableEvents = True
Конец с

У меня Excel 2016. Я сделал перемещение, скопировал прошлые значения, и все форматирование исчезло. Я попытался скопировать в буфер обмена, но выдает сообщение об ошибке, в котором говорится: «Мы не можем внести это изменение для выбранных ячеек, потому что оно повлияет на сводную таблицу. Я пытаюсь заменить сводную таблицу в новой книге, чтобы удалить ссылку на исходный файл. Мне нужна версия, которую я могу отправить по электронной почте без ссылок. Кто-нибудь может помочь мне с этой проблемой?

В ответ на опасения, высказанные Бобом и Стивом Кларком, может потребоваться другой подход. Хотя я постоянно использую функцию «Создать копию», в данном случае я бы вместо этого выполнил следующие шаги:

Создайте новую книгу (например, Ctrl-N создаст новую книгу Book1.xls в моей версии Excel). Если вам нужно, чтобы это был файл .xlsx (особенно потому, что вы предполагаете иметь более 256 столбцов и/или 65 526 строк), создайте новый файл Book1.xlsx.

В исходной книге и листе используйте Ctrl-A, чтобы выбрать Все данные. Ctrl-C копирует его в буфер обмена.

Перейдите к новой целевой книге и рабочему листу, поместите их в ячейку A1. Paste-Special-Values либо с помощью механизма, указанного Алленом, либо с помощью Alt-E-S-V (я делаю это часто, поэтому я запомнил сочетания клавиш Paste-Special).

При таком подходе вам будет не хватать форматирования, которое присутствовало в исходном диапазоне, включая высоту строк и ширину столбцов, а также определенные имена, которые подход Аллена неявно скопировал бы. Если вам нужно форматирование, вы можете сделать вторую Специальную вставку - на этот раз с форматированием (Alt-E-S-T - это не Alt-E-S-F, потому что это копирует формулы, что, конечно, вам не нужно). Если вам нужно скопировать ширину столбцов, это еще одна специальная вставка (Alt-E-S-W). Я не знаю, есть ли способ перенести высоту строк — как ни странно, это не опция «Специальная вставка», по крайней мере, не в версиях Excel, которые я использовал. Но Autofit Row Height (Alt-O-R-A) установит для всех строк минимальную высоту, соответствующую их содержимому, оставляя пустые строки без изменений.

Очевидно, что если вам нужно все форматирование, подход Аллена лучше моего, но если, как и у Стива, на целевом листе нет нужных вам данных, тогда мой подход победит.

Спасибо,
Стив С.

Я сделал все вышеперечисленное, и это не сработало. :-(. Мой рабочий лист Excel был экспортирован из MS Dynamics как статический рабочий лист, так как я хочу иметь возможность редактировать данные, но я все еще не могу их редактировать, несмотря на то, что пробовал много разных типов копирования и вставки.< /p>

У меня есть рабочая книга, в которой пользователь вводит данные на одном листе и извлекает (после форматирования и синтаксического анализа — отчет, если хотите) на другом листе.
Извлечение выполняется с помощью макроса, поэтому пользователь только нажимает кнопку после ввода данных и получает отчет во второй рабочей книге, названной по дате.
Моя проблема. Я хочу защитить лист извлечения (отчет), чтобы пользователь не мог непреднамеренно или намеренно изменить формулы, составляющие отчет. Но при запуске макроса он зависает на защищенном листе, крутится или вылетает. Когда я использую команду Unprotect.Sheet, ей требуется пароль (который я хотел бы вставить автоматически), и после извлечения он не повторно защищает лист - по крайней мере, не так, как я пытался это сделать (через Макросы).
Так что же я упускаю?

И да, я понимаю, что могу воспользоваться этим преимуществом, но я впадаю в отчаяние.

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

Чтобы управлять тем, что копируется в целевой диапазон (группу ячеек), на вкладке "Главная" в группе "Буфер обмена" нажмите "Вставить", а затем выполните одно из следующих действий:

Вставить группу:

Группа "Вставить значения":

Группа «Другие параметры вставки»:

Вставить группу:

Все: эквивалентно использованию команды "Вставить". Он копирует содержимое ячейки, форматы и проверку данных из буфера обмена Windows.
Формулы: копируются только формулы, содержащиеся в исходном диапазоне.
Значения. Копирует результаты формул. Местом назначения копии может быть новый диапазон или исходный диапазон. В последнем случае Excel заменяет исходные формулы их текущими значениями.
Форматы: копирование только форматирования.
Комментарии: копирование только комментариев из ячейки или диапазона. Этот параметр не копирует содержимое или форматирование ячейки.
Проверка: копируются критерии проверки, чтобы применялась та же проверка данных.
Все с использованием исходной темы: вставляет все, но использует форматирование исходной темы документа. Этот параметр актуален только в том случае, если вы вставляете информацию из другой книги, а в книге используется тема документа, отличная от активной книги.
Все, кроме границ: копирует все, кроме границ, которые появляются в исходном диапазоне.
Ширина столбца: копирует информацию о ширине столбца из одного столбца в другой.
Формулы и числовые форматы. Копируются все формулы и числовые форматы, но не значения.
Значения и числовые форматы. Копируются все текущие значения и числовые форматы, но не сами формулы.
Объединение всех условных форматов: объединяет скопированное условное форматирование с любым условным форматированием в целевом диапазоне.Этот параметр доступен только при копировании диапазона, содержащего условное форматирование.

Группа операций: позволяет выполнять арифметические операции над значениями и формулами в целевом диапазоне. Эта функция также работает с одной скопированной ячейкой, вставленной в диапазон из нескольких ячеек.

Пропускать пробелы: запрещает Excel перезаписывать содержимое ячеек в области вставки пустыми ячейками из скопированного диапазона. Этот параметр полезен, если вы копируете диапазон в другую область, но не хотите, чтобы пустые ячейки в скопированном диапазоне перезаписывали существующие данные.

Транспонировать: изменяет ориентацию скопированного диапазона. Строки становятся столбцами, а столбцы становятся строками. Все формулы в скопированном диапазоне корректируются таким образом, чтобы они правильно работали при транспонировании.

Создание раскрывающегося списка в ячейке

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

Группировка и разгруппировка листов

Листы могут быть сгруппированы (соединены), чтобы облегчить создание или форматирование двух или более одинаковых листов. Например, вы можете создать один лист и скопировать его на два других листа, или вы можете сгруппировать три пустых листа и ввести и отформатировать содержимое один раз — последующее копирование не требуется.

Как создать маркированную диаграмму в Excel

Списочная диаграмма (название по имени ее создателя Стивен Фью) или маркированная диаграмма (в Microsoft Office) предназначена для демонстрации разницы между целевым значением и фактическим. Иногда рядом со шкалой текущих значений включены диапазоны производительности — они показывают что-то вроде диапазонов значений «низкий», «средний» и «высокий» или «плохой», «удовлетворительный», «хороший» и «отличный»:

Читайте также: