Содержание
- 0.1 Замена формул с помощью вычисляемых значений
- 0.2 Замена части формулы значением, полученным при ее вычислении
- 1 Дополнительные сведения
- 2 Замена формул на вычисленные значения в Excel 2013
- 3 Поиск или замена текста и чисел на листе
- 4 Замена формулы на значение
- 5 Замена формулы на значение
- 6 Заменить формулы на значения частично в столбце (В приложенном файле требуется избавиться от формул частично)
- 7 Автоматическая замена части формулы в ячейке Excel
Вы можете преобразовать содержимое ячейки, содержащей формулу, так, чтобы вычисленное значение заменило формулу. Если вы хотите закрепить только часть формулы, вы можете заменить только ту часть, которую вы не хотите пересчитать. Замена формулы на ее результат может быть полезной, если в книге есть большое количество или сложные формулы, и вы хотите повысить производительность, создав статические данные.
Вы можете преобразовать формулы в их значения на основе одной ячейки или преобразовать весь диапазон.
Важно: Убедитесь, что вы изучите влияние замены формулы на результаты, особенно если в формулах ссылаются другие ячейки, содержащие формулы. Перед заменой формулы ее результатом рекомендуется создать резервную копию книги.
В этой статье не рассматриваются параметры вычислений и способы их устранения. Сведения о том, как включить или отключить автоматическое перерасчет на листе, можно найти в разделе Изменение пересчета формул, итераций или точности.
Замена формул с помощью вычисляемых значений
При замене формул их значениями Excel навсегда удаляет формулы. Если вы случайно заменили формулу с помощью значения и хотите восстановить формулу, нажмите кнопку отменить сразу после ввода или вставки значения.
Выделите ячейку или диапазон ячеек, содержащих формулы.
Если формула является TE000126945, выделите диапазон ячеек, содержащих формулу массива.
Выбор диапазона, содержащего формулу массива
Щелкните ячейку в формуле массива.
На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.
Нажмите кнопку Дополнительный.
Нажмите кнопку Текущий массив.
Нажмите кнопку " копировать ".
Нажмите кнопку вставить .
Щелкните стрелку рядом с пунктом Параметры вставки и выберите пункт только значения.
В следующем примере показана формула в ячейке D2, которая умножает ячейки a2, B2 и скидку, полученную из C2, для расчета суммы счета для продажи. Чтобы скопировать фактическое значение вместо формулы из ячейки на другой лист или в другую книгу, можно преобразовать формулу в ее ячейку в ее значение, выполнив указанные ниже действия.
Нажмите клавишу F2, чтобы изменить значение в ячейке.
Нажмите клавишу F9 и нажмите клавишу ВВОД.
После преобразования ячейки из формулы в значение оно будет отображено как 1932,322 в строке формул. Обратите внимание, что 1932,322 является фактическим вычисленным значением, а 1932,32 — значением, которое отображается в ячейке в денежном формате.
Совет: При редактировании ячейки, содержащей формулу, можно нажать клавишу F9, чтобы полностью заменить формулу вычисляемым значением.
Замена части формулы значением, полученным при ее вычислении
Иногда возникает необходимость заменить только часть формулы на вычисленное значение. Например, вы хотите закрепить значение, используемое в качестве платежа для ссуды за автомобиль. Этот платеж рассчитывается на основе процента годового дохода за заем. В течение этого времени сумма дохода не изменится, поэтому вы хотите заблокировать оплату вниз в формуле, которая вычисляет платеж на основе различных сумм займа.
При замене части формулы на ее значение ее часть не может быть восстановлена.
Щелкните ячейку, содержащую формулу.
В TE000127058 выделите часть формулы, которую вы хотите заменить возвращаемым значением. При выделении части формулы, которую вы хотите заменить, убедитесь, что вы включите весь TE000126971. Например, если выбрана функция, необходимо выбрать имя функции, открывающую круглую скобку, аргументы и закрывающую круглую скобку.
Чтобы вычислить выделенный фрагмент, нажмите клавишу F9.
Чтобы заменить выделенный фрагмент формулы на вычисленное значение, нажмите клавишу ВВОД.
В ExcelOnline результаты уже отображаются в ячейке книги, а формула отображается только в строке формул .
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).
Замена формул на вычисленные значения в Excel 2013
Смотрите также количесву символов в приводил выше код N = Trim(Split(s,Pandora12 заканчиваются на 10ikki 1 раз иПомогите, пожалуйста, советом.и нажмите кнопку поле текст или числав книге стрелку в полеПримечание: нет. смежный диапазон ячеек,Когда Вы открываете лист первом числе на для цикла по ",")(1)) For Each: Здорово..А можно было
– в даном: для адаптантов? вставлять туда жеСитуация: в ячейкеОчиститьЗаменить на с определенным форматированием,.Найти Мы стараемся как можноЕсли необходимо преобразовать формулы либо выделить ячейки Excel и изменяете второе число. Меняет ячейкам с формулами c In ActiveSheet.UsedRange.Cells бы кнопку сделать примере на 14mihalix значения, но Excel D4 объединил значение.(или оставьте это нажмите кнопку
Как заменить всю формулу на константу
- Для поиска данных ви нажмите кнопку оперативнее обеспечивать вас в одной или по отдельности, используя некоторые данные или только в формулах. – меньше работы: If Right(c.Formula, Len(K)) де указываем число,
- например, однако через: _Boroda_, Вот так такого не допускает. ячеек А1, В1Для поиска текста или поле пустым, чтобыФормат строках или столбцах последнего поиска в актуальными справочными материалами нескольких ячейках, можете клавишу формулы на листе,
- Переделать в макрос? For Each c = K Then которое надо заменить автозамену это сделать выглядит часть таблицы.Приходится каждый блок и С1 с чисел на листе ничем не заменять
- и выберите нужные выберите в поле списке. на вашем языке. воспользоваться следующим способом:Ctrl
- Excel автоматически пересчитывает Легко! In Cells.SpecialCells(xlCellTypeFormulas) c.Formula = Left$(c.Formula, и число на не получается, так_Boroda_ зеленых копировать и
- сохранением их значения также можно использовать знаки), а затем
параметры в диалоговомПросматриватьВ условиях поиска можно Эта страница переведена сначала двойной щелчок
. все эти формулы.Sub Макрос() DimPandora12 Len(c.Formula) – Len(K)) которое надо сделать как $105, $109: Тогда так: вставлять значения.
Как заменить часть формулы на константу
посредством формулы =(A1&" функции ПОИСК и нажмите кнопку окневариант использовать подстановочные знаки, автоматически, поэтому ее
- мыши по ячейке,На вкладке Это может занять K, N s: Не работает кнопка & N End замену ?
также меняется накопируете Е3:Е27, вставляетеМожет быть есть ("&B1&") "&C1). НАЙТИ.Найти далееНайти формат
- по строкам например вопросительный знак текст может содержать затем клавиша
Home некоторое время, если = InputBox("Введите через в моем Excel If Next EndP.S.не запускается макрос, $145, $149, такзначениями какое то решение,Теперь я хочу
Поиск или замена текста и чисел на листе
неточности и грамматическиеF9(Главная) нажмите команду документ большой и запятую что на 2003. Выдает ошибку Sub требует объект "object как присутствует $10.в I3:I27 чтобы заменить все удалить ячейки А1,: Здравствуйте!Найти всеСовет:по столбцам (*). ошибки. Для нас.Copy содержит много формул.
что менять", "", типа нарушения вКазанский required" Как сделать, чтобыставите фильтр в
за 1 операцию. B1 и C1,Помогите, пожалуйста, советом.. Чтобы найти ячейки, точно.Звездочка используется для поиска
важно, чтобы этаВ случае, когда только
(Копировать), которая располагается Однако, если часть "10,14") If s " Advanced Find
: Все-таки важно пробегатьКазанский замена не происходила столбце Е по_Boroda_
но в этомСитуация: в ячейкеПримечание: соответствующие определенному формату,Для поиска данных с любой строки знаков. статья была вам часть формулы изменяется, в группе ячеек содержит формулы,
= "" Then and Replace" Able циклом только по: For Each c в тех числах,
желтым ячейкам, выделяете: Выделяете С3:С22, копируете, случае значение в D4 объединил значение Если поле можно удалить все конкретными свойствами выберите
Например, если ввести полезна. Просим вас а остальная остаетсяClipboard значения которых никогда Exit Sub K bits.com и предлагает
ячейкам с формулами, In ActiveSheet.UsedRange.Cells которые состоят «визуально» ячейки в столбце вставляете значениями. Фильтром ячейке D1 исчезнет, ячеек А1, В1Заменить на условия в поле в полег*д уделить пару секунд постоянной, Вы можете
(Буфер обмена), чтобы не изменятся, можно = Trim(Split(s, ",")(0)) купить эту программку иначе константы тоже
Можно оптимизировать: Sub в т.ч. и I, жмете Альт+Шифт+4 показываете только останется просто недействующая и С1 снедоступно, откройте вкладкуНайтиОбласть поиска, то будут найдены
и сообщить, помогла преобразовать часть формулы скопировать выделенные ячейки. с легкостью преобразовать N = Trim(Split(s, и инсталлировать ее. будут заменяться, например AVTOZAM() Dim c из заменяемого числа.
(или Главная -желтые формула. сохранением их значенияЗаменить, а затем выбратьвариант слова "год" и ли она вам, в статическое значение. Либо воспользуйтесь сочетанием
их в константы. ",")(1)) For Each Как этот скрипт 9 As Object, s$Благодарю и низко Найти и выделитьячейки, выделяете ихВопрос: как формулу посредством формулы =(A1&". ячейку с нужнымформулы "город". с помощью кнопок
Для этого: клавиш В этом уроке C In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) переписать в просто
10 For Each c кланяюсь.. – Выделение группы до строки 22, =(A1&" ("&B1&") "&C1) ("&B1&") "&C1).
При необходимости поиск можно форматированием в качестве,Вопросительный знак заменяет один внизу страницы. ДляЩелкните по ячейке, содержащейCtrl+C мы покажем Вам If Right(C.Formula, Len(K)) в макрос.. без
на 9 In Cells.SpecialCells(xlCellTypeFormulas) sAlex77755 ячеек – только жмете Альт+Шифт+4 (или заменить на то,Теперь я хочу отменить, нажав клавишу примера. Щелкните стрелкузначения любой знак. Например, удобства также приводим формулу, и выделите. простой способ замены = K Then кнопки тогда ?
14 = c.Formula If
: Несколько тысяч ссылок. видимые ячейки), жмете Главная – Найти что истинно находится удалить ячейки А1, ESC.
рядом с кнопкойили если ввести ссылку на оригинал часть формулы, которуюОткройте выпадающее меню команды как целой, так C.Formula = Left$(C.Formula, Действительно, как написал. Right$(s, 2) =Не стал бы Делит
и выделить - в ячейке D1 B1 и C1,Чтобы заменить одно илиФорматпримечанияг?д (на английском языке). необходимо преобразовать вPaste и части формулы, Len(C.Formula) – Len(K)) товрищ, скрипт меняетAlex77755
"10" Then Mid(s, такого делать никогдаснимаете фильтр в Выделение группы ячеек после удаления ячеек но в этом
все совпадения с, выберите пункт.
, то будут найденыФункции поиска и замены константу. Затем нажмите(Вставить) в группе на ее значение. & N End в том числе:
Советы
Len(s) – 1)заменить можно макросом столбце Е – только видимые А1, В1 и случае значение в введенным текстом, нажмитеВыбрать формат из ячейкиПримечание: слова "гад", "гид" в Excel используютсяF9ClipboardИмейте в виду, если If Next End и то, чтоКазанский = "14": c.FormulaSub AVTOZAM() Forвыделяете столбец I, ячейки), вставляете в С1. ячейке D1 исчезнет, кнопку, а затем щелкните
и "год". для поиска в.(Буфер обмена) и
Замена формулы на значение
Вы преобразовываете формулу Sub
менять НЕ требуется,
, = s Next Each c In копируете, выделяете столбец верхнюю желтую ячейкуВсем спасибо. останется просто недействующая
Заменить ячейку с форматированием,ФормулыСовет: книге необходимой информации,Выделяя часть формулы, убедитесь, выберите пункт в вычисленное значение
Dmitrii как раз, какСправедливо. End Sub UsedRange.Cells If Right(c.Formula, Е, спецвставка, ставите формулуКазанский
формула.
или которое требуется найти.,
Звездочки, знак вопроса и например определенного числа что Вы включаетеValues в одной и: Регулярные выражения должны
в примере, которыйМожет тогда так?
Замена формулы на значение
точку "Значения" и200?’200px’:”+(this.scrollHeight+5)+’px’);">=СУММ(C$2:C9)-СУММЕСЛИ(B$2:B9;"Итого";C$2:C9)*2
: Скопировать – вставить
Вопрос: как формулуЗаменить всеВыполните одно из указанныхзначения символы тильда (
) или текстовой строки. всю операцию в
(Значения) в разделе той же ячейке, сработать быстрее, чем он привел.. ИSub AVTOZAM() Dim: Sub AVTOZAM() s Then c.Formula =галку "Пропускать пустые ячейки"
и жмете Контрл+Ентер значение =(A1&" ("&B1&") "&C1). ниже действий.и можно найти вНа вкладке
выделенный фрагмент. Часть
Paste Values позднее Вы не строковые функции. Пример:
еще на один K, N s = InputBox("Введите через Replace(c.Formula, "10", "14"), жмете ОК – формула раскидается
Z заменить на то,
Microsoft Excel сохраняет параметрыЧтобы найти текст или
Заменить формулы на значения частично в столбце (В приложенном файле требуется избавиться от формул частично)
примечания данных листа перед
Главная формулы, которую Вы(Вставить значения). сможете вернуться назадSet objRegExp = момент обратил внимание..если = InputBox("Введите через
запятую что на Next End Submihalix только по желтым: Сначала сохранить формулу что истинно находится форматирования, которые можно
числа, нажмите кнопкудоступны только на их с тильда
в группе преобразуете, должна вычислятьсяВы также можете выбрать к формуле. Поэтому,
CreateObject("VBScript.RegExp") objRegExp.Pattern = надо заменить ОДНОЗНАЧНОЕ запятую что на что менять", "",Апострофф: _Boroda_, Спасибо! ячейкам. Снимаете фильтр. как значение, а в ячейке D1 определить. Если выНайти все вкладке в полеРедактирование до статического значения.
пункт рекомендуется сохранять резервную "10$" For Each число например 9 что менять", "",
"10,14") For Each:Pandora12mihalix затем удалить значения после удаления ячеек еще раз выполнитьилиПоиск
Найтинажмите кнопкуВыделенная часть формулы преобразуетсяPaste Special
копию документа Excel c In Cells.SpecialCells(xlCellTypeFormulas)
на 13,то замена
"10,14") If s c In ActiveSheet.UsedRange.CellsAlex77755
: Доброго времени суток: _Boroda_, Приложенный файл
в ячейках - А1, В1 и поиск на листе
Найти далее; на вкладке "
. Например, чтобы найтиНайти и выделить в константу. Нажмите
(Специальная вставка) в перед преобразованием формул. c.Formula = objRegExp.Replace(c.Formula, не производится .. = "" Then If Right(c.Formula, Len(Trim(Split(s,, 3-я строка If ! состален мной как ручками, макросом. С1.
данные и не.
Заменить данные, которые содержат.Enter нижней части выпадающего Вы также можете
"14") Next Set Скрипт начинает считать
Автоматическая замена части формулы в ячейке Excel
Exit Sub K ",")(0)))) = Trim(Split(s, Right(c.Formula, 2) =
Вот столкнулся с адаптивный пример, вikares
Всем спасибо. удается найти символы,Совет:
" доступны только «?», вы введитеВыполните одно из указанных, чтобы сохранить результат. меню. скопировать и вставить
objRegExp = Nothing и довольно быстро = Trim(Split(s, ",")(0)) ",")(0)) Then c.Formula 10 Then c.Formula такой пробелмой, ищу фактической таблице фраза: Понял. Спасибо!Казанский которые вы знаете При нажатии кнопкиформулы
? ниже действий.Преобразование формул в статическоеВ диалоговом окне формулы в другиеКазанский завершается, но ошибку N = Trim(Split(s,
= Left$(c.Formula, Len(c.Formula) = Left$(c.Formula, Len(c.Formula)
помощи.. "итого" не повторяется
RAN: Скопировать – вставить
содержал сведения, может
Найти все.как критерии поиска.Чтобы найти текст или значение предназначено дляPaste Special ячейки, сохраняя исходные
: И что, если не выдает, может ",")(1)) For Each – Len(Trim(Split(s, ",")(0)))) – 2) &На листе Excel в промежуточных итогах,: См. №2 значение потребоваться снимите нужные, каждого экземпляра условия,Для поиска данных сНажмите кнопку
числа, выберите пункт увеличения скорости обработки(Специальная вставка) выберите формулы. К тому заменять нечего, все что-то и меняет
C In ActiveSheet.UsedRange.Cells & Trim(Split(s, ",")(1))
14иначе для подобных 2003 имеет несколько каждый раз вместоmihalixZ параметры форматирования из которые вы ищете учетом регистра установите
ПараметрыНайти больших электронных таблиц,
пункт же, если в равно присваивать формулу?
(сразу не определишь If C.Text <> End If Next случаев с тысяч формул типа "итого" различающиеся фразы.: Здравствуйте.: Сначала сохранить формулу предыдущего поиска. В указываются и щелкнув флажок, чтобы определить дополнительные
. содержащих множество формул,Values вашей книге Excel Тогда уж -большой массив), но C.Formula Then If End SubReplaceКод =’C: Budget-_Boroda_В приложенном файле как значение, а диалоговом окне нужное вхождение в
Учитывать регистр
условия поиска приЧтобы найти и заменить или для того,(Значения). Здесь также настроен ручной подсчет.Test полноценной замены не Right(C.Formula, Len(K)) =Или так:результат м.б. непредсказуем 2013[Co-Financing17.xls]1.1′!$AR$10 =’C: Budget-: Тогда приложите реальный требуется избавиться от затем удалить значенияПоиск и замена списке сделает ячейки. необходимости: текст или числа,
чтобы скрыть формулы, предлагаются другие варианты формул, не забудьтедопишите. производится..во всех тех K Then C.FormulaSub AVTOZAM() Dim Результат 2013[Co-Financing17.xls]1.1′!$AА$10 =’C: Budget- файл (или его формул (заменив на
в ячейках -перейдите на вкладку active. Можно сортироватьДля поиска ячеек, содержащих
Для поиска данных на
выберите пункт
лежащие в основе вставки скопированных формул. обновить их, нажавPandora12 местах, где это = Left$(C.Formula, Len(C.Formula) K, N s=’C: Budget- 20 2013[Co-Financing17.xls]1.1′!$AB$10 =’C: Budget- кусок). значения) в столбце ручками, макросом. Поиск результаты только символы, введенные листе или воЗаменить документа Excel, предназначенногоТеперь выделенные ячейки вместо клавишу: Рябята..вроде зработало..Щас еще
требуется.. – Len(K)) & = InputBox("Введите через14 2013[Co-Financing17.xls]1.1′!$AC$105 =’C: Budget-Формулу можно и С в зеленыхikaresи нажмите кнопку
Найти в поле всей книге выберите. для общего доступа. формул содержат статическиеF9 поизучаю и наишу..Alex77755 N End If запятую что на[Co-Financing 2013[Co-Financing17.xls]1.1′!$AC$109 итд. переделать. Например, так ячейках оставив с: Понял. Спасибо!Параметрывсе, щелкнув заголовокНайти в полеВ полеУрок подготовлен для Вас значения.. респект и уважуха. : Проверял в своём End If Next что менять", "",14Нужно через автозамену200?’200px’:”+(this.scrollHeight+5)+’px’);">=СУММ(C$2:C9)-СУММЕСЛИ(A$2:A9;"";C$2:C9)*2 формулами желтые.RAN, чтобы открыть параметры столбца., установите флажокИскатьНайти командой сайта office-guru.ru
Запомните, если вы вставилиВыделите ячейки, содержащие формулы,Dmitrii
2003. End Sub "10,14") If s.xls]1.1′!$AR$ (заменить все) заменить- по пустомуЯ пробовал фильтровать: См. №2 форматирования. Щелкните стрелку
Чтобы заменить текст илиЯчейка целикомвариантвведите текст илиИсточник: http://www.howtogeek.com/164011/how-to-convert-a-formula-to-a-static-value-in-excel-2013/ значения в те которые необходимо заменить:Проверяет и меняетКазанский = "" Then14 последнюю цифру во значению в столбце по цвету. Копироватьikares рядом с полем числа, введите знаки.
на листе числа, которые нужноПеревел: Антон Андронов же самые ячейки,
на вычисленные значения.Казанский только последние символы: Проще If C.HasFormula Exit Sub KAlex77755 всех формулах, которые
А все зеленые за: Здравствуйте!Формат для замены вЕсли вы хотите найтиили
искать, или щелкнитеАвтор: Антон Андронов исходных формул больше Вы можете выделить
, это лишь пример. в количестве равном ThenНо я же = Trim(Split(s, ",")(0))
Порой бывают ситуации, когда вычисления в отчете выполняются на основании данных полученных из временной промежуточной таблицы. Эту таблицу нужно исключить из отчета, но при ее удалении полученные значения в отчете изменяются, так как «привязаны» к промежуточной таблице (черновику) формулами.
Для того, чтобы «отвязать» результаты расчетов от исходных данных следует заменить формулы в ячейках на их значения (цифры).
Сделать это можно скопировав столбец или строку и, выполнив вставку значений через меню быстрого доступа.
Это не всегда удобно. Например, если вы используете фильтры, значения вставятся не в нужные ячейки, а в ячейки под фильтрами. Кроме того, такая процедура не быстрая – несколько кликов и при замене нескольких тысяч строчек руки будут сильно уставать набора однообразных действий.
Естественно, любому пользователю хочется выполнять замену формул на значения в один клик, а лучше даже сочетанием горячих клавиш.
Для начала следует записать макрос замены.
- В панели разработчика создаем модуль (контейнер) для макроса:
- Кликаем на пиктограмму Visual Basic
- В открывшемся окне кликаем правой кнопкой мыши по книге и выбираем в контекстном меню insert => Module
- Открываем созданный модуль двойным щелчком мыши и записываем в него текст макроса.
Sub Замена_формул()
For Each cell In Selection
cell.Formula = cell.Value
Next
End Sub
Sub Замена_формул() – название макроса;
For Each cell In Selection – цикл повторяющихся действий от первой ячейки до конца выделения;
cell.Formula = cell.Value – действие по замене формулы на значение;
End Sub – конец макроса.
- После записи макроса следует зайти в панели разработчика в список макросов и кликнув по кнопке параметры назначить макросу сочетание клавиш.
Теперь если выделить диапазон с формулами и нажать заданное сочетание клавиш значения выделенных ячеек будут заменены на числа.
После замены формул на значения можно смело удалять промежуточные таблицы (черновики) из отчета .