Поискпоз не работает в excel почему
Добрый день.
Столкнулся с проблемой:
Лист1 - перечень железнодорожных отправок по станциям
Лист2 - перечень станций с кодами (данные были вытянуты из интернета при помощи запросов)
В столбце "Строка" (Лист1) - пытаюсь через
найти соответствующую строку соответствующую коду станции ("Код ст. отпр.")
Видим, что такие станции точно есть, но их не находит.
А вот если на Листе2 стать в соответствующую ячейку и нажать Enter - находит данный номер.
Что это за бред? Как от этого избавиться? Какой-то формат применить? Или форматирование?
Заранее спасибо)
Добрый день.
Столкнулся с проблемой:
Лист1 - перечень железнодорожных отправок по станциям
Лист2 - перечень станций с кодами (данные были вытянуты из интернета при помощи запросов)
В столбце "Строка" (Лист1) - пытаюсь через
найти соответствующую строку соответствующую коду станции ("Код ст. отпр.")
Видим, что такие станции точно есть, но их не находит.
А вот если на Листе2 стать в соответствующую ячейку и нажать Enter - находит данный номер.
Что это за бред? Как от этого избавиться? Какой-то формат применить? Или форматирование?
Заранее спасибо) Karbofox
В столбце "Строка" (Лист1) - пытаюсь через
найти соответствующую строку соответствующую коду станции ("Код ст. отпр.")
Видим, что такие станции точно есть, но их не находит.
А вот если на Листе2 стать в соответствующую ячейку и нажать Enter - находит данный номер.
Что это за бред? Как от этого избавиться? Какой-то формат применить? Или форматирование?
Заранее спасибо) Автор - Karbofox
Дата добавления - 11.08.2014 в 13:21
В столбце B у Вас получаются текстовые значения, а ищет в числовых данных. отсюда и значение Н/Д. alx74
Я пытался Текстовым форматом переиграть этот момент, т.к. номера некоторых станций начинаются с нуля.
Как правильнее всего поступать а таких ситуациях, когда есть текст, который состоит из чисел. И в чем отличия для экселя между "325" числом и "325" текстом?
из текстовой строки, состоящей из цифр, делает снова число. Такой вариант не подходит, т.е. есть числа начинающиеся с нуля.
Как привести данные в обеих таблицах к одному "типу/формату"?
Я пытался Текстовым форматом переиграть этот момент, т.к. номера некоторых станций начинаются с нуля.
Как правильнее всего поступать а таких ситуациях, когда есть текст, который состоит из чисел. И в чем отличия для экселя между "325" числом и "325" текстом?
из текстовой строки, состоящей из цифр, делает снова число. Такой вариант не подходит, т.е. есть числа начинающиеся с нуля.
Как привести данные в обеих таблицах к одному "типу/формату"? Karbofox
Я пытался Текстовым форматом переиграть этот момент, т.к. номера некоторых станций начинаются с нуля.
Как правильнее всего поступать а таких ситуациях, когда есть текст, который состоит из чисел. И в чем отличия для экселя между "325" числом и "325" текстом?
из текстовой строки, состоящей из цифр, делает снова число. Такой вариант не подходит, т.е. есть числа начинающиеся с нуля.
Как привести данные в обеих таблицах к одному "типу/формату"? Автор - Karbofox
Дата добавления - 11.08.2014 в 13:56
А почему их нет в примере?
тогда или вводите в таблицу код станции текстом и ищите обычным ПОИСКПОЗ, или используйте формулу массива
А почему их нет в примере?
тогда или вводите в таблицу код станции текстом и ищите обычным ПОИСКПОЗ, или используйте формулу массива
А почему их нет в примере?
тогда или вводите в таблицу код станции текстом и ищите обычным ПОИСКПОЗ, или используйте формулу массива
Это изначальный пример.
Тут 3 Листа со станциями: 1й в представлении РЖД, 2й в представлении УЗ. И первый и второй были вытянуты с сайтов при помощи запросов.
В данных УЗ какие-то левые пробелы в конце каждой ячейки (видимо для того, чтобы значение оставалось текстом) + коды станций 6-значные (первые 5 значений уникальные, поэтому проблемы тут нет) + всего кодов станций 22 тыс.
В данных РЖД кодов станций 20 тыс.
Лист3 - лист по отправкам. Желтым выделил свой вариант формулы для "извлечения" кода станции.
Как лучше совместить данные двух справочников (разница в 2 тыс . )? Если даже простая сверка по кодам находит кучу несоответствий по одинаковым станциям и кодам? Как изменить данные?
Может подскажете идею другой формулы для получения кода станции? Чтобы можно было простым ПОИСКПОЗ или ВПР находить название из справочника?
Это изначальный пример.
Тут 3 Листа со станциями: 1й в представлении РЖД, 2й в представлении УЗ. И первый и второй были вытянуты с сайтов при помощи запросов.
В данных УЗ какие-то левые пробелы в конце каждой ячейки (видимо для того, чтобы значение оставалось текстом) + коды станций 6-значные (первые 5 значений уникальные, поэтому проблемы тут нет) + всего кодов станций 22 тыс.
В данных РЖД кодов станций 20 тыс.
Лист3 - лист по отправкам. Желтым выделил свой вариант формулы для "извлечения" кода станции.
Как лучше совместить данные двух справочников (разница в 2 тыс . )? Если даже простая сверка по кодам находит кучу несоответствий по одинаковым станциям и кодам? Как изменить данные?
Может подскажете идею другой формулы для получения кода станции? Чтобы можно было простым ПОИСКПОЗ или ВПР находить название из справочника? Karbofox
Как лучше совместить данные двух справочников (разница в 2 тыс . )? Если даже простая сверка по кодам находит кучу несоответствий по одинаковым станциям и кодам? Как изменить данные?
Может подскажете идею другой формулы для получения кода станции? Чтобы можно было простым ПОИСКПОЗ или ВПР находить название из справочника? Автор - Karbofox
Дата добавления - 11.08.2014 в 15:33
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel для Windows Phone 10 Еще. Меньше
Проблема: Нет соответствий
Если вы считаете, что данные есть в электронных таблицах, но поиск поиску по поиску не удается найти, это может быть по причине:
Ячейка содержит непредвиденные символы или скрытые пробелы.
К ячейке применен неправильный формат данных. Например, ячейка содержит числовое значение, но отформатирована как текстовая.
РЕШЕНИЕ.Чтобы удалить непредвиденные символы или скрытые пробелы, используйте функции CLEAN и TRIM соответственно. Кроме того, убедитесь, что ячейки отформатированы как правильные типы данных.
Вы использовали формулу массива, но не нажали клавиши CTRL+SHIFT+ВВОД
При использовании массива в функции ИНДЕКС,НАЙТИВ ИЛИ сочетании этих двух функций необходимо нажать клавиши CTRL+SHIFT+ВВОД. Excel автоматически заключит формулу в фигурные скобки <>. Если вы попытаетесь ввести квадратные скобки самостоятельно, Excel отобразит формулу как текст.
Примечание: Если у вас есть текущая версия Microsoft 365 ,можно просто ввести формулу в выходную ячейку, а затем нажать ввод, чтобы подтвердить формулу как формулу динамического массива. В противном случае формула должна быть введена как формула массива устаревшей: сначала выберем диапазон вывода, введите формулу в ячейку вывода, а затем нажимая CTRL+SHIFT+ВВОД, чтобы подтвердить ее. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Проблема: Несоответствие типа сопоставления и порядка сортировки данных
Если match_type 1 или не указан, значения в lookup_array должны быть в порядке возрастания. Примеры: -2, -1, 0, 1, 2…; А, Б, В…; ЛОЖЬ, ИСТИНА и т. д.
Если match_type -1, значения в lookup_array должны быть упорядочены по убытию.
В следующем примере функция MATCH имеет следующий
=ПОИСКПОЗ(40;B2:B10;-1)
Решение: Измените match_type на 1 или отсортирование таблицы в формате "нисходящее". Затем попробуйте еще раз.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Основное назначение этой функции в том, чтобы искать позицию заданного элемента в наборе значений. Чаще всего она применяется для поиска порядкового номера ячейки в диапазоне, где лежит нужное нам значение.
Синтаксис этой функции следующий:
=ПОИСКПОЗ( Что_ищем ; Где_ищем ; Режим_поиска )
- Что_ищем - это значение, которое надо найти
- Где_ищем - это одномерный диапазон или массив (строка или столбец), где производится поиск
- Режим_поиска - как мы ищем: точно (0), с округлением в большую строну (-1) или в меньшую сторону (1)
Давайте рассмотрим несколько полезных вариантов ее применения на практике.
Точный поиск
Классический сценарий - поиск точного текстового совпадения для нахождения позиции нужного нам текста или числа в списке:
Поиск первой или последней текстовой ячейки
Если в качестве искомого значения задать звездочку, то функция будет искать первую ячейку с текстом и выдавать её позицию. Для поиска последней текстовой ячейки можно изменить третий аргумент Режим_поиска с нуля на минус 1:
Числа и пустые ячейки в этом случае игнорируются.
Поиск ближайшего числа или даты
Если последний аргумент задать равным 1 или -1, то можно реализовать поиск ближайшего наименьшего или наибольшего числа. Таблица при этом обязательно должна быть отсортирована по возрастанию или убыванию соответственно. В общем и целом, это чем-то похоже на интервальный просмотр у функции ВПР (VLOOKUP) , но там возможен только поиск ближайшего наименьшего, а здесь - есть выбор.
Например, нам нужно выбрать генератор из прайс-листа для расчетной мощности в 47 кВт. Если последний аргумент задать равным 1 и отсортировать таблицу по возрастанию, то мы найдем ближайшую наименьшую по мощности модель (Зверь):
Если же третий аргумент равен -1 и таблица отсортирована по убыванию, то мы найдем ближайшую более мощную модель (Бомба):
Связка функций ПОИСКПОЗ и ИНДЕКС
Очень часто функция ПОИСКПОЗ используется в связке с другой крайне полезной функцией - ИНДЕКС (INDEX) , которая умеет извлекать данные из диапазона по номеру строки-столбца, реализуя, фактически, "левый ВПР".
Так, в предыдущем примере получить не номер, а название модели генератора можно очень легко:
Ну, и поскольку Excel внутри хранит и обрабатывает даты как числа, то подобный подход на 100% работает и с датами. Например, мы можем легко определить на каком этапе сейчас находится наш проект:
Принципиальное ограничение функции ПОИСКПОЗ состоит в том, что она умеет искать только в одномерных массивах (т.е. строчке или столбце), но никто не запрещает использовать сразу два ПОИСКПОЗа вложенных в ИНДЕКС, чтобы реализовать двумерный поиск по строке и столбцу одновременно:
Основное назначение этой функции в том, чтобы искать позицию заданного элемента в наборе значений. Чаще всего она применяется для поиска порядкового номера ячейки в диапазоне, где лежит нужное нам значение.
Синтаксис этой функции следующий:
=ПОИСКПОЗ( Что_ищем ; Где_ищем ; Режим_поиска )
- Что_ищем - это значение, которое надо найти
- Где_ищем - это одномерный диапазон или массив (строка или столбец), где производится поиск
- Режим_поиска - как мы ищем: точно (0), с округлением в большую строну (-1) или в меньшую сторону (1)
Давайте рассмотрим несколько полезных вариантов ее применения на практике.
Точный поиск
Классический сценарий - поиск точного текстового совпадения для нахождения позиции нужного нам текста или числа в списке:
Поиск первой или последней текстовой ячейки
Если в качестве искомого значения задать звездочку, то функция будет искать первую ячейку с текстом и выдавать её позицию. Для поиска последней текстовой ячейки можно изменить третий аргумент Режим_поиска с нуля на минус 1:
Числа и пустые ячейки в этом случае игнорируются.
Поиск ближайшего числа или даты
Если последний аргумент задать равным 1 или -1, то можно реализовать поиск ближайшего наименьшего или наибольшего числа. Таблица при этом обязательно должна быть отсортирована по возрастанию или убыванию соответственно. В общем и целом, это чем-то похоже на интервальный просмотр у функции ВПР (VLOOKUP) , но там возможен только поиск ближайшего наименьшего, а здесь - есть выбор.
Например, нам нужно выбрать генератор из прайс-листа для расчетной мощности в 47 кВт. Если последний аргумент задать равным 1 и отсортировать таблицу по возрастанию, то мы найдем ближайшую наименьшую по мощности модель (Зверь):
Если же третий аргумент равен -1 и таблица отсортирована по убыванию, то мы найдем ближайшую более мощную модель (Бомба):
Связка функций ПОИСКПОЗ и ИНДЕКС
Очень часто функция ПОИСКПОЗ используется в связке с другой крайне полезной функцией - ИНДЕКС (INDEX) , которая умеет извлекать данные из диапазона по номеру строки-столбца, реализуя, фактически, "левый ВПР".
Так, в предыдущем примере получить не номер, а название модели генератора можно очень легко:
Ну, и поскольку Excel внутри хранит и обрабатывает даты как числа, то подобный подход на 100% работает и с датами. Например, мы можем легко определить на каком этапе сейчас находится наш проект:
Принципиальное ограничение функции ПОИСКПОЗ состоит в том, что она умеет искать только в одномерных массивах (т.е. строчке или столбце), но никто не запрещает использовать сразу два ПОИСКПОЗа вложенных в ИНДЕКС, чтобы реализовать двумерный поиск по строке и столбцу одновременно:
В зеленом столбце вводятся данные, а в желтом столбце по формуле
В зеленом столбце вводятся данные, а в желтом столбце по формуле
В зеленом столбце вводятся данные, а в желтом столбце по формуле
Проверьте, какая часть вашей формулы работает не корректно и исходя их этого меняйте название темы, оно и так не очень хорошо, а тут еще и совсем невпопад.
Проверьте, какая часть вашей формулы работает не корректно и исходя их этого меняйте название темы, оно и так не очень хорошо, а тут еще и совсем невпопад. bmv98rus
наоборот - у вас в источнике числа, а в зеленый столбец вы ввели текст, вот и не работает прохожий2019
наоборот - у вас в источнике числа, а в зеленый столбец вы ввели текст, вот и не работает Автор - прохожий2019
Дата добавления - 23.04.2020 в 08:20
Да вообще-то - нет.
У меня и там, и там текстовый формат - и все равно не работает.
Да вообще-то - нет.
У меня и там, и там текстовый формат - и все равно не работает. КрасноглазыйПиркаф
Да вообще-то - нет.
У меня и там, и там текстовый формат - и все равно не работает. Автор - КрасноглазыйПиркаф
Дата добавления - 23.04.2020 в 09:47
Pelena, так название темы - это всего пять слов.
Больше не дают написать.
Pelena, так название темы - это всего пять слов.
Больше не дают написать.
либо на листе TDSheet столбец В сделать текстовым. Для этого выделить столбец -- Данные -- Текст по столбцам -- Далее -- Далее -- переключатель Текстовый -- Готово
либо на листе TDSheet столбец В сделать текстовым. Для этого выделить столбец -- Данные -- Текст по столбцам -- Далее -- Далее -- переключатель Текстовый -- Готово Pelena
либо на листе TDSheet столбец В сделать текстовым. Для этого выделить столбец -- Данные -- Текст по столбцам -- Далее -- Далее -- переключатель Текстовый -- Готово Автор - Pelena
Дата добавления - 23.04.2020 в 10:24
C этим надо осторожно, это не касается данных из примера это темы. Например и там и там "1 Май 2020" . IFERROR(--B8,B8) вернет дату и она благополучно не будет найдена среди текста. тут нужно комбинировать, число среди данных и если не нашлось, то снова искать но текст и даже числа тогда надо превращать в текст.
C этим надо осторожно, это не касается данных из примера это темы. Например и там и там "1 Май 2020" . IFERROR(--B8,B8) вернет дату и она благополучно не будет найдена среди текста. тут нужно комбинировать, число среди данных и если не нашлось, то снова искать но текст и даже числа тогда надо превращать в текст. bmv98rus
Замечательный Временно просто медведь , процентов на 20.
C этим надо осторожно, это не касается данных из примера это темы. Например и там и там "1 Май 2020" . IFERROR(--B8,B8) вернет дату и она благополучно не будет найдена среди текста. тут нужно комбинировать, число среди данных и если не нашлось, то снова искать но текст и даже числа тогда надо превращать в текст. Автор - bmv98rus
Дата добавления - 23.04.2020 в 10:44
Подскажите, в чем проблема?
Файлик прилагаю.
Подскажите, в чем проблема?
Файлик прилагаю. kasianjob
Подскажите, в чем проблема?
Файлик прилагаю. Автор - kasianjob
Дата добавления - 08.02.2022 в 21:21
kasianjob, у Вас диапазон в формуле не захватывает все ячейки на листе TDSheet. Увеличьте до 2000 хотя бы, а можно и с запасом, но без фанатизма
kasianjob, у Вас диапазон в формуле не захватывает все ячейки на листе TDSheet. Увеличьте до 2000 хотя бы, а можно и с запасом, но без фанатизма Pelena
Читайте также: