Поиск нужных данных в диапазоне

Рассмотрим поиск текстовых значений в списке с повторами. Поиск позиции можно производить не только в диапазонах ячеек, но и в массивах констант. Функция CHOOSE (ВЫБОР) возвращает значение из списка, выбирая его в соответствии с числовым индексом. Если тип_сопоставления равен 0, то функция ПОИСКПОЗ() находит первое значение, которое в точности равно аргументу искомое_значение.


Рассмотрим задачу в случае текстовых значений. Например: ПОИСКПОЗ(«б»;{«а»;»б»;»в»;»б»};0) возвращает число 2 — относительную позицию буквы «б» в массиве {«а»;»б»;»в»;»б»}. Искомое_значение — значение, используемое при поиске значения в просматриваемом_массиве. В файле примера можно найти применение функции при поиске в горизонтальном массиве. В качестве критерия можно задать»яблок*» и формула =ПОИСКПОЗ(«яблок*»;B53:B62;0) вернет позицию текстового значения, начинающегося со слова яблок (если она есть в списке).

Для этого необходимо выделить несколько ячеек (расположенных вертикально), в Строке формул ввести вышеуказанную формулу и нажать CTRL+SHIFT+ENTER. Столбец Позиция приведен для наглядности и не влияет на вычисления.

На пятый день марафона мы займёмся изучением функции CHOOSE (ВЫБОР). Стоит отметить, что в большинстве случаев лучше выбрать другую функцию, например, INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ) или VLOOKUP (ВПР). Мы рассмотрим эти функции позже в рамках данного марафона.

Выполнять поиск по списку гораздо удобнее на рабочем листе, чем вносить все элементы в формулу. Например, если ячейка B2 содержит номер месяца, функция CHOOSE (ВЫБОР) может вычислить, к какому финансовому кварталу он относится.

В таблице, представленной ниже, Вы можете увидеть номер финансового квартала под каждым номером месяца. В функцию CHOOSE (ВЫБОР) номер квартала необходимо вводить в том порядке, в каком они расположены в таблице. В столбце H для каждого дня недели записано число дней, которое нужно прибавить к текущей дате, чтобы получить следующий понедельник.

Вы можете использовать функцию CHOOSE (ВЫБОР) в сочетании с другими функциями, например, SUM (СУММ). В нашем примере номер магазина (101, 102 или 103) введён в ячейке C2. Чтобы получить значение индекса, такое как 1, 2 или 3, вместо 101, 102 или 103, используйте формулу: =C2-100. Внутри функции SUM (СУММ) в первую очередь будет выполнена функция CHOOSE (ВЫБОР), которая вернет требуемый диапазон для суммирования, соответствующий выбранному магазину.

Николай, добрый день! При попытке добавить значение, которого нет в списке, макрос ломается. Как быть в такой ситуации? Николай, большое вам спасибо за очень полезный ресурс и, в частности, за эту статью. С какого листа был указан диапазон для создания выпадающего списка (Данные — Проверка данных — Список — Источник) — все равно (в пределах одной книги). Макрос привязан только к тому листу, где создан выпадающий список, а не с которого берутся для него данные.

Маленький, но важный вопрос — есть ли возможность фильтрации по столбцу в котором данные идут с накоплением по первичным значениям? Excel воспринимает строки с данными полученными накоплением как новые полные данные, а мне необходимо из множествастрок в столбце выбирать те которые содержат первичное значения.

Например: список у меня в ячейке B1, в нем я выбираю нужную позицию, и эта позиция должна отобразиться в ячейке A1 (т.е. со смещением влево). Как сделать так чтобы список работал на нескольких листах одной книги? Опыт в работе с макросами = новичок. И ещё проблема — при попытке прописать формулу, ссылающуюся на ячейку с введёнными из списка значениями, формула просто пропадает при нажатии Enter. То есть как будто и не писала ничего.

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

Не перестаю благодарить за сайт и за ответы, но есть проблема с 3-м вариантом. Другие ячейки ссылаются на этот список, но при выборе значений из списка отображаются все 10 значений, из которых 5 названных, а 5 пустые.

2. как применить каждый из вариантов только к одному столбцу в одном листе?3. Как правильно вставить в окно VBA одновременно несколько макросов по всем 3-м вариантам? Основная проблема в том, что в ячейке выбрано несколько вариантов . Заранее спасибо!

Сам список, из которого выбираются данные, я создал рядом с этой таблицей. Тогда я взял эти данные и перенес на соседний лист этой книги, создал снова выпадающий список, подтянул данные с соседнего листа и все работает.

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

Не как у вас фиксированно к примеру 6 строк с С2:С8 а одна активная, между скриптами одна разделительная строка и перемещались бы они автоматически в зависимости от колличества выбранных данных. P.S. на листе 3 столбца с выпадающим списком, но срабатывает с мультивыбором только один, как сделать чтобы все работали? Подскажите пожалуйста, возможно ли составить все три макроса в модуле одного листа. Когда я пробую сделать все так как описано, макросы не работают.

Поиск нужных данных в диапазоне

Файл постоянно дополняется данными, а этот макрос упрощает работу в 1000 раз…. P.S. В перспективе надеюсь сделать общую форму в которой будут прописываться артикулы названия и цены для заказа запчастей на производстве.

Функция CHOOSE (ВЫБОР) отлично работает с простыми списками чисел в качестве значений. В Excel 2003 и более ранних версиях функция CHOOSE (ВЫБОР) поддерживала лишь 29 аргументов value (значение). Если в списке присутствует несколько значений, удовлетворяющих критерию, то эта функция не поможет. Функции ПОИСКПОЗ() и ИНДЕКС() часто используются вместе, т.к. позволяют по найденной позиции в одном диапазоне вывести соответствующее значение из другого диапазона.