Как найти сходные числа

В материале, переводом которого мы решили поделиться к старту курса о машинном и глубоком обучении, простым языком рассказывается о семантическом поиске, статья охватывает шесть его методов; начиная с простых сходства по Жаккару, алгоритма шинглов и расстояния Левенштейна, автор переходит к поиску с разреженными векторами — TF-IDF и BM25 и заканчивает современными представлениями плотных векторов и Sentence-BERT. Простые примеры сопровождаются кодом и иллюстрациями, а в конце вы найдёте ссылки на соответствующие блокноты Jupyter.

Поиск сходства — одна из самых быстрорастущих областей в ИИ и машинном обучении. По своей сути, это процесс сопоставления релевантных частей информации друг с другом. Велика вероятность, что вы нашли эту статью через поисковую систему — скорее всего, Google. Возможно, вы искали что-то вроде “what is semantic similarity search?” или “traditional vs vector similarity search”. Google обработал ваш запрос и использовал многие основные принципы поиска по сходству, о которых рассказывается в этой статье. 

Если поиск сходства лежит в основе успеха компании стоимостью $ 1,65 Т — пятой по стоимости в мире^[1], есть большой шанс, что о нём стоит узнать больше. Поиск сходства — сложная тема, есть бесчисленное множество методик построения эффективных поисковых систем. В этой статье мы рассмотрим несколько наиболее интересных и мощных из этих методов, уделяя особое внимание семантическому поиску. Посмотрим, как они работают, чем они хороши и как мы можем применять их самостоятельно. Можно посмотреть это видео или прочитать статью.

Два видео о двух классах подходов

Традиционный поиск

Мы начинаем с лагеря традиций и там находим нескольких ключевых игроков:

• Сходство Жаккара.

• Алгоритм шинглов.

• Расстояние Левенштейна.

Все это показатели отлично работают при поиске сходства, из них мы рассмотрим три самых популярных: сходство по Жаккару, алгоритм шинглов и расстояние Левенштейна.

Сходство Жаккара

Сходство Жаккара — это простая, но иногда мощная метрика сходства. Даны две последовательности A и B: находим число общих элементов в них и делим найденное число на количество элементов обеих последовательностей.

Пример: даны две последовательности целых чисел, запишем:

Здесь мы определили два общих неодинаковых целых числа, 3 и 4 — между двумя последовательностями с общим количеством десяти целых чисел в обеих, где восемь чисел  являются уникальными значениями — 2/8 даёт нам оценку сходства по Жаккарду 0,25. Ту же операцию можно выполнить и для текстовых данных: всё, что мы делаем, — заменяем целые числа на токены.

Мы обнаружили, что, как и ожидалось, предложения b и c набрали гораздо больше баллов. Конечно, это не лучшее решение — два предложения, в которых нет ничего общего, кроме слов ‘the’, ‘a’, ‘is’ и т. д., могут получить высокие оценки Жаккара, несмотря на то, что они неодинаковы по смыслу. Эти недостатки могут быть частично устранены с помощью методов предварительной обработки, таких как удаление стоп-слов, стемминг/лемматизация и т. д. Однако, как мы скоро увидим, некоторые методы позволяют полностью избежать этих проблем.

Алгоритм шинглов

Другая похожая техника — алгоритм использует точно такую же логику пересечения/объединения — но с “шинглом”. 2-шингловое предложение a будет выглядеть так:

a = {'his thought', 'thought process', 'process is', ...}

После можно применить тот же расчёт пересечения/объединения между разбитыми на шинглы предложениями:

При помощи двух шинглов находим три совпадающих шингла между предложениями b и c, в результате сходство составляет 0,125.

Расстояние Левенштейна

Другая популярная метрика сравнения двух строк — расстояние Левенштейна — это количество операций, необходимых для преобразования одной строки в другую, вот его формула:

На вид она довольно сложна; если вы её поняли, это прекрасно! Если нет, не волнуйтесь — мы разберём её. Переменные a и b представляют две наши строки, i и j — позиции символов в a и b соответственно. Итак, даны строки:

Мы найдём, что:

Это легко! Прекрасный способ понять логику этой формулы — визуализировать алгоритм Вагнера — Фишера, рассчитывающий расстояние Левенштейна при помощи простой матрицы. Мы берём два слова a и b и размещаем их по обеим осям нашей матрицы, включая символ ничего как пустое пространство:

Код инициализации пустой матрицы Вагнера — Фишера:

Переберём все позиции в матрице и применим ту сложную формулу, которую видели ранее. Первый шаг в нашей формуле if min(i, j) = 0 — здесь мы уточняем, не равны ли i и j нулю? Если да, переходим к функции max(i, j), которая присваивает текущей позиции в нашей матрице больше из двух значений — i и j:

Код операций min(i, j) == 0, за которой следует визуализированная выше max(i, j).

С внешними краями нашей матрицы мы разобрались, но нам всё ещё нужно вычислить внутренние значения — именно там будет проходить оптимальный путь. Вернёмся к if min(i, j) = 0 — что, если ни одно из них не равно 0? Затем переходим к сложной части уравнения в разделе min {. Нам нужно вычислить значение для каждой строки, а после взять минимальное значение. Сейчас эти значения известны — они находятся в нашей матрице:

lev(i-1, j), а все остальные операции — это операции индексации, когда мы извлекаем значение в данной позиции. Затем берём минимальное значение из трёх. Остаётся только одна операция. +1 слева должно применяться только в том случае, если a[i] != b[i] — это штраф за несовпадение символов.

Эта операция в цикле по всей матрице выглядит так:

Полная реализация расчёта расстояния Левенштейна при помощи матрицы Вагнера — Фишера.

Итак, мы вычислили каждое значение в матрице — эти значения представляют собой количество операций, необходимых для преобразования из строки a до позиции i в строку b до позиции j. Мы ищем количество операций для преобразования a в b, поэтому мы берём правое нижнее значение нашего массива lev[-1, -1].

Векторное сходство

Для поиска на основе вектора мы обычно используем один из нескольких методов построения векторов:

• TF-IDF.

• BM25.

• word2vec/doc2vec.

• BERT.

• USE.

В тандеме с некоторыми реализациями приблизительных ближайших соседей (ANN) эти векторные методы в мире поиска сходства, если говорить терминами программирования, — минимально жизнеспособные продукты. Рассмотрим подходы на основе TF-IDF, BM25 и BERT, поскольку они являются наиболее распространёнными и охватывают как разреженные, так и плотные векторные представления.

1. TF-IDF

Почтенный дедушка векторного поиска сходства, родившийся ещё в 1970-х годах. Он состоит из двух частей: Term Frequency (TF) и Inverse Document Frequency (IDF). Компонент TF подсчитывает, сколько раз термин встречается в документе, и делит его на общее количество терминов этого же документа.

Это первая половина нашего расчёта, мы имеем частоту запроса в рамках текущего документа f(q, D) в числителе и частоту всех терминов в рамках текущего документа f(t, D) в знаменатели дроби. TF — хороший показатель, но не позволяет нам провести различие между распространёнными и необычными словами. Если бы мы искали слово “the”, используя только TF, мы бы присвоили этому предложению ту же релевантность, что и при поиске “bananas”. Это хорошо, пока мы не начнём сравнивать документы или искать что-то с помощью длинных запросов. Мы не хотим, чтобы такие слова, как ‘the’, ‘is’ или ‘it’, были оценены так же высоко, как ‘bananas’ или ‘street’.

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

Здесь три предложения. Вычислив IDF для нашего распространённого слова ‘is’, получаем гораздо меньшее число, чем в случае более редкого слова ‘forest’. Если бы после мы искали оба слова ‘is’ и ‘forest’, то объединили бы TF и IDF следующим образом:

Мы вычисляем оценки TF(‘is’, D) и TF(‘forest’, D) для документов a, b и c. Значение IDF относится ко всем документам, поэтому IDF(‘is’) и IDF(‘forest’) вычисляются только один раз. Затем мы получаем значения TF-IDF для обоих слов в каждом документе путём умножения компонентов TF и IDF. Предложение a набирает наибольшее количество баллов для ‘forest’, а ‘is’ всегда набирает 0, поскольку IDF(‘is’) равно 0.

Это замечательно, но где здесь применяется векторное сходства? Итак, мы берём наш словарный запас (большой список всех слов в нашем наборе данных) и вычисляем TF-IDF для каждого слова.

Чтобы сформировать векторы TF-IDF, мы можем собрать это воедино:

Отсюда мы получаем вектор TF-IDF. Стоит отметить, что словарь легко может иметь более 20 000 слов, поэтому созданные этим методом векторы невероятно разрежены, а это означает, что закодировать какой-либо семантический смысл мы не можем.

2. BM25

Преемник TF-IDF, Okapi BM25, является результатом оптимизации TF-IDF в первую очередь для нормализации результатов на основе длины документа. TF-IDF — отличный инструмент, но он может давать сомнительные результаты, когда мы начинаем сравнивать несколько упоминаний. Если мы возьмём две статьи по 500 слов и обнаружим, что  “Черчилль” в статье А упоминается 6 раз, а в статье Б — 12 раз, должны ли мы считать статью А в два раза менее релевантной? Скорее всего, нет. BM25 решает эту проблему путём модификации TF-IDF:

Уравнение выглядит довольно неприятно, но это не что иное, как наша формула TF-IDF с несколькими новыми параметрами! Давайте сравним два компонента TF:

И затем часть IDF, которая даже не вводит никаких новых параметров — она просто переставляет IDF из TF-IDF.

Итак, каков результат этой модификации? Если взять последовательность из 12 лексем и постепенно подавать в неё всё больше и больше “подходящих” лексем, то получим следующие результаты:

Показатель TF-IDF линейно увеличивается с ростом числа релевантных лексем. Таким образом, если частота удваивается — увеличивается и показатель TF-IDF. Звучит круто! Но как реализовать это на Python? Опять же напишем простую и понятную реализацию, как с TF-IDF.

Мы использовали параметры по умолчанию для k и b, и наши результаты выглядят многообещающе. Запрос ‘purple’ соответствует только предложению a, а ‘bananas’ набирает приемлемые баллы и для b, и для c, но для c — немного выше благодаря меньшему количеству слов. Чтобы построить векторы на основе этих данных, сделаем то же самое, что и с TF-IDF.

Опять же, как и в случае с векторами TF-IDF, эти векторы разрежены. Мы не сможем кодировать семантическое [смысловое] значение, вместо этого сосредоточимся на синтаксисе. 

Теперь давайте посмотрим, как можно начать рассматривать семантику.

3. BERT

BERT (Bidirectional Encoder Representations from Transformers) — это чрезвычайно популярная модель трансформатора, в NLP она используется почти для всего. Посредством 12 (или около того) уровней кодера BERT кодирует огромное количество информации в набор плотных векторов. 

Каждый плотный вектор обычно содержит 768 значений, и мы обычно имеем 512 таких векторов для каждого закодированного BERT предложения. Эти векторы содержат то, что мы можем рассматривать как числовые представления языка. Эти векторы также возможно извлечь (если этого захочется) из разных слоёв, но обычно из последнего слоя. Теперь, имея два правильно закодированных плотных вектора, мы можем использовать метрику сходства, такую как косинусное сходство, чтобы рассчитать теперь уже семантическое сходство. Более выровненные векторы семантически схожи, и наоборот.

Но есть одна проблема: каждая последовательность представлена 512 векторами — не одним вектором. Вот здесь-то и появляется ещё одна — блестящая — адаптация BERT. Sentence-BERT позволяет нам создать один вектор, который представляет нашу полную последовательность, иначе известную как вектор предложений^[2]. У нас есть два способа реализации SBERT: простой способ с использованием библиотеки sentence-transformers или уже не столько простой способ с использованием transformers и PyTorch. 

Рассмотрим оба варианта, начиная с подхода через transformers и PyTorch, чтобы получить интуитивное представление о построении векторов. Если вы уже использовали библиотеку трансформаторов HF, первые несколько шагов покажутся вам очень знакомыми: инициализируем SBERT и токенизатор, токенизируем наш текст и обработаем токены моделью.

Мы добавили сюда новое предложение, предложение g несёт тот же семантический смысл, что и b, — без тех же ключевых слов. Из-за отсутствия общих слов все наши предыдущие методы не смогли бы найти сходство между этими двумя последовательностями — запомните это на будущее.

У нас есть векторы длины 768, но это не векторы предложений, так как мы имеем векторное представление каждой лексемы в последовательности (поскольку мы работаем SBERT, их здесь 128, а в случае BERT их 512). Для создания вектора предложений нам необходимо выполнить вычислить среднее значение.

Первое, что мы делаем, — умножаем каждое значение в тензоре embeddings на соответствующее значение attention_mask. Маска attention_mask содержит единицы там, где у нас есть “настоящие токены” (например, не токен заполнения), и нули в других местах — эта операция позволяет нам игнорировать ненастоящие токены.

И вот наши векторы предложений, при помощи которых мы можем измерить сходство, вычислив косинусное сходство между всеми векторами.

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

Теперь вспомните замечание о том, что предложения b и g имеют по сути одинаковый смысл, но не имеют ни одного одинакового ключевого слова. Мы надеемся, что SBERT с его превосходными семантическими представлениями языка определит эти два предложения как похожие — и вот, пожалуйста, сходство между ними составляет 0,66 балла (обведено выше). Итак, альтернативный (простой) подход заключается в применении SBERT. Чтобы получить точно такой же результат, как выше, напишем:

Что, конечно, гораздо проще. На этом завершаем прогулку по истории с Жаккаром, Левенштейном и Bert!

Ссылки

Блокноты Colab

Эта статья — живая иллюстрация развития технологий анализа текста и искусственного интеллекта. Если вы не хотите оставаться в стороне от области ИИ, то вы можете обратить внимание на наш курс «Machine Learning и Deep Learning». Или же прокатайтесь в работе с данными на нашем курсе о Data Science.

Узнайте, как прокачаться и в других специальностях или освоить их с нуля:

• Профессия Data Scientist

• Профессия Data Analyst

• Курс по Data Engineering

Другие профессии и курсы

Математическое совпадение — ситуация, когда два выражения дают почти одинаковые значения, хотя теоретически это совпадение никак объяснить нельзя. Например, существует близость круглого числа 1000, выраженного как степень 2 и как степень 10: . Некоторые математические совпадения используется в инженерном деле, когда одно выражение используется как приближение другого.

Введение[править | править код]

Математическое совпадение часто связано с целыми числами, и удивительные («случайные») примеры отражают факт, что вещественные числа, возникающие в некоторых контекстах, оказываются по некоторым стандартам «близкой» аппроксимацией малых целых чисел или степени десятки, или, более общо, рационального числа с малым знаменателем. Другой вид математических совпадений, таких как целые числа, одновременно удовлетворяющие нескольким, внешне не связанным, критериям или совпадения, относящиеся к единицам измерения. В классе чисто математических совпадений некоторые простые результаты имеют глубокое математическое основание, в то время как другие появляются «нежданно-негаданно».

Если дано счётное число путей образования математических выражений, использующих конечное число символов, совпадение числа используемых символов и точности приближения может быть наиболее очевидным путём получения математического совпадения. Стандарта, однако, нет и сильный закон малых чисел^[en] является видом аргумента, к которому прибегают, когда нет формального математического понимания. Необходимо некоторое эстетическое математическое чувство для вынесения решения о значении математического совпадения, является ли это исключительным явлением, либо это важный математический факт (например, постоянная Рамануджана^[en] ниже о константе, которая появилась в печати несколько лет назад как научная первоапрельская шутка^[1]). Подводя итог, эти случайные совпадения рассматриваются из-за их курьёзности или для ободрения любителей математики на элементарном уровне.

Некоторые примеры[править | править код]

Рациональные приближения[править | править код]

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

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

Некоторые совпадения с числом :

• первая подходящая дробь числа , [3; 7] = 22/7 = 3,1428…, известна с времён Архимеда ^[3], и даёт точность около 0,04 %. Третья подходящая дробь, [3; 7, 15, 1] = 355/113 = 3,1415929…, которую нашёл Цзу Чунчжи ^[4], верна до шести десятичных знаков^[3]. Эта высокая точность получается из-за того, что следующий член непрерывной дроби имеет необычно большое значение: = [3; 7, 15, 1, 292, …]^[5];
• совпадение, в котором участвует и золотое сечение φ, задаётся формулой . Это соотношение связано с треугольником Кеплера; некоторые исследователи считают, что это совпадение найдено в пирамидах Гизы, но крайне невероятно, что оно является преднамеренным^[6];
• существует последовательность шести девяток, которая начинается с 762-й позиции десятичного представления числа . Для случайно выбранного нормального числа вероятность любой выбранной последовательности шести цифр (например, 658 020) встречается редко в десятичном представлении, только 0,08 %. Есть гипотеза, что является нормальным числом, но это не доказано;
• ; верно с точностью до 0,002 %.

Совпадения с числом :

Также широко используется совпадение , верное с точностью 2,4 %. Рациональное приближение , или совпадает с точностью до 0,3 %. Это совпадение используется в инженерных раcчётах для приближения удвоенной мощности как 3 децибела (фактическое значение равно 3,0103 dB — точка половинной мощности^[en]), либо для перевода кибибайтов в килобайты ^[9][10]. Это же совпадение можно переписать как (исключаем общий множитель , так что относительная погрешность остаётся той же самой, 2,4 %), что соответствует рациональному приближению , или (также в пределах 0,3 %). Это совпадение используется, например, для установки выдержки в камерах как приближение степеней двойки (128, 256, 512) в последовательности выдержек 125, 250, 500, и так далее^[2].

Совпадения с музыкальными интервалами[править | править код]

Совпадение , обычно используется в музыке при настройке 7 полутонов равномерно темперированного строя в чистую квинту натурального строя: , что совпадает с точностью до 0,1 %. Чистая квинта служит основой пифагорова строя и является наиболее распространённой системой в музыке. Из вытекающей аппроксимации следует, что квинтовый круг завершается на семь октав выше начала^[2].

Совпадение приводит к рациональной версии 12-TET ладов, как заметил Иоганн Кирнбергер.

Совпадение приводит к рациональной версии темперации среднетонового строя на 1/4 коммы.

Совпадение ведёт к очень маленькому интервалу (около миллицента).

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

Числовые выражения[править | править код]

Выражения со степенями :

;

;

;

;

Некоторые правдоподобные связи выполняются с высокой степени точности, но тем не менее остаются совпадениями. Примером служит:

.

Две стороны этого выражения отличаются лишь в 42-м десятичном знаке^[15].

Выражения со степенями и :

Выражения с , и 163:

Выражение с логарифмами:

• (точность 0,00024 %).

В обсуждении парадокса дней рождения возникает число , которое «забавно» равно с точностью до 4 знаков^[19].

Числовые совпадения в физическом мире[править | править код]

Длина шести недель[править | править код]

Число секунд в шести неделях, или 42 днях, равно в точности 10! (факториал) секунд (так как , и ). Многие заметили это совпадение, в частности, число 42 имеет важное значение в романе Дугласа Адамса «Автостопом по галактике».

Скорость света[править | править код]

Скорость света (по определению) равна в точности
299 792 458 м/с, очень близко к 300 000 000 м/с. Это чисто совпадение, поскольку метр был первоначально определён как 1/10 000 000 расстояния между земным полюсом и экватором на уровне моря, длина земной окружности получилась около 2/15 световой секунды^[20].

Гравитационное ускорение[править | править код]

Не являясь константной, а зависящей от широты и долготы, числовое значение ускорение свободного падения на поверхности лежит между 9,74 и 9,87, что достаточно близко к 10. Это означает, что в результате второго закона Ньютона вес килограмма массы на земной поверхности Земли соответствует примерно 10 ньютонам приложено на объект силы ^[21].

Это совпадение на самом деле связано с вышеупомянутым совпадением квадрата с 10. Одно из ранних определений метра — длина маятника, период колебания которого равна двум секундам. Поскольку период полного колебания примерно задаётся формулой ниже, после алгебраических выкладок, получим, что гравитационная постоянная равна квадрату ^[22]

Когда было обнаружено, что длина окружности Земли очень близка 40 000 000 метрам, определение метра было изменено, чтобы отразить этот факт, поскольку это был более объективный стандарт (гравитационная постоянная на поверхности Земли не постоянна). Это привело к увеличению длины метра чуть меньше чем на 1 %, что попадало в пределы экспериментальных ошибок измерения.

Ещё одно совпадение — что величина g, равная примерно 9,8 м/с², равна 1,03 светового года/год², что близко к 1. Это совпадение связано с фактом, что g близко к 10 в системе единиц СИ (м/с²), как упоминалось выше, вместе с фактами, что число секунд в году близко к численному значению c/10, где c — скорость света в м/с.

Константа Ридберга[править | править код]

Постоянная Ридберга, умноженная на скорость света и выраженная как частота, близка к Гц:^[20]

Гц ^[23].

Постоянная тонкой структуры[править | править код]

Постоянная тонкой структуры близка к и была гипотеза, что она в точности равна .

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

См. также[править | править код]

• Треугольник Кеплера#Математическое совпадение
• Формула Коидэ

Примечания[править | править код]

Литература[править | править код]

• Martin Gardner. Six Sensational Discoveries // The Colossal Book of Mathematics. — New York: W. W. Norton & Company, 2001. — С. 674–694. — ISBN 0-393-02023-1.
• Yoshio Mikami. Development of Mathematics in China and Japan. — B. G. Teubner, 1913. — С. 135.
• Petr Beckmann. A History of Pi. — Macmillan, 1971. — С. 101, 170. — ISBN 978-0-312-38185-1.
• Roger Herz-Fischler. The Shape of the Great Pyramid. — Wilfrid Laurier University Press, 2000. — С. 67. — ISBN 978-0-889-20324-2.
• Ottmar Beucher. Matlab und Simulink. — Pearson Education, 2008. — С. 195. — ISBN 978-3-8273-7340-3.
• K. Ayob. Digital Filters in Hardware: A Practical Guide for Firmware Engineers. — Trafford Publishing, 2008. — С. 278. — ISBN 978-1-4251-4246-9.
• Manfred Robert Schroeder. Number theory in science and communication. — 2nd. — Springer, 2008. — С. 26–28. — ISBN 978-3-540-85297-1.
• John D Barrow. The Constants of Nature. — London: Jonathan Cape, 2002. — ISBN 0-224-06135-6.
• Richard Arratia, Larry Goldstein, Louis Gordon. Poisson approximation and the Chen-Stein method // Statistical Science. — 1990. — Т. 5, вып. 4. — С. 403–434. — doi:10.1214/ss/1177012015. — JSTOR 2245366.
• Charles Smythe. Our Inheritance in the Great Pyramid. — Kessinger Publishing, 2004. — С. 39. — ISBN 1-4179-7429-X.
• Steven A. Leduc. Cracking the AP Physics B & C Exam, 2004–2005 Edition. — Princeton Review Publishing, 2003. — С. 25. — ISBN 0-375-76387-2.
• Rydberg constant times c in Hz. Fundamental physical constants. NIST. Дата обращения: 25 июля 2011.
• Randall Munroe. What If?. — 2014. — ISBN 9781848549562.
• Roger Herz-Fischler. The Shape of the Great Pyramid. — Wilfrid Laurier University Press, 2000. — С. 67. — ISBN 978-0-889-20324-2.
• Eric W. Weisstein. CRC concise encyclopedia of mathematics. — CRC Press, 2003. — С. 2232. — ISBN 978-1-58488-347-0.

Ссылки[править | править код]

• В. Левшин. Магистр рассеянных наук. — Москва: Детская Литература, 1970. — С. 256.
• Hardy, G. H. — A Mathematician’s Apology. — New York: Cambridge University Press, 1993, (ISBN 0-521-42706-1)
• Weisstein, Eric W. Almost Integer (англ.) на сайте Wolfram MathWorld.
• Various mathematical coincidences in the «Science & Math» section of futilitycloset.com
• Press, W. H., Seemingly Remarkable Mathematical Coincidences Are Easy to Generate

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

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

Например, имеем последовательный ряд чисел от 1 до 10, записанных в ячейках B1:B10. Функция =ПОИСКПОЗ(3;B1:B10;0) вернет число 3, поскольку искомое значение находится в ячейке B3, которая является третьей от точки отсчета (ячейки B1).

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

Например, массив {“виноград”;”яблоко”;”груша”;”слива”} содержит элементы, которые можно представить как: 1 – «виноград», 2 – «яблоко», 3 – «груша», 4 – «слива», где 1, 2, 3, 4 – ключи, а названия фруктов – значения. Тогда функция =ПОИСКПОЗ(“яблоко”;{“виноград”;”яблоко”;”груша”;”слива”};0) вернет значение 2, являющееся ключом второго элемента. Отсчет выполняется не с 0 (нуля), как это реализовано во многих языках программирования при работе с массивами, а с 1.

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



Формула для поиска неточного совпадения текста в Excel

Пример 1. Найти позицию первого частичного совпадения строки в диапазоне ячеек, хранящих текстовые значения.

Вид исходной таблицы данных:

Для нахождения позиции текстовой строки в таблице используем следующую формулу:

=ПОИСКПОЗ(D2&”*”;B:B;0)-1

Описание аргументов:

• D2&”*” – искомое значение, состоящее и фамилии, указанной в ячейке B2, и любого количества других символов (“*”);
• B:B – ссылка на столбец B:B, в котором выполняется поиск;
• 0 – поиск точного совпадения.

Из полученного значения вычитается единица для совпадения результата с id записи в таблице.

Пример поиска:

Сравнение двух таблиц в Excel на наличие несовпадений значений

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

Вид таблицы данных:

Для сравнения значений, находящихся в столбце B:B со значениями из столбца A:A используем следующую формулу массива (CTRL+SHIFT+ENTER):

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

Чтобы вычислить остальные значения «протянем» формулу из ячейки C2 вниз для использования функции автозаполнения. В результате получим:

Как видно, третьи элементы списков не совпадают.

Поиск ближайшего большего знания в диапазоне чисел Excel

Пример 3. Найти ближайшее меньшее числу 22 в диапазоне чисел, хранящихся в столбце таблицы Excel.

Вид исходной таблицы данных:

Для поиска ближайшего большего значения заданному во всем столбце A:A (числовой ряд может пополняться новыми значениями) используем формулу массива (CTRL+SHIFT+ENTER):

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

Результат расчетов:

Для поиска ближайшего меньшего значения достаточно лишь немного изменить данную формулу и ее следует также ввести как массив (CTRL+SHIFT+ENTER):

Результат поиска:

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

Функция имеет следующую синтаксическую запись:

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

Описание аргументов:

• искомое_значение – обязательный аргумент, принимающий текстовые, числовые значения, а также данные логического и ссылочного типов, который используется в качестве критерия поиска (для сопоставления величин или нахождения точного совпадения);
• просматриваемый_массив – обязательный аргумент, принимающий данные ссылочного типа (ссылки на диапазон ячеек) или константу массива, в которых выполняется поиск позиции элемента согласно критерию, заданному первым аргументом функции;
• [тип_сопоставления] – необязательный для заполнения аргумент в виде числового значения, определяющего способ поиска в диапазоне ячеек или массиве. Может принимать следующие значения:

1. -1 – поиск наименьшего ближайшего значения заданному аргументом искомое_значение в упорядоченном по убыванию массиве или диапазоне ячеек.
2. 0 – (по умолчанию) поиск первого значения в массиве или диапазоне ячеек (не обязательно упорядоченном), которое полностью совпадает со значением, переданным в качестве первого аргумента.
3. 1 – Поиск наибольшего ближайшего значения заданному первым аргументом в упорядоченном по возрастанию массиве или диапазоне ячеек.

Скачать примеры ПОИСКПОЗ для поиска совпадения значений в Excel

Примечания:

1. Если в качестве аргумента искомое_значение была передана текстовая строка, функция ПОИСКПОЗ вернет позицию элемента в массиве (если такой существует) без учета регистра символов. Например, строки «МоСкВа» и «москва» являются равнозначными. Для различения регистров можно дополнительно использовать функцию СОВПАД.
2. Если поиск с использованием рассматриваемой функции не дал результатов, будет возвращен код ошибки #Н/Д.
3. Если аргумент [тип_сопоставления] явно не указан или принимает число 0, для поиска частичного совпадения текстовых значений могут быть использованы подстановочные знаки («?» – замена одного любого символа, «*» – замена любого количества символов).
4. Если в объекте данных, переданном в качестве аргумента просматриваемый_массив, содержится два и больше элементов, соответствующих искомому значению, будет возвращена позиция первого вхождения такого элемента.