Как построить квадратную диаграмму в excel
Обновлено: 05.07.2024
Кривая нормального распределения заданного набора данных. В этой статье описано, как создать диаграмму кривой колокольчика в Microsoft Excel.
Дополнительные сведения
В следующем примере можно создать кривая колокольчика с данными, генерируемой Приложением Excel, с помощью инструмента "Генерация случайных чичек" в области анализа. После того как Microsoft Excel создаст набор случайных чисел, вы можете создать гистограмму, используя эти случайные числа и инструмент "Гистограмма" из набора инструментов "Анализ". На гистограмме можно создать диаграмму для представления кривой колокольчика.
Чтобы создать образец кривой колокольчика, выполните следующие действия:
Введите на новый таблицу следующие заголовки столбцов: A1:Original B1:Average C1:Bin D1:Random E1:Histogram G1:Histogram
Введите на тот же самый таблицу следующие данные:
Введите следующие формулы на одном и том же телефоне:
Эти формулы генерируют среднее (среднее) и стандартное отклонение исходных данных соответственно.
Чтобы создать диапазон для гистограммы, введите следующие формулы:
При этом создается нижняя граница диапазона. Это число представляет три стандартных отклонения меньше среднего.
C3: =C2+$B$4
Эта формула добавляет одно стандартное отклонение к числу, вычисляемму в ячейке выше.
Вы выберите ячейку C3, захватите его, а затем заполните формулу вниз от ячейки C3 до ячейки C8.
Чтобы создать случайные данные, которые станут основой для кривой колокольчика, выполните следующие действия:
В меню "Инструменты" выберите пункт "Анализ данных".
В окне "Средства анализа" нажмите кнопку "Генерация случайного числа" и нажмите кнопку "ОК".
В поле "Число переменных" введите 1.
В поле "Число случайных чисел" введите 2000.
Примечание: Изменение этого числа увеличивает или уменьшает точность кривой колокольчика.
В окне "Распределение" выберите "Обычный".
В области "Параметры" введите число, вычислитее в ячейке B2 (29 в примере) в поле "Подмень".
В поле "Стандартное отклонение" введите число, вычислитее в ячейке B4 (14,68722).
Оставьте поле "Случайное семя" пустым.
В области "Параметры вывода" щелкните "Диапазон вывода".
В поле "Диапазон вывода" введите D2.
При этом будет генерируться 2000 случайных чисел, которые соответствуют нормальному распределению.
Чтобы создать гистограмму для случайных данных, выполните следующие действия:
В меню "Инструменты" выберите пункт "Анализ данных".
В окне "Средства анализа" выберите гистограмму и нажмите кнопку "ОК".
В поле "Диапазон ввода" введите D2:D2001.
В поле "Диапазон диапазонов диапазонов" введите C2:C8.
В области "Параметры вывода" щелкните "Диапазон вывода".
Введите E2 в поле "Диапазон вывода".
Чтобы создать гистограмму для исходных данных, выполните указанные здесь действия.
В меню "Инструменты" выберите пункт "Анализ данных".
Щелкните "Гистограмма" и нажмите кнопку "ОК".
В поле "Диапазон ввода" введите A2:A9.
В поле "Диапазон диапазонов диапазонов" введите C2:C8.
В области "Параметры вывода" щелкните "Диапазон вывода".
В поле "Диапазон вывода" введите G2.
Чтобы создать метки для легенды на диаграмме, введите следующую:
Вы выберите на этом сайте диапазон ячеек (E2:H10).
В меню "Вставка" выберите пункт "Диаграмма".
В области "Тип диаграммы"выберите "Точечная диаграмма XY".
В подвиде диаграммыщелкните в средней строке диаграмму справа.
Примечание: Сразу под этими пятью подпапами в описании будет говорить "Точечная диаграмма с точками данных, соединенная сглаживными линиями без маркеров".
Перейдите на вкладку "Ряд".
В поле "Имя" удалите ссылку на ячейку, а затем выберите ячейку E15.
В поле "Значения X" удалите ссылку на диапазон, а затем выберите диапазон E3:E10.
В поле "Значения Y" удалите ссылку на диапазон, а затем выберите диапазон F3:F10.
Щелкните поле "Имя" и выберите ячейку E14.
Щелкните поле "Значения X" и выберите диапазон E3:E10.
В поле "Значения Y" удалите значение и выберите диапазон G3:G10.
Щелкните поле "Имя" и выберите ячейку E16.
Щелкните поле "Значения X" и выберите диапазон E3:E10.
Щелкните поле "Значения Y", удалите значение и выберите диапазон H3:H10.
Диаграмма будет иметь два изогнутых ряда и плоский ряд вдоль оси x.
Дважды щелкните второй ряд; в легенде она должна быть помечена как "- Bin".
В диалоговом окне "Формат ряда данных" щелкните вкладку "Ось".
Щелкните "По вторичной оси" и нажмите кнопку "ОК".
Теперь у вас есть диаграмма, которая сравнивает заданный набор данных с кривой колокольчика.
Ссылки
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Мы уже рассматривали концепцию динамической диаграммы в одной из предыдущих статей, когда проектировали дашборд. На тот момент, внедренная полоса прокрутки, позволила нам отображать определенную часть информации и прокручивать столбцы диаграмм по мере необходимости. Данный функционал дает возможность экономить место на рабочем листе Excel и фокусироваться на тех данных, которые на самом деле важны. Сегодня мы рассмотрим, как с помощью выпадающего списка и именованных диапазонов создать динамическую диаграмму, которая меняет свой внешний вид в зависимости от ваших потребностей.
Описание проблемы
Представьте, что у вас есть таблица с показателями, разбитыми по годам и вам необходимо визуализировать их.
Создание диаграммы по всем рядам данных ни к чему хорошему не приведет – диаграмма будет перегружена и нечитабельна. Выходом будет создание интерактивной диаграммы, которая будет отображать только тот ряд данных, который нам необходим.
Создание динамической диаграммы
В первую очередь необходимо создать выпадающий список, откуда мы будем выбирать, интересующий нас, показатель. Переходим по вкладке Разработчик в группу Элементы управления, выбираем Вставить –> Элементы управления формы –> Поле со списком.
После того, как вы щелкните по иконке Поле со списком, ваш курсор превратится в перекрестье, это означает, что Excel ожидает указание места, куда необходимо разместить элемент управления. Щелкните в то место на рабочем листе, где вы хотите разместить выпадающий список. Вы увидите большое квадратное поле с треугольником внутри – это и есть первоначальный вид поля со списком. Задайте вашему элементу требуемые размеры, используя маркеры, находящиеся по краям элемента.
Щелкните правой кнопкой мыши по выпадающему списку, выберите Формат объекта. В появившемся диалоговом окне Формат элемента управления, задайте диапазон ячеек, откуда будет формироваться список (в нашем случае, это список всех показателей, по которым мы будем строить график), и ячейку, куда будет помещаться результат выбора из списка.
Далее необходимо создать два именованных диапазона. Первый будет отвечать за значения ряда данных, второй за название. В зависимости от выбора элемента из списка, оба диапазона будут менять свои адреса.
Переходим по вкладке Формулы в группу Определенные имена, выбираем Диспетчер имен и создаем два диапазона с именами значения и название с соответствующими формулами.
На рабочем листе с таблицей с данными выбираем диапазон A1:H2, переходим по вкладке Вставка в группу Диаграммы, выбираем Диаграмму с областями. Excel построил нам диаграмму с одним рядом данных, как мы его и просили.
Меняем значения первого и третьего параметра на уже подготовленные именованные диапазоны
=РЯД(ДинамДиагр! $A$2 ;ДинамДиагр!$B$1:$H$1;ДинамДиагр! $B$2:$H$2 ;1)
Должно получиться так:
=РЯД(ДинамДиагр! название ;ДинамДиагр!$B$1:$H$1;ДинамДиагр! значения ;1)
Теперь при изменении значения в выпадающем списке, наша динамическая диаграмма будет менять внешний вид. Так как именованные диапазоны, которые мы подставили вместо статических параметров в формуле РЯД, тоже будут менять свои адреса.
Осталось задать привлекательный формат нашей диаграмме. Убираем все лишние элементы: линии сетки и название диаграммы. Меняем цвет ряда данных, добавляем к нему линии проекции. Задаем цвет области построения и области диаграммы.
Чтобы название диаграммы вписывалось в общую композицию, вставляем элемент Надпись над диаграммой. Щелкаем левой кнопкой мыши по границе надписи, при этом линия границы становится сплошной линией, в строке формул вставляем именованный диапазон, отвечающий за название ряда данных. В нашем случае он так и называется название.
Динамическая диаграмма готова.
Вам также могут быть интересны следующие статьи
15 комментариев
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Использование новых видов визуализаций в отчетах или дашбордах всегда несет риск введения пользователей в заблуждение или снижения читабельности данных. Квадратная (или вафельная) диаграмма, не искажая понимания данных, является интересной альтернативой стандартным диаграммам Excel.
Как вы видите, вафельная диаграмма представляет из себя квадрат размером 10х10 ячеек, где 1 ячейка соответствует одному проценту из 100. Количество закрашенных ячеек соответствует значению показателя, который вы пытаетесь визуализировать. Данный вид графика будет интересен тем, кто хочет добавить новый вид диаграмм, не искажая данных и не занимая много места на дашборде.
Есть несколько способов реализации диаграмм данного вида. Описанный ниже метод использует инструменты встроенных диаграмм и хотя он сложнее в реализации (чем создание подобных графиков с помощью условного форматирования), является более гибким и позволяет внедрять квадратные диаграммы любых размеров в любое место вашего дашборда.
В сегодняшней статье мы рассмотрим шаги по созданию диаграммы – вафли и научимся быстро их дублировать для визуализации стольких метрик, сколько вам необходимо.
Подготовка данных для вафельной диаграммы
Для начала необходимо создать три диапазона данных, которые будут играть определенную роль в построении диаграммы:
Горизонтальные линии: Данный диапазон необходим для построения горизонтальных линий. В нашем случае это будет ряд от 1 до 10.
Вертикальные линии: Диапазон поможет нам построить вертикальные линии и заполняется нулями.
Значение ячейки: Данный диапазон будет определять, какие ячейки буду закрашены. Он будет содержать формулу.
В ячейки диапазона Значение ячейки вставляем формулу =МАКС(МИН(E$3*100-($B6-1)*10;10);0) и протягиваем ее вниз.
Обратите внимание на абсолютные ссылки в формуле (помечены знаком $). Это позволит быстро копировать и вставлять формулы, когда придет время дублировать диаграммы.
Формула, которую мы вставили в диапазон Значения ячейки, разбивает показатель KPI на группы, размером по 10 единиц. Обратите внимание, что после того, как мы протянули формулу, показатель 45% разбился на 5 групп, состоящих из 4 групп с целыми десятками и одна группа из неполной десятки (5%).
Если вы измените показатель на другое значение, например, 67%, формула разобьет его на 7 групп (6 – целых десяток, 1 – неполная).
На данном этапе мы имеем все данные для построения квадратной диаграммы.
Построение вафельной диаграммы
Создание вафельной диаграммы потребует от вас некоторых усилий. Но есть хорошая новость. Создав диаграмму, ее можно легко дублировать. Итак, алгоритм действий по шагам.
Строим линейчатую диаграмму с группировкой на основе данных диапазона Значения ячейки.
Копируем диапазоны Горизонтальны и вертикальные линии, выделяем диаграмму и вставляем данные.
Закрашиваем область построения в серый цвет, для этого щелкаем правой кнопкой по области построения. В выпадающем меню выбираем пункт Формат области построения. В появившейся панели Параметры области построения, переходим во вкладку Заливка и указываем понравившийся тон серого в пункте Цвет.
Далее необходимо превратить линейчатые диаграммы Горизонтальных и вертикальных линий в точечные. Для этого щелкаем по любому ряду данных правой кнопкой мыши, из выпадающего меню выбираем Изменить тип диаграммы для ряда. В появившемся диалоговом окне Изменение типа диаграммы выбираем для рядов данных Горизонтальные линии и Вертикальные линии из выпадающего списка Тип диаграммы – точечная.
Для ряда данных Горизонтальные линии необходимо добавить значения оси X. Щелкаем правой кнопкой мыши по ряду данных Горизонтальные линии, из выпадающего меню выбираем Выбрать данные. И добавляем диапазон Вертикальные линии, состоящий из нулей.
На данном этапе график должен иметь следующий вид.
Устанавливаем максимальное значение горизонтальной и вспомогательной вертикальной осей равным 10. Для этого щелкаем правой кнопкой мыши по шкале оси, из выпадающего меню выбираем Формат оси. Во всплывающей справа панели Параметры оси устанавливаем фиксированное максимальное значение равным 10. То же самое делаем для вертикальной вспомогательной оси.
Удаляем все шкалы осей. Выбираем ось и нажимаем клавишу Delete.
На следующем шаге необходимо добавить планки погрешностей, они будут играть роль разделителей. Для этого выбираем ряд данных Горизонтальные линии, щелкаем на плюсик, появившийся справа от диаграммы. Во всплывающем окне ставим галочку напротив поля Предел погрешностей. То же самое проделываем для ряда данных Вертикальные линии.
Далее щелкаем правой кнопкой по ряду данных Горизонтальные линии, в выпадающем меню выбираем Формат ряда данных, в панели Параметры ряда устанавливаем наличие маркера в положение Нет.
На этом этапе внешний вид нашей квадратной диаграммы должен иметь следующий вид.
Щелкаем правой кнопкой мыши по ряду данных Значение ячейки, выбираем из выпадающего меню пункт Формат ряда данных. В появившейся панели Параметры ряда устанавливаем Боковой зазор равным 0.
Растяните область построения графика таким образом, чтобы получился квадрат, также вы можете задать другой цвет ряду данных Значения ячейки.
Опционально, вы можете отобразить значение показателя в названии диаграммы. Для этого щелкаем по полю название диаграммы, в строке формул указываем адрес ячейки, которая содержит значение показателя.
На данном этапе вы имеете готовую вафельную диаграмму.
Клонирование вафельной диаграммы
Как мы и говорили ранее, хотя вам потребуются некоторые усилия для создания квадратной диаграммы, вам не нужно будет создавать ее каждый раз с нуля, чтобы визуализировать остальные показатели. Достаточно скопировать ее и указать новый набор данных.
Для начала вам необходимо будет продублировать диапазоны Значения ячейки.
Теперь вы можете вставить необходимое значение в только что созданный диапазон. Далее копируем нашу вафельную диаграмму и вставляем ее в любое место рабочего листа. И в конце, выделяем ряд данных Значения ячейки вновь созданной диаграммы и переносим адрес ряда данных в новый диапазон.
Вы можете повторить процесс для неограниченного количества KPI.
И помните, так как это является полноценной диаграммой, вы можете изменять ее размер по вашему усмотрению и перемещать ее куда захочется.
Как вы видите, данные графики позволяют оживить отчет или дашборд с помощью нового вида визуализации и привлечь к себе внимание. К тому же, их размеры можно сократить таким образом, чтобы они не занимали много места. Тем не менее у данной диаграммы есть один очень большой недостаток, он отображает только один показатель без трендов.
Вам также могут быть интересны следующие статьи
9 комментариев
Очень интересная диаграмма!
Спасибо Вам за навык!
Попробовал макросом цвет менять в зависимости от значения, ничего не получилось.
Можете посоветовать как бы Вы сделали?
С помощью диаграммы с картой можно сравнивать значения и показывать категории по географическим регионам. Используйте ее, если ваши данные включают географические регионы (например, сведения о странах, областях или почтовых индексах).
Скачивание примеров
Вы можете скачать книгу с несколькими примерами диаграмм с картами, как в этой статье.
На диаграммах с картами значения и категории могут отображаться по-разному. Значения представляются небольшими вариантами двух–трех цветов. Категории представлены разными цветами.
Например, на приведенной ниже диаграмме Countries by Tax Revenue % используются значения. Значения представляют налоговый доход в каждой стране, каждый из которых представлен с использованием спектра двух цветов градиента. Цвет для каждой области определяется местом, в котором находится его значение в спектре. По умолчанию чем выше значение, тем темнее соответствующий цвет.
В приведенном ниже примере карты стран по численности населения для отображения категорий используется легенда, чтобы показать группы или связи. Каждая страна представлена разными цветами.
Создание диаграммы с картой с типами данных
Диаграммы с картами стали еще проще благодаря географическим типам данных. Просто ввести список географических значений, таких как страна, область, округ, город, почтовый индекс и так далее, а затем выберите свой список и перейдите на вкладку Данные > Типы данных >Geography. Excel данные будут автоматически преобразованы в географический тип данных и будут включать свойства, соответствующие этим данным, которые можно отобразить на диаграмме с картой. В следующем примере мы преобразуем список стран в географические типы данных, а затем выбрали налоговый доход (%). из области "Добавить столбец" для использования на карте.
Теперь нужно создать диаграмму с картой, поэтому выберем любую ячейку в диапазоне данных, а затем перейдите на вкладку Вставка > Диаграммы > Карты> Картас заливкой .
Если предварительный просмотр выглядит хорошо, нажмите кнопку ОК. В зависимости от данных Excel вставляется карта значений или категорий.
Совет: Если данные настроены как таблица Excel ,а затем вы добавите страну в список, Excel автоматически обновить их как географический тип данных и обновить связанную диаграмму с картой. Аналогично, если вы удалите страну, Excel также удалит ее с диаграммы.
Форматирование диаграммы с картой
После создания диаграммы с картой вы можете легко настроить ее оформление. Просто щелкните карту, а затем выберите вкладку Конструктор диаграмм или Формат на ленте. Вы также можете дважды щелкнуть диаграмму, чтобы запустить области задач Формат объекта ,которая появится в правой части окна Excel объекта. При этом на диаграмме с картой также будут доступны определенные параметры ряда (см. ниже).
Существует несколько параметров ряда диаграмм скартами, но они не поддерживаются на устройствах с Android и Excel Mobile. Если вам нужны некоторые параметры ряда карт, вы можете создать диаграмму в Excel для Windows или Mac и просмотреть ее на устройстве с Android или Excel Mobile.
Дополнительные сведения см. в статье Форматирование диаграмм с картой.
Проекции карты— изменение стиля проекции карты. По умолчанию Excel будет отображаться наиболее эффективный стиль.
Область карты: изменение масштаба карты в представлении области или края до всемирного представления.
Подписи на карте— показывать географические названия стран и регионов. Выберите отображение названий по размещению или всех надписей.
Вопросы и ответы
Ответ. Если вы используете данные, в которых может быть несколько похожих мест в мире, диаграммы с картами не могут различить их без дополнительных указаний. По возможности добавьте столбец с подробными данными более высокого уровня. Например, следующие расположения могут не совпадать с тем, как вы ожидаете, так как в мире есть много мест, где есть допустимые названия округов:
Однако данные должны правильно соотвяться, если добавить еще один столбец для более высокого уровня детализации, в данном случае — Province — это называется Устранение неоднозначности.
Если имеется несколько уровней географических данных, необходимо разделить каждый уровень на отдельную ячейку или столбец. Например, "Вашингтон, США" (штат, страна) не будет успешно создавать карту. В этом случае данные будут успешно соблюском при размещении "Вашингтон" и "США" в отдельных столбцах.
данные, которые не соединяются (объединяются области и страна)
данных, которые создадут карту штата Вашингтон
Ограничения
На диаграммах с картами могут быть только географические данные высокого уровня, поэтому широта и долгота, а сопоставление улиц не поддерживается. Кроме того, на диаграммах с картами допустимо только одномерное отображение, поэтому для работы с многомерными данными используйте функцию 3D Maps в Excel.
При создании новых карт или добавлении данных на имеющиеся необходимо подключение к Интернету (для подключения к службе карт Bing).
Для просмотра имеющихся карт подключение к Интернету не требуется.
Существует известная проблема, из-за которой диаграммы с картами, в которых используются географические типы данных, иногда соеданы неправильно. При попытке отфиксировать эти поля, пока проблема не будет устранена, попробуйте включить в нее поля администрирования, например "Край" или "Страна".
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Построение диаграммы в Microsoft Excel по таблице – основной вариант создания графиков и диаграмм другого типа, поскольку изначально у пользователя имеется диапазон данных, который и нужно заключить в такой тип визуального представления.
В Excel составить диаграмму по таблице можно двумя разными методами, о чем я и хочу рассказать в этой статье.
Способ 1: Выбор таблицы для диаграммы
Откройте необходимую таблицу и выделите ее, зажав левую кнопку мыши и проведя до завершения.
Вы должны увидеть, что все ячейки помечены серым цветом, значит, можно переходить на вкладку «Вставка».
Там нас интересует блок «Диаграммы», в котором можно выбрать одну из диаграмм или перейти в окно с рекомендуемыми.
Откройте вкладку «Все диаграммы» и отыщите среди типов ту, которая устраивает вас.
Справа отображаются виды выбранного типа графика, а при наведении курсора появляется увеличенный размер диаграммы. Дважды кликните по ней, чтобы добавить в таблицу.
Предыдущие действия позволили вставить диаграмму в Excel, после чего ее можно переместить по листку или изменить размер.
Дважды нажмите по названию графика, чтобы изменить его, поскольку установленное по умолчанию значение подходит далеко не всегда.
Не забывайте о том, что дополнительные опции отображаются после клика правой кнопкой мыши по графику. Так вы можете изменить шрифт, добавить данные или вырезать объект из листа.
Для определенных типов графиков доступно изменение стилей, что отобразится на вкладке «Конструктор» сразу после добавления объекта в таблицу.
Как видно, нет ничего сложного в том, чтобы сделать диаграмму по таблице, заранее выбрав ее на листе. В этом случае важно, чтобы все значения были указаны правильно и выбранный тип графика отображался корректно. В остальном же никаких трудностей при построении возникнуть не должно.
Способ 2: Ручной ввод данных
Преимущество этого типа построения диаграммы в Экселе заключается в том, что благодаря выполненным действиям вы поймете, как можно в любой момент расширить график или перенести в него совершенно другую таблицу. Суть метода заключается в том, что сначала составляется произвольная диаграмма, а после в нее вводятся необходимые значения. Пригодится такой подход тогда, когда уже сейчас нужно составить график на листе, а таблица со временем расширится или вовсе изменит свой формат.
На листе выберите любую свободную ячейку, перейдите на вкладку «Вставка» и откройте окно со всеми диаграммами.
В нем отыщите подходящую так, как это было продемонстрировано в предыдущем методе, после чего вставьте на лист и нажмите правой кнопкой мыши в любом месте текущего значения.
Из появившегося контекстного меню выберите пункт «Выбрать данные».
Задайте диапазон данных для диаграммы, указав необходимую таблицу. Вы можете вручную заполнить формулу с ячейками или кликнуть по значку со стрелкой, чтобы выбрать значения на листе.
В блоках «Элементы легенды (ряды)» и «Подписи горизонтальной оси (категории)» вы самостоятельно решаете, какие столбы с данными будут отображаться и как они подписаны. При помощи находящихся там кнопок можно изменять содержимое, добавляя или удаляя ряды и категории.
Обратите внимание на то, что пока активно окно «Выбор источника данных», захватываемые значения таблицы подсвечены на листе пунктиром, что позволит не потеряться.
По завершении редактирования вы увидите готовую диаграмму, которую можно изменить точно таким же образом, как это было сделано ранее.
Вам остается только понять, как сделать диаграмму в Excel по таблице проще или удобнее конкретно в вашем случае. Два представленных метода подойдут в совершенно разных ситуациях и в любом случае окажутся полезными, если вы часто взаимодействуете с графиками во время составления электронных таблиц. Следуйте приведенным инструкциям, и все обязательно получится!
Читайте также: