Excel регулярные выражения в поиске

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

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

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

Как узнать, что ячейка Эксель содержит латиницу

Как найти значение в Excel

14 комментариев

Ренат, огромное спасибо за «Ctrl+J » — я так долго искала, как указать знак переноса в «Найти». Не поленилась написать сообщение, чтобы сказать еще раз спасибо ))

Согласен, хороший трюк, сам пользуюсь)

Спасибо огромное. Ctrl+j — это меша круто! !

Как найти и заменить масив данных к примеру из базы в 200 000 вычистить одно

Как найти и заменить массив данных, к примеру из базы в 200 000 вычистить одной операцией 2 000 контактов?

Спасибо большое! Нигде больше не мог найти описание, что это именно Ctrl+J!

Ctrl+J спас меня.

Добрый день! А не подскажете ли, как найти (и выделить) пустой текст в ячейке? Объясню, чего хочу. В одном файле формулой обрабатываются штрих-коды, в текстовом формате, так как код больше 13 символов. При копировании в другой файл на месте отсутствующих штрих-кодов вставляется пустой текст «». О не видим и в ячейке никак не отображается. Для выгрузки в 1С нужно такие ячейки очистить.

Всем привет, такие вопросы по Эксель: 1. ФИО в нашей базе записано в одну стороку, как разбить на три отдельных, не потеряв данные. 2. Телефон в столбце, имеет разные форматы, пример: 375 29 123456, 8029-223456, 375-29-123456. Как привести в один правильный порядок: +37529123456. Спасибо за ответы.

Андрей, нужно выделить столбец с ФИО, нажать вкладку «данные» на ПИ, «текст по столбцам», «фиксированной ширины», несколько раз «далее», готово!!

Здравствуйте, Я смотрел Ваше руководство по вопросу найти определенное слово в Excel. У меня возникла одна задача и есть к Вам вопрос. А можно это слово которое ищешь по тексту в Excel затем, как нашел все варианты, заменить их на такое же самое слово, но только что бы оно было полужирным курсивом выделено по всей таблице. Я пытался так сделать с помощью найти и заменить, но оно заменяет на жирный формать весь текст, а не заданое слово. Что Вы мне посоветуете? Спасибо Вам зарание

У меня нормально всё получается, специально проделала эту операцию ))

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

д.д. как переставитьместами, что бы не перепечатывать, заранее спасибо! 262-о на О-262 и.тд. 263-о 264-о 265-о 266-о 267-о 268-о 269-о 270-о 271-о 271-о-а 272-о 273-о 274-о 275-о 276-о 277-о 278-о 279-о 280-о 281-о 282-о 283-о 284-о 285-о 286-о 287-о 288-о 289-о 290-о 291-о 291-о-а 292/1-о 292-о 293-о 294-о 295-о 296-о 297-о 297/1-о 298-о 298-а-о 299-о 300-о 301-о 302-о 303-о 304-о 305-о 306-о 307-о 308-о 309-о 310-о 311-о 312-о 313-о 314-о 315-о 316-о 317-о 318-о 319-о 320-о 321-о 322-о 323-о 324-о 326-о 325-о 327-о 329-о 330-о 331-о 332-о 333-о 334-о 335-о 336-о 337-о 338-о 339-о 340-о 342-о 343-о 344-о 345-о 347-о 348-о 349-о

Поиск и подстановка по нескольким условиям

СУММЕСЛИМН

Функция СУММЕСЛИМН позволяет суммировать значения по определенным условиям. Условий может быть несколько. В Excel также есть функция СУММЕСЛИ, которая позволяет суммировать по одному критерию. Призываю вас использовать более универсальную формулу.

— Диапазон суммирования — область листа Эксель, из которой мы суммируем данные— Диапазон условия 1 — Диапазон ячеек, которые мы проверяем на соответствие условию— Условие 1 — Условие, которое проверяется на соответствие в Диапазоне 1.Обратите внимание, что диапазонов условий и соответственно условий может быть столько, сколько вам нужно. Для примера выше мы хотим получит выручку, которую принес нам Петров в городе Москва

Формула имеет вид СУММЕСЛИМН(C2:C13;A2:A13;E2;B2:B13;F2), где C2:C13 — диапазон со значениями выручки, которые необходимо просуммировать; А2:А13 — диапазон с фамилиями, которые мы будем проверять; Е2 — ссылка на конкретную фамилию; B2:B13 — ссылка на диапазон с городами; F2 — ссылка на конкретный город

Для примера выше мы хотим получит выручку, которую принес нам Петров в городе Москва. Формула имеет вид СУММЕСЛИМН(C2:C13;A2:A13;E2;B2:B13;F2), где C2:C13 — диапазон со значениями выручки, которые необходимо просуммировать; А2:А13 — диапазон с фамилиями, которые мы будем проверять; Е2 — ссылка на конкретную фамилию; B2:B13 — ссылка на диапазон с городами; F2 — ссылка на конкретный город.

Более подробно о функциях СУММЕСЛИМН и СУММЕСЛИ рассказано в статье «СУММЕСЛИ и СУММЕСЛИМН в Excel».

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

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

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

Функция ПОДСТАВИТЬ при условии подставляет значение

Пример 1. В результате расчетов, произведенных в некотором приложении, были получены некоторые значения, записанные в таблицу Excel. Некоторые величины рассчитать не удалось, и вместо числового представления была сгенерирована ошибка “NaN”. Необходимо заменить все значения “NaN” на число 0 в соответствующих строках.

Для замены и подстановки используем рассматриваемую формулу в качестве массива. Вначале выделим диапазон ячеек C2:C9, затем введем формулу через комбинацию Ctrl+Shift+Enter:

Функция ЧЗНАЧ выполняет преобразование полученных текстовых строк к числовым значениям. Описание аргументов функции ПОДСТАВИТЬ:

• B2:B9 – диапазон ячеек, в которых требуется выполнить замену части строки;
• “NaN” – фрагмент текста, который будет заменен;
• 0 – фрагмент, который будет вставлен на место заменяемого фрагмента.

Для подстановки значений во всех ячейках необходимо нажать Ctrl+Shift+Enter, чтобы функция была выполнена в массиве. Результат вычислений:

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

Excel строковые функции

Чем заменить Word и Excel (мой Office). Аналоги, онлайн-редакторы

Одна из самых основных программ для большинства юзеров (к слову, ради которой почти все и приобретают компы) — это Microsoft Word (текстовый редактор).

С помощью него можно сделать и приготовить всякую текстовую работу: реферат, доклад, курсовую и т.д. Но есть у Microsoft Office один недочет — программка платная, а потому имеется она не на любом компе.

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

Примеры функции ПОДСТАВИТЬ для замены текста в ячейке Excel

Видео: Excel: как изменить цифры на буквы в заголовках столбцов?

Замена текстовых значений

С помощью ленты редактора запросов

На ленте Редактора запросов выберите на вкладке Преобразование Замена значений.

С помощью контекстного меню Редактора запросов:

Щелкните текстовую ячейку правой кнопкой мыши и выберите команду Замена значений.

Чтобы заменить текстовые значения:

В диалоговом окне Замена значений введите значения в поля Значение для поиска и Заменить на.

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

Нажмите кнопку ОК.

30 функций Excel за 30 дней: ЗАМЕНИТЬ (REPLACE)

​ в базе в​​: вопрос решен, но​ столбце-на другую​​ функция заменить()​​ «, на .​​ результат:​ для возврата определенного​REPLACE​new_text​

​REPLACE​ отменить, нажав клавишу​​ форматированием в качестве​​формулы​ слова «год» и​ ли она вам,​ другой столбец Код​ оказании халяво-поддержки для​

​ 1,35 раза!!!​ не до конца.​понимаете?​​Z​​Но при использовании​Пример 2. Рассмотрим еще​ числа знаков от​(ЗАМЕНИТЬ) разрешается вкладывать​(новый_текст) – текст,​

Функция 25: REPLACE (ЗАМЕНИТЬ)

​(ЗАМЕНИТЬ), которая относится​​ ESC.​​ примера. Щелкните стрелку​,​ «город».​ с помощью кнопок​ =ПОДСТАВИТЬ(C1;»0″;»1″;2)​

Как можно использовать функцию REPLACE (ЗАМЕНИТЬ)?

​ того, чтобы ТС​​Z​​результат не достигается,​iba2004​: Вопрос: как вам​

• ​ функций ПОДСТАВИТЬ и​ один небольшой пример.​
• ​ конца строки текста.​ одну в другую,​
• ​ который будет вставлен​​ к категории текстовых.​​Чтобы заменить одно или​ рядом с кнопкой​

Синтаксис REPLACE (ЗАМЕНИТЬ)

​значения​​Вопросительный знак заменяет один​​ внизу страницы. Для​Вот так наверное​

• ​ поскольку повторяющийся номер​​: Ловите!​ задали/требуется и как​ ЗАМЕНИТЬ, выдает ошибку,​
• ​ Предположим у нас​​ То есть, например,​ таким образом в​
• ​ вместо старых символов.​​ Она заменяет определённое​ все совпадения с​
• ​Формат​​или​ любой знак. Например,​ удобства также приводим​

Ловушки REPLACE (ЗАМЕНИТЬ)

​ вернее)) Код =ЗАМЕНИТЬ(A1;3;1;»1″)​​ по освоению азбучных​​ ребята… » (с)​ телефона пропадает, но​Только, видимо, под​ вы сами пытались​ т.к. используются в​ в ячейках табличного​ у нас есть​ исходном тексте можно​​Функция​​ количество символов текстовой​ введенным текстом, нажмите​, выберите пункт​

Пример 1: Меняем код города в телефонном номере

​примечания​​ если ввести​​ ссылку на оригинал​Класс. работает, спасибо​ премудростей XL…​Файл — до​ остается городской номер​ себя малёк придётся​ сделать?!.​ этих функциях​ процессора Excel имеются​ два слова: молоко​ делать множественные замены.​REPLACE​

Пример 2: Заменяем пробел на двоеточие с пробелом

​Выбрать формат из ячейки​.​​г?д​​ (на английском языке).​ большое​​Кто «За!»?..​​ 100 кб -​ телефона и адрес​ заточить!​ps На форуме​Bema​ столбцы с мужскими​ и каток, в​ В этом примере​

Пример 3: Вложенные функции REPLACE (ЗАМЕНИТЬ) для вставки нескольких дефисов

​Заменить​​, а затем щелкните​​Примечание:​, то будут найдены​Функции поиска и замены​Константин ж​ps эксельпользователь! А​ в реальной структуре​ и поэтому не​Удачи!​ есть раздел «Приемы»​: Пример можете показать?​ фамилиями и именами​​ результате мы должны​​ телефонные номера должны​ количество символов, начиная​ информацию и примеры​или​ ячейку с форматированием,​ ​ слова «гад», «гид»​ в Excel используются​

​ и форматах -​ понятно был ли​​эксельпользователь​​ и неплохо работает​

​Сергей​

office-guru.ru>

Метод 2: удаление повторений при помощи “умной таблицы”

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

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

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

В ячейке Excel отображается формула

Устранение проблем с вычислением

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

Способ 1: изменение формата ячеек

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

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

Способ 2: отключение режима «Показать формулы»

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

Способ 3: исправление ошибки в синтаксисе

Формула также может отображаться как текст, если в её синтаксисе были допущены ошибки, например, пропущена или изменена буква. Если вы вводили её вручную, а не через Мастер функций, то такое вполне вероятно. Очень распространенной ошибкой, связанной с отображением выражения, как текста, является наличие пробела перед знаком «=».

В таких случаях нужно внимательно пересмотреть синтаксис тех формул, которые неправильно отображаются, и внести в них соответствующие коррективы.

Способ 4: включение пересчета формулы

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

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

Способ 5: ошибка в формуле

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

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

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

​ формул, выделите их​ аргументом​​ соответствующие значения из​​Есть ещё функция​​ =ЕСЛИ(A6=»%»;1;»нет») Здесь в​​В формуле «ЕСЛИ» нужно​​ A2 больше значения​​ (см. пример ниже).​ A1 и нажмите​

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

Выбор уникальных и повторяющихся значений в Excel

Способ 2: Использование макроса

Как заменить текст в разных местах

Замена текстовых значений

С помощью ленты редактора запросов

На ленте Редактора запросов выберите на вкладке Преобразование Замена значений.

С помощью контекстного меню Редактора запросов:

Щелкните текстовую ячейку правой кнопкой мыши и выберите команду Замена значений.

Чтобы заменить текстовые значения:

В диалоговом окне Замена значений введите значения в поля Значение для поиска и Заменить на.

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

Нажмите кнопку ОК.

Замена формулы на ее результат

​ в поле​Функции поиска и замены​ 125 7415 6372​ не учитывается в​Данный способ подойдет в​ в «Заменить на»​ выделенные объекты, нажимаем​ место таблицы. Вызвать​ «Shift» + «F5».​Формат​ содержит 31/12/2007. Тем​Щелкните ячейку с данными,​ Для удобства также​ значение, выполнив следующие​Преобразовать формулы в значения​ кодировкой, а затем​ нажмите кнопку​

​Область поиска​ в Excel используются​4 4 1​ новом слове, в​ тех случаях, когда​

​ — запятую.​​ на кнопку «Скрыть​ окно функции «Перейти»​2)Сочетание клавиш –​и в разделе​ не менее столбец​ которые требуется изменить,​ приводим ссылку на​ действия.​ можно как для​ этот язык назначен​

​Найти далее​вариант​ для поиска в​ 1 100 3​ качестве нового текста​ нужно произвести замену​Нажмите кнопку «Заменить все».​ все». Чтобы показать​ можно функциональной клавишей​

​ «Ctrl» + «F».​Размер ячейки​

​ только ширину, чтобы​ а затем щелкните​

Замена формул на вычисленные значения

​ оригинал (на английском​Нажмите клавишу F2 для​ отдельных ячеек, так​ языком по умолчанию.​или​формулы​ книге необходимой информации,​ 2 232​​ установили функцию ПРАВСИМВ​

1. ​ все скрытые объекты,​ «F5».​

   ​3)На закладке «Главная»​выберите пункт​ отобразить шесть знаков.​

   ​ в любом месте​ языке) .​

   1. ​ редактирования ячейки.​ и для целого​

   2. ​ В противном случае​​Найти все​​,​​ например определенного числа​​2 1131​​ с параметрами ячейки​​ оставались действенными. Выполняется​​ как в Excel​​ нажимаем на кнопку​

   3. ​Как быстро открыть​​ в разделе «Редактирование»​​Автоподбор размера строки​

   4. ​ В ячейке отображается​​ строки формул.​​Вы можете редактировать содержимого​

2. ​Нажмите клавишу F9, а​​ диапазона за раз.​

   ​ функция ЗАМЕНИТЬБ считает​

3. ​.​​значения​

   ​ или текстовой строки.​

4. ​201101122221​ А3 и взятием​​ он следующим образом:​

   ​ заменить точку на​​ «Показать все».​​ такое окно и​

​ нажимаем на кнопку​.​#​При этом запускается режим​ ячейки непосредственно в​ затем — клавишу​Важно:​ каждый символ за​Примечание:​или​На вкладке​11 12 1422112​ последних двух символов​Нажмите кнопку «Файл».​ запятую. Но возможно,​Чтобы удалить выделенные​ работать в нем,​ «Найти и выделить».​

1. ​Чтобы перестанут работать в​. Чтобы просмотреть все​

2. ​ редактирования и размещение​ ячейку. Можно также​ ВВОД.​

   

   

   

   

   

   

   

   

   

   

​ Убедитесь в том, что​ один.​ Если поле​примечания​Главная​44 4 0​ «ок».​Перейдите в меню «Параметры».​ он вам не​ объекты, нажимаем клавишу​ смотрите в статье​ Если нажмем на​

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

​Заменить на​

Замена части формулы на вычисленное значение

​.​в группе​ 512264423242​Далее нажимаем на кнопку​В появившемся окне перейдите​ подойдет, поэтому переходим​ «Delete».​ «Горячие клавиши Excel».​ стрелку у этой​ одно из указанных​ его текущий формат,​ формул в месте​ введя в строка​ ячейке в значение​ на вычисленные значения​ относятся японский, китайский​недоступно, откройте вкладку​Примечание:​Редактирование​

​11 2 62311117973554​ «ОК» и получаем​ в раздел «Дополнительно».​ ко второму.​

1. ​Другой способ выделения​

2. ​Функция «Выделить группу ячеек…»​

   ​ кнопки, то выйдет​ ниже действий.​ необходимо увеличить ширину​ щелчка.​ формул.​ это значение (1932,322)​ проверен, особенно если​ (упрощенное письмо), китайский​Заменить​ ​нажмите кнопку​3 2 1144​

3. ​ результат:​В меню «Параметры вставки»​Сейчас мы разберемся, как​

4. ​ сразу всех картинок,​- выделяет ячейки​ диалоговое окно со​

   ​Нажмите клавишу ВВОД.​ столбца.​Щелкните ячейку, которая содержит​

​При редактировании содержимого ячейки​

support.office.com>

Базовая информация об ИНДЕКС и ПОИСКПОЗ

Особенности использования функции ПОИСКПОЗ в Excel

Создание условной формулы с результатом, являющимся логическим значением (ИСТИНА или ЛОЖЬ)

• текст – обязательный аргумент, характеризующий текстовую строку, в которой необходимо выполнить замену части текста. Может быть задан как текстовая строка («некоторый текст») или ссылка на ячейку, которая содержит текстовые данные.
• стар_текст – часть текстовой строки, принимаемой в качестве первого аргумента данной функции, которую требуется заменить. Аргумент обязателен для заполнения.
• нов_текст – обязательный для заполнения аргумент, содержащий текстовые данные, которые будут вставлены на место заменяемой части строки.
• – числовое значение, характеризующее номер вхождения старого текста, который требуется заменить на фрагмент нового текста. Возможные варианты записи:

1. Аргумент явно не указан. Функция ПОДСТАВИТЬ определит все части текстовой строки, соответствующие фрагменту текста стар_текст, и выполнит их замену на нов_текст;
2. В качестве аргумента передано числовое значение. Функция ПОДСТАВИТЬ заменит только указанное вхождение. Отсчет начинается слева направо, число 1 соответствует первому вхождению. Например, функция =ПОДСТАВИТЬ(«текст №1, №2, №3»;«текст»;«новый»;1) вернет значение «новый_текст №1, №2, №3».

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

Как Формуле Присвоить Значение в Excel… Коротко о синтаксисе | Все о программе Word
​ копировании, нужно в​ ячейку в формуле,​ не будет расширять​ не менее, он​ способов лист, жмем​«Ширина столбца…»​.​ из ячеек (для​ этом появляется крестик​ помоему это не​ ячейка была постоянной​Этот знак можно​ в которой формула.​ ссылка на одну​ Нажимаем на кнопку​ ведома не изменяли​

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

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

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

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

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

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

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

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

Как сделать массовую замену формул

Скопированные формулы повторяют имеющиеся, но ссылаются не на те ячейки, которые нам нужны. Это происходит из-за того, что в одном листе (Форма2) для расчета показателя за год используется всего одна ячейка, а в другом листе (Форма1) — 5 ячеек.

Т.е. в части ссылок на лист «Форма2» ссылки при копировании автоматически идут на нужные ячейки, а в листе «Форма1» нам придется передвинуть формулы на 4 ячейки (причем речь идет о буквенных обозначениях, цифровые меняться не будут).

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

В нашем случае, второй столбик после копирования формулы ссылается в листе «Форма1» на ячейки J, K, L, M, N, в то время как для правильного результата должны ссылаться на M, N, O, P, Q. Как видим, две буквы повторяются. Их мы оставим. Значит, нам останется поменять J, K, L на O, P, Q.

А дальше все проще простого. Выделяем нужный столбец по серому полю (обязательно, иначе мы, не желая того, можем поменять значения других ячеек), вызываем функцию «поиска и замены» и меняем J на O, K на P, L на Q и т.д.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ctrl+HCtrl+J

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

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

Важно понимать, что если вы ничего не нашли, это еще не значит, что там этого нет

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

Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)

Замена одного текста на другой внутри заданной текстовой строки — весьма частая ситуация при работе с данными в Excel. Реализовать подобное можно двумя функциями: ПОДСТАВИТЬ (SUBSTITUTE) и ЗАМЕНИТЬ (REPLACE) . Эти функции во многом похожи, но имеют и несколько принципиальных отличий и плюсов-минусов в разных ситуациях. Давайте подробно и на примерах разберем сначала первую из них.

Её синтаксис таков:

=ПОДСТАВИТЬ( Ячейка ; Старый_текст ; Новый_текст ; Номер_вхождения )

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

Обратите внимание, что:

• Если не указывать последний аргумент Номер_вхождения, то будут заменены все вхождения старого текста (в ячейке С1 — обе «Маши» заменены на «Олю»).
• Если нужно заменить только определенное вхождение, то его номер задается в последнем аргументе (в ячейке С2 только вторая «Маша» заменена на «Олю»).
• Эта функция различает строчные и прописные буквы (в ячейке С3 замена не сработала, т.к. «маша» написана с маленькой буквы)

Давайте разберем пару примеров использования функции ПОДСТАВИТЬ для наглядности.

Замена или удаление неразрывных пробелов

При выгрузке данных из 1С, копировании информации с вебстраниц или из документов Word часто приходится иметь дело с неразрывным пробелом — спецсимволом, неотличимым от обычного пробела, но с другим внутренним кодом (160 вместо 32). Его не получается удалить стандартными средствами — заменой через диалоговое окно Ctrl + H или функцией удаления лишних пробелов СЖПРОБЕЛЫ (TRIM) . Поможет наша функция ПОДСТАВИТЬ, которой можно заменить неразрывный пробел на обычный или на пустую текстовую строку, т.е. удалить:

Подсчет количества слов в ячейке

Если нужно подсчитать количество слов в ячейке, то можно применить простую идею: слов на единицу больше, чем пробелов (при условии, что нет лишних пробелов). Соответственно, формула для расчета будет простой:

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

Извлечение первых двух слов

Если нужно вытащить из ячейки только первые два слова (например ФИ из ФИО), то можно применить формулу:

У нее простая логика:

1. заменяем второй пробел на какой-нибудь необычный символ (например #) функцией ПОДСТАВИТЬ (SUBSTITUTE)
2. ищем позицию символа # функцией НАЙТИ (FIND)
3. вырезаем все символы от начала строки до позиции # функцией ЛЕВСИМВ (LEFT)

голоса

Рейтинг статьи

5 вариантов использования функции ИНДЕКС (INDEX)

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

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

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

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;”1″;””);”2″;””);”3″;””);”4″;””);”5″;””);”6″;””);”7″;””);”8″;””);”9″;””);”0″;””)

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

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

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

=regexreplace(A1;”d”;”#”)=regexreplace(A1;”w”;”#”)=regexreplace(A1;”а-яА-Я”;”#”)=regexreplace(A1;”s”;”_”)

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

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

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

Поиск ТЕКСТовых значений в Excel. Бесплатные примеры и статьи.

Пример суммирования с использованием функции СУММЕСЛИ