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

Чтобы воспользоваться диалоговым окном Найти и заменить, выделите диапазон ячеек, в котором вы хотите произвести поиск (если будет выделена только одна ячейка, Excel будет искать во всем листе). Перейдите по вкладке Главная в группу Редактирование, выберите Найти и выделить -> Найти (или нажмите сочетание клавиш Ctrl + F).

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

Поиск информации

Введите искомую информацию в поле Найти и укажите требуемые параметры поиска:

• Выберите место поиска в выпадающем меню Искать (на листе, в книге)
• В выпадающем меню Просматривать, укажите Excel вариант просмотра по строкам или по столбцам.
• Задайте Область поиска в формулах, значениях или примечаниях
• С помощью опций Учитывать регистр и Ячейка целиком, вы можете указать, следует ли производить поиск с учетом регистра символов и ищется ли все содержимое ячейки или только фрагмент.

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

Замена информации

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

Щелкните по кнопке Найти далее, чтобы Excel выделил первую совпавшую ячейку, затем Заменить для замены. После щелчка, Excel выделит следующую совпавшую ячейку. Чтобы пропустить замещение, щелкните Найти далее. Для замены всех элементов без проверки,щелкните Заменить все. Если замещениепошлоне по плану, вы можете отменить действие с помощью сочетания клавиш Ctrl + Z.

Поиск форматов

Помимо текста вы можете найти ячейки отформатированные определенным образом. Как вариант, вы также можете заменить форматирование ячейки на другое. Предположим, что необходимо определить все ячейки с жирным текстом и заменить их на жирный и курсив. Перейдите по вкладке Главная в группу Редактирование, выберите Найти и выделить -> Заменить (или нажмите сочетание клавиш Ctrl + H). В появившемся диалоговом окне перейдите во вкладку Заменить и щелкните по кнопке Параметры, чтобы отобразить расширенные опции замены. Щелкните по верхней кнопке Формат.

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

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

Щелкните по кнопке Заменить все, Excel заменит текст с жирным начертанием на жирный курсив.

Хитрости использования Найти и заменить Excel

Замена переноса строки (Alt+Enter) в ячейке

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

Для этого вызываем диалоговое окно Найти и заменить сочетанием клавиш Ctrl+H. В поле Найти вводим Ctrl+J (внешне поле останется пустым), в поле Заменить на вводим Пробел (или любой другой символ).

Щелкаем Заменить все и, о чудо, все наши каретки превратились в пробелы!

Работа с VB проектом (12)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (3)

Как заменить/удалить/найти звездочку?

Наверное кто-то уже сталкивался с подобной ситуацией - на листе в ячейках записаны слова/предложения, в которых имеется знак - * - звездочка. Иногда его нужно либо найти, либо заменить на другой символ, либо просто удалить. Например, строку вида "496*120*45 " надо преобразовать в такой: "496x120x45 " . И вот тут как раз возникает проблема: символ звездочки(как и знак вопроса) является служебным символом - он заменяет собой группу любых символов(а знак вопроса - один любой символ) . И при попытке заменить только звездочку Excel заменят ВСЕ данные в ячейке. Сейчас пока разговор про стандартную замену - (Excel 2003 : Правка -Заменить ; Excel 2007-2010 : вкладка Главная -Найти и выделить -Заменить ) - Ctrl +H . Неужели ничего нельзя сделать? Можно. Разработчики предусмотрели такой вариант. Просто перед служебным символом необходимо поставить другой служебный знак, который сообщает Excel, что следующий за ним символ не выполняет свои служебные функции, а выступает в роли обычного символа. Знак этот называется Тильда . Вот такой - ~ . На обычной клавиатуре этот знак обычно расположен на кнопке вместе с буквой Ё.

Таким образом в поле Найти поискового окошка должен получиться такой текст - " ~* "(в поле Заменить на указывается символ/ы, которым требуется заменить звездочку. Если звездочку надо удалить - поле оставляем пустым). И не стоит забывать про дополнительные параметры замены(они раскрываются при нажатии кнопки "Параметры " поискового окна) . А именно - необходимо поставить/снять галочку у параметра Ячейка целиком . В рассматриваемом случае эту галочку необходимо снять.

Если же надо найти саму Тильду, то тут все также - просто указываете две тильды: ~~

По тому же принципу этот символ используется и в формулах, которые работают с служебными символами (ВПР , СЧЁТЕСЛИ , СУММЕЛИ и т.п.)

Но. Всегда есть но. Некоторые формулы любые символы воспринимают как текст сразу и для них нет такого понятия, как служебный символ. К ним относится функция НАЙТИ . В данной функции для поиска данного символа не надо ставить перед ним еще один, потому как она не воспринимает символы подстановки как таковые. Она абсолютно любой символ принимает как текст и ищет строго то, что указано:
=НАЙТИ("~"; A1)
=НАЙТИ("*"; A1)
=НАЙТИ("?"; A1)
=FIND("~", A1)
=FIND("*", A1)
=FIND("?", A1)

Есть аналогичная ей функция - ПОИСК . В этой функции обязательно применять тот же принцип, что и во всех вышеперечисленных - т.е. явно указывать Excel, что мы ищем спец.символ:
=ПОИСК("~~"; A1)
=ПОИСК("~*"; A1)
=ПОИСК("~?"; A1)
=SEARCH("~~", A1)
=SEARCH("~*", A1)
=SEARCH("~?", A1)

И к слову сказать, символы звездочки и вопр.знака так же используются формулами ВПР (VLOOKUP) , ГПР (HLOOKUP) и ПОИСКПОЗ (MATCH) . При этом самый неприятный момент здесь заключается в том, что звездочка, вопросительный знак или сама тильда могут быть внутри текста в искомом массиве данных:


и как видно в этом случае данные с тильдой не могут быть найдены. Чтобы найти их через ВПР(и прочие схожие функции) необходимо заменить тильду на две. Я бы советовал делать это так(на примере формул из таблицы выше):
=ВПР(ПОДСТАВИТЬ(F2 ;"~";"~~"); $A$2:$C$7 ;3;0)
=VLOOKUP(SUBSTITUTE(F2,"~","~~"),$A$2:$C$7,3,0)

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

почему ВПР так поступает лучше прочитать здесь: Как найти значение в другой таблице или сила ВПР . А в этой статье я лишь приведу формулу, как избежать таких проблем. Так же как и с тильдой используем функцию ПОДСТАВИТЬ (SUBSTITUTE) , но теперь подставляем тильду к звездочке:
=ВПР(ПОДСТАВИТЬ(D2 ;"*";"~*"); $A$2:$A$4 ;1;0)
=VLOOKUP(SUBSTITUTE(D2,"*","~*"),$A$2:$A$4,1,0)

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

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

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

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

Способ 1: использования интерфейса программы

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

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

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

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

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

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

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

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

Как в Excel заменить точку, на запятую?

Выделите и скопируйте данные из ниже приведенной таблицы:

Теперь перейдите на рабочий лист и щелкните правой кнопкой мышки по ячейке A1. Из появившегося контекстного меню выберите опцию «Специальная вставка». В диалоговом окне выберите «Текст в кодировке Unicode» и нажмите ОК.

Как видно Excel распознает числа только в колонке C. Значения в этой колонке выровнены по правой стороне. В других колонках по левому краю. Во всех ячейках формат по умолчанию «Общий», а в ячейках D3, D5, E3, E5 вообще отображается формат «Дата». Мы копировали данные через специальную вставку и все форматы исходной таблицы устранены. Причина только одна – вместо запятой стоит точка. Такой тип данных не подготовлен и его нельзя использовать для вычислений.

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

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

Чтобы заменить точку, на запятую в импортированных данных можно использовать 4 способа:



Способ 1 замена точки на запятую в Excel через Блокнот

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

Программа Блокнот заменила все точки, на запятые. Теперь данные готовы для копирования и вставки на лист.

Это весьма простой, но очень эффективный способ.

Способ 2 временно меняем настройки Excel

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

Для этого нужно открыть «Файл»-«Параметры»-«Дополнительно». В разделе «Параметры правки» следует временно убрать галочку «Использовать системные разделители». А в поле «Разделитель целой и дробной части» следует удалить запятую и ввести точку.

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

Внимание! Данный способ сработает, если сделать все изменения до импорта данных, а не после.

Способ 3 временно меняем системные настройки Windows

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

Открываем «Пуск»-«Панель управления»-«Языки и региональные стандарты». Нажимаем на кнопку «Дополнительно». В появившимся окне изменяем в первом поле «Разделитель целой и дробной части» - вводим нужное нам значение. Дальше ОК и ОК.

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

Способ 4 используем функцию найти и заменить в Excel.

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

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

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

Все точки сменились на запятые. А текст автоматически преобразился в число.

Вместо 4-го и 5-го пункта можно использовать в отдельной колонке формулу с функциями:

Например, выделите диапазон ячеек G2:I5, введите эту формулу и нажмите CTRL+Enter. А потом переместите значения ячеек диапазона G2:I5 в диапазон D2:F5.

Эта формула находит в тексте точку с помощью функции НАЙТИ. Потом вторая функция меняет ее на запятую. А функция ЗНАЧЕН преобразует полученный результат в число.

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

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

Чтобы активировать диалоговое окно, позволяющее находить и менять, например, запятую на точку, предварительно нужно обозначить диапазон ячеек. В выделенном фрагменте будет производиться непосредственный поиск. Юзер должен знать, что в случае выделения одной-единственной ячейки, инструмент программы проверит на совпадение с введённым элементом весь лист. Чтобы воспользоваться особенностями программы Excel и сделать замену (например, поменять запятую на точку), в разделе «Главная» нужно перейти в подкатегорию «Редактирование» и нажать «Найти и выделить». Вызвать диалоговое окно поможет совместное нажатие на клавиши Ctrl и F.

Открытое перед юзером окно позволяет эксплуатировать в работе команды: «Найти» (поиск фрагмента текста) и «Заменить». Перейти в расширенный режим просмотра файла поможет кнопка «Параметры».

Инструменты поиска

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

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

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

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

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

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

Инструменты замены

В программе Excel достаточно просто сделать замену одного символа на другой (поменять запятую на точку), для этого следует открыть раздел замены в категории «Найти и заменить». Посредством поиска в окно ввести запятую (символ или элемент, который впоследствии будет изменён). В разделе «Заменить» ввести точку или букву, имеется ввиду тот текст, который появится на месте изменённого.

Символы поменяются после нажатия на кнопку «Далее». Инструментарий софта покажет пользователю ячейки, в которых необходимо будет произвести замещение. По клику Excel сможет пройти весь документ, поэтапно выделяя каждое совпадение. В случае если в каком-то месте нет нужды менять символы (запятую на точку), нужно пропустить замещение, попросту кликнув на «Далее». Если юзеру не нужно проверять каждое совпадение, можно прибегнуть к помощи кнопки «Заменить всё». Отменить действие поможет