Сегодня мы рассмотрим функцию Excel – НАЙТИ (в английской версии FIND ), которая позволяет находить позицию искомого текста в тексте относительного первого символа. Обычно, но не всегда функцию НАЙТИ используют совместно с другими функциями Excel, что мы и рассмотрим в следующих статьях, а сегодня попробуем разобраться с принцип работы данной функции.

Допустим у нас есть таблица со списком имен и фамилий.

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

Синтаксис функции выглядит следующим образом

НАЙТИ(искомый_текст ; просматриваемый_текст ; [нач_позиция ]), где

искомый_текст – это текст, который нам необходимо найти в тексте, в нашем случае это пробел, он обозначается вот так “ “.

просматриваемый_текст – это текст в котором ищется искомый текст, в нашем случае это имя и фамилия или ячейка A2

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

Прописываем формулу =НАЙТИ(" " ;A2 ;1 ), протягиваем ее вниз и получаем нужный нам результат.

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

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

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

Функция ПОИСК работает не только для поиска позиции отдельных букв в тексте, но и для целой комбинации. Например, задав данную команду для слов «book», «notebook», мы получим значение 5, так как именно с этого по счету символа начинается искомое слово «book».

Используют функцию ПОИСК наряду с такими, как:

• НАЙТИ (осуществляет поиск с учетом регистра);
• ПСТР (возвращает текст);
• ЗАМЕНИТЬ (заменяет символы).

Важно помнить, что рассматриваемая команда ПОИСК не учитывает регистра. Если мы с помощью нее станем искать положение буквы «а» в слове «Александр», в ячейке появится выражение 1, так как это первый символ в анализируемой информации. При задании команды НАЙТИ «а» в том же отрезке текста, мы получим значение 6, так как именно 6 позицию занимает строчная «а» в слове «Александр».

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

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

ПОИСК(нужный_текст;анализируемый_текст;[начальная_позиция]).

В этой формуле задаваемые значения определяются следующим образом.

1. Искомый текст. Это числовая и буквенная комбинация, позицию которой требуется найти.
2. Анализируемый текст. Это тот фрагмент текстовой информации, из которого требуется вычленить искомую букву или сочетание и вернуть позицию.
3. Начальная позиция. Данный фрагмент необязателен для ввода. Но, если вы желаете найти, к примеру, букву «а» в строке со значением «А015487.Мужская одежда», то необходимо указать в конце формулы 8, чтобы анализ этого фрагмента проводился с восьмой позиции, то есть после артикула. Если этот аргумент не указан, то он по умолчанию считается равным 1. При указании начальной позиции положение искомого фрагмента все равно будет считаться с первого символа, даже если начальные 8 были пропущены в анализе. То есть в рассматриваемом примере букве «а» в строке «А015487.Мужская одежда» будет присвоено значение 14.

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

1. Вопросительный знак (?). Он будет соответствовать любому знаку.
2. Звездочка (*). Этот символ будет соответствовать любой комбинации знаков.

Если же требуется найти подобные символы в строке, то в аргументе «искомый_текст» перед ними нужно поставить тильду (~).

Если искомый текст не был найден приложением или начальная позиция установлена меньше 0, больше общего количества присутствующих символов, в ячейке отобразиться ошибка #ЗНАЧ.

Если «искомый_текст» не найден, возвращается значение ошибки #ЗНАЧ.



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

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

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

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

ПОИСК(“, тел.”;адрес_анализируемой_ячейки).

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

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

Пример формулы ПОИСК и ЗАМЕНИТЬ

Пример 2. Есть таблица с текстовой информацией, в которой слово «маржа» нужно заменить на «объем».

Откроем книгу Excel с обрабатываемыми данными. Пропишем формулу для поиска нужного слова «маржа»:

Теперь дополним формулу функцией ЗАМЕНИТЬ:

Чем отличается функция ПОИСК от функции НАЙТИ в Excel?

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

Но опытный пользователь Excel знает, что отличие у этих двух функций очень существенные.

Отличие №1. Чувствительность к верхнему и нижнему регистру (большие и маленькие буквы). Функция НАЙТИ чувствительна к регистру символов. Например, есть список номенклатурных единиц с артикулом. Необходимо найти позицию маленькой буквы «о».

Теперь смотрите как ведут себя по-разному эти две функции при поиске большой буквы «О» в критериях поиска:

Отличие №2. В первом аргументе «Искомый_текст» для функции ПОИСК мы можем использовать символы подстановки для указания не точного, а приблизительного значения, которое должно содержаться в исходной текстовой строке. Вторая функция НАЙТИ не умеет использовать в работе символы подстановки масок текста: «*»; «?»; «~».

Для примера попробуем в этих же исходных строках столбца «наименования» найти приблизительный текст. Для этого укажем следующий вид критерия поиска используя символы подстановки: «н*ая».

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

Функция ПОИСК (SEARCH) в Excel используется для определения расположения текста внутри какого-либо текста и указания его точной позиции.

Что возвращает функция

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

Синтаксис

=SEARCH(find_text, within_text, ) – английская версия

=ПОИСК(искомый_текст;просматриваемый_текст;[начальная_позиция]) – русская версия

Аргументы функции

• find_text (искомый_текст) – текст или текстовая строка которую вы хотите найти;
• within_text (просматриваемый_текст) – текст, внутри которого вы осуществляете поиск;
• ([начальная_позиция]) – числовое значение, обозначающее позицию, с которой вы хотите начать поиск. Если не указать этот аргумент, то функци начнет поиск с начала текста.

Дополнительная информация

• Если стартовая позиция поиска не указана, то поиск текста осуществляется сначала текста;
• Функция не чувствительна к регистру. Если вам нужна чувствительность к регистру, то используйте функцию ;
• Функцияможет обрабатывать подстановочные знаки. В Excel существует три подстановочных знака – ?, *, ~.
  • знак “?” – сопоставляет любой одиночный символ;
  • знак “*” – сопоставляет любые дополнительные символы;
  • знак “~” – используется, если нужно найти сам вопросительный знак или звездочку.
• Функция возвращает ошибку, в случае если искомый текст не найден.

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

Пример 1. Ищем слово внутри текстовой строки (с начала)

На примере выше видно, что когда мы ищем слово “доброе” в тексте “Доброе утро”, функция возвращает значение “1”, что соответствует позиции слова “доброе” в тексте “Доброе утро”.

Так как функция не чувствительна к регистру, нет разницы каким образом мы указываем искомое слово “доброе”, будь то “ДОБРОЕ”, “Доброе”, “дОброе” и.т.д. функция вернет одно и то же значение.

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

Пример 2. Ищем слово внутри текстовой строки (с указанием стартовой позиции поиска)

Третий аргумент функцииуказывает на порядковый номер позиции внутри текста, с которой будет осуществлен поиск. На примере выше, функция возвращает значение “1” при поиске слова “доброе” в тексте “Доброе утро”, начиная свой поиск с первой позиции.

Вместе с тем, если мы указываем функции, что поиск следует начинать со второго символа текста “Доброе утро”, то есть функция в этом случае видит текст как “оброе утро” и ищет слово “доброе”, то результатом будет ошибка.

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

Пример 3. Поиск слова при наличии нескольких совпадений в тексте

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

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

Пример 4. Используем подстановочные знаки при работе функции ПОИСК в Excel

При поиске функция учитывает подстановочные знаки. На примере выше мы ищем текст “c*l”. Наличие подстановочного знака “*” в данном запросе обозначает что мы ищем любо слово, которое начинается с буквы “c” и заканчивается буквой “l”, а что между этими двумя буквами не важно. Как результат, функция возвращает значение “3”, так как в слове “Excel”, расположенном в ячейке А2 буква “c” находится на третьей позиции.

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

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

Что такое Поиск решений

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

Где в Excel поиск решений

Надстройка Поиск решений поставляется вместе с Excel, но по умолчанию отключена. Чтобы включить его, перейдите по вкладке Файл в группу Параметры. В появившемся диалоговом окне Параметры, выберите Надстройки -> Управление: Надстройки Excel -> Перейти. В окне Надстройки устанавливаем галочку напротив поля Поиск решения, жмем ОК.

Теперь во вкладке Данные появилась новая группа Анализ с кнопкой Поиск решения.

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

Данный пост основан на примере использования . Файл совместим со всеми версиями Excel.

Определение проблемы

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

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

Для начала требуется определить каждый пункт к какой-нибудь группе.

Чтобы указать привязанность пункта к группе, будем помечать их единицей (1), в противном случае нулем (0).

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

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

Наконец, нам необходимо свести сумму групп и работать с разницей между ними.

Наша задача минимизировать разницу между суммами групп.

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

Проблема в том, что количество возможных комбинаций 2 8 , т.е. 256 вероятных ответов на вопрос. Если на каждый из них тратить по 5 секунд, это займет у нас 21,3 минуты, предполагая, что мы сможем выдержать темп и запомнить лучшую комбинацию.

Вот где Поиск решения находит применение.

Поиск оптимального решения в Excel

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

Наши правила

Наше основное требование – это минимизировать разницу между двумя группами. В нашем примере она находится в ячейке G11 – Группа B минус Группа A. Нам нужно, чтобы значение в ячейке G11 было настолько малым насколько это возможно, но больше или равно 0.

Мы также знаем, что пункт может находиться либо в Группе A, либо в Группе B, к тому он не может быть дробным. Таким образом у нас два ограничения для каждого элемента:

Во-первых: Значение элемента в колонке Итог должна равняться единице.

Во-вторых: Значения элементов в группах должны быть целыми.

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

Диалоговое окно Поиска решения

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

Пустое окно Поиска решения

Заполненное окно Поиска решения

Оптимизировать целевую функцию

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

До

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

Мы хотим, чтобы суммы обоих групп совпадали, т.е. чтобы разница сумм была равна 0. Это может показаться странным, но нам не требуется минимизировать разницу, потому что при этом все элементы будут помещены в Группу A, что приведет к значению ячейки G11 меньше нуля.

Другой способ наложения ограничения – изменить G11 на =ABS(G10-F10). При этом мы сможем установить маркер на Минимум, как результат достижения целевой функции.

Но пока мы остановимся на формуле =G10-F10 и установим маркер в значение равным 0.

Изменяя ячейки переменных

Изменяемые ячейки – ячейки, которые надстройка попытается изменить, чтобы решить задачу. В нашем случае это привязка элемента к конкретной группе: $ C$2:$ D$9.

В соответствии с ограничениями

Ограничения – это правила, которые лимитируют возможные решения проблемы.

Нам необходимо добавить несколько ограничений в наш список:

1. В колонке Итого каждый элемент должен равняться 1
2. Элементы групп должны быть целым числом
3. Сумма значений столбца Итого должна равняться 8

Чтобы наложить ограничения, жмем кнопку Добавить

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

Загрузить/сохранить параметры поиска решений

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

Запуск поиска оптимального решения в Excel

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

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

Два параметра, которые необходимо будет менять время от времени:

Точность ограничения: значение от 0 до 1, где, чем больше цифра, тем больше ограничение

Целочисленная оптимальность: показывает насколько далеко от целого числа ограничение имеет право быть.

Запуск модели

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

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

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

Теперь у вас есть 4 варианта на выбор:

— Восстановить исходные значения

Запустить отчет

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

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

Сохранить сценарий

Где необходимо ввести название вашего сценария модели и нажать кнопку ОК.

Все сценарии доступны в Диспетчере сценариев , который находится во вкладке Данные в группе Работа с данными –> Анализ что-если -> Диспетчер сценариев.

Функция ПОИСК() , английский вариант SEARCH(), находит первое вхождение одной текстовой строки в другой строке и возвращает начальную позицию найденной строки.

Синтаксис функции

ПОИСК (искомый_текст ;просматриваемая_строка ;[нач_позиция])

Искомый_текст - текст, который требуется найти.

Просматриваемая_строка - текст, в которой ищется Искомый_текст .

Нач_позиция - позиция знака в просматриваемой_строке, с которой должен начинаться поиск. Если аргумент нач_позиция опущен, то предполагается значение 1.

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

Если искомый_текст не найден, возвращается значение ошибки #ЗНАЧ!

Функция ПОИСК() не учитывает РЕгиСТР букв. Для поиска с учетом регистра следует воспользоваться функцией НАЙТИ() .

Примеры

Формула =ПОИСК("к";"Первый канал") вернет 8, т.к. буква к находится на 8-й позиции слева.

Пусть в ячейке А2 введена строка Первый канал - лучший . Формула =ПОИСК(СИМВОЛ(32);A2) вернет 7, т.к. символ пробела (код 32) находится на 7-й позиции.

Формула =ПОИСК("#???#";"Артикул #123# ID") будет искать в строке "Артикул #123# ID " последовательность из 5 символов, которая начинается и заканчивается на знак #.

Чтобы найти позицию второго вхождения буквы "а" в строке "мама мыла раму" используйте формулу =ПОИСК("а";"мама мыла раму";ПОИСК("а";"мама мыла раму")+1). Чтобы определить есть ли третье вхождение буквы "м" в строке "мама мыла раму" используйте формулу =ЕСЛИ(ДЛСТР(ПОДСТАВИТЬ("мама мыла раму";"м";"";3))=ДЛСТР("мама мыла раму");"Нет третьего вхождения м";"Есть третье вхождение м")

Формула =ПОИСК("клад?";"докладная") вернет 3, т.е. в слове "докладная" содержится слово из 5 букв, первые 4 из которых клад (начиная с третьей буквы слова докладная ).

Функция НАЙТИ() vs ПОИСК()

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

Связь с функциями ЛЕВСИМВ() , ПРАВСИМВ() и ПСТР()

Функция ПОИСК() может быть использована совместно с функциями ЛЕВСИМВ() , ПРАВСИМВ() и ПСТР() .

Например, в ячейке А2 содержится фамилия и имя "Иванов Иван", то формула =ЛЕВСИМВ(A2;ПОИСК(СИМВОЛ(32);A2)-1) извлечет фамилию, а =ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(СИМВОЛ(32);A2)) - имя. Если между именем и фамилией содержится более одного пробела, то для работоспособности вышеупомянутых формул используйте функцию СЖПРОБЕЛЫ() .