Иногда формула в ячейке нужна лишь на короткое время — только для получения результата. А затем уже не нужна ни сама формула, ни ячейки, на которые она ссылается. Но вот беда — если удалить ячейки, то формула «ломается», как это говорится. Т.е. в ячейке получается либо
Выделяем ячейку(несколько ячеек) с формулами -Копируем -Правая кнопка мыши — Специальная вставка
Данную команду можно вынести на Панель быстрого доступа и использовать в одно нажатие тогда, когда нужно. Как это сделать:
- 2010 Excel и выше Файл— Параметры— Настройка. В выпадающем списке Выбрать команды из: выбираем Все команды. Находим команду Вставить значения и добавляем
- 2007 Excel Меню— Параметры Excel— Настройка. В выпадающем списке Выбрать команды из: выбираем Все команды. Находим команду Вставить значения и добавляем
- 2003 Excel: Сервис— Настройка— вкладка Команды— категория Правка— Вставить значения.
Теперь в Панели быстрого доступа появится значок Вставка значений.
Тоже самое можно сделать при помощи кода VBA:
Sub Formulas_To_Values() Selection.Value = Selection.Value End Sub |
Данный код заменяет формулы в выделенном диапазоне на значения. Только необходимо учитывать, что выделенный диапазон должен быть неразрывным. Иначе код выдаст ошибку. А такой код:
Sub All_Formulas_To_Values() ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value End Sub |
заменит все формулы на значения во всех ячейках листа. При этом будут разорваны все связи на данном листе. Так же этот код заменит все формулы даже если на листе применен фильтр(т.е. замена будет и в скрытых фильтром ячейках). Это стоит учитывать. Кстати, иногда именно для этого и применяют подобный код.
Так же следует помнить, что после выполнения практически любого макроса буфер обмена Excel очищается и отменить действие макроса невозможно. Поэтому все действия и «испытания» лучше производить на копиях данных. Но если все же заменили на рабочем файле — можно попробовать закрыть книгу без сохранений.
А следующий код заменит формулы на значения во всех ячейках на всех листах активной книги:
Sub All_Formulas_To_Values_In_All_Sheets() Dim wsSh As Worksheet For Each wsSh In Worksheets wsSh.UsedRange.Value = wsSh.UsedRange.Value Next wsSh End Sub |
У этого кода нюансы со связями и скрытыми ячейками точно такие же, как и у предыдущего, но уже в масштабах всех листов книги. Плюс, даже если какие-то листы скрыты — замена будет произведена и на них тоже.
А вот код ниже более интересен с точки зрения повседневного использования: он заменить формулы на значения только в видимых ячейках выделенного диапазона. Может пригодиться, если заменить надо формулы при установленном фильтре или в несмежных областях:
Sub All_Formulas_To_Values_OnlyVisible() Dim rRng As Range, rArea As Range If Selection.Count = 1 Then Set rRng = ActiveCell Else Set rRng = Selection.SpecialCells(12) End If For Each rArea In rRng.Areas rArea.Value = rArea.Value Next rArea End Sub |
Если один раз выполнить код ниже, то в контекстном меню по правому клику мыши появится стандартная команда Вставить значения
Sub Add_PasteSpecials() Dim cbb Set cbb = Application.CommandBars("Cell").FindControl(ID:=370) If Not cbb Is Nothing Then cbb.Delete 'удаляем пункт, если он был уже добавлен ранее Application.CommandBars("Cell").Controls.Add ID:=370, before:=4 End Sub |
Также см.:
Вставить значения
Формулы перестали считать — как исправить?
Отличный макрос, только вот он почему-то всё форматирование сбивает. К примеру и после выполнения макроса становятся обычным текстом. Как-то можно этого избежать?
Сайт скушал текст :) Там было [sup] и [sub]
Если честно макрос форматирование вообще не трогает. Он работает исключительно со значениями. И приведенный текст никак не мог быть преобразован во что-то другое. Видимо у Вас применено не простое форматирование, а что-то еще. Без файла сложно сказать — советую обратиться в форум, там можно приложить пример файла. Или можете выслать файл мне на почту(на странице Контакты есть), я как будет возможность посмотрю.
Согласен, не должен трогать, но тем не менее, делает это :)
Спасибо, я нашёл корявенький вариант решения, так он форматирование не портит.
For Each Cell In ActiveSheet.UsedRange
If Cell.HasFormula() = True Then
Cell.Activate
Selection.Value = Selection.Value
End If
Next Cell
Корявенько всё это. Забыли о просто функции Excel — выделить нужную ячейку f2 -> f9 -> enter
Формула удаляется — остаётся только её значение. Связи разорваны.
Profit!
Да, корявенько…Но что делать — я вот так привык. Только еще один момент: попробуйте своим методом заменить формулу на значения примерно тысячах в двух-трех и более ячеек. Да хотя бы в ста. А потом попробуйте описанным в статье методом выделить их все и разом преобразовать. И сравните количество затраченного времени на замену формул этими двумя методами.
Это я к чему: прежде чем писать, что корявенько — сначала рассмотрите все за и против в различных ситуациях. В одной ячейке очень редко заменяют значения — его можно тупо вписать руками…Куда чаще это приходится делать как раз с массивами ячеек.
Ну тут, Дмитрий и соглашусь и не соглашусь.https://support.office.microsoft.com/ru-ru/article/%D0%A1%D0%BE%D1%87%D0%B5%D1%82%D0%B0%D0%BD%D0%B8%D1%8F-%D0%BA%D0%BB%D0%B0%D0%B2%D0%B8%D1%88-%D0%B3%D0%BE%D1%80%D1%8F%D1%87%D0%B8%D0%B5-%D0%BA%D0%BB%D0%B0%D0%B2%D0%B8%D1%88%D0%B8-%D0%B2-Excel-2010-20603861-42b6-4c93-82ec-66924ea9b323?CorrelationId=8bd8413c-616d-4050-923c-82c972e1b91c&ui=ru-RU&rs=ru-RU&ad=RU
Слово макрос обывателя пугает и настораживает, особенно при появлении разных предупреждений от excel что тут есть макрос и вообще она щас нас всех заразит вирусами (неважно, что ты тока что сам его написал:)).
Также писать макрос под две ячейки тоже, на мой взгляд не с руки.
А вот, когда надо на листе две, три ячейки отвязать от «родителей» — описанный мною метод самое то, особенно, если люди не любят (как я, например) мышь тыркать где надо и не надо. Это просто быстрее, «3 нажатия» vs «Х нажатий + запомни Х-значное число» — на мой взгляд выигрывает первый вариант.
Экселем пользуюсь давно, но про F9 узнал относительно недавно, и удивлюсь, что скажите что знали, про данный финт не описанный даже в официальном хэлпе. ;)
А что касается массивов — с моей стороны тут спорить будет глупо. ) Удобнее описанный Вами метод. ;)
В принципе я вообще не имел ввиду макрос. В начале статьи описан метод через специальную вставку — его я и имел ввиду, когда говорил про применение для массива ячеек. Скопировали ячейки с формулами-правая кнопка мыши — Специальная вставка-Значения. Все. Никаких макросов и настроек безопасности — пара щелчков мыши и тысячи ячеек с формулами стали значениями :-)
Очень полезный макрос, но у меня при попытки его использовать выскакивает ошибка «RunTime Error 7 — Out of Memory» ((
Добрый день
а как удалить из контекстного меню лишнюю строку, если выполнил код более 1 раза?
Sub Add_PasteSpecials()
Application.CommandBars(«Cell»).Controls.Add ID:=370, before:=4
End Sub
Игорь,
Так же дополнил код в статье, чтобы при повторном выполнении данный пункт не дублировался.
Помогите пожалуйста. Как при удалении листа в екселе, удалить его привязанность к документу, чтобы не потерять данные?
1. Разорвать связи(Данные -Изменить связи)
2. Во всех связанных документах заменить ссылки на этот лист на значения
Прошу прощения, если уже было — всё не читал. Есть отличный вариант: вывести на панель быстрого доступа стандартную команду «Вставить значения» и поставить её (к примеру) на ПЕРВОЕ место панели. Теперь после стандартного копирования Ctrl+C можно СРАЗУ вставить ТОЛЬКО ЗНАЧЕНИЯ через Alt+1. Отлично помогает при копировании значений, в зоне условного форматирования — позволяет избегать дублирования кучи условий (см.Условное форматирование —> Управление правилами)
Забыл сказать ГЛАВНЫЙ плюс: откатывается/отменяется стандартным методом (в отличие от макросов)
Спасибо! помогло
Спасибо!