Простое изменение диапазонов данных диаграммы (Microsoft Excel)

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» (без кавычек), затем введите следующее в поле «Ссылается на»:

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

Теперь вы, наконец, готовы обновить ссылки в вашей диаграмме.

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

Рисунок 4. Вкладка Series диалогового окна Source Data.

Для каждого ряда данных, перечисленных в левой части диалогового окна, нажмите кнопку «Изменить» и введите Имя и Значения в соответствии с заданными вами именами. Таким образом, для серии Cost1 вы должны ввести Name of = ‘Source Data’! AddrCost1 и Values ​​of = ‘Source Data’! RngCost1.

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

Обратите внимание, что вы должны включить имя вашего рабочего листа (исходные данные) в апострофы во вводимых вами ссылках. В Категории (X)

Ссылку на метки осей можно ввести = ‘Source Data’! RngXVal.

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

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

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

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

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

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

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

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (8667) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Easily_Changing_Chart_Data_Ranges [Простое изменение диапазонов данных диаграммы].