ПОИСКПОЗ в Excel
Microsoft Excel – мощный аналитический инструмент и средство для работы с таблицами. Изучив доступные функции и команды, вы сможете выполнять сложные операции и оптимизировать большие базы данных. В данной инструкции пойдет речь о функции ПОИСКПОЗ, ее назначении и комбинациях с другими командами.
ПОИСКПОЗ – функция для определения позиции значения относительно выбранного диапазона ячеек или таблицы. После ввода формулы данная функция автоматически ищет совпадения в указанном массиве. Если совпадения по заданным параметрам найдены, то ПОИСКПОЗ возвращает номер позиции.
На английском написание команды в «Эксель» выглядит как MATCH. Чаще всего используется в качестве вспомогательной функции вкупе с ИНДЕКС, ВПР, ВЫБОР и другими. Однако мы рассмотрим и одиночное использование.
Как сделать поиск в excel 2010?
- 1 или без указания данного параметра – определяет наибольшее значение, которое максимально приближено к указанному в формуле;
- 0 – первое значение в диапазоне, которое равно искомому;
- -1 – наименьшее значение относительно искомого.
Найти решение задачи можно тремя способами. Во-первых, вручную перебирать параметры, пока не найдется оптимальное соотношение. Во-вторых, составить уравнение с большим количеством неизвестных. В-третьих, вбить данные в Excel и использовать «Поиск решений». Последний способ самый быстрый и покажет максимально точное решение, если знать, как использовать функцию.
Расширенный поиск
Предположим, что требуется найти все значения в диапазоне от 3000 до 3999. В этом случае в строке поиска следует набрать 3. Подстановочный знак «?» заменяет собой любой другой.
Анализируя результаты произведённого поиска, можно отметить, что, наряду с правильными 9 результатами, программа также выдала неожиданные, подчёркнутые красным. Они связаны с наличием в ячейке или формуле цифры 3.
Можно удовольствоваться большинством полученных результатов, игнорируя неправильные. Но функция поиска в эксель 2010 способна работать гораздо точнее. Для этого предназначен инструмент «Параметры» в диалоговом окне.
Щёлкнув «Параметры», пользователь получает возможность осуществлять расширенный поиск. Прежде всего, обратим внимание на пункт «Область поиска», в котором по умолчанию выставлено значение «Формулы».
Это означает, что поиск производился, в том числе и в тех ячейках, где находится не значение, а формула. Наличие в них цифры 3 дало три неправильных результата. Если в качестве области поиска выбрать «Значения», то будет производиться только поиск данных и неправильные результаты, связанные с ячейками формул, исчезнут.
Для того чтобы избавиться от единственного оставшегося неправильного результата на первой строчке, в окне расширенного поиска нужно выбрать пункт «Ячейка целиком». После этого результат поиска становимся точным на 100%.
Такой результат можно было бы обеспечить, сразу выбрав пункт «Ячейка целиком» (даже оставив в «Области поиска» значение «Формулы»).
Если вместо установленного по умолчанию «На листе» выбрать значение «В книге», то нет необходимости находиться на листе искомых ячеек. На скриншоте видно, что пользователь инициировал поиск, находясь на пустом листе 2.
Следующий пункт окна расширенного поиска – «Просматривать», имеющий два значения. По умолчанию установлено «по строкам», что означает последовательность сканирования ячеек по строкам. Выбор другого значения – «по столбцам», поменяет только направление поиска и последовательность выдачи результатов.
При поиске в документах Microsoft Excel, можно использовать и другой подстановочный знак – «*». Если рассмотренный «?» означал любой символ, то «*» заменяет собой не один, а любое количество символов. Ниже представлен скриншот поиска по слову Louisiana.
Иногда при поиске необходимо учитывать регистр символов. Если слово louisiana будет написано с маленькой буквы, то результаты поиска не изменятся. Но если в окне расширенного поиска выбрать «Учитывать регистр», то поиск окажется безуспешным. Программа станет считать слова Louisiana и louisiana разными, и, естественно, не найдёт первое из них.
Поиск решения в Excel: пример использования функции для решения задачи с неизвестными параметрами
Подобным образом функция работает и для текстовых значений. Видим таблицу с месяцами года. Допустим, хотим узнать расположение августа относительно данного массива. Так же указываем ссылку на ячейку с названием месяца, затем выделяем массив и добавляем тип сопоставления. Если речь идет о текстовых значениях, то необходимо ставить 0.
Пример использования поиска решений
1) Чтобы включить «Поиск решений», выполните следующие шаги:
- нажмите «Параметры Excel», а затем выберите категорию «Надстройки»;
- в поле «Управление» выберите значение «Надстройки Excel» и нажмите кнопку «Перейти»;
- в поле «Доступные надстройки» установите флажок рядом с пунктом «Поиск решения» и нажмите кнопку ОК.
2) Теперь упорядочим данные в виде таблицы, отражающей связи между ячейками. Советуем использовать цветовые обозначения: на примере красным выделена целевая функция, бежевым — ограничения, а желтым — изменяемые ячейки.
Не забудьте ввести формулы. Стоимость заказа рассчитывается как «Оплата труда за 1 изделие» умножить на «Число заготовок, передаваемых в работу». Для того, чтобы узнать «Время на выполнение заказа», нужно «Число заготовок, передаваемых в работу» разделить на «Производительность».
3) Выделите целевую ячейку, которая должна показать максимум, минимум или определенное значение при заданных условиях. Для этого на панели нажмите «Данные» и выберете функцию «Поиск решений» (обычно она в верхнем правом углу).
4) Заполните параметры «Поиска решений» и нажмите «Найти решение».
Совокупная стоимость 1000 изделий рассчитывается как сумма стоимостей количества изделий от каждого работника. Данная ячейка (Е13) — это целевая функция. D9:D12 — изменяемые ячейки. «Поиск решений» определяет их оптимальные значения, чтобы целевая функция достигла минимума при заданных ограничениях.
5) В конце проверьте полученные данные на соответствие заданному целевому значению. Если что-то не сходится — нужно пересмотреть исходные данные, введенные формулы и ограничения.
Хотите научиться решать задачи в Excel, как это делают в компаниях-лидерах? Приходите на наш онлайн-курс, на котором вы освоите этот инструмент на уровне профи. Вашими преподавателями будут эксперты-практики, а после обучения вы сможете дополнить резюме весомой строчкой. Регистрируйтесь!
Как в Excel найти нужное слово
Как видно, в нижней части диалогового окна появились результаты поиска. Найденные значения подчёркнуты красным в таблице. Если вместо «Найти все» щёлкнуть «Найти далее», то сначала будет произведён поиск первой ячейки с этим значением, а при повторном щелчке – второй.
Поисковая функция в Excel
Поисковая функция в программе Microsoft Excel предлагает возможность найти нужные текстовые или числовые значения через окно «Найти и заменить». Кроме того, в приложении имеется возможность расширенного поиска данных.
Способ 1: простой поиск
Простой поиск данных в программе Excel позволяет найти все ячейки, в которых содержится введенный в поисковое окно набор символов (буквы, цифры, слова, и т.д.) без учета регистра.
Поиск и выдача результатов производится построчно. Сначала обрабатываются все ячейки первой строки. Если данные отвечающие условию найдены не были, программа начинает искать во второй строке, и так далее, пока не отыщет удовлетворительный результат.
Для того, чтобы перейти к следующему результату, опять нажмите кнопку «Найти далее».
Так можно продолжать до тех, пор, пока отображение результатов не начнется по новому кругу.
Способ 2: поиск по указанному интервалу ячеек
Если у вас довольно масштабная таблица, то в таком случае не всегда удобно производить поиск по всему листу, ведь в поисковой выдаче может оказаться огромное количество результатов, которые в конкретном случае не нужны. Существует способ ограничить поисковое пространство только определенным диапазоном ячеек.
- Выделяем область ячеек, в которой хотим произвести поиск.
- Набираем на клавиатуре комбинацию клавиш Ctrl+F, после чего запуститься знакомое нам уже окно «Найти и заменить». Дальнейшие действия точно такие же, что и при предыдущем способе. Единственное отличие будет состоять в том, что поиск выполняется только в указанном интервале ячеек.
Способ 3: Расширенный поиск
Как уже говорилось выше, при обычном поиске в результаты выдачи попадают абсолютно все ячейки, содержащие последовательный набор поисковых символов в любом виде не зависимо от регистра.
Но, как отсечь такие, и другие заведомо неприемлемые результаты выдачи поиска? Именно для этих целей существует расширенный поиск Excel.
- После открытия окна «Найти и заменить» любым вышеописанным способом, жмем на кнопку «Параметры».
- В окне появляется целый ряд дополнительных инструментов для управления поиском. По умолчанию все эти инструменты находятся в состоянии, как при обычном поиске, но при необходимости можно выполнить корректировку.
По умолчанию, поиск производится только на активном листе Excel. Но, если параметр «Искать» вы переведете в позицию «В книге», то поиск будет производиться по всем листам открытого файла.
В параметре «Просматривать» можно изменить направление поиска. По умолчанию, как уже говорилось выше, поиск ведется по порядку построчно. Переставив переключатель в позицию «По столбцам», можно задать порядок формирования результатов выдачи, начиная с первого столбца.
Ещё более точно поиск можно задать, нажав на кнопку «Формат».
При этом открывается окно формата ячеек. Тут можно установить формат ячеек, которые будут участвовать в поиске. Можно устанавливать ограничения по числовому формату, по выравниванию, шрифту, границе, заливке и защите, по одному из этих параметров, или комбинируя их вместе.
Если вы хотите использовать формат какой-то конкретной ячейки, то в нижней части окна нажмите на кнопку «Использовать формат этой ячейки…».
После этого, появляется инструмент в виде пипетки. С помощью него можно выделить ту ячейку, формат которой вы собираетесь использовать.
После того, как формат поиска настроен, жмем на кнопку «OK».
Бывают случаи, когда нужно произвести поиск не по конкретному словосочетанию, а найти ячейки, в которых находятся поисковые слова в любом порядке, даже, если их разделяют другие слова и символы. Тогда данные слова нужно выделить с обеих сторон знаком «*». Теперь в поисковой выдаче будут отображены все ячейки, в которых находятся данные слова в любом порядке.
Мы рады, что смогли помочь Вам в решении проблемы.
Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
Значительная часть задач, которые решаются с помощью электронных таблиц, предполагают, что для обнаружения нужного результата у пользователя уже есть хоть какие-то исходные данные. Однако Exсel 2010 располагает необходимыми инструментами, с помощью которых можно решить эту задачу наоборот – подобрать нужные данные, чтобы получить необходимый результат.
«Поиск решения» и является одним из таких инструментов, максимально удобных для «задач оптимизации». И если ранее вам еще не приходилось его использовать, то сейчас самое время исправить это.
Итак – начинаем с установки данной надстройки (поскольку самостоятельно она не появится). К счастью сейчас сделать это можно достаточно просто и быстро – открываем меню «Сервис», а уже в нем «Надстройки»
Останется только в графе «Управление» указать «Надстройки Excel», а после нажать кнопочку «Перейти».
После этого несложного действия кнопка активации «Поиска решения» будет отображаться в «Данных». Как и показано на картинке
Давайте рассмотрим, как правильно используется поиск решений в Excel 2010, на нескольких простых примерах.
Допустим, что вы занимаете пост начальника крупного отдела производства и необходимо правильно распределить премии сотрудникам. Допустим, общая сумма премий составляет 100 000 рублей, и необходимо, чтобы премии были пропорциональны окладам.
То есть, сейчас нам необходимо подобрать правильный коэффициент пропорциональности, чтобы определить размер премии относительно оклада.
Теперь можно активировать «Поиск решений». Откроется новое окошко, в котором нам необходимо указать необходимые параметры.
Под «» обозначена наша целевая ячейка. Она может быть только одна.
«» — это возможные варианты оптимизации. Всего можно выбрать «Максимальное», «Минимальное» или «Конкретное» возможные значения. И если вам необходимо именно конкретное значение, то его нужно указать в соответствующей графе.
«» — изменяемых ячеек может быть несколько (целый диапазон или же отдельно указанные адреса). Ведь именно с ними и будет работать Excel, перебирая варианты так, чтобы получилось значение, заданное в целевой ячейке.
«» — Если понадобиться задать ограничения, то стоит воспользоваться кнопкой «Добавить», но мы это рассмотрим чуть позже.
«» — кнопка перехода к интерактивным вычислениям на основе заданной нами программы.
Но теперь вернемся к возможности изменять наше задание, воспользовавшись кнопкой «Добавить». Данный этап является довольно ответственным (не менее чем построение формул), поскольку именно ограничение позволяют получить правильный результат на выходе. Здесь все сделано максимально удобно, так что задать их вы сможете не только для всего диапазона сразу, но и для определенных ячеек.
Для этого можно использовать ряд определенных (и знакомых всем пользователям Excel 2010) знаков «=», «>=», «
ПОИСКПОЗ в Excel: примеры и пошаговая инструкция
Можно удовольствоваться большинством полученных результатов, игнорируя неправильные. Но функция поиска в эксель 2010 способна работать гораздо точнее. Для этого предназначен инструмент «Параметры» в диалоговом окне.
Векторная форма функции Просмотр
Предприятию выдается кредит. В зависимости от суммы кредита начисляется процент за использование кредита (таблица Шкала). Сумма возврата складывается из суммы кредита и суммы процента за пользование кредитом. Величина процента за использование кредита рассчитывается с помощью функции Просмотр() (см. рис.26).
Для расчета процента за использование кредита для Предприятия1 вводится формула:
ПРОСМОТР(искомое_значение;вектор_просмотра;вектор_результата)
Искомое_значение — это значение, которое ПРОСМОТР() ищет в векторе просмотра. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой, ссылающимися на значение.
Вектор_просмотра — это интервал, содержащий только одну строку или один столбец. Значения в аргументе Вектор_просмотра могут быть текстом, числами или логическими значениями.
Значения в аргументе Вектор_просмотра должны быть упорядочены, т.е. расположены в порядке возрастания: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР() может вернуть неверный результат. Тексты в нижнем и верхнем регистре считаются эквивалентными.
Вектор_результата— это интервал, содержащий только одну строку или один столбец. Он должен быть того же размера, что и Вектор просмотра.
Если ПРОСМОТР не может найти Искомое_значение, то подходящим считается наибольшее значение в аргументе Вектор_просмотра, которое меньше, чем Искомое_значение.
Если Искомое_значение меньше, чем наименьшее значение в аргументе Вектор_просмотра, то функция ПРОСМОТР возвращает значение ошибки #Н/Д.
Обратить внимание на использование абсолютной адресацией, использование имен диапазонов в формуле.
Форма массива функции ПРОСМОТР() просматривает первую строку или первый столбец массива, находит указанное значение и возвращает значение из аналогичной позиции последней строки или столбца массива.
Эта форма функции ПРОСМОТР используется, когда сравниваемые значения находятся в первой строке или в первом столбце. Если требуется указать положение столбца или строки, следует использовать другую форму функции ПРОСМОТР.
Лучше использовать функции ГПР() или ВПР() вместо функции ПРОСМОТР(), используемой в форме массива.
Искомое_значение — это значение, которое ПРОСМОТР ищет в массиве. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой, ссылающимися на значение.
Если ПРОСМОТР() не может найти искомое_значение, то подходящим считается наибольшее значение в аргументе массив, которое меньше, чем искомое_значение.
Если искомое_значение меньше, чем наименьшее значение в первой строке или первом столбце (в зависимости от размерности массива), то функция ПРОСМОТР() возвращает значение ошибки #Н/Д.
Форма массива функции ПРОСМОТР() очень похожа на функции ГПР() и ВПР.() Различие заключается в том, что функция ГПР() ищет искомое_значение в первой строке, функция ВПР() ищет в первом столбце, а функция ПРОСМОТР ищет в соответствии с размерностями массива.
Если ширина массива больше его высоты (то есть имеет больше столбцов чем строк), то функция ПРОСМОТР() ищет искомое_значение в первой строке.
Если массив больше в высоту, чем в ширину (то есть имеет больше строк, чем столбцов), то функция ПРОСМОТР() ищет в первом столбце.
Используя функции ГПР() и ВПР() можно указывать индекс по направлению вниз и вправо, а функция ПРОСМОТР() всегда выбирает последнюю ячейку в строке или столбце.
Примеры функции индекс и поискпоз с несколькими условиями excel
Основное назначение офисной программы Excel – осуществление расчётов. Документ этой программы (Книга) может содержать много листов с длинными таблицами, заполненными числами, текстом или формулами. Автоматизированный быстрый поиск позволяет найти в них необходимые ячейки.
