Поискпоз не работает в excel почему

Обновлено: 07.07.2024

Добрый день.
Столкнулся с проблемой:
Лист1 - перечень железнодорожных отправок по станциям
Лист2 - перечень станций с кодами (данные были вытянуты из интернета при помощи запросов)

В столбце "Строка" (Лист1) - пытаюсь через

найти соответствующую строку соответствующую коду станции ("Код ст. отпр.")
Видим, что такие станции точно есть, но их не находит.
А вот если на Листе2 стать в соответствующую ячейку и нажать Enter - находит данный номер.

Что это за бред? Как от этого избавиться? Какой-то формат применить? Или форматирование?
Заранее спасибо)

В столбце "Строка" (Лист1) - пытаюсь через

Что это за бред? Как от этого избавиться? Какой-то формат применить? Или форматирование?
Заранее спасибо) Karbofox

В столбце "Строка" (Лист1) - пытаюсь через

Что это за бред? Как от этого избавиться? Какой-то формат применить? Или форматирование?
Заранее спасибо) Автор - Karbofox
Дата добавления - 11.08.2014 в 13:21

Автор - _Boroda_
Дата добавления - 11.08.2014 в 13:30 В столбце B у Вас получаются текстовые значения, а ищет в числовых данных. отсюда и значение Н/Д. В столбце B у Вас получаются текстовые значения, а ищет в числовых данных. отсюда и значение Н/Д. alx74 В столбце B у Вас получаются текстовые значения, а ищет в числовых данных. отсюда и значение Н/Д.

Я пытался Текстовым форматом переиграть этот момент, т.к. номера некоторых станций начинаются с нуля.
Как правильнее всего поступать а таких ситуациях, когда есть текст, который состоит из чисел. И в чем отличия для экселя между "325" числом и "325" текстом?

Вам так нужно?
[Перевод / Translate]
=ПОИСКПОЗ(--B2;Лист2!A:A;0) из текстовой строки, состоящей из цифр, делает снова число. Такой вариант не подходит, т.е. есть числа начинающиеся с нуля.
Как привести данные в обеих таблицах к одному "типу/формату"? В столбце B у Вас получаются текстовые значения, а ищет в числовых данных. отсюда и значение Н/Д.

Вам так нужно?
[Перевод / Translate]
=ПОИСКПОЗ(--B2;Лист2!A:A;0) из текстовой строки, состоящей из цифр, делает снова число. Такой вариант не подходит, т.е. есть числа начинающиеся с нуля.
Как привести данные в обеих таблицах к одному "типу/формату"? Автор - Karbofox
Дата добавления - 11.08.2014 в 13:56 Такой вариант не подходит, т.е. есть числа начинающиеся с нуля. А почему их нет в примере?
тогда или вводите в таблицу код станции текстом и ищите обычным ПОИСКПОЗ, или используйте формулу массива
Такой вариант не подходит, т.е. есть числа начинающиеся с нуля. А почему их нет в примере?
тогда или вводите в таблицу код станции текстом и ищите обычным ПОИСКПОЗ, или используйте формулу массива
Такой вариант не подходит, т.е. есть числа начинающиеся с нуля. А почему их нет в примере?
тогда или вводите в таблицу код станции текстом и ищите обычным ПОИСКПОЗ, или используйте формулу массива
Автор - _Boroda_
Дата добавления - 11.08.2014 в 14:05

Это изначальный пример.
Тут 3 Листа со станциями: 1й в представлении РЖД, 2й в представлении УЗ. И первый и второй были вытянуты с сайтов при помощи запросов.
В данных УЗ какие-то левые пробелы в конце каждой ячейки (видимо для того, чтобы значение оставалось текстом) + коды станций 6-значные (первые 5 значений уникальные, поэтому проблемы тут нет) + всего кодов станций 22 тыс.
В данных РЖД кодов станций 20 тыс.
Лист3 - лист по отправкам. Желтым выделил свой вариант формулы для "извлечения" кода станции.

Как лучше совместить данные двух справочников (разница в 2 тыс . )? Если даже простая сверка по кодам находит кучу несоответствий по одинаковым станциям и кодам? Как изменить данные?
Может подскажете идею другой формулы для получения кода станции? Чтобы можно было простым ПОИСКПОЗ или ВПР находить название из справочника?

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

Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек и возвращает относительную позицию этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, то формула =ПОИСКПОЗ(25;A1:A3;0) возвращает значение 2, поскольку элемент 25 является вторым в диапазоне.

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

Синтаксис

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

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

Аргумент искомое_значение может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую такое значение.

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

Тип_сопоставления. Необязательный аргумент. Число -1, 0 или 1. Аргумент тип_сопоставления указывает, каким образом в Microsoft Excel искомое_значение сопоставляется со значениями в аргументе просматриваемый_массив. По умолчанию в качестве этого аргумента используется значение 1.

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

Тип_сопоставления

Функция ПОИСКПОЗ находит наибольшее значение, которое меньше или равно значению аргумента искомое_значение. Просматриваемый_массив должен быть упорядочен по возрастанию: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА.

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

Функция ПОИСКПОЗ находит наименьшее значение, которое больше или равно значению аргумента искомое_значение. Просматриваемый_массив должен быть упорядочен по убыванию: ИСТИНА, ЛОЖЬ, Z — A, . 2, 1, 0, -1, -2, . и т. д.

Функция ПОИСКПОЗ возвращает не само значение, а его позицию в аргументе просматриваемый_массив. Например, функция ПОИСКПОЗ("б";а";"б";"в">;0) возвращает 2 — относительную позицию буквы "б" в массиве .

Функция ПОИСКПОЗ не различает регистры при сопоставлении текста.

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

Пример

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

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

Синтаксис этой функции следующий:

=ПОИСКПОЗ( Что_ищем ; Где_ищем ; Режим_поиска )

Что_ищем - это значение, которое надо найти
Где_ищем - это одномерный диапазон или массив (строка или столбец), где производится поиск
Режим_поиска - как мы ищем: точно (0), с округлением в большую строну (-1) или в меньшую сторону (1)

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

Точный поиск

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

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

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

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

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

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

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

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

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

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

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

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

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

Так и в Microsoft Excel: есть несколько похожих функций, про которых фраза "внешность обманчива" работает на 100%. Одна из наиболее многогранных и полезных - функция ИНДЕКС (INDEX) . Далеко не все пользователи Excel про нее знают, и еще меньше используют все её возможности. Давайте разберем варианты ее применения, ибо их аж целых пять.

Вариант 1. Извлечение данных из столбца по номеру ячейки

Самый простой случай использования функции ИНДЕКС – это ситуация, когда нам нужно извлечь данные из одномерного диапазона-столбца, если мы знаем порядковый номер ячейки. Синтаксис в этом случае будет:

=ИНДЕКС( Диапазон_столбец ; Порядковый_номер_ячейки )

Этот вариант известен большинству продвинутых пользователей Excel. В таком виде функция ИНДЕКС часто используется в связке с функцией ПОИСКПОЗ (MATCH) , которая выдает номер искомого значения в диапазоне. Таким образом, эта пара заменяет легендарную ВПР (VLOOKUP) :

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

Вариант 2. Извлечение данных из двумерного диапазона

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

=ИНДЕКС( Диапазон ; Номер_строки ; Номер_столбца )

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

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

Вариант 3. Несколько таблиц

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

=ИНДЕКС( (Диапазон1;Диапазон2;Диапазон3) ; Номер_строки ; Номер_столбца ; Номер_диапазона )

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

Вариант 4. Ссылка на столбец / строку

Если во втором варианте использования функции ИНДЕКС номер строки или столбца задать равным нулю (или просто не указать), то функция будет выдавать уже не значение, а ссылку на диапазон-столбец или диапазон-строку соответственно:

Обратите внимание, что поскольку ИНДЕКС выдает в этом варианте не конкретное значение ячейки, а ссылку на диапазон, то для подсчета потребуется заключить ее в дополнительную функцию, например СУММ (SUM) , СРЗНАЧ (AVERAGE) и т.п.

Вариант 5. Ссылка на ячейку

Общеизвестно, что стандартная ссылка на любой диапазон ячеек в Excel выглядит как Начало-Двоеточие-Конец, например A2:B5. Хитрость в том, что если взять функцию ИНДЕКС в первом или втором варианте и подставить ее после двоеточия, то наша функция будет выдавать уже не значение, а адрес, и на выходе мы получим полноценную ссылку на диапазон от начальной ячейки до той, которую нашла ИНДЕКС:

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

Один из весьма распространенных на практике сценариев применения ИНДЕКС в таком варианте - это сочетание с функцией СЧЁТЗ (COUNTA) , чтобы получить автоматически растягивающиеся диапазоны для выпадающих списков, сводных таблиц и т.д.

Читайте также: