Эксель поиск в массиве по условию

Эксель поиск в массиве по условию

В данной статье показаны 2 способа быстрого поиска значений в двумерных массивах.

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

оба способа получают на выходе отфильтрованный двумерный массив.

Способы формирования отфильтрованных массивов — разные:

второй способ — функцию ArraySearchResults

Основные отличия и особенности этих 2 способов поиска:

  • ArrAutofilterEx позволяет задавать несколько критериев поиска (фильтрации)
  • ArrAutofilterEx ищет вхождение искомого текста в значения заданных столбцов (неточное совпадение)
  • ArrAutofilterEx при каждом вызове заново в цикле перебирает все элементы массива,
    соответственно, при поиске 10 значений время работы кода увеличивается в 10 раз
  • ArraySearchResults позволяет использовать фильтрацию массива только по одному столбцу
  • ArraySearchResults ищет совпадение искомого текста со значением столбца (точное совпадение)
  • ArraySearchResults производит поиск в заранее сформированной текстовой строке
    Таким образом, перебираются все ячейки массива в цикле только один раз, и поиск 100 значений в массиве займёт ненамного больше времени, чем поиск 1 значения.

Примеры поиска в огромных массивах:

Поиск с использованием ArrAutofilterEx

Поиск с использованием ArraySearchResults

Код функции ArraySearchResults:

При поиске только одного значения время работы обоих макросов поиска не сильно отличается — но обычно функция ArraySearchResults оказывается немного быстрее.

  • 58636 просмотров

Комментарии

подскажите, как сделать поиск нескольких искомых значений?

Привет!
Для уважающих Option Explicit
в ArraySearchResults
Dim ro As Long, spl, i As Long, j As Long

В SearchString
Dim buffer As String, buffer2 As String, Sep2 As String, i As Long

и скорость возрастёт

Здравствуйте! А подскажите, пожалуйста, возможно ли использование подстановочных знаков для поиска искомого значения?

решено — можно. Всё работает

И второй вопрос — есть ли у вас функция типа SearchString, но для сцепления ВСЕГО двумерного массива в текстовую строку с разделителями, а не одного столбца. Или придётся цикл делать, чего бы очень не хотелось.
Я так понимаю, что ваш вариант даже шустрее, чем Join, который, к тому же, не работает с двумерными массивами (как я понял).

Здравствуйте! Подскажите пожалуйста — могу ли я объявить Optional ByVal ArrayColumn As Long=1 в функции SearchString? Дело в том, что я часто загружаю в массив данные с листа в 1 столбец…

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

Справился. Немного не так, как хотел, но работает.
For i = 1 To UBound(resArr)
ListBox1.AddItem resArr(i, 2)
Next i

Уважаемый Игорь, подскажите, пожалуйста, как вывести значения, например, второго столбца отфильтрованного массива в листбокс?
Debug.Print "Результат — строка " & i & " из " & UBound(resArr) & ": ", resArr(i, 2)
все показывает, а вывести в листбокс не могу
ListBox1.List = resArr
естественно выводит 2 столбца

А вот и третья функция, которую я применил в своей работе в течение недели.
Все работает "на ура"!
А теперь вот думаю, чтобы я делал без Ваших функций? 🙂
Большое Вам спасибо!
Удачи!

А для большого файла и делается два массива, при этом каждый состоит только из одного столбца (своего рода индексы получаю для поиска (пробовал для теста подсовывать файл на 870 000 записей и загружал в массив порядка 20 столбцов — "машинка" с 4 гигами очень серьезно задумывалась при этом (собственно еще и по этой причине отказался от загрузки всего листа в массив (первая причина отказа — искажение данных при "перегонах")))).

При поиске важно найти все строки, которые есть в большом файле и которым соответствуют строки из маленького, при этом должны анализироваться 2 колонки большого (чтобы было более понятно: по номерам продавцов найти все операции, которые они совершали (первый столбец большого), при этом, отбор производится только в том случае, если операции проводились с другими продавцами). Т.е. в результате поиска по продавцу "Пегасову" (из мелкого файла) должны отобраться строки, когда "Пегасов" что либо продавал другим продавцам, перечисленным в мелком файле. Одному продавцу может соответствовать множество операций.

Читайте также:  Ошибка 0xc0000022 windows 10 как исправить

За ответ: премного благодарен — попробую поэксперементировать (о результатах нагрузочного тестирования сообщу (для информации: обработка 5 файлов (6 500 + 870 000 + 870 000 + 870 000 + 870 000) занимает порядка 28-30 минут на машине с intel i5 650, RAM 4 Gb (на 2 гиговой пробовать этих монстров не стал — жалко, "старушку"))).

Сделать можно, но — оба столбца большого файла не надо загружать в один массив (иначе компу памяти не хватит, т.к. в массиве будет много лишних столбцов)
Компьютер с 2 гигами памяти — для такого макроса более чем достаточно.

Функцию быстрого поиска в массиве можно использовать, только надо искать значения второго (огромного) массива в маленьком (первом)
Можно и наоборот — но возможно понадобится тройное кеширование строки поиска ( buffer$, buffer2$, и ещё добавить buffer3$)

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

Доброго времени суток!
Подскажите. пожалуйста, стоит ли использовать предложенные функции при сравнении трех массивов и копировании результатов на отдельный лист (первый массив — порядка 2 500 записей (данные отдельного файла, берется только 1 столбец), второй и третий — 150 000 — 250 000 (второй и третий формируются на основе одной таблицы (второй файл), но разных столбцов, которые отстоят друг от друга на неком расстоянии (грубо — первый столбец "А", второй — "AB") и изменять порядок столбцов нельзя))?
Последовательный перебор записей относительно медленный, при этом внесение всей таблицы из второго файла (по которому строятся второй и третий массивы) нежелательно, т.к. теряется формат отдельных столбцов при перегоне данных сначала в массив, а потом на лист Excel (собственно по этой причине приходится копировать с листа исходного файла на итоговый лист диапазон ячеек, при этом номер строки вычисляется на основании номера элемента массива). Количество колонок во втором файле — порядка 50-60.
Есть существенное ограничение: рабочая станция, на которой происходит обработка данных, относительно слабая и ждать от нее рекордов не приходится (памяти на ней всего 2 гига, но офис — 2010). Если бы была возможность прикрепить файл, то показал бы — что получилось (если вставить код здесь, то очень много получится).

а если необходимо найти значение в столбце равное 3, затем спуститься на 2 строки и от этой строчки начать отсчет. такое возможно реализовать?
помогите, пожалуйста

Да, можно, если написать для этого специальную функцию.

а если использовать один массив и фильтровать его на основе значений из другого массива? так можно?

Всё можно сделать — но проще под вашу задачу написать отдельную функцию.

Или поступить иначе:
1) сформировать 3 массива при помощи функции ArrAutofilterEx (для каждого из значений)
2) соединить 3 массива в один при помощи функции CombineArrays

Ещё вариант: использовать средства Excel (автофильтр по нескольким значениям)
Тут вам поможет макрорекордер (запись макросов)

И как можно сделать, чтобы отбирать значения из столбца не только с одним значением? Например, столбец для поиска один и тот же = 3, а значения надо отобрать 560, 570, и 580.

А возможна работа только для значений со знаком "="? А можно ли использовать "<>"?

Excel работает за вас

Excel works!

Thanks for Visiting

Поиск в двумерном массиве Excel (по матрице). ВПР и ПОИСКПОЗ

Предположим, нам задан месяц, город и зависящая от них стоимость продукта. Необходимо найти соответствующее им значение в большом двухмерном массиве/матрице/таблице и вывести его в ячейку, формулой. По сути нам нужно вернуть значение на пересечении нужных строки и столбца таблицы. Поиск в двумерном массиве Excel готовыми функциями не сделаешь — СУММЕСЛИ или просто ВПР здесь уже не подходит. Я использую конструкцию из функций ВПР и ПОИСКПОЗ. Как ее правильно записать смотрим ниже.

Конструкция здесь довольно нетривиальная. Поэтому рекомендую ознакомиться с принципами работы функции ВПР и ПОИСКПОЗ перед прочтением статьи ниже, особенно если не встречались с такими функциями ранее.

Пример

Чтобы было было понятнее, начнем с примера.

Имеется таблица по городам и стоимость услуг, стоимости зависят от месяца.

Читайте также:  Коммутатор microlink ml ipsw3300 инструкция

Необходимо найти стоимость для третьего месяца по городу Краснодару или найти Sij, если по умному. Как будем делать?

Поиск в двумерном массиве Excel. Как записать формулу

Как мы обсудили, готовой формулы в Excel нет. Поэтому создадим комбинацию двух формул. В любой ячейки запишем:

Где столбец G — искомый город (Краснодар), а столбец H — месяц (третий). При записи можно использовать и просто одну ячейку, например, G4 и H4

Как видите, формулу на рисунке (с использованием столбцов) можно продлить ниже в другие ячейки.

Описание конструкции формулы:

  1. Функция ВПР осуществляет поиск нужного нам значения (Города, синий столбец G) в большой таблице $B:$E (отмечена зеленым). Для этого нам необходимо указать в каком же именно столбце этой таблицы ВПРу искать город — втором, третьем или четвертом (C, D, E)
  2. Номер этого столбца найдем при помощи функции ПОИСКПОЗ, которая находит номер элемента в массиве. А точнее найдем номер значения фиолетового столбца H (месяца) в массиве $B$2:$E$2. Тем самым мы определили, что ВПР надо использовать третий столбец.
  3. Четвертый аргумент оставляем пустым (после последней «;»), т.к. по умолчанию можно оставить ЛОЖЬ или пусто. Подробнее здесь.

Обратите внимание! Нужно обязательно выполнить два условия, чтобы формула считалась правильно!

— Ширина массива $B$2:$E$2 в ПОИСКПОЗ и ширина таблицы $B:$E должны совпадать.

— Данные в массиве для ПОИСКПОЗ ($B$2:$E$2) должны быть отсортированы от большего к меньшему или от А до Я!

Если есть вопросы — не стесняйтесь писать комментарии!

Допустим ваш отчет содержит таблицу с большим количеством данных на множество столбцов. Проводить визуальный анализ таких таблиц крайне сложно. А одним из заданий по работе с отчетом является – анализ данных относительно заголовков строк и столбцов касающихся определенного месяца. На первый взгляд это весьма простое задание, но его нельзя решить, используя одну стандартную функцию. Да, конечно можно воспользоваться инструментом: «ГЛАВНАЯ»-«Редактирование»-«Найти» CTRL+F, чтобы вызвать окно поиска значений на листе Excel. Или же создать для таблицы правило условного форматирования. Но тогда нельзя будет выполнить дальнейших вычислений с полученными результатами. Поэтому необходимо создать и правильно применить соответствующую формулу.

Поиск значения в массиве Excel

Схема решения задания выглядит примерно таким образом:

  • в ячейку B1 мы будем вводить интересующие нас данные;
  • в ячейке B2 будет отображается заголовок столбца, который содержит значение ячейки B1
  • в ячейке B3 будет отображается название строки, которая содержит значение ячейки B1.

Фактически необходимо выполнить поиск координат в Excel. Для чего это нужно? Достаточно часто нам нужно получить координаты таблицы по значению. Немного напоминает обратный анализ матрицы. Конкретный пример в двух словах выглядит примерно так. Поставленная цель в цифрах является исходным значением, нужно определить кто и когда наиболее приближен к этой цели. Для примера используем простую матрицу данных с отчетом по количеству проданных товаров за три квартала, как показано ниже на рисунке. Важно, чтобы все числовые показатели совпадали. Если нет желания вручную создавать и заполнять таблицу Excel с чистого листа, то в конце статьи можно скачать уже с готовым примером.

Последовательно рассмотрим варианты решения разной сложности, а в конце статьи – финальный результат.

Поиск значения в столбце Excel

Сначала научимся получать заголовки столбцов таблицы по значению. Для этого выполните следующие действия:

  1. В ячейку B1 введите значение взятое из таблицы 5277 и выделите ее фон синим цветом для читабельности поля ввода (далее будем вводить в ячейку B1 другие числа, чтобы экспериментировать с новыми значениями).
  2. В ячейку C2 вводим формулу для получения заголовка столбца таблицы который содержит это значение:
  3. После ввода формулы для подтверждения нажимаем комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве. Если все сделано правильно в строке формул по краям появятся фигурные скобки < >.

В ячейку C2 формула вернула букву D — соответственный заголовок столбца листа. Как видно все сходиться, значение 5277 содержится в ячейке столбца D. Рекомендуем посмотреть на формулу для получения целого адреса текущей ячейки.

Поиск значения в строке Excel

Теперь получим номер строки для этого же значения (5277). Для этого в ячейку C3 введите следующую формулу:

Читайте также:  Как изменить обороты электродвигателя

После ввода формулы для подтверждения снова нажимаем комбинацию клавиш CTRL+SHIFT+Enter и получаем результат:

Формула вернула номер 9 – нашла заголовок строки листа по соответствующему значению таблицы. В результате мы имеем полный адрес значения D9.

Как получить заголовок столбца и название строки таблицы

Теперь научимся получать по значению координаты не целого листа, а текущей таблицы. Одним словом, нам нужно найти по значению 5277 вместо D9 получить заголовки:

  • для столбца таблицы – Март;
  • для строки – Товар4.

Чтобы решить данную задачу будем использовать формулу с уже полученными значениями в ячейках C2 и C3. Для этого делаем так:

  1. Для заголовка столбца. В ячейку D2 введите формулу: На этот раз после ввода формулы для подтверждения жмем как по традиции просто Enter:
  2. Для строки вводим похожую, но все же немного другую формулу:

В результате получены внутренние координаты таблицы по значению – Март; Товар 4:

На первый взгляд все работает хорошо, но что, если таблица будет содержат 2 одинаковых значения? Тогда могут возникнуть проблемы с ошибками! Рекомендуем также посмотреть альтернативное решение для поиска столбцов и строк по значению.

Поиск одинаковых значений в диапазоне Excel

Чтобы проконтролировать наличие дубликатов среди значений таблицы создадим формулу, которая сможет информировать нас о наличии дубликатов и подсчитывать их количество. Для этого в ячейку E2 вводим формулу:

Более того для диапазона табличной части создадим правило условного форматирования:

  1. Выделите диапазон B6:J12 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Правила выделения ячеек»-«Равно».
  2. В левом поле введите значение $B$1, а из правого выпадающего списка выберите опцию «Светло-красная заливка и темно-красный цвет» и нажмите ОК.
  3. В ячейку B1 введите значение 3478 и полюбуйтесь на результат.

Как видно при наличии дубликатов формула для заголовков берет заголовок с первого дубликата по горизонтали (с лева на право). А формула для получения названия (номера) строки берет номер с первого дубликата по вертикали (сверху вниз). Для исправления данного решения есть 2 пути:

  1. Получить координаты первого дубликата по горизонтали (с лева на право). Для этого только в ячейке С3 следует изменить формулу на: В результате получаем правильные координаты как для листа, так и для таблицы:
  2. Получить координаты первого дубликата по вертикали (сверху вниз). Для этого только в ячейке С2 следует изменить формулу на:

В данном случаи изменяем формулы либо одну либо другую, но не две сразу. Стоит напомнить о том, что в ячейке С3 должна оставаться старая формула:

Здесь правильно отображаются координаты первого дубликата по вертикали (с верха в низ) – I7 для листа и Август; Товар2 для таблицы. Оставим такой вариант для следующего завершающего примера.

Поиск ближайшего значения в диапазоне Excel

Данная таблица все еще не совершенна. Ведь при анализе нужно точно знать все ее значения. Если введенное число в ячейку B1 формула не находит в таблице, тогда возвращается ошибка – #ЗНАЧ! Идеально было-бы чтобы формула при отсутствии в таблице исходного числа сама подбирала ближайшее значение, которое содержит таблица. Чтобы создать такую программу для анализа таблиц в ячейку F1 введите новую формулу:

После чего следует во всех остальных формулах изменить ссылку вместо B1 должно быть F1! Так же нужно изменить ссылку в условном форматировании. Выберите: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами»-«Изменить правило». И здесь в параметрах укажите F1 вместо B1 . Чтобы проверить работу программы, введите в ячейку B1 число которого нет в таблице, например: 8000. Это приведет к завершающему результату:

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

Наша программа в Excel нашла наиболее близкое значение 4965 для исходного – 5000. Такая программа может пригодится для автоматического решения разных аналитических задач при бизнес-планировании, постановки целей, поиска рационального решения и т.п. А полученные строки и столбцы позволяют дальше расширять вычислительные возможности такого рода отчетов с помощью новых формул Excel.

Ссылка на основную публикацию
Шаблоны букв английского алфавита для вырезания
Трафареты и шаблоны букв английского алфавита для вырезания из бумаги, это разнообразные шрифты разного стиля и тематики. Трафареты помогут вам...
Что значит спящий режим компьютера
В операционной системе Windows есть несколько режимов выключения компьютера – это обыкновенный режим, (который полностью выключает PC), режим гибернации и...
Что значит сторнировать документ
Сто́рно (итал. storno — перевод на другой счёт, отвод; от stornare — поворачивать обратно) — в общем смысле возврат к...
Шаблоны для брошюры в ворде
Автор: admin Дата записи Быстрей всего набросать буклет, если под рукой окажется готовый шаблон. Проще всего создать буклет в программе...
Adblock detector