Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Выборка приближенных значений по дополнительному условию
 
#1
03.12.2019 21:49:35
Добрый вечер.

Увы не нашел поиском решения моей проблемы. Надеюсь на вашу помощь.
Имеем 2 листа. На 1ом листе 2 столбца А - время B - госномер. На втором листе 3 столбца А - время B - госномер С - точка. Суть том что время не точно соответствует в 2 листах и надо сравнивать по максимальному  приближению, а проблема в том что при поиске по приближению - первым  может выпасть значение не с тем госномером. Итого задача сводится к тому что мне нужно перенести на первый лист значения точка из второй таблицы, найдя в начале близкое по времени, а затем сравнить соответствует ли гос номер и если нет то продолжить искать до совпадения.

Ещё одна проблема что решение нужно без использования массива (в формулах). Я так понял что формулами я эту проблему не решу, а при попытке написать макрос мне не хватает немного знаний.
Пытаюсь через функцию Match в цикле  найти позицию приближенную по времени, а затем ВПРом  значение гос номера по этой строке и если оно не соответствует то в ВПР меняю адресность поиска на значения ниже на 1 первой найденной строки.
В голове выглядит логичным. На практике из последних ошибок то что не могу присвоить переменной значение полученное функцией Match. Получаю Type mismath. Завтра выложу сюда макрос. А пока вопрос, не слишком ли сложным путем я пошул встраивая в цикл ещё один цикл с предусловием?

Может есть вариант проще?
Файл выложил с примером таблицы
Изменено: komandirada - 04.12.2019 09:22:36
 
 
 
#2
03.12.2019 21:57:34
Цитата
komandirada написал:
А пока вопрос, не слишком ли сложным путем я пошул
Пошули вы не тем путем, долго описывая что нужно, без файла примера.
По вопросам из тем форума, личку не читаю.
 
 
 
#3
03.12.2019 22:06:31
Цитата
komandirada написал:
сложным путем я пошул
вы, главное, не отчаивайтесь если путь окажется и вправду сложным или ложным. то что вы создали тему на этом форуме уже значит что пол-дела сделано.
загляните еще в правила форума, пункты про название темы и файл-пример. и все будет в ажуре
 
 
 
#4
03.12.2019 22:12:35
Как теперь поменять название не знаю. А файл обязательно прикреплю завтра. Пока нет возможности. Но из головы не выходит, и думал, может просто зададут вектор знатоки для размышления
 
 
 
#5
03.12.2019 22:18:57
Цитата
komandirada написал:
Как теперь поменять название не знаю
Предложите новое прямо здесь - модераторы поменяют.
 
 
 
#6
03.12.2019 23:21:39
Цитата
komandirada написал:
найдя в начале близкое по времени, а затем сравнить соответствует ли гос номер и если нет то продолжить искать до совпадения.
Вам надо искать близкое по времени среди тех что соответвуют по гос номеру.
По вопросам из тем форума, личку не читаю.
 
 
 
#7
03.12.2019 23:36:00
Такая мысль тоже была, но как отобрать все совпадения по гос номеру?  
 
 
 
#8
03.12.2019 23:37:30
Без файла будет не показательно.
По вопросам из тем форума, личку не читаю.
 
 
 
#9
03.12.2019 23:57:26
Цитата
komandirada написал:
как отобрать все совпадения по гос номеру?
- словарь поможет.
 
 
 
#10
04.12.2019 09:23:08
Файл выложил. Надеюсь на Вашу помощь
 
 
 
#11
04.12.2019 09:40:20
Цитата
komandirada написал:
найдя в начале близкое по времени,
ближайшее, ближайшее меньшее, или большее?
 
 
 
#12
04.12.2019 09:40:59
Ближайшее меньшее
 
 
 
#13
04.12.2019 09:46:28
Пытался решить вот так, но у меня тайп мисматч
Код
Sub вставка2()
'
' вставка Макрос
'

'
    Dim I As Long
    Dim B As Long
    Dim J As Long
    Application.EnableEvents = 0
    I = 2
    B = 2
    J = Cells(Rows.Count, 1).End(xlUp).Row
     For I = 2 To J
        B = Application.Match(CDbl(Range("A" & I)), [Табл2!Range"A" & B):Range("A" & J)], 1)
           If Not IsError(B) Then
             Do
              Range("С" & I).FormulaR1C1 = Application.VLookup(Range("A" & I), [Табл2!Range("A" & B):Range("В" & J)], 2, 1)
              B = B + 1
             Loop Until (Range("A" & I) <> Range("C" & I)) Or B <> J
            End If
     Next I

End Sub
Изменено: komandirada - 04.12.2019 09:48:14
 
 
 
#14
04.12.2019 10:21:44
Цитата
komandirada написал:
решение нужно без использования массива (в формулах)
формула с использованием массива. как понял
Код
=ЕСЛИОШИБКА(ИНДЕКС(Табл2!$B$2:$B$2262;ПОИСКПОЗ(МАКС(ЕСЛИ(Табл2!$C$2:$C$2264=B2;Табл2!$A$2:$A$2264))&B2;Табл2!$A$2:$A$2262&Табл2!$C$2:$C$2262;));"")
 
 
 
#15
04.12.2019 10:27:53
Цитата
komandirada написал:
как отобрать все совпадения по гос номеру?
так вам надо все совпадения?
формула, которую предложил, ищет гос.номер и ближайшую прошедшую дату.  
 
 
 
#16
04.12.2019 10:56:28
Может это Вам поможет: https://www.planetaexcel.ru/techniques/25/81/
 
 
 
#17
04.12.2019 11:12:09
komandirada, у Вас в файле часть госномеров в кириллице, а часть в латинице.
А "C" латиницей не равно "С" кириллицей, как и "O" и "О" и т.п.
 
 
 
#18
04.12.2019 11:37:23
=INDEX(Табл2!B:B;1+MATCH(MIN(IF(Табл2!$C$2:$C$2262=B2;ABS(A2-Табл2!$A$2:$A$2262)));IF(Табл2!$C$2:$C$2262=B2;ABS(Табл1!A2-Табл2!$A$2:$A$2262));))

но с латиницей и правда беда 12 подстановок делать- ну совсем печально, но можно
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(UPPER(B2));"A";"A");"B";"В");"E";"Е");"K";"К");"M";"М");"H";"Н");"O";"О");"P";"Р");"C";"С");"T";"Т");"Y";"У");"X";"Х")
Изменено: БМВ - 04.12.2019 12:50:37 (Файл ниже.)
По вопросам из тем форума, личку не читаю.
 
 
 
#19
04.12.2019 11:42:51
Цитата
artyrH написал:
формула с использованием массива. как понял
Да. Спасибо. Так был реализован первый вариант. Но при условии что в таблице 100 000 строк у меня постоянно медленно отсчитывается "Проценты" в статусе и комп подвисает переодически.
Цитата
Stics написал:
komandirada , у Вас в файле часть госномеров в кириллице, а часть в латинице.А "C" латиницей не равно "С" кириллицей, как и "O" и "О" и т.п.
Я увидел тоже. Буду править
 
 
 
#20
04.12.2019 12:03:35
Цитата
komandirada написал:
100 000
Цитата
komandirada написал:
в таблице 100 000 строк
Нужно уходить от формул в таком случае.
По вопросам из тем форума, личку не читаю.
 
 
 
#21
04.12.2019 12:36:34
БМВ, Спасибо. за это
Цитата
БМВ написал:
=INDEX(Табл2!B:B;1+MATCH(MIN(IF(Табл2!$C$2:$C$2262=B2;ABS(A2-Табл2!$A$2:$A$2262)));IF(Табл2!$C$2:$C$2262=B2;ABS(Табл1!A2-Табл2!$A$2:$A$2262)))

Но в результате вычислений ищет в таблице 2 значение времени ближайшее ко времени из таблицы 1, но время найденное всегда меньше сраниваемого, а как сделать наоборот?
 
 
 
#22
04.12.2019 12:47:34
komandirada, ищет ближайшее как позднее, так и ранее. Это и есть определение ближайшего, если надо большее, то чуть меняем
=INDEX(Табл2!B:B;1+MATCH(MIN(IF((Табл2!$C$2:$C$2262=B2)*(A2<=Табл2!$A$2:$A$2262);Табл2!$A$2:$A$2262-A2));IF((Табл2!$C$2:$C$2262=B2)*(A2<=Табл2!$A$2:$A$2262);Табл2!$A$2:$A$2262-A2);))
Изменено: БМВ - 04.12.2019 12:50:16
По вопросам из тем форума, личку не читаю.
 
 
 
#23
16.12.2019 10:14:08
Прошу прощения, что сразу не поблагодарил.
Всем огромное спасибо за помощь.  
 
 
 
Страницы: 1
Наверх