Найдите, пометьте и выделите определенную точку данных на диаграмме рассеяния Excel

В учебном пособии показано, как определить, выделить и пометить конкретную точку данных на точечной диаграмме, а также как определить ее положение на осях x и y.

На прошлой неделе мы рассмотрели, как сделать точечную диаграмму в Excel. Сегодня мы будем работать с отдельными точками данных. В ситуациях, когда на точечной диаграмме много точек, определить конкретную из них может быть настоящей проблемой. Профессиональные аналитики данных часто используют для этого сторонние надстройки, но есть быстрый и простой способ определить положение любой точки данных с помощью Excel. В нем есть несколько частей:

Исходные данные

Предположим, у вас есть два столбца связанных числовых данных, скажем, ежемесячные расходы на рекламу и продажи, и вы уже создали точечный график, показывающий корреляцию между этими данными:

Теперь вы хотите иметь возможность быстро найти точку данных за определенный месяц. Если бы у нас было меньше точек, мы могли бы просто пометить каждую точку по имени. Но на нашем точечном графике довольно много точек и метки только загромождают его. Итак, нам нужно найти способ найти, выделить и, при необходимости, пометить только конкретную точку данных.

Извлеките значения x и y для точки данных

Как вы знаете, на точечной диаграмме коррелированные переменные объединяются в одну точку данных. Это означает, что нам нужно получить x (Реклама) и у (Продано товаров) значения для интересующей точки данных. И вот как вы можете их извлечь:

1. Введите текстовую метку точки в отдельную ячейку. В нашем случае пусть это будет месяц май в ячейке E2. Важно, чтобы вы вводили метку точно так, как она отображается в исходной таблице.
2. В F2 вставьте следующую формулу ВПР, чтобы получить количество проданных товаров за целевой месяц:

   =ВПР($E$2,$A$2:$C$13,2,ЛОЖЬ)

3. В G2 подсчитайте стоимость рекламы за целевой месяц, используя следующую формулу:

   =ВПР($E$2,$A$2:$C$13,3,ЛОЖЬ)

   На этом этапе ваши данные должны выглядеть примерно так:
   

Добавьте новый ряд данных для точки данных

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

1. Щелкните правой кнопкой мыши любую ось на диаграмме и выберите «Выбрать данные…».
   
2. в Выберите источник данных диалоговое окно, нажмите кнопку «Добавить».
   
3. в Редактировать серию окно, выполните следующие действия:
   • Введите осмысленное имя в поле Название серии коробка, например Целевой месяц.
   • Как Значение серии X, выберите независимую переменную для вашей точки данных. В данном примере это F2 (Реклама).
   • Как Значение серии Yвыберите зависимый. В нашем случае это G2 (Проданные товары).
4. Когда закончите, нажмите ХОРОШО.

В результате среди существующих точек данных появится точка данных другого цвета (в нашем случае оранжевого), и это та точка, которую вы ищете:

Конечно, поскольку ряды диаграмм обновляются автоматически, выделенная точка изменится, как только вы введете другое имя в поле. Целевой месяц ячейка (Е2).

Настройте целевую точку данных

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

Изменение внешнего вида точки данных

Для начала поэкспериментируем с цветами. Выберите эту выделенную точку данных, щелкните ее правой кнопкой мыши и выберите «Формат ряда данных…» в контекстном меню. При этом убедитесь, что выбрана только одна точка данных:

О формате Серия данных панель, перейти к Заполнение и линия > Маркер и выберите любой цвет для маркера Наполнять а также Граница. Например:

В некоторых ситуациях использование другого цвета для целевой точки данных может оказаться неуместным, поэтому вы можете закрасить ее тем же цветом, что и остальные точки, а затем выделить ее, применив некоторые другие параметры производителя. Например, вот эти:

Добавьте метку точки данных

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

1. Нажмите на выделенную точку данных, чтобы выбрать ее.
2. Нажмите на Элементы диаграммы кнопка.
3. Выберите поле Метки данных и выберите, где разместить метку.
   
4. По умолчанию Excel показывает одно числовое значение для метки, в нашем случае значение y. Чтобы отобразить значения x и y, щелкните метку правой кнопкой мыши, щелкните Форматировать метки данных…выберите Х значение а также значение Y ящики и установите Разделитель на ваш выбор:
   

Пометьте точку данных по имени

В дополнение к значениям x и y или вместо них на этикетке можно указать название месяца. Для этого установите флажок «Значение из ячейки» на Форматировать метки данных нажмите на Выберите диапазон… и выберите соответствующую ячейку на листе, в нашем случае E2:

Если вы хотите, чтобы на этикетке отображалось только название месяца, снимите флажок Х значение а также Значение Y коробки.

В результате вы получите следующую диаграмму рассеяния с выделенной точкой данных и пометкой по имени:

Определите положение точки данных по осям x и y

Для лучшей читаемости вы можете отметить положение важной для вас точки данных на осях x и y. Вот что вам нужно сделать:

1. Выберите целевую точку данных на диаграмме.
2. Нажмите на Элементы диаграммы кнопка > Планки ошибок > Процент.
   
3. Щелкните правой кнопкой мыши на горизонтальной полосе ошибок и выберите Форматирование полос ошибок… из всплывающего меню.
   
4. На Панель «Панель ошибок форматирования»иди в Параметры панели ошибок вкладку и изменить Направление в Минус и Процент до 100:
   
5. Нажмите на вертикальную полосу ошибок и выполните ту же настройку.

   В результате горизонтальная и вертикальная линии будут простираться от выделенной точки до осей y и x соответственно:
   

6. Наконец, вы можете изменить цвет и стиль полос погрешностей, чтобы они лучше соответствовали цветам вашей диаграммы. Для этого переключитесь на Заполнение и линия вкладка Планки ошибок формата панель и выберите нужный Цвет а также Тип тире для текущей выбранной полосы ошибок (вертикальной или горизонтальной). Затем сделайте то же самое для другой полосы ошибок:
   

И вот окончательная версия нашего графика рассеивания с выделенной целевой точкой данных, помеченной и расположенной на осях:

Самое лучшее в этом то, что вы должны выполнять эти настройки только один раз. Из-за динамического характера диаграмм Excel выделенная точка изменится автоматически, как только вы введете другое значение в целевую ячейку (E2 в нашем примере):

Показать положение средней или контрольной точки

Тот же метод можно использовать для выделения средней, эталонной, наименьшей (минимум) или наивысшей (максимум) точки на диаграмме рассеивания.

Например, чтобы выделить среднюю точку, вы вычисляете среднее значение значений x и y с помощью функции СРЗНАЧ, а затем добавляете эти значения в качестве нового ряда данных точно так же, как мы это делали для целевого месяца. В результате у вас будет точечный график с помеченной и выделенной средней точкой:

Вот как вы можете определить и выделить определенную точку данных на диаграмме рассеяния. Чтобы поближе ознакомиться с нашими примерами, вы можете скачать наш образец рабочей тетради ниже. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе.

Практическая рабочая тетрадь

Точечная диаграмма Excel — примеры (файл .xlsx)

Вас также могут заинтересовать