Во вкладке «Выбор группы» автоматически формировалась база контактов

Запрос от: Симеон (17.01.16)     РЕШЕНО

Хочу чтобы во вкладке “Выбор группы” при выборе из выпадающего списка в таблице автоматически формировалась база и подтягивались данные из вкладки “Список”. Что-то на подобии как при использовании функции ВПР, только ВПР находит только первое значение по списку, а вот как сделать так, чтобы выводились все значения я ломаю голову и не могу придумать формулу.

Файл запроса: Формирование_группы.xlsx


Файл ответа:  Формирование_группы_ответ.xlsx

ОтветСимеон, итак, ваша задача такова, что вы хотите в ячейке В1 выбрать из списка «группу».

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

sim1

Контакты вы хотите подтягивать с листа «Список».

Решение:

1. На листе «Список» необходимо добавить два пустых столбика перед вашей таблицей контактов.

sim2

2. В столбике В, начиная с ячейки В2, вам необходимо ввести формулу

sim3

Обратите внимание, что мы зафиксировали только верхнюю ячейку диапазона $C$1, – это позволяет копировать формулу вниз, смещая при этом нижнюю границу диапазона. Это позволяет для каждой новой строки выводить порядковый номер строки, в которой повторяется одна и та же группа в диапазоне выше.

3. Копируем формулу вниз для всего списка контактов 

sim4

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

4. В столбике А, начиная с ячейки А2, нам необходимо вписать формулу:

sim5

Это формула позволяет нам объединить наименование группы из столбика С и порядковый номер одной и той же группы  из столбика В.

5. Копируем формулу для остальных строк списка контактов в столбике А.

sim6

Это позволяет нам получить уникальный код каждого контакта в списке, который содержит в себе наименование группы в первой части объединенного кода в столбике А и порядковый номер записи одной и той же группы (вторая часть объединенного кода).

6. Переходим на лист «Выбор группы» и в столбике В, начиная с ячейки В2, проставляем нумерацию строк по возрастанию.

sim7

7. В ячейке А2 пишем формулу

sim8

Что видно из приведенной формулы? Мы объединяем текст из ячейки В1, в которой находится название группы, которую мы выбираем из выпадающего списка. И объединяем этот текст с порядковым номером строки таблицы, в которую мы хотим внести информацию с листа «Список».

8. Копируем формулу напротив всех строк таблицы

sim9

9. В ячейке С2 пишем формулу ВПР, которая позволяет найти фамилию на листе «Список» по уникальному объединенному коду, состоящему из названия группы и порядкового номера строки.

sim10

10. Полученную формулу ВПР заключаем внутрь формулы ЕСЛИОШИБКА таким образом:

sim11

Это позволит скрыть информацию об ошибке, если в нашем списке контактов не будет определенного порядкового номера. Например, у нас на листе «Список» всего три контакта, которые относятся к «Группе 2». Если формулу копировать на листе «Выбор группы» ниже третьей строки, то ВПР даст ошибку, так как, например, 4 записи (и так далее) в нашем изначальном списке нет.

11. Копируем формулу в ячейку D2 и изменяем в формуле ВПР номер столба с 4 на 5.

sim12

Это позволяет по объединенному уникальному коду подбросить номер контакта из списка контактов.

12. Аналогичным образом копируем формулу в ячейку Е2 и меняем номер столбика с 4 на 6.

sim13

13. Все три формулы из диапазона C2:E2 копируем вниз нашей таблицы

sim14

14. Теперь мы можем выбрать любую другую группы из списка я ячейке В2 и посмотреть на результат. Например, выберем группу 1. 

sim15

Отправленный запрос был решён – при выборе группы выводиться весь список контактов с листа «Список», которые попадают в определенную группу.

Свой запрос Вы можете оставить с помощью формы по этой ссылке

Если Вы хотите изучить MS Excel, мы Вам предлагаем пройти дистанционный курс обучения «MS Excel для бизнеса»

ex102_course