Как работает функция поиск в excel

Функция ПОИСК() в EXCEL

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

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

Нач_позиция — позиция знака в просматриваемой_строке, с которой должен начинаться поиск. Если аргумент нач_позиция опущен, то предполагается значение 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)) — имя. Если между именем и фамилией содержится более одного пробела, то для работоспособности вышеупомянутых формул используйте функцию СЖПРОБЕЛЫ() .

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

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

Как работает функция ПОИСК в Excel

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

У нас есть слово «Excel». В этом примере нужно найти положение буквы «Х» в слове. Функция возвратит значение 2, поскольку буква находится по счету на втором месте в искомых данных:

Несмотря на то, что искомая буква «Х» находится в верхнем регистре, функция нашла ее аналог в нижнем регистре и выдала результат. В этом и есть отличие с функцией НАЙТИ – она обращает внимание на соответствие регистров.

Также мы можем искать часть слова или слово в искомой области, например найти слово «Excel» в «Exceltable» и «table» во фразе «Excel table». В первом случае в результате мы получим 1, потому что слово «Excel» начинается с первого символа. Во втором случае у нас будет результат 7, потому что «table» начинается с седьмого символа:

Аргумент начальная_позиция используем, когда нужно отсчитать положение символа, начиная с которого возвратится искомое значение. Например, нужно отследить с какой позиции начинается буквенное значение кода. Если не указать номер позиции, у нас возвратится число «2», поскольку буква «о» в ячейке А8 находится второй по порядку:

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

Функция ПОИСКа значения в столбце Excel

В данном примере будет использоваться формула комбинации функции ПОИСК с функциями: ЛЕВСИМВ, ПРАВСИМВ, ДЛСТР. Рассмотрим поэтапно пример, где мы сможем извлекать части фраз с текста, из которого получим искомое значение. У нас есть товар и артикул товара. Наше задание – возвратить только буквенную часть названия артикула. Для этого в ячейке C12 начинаем писать формулу. Для получения результата нам нужна функция ЛЕВСИМВ.

1. Первый аргумент — текст, в котором происходит поиск (ячейка B12).
2. Второй аргумент — нужна длина искомого слова. В первом артикуле она равна 3, а в последующих меняется, поэтому используем формулу ПОИСК(«_»;B12).

Формула с аргументами («_»;B12) указала, что будут возвращаться те символы, которые расположены перед символом нижнего подчеркивания. Проверим наш результат:

Функция ПОИСК возвратила число 4 (порядочное положение знака нижнего подчеркивания), и в качестве второго аргумента функции ЛЕВСИМВ указала какие символы будут находиться в ячейке С12. Пока что это не совсем то, что необходимо получить – знак «_» в идеале должен отсутствовать. Для этого немного подкорректируем формулу: перед вторым аргументом (формулой ПОИСК) отнимаем единицу, этим мы указали что вывод символов будет без знака нижнего подчеркивания (4-1):

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

Если вдруг вам понадобится в этой же таблице изменить артикул – функция среагирует на изменения корректно и автоматически возвратит текстовое значение заменённого артикула. Например для товара «Бокал» будет буквенная часть «К», для «Тарелки» — «М», для «Стола» — ADCDE:

Теперь рассмотрим пример, где будем извлекать символы не ПЕРЕД нижним подчеркиванием, а ПОСЛЕ. В этом нам поможет функция ДЛСТР. Она помогает узнать длину текстовой строки. В ячейке С20 пишем формулу:

В результате она возвратила нам длину артикула товара «Бокал». Скопируем формулу до конца столбца и в следующем этапе в ячейке Е20 напишем формулу ПОИСК. Нижнее подчеркивание – это искомое значение (аргумент 1), возвращаемое значение – позиция порядковый номер положения нижнего подчеркивания. Копируем до конца столбца:

Затем нам нужен столбец, где мы от длины строки отнимаем позицию нижнего подчеркивания (9 — 4) и копируем формулу до конца столбца. То есть этот столбец содержит длину числового значения артикула (значения ПОСЛЕ нижнего подчеркивания):

Теперь в ячейке F20 пишем функцию ПРАВСИМВ, которая возвратит нам текст, часть фразы, которую мы запрашимаем. Первый аргумент функции – это ячейка, которую проверяет формула, а второй – длина возвращаемого значения:

Функция возвратила нам числовое значение артикула товара. Копируем до конца столбца и получаем результат по каждому товару:

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

В этом примере мы сделали то же самое, что и ранее, только все операции сделали в одной формуле: нашли длину текста, отняли длину текста после знака «_» и возвратили эту длину функцией ПРАВСИМВ.

Как использовать функцию ПОИСК с функцией ЕСЛИ

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

Если почтовый индекс начинается с СВ2, тогда это локальный адрес. Мы не можем использовать функцию ЕСЛИ в её обычном выражении, потому что в ячейках много другого текста. Нам нужно знать содержит ли ячейка часть «СВ2». Чтобы лучше понимать логику построения функции, будем делать это от простого к более сложному выражению:

1. Строим функцию ПОИСК: 1-й аргумент – «СВ2», 2-й аргумент – ячейка А37, копируем до конца столбца. Так мы указали что и где будем искать. Там, где нет текста СВ2, возвратилась ошибка. Но сейчас у нас есть только номер позиции текста СВ2. А функция ЕСЛИ будет искать идентичное совпадение. То есть она будет искать число 11 в тексте ячейки А37, которого у нас, конечно, не будет нигде.
2. Формулу функции ПОИСК вкладываем в функцию ЕЧИСЛО (имеет только 1 аргумент), которая будет указателем в дальнейшем для ЕСЛИ, что результатом функции ПОИСК является число (как сейчас, у нас 11):
3. Полученную формулу вкладываем в ЕСЛИ, указывая, что для значения ИСТИНА у нас будет слово «Местный», а для значения ЛОЖЬ – «Далеко». Копируем до конца столбца:

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Как работает функция поиск решения Excel

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

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

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

Зачем нужна функция поиска решения в Excel

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

С помощью функции поиска решения пользователи могут:

1. Решать сложные математические задачи: Функция поиска решения может использоваться для решения различных математических задач, таких как поиск корней уравнений, оптимальных значений или нахождение экстремумов функций.
2. Оптимизировать процессы и принимать обоснованные решения: Функция поиска решения позволяет находить оптимальные значения переменных для достижения желаемых результатов. Это особенно полезно при принятии решений, связанных с финансами, производством, инвестициями или другими сложными задачами.
3. Моделировать и анализировать данные: Функция поиска решения позволяет пользователям моделировать различные сценарии и анализировать данные с помощью численных методов. Она помогает определить зависимости, прогнозировать результаты, оценивать риски и принимать обоснованные решения.
4. Автоматизировать рутинные вычисления: Функция поиска решения может быть использована для автоматизации повторяющихся вычислений или оптимизации процессов, что экономит время и снижает вероятность ошибок.

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

Как использовать функцию поиска решения в Excel

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

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

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

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

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

Примеры применения функции поиска решения в Excel

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

1. Оптимизация процесса производства

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

2. Расчет финансовых моделей

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

3. Поиск оптимальных решений в области маркетинга

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

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

Особенности работы функции поиска решения в Excel

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

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

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

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

Достоинства функции поиска решения в Excel

1. Автоматизация процесса

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

2. Гибкость и точность

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

3. Легкость использования

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

4. Возможность поиска оптимальных решений

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

5. Вариативность

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

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

Поиск позиции элемента в списке с ПОИСКПОЗ (MATCH)

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

Точный поиск

Классический сценарий — поиск точного текстового совпадения для нахождения позиции нужного нам текста или числа в списке:

Поиск первой или последней текстовой ячейки

Если в качестве искомого значения задать звездочку, то функция будет искать первую ячейку с текстом и выдавать её позицию. Для поиска последней текстовой ячейки можно изменить третий аргумент Режим_поиска с нуля на минус 1:

Числа и пустые ячейки в этом случае игнорируются.

Поиск ближайшего числа или даты

Если последний аргумент задать равным 1 или -1, то можно реализовать поиск ближайшего наименьшего или наибольшего числа. Таблица при этом обязательно должна быть отсортирована по возрастанию или убыванию соответственно. В общем и целом, это чем-то похоже на интервальный просмотр у функции ВПР (VLOOKUP) , но там возможен только поиск ближайшего наименьшего, а здесь — есть выбор.

Например, нам нужно выбрать генератор из прайс-листа для расчетной мощности в 47 кВт. Если последний аргумент задать равным 1 и отсортировать таблицу по возрастанию, то мы найдем ближайшую наименьшую по мощности модель (Зверь):

Если же третий аргумент равен -1 и таблица отсортирована по убыванию, то мы найдем ближайшую более мощную модель (Бомба):

Связка функций ПОИСКПОЗ и ИНДЕКС

Очень часто функция ПОИСКПОЗ используется в связке с другой крайне полезной функцией — ИНДЕКС (INDEX) , которая умеет извлекать данные из диапазона по номеру строки-столбца, реализуя, фактически, «левый ВПР».

Так, в предыдущем примере получить не номер, а название модели генератора можно очень легко:

Ну, и поскольку Excel внутри хранит и обрабатывает даты как числа, то подобный подход на 100% работает и с датами. Например, мы можем легко определить на каком этапе сейчас находится наш проект:

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

Ссылки по теме

• 5 вариантов применения функции ИНДЕКС (INDEX)
• Интервальный просмотр с помощью функции ВПР (VLOOKUP)
• Поискпоследнего значения в строке или столбце
• Как создать авторастягивающийся диапазон с автоподстройкой размеров