Резюме
Один из способов создать гистограмму - использовать функцию ЧАСТОТА. В показанном примере формула в ячейках G5: G8:
(=FREQUENCY(data,bins))
где данные (C5: C16) и ячейки (F5: F8) являются именованными диапазонами.
Эта формула вводится как формула массива с несколькими ячейками в диапазоне G5: G8.
Объяснение
Примечание: более поздние версии Excel включают собственную гистограмму, которую легко создать, но не так гибко форматировать. В примере на этой странице показан один из способов создания собственных данных гистограммы с помощью функции ЧАСТОТА и использования обычной столбчатой диаграммы для построения результатов. Поскольку ЧАСТОТА - это формула, результаты и диаграмма будут динамически обновляться при изменении данных.
Функция ЧАСТОТА возвращает частотное распределение, которое представляет собой сводную таблицу, в которой показано количество каждого значения в диапазоне по «ячейкам». FREQUENCY немного сложно использовать, потому что ее нужно вводить как формулу массива. С другой стороны, как только вы правильно настроите свои бункеры, FREQUENCY даст вам все данные сразу!
Настройка и формула
В показанном примере у нас есть список из 12 оценок в именованном диапазоне «данные» (C5: C16). Диапазон F5: F8 - это именованный диапазон «бинов». FREQUENCY будет обрабатывать каждое значение ячейки как верхний предел для этой ячейки. Другими словами, каждая ячейка будет включать количество баллов до значения ячейки включительно. ЧАСТОТА также вернет «счетчик переполнения» - количество значений, превышающих последний интервал.
Чтобы ввести формулу ЧАСТОТА, выполните следующие действия в прилагаемой книге.
1. При необходимости удалите существующие формулы (см. Примечание ниже).
2. Выберите диапазон G5: G8 (все четыре ячейки).
3. Вставьте или введите эту формулу в строку формул:
=FREQUENCY(data,bins)
4. Введите формулу как формулу массива, нажав Ctrl + Shift + Enter. Excel автоматически добавит фигурные скобки ().
Примечание. Чтобы изменить существующие формулы, вам нужно сначала выбрать все четыре ячейки, а затем нажать «Удалить». Вы можете выбрать все ячейки сразу с помощью сочетания клавиш + /.
ЧАСТОТА будет генерировать счетчик для каждого бункера и возвращать массив таких результатов:
(2;4;5;1;0) // array returned by FREQUENCY
Excel поместит первые 4 значения в диапазон G5: G8. Обратите внимание, что счетчик переполнения, ноль, также возвращается как пятый элемент в массиве, поскольку нет значений больше 100. Это значение не отображается на листе, потому что мы ввели формулу только в четыре ячейки.
Excel 365
В Excel 365 вы можете просто ввести одну формулу в ячейку G5, и результаты будут автоматически перенесены на рабочий лист. Нет необходимости использовать Ctrl + Shift + Enter. Однако, если вы воспользуетесь этим подходом, вы также увидите счетчик выходных данных переполнения. Ввод формулы только в 4 ячейки (как указано выше) подавляет это последнее значение. Если вы не возражаете против дополнительного значения, вариант с единственной формулой проще, и вы можете не отображать значение в диаграмме.
Этикетки
Ярлыки в E5: E8 предназначены только для удобства чтения и представления. Их можно настроить по своему усмотрению. В показанном примере формула в ячейке E5, скопированная вниз, имеет следующий вид:
=IF(F5=MIN(bins),"≤"&F5,F4+1&"-"&F5)
Эта формула просто создает метку для каждой ячейки, используя значения ячейки в столбце F.
Гистограмма
Чтобы построить график выходных данных FREQUENCY, выполните следующие действия. Сначала, удерживая клавишу Control, выберите два диапазона: E4: E8 и G4: G8. Затем вставьте столбчатую диаграмму (Вставка> Диаграммы> Кластерный столбец):
Затем щелкните полосу правой кнопкой мыши и отформатируйте ряд данных, чтобы уменьшить ширину зазора до 5% (или по желанию):
Измените название диаграммы по своему усмотрению. В показанном примере мы берем значение в ячейке B2:
Окончательная диаграмма, показывающая нанесенные значения:
Гистограмма с Data Analysis ToolPak
Еще один способ создать гистограмму в Excel - использовать надстройку Data Analysis ToolPak. Это очень простой метод, и он работает в более старых версиях Excel. Однако есть одно ограничение: выходные данные статичны и не будут обновляться автоматически при изменении значений в данных.
