по каким формулам в экселе решится 13 задача?
Формулировка подсчёта в Excel
Мне нужно разработать способ с помощью формулы учитывать дублирующихся детей и родственные связи, в частности подсчитать уникальных родителей мужского пола, у которых есть хотя бы один ребёнок. Следует использовать формулы Excel и указать, работаю ли я с двумя листами/таблицами и соответствующими столбцами. Я хочу составить надёжные формулы со структурированными ссылками на таблицы.
Поскольку фактическая книга Excel отсутствует, итогового числа у меня не будет. Но пользователь запросил формулы, поэтому я сосредоточусь на этом. Также нужно точно разобрать изображение, учитывая задание № 13.
В таблице Люди есть такие поля, как ID, Фамилия ИО, Пол и Город рождения. В таблице Родственные связи есть столбцы ID, ID родителя и ID ребёнка. Мне нужно подсчитать родителей мужского пола, указанных в столбце ID родителя, у которых Пол = "м", при этом каждый родитель мужского пола должен учитываться только один раз.
Краткий ответ: если в таблице «Люди» столбец A — ID, C — Пол, а в таблице «Родственные связи» столбец B — ID родителя, используйте:
=СУММПРОИЗВ((Люди!$C$2:$C$1000="м")*(СЧЁТЕСЛИ('Родственные связи'!$B$2:$B$1000;Люди!$A$2:$A$1000)>0))
Число 1000 замените на последнюю строку таблицы.
Как работает формула
СЧЁТЕСЛИпроверяет, сколько раз ID каждого человека встречается среди ID родителей.- Условие
>0оставляет только людей, у которых есть хотя бы один ребёнок. - Условие
Люди!$C$2:$C$1000="м"выбирает мужчин. СУММПРОИЗВсчитает людей, для которых выполнены оба условия.
Если у одного мужчины несколько детей, он всё равно будет посчитан один раз, потому что проверяется таблица «Люди», где каждый человек записан одной строкой.
Вариант через вспомогательный столбец
В таблице «Люди» в новом столбце, например E2, введите:
=ЕСЛИ(СЧЁТЕСЛИ('Родственные связи'!$B$2:$B$1000;A2)>0;1;0)
Протяните формулу вниз. Затем посчитайте:
=СЧЁТЕСЛИМН(Люди!$C$2:$C$1000;"м";Люди!$E$2:$E$1000;1)
Эта формула даст количество мужчин, у которых есть хотя бы один ребёнок.
Обсуждение
6 сообщенийа есть попроще формула?
Да. Самый простой вариант — через вспомогательный столбец.
В таблице «Люди» в новом столбце E2 напишите:
=ЕСЛИ(СЧЁТЕСЛИ('Родственные связи'!$B:$B;A2)>0;1;0)
Протяните формулу вниз. Затем посчитайте мужчин с детьми:
=СЧЁТЕСЛИМН(C:C;"м";E:E;1)
Здесь:
C:C— столбец Пол;E:E— вспомогательный столбец;1означает, что у человека есть ребёнок.
как будет решаться 14 задание
В 14-м задании нужно найти, сколько раз каждый ID родителя встречается в таблице «Родственные связи», а затем выбрать максимальное значение.
Если столбец B — это ID родителя:
- В соседнем столбце
C2введите:
=СЧЁТЕСЛИ($B:$B;B2)
- Протяните формулу вниз.
- Найдите наибольшее значение:
=МАКС(C:C)
Получившееся число — это максимальное количество детей у одного родителя.
столбец A это айди родителя
Тогда формула должна ссылаться на столбец A.
В ячейке B2:
=СЧЁТЕСЛИ($A$2:$A$1000;A2)
Протяните её вниз. Она посчитает, сколько раз каждый ID родителя встречается в столбце A.
Затем найдите самое большое количество детей:
=МАКС(B2:B1000)
Диапазон 1000 замените на номер последней строки таблицы.
