Как найти координату точки в экселе - Исправление недочетов и поиск решений вместе с Examum.ru

Scatter plots are one of the most frequently used charts for data analysis. There can be situations when you want to highlight a particular data point from the scatter chart that contains hundreds of data points. This seems to be a tedious task but it could be achieved very easily in excel. We will learn about how to find, highlight and label a data point in an excel Scatter plot.

Use Hover for Small Data

For table size less than equal to 10, this technique is quite efficient. For example, you are given a Pressure Vs Temperature plot. The number of rows in the table is 6.

Simply hover on the data points in the scatter chart. Here we can see that the point hovered has a pressure of 5 and a temperature of 12.

This method is not efficient when we have more than 2 columns in our table or the number of rows greater than 10.

Using Data Labels

To highlight the data points for more information we can use data labels. These help us to make data more understandable. This technique is efficient if the number of rows in the given data source is less than 20. It’s been observed that if the number of rows is high then the data labels make complete chaos. The data labels start overlapping and the observation starts fading. For example, given a data source of students, the number of hours they study, and the marks obtained. Make data labels as students’ names on the given scattered graph for better observations.

Example

Following are the steps:

Step 1: Select the chart and click on the plus button. Check the box data labels.

Step 2: The data labels appear. By default, the data labels are the y-coordinates.

Step 3: Right-click on any of the data labels. A drop-down appears. Click on the Format Data Labels… option.

Step 4: Format Data Labels dialogue box appears. Under the Label Options, check the box Value from Cells.

Step 5: Data Label Range dialogue-box appears.

Step 6: Select the range for which you want to add your custom data labels. For example, B4:B9. Click Ok.

Step 7: Uncheck the rest of the boxes under Label Options.

Step 8: Data labels as student name appears.

We can observe that the highest and second-highest marks obtained by students are Arushi and Gautam respectively.

Using Color Separation Technique

This is one of the most used techniques to highlight a data point in Excel. When we are having hundreds or thousands of data points in excel, the use of data labels is inefficient as it creates chaos and neatness starts fading from the scatter chart. To solve this problem, you can highlight a data point that you want to access. The chart we will create now will be a dynamic chart that finds and highlights a data point and changes according to the custom input. For example, you are given the same data source as above. Student name, study hours, and marks obtained. Highlight the cell you want as a custom input.

Following are the steps:

Step 1: Add a new table with three new columns in it. This table helps you input the cell you want to highlight.

Step 2: Enter the Student name you want to highlight in your scatter chart. For example, Arushi. Now, our task is to find the Hours studied and Marks Obtained from the student name entered. You can use the VLOOKUP function for this. The formula written for cell G4 is =VLOOKUP(F4, B4:D9, 2, FALSE). You will obtain the Hours of study for the corresponding student’s name.

Step 3: Also use the VLOOKUP function for adding the Marks obtained. The formula written for cell H4 is =VLOOKUP(F4, B4:D9, 3, FALSE). You will obtain the Marks obtained corresponding to the student’s name.

Step 4: Enter the Student’s name to be highlighted. For example, Arushi.

Step 5: Select the Chart. Go to the Chart Design tab. Click on the Select Data.

Step 6: Select the Data Source dialogue box that appears. Click on Add.

Step 7: Edit Series dialogue box appears. In the Series name, select cell F3. In the Series X value, select the cell G4 and in the Series Y value, select the cell H4. Click Ok.

Step 8: A series name Student is added in the Legend Entries. Click Ok.

Step 9: You can see that the data point is highlighted. To add the data label, repeat the same process done in method 2. Select the highlighted cell, click on the plus button. Check the box Data Labels.

Step 10: Now, right-click inside the highlighted cell. Click on Format Data Series…

Step 11: Format Data Series dialogue box appears. Under Series Options, click on the Marker. Here, you can customize the data point. You can change the color of the data point. You can also add a border color of the data point.

Step 12: Click on the Marker Options. Here you can customize, the type of data point and its size.

Step 13:To add the Student name as the data label. Right-click inside the data label, of the highlighted cell. Click on Format Data Labels.

Step 14: Format Data Labels dialogue-box appears. Under Label Options, check the box Value from Cells.

Step 15: Data Label Range dialogue-box appears. Select cell F4. Click Ok.

Step 16: Uncheck the rest of the boxes.

Step 17: Here the highlighted data point is Arushi.

Step 18: The chart you have created is dynamic. Changing the name to Gautam. Changes the highlighted cell.

Add position Lines in the highlighted data point

You may require adding the position of the highlighted point on the x-axis and the y-axis. Consider the above data set, with the highlighted student name as Arushi.

Following are the steps:

Step 1: Select the highlighted point in your scattered chart. Click on the plus icon. Under the Charts Elements, click on the Error Bars. Then, select the percentage.

Step 2: Select the highlighted data point.

Step 3: Right-click on it. A drop-down appears. You can see that the series name of the highlighted data point is Student. Click on it. A drop-down appears.

Step 4: Click on Series “Student” X Error Bars. This helps us add the X position line of the highlighted data point. Series “Student” Y Error Bars, adds the Y position line of the highlighted data point.

Step 5: This is the most important step. The X error line is selected. This line is so small that it’s not visible with your naked eyes. So, right-click on the light blue dot of the cell. Note, do not click on the highlighted data point.

Step 6: A drop-down appears. Click on the Format Error Bars…

Step 7: Format Error Bars dialogue box appears. In the Horizontal Error Bar, under Direction click on Minus. In the Error Amount, set the Percentage to 100%.

Step 8: Horizontal Position line appears.

Step 9: Repeat the same steps to add a Vertical Position line.

Источник

Содержание:

Точечная точка данных в точечной диаграмме Excel
2.1 — Создание раскрывающегося списка с названиями компаний (точек данных)
2.2 — Извлечение значений для выбранной компании
2.3 — Создание корректировщика

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

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

Как найти точку данных в точечной диаграмме Excel

Что-то вроде того, что показано ниже:

Этот метод позволяет мгновенно определить позицию компании на точечной диаграмме.

Точечная точка данных в точечной диаграмме Excel

Перейдите в Вставка -> Диаграммы -> Точечная диаграмма.

Щелкните пустую диаграмму и перейдите в Дизайн -> Выбрать данные.

В диалоговом окне «Выбор источника данных» нажмите «Добавить».

В диалоговом окне «Редактировать серию» выберите диапазон для оси X и оси Y.
Щелкните ОК.

Это создаст для вас простую диаграмму разброса. А теперь самое интересное — создание маркера для определения выбранной вами компании. Это состоит из 3 частей:

2.1 — Создание раскрывающегося списка с названиями компаний (точек данных)

Перейдите в ячейку, в которой вы хотите создать раскрывающийся список.
Перейдите в Data -> Data Validation.
В диалоговом окне «Проверка данных» выберите «Список» (в качестве критерия проверки), выберите весь диапазон, содержащий названия компаний (в данном случае список находится в B3: B22), и нажмите «ОК».

2.2 — Извлечение значений для выбранной компании

Выберите ячейку и укажите ее в раскрывающемся списке. Например, в этом случае раскрывающийся список находится в F3, а в B25 у меня есть формула = F3.
- Ячейка B25 будет меняться всякий раз, когда я изменяю раскрывающийся список.
В ячейке C25 используйте формулу ВПР, чтобы извлечь значение дохода (ось X) для компании в ячейке B25:
= ВПР (B25; $ B $ 3: $ D $ 22,2,0)
В ячейке D25 используйте формулу ВПР, чтобы извлечь значение прибыли (ось Y) для компании в ячейке B25:
= ВПР (B25; $ B $ 3: $ D $ 22,3,0)

2.3 — Создание корректировщика

Выберите уже созданную точечную диаграмму.
Перейдите в Дизайн -> Выбрать данные.
В диалоговом окне «Выбор источника данных» нажмите «Добавить».
Выберите ячейку C25 в качестве значения оси x.
Выберите ячейку D25 в качестве значения оси Y.
Точка данных будет иметь цвет и форму, отличные от других точек данных. Выберите эту точку данных, щелкните правой кнопкой мыши и выберите «Форматировать ряд данных».

В диалоговом окне «Форматирование ряда данных»
- Выберите параметр «Маркер» -> «Встроенный» -> «Тип» (выберите круглую форму и увеличьте размер до 11).
- Заливка маркера -> Без заливки.
- Цвет линии маркера -> Сплошная линия (красный или любой другой цвет).
- Стиль линии маркера -> Ширина (сделайте 1 или выше).

Вот и все. У тебя все готово. Теперь вы можете выбрать компанию, и она будет обведена и выделена. Круто, не правда ли?

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

Источник

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

Строим графики с точками пересечений

Имеются две функции, по которым нужно построить графики:

Выделяем диапазоны данных, на вкладке «Вставка» в группе «Диаграммы» подбираем нужный тип графика. Как:

Нужно найти точки пересечения графиков со значением Х, поэтому столбчатые, круговые, пузырьковые и т.п. диаграммы не выбираем. Это должны быть прямые линии.
Для поиска точек пересечения необходима ось Х. Не условная, на которой невозможно задать другое значение. Должна быть возможность выбирать промежуточные линии между периодами. Обычные графики не подходят. У них горизонтальная ось – общая для всех рядов. Периоды фиксированы. И манипулировать можно только с ними. Выберем точечную диаграмму с прямыми отрезками и маркерами.

Для данного типа диаграммы между основными периодами 0, 2, 4, 6 и т.д. можно использовать и промежуточные. Например, 2,5.

Находим точку пересечения графиков в Excel

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

Первый способ. Найти общие значения в рядах данных для указанных функций.

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

Значения Y одинаковые при Х = 4. Следовательно, точка пересечения двух графиков имеет координаты 4, 5.

Изменим график, добавив новые данные. Получим две пересекающиеся линии.

Второй способ. Применение для решения уравнений специального инструмента «Поиск решения». Кнопка вызова инструмента должна быть на вкладке «Данные». Если нет, нужно добавить из «Надстроек Excel».

Преобразуем уравнения таким образом, чтобы неизвестные были в одной части: y – 1,5 х = -1; y – х = 1. Далее для неизвестных х и y назначим ячейки в Excel. Перепишем уравнения, используя ссылки на эти ячейки.

Вызываем меню «Поиск решения» — заполняем условия, необходимые для решения уравнений.

Нажимаем «Выполнить» — инструмент предлагает решение уравнений.

Найденные значения для х и y совпадают с предыдущим решением с помощью составления рядов данных.

Точки пересечения для трех показателей

Существует три показателя, которые измерялись во времени.

По условию задачи показатель В имеет постоянную величину на протяжении всех периодов. Это некий норматив. Показатель А зависит от показателя С. Он то выше, то ниже норматива. Строим графики (точечную диаграмму с прямыми отрезками и маркерами).

Точки пересечения имеются только у показателей А и В. Но их точные координаты нужно еще определить. Усложним задачу – найдем точки пересечения показателя C с показателями А и В. То есть в какие временные периоды и при каких значениях показателя А линия показателя С пересекает линию норматива.

Точек у нас будет две. Их рассчитаем математическим путем. Сначала найдем точки пересечения показателя А с показателем В:

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

Теперь рассчитаем точки, найденных значений по оси Х с показателем С. Используем близкие формулы:

На основе новых данных построим точечные диаграммы на том же поле (где наши графики).

Получается такой рисунок:

Для большей информативности и эстетики восприятия добавим пунктирные линии. Их координаты:

Добавим подписи данных – значения показателя C, при которых он пересечет линию норматива.

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

Источник

537 / 28 / 4

Регистрация: 25.04.2011

Сообщений: 238

получение координат точки

13.11.2011, 11:00. Показов 42357. Ответов 8

Доброго всем времени суток! Есть такой вопрос: заданы точки — построена диаграмма с этими точками, у меня вопрос можно ли выполнить обратную задачу сначала поставь точку на диаграмму а затем получить ее координаты?

537 / 28 / 4

Регистрация: 25.04.2011

Сообщений: 238

13.11.2011, 11:01

[ТС]

Файл не прицепился

730 / 406 / 95

Регистрация: 19.12.2010

Сообщений: 756

13.11.2011, 19:41

Novichek =),для Excel 2007. Выделить график и подвести указатель к точке. Появится координаты точки.

Казанский

15137 / 6411 / 1730

Регистрация: 24.09.2011

Сообщений: 9,999

13.11.2011, 20:12

А как это можно поставить точку на диаграмму, не задав ее координаты? О_о
Правда, решал однажды задачу получения координат точек диаграммы, источник данных которой утрачен:

Комментарий модератора

Ссылка удалена администрацией

Тот файл разрабатывался в 2007. В 2000 макрос надо немного поправить:

Visual Basic

Sub GetXY()
Dim x, y, s As Series
On Error GoTo err_sel
Set s = Selection
On Error GoTo 0
x = s.XValues
y = s.Values
ActiveSheet.Cells(1, 1).Select
Cells(1, 1) = "Ряд:"
Cells(1, 2) = s.Name
Cells(2, 1).Resize(, UBound(x)) = x
Cells(3, 1).Resize(, UBound(y)) = y
Exit Sub
err_sel:
MsgBox "Выделение не является рядом на диаграмме", vbCritical
End Sub

и запускать через Alt+F8, а не через кнопку (в 2000 нажатие кнопки снимает выделение с диаграммы).

537 / 28 / 4

Регистрация: 25.04.2011

Сообщений: 238

14.11.2011, 12:58

[ТС]

Сообщение от Казанский

Правда, решал однажды задачу получения координат точек диаграммы, источник данных которой утрачен:

Прикольно, успел скачать Правда не совсем то… Поясню для чего нужны координаты точки: на диаграмме используется подложка в виде плана, допустим нужна координаты точки расположения дома №2 по улице Мира (красная точка). Поэтому сначала указать точку, а затем получить ее координаты… Есть мысль создания пользовательской формы, со счетчиком или полосой прокрутки для каждой из осей для подгонки точки (пока пытаюсь сделать чтобы шаг счетчика был дробным, а не единицей).

6170 / 935 / 309

Регистрация: 25.02.2011

Сообщений: 1,367

Записей в блоге: 1

14.11.2011, 13:37

Как вариант:
1. Тип диаграммы «Точечная».
2. Зафиксируйте оси (уберите галки автомаштаба в осях и укажите нужный маштаб).
3. Установите в ячейках предполагаемы координаты точек, совершенно не обязательно точно, можно любые.
4. Дважды щелкните на точку (не двойным кликом, а с паузой в пару секунд), чтобы выделился не весь ряд, а только одна точка.
5. Нажмите на точку, и удерживая ЛКМ перенесите ее в нужно место, координаты в ячейках автоматически пересчитаются.

537 / 28 / 4

Регистрация: 25.04.2011

Сообщений: 238

14.11.2011, 14:54

[ТС]

Сообщение от m-ch

2. Зафиксируйте оси (уберите галки автомаштаба в осях и укажите нужный маштаб).

Что-то не переносятся. Автомасштаб это в «параметрах оси»?

537 / 28 / 4

Регистрация: 25.04.2011

Сообщений: 238

14.11.2011, 15:25

[ТС]

Решение с другого сайта (я так понял что в 2007 перетаскивание точек убрали)…

537 / 28 / 4

Регистрация: 25.04.2011

Сообщений: 238

14.11.2011, 15:48

[ТС]

m-ch, Спасибо попробовал на 2003 Экселе, все работает!

Источник

Определение координат точки графика — на листе.

Grell

Дата: Среда, 15.03.2017, 09:33 |
Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 113

Репутация:
0
±

Замечаний:
60% ±

Excel 2007

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

В книге расположена точечная диаграмма.
Точка.5 — этого графика — на диаграмме имеет координаты (3;4)

Как макросом определить — какие координаты точка.5 имеет на листе?

К сообщению приложен файл:

2345.xls
(37.0 Kb)

Ответить

Roman777

Дата: Среда, 15.03.2017, 11:17 |
Сообщение № 2

Группа: Проверенные

Ранг: Ветеран

Сообщений: 980

Репутация:
127
±

Замечаний:
0% ±

Excel 2007, Excel 2013

Grell,
Например, вот так:
[vba]

Код

Sub Макрос2()
    Dim r As Series
    n& = 5 ‘ 5я точка
    ActiveSheet.ChartObjects(«Диаграмма 1»).Activate
    Set r = ActiveChart.SeriesCollection(1)
    MsgBox «y = » & r.Values(n) & Chr(13) & «x = » & r.XValues(n)
End Sub

[/vba]

Много чего не знаю!!!!

Сообщение отредактировал Roman777 — Среда, 15.03.2017, 20:56

Ответить

Grell

Дата: Среда, 15.03.2017, 18:37 |
Сообщение № 3

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 113

Репутация:
0
±

Замечаний:
60% ±

Excel 2007

Roman777, вы меня не совсем правильно поняли.
Определить координаты точки на графике — я могу просто посмотрев на таблицу с исходными данными.

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

Ответить

Roman777

Дата: Среда, 15.03.2017, 20:55 |
Сообщение № 4

Группа: Проверенные

Ранг: Ветеран

Сообщений: 980

Репутация:
127
±

Замечаний:
0% ±

Excel 2007, Excel 2013

Grell, Тогда так:
[vba]

Код

Sub Макрос3()
    Dim r As Point
    n& = 5 ‘ 5я точка
    ActiveSheet.ChartObjects(«Диаграмма 1»).Activate
    Set r = ActiveChart.SeriesCollection(1).Points(n)
    MsgBox «y = » & r.Top & Chr(13) & «x = » & r.Left
End Sub

[/vba]

Много чего не знаю!!!!

Сообщение отредактировал Roman777 — Среда, 15.03.2017, 20:56

Ответить

cerber412

Дата: Среда, 15.03.2017, 21:52 |
Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 131

Репутация:
0
±

Замечаний:
0% ±

Excel 2013

Roman777, похоже — это то, что нужно.
Спасибо большое.

Ответить

Источник