Excel отлично подходит для создания диаграмм на основе данных в таблице данных. Вы можете использовать инструменты на вкладке Вставка на ленте, чтобы быстро идентифицировать всю таблицу данных, или вы можете выбрать часть таблицы данных и использовать те же инструменты для создания диаграммы на основе только этой части.
Если вы изменяете диапазон данных для диаграммы довольно часто, может быть утомительно постоянно менять ссылку на диапазон данных. Например, если у вас есть таблица данных, которая включает данные за несколько лет, вы можете просмотреть диаграмму, основанную на данных за первые пять лет, а затем изменить диапазон данных, чтобы диаграмма относилась к другому подмножеству данные. Вносите изменения достаточно часто, и вы начнете искать способы сделать изменения проще (и более надежно).
Один из способов сделать это – использовать именованные диапазоны и несколько функций рабочего листа . Допустим, ваша диаграмма встроена в рабочий лист, но рабочий лист отличается от того, на котором расположены исходные данные. На том же листе, что и диаграмма, создайте две ячейки ввода, которые будут служить индикаторами «от» и «до». Назовите эти две ячейки примерно так: FromYear и ToYear.
На вашем листе данных (тот, который без диаграммы; я назову лист «Исходные данные»), данные упорядочены по годам в отдельный столбец и ряд факторов стоимости в каждой строке. Начните таблицу в столбце F и поместите свои годы в строку 2. Поместите коэффициенты затрат в столбец E, начиная со строки 3. Над годами поместите заглавную букву, которая совпадает с буквой столбца, а в столбце D поместите число. это то же самое, что номер строки данных. (См. Рис. 1.)
Рис. 1. Первый этап подготовки данных.
В этом примере диаграмма, встроенная на другой рабочий лист, основана на данных в диапазоне F2: I5. В диаграмме нет ничего особенного, но изменения, которые вы готовитесь внести, сделают ее динамической и, следовательно, гораздо более полезной.
Продолжая работать с листом «Исходные данные», начните с размещения следующая формула в ячейке B1:
= «Тенденции для» & IF (FromYear = ToYear, FromYear, FromYear & «to» & ToYear)
Эта формула обеспечивает динамический заголовок, который вы позже будете использовать для своей диаграммы. Дайте ячейке B1 имя addrTitle , затем поместите в ячейку B2 следующую формулу:
= «‘Source Data’! $» & INDEX ($ F $ 1: $ I $ 1,1, ПОИСКПОЗ (FromYear, $ F $ 2: $ I $ 2)) & «$» & D2 & «: $» & INDEX ($ F $ 1: $ I $ 1,1, MATCH (ToYear, $ F $ 2 : $ I $ 2)) & «$» & D2
Помните, что это единственная формула, хотя для ясности она показана здесь в двух строках. Скопируйте формулу из B2 в ячейки B3: B5. Формула возвращает строки адреса, которые представляют желаемые диапазоны для значений оси X и рядов данных.. Фактические диапазоны, возвращаемые формулами, будут различаться в зависимости от значений, которые вы вводите в ячейки FromYear и ToYear на другом листе. Чтобы было понятнее, вы можете ввести несколько меток в столбец A. (см. Рисунок 2)
Рисунок 2. Второй этап подготовки данных.
Теперь вам нужно назвать каждую из ячеек в диапазоне B2: B5 . Выберите B2 и в поле «Имя» (чуть выше столбца A) введите имя «addrXVal» (без кавычек). Аналогично назовите B3 как addrCost1, B4 как addrCost2 и B5 как addrCost3.
Следующим шагом является создание пары именованных формул, которые вы можете использовать при создании диаграмм. Откройте вкладку «Формулы» на ленте и щелкните инструмент «Определить имя», чтобы открыть диалоговое окно «Новое имя». (См. Рис. 3.)
Рис. 3. Диалоговое окно «Новое имя».
В области «Имя» в верхней части диалогового окна введите «rngXVal» (без кавычек), затем введите следующее в поле “Ссылается на”:
= INDIRECT (addrXVal)
Нажмите “ОК”, и имя будет определено. Затем, используя то же диалоговое окно «Новое имя», определите дополнительные имена (rngCost1, rngCost2 и rngCost3), которые используют тот же тип косвенной формулы для ссылки на диапазоны addrCost1, addrCost2 и addrCost3 соответственно.
Теперь вы, наконец, готовы обновить ссылки в вашей диаграмме. Щелкните диаграмму правой кнопкой мыши и выберите «Выбрать источник» или «Выбрать данные» в зависимости от вашей версии Excel. Excel отображает диалоговое окно «Выбор источника данных». (См. Рис. 4.)
Рис. 4. Вкладка «Серии» диалогового окна «Исходные данные».
Для каждой серии данных, перечисленных в левой части диалогового окна, нажмите кнопку «Изменить» и введите имя и значения в соответствии с указанными вами именами. Таким образом, для серии Cost1 вы должны ввести Имя = ‘Source Data’! AddrCost1 и значение = ‘Source Data’! RngCost1 . Вы также должны использовать аналогичные ссылки и имена для каждой из других серий данных.
Обратите внимание, что вы должны включить имя вашего рабочего листа (исходные данные) в апострофы в ссылках, которые вы входить. В справочнике по ярлыкам оси категорий (X) вы можете ввести = ‘Source Data’! RngXVal .
Как только это будет сделано, вы можете изменить начало и конец лет в ячейках FromYear и ToYear, а Excel автоматически и немедленно обновит диаграмму, чтобы представить указанные вами данные.
Для дополнительного прикосновения, если вы еще не добавили заголовок диаграммы, продолжайте и сделай так. Если вы используете Excel 2007 или Excel 2010, выберите диаграмму, откройте вкладку «Макет» ленты, щелкните инструмент «Заголовок диаграммы» и выберите способ отображения заголовка. Заголовок должен сразу появиться на диаграмме.
Если вы используете Excel 2013 или более позднюю версию, щелкните значок «Элементы диаграммы» в правом верхнем углу диаграммы. (Это похоже на знак плюса.) Excel отображает «всплывающее» меню, в котором перечислены различные элементы, которые вы можете добавить в свою диаграмму. (См. Рис. 5.)
Рис. 5. Добавление заголовков к диаграмме в Excel в Office 365.
Убедитесь, что рядом с элементом заголовка диаграммы стоит галочка. Заголовок должен сразу появиться на диаграмме.
Независимо от версии Excel, которую вы используете, щелкните заголовок (внутри самой диаграммы) один раз, чтобы выбрать его. Вы должны увидеть рамку выбора вокруг заголовка. В строке формул введите следующее:
= ‘Source Data’! AddrTitle
Теперь заголовок диаграммы связан с ячейкой, содержащей строку заголовка, которая в Turn динамически обновляется каждый раз, когда вы меняете значения FromYear и ToYear.
Источник: