Ref-sgain.ru

Программы и сервисы
6 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как скрыть нулевые метки данных на диаграмме в Excel?

Как сделать разрыв на графике в excel?

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

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

Подготовка данных

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

Значения необходимо преобразовать в значения, близкие к основному массиву. Используя формулу = ЕСЛИ(C3>100;100+C3*100/1000;C3), значения, превышающие 100, умножаются на 0,1 и прибавляются к 100. Таким образом, значение 960 будет преобразовано в 196 = 100 + 960*0,1 и не будет так разительно отличаться от общей массы.

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

Построение диаграммы

Удерживайте клавишу CTRL. Затем выберите в столбцах Пересчет, Разделитель и Дата. Здесь указаны диапазоны B2:B17 и D2:E17. В группе Диаграммы нажмите вкладку Вставка и выберите Вставить диаграмму -> Диаграмма. У вас должен получиться следующий график.

Теперь необходимо настроить отображение главной и вспомогательной оси. Чтобы отобразить вспомогательную ось, щелкните правой кнопкой мыши на разделителе рядов данных. В раскрывающемся меню выберите Формат строки данных. В правой панели появившегося формата строки данных перейдите на вкладку Параметры строки и выберите маркер Построить строку -> Вспомогательная ось. Ось Y должна появиться в правой части графика.

Затем настройте оси так, чтобы на главной оси (та, что слева) отображались значения меньше 100 с шагом 20, а значения от 100 и выше не должны быть видны на оси, а на вторичной оси отображались значения от 100 и выше с шагом 200, но нижняя половина не должна быть видна. В этом случае пользовательский формат будет полезен. Обо всем по порядку.

Читайте так же:
Как скрыть картинку при скрытии строк или столбцов в Excel?

Щелкните правой кнопкой мыши на главной оси и выберите в выпадающем меню пункт Форматировать ось. На вкладке Параметры оси в правой панели установите Минимальное значение 0, Максимальное значение 200 и Основные деления 20. На той же вкладке перейдите в группу Число и в поле Код формата введите Пользовательский формат 0;[ Конструктор в группе Макеты графиков. Выберите Add Chart Element -> Data Handling -> Centered. Щелкните правой кнопкой мыши по надписям, которые появляются на диаграмме, и выберите в выпадающем меню пункт Форматировать ручки данных. Затем в правой панели щелкните Caption Labels, выберите Values from Cells, а в появившемся диалоговом окне Data Label Range укажите диапазон для столбца Data (в моем примере это С3:С17), нажмите OK и снимите все остальные опции с Include in Caption.

Мы должны скорректировать формат нашей диаграммы — убрать легенду, изменить название диаграммы. В нашей диаграмме мы можем сделать подписи данных более контрастными, так как это выглядит не совсем традиционно и может ввести пользователей в заблуждение.

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

Подписи данных в Excel 2010

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

Наша таблица должна иметь дополнительную колонку, назовем ее Captions и введем формулу =C3&» «, которая будет переводить числовой формат в текстовый.

Затем щелкните правой кнопкой мыши строку Пересчитать данные и выберите в выпадающем меню пункт Данные. В диалоговом окне Выбор источника данных нажмите кнопку Изменить в поле Обозначение горизонтальной оси. В следующем окне выберите Колонка данных для подписи. Дважды щелкните на кнопке OK.

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

Выберите строку Преобразования, перейдите в раздел Работа с диаграммами -> Макет в группе Надписи, выберите Надписи -> Центрировать. Щелкните правой кнопкой мыши на отображаемых надписях на диаграмме и выберите в выпадающем меню пункт Форматировать обработку данных. На вкладке Параметры подписи диалогового окна установите флажок в поле Имена категорий и снимите все остальные флажки. Нажмите Закрыть.

Читайте так же:
Как сделать закругленные углы в рамке диаграммы в Excel?

Еще необходимо настроить горизонтальные оси. Выберите Format Axis из выпадающего меню при щелчке правой кнопкой мыши по нижней горизонтальной оси. Выберите No в поле Axis Signature на вкладке Axis Parameters и нажмите Close.

Снова выделите весь график, перейдите в раздел Работа с графиками -> Макет в группе Оси, выберите Оси -> Горизонтальная вспомогательная ось -> Слева направо. На диаграмме должна быть отображена дополнительная горизонтальная ось, которая по умолчанию располагается в верхней части диаграммы. Для этого щелкните правой кнопкой мыши на вспомогательной оси и выберите в выпадающем меню пункт Форматировать ось. Выберите No в поле Main на вкладке Axis Parameters и выберите Bottom в поле Axis label. Перейдите на вкладку Цвет линии, установите флажок напротив Нет линии и нажмите Закрыть.

Резюме

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

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

Отражение пропущенных данных на графиках Excel

В некоторых данных некоторые значения отсутствуют. Например, можно забыть провести измерение при ручном сборе данных. Например, рассмотрим ситуацию в Москве с измерением температуры каждые три часа. На графике ниже приведена статистика за 25 августа — 2 сентября 2011 года. 1.

Рисунок 1: Данные о температуре в Москве (по оси х указаны дата и время измерения).

Скачать конспекты в формате Word, примеры в формате Excel

Допустим, что некоторые измерения не были проведены (рис. 2).

Рис. 2: Устройство для выравнивания данных

Excel предлагает три варианта отображения отсутствующих данных на диаграммах. Чтобы получить доступ к выбору этих опций, активируйте диаграмму и перейдите в раздел Работа с диаграммами — Конструктор — Выбрать данные (Рисунок 3).

Рис. 3. Выбрать данные

В открывшемся окне «Выбор источника данных» нажмите на «Скрытые и пустые ячейки» (рис. 4).

Рисунок 4: Скрытые и пустые ячейки

В открывшемся окне «Настройка скрытых и пустых ячеек» (рис. 5) выберите одну из опций:

  • Пустые значения (по умолчанию); график будет выглядеть как на рисунке 3.
  • Нулевые значения; график будет выглядеть как на рисунке 6; такая интерпретация отсутствующих данных верна, если действительно пустое значение соответствует нулю; например, если график отражает продажи продукции и система не показывает дни, в которые не было продаж, то при последующем построении графика, отражающего все календарные (рабочие) дни, график должен показать нули в пропущенных днях.
  • Линия; способ отображения графика показан на рисунке 7; такое отражение отсутствующих данных верно, если значение не было введено (не измерено) по ошибке, и отсутствующие данные должны были быть каким-то образом интерпретированы.
Читайте так же:
Как сделать так, чтобы определенные строки повторялись вверху каждой печатной страницы в Excel?

Рисунок 5: Выбор способа отображения отсутствующих данных

Рисунок 6 — Замена недостающих данных нулевыми значениями

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

Еще два слова об опции «Показать данные в скрытых строках и столбцах» (рис. 5). По умолчанию эта опция отключена, и она будет отключена, если мы скроем часть данных, например, за 27 августа (рис. 8).

Рисунок 8. Скрытие части данных на листе приводит к тому, что они не отражаются на диаграмме

Вы можете включить опцию «Показывать данные в скрытых строках и столбцах», чтобы данные на листе были скрыты, но видны в графике (рис. 9).

Рисунок 9. Скрытые данные отражены на диаграмме

В ситуациях, когда ось Y имеет несколько значений в пределах 10-20, высота столбца увеличивается в 5-10 раз. Тогда для наглядности я хочу вставить разрыв по оси Y на графике. Я искал в Яндексе, Google, на сайтах поддержки и форумах MS, если их можно так назвать, и на форуме пользователей тоже — было несколько похожих случаев, но ответа не было.

М С никогда не задумывался об этом, не так ли?

Моим решением до сих пор было сложить два одинаковых графика друг на друга, установить нижний график в начало разрыва, верхний график в конец разрыва и сдвинуть графики (см. снимок экрана ниже). Но это не является решением проблемы. Есть ли у вас какие-либо идеи?

как сделать разрыв на графике в excel

П.С. Я создал темы на 6 тематических форумах. Если есть ответ, я воспроизведу его, поскольку информация полезна.

P.S.2. Прилагается документ с примером.

как сделать разрыв на графике в excel

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

Добавим контрольное значение для ежедневных денежных поступлений (например, выручки) в размере 30 000 рублей.

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

Как поменять оси в графике в Эксель?

Вы можете показать или скрыть оси на диаграмме, щелкнув в любом месте на ней. На панели Работа с диаграммами есть три дополнительные вкладки: Конструктор, Макет и Формат. На вкладке Макет в группе Оси нажмите кнопку Оси. Выберите тип оси, которую нужно отобразить или скрыть, а затем щелкните нужные параметры.

Как поменять Х и У в Экселе?

после выбора диапазона и вставки диаграммы (графика) — кликаем правой кнопкой мыши и выбираем в контекстном меню Выбрать данные… в окне Выбор источника данных нажимаем Изменить в области Элементы легенды (ряды) в окне Изменение ряда аналогично выбираем другие диапазоны для Значения X и Значения Y — меняем их местами

Как изменить значения горизонтальной оси в Excel?

Для изменения площади оси X.

  1. Откройте файл Excel с диаграммой, которую вы хотите настроить.
  2. Щелкните правой кнопкой мыши по оси X на графике, который вы хотите изменить. …
  3. Затем нажмите «Выбрать данные».
  4. Выберите «Правка» под вкладкой «Метки горизонтальной оси».
  5. Затем нажмите на Выбрать диапазон.

Как изменить числа в графике Excel?

Формат чисел на оси значений можно изменить, выполнив следующие шаги:

  1. щелкните правой кнопкой мыши подписи оси значений, которые нужно отформатировать;
  2. нажмите кнопку Формат оси;
  3. в области Формат оси выберите параметр Число; …
  4. выберите нужные параметры формата чисел;

Как подписать оси графика в Excel?

Добавление обозначения оси вручную

  1. Щелкните в любом месте диаграммы, к осям которой нужно добавить названия. …
  2. На вкладке Макет в группе Подписи нажмите кнопку Названия осей.
  3. Выполните одно из следующих действий: …
  4. В текстовом поле Название оси, появившемся на диаграмме, введите нужный текст.

Как изменить значения вертикальной оси в Excel?

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

  1. Щелкните в любом месте диаграммы. …
  2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы , а затем щелкните Вертикальная ось (значений) .

Как добавить точки на график Эксель?

Как добавить одну точку данных к линейному графику в Excel?

  1. Рядом с исходными данными введите указанную точку данных, которую вы добавите на диаграмму. …
  2. Щелкните линейную диаграмму правой кнопкой мыши и выберите Выберите данные из контекстного меню. …
  3. В диалоговом окне «Выбор источника данных» щелкните значок Добавить в Легендарные записи (серия) раздел.

Как добавить еще один график в Excel?

На вкладке «Вставка» выберите точечный график с плавными кривыми. Чтобы добавить еще один график, выберите существующий график и нажмите «Выбрать данные» на вкладке «Конструктор». В новом окне нажмите «Добавить» в поле «Элементы легенды» и укажите ячейки для «Имя строки:», «Значения X:» и «Значения Y:».

Как поменять местами основную и вспомогательную ось?

Щелкните правой кнопкой мыши на строке данных с текущим значением эффективности и выберите во всплывающем меню пункт Форматировать строку данных. Измените боковой зазор на 315% в правой панели вкладки Параметры ряда.

Как изменить значения вертикальной оси в диаграмме?

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

  1. Щелкните в любом месте диаграммы. …
  2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем щелкните Вертикальная ось (значений).

Как изменить масштаб диаграммы?

Как изменить масштаб осей диаграммы в Excel?

  1. Изменить масштаб оси на диаграмме
  2. Щелкните правой кнопкой мыши по оси, которую вы хотите изменить, выберите Ось формата из контекстного меню. …
  3. в Ось формата диалог, проверьте Основная единицаАвтора Исправлена вариант, затем в текстовом поле введите новую необходимую вам единицу измерения.

Как изменить шкалу в диаграмме Excel?

Выберите ось значений, которую вы хотите изменить. В меню Формат выберите команду Выделить ось, а затем вкладку Масштаб. В списке Scale Value выберите значение шкалы или введите числовое значение. Установите флажок Показывать на диаграмме, чтобы отобразить заголовок блока.

Как внести изменения в диаграмму в Excel?

Изменение данных, включаемых в диаграмму

  1. В меню Вид выберите пункт Макет.
  2. Щелкните график.
  3. На вкладке Диаграммы в группе Данные нажмите стрелку рядом с полем Изменить и выберите Изменить данные в Excel.

Как изменить текст в диаграмме Excel?

Редактирование имени или метки данных, связанных с данными на диаграмме

Как выделить другим цветом колонки диаграммы с минусовыми значениями (значениями меньше нуля) в программе «Excel» .

Рассмотрим диаграмму построенную на основе таблицы температур марта месяца.
Ось X диаграммы — это нулевое значение температуры, столбцы идущие вверх- температура выше нуля градусов, вниз — ниже нуля.

шкала температур

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

Первый способ – «ручной», прост и примитивен:

Заливка столбца цветом в эксель

Необходимо по очереди выделить каждый столбец диаграммы и при помощи опции «Заливка» окрасить столбцы по очереди в нужный цвет.

Этот метод подходит для небольших графиков из 5-10 полос, но не подходит для анализа больших наборов данных.

Второй способ – «автоматический».

Условное форматирование (условный формат) столбцов (колонок) диаграммы.

  • Выделяем нужный нам ряд диаграммы;
  • Кликаем по нему правой кнопкой мыши;
  • В появившемся контекстном меню выбираем пункт «Формат ряда данных…»;Формат ряда данных в эксель диаграмме
  • Во вкладке «Заливка» выбираем «Сплошная заливка»;
  • Далее ставим флажок (галочку) напротив пункта «Инверсия для чисел < 0 »;инверсия заливки в эксель
  • Выбираем цвет заливки в появившейся палитре цветов;
  • После проделанных операций закрываем меню формата ряда нажатием кнопки «Ок».

итоги заливки диаграммы

В результате получится график с разными цветными (заполненными) столбцами в зависимости от знака («плюс» и «минус»).

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector