Как заменить формулу на значение в экселе: Замена формулы ее результатом в Excel для Mac

Замена формулы ее результатом в Excel для Mac

Excel для Microsoft 365 для Mac Excel 2021 for Mac Excel 2019 для Mac Excel 2016 для Mac Excel для Mac 2011 Еще…Меньше

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

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

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

1. Выделите ячейку с формулой.

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

2. На вкладке Главная нажмите кнопку Копировать.

3. На вкладке Главная нажмите кнопку Вировать.

4. Щелкните стрелку рядом с кнопкой Параметры в и выберите только значения.

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

1. Выделите ячейку с формулой.

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

2. На панели инструментов Стандартная нажмите кнопку Копировать .

3. org/ListItem»>

   На панели инструментов Стандартная нажмите кнопку В .

4. Щелкните стрелку рядом с кнопкой Параметры в и выберите только значения.

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

Внимание: После замены части формулы ее значением восстановить эту часть не удастся.

1. Выделите ячейку с формулой.

2. В строка формул выделите часть формулы, которую нужно заменить значением, полученным при ее вычислении.

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

3. нажмите клавиши +=.

   В приложении Excel будет выполнено вычисление выделенной части формулы.

4. Нажмите клавишу RETURN.

   Если формула является формула массива, нажмите +RETURN.

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

Дополнительные сведения

Изменение режима пересчета, числа итераций или точности формул

Копирование и в копирование определенных результатов в ячейке

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

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

В силу различных обстоятельств иногда возникает потребность заменить формулы в ячейках значениями. Если это связный прямоугольный диапазон, сделать такую замену весьма просто. Выделите диапазон, нажмите Ctrl+C. Не снимая выделения, кликните правой кнопкой мыши в любом месте выделенного диапазона, выберите Специальная вставка, и в открывшемся окне выберите значения. Нажмите Ok. Если вы любите клавиатурные сокращения, то для специальной вставки используйте Alt+Я+М+З (версия для Excel 2016 и позднее; все буквы русские).

Рис. 1. Вставить только значения

Скачать заметку в формате Word или pdf

Иногда заменить формулы на значения нужно в отдельных ячейках, или в нескольких несвязанных диапазонах. У меня такая потребность возникала в следующих ситуациях. 1. Использовал модель на основе функции СЛЧИС(), а затем презентовал результаты в отчете (Excel, Word, PowerPoint). Требовалось, чтобы вид графиков в Excel и в презентации совпадал. Поскольку волатильная функция СЛЧИС() пересчитывается при каждом изменении в книге, нужно было сгенерировать модель и графики на ее основе, а затем избавиться от пересчета. 2. Построил отчет о продажах на основе функций кубов (в частности КУБЗНАЧЕНИЕ). В отчете представлены, как исторические данные, так и текущие. Исходных данных очень много, и при каждом обновлении пересчитываются все суммы в отчете. Понятно, что исторические данные не изменяются, и в них формулы можно заменить на значения.

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

Выделение ячеек

Чтобы выделить ячейки, содержащие определенную формулу или фрагмент формулы, выберите диапазон, покрывающий все ячейки, требующие замены (естественно, диапазон будет содержать и некоторые ячейки, которые не нужно изменять). Если требуется выберите все ячейки на листе. Для этого нажмите Ctrl+Ф (или А английское). По возможности избегайте выделения всего листа, так как большое количество ячеек обрабатывается дольше.

Вызовите окно поиска – Ctrl+F (или А русское). Введите строку для поиска в поле Найти. В нашем примере мы заменяем значениями формулы кубов, относящиеся к 2002 году. Строка «2002» содержится в формуле КУБЗНАЧЕНИЕ только для ячеек, извлекающих данные за соответствующий год (см. цифру 1 на рис. 2).

Нажмите кнопку Параметры (2) и в окне появятся дополнительные опции. Проверьте, что Область поиска = формулы, а галочка Ячейка целиком отсутствует (3). Нажмите кнопку Найти все (4). В нижней части окна появятся строки с ячейками, удовлетворяющими условиям поиска. Выберите первый элемент (5), а затем нажмите Ctrl+Ф, чтобы выбрать все элементы в списке. Нажмите Закрыть (6). Ячейки, содержащие формулу куба и относящиеся к 2002 г. выбраны на листе Excel.

Рис. 2. Окно Найти; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Замена формул на значения

Напишите и запустите следующий макрос:

Sub Paste_Values_Selection()
Dim myCell As Range
    For Each myCell In Selection. Cells
        myCell.Value = myCell.Value
    Next myCell
End Sub

Макрос заменит все формулы в выбранных ячейках значениями. Будьте внимательны, работу макроса нельзя отменить. Поэтому экспериментируйте на копии вашей боевой книги Excel.

Идея подсмотрена у Jon Acampora.

Замена формулы ее результатом в Excel для Mac

Excel для Microsoft 365 для Mac Excel 2021 для Mac Excel 2019 для Mac Excel 2016 для Mac Excel для Mac 2011 Больше…Меньше

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

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

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

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

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

2. org/ListItem»>

   На вкладке Главная нажмите Копировать .

3. На вкладке Главная щелкните Вставить .

4. Щелкните стрелку рядом с Параметры вставки   и выберите Только значения .

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

org/ItemList»>

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

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

2. На панели инструментов Standard нажмите Копировать .

3. На стандарте , нажмите Вставить .

4. Щелкните стрелку рядом с Параметры вставки   и выберите Только значения .

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

Внимание!  При замене части формулы ее значением ее нельзя восстановить.

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

2. В строке формул выберите часть формулы, которую вы хотите заменить вычисленным значением.

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

3. Нажмите + = .

   Excel вычисляет выбранную часть формулы.

4. org/ListItem»>

   Нажмите ВОЗВРАТ.

   Если формула является формулой массива, нажмите + RETURN.

   Excel заменяет выбранную часть формулы ее вычисленным значением.

См. также

Изменение пересчета формулы, итерации или точности

Скопируйте и вставьте результаты определенных ячеек

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

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

Home > Microsoft Excel > Как преобразовать формулы в значения в Excel? 6 простых методов

(Примечание. Это руководство по преобразованию формул в значения в Excel подходит для всех версий Excel, включая Office 365)

В хранилище Excel есть множество функций и формул. Цель формулы — выполнить определенные операции и получить результирующее значение.

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

В этой статье я расскажу вам 6 способов преобразования формул в значения в Excel.

Вы узнаете:

• Зачем преобразовывать формулы в значения в Excel?
• О чем следует помнить перед преобразованием формул в значения в Excel
• Как преобразовать формулы в значения в Excel?
  • Путем копирования и вставки значений 
  • Использование функциональных клавиш
  • Вставить как значения с помощью сочетания клавиш
  • Использование Shift и функциональных клавиш
  • Путем перетаскивания ячеек
  • Использование Power Query

Посмотрите наше видео о том, как преобразовать значения в формулы в Excel. – 2 простых метода 

Как преобразовать Excel в Word? 2 простых метода

Как преобразовать текст в числа в Excel? 5 простых методов

Зачем преобразовывать формулы в значения в Excel?

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

• Формулы лучше скрывать при отправке другим, чтобы сохранить конфиденциальность. Вы бы не хотели, чтобы другие знали формулу, которую вы использовали для получения определенного значения.
• Некоторые функции, такие как =RAND(), продолжают колебаться и изменять свои значения при любых изменениях листа. Вы можете преобразовать формулы в значения в Excel, чтобы предотвратить такие колебания.
• Копировать ячейки или группы ячеек между рабочими листами проще, если они хранятся как значения, а не как формулы. Это поможет пользователю выполнять операции с легкостью.
• Вы также можете преобразовать формулы в значения, если вы не хотите, чтобы конечный пользователь изменил или отредактировал одно значение, которое может изменить другие значения. Вы также можете скрыть формулы и заблокировать их, чтобы пользователь не мог их изменять или редактировать.
• Чтобы предотвратить изменение данных или их изменение конечным пользователем при изменении любых значений связанных ячеек.
• Если имеется много сложных формул и вы хотите повысить производительность листа, вы можете преобразовать формулы в статические значения.

О чем следует помнить перед преобразованием формул в значения в Excel

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

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

Путем копирования и вставки в виде значений 

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

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

Выберите ячейки

Затем скопируйте выбранные ячейки, щелкнув правой кнопкой мыши выбранные ячейки и выбрав Скопировать . Или вы можете использовать сочетание клавиш Ctrl+C . После копирования ячейки появляются с анимацией движущихся точек.

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

Вставить их как значения

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

Вставить их как значения из главного меню

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

Преобразование формул в значения в Excel путем копирования и вставки значений

Примечание: Вы можете копировать и вставлять ячейки как значения между листами. Однако это не работает, если листы защищены, а ячейки заблокированы.

Использование функциональных клавиш

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

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

Чтобы преобразовать ячейки с формулами в значения с помощью функциональной клавиши, сначала выберите ячейку .

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

Войдите в режим редактирования

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

Преобразование формул в значения в Excel с помощью функциональных клавиш

Примечание: Будьте осторожны при преобразовании формул в значения с помощью этого метода. Этот метод заменяет значение вместо формулы. Вы не сможете получить их после определенных операций.

Вставить как значения с помощью сочетания клавиш

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

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

Выберите ячейки

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

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

Нажмите Alt+E . Это активирует параметры сочетания клавиш и соответствующие им клавиши, привязанные к определенным операциям.

При нажатии Alt+E главное меню Home выделяется.

Теперь нажмите S . Откроется диалоговое окно «Специальная вставка».

Диалоговое окно «Специальная вставка»

Теперь нажмите V , чтобы выделить параметр вставки «Значения».

Выберите значения

Нажмите Введите . Это преобразует ячейки с формулами в значения.

Как преобразовать формулы в значения в Excel с помощью сочетаний клавиш

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

Как рассчитать процентиль в Excel? 3 полезных формулы

Как выделить несмежные ячейки в Excel? 5 простых способов

Как добавить дополнительную ось в Excel? 2 простых способа

Использование клавиш Shift и функциональных клавиш

Еще один простой способ преобразования формул в значения путем их вставки в качестве значений — использование клавиш Shift и функциональных клавиш.

Выберите ячейки , которые вы хотите преобразовать.

Копировать выбранные ячейки.

Теперь, удерживая клавишу Shift, нажмите F10 . Это похоже на щелчок правой кнопкой мыши по ячейке и выбор «Специальная вставка как значения». Но, этот метод еще быстрее и эффективнее.

Выберите ячейки и нажмите F10

Теперь нажмите V . Значения заменят ячейки формулами.

Преобразование формул в значения в Excel с помощью Shift и функциональных клавиш

Путем перетаскивания ячеек

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

Сначала выберите ячейки, которые вы хотите преобразовать в значения.

Выберите ячейки

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

Как только вы отпустите правую кнопку мыши, откроется диалоговое окно. Выберите Копировать сюда как значения только в диалоговом окне.

Щелкните Копировать сюда как значения

Это преобразует все выбранные ячейки в значения.

Преобразование формул в значения в Excel путем перетаскивания

Примечание. При замене формул значениями в Excel иногда формулы могут быть безвозвратно удалены или заменены. Чтобы получить формулы, немедленно отмените выделение или нажмите Ctrl+Z.

Использование Power Query

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

В таких случаях с помощью Power Query можно преобразовать формулы в значения на новом листе.

Чтобы создать запрос мощности, сначала выберите ячейки .

Затем перейдите к Данные . В разделе Get & Transform Data выберите From Table/Range .

Выбрать таблицу/диапазон

Откроется диалоговое окно «Создать таблицу», в котором вам будет предложено выбрать данные для таблицы. Поскольку мы уже выбрали ячейки, диапазон ячеек заполняется автоматически. Щелкните OK .

Выберите ячейки

Откроется редактор Power Query, в котором выбранные ячейки будут преобразованы в значения.

Нажмите Закрыть и загрузить .

Закрыть и загрузить

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

Как преобразовать формулы в значения в Excel с помощью запроса Power

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

Рекомендуемое чтение:

Как скопировать только видимые ячейки в Excel? 3 простых способа

Как переносить текст в Excel? С помощью 6 простых методов

Как использовать разделенный экран Excel? 3 простых способа

Часто задаваемые вопросы

Как преобразовать формулы в значения в Excel в одной ячейке?

Вы можете копировать и вставлять ячейки как значения, выбирая целевую ячейку/ячейки, такие же, как скопированные ячейки.

Как преобразовать формулы в значения в Excel без специальной вставки?

Используйте функциональные клавиши для преобразования формул в значения в Excel без использования специальной вставки. Выберите ячейку, нажмите F2, чтобы войти в режим редактирования, и нажмите F9, чтобы преобразовать формулу в значение. Или вы также можете использовать Power Query для преобразования формулы в значение.

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

Просто щелкните левой кнопкой мыши и перетащите ячейки, чтобы выбрать их, или используйте выделение ячеек, нажав Ctrl. Теперь вы можете копировать и вставлять ячейки как значения.

Заключительные мысли

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