Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
поиск похожего числа одной таблицы в диапазоне другой
 
#1
31.01.2020 02:14:45
Здравствуйте!
К пересылаемому по почте файлу, где в одном столбце список из 8-ми значных чисел, надо добавить данные в соседний столбик. Но начальный
файл может содержать ошибки в любой из 8-ми цифр. Вопрос: можно ли формулой (именно формулой) найти похожее число из начального файла
на число в файле с данными (при условии, что там ошибок нет).
Ошибка в 2-х цифрах бывает настолько редко, что этим случаем можно пренебречь.
Файл прилагается.


Заранее благодарю за помощь.
 
 
 
#2
31.01.2020 08:48:42
Доброе время  суток.
Пока формулисты спят
Код
=ЕСЛИОШИБКА(ВПР(A12;$E$3:$E$30;1;ЛОЖЬ);ИНДЕКС($E$3:$E$30;СУММПРОИЗВ(--(ДЛСТР(ПОДСТАВИТЬ(ABS(A12-$E$3:$E$30);"0";""))<2);СТРОКА($3:$30))-2;1))
 
 
 
#3
31.01.2020 10:42:04
Цитата
Андрей VG написал:
Пока формулисты спят
..., а ТС не видит, поменял в E19 число 77255420 на 77255421.
И формула для числа 77255420 в яч. А4 выдала ошибку #ССЫЛКА!  :cry:  
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
 
 
#4
31.01.2020 11:44:41
Андрей, спасибо. Ваша формула выдала правильный № в этом файле. Сейчас разберусь с синтаксисом и проверю на предыдущих файлах с ошибками.
Михаил, спасибо за шедевр. С ним надо посидеть подольше. Правда, вариант Андрея более предпочтительнее (по затраченному времени на проверку).
Благодарю обоих.
 
 
 
#5
31.01.2020 11:45:49
Формула массива:
=ЕСЛИ(ИЛИ(МУМНОЖ(Ч((ПСТР(A3;СТОЛБЕЦ(A1:H1);1)=ПСТР($E$3:$E$30;СТОЛБЕЦ(A1:H1);1)));ТРАНСП(СТОЛБЕЦ(A1:H1)^0))=8);"Совпадает";"Отличие в 1 цифру")
Изменено: Максим В. - 31.01.2020 11:46:13
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#6
31.01.2020 12:12:58
М.б. еще вот так (в основе остается идея от Андрея VG)? (Массивная):
Код
=ЕСЛИОШИБКА(ВПР(A3;$E$3:$E$30;1;ЛОЖЬ);ИНДЕКС($E$3:$E$30;ПОИСКПОЗ(1;--(ДЛСТР(ПОДСТАВИТЬ(ABS(A3-$E$3:$E$30);"0";""))<2);0)))
Изменено: Михаил Лебедев - 31.01.2020 12:13:53
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
 
 
#7
31.01.2020 12:34:10
Цитата
Михаил Лебедев написал:
М.б.
Михаил, а вот тут думать надо. Пусть Таблица правильных восьмизначных чисел содержит числа с 10000000 по 99999999. Тогда всегда ВПР найдёт нужный индекс.
Добро, не будет рассматривать крайний случай. Но, входное число может содержать различие на один символ не с одним правильным числом, а с двумя или более?
Тогда вопрос - какое из двух или более нужно использовать? ТС ни слова об этом не сказал, предоставив проанализировать что-если форумчанам, хотя никто из нас не обладает всей полнотой исходных данных :)
 
 
 
#8
31.01.2020 13:48:16
Максим В., Спасибо, сейчас закончу с вариантом Андрея, потом проработаю Ваш вариант
Михаил Лебедев,  - спасибо, тоже вернусь к Вашей ф-ле чуть позже
Андрей VG,  -
Цитата
Андрей VG написал:
входное число может содержать различие на один символ не с одним правильным числом, а с двумя или более?
1. Честно, я не продумал это, хотя за год не было таких случаев. Но все равно, подумать надо. Спасибо.
2. Ваша формула работает отлично, только № одного вагона дал результат "#ССЫЛКА!". Посмотрите, пжл.
 
 
 
#9
31.01.2020 14:10:15
Цитата
Александр Чекирда написал:
хотя за год не было таких случаев
Значит появились в новом году (крыса же) :)
Правильные 94707148 94608148
и тестируемое 94607148
 
 
 
#10
31.01.2020 14:19:13
Андрей VG, - Я эту крысу... Понял, спасибо. Как раз тот случай, о котором Вы написали.
 
 
 
#11
31.01.2020 14:30:16
Максим В., - Спасибо. Но Ваша ф-ла дает только констатацию факта, что номер введен ошибочно. Этого недостаточно, т.к. приходится продолжать поиски, где ошибка. Но все равно спасибо, что отозвались
 
 
 
#12
31.01.2020 15:25:03
Михаил Лебедев, - Спасибо, хороший вариант.

Всем спасибо, всех благ!
 
 
 
#13
31.01.2020 15:47:23
Цитата
Александр Чекирда написал:
Ваша ф-ла дает только констатацию факта
Это не сложно поправить:
=ИНДЕКС(F$3:F$12612;МИН(ЕСЛИ(МУМНОЖ(Ч((ПСТР(A3;{1;2;3;4;5;6;7;8};1)=ПСТР(F$3:F$12612;{1;2;3;4;5;6;7;8};1)));{1:1:1:1:1:1:1:1})>6;СТРОКА(F$3:F$12612)-2)))
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#14
31.01.2020 17:05:33
Максим В., - Классно! Есть, правда, 1 нюанс. Ошибочный № 94607148 имеет 2 аналога (с несовпадением 1 цифры, см https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=125152&a...  от Андрей VG, Можно ли подправить, если есть время и желание, Вашу формулу?
 
 
 
#15
31.01.2020 17:20:57
Александр Чекирда, а какой и по какой логике должен быть результат?
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#16
31.01.2020 18:30:55
Максим В., - Чебурашка посылает Гене файл с номерами вагонов. К ним Гена должен добавить в соседний столбик данные по грузовладельцам. Данные Гены считать 100% правильными. Но Чебурашка, по каким-то причинам, часто ошибается в №№, а данные нужно отослать, т.к. несколько Шапокляк ждут эту инфо. Времени на это желательно потратить как можно меньше. Загвоздка в том, что ошибочные №№, посылаемые Чебурашкой, отсутствуют среди №№ в сведениях Гены (из-за несовпадения в какой-либо цифре). Приходится крокодилу исправлять ошибки и все-таки посылать данные и на эти номера, предварительно их исправив. Чебурашку убивать нельзя - они с Геной все-таки друзья.
 
 
 
#17
31.01.2020 19:43:24
Красиво объяснили. Необычно. Но на мой вопрос Вы не ответили.
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#18
31.01.2020 22:07:28
Максим В., - Приходит по почте файл (назовем его Файл1) со списком из 8-ми значных чисел (№№ вагонов).

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

1. Расширенным фильтром оставляю в Файле2 только те вагоны, которые есть в Файле1 по определенной дате (номеры в Файле1 фигурируют за предыдущие сутки).
2. Добавляются данные по этим вагонам. Если ошибок нет, файл готов к обратной отправке, если есть - "танцы с бубном".
Изменено: Александр Чекирда - 31.01.2020 22:11:03 (орфография)
 
 
 
#19
01.02.2020 00:19:00
посмотрите в Fuzzy Lookup приемах
может полезным оказаться
в каких-то версиях PQ уже Table.FuzzyJoin есть
если не будете ограничивать себя рамками формул, то думаю, что и вариантом с PQ помогут
немного не в рамках форума, но в R эта задача тоже практически одной строкой скрипта решается
 
 
 
#20
01.02.2020 18:26:53
Stics, Спасибо, что отозвались. Но мне думается, что у меня была одна проблема - решить по-быстрому этот вопрос. А, с Вашей подсказкой, появится еще одна, причем глобальная - установить, разобраться, почитать хэлпы по этой надстройке... Может, и дорасту до следующего этапа, но не сейчас. Еще раз спасибо.
 
 
 
#21
20.02.2020 16:13:25
Здравствуйте!

Задача усложнилась, файл поэтому немного переделан, - разбит на 2 листа ("Лист1" и "п-у"). На листе п-у представлены сведения, типа базы данных. На Листе1 - рабочие ф-лы, выборка данных. Мне надо тоже самое, что делают введенные формулы в столбце E (формулы от Андрея VG, за что ему еще раз "Спасибо"), но проверять числа (номеры) надо с выборкой по датам. Скажем, не ранее, чем за 5 дней до даты, введенной в ячейку E2.
И попутно вылезла оказия, каждому номеру соответствует Имя, но в столбце F некоторые ячейки #Н/Д. Не подскажите, почему?
 
 
 
Страницы: 1
Читают тему
Наверх