Функция в excel: поиск решения

4) Способ поиска номер четыре — это макрос VBA для поиска (перебора значений).

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

Sub Poisk()

‘ ruexcel.ru макрос проверки значений (поиска)

Dim keyword As String

keyword = «Искомое слово» ‘присвоить переменной искомое слово

On Error Resume Next ‘при ошибке пропустить

For Each cell In Selection ‘для всх ячеек в выделении (выделенном диапазоне)

If cell.Value = «» Then GoTo Line1 ‘если ячейка пустая перейти на «Line1″

If InStr(StrConv(cell.Value, vbLowerCase), keyword) > 0 Then cell.Interior.Color = vbRed ‘если в ячейке содержится слово окрасить ее в красный цвет (поиск)

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

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

Чтобы наглядно отобразить пример данного урока нам потребуется лист содержащий формулы:

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

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

Так же можно использовать комбинацию горячих клавиш CTRL+` (над клавишей Tab). Данная комбинация работает в режиме переключателя, то есть повторное нажатие возвращает в обычный режим отображения результатов вычисления в ячейках.

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

Горячие клавиши Excel способствуют более эффективной работе с программой.

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

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

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

Как извлечь дубликаты из диапазона.

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

Рассмотрим это на примере числовой матрицы. К сожалению, с символьными значениями этот метод не работает.

При помощи формулы массива

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

Затем протащите маркер заполнения вниз на сколько это необходимо.

Чтобы убрать сообщения об ошибке, когда дублирующиеся значения закончатся, можно использовать функцию ЕСЛИОШИБКА:

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

Поэтому если вам нужно разместить его, к примеру, в ячейке K4, то выражение СТРОКА()-1 в конце замените на СТРОКА()-3.

Параметры поиска

Можете задать свои условия. Например, запустить поиск по нескольким знакам. Вот как в Экселе найти слово, которое вы не помните целиком:

1. Введите только часть надписи. Можно хоть одну букву — будут выделены все места, в которых она есть.
2. Используйте символы * (звёздочка) и ? (вопросительный знак). Они замещают пропущенные знаки.
3. Вопрос обозначает одну отсутствующую позицию. Если вы напишите, к примеру, «П???», отобразятся ячейки, в которых есть слово из четырёх символов, начинающееся на «П»: «Плуг», «Поле», «Пара» и так далее.
4. Звезда (*) замещает любое количество знаков. Чтобы отыскать все значения, в которых содержится корень «раст», начните поиск по ключу «*раст*».

Также вы можете зайти в настройки:

1. В окне «Найти» нажмите «Параметры».
2. В разделах «Просматривать» и «Область поиска», укажите, где и по каким критериям надо искать совпадения. Можно выбрать формулы, примечания или значения.
3. Чтобы система различала строчные и прописные буквы, поставьте галочку в «Учитывать регистр».
4. Если вы отметите пункт «Ячейка целиком», в результатах появятся клетки, в которых есть только заданная поисковая фраза и ничего больше.

Параметры формата ячеек

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

1. В окне поиска нажмите «Параметры» и кликните на кнопку «Формат». Откроется меню с несколькими вкладками.
2. Можете указать определённый шрифт, вид рамки, цвет фона, формат данных. Система будет просматривать места, которые подходят к заданным критериям.
3. Чтобы взять информацию из текущей клетки (выделенной в этот момент), нажмите «Использовать формат этой ячейки». Тогда программа отыщет все значения, у которых тот же размер и вид символов, тот же цвет, те же границы и тому подобное.

Поиск нескольких слов

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

Чтобы в Экселе найти не одно слово, а сразу несколько, сделайте следующее:

1. Напишите их в строке поиска.
2. Поставьте между ними звёздочки. Получится «*Текст* *Текст2* *Текст3*». Так отыщутся все значения, содержащие указанные надписи. Вне зависимости от того, есть ли между ними какие-то символы или нет.
3. Этим способом можно задать ключ даже с отдельными буквами.

Параметры поиска

Можете задать свои условия. Например, запустить поиск по нескольким знакам. Вот как в Экселе найти слово, которое вы не помните целиком:

1. Введите только часть надписи. Можно хоть одну букву — будут выделены все места, в которых она есть.
2. Используйте символы * (звёздочка) и ? (вопросительный знак). Они замещают пропущенные знаки.
3. Вопрос обозначает одну отсутствующую позицию. Если вы напишите, к примеру, «П. », отобразятся ячейки, в которых есть слово из четырёх символов, начинающееся на «П»: «Плуг», «Поле», «Пара» и так далее.
4. Звезда (*) замещает любое количество знаков. Чтобы отыскать все значения, в которых содержится корень «раст», начните поиск по ключу «*раст*».

Также вы можете зайти в настройки:

1. В окне «Найти» нажмите «Параметры».
2. В разделах «Просматривать» и «Область поиска», укажите, где и по каким критериям надо искать совпадения. Можно выбрать формулы, примечания или значения.
3. Чтобы система различала строчные и прописные буквы, поставьте галочку в «Учитывать регистр».
4. Если вы отметите пункт «Ячейка целиком», в результатах появятся клетки, в которых есть только заданная поисковая фраза и ничего больше.

Параметры формата ячеек

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

1. В окне поиска нажмите «Параметры» и кликните на кнопку «Формат». Откроется меню с несколькими вкладками.
2. Можете указать определённый шрифт, вид рамки, цвет фона, формат данных. Система будет просматривать места, которые подходят к заданным критериям.
3. Чтобы взять информацию из текущей клетки (выделенной в этот момент), нажмите «Использовать формат этой ячейки». Тогда программа отыщет все значения, у которых тот же размер и вид символов, тот же цвет, те же границы и тому подобное.

Поиск нескольких слов

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

Чтобы в Экселе найти не одно слово, а сразу несколько, сделайте следующее:

1. Напишите их в строке поиска.
2. Поставьте между ними звёздочки. Получится «*Текст* *Текст2* *Текст3*». Так отыщутся все значения, содержащие указанные надписи. Вне зависимости от того, есть ли между ними какие-то символы или нет.
3. Этим способом можно задать ключ даже с отдельными буквами.

Вот как искать в Экселе, используя фильтр:

1. Выделите какую-нибудь заполненную ячейку.
2. Нажмите Главная — Сортировка — Фильтр.
3. В верхней строке у клеток появятся стрелочки. Это выпадающее меню. Откройте его.
4. В текстовом поле введите запрос и нажмите «OK».
5. В столбце будут отображаться только ячейки, содержащие искомую фразу.
6. Чтобы сбросить результаты, в выпадающем списке отметьте «Выделить всё».
7. Чтобы отключить фильтр, повторно нажмите на него в сортировке.

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

Чтобы найти в Excel какую-то фразу или число используйте встроенные возможности интерфейса. Можно выбрать дополнительные параметры поиска и включить фильтр.

Пакетный поиск и замена определенного текста в гиперссылках в Excel

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

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

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

3. Нажмите Формат в строке Найти, чтобы включить Найти формат Диалог.

4. Затем под шрифт , выберите Один вариант из выпадающего списка подчеркивание в Найти формат Диалог.

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

6. Нажмите Заменить все, появится диалоговое окно с напоминанием о том, сколько ячеек выполнено. Нажмите OK закрыть, а затем закрыть Найти и заменить Диалог.

Если вы хотите заменить содержимое ячейки адресами гиперссылок, вы можете выбрать Содержимое ячейки заменяет адреса гиперссылок вариант в Конвертировать гиперссылки полезности Kutools for Excel, если вы хотите извлечь все гиперссылки из диапазона ячеек, выберите Адреса гиперссылок заменяют содержимое ячейки опцию.      Нажмите, чтобы получить 30-дневную бесплатную пробную версию!

Другие операции (статьи), связанные с гиперссылками

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

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

Запретить / отключить автоматические гиперссылки в ExcelКак все мы знаем, Excel автоматически создает интерактивные гиперссылки, когда мы вводим веб-адреса в ячейки. Может быть, иногда это немного раздражает. Сегодня я познакомлю вас с некоторыми быстрыми приемами, позволяющими предотвратить автоматические гиперссылки в Excel.

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

Пример использования функции ПОИСК и ПСТР

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

Введем исходные данные в таблицу:

В ячейке, которая будет учитывать данные клиентов без телефона, введем следующую формулу:

Нажмем Enter для отображения искомой информации:

Далее мы можем использовать любые другие функции для отображения представленной информации в удобном формате:

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

Использование

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

Перенесем эти сведения на рабочий лист excel.

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

1. 1. Процентная ставка.
2. 2. Период (кпер).
3. 3. Сумма платежа (плт).

Для первого года формула будет выглядеть следующим образом:

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

Воспользуемся автозаполнением и получим сумму средств после 5 лет нахождения на депозите под 4 процента годовых с ежегодным пополнением.

Полученная цифра не удовлетворяет условию в 12000 после пятилетнего периода. Теперь необходимо воспользоваться инструментом Поиск решения. При этом изменяемыми параметрами будет процент и первоначальная сумма. Заполняете диалоговое окно построчно.

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

Как видите, изменилась только процентная ставка, хотя изменяемыми величинами были два параметра. Чтобы это исправить, в настройках необходимо поставить галочки напротив строчки Автоматическое масштабирование.

Повторяете решение с новой конфигурацией и получаете следующие данные: Как видите, чтобы достигнуть отметки в 12000$ через пять лет, необходимо найти депозит под 4,03 процента годовых и ежегодно пополнять его на сумму 2214 доллара 01 цент.

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

Замечание

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

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

Если значение аргумента искомый_текст не найдено, #VALUE! возвращено значение ошибки.

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

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

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

Поиск значений в таблице Excel

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

Лист с таблицей для поиска значений по вертикали и горизонтали:

Над самой таблицей расположена строка с результатами. В ячейку B1 водим критерий для поискового запроса, то есть заголовок столбца или название строки. А в ячейке D1 формула поиска должна возвращать результат вычисления соответствующего значения. После чего в ячейке F1 сработает вторая формула, которая уже будет использовать значения ячеек B1 и D1 в качестве критериев для поиска соответствующего месяца.

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

​ на Вашу помощь)​​ Эксперт ООО; Бартош​Но возникла проблема.​ в ячейку B1​ м2 краски на​1. если найдено​ заморочил голову.​ с конкретным примером.​​ Range) As String​​ с «Партийным номером»​Nic70y​ вытаскивать данные представлен​​ мы можем экспонировать​

​Чтобы на основе идентификатора​​ оригинал (на английском​ из текстовых строк​ В условиях поиска можно​Julia8​

​ Е.В. = Бартош​​ Я делаю вытаскивание​

​Пример строки​​ одну тонну разных​ «ябло» или «груш»​Большая часть функций​​ В красной ячейке​Dim r As​ первой, перенести количество​​: А теперь?​

​ как 44А, а​​ цветом определенную группу​ выделить изделия 2006-го​​ языке) .​ (т.е. в ячейке​ использовать подстановочные знаки.​​, уж очень абстрактное​ Евгений Владимирович ИП.​ городов из адресов​movie 0:00:35.00 Z:\01-Анонс​ элементов:​ в столбце А,​​ (по крайне мере​​ должно отобразиться название​ Range​

Как правильно искать в таблицах Excel

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

Выполните команду Главная ► Редактирование ► Найти и выделить ► Найти (или нажмите Ctrl+F), чтобы открыть диалоговое окно Найти и заменить. Если вам нужно заменить данные, то выберите команду Главная ► Редактирование ► Найти и выделить ► Заменить (или нажмите Ctrl+H). От того, какую именно команду вы выполните, зависит, на какой из двух вкладок откроется диалоговое окно.

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

Рис. 21.1. Вкладка Найти диалогового окна Найти и заменить

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

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

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

• ? — соответствует любому символу;
• * — соответствует любому количеству символов.

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

Для поиска вопросительного знака или звездочки поставьте перед ними символ тильды (

). Например, следующая строка поиска находит текст *NONE*: -*N0NE

* Чтобы найти символ тильды, поставьте в строке поиска две тильды.

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

• Флажок Учитывать регистр — установите его, чтобы регистр искомого текста совпадал с регистром заданного текста. Например, если вы зададите в поиске слово иван и установите указанный флажок, то слово Иван в результатах поиска не отобразится.
• Флажок Ячейка целиком — установите его, чтобы найти ячейку, которая содержит в точности тот текст, который указан в строке поиска. Например, набрав в строке поиска слово Excel и установив указанный флажок, вы не найдете ячейку, содержащую словосочетание Microsoft Excel.
• Раскрывающийся список Область поиска — список содержит три пункта: значения, формулы и примечания. Например, если в строке поиска вы зададите число 900 и в раскрывающемся списке Область поиска выберете пункт значения, то в результатах поиска вы не увидите ячейку, содержащую значение 900, если оно получено при использовании формулы.

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

Кроме того, учтите, что с помощью окна Найти и заменить нельзя найти отформатированные числовые значения. Например, если в строку поиска вы введете $5*, то значение, к которому применено денежное форматирование и которое выглядит как $54.00, не будет найдено.

Работа с датами может оказаться непростой, поскольку Excel поддерживает очень много форматов дат. Если вы ищете дату, к которой применено форматирование по умолчанию, Excel находит даты, даже если они отформатированы различными способами. Например, если ваша система использует формат даты m/d/y, строка поиска 10/*/2010 находит все даты в октябре 2010 года, независимо от того, как они отформатированы.

Используйте пустое поле Заменить на, чтобы быстро удалить какую-нибудь информацию на рабочем листе. Например, введите — * в поле Найти и оставьте поле Заменить на пустым. Затем нажмите кнопку Заменить все, чтобы Excel нашел и убрал все звездочки на листе.

Дополнительные параметры поиска слов и фраз

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

Здесь можно указать дополнительные параметры поиска.

Искать:

• на листе — только на текущем листе;
• в книге — искать во всем документе Excel, если он состоит из нескольких листов.

Просматривать:

• по строкам — искомая фраза будет искаться слева направо от одной строки к другой;
• по столбцам — искомая фраза будет искаться сверху вниз от одного столбца к другому.

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

Область поиска — определяет, где именно нужно искать совпадения:

• в формулах;
• в значениях ячеек (уже вычисленные по формулам значения);
• в примечаниях, оставленных пользователями к ячейкам.

А также дополнительные параметры:

Учитывать регистр — означает, что заглавные и маленькие буквы будут считаться как разные.

Например, если не учитывать регистр, то по запросу «excel» будет найдены все вариации этого слова, например, Excel, EXCEL, ExCeL и т.д.

Если поставить галочку учитывать регистр, то по запросу «excel» будет найдено только такое написание слова и не будет найдено слово «Excel».

• Ячейка целиком — галочку нужно ставить в том случае, если нужно найти те ячейки, в которых искомая фраза находится целиком и нет других символов. Например, есть таблица со множеством ячеек, содержащих различные числа. Поисковый запрос: «200». Если не ставить галочку ячейка целиком, то будут найдены все числа, содержащие 200, например: 2000, 1200, 11200 и т.д. Чтобы найти ячейки только с «200», нужно поставить галочку ячейка целиком. Тогда будут показаны только те, где точное совпадение с «200».
• Формат… — если задать формат, то будут найдены только те ячейки, в которых есть искомый набор символов и ячейки имеют заданный формат (границы ячейки, выравнивание в ячейке и т.д.). Например, можно найти все желтые ячейки, содержащие искомые символы.

Формат для поиска можно задать самому, а можно выбрать из ячейки-образца — Выбрать формат из ячейки…

Чтобы сбросить настройки формата для поиска нужно нажать Очистить формат поиска.

Это меню вызывается, если нажать на стрелочку в правой части кнопки Формат.

Поиск

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

1. Первый блок используется для записи искомой информации.
2. Вторая часть функции позволяет задать диапазон поиска по части текста.
3. Третий аргумент является необязательным. Его использование оправдано, если известна точка начала поиска внутри ячейки.

Рассмотрим пример: необходимо найти фрукты, которые начинаются на букву А из списка.

1. Составляете список на рабочем листе

1. В соседнем столбце записываете =ПОИСК(«а»;$B$4:$B$11). Не забывайте ставить двойные кавычки при использовании текста в качестве аргумента.

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

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

Позиции с единицами показывают, какие из строк содержат фрукты, начинающиеся на букву а. Как видите, остальные цифры также указывают на местоположение искомой буквы в остальных позициях диапазона. Однако, одна ячейка содержит ошибку ЗНАЧ!. Эта проблема возникает в двух случаях, при использовании функции ПОИСК:

1. Нулевая ячейка
2. Блок не содержит искомой информации.

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

Фильтр

Вот как искать в Экселе, используя фильтр:

1. Выделите какую-нибудь заполненную ячейку.
2. Нажмите Главная — Сортировка — Фильтр.
3. В верхней строке у клеток появятся стрелочки. Это выпадающее меню. Откройте его.
4. В текстовом поле введите запрос и нажмите «OK».
5. В столбце будут отображаться только ячейки, содержащие искомую фразу.
6. Чтобы сбросить результаты, в выпадающем списке отметьте «Выделить всё».
7. Чтобы отключить фильтр, повторно нажмите на него в сортировке.

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

Чтобы найти в Excel какую-то фразу или число используйте встроенные возможности интерфейса. Можно выбрать дополнительные параметры поиска и включить фильтр.

Надстройка для поиска на всех листах книги Excel

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

При запуске надстройка формирует панель инструментов, с которой осуществляется запуск всех макросов:

В Excel 2007 и 2010 панель инструментов можно найти на вкладке «Надстройки»:

Надстройка SearchText является расширенной версией надстройки для поиска на текущем листе.

Назначение кнопок надстройки:

— поле для ввода искомого текста. Нажатие клавиши Enter запускает поиск.

— удаление листа с результатами поиска. (выполняется автоматически при повторном поиске)

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

или — переключатель режимов поиска (щелкните на этой кнопке для переключения режима поиска)

При запуске поиска (вводе числа в текстовое поле, и нажатии Enter) макрос создаёт в текущем файле дополнительный лист (с названием «Результаты поиска»), и копирует на этот лист все строки, в которых содержится заданный текст.

Выполняется поиск по частичному совпадению текста (т.е., при поиске текста 1234 будут найдены ячейки, содержащие числа 12345, 5612348 и т.д.)

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

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

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

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

Для выполнения этой задачи используйте функции СМЕЩ и ПОИСКПОЗ.

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

C1 — это верхняя левая ячейка диапазона (также называемая начальной ячейкой).

Match (“апельсины”; C2: C7; 0) ищет оранжевый цвет в диапазоне C2: C7. Не следует включать начальную ячейку в диапазон.

1 — количество столбцов справа от начальной ячейки, для которых должно быть возвращено возвращаемое значение. В нашем примере возвращаемое значение находится в столбце D, Sales.