Как опустить перпендикуляр на графике в Excel

Обновлено: 21.11.2024

Примечание. Другие языки веб-сайта переведены с помощью Google. Назад на английский

Как добавить линии перетаскивания на линейную диаграмму Excel?

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

Вкладка Office Включите редактирование и просмотр с помощью вкладок в Office и значительно упростите свою работу.

Kutools for Excel решит большинство ваших проблем и повысит вашу производительность на 80 %

  • Повторное использование всего: добавляйте наиболее часто используемые или сложные формулы, диаграммы и другие объекты в избранное и быстро используйте их повторно в будущем.
  • Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
  • Инструменты объединения: несколько книг и листов в одну; Объединение нескольких ячеек/строк/столбцов без потери данных; Объединить повторяющиеся строки и суммировать.
  • Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Одна рабочая книга для нескольких файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
  • Вставить, пропуская скрытые/отфильтрованные строки; Подсчет и сумма по цвету фона; Массовая рассылка персонализированных электронных писем нескольким получателям.
  • Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировка по неделям, дням, частоте и т. д.; Фильтр по полужирному шрифту, формулам, комментариям.
  • Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Сэкономьте 50 % своего времени и сократите количество кликов мышью каждый день!

Добавление перетаскиваемых линий на линейную диаграмму Excel

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

Нажмите на диаграмму, чтобы активировать инструменты для работы с диаграммами, а затем нажмите «Дизайн» > «Добавить элемент диаграммы» > «Линии» > «Перетащите линии». Смотрите скриншот:

Примечание. Если вы используете Excel 2010 или более ранние версии, нажмите на диаграмму, чтобы активировать инструменты для работы с диаграммами, а затем нажмите Макет > Линии > Перетащите линии.

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

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

В любом случае, перейдем к дополнительным информационным панелям Excel

Итак, у меня есть хороший друг, который был здесь в начале моего блога по шаблону информационной панели Excel, и его зовут Дон. Дон — пенсионер, живущий на Среднем Западе США, и ему нравится отслеживать события с помощью Excel, а затем ему нравится просматривать свои данные визуально. Дон также любит использовать Excel, чтобы отслеживать все, что связано с сериалом «Доктор Кто». Но это для другого поста.

Недавно Дон прислал мне электронное письмо и спросил, могу ли я помочь ему добавить вертикальную линию на диаграмму в Excel. Диаграмма Excel представляла собой линейную диаграмму. Поэтому он прислал мне образец диаграммы (см. линейную диаграмму Excel, изображенную ниже), и он хотел добавить 2 вертикальные линии. Один выше вторника и один выше четверга. Ниже приведены пошаговые инструкции для каждого из трех методов, а ссылка на видео находится в конце поста.

Вот как должна выглядеть окончательная диаграмма:

Итак, в этом посте я покажу вам 3 способа добавления вертикальной линии на линейную диаграмму Excel.

Итак, мы начнем с этих данных:

1) Добавьте вертикальную линию с помощью фигур Excel

Первый и, как правило, самый простой способ добавить вертикальную линию в линейную диаграмму Excel — просто нарисовать форму линии Excel поверх линейной диаграммы Excel.

A) Создайте линейную диаграмму Excel

B) Выберите «Вставить ленту»

C) Выберите фигуры в группе иллюстраций

D) Выберите линию без стрелок в разделе линии

E) Нарисуйте свою линию поверх уже созданной линейной диаграммы Excel.

F) Выбрав линию, нажмите CTRL+1, чтобы отформатировать фигуру Excel

G) Измените ширину на 3pt

H) Измените цвет линии на красный

Это действительно самый простой способ добавить линию, ОДНАКО, линия не очень динамична и может стать «ВНЕ ЛИНИИ», если вы будете добавлять новые точки данных на линейную диаграмму. Таким образом, вам придется постоянно корректировать линию по мере корректировки диаграммы.

2) Добавить дополнительную серию столбцов

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

A) Добавьте дополнительный столбец данных, который создаст дополнительный ряд строк в вашей диаграмме Excel. Вы увидите, что я добавил точку данных для каждого дня со значением 12. Это на 20% выше реальных данных, потому что я хочу, чтобы вертикальные линии проходили над данными и достигали максимального вертикального положения вертикальной оси. (Обратите внимание, что вы установите максимум этой вертикальной оси, как описано ниже.)

B) Выделите новый диапазон данных и создайте линейную диаграмму. У вас будет 2 горизонтальные линии, и ваш график будет выглядеть как на картинке ниже. Обратите внимание, вы можете видеть красную горизонтальную линию (под названием «Вертикальные линии»), потому что мы указали значение для каждого дня. Если бы мы поставили только 12 для вторника и четверга, мы бы не увидели линию, потому что это были бы две точки данных, которые не были смежными, поэтому это не отображало бы линию, которую вы могли бы легко увидеть и выбрать.

C) Щелкните правой кнопкой мыши серию вертикальных линий. В данном случае Red Data Series. Затем во всплывающем меню выберите «Изменить тип диаграммы серии…»

D) Измените тип диаграммы Excel на двухмерную столбчатую диаграмму с кластерами.

Тогда это будет выглядеть так:

E) Мы очень близки. Давайте изменим несколько вещей. Во-первых, мы хотели иметь только вертикальные линии во вторник и четверг. Итак, давайте удалим значения данных вертикальных линий за все дни, кроме вторника и среды. Теперь ваша диаграмма будет выглядеть так:

F) Теперь давайте сделаем столбцы более похожими на строки, изменив их форматирование. Сначала щелкните правой кнопкой мыши любой столбец, а затем выберите «Формат ряда данных…» во всплывающем меню.

а затем переместите ползунок "Ширина зазора" вправо в сторону значения "Большой зазор". Чем дальше вправо вы переместите его, тем меньше будет казаться столбец. Он может стать очень маленьким и выглядеть почти как линия.

На линейной диаграмме с очень небольшим количеством горизонтальных категорий столбец не станет намного тоньше, чем это:

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

G) Хорошо, это последний шаг для простых динамических вертикальных линий. Щелкните правой кнопкой мыши основную вертикальную ось и выберите «Формат оси…» во всплывающем меню Excel.

Затем в диалоговом окне "Формат оси" измените максимальное значение параметров оси на 12 (равное количеству, указанному в столбце вертикальных линий).

Это создаст видимость линий, которые идут от нижней части вертикальной оси до самого верха. Вот ваш окончательный график.

Выглядит хорошо для меня. Как вы думаете? Дайте мне знать в комментариях.

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

a) Добавьте еще один столбец данных для каждой строки. На картинке ниже я сделал набор данных второй вертикальной линии = 10 для каждого значения, чтобы вы могли различать каждую линию по отдельности. Если бы я установил их обоих на 12, они бы перекрывались, и вы не смогли бы так же легко отличить их друг от друга. Вы все уже можете видеть, что две линии имеют разные цвета. Ваш набор данных и новая линейная диаграмма будут выглядеть следующим образом:

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

3) Добавьте вертикальную линию с помощью точечных линий XY

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

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

A) Создайте линейную диаграмму Excel с исходным набором данных, которая выглядит следующим образом:

B) Теперь создайте 2 набора данных в любом месте электронной таблицы Excel, которые выглядят следующим образом:

C) Теперь я рекомендую вам выделить и КОПИРОВАТЬ данные вторника из правого верхнего угла вниз на одну ячейку до 12. Ваш диапазон будет выглядеть следующим образом:

затем выберите свою диаграмму и выберите «Специальная вставка». Вы увидите следующее диалоговое окно:

Затем нажмите OK, и теперь ваша диаграмма будет выглядеть так:

D) Теперь вы можете сказать, что это НЕ вертикальная линия. И вы правы. Однако вот как мы начинаем преобразование в точечную диаграмму XY. Сначала щелкните правой кнопкой мыши серию вертикальных линий. В данном случае Red Data Series. Затем во всплывающем меню выберите «Изменить тип диаграммы серии…»

а затем измените тип диаграммы на XY (точечная) — точечная с прямыми линиями:

Ваша результирующая диаграмма теперь будет выглядеть так. Выглядит не лучше, но мы теперь очень-очень близки

E) Теперь выберите свою диаграмму, а затем на ленте дизайна нажмите кнопку «Выбрать данные» в группе данных.

и вы увидите это диалоговое окно. Затем вам нужно будет выбрать «Series2» в области Legend Entries (Series). затем нажмите «Изменить»

Теперь, когда вы нажимаете «Изменить», вы видите диалоговое окно «Редактировать серию». Дайте вашей серии название, чтобы вы могли легко узнать его в будущем. Затем измените значения серии X на X для вторника и измените значения серии Y на Y для вторника.

А затем нажмите OK, но не закрывайте диалоговое окно «Выбор источника данных», так как оно понадобится вам на следующем шаге. Теперь, глядя на диаграмму, вы увидите вертикальную красную линию на линейной диаграмме Excel.

F) Теперь мы создали только одну линию, поэтому давайте создадим вторую линию. Для этого вам нужно ДОБАВИТЬ новую серию в диалоговом окне «Выбор источника данных».

В диалоговом окне "Редактировать серию" добавьте данные вертикальной линии за четверг, добавив X и Y, как показано ниже:

Тогда продолжайте и нажмите OK в диалоговом окне «Выбор источника данных». ВАЖНО: если вы нажмете «Отмена» на этом шаге, вам придется начать редактирование ОБЕИХ рядов данных XY заново, поскольку Excel решит, что вы хотите начать редактирование всех рядов для диаграммы с тех пор, как вы заявили об их редактировании.

Теперь ваша диаграмма будет выглядеть так:

Это когда вы говорите Стив, почему данные не совпадают со вторником и четвергом?

Ну, это потому, что нам нужно сделать последний шаг! То есть просто удалить вторичную горизонтальную ось и вторичную вертикальную ось.

G) Нажмите на горизонтальную вторичную горизонтальную ось и нажмите клавишу удаления на клавиатуре, затем выберите вторичную вертикальную ось и нажмите клавишу удаления. И бум взрывается, и вы получаете вот такую ​​диаграмму:

H) Хорошо, вот последний шаг для настоящих вертикальных линий. Щелкните правой кнопкой мыши основную вертикальную ось и выберите «Формат оси…» во всплывающем меню Excel.

Затем в диалоговом окне «Формат оси» вы должны изменить максимальное значение параметров оси на 12 (равное количеству, которое у вас есть в данных вертикальных линий).

Это создаст видимость линий, которые идут от нижней части вертикальной оси до самого верха. Вот ваш окончательный график:

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

ВИДЕО-демонстрация

Дополнительное руководство, упомянутое в видео:

Я хочу еще раз поблагодарить всех и каждого за то, что вы подписались на мой блог и сделали его таким успешным. СПАСИБО!

Зарегистрированный пользователь Регистрация Дата 06.09.2011 Местоположение Англия MS-Off Ver Excel 2003 Сообщений 40

Автоматическое считывание перпендикулярного значения на графике

Я столкнулся со следующей проблемой. У меня есть график, скажем, y vs x, который нелинейный, и в основном я хочу знать значение x для определенного значения y, не считывая его путем проверки.Я не хочу строить график, чтобы на самом деле сделать это. Я знаю, что могу использовать ЛИНЕЙН для получения полиномиальных коэффициентов и получить их таким образом, но я не хочу строить полином. Мне просто нужен график с линиями, соединяющими точки, как они есть, и, по сути, я хочу получить значения под прямым углом на графике только с помощью формулы.

Это кажется довольно простым, поэтому я предполагаю, что в Excel есть какая-то функция для этого, но я не могу ее найти. Кто-нибудь может мне помочь?

Гуру форума Дата регистрации 13 апреля 2005 г. Местоположение Северная Америка MS-Off Ver 2002/XP и 2007 Сообщений 14 047

Я знаю, что могу использовать ЛИНЕЙН для получения полиномиальных коэффициентов, но я не хочу строить полином. Мне просто нужен график с линиями, соединяющими точки, как они есть

Если я правильно понимаю, мне кажется, что вы хотите использовать линейную интерполяцию для получения значений, верно? Если так.

Это кажется довольно простым, поэтому я предполагаю, что в Excel есть какая-то функция для этого, но я не могу ее найти.

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

Гуру форума Дата регистрации 20.06.2007 Местоположение The Great State of Texas MS-Off Ver 2003, 2010 Сообщений 40 677

Зарегистрированный пользователь Регистрация Дата 06.09.2011 Местоположение Англия MS-Off Ver Excel 2003 Сообщений 40

Эээ… извините, ребята, ответ действительно очевиден… не знаю, почему мне это не пришло в голову, лол.

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

В современных версиях Excel 2013, Excel 2016 и Excel 2019 вы можете несколькими щелчками мыши добавить на диаграмму горизонтальную линию, будь то средняя линия, целевая линия, контрольный показатель, базовый уровень или что-то еще. Но до сих пор нет простого способа нарисовать вертикальную линию в графике Excel. Однако «нет легкого пути» вовсе не означает никакого пути. Нам просто нужно немного подумать!

Как добавить вертикальную линию на точечную диаграмму

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

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

Чтобы добавить вертикальную линию в точечную диаграмму Excel, вам нужно сделать следующее:

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

Примечание. Если вы хотите нарисовать линию в какой-либо существующей точке данных, извлеките ее значения x и y, как описано в этом совете: Получите значения x и y для определенной точки данных на точечной диаграмме.

  • В поле Имя серии введите название серии вертикальных линий, например Среднее.
  • В поле Значение ряда X выберите независимое значение x для интересующей точки данных. В данном примере это E2 (реклама в среднем).
  • В поле Значение серии Y выберите зависимое значение для той же точки данных. В нашем случае это F2 (средний объем продаж).
  • По завершении дважды нажмите ОК, чтобы открыть оба диалоговых окна.

Примечание. Обязательно сначала удалите существующее содержимое полей Серийные значения — обычно это массив из одного элемента, например =. В противном случае выбранная ячейка x и/или y будет добавлена ​​к существующему массиву, что приведет к ошибке.

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

Готово! На вашем точечном графике нанесена вертикальная линия. В зависимости от ваших настроек на шагах 8 и 9, это будет выглядеть как одно из следующих изображений:

Как добавить вертикальную линию в гистограмму Excel

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

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

  1. Выберите данные и создайте гистограмму (вкладка Вставка >Диаграммы группа >Вставить столбец или гистограмму >2- Панель D).
  2. В некоторых пустых ячейках настройте данные для вертикальной линии, как показано ниже.

X Y
Значение/формула 0
Значение/формула 1

Поскольку мы собираемся рисовать вертикальную среднюю линию, мы вычисляем значение X как среднее значение ячеек с B2 по B7:

  • В Excel 2013, Excel 2016, Excel 2019 и более поздних версиях выберите «Комбинированный» на вкладке Все диаграммы, выберите «Разброс с прямыми линиями» для ряда Среднее и нажмите < em>ОК, чтобы закрыть диалоговое окно.
  • В Excel 2010 и более ранних версиях выберите X Y (разброс) Разброс с прямыми линиями и нажмите ОК.

Примечание. Прежде чем выбирать ячейки со значениями X и Y, не забудьте сначала очистить соответствующее поле, чтобы избежать ошибок.

Вот оно! Ваша линейчатая диаграмма с вертикальной средней линией готова и готова к работе:

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

Как добавить вертикальную линию на график в Excel

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

Вставить вертикальную линию в график Excel

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

  1. Выберите исходные данные и постройте линейный график (вкладка Вставка >Чаты, группа >Линия).
  2. Настройте данные для вертикальной линии следующим образом:
    • В одной ячейке (E1) введите текстовую метку для точки данных, в которой вы хотите нарисовать линию точно так, как она отображается в исходных данных.
    • В двух других ячейках (D3 и D4) извлеките значение X для целевой точки данных, используя следующую формулу:

Функция ПОИСКПОЗ возвращает относительное положение искомого значения в массиве, а функция ЕСЛИОШИБКА заменяет потенциальную ошибку нулем, если искомое значение не найдено.

  • В двух соседних ячейках (E3 и E4) введите значения Y, равные 0 и 1.

  • На вкладке Все диаграммы выберите Комбо.
  • Для основного ряда данных выберите тип линейной диаграммы.
  • Для серии данных Вертикальная линия выберите Разброс с прямыми линиями и установите рядом с ним флажок Вторичная ось.
  • Нажмите ОК.

Ваша диаграмма с вертикальной линией готова, и теперь пришло время попробовать ее. Введите еще одну текстовую метку в E2 и посмотрите, как вертикальная линия перемещается соответственно.

Не хотите печатать? Украсьте свой график, добавив полосу прокрутки!

Сделайте вертикальную линию интерактивной с помощью полосы прокрутки

Для непосредственного взаимодействия с диаграммой давайте вставим полосу прокрутки и соединим с ней нашу вертикальную линию. Для этого вам понадобится вкладка «Разработчик». Если у вас его еще нет на ленте Excel, его очень легко включить: щелкните правой кнопкой мыши ленту, выберите Настроить ленту, выберите Разработчик в разделе Основные вкладки и нажмите ОК. Вот и все!

А теперь выполните следующие простые шаги, чтобы вставить полосу прокрутки:

  1. На вкладке Разработчик в группе Элементы управления нажмите кнопку Вставить, а затем нажмите Полоса прокрутки в разделе Элементы управления формой. :
  2. Вверху или внизу графика (в зависимости от того, где вы хотите разместить полосу прокрутки) с помощью мыши нарисуйте прямоугольник нужной ширины. Или просто щелкните в любом месте листа, а затем переместите и измените размер полосы прокрутки по своему усмотрению.
  3. Щелкните правой кнопкой мыши полосу прокрутки и выберите Управление форматами….
  4. Свяжите полосу прокрутки с какой-нибудь пустой ячейкой (D5), установите Максимальное значение на общее количество точек данных и нажмите ОК. У нас есть данные за 6 месяцев, поэтому мы устанавливаем Максимальное значение равным 6.
  5. Связанная ячейка теперь показывает значение полосы прокрутки, и нам нужно передать это значение в наши ячейки X, чтобы привязать вертикальную линию к полосе прокрутки. Итак, удалите формулу ЕСЛИОШИБКА/ПОИСКПОЗ из ячеек D3:D4 и введите вместо нее эту простую формулу: =$D$5

    6. Ячейки Целевой месяц (D1 и E1) больше не нужны, и вы можете их удалить. Или вы можете вернуть целевой месяц, используя приведенную ниже формулу (которая относится к ячейке E1):

    Вот оно! Наша интерактивная линейная диаграмма завершена. Это заняло совсем немного времени, но оно того стоит. Вы согласны?

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

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