Как изменить значения в диаграмме excel

Изменение ряда данных на диаграмме

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

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

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

отобразить или скрыть данные на диаграмме с помощью фильтров;

Редактирование или изменение порядка рядов

Щелкните диаграмму правой кнопкой мыши и выберите команду выбрать данные.

В поле Элементы легенды (ряды) щелкните ряд, который нужно изменить.

Нажмите кнопку Изменить, внесите изменения и нажмите кнопку ОК.

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

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

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

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

Фильтрация данных на диаграмме

Начнем с фильтров диаграмм.

Щелкните в любом месте диаграммы.

Нажмите кнопку Фильтры диаграммы рядом с диаграммой.

На вкладке Значения выберите ряды или категории, которые нужно отобразить или скрыть.

Нажмите кнопку Применить.

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

Редактирование или изменение порядка рядов

На ленте нажмите кнопку конструктор диаграмм и выберите пункт выбрать данные.

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

Чтобы изменить ряд легенды, в поле элементы легенды (ряды) выберите ряд, который вы хотите изменить. Затем измените значения в полях имя и Y , чтобы внести изменения.

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

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

Фильтрация данных на диаграмме

В Word и PowerPoint сделайте следующее: Выберите диаграмму, а затем на вкладке конструктор диаграмм нажмите кнопку изменить данные в Excel.

В Excel выберите название категории, а затем на вкладке Главная выберите Сортировка и фильтр > Фильтр.

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

Как изменить график в Excel с настройкой осей и цвета

Далеко не всегда удается сразу создать график и диаграмму в Excel соответствующий всем требованиям пользователя.

Изначально сложно определить в каком типе графиков и диаграмм лучше представить данные: в объемно разрезной диаграмме, в цилиндрической гистограмме с накоплением или графике с маркерами.

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

Изменение графиков и диаграмм

Создайте табличку с данными как ниже на рисунке. Вы уже знаете, как построить график в Excel по данным. Выделите таблицу с данными и выберите инструмент «Вставка»-«Гистограмма»-«Гистограмма с группировкой».

Получился график, который нужно отредактировать:

• удалить легенду;
• добавить таблицу;
• изменить тип графика.



Легенда графика в Excel

Можно легенду добавить на график. Для решения данной задачи выполняем следующую последовательность действий:

1. Щелкните левой кнопкой мышки по графику, чтобы активировать его (выделить) и выберите инструмент: «Работа с диаграммами»-«Макет»-«Легенда».
2. Из выпадающего списка опций инструмента «Легенда», укажите на опцию: «Нет (Не добавлять легенду)». И легенда удалится из графика.

Таблица на графике

Теперь нужно добавить в график таблицу:

1. Активируйте график щелкнув по нему и выберите инструмент «Работа с диаграммами»-«Макет»-«Таблица данных».
2. Из выпадающего списка опций инструмента «Таблица данных», укажите на опцию: «Показывать таблицу данных».

Типы графиков в Excel

Далее следует изменить тип графика:

1. Выберите инструмент «Работа с диаграммами»-«Конструктор»-«Изменить тип диаграммы».
2. В появившимся диалоговом окне «Изменение типа диаграммы» укажите в левой колонке названия групп типов графиков — «С областями», а в правом отделе окна выберите – «С областями и накоплением».

Для полного завершения нужно еще подписать оси на графике Excel. Для этого выберите инструмент: «Работа с диаграммами»-«Макет»-«Название осей»-«Название основной вертикальной оси»-«Вертикальное название».

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

Удалите график, чтобы перейти к следующему заданию. Для этого активируйте его и нажмите клавишу на клавиатуре – DELETE.

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

На основе исходной таблицы снова создайте график: «Вставка»-«Гистограмма»-«Гистограмма с группировкой».

Теперь наша задача изменить заливку первой колонки на градиентную:

1. Один раз щелкните мышкой по первой серии столбцов на графике. Все они выделятся автоматически. Второй раз щелкните по первому столбцу графика (который следует изменить) и теперь будет выделен только он один.
2. Щелкните правой кнопкой мышки по первому столбцу для вызова контекстного меню и выберите опцию «Формат точки данных».
3. В диалоговом окне «Формат точки данных» в левом отделе выберите опцию «Заливка», а в правом отделе надо отметить пункт «Градиентная заливка».

Для вас теперь доступны инструменты для сложного оформления градиентной заливки на графике:

• название заготовки;
• тип;
• направление;
• угол;
• точки градиента;
• цвет;
• яркость;
• прозрачность.

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

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

График в Excel не является статической картинкой. Между графиком и данными существует постоянная связь. При изменении данных «картинка» динамически приспосабливается к изменениям и, таким образом, отображает актуальные показатели.

Динамическую связь графика с данными продемонстрируем на готовом примере. Измените значения в ячейках диапазона B2:C4 исходной таблицы и вы увидите, что показатели автоматически перерисовываются. Все показатели автоматически обновляются. Это очень удобно. Нет необходимости заново создавать гистограмму.

Excel. Диаграмма, изменяющаяся при добавлении данных

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

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

Рис. 1. График с маркерами

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

Далее создадим два именованных динамических диапазона: один для меток категорий (Даты), второй – для точек данных (Курс $). Для создания именованного диапазона пройдите по меню Формулы → Диспетчер имен (рис. 2).

Рис. 2. Диспетчер имен

В открывшемся окне «Диспетчер имен» нажмите кнопку создать, и в окне «Создание имени» введите имя диапазона – «Даты» и формулу для ссылки на диапазон: =СМЕЩ(Лист1!$A$1;1;0;СЧЁТЗ(Лист1!$A$1:$A$100)-1;1)

Рис. 3. Присвоение имени динамическому диапазону

Обратите внимание, что сразу же за аргументом функции СЧЁТЗ стоит «–1». Благодаря этому заголовок ряда не будет включен в именованный диапазон. Заметьте также, что в качестве аргумента функции СЧЁТЗ указан не весь столбец А, а лишь первые 100 ячеек. Если вы используете большой массив данных, укажите соответствующее число, например, 1000 или 10 000. В ранних версиях Excel такое ограничение весьма желательно, дабы не перегружать вычисления. Указывая колонку полностью, вы заставляете Excel просматривать тысячи ненужных ячеек. Некоторые функции Excel достаточно умны, чтобы определить, какие ячейки содержат данные, некоторые сделать этого не могут. В новых версиях Excel не обязательно строго ограничивать диапазон, так как обработка больших диапазонов в них улучшена.

Затем создайте второй именованный диапазон для данных столбца В (рис. 4)

Рис. 4. Динамический диапазон «Курс»

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

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

В открывшемся окне «Выбор источника данных» выделяем ряд и жмем «Изменить» (рис. 6).

Рис. 6. Изменить ряд

В открывшемся окне «Изменение ряда» заменяем ссылки на ячейки на имя ряда «Курс» (рис. 7). Обратите внимание, что имя листа Excel следует оставить в неизменном виде «=Лист1!»

Рис. 7. Замена ссылок на имя диапазона

Аналогично заменяем подписи горизонтальной оси (категории): жмем другую кнопку «Изменить» в правой части окна «Выбор источника данных» (см. рис. 6) и вводим имя «Даты» вместо ссылок на ячейки (рис. 8).

Рис. 8. Замена подписей оси (категорий)

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

Рис. 9. Новые данные, добавленные в таблицу (выделены желтым) автоматически отражаются на диаграмме

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

Добавление от 19 июня 2018 г. Эту же проблему гораздо проще решить, если встать на любую ячейку диапазона, и нажать Ctrl+T (англ.). Диапазон превратится в Таблицу. Создайте на ее основе диаграмму. При добавлении строк в Таблицу, диаграмма будет отражать их автоматически.

Построение диаграммы для фиксированного числа последних данных

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

См. пример на Лист2 в Excel-файле. Для данных в столбце А создайте динамический именованный диапазон с именем Даты30 (последние 30 дней), который ссылается на следующие данные: =СМЕЩ($A$1;СЧЁТЗ($A$1:$A$100)-30;0;30;1). Для данных в столбце В создайте динамический именованный диапазон с именем Курс30, который ссылается на следующие данные: =СМЕЩ($B$1;СЧЁТЗ($B$1:$B$100)-30;0;30;1). Замените в диаграмме ссылки на диапазоны данных именами динамических диапазонов. Получится диаграмма, отражающая последние 30 значений (рис. 10).

Рис. 10. На диаграмме отражаются 30 последних значений

При добавлении данных в таблицу область отражения на диаграмме сместится (рис 11).

Рис. 11. При добавлении данных диаграмма по-прежнему отражает 30 последних значений

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

17 комментариев для “Excel. Диаграмма, изменяющаяся при добавлении данных”

Здравствуйте! Подскажите, а можно реализовать то же самое, только с учетом времени, а не только даты. То есть, когда временной формат имеет значение не «дд.мм.гггг», а «дд.мм.гггг чч.мм.сс» или «дд.мм.гггг» «чч.мм.сс». Чтобы на графике было видно изменение значения на протяжении дня. Заранее Вам спасибо.

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

Огромнейшее-преогромнейшее Вам спасибо!
А я так долго мучился, делая вручную…

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

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

В диаграмме изначально пытался подсунуть экселю СМЕЩ, и сослаться на нужный мне диапазон. Однако он ругается что данная функция не действительна. Тоже пишет и о ДВССЫЛ.

Николай, пришлите файл в личку, и напишите подробно, что Вам нужно…

А у меня с точечной диаграммой этот фокус не проходит…

Алексей, а у меня получилось на полтыка)) В прилагаемом файле Excel на Лист1 просто поменял тип диаграммы График на Точечная (у меня Excel 2013)

Сергей.
Вот находясь в поисках нашел Ваш сайт. Мне надо чтобы отображались не только даты, но и время. Мои эксперименты ни к чему не привели. Единственное, что понял — не надо использовать точечную диаграмму — только график. Поэтому отсечки времени в двух колонках: «А» — дата, «В» — время. Поэтому и проблема с именованным динамическим диапазоном «Дата». Может подскажете?

Василий, пришлите файл на s_bag@mail.ru. Подробно опишите, что вам нужно получить.

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

Добрый день.
Мне пришлось создать таблицуграфик с параметрами: Верхнеенижнее давление, пульс, глюкоза. Первые три параметра измеряются 4 раза в день, глюкоза — 3. Потом для графика считаются средние значения за сутки (по каждому параметру)и выводятся в виде графика. Для глюкозы цветом в таблице выделяются значения меньшеравно 5,7 и на графике создана дополнительная ось 4,6-8,0.
Очень хотелось бы сделать автоматическую установку даты (СЕГОДНЯ)с изменением на оси Х диаграммы и автоматические же изменения (по дате) в данных для графика. Пусть крайним значением графика будет текущая дата, а диапазон оси Х -1,5 месяца.
В каком направлении мне «копать»?
PS. Сейчас, чтобы избежать «завала» графика в «0» на завтрашней дате, я заполняю поля будущих измерений средними значениями.
PPS. Думаю, этот график многим пригодится, если его доделать…

Сергей, добрый день! Пробую по вашему второму способу сделать таблицу, но у меня она горизонтальная — так удобнее вести, а таблица формируется вертикальной((( как подстроить эту функцию под мою горизонтальную табличку?)))

Сергей, добрый день!

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

Форматирование диаграмм в Excel ч.1

Здравствуйте, друзья! Вот мы и научились строить диаграммы, и, возможно, вы уже попробовали изобразить такую в своей рабочей книге. А что дальше? А дальше ее, как и любой другой объект в Майкрософт Эксель, можно всячески настраивать, изменять и оформлять на свой вкус, чтобы получить наилучшую отдачу.

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

Как выделить, скопировать, переместить диаграмму Excel

Как и многие другие объекты в Microsoft Office, диаграмму можно выделить, переместить, изменить ее размеры. Чтобы выделить диаграмму – кликните по ней мышью один раз. Вокруг диаграммы появится рамка, означающая, что этот объект активирован. Теперь чтобы выбрать любой элемент диаграммы, например, ось, легенду, заголовок, сделайте по нему одиночный клик.

Чтобы переместить диаграмму – активируйте ее. При этом, курсор изменит вид, к стандартной стрелке добавится четырехнаправленная черная стрелка. Это означает, что диаграмму можно перемещать. Зажмите мышь в любом месте диаграммы и тяните ее в нужном направлении. После перемещения – отпустите. Некоторые элементы можно перемещать в пределах области диаграммы: заголовок, названия осей, легенду, подписи данных. Активируйте его и тяните в нужное положение.

Так же, для перемещения диаграммы можно воспользоваться методом копирования-вставки.

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

Скопировать диаграмму можно все тем же методом копирования-вставки с помощью горячих клавиш или команд на ленте.

Чтобы удалить диаграмму или ее элемент— выделите ее и нажмите Del. Или же нажмите правой кнопкой мыши и в контекстном мню выберите «Удалить».

Чтобы перенести диаграмму на другой лист, можно воспользоваться копированием-вставкой, но есть более правильный способ: выделите ее и нажмите на ленте Конструктор – Расположение – Переместить диаграмму . В открывшемся окне можно выбрать куда перемещать (на отдельный лист, или на другой существующий).

Как напечатать диаграмму

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

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

Как изменить тип диаграммы

Так часто бывает, чтобы выбрать подходящий тип диаграммы, нужно поэкспериментировать и посмотреть, как будут выглядеть данные в различных исполнениях. Чтобы изменить тип уже созданной диаграммы, выделите ее и нажмите на ленте Конструктор – Тип – Изменить тип диаграммы . Откроется окно, в котором доступны все возможные варианты диаграмм.

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

Как изменять ряды данных в графиках Эксель

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

Чтобы поменять местами оси X и Y (а это иногда делает диаграмму более читаемой), выполните на ленте: Конструктор – Данные – Строка/Столбец. Еще один способ – сделайте правый клик на диаграмме, в контекстном меню выберите «Выбрать данные» и в открывшемся окне нажмите кнопку «Строка/Столбец».

Например, у нас есть вот такая диаграмма:

Поменяем оси местами и получим вот это:

Как видите, категории стали значениями, а значения – категориями.

Чтобы удалить ряд – выделите его прямо на диаграмме и нажмите Del. После этого, он больше не будет отображаться на диаграмме, хотя в исходной таблице сохранится.

Еще один способ – правый клик на диаграмме, выбираем в контекстном меню «Выбрать данные», в открывшемся окне выделяем ряд и жмем «Удалить».

Начиная с Excel 2013 ряды можно скрывать и отображать. Это удобнее, чем удалять, ведь скрытый ряд можно в пару кликов снова отобразить. Чтобы скрыть ряд – выделите диаграмму, справа от нее появятся три иконки: Элементы диаграммы, Стили диаграммы, Фильтры диаграммы. Выберите «Фильтры диаграммы» и в открывшемся меню выберите те ряды и категории, которые нужно отображать на графике. После этого нажмите «Применить» и получайте результат. Очень удобно, не правда ли?

Чтобы добавить еще один ряд в диаграмму, активируйте ее и выполните на ленте Конструктор – Данные – Выбрать данные (или выберите соответствующий пункт в контекстном меню. Откроется окно работы с рядами, где нажимаем кнопку «Добавить». В открывшемся диалоговом окне указываем название и значения ряда (или ссылки на них, что будет более продуманным решением). Нажимаем Ок и ряд добавлен.

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

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

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

Чтобы добавить подписи по оси Х, в том же окне «Выбор источника данных» найдите пункт «Подписи горизонтальной оси» и нажмите «Изменить». В открывшемся диалоговом окне задайте ссылки на подписи и нажмите Ок.

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

1. Пустые значения – в этом случае в месте пропуска на графике будет пусто

1. Нулевые значения – Excel будет считать, что в незаполненной ячейке ноль. Он и будет показан на графике
2. Линия – программа соединит линией предыдущую и последующую точки данных.

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