Как уменьшить расстояние между столбцами в гистограмме в excel

5 приёмов, как улучшить диаграммы в Excel

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

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

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

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

Как сделать:

Клик правой кнопкой мыши — ПКМ — по любому столбцу диаграммы.

Формат ряда данных.

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

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

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

Как сделать:

ПКМ по ряду данных;

Формат ряда данных;

Выбрать боковой зазор в процентах.

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

ольга базалева

Курс

EXCEL

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

• Научитесь работать с любыми отчётами
• Узнаете, как сортировать и анализировать данные
• Сможете работать с формулами

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

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

Как сделать:

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

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

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

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

Как сделать:

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

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

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

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

Подборка курсов, бесплатных лекций и материалов по Excel

Как сделать:

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

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

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

Читать также

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

Как уменьшить расстояние между столбцами в гистограмме в excel

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

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

Сдвигайте столбцы ближе друг к другу на гистограмме Excel

Чтобы переместить стержни намного ближе, вы можете изменить ширину зазора.

1. Щелкните правой кнопкой мыши ряд данных и выберите Форматировать ряд данных из контекстного меню. Смотрите скриншот:

2. Затем в Форматировать ряд данных панель, измените данные в Ширина зазора Чтобы изменить размер промежутков между полосами, чем меньше данные, тем меньше полосы вместе.

Если в Excel 2007 или 2010 вы можете изменить Ширина зазора в Форматировать ряд данных Диалог.

Гистограмма с переменной шириной столбцов

Имеем, в качестве примера, вот такую табличку с информацией по количеству сотрудников и стоимостью четырех известных IT-компаний:

Задача: необходимо наглядно отобразить оба параметра для сравнения по всем компаниям.

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

Этап 1. Подготовка данных

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

Давайте разберем все изменения, которые были сделаны.

• Каждая компания превратилась из строки в столбец.
• По каждой компании должен быть блок из 5 ячеек, где первая и последняя ячейки с нулями, а три центральных содержат одинаковые значения — стоимость каждой компании.
• Добавлены вспомогательные столбцы Пустышка и Подписи, в которых напротив центральных значений каждого блока размещены стоимости и названия компаний. Эти колонки понадобятся нам чуть позже для правильного размещения подписей к столбикам.
• Первый (неподписанный) столбец представляет из себя чуть более хитрую штуку. Не залитые голубым цветом в нем ячейки — это количества сотрудников по каждой компании (92, 126, 54, 380), но взятые последовательно с нарастающим итогом. Т.е. для Apple это 92, для Microsoft уже 92+126=218, для Google 92+126+54=272 и т.д. Голубые же ячейки будут впоследствии, своего рода, «переходниками» и содержат среднее арифметическое соседних ячеек, т.е. для Apple это (0+92)/2=46, для Microsoft (92+218)/2=155 и т.д.

Этап 2. Строим диаграмму

Выделим в нашей таблице все столбцы кроме последних двух и на вкладке Вставка (Insert) выберем для построения вариант диаграммы С областями с накоплением (Stacked Area) :

На выходе должно получиться что-то похожее:

Основная проблема в том, что Excel на автомате интерпретирует подписи к оси X не как числа, а как текст — интервалы между подписями не соответствуют математической разнице между числами, да и сами подписи повторяются. Именно поэтому наша диаграмма еще не похожа на желаемую, но это, на самом деле, легко исправить. Щелкните правой кнопкой мыши по горизонтальной оси и выберите в контекстном меню команду Формат оси (Axis Format) . В открывшемся окне переключите Тип оси с Автовыбора на Ось дат (Date Axis) и все сразу изменится!

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

Этап 3. Добавляем подписи

Теперь неплохо бы добавить в нашу диаграмму подписи с названиями компаний. Скопируйте столбец Пустышка (выделить диапазон G11:G25 и нажать Ctrl+C), выделите диаграмму и вставьте скопированные данные прямо в нее (нажать Ctrl+V) — к нашей диаграмме должен добавиться новый ряд, выглядящий как крыши домов над нашими прямоугольниками:

Теперь щелкните правой кнопкой мыши по любой «крыше» и выберите команду Изменить тип диаграммы для ряда (Change series chart type) . В открывшемся окне измените тип диаграммы для добавленного ряда на обычный график с маркерами:

Затем щелкните правой кнопкой мыши по графику и выберите Добавить подписи данных (Add Data Labels) . Excel подпишет значения стоимости компании на каждом «пике».

Дальнейший ход действий зависит от того, какая у вас версия Excel. Если у вас последние Excel 2013 или 2016, то все будет проще. Щелкните правой кнопкой мыши по подписям, выберите команду Формат подписей (Format Data Labels) и затем включите флажок Значения из ячеек (Values from cells) . Останется выделить данные из последнего столбца в нашей таблице, чтобы они попали на диаграмму в подписи к столбикам вместо чисел:

Если же у вас более древние версии Excel, то придется либо изменять подписи вручную (несколько одиночных щелчков по подписи, пока не начнет мигать курсор, а потом вбить название компании), либо использовать специальную бесплатную надстройку XYChartLabeler (дай бог здоровьичка Rob Bovey — ее автору, спасшему много-много моих нервных клеток и времени).

Excel 2016 стал первой версией за последние 20 лет, где Microsoft, наконец-то, добавила новые типы диаграмм (водопад, Парето, Маримекко и др.) в стандартный набор. Очень хочется верить, что «лед тронулся» и в следующих обновлениях мы увидим еще больше красивых и наглядных вариантов. Возможно, когда-нибудь и описанная выше гистограмма с переменной шириной столбцов будет строиться без «шаманских танцев» из этой статьи, а за два движения. Будем надеяться 🙂

Диаграммы в Microsoft Excel: советы, хитрости и приёмы

Некоторые советы, хитрости и приёмы для создания замечательных диаграмм в Microsoft Excel.

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

Чёрно-белые узоры

Новое, что появилось в Microsoft Office 2010, – это возможность использовать в качестве заливки Вашей диаграммы узоры в оттенках серого. Чтобы увидеть, как все это работает, выделите диаграмму, откройте вкладку Chart Tools (Работа с диаграммами) > Layout (Формат) и нажмите Fill (Заливка) > Pattern Fill (Узорная заливка). Для создания монохромной диаграммы установите для параметра Foreground Color (Передний план) чёрный цвет, для Background Color (Фон) – белый цвет и выберите узор для заливки ряда данных. Повторите те же действия для следующего ряда данных, выбрав другой узор. Вам не обязательно использовать белый и чёрный цвета, главное выберите разные узоры для разных рядов данных, чтобы диаграмма оставалась понятной, даже если Вы распечатаете ее на монохромном принтере или скопируете на черно-белом ксероксе.

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

Сохраняем диаграмму в Excel как картинку

Вы можете сохранить диаграмму в Excel как картинку и использовать её в дальнейшем где угодно, например, в отчёте или для размещения в интернете. Делать это придётся окольными путями, и вот самый простой из них. Сделайте нужный размер диаграммы на листе. Откройте File (Файл) > Save As (Сохранить как), укажите куда сохранить файл и в выпадающем списке Save As Type (Тип файла) выберите Web Page (*.htm;*.html) (Веб-страница), задайте имя и нажмите Save (Сохранить).

В результате лист Excel превращается в файл HTML, а в связи с тем, что HTML-файлы не могут содержать рисунки, диаграмма будет сохранена отдельно и связана с файлом HTML. Свою диаграмму Вы найдёте в той же папке, в которую сохранили файл HTML. Если Ваш файл был назван Sales.html, то нужный Вам рисунок будет помещён во вложенную папку Sales.files. Рисунок будет сохранён как самостоятельный файл PNG.

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

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

Настраиваем перекрытие и дистанцию между столбцами

Если Вы считаете, что диаграмма будет смотреться лучше, когда столбцы станут шире или будут накладываться друг на друга – Вы можете сделать это. Чтобы настроить перекрытия между двумя рядами данных графика или изменить дистанцию между столбцами, кликните правой кнопкой мыши по любому из рядов данных на диаграмме и в появившемся контекстном меню выберите Format Data Series (Формат ряда данных). Отрегулируйте параметр Series Overlap (Перекрытие рядов):

• чтобы отдалить ряды данных друг от друга, перетаскивайте ползунок в сторону Separated (С зазором);
• чтобы создать наложение рядов данных, перетаскивайте ползунок в сторону Overlapped (С перекрытием).

Вы можете сдвинуть ряды ближе или дальше друг от друга, изменяя параметр Gap Width (Боковой зазор). Если у Вас имеется два ряда данных, и Вы хотите расположить их с наложением друг на друга, но при этом второй столбец графика должен скрываться за первым, то Вам придётся поменять порядок построения графика. Для начала настройте наложение так, как Вы хотите, чтобы столбцы накладывались друг на друга. Затем кликните правой кнопкой мыши по видимому Вам ряду данных и нажмите Select Data (Выбрать данные). Кликните по Series 1 (Ряд 1) и нажмите стрелку вниз, чтобы поместить его под Series 2 (Ряд 2). Таким образом, будет изменён порядок вывода рядов данных в диаграмме, и Вы сможете увидеть меньший столбец впереди большего.

Увеличенные графики

Когда Вы строите диаграмму на основании данных, содержащих календарные даты, то можете обнаружить, что столбцы в ней получились очень узкими. Чтобы решить эту проблему, нужно выделить ось Х графика, щелкнуть по ней правой кнопкой мыши и нажать Format Axis (Формат оси). Выберите Axis Options (Параметры оси), а затем вариант Text Axis (Ось текста). Эти действия меняют способ отображения оси, делая столбцы диаграммы широкими. В дальнейшем, если необходимо, вы можете настроить зазоры между столбцами и сделать их шире.

Построение на второй оси

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

Выделите диаграмму и зайдите на вкладку Chart Tools (Работа с диаграммами) > Layout (Формат) и в разделе Сhart Element (Текущий фрагмент) слева вверху выберите ряд данных, который плохо видно. Нажмите кнопку Format Selection (Формат выделенного), которая находится сразу под списком выбора фрагментов, и в разделе Series Option (Параметры ряда) выберите Secondary Axis (По вспомогательной оси) и нажмите Close (Закрыть). Не снимая выделения, зайдите на вкладку Chart Tools (Работа с диаграммами) > Design (Конструктор) и нажмите Change Chart Type (Изменить тип диаграммы). Теперь Вы можете выбрать другой тип диаграммы для второго ряда, например, график. В итоге у Вас получится комбинированная диаграмма – столбцы гистограммы с наложенным на них линейным графиком.

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

Создаём комбинированные диаграммы

Хоть Microsoft Excel специально не выделяет тот факт, что можно создавать комбинированные диаграммы, это делается без особого труда. Для этого выберите данные и постройте первый график (например, гистограмму). Затем выберите данные, которые нужно отобразить в другом виде (например, в виде линейного графика) и нажмите Chart Tools (Работа с диаграммами) > Design Tab (Конструктор) > Change Chart Type (Изменить тип диаграммы) и выберите тип диаграммы для второго графика. Некоторые типы диаграмм по своей сути не могут быть совмещены друг с другом, например, гистограмма и линейчатая диаграмма. Если же мы возьмем гистограмму и линейный график, то они отлично смотрятся вместе!

Автоматически растущие диаграммы в Excel

Если количество Ваших данных со временем будет увеличиваться, то можно построить диаграмму так, чтобы она расширялась по мере добавления данных. Для этого отформатируйте данные как таблицу, выделив их и нажав на вкладке Home (Главная) команду Format as Table (Форматировать как таблицу). Теперь Ваши данные отформатированы как таблица, а если на основе таблицы построить диаграмму, то увеличение количества данных в таблице будет приводить к автоматическому расширению диаграммы.

Умный заголовок диаграммы

Вы можете создать для диаграммы заголовок, который будет использовать содержимое одной из ячеек на листе. Первым делом, создайте заголовок при помощи команды Chart Title (Заголовок диаграммы) на вкладке Chart Tools (Работа с диаграммами) > Layout (Формат) и расположите его, к примеру, над графиком. Кликните в области заголовка диаграммы, далее в строке формул под Лентой, а затем введите ссылку на ячейку, содержащую данные, которые Вы хотите разместить в заголовке. Если Вы хотите озаглавить её именем таблицы, то введите выражение: =Sheet1!$B$2, где Sheet1 – имя листа, а $B$2 – абсолютная ссылка на ячейку с названием таблицы. Теперь, как только изменится содержимое этой ячейки, так сразу же изменится заголовок диаграммы.

Цвета на выбор для диаграммы Excel

Если Ваша диаграмма отображает только один диапазон данных, Вы обнаружите, что Excel раскрашивает все столбцы графика одним цветом. Это можно исправить, если выделить ряд данных, кликнуть по нему правой кнопкой мыши, нажать Format Data Series (Формат ряда данных) и затем Fill (Заливка). Если в диаграмме использован только один ряд данных, то будет доступна опция Vary colors by point (Разноцветные точки).

Конечно же, Вы всегда можете выбрать отдельный столбец, кликнуть по нему правой кнопкой мыши, выбрать Format Data Point (Формат точки данных) и установить особый цвет для этой точки.

Управляем нулями и пустыми ячейками

Если в Ваших данных есть нулевые значения или пустые ячейки, Вы можете управлять тем, как нули будут отображаться на графике. Для этого нужно выделить ряд данных, открыть вкладку Chart Tools (Работа с диаграммами) > Design (Конструктор) и кликнуть Select Data (Выбрать данные) > Hidden and Empty Cells (Скрытые и пустые ячейки). Здесь Вы сможете выбрать, будут ли пустые ячейки отображены как пропуски, как нулевые значения или, если Ваш график имеет линейный вид, вместо пустого значения от точки до точки будет проведена прямая линия. Нажмите ОК, когда выберете нужный вариант.

Это работает только для пропущенных значений, но не для нулей!

Создаём диаграмму из несмежных данных

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

Сохраняем диаграмму как шаблон

Чтобы сохранить диаграмму как шаблон, доступный снова и снова для повторного использования, первым делом нужно создать эту диаграмму и настроить её вид по своему желанию. Затем выделите её, откройте вкладку Chart Tools (Работа с диаграммами) > Design (Конструктор) и нажмите Save as Template (Сохранить как шаблон). Введите имя шаблона и нажмите Save (Сохранить).

Позже Вы сможете применить этот шаблон в процессе создания или к уже созданной диаграмме, чтобы придать ей точно такой же вид. Для этого кликните по диаграмме, затем на вкладке Chart Tools (Работа с диаграммами) > Design (Конструктор) выберите Change Chart Type (Изменить тип диаграммы), откройте раздел Templates (Шаблоны), выберите нужный шаблон и нажмите ОК.

Эти советы и хитрости помогут Вам быстрее и эффективнее создавать в Excel 2007 и 2010 более привлекательные диаграммы.