Заменить ссылку на значение в эксель
Формулы – это хорошо. Они автоматически пересчитываются при любом изменении исходных данных, превращая 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) выглядит так:
Если вам нужно преобразовать в значения текущий лист, то макрос будет таким:
И, наконец, для превращения всех формул в книге на всех листах придется использовать вот такую конструкцию:
Код нужных макросов можно скопировать в новый модуль вашего файла (жмем Alt + F11 чтобы попасть в Visual Basic, далее Insert - Module). Запускать их потом можно через вкладку Разработчик - Макросы (Developer - Macros) или сочетанием клавиш Alt + F8 . Макросы будут работать в любой книге, пока открыт файл, где они хранятся. И помните, пожалуйста, о том, что действия выполненные макросом невозможно отменить - применяйте их с осторожностью.
Способ 6. Для ленивых
Если ломает делать все вышеперечисленное, то можно поступить еще проще - установить надстройку PLEX, где уже есть готовые макросы для конвертации формул в значения и делать все одним касанием мыши:
Попробую в двух словах описать суть статьи: предположим на листе есть формула: = A1 * A14 +(5+ C13 )* C14 / B11
В принципе все понятно и наглядно. Но иногда требуется понять, что за значения скрываются за ссылками на ячейки. Т.е. из приведенной выше формулы надо сделать: =10*5,2+(5+5)*10 /7,8
Одно дело, если ссылок на ячейки в формуле пара штук. Но вот когда их 10 и более, да еще и на разные листы/книги ссылаются - высматривать значение каждой достаточно утомительное занятие.
Выделять каждую формулу, чтобы посмотреть это - тоже не всегда удобно. И стандартными методами Excel заменить не получится. Поэтому в один прекрасный момент я решил написать код, приведенный в файле, который вместо ссылок на ячейки отображает их значения. Все функции и знаки при этом так же присутствуют. Таким образом формулу можно изучить более наглядно.
Прежде всего необходимо сделать следующее:
- скачать файл, приложенный к статье
- ознакомиться со статьей Что такое макрос и где его искать?, если еще не знакомы с макросами
- при необходимости коды из файла перенести в свой файл(перейти к просмотру кодов можно, нажав в файле кнопку "Посмотреть код")
Как это работает:
- выделяем ячейки с формулами, которые необходимо "показать";
- нажимаем кнопку "Показать значения формулы в выделенных ячейках";
- появится запрос
-
если выбрать "Нет" преобразованная формула будет записана в ячейках, правее каждой из выделенных
Если в какой-либо из ячеек не будет ссылок на другие ячейки, а просто текстовая формула, то как результат отобразится сама формула и за ней текст: "[ссылок на другие ячейки нет]"
Если в формуле применяются функции( ВПР , СЧЁТЕСЛИ , МИН , МАКС и т.д.), то их имена будут отображены без искажений(как во вложенном примере =СУММ(5,2;7,8)+ЦЕЛОЕ(5/11) )
Если присутствуют ссылки на ячейки из других листов или книг, то они отображаются как и все остальные - просто значениями.
Если в формулах встречаются ссылки на массивы ячеек (A14:B16) - будут отображены все значения непустых ячеек массива(как и положено массиву в фигурных скобках: , двоеточием разделяются строки, а точкой-с-запятой - столбцы).
В ближайшее время планирую сделать некую настройку данного кода, чтобы можно было рядом со значениями отображать названия листов и книг, с которых получены эти значения. Пока размышляю насколько это может быть полезно и нужно и как наиболее удобочитаемо это отображать.
Tips_All_Get_ValFormulaText.xls (53,0 KiB, 5 916 скачиваний)
В этой статье я покажу как можно быстро и качественно изменить адреса гиперссылок на листе Excel.
Существуют ситуации, когда на листе есть много гиперссылок(если еще на знакомы с гиперссылками - Что такое гиперссылка?) на различные папки или интернет ресурсы. И иногда случаются ситуации когда адреса этих гиперссылок надо поменять. Как правило это происходит если либо домен сменился, либо на сервере добавилась директория и эти изменения надо отразить в гиперссылках, либо все просто было перемещено в другую папку. Для примера возьмем такие исходные данные: надо заменить текст ссылки .excel_vba на текст excel-vba .
Прежде чем начать замену необходимо еще определить каким способом установлена гиперссылка. Если установлена через формулу ГИПЕРССЫЛКА (HYPERLINK) , то все просто:
- выделяем диапазон с гиперссылками;
- жмем Ctrl + H .
- Найти: .excel_vba
- Заменить на: excel-vba
- Жмем кнопочку "Параметры" и устанавливаем Область поиска - Формулы и снимаем галочку "Ячейка целиком"
- Жмем "Заменить все"
Теперь адреса ссылок должны поменяться.
Все гораздо сложнее, если гиперссылки были созданы через стандартное меню: правый клик мыши на ячейке - Гиперссылка. Тут фокус с заменой через Ctrl + H не пройдет. В таких случаях придется прибегнуть к помощи VBA(Visual Basic for Applications) или как еще называют эти коды - макросы. Текст такого макроса:
Sub Replace_Hyperlink() Dim rCell As Range, rRange As Range, sWhatRep As String, sRep As String On Error Resume Next Set rRange = Application.InputBox("Укажите диапазон для замены", "Выбор данных", Type:=8) If rRange Is Nothing Then Exit Sub sWhatRep = InputBox("Что меняем?", "Ввод данных", ".excel_vba") sRep = InputBox("На что меняем?", "Ввод данных", "excel-vba") If sWhatRep = "" Then Exit Sub If sRep = "" Then If MsgBox("Хотите заменить " & sWhatRep & " на пусто?", vbCritical + vbYesNo, "Предупреждение") = vbNo Then Exit Sub End If Application.ScreenUpdating = 0 For Each rCell In rRange If rCell.Hyperlinks.Count > 0 Then If rCell.Hyperlinks(1).Address = rCell.Value Then rCell = Replace(rCell.Value, sWhatRep, sRep) End If If rCell.Hyperlinks(1).Address <> "" Then rCell.Hyperlinks(1).Address = Replace(rCell.Hyperlinks(1).Address, sWhatRep, sRep) End If If rCell.Hyperlinks(1).SubAddress <> "" Then rCell.Hyperlinks(1).SubAddress = Replace(rCell.Hyperlinks(1).SubAddress, sWhatRep, sRep) End If End If Next rCell Application.ScreenUpdating = 1 End Sub
Как все это использовать:
- создаем стандартный модуль и помещаем в него код макроса выше
- жмем Alt + F11 и выбираем макрос Replace_Hyperlink (или создаем кнопку для вызова макроса на листе)
- в первом диалоговом окне указываем в каком диапазоне надо найти гиперссылки и заменить в них адрес
- во втором диалоговом окне указываем какой текст заменить
- в третьем диалоговом окне указываем на что заменить указанный в первом окне текст
Примерно так же можно заменить гиперссылки в объектах на листе(например, картинках и кнопках):
Sub Replace_Hyperlink_inShape() Dim oSh As Shape, sWhatRep As String, sRep As String Dim s As String sWhatRep = InputBox("Что меняем?", "Ввод данных", "www.excel-vba.com") sRep = InputBox("На что меняем?", "Ввод данных", "www.excel-vba.ru") On Error Resume Next For Each oSh In ActiveSheet.Shapes s = "" s = oSh.Hyperlink.Address If s <> "" Then oSh.Hyperlink.Address = Replace(oSh.Hyperlink.Address, sWhatRep, sRep) End If Next End Sub
Данные код работает почти так же как и предыдущий:
- создаем стандартный модуль и помещаем в него код макроса выше
- жмем Alt + F11 и выбираем макрос Replace_Hyperlink_inShape (или создаем кнопку для вызова макроса на листе)
- в первом диалоговом окне указываем какой текст заменить
- во втором диалоговом окне на что заменить указанный в первом окне текст
Гиперссылки всех объектов на листе будут изменены. Если у объекта нет гиперссылки - объект будет пропущен.
Чтобы заменить гиперссылки только в выделенных объектах необходимо строку
For Each oSh In ActiveSheet.Shapes
заменить на такую:
For Each oSh In Selection.ShapeRange
тогда надо будет выделить объекты на листе, для которых необходимо заменить гиперссылки, и запустить макрос.
Пример замены гиперссылок.xls (58,0 KiB, 10 267 скачиваний)
Команда преобразует ссылки на ячейки в формуле на значения ячеек, на которые ведут эти ссылки. К примеру на листе есть формула: = A1 * A14 +(5+ C13 )* C14 / B11
И необходимо понять какие значения скрываются за ссылками на ячейки. Т.е. из приведенной выше формулы надо сделать что-то вроде: = 10 * 5,2 +(5+ 5 )* 10 / 7,8
Можно это сделать и вручную, конечно. Для одной формулы. И если ссылок не очень много и не ссылаются они на другие листы или книги. Но вот если формул, которые надо преобразовать много, то сделать это будет не так-то просто и тем более не быстро. А с помощью данной команды это можно сделать буквально за несколько секунд и практически не прилагая усилий. Все, что потребуется, это определить диапазон с формулами и метод отображения преобразованных формул.
Диапазон с формулами( Лист1!$B$3:$D$16 ) - указывается диапазон, формулы из которого необходимо преобразовать.
Метод вывода:
-
В комментарии к ячейкам с формулами - преобразованная формула будет записана в примечание к ячейке, в которой расположена эта формула.
Выводить значения с точностью как на экране - если установлен, то значения ссылок выводятся так же, как они отображаются в ячейках. К примеру, если в ячейке A14 отображается значение "5,2" это не всегда означает, что само значение ячейки так же " 5,2 ". Если к ячейке применен формат Числовой с количеством знаков после запятой 1, а в ячейке значится "5,159", то это значение тоже будет отображаться как "5,2". Это следует учитывать, применяя данную команду. Если флажок для данной опции не установлен, то в преобразованной формуле будут использованы реальные значения ячеек, несмотря на примененные к ним числовые форматы.
Проверять вложенность формул в ссылках - Если установлен, то при обнаружении внутри формулы ссылок на другие ячейки,которые так же содержат формулы - формулы этих ячеек так же будут разобраны и все ссылки в них заменены на значения. Используется в случаях, если формулы имеют множество связей друг с другом и надо проследить все значения.
Не выводить [значение недоступно] - данная опция необходима в случаях, когда в формуле присутствуют ссылки на другие книги и эти книги закрыты. Получить значение таких ссылок в этом случае невозможно. Поэтому для таких ссылок замена происходит следующим образом: путь к книге будет оставлен без изменений, а имя книги, листа и адрес ячеек будут заменены на [значение недоступно]. Однако, далеко не всегда требуется скрывать источник ссылки. К тому же, если выбран пункт Заменить ссылки на их значения прямо в формуле - текст [значение недоступно] вызовет ошибку формулы. Но если ссылку оставить без изменений, то формула будет работать.
Не выводить [ссылок на другие ячейки нет] - Если установлен, то для ячеек, не содержащих ссылок, будет возвращено значение пусто, а не текст [ссылок на другие ячейки нет]. Пригодится в случае, если применяется метод Заменить ссылки на их значения прямо в формуле, т.к. в этом случае лишний текст может привести к ошибке расчетов формулы.
Не заменять именованные диапазоны на значения - Если установлен, то ссылки на именованные диапазоны внутри формулы не будут заменяться значениями, а имена останутся "как есть". Может пригодится, если именованные диапазоны были созданы специально для удобства разбора формулы. В этом случае замена их на значения может только запутать.
Если в какой-либо из ячеек не будет ссылок на другие ячейки, а просто текстовая формула, то как результат отобразится сама формула. Если при этом пункт Не выводить [ссылок на другие ячейки нет] не отмечен, то за формулой так же будет дописан текст: "[ссылок на другие ячейки нет]".
Если в формуле применяются функции (ВПР, СЧЁТЕСЛИ, МИН, МАКС и т.д.), то их имена будут отображены без искажений (=СУММ(5,2;7,8)+ЦЕЛОЕ(5/11))
Если присутствуют ссылки на ячейки из других листов или книг, то они отображаются как и все остальные - просто значениями. Если в формуле есть ссылка на другую книгу, которая в настоящий момент закрыта - то сам путь к книге будет оставлен без изменений, а имя книги, листа и адрес ячеек будут заменены на [значение недоступно]. Если при этом установлен флажок Не выводить [значение недоступно], то ссылка пропускается и ничем не заменяется.
Если в формулах встречаются ссылки на массивы ячеек (A14:B16) — будут отображены все значения непустых ячеек массива (как и положено массиву в фигурных скобках: . Для русской локализации двоеточием разделяются строки, а точкой-с-запятой - столбцы).
Как переместить или заменить ссылки в формуле Excel?
Например, у вас есть список формул, показанный как = Лист1! (Ячейка) +1, теперь вы хотите заменить все Sheet1 в формулах с Sheet3 как показано на скриншоте ниже, как вы можете быстро решить эту проблему в Excel?
Перемещение или замена ссылок в формуле Excel
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Если вы хотите переместить или заменить конкретную ссылку во всех формулах диапазона или листа, вы можете применить Найти и заменить утилита для ее решения.
1. Выберите диапазон или лист, на котором вы хотите использовать формулы, нажмите Ctrl + H ключи, чтобы показать Найти и заменить диалог. Смотрите скриншот:
2. Нажмите Доступные опции чтобы развернуть диалоговое окно, введите ссылку, которую хотите найти в Найти то, что текстовое поле, введите новую ссылку, которую вы используете в Заменить диалоговое окно и выберите Формулы в Заглянуть раскрывающийся список. Смотрите скриншот:
3. Нажмите Заменить все, то все ссылки в выбранном или активном листе были заменены на новые.
Наконечник.
1. Если вы хотите переместить ссылки, вам просто нужно ничего не оставлять in Заменить текстовое поле в Найти и заменить Диалог.
2. Если вы хотите найти или заменить символы или ссылки в нескольких книгах или листах, вы можете применить Kutools for Excelпродвинутый Найти и заменить функция, которая может оказать вам любезную услугу. Полная функция без ограничений в течение 60 дней, пожалуйста, скачайте и получите бесплатную пробную версию сейчас.
Читайте также: