Как в excel сделать замену одной цифры на другую?

Найти и заменить в Excel

Поиск и замена данных – одна из часто применяемых операций в Excel. Используют даже новички. На ленте есть большая кнопка.

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

Далее вызываем Главная → Редактирование → Найти и выделить → Найти (кнопка с рисунка выше). Поиск также можно включить с клавиатуры комбинацией клавиш Сtrl+F. Откроется диалоговое окно под названием Найти и заменить.

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

После запуска поиска программа Excel быстро-быстро просматривает содержимое листа (или указанного диапазона) на предмет наличия искомой комбинации символов. Если такая комбинация обнаружена, то в случае нажатия кнопки Найти все Excel вываливает все найденные ячейки.

Если в нижней части окна выделить любое значение и затем нажать Ctrl+A, то в диапазоне поиска будут выделены все соответствующие ячейки.

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

Поиск нестрогого соответствия символов

Иногда пользователь не знает точного сочетания искомых символов что существенно затрудняет поиск. Данные также могут содержать различные опечатки, лишние пробелы, сокращения и пр., что еще больше вносит путаницы и делает поиск практически невозможным. А может случиться и обратная ситуация: заданной комбинации соответствует слишком много ячеек и цель поиска снова не достигается (кому нужны 100500+ найденных ячеек?).

Для решения этих проблем очень хорошо подходят джокеры (подстановочные символы), которые сообщают Excel о сомнительных местах. Под джокерами могут скрываться различные символы, и Excel видит лишь их относительное расположение в поисковой фразе. Таких джокеров два: звездочка «*» (любое количество неизвестных символов) и вопросительный знак «?» (один «?» – один неизвестный символ).

Так, если в большой базе клиентов нужно найти человека по фамилии Иванов, то поиск может выдать несколько десятков значений. Это явно не то, что вам нужно. К поиску можно добавить имя, но оно может быть внесено самым разным способом: И.Иванов, И. Иванов, Иван Иванов, И.И. Иванов и т.д. Используя джокеры, можно задать известную последовательно символов независимо от того, что находится между. В нашем примере достаточно ввести и*иванов и Excel отыщет все выше перечисленные варианты записи имени данного человека, проигнорировав всех П. Ивановых, А. Ивановых и проч. Секрет в том, что символ «*» сообщает Экселю, что под ним могут скрываться любые символы в любом количестве, но искать нужно то, что соответствует символам «и» + что-еще + «иванов». Этот прием значительно повышает эффективность поиска, т.к. позволяет оперировать не точными критериями.

Если с пониманием искомой информации совсем туго, то можно использовать сразу несколько звездочек. Так, в списке из 1000 позиций по поисковой фразе мол*с*м*уход я быстро нахожу позицию «Мол-ко д/сн мак. ГАРНЬЕР Осн.уход д/сух/чув.к. 200мл» (это сокращенное название от «Молочко для снятия макияжа Гараньер Основной уход….»). При этом очевидно, что по фразе «молочко» или «снятие макияжа» поиск ничего бы не дал. Часто достаточно ввести первые буквы искомых слов (которые наверняка присутствуют), разделяя их звездочками, чтобы Excel показал чудеса поиска. Главное, чтобы последовательность символов была правильной.

Есть еще один джокер – знак «?». Под ним может скрываться только один неизвестный символ. К примеру, указав для поиска критерий 1?6, Excel найдет все ячейки содержащие последовательность 106, 116, 126, 136 и т.д. А если указать 1??6, то будут найдены ячейки, содержащие 1006, 1016, 1106, 1236, 1486 и т.д. Таким образом, джокер «?» накладывает более жесткие ограничения на поиск, который учитывает количество пропущенных знаков (равный количеству проставленных вопросиков «?»).

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

Продвинутый поиск

Мало, кто обращается к кнопке Параметры в диалоговом окне Найти и заменить. А зря. В ней скрыто много полезностей, которые помогают решить проблемы поиска. После нажатия кнопки Параметры добавляются дополнительные поля, которые еще больше углубляют и расширяют условия поиска.

С помощью дополнительных параметров поиск в Excel может заиграть новыми красками в прямом смысле слова. Так, искать можно не только заданное число или текст, но и формат ячейки (залитые определенным цветом, имеющие заданные границы и т.д.).

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

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

Поиск формата – это хорошо, но чаще искать приходится конкретные значения. И тут Excel предоставляет дополнительные возможности для расширения и уточнения параметров поиска.

Первый выпадающий список Искать предлагает ограничить поиск одним листом или расширить его до целой книги.

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

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

В следующем выпадающем списке находится замечательная возможность поиска по формулам, значениям, а также примечаниям. По умолчанию Excel производит поиск в формулах либо, если их нет, в содержимом ячейки. Например, если искать фамилию Иванов, а фамилия эта есть результат формулы (копируется из соседнего листа), то поиск нечего не даст, т.к. в ячейке нет искомого перечня символов. По той же причине не удастся отыскать число, являющееся результатом работы какой-либо функции. Поэтому бывает смотришь в упор на ячейку, видишь искомое значение, а Excel его почему-то не видит. Это не глюк, это настройка поиска. Измените данный параметр на Значения и поиск будет осуществляться по тому, что отражено в ячейке, независимо от содержимого. Например, если в ячейке содержится результат вычисления 1/6 (как значение, а не формула) и при этом формат отражает только 3 знака после запятой (т.е 0,167), то поиск символов «167» при выборе параметра Формулы эту ячейку не обнаружит (реальное содержимое ячейки — это 0,166666…), а при выборе Значения поиск увенчается успехом (искомые символы совпадают с тем, что отражается в ячейке). И последний пункт в данном списке – Примечания. Поиск осуществляется только в примечаниях. Очень может помочь, т.к. примечания часто скрыты.

В диалоговом окне поиска есть еще две галочки Учитывать регистр и Ячейка целиком. По умолчанию Excel игнорирует регистр, но можно сделать так, чтобы «иванов» и «Иванов» отличались. Галочка Ячейка целиком также может оказаться весьма полезной, если ищется ячейка не с указанным фрагментом, а полностью состоящая из искомых символов. К примеру, как найти ячейки, содержащие только 0? Обычный поиск не подойдет, т.к. будут выдаваться и 10, и 100. Зато, если установить галочку Ячейка целиком, то все пойдет, как по маслу.

Читать еще:  Как сделать калькуляцию блюд в excel?

Поиск и замена данных

Данные обычно ищутся не просто так, а для каких-то целей. Такой целью часто является замена искомой комбинации (или формата) на другую. Чтобы найти и заменить в выделенном диапазоне Excel одни значения на другие, в окне Найти и заменить необходимо выбрать вкладку Замена. Либо сразу выбрать на ленте команду Главная → Редактирование → Найти и выделить → Заменить.

Еще удобнее применить сочетание горячих клавиш найти и заменить в Excel – Ctrl+H.

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

По аналогии с простым поиском, менять можно и формат.

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

Напоследок рассмотрим один классный трюк с поиском и заменой. Многие знают, что в ячейку можно вставить разрыв строк с помощью комбинации Alt+Enter.

А как быстро удалить все разрывы строк? Обычно это делают вручную. Однако ловкое использование поиска и замены сэкономит много времени. Вызываем команду поиска и замены с помощью комбинации Ctrl+H. Теперь в строке поиска нажимаем Ctrl+J — это символ разрыва строки — на экране появится точка. В строке замены указываем, например, пробел.

Жмем Ok. Все переносы строк заменились пробелами.

Функция поиска и замены при правильном использовании заменяет часы работы неопытного пользователя. Настоятельно рекомендую использовать все вышеизложенное. Если что-то не ищется в ваших данных или наоборот, выдает слишком много лишних ячеек, то попробуйте уточнить поиск с помощью подстановочных символов «*» и «?» или настраиваемых параметров поиска. Важно понимать, что если вы ничего не нашли, это еще не значит, что там этого нет.

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

Как использовать функцию «Найти и заменить» в Excel

Excel – это табличный процессор от Microsoft. Он предназначен для удобного ведения расчетов и систематизации данных. При этом, одной из самых часто используемых функций является — «Найти и заменить».

Что это за функция?

Как понятно из названия, данная функция позволяет найти нужную информацию на странице или в книге, и заменить ее на другу.

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

Как найти и заменить в Excel?

Прежде всего, нужно вызвать окно функции. Начиная с Word 2007 вызов «найти и заменить» производится с главной вкладки панели инструментов. Интерфейс может немного отличаться, но расположение инструмента особо не менялось.

В Word 2010 в правой части панели инструментов находим надпись «найти и выделить».

Следует нажать на кнопку и в контекстном меню выбрать «заменить». Также можно использовать комбинацию Ctrl+H.

После этого откроется окно управления инструментом, где и будем вводить все настройки.

Настройки поиска

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

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

Настройки замены

Теперь переходим к замене. Стандартно инструмент предлагает только два поля для заполнения.

Если нажать кнопку «Параметры», будут открыты дополнительные инструменты.

В поле «Заменить на:» нужно вписать те данные, на которые будет заменена искомая информация.

Правее, кнопка «Формат» позволяет менять формат искомой ячейки.

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

Еще ниже расположены кнопки управления.

Кнопки с препиской «все» позволяют произвести действие сразу над всеми элементами, без подтверждения и очередности.

Оставшиеся делают тоже самое, но по очереди, переходя от элемента к элементу и спрашивая подтверждения.

Кнопка «Закрыть» позволяет выйти из текущего окна.

Примеры использования

Теперь разберем несколько примеров, которые позволят понять принцип работы и использования инструмента.

Заменить один символ на другой

Самый простой и часто используемый вариант – замена символов.

Допустим у нас есть набор чисел.

Нам нужно найти на листе все цифры 1 и заменить их на 9.

Для этого запускаем «найти и заменить», после чего в поле поиска пишем 1, а в поле замены 9.

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

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

Теперь нажимаем «Заменить все» и получаем нужный результат. Цифры в числах изменились на заданные пользователем.

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

Заменить текст

Процедура изменения всего слова или его части происходит аналогично.

У нас есть некоторый список фраз, в котором нужно изменить часть текста.

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

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

Точно также можно изменить одну букву.

Вводим нужную букву и запускаем вышеописанные инструменты.

Замена формата

Также можно использовать удобную функцию изменения формата.

Допустим нужно поменять в списке цифр формат ячейки с цифрой «7» на денежный. Для этого в поле поиска указываем нужную цифру, а рядом с полем отвечающим за изменение нажимаем кнопку «Формат» и указываем нужно значение.

В результат получим то, что было нужно.

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

Перенос строки в ячейке

Есть еще один интересный способ, который позволяет отыскать на странице переносы строки в ячейках и изменить их. Допустим у нас есть список имен.

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

Для этого в поле поиска ничего не вводим, а нажимаем комбинацию Ctrl+J. А во втором поле нажимаем пробел. После выполнения заданных условий имена на страницы примут нужны вид.

Функции Найти() и Заменить ()

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

Вводятся эти операторы одинаково. В ячейке нужно нажать «=» и вписать требуемую функцию.

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

Тут, первое значение – искомые символы, текст или часть слова указанная в кавычках, в нашем случае «кот».

Второе – адрес ячейки с которой будем работать.

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

В результате, в наше примере, получаем «1». Это говорит о том, что искомая часть слова начинается с первого символа.

Во втором случае нам нужно будет поменять слово «котобой» на «китобой». Для этого вводим в любой ячейке:

Аналогично можно использовать конструкцию:

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

Читать еще:  Как в excel сделать остатки?

Во втором случае изменим только одну вторую букву, то есть вторым параметром указываем «2», третьим «1» и последним букву «и».

Массовая замена слов

Предположим, что нам необходимо заменить все «ул.» на листе на «улица «. Или русское слово «дом» на английское «house». Или еще интереснее: все английские буквы на русские. Например, английская «а» должна быть заменена на русскую «a», английская «c» на русскую «с», английская «H» на русскую «Н» и т.д. А такое тоже нередко бывает и доставляет проблемы. Ведь если в одной таблице будут русские буквы, а в другой английские — то применение большинства встроенных функций поиска(та же ВПР) просто не найдут соответствия.
Если подобную замену надо сделать для одного сочетания, то все просто: жмем Ctrl+H и указываем что заменить и на что. Но если таких замен надо сделать 20? Или 120? Это надо будет 120 раз нажать и ввести что заменять и на что. А если это надо сделать еще и не в одном документе — то. Думаю сами справитесь с умножением количества замен на количество файлов, в которых это надо сделать. И вроде бы простая операция превращается в ваш личный ад на работе.
Недавно на форуме участнику потребовалось автоматизировать именно такую штуку. Т.к. код несложный — решил написать и чуть дополнив выложить для всех кому код может потребоваться:

Option Explicit Sub Replace_Mass() Dim s As String Dim lCol As Long Dim avArr, lr As Long Dim lLastR As Long Dim lToFindCol As Long, lToReplaceCol As Long, lLookAt As Long ‘запрашиваем направление перевода — с русского на англ. или наоборот lCol = Val(InputBox(«Укажите направление перевода:» & vbNewLine & _ » 1 — ru-en» & vbNewLine & _ » 2 — en-ru», «Запрос», 1)) If lCol = 0 Then Exit Sub ‘запрашиваем по части ячейки искать или по всему тексту ‘по умолчанию — по части lLookAt = Val(InputBox(«Искать соответствие по части ячейки или по всему тексту:» & vbNewLine & _ » 1 — по всему тексту» & vbNewLine & _ » 2 — по части ячейки», «Запрос», 2)) If lLookAt = 0 Then Exit Sub Select Case lCol Case 1 lToFindCol = 1 lToReplaceCol = 2 Case 2 lToFindCol = 2 lToReplaceCol = 1 End Select Application.ScreenUpdating = 0 ‘Получаем с листа Соответствия значения, которые надо заменить в выделенном диапазоне With ThisWorkbook.Sheets(«Соответствия») lLastR = .Cells(.Rows.Count, 1).End(xlUp).Row avArr = .Cells(1, 1).Resize(lLastR, 2) End With ‘заменяем For lr = 1 To UBound(avArr, 1) s = avArr(lr, lToFindCol) If Len(s) Then ‘если значение для замены не пустое Selection.Replace s, avArr(lr, lToReplaceCol), lLookAt End If Next lr Application.ScreenUpdating = 1 End Sub

Как это работает. В книге есть специальный лист с именем «Соответствия». На нем в столбце А записаны слова, которые необходимо заменить, а в столбце В — на что эти слова заменить. Если в столбце А пусто — то замена не будет произведена. Если в столбце В пусто — то значение из столбца А будет просто удалено.
Замены производятся исключительно в выделенных на листе ячейках. Ячейки могут быть несмежными.

Итак, необходимо сделать много замен. Скачиваете файл:

Массовая замена слов (54,5 KiB, 6 171 скачиваний)

Примечание: Я сделал файл как переводчик. Т.е. в первом столбце русские слова, во втором английские. Но в столбцах может быть что угодно — хоть слова, хоть символы, хоть числа.
На лист «Соответствия» записываете в столбец А — что заменять, в столбец В — на что заменять. Переходите на лист книги, в котором необходимо произвести замену. Выделяете ячейки, значения в которых надо найти и заменить. После чего жмете Alt+F8 и выбираете макрос «Tips_Macro_ReplaceMASS.xls!Replace_Mass»(или просто «Replace_Mass», если код в той же книге, что и ячейки для поиска и замены).
Первым появится окно с запросом направления перевода. По умолчанию 1(ru-en). Т.е. будет браться слово из столбца А и заменяться словом из столбца В. Но если указать 2 — то будет браться слово из столбца В и заменяться словом из столбца А. Т.е. аналог переводчика — с рус. на англ. и наоборот. Либо из А в В, либо из В в А.
Вторым появится запрос на метод просмотра данных:

  • если указать «1 — по всему тексту» — данные из столбца А будут заменять только в том случае, если ячейка в выделенном для замены диапазоне полностью совпадает со значением из столбца А листа «Соответствия». Например, в любой из выделенных ячеек записано «На столе книга», а на листе «Соответствия» в столбце А есть только слово «книга». Замена не будет произведена, т.к. необходимо, чтобы в столбце А было так же «На столе книга».
  • если указать «2 — по части ячейки» — данные из столбца А будут заменять в случае, если ячейка в выделенном для замены диапазоне содержит любое слово из столбца А листа «Соответствия». На том же примере — «На столе книга». Если выбрать 2, то в тексте «На столе книга» слово книга будет заменено на слово из столбца В — «book».

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

Как видно, здесь присутствую только номера статей, но нет их расшифровки. Зато расшифровка есть в отдельном листе «Справочник»:

Как видно, в справочнике присутствуют нужные номера статей и можно было бы применить ту же ВПР(VLOOKUP) для замен. Если бы не одно но: в таблице по реализации помимо номеров статьей есть еще лишний текст «Статья затрат:». Конечно, можно сначала заменить этот текст, потом в отдельном столбце применить ВПР, заменить формулу значениями и вернуть в исходный столбец. Если при этом надо еще оставить текст «Статья затрат:», то надо будет сделать еще доп.манипуляции либо при составлении формулы, либо после. В любом случае — слишком много лишних телодвижений. А значит бОльшие времязатраты.
Приложенный ниже файл поможет сделать это в разы быстрее:
Скачать файл с примером и кодом:

Массовая замена слов — статьи.xls (91,5 KiB, 1 413 скачиваний)

и в итоге за пару секунд получим следующий результат:

Достаточно выделить столбец со статьями на листе с реализацией и запустить код(либо нажатием кнопки заменить значения, либо нажав Alt+F8 и выбрав из списка макросов макрос Replace_Mass ).
После нажатия на кнопку будут запрошены следующие параметры:

  1. указать номер столбца значений в листе «Справочник», в котором искать соответствия номерам статей(в нашем случае это столбец 1(А))
  2. указать номер столбца, значениями которого заменять найденные в таблице реализации значения(это может быть один из трех столбцов справочника: Группа затрат, Статья затрат, Подстатьи затрат). Логичнее всего указать столбец 4, т.к. он наиболее детализирован и конкретнее указывает расшифровку статьи
  3. далее будет предложено указать точность поиска:
    • если указать » 1 — по всему тексту » — данные будут заменены только в том случае, если значение ячейки в выделенном для замены диапазоне полностью совпадает со значением из столбца А листа «Справочник». Т.е. если бы у нас в таблице реализации был бы записан только номер статьи(1.01), тогда можно было бы указать именно 1
    • если указать » 2 — по части ячейки » — данные будут заменены только в том случае, если значение ячейки в выделенном для замены диапазоне содержит любое значение из столбца А листа «Справочник». Это больше подходит к описанному случаю, т.к. нам необходимо заменить исключительно номер статьей на их расшифровку, оставив при этом текст «Статья затрат: «

Если все указано корректно, то на листе будут произведены все необходимые замены.
Возможные ошибки, которые предусмотрены кодом и о которых будет сообщено соответствующим сообщением(код прервется, замены не будут произведены):

  • на листе Справочник нет значений
  • в качестве столбца для поиска значений и для замены значений на листе Справочник указано одно и то же число
  • в качестве столбца значений для замены указано число, превышающее общее количество столбцов на листе Справочник

Особое внимание хочу уделить случаю, когда выбирается замена по части ячейки. В этом случае лучше список на листе Справочник отсортировать по длине текста по тому столбцу, в котором будут значения для поиска. Зачем это надо: т.к. значение по части ячейки будет заменять не полное соответствие, то есть вероятность неверных замен. Например, есть текст «Статья затрат: 1.011» . В то же время на листе Справочник есть статьи «1.01» и «1.011» . Т.к. «1.01» идет раньше в большинстве случаев, то текст будет заменен некорректно: «Статья затрат: ТВ 1 » .
Чтобы получить длину строки текста можно использовать функцию ДЛСТР(LEN):
=ДЛСТР( A2 )
=LEN(A2)

Читать еще:  Как сделать в excel проверку совпадений с введенными данными?

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

Статья помогла? Поделись ссылкой с друзьями!

Как в excel сделать замену одной цифры на другую?

Други — срочно!
кто знает как в множестве ячеек листа избавиться от ненужного символа переноса строки?
чертов квадратик не дает вычислять и корректно конвертировать содержимое.
имею ввиду символ нажатия «enter» , который приводит к переносу вводимого текста на строку ниже.

Добавление от 12.12.2004 01:23:

замена сработала с кодом 010 — НО! после этого функция замены вырубается
как пофиксить?
и еще — нужно в пустые ячейки автоматом вставлять некое значение — возможно ли? — путем автозамены? или только вручную? — большой гимор.

спасибо за ответы

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

вопрос — можно ли в ПУСТЫЕ ячейки забить нули автозаменой? похоже нет. а лопатить огромные листы на тысячи позиций и вручную заполнять пустые позиции — это тупо и долго.

Наверно мы с Вами говорим о разных вещах.

Настоящая автозамена, это когда мы вводим в ячейку, например z, а получаем 0 (Сервис-Автозамена)
Естественно, что для этого нам необходимо предварительно указать, что z — это неправильный вариант, а 0 — правильный.

Вы, по всей видимости, имеете ввиду меню Правка — команда Заменить

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

— Выделяете диапазон ячеек (можно выделить несмежные и не обязательно выделять строго пустые ячейки), затем в меню Правка-Перейти-Выделить-Пустые ячейки-ОК
— Вводим 0, затем CTRL + ENTER *

SS-18
Чтобы определить код символа можно воспользоваться функцией КОДСИМВ.
Допустим, символ переноса строки находится на пятой позиции в ячейке G30, тогда формула:
=КОДСИМВ(ПСТР(G30;5;1))
возвратит значение 10

Удалить этот символ из ячейки можно с помощью формулы:
=ПОДСТАВИТЬ(G30;СИМВОЛ(10);»»)

В некоторых случаях может понадобиться заменить этот символ другим, например, пробелом:
=ПОДСТАВИТЬ(G30;СИМВОЛ(10);СИМВОЛ(32))

Другой способ удалить символ с кодом 10 — использовать функцию ПЕЧСИМВ:
=ПЕЧСИМВ(G30)

SS-18
замена сработала с кодом 010 — НО! после этого функция замены вырубается

Когда вызываешь Замену во второй раз, в полях ввода ОСТАЮТСЯ предыдущие значения!
То есть там стоИт сомвол 010, но его не видно, ты дописываешь что-то. и такая комбинация символов, действительно, не встречается.
Метод: после вызова Замены поставить курсор в поле «Что» и несколько раз нажать BackSpace и Delete.

Забить пустые ячейки нулями? Нет проблем!
Выдели ячейку где-то внутри таблицы, можно в первой строке, где заголовки.
Правка-Перейти-Выделить-Текущую область (выделил всю таблицу).
Ctrl-H — Что:пустая строка, Заменить на:0 — Заменить все.

Поторопился я обрадоваться
— не помогает стирание спецсимвола и не работает функция замены после его удаления. Стирание поля в графе «заменить» — не приводит к ее активизации.
— не работают оба способа замены массивы пустых ячеек на ноль — в способе Nosorog
выделяется ТОЛЬКО вся таблица ( включая заголовки и графы с названием) и нули вставляются ТОЛЬКО вокруг ячеек массива.
в способе Vitaly K.
вообще не активна функция ОПЕРАЦИИ в специальной вставке — ее просто НЕТ ТАМ.

Попробуй задействовать такой макрос:

код: Он, собственно, применяет функцию ПЕЧСИМВ к каждой ячейке таблицы, и пишет 0 в каждую пустую ячейку.

2pashulka
хммм.. уточняю — у меня дома стоит excel 2000-й. попытка использовать режим «правка-спецвставка» показывает окошко в котором и в помине нет группы ОПЕРАЦИЯ, а есть только выбор вида текста.. ну там Unicode или другой. и больше никаких ..
-отсюда вопрос — как в Excel 2000 получить указанный режим функции спец вставка?
Vic_K
Спасибо за вопрос, но я не знаю как формируется файл из 1С.. знаю, что у жены на работе что-то сбито в ее настройках, после того как там покопался вызванный программист по 1С. а вызывать их на каждый сбой ох как дорого, да еще с такими результатами. все вышеописанные глюки появились именно после визита программиста. т.е. справеливее сказать, что он настраивал именно то, что просили добавить, но потом обнаруживались ужасные последствия в других местах.
Политика у них такая что-ли, чтобы делать людей зависимыми от их «услуг» и на бабки разводить?

извините, ответил не квалифицировано, как смог.

Поиск и замена в Excel

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

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

Поиск данных в ячейках Excel

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

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

  1. На вкладке Главная воспользуйтесь командой Найти и выделить, а затем из раскрывающегося списка выберите пункт Найти.
  2. Появится диалоговое окно Найти и заменить. Введите данные, которые необходимо отыскать. В нашем примере мы введем имя сотрудника.
  3. Нажмите Найти далее. Если данные присутствуют на листе, то они будут выделены.
  4. Если Вы еще раз нажмете на кнопку Найти далее, то увидите следующий вариант поиска. Вы также можете выбрать Найти все, что бы увидеть все варианты, которые для Вас отыскал Excel.
  5. Закончив поиск, воспользуйтесь кнопкой Закрыть, чтобы выйти из диалогового окна Найти и Заменить.

Вы можете получить доступ к команде Найти с помощью комбинации клавиш Ctrl+F на клавиатуре.

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

Замена содержимого ячейки в Excel

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

  1. На вкладке Главная нажмите команду Найти и выделить, а затем из раскрывающегося списка выберите пункт Заменить.
  2. Появится диалоговое окно Найти и заменить. Введите текст, который Вы ищете в поле Найти.
  3. Введите текст, на который требуется заменить найденный, в поле Заменить на. А затем нажмите Найти далее.
  4. Если значение будет найдено, то содержащая его ячейка будет выделена.
  5. Посмотрите на текст и убедитесь, что Вы согласны заменить его.
  6. Если согласны, тогда выберите одну из опций замены:
    • Заменить: исправляет по одному значению зараз.
    • Заменить все: исправляет все варианты искомого текста в книге. В нашем примере мы воспользуемся этой опцией для экономии времени.

  • Появится диалоговое окно, подтверждающее количество замен, которые будут сделаны. Нажмите ОК для продолжения.
  • Содержимое ячеек будет заменено.
  • Закончив, нажмите Закрыть, чтобы выйти из диалогового окна Найти и заменить.
  • Ссылка на основную публикацию
    Adblock
    detector