Как увеличить ширину столбца в диаграмме Excel
Обновлено: 03.07.2024
Сегодняшний пост посвящен тактическим вопросам: как настроить ширину гистограмм в Excel (и почему это нужно делать).
Прежде чем мы перейдем к пошаговым инструкциям, я должен упомянуть, что не существует каких-либо строгих правил для оптимального расстояния между стержнями. Скорее, это личное предпочтение, похожее на ношение белого после Дня труда (в США это первые выходные сентября). Как житель душного юго-востока, я буду раскачиваться белым до тех пор, пока в середине октября не наступят осенние температуры. Однако, если вы живете в более прохладном климате и считаете День труда символическим концом лета, вы, возможно, предпочитаете говорить sayonara для белого до Дня памяти.
Та же серая область соответствует оптимальному расстоянию между столбцами. Фактическая ширина не установлена в камне. Наша цель — дать зрителям возможность сравнивать длину полос (а не площадь между ними), поэтому в целом рекомендуется делать полосы толще, чтобы свести к минимуму окружающее пустое пространство.
Теперь давайте обратимся к тому, как это сделать в Excel. В духе Дня труда я буду использовать некоторые данные Бюро статистики труда (BLS), показывающие десять самых популярных профессий в США по состоянию на май 2020 года.
Сравните расстояние между полосами в двух изображениях, показанных ниже:
Пробелы слева привлекают внимание и создают ненужный мерцающий эффект. В скорректированной версии акцент сделан на длину стержней. Загрузите файл Excel, чтобы выполнить следующие шаги для ручной настройки:
Выделите все столбцы, щелкните правой кнопкой мыши и выберите "Формат ряда данных":
В Excel стандартная столбчатая диаграмма будет отображать все столбцы одинаковой ширины через равные промежутки времени. Однако в некоторых случаях было бы лучше, если бы ширина каждого столбца была разной. Например, где каждый столбец представляет разные диапазоны данных. Тот же принцип ширины столбца применим и к гистограммам.
Excel не имеет настроек для изменения ширины отдельных столбцов при использовании гистограммы. Однако можно проявить творческий подход с диаграммой с областями с накоплением и правильным расположением данных. Приложив немного хитрости, можно создать эту диаграмму:
Основные принципы
Чтобы создать столбчатую диаграмму переменной ширины, мы будем использовать диаграмму с областями с накоплением. Обычно этот тип диаграммы использует фиксированные интервалы для оси x (нижняя ось). Изменив ось X, чтобы отобразить ось данных, мы можем нанести точку в любом месте вдоль оси X. Можно даже нанести точки в одном и том же месте по оси x, что приведет к вертикальной линии. Это дает нам возможность создать иллюзию столбцов.
Ярлыки данных на приведенной выше диаграмме также являются иллюзией. Они создаются на линейной диаграмме, для которой не задан цвет.
Диаграмма
Диаграмма, которую мы будем создавать в этом примере, использует вымышленные данные, чтобы показать удовлетворенность клиентов продуктом в зависимости от возрастных диапазонов. Основные данные показаны ниже, Исходные данные
Эти данные будут использованы для создания этой диаграммы. Диаграмма для создания
Настройка данных
Первый шаг – правильная настройка данных.
Наши данные начинаются с 18, но ось диаграммы начинается с нуля, поэтому значения в ячейках A13–A57 (показаны выше) представляют собой количество лет старше 18.
Создание диаграммы переменной ширины
Это руководство основано на Excel 2016.Диаграмму можно создать и в других версиях Excel, хотя параметры могут быть в другом месте, но принципы те же.
Выберите ячейки без столбца возрастного диапазона (в нашем примере ячейки B12–F24).
На ленте нажмите Вставка -> Диаграммы -> Просмотреть все диаграммы.
В окне Вставить диаграммы нажмите Все диаграммы -> Область -> Область с накоплением -> ОК.
Появится новая диаграмма с областями
Щелкните правой кнопкой мыши ось X и выберите Формат оси…
В окне Ось формата выберите Ось даты.
Щелкните правой кнопкой мыши одну из серий диаграмм и выберите Выбрать данные…
В окне Выбор источника данных нажмите Изменить в поле Метки горизонтальной оси (категории).
Установите столбец «Возраст» в качестве меток оси. Затем нажмите ОК.
Теперь диаграмма начнет принимать форму столбчатой диаграммы переменной ширины или гистограммы.
Добавление меток данных к диаграмме переменной ширины
Приведенная выше диаграмма в порядке. Но было бы лучше, если бы метки данных располагались прямо над или под столбцами. Получив это, мы можем удалить неприглядную нумерацию на оси X. К сожалению, добавление ярлыков требует изменения исходных данных, нужны дополнительные столбцы и строки.
Обратите внимание, что теперь для каждого значения имеется 3 строки, а средняя точка была вставлена в середину каждого возрастного диапазона. Создайте диаграмму так же, как описано выше, включая высоту метки в источнике данных диаграммы. Диаграмма должна выглядеть так:
Нажмите правой кнопкой мыши серию диаграммы "Высота метки" и выберите Изменить тип диаграммы серии…
Измените тип диаграммы на линейную диаграмму — поскольку здесь только одна линия, тип линейной диаграммы не имеет большого значения.
Нажмите правой кнопкой мыши линейный график и выберите Добавить метки данных…
Затем щелкните правой кнопкой мыши метки данных и выберите Форматировать метки данных…
В окне Форматировать метки данных установите для параметра Положение метки значение Выше. Если вы используете Excel 2013 или более позднюю версию, нажмите «Значение из ячеек» и выберите диапазон, содержащий метки данных (в нашем примере ячейки H13–H28).
Для тех, кто использует Excel 2010 и более ранние версии, Значение из ячеек недоступно. Если это так, дважды щелкните каждую метку данных и введите описание метки вручную. Удалите все ненужные ярлыки.
Нажмите правой кнопкой мыши линейную диаграмму и выберите Форматировать ряд данных…
Установите для параметра Линия значение Нет линии, а для параметра "Параметры маркера" — значение "Нет".
Удалите все ненужные элементы диаграммы, такие как название диаграммы, условные обозначения и т. д., а затем примените любое дополнительное форматирование по своему усмотрению.
Если вы предпочитаете метки данных ниже, это тоже возможно.
Получите БЕСПЛАТНУЮ электронную книгу VBA с 30 наиболее полезными макросами Excel VBA.
Автоматизируйте Excel, чтобы сэкономить время и перестать выполнять работу, которую могла бы выполнять обученная обезьяна.
Не забывайте:
Если вы нашли этот пост полезным или у вас есть лучший подход, оставьте комментарий ниже.
Вам нужна помощь в адаптации этого к вашим потребностям?
Я предполагаю, что примеры в этом посте не совсем соответствуют вашей ситуации. Мы все используем Excel по-разному, поэтому невозможно написать пост, который удовлетворит потребности всех. Потратив время на изучение методов и принципов, изложенных в этом посте (и в других местах на этом сайте), вы сможете адаптировать его к своим потребностям.
- Читайте другие блоги или смотрите видео на YouTube по той же теме. Вы получите гораздо больше пользы, найдя собственные решения.
- Спросите «Excel Ninja» в вашем офисе. Удивительно, какие вещи знают другие люди.
- Задайте вопрос на форуме, таком как Mr Excel, или в сообществе Microsoft Answers. Помните, что люди на этих форумах обычно отдают свое время бесплатно. Поэтому постарайтесь сформулировать свой вопрос, убедитесь, что он четкий и лаконичный. Перечислите все, что вы пробовали, и предоставьте скриншоты, сегменты кода и примеры книг.
- Используйте Excel Rescue, моего партнера-консультанта. Они помогают решить небольшие проблемы с Excel.
Что дальше?
Пока не уходите, в Excel Off The Grid есть чему поучиться. Ознакомьтесь с последними сообщениями:
Инструменты построения диаграмм в Microsoft Excel 2010 и 2007 намного лучше по внешнему виду и функциональности по сравнению с теми, что были доступны в более ранних версиях Excel. Хотя диаграммы выглядят лучше, не все опции, которые вы можете использовать, чтобы сделать их более функциональными, сразу бросаются в глаза. В этом посте я покажу вам несколько полезных советов, приемов и методов создания диаграмм в Excel, которые помогут вам выполнять свою работу более эффективно.
Черно-белые узоры
Новым в Microsoft Office 2010 является возможность использовать заливку в градациях серого для диаграммы. Чтобы увидеть это в действии, выберите диаграмму и выберите Инструменты для работы с диаграммами > вкладка «Макет», а затем выберите ряд для редактирования в раскрывающемся списке в левом верхнем углу ленты. Выберите «Выделение формата» (чуть ниже на ленте) и выберите Заливка > Заливка узором.Для монохромной диаграммы установите черный цвет переднего плана и белый цвет фона и выберите шаблон заливки для ряда. Повторите это для второй серии, выбрав другой узор. Вам не нужно использовать черно-белое изображение, так как ваши цвета просто выбирают разные узоры, чтобы диаграммы можно было понять при печати на монохромном принтере или фотокопировании в черно-белом режиме.
Вы можете заполнить диаграмму шаблоном в Excel 2010, чтобы ее можно было распечатать на монохромном принтере или сделать фотокопию в черно-белом режиме.
Сохранение диаграммы Excel как изображения
Вы можете сохранить диаграмму в виде изображения в Excel, чтобы использовать ее в другом месте, например в отчете или в Интернете. Для этого вы будете использовать обходной путь, и самый простой способ сделать это — изменить размер диаграммы на листе так, чтобы она была подходящего размера. Выберите Файл > Сохранить как, выберите место для сохранения файла и в раскрывающемся списке Тип файла выберите Веб-страница (*.htm;*.html). ), введите имя и нажмите Сохранить.
При этом рабочий лист преобразуется в html-файл, а поскольку html-файлы не могут содержать изображения, диаграмма сохраняется отдельно и связана с html-файлом. Вы можете найти свою диаграмму в папке, в которую вы сохранили html-файл. Итак, если ваш файл называется sales.htm, то изображения будут находиться в папке с именем sales_files. Изображение сохраняется как отдельный файл PNG. Конечно, вам также потребуется сохранить рабочий лист Excel, если вы хотите сохранить данные и диаграмму для работы в Excel в будущем.
Вы можете сохранить диаграмму в виде файла изображения, если впоследствии вам понадобится использовать ее в другом проекте.
Настройка перекрытия и интервала между столбцами
Если вы считаете, что ваша диаграмма выглядела бы лучше, если бы столбцы были шире или если бы они перекрывали друг друга, вы можете сделать это. Чтобы настроить перекрытие между двумя рядами диаграммы или изменить расстояние между столбцами, щелкните правой кнопкой мыши любой ряд на диаграмме и выберите Форматировать ряд данных. Используйте параметр Перекрытие серий, чтобы разделить серию, перетащив ее в сторону Раздельно или Перекрыть серию, перетащив ее в сторону Перекрыть .
Вы можете отрегулировать Ширина промежутка, чтобы сдвинуть серию ближе или дальше друг от друга. Если у вас есть две серии диаграмм, которые вы хотите перекрыть, и если меньшая серия оказывается позади большей, вам нужно будет изменить порядок построения диаграммы. Сначала установите перекрытие, чтобы они перекрывались, как вы хотите. Затем щелкните правой кнопкой мыши ряд данных, который вы видите, и выберите Выбрать данные. Нажмите Серия 1 и нажмите кнопку Вниз, чтобы поместить ее за Серию 2. Это изменяет порядок, в котором они отображаются, поэтому вы можете видеть меньшие данные перед большими данными.
Полосы большего размера
При отображении данных на основе дат вы можете обнаружить, что столбцы на диаграмме очень узкие. Решение для этого состоит в том, чтобы щелкнуть ось X диаграммы Excel, щелкнуть правой кнопкой мыши и выбрать Формат оси. Выберите Параметры оси, а затем нажмите Ось текста, чтобы выбрать ее. Это изменяет способ построения оси, давая более широкие полосы. Затем вы можете отрегулировать ширину зазора, если необходимо, чтобы сделать их шире.
Построение на второй оси
Когда вы отображаете очень маленькие данные, такие как проценты, по сравнению с гораздо большими данными, такими как миллионы, проценты будут потеряны и не будут видны. Вы можете решить эту проблему, отложив проценты по другой оси. Выберите диаграмму и выберите Инструменты для работы с диаграммами > вкладка «Макет», а затем в селекторе элементов диаграммы в верхнем левом углу выберите серию, которую трудно увидеть. Нажмите Выбор формата, который появляется сразу под ним, и в группе параметров серии выберите Вторичная ось и нажмите Закрыть. Не перемещая выделение, выберите Инструменты для работы с диаграммами > вкладка "Дизайн" и выберите Изменить тип диаграммы. Теперь вы можете выбрать другой тип диаграммы, например линейную. Поскольку у вас выбрана серия, которая будет применяться только к этой серии, а не ко всей диаграмме, вы получите комбинированную диаграмму, например, гистограмму с линейной диаграммой поверх.Мне нравится, чтобы текст на оси диаграммы соответствовал цвету соответствующей части диаграммы, к которой он относится. Поэтому, если у меня есть зеленые полосы, я буду печатать зеленый текст на оси, относящейся к этому, а красная линия будет иметь красный шрифт на своей оси.
Создание комбинированных диаграмм
Microsoft Excel не указывает, что вы можете создавать комбинированные диаграммы; однако это легко сделать. Для этого выберите данные и постройте первый тип диаграммы, например гистограмму. Затем выберите серию, которую вы хотите отобразить другим способом, например линейной диаграммой, и выберите Инструменты для работы с диаграммами > вкладка «Дизайн» > «Изменить тип диаграммы» и выберите второй тип диаграммы. Некоторые типы нельзя разумно комбинировать, например гистограмму и столбец, но линейная и столбчатая диаграммы хорошо работают вместе.
Автоматическое увеличение диаграмм Excel
Если у вас есть данные, объем которых со временем будет увеличиваться, вы можете создать диаграмму, чтобы она увеличивалась по мере добавления новых данных в хранилище данных. Для этого отформатируйте данные в виде таблицы, выбрав данные и на вкладке Главная на ленте выберите Форматировать как таблицу. Теперь, поскольку ваши данные отформатированы в виде таблицы, если вы основываете свою диаграмму на данных таблицы, добавление дополнительных данных в таблицу автоматически увеличит диаграмму.
Умное название диаграммы
Вы можете сделать так, чтобы заголовок диаграммы поднимал свое содержимое из ячейки на рабочем листе. Сначала добавьте заголовок диаграммы, используя Инструменты для работы с диаграммами > вкладка «Макет» > «Заголовок диаграммы», и расположите его, например, над диаграммой. Щелкните поле заголовка диаграммы, затем щелкните строку формул на ленте и введите ссылку на ячейку, содержащую данные, которые следует поместить в качестве заголовка диаграммы. Если вам нужно предварить его именем листа, поэтому D5 на листе 1 должно быть пустым. Теперь всякий раз, когда содержимое этой ячейки изменяется, заголовок диаграммы также будет меняться.
Переменные цвета для диаграмм Excel
Если у вас есть диаграмма, отображающая только один диапазон данных, вы обнаружите, что Excel окрашивает все столбцы в один и тот же цвет. Вы можете изменить это, щелкнув ряд, щелкните правой кнопкой мыши и выберите Форматировать ряд данных, а затем Заполнить. Если в диаграмму включена только одна серия, можно будет выбрать параметр Изменять цвета по точкам.
Конечно, вы всегда можете щелкнуть отдельную серию, чтобы выбрать ее, щелкнуть правой кнопкой мыши и выбрать Форматировать точку данных, а затем установить определенный цвет для этой точки данных.
Управление нулями и отсутствующими данными
Если у вас есть нулевые значения или отсутствуют данные на диаграмме, вы можете управлять отображением нулей, выбрав серию диаграммы, затем выберите Инструменты диаграммы > вкладка "Конструктор" и нажмите Выбрать данные > Скрытый и Пустые ячейки. Здесь вы можете выбрать, будут ли пустые ячейки отображаться как пробелы, нули или если диаграмма является линейной диаграммой, должна ли точка данных быть соединена линией. Нажмите ОК, когда сделаете свой выбор.
Отображение несмежных данных
Для диаграммы данных, которые не выстроены в столбцы рядом друг с другом, используйте ярлык Excel, удерживая клавишу Ctrl при выборе каждого диапазона данных. После выбора диапазонов создайте диаграмму на основе выбранных данных.
Сохранить диаграмму как шаблон
Чтобы сохранить диаграмму в качестве шаблона и использовать ее снова и снова, сначала создайте диаграмму, чтобы она выглядела так, как вы хотите. Выберите его, нажмите Инструменты для работы с диаграммами > вкладка "Конструктор" и нажмите Сохранить как шаблон. Введите имя диаграммы и нажмите Сохранить. Позже вы можете применить этот формат к другой диаграмме, используя его при создании диаграммы или существующей диаграммы, чтобы она выглядела как созданная вами. Для этого щелкните диаграмму, чтобы выбрать ее, на вкладке Инструменты работы с диаграммами > вкладка «Дизайн» выберите Изменить тип диаграммы, найдите параметр «Шаблоны», выберите свой шаблон и нажмите Хорошо.
Эти советы и рекомендации по созданию диаграмм помогут вам быстрее и эффективнее создавать более привлекательные диаграммы в Excel 2007 и 2010.
В недавнем посте я показал вам, как создать маркированную диаграмму в Excel. Затем Пит прислал мне еще один способ создания маркированной диаграммы в Excel. Но действительно невероятно то, что вы можете использовать эту же технику для создания более чем двух размеров ширины столбцов в ваших диаграммах Excel. Вот диаграмма, которую мне прислал Пит:
Разбивка
1) Настройка данных диаграммы
2) Создать столбчатую диаграмму с накоплением
3) Переключение строк/столбцов для диаграммы
4) Изменить цвета порогов шкалы
5) Переместите фактические и целевые ряды на дополнительную ось и увеличьте ширину разрыва
6) Изменение типов диаграмм фактических и целевых рядов
7) Создание и группировка фигур
8) Заменить столбец пользовательской формой
9) Удалить элементы легенды и вторую вертикальную ось
Шаг за шагом
1) Настройка данных маркированной диаграммы
Сначала вам нужно настроить данные для диаграммы. У вас будут значения для каждого порога производительности (красный, желтый и зеленый), а также значения фактической производительности и целевой производительности. Ваши данные будут выглядеть следующим образом:
2) Создайте столбчатую диаграмму с накоплением
Теперь мы можем создать нашу диаграмму. Выберите диапазон A1:D6. Затем на ленте «Вставка» выберите столбчатую диаграмму с накоплением.
Теперь ваша диаграмма будет выглядеть так:
3) Переключение строк/столбцов диаграммы
Этот шаг может потребоваться или не потребоваться в зависимости от данных вашей диаграммы. В этом примере нам нужно переключить строки/столбцы на диаграмме. Сначала выберите диаграмму, а затем выберите ленту «Дизайн». Затем нажмите кнопку переключения строки/столбца:
Теперь ваша диаграмма будет выглядеть так:
4) Изменить цвета пороговых значений показателя KPI
Диаграмма выглядит хорошо, теперь нам нужно изменить цвета рядов Bad, Poor и Good на их KPI Meter. Сначала щелкните правой кнопкой мыши серию Bad «Red», а затем выберите «Форматировать серию данных…» в меню:
Теперь измените цвет заливки на Сплошной красный цвет заливки.
Повторите этот процесс для плохого "желтого" ряда и для хорошего "зеленого" ряда. Ваша диаграмма будет выглядеть следующим образом:
Если у вас возникли проблемы с выбором нужного ряда данных, ознакомьтесь с этими сообщениями:
Самый быстрый способ выбрать ряд данных на диаграмме Excel
5) Переместите фактические и целевые ряды на дополнительную ось и увеличьте ширину разрыва
Затем переместите фактический ряд и целевой ряд на вторичную (2-ю) ось, чтобы они перекрывали наши пороговые значения показателей KPI. Сначала щелкните правой кнопкой мыши фактический ряд на диаграмме и выберите «Форматировать ряд данных…»
Теперь выберите «Параметры серии» и выберите переключатель «Вторичная ось», чтобы переместить серию на второстепенную ось. Кроме того, не закрывая диалоговое окно, измените ширину зазора на 500%.
Повторите этот шаг для целевой серии, чтобы переместить ее на 2-ю ось.
Теперь ваша диаграмма должна выглядеть так:
6) Измените типы диаграмм фактических и целевых рядов и перекрытие рядов на 100 %
Нам нужно изменить тип диаграммы фактических и целевых рядов на столбчатую диаграмму с кластеризацией для следующих шагов процесса. Для этого щелкните правой кнопкой мыши фактический ряд и выберите «Изменить тип диаграммы ряда…»
Затем измените типы диаграмм Фактический и Целевой на Кластерный столбец.
Теперь ваша диаграмма будет выглядеть так:
Это становится лучше, но нам нужно перекрыть ряды Целевой и Фактический. Для этого щелкните Целевой ряд и нажмите CTRL+1, чтобы открыть диалоговое окно «Формат ряда данных». Затем измените параметр «Перекрытие серий» на 100%.
Теперь ваша диаграмма будет выглядеть так:
7) Создание и группировка фигур
Вот первая часть настоящего трюка. Нам нужно создать две фигуры, наложить их друг на друга, изменить форму сзади, чтобы она не заполнялась, а затем сгруппировать две фигуры вместе. Для этого перейдите на ленту «Вставка», нажмите кнопку «Фигуры» и выберите фигуру «Прямоугольник».
Затем, наведя курсор на рабочий лист, перетащите фигуру прямоугольника.
Далее мы хотим изменить контур фигуры на «Без контура».
Кроме того, мы хотим изменить заливку фигуры на черный цвет:
Теперь, когда у нас есть реальная установка столбца, нам нужно сделать ее копию. Выберите фигуру, нажмите CTRL+C, чтобы скопировать ее, а затем нажмите CTRL+V, чтобы вставить ее на лист Excel.
Я предлагаю изменить цвет задней части на другой, чтобы упростить следующий шаг. Итак, здесь я изменил цвет формы спины на синий.
Теперь нам нужно сделать черную фигуру более узкой, чем синяя, а затем переместить ее поверх синей фигуры. Затем выберите обе фигуры, сначала выбрав одну фигуру, а затем, удерживая клавишу Shift, выберите другую фигуру.
Теперь давайте сгруппируем их, щелкнув правой кнопкой мыши фигуры и выбрав меню группы, а затем параметр "Группировать".
Почти готово. Но теперь нам нужно изменить заливку формы синего прямоугольника на «Без заливки». Это может показаться вам сложным, но выберите сгруппированную фигуру, а затем выберите синий прямоугольник.
После того, как вы его выбрали, вам нужно изменить заливку формы на "Без заливки"
8) Заменить столбец пользовательской формой
Поскольку мы создали специальную фигуру с черным цветом спереди и без заливки по бокам, мы можем использовать ее для замены обычных столбцов с кластеризацией для серии Actual.
Как создавать и добавлять собственные маркеры в диаграммы панели инструментов Excel
Итак, приступим. Вам нужно нажать на сгруппированную фигуру. Затем нажмите CTRL+C, чтобы скопировать фигуру в буфер обмена. Затем выберите свою диаграмму и выберите серию Actual. Затем нажмите CTRL+V, чтобы вставить фигуру в качестве новой фигуры для серии фактических кластеризованных столбцов.
Чтобы диаграмма выглядела так же, как у Пита, вы можете изменить ряд Целей на серый цвет заливки. Теперь ваша диаграмма будет выглядеть так:
9) Удалить элементы легенды и вторую вертикальную ось
Заключительные шаги включают удаление элементов легенды для показателей KPI. Для этого выберите диаграмму, затем легенду, затем элемент легенды и нажмите клавишу удаления.
Кроме того, мы хотим выровнять значения по двум осям с одинаковыми минимальными и максимальными границами. Это можно легко сделать, выбрав диаграмму, выбрав 2-ю ось и нажав клавишу удаления. Ваша окончательная диаграмма выглядит так:
Вот как вы можете обмануть Excel, чтобы позволить вам использовать 3 разных размера формы для столбцов (или более) вместо обычных 2 (по одному на ось). Довольно крутой трюк, если вы спросите меня. Как вы думаете? Кроме того, какая буллет-диаграмма вам больше нравится (эта или та, что в предыдущем посте)?
Демонстрация видео
Посмотрите видео, демонстрирующее технику построения диаграмм, описанную выше.
Читайте также: