Как сделать диаграмму в excel, используя данные из таблицы?

image

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

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

Содержание:

  • Как в excel построить диаграмму.
  • Как в excel строить графики.
  • Как в excel построить гистограмму.
  • Каким образом просто защитить в excel книгу и лист.
  • Как в excel просто запрограммировать выделение цветом заданного значения.
  • Как  закрепить столбцы и строки при «прокрутке файла».

Как в excel построить диаграмму

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

image
рис 1.

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

Для этого мы делаем следующие простые шаги:

  1. На верхней панели листа excel ищем окошко «ВСТАВКА» (указал красной стрелкой)
  2. Выделяем нужный нам диапазон данных.

см. рис 2.

рис .2

Далее, жмем на окно диаграммы, выбираем, например,  «круговая». См. рис 3.

рис.3

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

рис.4

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

рис.5

На полученной диаграмме, правой клавишей мышки открываем новое окно, жмем на «выбрать данные», см. рис. 6

рис.6

У нас открывается окно, где мы изменяем диапазон данных, см рис 7 и 8

рис 7

Что бы получить данные в процентах, нужно увеличить диапазон данных, то есть выделить столбец А и В.

рис. 8 изменение диапазона данных

Таким образом мы получаем диаграмму с процентами по долям, см. рис 9

рис 9

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

Как в excel строить графики

Здесь также все просто. В верхней панели выбираем «ВСТАВКА», рядом с диаграммами, есть графики. (В последней версии excel можно воспользоваться поиском.) Вариации графиков могут быть разные. Я покажу основные, затем вы сами легко разберетесь в деталях.

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

рис 10

Выделяем нужный диапазон за полгода, жмем на окошко «график». Выбираем вид графика и получаем вариант, где наглядно видны колебания продаж по каждому товару за период. См. рис 11.

рис. 11

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

рис.12

Как в excel построить гистограмму

По той же аналогии можно построить и гистограмму.

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

В той же панели инструментов, «ВСТАВКА», рядом с графиками и диаграммами, есть гистограмма. Рис 13.

рис.13

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

рис 14. Гистограмма

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

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

рис 15. Линейчатая диаграмма.

Как в excel защитить информацию

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

Проще всего защитить доступ к самому файлу. Это делается просто.

Итак, у нас есть файл excel, который требуется защитить паролем.

рис 16

Выбираем:  Файл — сведения — защитить книгу — зашифровать паролем. ( см. рис. 17)

рис. 17

У нас появится окошко для пароля. Эксель запросит в этом окошке ввести пароль еще раз.

рис 18

Теперь, что бы открыть этот документ после его закрытия, появится окно, где потребуется ввести пароль. См. рис 19.

рис 19.

После ввода пароля, ваш файл успешно откроется.

Как в excel защитить лист

Теперь рассмотрим, как в excel защитить лист. Здесь также все не сложно. Берем наш исходный документ, где на листе-2 наша информация по продажам. Правой клавишей мышки нажимаем на ЛИСТ 2. Открывается окошко, где мы выбираем «защитить лист».

рис 20

Ecxel нам предложит ввести пароль два раза. См рис 21.

рис 21

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

рис. 22

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

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

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

В excel заданное значение будет высвечиваться автоматически.

Возьмем небольшую таблицу, с товарными остатками, см рис 23.

рис.23

Итак, нам требуется увидеть, по каким товарам есть остатки выше верхней границы. Например, более 250 коробов. Мы делаем следующую простую операцию:

Главная — условное форматирование — правило выделения ячеек. Выбираем «БОЛЬШЕ».

рис 24

В левой части окошка задаем верхний порог 250 коробов. В правой части мы выбираем цвет, который нам будет сигнализировать об излишках по товару. Пусть цвет будет красным. Жмем Ок.

рис 25

Нам автоматически подсветило красным ту цифру, где остаток выше 250 коробов. В нашем случае, это 500 коробов. См. рис 25.

Теперь то же самое можно сделать с нижним порогом. Я взял, все, что меньше 20 коробов. Цвет выбрал желто-песочный. См. рис 26.

рис.26

Жмем Ок. Мы получили желаемую помощь для визуального определения. См. рис. 27.

рис.27

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

Как в excel закрепить строку при прокрутке

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

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

рис.28

Файл — Вид — Закрепить области, см рис. 29.

рис.29

Если нам требуется закрепить столбцы А и В для прокрутки вправо, только выделяем третий столбец С, в окошке «закрепить области» выбираем «закрепить области листа».

рис.30

Все. Готово. Прокручиваем вправо. Как мы видим, столбцы С и D скрыты, и нам открылись крайние правые столбцы, см. рис 31.

рис. 31

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

Заключение

Мы разобрали темы, как в excel строить графики, диаграммы, гистограммы, как защитить лист excel и так далее. Надеюсь, информация была полезной. Если по теме было не все до конца понятно, пишите в комментариях, постараюсь ответить. А также, если тема excel будет востребована, возможно сделаю на каждую функцию отдельный видео-обзор.

Всего Вам хорошего и успехов!

Автор книги «Мастерство визуализации данных» и создатель блога data.vis Ольга Базалева поделилась приемами создания профессиональных диаграмм в Excel.

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

Прием 1: задать перекрытие столбцов на диаграмме

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

Как сделать:

  1. Клик правой кнопкой мыши — ПКМ — по любому столбцу диаграммы;
  2. Формат ряда данных;
  3. Перекрытие рядов.

Чтобы одни столбцы полностью перекрывали другие, выбирайте перекрытие 100%. Можно настроить другое соотношение перекрытия, например, 70%.

Пример перекрытия на 70%

Прием 2: уменьшить расстояние между столбцами

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

Одна и та же диаграмма со стандартным и уменьшенным расстоянием между столбцами

Как сделать:

  1. ПКМ по ряду данных;
  2. Формат ряда данных;
  3. Выбрать боковой зазор в процентах.

Чтобы сохранить визуальную целостность и сделать акцент на данных, выбирайте зазор до 25-50%.

Курс

EXCEL

Узнать больше

  • Учитесь анализировать любые данные и приводить таблицы в порядок
  • Работайте с любыми отчетами и формулами Excel

Прием 3: отобразить среднее скользящее

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

Пример скользящей средней — красной линии на диаграмме

Как сделать:

  1. ПКМ по линии графика;
  2. Добавить линию тренда;
  3. Выбрать скользящее среднее;
  4. Установить период.

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

График посещаемости сайта РИА Новости, среднее скользящее за 21 день
Тот же график, среднее скользящее за 7 дней

Прием 4: оставить одну подпись на несколько диаграмм

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

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

Как сделать:

  1. Построить первую диаграмму;
  2. Расположить следующие поверх первой основной;
  3. Убрать с листа все лишние элементы: подписи рядов, линии сетки, белую заливку области диаграмм.

Подробнее процесс показан в видеоуроке.

Прием 5: добавить вспомогательную ось

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

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

Как сделать:

  1. ПКМ на ряду, который пойдет по вспомогательной оси;
  2. Формат ряда данных;
  3. Параметры ряда;
  4. Построить ряд «По вспомогательной оси».
Благодаря вспомогательной оси считывать информацию легче

Чтобы упростить восприятие информации, сделайте на диаграмме подсказки цветом. На примере все элементы ряда «Выручка», в том числе сами столбцы, название и подписи оси, окрашены в зеленый. Ряд «Средний чек» и его элементы — в красный.

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

Мнение автора и редакции может не совпадать. Хотите написать колонку для «Нетологии»? Читайте наши условия публикации. Чтобы быть в курсе всех новостей и читать новые статьи, присоединяйтесь к Телеграм-каналу Нетологии.

Читать также

Подборка книг и блогов по визуализации данных

Графики и диаграммы – это лучший способ для того, чтобы представить статистику визуально. Такое оформление данных помогает провести самый правильный и точный анализ происходящих событий. И современные технологии позволяют сделать их минут за пять. В этой статье я покажу вам, как произвести построение диаграмм и графиков в Excel. Вам также будет интересно о Microsoft Visio 2016 — профессиональном инструменте для редактирования диаграмм. Об этом рассказано здесь — http://www.word-excel.ru/microsoft-visio.

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

Кроме этого, вам будет интересно узнать о том, как создавать диаграммы в Word.

Построение диаграмм и графиков в Excel – инструкция

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

  1. Откройте Excel с необходимыми данными.
  2. Выделите мышкой область таблицы данных, которую следует отобразить в виде графика.
  3. В верхнем меню выберите вкладку «Вставка» и обратите внимание на блок «Диаграммы». Из предложенных выберите нужный вид графика или диаграммы, просто кликните на него.
    Построение диаграмм и графиков в Excel
  4. Откроется выпадающий список, где вы сможете определить более точно внешний вид будущего графика или диаграммы. Кликните на нужное.
    Построение диаграмм и графиков в Excel
  5. В результате вы получите ещё одно окно, где и будет выведен ваш график. С ним можно делать разные операции, например, распечатать, или скопировать и вставить в Point или другую графическую программу.

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

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

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

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

Чтобы создать такую диаграмму в MS Excel (версии 2007 года и далее), нужно сделать всего 10 шагов.

Что есть что на этой диаграмме? Ключевые сведения показаны с помощью разноцветных линий и планок:

  • линия текущего дня — красная вертикальная линия, которая показывает, где вы находитесь сегодня;
  • сроки задач — серые горизонтальные планки, которые показывают, сколько времени займёт выполнение каждой задачи;
  • готовность задач — зелёные горизонтальные линии, которые показывают, на какой стадии находится выполнение задачи и сколько ещё осталось сделать.

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

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

Шаг 1. Начиная, думайте о результате

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

Шаг 2. Создайте таблицу с данными

Таблица с данными для диаграммы

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

Сформируйте таблицу под графиком. На 30-й строке располагаются заголовки столбцов.

Разберёмся, что означают данные в таблице:

  • Фаза: «Фаза 1», «Фаза 2», «Фаза 3»,«Фаза 4» или «Сегодня». Показывает, какой стадии проекта соответствует событие.
  • Тип: «Фаза», «Задача», «Сегодня». Показывает тип события, разграничивая задачи и фазы исполнения проекта.
  • Дата начала: дата старта события.
  • Событие: показывает наименование события в диаграмме.
  • Сроки: показывает, сколько дней занимает выполнение задачи.
  • Готовность в процентах: демонстрирует степень выполнения для каждой задачи и фазы, 0% — задача не начата, 100% — завершена. Этот показатель для категории «Фаза» не связан с завершённостью задач и выставляется оценочно. Чтобы решить, на сколько процентов завершена та или иная фаза, нужно применять свои собственные правила.
  • Готовность в днях: срок, за который был достигнут показатель готовности события в процентах. У задачи, которая отнимает 10 дней и которая завершена на 50%, этот показатель будет равен пяти дням (10 дней × 50%).
  • Высота: значение высоты важно для оформления, потому что определяет, как высоко или низко будет событие отражаться в карте расписания. Значения можете указывать любые, но лучше брать небольшие числа, приближенные к нулю. В примере использован диапазон от +25 до −25. Если высота положительная, событие отразится на графике выше горизонтальной оси, если отрицательное — ниже оси.

Шаг 3. Создайте диаграмму с осями X и Y, добавьте в неё первые данные из столбца «Сроки»

Диаграмма

Пора создать диаграмму, чтобы визуализировать данные о событиях из таблицы, сделанной на предыдущем этапе. Используйте точечную диаграмму с осями X и Y, потому что она позволяет свободно располагать базовые координаты на обеих осях.

Следуйте инструкции:

  • Выберите в основном меню вкладку «Вставка».
  • Далее выберите «Диаграммы» → «Точечная» → «Точечная  с маркерами», перед вами появится пустая диаграмма.
  • Зацепите края диаграммы и измените её размеры и положение так, чтобы поле диаграммы охватывало диапазон от ячейки B4 до ячейки К26 (нажмите и удерживайте клавишу Alt, чтобы выровнять область диаграммы точно по границам ячеек).
  • Кликните правой кнопкой мышки на диаграмме и выберите пункт меню «Выбрать данные».

Добавьте ряд данных в диапазон, нажимая на кнопку «Добавить» в открывшемся диалоговом окне.

  1. Для изменения названия «Имя ряда» поставьте в соответствующем поле значение ячейки E30 «Сроки».
  2. Для значения оси X выберите соответствующее поле и выделите ячейки C33:C46 «Дата начала».
  3. Для значения оси Y выберите соответствующее поле и выделите ячейки H33:H46 «Высота».
  4. Нажмите ОК, чтобы закрыть окно добавления данных.
  5. Нажмите ОК, чтобы закрыть окно выбора данных.

Эти действия дадут нам простую точечную диаграмму с автоматическим форматированием.

Сделаем маркеры событий зелёными:

  • Правой кнопкой мыши кликните на любом выбранном маркере данных, выберите пункт меняю «Формат ряда данных».
  • В открывшемся списке слева выберите вкладку «Параметры маркера», затем выберите встроенный тип маркера «Кристалл». Увеличьте размер до 10 пт.
  • На вкладке «Заливка маркера» выберите сплошную заливку. Измените цвет заливки на зелёный.

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

Измените название графика: дважды щёлкните на текстовом поле с названием и введите своё наименование.

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

Шаг 4. Приводим в порядок внешний вид диаграммы и добавляем наименования событий

Таблица с форматированием

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

Ось Y. Спрячьте вертикальную ось, потому что она не несёт информационной нагрузки. Выделите диаграмму, в основном меню на вкладке «Работа с диаграммами» выберите пункт «Макет». Затем выберите «Оси» → «Основная вертикальная ось» → «Не показывать».

Горизонтальные направляющие. Они тоже бесполезны. Выделите диаграмму, в основном меню перейдите на вкладку «Макет», затем выберите пункты «Сетка» → «Горизонтальные линии сетки по основной оси» → «Не показывать».

Легенда диаграммы. Мы потом её заменим на более красивую, а пока отключаем: «Макет» → «Легенда» → «Нет».

Напоследок отображаем подписи данных для каждого маркера. В основном меню на вкладке «Макет» выбираем «Подписи данных» → «Слева».

Как изменить данные в подписи маркеров

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

Но этот процесс можно ускорить в четыре раза:

  • Кликните на подпись первого маркера (левый верхний угол диаграммы), чтобы выбрать все значения сразу. Не нажимайте на зелёный маркер, так вы выделите другие объекты!
  • Кликните повторно на подпись маркера, чтобы отредактировать единичное наименование.
  • В поле ввода формулы наберите символ =.
  • Кликните на ячейку D33, в ней записано событие «Фаза 1», нажмите Enter.
  • Повторите первые четыре шага для оставшихся подписей к каждому маркеру.
У каждого маркера своё значение

Как закрепить области

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

Чтобы закрепить области :

  • Выделите ячейку А28.
  • Выберите вкладку «Вид» в основном меню.
  • Выберите «Окно» → «Закрепить области».
  • В выпадающем меню выберите пункт «Закрепить области».

Над 28-й строкой появится чёрная горизонтальная линия. Всё, что находится выше, теперь закреплено, и при прокрутке будут двигаться только нижние строки.

Шаг 5. Добавьте планки погрешностей для отображения сроков, чтобы превратить диаграмму в наглядное расписание

Диаграмма с планками

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

  • Выделите график.
  • В основном меню «Работа с диаграммами» выберите вкладку «Макет».
  • В выпадающем списке в левом верхнем углу (область построения) выберите ряд данных «Сроки».
  • На вкладке «Макет» выберите пункт «Планки погрешностей» → «Дополнительные параметры планок погрешностей». Откроется диалоговое окно.
  • В выпадающем списке в левом верхнем углу выберите элемент форматирования диаграммы «Планки погрешностей по оси X».
  • В диалоговом окне для изменения горизонтальных планок поставьте флажок на значении вывода «Плюс». «Конечный стиль» → «Без точки».
  • В списке «Величина погрешности» выберите пункт «Пользовательские» и нажмите кнопку «Укажите значение». В открывшемся новом окне «Настраивание планки погрешностей» укажите «Положительное значение ошибки», для этого в соответствующем поле поставьте курсор и выделите диапазон ячеек с E33 до E74. Нажмите ОК.

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

  • В окне «Формат планок погрешностей» перейдите к вкладке «Цвет линий». Выберите пункт «Сплошная линия». Сделайте заливку серого цвета.
  • На вкладке «Тип линии» увеличьте ширину линии до 4 пт.

Чтобы лучше ориентироваться в диаграмме, нужно провести тонкие вертикальные линии вниз от маркеров. Для этого в выпадающем списке в левом верхнем углу выберите «Планки погрешности по оси Y». В диалоговом окне отобразятся вертикальные планки погрешностей. Выберите направление «Минус», «Конечный стиль» → «Без точки», «Величина погрешности» → «Относительное значение», введите в это поле значение 100%. Выберите цвет и толщину линий самостоятельно.

Шаг 6. Добавьте значение готовности к диаграмме

Добавление новых данных

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

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

  • Кликните правой кнопкой мыши на области диаграммы, в контекстном меню выберите пункт «Выбрать данные».
  • Нажмите кнопку «Добавить», в появившемся окне определите «Имя ряда», выделив ячейку G30. Диапазон данных для осей X и Y выберите такой же, как в шаге № 3.
  • На диаграмме появятся красные маркеры, которые спрячут под собой зелёные.

Шаг 7. Форматируем показатели новых данных

Чтобы диаграмма отражалась в едином стиле, а данные не наслаивались друг на друга, форматируем маркеры:

  • В выпадающем списке в левом верхнем углу выберите «Ряд Готовность». Сразу под выпадающим списком находится кнопка «Формат выделенного фрагмента». Нажмите её, откроется диалоговое окно.
  • Измените параметры маркера. Выберите тип формы «Кристалл», установите размер 10 пт, заливку маркера выберите сплошную, зелёного цвета. Закройте диалоговое окно.
  • Не снимая выделения маркеров, нажмите в основном меню на вкладке «Макет» кнопку «Подписи данных», выберите в появившемся меню пункт «Не показывать».

Шаг 8. Добавьте планки погрешностей, чтобы отследить процент завершённости задач

Диаграмма с процентом выполнения задач

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

  • Снова выберите «Ряд Готовность» в выпадающем списке слева. Нажмите в основном меню кнопку «Планки погрешностей». Выберите в выпадающем меню пункт «Дополнительные параметры». Откроется диалоговое окно. Установите для вертикальных планок фиксированное значение, вписав в соответствующее поле ноль.
  • Переключитесь на параметры планок, расположенных параллельно оси Х. Установите направление «Плюс». «Конечный стиль» → «Без точки», «Величина погрешности» → «Пользовательские». В поле диалогового окна «Положительное значение ошибки» укажите диапазон ячеек G33-G47.
  • Поменяйте цвет линий на зелёный, выбрав сплошную заливку. Установите ширину линии 7 пт.

Горизонтальные зелёные линии показывают, насколько выполнены задачи: частично или полностью .

Шаг 9. Добавьте вертикальную линию, обозначающую текущий день

Красная линия обозначает текущий день

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

  • Правой кнопкой вызовите контекстное меню для диаграммы. Выберите пункт «Выбрать данные».
  • Нажмите кнопку «Добавить», впишите в поле «Имя ряда» слово «Сегодня». Для значения оси Х введите диапазон ячеек C31-C32, для значения оси Y — Н31-Н32.

На графике появилось два новых маркера. Обозначьте их: выделите подписи данных и напишите в строке формул слово «Сегодня». Кликните на подписи правой кнопкой и выберите в контекстном меню пункт «Формат данных». Поставьте положение подписи «Сверху».

  • Перейдите в основном меню на вкладку «Макет».
  • Выберите в выпадающем списке слева в верхнем углу пункт «Ряд Сегодня».
  • Добавьте планки погрешностей с дополнительными параметрами, следуя инструкции, как в пятом шаге. Вам потребуется планка по вертикальной оси, с направлением «Минус», конечным стилем «Без точки», относительным значением 100%.

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

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

Шаг 10. Добавьте текстовые блоки

Так выглядит диаграмма в итоге

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

  • Выделите диаграмму, перейдите в основном меню на вкладку «Вставка». Выберите пункт «Надпись».
  • Расположите область надписи в правом верхнем углу диаграммы. Выделите границу надписи правой кнопкой мыши, в контекстном меню выберите пункт «Формат фигуры». В открывшемся окне выберите тип заливки «Сплошная», цвет серый. Отформатируйте надпись. Можете вставлять любое количество блоков.

Теперь вы сможете сделать диаграмму за 20−30 минут, используя только всеми любимый редактор MS Excel.

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

Содержание

Что такое ярусная диаграмма?

Перед тем как мы приступим практике, давайте разберемся, что же такое ярусная диаграмма и с чем ее «едят»? Если взглянуть на прикрепленную ниже картинку, то мы увидим, что каждый столбик, отождествляющий в данном случае того или иного человека, состоит из нескольких частей. Так вот, каждая из таких частей и является ярусом.

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

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

Как построить в Excel

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

Создаем таблицу и диаграмму

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

  1. Сначала мы открываем документ, в котором будем создавать таблицу и диаграмму. Дальше рисуем саму таблицу если конечно, ее изначально не было.

  1. Давайте придадим нашей таблицы более симпатичного внешнего вида. Для этого достаточно выделить диапазон ячеек с зажатой кнопкой «Shift», перейти во вкладку «Главная», выбрать кнопку, обозначенного цифрой «2», а потом понравившийся стиль.

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

  1. Стало лучше, не правда ли? Теперь переходим к построению самой ярусной диаграммы. Для этого четко выделяем всю нашу таблицу, включая название столбцов и строк. Дальше переходим во вкладку «Вставка» и кликаем по кнопке «Рекомендуемые диаграммы».

  1. Откроется еще одно небольшое окошко, в котором мы должны перейти во вкладку «Все диаграммы». После этого в левой боковой колонке выбираем ссылку «Гистограмма». Дальше в правой части окна жмем по иконке ярусной диаграммы (обозначена цифрой «2») и выбираем нужный нам элемент. Подтверждаем создание гистограммы, нажав на «ОК».

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

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

Настраиваем элементы

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

В появившемся текстовом поле вводим название нашей оси и подтверждаем изменения, нажав кнопку «Enter» на клавиатуре. Как видите, название четко вписалось на свое место, и теперь ярусная диаграмма стала гораздо более наглядной.

Мы же тем временем переходим к настройке внешнего вида индикатора.

Редактируем стили

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

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

Перейди на вторую вкладку, которая называется «Цвет», мы можем вручную настроить оттенки каждого из столбцов ярусной диаграммой. Прокручивая содержимое небольшого окошка ниже, мы получим доступ к другим предустановкам.

Работаем с фильтрами

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

На этом с созданием, настройкой и изменением ярусной диаграммы в Microsoft Excel покончено.

Другие виды диаграмм

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

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

Также большой популярностью пользуются и круговые диаграммы. Они могут быть как в 3D-стиле, так и плоские. Такие индикаторы отображают параметры в виде долей круга. При этом соответственно, эти доли имеют пропорциональный размер по отношению друг к другу, а общая сумма параметров составляет 360°.

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

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

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

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

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

Надеемся, тема, затронутая в статье, раскрыта для вас на 100%. Для того чтобы пользователю было еще легче разобраться с построением ярусных диаграмм в Excel, мы предлагаем посмотреть тематический ролик, который отнимет у вас всего каких-нибудь пару минут.

Вопросы и ответы

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

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

Оцените статью
Рейтинг автора
5
Материал подготовил
Илья Коршунов
Наш эксперт
Написано статей
134
А как считаете Вы?
Напишите в комментариях, что вы думаете – согласны
ли со статьей или есть что добавить?
Добавить комментарий