Подписи для точечной диаграммы в EXCEL
В качестве подписей данных в точечной диаграмме (XY Scatter) можно установить: имя ряда, значения Х и значения Y. Однако, часто требуется присвоить индивидуальную подпись для каждой точки. Если у Вас MS EXCEL 2013 для Windows или более поздняя версия, то волноваться нечего — индивидуальные подписи можно установить стандартными средствами. В противном случае придется использовать VBA.
Пусть имеется исходная таблица с координатами точек. Построим на ее основе точечную диаграмму (XY Scatter) в MS EXCEL 2010.
СОВЕТ: Об основах построения диаграмм можно прочитать статью Основы построения диаграмм в MS EXCEL .
СОВЕТ: Про точечную диаграмму можно прочитать в статье Основные типы диаграмм в MS EXCEL .
Выделим любую точку на диаграмме. В меню Работа с диаграммами , во вкладке Макет , в группе Подписи выберите Подписи данных/ Дополнительные параметры подписей данных .
Как видно из картинки выше, в качестве подписи для точки можно установить имя ряда, значения Х и значения Y . Но, нам требуется присвоить индивидуальную подпись для каждой точки.
Для этого напишем Макрос. Для подписей к точкам макрос будет использовать данные из столбца, расположенного левее значений Х. Подробности работы макроса можно посмотреть в файле примера .
Нажав на листе кнопку «Изменить подписи к точкам» на диаграмме появятся подписи точек из столбца А .
2. Типы диаграмм в MS Excel
Microsoft Excel поддерживает различные типы диаграмм, позволяя представлять данные понятным для конкретной аудитории способом.
Гистограммы
Данные в столбцах или строках листа можно отобразить в виде гистограммы. Гистограммы полезны для представления изменений данных с течением времени и для наглядного сравнения различных величин. В гистограммах категории обычно формируются по горизонтальной оси, а значения — по вертикальной.
Данные, расположенные в столбцах или строках листа, можно представить в виде графика. Графики позволяют изображать непрерывное изменение данных с течением времени в едином масштабе и идеально подходят для представления тенденций изменения данных с равными интервалами.
На графиках данные категорий равномерно распределяются вдоль горизонтальной оси, а все значения равномерно распределяются вдоль вертикальной оси.
Графики можно использовать, если метки категорий являются текстовыми и представляют значения, разделённые равными интервалами, например месяцы, кварталы или финансовые годы. Это особенно важно при наличии нескольких рядов: для отображения одного ряда можно использовать точечную диаграмму. Также графики можно использовать при наличии нескольких разделённых равными интервалами числовых меток, в частности лет. Если числовых меток больше десяти, вместо графика лучше использовать точечную диаграмму.
Круговые диаграммы
Данные в одном столбце или строке листа можно представить в виде круговой диаграммы. Круговая диаграмма демонстрирует размер элементов одного ряда данных относительно суммы элементов. Точки данных на круговой диаграмме выводятся как проценты от всего круга.
Линейчатые диаграммы
Данные в столбцах или строках листа можно представить в виде линейчатой диаграммы. Линейчатые диаграммы используют для сравнения отдельных элементов.
Диаграммы с областями
Данные в столбцах или строках листа можно представить в виде диаграммы с областями. Диаграммы с областями подчёркивают величину изменений с течением времени и могут использоваться для привлечения внимания к суммарному значению в соответствии с тенденцией. Например, данные, отражающие прибыль в зависимости от времени, можно отобразить на диаграмме с областями для привлечения внимания к общей прибыли.
Точечные диаграммы
Данные в столбцах и строках листа можно представить в виде точечной диаграммы. Точечная диаграмма показывает отношения между численными значениями в нескольких рядах данных или отображает две группы чисел как один ряд координат x и y .
Точечная диаграмма имеет две оси значений, при этом одни числовые значения выводятся вдоль горизонтальной оси (оси X), а другие — вдоль вертикальной оси (оси Y). На точечной диаграмме эти значения объединяются в одну точку и выводятся через неравные интервалы или кластеры.
Точечные диаграммы обычно используются для иллюстрации и сравнения числовых значений, например научных, статистических или технических данных.
Биржевые диаграммы
Данные, расположенные в столбцах или строках листа в определённом порядке, можно представить в виде биржевой диаграммы.
Как следует из названия, биржевые диаграммы чаще всего используются для иллюстрации изменений цен на акции.
Однако их также можно использовать для вывода научных данных.
Например, с помощью биржевой диаграммы можно представить дневные или годичные колебания температуры.
Поверхностные диаграммы
Данные в столбцах или строках листа можно представить в виде поверхностной диаграммы.
Поверхностная диаграмма полезна, если требуется найти оптимальные комбинации данных из двух наборов.
Как на топографической карте, области, относящиеся к одинаковым диапазонам, при этом выделяются цветами и штриховкой.
Поверхностные диаграммы можно использовать для иллюстрации категорий и наборов данных, представляющих собой числовые значения.
Кольцевые диаграммы
Данные, расположенные только в столбцах или строках листа, можно представить в виде кольцевой диаграммы. Как и круговая диаграмма, кольцевая диаграмма демонстрирует отношение частей к целому, но может содержать более одного ряда данных.
Пузырьковые диаграммы
На пузырьковой диаграмме можно отобразить данные столбцов листа, при этом значения по оси X выбираются из первого столбца данных, а соответствующие значения по оси Y и значения, определяющие размер пузырьков, выбираются из соседних столбцов.
Пузырьковая диаграмма (Bubble Chart)
Большинство из тех, кто хоть раз строил графики в Microsoft Excel или PowerPoint, замечали необычный и забавный тип диаграмм — пузырьковые (bubble chart). Многие видели их в чужих файлах или презентациях. Однако, в 99 случаев из 100, при попытке самостоятельно построить такую диаграмму впервые, пользователи сталкиваются с рядом неочевидных сложностей. Обычно Excel или отказывается ее создавать вообще, или создает, но совершенно непонятного вида, без подписей и наглядности. Давайте разберемся в этой теме.
Что такое пузырьковая диаграмма
Пузырьковая диаграмма — это специфический тип диаграмм, способных отображать трехмерные данные в двумерном пространстве. Рассмотрим для примера вот такую диаграмму, отображающую статистику по странам с известного сайта-конструктора диаграмм http://www.gapminder.org/ : Скачать полноразмерный PDF можно отсюда http://www.gapminder.org/downloads/gapminder-world-map/ По горизонтальной оси Х откладывается средний годовой доход на душу населения в USD. По вертикальной оси Y откладывается средняя продолжительность жизни в годах. Размер же (диаметр или площадь) каждого пузырька пропорционален населению каждой страны. Таким образом, на одной плоской диаграмме удается отобразить трехмерную информацию. Дополнительную информационную нагрузку несет еще и цвет, отображающий региональную принадлежность каждой страны к определенному континенту.
Как построить пузырьковую диаграмму в Excel
- Параметр для откладывания по оси Х
- Параметр для откладывания по оси Y
- Параметр, определяющий размер пузырька
Возьмем для примера вот такую таблицу с данными по игровым приставкам:
Чтобы построить по ней пузырьковую диаграмму, нужно выделить диапазон C3:E8 (строго — только оранжевые и серые ячейки без столбца с названиями) и затем:
- В Excel 2007/2010 — перейти на вкладку Вставка— группа Диаграммы—Другие—Пузырьковая (Insert — Chart — Bubble)
- В Excel 2003 и старше — выбрать в меню Вставка — Диаграмма — Пузырьковая (Insert — Chart — Bubble)
Получившаяся в итоге диаграмма будет отображать быстродействие приставок по оси X, число программ для них по оси Y и долю рынка, занимаемого каждой приставкой — как размер пузырька:
После создания пузырьковой диаграммы имеет смысл настроить подписи к осям — без названий осей трудно понять, что по какой из них откладывается. В Excel 2007/2010 это можно сделать на вкладке Макет (Layout) , а в старых версиях Excel — щелкнув по диаграмме правой кнопкой мыши и выбрав команду Параметры диаграммы (Chart options) — вкладка Заголовки (Titles) .
К сожалению, Excel не позволяет автоматически привязывать цвет пузырьков к исходным данным (как в примере выше со странами), но для наглядности можно быстро отформатировать все пузырьки в разные цвета. Для этого щелкните правой кнопкой мыши на любом пузырьке, выберите команду Формат ряда данных (Format series) из контекстного меню и включите опцию Разноцветные точки (Vary colors) .
Проблема с подписями
Общей трудностью, с которой сталкиваются абсолютно все пользователи при построении пузырьковых (и точечных, кстати, тоже) диаграмм, являются подписи к пузырькам. Стандартными средствами Excel можно вывести в качестве подписей только значения X, Y, размер пузырька или называние ряда (общее для всех). Если вспомнить что при построении пузырьковой диаграммы вы не выделяли столбец с подписями, а только три столбца с данными X, Y и размер пузырьков, то все оказывается в общем-то логично: то, что не выделено — в диаграмму само никак попасть не может.
Решить проблему подписей можно тремя путями:
Способ 1. Вручную
Вручную переименовывать (менять) подписи для каждого пузырька. Можно просто щелкнуть мышью по контейнеру с подписью и ввести с клавиатуры новое название вместо старого. Очевидно, что при большом количестве пузырьков этот способ начинает напоминать мазохизм.
Способ 2. Надстройка XYChartLabeler
Нетрудно предположить, что с подобной проблемой до нас с вами сталкивались и другие пользователи Excel. И один из них, а именно легендарный Rob Bovey (дай бог ему здоровья) написал и выложил в открытый доступ бесплатную надстройку XYChartLabeler, добавляющую в Excel эту недостающую функцию.
После установки у вас появится новая вкладка (в старых версиях Excel — панель инструментов) XY Chart Labels:
Выделив пузырьки и воспользовавшись кнопкой Add Labels можно быстро и удобно добавить подписи сразу ко всем пузырькам в диаграмме, просто задав диапазон ячеек с текстом для подписей:
Способ 3. Excel 2013
В новой версии Microsoft Excel 2013 появилась наконец-таки возможность добавлять подписи к элементам данных диаграммы из любых произвольно выделенных ячеек. Дождались 🙂
Изменение внешнего вида текста и меток диаграммы в Numbers на Mac
Можно изменить внешний вид текста на диаграмме, применив другой стиль, изменив шрифт, добавив границу и настроив другие параметры.
Если отредактировать диаграмму не удается, скорее всего, она закреплена и Вам нужно открепить ее.
Изменение шрифта, стиля и размера текста на диаграмме
Можно изменить внешний вид сразу всего текста на диаграмме.
- Нажмите диаграмму, затем в боковой панели «Формат» нажмите вкладку «Диаграмма».
- С помощью элементов управления в разделе «Шрифт диаграммы» в боковой панели выполните одно из указанных ниже действий.
- Изменение шрифта. Нажмите всплывающее меню «Шрифт диаграммы» и выберите шрифт.
- Изменение стиля символов. Нажмите всплывающее меню под именем шрифта и выберите вариант («Обычный», «Жирный» и т. д.).
- Увеличение или уменьшение шрифта. Нажмите маленькую букву A или большую букву A. Весь текст на диаграмме увеличивается или уменьшается пропорционально (на одинаковый процент).
В следующих разделах описано, как изменить стиль заголовка диаграммы и меток значений, чтобы выделить их относительно другого текста.
Редактирование заголовка диаграммы
Диаграммы имеют заголовок-заполнитель (Заголовок), который по умолчанию скрыт. Можно показать его и изменять по собственному желанию.
- Нажмите диаграмму.
- В боковой панели «Формат» нажмите вкладку «Диаграмма», затем нажмите кнопку «Заголовок».
- Дважды нажмите заголовок-заполнитель диаграммы и введите собственный заголовок.
- Для изменения внешнего вида заголовка (например, шрифта, размера и цвета) снова дважды нажмите заголовок и внесите требуемые изменения с помощью элементов управления из раздела «Заголовок диаграммы» боковой панели. Чтобы переместить заголовок в центр кольцевой диаграммы, нажмите всплывающее меню «Позиция заголовка» и выберите вариант «По центру».
Добавление и изменение меток значений
На диаграммах есть метки, в которых отображаются значения конкретных точек данных. Можно обозначить их формат (например, числа, значения валют или процентные значения), изменить стиль и место их отображения и т. д.
- Нажмите диаграмму, затем в боковой панели «Формат» выполните одно из указанных ниже действий.
- Круговая или кольцевая диаграммы. Нажмите вкладку «Сегменты».
- Другие типы диаграмм. Нажмите вкладку «Серии».
- Чтобы добавить метки значений и выбрать формат значений (например, «Число», «Валюта» или «Проценты»), выполните одно из перечисленных ниже действий.
- Круговая или кольцевая диаграммы. Установите флажок «Значения», затем нажмите стрелку раскрытия рядом со всплывающим меню «Формат данных значений» и выберите один из вариантов. Также можно включить отображение меток данных в круговых и кольцевых диаграммах, установив флажок «Названия точек данных».
- Пузырьковая диаграмма. Нажмите стрелку раскрытия рядом с элементом «Метки пузырьков», установите флажок «Значения», затем нажмите всплывающее меню «Формат данных значений» и выберите один из вариантов.
- Диаграмма рассеивания. Нажмите стрелку раскрытия рядом с элементом «Метки значений», установите флажок «Значения», затем нажмите всплывающее меню «Формат данных значений» и выберите один из вариантов.
- Другие типы диаграмм. Нажмите стрелку раскрытия рядом с параметром «Метки значений», затем нажмите всплывающее меню ниже и выберите один из вариантов.
Если Вы хотите, чтобы формат меток значений совпадал с форматом исходных данных в таблице, выберите «Как источник».
Совет. Чтобы добавить метку значения только к одному объекту в диаграмме (например, к одному сегменту круговой диаграммы), сначала выберите объект, затем добавьте метку значения.
- Задание количества знаков после запятой. Нажмите стрелку вверх или вниз.
- Выбор отображения отрицательных чисел. Выберите «-100» или «(100)».
- Отображение разделителя групп разрядов. Установите флажок «Разделитель тысяч».
- Добавление суффикса или префикса. Введите текст. Он будет добавлен в начало или конец метки.
- Выбор места для меток. Нажмите всплывающее меню «Размещение» и выберите требуемый вариант (например, «Сверху», «По центру», «Внутри» и т. д.; доступные варианты зависят от типа диаграммы). Когда создается диаграмма, к меткам значений автоматически применяется функция «Автоподбор» во избежание наложения. Чтобы просмотреть все метки значений, снимите флажок «Автоподбор». (Параметр «Автоподбор» доступен не для всех диаграмм.)
Примечание. Шрифт всех меток меняется при изменении параметра «Шрифт диаграммы» во вкладке «Диаграмма» боковой панели «Формат».
- Изменение положения меток. Задайте положение меток с помощью бегунка «Расстояние от центра». Чтобы избежать наложения меток, их следует переместить подальше от центра диаграммы.
- Добавление выносных линий. Установите флажок «Линии выноски». Можно изменить тип линии, цвет и толщину линий выноски, а также добавить их конечные точки.
- Выбор стиля выносной линии. Нажмите всплывающее меню и выберите «Прямая» или «Косая». При выборе косых линий выноски сами выноски будут выровнены в столбцы, как показано ниже.
Добавление сводных меток
При работе со сложенными диаграммами (столбчатой, полосчатой или площадной) можно добавлять сводные метки для отображения суммы над каждым элементом со сложенными данными.
- Нажмите сложенную диаграмму, затем в боковой панели «Формат» нажмите вкладку «Серии».
- В разделе «Сводные метки» нажмите всплывающее меню и выберите формат чисел для данной метки. Включите параметр «Как источник», чтобы использовать такой же формат чисел, как в данных диаграммы.
- Чтобы точно настроить отображение значений сводных меток, выберите нужные параметры под всплывающим меню «Сводные метки». Эти параметры будут различаться в зависимости от выбранного формата сводных меток. Например, если выбран формат «Валюта», можно выбрать количество знаков после запятой, способ отображения отрицательных значений (в скобках или со знаком «минус»), а также формат валюты.
- Для добавления префикса или суффикса к каждой сводной метке, введите текст, который необходимо добавить, в соответствующие поля в разделах «Префикс» или «Суффикс».
- Чтобы изменить шрифт, цвет или стиль сводных меток, нажмите любую сводную метку на диаграмме и внесите необходимые изменения с помощью элементов управления в разделе «Шрифт» боковой панели «Формат» .
Примечание. Шрифт всех меток меняется при изменении параметра «Шрифт диаграммы» во вкладке «Диаграмма» боковой панели «Формат».
Изменение меток осей
Можно выбрать метки, отображаемые на оси, отредактировать их имена и изменить угол их ориентации.
- Нажмите диаграмму, затем в боковой панели «Формат» нажмите вкладку «Оси».
- Выполните любое из описанных ниже действий.
- Чтобы изменить деления на оси значений: Нажмите кнопку «Значение (Y)» вверху боковой панели.
- Изменение делений на оси категорий. Нажмите кнопку «Категория (X)» вверху боковой панели.
- Используя элементы управления в боковой панели, внесите корректировки. Чтобы увидеть все параметры, нажимайте стрелку раскрытия слева от заголовков секций.
- Если Вы включили режим «Имя оси» и хотите изменить имя оси на диаграмме, выберите диаграмму, дважды коснитесь имени оси и введите пользовательское имя.
Чтобы изменить шрифт, цвет и стиль меток осей, нажмите любую метку оси на диаграмме и используйте элементы управления в разделе «Шрифт» в боковой панели.
Редактирование меток данных сводной диаграммы
Можно изменить метки, отображающиеся в сводной диаграмме. Например, можно отобразить имена групп из сводной таблицы на оси X. Чтобы узнать, как создать сводную диаграмму на основе сводной таблицы, см. Выбор ячеек в сводной таблице для создания сводной диаграммы.
- Выберите сводную диаграмму, которую Вы хотите изменить.
- На вкладке «Диаграмма» боковой панели «Формат» нажмите всплывающее меню «Подписи сводных данных», затем выберите имена, которые нужно отобразить, или выберите «Скрыть все имена».
Примечание. Варианты в меню «Метки сводных данных» могут меняться в зависимости от полей сводной таблицы.
Примечание. Параметры осей для диаграмм рассеивания и пузырьковых диаграмм могут различаться.
О добавлении подписи или заголовка к диаграмме см. в разделе Добавление подписей или заголовков к объектам в Numbers на Mac.