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

Вход

Регистрация

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

 

= Мир MS Excel/Как работает простая функция-аналог ВПР. - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Как работает простая функция-аналог ВПР. (Макросы/Sub)
Как работает простая функция-аналог ВПР.
moland Дата: Пятница, 10.11.2017, 15:37 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Встретил на форуме такую функцию-аналог ВПР.

[vba]
Код
Function VPR(ByRef cell As Range, ByRef rng As Range, col&
VPR = rng.Find(cell, , xlValues, 1).Offset(0, IIf(col + Abs(col), col - 1, 1 + col))
End Function.
[/vba]

Как я ее понимаю
1. Функция имеет 3 переменных: cell и rng типов range и col (какой тип данных у col и зачем стоит знак "&", что будет если его ("&") убрать.
2. rng - диапазон в котором производится поиск
3. Далее метод Find (cell - то что ищем, lookIn=xlValues (по значениям), а 1 это что? lookAt=xlWhole или matchCase= true.
4. В функции IIf должно сначала идти условие но там стоит col + Abs(col) - возвращает либо ноль либо удвоенное col. насколько я понимаю. Что это за условие?
5. Что такое col в этой функции?
6. Как мне сделать так чтобы она работала для моего случая. (вставила значения толщины на первый лист "MaterialsNorms" из листа "справочник" поиск по диаметру)
К сообщению приложен файл: vba___PE-RT-tes.xlsm(29Kb)


Сообщение отредактировал moland - Пятница, 10.11.2017, 16:48
 
Ответить
СообщениеВстретил на форуме такую функцию-аналог ВПР.

[vba]
Код
Function VPR(ByRef cell As Range, ByRef rng As Range, col&
VPR = rng.Find(cell, , xlValues, 1).Offset(0, IIf(col + Abs(col), col - 1, 1 + col))
End Function.
[/vba]

Как я ее понимаю
1. Функция имеет 3 переменных: cell и rng типов range и col (какой тип данных у col и зачем стоит знак "&", что будет если его ("&") убрать.
2. rng - диапазон в котором производится поиск
3. Далее метод Find (cell - то что ищем, lookIn=xlValues (по значениям), а 1 это что? lookAt=xlWhole или matchCase= true.
4. В функции IIf должно сначала идти условие но там стоит col + Abs(col) - возвращает либо ноль либо удвоенное col. насколько я понимаю. Что это за условие?
5. Что такое col в этой функции?
6. Как мне сделать так чтобы она работала для моего случая. (вставила значения толщины на первый лист "MaterialsNorms" из листа "справочник" поиск по диаметру)

Автор - moland
Дата добавления - 10.11.2017 в 15:37
buchlotnik Дата: Пятница, 10.11.2017, 16:30 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3327
Репутация: 886 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
moland, прочитайте правила форума и оформите код тегами (кнопка #)
1. Знак & определяет тип данных - Long
2. Да
3. lookAt=xlWhole
4. col + Abs(col) даст ноль для отрицательного col и НЕ ноль для положительного - ноль - это False, не ноль - True
5. как и в обычном ВПР - номер столбца, только может быть отрицательным
6.
Код
=ЕСЛИ(D10;VPR(D10;Справочник!$A$2:$B$12;2);"")
Только не ясно, чем обычный ВПР() не угодил?
К сообщению приложен файл: vba_PE-RT-tes.xlsm(22Kb)


каждому For - Next!
платная помощь:
ЯД: 410012595572239
buchlotnik@mail.ru


Сообщение отредактировал buchlotnik - Пятница, 10.11.2017, 16:42
 
Ответить
Сообщениеmoland, прочитайте правила форума и оформите код тегами (кнопка #)
1. Знак & определяет тип данных - Long
2. Да
3. lookAt=xlWhole
4. col + Abs(col) даст ноль для отрицательного col и НЕ ноль для положительного - ноль - это False, не ноль - True
5. как и в обычном ВПР - номер столбца, только может быть отрицательным
6.
Код
=ЕСЛИ(D10;VPR(D10;Справочник!$A$2:$B$12;2);"")
Только не ясно, чем обычный ВПР() не угодил?

Автор - buchlotnik
Дата добавления - 10.11.2017 в 16:30
moland Дата: Пятница, 10.11.2017, 16:53 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Причина по которой я ищу замену функции ВПР. Это дальнейшая интеграция кода из эксель в аксесс.
1. Вышеприведенный код тоже использует встроенные функции в эксель?
2. И его нельзя будет добавить в аксесс, просто заменив объекты эксель (воркшитс, селлс и прочее)?
 
Ответить
СообщениеПричина по которой я ищу замену функции ВПР. Это дальнейшая интеграция кода из эксель в аксесс.
1. Вышеприведенный код тоже использует встроенные функции в эксель?
2. И его нельзя будет добавить в аксесс, просто заменив объекты эксель (воркшитс, селлс и прочее)?

Автор - moland
Дата добавления - 10.11.2017 в 16:53
buchlotnik Дата: Пятница, 10.11.2017, 17:01 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3327
Репутация: 886 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
moland,
1 - нет не использует (функции), но использует константы и метод VBA. В Access вроде .FindRecord
2 - а зачем? какова конечная цель? У СУБД есть встроенные средства, зачем там вообще ВПР()?


каждому For - Next!
платная помощь:
ЯД: 410012595572239
buchlotnik@mail.ru


Сообщение отредактировал buchlotnik - Пятница, 10.11.2017, 17:07
 
Ответить
Сообщениеmoland,
1 - нет не использует (функции), но использует константы и метод VBA. В Access вроде .FindRecord
2 - а зачем? какова конечная цель? У СУБД есть встроенные средства, зачем там вообще ВПР()?

Автор - buchlotnik
Дата добавления - 10.11.2017 в 17:01
krosav4ig Дата: Пятница, 10.11.2017, 17:13 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1480
Репутация: 599 ±
Замечаний: 0% ±

Excel 2007, 2013
зачем стоит знак "&"
это символ типа данных (Long)
что будет если его ("&") убрать
Тип данных станет variant
должно сначала идти условие но там стоит col + Abs(col)
эт просто настроение было игривое :) VBA любое числовое значение<>0 при преобразовании в логический тип данных интерпретирует как True, а 0 соответственно - False. Т.е. [vba]
Код
col + Abs(col) ≡ col>0
[/vba]
Что такое col в этой функции?
Номер столбца в диапазоне, содержащий возвращаемое значение
если номер столбца не отрицательный, можно пользовать встроенную функцию VLookup
[vba]
Код
Sub макрос()
    
    Dim sh_mat As Worksheet, sh_spravka As Worksheet
    Dim i As Long, lr As Long
    
    
    '1. Отключение монитора, чтобы ускорить макрос.
    Application.ScreenUpdating = False
    
    '2. Присваиваем имена листам, с которыми надо работать, чтобы по этим именам обращаться к листам.
    Set sh_mat = Worksheets("MaterialsNorms")
    Set sh_spravka = Worksheets("Справочник")
    '3. Удаление строк на листе "MaterialsNorms", у которых пусто в столбце 4.
    ' Поиск последней строки. Не должно быть скрытых строк, т.к. End не ищет в скрытых строках.
    lr = sh_mat.Cells(sh_mat.Rows.Count, 2).End(xlUp).row
    With sh_mat.Range("D10:D" & lr)
        ' Удаление.
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        '4. Заполнение столбца 6 на листе "MaterialsNorms".
        .Offset(0, 2).Value = Application.VLookup(.Value, sh_spravka.UsedRange.Columns("A:B"), 2, 0)
    End With
    
    '5. Вкл. монитора.
    Application.ScreenUpdating = True
    
    '6. Сообщение.
    MsgBox "Готово.", vbInformation

End Sub
[/vba]


(_)Õvõ(_)

Сообщение отредактировал krosav4ig - Пятница, 10.11.2017, 17:20
 
Ответить
Сообщение
зачем стоит знак "&"
это символ типа данных (Long)
что будет если его ("&") убрать
Тип данных станет variant
должно сначала идти условие но там стоит col + Abs(col)
эт просто настроение было игривое :) VBA любое числовое значение<>0 при преобразовании в логический тип данных интерпретирует как True, а 0 соответственно - False. Т.е. [vba]
Код
col + Abs(col) ≡ col>0
[/vba]
Что такое col в этой функции?
Номер столбца в диапазоне, содержащий возвращаемое значение
если номер столбца не отрицательный, можно пользовать встроенную функцию VLookup
[vba]
Код
Sub макрос()
    
    Dim sh_mat As Worksheet, sh_spravka As Worksheet
    Dim i As Long, lr As Long
    
    
    '1. Отключение монитора, чтобы ускорить макрос.
    Application.ScreenUpdating = False
    
    '2. Присваиваем имена листам, с которыми надо работать, чтобы по этим именам обращаться к листам.
    Set sh_mat = Worksheets("MaterialsNorms")
    Set sh_spravka = Worksheets("Справочник")
    '3. Удаление строк на листе "MaterialsNorms", у которых пусто в столбце 4.
    ' Поиск последней строки. Не должно быть скрытых строк, т.к. End не ищет в скрытых строках.
    lr = sh_mat.Cells(sh_mat.Rows.Count, 2).End(xlUp).row
    With sh_mat.Range("D10:D" & lr)
        ' Удаление.
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        '4. Заполнение столбца 6 на листе "MaterialsNorms".
        .Offset(0, 2).Value = Application.VLookup(.Value, sh_spravka.UsedRange.Columns("A:B"), 2, 0)
    End With
    
    '5. Вкл. монитора.
    Application.ScreenUpdating = True
    
    '6. Сообщение.
    MsgBox "Готово.", vbInformation

End Sub
[/vba]

Автор - krosav4ig
Дата добавления - 10.11.2017 в 17:13
Pelena Дата: Пятница, 10.11.2017, 17:20 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11755
Репутация: 2596 ±
Замечаний: 0% ±

Excel 2010, 2016 & Mac Excel
интеграция кода из эксель в аксесс

В Access аналогом ВПР() является DLookup()


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщение
интеграция кода из эксель в аксесс

В Access аналогом ВПР() является DLookup()

Автор - Pelena
Дата добавления - 10.11.2017 в 17:20
moland Дата: Суббота, 11.11.2017, 14:51 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
- а зачем? какова конечная цель? У СУБД есть встроенные средства, зачем там вообще ВПР()?


у меня просто познания малы.
 
Ответить
Сообщение
- а зачем? какова конечная цель? У СУБД есть встроенные средства, зачем там вообще ВПР()?


у меня просто познания малы.

Автор - moland
Дата добавления - 11.11.2017 в 14:51
buchlotnik Дата: Суббота, 11.11.2017, 15:31 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3327
Репутация: 886 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
moland,
Цитата
какова конечная цель?
Не путайте табличный процессор с СУБД, у них разная структура и логика, да и решаемые задачи разные. В чём суть вашего проекта?


каждому For - Next!
платная помощь:
ЯД: 410012595572239
buchlotnik@mail.ru
 
Ответить
Сообщениеmoland,
Цитата
какова конечная цель?
Не путайте табличный процессор с СУБД, у них разная структура и логика, да и решаемые задачи разные. В чём суть вашего проекта?

Автор - buchlotnik
Дата добавления - 11.11.2017 в 15:31
moland Дата: Воскресенье, 12.11.2017, 08:56 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Раньше были нормы и ценообразование в эксель рассчитаны в отдельных файлах. Сейчас хотят это все делать через аксесс.
 
Ответить
СообщениеРаньше были нормы и ценообразование в эксель рассчитаны в отдельных файлах. Сейчас хотят это все делать через аксесс.

Автор - moland
Дата добавления - 12.11.2017 в 08:56
buchlotnik Дата: Воскресенье, 12.11.2017, 10:16 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3327
Репутация: 886 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
Цитата
ценообразование
тогда это не ВПР-ом, а вычисляемыми полями в запросе (скорее всего) - но это уже отдельная тема для ветки про Access


каждому For - Next!
платная помощь:
ЯД: 410012595572239
buchlotnik@mail.ru
 
Ответить
Сообщение
Цитата
ценообразование
тогда это не ВПР-ом, а вычисляемыми полями в запросе (скорее всего) - но это уже отдельная тема для ветки про Access

Автор - buchlotnik
Дата добавления - 12.11.2017 в 10:16
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Как работает простая функция-аналог ВПР. (Макросы/Sub)
Страница 1 из 11
Поиск:

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