Как обновить диаграмму в Excel при изменении данных

Обновлено: 03.07.2024

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

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

Один из способов сделать это — использовать именованные диапазоны и несколько функций рабочего листа. Допустим, ваша диаграмма встроена в рабочий лист, но этот рабочий лист отличается от того, на котором находятся исходные данные. На том же листе, что и диаграмма, создайте две входные ячейки, которые будут служить индикаторами «от» и «до». Назовите эти две ячейки, например FromYear и ToYear.

В вашем листе данных (тот, где нет диаграммы; я назову этот лист "Исходные данные"), данные располагаются по годам в отдельном столбце, а в каждой строке ряд факторов стоимости. Начните свою таблицу со столбца F и поместите свои годы в строку 2. Поместите факторы стоимости в столбец E, начиная с строки 3. Над годами поместите заглавную букву, совпадающую с буквой столбца, а в столбце D поместите число это то же самое, что и номер строки данных. (См. рис. 1.)

Рисунок 1. Первый этап подготовки данных.

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

Все еще работая с листом "Исходные данные", начните с размещения следующей формулы в ячейке B1:

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

Помните, что это одна формула, хотя для ясности она показана здесь в две строки. Скопируйте формулу из ячейки B2 в ячейки B3:B5. Формула возвращает адресные строки, представляющие требуемые диапазоны значений по оси X и ряда данных. Фактические диапазоны, возвращаемые формулами, будут различаться в зависимости от значений, которые вы вводите в ячейки FromYear и ToYear на другом листе. Чтобы было понятнее, вы можете ввести несколько меток в столбец A. (См. рис. 2.)

Рисунок 2. Второй этап подготовки данных.

Теперь вам нужно назвать каждую ячейку в диапазоне B2:B5. Выберите B2 и в поле «Имя» (чуть выше столбца A) введите имя «addrXVal» (без кавычек). Точно так же назовите B3 как addrCost1, B4 как addrCost2 и B5 как addrCost3.

Следующий шаг — создать пару именованных формул, которые можно использовать при создании диаграмм. Откройте вкладку «Формулы» на ленте и щелкните инструмент «Определить имя», чтобы открыть диалоговое окно «Новое имя». (См. рис. 3.)

Рисунок 3. Диалоговое окно "Новое имя".

В области "Имя" в верхней части диалогового окна введите "rngXVal" (без кавычек), затем введите следующее в поле "Относится к":

Нажмите OK, и имя будет определено. Затем в том же диалоговом окне «Новое имя» определите дополнительные имена (rngCost1, rngCost2 и rngCost3), которые используют формулу ДВССЫЛ того же типа для ссылки на диапазоны addrCost1, addrCost2 и addrCost3 соответственно.

Теперь вы наконец готовы обновить ссылки на диаграмме. Щелкните диаграмму правой кнопкой мыши и выберите «Выбрать источник» или «Выбрать данные» в зависимости от вашей версии Excel. Excel отображает диалоговое окно «Выбор источника данных». (См. рис. 4.)

Рисунок 4. Вкладка "Серии" диалогового окна "Исходные данные".

Для каждого из рядов данных, перечисленных в левой части диалогового окна, нажмите кнопку «Изменить» и введите имя и значения в соответствии с именами, которые вы определили. Таким образом, для ряда Cost1 вы должны ввести Имя ='Исходные данные'!addrCost1 и Значения ='Исходные данные'!rngCost1. Аналогичные ссылки и имена следует использовать и для всех остальных рядов данных.

Обратите внимание, что вы должны включать название вашего листа (исходные данные) в апострофах во вводимые ссылки. В справке по меткам оси категории (X) вы можете ввести = «Исходные данные»!rngXVal.

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

Для дополнительного удобства, если вы еще не добавили название диаграммы, сделайте это. Если вы используете Excel 2007 или Excel 2010, выберите диаграмму, откройте вкладку «Макет» на ленте, щелкните инструмент «Заголовок диаграммы» и выберите способ отображения заголовка. Заголовок должен сразу появиться на диаграмме.

Если вы используете Excel 2013 или более позднюю версию, щелкните значок "Элементы диаграммы" в правом верхнем углу диаграммы. (Он выглядит как знак «плюс».) Excel отображает всплывающее меню, в котором перечислены различные элементы, которые можно добавить на диаграмму. (См. рис. 5.)

Рисунок 5. Добавление заголовков к диаграмме в Excel в Office 365.

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

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

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

Биография автора

На его счету более 50 научно-популярных книг и множество журнальных статей. Аллен Уайатт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене.

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

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

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

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

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

Затем нажмите "ОК", чтобы обновить диаграмму:

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

Чтобы добавить еще один ряд данных на диаграмму, просто нажмите кнопку "Добавить". Откроется следующее окно:

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

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

Чтобы удалить серию данных, выберите ее и нажмите кнопку "Удалить". Например, я собираюсь удалить только что добавленный ряд данных:

Затем нажмите "ОК", чтобы обновить диаграмму.

Примечание. Я снова добавил ряд данных о расходах в оставшуюся часть руководства.

Как редактировать данные на диаграмме в Excel

Чтобы отредактировать ряд данных, выберите его и нажмите кнопку "Изменить" в разделе "Элементы легенды". Откроется следующее окно, в котором вы сможете изменить название серии и базовые данные:

Как перемещать ряды данных вверх/вниз в Excel

Чтобы переместить серию вверх и вниз, выберите ее, а затем нажимайте стрелки вверх/вниз, чтобы изменить порядок. Так, например, я собираюсь переместить ряд данных о расходах вверх:

Затем нажмите "ОК", чтобы обновить диаграмму:

Обратите внимание, как полосы поменялись местами.

Переключение строк/столбцов в диаграммах в Excel

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

Затем я нажимаю кнопку "Переключить строку/столбец" и затем "ОК", чтобы обновить диаграмму.

Обратите внимание, что штаты теперь являются моим ключом, а продажи находятся на моей оси Y, а записи легенды / метки горизонтальной оси поменялись местами:

Редактирование меток горизонтальной оси для диаграммы в Excel

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

Откроется следующее окно, позволяющее редактировать ссылки на ячейки для ярлыков:

Скрытые и пустые ячейки в диаграммах Excel

Если вы просмотрели прилагаемую книгу Excel, то могли заметить, что в исходных данных для моей диаграммы есть пустая строка и несколько скрытых ячеек. Чтобы изменить способ интерпретации таких ячеек диаграммой, нажмите кнопку «Скрытые и пустые ячейки» в левом нижнем углу окна. Откроется следующее всплывающее окно:

Чтобы включить скрытые ячейки, установите флажок Показать данные в скрытых строках и столбцах.

Нажмите "ОК", чтобы обновить диаграмму, и теперь она будет включать данные, которые я спрятал между строками 14:20.

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

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

Линейная диаграмма теперь сводится к 0 для пустых ячеек. В данном примере это между Небраской и Индианой.

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

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

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

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

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

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

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

Вопрос? Спросите об этом на нашем форуме Excel

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков 50+ часов обучения 200+ руководств Excel

С помощью этого онлайн-курса станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel. (Опыт работы с VBA не требуется.)

Курс VBA — от новичка до эксперта

Выделение, сортировка и группировка лучших и худших исполнителей в списке в Excel
Учебник: Как выделить строки лучших и худших исполнителей в списке данных. Это позволяет.

Цикл по диапазону ячеек в пользовательской функции в Excel
Учебное пособие: Как выполнить цикл по диапазону ячеек в пользовательской функции (пользовательская функция) в Excel. Это .

Показать все формулы на листе в Excel
Учебник: Отображение всех формул вместо их выходных значений. Это позволяет быстро устранять неполадки.

Как создать диаграмму и управлять ею в Excel
Учебное пособие. В этом учебном пособии я собираюсь познакомить вас с созданием и управлением диаграммами в Excel. Перед.

Создание таблиц и управление ими в Excel
Учебное пособие. Здесь я покажу вам все, что вам нужно знать, чтобы начать работу с таблицами в Excel; как.

Функция БС — получение будущей стоимости в Excel
Учебное пособие: Функция будущей стоимости (БС) в Excel вернет будущую стоимость инвестиционного ба.

Подпишитесь на еженедельные уроки

БОНУС: подпишитесь сейчас, чтобы загрузить нашу электронную книгу Top Tutorials!

Ссылка на наши 15 лучших учебных пособий была отправлена вам, проверьте свою электронную почту, чтобы загрузить ее!

(Если вы не видите электронное письмо, проверьте папку "Спам" или "Промоакции" и обязательно добавьте нас в список контактов, чтобы получать наши электронные письма в будущем.)

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

Добавить серию данных

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

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

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

Переименовать серию данных

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

Выберите диаграмму.
Перейдите на вкладку "Дизайн".
Нажмите кнопку "Выбрать данные".

Имя на диаграмме обновляется, но данные рабочего листа остаются неизменными.

Изменить порядок ряда данных

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

Выберите диаграмму.
Перейдите на вкладку "Дизайн".
Нажмите кнопку "Выбрать данные".

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

БЕСПЛАТНЫЙ краткий справочник

Бесплатно для распространения с нашей благодарностью; мы надеемся, что вы рассмотрите наше платное обучение.

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

Если вы похожи на меня, вам нравится идея диаграмм Excel, и вы можете придумать для них десятки полезных приложений. Но что происходит, когда вам нужно добавить больше данных? Может показаться, что для внесения изменений требуется много ручной работы. Ну, не больше! Я собираюсь показать вам, как сделать диаграмму в Microsoft Excel, которая обновляется автоматически.

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

Все, что вам нужно знать, это основы работы с диаграммами Microsoft Excel, чтобы начать работу.

Зачем использовать диаграммы Excel?

Диаграммы позволяют использовать данные в Excel для принятия решений. Это хорошая альтернатива просмотру рядов и столбцов чисел.

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

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

1. Настройте электронную таблицу Excel

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

Вот базовый макет с аккуратным форматированием:

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

Для этого проекта я создаю диаграмму, в которой отслеживаются продажи каждого романа о Гарри Поттере в книжном магазине.

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

Теперь, когда диапазон отформатирован, заголовки помечены и данные заполнены, вы готовы перейти к следующему шагу.

2. Создать таблицу

В Microsoft Excel таблицы представляют собой эффективный способ работы с диапазоном данных.

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

Чтобы создать таблицу, выберите все данные, которые вы хотите превратить в диаграмму Excel. Затем перейдите на вкладку «Вставка» и выберите «Таблица». Кроме того, вы можете использовать сочетание клавиш CTRL + T.

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

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

Теперь, когда информация аккуратно размещена в таблице, пришло время создать диаграмму.

3. Вставьте диаграмму и добавьте данные

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

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

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

Чтобы добавить больше данных, просто добавьте еще одну строку внизу существующей диаграммы. Поскольку столбец «Дата» определяет значения по оси X диаграммы, вам следует начать с нее.

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

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

Теперь вы можете добавить все свои новые данные, и диаграмма автоматически обновится с новой информацией.

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

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

Заставьте Microsoft Excel работать на вас

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

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

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