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

Вход

Регистрация

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

 

= Мир MS Excel/игнорирование буквенного текста в цифровом коде функции ВПР - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » игнорирование буквенного текста в цифровом коде функции ВПР (Макросы/Sub)
игнорирование буквенного текста в цифровом коде функции ВПР
grh1 Дата: Воскресенье, 11.02.2018, 11:47 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 74
Репутация: 0 ±
Замечаний: 0% ±

Уважаемые знатоки!

Подскажите пожалуйста, как можно заставить функцию ВПР игнорировать буквенные обозначения кодов в столбце «код»
На этом скрине сам вопрос.



Формула такая:
Код
=ВПР(A40;'наименован кодов'!A:B;2)


где:
А40 – ячейка с кодом;
наименован кодов – наименование листа, в котором таблица с кодами и их названиями;
А:В – диапазон – в столбце «А» – коды, в столбце «В» их название;
2 – второй столбец с названиями кодов.

Как сделать, чтобы эта функция ВПР игнорировала буквенный текст в названии кодов, а руководствовалась бы только четырьмя ЦИФРАМИ?

Текстовые добавления будут разные и много – это сейчас только одно добавление «СФ»… так как начало года еще.

Варианты:
1. Существует ДРУГАЯ формула ВПР;
2. Если нет такой формулы, то возможно ли кодом VBA что-либо сделать?

Спасибо.


Сообщение отредактировал grh1 - Воскресенье, 11.02.2018, 18:52
 
Ответить
СообщениеУважаемые знатоки!

Подскажите пожалуйста, как можно заставить функцию ВПР игнорировать буквенные обозначения кодов в столбце «код»
На этом скрине сам вопрос.



Формула такая:
Код
=ВПР(A40;'наименован кодов'!A:B;2)


где:
А40 – ячейка с кодом;
наименован кодов – наименование листа, в котором таблица с кодами и их названиями;
А:В – диапазон – в столбце «А» – коды, в столбце «В» их название;
2 – второй столбец с названиями кодов.

Как сделать, чтобы эта функция ВПР игнорировала буквенный текст в названии кодов, а руководствовалась бы только четырьмя ЦИФРАМИ?

Текстовые добавления будут разные и много – это сейчас только одно добавление «СФ»… так как начало года еще.

Варианты:
1. Существует ДРУГАЯ формула ВПР;
2. Если нет такой формулы, то возможно ли кодом VBA что-либо сделать?

Спасибо.

Автор - grh1
Дата добавления - 11.02.2018 в 11:47
bmv98rus Дата: Воскресенье, 11.02.2018, 12:09 | Сообщение № 2
Группа: Проверенные
Ранг: Старожил
Сообщений: 1251
Репутация: 213 ±
Замечаний: 0% ±

Excel 2013/2016
ответ 3. существует формула с использованием ВПР и дополнительных функций, но он требует серьезной проработки указанного тут материала.


Сообщение отредактировал bmv98rus - Воскресенье, 11.02.2018, 12:10
 
Ответить
Сообщениеответ 3. существует формула с использованием ВПР и дополнительных функций, но он требует серьезной проработки указанного тут материала.

Автор - bmv98rus
Дата добавления - 11.02.2018 в 12:09
fan-vba Дата: Воскресенье, 11.02.2018, 17:08 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 10 ±
Замечаний: 0% ±

Excel 2007
Не знаю, правильно ли я понял суть задачи, но сделал следующее. данная функция ВПР2 работает, как формула на листах и отбрасывает все текстовые символы из таблицы для поиска при поиске числовых значений:
[vba]
Код
Function ВПР2(Table As Variant, SearchColumnNum As Long, SearchValue As Variant, _
N As Long, ResultColumnNum As Long)
Dim i As Long, iCount As Long, temp As Variant, st As Long
Select Case TypeName(Table)
Case "Range"
For i = 1 To Table.Rows.Count
st = 0
For j = 1 To Len(Table.Cells(i, SearchColumnNum))
temp = Mid(Table.Cells(i, SearchColumnNum), j, 1)
If IsNumeric(temp) Then
st = st & temp
End If
Next j
If Val(st) = SearchValue Then
iCount = iCount + 1
End If
If iCount = N Then
ВПР2 = Table.Cells(i, ResultColumnNum)
Exit For
End If
Next i
Case "Variant()"
For i = 1 To UBound(Table)
If Val(st) = SearchValue Then iCount = iCount + 1
If iCount = N Then
ВПР2 = Table(i, ResultColumnNum)
Exit For
End If
Next i
End Select
End Function
[/vba]
Код
=ВПР2(таблица; номер_столбца_где_ищем; искомое_значение; N; номер_столбца_из_которого_берем_значение)

извините, я тоже поправился... я еще не до конца тут освоился)


Сообщение отредактировал fan-vba - Воскресенье, 11.02.2018, 18:38
 
Ответить
СообщениеНе знаю, правильно ли я понял суть задачи, но сделал следующее. данная функция ВПР2 работает, как формула на листах и отбрасывает все текстовые символы из таблицы для поиска при поиске числовых значений:
[vba]
Код
Function ВПР2(Table As Variant, SearchColumnNum As Long, SearchValue As Variant, _
N As Long, ResultColumnNum As Long)
Dim i As Long, iCount As Long, temp As Variant, st As Long
Select Case TypeName(Table)
Case "Range"
For i = 1 To Table.Rows.Count
st = 0
For j = 1 To Len(Table.Cells(i, SearchColumnNum))
temp = Mid(Table.Cells(i, SearchColumnNum), j, 1)
If IsNumeric(temp) Then
st = st & temp
End If
Next j
If Val(st) = SearchValue Then
iCount = iCount + 1
End If
If iCount = N Then
ВПР2 = Table.Cells(i, ResultColumnNum)
Exit For
End If
Next i
Case "Variant()"
For i = 1 To UBound(Table)
If Val(st) = SearchValue Then iCount = iCount + 1
If iCount = N Then
ВПР2 = Table(i, ResultColumnNum)
Exit For
End If
Next i
End Select
End Function
[/vba]
Код
=ВПР2(таблица; номер_столбца_где_ищем; искомое_значение; N; номер_столбца_из_которого_берем_значение)

извините, я тоже поправился... я еще не до конца тут освоился)

Автор - fan-vba
Дата добавления - 11.02.2018 в 17:08
bmv98rus Дата: Воскресенье, 11.02.2018, 17:37 | Сообщение № 4
Группа: Проверенные
Ранг: Старожил
Сообщений: 1251
Репутация: 213 ±
Замечаний: 0% ±

Excel 2013/2016
Код
=VLOOKUP(--LEFT(A40;FIND(" ";A40&" ")-1);A:B;2;)


[p.s.]Да увидит модератор, я держался пять часов :-)[/p.s.]


Сообщение отредактировал bmv98rus - Воскресенье, 11.02.2018, 17:38
 
Ответить
Сообщение
Код
=VLOOKUP(--LEFT(A40;FIND(" ";A40&" ")-1);A:B;2;)


[p.s.]Да увидит модератор, я держался пять часов :-)[/p.s.]

Автор - bmv98rus
Дата добавления - 11.02.2018 в 17:37
Pelena Дата: Воскресенье, 11.02.2018, 18:14 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 12686
Репутация: 2791 ±
Замечаний: 0% ±

Excel 2010, 2016 & Mac Excel
grh1, вроде не первый день на форуме, формула без тегов, файла с примером нет. Исправляйте >(

держался пять часов
Я оценила)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщениеgrh1, вроде не первый день на форуме, формула без тегов, файла с примером нет. Исправляйте >(

держался пять часов
Я оценила)

Автор - Pelena
Дата добавления - 11.02.2018 в 18:14
grh1 Дата: Воскресенье, 11.02.2018, 18:49 | Сообщение № 6
Группа: Пользователи
Ранг: Участник
Сообщений: 74
Репутация: 0 ±
Замечаний: 0% ±

всем спасибо за помощь...
уважаемый nilem помог такой формулой:
Код
=ВПР(ЛЕВСИМВ(A40;4)+0;'наименован кодов'!A:B;2)

всё работает.
Вопрос закрыт


Сообщение отредактировал grh1 - Воскресенье, 11.02.2018, 18:54
 
Ответить
Сообщениевсем спасибо за помощь...
уважаемый nilem помог такой формулой:
Код
=ВПР(ЛЕВСИМВ(A40;4)+0;'наименован кодов'!A:B;2)

всё работает.
Вопрос закрыт

Автор - grh1
Дата добавления - 11.02.2018 в 18:49
fan-vba Дата: Воскресенье, 11.02.2018, 19:03 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 10 ±
Замечаний: 0% ±

Excel 2007
опять я все усложняю), отвык я почти в формулах ковыряться... хотя тоже норм вариант вроде вышел))
 
Ответить
Сообщениеопять я все усложняю), отвык я почти в формулах ковыряться... хотя тоже норм вариант вроде вышел))

Автор - fan-vba
Дата добавления - 11.02.2018 в 19:03
Pelena Дата: Воскресенье, 11.02.2018, 19:06 | Сообщение № 8
Группа: Модераторы
Ранг: Местный житель
Сообщений: 12686
Репутация: 2791 ±
Замечаний: 0% ±

Excel 2010, 2016 & Mac Excel
уважаемый nilem помог
И когда успел :D :D

тоже норм вариант вроде вышел))
чем больше вариантов, тем лучше yes


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщение
уважаемый nilem помог
И когда успел :D :D

тоже норм вариант вроде вышел))
чем больше вариантов, тем лучше yes

Автор - Pelena
Дата добавления - 11.02.2018 в 19:06
bmv98rus Дата: Воскресенье, 11.02.2018, 19:14 | Сообщение № 9
Группа: Проверенные
Ранг: Старожил
Сообщений: 1251
Репутация: 213 ±
Замечаний: 0% ±

Excel 2013/2016
[offtop]
отвык я почти в формулах ковыряться
Поменяете ник на fan-fx, это резко меняет привычки :-)[/offtop]
 
Ответить
Сообщение[offtop]
отвык я почти в формулах ковыряться
Поменяете ник на fan-fx, это резко меняет привычки :-)[/offtop]

Автор - bmv98rus
Дата добавления - 11.02.2018 в 19:14
fan-vba Дата: Воскресенье, 11.02.2018, 19:19 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 10 ±
Замечаний: 0% ±

Excel 2007
Поменяете ник на fan-fx, это резко меняет привычки :-)

я раньше и был fan-fx, 3-х этажные формулы составлял,но потом мне даже этого стало не хватать под мои задачи и я ушел с головой в vba, а fx стал немного подзабывать... но я учту)))
 
Ответить
Сообщение
Поменяете ник на fan-fx, это резко меняет привычки :-)

я раньше и был fan-fx, 3-х этажные формулы составлял,но потом мне даже этого стало не хватать под мои задачи и я ушел с головой в vba, а fx стал немного подзабывать... но я учту)))

Автор - fan-vba
Дата добавления - 11.02.2018 в 19:19
Мир MS Excel » Вопросы и решения » Вопросы по VBA » игнорирование буквенного текста в цифровом коде функции ВПР (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

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