Vba excel поиск значения в двумерном массиве

Обновлено: 06.07.2024

У меня отличная функция, которую я использую все время для одномерного массива Excel VBA, который проверяет, находится ли строка в массиве:

К сожалению, он не работает при использовании его для проверки 2-мерного массива, как у меня здесь:

Он продолжает выходить из строя в функции, говорящей, что индекс не соответствует диапазону, у кого есть мысль, как я могу проверить, находится ли строка в 2-мерном массиве?

спросил(а) 2015-06-04T23:05:00+03:00 6 лет, 5 месяцев назад

Что-то из моей коллекции кода

Вы можете использовать Application.Match . Это будет работать как для 1D и для 2D массива :)

ответил(а) 2015-06-04T23:18:00+03:00 6 лет, 5 месяцев назад

Пока вы находитесь в Excel (или имеете ссылку на него), вы можете использовать функцию Index, чтобы нарезать ваш массив на строки или столбцы.

ответил(а) 2015-06-04T23:58:00+03:00 6 лет, 5 месяцев назад

Если вы получите данные из набора записей, я использую этот метод; сначала я использую GetString для набора записей, во-вторых, используйте Split для преобразования строки в массив unimimensional, где каждый элемент является строкой со всей информацией. После этого вы используете функцию IsInArray.

вы можете протестировать код, но помните, что он работает только в том случае, если вы получаете данные из набора записей

ответил(а) 2016-06-26T14:09:00+03:00 5 лет, 5 месяцев назад

У меня также есть решение для пользователей Excel.

Не можете ли вы просто разбить конкатенацию массива на один столбец (1-й массив)? у вас есть х столбцов. кто заботится о количестве строк на данный момент.

превращение 2-го массива в 1-й массив.

и индекс соответствует этому объединенному массиву/столбцу, чтобы найти множественные вхождения строки, расположенной в исходном массиве.

Разве это не делает то же самое, что и макросы выше или вопрос задается? и (почти) не макро */не VBA способом?

* см. ниже, почему это можно сделать без макросов.

Таким образом, в конце концов, вы можете просто превратить любой двумерный массив NM в массив 1-d X, сохранив при этом всю информацию (столбцу которой изначально принадлежал текст), и при этом выполнить простой поиск, сопоставление индекса или функция LoopALL (что здорово):

Макрос Lookupall, используемый для поиска и возврата нескольких найденных вхождений строки:

Вы сами решаете, используете ли вы VBA lookup all function выше или формулу соответствия индекса в excel, которая может находить и возвращать множественные вхождения поискового поиска.

Делимация и объединение отдельных столбцов массива избавляет от необходимости поиска в массиве (что я никогда не мог сделать так, как я хотел - т.е. получить результаты всего в 1 ячейку), и превращает его в одну и более простую 1 -d массив без потери информации.

Я считаю, что скорость будет такой же быстрой (и точной), как и все остальное. Особенно, если вы уменьшили/сузили массив в один массив - 1 столбец.

Данная функция ищет в массиве все строки, подходящие под заданные критерии, и возвращает результат в виде отфильтрованного массива:

PS: Код обновлён 4 января 2021 года, — теперь он работает в 40 раз быстрее, нежели прежняя версия функции

Комментарии

UPD. Разогнал поиск и сопоставление с нескольких часов до нескольких минут для 800к+ записей в одном массиве и 365к+ в другом. если кому интересно - могу поделиться наработками.

Игорь, добрый день.
Доработал Ваш код под свои потребности и дополнительно сделал следующее (как говорил ранее, требуется сопоставление довольно больших массивов):
1. перед вызовом функции формирую временный массив, в котором содержится только 2 колонки: колонка со значениями и колонка с номером строки в исходном массиве.
2. при поиске в функции добавляю в отдельную коллекцию найденные значения (номера строк).
3. если количество найденных элементов более 250 - применяю Ваш способ формирования итогового массива на выходе из функции (т.е. перебором по временному массиву), иначе перебор идет по коллекции. При сотнях тысяц записей прирост по скорости ощутимый. В исходном (из п.1) массиве найденные строки очищаю от значений.
4. после обработки заданного количества строк (например, 5000-10000) удаляю из массива (п.1) пустые строки. Удаление производится перебором (через промежуточный массив), но это позволяет сократить время обработки. Если при первом запуске на 50000 сопоставленных строк тратится 4584 секунды, то после 250 000 - 365 секунд (365 000 записей сопоставлял с 596 000, на все про все ушло порядка 3 часов, при том что если делать сопоставление "в лоб" - потребуется более 7 часов).
Код обработки получился объемный, но попробую как пример сделать лайтовую версию (безусловно, если интересует).
Но без Вас вряд ли смог сделать нормальное решение. Спасибо.

Игорь, доброго времени суток.
Вариант фильтрации массива реализован достойно, но не проще ли сразу номера нужных строк загонять, например, в коллекцию либо словарь и потом на основании сформированного перечня выдавать результирующий массив с нужными строками (чтобы не бегать по исходному массиву несколько раз)? единственное НО - в случае с коллекцией есть ограничение порядка 250 записей (получено опытным путем, в документации не нашел такого).
Так же есть проблема с выводом значений, если найдена всего одна строка - итоговый массив не совсем некорректно формируется (не исключаю, что мои кривые ручки/параметры Excel виноваты, но факт остается фактом и для обхода данного "ограничения") немного поправил строчку: ReDim newarr(RowsCount&, UBound(arr, 2)) (безусловно, в таком случае образуется пустая нулевая строчка, но её можно легко пропустить при дальнейшей обработке результатов (для перфекционистов Redim Preserve в помощь, но оно того не стоит)).

Функция UniqueValuesFromArray позволяет найти в указанном столбце двумерного массива все уникальные значения, и получить новый массив, содержащий все найденные уникальные значения.
Это может пригодиться, если надо, к примеру, заполнить ComboBox на форме возможными вариантами значений из базы данных:

Код самой функции:

Во вложении - пример использования этой функции в макросе (вывод уникальных записей в другой столбец листа), и пользовательская функция Уникальные - для использования в формулах листа Excel.

Макрос и дополнительная функция из файла во вложении:

Комментарии

Благодарю за Вашу работу. Столкнулся с проблемой выборки дубликатов из массива около 80000 строк и записью строки из 6 значений другого массива на лист ошибок. Решил вопрос доработкой функции UniqueValuesFromArray плюс быстрый поиск ArraySearchResults. Временные показатели очень порадовали 40 секунд вместо 10 минут перебора и выборки из другого массива. Прошу дополнить страницу или создать новую с функцией DoubleValuesFromArray (Поиск уникальных ПОВТОРЯЮЩИХСЯ значений в массиве)

Еще раз спасибо за Вашу работу. Выручали не раз.

Игорь, спасибо!
Подскажите, как доработать функцию так, чтобы уникальные значения переносились в том формате, в котором они находились в начальном массиве? В моем случае начальный массив - это числа, которые выгружаются из другой программы и не отформатированы, как числа, в то время, как переносятся они в виде чисел. Из-за этого в последствии не работает функция ВПР.
Или может быть лучше сделать так, чтобы функция сначала преобразовывала начальный массив в числа и только потом переносила уникальные?

Это потому что вы в другой файл перенесли только часть кода (а код функции UniqueValuesFromArray забыли скопировать)

Добрый день!
На родном файле макрос работает отлично, на другом, с тем же расширением не работает, выдает ошибку Sub or Function not defined на UniqueValuesFromArray. В чем может быть причина?
Расширения обоих файлов поменял на .xlsm
Спасибо!

РАЗОБРАЛСЯ.
СПАСИБО БОЛЬШОЕ ЗА ФУНКЦИЮ1111111

Нет.
выглядит так:
А B
1 текст 1 текст 1
2 текст 2 текст 2
3 текст 3 текст 3
4 текст 4 текст 4
5 текст 5 текст 5
6 текст 6 текст 6
7 текст 7 текст 7
.
33 текст 33 текст 33
34 текст 34 текст 34
35 текст 35 текст 1
36 текст 36 текст 1
37 текст 37 текст 1
и так делее
Т.е. в столбце А я вставил только уникальные значения до строки 80 (текст 80), но на 35 строке функция перестает работать как-будто, может я что не так сделал?
Помогите пожалуйста, я могу это сделать формулами, ног проблема в нагрузке, в моем файле просматриваемый массив имеет длину 2000 строк, excel умирает на расчетах.(((((

Видимо, 36-е значение (которое «первое уникальное») - только с виду похоже на первое
(например, есть лишний пробел, или одна русская буква заменена похожей английской)

Добрый день.
Сразу скажу, что в макросах не че не понимаю.
Мне очень понравилась ваша функция, но появилась проблема.
Функция выбирает 35 уникальных значений, далее возвращает первое уникальное.
В чем проблема?

В статье написано:

пользовательская функция - для использования в качестве формулы массива

выделяете НЕСКОЛЬКО ячеек, вводите эту формулу, и завершаете ввод нажатием Ctrl + Shift + Enter (вместо обычного Enter)
Тогда будет выводиться несколько уникальных значений

А вообще, уникальные можно и формулами вывести, без макросов.

Доброго времени суток. Макрос у меня заработал отлично а вот функция "Уникальные" почему то работает только в файле примере Unique.xls. Ситуация такая: все как положено добавил функцию в модуль, но она на отрез отказывается корректно работать, вместо уникальных значений выдает первое попавшееся значение причем оно возвращается во всех ячейках где прописана функция. Помогите пожалуйста разобраться.

Не знаю, увидите ли вы, но спасибо!
И автору тоже!

Добрый вечер. Разбираю макрос, который здесь представлен. С VBA не работала. Можно ли усложнить функцию вывода уникальных: вывод за период временной с исключением каких-то уникальных значений, т.е. возможно это значение встречается в этот период несколько раз, но выводить его не надо, так как не интересно. Буду очень благодарна за помощь

Добрый день. Разбираю сейчас Ваш макрос. Не сильна в них. Прошу помочь. В целом все работает отлично, но не могу понять как поменять его, чтобы новый массив уникальных вставал на другой (заданный) лист и не столбцом, а строкой. Буду очень признательна за помощь.

Тут самое главное, вот это: On Error Resume Next: Dim coll As New Collection
то есть при ошибке продолжить заполнение коллекции, а так как в коллекцию добавляется ключ такой же как и само значение
txt$ = Trim(cell): If Len(txt$) Then coll.Add txt$, txt$ 'коллекция.добавить значение, ключ
то в коллекции автоматически остается только один уникальный ключ, по определению, и вместе с ним одно уникальное значение

Владимир, ваш вопрос не имеет никакого отношения к теме статьи
В вашем случае, макросы не нужны, - достаточно одной формулы
Обратитесь на любой форум по Excel, прикрепив пример файла, - вам помогут с формулой.

Здравствуйте!
Давно пытаюсь найти решение подобной задачи. Знаний к сожалению не хватает. И вот нашёл Ваш пример. У меня задача хоть и подобная но масштабы больше. В одной книге есть 2 листа:
1 лист (таблица заказов) имеет 159 столбцов с данными, 165 столбец с датой. Каждый столбец с данными пронумерован № продукции 0001,0002,0003, и т.д.0159.
2 лист (отчёт) В отчёте 2 столбец №-ра продукции построчно и 4 столбец для копирования данных продукции с 1 листа.
И вот надо решить задачу: как выбрать продукцию с 1 листа и скопировать во 2 лист по определённой дате заданной во 2 листе.
Вроде бы кажется, что всё просто, но ничего не получается. Прошу Вас подскажите как всё это решить.

Спасибо большое! Действительно быстро работает.
Единственное, что добавил - пропуск пустых ячеек:
For i = LBound(arr) To UBound(arr)
If Trim(arr(i, col)) = "" Then GoTo 1
txt$ = Trim(arr(i, col)): coll.Add txt$, txt$
1: Next i

Function UniqueValuesFromArray может возвращать массив размерностью исходного массива, а не размерностью N * 1?

В дополнительном столбце, при помощи формулы типа =СЦЕПИТЬ(A1;"//";B1), объедините значения этих 2 столбцов,
и потом по этому доп.столбцу отбирайте уникальные

Подскажите пожалуйста, как изменить ваши процедуры, чтобы можно было выбрать уникальные значения не одного столбца, а нескольких. Например в Стобце A значения а,в,а,в. В столбце B в соседних ячейках значения с,д,с,е. Нужно чтобы макрос вдавал массив: В первом столбце: а,в,в. В соседнем с,д,е.

Спасибо! Очень помогло.

Спасибо за функцию.
Работает отлично.
Это уже вторая Ваша функция в течение двух дней, которую я применил в своей работе.
Завтра еще третью буду пробовать - поиск в массиве :)

Еще раз большое спасибо!

Спасибо. Буду изучать.

Так сделать можно, но.
одно дело - выбрать уникальные значения (тут всё ясно, вариантов особо нет),
и совсем другое - выбрать строки с уникальными значениями в каком-то столбце.

К примеру, есть у нас 3 строки с одинаковыми значениями в 1-м столбце.
Какую из этих трёх строк выводить в результат? Первую, третью, вторую?

Пример её использования для вашего случая:

Извините за надоедливость ))
Возни еще один вопрос.

Сейчас выборка уникальный происходит только по столбцу "A".
Возможно ли сделать выборку уникальных по столбцу "A", но с условием, чтобы в диапазон фильтрования попадали также столбцы B и C ?

Т.е. чтобы был аналог функционалу Excel 2007: Выделяю столбцы A,B,C => Данные => Удалить дубликаты => В качестве столбца, по которому будет происходить удаление дубликатов выбираю только столбец A.

Как результат: уникальные значения будут отобраны по столбцу "А", но соответствующие записи из столбцов B и C будут также сохранены.

Вроде разобрался:
Dim ПервыйСтолбец As Range: Set ПервыйСтолбец = Range(Sheets("данные").Range("A1"), Sheets("данные").Range("A" & Rows.Count).End(xlUp))

Подскажите, пожалуйста. Сейчас макрос срабатывает в случае если активен лист с массивом.
Как правильно добавить название листа в запись, чтобы активация листа с массивом не была обязательной?

Я пробовал вот так, но макрос выдает ошибку:

Dim ПервыйСтолбец As Range: Set ПервыйСтолбец = Sheets("данные").Range([A1], Sheets("данные").Range("A" & Rows.Count).End(xlUp))

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

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

Добрый день! Помогите решить. Ничего не нашел по этой теме.

Необходимо разработать подпрограмму поиска максимального элемента в двухмерном массиве:

__________________
Помощь в написании контрольных, курсовых и дипломных работ здесь

Поиск максимального элемента в двумерном массиве
Public Function Max(ByVal ArrM(,) As Integer) As Integer Dim C1, C2 As Integer .


Поиск максимального/минимального элемента в массиве
Данная матрица размера m * n. Найти 1. минимальный 2. максимальный среди 1).


Поиск максимального элемента в двухмерном массиве и нахождение его индекса
Помогите, пожалуйста, найти индекс максимального элемента в двумерном массиве. Массив - результат.

Решение

Здравствуйте, также решаю эту задачу. Смог только сделать функцию, которая возвращает максимальное значение самой ячейки, а как вернуть еще и индекс этой ячейки двухмерного массива?
Немного переделал твою функцию.А получить значения ячеек вне функции:

В двухмерном массиве вычислить произведение минимального и максимального элемента массива
В двухмерном массиве вычислить произведение минимального и максимального элемента массива.Решить в.


Написать программу поиска номеров строки и столбца минимального и максимального элемента в заданном двухмерном массиве целых чисел
Написать программу поиска номеров строки и столбца минимального и максимального элемента в заданном.


Поиск максимального четного элемента в массиве
Задан целочисленный массив a из n строк и m столбцов. Найти значение максимального элемента среди.


Поиск максимального и минимального элемента в массиве
Как найти максимальный и минимальный элемент в одномерном массиве

Поиск максимального и минимального элемента в массиве
Помогите отредактировать программу. Задание: Ввести двумерный массив, найти индексы и значения.

Поиск максимального элемента в двумерном массиве
Здравствуйте! Собственно вопрос - оптимальный алгоритм. Есть ли тут вообще алгоритм который находит.

Читайте также: