Как пометить точки на графике в Excel

Обновлено: 20.11.2024

На рисунке выше показана диаграмма с пользовательскими метками данных, которые связаны с определенными значениями ячеек.

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

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

В статье от марта 2013 г. я продемонстрировал, как создавать настраиваемые метки данных на диаграмме.

К сожалению, этот метод работает только для гистограмм и гистограмм.

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

Что на этой странице

К счастью, сотрудники Microsoft услышали наши молитвы.

Они внедрили в Excel 2013 функцию, которая позволяет назначать ячейку метке точки данных диаграммы a на точечной диаграмме x y.

В этой статье я покажу, как это сделать для Excel 2013 и более поздних версий, а также обходной путь для более ранних версий.

1.1 Как применять настраиваемые метки данных в Excel 2013 и более поздних версиях

В этом примере диаграммы показано расстояние между планетами в нашей Солнечной системе в виде точечной диаграммы x y.

Первые три шага расскажут вам, как построить точечную диаграмму.

Выберите диапазон ячеек B3:C11.
Перейти на вкладку "Вставка"
Нажмите левой кнопкой мыши на кнопку "разброс".
Нажмите правую кнопку мыши на точку диаграммы и нажмите левую кнопку мыши на "Добавить метки данных"
Нажмите правой кнопкой мыши на любую точку еще раз и нажмите левой кнопкой мыши на "Форматировать метки данных"
Справа появится новое окно, отмените выбор значений X и Y.
Включить "Значение из ячеек"
Выберите диапазон ячеек D3:D11.
Нажмите левой кнопкой мыши на OK

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

1.1 Видео

В следующем видео показано, как добавить метки данных в точечную диаграмму X Y [Excel 2013 и более поздние версии].

Подробнее

1.2 Как добавить формы меток данных

Нажмите мышью на элементе "Изменить формы меток данных".
Выберите фигуру.

1.3 Как изменить расположение меток данных

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

Получить файл Excel

Пользовательские метки данных в a-x-y-scatter-chartv3.xlsx

Подробнее

2. Обходной путь для более ранних версий Excel

Этот обходной путь предназначен для Excel 2010 и 2007. Он лучше всего подходит для небольшого количества точек данных диаграммы.

Дважды нажмите левую кнопку мыши на ярлыке, чтобы выбрать его.
Нажмите левую кнопку мыши в строке формул.
Тип =
Используйте мышь, чтобы нажать левой кнопкой мыши на ячейку, содержащую значение, которое вы хотите использовать.
Возможно, строка формул изменится на =Sheet1!$D$3
Повторите шаги 1–5 с оставшимися метками данных.

Измените значение в ячейке D3 и посмотрите, как мгновенно изменится метка данных на диаграмме.

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

Если на диаграмме много точек данных, этот метод быстро становится утомительным и занимает много времени.

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

Подробнее

2.1 Применение пользовательских меток данных (макрос VBA)

Этот макрос добавляет ссылку на ячейку к каждой метке данных, после чего значение в указанной ячейке связывается с меткой. Если вы измените значение в ячейке, значение метки также изменится.

Подробнее

2.2 Где разместить код?

Копировать макрос (CTRL + C)
Перейдите в редактор VB (Alt + F11)
Нажмите левой кнопкой мыши на "Вставить" в верхнем меню.
Нажмите левой кнопкой мыши на "Модуль", чтобы вставить модуль кода в книгу.
Вставьте код в модуль. (CTRL + V)
Вернуться в Excel.
Сохраните книгу как книгу с поддержкой макросов (файл *.xlsm).
Если вы этого не сделаете, макрос исчезнет при следующем открытии той же книги.

Подробнее

2.3 Как использовать макрос

Получить файл Excel

Link-data-labels-to-data-points-in-an-x-y-scatter-chart.xlsm

В этом руководстве показано, как добавить горизонтальную/вертикальную линию на диаграмму. Excel позволяет комбинировать два типа […]

В этой статье показано, как вставлять изображения на ось диаграммы. На рисунке выше показана столбчатая диаграмма со страной […]

Вопрос. Как создать диаграмму, которая динамически добавляет значения по мере их ввода на листе? Ответ: […]

Сегодня я покажу вам, как создать динамическую диаграмму Ганта в Excel 2007. Диаграмма Ганта помогает […]

В этой статье демонстрируются макросы, которые автоматически изменяют цвета столбцов диаграммы в зависимости от соответствующей ячейки, первый пример […]

Еженедельный блог EMAIL

[newsletter_signup_form Я Оскар и здесь, чтобы помочь вам.

Не стесняйтесь оставлять комментарии и задавать вопросы по Excel.

Подпишитесь на мою рассылку, чтобы не пропустить новые статьи блога.

Статьи по теме

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

В этой статье описывается, как создать карту в Excel, карта представляет собой точечную диаграмму x y с […]

На рисунке выше показано следующее уравнение x^3+3*x^2-3, нанесенное на точечную диаграмму x y. Вот инструкции о том, как […]

Приведенная выше диаграмма построена с использованием функции НОРМ.РАСП и называется диаграммой нормального распределения или кривой Белла. Эта кривая часто […]

Вопрос. Как создать диаграмму, которая динамически добавляет значения по мере их ввода на листе? Ответ: […]

Недавно я обнаружил интересный способ использования пользовательской функции в функции HYPERLINK. Джордан на выбор […]

В этой статье я покажу, как быстро изменить диапазон данных диаграммы, используя поле со списком (раскрывающийся список). Вышеуказанное […]

В этой статье описывается, как создать интерактивную диаграмму, пользователь может нажать левой кнопкой мыши на кнопку […]

В этой статье описывается, как создать карту в Excel, карта представляет собой точечную диаграмму x y с […]

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

11 ответов на вопрос «Улучшите свою точечную диаграмму X Y с помощью настраиваемых меток данных»

Похоже, это не работает, по крайней мере, не с Excel для Mac. Параметр «Значение из ячеек» не указан в параметрах метки данных. Доступны только следующие параметры: Имя серии, Значение X, Значение Y и Размер пузырька. В более общем плане Microsoft постоянно отставала в выпуске Office для Mac от версии для Windows. Варианты построения диаграмм немного улучшились, но UX по-прежнему ужасно неадекватен и запутан.

Благодарим вас за обходной путь Excel 2010 для настраиваемых меток данных в точечных диаграммах XY. В основном это работает для меня, пока я не вставлю новую строку на лист, связанный с диаграммой. Это нарушает абсолютные ссылки на метки данных после вставленной строки, и Excel не позволяет мне изменить метки данных на относительные ссылки. Вы знаете обходной путь для этого?

Попробуйте создать именованный диапазон.

¨Значение из ячеек¨ не отображается в меню боковой панели «Формат меток данных»:/ Есть идеи, как это исправить? Я бы попробовал обходные пути, но предпочел бы иметь простой доступный способ.

Спасибо за отличную статью :)

Какую версию Excel вы используете?

Это не работает в версии для Mac. Я могу перейти к «Форматировать метки данных», и есть варианты для значения X, значения Y, имени серии, Показать линии выноски и ключ легенды, но не «Значение из ячейки». Как это сделать на Mac?

Кто-то написал этот комментарий в блоге Peltier:

Дело в том, что я работаю на Mac и, как вы уже знаете, «Значения из ячеек» не существует. Мне пришлось открыть файл на компьютере с Windows, я выполнил задание, снова открыл его на своем Mac, и все заработало очень хорошо.

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

На последнем шаге цикла for:
SerPo(i).DataLabel.Formula = " !" & Rng.Cells(i).Address(ReferenceStyle:=xlR1C1)

Это работает только с ДАННЫМИ на том же листе, что и точечный график, который будет использоваться в качестве меток.
У меня есть ДАННЫЕ на другом листе, а не на том же активном листе, что и точечный график.
Как бы вы изменили это, чтобы ДАННЫЕ на другом листе можно было использовать в качестве меток данных?

Я пытаюсь выяснить, как заставить ярлыки отображаться в таблицах Google, Excel или Numbers.

У меня есть информация, которая выглядит следующим образом

Тогда я бы хотел, чтобы мой окончательный график выглядел так.

Например, почему я не могу обозначить каждую из этих точек своим именем? Кажется, я могу только пометить value, например, (c) покажет 4

Является ли единственным решением D3?

5 ответов 5

Ну, я не думал, что это возможно, пока не пошел и не проверил. В какой-то предыдущей версии Excel я не мог этого сделать. В настоящее время я использую Excel 2013.

Вот что вы хотите сделать на точечной диаграмме:

щелкните правой кнопкой мыши точку данных

выберите «Форматировать метки данных» (обратите внимание, что вам, возможно, придется сначала добавить метки данных)

Чтобы раскрасить этикетки по отдельности, выполните следующие действия:

выберите ярлык. При первом выборе все ярлыки серии должны быть обведены рамкой, как показано на графике выше.
Выберите отдельный ярлык, который хотите отредактировать. Только выбранный вами ярлык должен иметь рамку, как показано на графике ниже.
Справа, как показано ниже, выберите "НАСТРОЙКИ ТЕКСТА".
При необходимости разверните категорию "ЗАПОЛНЕНИЕ ТЕКСТА".
Вторым снизу в списке категорий является "ЦВЕТ". Выберите нужный цвет из палитры.

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

Я только что попросил ИТ предоставить мне Microsoft Office 2016 для Mac, но, к сожалению, в Excel для Mac нет этой функции :(. Спасибо, однако

Это превосходно. Вся официальная документация, которую я могу найти от Microsoft, объясняет, как это сделать с помощью неуклюжих макросов. Это идеальное решение.

Я использую Excel 365 (сборка 1805) в Windows, и у меня нет опции "значение из ячеек" :( по-другому?

@SiMon Не то, чтобы я знал об этом. Если у вас небольшой набор данных, у вас может быть вариант в другом доступном вам ответе.

Ни один из них мне не помог. Я использую Microsoft 360 на Mac. Я обнаружил, что DID работает: этот обходной путь предназначен для Excel 2010 и 2007, он лучше всего подходит для небольшого количества точек данных диаграммы.

Дважды щелкните ярлык, чтобы выбрать его. Щелкните в строке формул. Введите = используйте мышь, чтобы щелкнуть ячейку, содержащую значение, которое вы хотите использовать. Строка формул изменится, возможно, на =Sheet1!$D$3

Повторите шаги с 1 по 5 с оставшимися метками данных.

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

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

Короче говоря, это нормально работает для небольшого набора данных или просто ключевых точек из набора данных.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Назовите точку данных по имени

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

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

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

Определить положение точки данных по осям X и Y

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

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

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

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

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

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

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

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

14 комментариев к «Как найти, выделить и пометить точку данных в точечной диаграмме Excel»

Вы автоматически предполагаете, что на диаграмме будут отображаться все строки данных, когда вы нажимаете на диаграмму и пытаетесь найти значения, используя символы Series Points x на диаграмме. У меня есть 194 строки данных, соответствующих еженедельным результатам, но линейная диаграмма показывает только 5 июля 2021 года, тогда следующая точка данных на линейном графике — 2 ноября 2021 года? Почему на графике не отображаются все НЕДЕЛИ в виде серийных баллов, чтобы я мог легко увидеть, какими были недельные цены, когда я навел указатель мыши на линейный график?

Можно ли изменить размер точек данных на графике?
например. сделать их больше размера по умолчанию

При выборе одной точки на диаграмме метки данных | Параметры, когда вы показываете «Параметры ярлыка», а затем «Ярлык содержит». В разделе «Ярлык содержит» я вижу 5 вещей, а не 6. По сравнению с вашим снимком экрана отсутствует элемент «Значение из ячеек». Я могу установить флажки в разделе «Ярлык содержит»: «Имя серии», «Значение X», «Значение Y», «Показать линии выноски» и «Ключ легенды».

Знаете ли вы, что в недавнем выпуске Excel вариант «Из ячейки» был исключен, или знаете ли вы какую-либо другую причину, по которой у меня может отсутствовать «Из ячейки»? Я хотел бы опубликовать снимок экрана, но я описал его как можно лучше.

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

В точечных (пузырьковых) диаграммах часто есть группы, разделенные разрывами числовых значений по оси x. Было бы полезно иметь вертикальную линию, разделяющую эти группы, которая идет от оси X к верхней части диаграммы. Таким образом, горизонтальная ось может быть помечена и обозначена более четко

Это легко сделать с помощью линии выбора в объектах и просто провести вертикальную линию сверху вниз между группами. Но линия не движется вместе с диаграммой.

Кажется, приведенные здесь примеры не подходят для этой ситуации.

Спасибо. Я, наверное, не лучший человек в Excel. Ха!! Я могу ошибаться в своей оценке. Майк

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

Используемые данные: продажа мороженого разных вкусов в магазине 1 и магазине 2.

Шаг 1. Выберите данные, ВСТАВЬТЕ -> Рекомендуемые диаграммы -> Точечная диаграмма (3-я диаграмма будет точечной)

Пусть построенная точечная диаграмма будет

Шаг 2. Нажмите на символ + и добавьте метки данных, щелкнув его, как показано ниже

Шаг 3. Теперь нам нужно добавить названия вкусов к ярлыку. Теперь щелкните правой кнопкой мыши на метке и выберите «Форматировать метки данных». В разделе ВАРИАНТЫ МЕТКИ выберите значение из ячеек, как показано ниже.

Шаг 4. Откроется всплывающее окно, в котором выберите диапазон меток данных, в нашем случае от A2 до A7, и нажмите «ОК», как показано ниже.

Шаг 5. Теперь на этикетках появятся вкусы мороженого. Нажмите «Значение X» и «Значение Y» в разделе «ОПЦИИ МЕТКИ».Таким образом, результирующая диаграмма даст вам график рассеяния с метками вкусов и метками значений X и значений Y (координаты x, y), как показано ниже

Шаг 6. Добавьте подходящие названия и метки осей, чтобы итоговая диаграмма была

Пример: продажа ароматизатора vennila в магазине 1 составляет 45 единиц, а в магазине 2 — 80 единиц.

Читайте также:

Вкус	Магазин 1	Магазин 2
Веннила	45	80
Шоколад	60	35
Клубника	65	45
Карамель	34	36
кулфи	10	62
Бекон	45	34