Преобразование формул в значения
Формулы – это хорошо. Они автоматически пересчитываются при любом изменении исходных данных, превращая Excel из "калькулятора-переростка" в мощную автоматизированную систему обработки поступающих данных. Они позволяют выполнять сложные вычисления с хитрой логикой и структурой. Но иногда возникают ситуации, когда лучше бы вместо формул в ячейках остались значения. Например:
- Вы хотите зафиксировать цифры в вашем отчете на текущую дату.
- Вы не хотите, чтобы клиент увидел формулы, по которым вы рассчитывали для него стоимость проекта (а то поймет, что вы заложили 300% маржи на всякий случай).
- Ваш файл содержит такое больше количество формул, что Excel начал жутко тормозить при любых, даже самых простых изменениях в нем, т.к. постоянно их пересчитывает (хотя, честности ради, надо сказать, что это можно решить временным отключением автоматических вычислений на вкладке Формулы – Параметры вычислений).
- Вы хотите скопировать диапазон с данными из одного места в другое, но при копировании "сползут" все ссылки в формулах.
В любой подобной ситуации можно легко удалить формулы, оставив в ячейках только их значения. Давайте рассмотрим несколько способов и ситуаций.
Способ 1. Классический
Этот способ прост, известен большинству пользователей и заключается в использовании специальной вставки:
- Выделите диапазон с формулами, которые нужно заменить на значения.
- Скопируйте его правой кнопкой мыши – Копировать (Copy).
- Щелкните правой кнопкой мыши по выделенным ячейкам и выберите либо значок Значения (Values):
либо наведитесь мышью на команду Специальная вставка (Paste Special), чтобы увидеть подменю:
Из него можно выбрать варианты вставки значений с сохранением дизайна или числовых форматов исходных ячеек.
В старых версиях Excel таких удобных желтых кнопочек нет, но можно просто выбрать команду Специальная вставка и затем опцию Значения (Paste Special - Values) в открывшемся диалоговом окне:
Способ 2. Только клавишами без мыши
При некотором навыке, можно проделать всё вышеперечисленное вообще на касаясь мыши:
- Копируем выделенный диапазон Ctrl+C
- Тут же вставляем обратно сочетанием Ctrl+V
- Жмём Ctrl, чтобы вызвать меню вариантов вставки
- Нажимаем клавишу с русской буквой З или используем стрелки, чтобы выбрать вариант Значения и подтверждаем выбор клавишей Enter:
Способ 3. Только мышью без клавиш или Ловкость Рук
Этот способ требует определенной сноровки, но будет заметно быстрее предыдущего. Делаем следующее:
- Выделяем диапазон с формулами на листе
- Хватаем за край выделенной области (толстая черная линия по периметру) и, удерживая ПРАВУЮ клавишу мыши, перетаскиваем на пару сантиметров в любую сторону, а потом возвращаем на то же место
- В появившемся контекстном меню после перетаскивания выбираем Копировать только значения (Copy As Values Only).
После небольшой тренировки делается такое действие очень легко и быстро. Главное, чтобы сосед под локоть не толкал и руки не дрожали ;)
Способ 4. Кнопка для вставки значений на Панели быстрого доступа
Ускорить специальную вставку можно, если добавить на панель быстрого доступа в левый верхний угол окна кнопку Вставить как значения. Для этого выберите Файл - Параметры - Панель быстрого доступа (File - Options - Customize Quick Access Toolbar). В открывшемся окне выберите Все команды (All commands) в выпадающем списке, найдите кнопку Вставить значения (Paste Values) и добавьте ее на панель:
Теперь после копирования ячеек с формулами будет достаточно нажать на эту кнопку на панели быстрого доступа:
Кроме того, по умолчанию всем кнопкам на этой панели присваивается сочетание клавиш Alt + цифра (нажимать последовательно). Если нажать на клавишу Alt, то Excel подскажет цифру, которая за это отвечает:
Способ 5. Макросы для выделенного диапазона, целого листа или всей книги сразу
Если вас не пугает слово "макросы", то это будет, пожалуй, самый быстрый способ.
Макрос для превращения всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с Ctrl) выглядит так:
Sub Formulas_To_Values_Selection()
'преобразование формул в значения в выделенном диапазоне(ах)
Dim smallrng As Range
For Each smallrng In Selection.Areas
smallrng.Value = smallrng.Value
Next smallrng
End Sub
Если вам нужно преобразовать в значения текущий лист, то макрос будет таким:
Sub Formulas_To_Values_Sheet()
'преобразование формул в значения на текущем листе
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
End Sub
И, наконец, для превращения всех формул в книге на всех листах придется использовать вот такую конструкцию: Sub Formulas_To_Values_Book()
'преобразование формул в значения во всей книге
For Each ws In ActiveWorkbook.Worksheets
ws.UsedRange.Value = ws.UsedRange.Value
Next ws
End Sub
Код нужных макросов можно скопировать в новый модуль вашего файла (жмем Alt+F11 чтобы попасть в Visual Basic, далее Insert - Module). Запускать их потом можно через вкладку Разработчик - Макросы (Developer - Macros) или сочетанием клавиш Alt+F8. Макросы будут работать в любой книге, пока открыт файл, где они хранятся. И помните, пожалуйста, о том, что действия выполненные макросом невозможно отменить - применяйте их с осторожностью.
Способ 6. Для ленивых
Если ломает делать все вышеперечисленное, то можно поступить еще проще - установить надстройку PLEX, где уже есть готовые макросы для конвертации формул в значения и делать все одним касанием мыши:
В этом случае:
- всё будет максимально быстро и просто
- можно откатить ошибочную конвертацию отменой последнего действия или сочетанием Ctrl+Z как обычно
- в отличие от предыдущего способа, этот макрос корректно работает, если на листе есть скрытые строки/столбцы или включены фильтры
- любой из этих команд можно назначить любое удобное вам сочетание клавиш в Диспетчере горячих клавиш PLEX
Ссылки по теме
- Что такое макросы, как их использовать, копировать и запускать
- Как скопировать формулы без сдвига ссылок
- Как считать в Excel без формул
По классическому способу можно добавить вариант с панелью быстрого доступа (либо в приемах, либо на форуме это было подсмотрено, уже и не вспомнить) - это для тех, кто привык к CTRl +C, CTRL +V - только будет ALT+ назначенная клавиша.
Вы просто мастер.
Есть книга с несколькими листами. С помощью Shift выделяю листы, далее диапазон столбца и запускаю макрос по замене формулы на значение
Sub Заменятель()
For Each cell In Selection
cell.Formula = cell.Value
Next cell
MsgBox "Я всё сделал!"
End Sub
но его приходится повторять на каждом листе.
Подскажите возможно ли выполнить этот макрос на всех выделенных листах без постоянных повторений.
Спасибо, огромное!
При том как я эксперементировал - поставил ваш код в модуль - запустил - и все... ничего больше я в Excel сделать не мог - пришлось через диспетчер задач отключать excel! (хотел поставить паузу у выполнении не помогло. - также повис)... сtrl+alt+pause - у меня не работает.... так что это дурная затея
Только в одном листе (формат книги 97-2003 у меня на 100 строк ушло - чуть более 10 секунд... (С Excel 2007 - трудно себе представить сколько бы вы ждали ))- Так что удачи через макрос я бы это точно не делал!!!!
Намного проще 1 классическим методом. - все делается в 3 клика - без задержек. Лист только со значениями готов!!!
Пробовал способ с макросом, но он некорректно работает на отфильтрованных диапазонах.
После первой же скрытой строки он начинает вставлять некорректные значения.
Мне кажется что это из-за того что он делает это для всего выделенного диапазона, однако если его в цикле заставить это делать для каждой выделенной ячейки по отдельности то все будет нормально, но я не могу реализовать такой вариант (не хватает знаний).
Не могли бы вы помочь???
Передо мной стоит более комплексная задача:
1) нужно скопировать ячейку из Книга1 в первую пустую ячейку в Книга2,
2) в этой самой ячейке изначально записана формула, ее нужно заменить на значение.
3) всё это с нажатия кнопки в Книга1.
У вас конечно можно найти эти макросы по отдельности, но вот объединить их вряд ли у меня получится(
Прошу вас, помогите разобраться с этим!
Прочие уже утомили )
Заранее спасибо!
Подскажите, пожалуйста, по какой причине не работает для вставки значения при использовании способа 4. Кнопка для вставки значений на Панели быстрого доступа в Microsoft Office 2007?
Все сделано как описано в статье. Кнопка Ускорить специальную вставку можно, если добавить на панель быстрого доступа в левый верхний угол окна кнопку Вставить как значения. Для этого выберите Файл - Параметры - Панель быстрого доступа (File - Options - Customize Quick Access Toolbar). В открывшемся окне выберите Все команды (All commands) в выпадающем списке, найдите кнопку Вставить значения (Paste Values) и добавьте ее на панель.
Кнопка добавляется, но она не активна!!!
Я подозреваю, что, может быть, данный способ в Microsoft Office 2007 реализован по-другому? И описанный способ реализуется на версиях, начиная с 2010?
Я часто использую ещё один способ превращения формул со ссылками на другие файлы в значения. К примеру, формула VLOOKUP (он же ВПР) или любые другие, где есть ссылка на другие Книги Excel
Способ достаточно удобный и быстрый (особенно, если вывести его выполнение на кнопку с макро-командой)
Идём в меню Data -> Edit Links -> жмём Break Links. Всё. Все формулы Книги, связанные с другими файлами, превращаются в значения
Вот макро-команда, если кому-нибудь интересно:
Sub BreakAllWorkbookLinks()
'Breaks all workbook links and transforms all external references into values
Dim aLinks As Variant
Dim i As Integer
With Application.ActiveWorkbook
aLinks = .LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
Debug.Print aLinks(i)
.BreakLink _
Name:=aLinks(i), _
Type:=xlLinkTypeExcelLinks
Next i
End If
End With
End Sub
А именно то, что "не попало" в фильтр (то что скрыто).
У меня в ячейки попадают результаты переноса данных с помощью ИНДЕКС_ПОИСКПОЗ. Как сделать что бы Эксель мог искать данные в этом массиве? Поиск работает только через фильтр. Через ctrl-f не ищет.
Спасибо Вам за надстройку, регулярно ею пользуюсь, поставила везде где только можно. А это два домашних, два рабочих и на один компьютер коллеги. Особенно нравится Ваша функция книга в значения, часто нужно по работе.
Сейчас пытаюсь реализовать ряд однотипных действий по заключительной обработке и рассылке файла конечным пользователям.
Необходимо преобразовать книгу с несколькими скрытыми листами в значения.
К сожалению указанный Вами макрос выдает ошибку.
Строка "For Each WS In ActiveWorkbook.Worksheets"
Выделяет WS
и пишет Compile Error Variable not defined
При этом макросы перевода в значение листа и диапазона отрабатывают корректно.
Посмотрел Ваш урок на youtube Программирование макросов на VBA в Excel.
Когда преобразуем формулу в значение в выбранном диапазоне.
Sub Заменятель()
For Each cell In Selection
cell.Formula = cell.Value
Next cell
MsgBox "Я всё сделал"
End Sub
Замена происходит значительно дольше, нежели если бы мы использовали Способ1. Классический
Я выбираю диапазон 2 столбца на 360 строк.
В Интернете посмотрел, написано все дело в Select, рекомендуют использовать AutoFillTable, но я не понимаю как тогда должен выглядеть код.
Не могли бы Вы подсказать ?
'преобразование формул в значения в выделенном диапазоне(ах)
Application.DisplayAlerts = False 'не вывовдит какие либо оповещения
Application.Calculation = xlCalculationManual 'не делает расчет формул
Application.ScreenUpdating = False 'отключает отображение действий
Selection.SpecialCells(xlCellTypeVisible).Select
Dim smallrng As Range
For Each smallrng In Selection.Areas
smallrng.Value = smallrng.Value
Next smallrng
Application.DisplayAlerts = True 'не вывовдит какие либо оповещения
Application.Calculation = xlCalculationAutomatic 'не делает расчет формул
Application.ScreenUpdating = True 'отключает отображение действий
End Sub