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

Вход

Регистрация

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

 

= Мир MS Excel/Подстановка значения из массива по условию в тексте - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Подстановка значения из массива по условию в тексте
Подстановка значения из массива по условию в тексте
EvaNa Дата: Среда, 09.12.2020, 13:12 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Добрый день! Уважаемые специалисты, подскажите, пожалуйста.
Столкнулась с такой задачкой. Есть список адресов, но в некоторых отсутствует индекс, а его наличие обязательно.
Ход мысли такой. Если первое значение в строке число, то ничего не менять. А если нет, то найти совпадение из списка областей (первый столбец листа Индексы) с текстом из строки. Если есть совпадение, до вставить соответствующее найденному совпадению значение (индекс из столбца 2 листа Индекс) на 0 позицию строки
 
Ответить
СообщениеДобрый день! Уважаемые специалисты, подскажите, пожалуйста.
Столкнулась с такой задачкой. Есть список адресов, но в некоторых отсутствует индекс, а его наличие обязательно.
Ход мысли такой. Если первое значение в строке число, то ничего не менять. А если нет, то найти совпадение из списка областей (первый столбец листа Индексы) с текстом из строки. Если есть совпадение, до вставить соответствующее найденному совпадению значение (индекс из столбца 2 листа Индекс) на 0 позицию строки

Автор - EvaNa
Дата добавления - 09.12.2020 в 13:12
EvaNa Дата: Среда, 09.12.2020, 13:18 | Сообщение № 2
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
... на 0 позицию строки. А если совпадения не будет, то окрасить ячейку в красный цвет (на случай ошибки недостаточных данных, ни индекса, ни региона). Вот с задачей присвоения цвета я так понимаю можно справиться только макросом.
Сама пыталась решить формулой, но вышло так, что он вставляет не соответствующий искомому значению индекс, а индекс который в такой же строке на другом листе.
К сообщению приложен файл: ___.xls(23.0 Kb)
 
Ответить
Сообщение... на 0 позицию строки. А если совпадения не будет, то окрасить ячейку в красный цвет (на случай ошибки недостаточных данных, ни индекса, ни региона). Вот с задачей присвоения цвета я так понимаю можно справиться только макросом.
Сама пыталась решить формулой, но вышло так, что он вставляет не соответствующий искомому значению индекс, а индекс который в такой же строке на другом листе.

Автор - EvaNa
Дата добавления - 09.12.2020 в 13:18
Kuzmich Дата: Среда, 09.12.2020, 17:08 | Сообщение № 3
Группа: Проверенные
Ранг: Ветеран
Сообщений: 560
Репутация: 111 ±
Замечаний: 0% ±

Excel 2003
Почему в строке 632336, Новосибирская обл., Барабинск г, Кузнечный пер, д. 5, кв. 7
индекс не Новосибирской обл.?
 
Ответить
СообщениеПочему в строке 632336, Новосибирская обл., Барабинск г, Кузнечный пер, д. 5, кв. 7
индекс не Новосибирской обл.?

Автор - Kuzmich
Дата добавления - 09.12.2020 в 17:08
EvaNa Дата: Среда, 09.12.2020, 17:21 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Kuzmich, Потому что это точный индекс данного адреса. Но если, в адресе отсутствует таковой точный индекс, то хотя бы присвоить индекс области в которую он входит
 
Ответить
СообщениеKuzmich, Потому что это точный индекс данного адреса. Но если, в адресе отсутствует таковой точный индекс, то хотя бы присвоить индекс области в которую он входит

Автор - EvaNa
Дата добавления - 09.12.2020 в 17:21
Kuzmich Дата: Среда, 09.12.2020, 17:24 | Сообщение № 5
Группа: Проверенные
Ранг: Ветеран
Сообщений: 560
Репутация: 111 ±
Замечаний: 0% ±

Excel 2003
На листе Индексы Новосибирской обл. соответствует индекс 630000
Посмотрите макрос
[vba]
Код
Sub Tablica()
Dim i As Long
Dim iLastRow As Long
Dim Region As String
Dim FoundRegion As Range
iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("C3:C" & iLastRow).Interior.ColorIndex = xlNone
With CreateObject("VBScript.RegExp")
   .IgnoreCase = True
   For i = 3 To iLastRow
     If Not IsNumeric(Left(Cells(i, "B"), 6)) Then
       .Pattern = "[А-ЯЁ]+(?= обл)"
       Region = "нет такой области"
       If .test(Cells(i, "B")) Then Region = .Execute(Cells(i, "B"))(0)
       Set FoundRegion = Worksheets("Индексы").Columns(1).Find(Region, , xlValues, xlWhole)
       If Not FoundRegion Is Nothing Then
         Cells(i, "C") = FoundRegion.Offset(, 1) & ", " & Cells(i, "B")
       Else
         Cells(i, "C") = Cells(i, "B")
         Cells(i, "C").Interior.ColorIndex = 6  'если нет области и индекса, то красим ячейку желтым
       End If
     Else
       Cells(i, "C") = Cells(i, "B")
     End If
   Next
End With
End Sub
[/vba]


Сообщение отредактировал Kuzmich - Среда, 09.12.2020, 17:25
 
Ответить
СообщениеНа листе Индексы Новосибирской обл. соответствует индекс 630000
Посмотрите макрос
[vba]
Код
Sub Tablica()
Dim i As Long
Dim iLastRow As Long
Dim Region As String
Dim FoundRegion As Range
iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("C3:C" & iLastRow).Interior.ColorIndex = xlNone
With CreateObject("VBScript.RegExp")
   .IgnoreCase = True
   For i = 3 To iLastRow
     If Not IsNumeric(Left(Cells(i, "B"), 6)) Then
       .Pattern = "[А-ЯЁ]+(?= обл)"
       Region = "нет такой области"
       If .test(Cells(i, "B")) Then Region = .Execute(Cells(i, "B"))(0)
       Set FoundRegion = Worksheets("Индексы").Columns(1).Find(Region, , xlValues, xlWhole)
       If Not FoundRegion Is Nothing Then
         Cells(i, "C") = FoundRegion.Offset(, 1) & ", " & Cells(i, "B")
       Else
         Cells(i, "C") = Cells(i, "B")
         Cells(i, "C").Interior.ColorIndex = 6  'если нет области и индекса, то красим ячейку желтым
       End If
     Else
       Cells(i, "C") = Cells(i, "B")
     End If
   Next
End With
End Sub
[/vba]

Автор - Kuzmich
Дата добавления - 09.12.2020 в 17:24
EvaNa Дата: Среда, 09.12.2020, 17:35 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Kuzmich, Все верно. Тут ка иерархия Новосибирская область (630000), в нее входят города Барыбинск (632330), Куйбышев (632380) и др. Далее в городе идет разделение по отделениям. У меня нет базы всех адресов нашей страны из которой бы подбирался точный индекс. Поэтому если в адресе не будет указан точный индекс (например Новосибирская обл., г. Куйбышев...), то тогда будет подставлен индекс области в которую он входит. Для дальнейшей обработки необходимо наличие индекса в строке адреса
 
Ответить
СообщениеKuzmich, Все верно. Тут ка иерархия Новосибирская область (630000), в нее входят города Барыбинск (632330), Куйбышев (632380) и др. Далее в городе идет разделение по отделениям. У меня нет базы всех адресов нашей страны из которой бы подбирался точный индекс. Поэтому если в адресе не будет указан точный индекс (например Новосибирская обл., г. Куйбышев...), то тогда будет подставлен индекс области в которую он входит. Для дальнейшей обработки необходимо наличие индекса в строке адреса

Автор - EvaNa
Дата добавления - 09.12.2020 в 17:35
EvaNa Дата: Среда, 09.12.2020, 18:17 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Kuzmich, Спасибо большое. Насколько я могу понять будет проверка по строкам в которых встретиться слово область (" обл"). Тогда не будут проверены Республика и Край. Я правильно понимаю? Тут мой грешок, что в списке адресов в приложенном файле таковых не оказалось
 
Ответить
СообщениеKuzmich, Спасибо большое. Насколько я могу понять будет проверка по строкам в которых встретиться слово область (" обл"). Тогда не будут проверены Республика и Край. Я правильно понимаю? Тут мой грешок, что в списке адресов в приложенном файле таковых не оказалось

Автор - EvaNa
Дата добавления - 09.12.2020 в 18:17
Kuzmich Дата: Среда, 09.12.2020, 18:20 | Сообщение № 8
Группа: Проверенные
Ранг: Ветеран
Сообщений: 560
Репутация: 111 ±
Замечаний: 0% ±

Excel 2003
Если нет области и индекса, то красим ячейку желтым


Сообщение отредактировал Kuzmich - Среда, 09.12.2020, 18:21
 
Ответить
СообщениеЕсли нет области и индекса, то красим ячейку желтым

Автор - Kuzmich
Дата добавления - 09.12.2020 в 18:20
EvaNa Дата: Среда, 09.12.2020, 18:45 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Спасибо большое еще раз! С остальными регионами постараюсь доделать сама
 
Ответить
СообщениеСпасибо большое еще раз! С остальными регионами постараюсь доделать сама

Автор - EvaNa
Дата добавления - 09.12.2020 в 18:45
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Подстановка значения из массива по условию в тексте
  • Страница 1 из 1
  • 1
Поиск:

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