Создать двойную группу рассеяния

Question

Мне нужно создать "двойную сгруппированную диаграмму рассеяния", как на рисунке ниже, где я бы хотел изобразить 20 чисел каждый раз. Данные уже существуют в соответствующем формате (пример приведен на втором изображении ниже), но некоторые точки данных отсутствуют (#Null!).

Я долго думал о лучшем способе создания нужной мне схемы, но не смог найти удовлетворительного решения. У вас есть какие-нибудь умные идеи о простом способе создания диаграммы?

Ура, мил

Answer 1

Это был хороший вызов. Я сделал оба графика, которые вы ищете, но никогда не на одном графике.

Настройка данных

Первое, что вам нужно сделать, это удалить #NULL! значения из данных и заменить их пробелами.

Диапазон B3:F23

Затем вам нужно будет рассчитать медиану и квартили

Вот результаты (диапазон A27:F29):

С этими формулами:

75% quartile: =QUARTILE.INC(B4:B23,3)
Median:       =MEDIAN(B4:B23)
25% quartile: =QUARTILE.INC(B4:B23,1)

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

Вот результаты (диапазон A32:F24):

Куда:

"Col Blank" is equal to Quartile 25%
"Col 25-50%" is equal to Median less Quartile 25%
"Col 50%-75% is equal to Quartile 75% less Median

Теперь для точек нам нужно добавить значения x (может быть другой способ сделать это, но это быстрый и простой способ - диапазон I4:M23)

Теперь для создания графика

1. Выберите диапазон A32:F34 и вставьте столбчатую диаграмму с накоплением.
2. Отформатируйте Col Blank поле без заливки и границы
3. Отформатируйте другие 2 серии как светло-серую заливку с прозрачностью 75% и светло-серой рамкой
4. Отредактируйте метки горизонтальной оси, чтобы включить названия серий (W11, W21 и т.д.)

это даст вам коробки, теперь, чтобы добавить точки.

1. Добавьте новую серию в свою диаграмму (через Выбор данных) и выберите что-нибудь в качестве своего имени серии и значений серии (хотя некоторые ячейки с номером)
2. Щелкните правой кнопкой мыши новую серию на диаграмме и выберите « Change Series Chart Type
3. Выберите Scatter with markers only
4. Вернитесь в область « Select Data и отредактируйте новую серию, чтобы она выглядела как на следующем снимке экрана

Затем повторите это с другими сериями.

Конечный продукт

После небольшого дополнительного форматирования мы получаем это: