Поиск функция эксель: ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)

Надстройка «Поиск решения» в Microsoft Excel

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

Как раз об этой опции и пойдет речь далее.

Используемый пример для поиска решения

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

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

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

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

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

Для этого я сначала добавляю функцию СУММ для суммы счетов и считаю сумму каждого в последнем цикле.

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

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

Возможно, текстом описать принцип работы этой таблицы сложно, но я постарался сделать это максимально доходчиво. В итоге получил таблицу с двумя счетами с разными процентами начислений и разными целями. Общая сумма довложений не должна быть более 500, а цель является общей, поскольку предполагается, что весь баланс с депозитных счетов все равно будет выведен на один. Поэтому далее я сделаю так, чтобы баланс к концу всех циклов получился 32500 (7500 + 25000, это предполагаемые цели первого и второго счета). При этом количество довложений должно быть минимальным, чтобы не тратить личные средства, и, соответственно, не превышать установленное ограничение в 500 условных единиц. Теперь давайте разберемся с тем, как реализовать это при помощи рассматриваемой надстройки.

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Подписаться

Включение надстройки «Поиск решения»

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

1. В таблице перейдите на вкладку «Файл».

2. Откройте раздел «Другие».

3. Из появившегося меню выберите пункт «Параметры».

4. Откройте категорию настроек «Надстройки» и отыщите пункт с названием «Поиск решения», после чего выделите его нажатием левой кнопки мыши.

5. Кликните по кнопке «Перейти», находящейся внизу окна.

6. Активируйте галочку возле пункта «Поиск решения» и нажмите «ОК», чтобы выйти из данного окна.

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

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

Настройка «Поиска решений» для таблицы

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

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

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

3. Теперь обратите внимание на «В соответствии с ограничениями». У нас есть ограничения, поэтому нужно указать их, чтобы программа понимала, какие значения может использовать и к какому результату ей стремиться. Нажмите «Добавить», чтобы создать первое ограничение.

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

5. Вторым ограничением является максимальное количество довложений для каждой ячейки. Оно может равняться или быть меньше 250. Соответственно, в вашем случае это будут совершенно другие значения в зависимости от того, с какими исходными данными вы работаете.

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

7. Расчет происходит буквально за несколько секунд, после чего мы видим оптимальное решение. В моем случае каждый цикл на балансы начислялось меньше 250, в один месяц даже 0, а в конце всех циклов получилось достичь нужной суммы с точностью до сотых. «Найти решение» показало, как мне действовать каждый цикл, чтобы вкладывать минимальную сумму, но дойти до нужного результата в конце. У вас решение может быть совершенно другим.

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

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

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

VBA Excel. Функция InStr (синтаксис, параметры, примеры)

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

Описание функции InStr

InStr – это функция, которая предназначена для определения номера позиции первого вхождения одной строки в другую. Она возвращает значение типа Variant (Long).

Функция InStr ищет первое вхождение одной строки в другую с начала исходной строки. Для поиска первого совпадения с конца исходной строки используется функция InStrRev.

Функция InStr часто незаменима при определении параметров функций Left, Mid и Right. Также ее можно использовать для определения наличия искомой подстроки в заданной строке.

Еще есть в VBA Excel функция InStrB, которая работает с байтовыми данными, содержащимися в строке. Она возвращает позицию байта, а не символа первого вхождения одной строки в другую. Смотрите ниже Пример 3.

Синтаксис, параметры, значения

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

Полный вариант:

Сокращенный вариант:

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

Параметры функции InStr

* Если задан аргумент compare, аргумент start является обязательным.
** Если аргумент compare не указан, используется значение инструкции Option Compare, заданное на уровне модуля. Если инструкция Option Compare в начале модуля отсутствует, используется ее значение по умолчанию – 0 (двоичное сравнение).

Если параметр start или параметр compare содержит значение NULL, возникает ошибка.

Значения аргумента «compare»

* При двоичном сравнении учитывается регистр букв, при текстовом – не учитывается.

Значения функции InStr

Примеры использования в VBA Excel

Пример 1

Самый простой пример:

Пример 2

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

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

Пример 3

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

Результат 7 при побайтовом сравнении получен для кодировки, у которой один символ составляет 2 байта.

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

Как использовать функцию ПОИСК в Excel (примеры + видео)

Функция ПОИСК в Excel (пример + видео)

Когда использовать функцию ПОИСК в Excel

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

Что он возвращает

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

Синтаксис

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

Входные аргументы

• find_text — текст или строка, которую нужно найти.
• внутри_текста — текст, в котором вы хотите найти аргумент искомый_текст.
• [start_num] — число, представляющее позицию, с которой вы хотите начать поиск. Если его опустить, он начинается с самого начала.

Дополнительные примечания

• Если начальный номер не указан, то поиск начинается с начала строки.
• Функция ПОИСК не учитывает регистр . Если вы хотите выполнить поиск с учетом регистра, используйте функцию НАЙТИ.
• Функция ПОИСК может обрабатывать подстановочные знаки.
  • В Excel есть три подстановочных знака — вопросительный знак (?), звездочка (*) и тильда (~).
    • Знак вопроса соответствует любому одиночному символу.
    • Звездочка соответствует любой последовательности символов.
    • Если вы хотите найти фактический вопросительный знак или звездочку, введите тильду ( ~ ) перед символом.
• Возвращает #ЗНАЧ! ошибка, если искомая строка не найдена в тексте.

Функция ПОИСК в Excel – примеры

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

#1 Поиск слова в текстовой строке (с начала)

В приведенном выше примере при поиске для слова good в тексте Good Morning возвращается 1, что является позицией начальной точки искомого слова.

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

Если вы ищете чувствительный к регистру поиск, используйте функцию ПОИСК в Excel.

#2 Поиск слова в текстовой строке (с указанным началом)

Третий аргумент функции ПОИСК — это позиция в тексте, с которой вы хотите начать поиск. В приведенном выше примере функция возвращает 1 при поиске текста «Доброе утро» в «Доброе утро», а начальная позиция — 1.

Однако он возвращает ошибку, когда вы начинаете с 2. Следовательно, он ищет текст доброе утро. Так как он не может его найти, он возвращает ошибку.

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

#3 При многократном вхождении искомого текста

Запускается функция ПОИСК Excel поиск в указанном тексте с указанной позиции. В приведенном выше примере, когда вы ищете текст good в Good Good Morning с начальной позицией 1, он возвращает 1, поскольку находит его в начале.

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

 #4 Использование подстановочных знаков в функции поиска Excel

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

Функция ПОИСК в Excel – Видеоруководство

Связанные функции Excel:

• Функция ПОИСК в Excel.
• Excel Функция МЕНЬШЕ.
• Функция Excel ПРОПИСН.
• Функция Excel PROPER.
• Excel Функция ЗАМЕНА.
• Excel Функция ПОДСТАВИТЬ.

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

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

Функции Excel ПОИСК и НАЙТИ: пошаговое руководство (2023)

Перейти к содержимому

Функции ПОИСКА и НАЙТИ в Excel: пошаговое руководство (2023 г.)

Функция НАЙТИ в Excel находит и возвращает позицию символа в заданной текстовой строке.

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

К вашему удивлению, обе эти функции выполняют одну и ту же работу, но имеют разные функции.

Что это? И как вы можете использовать эти функции?

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

Содержание

Как использовать функцию НАЙТИ

Функция НАЙТИ — это простая функция с тремя основными аргументами. Он ищет символ в заданной текстовой строке и возвращает его относительное положение.

Как? Посмотрите здесь 👇

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

1. Запишите аргумент find_text функции НАЙТИ следующим образом:

= НАЙТИ («p»,

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

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

= НАЙТИ («p», «компания»)

Мы набрали слово «компания», заключенное в двойные кавычки.

Третий аргумент функции НАЙТИ — это аргумент [начальный_номер].

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

Каспер Лангманн , специалист Microsoft Office

3. Нажмите «Ввод», и все готово!

Excel возвращает относительное положение «p» из текстовой строки «company» как 4. Это потому, что «p» — четвертая буква слова «company» 4️⃣

Вот как просто использовать функцию НАЙТИ в Microsoft Excel.

Теперь давайте посмотрим на его вариант.

4. Напишите функцию НАЙТИ, чтобы найти «с» в текстовой строке «цикл».

= НАЙТИ («c», «цикл»

5. Укажите аргумент start_num равным 2.

= НАЙТИ («с», «цикл», 2)

Результат равен 3. Но мы видим, что символ «с» стоит первым в текстовой строке «цикл». Тогда почему функция НАЙТИ возвращает 3?

Когда мы устанавливаем аргумент [start_num] равным 2, Excel начинает подсчет символов со второй подстроки.

Начните поиск подстроки «с» в слове «цикл», но пропустите первые две буквы. Следующая буква «c» стоит на третьей позиции 🚴‍♀️

C Y C L E

Итак, Excel возвращает 3.

Если аргумент [start_num] опущен, Excel по умолчанию устанавливает его равным 1. Таким образом, счетчик символов начинается с первого символа.

Профессиональный совет!

Excel вернет ошибку #ЗНАЧ, если вы установите для аргумента [start_num] значение:

1. Ноль (0)
2. Отрицательное число
3. Или на число, превышающее количество слов в аргументе [внутри_текста].

Например, вы установили аргумент [start_num] для функции выше равным 6, тогда как количество символов в слове «цикл» составляет всего 5 🚩

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

Теперь настала очередь функции ПОИСК.

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

Давайте посмотрим на быстрый пример функции ПОИСК здесь 👀

Наша миссия — найти позицию символа «b» в текстовой строке «Мой мяч».

1. Напишите функцию ПОИСК, как показано ниже.

= ПОИСК («b»

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

2. Далее укажите в текстовой строке, откуда следует искать позицию символа.

= ПОИСК («b», «Мой мяч»)

Закройте функцию прямо здесь. Или вы можете определить аргумент [start_num], если вы не хотите, чтобы счетчик символов начинался с первого символа.

3. Нажмите «Ввод», и вот вам результаты.

Результат снова равен 4, и мы знаем, что это правильно ✅

Кстати, это показывает, что Excel также считает пробелы. Подстрока «b» стоит на четвертом месте после M, Y и пробела.

Профессиональный совет!

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

Символ «b» в нашей функции написан строчными буквами. Принимая во внимание, что «b» в «Мой мяч» в верхнем регистре ⚽

Но это не имеет значения для функции ПОИСК. Ему все же удалось найти относительное положение символа «b» в «My Ball».

ПОИСК и ПОИСК: различия

Прежде всего мы рассмотрели, как применять функции ПОИСК и ПОИСК в Excel.

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

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

Чувствительность к регистру

Давайте быстро найдем позицию «Деньги» в текстовой строке «Время — деньги», используя функцию ПОИСК.

Запишите функцию ПОИСК следующим образом:

= ПОИСК («Деньги», «Время-деньги»)

Обратите внимание, что буква «М» для денег в нашем первом аргументе написана в верхнем регистре. Тогда как «м» денег в «Время-деньги» — строчная буква🔍

Каспер Лангманн , специалист по Microsoft Office

Функция ПОИСК возвращает 9. И это верно — М денег — это 9-й символ текстовой строки «Время — деньги».

Теперь давайте проделаем то же самое с функцией НАЙТИ.

Напишите функцию НАЙТИ следующим образом:

= НАЙТИ («Деньги», «Время — деньги»)

Странно! Функция НАЙТИ возвращает ошибку #ЗНАЧ.

Это потому, что функция НАЙТИ ищет деньги с большой буквы М в заданной текстовой строке. И возвращает ошибку #ЗНАЧ при невозможности найти что-либо.

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

Профессиональный совет!

Вот вам на что посмотреть.

Что произойдет, если указать более одного символа в качестве аргумента find_text? Как «Деньги» в приведенном выше примере 💸

Обе функции ПОИСК и НАЙТИ возвращают позицию первого символа аргумента find_text. (в данном случае «M»)

Подстановочные знаки

Еще одна большая разница между функциями ПОИСК и ПОИСК!

Функция ПОИСК принимает подстановочные знаки. Однако функция НАЙТИ не работает.

1. Используйте подстановочный знак с функцией ПОИСК, как показано ниже.

= ПОИСК («Excel?????», B2)

За словом Excel следуют пять вопросительных знаков (подстановочных знаков)❓ Эти вопросительные знаки представляют любые возможные пять символов после слова Excel.

Каспер Лангманн , специалист по Microsoft Office

В ячейке B2 указан внутренний_текст, поэтому мы создали ссылку на него.

2. Нажмите «Ввод», и готово!

Excel дает результат 7. Это потому, что буква «E» стоит на седьмой позиции в текстовой строке «Great Excel 2019».

Функция ПОИСК применила подстановочные знаки вместо пробела, 2, 0, 1 и 9.

Пришло время сделать то же самое с помощью функции ПОИСК.

3. Используйте те же подстановочные знаки с функцией НАЙТИ, как показано ниже.

= НАЙТИ («Excel?????», B3)

4. Нажмите Enter.

Excel возвращает ошибку #ЗНАЧ. Это потому, что функция НАЙТИ рассматривает каждый вопросительный знак как настоящий вопросительный знак.

И не находит их в текстовой строке « Great Excel 2019 » 😵

Вот и все — что теперь?

У-у-у! До сих пор мы видели практические примеры использования функций ПОИСК и НАЙТИ в Excel.

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

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

В Excel существует так много других умных функций, которые вам необходимо изучить. Чтобы упомянуть некоторые, функции VLOOKUP, SUMIF и IF Excel.

Вот ссылка на мой 30-минутный бесплатный курс электронной почты, который научит вас этим (и многим другим) функциям Excel.

Прочие ресурсы

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

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

Узнайте, как извлекать подстроки в Excel с помощью функций LEFT, RIGHT и MID.

Часто задаваемые вопросы

Как искать текст в ячейке в Excel?

Для поиска текста в ячейке функции ISNUMBER и SEARCH можно использовать вместе.

Функция ПОИСК будет искать заданный текст в определенной ячейке. Если он найден, он вернет позицию текста в этой ячейке.

Если не найдено, возвращается ошибка #ЗНАЧ. ISNUMBER возвращает FALSE или TRUE, проверяя результат на числовое значение.