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

Добавил пользователь Валентин П.
Обновлено: 09.09.2024

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

Какая общая сумма ряда?
Какой процент у каждого сегмента в ряду?
Какое процентное изменение по сравнению с предыдущим периодом?

Динамические подписи данных на гистограмме

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

Как создать график с динамическими подписями данных

Функция ТЕКСТ
Функция ВЫБОР
Одна сводная таблица
Один срез
Одна гистограмма

Загрузите файл в качестве примера, что бы следовать за моими пояснениями. Внимание: Этот файл работает правильно в Excel 2013 или 2016.

Шаг 1: Создайте гистограмму с итоговыми данными

Первым шагом является создание обычной гистограммы с общими итогами над столбцами. Думаю, как ее создать Вы знаете ;)

Шаг 2: Расчет значений для подписей данных

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

Я создал раздел на листе для каждого измерения: Сумма, % от общего, и % изменения.
Это довольно легко, и я не буду вдаваться в детали каждого расчета.

Шаг 3: Используйте формулу ТЕКСТ для формирования подписей

Как правило, диаграмма отображает подписи данных на основе основного источника для диаграммы.

В Excel 2013 была введена новая возможность под названием "Значения из ячеек". Эта возможность позволяет указать диапазон, который мы хотим использовать для подписей данных.

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

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

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

Шаг 4: Использование функции ВЫБОР

Используйте функцию ВЫБОР, для того что бы определить какие именно подписи данных необходимо отобразить на диаграмме.

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

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

Функция ВЫБОР позволяет нам указать индекс (1,2,3. ) и она возвратит то значение, которое соответствует индексу.

Сейчас мы просто добавим ячейку, которая содержит номер индекса, и указывает на три метрики для каждого значения в формуле ВЫБОР.

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

Шаг 5: Настройка подписей данных

Следующим шагом является изменение подписей данных, чтобы они отображали значения из ячеек, сформированных нами при помощи формулы ВЫБОР.
Как я говорил ранее, мы будем использовать функцию "Значения из ячеек", которая есть в Excel 2013 или 2016, что бы сделать это проще.
Вам нужно выбрать на графике ряд подписей, затем нажать на кнопку "Выбрать диапазон" в меню Параметры подписи.

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

Повторите этот шаг для каждого ряда на гистограмме.

Шаг 6: Настройка сводной таблицы и среза

Последний шаг позволит сделать наши подписи данных интерактивными. Мы сделаем это при помощи сводной таблицы и среза. Исходные данные для сводной таблицы - это таблица на рисунке ниже.

Эта таблица содержит три варианта для различных подписей данных.
Она также включает в себя номер индекса, на который будет ссылаться формула ВЫБОР (см. шаг 4).
Создайте сводную таблицу. Добавьте Name, Index и Symbol в строки сводной таблицы.

Затем вставьте срез для поля Символ. Для этого кликните на сводную таблицу, зайдите в меню Конструктор и там нажмите кнопку Вставить срез.

Срез может быть отформатирован, чтобы соответствовать оформлению Вашего графика. Я изменил количество столбцов до 3, что бы показать кнопки среза по горизонтали.

Последний шаг состоит в том, что бы в ячейке С38 сослаться на значение индекса из сводной таблицы для корректной работы формулы ВЫБОР. Когда пользователь нажимает на кнопку среза, в ячейке С38 отразится индекс выбранного элемента сводной таблицы.

Формулы ВЫБОР автоматически отобразят подписи данных для выбранного среза.

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

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

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

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

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

Шаг 1 — Нажмите на график. В правом верхнем углу диаграммы появляются три кнопки. Они —

Шаг 2 — Нажмите Значок элементов диаграммы. Будет отображен список доступных элементов.

Доступны следующие элементы диаграммы —

Топоры
Названия осей
Названия диаграмм
Метки данных
Таблица данных
Панель ошибок
Сетки
легенда
Trendline

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

появляется рядом с заголовками осей в списке элементов диаграммы.

Шаг 4 — Нажмите чтобы увидеть варианты названий осей.

Шаг 5 — Выберите / отмените выбор элементов диаграммы, которые вы хотите отображать на диаграмме, из списка.

В этой главе вы поймете различные элементы диаграммы и их использование.

Топоры

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

Вертикальная ось (также известная как ось значений или ось y), и
Горизонтальная ось (также известная как ось категории или ось x)

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

Радарные карты не имеют горизонтальных (категория) осей. Круговые и кольцевые диаграммы не имеют осей.

Не все типы диаграмм отображают оси одинаково.

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

Диаграммы столбцов, линий и площадей показывают числовые значения только по вертикальной оси (значения) и текстовые группировки (или категории) по горизонтальной оси. Ось глубины (серии) является еще одной формой оси категории.

Названия осей

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

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

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

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

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

Чтобы добавить заголовки осей,

Шаг 1 — Нажмите на график.

Шаг 2 — Нажмите Значок элементов диаграммы.

Шаг 3 — Из списка выберите Названия осей. Названия осей отображаются для горизонтальной, вертикальной и глубинных осей.

Шаг 4 — Щелкните Название оси на диаграмме и измените названия осей, чтобы дать осмысленные имена представляемым ими данным.

Вы можете связать названия осей с ячейками, содержащими текст на листе. При изменении текста на рабочем листе названия осей также меняются соответствующим образом.

Шаг 1 — На графике щелкните поле заголовка любой оси.

Шаг 2 — На листе в строке формул введите знак равенства (=). Выберите ячейку листа, которая содержит текст, который вы хотите использовать для заголовка оси. Нажмите Ввод.

Название оси изменяется на текст, содержащийся в связанной ячейке.

Заголовок диаграммы

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

Чтобы добавить заголовок диаграммы —

Шаг 1 — Нажмите на график.

Шаг 2 — Нажмите Значок элементов диаграммы.

Шаг 3 — Из списка выберите Заголовок диаграммы . Над графиком появится поле Заголовок диаграммы.

Шаг 4 — Выберите заголовок диаграммы и введите нужный заголовок .

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

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

Шаг 1 — На диаграмме щелкните поле заголовка диаграммы.

Шаг 2 — На листе в строке формул введите знак равенства (=). Выберите ячейку листа, которая содержит текст, который вы хотите использовать в качестве заголовка диаграммы. Нажмите Ввод.

Название диаграммы изменяется на текст, содержащийся в связанной ячейке.

Когда вы измените текст в связанной ячейке, название диаграммы изменится.

Метки данных

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

Рассмотрим круговую диаграмму, как показано на рисунке ниже.

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

Теперь давайте добавим метки данных в круговую диаграмму.

Шаг 1 — Нажмите на диаграмму.

Шаг 2 — Нажмите Значок элементов диаграммы.

Шаг 3 — Выберите метки данных из списка элементов диаграммы. Метки данных появляются в каждом из секторов.

Из меток данных на графике мы можем легко понять, что Mystery обеспечил 32%, а Classics — 27% от общего объема продаж.

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

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

Шаг 5 — Укажите на каждом из вариантов, чтобы увидеть, как метки данных будут расположены на вашем графике. Например, укажите на выноску данных.

Метки данных размещаются вне круговых фрагментов в выноске.

Таблица данных

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

Шаг 1 — Нажмите на график.

Шаг 2 — Нажмите Значок элементов диаграммы.

Шаг 3 — Из списка выберите Таблица данных. Таблица данных появляется под диаграммой. Горизонтальная ось заменяется строкой заголовка таблицы данных.

В гистограммах таблица данных не заменяет ось диаграммы, а выравнивается по диаграмме.

Панель ошибок

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

Вы можете добавить столбцы ошибок в ряд данных в двухмерной области, столбце, столбце, линии, xy (разброс) и пузырьковых диаграммах.

Чтобы добавить строки ошибок, выполните следующие действия:

Шаг 1 — Нажмите на диаграмму.

Шаг 2 — Нажмите Значок элементов диаграммы.

Шаг 3 — Из списка выберите Бары ошибок. Нажмите на значок, чтобы увидеть опции, доступные для панели ошибок.

Шаг 4 — Нажмите Дополнительные параметры… в отображаемом списке. Откроется небольшое окно для добавления серии.

Шаг 5 — Выберите серию. Нажмите ОК.

Строки ошибок появятся для выбранной серии.

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

Для диаграмм XY (точечная) и пузырьковых диаграмм можно отображать полосы ошибок для значений X, Y и обоих.

Сетки

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

Линии сетки проходят от любых горизонтальных и вертикальных осей через область графика.

Вы также можете отображать линии сетки глубины на трехмерных графиках.

Линии сетки проходят от любых горизонтальных и вертикальных осей через область графика.

Вы также можете отображать линии сетки глубины на трехмерных графиках.

Чтобы вставить линии сетки —

Шаг 1 — Нажмите на трехмерный столбец диаграммы.

Шаг 2 — Нажмите Значок элементов диаграммы.

Шаг 3 — Из списка выберите Бары ошибок. Нажмите на значок, чтобы увидеть опции, доступные для линий сетки.

Шаг 4 — Выберите Первичную мажорную горизонталь, Первичную мажорную вертикаль и Глубину мажор из отображаемого списка.

Выбранные линии сетки будут отображены на графике.

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

легенда

При создании диаграммы легенда появляется по умолчанию.

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

Trendline

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

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

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

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

Способ 1: Выбор таблицы для диаграммы

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

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

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

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

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

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

Способ 2: Ручной ввод данных

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

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

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

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

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

Программа MS Office Excel 2010 предоставляет мощные средства для создания диаграмм. В данной статье мы разберем на примерах, как сделать график в Excel 2010, как создаются гистограммы и некоторые другие наиболее часто используемые диаграммы.

Создание графика в Excel 2010

Для начала запустим Excel 2010. Поскольку любая диаграмма использует данные для построения, создадим таблицу с примером данных.

Ячейки с N по AC содержат, собственно, значения, по которым будет строиться график.

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

В результате график должен выглядеть, как показано на рисунке 6:

Как видите, все достаточно просто.

Теперь расскажем о дополнительных возможностях по работе с графиками в Excel.

Откроется пугающее, на первый взгляд, окно (рис. 7):

Откроется немного отличающееся от предыдущего окно (рис. 8):

После внесенных нами изменений график должен выглядеть вот так (рис. 9):

В результате новая диаграмма с двумя графиками должна принять вид, как показано на рисунке 13:

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

Создание гистограммы в Excel 2010.

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

Создастся график, как на рисунке 15:

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

В результате новая гистограмма с двумя рядами должна принять вид, как показано на рисунке 16:

Создание круговой диаграммы в Excel 2010.

Круговая диаграмма показывает вклад каждой составляющей в общее целое. Например, в чьем-нибудь дневном рационе хлеб составляет 20%, молоко 30%, яйца 15%, сыр 25% и масло 10%. Чтобы создать соответствующую круговую диаграмму нужно таблицу следующего вида:

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

Добавится диаграмма, как на рисунке 19:

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

На диаграмму добавятся названия областей и их процентный вклад:

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

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

На этом краткий обзор возможностей программы Excel по работе с диаграммами завершен.

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

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

Зачем добавлять подписи в графики Excel?

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

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

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

Этот график был создан с использованием следующего набора данных.

Как создать текст заголовка в Excel

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

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

На английском эта формула записывается так:

Затем мы можем использовать формулу в ячейке D3 с комбинацией ИНДЕКС и ПОИСКПОЗ, чтобы вернуть название этого продукта.

Запись формулы для английской версии Excel:

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

Как добавить подписи к графику Excel

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

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

Нажмите клавишу ввода.

Текст заголовка отображается в текстовом поле и может быть перемещён и изменён в нужное вам положение на диаграмме.

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

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