Как создать динамический график / матрицу разброса с метками и категориями на обеих осях в Excel 2010?

Давайте рассмотрим следующий набор данных:

Name   |  Age  |  Hair Color
----------------------------- 
John   | Young | Brown
Sophie |  Old  | Blond
Adam   | Mature| Blond
Mark   |  Teen | Dark
Jeremy |  Old  | Grey
Alex   | Young | Brown

etc...

Возраст и цвет волос могут принимать только определенные значения (молодые / подростки / зрелые / старые и светлые / коричневые / темные / серые). Имя является единственной реальной переменной здесь.

Я хочу создать Scatter Plot / Matrix, который будет выглядеть примерно так:

Желаемый график

Я знаю, что должен использовать этот инструмент, чтобы добавить метки на график рассеяния.

Я также нашел это видео на YouTube, которое объясняет, как отображать категории по оси Y

Кроме того, мне нужно, чтобы график был динамичным, как объяснено в другом видео на YouTube.

Как мне объединить все эти подходы, чтобы получить график рассеяния с категориями в качестве значений на обеих осях?

Источник

1 ответ

Решение

Excel (и надстройка Chart Labeler) могут создать диаграмму такого типа с большим творческим потенциалом.

введите описание изображения здесь

Вот как:

  1. Разместите ваши данные. Для этого вам понадобится несколько вспомогательных столбцов.

xy_data

  1. Сначала вам нужно преобразовать свои категориальные данные в числа, а затем отобразить эти цифры на графике. В этом примере это происходит в X_Age а также Y_Hair колонны.

  2. Используйте надстройку Chart Labeler, чтобы добавить свои метки для ваших точек данных. В этом случае у вас было две перекрывающиеся метки (Alex & John), которые нужно было настраивать вручную, но вы могли бы использовать VBA, чтобы сделать это для вас.

  3. Добавьте ряд данных для ваших меток оси X. Это три столбца меток X в образце данных (значения X, значения Y и метки точек). Затем используйте надстройку Chart Labeler, чтобы добавить метки под точками.

  4. Добавьте ряд данных для ваших меток оси Y. Это три столбца метки Y в образце данных (значения X, значения Y и метки точек). Снова используйте надстройку Chart Labeler, чтобы добавить метки слева от точек.

  5. Наконец, вам нужно выполнить тонну форматирования, особенно очистить значения min/max для оси, удалить метки оси по умолчанию и т. Д.

  6. Чтобы сделать их динамическими, вам просто нужно создать именованный диапазон для каждого ряда данных (столбца), используя Count и Offset, чтобы они росли вместе с вашими данными. Затем используйте имена диапазонов вместо ссылок на ячейки ряда в формулах ряда диаграммы.

РЕДАКТИРОВАТЬ:

Для анализа рисков (или чего-либо еще, где вы делаете квадранты), установите ваши значения от отрицательного до положительного, и вы можете использовать обычную ось Excel для своих квадрантов. Кроме того, добавьте еще одну серию вспомогательных данных, чтобы определить свой высокий риск (или другие значения, которые вы хотите выделить), и вы можете отформатировать их, чтобы привлечь внимание.

Risk_Quad

Чтобы узнать больше о выделении значений на диаграмме рассеяния, ознакомьтесь с моим ответом на этот вопрос: выделите значения диаграммы рассеяния.

Источник
Другие вопросы по тегам