Как обновить гистограмму в excel

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

Перетаскивание данных

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

При помощи мастера диаграмм создайте обыкновенную гистограмму только для диапазона $A$1:$D$5 (рис. 5.14).

Выделите диапазон A6:D6, правой кнопкой мыши щелкните рамку выделения и, удерживая правую кнопку, перетащите данные на диаграмму. Когда вы отпустите кнопку, появится диалоговое окно Специальная вставка (Paste Special) (рис. 5.15).

Выберите параметр В столбцах (Columns) и щелкните на кнопке ОК. Ряд данных для мая (May) будет добавлен на диаграмму (рис. 5.16).

Диалоговое окно Специальная вставка (Paste Special) выполняет большинство действий, которые нужны для этого искусного трюка.

Диаграмма и строка формул

Диаграмму можно обновить и при помощи строки формул. Выделив диаграмму и щелкнув на ней ряд данных, посмотрите на строку формул: вы увидите формулу, которую Excel использует для ряда данных. В этой формуле, которая называется функцией РЯД (SERIES), обычно указывается четыре аргумента, хотя для пузырьковой диаграммы требуется дополнительный пятый аргумент, обозначающий размер ([Size]).

Синтаксис (или порядок структуры) функции РЯД (SERIES) выглядит так: =SERIES([Name];[X Values];[Y Values];[Plot Order]), в русской версии Excel =РЯД([Имя];[Значения X];[Значения Y];[Номер графика]). Так, допустимая функция РЯД (SERIES) может выглядеть, как на рис. 5.17: =SERIES(Sheet1!$В$1;Sheet1!$А$2:$А$5;Sheet1!$В$2:$В$5;1), в русской версии Excel =РЯД(Лист1!$В$1;Лист!!$А$2:$А$5;Лист1!$В$2:$В$5;1).

На рис. 5.17 первая часть ссылки, Sheet1!$B$1, относится к имени или заголовку диаграммы — 2004. Вторая часть ссылки, Sheet1!$A$2:$A$5, относится к значениям по оси X, в данном случае — к месяцам. Третья часть ссылки, Sheet1!$B$2:$B$5, относится к значениям по оси Y, то есть 7.43, 15, 21.3 и 11.6. Наконец, последняя часть формулы, 1, относится к порядковому номеру графика, или к номеру ряда. В данном случае, когда у нас только один ряд, значение может быть равно только 1. Если бы рядов было несколько, у первого ряда был бы номер 1, у второго — номер 2 и т. д.

Чтобы изменить диаграмму, измените ссылки на ячейки в строке формул. Помимо ссылок на ячейки, в диаграммы можно вводить и явные значения, известные как массивы констант (подробнее об этом в разделе «Константы в формулах массива» справки по Excel — для вызова справки нажмите кнопку F1). Для этого добавьте {} (фигурные скобки) вокруг значений по осям X и Y, как показано в следующей формуле: =SERIES(«My Ваr»;{«А»;»В»;»С»;»D»};{1;2;3;4};1), в русской версии Excel =РЯД(«My Ваr»;{«А»;»В»;»С»;»D»};{1;2;3;4};1). В этой формуле РЯД (SERIES) А, В, С и D — это значения по оси X, а 1, 2, 3 и 4 — соответствующие им значения по оси Y. Используя этот метод, можно создавать и обновлять диаграммы, не храня данные в ячейках.

Перетаскивание граничной области

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

Если увеличивать или уменьшать ряд данных, исходный источник данных, а также подписи осей, если для них выбран параметр Авто (Auto), обновляются, отражая сделанные изменения. Это прекрасная возможность проверки сценариев, если вы хотите узнать, какие получатся результаты для различных наборов данных.

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

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

Автоматическое обновление диаграммы после ввода новых данных с помощью динамической формулы

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

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

1. Выберите диапазон данных и нажмите Настольные под Вставить вкладку, см. снимок экрана:

2. В Создать таблицу диалоговое окно, если у ваших данных есть заголовки, проверьте В моей таблице есть заголовки , затем нажмите OK. Смотрите скриншот:

3. А диапазон данных отформатирован как таблица, см. Снимок экрана:

4. Теперь при добавлении значений за июнь график обновится автоматически. Смотрите скриншот:

Ноты:

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

2. В таблице вы можете вставить данные между существующими значениями.

Автоматическое обновление диаграммы после ввода новых данных с помощью динамической формулы

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

1. Во-первых, вам нужно создать определенное имя и динамическую формулу для каждого столбца. Нажмите Формулы > Определить имя.

2. В Новое имя диалоговое окно, введите Время в Имя и фамилия поле и выберите имя текущего листа из Объем раскрывающийся список, а затем введите = СМЕЩЕНИЕ (2,0,0 $ A $; СЧЁТ (A $ A: A) -1) формула в Относится к box, см. снимок экрана:

3. Нажмите OK, а затем повторить два вышеуказанных шага, вы можете создать динамический диапазон для каждой серии, используя следующие имена диапазонов и формулы:

• Столбец B: Ruby: = OFFSET ($ B $ 2,0,0, COUNTA ($ B: $ B) -1);
• Столбец C: Джеймс: = OFFSET ($ C $ 2,0,0, COUNTA ($ C: $ C) -1);
• Столбец D: Freda: = OFFSET ($ D $ 2,0,0, COUNTA ($ D: $ D) -1)

Внимание: В приведенных выше формулах OFFSET функция относится к первой точке данных, а COUNTA относится ко всему столбцу данных.

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

5. В Выберите источник данных диалоговое окно, из Легендарные записи (серия) разделом, щелкните рубин а затем нажмите Редактировать кнопка, в выдвинутой Редактировать серию диалог, введите = Sheet3! Рубин под Значения серии раздел, см. скриншоты:

6. Затем нажмите OK вернуться к Выберите источник данных В диалоговом окне повторите шаг 5, чтобы обновить оставшиеся серии для отражения их динамических диапазонов:

• Джеймс: Значения серий: = Sheet3! James;
• Freda: Значения серии: = Sheet3! Freda

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

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

Ноты:

• 1. Вы должны вводить новые данные непрерывно, если вы пропустите строки, этот метод не будет работать должным образом.
• 2. Если вы введете данные нового столбца, этот метод не сработает.

Наконечник.Если вы хотите быстро экспортировать содержимое диапазона с листа на графику, попробуйте использовать Kutools for ExcelАвтора Диапазон экспорта как график как показано на следующем снимке экрана. Полная функция без ограничений в [модуль 745} дней, пожалуйста, скачайте и получите бесплатную пробную версию сейчас.

Статьи по теме:

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

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

In this tutorial I am going to show you how to update, change and manage the data used by charts in Excel.

This tutorial is split up in order to breakdown the Select Data Source window into small and easy to understand parts. To edit the data selection of a chart, right click the chart and select the Select Data option — or select the chart, go to the Design tab, and click the Select Data button from the left side of the ribbon menu.

From now on the tutorial will discuss features from this pop-up window:

How to Change the Data Used in a Chart

To change the data used in a chart, clear the current data reference in the Chart data range box at the top of the window (click the button to the right of the box to minimize the window if required) then select your new data. In the previous tutorial I only had 10 items for my chart. I will now expand this with a new selection:

Then click ok to update the chart:

How to Add Data to a Chart in Excel

To add another data series to your chart, simply click the Add button. The following window will open:

This allows you to select a new series name and a reference to the cells which contain the new series data. Click ok to update the chart:

How to Remove Data from a Chart in Excel

To remove a data series, select it then click the Remove button. For example I am going to remove the data series I just added:

Then click ok to update the chart.

Note: I have added the Costs data series back in for the rest of the tutorial.

How to Edit Data in a Chart in Excel

To edit a data series, select it then click the Edit button under the Legend Entries section. The following window will open allowing you to change the series name and referenced data:

How to Move Data Series Up/Down in Excel

To move a series up and down, select it then click the up/down arrows to rearrange. So for example I am going to move the Costs data series up:

Then click ok to update the chart:

Notice how the bars have swapped round.

Switching Rows/Columns in Charts in Excel

Switching the Row/Column of your data can come in handy if data in a worksheet has a poor layout. It can also give you an alternative layout for your chart, which may be better depending on how you plan on using your chart. Basically what this feature does is swap your Legend Entries (Series) round with your Horizontal Axis Labels (Category). This only works with 1 data series so I have removed Costs for this example:

Here is how the Select Data Source window looks before:

I then click the Switch Row/Column button, then ok to update the chart.

 Notice how the States are now my key and Sales is on my Y-axis and the Legend Entries / Horizontal Axis Labels are swapped round:

Editing the Horizontal Axis labels for a Chart in Excel

To edit the Horizontal Axis Labels, click the Edit button under the Horizontal Axis Labels section. This allows you to choose different labels for your charts data if you need to, just make sure the new selection is the same as the old one so all of your data will have labels.

The following window will open allowing you to edit the cell references for the labels:

Hidden and Empty Cells in Charts in Excel

If youve looked at the accompanying Excel workbook, you may have noticed that the source data for my chart has an empty row and a few hidden cells. To edit how a chart interprets such cells, click the Hidden and Empty Cells button in the bottom left corner of the window. This will open the following pop-up:

To include hidden cells, ensure the Show data in hidden rows and columns checkbox is checked.

Click ok to update the chart and it will now include the data I have hidden between rows 14:20.

For bar/column charts, empty cells will always be displayed as gaps. In order to make use of these options I have inserted a new line chart for the same data:

The default option for empty cells is Gaps. This leaves a gap between data entries as you can see above. If you change this to show Zero for empty cells:

The line chart now goes down to 0 for the empty cells. For this example this is between Nebraska and Indiana.

Now if you change this to the last option, Connect data points with line, instead of going down to Zero for empty cells, a line is drawn over the gap between the last 2 data points that werent empty:

How to Use Data from Another Worksheet for a Chart in Excel

It is worth noting that you can select data from another worksheet within the workbook and not just from within the same worksheet.

To demonstrate this, I am going to change the Costs data series to take a different set of values contained within Sheet2 instead of Sheet1.

To do this, I select the series, click the edit button and adjust the cell references as follows:

In order to select data from another sheet, select the desired sheet first and then the cell range you require. Click ok and then the chart will update:

Once youve done that, youve basically done almost everything with Data in Charts in Excel that you will ever need to do.  This tutorial should get you going for just about all of your charting data related needs and, if it doesnt, make sure to check out our other tutorials!

Similar Content on TeachExcel

Create and Manage Tables in Excel

Tutorial:
Here, I’ll show you everything you need to know to get started using tables in Excel; how…

Subscribe for Weekly Tutorials

BONUS: subscribe now to download our Top Tutorials Ebook!