Как добавить ряд в диаграмму в Excel?

Трюк №54. Три быстрых способа обновления диаграмм

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

Перетаскивание данных

Вы можете добавить данные в существующий ряд или создать совершенно новый ряд данных, просто перетащив их на график. Excel попытается решить, как обработать эти данные, но вы можете добавить их к существующей строке данных, когда вы хотели создать новую. Однако можно заставить Excel открыть диалоговое окно, в котором можно выбрать нужное действие. Попробуйте добавить некоторые данные на лист (Рисунок 5.13).

Рис. 5.13. Данные для нормальной гистограммы

Используйте Мастер диаграмм для создания простой гистограммы только для диапазона $A$1:$D$5 (Рисунок 5.14).

Рисунок 5.14. Обычная диаграмма только для определенного диапазона

Перетащите данные на диаграмму, выбрав диапазоны A6:D6 и щелкнув правой кнопкой мыши по рамке выделения. Появится диалоговое окно Paste Special (рис. 5.15).

Рис. 5.15. Обычная гистограмма и диалоговое окно специальной вставки

Выберите параметр Столбцы и нажмите OK. На диаграмму будет добавлен ряд данных за май (Рисунок 5.16).

Рис. 5.16. Обычная гистограмма с новым рядом данных

Этот хитрый трюк чаще всего выполняется с помощью диалогового окна Paste Special.

Диаграмма и строка формул

Вы также можете обновить график с помощью панели формул. Выделив график и щелкнув на линии данных, посмотрите на строку формул: вы увидите формулу, которую Excel использует для линии данных. Эта формула, называемая функцией SERIES, обычно задает четыре аргумента, хотя для пузырькового графика требуется дополнительный пятый аргумент, обозначающий размер ([Size]).

Синтаксис (или порядок структуры) функции SERIES следующий: =SERIES([Имя]; [X-значения]; [Y-значения]; [Порядок участков]), в русской версии Excel =SERIES([Имя]; [X-значения]; [Y-значения]; [Номер участка]). Таким образом, допустимая функция SERIES может выглядеть так, как показано на рисунке 5.17: =SERIES. 5.17: =SERIES(Sheet1!$B$1;Sheet1!$A$2:$A$5;Sheet1!$B$2:$B$5;1), в русской версии Excel =SERIES(Sheet1!$B$1;Sheet !$A$2:$A$5;Sheet1!$B$2:$B$5;1)

На рис. 5.17 первая часть ссылки, Sheet1!$B$1, относится к имени или заголовку диаграммы — 2004. Вторая часть ссылки, Sheet1!$A$2:$A$5, относится к значениям по оси X, в данном случае — к месяцам. Третья часть ссылки, Sheet1!$B$2:$B$5, относится к значениям по оси Y, то есть 7.43, 15, 21.3 и 11.6. Наконец, последняя часть формулы, 1, относится к порядковому номеру графика, или к номеру ряда. В данном случае, когда у нас только один ряд, значение может быть равно только 1. Если бы рядов было несколько, у первого ряда был бы номер 1, у второго — номер 2 и т. д.

Рисунок 5.17. Обычная гистограмма с выделенной линией формул

Чтобы изменить диаграмму, измените ссылки на ячейки в строке формул. Помимо ссылок на ячейки, в диаграммы можно вводить и явные значения, известные как массивы констант (подробнее об этом в разделе «Константы в формулах массива» справки по Excel — для вызова справки нажмите кнопку F1). Для этого добавьте <> (фигурные скобки) вокруг значений по осям X и Y, как показано в следующей формуле: =SERIES(«My Ваr»;<«А»;»В»;»С»;»D»>;<1;2;3;4>;1), в русской версии Excel =РЯД(«My Ваr»;<«А»;»В»;»С»;»D»>;<1;2;3;4>;1). В этой формуле РЯД (SERIES) А, В, С и D — это значения по оси X, а 1, 2, 3 и 4 — соответствующие им значения по оси Y. Используя этот метод, можно создавать и обновлять диаграммы, не храня данные в ячейках.

Перетаскивание граничной области

Если диаграмма содержит ссылки на последовательные ячейки, вы можете легко увеличить или уменьшить данные ряда, перетащив ограничительную рамку в нужную точку. Медленно щелкните по данным в строке, которую вы хотите увеличить или уменьшить. После двух медленных щелчков по краям линии появятся черные квадраты (маркеры). Просто нажмите на квадрат и перетащите границу в нужном направлении (рисунок 5.18).

Выделенная область границ в ряду диаграмм

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

Выбор источника данных

Выполните следующие шаги, чтобы открыть диалоговое окно Select Data Source (Выбор источника данных):

1. Выделите диаграмму. Щелкните по ней правой кнопкой мыши и нажмите Выбрать данные (Select Data).Появится диалоговое окно Выбор источника данных (Select Data Source).
2. Слева вы можете увидеть три ряда данных (Bears, Dolphins и Whales), а справа подписи горизонтальных осей (Jan, Feb, Mar, Apr, May и Jun).

Новые возможности диаграмм в Excel 2013

Диаграммы были проанализированы на примере широко используемой версии Excel 2010. Аналогичным образом можно работать и в Excel 2007. С другой стороны, в версии 2013 года появились некоторые новые функции, облегчающие работу с графиками:

• Окно «Вид вставки» теперь имеет предварительный просмотр в дополнение к маленькому значку;
• Окно «Вид вставки» имеет новый тип «Подключенный», который соединяет несколько представлений;
• Окно «Вид вставки» теперь имеет страницу «Рекомендуемые диаграммы», как предполагает версия 2013 года;
• Вкладка «Макет» была заменена тремя новыми кнопками — «Элементы диаграммы», «Стили диаграммы» и «Фильтры диаграммы».
• Вкладка «Макет» была заменена тремя новыми кнопками — «Элементы диаграммы», «Стили диаграммы» и «Фильтры диаграммы», назначение которых понятно из их названий;
• Дизайн элементов диаграммы настраивается с помощью всплывающей панели справа вместо диалогового окна;
• Тексты данных теперь могут быть оформлены в виде вызова и взяты непосредственно из таблицы;

Подписи данных в Excel 2010

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

Добавим дополнительный столбец в нашу таблицу, назовем его Подписи и введем следующую формулу: =C3&» » , которая будет переводить числовой формат в текстовый.

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

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

Выберите строку Преобразования, перейдите в раздел Работа с диаграммами -> Макет в группе Надписи, выберите Надписи -> Центрировать. Щелкните правой кнопкой мыши над отображаемыми подписями на диаграмме и выберите в раскрывающемся меню пункт Форматировать обработку данных. Установите флажок в поле Имена категорий на вкладке Параметры подписи диалогового окна и снимите остальные флажки. Нажмите Закрыть.

Еще необходимо настроить отображение горизонтальных осей. Щелкните правой кнопкой мыши на нижней горизонтальной оси и выберите в выпадающем меню пункт Форматировать ось. На вкладке Параметры оси выберите Нет в поле Ярлык оси и нажмите Закрыть.

Снова выделите всю диаграмму, перейдите на вкладку Работа с диаграммами -> Макет в группе Оси, выберите Оси -> Вспомогательная горизонтальная ось -> Слева направо. На диаграмме должна появиться дополнительная горизонтальная ось, расположенная по умолчанию вверху. В выпадающем меню при щелчке правой кнопкой мыши на вспомогательной оси выберите Format Axis. На вкладке Параметры оси выберите Нет в поле Главная и Внизу в поле Подпись оси. Установите маркер напротив элемента строки Нет, а затем нажмите кнопку Закрыть.

Пошаговый процесс создания диаграммы в Excel

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

Простой способ

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

1. Таблица заголовков выделяется на фоне остальных.

1. В главном меню книги перейдите в раздел «Вставка» и выберите нужный вид, например «Круг».

1. Щелкните по соответствующему изображению, и в результате на листе появится готовый рисунок. В верхней панели также доступен раздел «Работа с диаграммами» (Конструктор, Макет, Формат).

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

Чтобы значение из правой колонки на рисунке отображалось в процентах или дробях, щелкните соответствующий макет в разделе «Конструктор». Затем перейдите на вкладку «Макет» — «Подписи данных» и выберите вариант отображения сумм.

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

Кроме того, настройки можно изменить в «Формат подписей данных» и «Формат строк данных». Чтобы изменить настройки, щелкните правой кнопкой мыши на рисунке.

Существует также быстрый и простой метод. В этом случае работает обратная процедура:

1. С помощью «Вставки» выберите тип графика, на экране появится пустое окно.
2. Щелкните правой кнопкой мыши на окне и в выпадающем меню выберите «Выбрать данные». Эта опция также доступна в разделе «Конструктор» на верхней панели.

1. В открывшемся окне в поле «Диапазон» введите ссылку на ячейки таблицы. Поля «Элементы легенды» и «Отметки горизонтальной оси» заполняются автоматически при вводе шкалы значений. Если Excel неправильно заполняет поля, вам придется делать это вручную: Нажмите «Изменить» в полях «Имя строки» и «Значения», чтобы вставить ссылки на нужные ячейки, а затем нажмите «Ок».

По Парето (80/20)

Автор утверждает, что эффективные действия дают наибольшую отдачу. Таким образом, 20% усилий дают 80% результата и, наоборот, 80% усилий дают только 20% результата. При применении принципа Парето следует использовать гистограммы.

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

1. Выберите таблицу, в разделе «Вставка» выберите соответствующее изображение гистограммы.

1. Это показывает изображение с колонками разных цветов.

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

1. Готовая деталь изменяется по желанию, как описано выше.

Также можно рассчитать процентную пользу каждого приема пищи:

1. Создайте дополнительную строку с итогами и еще один столбец с процентами. Используйте формулу =SUM(диапазон) для расчета общей суммы.

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

1. Сортирует проценты (кроме общего) в порядке убывания. Выберите диапазон, щелкните правой кнопкой мыши, выберите пункт меню Сортировка — От максимума до минимума. Отмените автоматическое расширение выделенной области, переместив выделение в следующую точку.

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

1. Скройте 2 колонки (прибыль и покупки), нажав Ctrl+0. Выделите оставшиеся колонки, затем выберите «Вставить». — «Гистограмма».

1. Щелкните левой кнопкой мыши на вертикальной оси, затем щелкните правой кнопкой мыши и выберите «Форматировать ось». В параметрах установите максимальное значение на 1 (это означает 100%).

1. Добавьте проценты к рисунку, выбрав соответствующую раскладку. Выберите столбец «% общего воздействия» и измените тип рисунка на «График».

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

По Ганту

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