Как я могу суммировать возрастные диапазоны и количество в Excel?

Question

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

Необработанные данные

---------------
| ID   | Age  |
---------------
   1      1
   2      5
   3      10
   4      1
   5      6
   6      3
   7      1
   8      5
   ... and so on.

Суммированный выход

---------------
| Age | Count |
---------------
  1      13
  2      5
  3      2
    ....
  58     5
  59     2
  60     7

То, что я хотел бы сделать с вышеупомянутыми, обобщенными данными, далее суммировать их в группах по 5 человек. Например, я хочу найти общее количество детей в возрасте от 1 до 5 лет, от 6 до 10 лет, от 11 до 15 лет и т.д. И т.д., Например, приведенные ниже примеры данных.

---------------------
| Age Range | Count |
---------------------
  1-5          48
  6-10         35
  11-15        40
        ...
  46-50        25
  51-55        19
  56+          17    

Есть ли встроенная функция суммирования, которая может сделать это, или мне нужно будет группировать и суммировать эти числовые данные самостоятельно? Если для моего запроса нет четкой цели, мой конечный результат - отобразить гистограмму этих возрастных диапазонов, потому что показ графика для каждого возраста слишком велик. Вместо 60+ баров, я бы предпочел около 10. Это может быть сделано?

Answer 1

Метод 1: Новое поле и использование сводной таблицы

Добавьте новый столбец к вашим данным с заполнением следующего.

=INT((B2-1)/5)*5+1&"-"&(INT((B2-1)/5)+1)*5

Или еще лучше, используйте эту формулу, которая даст вам ту категорию «56+», которую вы просили:

=IF(B2>55,"56+",INT((B2-1)/5)*5+1&"-"&(INT((B2-1)/5)+1)*5)

Вы можете легко получить необходимую сводку с помощью сводной таблицы. Добавьте новое поле AgeRange для меток строк и Count of ID для значений.

Способ 2. Использование функции FREQUENCY для построения гистограммы

Создайте столбец верхних границ возрастных диапазонов, которые вы хотите построить, т. Е. {5, 10, ..., 55}. Выберите ячейки в соседнем столбце, пройдя одну ячейку за нижним рядом ваших верхних границ. Не изменяя выделение, вставьте следующее в строку формул и нажмите Ctrl+Shift+Enter:

=FREQUENCY(B2:B101,E2:E12)

Где B2:B101 - ваш столбец возрастов, а E2:E12 - ваш столбец верхних границ.

Результат:

Answer 2

Это, вероятно, не совсем то, что вы ищете, но при условии, что ID и возраст указаны в столбцах A и B соответственно. Поместите следующую формулировку в C2

=IF(AND($B2<E$1,$B2>=D$1),1,0)

Разместите разделители вашего возраста в строке 1.

Суммируйте полученные значения, чтобы получить ваши цифры на графике.