Vba как найти номер элементов

Dim pos, arr, val

arr=Array(1,2,4,5)
val = 4

pos=Application.Match(val, arr, False)

if not iserror(pos) then
   Msgbox val & " is at position " & pos
else
   Msgbox val & " not found!"
end if

Updated to show using Match (with .Index) to find a value in a dimension of a two-dimensional array:

Dim arr(1 To 10, 1 To 2)
Dim x

For x = 1 To 10
    arr(x, 1) = x
    arr(x, 2) = 11 - x
Next x

Debug.Print Application.Match(3, Application.Index(arr, 0, 1), 0)
Debug.Print Application.Match(3, Application.Index(arr, 0, 2), 0)

EDIT: it’s worth illustrating here what @ARich pointed out in the comments – that using Index() to slice an array has horrible performance if you’re doing it in a loop.

In testing (code below) the Index() approach is almost 2000-fold slower than using a nested loop.

Sub PerfTest()

    Const VAL_TO_FIND As String = "R1800:C8"
    Dim a(1 To 2000, 1 To 10)
    Dim r As Long, c As Long, t

    For r = 1 To 2000
        For c = 1 To 10
            a(r, c) = "R" & r & ":C" & c
        Next c
    Next r

    t = Timer
    Debug.Print FindLoop(a, VAL_TO_FIND), Timer - t
    ' >> 0.00781 sec

     t = Timer
    Debug.Print FindIndex(a, VAL_TO_FIND), Timer - t
    ' >> 14.18 sec

End Sub

Function FindLoop(arr, val) As Boolean
    Dim r As Long, c As Long
    For r = 1 To UBound(arr, 1)
    For c = 1 To UBound(arr, 2)
        If arr(r, c) = val Then
            FindLoop = True
            Exit Function
        End If
    Next c
    Next r
End Function

Function FindIndex(arr, val)
    Dim r As Long
    For r = 1 To UBound(arr, 1)
        If Not IsError(Application.Match(val, Application.Index(arr, r, 0), 0)) Then
            FindIndex = True
            Exit Function
        End If
    Next r
End Function

 

Здравствуйте.  
Необходима помощь в создании функции, которая должна определять порядковый номер элемента в диапазоне, вместе с которым накопительная сумма всех предыдущих элементов становится равной либо превышает заданный критерий.

Во вложении пример массива (D2:H2)
Задан критерий (целевое значение суммы элементов): E4= 370
Функция начинает последовательно суммировать элементы массива, начиная с первого до тех пор, пока сумма элементов не станет равной или превысит 370. Выводит номер порядковый номер элемента, при котором выполнилось данное условие. В данном случае условие выполнилось на элементе №3.
Спасибо.

 

Пытливый

Пользователь

Сообщений: 4652
Регистрация: 22.12.2012

#2

25.04.2016 11:23:50

Добрый день.
Как-то так, наверное:

Код
Function ElementsCount(Диапазон As Range, Критерий As Double)
    Dim objC As Range
    Dim intI As Integer
    Dim dblT As Double
        For Each objC In Диапазон
            intI = intI + 1
            dblT = dblT + objC
                If dblT >= Критерий Then
                    ElementsCount = intI
                    Exit Function
                End If
        Next objC
End Function

Кому решение нужно – тот пример и рисует.

 

Sanja

Пользователь

Сообщений: 14849
Регистрация: 10.01.2013

#3

25.04.2016 11:26:47

Без массивов и прочих словарей

Код
Function НОМЭЛЕМЕНТА(rng As Range, kr As Long) As Long
    For I = 1 To rng.Count
        S = S + rng(I).Value
        If S >= kr Then
            НОМЭЛЕМЕНТА = I
            Exit Function
        End If
    Next
End Function

Прикрепленные файлы

  • Номер элемента.xlsm (13.91 КБ)

Согласие есть продукт при полном непротивлении сторон.

 

Пытливый, спасибо. На дальнейших примерах все корректно сработало.
Sanja, почему то некорректно работает на следующем примере. Прикладываю.

 

Sanja

Пользователь

Сообщений: 14849
Регистрация: 10.01.2013

#5

25.04.2016 12:47:28

Код
Function НОМЭЛЕМЕНТА(rng As Range, kr As Double) As Double
    For I = 1 To rng.Count
        S = S + rng(I).Value
        If S >= kr Then
            НОМЭЛЕМЕНТА = I
            Exit Function
        End If
    Next
End Function

Согласие есть продукт при полном непротивлении сторон.

 

Михаил С.

Пользователь

Сообщений: 10514
Регистрация: 21.12.2012

#6

25.04.2016 13:14:15

VBA обязательно?
=ПОИСКПОЗ(E4-1;СУММЕСЛИ(СМЕЩ(D2;;;;СТОЛБЕЦ(D2:H2)-СТОЛБЕЦ(D2)+1);”<>”))+1
формула массива (Ctrl+Shift+Enter)

Определение порядкового номера элемента в колекции

Poltava

Дата: Суббота, 02.11.2013, 01:05 |
Сообщение № 1

Группа: Друзья

Ранг: Форумчанин

Сообщений: 232


Репутация:

50

±

Замечаний:
0% ±


Пока единственным способом который я смог придумать для определения номера элемента в колекции являеться перебор в цыкле всех элементов. Возможно кто то знает более эфективные способы?

Сообщение отредактировал PoltavaСуббота, 02.11.2013, 01:07

 

Ответить

AndreTM

Дата: Суббота, 02.11.2013, 01:42 |
Сообщение № 2

Группа: Друзья

Ранг: Старожил

Сообщений: 1762


Репутация:

498

±

Замечаний:
0% ±


2003 & 2010

Насколько я понимаю, суть использования коллекции (в VBA, и не только) в том и состоит, что “индекс” (порядковый номер элемента) не нужен. Это же динамический список, и поэтому сегодня “порядковый номер” может быть одним, а завтра – другим… Для этого и существует .Add Item, ItemKey, [Before|After]. Ведь Item – это не просто переменная, это объект, а ItemKey – как раз и определяет ключ для быстрого доступа к объекту. А ваш “Key” может быть просто свойством этого объекта.
Вы же пытаетесь решать задачу типа “поиск порядкового номера по ключу”. Это из серии того, что вы в книге будете определять,что лист с именем “Отчёт 666” – это третья закладка. Что даст это знание? Скорее всего, вы пытаетесь использовать коллекцию в задаче, алгоритм которой предполагает использование других средств…


Skype: andre.tm.007
Donate: Qiwi: 9517375010

 

Ответить

SkyPro

Дата: Суббота, 02.11.2013, 04:25 |
Сообщение № 3

Группа: Друзья

Ранг: Старожил

Сообщений: 1206


Репутация:

255

±

Замечаний:
0% ±


2010

Poltava, решил-таки дописать класс Нерва?


skypro1111@gmail.com

 

Ответить

Poltava

Дата: Суббота, 02.11.2013, 08:33 |
Сообщение № 4

Группа: Друзья

Ранг: Форумчанин

Сообщений: 232


Репутация:

50

±

Замечаний:
0% ±


Цитата

Poltava, решил-таки дописать класс Нерва?

Ну по сути не решил а приходиться :)

Цитата

лист с именем “Отчёт 666” – это третья закладка

По сути конечно да но ведь задача может стоять и таким образом.

 

Ответить

AndreTM

Дата: Суббота, 02.11.2013, 08:43 |
Сообщение № 5

Группа: Друзья

Ранг: Старожил

Сообщений: 1762


Репутация:

498

±

Замечаний:
0% ±


2003 & 2010

По сути конечно да но ведь задача может стоять и таким образом

Пример? И попробуем решить и коллекцией (с правильной реализацией), и, например, массивом…


Skype: andre.tm.007
Donate: Qiwi: 9517375010

 

Ответить

Poltava

Дата: Понедельник, 11.11.2013, 14:27 |
Сообщение № 6

Группа: Друзья

Ранг: Форумчанин

Сообщений: 232


Репутация:

50

±

Замечаний:
0% ±


Извините за долгое отсутствие. Пришлось немного отвлечься от темы. Теперь подробнее.
Попался в руки код в котором параллельно заполняется массив и коллекция и потом в коллекции проверяют наличие элемента после чего перебирается массив в поисках этого элемента. и проводятся определенные манипуляции с найденной строкой массива. Код подразумевает что номер элемента коллекции соответствует номеру строки массива. Отсюдова собственно и растут ноги моего вопроса, так как не хочется каждый раз перебирать массив в поисках номера строки содержащей элемент заранее зная что он совпадает с номером элемента в коллекции.

 

Ответить

Hugo

Дата: Понедельник, 11.11.2013, 14:52 |
Сообщение № 7

Группа: Друзья

Ранг: Участник клуба

Сообщений: 3140


Репутация:

670

±

Замечаний:
0% ±


2010, теперь уже с PQ

Может там не коллекция, а словарь?
Вот в словаре как раз удобно держать ключ с номером строки массива. И сразу по ключу имеем доступ к данным в массиве.


excel@nxt.ru
webmoney: R418926282008 Z422237915069

 

Ответить

Poltava

Дата: Понедельник, 11.11.2013, 16:22 |
Сообщение № 8

Группа: Друзья

Ранг: Форумчанин

Сообщений: 232


Репутация:

50

±

Замечаний:
0% ±


Hugo, К сожалению именно коллекции. Я понимаю что использовать словарь было бы правильнее и эффективнее. Но код не мой, а использованы были именно коллекции. Наверно все же придется переписать на словари потому как кроме перебора ничего в голову так и не пришло.

Сообщение отредактировал PoltavaПонедельник, 11.11.2013, 16:32

 

Ответить

nerv

Дата: Понедельник, 11.11.2013, 17:52 |
Сообщение № 9

Группа: Редакторы

Ранг: Обитатель

Сообщений: 431

Попался в руки код в котором параллельно заполняется массив и коллекция и потом в коллекции проверяют наличие элемента после чего перебирается массив в поисках этого элемента. и проводятся определенные манипуляции с найденной строкой массива. Код подразумевает что номер элемента коллекции соответствует номеру строки массива. Отсюдова собственно и растут ноги моего вопроса, так как не хочется каждый раз перебирать массив в поисках номера строки содержащей элемент заранее зная что он совпадает с номером элемента в коллекции.

абстрактный конь в вакууме. Где пример, код?

Вот в словаре как раз удобно держать ключ с номером строки массива.

в большинстве случаев без разницы


Чебурашка стал символом олимпийских игр. А чего достиг ты?
Тишина – самый громкий звук

YM 41001156540584 / WM WMR R21924176233

https://github.com/nervgh/vba

 

Ответить

Hugo

Дата: Понедельник, 11.11.2013, 18:01 |
Сообщение № 10

Группа: Друзья

Ранг: Участник клуба

Сообщений: 3140


Репутация:

670

±

Замечаний:
0% ±


2010, теперь уже с PQ

Ну в общем то можно и без перебора:
[vba]

Код

Sub tt()
     Dim col As New Collection

     col.Add 1, “раз”
     col.Add 2, “два”
     col.Add 3, “три”

     MsgBox col(“два”)
End Sub

[/vba]


excel@nxt.ru
webmoney: R418926282008 Z422237915069

 

Ответить

Poltava

Дата: Понедельник, 11.11.2013, 18:46 |
Сообщение № 11

Группа: Друзья

Ранг: Форумчанин

Сообщений: 232


Репутация:

50

±

Замечаний:
0% ±


Hugo, hands данный способ меня устраивает и в коде фактически ничего менять не нужно спасибо за подсказку, а то сам не дошел. я уже к Collection_ от nerv, прикрутил свойство index вычисляемое через перебор.

 

Ответить

Ксения



Знаток

(259),
закрыт



12 лет назад

Alexander Alenitsyn

Высший разум

(754460)


12 лет назад

В любом языке программирования записывается цикл по индексу
элемента, на каждом шаге цикла проверяется равенство элемента
числу 4, и если получается совпадение (т. е. равенство А (к) =4 имеет
значение “истина”), то запоминается этот номер (т. е. р=к) и сразу делается
выход из цикла.

Dim pos, arr, val

arr=Array(1,2,4,5)
val = 4

pos=Application.Match(val, arr, False)

if not iserror(pos) then
   Msgbox val & " is at position " & pos
else
   Msgbox val & " not found!"
end if

Обновлено, чтобы показать, используя Match (с .Index), чтобы найти значение в измерении двумерного массива:

Dim arr(1 To 10, 1 To 2)
Dim x

For x = 1 To 10
    arr(x, 1) = x
    arr(x, 2) = 11 - x
Next x

Debug.Print Application.Match(3, Application.Index(arr, 0, 1), 0)
Debug.Print Application.Match(3, Application.Index(arr, 0, 2), 0)

EDIT: стоит ли здесь упомянуть то, что @ARich указал в комментариях – использование Index() для среза массива имеет ужасную производительность, если вы делаете это в цикле.

При тестировании (код ниже) подход Index() почти в 2000 раз медленнее, чем использование вложенного цикла.

Sub PerfTest()

    Const VAL_TO_FIND As String = "R1800:C8"
    Dim a(1 To 2000, 1 To 10)
    Dim r As Long, c As Long, t

    For r = 1 To 2000
        For c = 1 To 10
            a(r, c) = "R" & r & ":C" & c
        Next c
    Next r

    t = Timer
    Debug.Print FindLoop(a, VAL_TO_FIND), Timer - t
    ' >> 0.00781 sec

     t = Timer
    Debug.Print FindIndex(a, VAL_TO_FIND), Timer - t
    ' >> 14.18 sec

End Sub

Function FindLoop(arr, val) As Boolean
    Dim r As Long, c As Long
    For r = 1 To UBound(arr, 1)
    For c = 1 To UBound(arr, 2)
        If arr(r, c) = val Then
            FindLoop = True
            Exit Function
        End If
    Next c
    Next r
End Function

Function FindIndex(arr, val)
    Dim r As Long
    For r = 1 To UBound(arr, 1)
        If Not IsError(Application.Match(val, Application.Index(arr, r, 0), 0)) Then
            FindIndex = True
            Exit Function
        End If
    Next r
End Function

Добавить комментарий