В этой статье рассматривается синтаксис функции ПОИСКПОЗ Excel , а также как использовать функцию ПОИСКПОЗ в Excel с примерами формул.

Также в этой статье рассматривается комбинация формул ИНДЕКС ПОИСКПОЗ в Excel .

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

Функция ПОИСКПОЗ в Excel - синтаксис и использование

Функция ПОИСКПОЗ в Excel ищет заданное значение в диапазоне ячеек и возвращает относительное положение этого значения.

Синтаксис формулы ПОИСКПОЗ выглядит следующим образом:

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

Искомое_значение (обязательный аргумент) - значение, которое вы хотите найти. Это может быть числовое, текстовое или логическое значение, а также ссылка на ячейку.

Просматриваемый_массив (обязательный аргумент) - диапазон ячеек для поиска.

Тип_сопоставления (необязательный аргумент) - определяет тип соответствия. Это может быть одно из следующих значений: 1, 0, -1.

• 1 или отсутствует (по умолчанию) - находит наибольшее значение в массиве, которое меньше или равно значению поиска. Массив поиска должен быть отсортирован в порядке возрастания, от самого маленького до большого или от A до Z.
• 0 - находит первое значение в массиве, которое в точности равно значению поиска. Сортировка не требуется.
• -1 - находит наименьшее значение в массиве, которое больше или равно значению поиска. Массив поиска должен быть отсортирован в порядке убывания, от самого большого до самого маленького или от Z до A.

Чтобы лучше разобраться в данной функции, давайте напишем простую формулу ПОИСКПОЗ , основанную на следующих данных: фамилии студентов в столбце A и их баллы по экзаменам в столбце B, отсортированные от самых высоких до самых низких. Чтобы узнать, где среди других находится конкретный студент (например, студентка Виноградова), используйте эту простую формулу:

ПОИСКПОЗ(E1; A2:A8; 0)

При желании вы можете поместить значение поиска в ячейку (E1 в данном примере) и сослаться на эту ячейку в формуле ПОИСКПОЗ Excel:

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

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

Особенности функции ПОИСКПОЗ Excel

Как вы только что видели, использовать ПОИСКПОЗ в Excel легко. Однако, как и у любой функции Excel, у нее есть несколько особенностей, о которых следует знать:

• Функция ПОИСКПОЗ возвращает относительное положение значения поиска в массиве, а не самого значения.
• ПОИСКПОЗ не зависит от регистра , что означает, что она не различает строчные и прописные символы при работе с текстовыми значениями.
• Если массив поиска содержит несколько вхождений искомого значения, возвращается позиция первого значения.
• Если искомое значение не найдено в просматриваемом массиве, возвращается ошибка #Н/Д.

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

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

Формула ПОИСКПОЗ Excel с подстановочными знаками

Как и многие другие функции Excel, ПОИСКПОЗ распознает следующие подстановочные знаки:

• Вопросительный знак (?) - заменяет любой символ
• Звездочка (*) - заменяет любую последовательность символов

Примечание . Подстановочные знаки могут использоваться только в формулах ПОИСКПОЗ с параметром тип_сопоставления равному 0.

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

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

Предполагая, что названия компаний находятся в диапазоне A2:A8, и вы ищете название, начинающееся с «восток», формула выглядит следующим образом:

ПОИСКПОЗ("восток*"; A2:A8; 0)

Чтобы сделать нашу формулу ПОИСКПОЗ более универсальной, вы можете ввести значение поиска в любой ячейке (E1 в этом примере) и эту ячейку с подстановочным символом, например:

ПОИСКПОЗ(E1&"*"; A2:A8; 0)

Как показано на изображении ниже, формула возвращает 2 позицию, которая соответствует компании «Восток-авто »:

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

Чтобы заменить только один символ в значении поиска, используйте подстановочный оператор «?», например:

ПОИСКПОЗ("б?йкер"; A2:A8;0)

Вышеуказанная формула будет соответствовать названию «Бейкер » и вернет его относительное положение, которое равно 5.

Формула ПОИСКПОЗ с учетом регистра

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

Чувствительность к регистру формулы для сопоставления данных в Excel заключается в следующем:

ПОИСКПОЗ(ИСТИНА;СОВПАД(искомое_значение;просматриваемый_массив); 0)

Логика этой формулы такова:

• Функция СОВПАД сравнивает значение поиска с каждым элементом просматриваемого массива. Если сравниваемые ячейки в точности равны, функция возвращает ИСТИНУ, или ЛОЖЬ в противном случае.
• Далее функция ПОИСКПОЗ сравнивает ИСТИНУ (которая является ее искомым_значением) с каждым значением в массиве, возвращаемом СОВПАД, и возвращает позицию первого совпадения

Обратите внимание , что это формула массива, которая требует нажатия Ctrl+Shift+Enter для правильной обработки.

Пусть значение поиска находится в ячейке E1, а массив поиска - A2:A8, тогда формула выглядит следующим образом:

ПОИСКПОЗ(ИСТИНА; СОВПАД(A2:A8; E1);0)

На следующем изображении демонстрируется формула ПОИСКПОЗ с учетом регистра в Excel:

Функция ПОИСКПОЗ в Excel - Пример функции ПОИСКПОЗ Excel с учетом регистра

Сравнить 2 столбца на совпадения и различия (ЕНД и ПОИСКПОЗ)

Проверка двух списков на совпадения и различия - одна из наиболее распространенных задач в Excel, и это можно сделать различными способами. Использование формулы ЕНД/ПОИСКПОЗ является одним из них:

ЕСЛИ(ЕНД(ПОИСКПОЗ (1-е значение в Списке 1; Список 2; 0)); "Нет в Списке 1"; "")

Для любого значения из Списка 2, отсутствующего в Списке 1, формула возвращает «Нет в Списке 1». И вот как это происходит:

• Функция ПОИСКПОЗ выполняет поиск значения из Списка 1 в Списке 2. Если значение найдено, она возвращает относительное положение, в противном случае ошибку #Н/Д.
• Функция ЕНД в Excel выполняет только одно: проверяет наличие ошибок #Н/Д (что означает «недоступно»). Если заданное значение является ошибкой #Н/Д, функция возвращает ИСТИНА, в противном случае - ЛОЖЬ. В нашем случае ИСТИНА означает, что значение из Списка 1 не найдено в Списке 2 (ПОИСКПОЗ возвращает ошибку #Н/Д).
• Поскольку для других пользователей будет сложно интерпретировать результаты в формате ИСТИНА/ЛОЖЬ, то мы будем использовать функцию ЕСЛИ для отображения текста «Нет в Списке 1» для значений, которых нет в Списке 1.

Предположим, что нам необходимо сравнить значения в столбце B со значениями в столбце A, формула примет следующую форму (где B2 - самая верхняя ячейка):

ЕСЛИ(ЕНД(ПОИСКПОЗ(B2;A:A;0));"Нет в Списке 1"; "")

Как вы помните, функция ПОИСКПОЗ Excel сама по себе не учитывает регистр. Чтобы заставить ее различать регистр, вставьте функцию СОВПАД в аргумент просматриваемый_массив и не забудьте нажать Ctrl+Shift+Enter , чтобы завершить эту формулу массива :

ЕСЛИ(ЕНД(ПОИСКПОЗ(ИСТИНА; СОВПАД(A:A;B2); 0));"Нет в Списке 1"; "")

На следующем изображении демонстрируются обе формулы в действии:

Функция ПОИСКПОЗ в Excel - Сравнение 2 столбцов на совпадения с использованием ЕНД и ПОИСКПОЗ

ИНДЕКС и ПОИСКПОЗ в Excel

С функцией ПОИСКПОЗ Excel мы уже знакомы. Давайте теперь рассмотрим, как можно использовать комбинацию ИНДЕКС ПОИСКПОЗ в Excel. Начнем с синтаксиса функции ИНДЕКС.

Синтаксис и использование функции ИНДЕКС

Функция ИНДЕКС Excel возвращает значение в массиве, основанное на указанных вами строках и столбцах. Синтаксис функции ИНДЕКС прост:

ИНДЕКС(массив; номер_строки; [номер_столбца])

Вот очень простое объяснение каждого параметра:

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

Если используются оба параметра номер_строки и номер_столбца, функция ИНДЕКС возвращает значение в ячейке на пересечении указанной строки и столбца.

Вот простейший пример формулы ИНДЕКС:

ИНДЕКС(A1:C10;2;3)

Формула ищет в ячейках от A1 до C10 и возвращает значение ячейки во второй строке и третьем столбце, то есть в ячейке C2.

Очень легко, не так ли? Однако при работе с реальными данными вы вряд ли знаете, какие строки и столбцы вам нужны, поэтому вам нужна помощь функции ПОИСКПОЗ .

ИНДЕКС ПОИСКПОЗ в Excel пример

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

Функция ПОИСКПОЗ определяет относительное положение значения поиска в указанном диапазоне ячеек. А функция ИНДЕКС принимает это число и возвращает значение в соответствующую ячейку.

Комбинацию ИНДЕКС ПОИСКПОЗ в Excel можно представить таким образом:

ИНДЕКС(столбец для возвращения значения, ПОИСКПОЗ (значение поиска, столбец для поиска, 0))

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

Функция ПОИСКПОЗ в Excel - Исходные данные для формулы ИНДЕКС ПОИСКПОЗ в Excel

Найдем численность населения, например, России, используя следующую формулу ИНДЕКС ПОИСКПОЗ :

ИНДЕКС($C$2:$C$11;ПОИСКПОЗ("Россия";$B$2:$B$11;0))

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

• Функция ПОИСКПОЗ выполняет поиск значения «Россия» в столбце B, точнее в ячейках B2:B11 и возвращает номер 7, потому что «Россия» находится на седьмом месте в списке.
• Функция ИНДЕКС принимает «7» во втором параметре (номер_строки), который указывает, из какой строки вы хотите вернуть значение, и превращается в простую формулу =ИНДЕКС($C$2:$C$11, 7). То есть формула производит поиск в ячейках C2-C11 и возвращает значение ячейки в седьмой строке, то есть ячейке C8, потому что мы начинаем отсчет со второй строки.

И вот результат, который мы получаем в Excel:

Функция ПОИСКПОЗ в Excel - ИНДЕКС ПОИСКПОЗ пример формулы

Обратите внимание! Количество строк и столбцов в массиве ИНДЕКС должно соответствовать значениям в параметрах номер_строки и/или номер_столбца функции ПОИСКПОЗ соответственно. В противном случае формула вернет неверный результат.

Оптимизация значений таблицы 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.

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

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

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

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

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

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

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

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

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

Поиск значения в строке Excel

Теперь узнаем, в каком максимальном объеме и в каком месяце была максимальная продажа Товара 4.

Чтобы выполнить поиск по столбцам следует:

Найдено в каком месяце и какая была наибольшая продажа Товара 4 на протяжении двух кварталов.

Принцип действия формулы поиска значения в строке Excel:

В первом аргументе функции ВПР (Вертикальный ПРосмотр) указывается ссылка на ячейку где находится критерий поиска. Во втором аргументе указывается диапазон ячеек для просмотра в процессе поиска. В третьем аргументе функции ВПР должен указываться номер столбца, из которого следует взять значение на против строки с именем Товар 4. Но так как нам заранее не известен этот номер мы с помощью функции СТОЛБЕЦ создаем массив номеров столбцов для диапазона B4:G15.

Это позволяет функции ВПР собрать целый массив значений. В результате в памяти хранится все соответствующие значения каждому столбцу по строке Товар 4 (а именно: 360; 958; 201; 605; 462; 832). После чего функции МАКС остается только взять из этого массива максимальное число и возвратить в качестве значения для ячейки D1, как результат вычисления формулы.

Как видно конструкция формулы проста и лаконична. На ее основе можно в похожий способ находить для определенного товара и другие показатели. Например, минимальное или среднее значение объема продаж используя для этого функции МИН или СРЗНАЧ. Вам ни что не препятствует, чтобы приведенный этот скелет формулы применить с использованием более сложных функций для реализации максимально комфортного анализа отчета по продажам.

Как получить заголовки столбцов по зачиню одной ячейки?

Например, как эффектно мы отобразили месяц, в котором была максимальная продажа, с помощью второй формулы. Не сложно заметить что во второй формуле мы использовали скелет первой формулы без функции МАКС. Главная структура формулы: ВПР(B1;A5:G14;СТОЛБЕЦ(B5:G14);0). Мы заменили функцию МАКС на ПОИСКПОЗ, которая в первом аргументе использует значение, полученное предыдущей формулой. Оно теперь выступает в качестве критерия для поиска месяца. И в результате функция ПОИСКПОЗ нам возвращает номер столбца 2 где находится максимальное значение объема продаж для товара 4. После чего в работу включается функция ИНДЕКС, которая возвращает значение по номеру сроки и столбца из определенного в ее аргументах диапазона. Так как у нас есть номер столбца 2, а номер строки в диапазоне где хранятся названия месяцев в любые случаи будет 1. Тогда нам осталось функцией ИНДЕКС получить соответственное значение из диапазона B4:G4 – Февраль (второй месяц).



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

Вторым вариантом задачи будет поиск по таблице с использованием названия месяца в качестве критерия. В такие случаи мы должны изменить скелет нашей формулы: функцию ВПР заменить ГПР, а функция СТОЛБЕЦ заменяется на СТРОКА.

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

Чтобы найти какой товар обладал максимальным объемом продаж в определенном месяце следует:

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

В первом аргументе функции ГПР (Горизонтальный ПРосмотр) указываем ссылку на ячейку с критерием для поиска. Во втором аргументе указана ссылка на просматриваемый диапазон таблицы. Третий аргумент генерирует функция СТРОКА, которая создает в памяти массив номеров строк из 10 элементов. Так как в табличной части у нас находится 10 строк.

Далее функция ГПР поочередно используя каждый номер строки создает массив соответственных значений продаж из таблицы по определенному месяцу (Июню). Далее функции МАКС осталось только выбрать максимальное значение из этого массива.

ВНИМАНИЕ! При использовании скелета формулы для других задач всегда обращайте внимание на второй и третий аргумент поисковой функции ГПР. Количество охваченных строк в диапазоне указанного в аргументе, должно совпадать с количеством строк в таблице. А также нумерация должна начинаться со второй строки!

В этой статье описаны синтаксис формулы и использование функций ПОИСК и ПОИСКБ в Microsoft Excel.

Описание

Функции ПОИСК И ПОИСКБ находят одну текстовую строку в другой и возвращают начальную позицию первой текстовой строки (считая от первого символа второй текстовой строки). Например, чтобы найти позицию буквы "n" в слове "printer", можно использовать следующую функцию:

ПОИСК("н";"принтер")

Эта функция возвращает 4 , так как "н" является четвертым символом в слове "принтер".

Можно также находить слова в других словах. Например, функция

ПОИСК("base";"database")

возвращает 5 , так как слово "base" начинается с пятого символа слова "database". Можно использовать функции ПОИСК и ПОИСКБ для определения положения символа или текстовой строки в другой текстовой строке, а затем вернуть текст с помощью функций ПСТР и ПСТРБ или заменить его с помощью функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ . Эти функции показаны в данной статьи.

Важно:

Эти функции могут быть доступны не на всех языках.

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

К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

Синтаксис

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

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

Аргументы функций ПОИСК и ПОИСКБ описаны ниже.

Искомый_текст Обязательный. Текст, который требуется найти.

Просматриваемый_текст Обязательный. Текст, в котором нужно найти значение аргумента искомый_текст .

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

Замечание

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

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

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

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

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

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

Примеры

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

Данные
Доход: маржа
Здесь "босс".
Формула	Описание	Результат
ПОИСК("и";A2;6)	Позиция первого знака "и" в строке ячейки A2, начиная с шестого знака.
ПОИСК(A4;A3)	Начальная позиция строки "маржа" (искомая строка в ячейке A4) в строке "Доход: маржа" (ячейка, в которой выполняется поиск - A3).
ЗАМЕНИТЬ(A3;ПОИСК(A4;A3);6;"объем")	Заменяет слово "маржа" словом "объем", определяя позицию слова "маржа" в ячейке A3 и заменяя этот знак и последующие пять знаков текстовой строкой "объем."	Доход: объем
ПСТР(A3;ПОИСК(" ";A3)+1,4)	Возвращает первые четыре знака, которые следуют за первым пробелом в строке "Доход: маржа" (ячейка A3).
ПОИСК("""";A5)	Позиция первой двойной кавычки (") в ячейке A5.
ПСТР(A5;ПОИСК("""";A5)+1;ПОИСК("""";A5;ПОИСК("""";A5)+1)-ПОИСК("""";A5)-1)	Возвращает из ячейки A5 только текст, заключенный в двойные кавычки.

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

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

Поиск в таблице Эксель, функции ВПР и ГПР

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

Синтаксис функции ВПР такой: =ВПР(Искомое_значение; таблица_для_поиска; номер_выводимого_столбца; [тип_сопоставления]) . Рассмотрим аргументы:

• Искомое значение – значение, которое будем искать. Это обязательный аргумент;
• Таблица для поиска – тот массив ячеек, в котором будет поиск. Столбец с искомыми значениями должен быть первым в этом массиве. Это тоже обязательный аргумент;
• Номер выводимого столбца – порядковый номер столбца (начиная с первого в массиве), из которого функция выведет данные при совпадении искомых значений. Обязательный аргумент;
• Тип сопоставления – выберите «1» (или «ИСТИНА») для нестрогого совпадения, «0» («ЛОЖЬ») – для полного совпадения. Аргумент необязателен, если его упустить – будет выполнен поиск нестрогого совпадения .

Поиск точного совпадения с помощью ВПР

Посмотрим на примере, как работает функция ВПР, когда выбран тип сопоставления «ЛОЖЬ», поиск точного совпадения. В массиве В5:Е10 указаны основные средства некой компании, их балансовая стоимость, инвентарный номер и место расположения. В ячейке В2 указано наименование, для которого нужно в таблице найти инвентарный номер и поместить его в ячейку С2 .

Функция ВПР в Excel

Запишем формулу: =ВПР(B2;B5:E10;3;ЛОЖЬ) .

Здесь первый аргумент указывает, что в таблице нужно искать значение из ячейки В2 , т.е. слово «Факс». Второй аргумент говорит, что таблица для поиска — в диапазоне В5:Е10 , а искать слово «Факс» нужно в первом столбце, т.е. в массиве В5:В10 . Третий аргумент сообщает программе, что результат расчета содержится в третьем столбце массива, т.е. D5:D10 . Четвёртый аргумент равен «ЛОЖЬ», т.е. требуется полное совпадение.

И так, функция получит строку «Факс» из ячейки В2 и будет искать его в массиве В5:В10 сверху вниз. Как только совпадение будет найдено (строка 8), функция вернёт соответствующее значение из столбца D , т.е. содержимое D8 . Именно это нам и требовалось, задача решена.

Если искомое значение не будет найдено, функция вернёт .

Поиск неточного совпадения с помощью ВПР

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

В массиве В5:С12 указаны процентные ставки по кредитам в зависимости от суммы займа. В ячейке В2 Указываем сумму кредита и хотим получить в С2 ставку для такой сделки. Задача сложна тем, что сумма может быть любой и вряд ли будет совпадать с указанными в массиве, поиск по точному совпадению не подходит:

Тогда запишем формулу нестрогого поиска: =ВПР(B2;B5:C12;2;ИСТИНА) . Теперь из всех представленных в столбце В данных программа будет искать ближайшее меньшее. То есть, для суммы 8 000 будет отобрано значение 5000 и выведен соответствующий процент.

Нестрогий поиск ВПР в Excel

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

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

Поиск данных с помощью функции ПРОСМОТР

Функция ПРОСМОТР работает аналогично ВПР, но имеет другой синтаксис. Я использую её, когда таблица данных содержит несколько десятков столбцов и для использования ВПР нужно дополнительно просчитывать номер выводимой колонки. В таких случаях функция ПРОСМОТР облегчает задачу. И так, синтаксис: =ПРОСМОТР(Искомое_значение; Массив_для_поиска; Массив_для_отображения ) :

• Искомое значение – данные или ссылка на данные, которые нужно искать;
• Массив для поиска – одна строка или столбец, в котором ищем аналогичное значение. Данный массив обязательно сортируем по возрастанию;
• Массив для отображения – диапазон, содержащий данные для выведения результатов. Естественно, он должен одного размера с массивом для поиска.

При такой записи вы даёте не относительную ссылку массива результатов. А прямо на него указываете, т.е. не нужно предварительно просчитывать номер выводимого столбца. Используем функцию ПРОСМОТР в первом примере для функции ВПР (основные средства, инвентарные номера): =ПРОСМОТР(B2;B5:B10;D5:D10) . Задача успешно решена!

Функция «ПРОСМОТР» в Microsoft Excel

Поиск по относительным координатам. Функции ПОИСКПОЗ и ИНДЕКС

Еще один способ поиска данных – комбинирование функций ПОИСКПОЗ и ИНДЕКС.

Первая из них, служит для поиска значения в массиве и получения его порядкового номера: ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив; [ Тип сопоставления ] ). Аргументы функции:

• Искомое значение – обязательный аргумент
• Просматриваемый массив – одна строка или столбец, в котором ищем совпадение. Обязательный аргумент
• Тип сопоставления – укажите «0» для поиска точного совпадения, «1» — ближайшее меньшее, «-1» — ближайшее большее. Поскольку функция проводит поиск с начала списка в конец, при поиске ближайшего меньшего – отсортируйте столбец поиска по убыванию. А при поиске большего – сортируйте его по возрастанию.

Позиция необходимого значения найдена, теперь можно вывести его на экран с помощью функции ИНДЕКС(Массив; Номер_строки; [Номер_столбца] ) :

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

Теперь скомбинируем эти функции, чтобы получить результат:

Функции ПОИСКПОЗ и ИНДЕКС в Эксель

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

Чувствуете, как растут и крепчают Ваши знания и умения? Тогда не останавливайтесь, продолжайте читать ! В следующем посте мы будем рассматривать : будет сложно и интересно!