Excel замена в формулах с ссылками на другую книгу

Обновлено: 07.07.2024

Процедура поиска и замены данных — одна из самых востребованных в Excel. Базовая процедура позволяет заменить за один заход только одно значение, но зато множеством способов. Рассмотрим, как эффективно работать с ней.

Горячие клавиши

Сочетания клавиш ниже заметно ускорят работу с инструментом:

Для запуска диалогового окна поиска — Ctrl + F
Для запуска окна поиска и замены — Ctrl + H
Для выделения всех найденных ячеек (после нажатия кнопки «найти все» — Ctrl + A
Для очистки всех найденных ячеек — Ctrl + Delete
Для ввода одних и тех же данных во все найденные ячейки — Ввод текста, Ctrl + Enter

Смотрите gif-примеры: здесь мы производим поиск ячеек с дальнейшим их редактированием. В отличие от замены, редактирование найденных ячеек позволяет быстро менять их содержимое целиком.

Находим все пустые ячейки в диапазоне и заполняем их нулями или одним значением с помощью горячих клавиш

Процедура «Найти и заменить» не работает

Я сам когда-то неоднократно впадал в ступор в подобных ситуациях. Уверен и видишь своими глазами, что искомый паттерн в данных есть, но Excel при выполнении процедуры поиска сообщает:

Не удалось ничего найти по вашему запросу

Мы не нашли ничего, что нужно было заменить

Статус опций «Учитывать регистр» и «Ячейка целиком» виден после нажатия кнопки «Параметры».

Подстановочные знаки, или как найти «звездочку»

Сухая официальная справка по Excel сообщает, что можно использовать подстановочные символы «*» и «?». Что они означают несколько символов, включая их отсутствие, и один любой символ. И что их можно использовать для соответствующих процедур поиска.

Чего не говорит справка — это того, что в комбинации с опцией «ячейка целиком» эти символы позволяют, не прибегая к помощи расширенного фильтра и процедуры поиска группы ячеек:

Находить ячейки, заканчивающиеся на определенный символ, слово или текст
Аналогично, находить ячейки, начинающиеся с определенного символа, слова или текста
Находить непустые ячейки

На примере ниже мы находим все двузначные числа, затем числа, заканчивающиеся и начинающиеся на 7, и, наконец, все непустые ячейки. Напомню, выделить все результаты поиска помогает горячее сочетание клавиш Ctrl + A

Так а как найти звездочку?

Действительно, забыл. Чтобы найти «звездочку», нужно в окошке поиска ставить перед ней знак

(тильда), находится обычно под клавишей Esc . Это позволяет экранировать «звездочку», как и вопросительный знак, и не воспринимать их как служебные символы.

Замена нескольких значений на несколько

Массовая замена в Excel — довольно частая потребность. Очень часто нужно массово и при этом быстро заменить несколько символов, слов и т.д. на другие. При этом на текущий момент простого инструмента в стандартном функционале Excel нет.

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

Эта задача более сложная, чем замена на одно значение. Как ни странно, функция «ЗАМЕНИТЬ» здесь не подходит — она требует явного указания позиции заменяемого текста. Зато может помочь функция «ПОДСТАВИТЬ«.

Массовая замена с помощью функции «ПОДСТАВИТЬ»

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

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

Но у решения есть и свои недостатки:

Функция ПОДСТАВИТЬ регистрозависимая, что заставляет при замене одного символа использовать два его варианта — в верхнем и нижнем регистрах. Хотя, в некоторых случаях, как пример на картинке выше, это и преимущество.
максимум 64 замены — хоть и много, но все же ограничение.
формально процедура замены таким способом будет происходить массово и моментально, однако, длительность написания таких формул сводит на нет это преимущество. За исключением случаев, когда они будут использоваться многократно.

Файл-шаблон с формулой множественной замены

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

Файл доступен по ссылке, но можно и не скачивать его, а просто скопировать текст формулы ниже и вставить ее в любую ячейку, кроме диапазона A1:B64. Формула заменяет в ячейке C1 значения в столбце A стоящими напротив в столбце B.

А вот и она сама (тройной клик по любой части текста = выделить всю формулу). Обращается к ячейке D1, делая 64 замены по правилам, указанным в ячейках A1-B64. При этом в столбцах можно удалять значения — это не нарушит ее работу.

Заменить несколько значений на одно

С помощью функции «ПОДСТАВИТЬ«

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

С помощью регулярных выражений

Важно: регулярные выражения не поставляются в Excel «из коробки», но формулы ниже доступны бесплатно, если установить надстройку !SEMTools

Регулярные выражения (RegEx, регулярки) — наиболее удобное решение, когда нужно заменить несколько символов на один. Все эти несколько символов обычным способом безо всяких разделителей нужно перечислить внутри квадратных скобок. Примеры формул:

Массовая замена символов регулярными выражениями при установленной надстройке !SEMTools

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

Массовая замена в !SEMTools

Надстройка для Excel !SEMTools позволяет в пару кликов производить замены на всех уровнях:

символов и их сочетаний
паттернов регулярных выражений
слов!
целых ячеек (В некоторой степени аналог ВПР)

Меню инструментов массовой замены в !SEMTools

При этом процедуры изменяют исходный диапазон, что экономит время. Все что нужно — предварительно выделить его, определиться с задачей, вызвать нужную процедуру и выделить 2 столбца сопоставления заменяемых и замещающих значений (предполагается, что если вы знаете, что на что менять, то и такие списки есть).

Пример: замена символов по вхождению

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

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

Массовая замена символов по вхождению на примере Leet Language (некоторые английские буквы заменяются на похожие цифры)

Пример: замена списка слов на другой список слов

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

С версии !SEMTools 9.18.18 появилась опция — при замене списка слов не учитывать пунктуацию в исходных предложениях, а регистр слов теперь сохраняется:

Инструменты находятся в группе макросов «ИЗМЕНИТЬ» в отдельном меню и для удобства продублированы в меню «Изменить символы«, «Изменить слова» и «Изменить ячейки«.

Для начала скажу, что я зарабатываю через вот этого брокера , проверен он временем! А вот хороший пример заработка , человек зарабатывает через интернет МНОГО МНОГО МНОГО и показывает все на примерах, переходи и читай! Добавь страницу в закладки. А теперь читаете информацию ниже и пишите свой отзыв

Видео: Как убрать ноль при вычислениях в формулах Excel

Работа с формулами Excel предполагает не только написать формулу, но и заменить ссылки в формулах, при необходимости.

Допустим, мы копируем лист Excel (лист 1) с таблицей, в которой есть формулы со ссылкой на «лист 2». Нам надо поменять в скопированной таблице в формулах ссылку «лист 2» на ссылку «лист 3».

Удобнее здесь выделить диапазон и заменить разом ссылку во всех нужных нам формулах. Делают это с помощью макросов.

Но, здесь есть нюансы, поэтому рассмотрим подробно на примере. В примере все таблицы придуманы для этого примера, поэтому не ищите в них бухгалтерскую логику. Итак, есть первый лист «январь», в нем, список сотрудников и их доход.

Третий лист «Итого», в нем таблица «итого январь».

Смотрите строку формул. Здесь стоит абсолютная ссылка, но может стоять и относительная. Есть еще такие же два листа, но: «февраль» и «касса февраль».

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

В таблице, в которой будем менять ссылки, выделяем нужные ячейки (диапазон ячеек). Диапазон ячеек быстрее и удобнее выбрать по имени диапазона, особенно, если ячейки не смежные.
На закладке «Главная» в разделе «Редактирование» нажимаем кнопку «Найти и выделить». В строке «Найти» пишем ссылку, которую будем менять.

Нажимаем кнопку «Найти все». Вышел список ячеек с формулами с ссылкой «январь!». Нюанс! Благодаря тому, что мы предварительно отметили диапазон с нужными нам ячейками, в списке будут показаны ячейки только из отмеченного диапазона.

Если бы не выделяли предварительно диапазон, то в списке были бы ячейки со всего листа. В т.ч., и за январь, где нам не надо менять ссылки. Это бы затруднило нашу работу.

Нажимаем вкладку «Заменить» и в строке «Заменить на» пишем новую ссылку, которую нам надо. А вот теперь придется поработать над каждой ячейкой. Наводим курсор на каждую строку списка ячеек и затем нажимаем кнопку «Заменить».

Если нужно поменять ссылку в формуле Excel на всем листе, то нажмем кнопку «Заменить все», и заменятся ссылки во всех формулах листа, даже в тех, которых нет в списке. Можно настроить список для удобной ориентировки в нем – это кнопка «Параметры».

Здесь можно настроить просмотр списка по строкам или по столбцам, искать формулы на этом листе или в книге. Всё, ссылки в формулах заменены. Также меняем ссылки «`касса январь`!» на ссылку «`касса февраль`!».

В этом названии ссылки нужно указывать и одинарные кавычки, писать строго, как написано в формуле.

В таблице Excel можно закрепить верхние строки (шапку таблицы) и левые столбцы в таблице. И, тогда, при прокрутке таблицы вниз или в сторону, эти строки и столбцы всегда будут видны на экране монитора. Так удобнее работать с большой таблицей.

Например, у вас есть список формул, показанный как = Лист1! (Ячейка) +1, теперь вы хотите заменить все Sheet1 в формулах с Sheet3 как показано на скриншоте ниже, как вы можете быстро решить эту проблему в Excel?

Перемещение или замена ссылок в формуле Excel

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

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

1. Выберите диапазон или лист, на котором вы хотите использовать формулы, нажмите Ctrl + H ключи, чтобы показать Найти и заменить диалог. Смотрите скриншот:

2. Нажмите Параметры чтобы развернуть диалоговое окно, введите ссылку, которую хотите найти в Найти то, что текстовое поле, введите новую ссылку, которую вы используете в Заменить диалоговое окно и выберите Формулы в Заглянуть раскрывающийся список. Смотрите скриншот:

3. Нажмите Заменить все, то все ссылки в выбранном или активном листе были заменены на новые.

Наконечник.

1. Если вы хотите переместить ссылки, вам просто нужно ничего не оставлять in Заменить текстовое поле в Найти и заменить Диалог.

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

Команда преобразует ссылки на ячейки в формуле на значения ячеек, на которые ведут эти ссылки. К примеру на листе есть формула: = 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) — будут отображены все значения непустых ячеек массива (как и положено массиву в фигурных скобках: . Для русской локализации двоеточием разделяются строки, а точкой-с-запятой - столбцы).

Читайте также: