Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Подбор ближайшего значения (текста) - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Подбор ближайшего значения (текста) (Макросы/Sub)
Подбор ближайшего значения (текста)
ZamoK Дата: Четверг, 11.05.2017, 12:53 | Сообщение № 1
Группа: Проверенные
Ранг: Обитатель
Сообщений: 257
Репутация: 4 ±
Замечаний: 0% ±

Excel 2003-2016
Добрый день!

Нужна помощь специалиста VBA.

Настоящая таблица очень большая порядка 8 - 10 тыс. строк на листе "Данные" тоже самое, потому файл обрезан до минимума.

Суть: нужно привести столбец "наименование" к ближайшему подходящему значению с листа "Данные", т.е. заменить значение. Ключом является цифровая часть наименования. Прочитал много тем по замене текста, перепробовал все! Но к сожалению либо печаль, либо куча несоответствий. Потому решил попытать счастья тут. Пробовал обработку найти и заменить, но она не даёт 100% результата. Предположительно думаю должен быть макрос, с использованием библиотек т.к. объём оч внушительный.

Любая помощь, совет, направление :D приветствуется.
К сообщению приложен файл: _2.xlsm(73Kb)


Я не Гуру, но стремлюсь!

Сообщение отредактировал ZamoK - Пятница, 12.05.2017, 09:32
 
Ответить
СообщениеДобрый день!

Нужна помощь специалиста VBA.

Настоящая таблица очень большая порядка 8 - 10 тыс. строк на листе "Данные" тоже самое, потому файл обрезан до минимума.

Суть: нужно привести столбец "наименование" к ближайшему подходящему значению с листа "Данные", т.е. заменить значение. Ключом является цифровая часть наименования. Прочитал много тем по замене текста, перепробовал все! Но к сожалению либо печаль, либо куча несоответствий. Потому решил попытать счастья тут. Пробовал обработку найти и заменить, но она не даёт 100% результата. Предположительно думаю должен быть макрос, с использованием библиотек т.к. объём оч внушительный.

Любая помощь, совет, направление :D приветствуется.

Автор - ZamoK
Дата добавления - 11.05.2017 в 12:53
wild_pig Дата: Четверг, 11.05.2017, 16:20 | Сообщение № 2
Группа: Проверенные
Ранг: Обитатель
Сообщений: 485
Репутация: 91 ±
Замечаний: 0% ±

2003, 2013
Хоть бы показали эти самые "соответствия" в файле.
 
Ответить
СообщениеХоть бы показали эти самые "соответствия" в файле.

Автор - wild_pig
Дата добавления - 11.05.2017 в 16:20
ZamoK Дата: Пятница, 12.05.2017, 09:37 | Сообщение № 3
Группа: Проверенные
Ранг: Обитатель
Сообщений: 257
Репутация: 4 ±
Замечаний: 0% ±

Excel 2003-2016
Перезалил файлик.
Не знаю какой алгоритм будет лучше, но если бы допустим появлялось какое-либо диалоговое окно в случае отсутствия позиции на листе "Данные", то было бы совсем хорошо, но это по возможности.


Я не Гуру, но стремлюсь!
 
Ответить
СообщениеПерезалил файлик.
Не знаю какой алгоритм будет лучше, но если бы допустим появлялось какое-либо диалоговое окно в случае отсутствия позиции на листе "Данные", то было бы совсем хорошо, но это по возможности.

Автор - ZamoK
Дата добавления - 12.05.2017 в 09:37
sboy Дата: Пятница, 12.05.2017, 11:00 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 769
Репутация: 200 ±
Замечаний: 0% ±

Excel 2010
Добрый день.
Вариант формулой (массива)
Код
=ИНДЕКС(Данные!$A$2:$A$261;МАКС(ЕЧИСЛО(ПОИСК(ПОДСТАВИТЬ(ПОДСТАВИТЬ(Данные!$A$2:$A$261;".";"");" ";"");ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;".";"");" ";"")))*СТРОКА($A$1:$A$260)))
К сообщению приложен файл: _2-1-.xlsm(75Kb)
 
Ответить
СообщениеДобрый день.
Вариант формулой (массива)
Код
=ИНДЕКС(Данные!$A$2:$A$261;МАКС(ЕЧИСЛО(ПОИСК(ПОДСТАВИТЬ(ПОДСТАВИТЬ(Данные!$A$2:$A$261;".";"");" ";"");ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;".";"");" ";"")))*СТРОКА($A$1:$A$260)))

Автор - sboy
Дата добавления - 12.05.2017 в 11:00
ZamoK Дата: Пятница, 12.05.2017, 14:17 | Сообщение № 5
Группа: Проверенные
Ранг: Обитатель
Сообщений: 257
Репутация: 4 ±
Замечаний: 0% ±

Excel 2003-2016
sboy, Диапазоны: Данные!$A$2:$A$261 и $A$1:$A$260 не постоянны, поэтому оч сложно

Настоящая таблица очень большая порядка 8 - 10 тыс. строк на листе "Данные" тоже самое, потому файл обрезан до минимума.

Меняются ежедневно.


Я не Гуру, но стремлюсь!
 
Ответить
Сообщениеsboy, Диапазоны: Данные!$A$2:$A$261 и $A$1:$A$260 не постоянны, поэтому оч сложно

Настоящая таблица очень большая порядка 8 - 10 тыс. строк на листе "Данные" тоже самое, потому файл обрезан до минимума.

Меняются ежедневно.

Автор - ZamoK
Дата добавления - 12.05.2017 в 14:17
sboy Дата: Пятница, 12.05.2017, 14:51 | Сообщение № 6
Группа: Проверенные
Ранг: Ветеран
Сообщений: 769
Репутация: 200 ±
Замечаний: 0% ±

Excel 2010
Диапазоныне постоянны, поэтому оч сложно

в диспетчер имен "Данные"
Код
=Данные!$A$1:ИНДЕКС(Данные!$A:$A;СЧЁТЗ(Данные!$A:$A))

формула
Код
=ИНДЕКС(Данные;МАКС(ЕЧИСЛО(ПОИСК(ПОДСТАВИТЬ(ПОДСТАВИТЬ(Данные;".";"");" ";"");ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;".";"");" ";"")))*СТРОКА(Данные)))
К сообщению приложен файл: 5045966.xlsm(75Kb)
 
Ответить
Сообщение
Диапазоныне постоянны, поэтому оч сложно

в диспетчер имен "Данные"
Код
=Данные!$A$1:ИНДЕКС(Данные!$A:$A;СЧЁТЗ(Данные!$A:$A))

формула
Код
=ИНДЕКС(Данные;МАКС(ЕЧИСЛО(ПОИСК(ПОДСТАВИТЬ(ПОДСТАВИТЬ(Данные;".";"");" ";"");ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;".";"");" ";"")))*СТРОКА(Данные)))

Автор - sboy
Дата добавления - 12.05.2017 в 14:51
ZamoK Дата: Пятница, 12.05.2017, 16:16 | Сообщение № 7
Группа: Проверенные
Ранг: Обитатель
Сообщений: 257
Репутация: 4 ±
Замечаний: 0% ±

Excel 2003-2016
sboy, Вроде все хорошо, только есть номера с двумя точками на конце (я этот момент упустил) формула подхватывает именно его, а нужен без точек.
Файл обновил

И ещё у меня макрос протягивает формулу до конца списка
[vba]
Код
Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row).Formula = [G2].FormulaR1C1 'протягиваем формулу
[/vba]
Он её превращает в простую формулу из формулы массива, может как-то по другому протягивать?
К сообщению приложен файл: 5363382.xlsm(75Kb)


Я не Гуру, но стремлюсь!

Сообщение отредактировал ZamoK - Пятница, 12.05.2017, 16:18
 
Ответить
Сообщениеsboy, Вроде все хорошо, только есть номера с двумя точками на конце (я этот момент упустил) формула подхватывает именно его, а нужен без точек.
Файл обновил

И ещё у меня макрос протягивает формулу до конца списка
[vba]
Код
Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row).Formula = [G2].FormulaR1C1 'протягиваем формулу
[/vba]
Он её превращает в простую формулу из формулы массива, может как-то по другому протягивать?

Автор - ZamoK
Дата добавления - 12.05.2017 в 16:16
sboy Дата: Пятница, 12.05.2017, 16:19 | Сообщение № 8
Группа: Проверенные
Ранг: Ветеран
Сообщений: 769
Репутация: 200 ±
Замечаний: 0% ±

Excel 2010
Для формулы массива нужно в коде писать
[vba]
Код
.FormulaArray
[/vba]
По поводу двух точек не совсем понял, покажите в файле
 
Ответить
СообщениеДля формулы массива нужно в коде писать
[vba]
Код
.FormulaArray
[/vba]
По поводу двух точек не совсем понял, покажите в файле

Автор - sboy
Дата добавления - 12.05.2017 в 16:19
ZamoK Дата: Пятница, 12.05.2017, 16:31 | Сообщение № 9
Группа: Проверенные
Ранг: Обитатель
Сообщений: 257
Репутация: 4 ±
Замечаний: 0% ±

Excel 2003-2016
Все ровно результата нет, хоть .FormulaArray хоть .Formula - все ровно убивает массив.


Я не Гуру, но стремлюсь!
 
Ответить
СообщениеВсе ровно результата нет, хоть .FormulaArray хоть .Formula - все ровно убивает массив.

Автор - ZamoK
Дата добавления - 12.05.2017 в 16:31
sboy Дата: Пятница, 12.05.2017, 16:57 | Сообщение № 10
Группа: Проверенные
Ранг: Ветеран
Сообщений: 769
Репутация: 200 ±
Замечаний: 0% ±

Excel 2010
[vba]
Код
Sub formula()
    fa = "=INDEX(Данные,MAX(ISNUMBER(SEARCH(SUBSTITUTE(SUBSTITUTE(Данные,""."",""""),"" "",""""),SUBSTITUTE(SUBSTITUTE(RC[-5],""."",""""),"" "","""")))*ROW(Данные)))"
    For Each cl In Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row).Cells
       cl.FormulaArray = fa
    Next cl
End Sub
[/vba]
так работает
 
Ответить
Сообщение[vba]
Код
Sub formula()
    fa = "=INDEX(Данные,MAX(ISNUMBER(SEARCH(SUBSTITUTE(SUBSTITUTE(Данные,""."",""""),"" "",""""),SUBSTITUTE(SUBSTITUTE(RC[-5],""."",""""),"" "","""")))*ROW(Данные)))"
    For Each cl In Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row).Cells
       cl.FormulaArray = fa
    Next cl
End Sub
[/vba]
так работает

Автор - sboy
Дата добавления - 12.05.2017 в 16:57
ZamoK Дата: Понедельник, 15.05.2017, 08:25 | Сообщение № 11
Группа: Проверенные
Ранг: Обитатель
Сообщений: 257
Репутация: 4 ±
Замечаний: 0% ±

Excel 2003-2016
Не не хочет, или я что не так сделал?
К сообщению приложен файл: 6290146.xlsm(81Kb)


Я не Гуру, но стремлюсь!

Сообщение отредактировал ZamoK - Понедельник, 15.05.2017, 08:34
 
Ответить
СообщениеНе не хочет, или я что не так сделал?

Автор - ZamoK
Дата добавления - 15.05.2017 в 08:25
sboy Дата: Понедельник, 15.05.2017, 09:27 | Сообщение № 12
Группа: Проверенные
Ранг: Ветеран
Сообщений: 769
Репутация: 200 ±
Замечаний: 0% ±

Excel 2010
ZamoK, ваш файл работает нормально, макросом формула нормально вставляется... Что Вы от него ждете?
Если Вы хотите, чтобы заменялись данные в столбце В, то макрос нужно дописывать
 
Ответить
СообщениеZamoK, ваш файл работает нормально, макросом формула нормально вставляется... Что Вы от него ждете?
Если Вы хотите, чтобы заменялись данные в столбце В, то макрос нужно дописывать

Автор - sboy
Дата добавления - 15.05.2017 в 09:27
ZamoK Дата: Понедельник, 15.05.2017, 09:58 | Сообщение № 13
Группа: Проверенные
Ранг: Обитатель
Сообщений: 257
Репутация: 4 ±
Замечаний: 0% ±

Excel 2003-2016
sboy, Вроде все хорошо, только есть номера с двумя точками на конце (я этот момент упустил) формула подхватывает именно его, а нужен без точек.


Вот я про что! , но если это никак наверно автозамену надо будет дописать или как-то по другому можно решить?


Я не Гуру, но стремлюсь!
 
Ответить
Сообщение
sboy, Вроде все хорошо, только есть номера с двумя точками на конце (я этот момент упустил) формула подхватывает именно его, а нужен без точек.


Вот я про что! , но если это никак наверно автозамену надо будет дописать или как-то по другому можно решить?

Автор - ZamoK
Дата добавления - 15.05.2017 в 09:58
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Подбор ближайшего значения (текста) (Макросы/Sub)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2017 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!