Недавно у меня возникла задача – отразить на графике уровень компетенций сотрудников. Как вы, возможно, знаете, изменить текст подписей оси значений невозможно, поскольку они всегда генерируются из чисел, обозначающих шкалу ряда. Можно управлять форматированием подписей, однако их содержимое жестко определено правилами Excel. Я же хотел, чтобы вместо 20%, 40% и т.п. на графике выводились названия уровней владения компетенциями, что-то типа:

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

Метод решения подсказала мне идея, почерпнутая в книге Джона Уокенбаха «Диаграммы в Excel»:

При создании диаграммы, показанной на рис. 1 использовались следующие принципы:

• Диаграмма фактически является смешанной: в ней сочетаются график и точечная диаграмма.
• «Настоящая» ось значений скрыта. Вместо нее выводится ряд точечной диаграммы, отформатированный таким образом, чтобы выглядеть как ось (фиктивная ось).
• Данные точечной диаграммы находятся в диапазоне А12:В18. Ось Y для этого ряда представляет числовые оценки каждого уровня компетенций, например, 40% для «базового».
• Подписи оси Y являются пользовательскими подписями данных ряда точечной диаграммы, а не подписями оси!

Чтобы лучше понять принцип действия фиктивной оси, посмотрите на рис. 2. Это стандартная точечная диаграмма, в которой точки данных соединены линиями, а маркеры ряда имитируют горизонтальные метки делений. В диаграмме используются точки данных, определенные в диапазоне А2:В5. Все значения Х одинаковы (равны нулю), поэтому ряд выводится как вертикальная линия. «Метки делений оси» имитируются пользовательскими подписями данных. Для того, чтобы вставить символы в подписи данных, нужно последовательно выделить каждую подпись по отдельности и применить вставку спецсимвола, пройдя по меню Вставка — Символ (рис. 3).

Рис. 2. Пример отформатированной точечной диаграммы.

Рис. 3. Вставка символа в подпись данных.

Давайте теперь рассмотрим шаги создания диаграммы, приведенной на рис. 1.

Выделите диапазон А1:С7 и постройте стандартную гистограмму с группировкой:

Разместите легенду сверху, добавьте заголовок, задайте фиксированные параметры оси значений: минимум (ноль), максимум (1), цену основных делений (0,2):

Выделите диапазон А12:В18 (рис. 1), скопируйте его в буфер памяти. Выделите диаграмму, перейдите на вкладку Главная и выберите команду Вставить->Специальная вставка.

Установите переключатели новые ряды и Значения (Y) в столбцах. Установите флажки Имена рядов в первой строке и Категории (подписи оси Х) в первом столбце.

Выделите новый ряд и правой кнопкой мыши выберите Изменить тип диаграммы для ряда. Задайте тип диаграммы «Точечная с гладкими кривыми и маркерами»:

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

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

Выделите ряд точечной диаграммы и отформатируйте его. Цвет линии – черный, маркер – похожий на деления оси (выберите размер маркера и задайте черный цвет), толщину линии подберите так, чтобы она не отличалась от горизонтальной линии, добавьте подписи данных (всё равно, какие). Отформатируйте подписи данных, указав на вкладке Параметры подписи – Слева.

Войдите в легенду, выделите и удалите описание ряда, относящегося к точечной диаграмме.

Выделяйте по очереди подписи данных ряда точечной диаграммы и (как показано на рис. 3) напечатайте в них те слова, которые хотели (область С13:С18 рис. 1).

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

Построим диаграмму MS EXCEL в форме гистограммы, отобразим подписи данных (см. файл примера ).

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

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

Нажмите Добавить.

Здесь мы применили условный формат (значения >350 выводятся в числовом формате, остальные значения заменяются пробелом).

При частой смене порогового значения этот прием не очень удобен, т.к. приходится вручную изменять формат.

Если значение выше порогового значения, то формула выводит его, если нет, то возвращается 0. Добавим в диаграмму этот ряд и установим перекрытие рядов без зазора.

Необходимо изменить цвет гистограммы дополнительного ряда с зеленого на синий, а формат подписей данных изменить на # ##0;# ##0; (чтобы не отображались нулевые значения).

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

Excel для Office 365 Word для Office 365 Outlook для Office 365 PowerPoint для Office 365 Excel для Office 365 для Mac Word для Office 365 для Mac PowerPoint для Office 365 для Mac Excel Online Excel 2019 Word 2019 Outlook 2019 PowerPoint 2019 Excel 2016 Excel 2019 для Mac PowerPoint 2019 для Mac Word 2019 для Mac Word 2016 Outlook 2016 PowerPoint 2016 Excel 2013 Word 2013 Outlook 2013 PowerPoint 2013 Excel 2010 Word 2010 Outlook 2010 PowerPoint 2010 Excel 2007 Word 2007 Excel 2016 для Mac PowerPoint 2016 для Mac Word 2016 для Mac Excel Starter 2010 Word Starter 2010 Меньше

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

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

Чтобы перейти в соответствующий раздел, щелкните один из четырех значков, показанных ниже (Заливка и границы , Эффекты , Размер и свойства (Макет и свойства в Outlook или Word) или Параметры подписи ).

Совет: Убедитесь, что выбрана только одна метка данных. Чтобы быстро применить пользовательское форматирование к другим точкам ряда данных, выберите пункты Параметры подписи > Метка рядов данных > Создать копию текущей метки .

Соединение подписей данных с точками данных с помощью линий выноски

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

Изменение вида линий выноски

Изменение вида подписей данных

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

Изменение формы подписей данных

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

Изменение размера подписей данных

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

Совет: Вы можете настроить другие параметры размера (в Excel и PowerPoint) и выравнивания на вкладке Размер и свойства (Макет и свойства в Outlook или Word). Для этого дважды щелкните подпись данных и выберите Размер и свойства .

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

Текст встречается в следующих элементах диаграммы:

• заголовок диаграммы;
• заголовки осей;
• легенда;
• подписи делений оси;
• подписи данных;
• таблицы данных.

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

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

Для изменения текста заголовков можно выполнить команду Диаграмма/Параметры диаграммы и во вкладке Заголовки диалогового окна Параметры диаграммы (см. рис. 28.10) внести необходимые изменения.

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

Наконец, можно сделать так, чтобы текст заголовков диаграммы формировался из ячеек листа. Для этого следует выделить заголовок, поставить курсор в строку формул, ввести с клавиатуры знак = (равно), мышью выделить ячейку с текстом заголовка и подтвердить ввод. Например, на рис. 28.22 показано формирование заголовка диаграммы из текста ячейки А1 . В дальнейшем при изменении содержимого ячейки А1 будет автоматически изменяться текст заголовка.

Рис. 28.22.

Следует отметить, что в дальнейшем такой текст можно изменить только во вкладке Заголовки диалогового окна Параметры диаграммы (см. рис. 28.10), при этом связь заголовка с ячейкой разорвется. Непосредственно на диаграмме текст изменить нельзя.

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

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

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

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

Основные параметры шрифта (шрифт, размер, цвет, начертание, простое подчеркивание) элементов диаграммы можно изменять также с использованием элементов панели инструментов Форматирование . Если выделена диаграмма, то будут изменяться параметры шрифта сразу всех элементов. Если же выделен один элемент, то только параметры шрифта этого элемента.

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

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

Для отключения этого режима и установки необходимой ориентации следует во вкладке Выравнивание диалогового окна Формат оси (рис. 28.24) щелкнуть по метке нужного угла поворота в поле, где написано слово Надпись градусов Текст .

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

Для изменения ориентации во вкладке Выравнивание (рис. 28.25) соответствующего диалогового окна (Формат названия диаграммы , Формат названия оси , Формат подписей данных ) можно щелкнуть по метке нужного угла поворота в поле, где написано слово Надпись , или установить значение в счетчике градусов . Для изменения ориентации на вертикальную "столбиком" следует щелкнуть в поле, где написано слово Текст .

Ориентацию текста в легенде изменить нельзя.

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

Для изменения расположения подписей данных на гистограммах, линейчатых диаграммах, точечных диаграммах и графиках следует дважды щелкнуть по подписям данных левой кнопкой мыши или выделить подписи данных и выполнить команду Формат/Выделенные подписи данных . Во вкладке Выравнивание диалогового окна Формат подписей данных (рис. 28.26) в раскрывающемся списке Положение подписи можно выбрать необходимое расположение.

Для гистограмм и линейчатых диаграмм можно выбрать: У вершины, снаружи ; У вершины, внутри ; В центре ; У основания (