Как сравнить два текста и вывести разницу? — Хабр Q&A
Так преобразуйте тексты в наборы строк, где каждая строка это отдельное слово или знак.
Находите различия и собираете обратно в текст, подсвечивая различия.
Ответ написан
Комментировать
На мой взгляд, главная сложность, что требуются 3 разные вещи при 3х типах ошибок:
При пропуске нужно идти дальше по оригиналу до совпадения
При добавке нужно идти по пользовательскому тексту до совпадения
При опечатке нужно идти «в чем не бывало».
Поэтому, алгоритм таков:
Идти по текстам (по буквам) до ошибки.
Пробуем «пропуск» идем по оригиналу дальше 10 символов с проверкой
— Если нашли совпадение 3х-4х символов — отмечаем ошибку и едем дальше
— Если не нашли совпадение — пробуем вариант «добавка» идем с проверкой по пользовательскому тексту 10 символов
— Иначе считаем опечаткой
Ответ написан
Комментировать
Комментировать
Зачем изобретать велосипед ? Воспользуйтесь этим.
Ответ написан
Комментировать
Делается проход с вычислением бинарной разницы одного слова относительно другого.
После обнаружения ошибки — фиксируете и затем двигаетесь дальше по эталонному тексту и ищите это «похожее» слово в набранном тексте.
Ответ написан
Комментировать
Попробуй это https://texttools.ru/difference-checker
Ответ написан 
2019, в 06:28″>
более трёх лет назад
Комментировать
В таких случаях, я обычно использую code compare
Ответ написан
Комментировать
Вот здесь сравнение текстов сделано на JQUERY
Ответ написан
Комментировать
Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ
Совет: Попробуйте использовать новые функции ПРОСМОТРX и XMATCH, а также улучшенные версии функций, описанные в этой статье.
Эти новые функции работают в любом направлении и возвращают точные совпадения по умолчанию, что упрощает и упрощает работу с ними по сравнению с предшественниками.
Предположим, у вас есть список номеров офисов, и вам нужно знать, какие сотрудники работают в каждом из них. Таблица очень угрюмая, поэтому, возможно, вам кажется, что это сложная задача. С функцией подытов на самом деле это довольно просто.
Функции ВВ., а также ИНДЕКС и ВЫБОРПОЗ — одни из самых полезных функций в Excel.
Примечание: Мастер подметок больше не доступен в Excel.
Ниже в качестве примера по выбору вы можете найти пример использования в этой области.
=ВПР(B2;C2:E7,3,ИСТИНА)
В этом примере B2 является первым аргументом —элементом данных, который требуется для работы функции. В случае СРОТ ВЛ.В.ОВ этот первый аргумент является искомой значением.
Этот аргумент может быть ссылкой на ячейку или фиксированным значением, таким как «кузьмина» или 21 000. Вторым аргументом является диапазон ячеек C2–:E7, в котором нужно найти и найти значение. Третий аргумент — это столбец в диапазоне ячеек, содержащий ищите значение.
Четвертый аргумент необязателен. Введите истина или ЛОЖЬ. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести ЛОЖЬ, функция будет соответствовать значению, заведомо первому аргументу. Другими словами, если оставить четвертый аргумент пустым или ввести ИСТИНА, это обеспечивает большую гибкость.
В этом примере показано, как работает функция. При вводе значения в ячейку B2 (первый аргумент) в результате поиска в ячейках диапазона C2:E7 (2-й аргумент) выполняется поиск в ней и возвращается ближайшее приблизительное совпадение из третьего столбца в диапазоне — столбца E (третий аргумент).
Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. Иначе потребуется ввести одно из значений в столбец C или D, чтобы получить какой-либо результат.
Если вы хорошо разучились работать с функцией ВГТ.В.В., то в равной степени использовать ее будет легко. Вы вводите те же аргументы, но выполняется поиск в строках, а не в столбцах.
Использование индекса и MATCH вместо ВРОТ
При использовании функции ВПРАВО существует ряд ограничений, которые действуют только при использовании функции ВПРАВО. Это означает, что столбец, содержащий и look up, всегда должен быть расположен слева от столбца, содержащего возвращаемого значения. Теперь, если ваша таблица не построена таким образом, не используйте В ПРОСМОТР. Используйте вместо этого сочетание функций ИНДЕКС и MATCH.
В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце.
Дополнительные примеры использования индексов и MATCH вместо В ПРОСМОТР см. в статье билла Https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Билла Джилена (Bill Jelen), MVP корпорации Майкрософт.
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функциями подытовки, прежде чем попробовать их с собственными данными, вот примеры данных.
Пример работы с ВЛОКОНПОМ
Скопируйте следующие данные в пустую таблицу.
Совет: Прежде чем врезать данные в Excel, установите для столбцов A–C ширину в 250 пикселей и нажмите кнопку «Перенос текста» (вкладка «Главная», группа «Выравнивание»).
|
Плотность |
Вязкость |
Температура |
|
0,457 |
3,55 |
500 |
|
0,525 |
3,25 |
400 |
|
0,606 |
2,93 |
300 |
| 0,675 |
2,75 |
250 |
|
0,746 |
2,57 |
200 |
|
0,835 |
2,38 |
150 |
|
0,946 |
2,17 |
100 |
|
1,09 |
1,95 |
50 |
|
1,29 |
1,71 |
0 |
|
Формула |
Описание |
Результат |
|
=ВПР(1,A2:C10,2) |
Используя приблизительное соответствие, функция ищет в столбце A значение 1, находит наибольшее значение, которое меньше или равняется 1 и составляет 0,946, а затем возвращает значение из столбца B в той же строке. |
2,17 |
|
=ВПР(1,A2:C10,3,ИСТИНА) |
Используя приблизительное соответствие, функция ищет в столбце A значение 1, находит наибольшее значение, которое меньше или равняется 1 и составляет 0,946, а затем возвращает значение из столбца C в той же строке. |
100 |
|
=ВПР(0,7,A2:C10,3,ЛОЖЬ) |
Используя точное соответствие, функция ищет в столбце A значение 0,7. Поскольку точного соответствия нет, возвращается сообщение об ошибке. |
#Н/Д |
|
=ВПР(0,1,A2:C10,2,ИСТИНА) |
Используя приблизительное соответствие, функция ищет в столбце A значение 0,1. |
#Н/Д |
|
=ВПР(2,A2:C10,2,ИСТИНА) |
Используя приблизительное соответствие, функция ищет в столбце A значение 2, находит наибольшее значение, которое меньше или равняется 2 и составляет 1,29, а затем возвращает значение из столбца B в той же строке. |
1,71 |
Поскольку 0,1 меньше наименьшего значения в столбце A, возвращается сообщение об ошибке.Пример ГВ.Г.В.В.
Скопируйте всю таблицу и вставьте ее в ячейку A1 пустого листа Excel.
Совет: Прежде чем врезать данные в Excel, установите для столбцов A–C ширину в 250 пикселей и нажмите кнопку «Перенос текста» (вкладка «Главная», группа «Выравнивание»).
|
Оси |
Подшипники |
Болты |
|
4 |
4 |
9 |
|
5 |
7 |
10 |
|
6 |
8 |
11 |
|
Формула |
Описание |
Результат |
|
=ГПР(«Оси»;A1:C4;2;ИСТИНА) |
Поиск слова «Оси» в строке 1 и возврат значения из строки 2, находящейся в том же столбце (столбец A). |
4 |
|
=ГПР(«Подшипники»;A1:C4;3;ЛОЖЬ) |
Поиск слова «Подшипники» в строке 1 и возврат значения из строки 3, находящейся в том же столбце (столбец B). |
7 |
|
=ГПР(«П»;A1:C4;3;ИСТИНА) |
Поиск буквы «П» в строке 1 и возврат значения из строки 3, находящейся в том же столбце. Так как «П» найти не удалось, возвращается ближайшее из меньших значений: «Оси» (в столбце A). |
5 |
|
=ГПР(«Болты»;A1:C4;4) |
Поиск слова «Болты» в строке 1 и возврат значения из строки 4, находящейся в том же столбце (столбец C). |
11 |
|
=ГПР(3;{1;2;3:»a»;»b»;»c»;»d»;»e»;»f»};2;ИСТИНА) |
Поиск числа 3 в трех строках константы массива и возврат значения из строки 2 того же (в данном случае — третьего) столбца. Константа массива содержит три строки значений, разделенных точкой с запятой (;). Так как «c» было найдено в строке 2 того же столбца, что и 3, возвращается «c». |
c |
Примеры индекса и match
В последнем примере функции ИНДЕКС и MATCH совместно возвращают номер счета с наиболее ранней датой и соответствующую дату для каждого из пяти городов.
Так как дата возвращается как число, для ее формата используется функция ТЕКСТ. Функция ИНДЕКС использует результат, возвращенный функцией ПОИСКПОЗ, как аргумент. Сочетание функций ИНДЕКС и ПОИСКПОЗ используется в каждой формуле дважды — сперва для возврата номера счета, а затем для возврата даты.
Скопируйте всю таблицу и вставьте ее в ячейку A1 пустого листа Excel.
Совет: Перед тем как вировать данные в Excel, установите для столбцов A–D ширину в 250 пикселей и нажмите кнопку «Перенос текста» (вкладка «Главная», группа «Выравнивание»).
|
Счет |
Город |
Дата выставления счета |
Счет с самой ранней датой по городу, с датой |
|
3115 |
Казань |
07. |
=»Казань = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Казань»,$B$2:$B$33,0),1)& «, Дата выставления счета: » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Казань»,$B$2:$B$33,0),3),»m/d/yy») |
|
3137 |
Казань |
09.04.12 |
=»Орел = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Орел»,$B$2:$B$33,0),1)& «, Дата выставления счета: » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Орел»,$B$2:$B$33,0),3),»m/d/yy») |
|
3154 |
Казань |
11. |
=»Челябинск = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Челябинск»,$B$2:$B$33,0),1)& «, Дата выставления счета: » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Челябинск»,$B$2:$B$33,0),3),»m/d/yy») |
|
3191 |
Казань |
21.04.12 |
=»Нижний Новгород = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Нижний Новгород»,$B$2:$B$33,0),1)& «, Дата выставления счета: » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Нижний Новгород»,$B$2:$B$33,0),3),»m/d/yy») |
|
3293 |
Казань |
25. |
=»Москва = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Москва»,$B$2:$B$33,0),1)& «, Дата выставления счета: » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Москва»,$B$2:$B$33,0),3),»m/d/yy») |
|
3331 |
Казань |
27.04.12 |
|
|
3350 |
Казань |
28.04.12 |
|
|
3390 |
Казань |
01. |
|
|
3441 |
Казань |
02.05.12 |
|
|
3517 |
Казань |
08.05.12 |
|
|
3124 |
Орел |
09. |
|
|
3155 |
Орел |
11.04.12 |
|
|
3177 |
Орел |
19.04.12 |
|
|
3357 |
Орел |
28. |
|
|
3492 |
Орел |
06.05.12 |
|
|
3316 |
Челябинск |
25.04.12 |
|
|
3346 |
Челябинск |
28. |
|
|
3372 |
Челябинск |
01.05.12 |
|
|
3414 |
Челябинск |
01.05.12 |
|
|
3451 |
Челябинск |
02. |
|
|
3467 |
Челябинск |
02.05.12 |
|
|
3474 |
Челябинск |
04.05.12 |
|
|
3490 |
Челябинск |
05. |
|
|
3503 |
Челябинск |
08.05.12 |
|
|
3151 |
Нижний Новгород |
09.04.12 |
|
|
3438 |
Нижний Новгород |
02. |
|
|
3471 |
Нижний Новгород |
04.05.12 |
|
|
3160 |
Москва |
18.04.12 |
|
|
3328 |
Москва |
26. |
|
|
3368 |
Москва |
29.04.12 |
|
|
3420 |
Москва |
01.05.12 |
|
|
3501 |
Москва |
06. |
04.12
04.12
04.12
05.12
04.12
04.12
04.12
05.12
05.12
05.12
04.12
05.12Поиск значений с помощью ВПР, ИНДЕКС или ПОИСКПОЗ
Совет: Попробуйте использовать новые функции XLOOKUP и XMATCH, улучшенные версии функций, описанных в этой статье. Эти новые функции работают в любом направлении и по умолчанию возвращают точные совпадения, что делает их более простыми и удобными в использовании, чем их предшественники.
Предположим, у вас есть список номеров офисов, и вам нужно знать, какие сотрудники работают в каждом офисе. Электронная таблица огромна, поэтому вы можете подумать, что это сложная задача. На самом деле это довольно легко сделать с помощью функции поиска.
Функции ВПР и ГПР вместе с ИНДЕКС и ПОИСКПОЗ являются одними из самых полезных функций в Excel.
Примечание. Мастер поиска больше не доступен в Excel.
Вот пример использования функции ВПР.
=ВПР(B2,C2:E7,3,ИСТИНА)
В этом примере B2 — это первый аргумент — элемент данных, необходимый для работы функции. Для ВПР этот первый аргумент — это значение, которое вы хотите найти. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, например «кузнец» или 21 000. Второй аргумент — это диапазон ячеек C2-:E7, в котором нужно искать значение, которое вы хотите найти. Третий аргумент — это столбец в том диапазоне ячеек, который содержит искомое значение.
Четвертый аргумент является необязательным. Введите ИСТИНА или ЛОЖЬ. Если вы введете TRUE или оставите аргумент пустым, функция вернет приблизительное соответствие значению, указанному вами в первом аргументе. Если вы введете FALSE, функция будет соответствовать значению, предоставленному первым аргументом. Другими словами, оставляя четвертый аргумент пустым или вводя значение TRUE, вы получаете больше гибкости.
В этом примере показано, как работает функция.
Когда вы вводите значение в ячейку B2 (первый аргумент), функция ВПР ищет ячейки в диапазоне C2:E7 (2-й аргумент) и возвращает наиболее близкое приближенное совпадение из третьего столбца диапазона, столбца E (3-й аргумент).
Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. Если бы это было не так, вам пришлось бы ввести одно из значений в столбцах C или D, чтобы вообще получить результат.
Когда вы освоитесь с функцией ВПР, функция ГПР будет столь же проста в использовании. Вы вводите те же аргументы, но поиск выполняется в строках, а не в столбцах.
Использование ИНДЕКС и ПОИСКПОЗ вместо ВПР
Существуют определенные ограничения при использовании функции ВПР — функция ВПР может искать значение только слева направо. Это означает, что столбец, содержащий искомое значение, всегда должен располагаться слева от столбца, содержащего возвращаемое значение.
Теперь, если ваша электронная таблица не построена таким образом, не используйте функцию ВПР. Вместо этого используйте комбинацию функций ИНДЕКС и ПОИСКПОЗ.
В этом примере показан небольшой список, в котором значение, которое мы хотим найти, Чикаго, отсутствует в крайнем левом столбце. Итак, мы не можем использовать функцию ВПР. Вместо этого мы будем использовать функцию ПОИСКПОЗ, чтобы найти Чикаго в диапазоне B1:B11. Оно находится в строке 4. Затем ИНДЕКС использует это значение в качестве аргумента поиска и находит население Чикаго в 4-м столбце (столбец D). Используемая формула показана в ячейке A14.
Дополнительные примеры использования ИНДЕКС и ПОИСКПОЗ вместо ВПР см. в статье https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Билла Джелена, Microsoft MVP.
Попробуйте
Если вы хотите поэкспериментировать с функциями поиска, прежде чем применять их к своим собственным данным, вот несколько примеров данных.
ВПР Пример работы
Скопируйте следующие данные в пустую электронную таблицу.
Совет: Перед вставкой данных в Excel установите ширину столбцов от A до C до 250 пикселей и нажмите Перенос текста (вкладка Главная , группа Выравнивание ).
Плотность | Вязкость | Температура |
0,457 | 3,55 | 500 |
0,525 | 3,25 | 400 |
0,606 | 2,93 | 300 |
0,675 | 2,75 | 250 |
0,746 | 2,57 | 200 |
0,835 | 2,38 | 150 |
0,946 | 2,17 | 100 |
1,09 | 1,95 | 50 |
1,29 | 1,71 | 0 |
Формула | Описание | Результат |
=ВПР(1,A2:C10,2) | Используя приблизительное совпадение, ищет значение 1 в столбце A, находит наибольшее значение меньшее или равное 1 в столбце A, равное 0,946, а затем возвращает значение из столбца B в той же строке. | 2,17 |
=ВПР(1,A2:C10,3,ИСТИНА) | Используя приблизительное совпадение, ищет значение 1 в столбце A, находит наибольшее значение, меньшее или равное 1 в столбце A, что составляет 0,946, а затем возвращает значение из столбца C в той же строке. | 100 |
=ВПР(0.7,A2:C10,3,ЛОЖЬ) | Используя точное совпадение, ищет значение 0,7 в столбце A. Поскольку точного совпадения в столбце A нет, возвращается ошибка. | #Н/Д |
=ВПР(0. | Используя приблизительное совпадение, ищет значение 0,1 в столбце A. Поскольку 0,1 меньше наименьшего значения в столбце A, возвращается ошибка. | #Н/Д |
=ВПР(2,A2:C10,2,ИСТИНА) | Используя приблизительное совпадение, ищет значение 2 в столбце A, находит наибольшее значение, меньшее или равное 2 в столбце A, что составляет 1,29, а затем возвращает значение из столбца B в той же строке. | 1,71 |
1,A2:C10,2,ИСТИНА)ГПР Пример
Скопируйте все ячейки этой таблицы и вставьте их в ячейку A1 на пустом листе Excel.
Совет: Перед вставкой данных в Excel установите ширину столбцов от A до C до 250 пикселей и нажмите Обтекание текста ( Главная вкладка, Группа выравнивания ).
Оси | Подшипники | Болты |
4 | 4 | 9 |
5 | 7 | 10 |
6 | 8 | 11 |
Формула | Описание | Результат |
=ГПР(«Оси», A1:C4, 2, ИСТИНА) | Ищет «Оси» в строке 1 и возвращает значение из строки 2, которая находится в том же столбце (столбец A). | 4 |
=ГПР(«Подшипники», A1:C4, 3, ЛОЖЬ) | Ищет «Подшипники» в строке 1 и возвращает значение из строки 3, которая находится в том же столбце (столбец B). | 7 |
=ГПР(«В», A1:C4, 3, ИСТИНА) | Ищет «B» в строке 1 и возвращает значение из строки 3, которая находится в том же столбце. Поскольку точное соответствие для «B» не найдено, используется наибольшее значение в строке 1, меньшее, чем «B»: «Оси» в столбце A. | 5 |
=ГПР(«Болты», A1:C4, 4) | Ищет «Болты» в строке 1 и возвращает значение из строки 4, которая находится в том же столбце (столбец C). | 11 |
=HLOOKUP(3, {1,2,3;»a»,»b»,»c»;»d»,»e»,»f»}, 2, ИСТИНА) | Ищет число 3 в константе трехстрочного массива и возвращает значение из строки 2 в том же (в данном случае третьем) столбце. В массиве констант есть три строки значений, каждая строка разделена точкой с запятой (;). Поскольку «c» находится в строке 2 и в том же столбце, что и 3, возвращается «c». | с |
Примеры ИНДЕКС и ПОИСКПОЗ
В этом последнем примере функции ИНДЕКС и ПОИСКПОЗ используются вместе для возврата самого раннего номера счета и соответствующей ему даты для каждого из пяти городов.
Поскольку дата возвращается в виде числа, мы используем функцию ТЕКСТ, чтобы отформатировать ее как дату. Функция ИНДЕКС фактически использует в качестве аргумента результат функции ПОИСКПОЗ. Комбинация функций ИНДЕКС и ПОИСКПОЗ используется дважды в каждой формуле — сначала для возврата номера счета, а затем для возврата даты.
Скопируйте все ячейки этой таблицы и вставьте их в ячейку A1 на пустом листе Excel.
Совет: Перед вставкой данных в Excel установите ширину столбцов от A до D до 250 пикселей и нажмите Перенос текста (вкладка Главная , группа Выравнивание ).
Счет-фактура | Город | Дата счета | Самый ранний счет по городу с датой |
3115 | Атланта | 07. | =»Атланта = «&ИНДЕКС($A$2:$C$33,MATCH(«Атланта»,$B$2:$B$33,0),1)& «, Дата счета: » & ТЕКСТ(ИНДЕКС($A$2 :$C$33,MATCH(«Атланта»,$B$2:$B$33,0),3),»м/д/гг») |
3137 | Атланта | 09.04.12 | =»Остин = «&ИНДЕКС($A$2:$C$33,MATCH(«Остин»,$B$2:$B$33,0),1)& «, Дата счета: » & ТЕКСТ(ИНДЕКС($A$2 :$C$33,MATCH(«Остин»,$B$2:$B$33,0),3),»м/д/гг») |
3154 | Атланта | 11. | =»Даллас = «&ИНДЕКС($A$2:$C$33,MATCH(«Даллас»,$B$2:$B$33,0),1)& «, Дата счета: » & ТЕКСТ(ИНДЕКС($A$2 :$C$33,MATCH(«Даллас»,$B$2:$B$33,0),3),»м/д/гг») |
3191 | Атланта | 21.04.12 | =»Новый Орлеан = «&ИНДЕКС($A$2:$C$33,MATCH(«Новый Орлеан»,$B$2:$B$33,0),1)& «, Дата счета: » & ТЕКСТ(ИНДЕКС($ A$2:$C$33,MATCH(«Новый Орлеан»,$B$2:$B$33,0),3),»м/д/гг») |
3293 | Атланта | 25. | =»Тампа = «&ИНДЕКС($A$2:$C$33,MATCH(«Тампа»,$B$2:$B$33,0),1)& «, Дата счета: » & ТЕКСТ(ИНДЕКС($A$2 :$C$33,MATCH(«Тампа»,$B$2:$B$33,0),3),»м/д/гг») |
3331 | Атланта | 27.04.12 | |
3350 | Атланта | 28.04.12 | |
3390 | Атланта | 01. | |
3441 | Атланта | 02.05.12 | |
3517 | Атланта | 08.05.12 | |
3124 | Остин | 09.04.12 | |
3155 | Остин | 11. | |
3177 | Остин | 19.04.12 | |
3357 | Остин | 28.04.12 | |
3492 | Остин | 06.05.12 | |
3316 | Даллас | 25. | |
3346 | Даллас | 28.04.12 | |
3372 | Даллас | 01.05.12 | |
3414 | Даллас | 01.05.12 | |
3451 | Даллас | 02. | |
3467 | Даллас | 02.05.12 | |
3474 | Даллас | 04.05.12 | |
3490 | Даллас | 5/5/12 | |
3503 | Даллас | 08. | |
3151 | Новый Орлеан | 09.04.12 | |
3438 | Новый Орлеан | 02.05.12 | |
3471 | Новый Орлеан | 04.05.12 | |
3160 | Тампа | 18. | |
3328 | Тампа | 26.04.12 | |
3368 | Тампа | 29.04.12 | |
3420 | Тампа | 01.05.12 | |
3501 | Тампа | 06. |
04.12
04.12
04.12
05.12
04.12
04.12
05.12
05.12
04.12
05.12Текст совпадения XLOOKUP содержит — ExcelKid
Узнайте, как использовать XLOOKUP для сопоставления текстовых значений с помощью 5-го аргумента, режима сопоставления подстановочных знаков.
Как искать совпадающие текстовые значения
Одной из мощных функций функции XLOOKUP является встроенная поддержка подстановочных знаков. Вы включаете подстановочные знаки, находите 5-й аргумент (режим соответствия) и устанавливаете параметр равным 2.
Общая формула:
=XLOOKUP(«*»&lookup_value&»*»,lookup_array, return_array,if_not_found,2)
Во-первых, мы используем именованных диапазонов , чтобы упростить формулу:
- ID = диапазон C3:C8
- Цена = диапазон D3:D8
Внимательно посмотрите на формулу в ячейке G3:
=XLOOKUP(«*»&F3&»*»,ID,Price»,значение не найдено»,2)
- искомое значение – F3
- поисковый массив — идентификатор представляет собой именованный диапазон, C3:C8
- возвращаемый массив — Цена является именованным диапазоном, D3:D8
- если не найдено – значение в случае не найденного совпадения, «значение не найдено».
- режим соответствия — чтобы включить режим сопоставления с подстановочными знаками, используйте 2
В примере мы оставляем 6-й аргумент (Режим поиска) по умолчанию — сначала к последнему.
Оцените формулу поиска совпадения текста
Первое, что вы должны помнить: вы должны объединить значения поиска с двумя подстановочными знаками (*).
Используйте символ «&» для объединения значений «на лету». Вот метод объединения искомого значения:
Искомое значение: «*» & F3 & «*»
Теперь формула выглядит следующим образом:
=XLOOKUP(«* AC *», ID, Цена, «значение не найдено», 2)
Эта часть формулы пытается найти первое соответствие текст, содержащий искомое значение. Например, первое совпадение, содержащее строку «AC», — это «TT-AC-431-662», расположенное в третьей строке таблицы. В случае совпадения, как в примере, функция возвращает цену в той же строке, $6100.
Обходной путь с функцией ВПР и текстом, содержащим подстановки
Если вы не используете последнюю версию Microsoft Excel, нет проблем. Есть обходной путь со старой доброй функцией ВПР.
Вот решение для ВПР:
=ВПР(«*»&F3&»*»,C3:D8,2,0)
Объяснение:
Объедините искомое значение со звездочкой (*). Формула ищет искомое значение в диапазоне C3:D8. Нажмите Enter, после чего формула вернет соответствующее значение из второго столбца; результат 6100р.
Поиск текста содержит с использованием XLOOKUP & SEARCH
Существует еще один вариант поиска «текст содержит» или случаи частичного совпадения. Если вы работаете с функцией ПОИСК и функцией НАЙТИ в одной и той же формуле XLOOKUP, вы получите правильный результат.
Пример формулы без учета регистра:
=XLOOKUP(1,–ISNUMBER(SEARCH(«AC»,ID)),Price,»значение не найдено»,2)
XLOOKUP & FIND (с учетом регистра)
Если вы ищете совпадение с учетом регистра, замените функцию ПОИСК на функцию НАЙТИ.