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

Вход

Регистрация

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

 

= Мир MS Excel/Улучшеный ВПР - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, китин  
Мир MS Excel » Вопросы и решения » Готовые решения » Улучшеный ВПР (ВПР±)
Улучшеный ВПР
DJ_Marker_MC Дата: Пятница, 07.12.2012, 14:40 | Сообщение № 1
Группа: Друзья
Ранг: Ветеран
Сообщений: 991
Репутация: 213 ±
Замечаний: 0% ±

Excel 2019
Перечитывал комментарии в приёмах Улушчаем функцию ВПР и наткнулся на одну интересную версию данной функции, автор СЕРГЕЙ.
Если кому будет интересно, а может даже и полезно заносим себе в копилку:

[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)

Думаю для многих данная функция будет полезной.


Сообщение отредактировал marker_mc - Пятница, 07.12.2012, 14:43
 
Ответить
СообщениеПеречитывал комментарии в приёмах Улушчаем функцию ВПР и наткнулся на одну интересную версию данной функции, автор СЕРГЕЙ.
Если кому будет интересно, а может даже и полезно заносим себе в копилку:

[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
Дата добавления - 07.12.2012 в 14:40
ikki Дата: Пятница, 07.12.2012, 23:51 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1906
Репутация: 504 ±
Замечаний: 0% ±

Excel 2003, 2010
Quote
[vba]
Code
Application.Volatile
[/vba]

это плохо.


помощь по Excel и VBA
ikki@fxmail.ru, icq 592842413, skype alex.ikki
 
Ответить
Сообщение
Quote
[vba]
Code
Application.Volatile
[/vba]

это плохо.

Автор - ikki
Дата добавления - 07.12.2012 в 23:51
DJ_Marker_MC Дата: Суббота, 08.12.2012, 00:35 | Сообщение № 3
Группа: Друзья
Ранг: Ветеран
Сообщений: 991
Репутация: 213 ±
Замечаний: 0% ±

Excel 2019
ikki, а без него станет лучше?
 
Ответить
Сообщениеikki, а без него станет лучше?

Автор - DJ_Marker_MC
Дата добавления - 08.12.2012 в 00:35
ikki Дата: Суббота, 08.12.2012, 22:24 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1906
Репутация: 504 ±
Замечаний: 0% ±

Excel 2003, 2010
без него станет хуже - функция, использованная на листе, не будет автоматически пересчитываться при изменении данных.
но и с ним плохо - функция пересчитывается КАЖДЫЙ раз при изменении ЛЮБЫХ данных (а не только тех, которые влияют на ее результат)
и даже при использовании автофильтра.


помощь по Excel и VBA
ikki@fxmail.ru, icq 592842413, skype alex.ikki
 
Ответить
Сообщениебез него станет хуже - функция, использованная на листе, не будет автоматически пересчитываться при изменении данных.
но и с ним плохо - функция пересчитывается КАЖДЫЙ раз при изменении ЛЮБЫХ данных (а не только тех, которые влияют на ее результат)
и даже при использовании автофильтра.

Автор - ikki
Дата добавления - 08.12.2012 в 22:24
DJ_Marker_MC Дата: Воскресенье, 09.12.2012, 23:42 | Сообщение № 5
Группа: Друзья
Ранг: Ветеран
Сообщений: 991
Репутация: 213 ±
Замечаний: 0% ±

Excel 2019
что можете посоветовать изменить в данной фунции чтоб сделать её лучше, ведь фунция и вправду очень интересна. У меня в копилке 3 разных ВПР, этот на мой взгляд самый крутой.
 
Ответить
Сообщениечто можете посоветовать изменить в данной фунции чтоб сделать её лучше, ведь фунция и вправду очень интересна. У меня в копилке 3 разных ВПР, этот на мой взгляд самый крутой.

Автор - DJ_Marker_MC
Дата добавления - 09.12.2012 в 23:42
старикашка Дата: Воскресенье, 23.12.2012, 22:29 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

прошу прощения, возможно вопрос не по теме, но какой формулой пользоваться если нужно найти значение столбца С для Иванова (а их 2 или 3 или...), но есть условия точного равенства по второму столбцу - например - 14, как в этом случае быть? впр не помогает - ивановых несколько, столбцы переставлять нельзя, по 2-му столбцу тоже искать не получится - много повторяющихся значений, как и в первом, но уникальное только одно!
 
Ответить
Сообщениепрошу прощения, возможно вопрос не по теме, но какой формулой пользоваться если нужно найти значение столбца С для Иванова (а их 2 или 3 или...), но есть условия точного равенства по второму столбцу - например - 14, как в этом случае быть? впр не помогает - ивановых несколько, столбцы переставлять нельзя, по 2-му столбцу тоже искать не получится - много повторяющихся значений, как и в первом, но уникальное только одно!

Автор - старикашка
Дата добавления - 23.12.2012 в 22:29
Pelena Дата: Воскресенье, 23.12.2012, 22:42 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 19162
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
старикашка, для вопросов предназначен раздел ВОПРОСЫ ПО EXCEL, прочитайте Правила форума, создайте тему, приложите файл с примером


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщениестарикашка, для вопросов предназначен раздел ВОПРОСЫ ПО EXCEL, прочитайте Правила форума, создайте тему, приложите файл с примером

Автор - Pelena
Дата добавления - 23.12.2012 в 22:42
ikki Дата: Понедельник, 24.12.2012, 02:15 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1906
Репутация: 504 ±
Замечаний: 0% ±

Excel 2003, 2010
Цитата (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)

по хорошему - ещё не мешало бы добавить "обрезку" указанного диапазона, ограничив его используемой областью.
а для ускорения работы - забирать в функции столбец поиска из диапазона в массив и перебирать значения в нём.


помощь по Excel и VBA
ikki@fxmail.ru, icq 592842413, skype alex.ikki


Сообщение отредактировал ikki - Понедельник, 24.12.2012, 02:16
 
Ответить
Сообщение
Цитата (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
Дата добавления - 24.12.2012 в 02:15
Archer Дата: Воскресенье, 19.01.2014, 10:31 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 47
Репутация: 0 ±
Замечаний: 40% ±

Excel 2003
Добрый день! Пробую запустить Вашу функцию, не получается. Вы пишите, что вводится только два значения, а в таблице для ввода 4 окна. Если не затруднит, покажите на примере (прилагаю). Нужно разнести платежи по № ИНН
К сообщению приложен файл: _function_.xls (33.0 Kb)
 
Ответить
СообщениеДобрый день! Пробую запустить Вашу функцию, не получается. Вы пишите, что вводится только два значения, а в таблице для ввода 4 окна. Если не затруднит, покажите на примере (прилагаю). Нужно разнести платежи по № ИНН

Автор - Archer
Дата добавления - 19.01.2014 в 10:31
nilem Дата: Воскресенье, 19.01.2014, 12:27 | Сообщение № 10
Группа: Авторы
Ранг: Старожил
Сообщений: 1613
Репутация: 563 ±
Замечаний: 0% ±

Excel 2013, 2016
Обычная ВПР не подойдет?:
Код
=ВПР(A2;$E$2:$F$8;2;0)


Яндекс.Деньги 4100159601573
 
Ответить
СообщениеОбычная ВПР не подойдет?:
Код
=ВПР(A2;$E$2:$F$8;2;0)

Автор - nilem
Дата добавления - 19.01.2014 в 12:27
romik85 Дата: Суббота, 22.03.2014, 17:06 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 16
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
можно ли доправить, вывод аргументов функции при нажатии шифт+ф3?
 
Ответить
Сообщениеможно ли доправить, вывод аргументов функции при нажатии шифт+ф3?

Автор - romik85
Дата добавления - 22.03.2014 в 17:06
Мир MS Excel » Вопросы и решения » Готовые решения » Улучшеный ВПР (ВПР±)
  • Страница 1 из 1
  • 1
Поиск:

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