Я не совсем уверен, является ли это самым простым способом, но если он уверен, что родители сгруппированы в Рабочем листе A (так, как будто вы написали, а не как например.
John Scott
Victoria Jason
John Lucas
), вы можете рассмотреть возможность использования этого решения (я предполагаю, что данные вашего рабочего листа A начинаются с "Джон" в ячейке A2, WA обозначает рабочий лист A, а WB обозначает рабочий лист B):
Прежде всего, добавьте еще один столбец, который называется смещение в WA. Поскольку "Возраст" - это столбец C , я поместил его в столбец D Формула имеет вид =Row(A2)-Row($A$2) и распространяется вниз. Вы должны получить 0 , 1 , 2 ... Это смещения каждой строки по отношению к первой.
Мы будем использовать это значение для нашего смещения с помощью функции VLOOKUP . Допустим, в WB вы вводите имя своего родителя в ячейку A1 , в B1 мы получим имя (имена) ребенка. Итак, во вспомогательном столбце, скажем C , мы помещаем формулу
=VLOOKUP(A1; WA!$A$2:$D$xxx; 4; FALSE)
параметры означают:
A1 - это значение ячейки, которое вы ищете - имя родителяWA!$A$2:$D$xxx обозначает диапазон, в котором вы ищете (замените xxx на количество строк, которое вы получите)4 означает, что мы хотим получить значение 4-го столбца, то есть WA!D в этом случае, и это наше смещениеFALSE означает, что мы хотим выполнить точный поиск как WA!A
Полученное вами значение - это значение, с которого должен начинаться ваш диапазон. Теперь нам нужно найти его окончание, это значит, сколько там строк. Самым простым способом может быть добавление еще одного столбца в WA, например, E , где мы помещаем простую формулу, подсчитывающую, сколько детей имеет родительский элемент:
=COUNTIF($A$2:$A$xxx; A2)
В вашем примере вы получите 3 , 3 , 3 , 2 , 2 , 2 , 2 .
Вы должны скопировать это значение в WB, столбец D , используя снова:
=VLOOKUP(A1; WA!$A$2:$E$xxx; 5; FALSE)
Теперь в WB мы имеем в столбце A имя родителя, в столбце B мы вставляем дочерний элемент, в столбце C смещение первого дочернего элемента родителя, а в столбце D количество дочерних элементов, которое имеет родительский элемент.
Теперь перейдите к менеджеру имен и добавьте новое имя Kids охватывающее ассортимент:
=OFFSET(WA!$B$2:$B$xxx; WB!D1; 0; WB!E1)
где параметры означают:
WA$B$2:$B$xxx - это диапазон, содержащий дочерние имена,WB!D1 (примечание: это относительно!) это смещение, поэтому, где дочерний список начинается в ранее упомянутом диапазоне в WA,0 означает, что мы не хотим делать смещение в столбцах,WB!E1 говорит, сколько строк мы хотим получить (т.е. сколько детей).
Имея это имя для Kids , вы добавляете сейчас в WB!B1 Проверка данных, установите список и источник =Kids . Теперь распространяйте это вниз.
Это все.
Если ваши данные в WA не содержат сгруппированных родителей, вы можете получить их, отсортировав их.
