Замена формулы на ее результат в excel

Замена формулы на ее результат в excel

Описание проблемы

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

Макрос из надстройки VBA-Excel преобразует формулы в значения всего одной кнопкой. Кроме того с помощью надстройки можно выделить несколько областей ячеек (в том числе и не смежных) и заменить формулы на значения во всех ячейках.

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

С помощью надстройки VBA-Excel можно произвести замену формул во всех листах книги или только на выбранных. Для этого:

  1. Откройте вкладку меню VBA-Excel
  2. Перейдите в меню Диспетчеры ->Диспетчер листов
  3. Выберите листы, в который все формулы будут заменены на значения
  4. Нажмите команду Только значения

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

Для того, чтобы «отвязать» результаты расчетов от исходных данных следует заменить формулы в ячейках на их значения (цифры).

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

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

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

Для начала следует записать макрос замены.

  1. В панели разработчика создаем модуль (контейнер) для макроса:
  • Кликаем на пиктограмму Visual Basic
  • В открывшемся окне кликаем правой кнопкой мыши по книге и выбираем в контекстном меню insert => Module
Читайте также:  Как открыть загрузки на ноутбуке

  1. Открываем созданный модуль двойным щелчком мыши и записываем в него текст макроса.

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 – конец макроса.

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

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

После замены формул на значения можно смело удалять промежуточные таблицы (черновики) из отчета .

Видео работы макроса замены формул на значения:

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

  • Вы хотите зафиксировать цифры в вашем отчете на текущую дату.
  • Вы не хотите, чтобы клиент увидел формулы, по которым вы рассчитывали для него стоимость проекта (а то поймет, что вы заложили 300% маржи на всякий случай).
  • Ваш файл содержит такое больше количество формул, что Excel начал жутко тормозить при любых, даже самых простых изменениях в нем, т.к. постоянно их пересчитывает (хотя, честности ради, надо сказать, что это можно решить временным отключением автоматических вычислений на вкладке Формулы – Параметры вычислений).
  • Вы хотите скопировать диапазон с данными из одного места в другое, но при копировании "сползут" все ссылки в формулах.

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

Способ 1. Классический

Этот способ прост, известен большинству пользователей и заключается в использовании специальной вставки:

  1. Выделите диапазон с формулами, которые нужно заменить на значения.
  2. Скопируйте его правой кнопкой мыши – Копировать(Copy) .
  3. Щелкните правой кнопкой мыши по выделенным ячейкам и выберите либо значок Значения (Values) :
Читайте также:  Замок который можно открыть любым ключом


либо наведитесь мышью на команду Специальная вставка (Paste Special) , чтобы увидеть подменю:


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

В старых версиях Excel таких удобных желтых кнопочек нет, но можно просто выбрать команду Специальная вставка и затем опцию Значения (Paste Special — Values) в открывшемся диалоговом окне:

Способ 2. Только клавишами без мыши

При некотором навыке, можно проделать всё вышеперечисленное вообще на касаясь мыши:

  1. Копируем выделенный диапазон Ctrl + C
  2. Тут же вставляем обратно сочетанием Ctrl + V
  3. Жмём Ctrl , чтобы вызвать меню вариантов вставки
  4. Нажимаем клавишу с русской буквой З или используем стрелки, чтобы выбрать вариант Значения и подтверждаем выбор клавишей Enter :

Способ 3. Только мышью без клавиш или Ловкость Рук

Этот способ требует определенной сноровки, но будет заметно быстрее предыдущего. Делаем следующее:

  1. Выделяем диапазон с формулами на листе
  2. Хватаем за край выделенной области (толстая черная линия по периметру) и, удерживая ПРАВУЮ клавишу мыши, перетаскиваем на пару сантиметров в любую сторону, а потом возвращаем на то же место
  3. В появившемся контекстном меню после перетаскивания выбираем Копировать только значения (Copy As Values Only) .

После небольшой тренировки делается такое действие очень легко и быстро. Главное, чтобы сосед под локоть не толкал и руки не дрожали 😉

Способ 4. Кнопка для вставки значений на Панели быстрого доступа

Ускорить специальную вставку можно, если добавить на панель быстрого доступа в левый верхний угол окна кнопку Вставить как значения. Для этого выберите Файл — Параметры — Панель быстрого доступа (File — Options — Customize Quick Access Toolbar) . В открывшемся окне выберите Все команды (All commands) в выпадающем списке, найдите кнопку Вставить значения (Paste Values) и добавьте ее на панель:

Читайте также:  Где очистка диска в windows 10

Теперь после копирования ячеек с формулами будет достаточно нажать на эту кнопку на панели быстрого доступа:

Кроме того, по умолчанию всем кнопкам на этой панели присваивается сочетание клавиш Alt + цифра (нажимать последовательно). Если нажать на клавишу Alt , то Excel подскажет цифру, которая за это отвечает:

Способ 5. Макросы для выделенного диапазона, целого листа или всей книги сразу

Если вас не пугает слово "макросы", то это будет, пожалуй, самый быстрый способ.

Макрос для превращения всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с Ctrl) выглядит так:

Если вам нужно преобразовать в значения текущий лист, то макрос будет таким:

И, наконец, для превращения всех формул в книге на всех листах придется использовать вот такую конструкцию:

Код нужных макросов можно скопировать в новый модуль вашего файла (жмем Alt + F11 чтобы попасть в Visual Basic, далее Insert — Module). Запускать их потом можно через вкладку Разработчик — Макросы (Developer — Macros) или сочетанием клавиш Alt + F8 . Макросы будут работать в любой книге, пока открыт файл, где они хранятся. И помните, пожалуйста, о том, что действия выполненные макросом невозможно отменить — применяйте их с осторожностью.

Способ 6. Для ленивых

Если ломает делать все вышеперечисленное, то можно поступить еще проще — установить надстройку PLEX, где уже есть готовые макросы для конвертации формул в значения и делать все одним касанием мыши:

  • всё будет максимально быстро и просто
  • можно откатить ошибочную конвертацию отменой последнего действия или сочетанием Ctrl + Z как обычно
  • в отличие от предыдущего способа, этот макрос корректно работает, если на листе есть скрытые строки/столбцы или включены фильтры
  • любой из этих команд можно назначить любое удобное вам сочетание клавиш в Диспетчере горячих клавиш PLEX
Ссылка на основную публикацию
Директ икс для виндовс 10 64 бит
Скачать Разработчик: Microsoft Для Windows: 32/64-bit XP, Vista, 7, 8, 10 Языки: Русский, Українська, English и др. Цена: Бесплатно Оценка...
Выбор ноутбука до 15000
На начало 2018 года найти нормальный ноутбук за 15000 рублей тяжело ‒ за эту сумму в основном доступны слабые модели,...
Выбор тихого корпуса для компьютера
Как выбрать корпус для ПК – самый важный параметр Когда возникает вопрос, какой корпус выбрать для компьютера, нужно определиться с...
Диск для однократной записи
Работа с оптическими дисками Оптический диск Образ оптического диска, ISO-образ Эмулятор оптических дисководов Программное обеспечение для работы с файловыми системами...
Adblock detector