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

Question

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

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.

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

Answer 1

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

Вот как:

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

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

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

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

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

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

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

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

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

Более подробной информации о выделении значений в диаграмме рассеяния, проверить мой ответ на этот вопрос:Выделите значение диаграммы рассеяния