Как удалить все ссылки на формулы, оставив в ячейках только их значения, в Excel?
Во многих файлах рабочей книги Excel ячейки часто содержат формулы, ссылающиеся на другие ячейки, листы или даже внешние файлы — исходный диапазон. Однако бывают ситуации, когда необходимо сохранить только вычисленные значения и удалить все исходные ссылки формул. Например, перед передачей данных другим пользователям или для фиксации текущих результатов вычислений и предотвращения случайных изменений при последующем редактировании может потребоваться преобразовать все формулы в их результирующие значения — фактическое значение. К счастью, Excel предлагает несколько эффективных способов удалить ссылки формул, сохранив при этом сами значения. В этой статье описаны различные практические методы достижения этой цели, которые помогут вам выбрать наиболее подходящий вариант с учётом конкретных задач или рабочего процесса.
Удаление всех ссылок на формулы с сохранением значений в ячейках путём копирования и вставки
Удаление всех ссылок на формулы с сохранением значений в ячейках одним щелчком мыши
Код VBA — использование макроса для автоматической замены всех формул в выбранных ячейках их вычисленными значениями
Удаление всех ссылок на формулы с сохранением значений в ячейках путём копирования и вставки
Один из самых простых и универсально совместимых способов удалить ссылки на формулы, сохранив при этом вычисленные значения на листе, — это использование функций «Копировать» и «Вставить как значения» в Excel. Этот метод идеален для быстрого выполнения задач: он не требует установки надстроек и работает во всех современных версиях Excel. Однако имейте в виду: после замены формул их значениями вы утратите автоматические вычисления и связи, которые обеспечивали формулы. Поэтому применяйте этот подход только тогда, когда уверены, что динамические обновления в этих ячейках больше не нужны.
Выполните следующие действия, чтобы удалить все ссылки на формулы, но сохранить значения в указанных ячейках Excel:
1. Выделите ячейки, содержащие формулы, которые нужно преобразовать в значения. Нажмите Ctrl+C, чтобы скопировать их. Не снимая выделения, щёлкните правой кнопкой мыши в любом месте выделенной области, чтобы открыть контекстное меню, и выберите Значения в разделе Выборочная вставка. (Если используется интерфейс «Лента», можно также перейти на вкладку)Главная, нажать стрелку под кнопкой Вставить и выбрать Вставить значения.) См. снимок экрана:
После выполнения этой операции все формулы в выбранных ячейках будут заменены их текущими значениями, как показано на снимке экрана ниже. Визуально содержимое ячеек останется прежним, однако сами формулы будут удалены, и вместо них останутся только фиксированные числа или текст.
Применимые сценарии:Этот метод идеально подходит для небольших и средних диапазонов файлов или когда требуется полный контроль над тем, какие области преобразовывать. Если вам часто приходится повторять эту операцию, используйте сочетания клавиш ()Ctrl+Alt+V, затем V) — это ускорит процесс.
Преимущества: Не требует надстроек или программирования; даёт мгновенный результат; работает во всех версиях Excel.
Недостатки: Изменения нельзя отменить после сохранения и закрытия книги; применяется каждый раз только к текущему выделению — выберите диапазон.
Советы и предостережения: Перед вставкой значений внимательно проверьте выделение, особенно если формулы ссылаются друг на друга. Если вы хотите сохранить резервную копию формул, рекомендуем сначала скопировать исходный лист на новый — сделайте это до применения данного метода.
Удаление всех ссылок на формулы с сохранением значений в ячейках одним щелчком мыши
Если вы ищете более быстрый способ — реализуемый всего одним щелчком мыши, особенно для больших наборов данных или регулярных задач, — воспользуйтесь функцией Преобразовать в значения, предоставляемой надстройкой Kutools для Excel. Эта утилита упрощает процесс, мгновенно превращая все формулы в выделенном диапазоне в статические значения — без лишних шагов с копированием и вставкой. Особенно полезно при работе с объёмными отчётами, общими шаблонами или конфиденциальными финансовыми данными, которые должны оставаться неизменными.
1. Выделите ячейки или диапазон с формулами, которые нужно преобразовать в значения. Затем просто нажмите Kutools > Преобразовать в значения на панели инструментов Excel.
Все формулы в выбранных ячейках мгновенно заменяются их итоговыми значениями, а числа и текст остаются точно такими же, как отображались. Подтверждение — на снимке экрана ниже:
Kutools для Excel — включает более 300 незаменимых инструментов для Excel. Работайте в Excel быстрее, проще и эффективнее.Скачать сейчас!
Рекомендации по применению: Надстройка Kutools для Excel идеально подходит пользователям, которым требуются эффективные решения для повторяющихся операций или которые регулярно работают с объёмными книгами, содержащими множество формул. Она особенно полезна тем, кто стремится свести к минимуму рутинные ручные действия и снизить риск ошибок.
Преимущества: Чрезвычайно высокая скорость; поддержка больших и нескольких диапазонов; оптимизация для экономии времени при выполнении частых задач.
Недостатки: Требуется установка надстройки Kutools для Excel (платная утилита, но доступны пробные версии).
Если не все формулы преобразуются, убедитесь, что перед запуском утилиты вы выделили весь необходимый диапазон. Перед выполнением массовых операций рекомендуем сохранить книгу — на случай, если понадобится отменить изменения.
Код VBA — использование макроса для автоматической замены всех формул в выбранных ячейках их вычисленными значениями
Для продвинутых пользователей или тех, кто стремится автоматизировать процесс, отличным решением станет использование пользовательского макроса VBA. Этот метод особенно эффективен при работе с повторяющимися задачами или когда требуется одновременно преобразовать несколько несмежных диапазонов из формул в значения. Благодаря VBA вы сможете выполнить всё одной командой, значительно сэкономив время и усилия при регулярном проведении таких операций.
Примечание: Этот метод подойдёт любому пользователю, знакомому с запуском макросов VBA в Excel. Если политика вашей организации ограничивает использование макросов, обязательно согласуйте его применение с администратором.
1. Откройте редактор VBA, нажав Разработчик > Visual Basic. В окне Microsoft Visual Basic for Applications выберите Вставка > Модуль и вставьте следующий код в новый модуль:
Sub ConvertFormulasToValues()
Dim rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Please select the range where you want to remove formulas and keep values:", xTitleId, WorkRng.Address, Type:=8)
If WorkRng Is Nothing Then Exit Sub
For Each rng In WorkRng
If rng.HasFormula Then
rng.Value = rng.Value
End If
Next
End Sub2. Чтобы запустить макрос, нажмите кнопку 
Выполнить в редакторе VBA. Появится диалоговое окно с запросом выбора диапазона, в котором нужно заменить формулы их значениями. После того как вы выберете целевой диапазон и подтвердите действие, макрос автоматически преобразует все формулы в указанном диапазоне в их вычисленные результаты: ссылки на формулы будут удалены, а отображаемые значения останутся без изменений.
Применимые сценарии: Это решение отличается высокой гибкостью и идеально подходит для пакетной обработки больших или нестандартных диапазонов ячеек. Оно особенно ценно, когда нужно автоматизировать процесс в составе более крупного рабочего процесса или макроса.
Преимущества: Автоматизирует повторяющиеся задачи, легко расширяется и комбинируется с другими шагами автоматизации, отлично справляется со сложными сценариями выделения.
Недостатки: Требует включения макросов, не подходит пользователям, незнакомым с VBA, а параметры безопасности макросов могут ограничивать его использование в некоторых организациях.
Устранение неполадок:Если возникает ошибка времени выполнения, убедитесь, что выделен допустимый диапазон. Также проверьте, включены ли макросы в вашем экземпляре Excel. Если нужно отменить операцию, воспользуйтесь стандартной функцией отмены в Excel ()Ctrl + Z), однако имейте в виду: действия макроса нельзя отменить после сохранения и закрытия книги.
Связанные статьи:
- Как сослаться на формат и значение другой ячейки в Excel?
- Как сослаться на значение или создать ссылку на него в закрытой книге Excel?
- Как сослаться на лист по его порядковому номеру, а не по имени, в Excel?
- Как сделать ссылку на ячейку в формуле постоянной в Excel?
- Как сослаться на имя вкладки в ячейке Excel?
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек