Есть такая задачка: При сравнении кодов из таблицы 1 с кодами из таблицы 2, если первая итерация не обнаруживает кода из таблицы 1 в таблице 2, то из кода таблицы 1 удаляется правый символ и проверка происходит ещё раз. Правые символы отбрасываются до тех пор, пока код из таблицы 1 не будет соответствовать точному коду из таблицы 2. В этом случае, к таблице 1 подставляется цена.
Возможно, реализовать можно только макросом.
Добрый день.
Есть такая задачка: При сравнении кодов из таблицы 1 с кодами из таблицы 2, если первая итерация не обнаруживает кода из таблицы 1 в таблице 2, то из кода таблицы 1 удаляется правый символ и проверка происходит ещё раз. Правые символы отбрасываются до тех пор, пока код из таблицы 1 не будет соответствовать точному коду из таблицы 2. В этом случае, к таблице 1 подставляется цена.
Возможно, реализовать можно только макросом.VadimVV
Function vpr(cll As Range, rng As Range, col As Integer) a = cll For i = Len(cll) - 1 To 1 Step -1 On Error Resume Next vpr = WorksheetFunction.VLookup(a, rng, col, 0) If vpr <> 0 Then Exit Function a = --Left(a, i) Next End Function
[/vba]
Не знаю, что-то наворотил типа УДФ: [vba]
Код
Function vpr(cll As Range, rng As Range, col As Integer) a = cll For i = Len(cll) - 1 To 1 Step -1 On Error Resume Next vpr = WorksheetFunction.VLookup(a, rng, col, 0) If vpr <> 0 Then Exit Function a = --Left(a, i) Next End Function
получаем из исходного числа - горизонтальный список возможных чисел-кодов для сравнения, отрезая по одному символу с конца (на самом деле, просто берем 5, 4, 3,.. символа от начала, и преобразуем результат в числа). Например, если исследуем число 89117 - то получим массив {89117;8911;891;89;1}
теперь берем два вектора - вертикальный $D$3:$D$6, содержащий коды для сравнения, и горизонтальный (вычисленный выше), содержащий коды для поиска. Математика говорит нам, что если мы попарно поделим все числа друг на друга, то при совпадающих числах получим 1. Для умножения мы воспользуемся формулой a/b == (1/b)*(a), а для попарного перемножения - векторным/матричным произведением (встроенной функцией МУМНОЖ). Не вдаваясь в подробности - мы должны получить матрицу чисел (строк в ней столько, сколько в диапазоне $D$3:$D$6 = 4, столбцов в ней столько, сколько в векторе для сравнения = 5), причем значение=1 в ячейках этой матрицы как раз означает, что сравниваемые коды совпали
теперь сравниваем каждый из элементов матрицы с единицей - и получаем такую же матрицу-массив, содержащую значение ИСТИНА (=1) в тех ячейках, где коды совпали. Если мы теперь скалярно домножим эту матрицу на вектор с номерами строк СТРОКА($D$3:$D$6) - то получим матрицу-массив, содержащий нули и, в каких-то ячейках, - номера строк листа, где происходят совпадения. Тут есть небольшая тонкость - если совпадение одно - то первая формула (с СУММПРОИЗВ) будет работать корректно, и при этом останется немассивной. Если же совпадений несколько - первая формула выдаст неверный результат (вернее, её можно модифицировать до большей "монструозности", и она всё же выдаст верный результат). В то же время, второй вариант формулы - выдаст верный результат. НО! - вторая формула - массивная. Ну и далее всё просто - нам остается только преобразовать полученный номер строки на листе в номер строки в диапазоне поиска (СтрокаНаЛисте-СТРОКА($D$3:$D$6)+1) как раз дает номер найденной строки в диапазоне, а затем - просто выбрать нужное результирующее значение из таблицы поиска, используя ИНДЕКС()
Формулы, выданные участниками данной темы, если они подходят и для работы со строками (или переделанные под это), можно поместить в "Эффективные решения" типа как "Нахождение первого совпадения между двумя наборами/диапазонами" :)
получаем из исходного числа - горизонтальный список возможных чисел-кодов для сравнения, отрезая по одному символу с конца (на самом деле, просто берем 5, 4, 3,.. символа от начала, и преобразуем результат в числа). Например, если исследуем число 89117 - то получим массив {89117;8911;891;89;1}
теперь берем два вектора - вертикальный $D$3:$D$6, содержащий коды для сравнения, и горизонтальный (вычисленный выше), содержащий коды для поиска. Математика говорит нам, что если мы попарно поделим все числа друг на друга, то при совпадающих числах получим 1. Для умножения мы воспользуемся формулой a/b == (1/b)*(a), а для попарного перемножения - векторным/матричным произведением (встроенной функцией МУМНОЖ). Не вдаваясь в подробности - мы должны получить матрицу чисел (строк в ней столько, сколько в диапазоне $D$3:$D$6 = 4, столбцов в ней столько, сколько в векторе для сравнения = 5), причем значение=1 в ячейках этой матрицы как раз означает, что сравниваемые коды совпали
теперь сравниваем каждый из элементов матрицы с единицей - и получаем такую же матрицу-массив, содержащую значение ИСТИНА (=1) в тех ячейках, где коды совпали. Если мы теперь скалярно домножим эту матрицу на вектор с номерами строк СТРОКА($D$3:$D$6) - то получим матрицу-массив, содержащий нули и, в каких-то ячейках, - номера строк листа, где происходят совпадения. Тут есть небольшая тонкость - если совпадение одно - то первая формула (с СУММПРОИЗВ) будет работать корректно, и при этом останется немассивной. Если же совпадений несколько - первая формула выдаст неверный результат (вернее, её можно модифицировать до большей "монструозности", и она всё же выдаст верный результат). В то же время, второй вариант формулы - выдаст верный результат. НО! - вторая формула - массивная. Ну и далее всё просто - нам остается только преобразовать полученный номер строки на листе в номер строки в диапазоне поиска (СтрокаНаЛисте-СТРОКА($D$3:$D$6)+1) как раз дает номер найденной строки в диапазоне, а затем - просто выбрать нужное результирующее значение из таблицы поиска, используя ИНДЕКС()
Формулы, выданные участниками данной темы, если они подходят и для работы со строками (или переделанные под это), можно поместить в "Эффективные решения" типа как "Нахождение первого совпадения между двумя наборами/диапазонами" :)AndreTM
Skype: andre.tm.007 Donate: Qiwi: 9517375010
Сообщение отредактировал AndreTM - Пятница, 08.05.2015, 13:49
А, ну да... это как раз несколько возможных совпадений. Тогда там надо вычислять не просто строку совпадения, а строку с наиболее длинным совпадающим кодом (скажем, добавив к числам ещё номер столбца матрицы, умноженный на 10e7). Но сам принцип тоже имеет право на существование
А, ну да... это как раз несколько возможных совпадений. Тогда там надо вычислять не просто строку совпадения, а строку с наиболее длинным совпадающим кодом (скажем, добавив к числам ещё номер столбца матрицы, умноженный на 10e7). Но сам принцип тоже имеет право на существование AndreTM
Конечно. Принцип хорош. А уж объяснялка вообще блеск! Читал с наслаждением. Только тебе нужен не МАКС, а МИН - минимальное количество "отрезанных" символов. Примерно вот так
Конечно. Принцип хорош. А уж объяснялка вообще блеск! Читал с наслаждением. Только тебе нужен не МАКС, а МИН - минимальное количество "отрезанных" символов. Примерно вот так
Гениальные формулы. Спасибо! Буду разбираться и ковырять. Раньше думал, что знаю эксель на каком-то более-менее приемлемом уровне. Сейчас понимаю, что мои познания эксель ограничивались уровнем "Hello, World".
Гениальные формулы. Спасибо! Буду разбираться и ковырять. Раньше думал, что знаю эксель на каком-то более-менее приемлемом уровне. Сейчас понимаю, что мои познания эксель ограничивались уровнем "Hello, World".VadimVV
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
