Svinkovod.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Найти и заменить в 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 добавить вторую ось

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

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

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

Заменить

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

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

Диалоговое окно Заменить

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

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

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

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

Поиск и замена разрывов строк в ячейке

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

Замена разделителей строк пробелами

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

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

Есть ли поиск в excel

В этой статье описаны синтаксис формулы и использование функций ПОИСК и ПОИСКБ в Microsoft Excel.

Описание

Функции ПОИСК И ПОИСКБ находят одну текстовую строку в другой и возвращают начальную позицию первой текстовой строки (считая от первого символа второй текстовой строки). Например, чтобы найти позицию буквы «n» в слове «printer», можно использовать следующую функцию:

Эта функция возвращает 4, так как «н» является четвертым символом в слове «принтер».

Можно также находить слова в других словах. Например, функция

возвращает 5, так как слово «base» начинается с пятого символа слова «database». Можно использовать функции ПОИСК и ПОИСКБ для определения положения символа или текстовой строки в другой текстовой строке, а затем вернуть текст с помощью функций ПСТР и ПСТРБ или заменить его с помощью функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ. Эти функции показаны в примере 1 данной статьи.

Эти функции могут быть доступны не на всех языках.

Функция ПОИСКБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой БДЦС. В противном случае функция ПОИСКБ работает так же, как функция ПОИСК, и отсчитывает по одному байту на каждый символ.

Читайте так же:
Как в ворде сделать сложение столбиком

К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

Синтаксис

Аргументы функций ПОИСК и ПОИСКБ описаны ниже.

Искомый_текст Обязательный. Текст, который требуется найти.

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

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

Замечание

Функции ПОИСК и ПОИСКБ не учитывают регистр. Если требуется учитывать регистр, используйте функции НАЙТИ и НАЙТИБ.

В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (

Если значение find_text не найдено, #VALUE! возвращается значение ошибки.

Если аргумент начальная_позиция опущен, то он полагается равным 1.

Если start_num больше нуля или больше, чем длина аргумента within_text, #VALUE! возвращается значение ошибки.

Аргумент начальная_позиция можно использовать, чтобы пропустить определенное количество знаков. Допустим, что функцию ПОИСК нужно использовать для работы с текстовой строкой «МДС0093.МужскаяОдежда». Чтобы найти первое вхождение «М» в описательной части текстовой строки, задайте для аргумента начальная_позиция значение 8, чтобы поиск не выполнялся в той части текста, которая является серийным номером (в данном случае — «МДС0093»). Функция ПОИСК начинает поиск с восьмого символа, находит знак, указанный в аргументе искомый_текст, в следующей позиции, и возвращает число 9. Функция ПОИСК всегда возвращает номер знака, считая от начала просматриваемого текста, включая символы, которые пропускаются, если значение аргумента начальная_позиция больше 1.

Примеры

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Поиск частичного совпадения в Эксель

Здравствуйте, друзья. Сегодня покажу вам, как найти ячейки, текст в которых содержит нужную вам строку. Почему я решил написать об этом отдельную статью? Для поиска чаще всего используют функции ВПР и ПОИСКПОЗ, которые определяют полное совпадение строк, либо ближайшее большее, меньшее значение. А чтобы найти частичное совпадение, придётся что-нибудь придумать.

найти частичное совпадение

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

Решение проще, чем может показаться. Многие формулы поддерживают подстановочные символы:

  • * — любое количество любых символов
  • ? – один любой символ

Воспользуемся ими, чтобы сформировать нужный запрос в формуле. Искомое значение будет примерно таким: *значение*. То есть, любое количество символов до и после указанной строки. Если использовать такой подход, например, в ВПР, можно получить желаемое:

частичное совпадение в ВПР

Смотрите на картинке, я ищу «pixel», и нахожу модель, у которой в названии есть это слово.

В приведенной формуле знак «&» — оператор конкатенации (объединения) строк. Больше про объединение строк — читайте здесь

Так же можно сделать, когда применяется поиск с помощью ПОИСКПОЗ и ИНДЕКС:

частичное совпадение с ПОИСКПОЗ

Результат аналогичен, всё работает, но есть и неожиданности. Если в любом из рассмотренных вариантов вообще не задать искомое значение:

ошибочный результат

Наша формула вернула не ошибку, а данные из первой строки таблицы. Это произошло, т.к. подстановочные символы командуют формуле искать любые символы. Не критично, но можно и убрать с помощью функции ЕСЛИ:

ошибка обработана

Смотрите на рисунке выше, нет искомого текста, нет и результата.

Кроме того, если совпадения не найдены, будет выведена ошибка #Н/Д. Как её обработать, я рассказывал в этой статье.

А у меня всё, жду ваших вопросов!

Добавить комментарий Отменить ответ

1 комментарий

Шикарно!
Пару раз попробовал — сразу формула что-то не сработала…
Но «*»&A1&»*» — в впр — это реально работает, я в шоке вообще… не ожидал, что можно эту формулу заставить искать так значение… а получается что можно искать совпадения в очень больших формулах, я в шоке. спасибо, очень ценная информация!!

Читайте так же:
Можно ли включать музыку на твиче

Функция поиска в Excel

Функция поиска в Excel

Иногда, когда мы много работаем в программе Microsoft Эксель, у нас может возникнуть необходимость искать определенные данные. Но листать всю таблицу крайне неудобно и времязатратно. Особенно если таблица у вас очень большая, и данных в ней записано довольно много. В данном случае нам может помочь специальная строка поиска позиций и значений в столбце по таблице. Давайте посмотрим, каким сделать поиск данных.

Как сделать поиск в Экселе, найти слово, ячейку, текст

Функция поиска в таблице Excel дает возможность найти любые необходимые слова, или определенные ячейки. Есть три способа поиска информации: простой, расширенный и поиск по указанному интервалу ячеек. Давайте рассмотрим все способы поиска.

Метод 1 — Простой поиск

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

Итак, во вкладке «Главная», выберите на панели инструментов функцию «Найти и выделить». В выпадающем меню выбираем пункт «Найти». Также можно воспользоваться горячими клавишами Ctrl+F.

Функция поиска в Excel

После этого у вас откроется новое окно. В нем в строке «Найти» введем то сочетание слов, которое нам необходимо найти. После введения нажмем «Найти все» либо «Найти далее».

Функция поиска в Excel

Если нажать «Найти далее», то поиск перейдет на первую строку где есть совпадение. Строка в этот момент станет активной.

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

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

Или ввести цифру 7, то система начнет выдавать все сочетания цифр, которые содержат указанную цифру, например «217».

Если вам нужен не первый выданный результат, нажмите «Найти далее».

Функция поиска в Excel

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

Также вы можете найти все совпадения в виде небольшого списка. Для этого в строке поиска необходимо нажать «Найти все». Система выдаст вам все данные в виде списка, в котором будут указаны адреса расположения, лист и книга, в которых они находятся.

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

Функция поиска в Excel

Метод 2 — Расширенный поиск

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

То есть под поиск попадает не только содержимое ячейки, но и адрес элемента, к которому она непосредственно относится. Вот к примеру, у нас имеется формула которая и находится в ячейке Е2, данная формула является суммой ячеек А4 и С3, сумма равна 10.

И если мы в поиске зададим цифру 4, то поиск выдаст нам ячейку Е2, все почему? Потому что в этой ячейке находится адрес на ячейку А4. А данная ячейка содержит цифру 4.

Функция поиска в Excel

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

Функция поиска в Excel

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

Читайте так же:
Где в ворде фигуры

Функция поиска в Excel

В простом поиске функции «Учитывать регистр» и «Ячейки целиком» отключены, и если мы их включим, то при поиске вам будут выдаваться точные совпадения, и при поиске будет учитываться регистр.

И если в поиске вы будите вводить данные с маленькой буквы, то поиск уже не выдаст те ячейки, данные которых написаны с большой буквы, как это было в простом поиске.

А если вы включите функцию «Ячейки полностью», то при поиске вы сможете получить только те данные наименование которых абсолютно одинаково. К примеру, если мы введем в поиске «Василий», то ячейка, которая названа «Василий Л.И» в результат поиска уже не попадет.

Функция поиска в Excel

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

Функция поиска в Excel

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

Функция поиска в Excel

Графа под названием «Область поиска», определяет по каким данным, будет происходить поиск.

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

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

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

Функция поиска в Excel

Более точно настроить строку поиска можно нажав на «Формат».

Функция поиска в Excel

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

Функция поиска в Excel

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

Функция поиска в Excel

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

Функция поиска в Excel

Когда вы произвели необходимую вам настройку, нажмите «ОK».

Функция поиска в Excel

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

В таком случае при поиске вам необходимо выделить слова с обеих сторон звездочкой (*). И при таком введении поисковых слов, вам покажут ячейки, слова которых указаны в любом порядке.

Функция поиска в Excel

После всех произведенных настроек, вы можете вводить данные, которые вы хотите найти и жать «Найти все», или «Найти далее».

Функция поиска в Excel

Метод 3 — Поиск по указанному интервалу ячеек

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

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

Функция поиска в Excel

При помощи горячих клавиш Ctrl+F откроем окно поиска. И дальше мы также вводим поисковые слова, и жмем «Найти все» либо «Найти далее». И поиск будет происходить среди выделенных ячеек.

Функция поиска в Excel

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector