Перечитывал комментарии в приёмах Улушчаем функцию ВПР и наткнулся на одну интересную версию данной функции, автор СЕРГЕЙ. Если кому будет интересно, а может даже и полезно заносим себе в копилку:
[vba]
Код
Function ВПР±(ИскомоеЗначение As Variant, ДиапазонЗначений As Range, Столбец As Long, НомерРезультата As Long) Application.ScreenUpdating = False Application.Volatile Dim i As Long Dim iCount As Long If НомерРезультата <= 0 Then Exit Function Do Until iCount = НомерРезультата i = i + 1 If i > ДиапазонЗначений.Rows.Count Then Exit Function If LCase(ДиапазонЗначений.Cells(i, 1)) = LCase(ИскомоеЗначение) Then iCount = iCount + 1 Loop ВПР± = ДиапазонЗначений.Cells(i, Столбец + 1) End Function
[/vba]
Цитата
Сергей 21.10.2010 Теперь нет необходимости задавать таблицу в которой будет поиск результата. Задаем теперь только диапазон значений по которому ищем результат и номер столбца в котором мы ищем сам результат. Причем если номер столбца отрицательный - поиск идет слева, если положительный , то справа. Если равен 0, то результатом будет само искомое значение. В дополнение можно задать номер результата.
аргументы: - Что ищем; - Выделяем только ТОТ столбец где ищем - указываем цифрой (+-) количество столбцов от того который мы указали с требуемым результатом - если повторяющихся значений несколько указываем то которое подставлять (по счету)
Например: А B C Иванов 10 10,01 Петров 15 14,02 Пупкин 20 17,03 Иванов 14 18,03 Васильев 21 17,03 Иванов 13 01,04
нам нужно найти кто сделал второй заказ 17,03, для этого пишем =ВПР±("17,03"[или указываем ячейку с искомым значением];C:C;-2;2)
Думаю для многих данная функция будет полезной.
Перечитывал комментарии в приёмах Улушчаем функцию ВПР и наткнулся на одну интересную версию данной функции, автор СЕРГЕЙ. Если кому будет интересно, а может даже и полезно заносим себе в копилку:
[vba]
Код
Function ВПР±(ИскомоеЗначение As Variant, ДиапазонЗначений As Range, Столбец As Long, НомерРезультата As Long) Application.ScreenUpdating = False Application.Volatile Dim i As Long Dim iCount As Long If НомерРезультата <= 0 Then Exit Function Do Until iCount = НомерРезультата i = i + 1 If i > ДиапазонЗначений.Rows.Count Then Exit Function If LCase(ДиапазонЗначений.Cells(i, 1)) = LCase(ИскомоеЗначение) Then iCount = iCount + 1 Loop ВПР± = ДиапазонЗначений.Cells(i, Столбец + 1) End Function
[/vba]
Цитата
Сергей 21.10.2010 Теперь нет необходимости задавать таблицу в которой будет поиск результата. Задаем теперь только диапазон значений по которому ищем результат и номер столбца в котором мы ищем сам результат. Причем если номер столбца отрицательный - поиск идет слева, если положительный , то справа. Если равен 0, то результатом будет само искомое значение. В дополнение можно задать номер результата.
аргументы: - Что ищем; - Выделяем только ТОТ столбец где ищем - указываем цифрой (+-) количество столбцов от того который мы указали с требуемым результатом - если повторяющихся значений несколько указываем то которое подставлять (по счету)
Например: А B C Иванов 10 10,01 Петров 15 14,02 Пупкин 20 17,03 Иванов 14 18,03 Васильев 21 17,03 Иванов 13 01,04
нам нужно найти кто сделал второй заказ 17,03, для этого пишем =ВПР±("17,03"[или указываем ячейку с искомым значением];C:C;-2;2)
Думаю для многих данная функция будет полезной.DJ_Marker_MC
Сообщение отредактировал marker_mc - Пятница, 07.12.2012, 14:43
без него станет хуже - функция, использованная на листе, не будет автоматически пересчитываться при изменении данных. но и с ним плохо - функция пересчитывается КАЖДЫЙ раз при изменении ЛЮБЫХ данных (а не только тех, которые влияют на ее результат) и даже при использовании автофильтра.
без него станет хуже - функция, использованная на листе, не будет автоматически пересчитываться при изменении данных. но и с ним плохо - функция пересчитывается КАЖДЫЙ раз при изменении ЛЮБЫХ данных (а не только тех, которые влияют на ее результат) и даже при использовании автофильтра.ikki
помощь по Excel и VBA ikki@fxmail.ru, icq 592842413, skype alex.ikki
что можете посоветовать изменить в данной фунции чтоб сделать её лучше, ведь фунция и вправду очень интересна. У меня в копилке 3 разных ВПР, этот на мой взгляд самый крутой.
что можете посоветовать изменить в данной фунции чтоб сделать её лучше, ведь фунция и вправду очень интересна. У меня в копилке 3 разных ВПР, этот на мой взгляд самый крутой.DJ_Marker_MC
прошу прощения, возможно вопрос не по теме, но какой формулой пользоваться если нужно найти значение столбца С для Иванова (а их 2 или 3 или...), но есть условия точного равенства по второму столбцу - например - 14, как в этом случае быть? впр не помогает - ивановых несколько, столбцы переставлять нельзя, по 2-му столбцу тоже искать не получится - много повторяющихся значений, как и в первом, но уникальное только одно!
прошу прощения, возможно вопрос не по теме, но какой формулой пользоваться если нужно найти значение столбца С для Иванова (а их 2 или 3 или...), но есть условия точного равенства по второму столбцу - например - 14, как в этом случае быть? впр не помогает - ивановых несколько, столбцы переставлять нельзя, по 2-му столбцу тоже искать не получится - много повторяющихся значений, как и в первом, но уникальное только одно!старикашка
Function ВПР±(ИскомоеЗначение As Variant, Таблица As Range, СтолбецПоиска As Long, СтолбецРезультата As Long, НомерРезультата As Long) Application.ScreenUpdating = False Dim i As Long, iCount As Long If НомерРезультата <= 0 Then Exit Function Do Until iCount = НомерРезультата i = i + 1 If i > Таблица.Rows.Count Then Exit Function If LCase(Таблица.Cells(i, СтолбецПоиска)) = LCase(ИскомоеЗначение) Then iCount = iCount + 1 Loop ВПР± = Таблица.Cells(i, СтолбецРезультата) End Function
[/vba]
использование (для вашего примера из поста №1) =ВПР±("17,03";A:C;3;1;2)
по хорошему - ещё не мешало бы добавить "обрезку" указанного диапазона, ограничив его используемой областью. а для ускорения работы - забирать в функции столбец поиска из диапазона в массив и перебирать значения в нём.
Цитата (marker_mc)
что можете посоветовать изменить в данной фунции
я бы сделал примерно так (не проверял): [vba]
Код
Function ВПР±(ИскомоеЗначение As Variant, Таблица As Range, СтолбецПоиска As Long, СтолбецРезультата As Long, НомерРезультата As Long) Application.ScreenUpdating = False Dim i As Long, iCount As Long If НомерРезультата <= 0 Then Exit Function Do Until iCount = НомерРезультата i = i + 1 If i > Таблица.Rows.Count Then Exit Function If LCase(Таблица.Cells(i, СтолбецПоиска)) = LCase(ИскомоеЗначение) Then iCount = iCount + 1 Loop ВПР± = Таблица.Cells(i, СтолбецРезультата) End Function
[/vba]
использование (для вашего примера из поста №1) =ВПР±("17,03";A:C;3;1;2)
по хорошему - ещё не мешало бы добавить "обрезку" указанного диапазона, ограничив его используемой областью. а для ускорения работы - забирать в функции столбец поиска из диапазона в массив и перебирать значения в нём.ikki
помощь по Excel и VBA ikki@fxmail.ru, icq 592842413, skype alex.ikki
Сообщение отредактировал ikki - Понедельник, 24.12.2012, 02:16
Добрый день! Пробую запустить Вашу функцию, не получается. Вы пишите, что вводится только два значения, а в таблице для ввода 4 окна. Если не затруднит, покажите на примере (прилагаю). Нужно разнести платежи по № ИНН
Добрый день! Пробую запустить Вашу функцию, не получается. Вы пишите, что вводится только два значения, а в таблице для ввода 4 окна. Если не затруднит, покажите на примере (прилагаю). Нужно разнести платежи по № ИННArcher
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
