Во вкладке «Выбор группы» автоматически формировалась база контактов
Запрос от: Симеон (17.01.16) РЕШЕНО
Хочу чтобы во вкладке «Выбор группы» при выборе из выпадающего списка в таблице автоматически формировалась база и подтягивались данные из вкладки «Список». Что-то на подобии как при использовании функции ВПР, только ВПР находит только первое значение по списку, а вот как сделать так, чтобы выводились все значения я ломаю голову и не могу придумать формулу.
Файл запроса: Формирование_группы.xlsx
Файл ответа: Формирование_группы_ответ.xlsx
Ответ: Симеон, итак, ваша задача такова, что вы хотите в ячейке В1 выбрать из списка «группу».
После выбора любой из группы, необходимо в таблице отобразить весь перечень контактов, которые входят в выбранную группу.
Контакты вы хотите подтягивать с листа «Список».
Решение:
1. На листе «Список» необходимо добавить два пустых столбика перед вашей таблицей контактов.
2. В столбике В, начиная с ячейки В2, вам необходимо ввести формулу
Обратите внимание, что мы зафиксировали только верхнюю ячейку диапазона $C$1, — это позволяет копировать формулу вниз, смещая при этом нижнюю границу диапазона. Это позволяет для каждой новой строки выводить порядковый номер строки, в которой повторяется одна и та же группа в диапазоне выше.
3. Копируем формулу вниз для всего списка контактов
Таким образом получаем, что каждая строка с определенной группой получает свой порядковый номер из одинакового списка одной и той же группы.
4. В столбике А, начиная с ячейки А2, нам необходимо вписать формулу:
Это формула позволяет нам объединить наименование группы из столбика С и порядковый номер одной и той же группы из столбика В.
5. Копируем формулу для остальных строк списка контактов в столбике А.
Это позволяет нам получить уникальный код каждого контакта в списке, который содержит в себе наименование группы в первой части объединенного кода в столбике А и порядковый номер записи одной и той же группы (вторая часть объединенного кода).
6. Переходим на лист «Выбор группы» и в столбике В, начиная с ячейки В2, проставляем нумерацию строк по возрастанию.
7. В ячейке А2 пишем формулу
Что видно из приведенной формулы? Мы объединяем текст из ячейки В1, в которой находится название группы, которую мы выбираем из выпадающего списка. И объединяем этот текст с порядковым номером строки таблицы, в которую мы хотим внести информацию с листа «Список».
8. Копируем формулу напротив всех строк таблицы
9. В ячейке С2 пишем формулу ВПР, которая позволяет найти фамилию на листе «Список» по уникальному объединенному коду, состоящему из названия группы и порядкового номера строки.
10. Полученную формулу ВПР заключаем внутрь формулы ЕСЛИОШИБКА таким образом:
Это позволит скрыть информацию об ошибке, если в нашем списке контактов не будет определенного порядкового номера. Например, у нас на листе «Список» всего три контакта, которые относятся к «Группе 2». Если формулу копировать на листе «Выбор группы» ниже третьей строки, то ВПР даст ошибку, так как, например, 4 записи (и так далее) в нашем изначальном списке нет.
11. Копируем формулу в ячейку D2 и изменяем в формуле ВПР номер столба с 4 на 5.
Это позволяет по объединенному уникальному коду подбросить номер контакта из списка контактов.
12. Аналогичным образом копируем формулу в ячейку Е2 и меняем номер столбика с 4 на 6.
13. Все три формулы из диапазона C2:E2 копируем вниз нашей таблицы
14. Теперь мы можем выбрать любую другую группы из списка я ячейке В2 и посмотреть на результат. Например, выберем группу 1.
Отправленный запрос был решён – при выборе группы выводиться весь список контактов с листа «Список», которые попадают в определенную группу.
Свой запрос Вы можете оставить с помощью формы по этой ссылке
Если Вы хотите изучить MS Excel, мы Вам предлагаем пройти дистанционный курс обучения «MS Excel для бизнеса»
