000818.ru

Финансовые и Бух. услуги
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как наложить линейную диаграмму на гистограмму в Excel?

Как сделать диаграмму по таблице в Экселе

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

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

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

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

Как наложить линейную диаграмму на гистограмму в Excel?

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

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

Избавиться от сетки на графике — это элементарно. Если вы хотите отформатировать какой-либо элемент в Excel (в диаграмме или таблице), просто выделите его и нажмите Ctrl+1 (для Mac: Command+1): откроется диалог Формат для выбранных объектов.

В нашем примере показано, что вы щелкаете одну из линий сетки на графике (любую линию, но верхняя будет выделять всю область графика) и открываете диалоговое окно форматирования. Цвет линии > Без линии (для Mac: Линия > Сплошная > Без цвета линии).

2. Переместите легенду

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

Для этого используйте окно с параметрами форматирования (вы должны знать, как это делается). и выберите нужную позицию в Настройках легенды, на Mac соответственно Placement > нужный элемент.

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

3. Удалите легенду с единственным рядом

4. Добавьте описание в название диаграммы

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

На приведенной ниже схеме, например, было бы неинтересно писать в заголовке только «Показы»:

5. Отсортируйте данные перед созданием диаграмм

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

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

Читайте так же:
Как найти ячейки с / с проверкой данных в Excel?

См. схему ниже. Я согласен с тем, что для сортировки доходов по категориям приходится смотреть туда-сюда.

6. Не заставляйте людей наклонять голову

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

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

7. Приведите в порядок оси

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

Группы цифр не имеют разделителей.

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

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

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

Загромождённость осей

Вертикальная ось верхней диаграммы загромождена бесполезными подписями. Вызовите диалоговое окно, выбрав ось. В Параметрах оси выключите переключатель Основная цена за единицу (на Mac: Формат оси > Масштаб > Основная цена за единицу). Ниже показана диаграмма изменения от 20 000 до 40 000.

При желании каждый шаг можно рассмотреть более подробно.

Ненужные децимации

Никогда не включайте десятичные дроби, если все числа являются целыми числами (другими словами, нет дробных частей). Чаще всего это происходит при использовании формата _Monetary_, где появляются такие подписи: $20,000.00, $30,000.00 и т.д. Это совершенно не нужно и только мешает.

Десятичная дробь вместо процентов

Не печатайте десятичные числа, если на оси отображаются проценты. Чем меньше времени вам придется потратить на интерпретацию данных, тем привлекательнее будет график. И даже в случае с процентами не забывайте удалять ненужные дроби. Проще говоря, не делайте этого: 10.00%, 20.00%. А следующим образом: 10%, 20%.

Ползучее нулевое форматирование.

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

Здесь достаточно изменить формат 0. Для этого выделите столбец, в котором взяты данные, вызовите диалоговое окно, затем перейдите на вкладку Число. В пункте (все форматы) найдите строку «их» и замените дефис на 0.

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

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

  • В окне вставки вида помимо маленького значка появился предварительный просмотр;
  • В окне вставки вида появился новый тип — «Комбинированный», который объединяет несколько видов;
  • В окне вставки вида появилась страница «Рекомендуемые диаграммы», которую предлагает версия 2013 года;
  • На месте вкладки «Дизайн» появились три новые кнопки — «Элементы диаграмм», «Стили диаграмм» и «Фильтры диаграмм». Вкладка «Дизайн» заменена тремя новыми кнопками — «Элементы диаграммы», «Стили диаграммы» и «Фильтры диаграммы», назначение которых понятно из их названий;
  • Расположение элементов диаграммы настраивается с помощью удобной правой панели вместо диалогового окна;
  • Заголовки данных теперь могут быть оформлены в виде выносок и взяты непосредственно с листа;
  • При изменении исходных данных диаграмма плавно переходит в новое состояние.
Читайте так же:
Как объединить два столбца в один с чередующимися значениями в Excel?

Пример построения гистограммы в Excel

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

Последующие столбцы — «Год производства», например, с 2001 по 2008 гг. Далее нажмите на поле «Вставка» и команду «Гистограмма», рассмотрите первую с группировкой, выберите ее. Под вашей таблицей появится диаграмма. Переместите на отдельный лист, так она будет иметь более объемный вид.

При работе с диаграммами Excel появляются три дополнительные вкладки: вкладка «Конструктор», вкладка «Макет» и «Формат»:

  1. «Layout» отвечает за обработку внешнего вида. Здесь можно задать имя, указать название горизонтальной и вертикальной оси, вставить рисунок из файла, готовые рисунки, блок-схемы, выноски, подзаголовки, которые можно разместить в любом месте страницы. Вы можете изменить расположение каждой оси, а также включить или отключить линии сетки.
  2. «Формат» задает стиль для каждого элемента. Основными окнами на этой вкладке являются Формат выбора (используется для улучшения форматирования) и Восстановление стиля форматирования (заменяет пользовательский выбор на общий стиль оформления, который согласует выбор с общей темой рабочей книги Excel). Вы можете заполнять и переписывать фигуру, а также применять к ней эффекты оформления, такие как тень, свечение, отражение или вращение объема.
  3. «Конструктор» позволяет изменить тип графика или сохранить его как шаблон (т.е. сохранить макет как шаблон), который можно применять к другим. Вы можете поменять местами данные на осях. Значения, введенные на оси X, переносятся на ось Y и наоборот. Можно переключиться на другой лист или закладку.

Гистограммы в Excel пригодятся для наглядного анализа динамики величин по времени

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

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

Инструкция по созданию диаграммы

    1. Создаем таблицу. Я придумал небольшое производство столов и стульев, использовав по ходу генератор случайных чисел, точнее его аналог для простых целей — функцию СЛЧИС. Как построить гистограмму в Excel
    2. Потом выбираем на вкладке «Вставка» нужную диаграмму. Как сделать гистограмму в Excel Я выбрал обычную сгруппированную, она самая первая в списке.
    3. Появится гистограмма с минимальными настройками прямо на листе. Как построить гистограмму в Excel Нам такого счастья не надо, поэтому правой кнопки мыши по диаграмме вызываем контекстное меню Как построить гистограмму в Excel и выбираем отдельный лист. Как построить гистограмму в Excel
    4. Переходим на лист диаграммы. Там появляются новые вкладки «Макет», «Конструктор» и «Формат». На вкладке «Макет» мы
      • Указать название графика
      • Указать названия осей
      • Добавить таблицу с данными
      • Внести незначительные исправления в легенду, область построения.

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

      1. На вкладке Формат можно задать форматирование для каждого элемента диаграммы, включая: заливку, контуры, цвет столбцов, стиль подзаголовков, размеры и порядок элементов и многое другое, что иногда совсем не нужно делать.
      2. Заливка, контуры, цвет полос
      3. Стили подзаголовков
      4. Размеры и порядок элементов
      5. И многое другое, что иногда вообще не нужно делать.

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

      Вот что я имею.

      Этот пример показывает, как не злоупотреблять форматированием.

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

      Гистограмма с накоплением

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

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

      Нормированная гистограмма с накоплением

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

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

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

      5-минутное видео по теме:

      «Глаза боятся, а руки делают дело».

      P.S. Вам понравилась статья? Подпишитесь на рассылку в правой части страницы (Бесплатный курс «10 лучших инструментов Excel»), чтобы быть в курсе новых событий.

      Условное форматирование в диаграммах Excel

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

      Условное форматирование: диаграмма Yandex

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

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

      Условное форматирование с помощью дополнительных столбцов.

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

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

      Условное форматирование: расширение источника данных

      Зеленым выделена наша основная таблица с данными. Справа от нее — 4 столбца, по которым распределяются исходные значения, в зависимости от попадания в определенный диапазон:

      • Значения меньше 3000. Формула в C2: =If( B2 <3000; B2 ;ND () )
      • Значения больше 3000, но меньше 5000. Формула в D2: =If(AND( B2 >=3000; B2 <5000);B2;ND () )
      • Значения больше 5000, но меньше 7000. Формула в E2: =If(AND( B2 >=5000; B2 <7000);B2;ND())
      • Значения больше 7000. Формула в ячейке F2: =If( B2 >=7000; B2 ;ND() )

      Если значение не попадает в какой-либо диапазон, то в соответствующем столбце выводится ошибка #Н/Д. Это нужно для того, чтобы «неправильные» значения не отображались на диаграмме. Если вам мешаются ячейки с #Н/Д, то могу предложить несколько вариантов:

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

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

      Условное форматирование диаграммы с помощью дополнительной таблицы

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

      Условное форматирование диаграммы с помощью VBA.

      Второй вариант — использование макросов VBA. Нравится этот способ мне гораздо больше: не нужно строить лишние таблицы, выбирать новые источники данных в настройках и настраивать «корректный» вывод ошибок с «#Н/Д». Достаточно один раз подготовить код и использовать его по необходимости.

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

      Все просто. Данный макрос подсвечивает диаграмму тремя цветами (по типу «Светофор»: красный, желтый и зеленый), в зависимости от принадлежности значения столбца диаграммы к определенному диапазону. Диапазонов, соответственно, тоже три и задаются они с помощью двух переменных: FirstValue и SecondValue (все значения меньше FirstValue, между FirstValue и SecondValue и больше SecondValue). Значения этих переменных задаются в макросе, точно так же, как и цвета.

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

      Условное форматирование: результат выполнения макроса VBA

      Все столбцы со значением менее 700000 были залиты красным цветом, со значением более 900000 — зеленым, а в диапазоне от 700000 до 900000 — желтым.

      Следующий макрос можно использовать для совершенного условного форматирования диаграммы:

      • Добавить больше условий
      • Добавить новые цвета к новым условиям
      • Создать форму VBA, в которой можно выбирать цвета из палитры и задавать диапазоны условий, не изменяя код

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

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

      Надстройка SHTEM для Excel.

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

      Предварительная версия инструмента условного форматирования диаграмм в тестовой версии дополнения выглядит следующим образом:

      Условное форматирование в надстройке для Excel

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

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

      Условное форматирование: заключение.

      Если Вы часто формируете диаграммы, где столбцы должны быть залиты разными цветами, в зависимости от их значения — перечисленные способы безусловно вам подойдут. «Гибкими» их, конечно, не назовешь, но в любом случае, это лучше, чем ручная заливка каждого из столбцов. Если Вам известны какие-нибудь другие способы условного форматирования диаграммы или просто хотите дополнить мою статью — пишите об этом в комментариях, обязательно все прочитаю. А скачать файл с реализацией перечисленных здесь способов заливки диаграммы, можно нажав на кнопочку ниже:

      голоса
      Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector