Простое изменение диапазонов данных диаграммы (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. Диалоговое окно Define Name.

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

В том же диалоговом окне определите дополнительные имена (rngCost1, rngCost2 и rngCost3), которые используют тот же тип формулы INDIRECT для ссылки на диапазоны addrCost1, addrCost2 и addrCost3 соответственно.

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

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

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

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

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

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

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

Для дополнительного удобства, если вы еще не добавили заголовок диаграммы, сделайте это. Щелкните диаграмму правой кнопкой мыши и выберите «Параметры диаграммы», затем откройте вкладку «Заголовки». (См. Рисунок 5.)

Рисунок 5. Вкладка Заголовки диалогового окна Параметры диаграммы.

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

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

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

Этот совет (2376) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и более поздних версий) здесь:

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