Как изменить максимальное значение в диаграмме excel
Обновлено: 06.07.2024
По умолчанию Microsoft Office Excel задает при создании диаграммы минимальное и максимальное значения шкалы вертикальной оси (оси значений, или оси Y). Однако шкалу можно настроить в соответствии со своими потребностями. Если отображаемые на диаграмме значения охватывают очень широкий диапазон, вы также можете использовать для оси значений логарифмическую шкалу.
На диаграмме щелкните ось значений, которую вы хотите изменить, или выберите ее в списке элементов диаграммы с помощью указанных ниже действий.
Щелкните в любом месте диаграммы.
Откроется вкладка Работа с диаграммами с дополнительными вкладками Конструктор и Формат.
На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем щелкните Вертикальная ось (значений).
На вкладке Формат в группе Текущий фрагмент нажмите кнопку Формат выделенного.
В диалоговом окне Формат оси в разделе Параметры оси выполните одно или несколько из следующих действий:
Важно Эти параметры доступны только в том случае, если выбрана ось значений.
Чтобы изменить число, с которого начинается или заканчивается вертикальная ось (значений), в поле "Минимальное значение" или "Максимальное значение" введите другое число. При необходимости вы можете нажать кнопку "Сброс", чтобы вернуть исходное значение.
Чтобы изменить интервал делений и линий сетки диаграммы, для параметра "Основные единицы" или "Основные единицы" введите другое число в поле "Основные единицы" или "Мелкие единицы". При необходимости вы можете нажать кнопку "Сброс", чтобы вернуть исходное значение.
Чтобы изменить порядок значений, установите флажок Обратный порядок значений.
Примечание При изменении порядка значений на вертикальной оси (значений) подписи категорий по горизонтальной оси (категорий) зеркально отобразятся по вертикали. При изменении порядка категорий слева направо подписи значений зеркально отобразятся слева направо.
Чтобы применить к оси значений логарифмическую шкалу, установите флажок Логарифмическая шкала.
Примечание Логарифмическую шкалу нельзя использовать для представления отрицательных значений и нуля.
Чтобы изменить единицы, отображаемые для оси значений, выберите нужный вариант в списке Отображаемые единицы.
Для вывода подписи, описывающей единицы измерения, установите флажок Отображать на диаграмме.
Совет. Измените единицы, если значения являются большими числами, которые вы хотите сделать более краткими и понятными. Например, можно представить значения в диапазоне от 1 000 000 до 50 000 000 как значения от 1 до 50 и добавить подпись о том, что единицами являются миллионы.
Чтобы изменить положение делений и подписей оси, в разделе "Деления" выберите нужные параметры в полях Главные и Дополнительные.
Щелкните стрелку раскрывающегося списка в разделе Подписи и выберите положение подписи.
Для изменения точки, в которой горизонтальная ось (категорий) пересекает вертикальную (значений), в разделе Основание пересекает установите переключатель Значение оси и введите число в текстовое поле. Также можно установить переключатель Максимальное значение по оси, чтобы указать, что горизонтальная ось (категорий) пересекает вертикальную ось (значений) в самом большом значении.
Примечание При выборе параметра Максимальное значение по оси подписи категорий перемещаются на другую сторону диаграммы.
На диаграмме щелкните ось значений, которую вы хотите изменить, или выберите ее в списке элементов диаграммы с помощью указанных ниже действий.
Щелкните в любом месте диаграммы.
Откроется панель Работа с диаграммами с дополнительными вкладками Конструктор, Макет и Формат.
На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем щелкните Вертикальная ось (значений).
На вкладке Формат в группе Текущий фрагмент нажмите кнопку Формат выделенного фрагмента.
В диалоговом окне Формат оси в разделе Параметры оси выполните одно или несколько из следующих действий:
Важно Эти параметры доступны только в том случае, если выбрана ось значений.
Чтобы изменить число, с которого начинается или заканчивается вертикальная ось (значений), выберите параметр "Минимальное или максимальное значение" и введите другое число в поле "Минимальное значение" или "Максимальное значение".
Чтобы изменить интервал делений и линий сетки диаграммы, для параметра "Основные единицы" или "Основные единицы" нажмите кнопку "Фиксированная" и введите другое число в поле "Основные единицы" или "Основные единицы".
Чтобы изменить порядок значений, установите флажок Обратный порядок значений.
Примечание При изменении порядка значений на вертикальной оси (значений) подписи категорий по горизонтальной оси (категорий) зеркально отобразятся по вертикали. При изменении порядка категорий слева направо подписи значений зеркально отобразятся слева направо.
Чтобы применить к оси значений логарифмическую шкалу, установите флажок Логарифмическая шкала.
Примечание Логарифмическую шкалу нельзя использовать для представления отрицательных значений и нуля.
Чтобы изменить единицы, отображаемые для оси значений, выберите нужный вариант в списке Отображаемые единицы.
Для вывода подписи, описывающей единицы измерения, установите флажок Отображать на диаграмме.
Совет. Измените единицы, если значения являются большими числами, которые вы хотите сделать более краткими и понятными. Например, можно представить значения в диапазоне от 1 000 000 до 50 000 000 как значения от 1 до 50 и добавить подпись о том, что единицами являются миллионы.
Чтобы изменить положение делений и подписей оси, выберите нужные параметры в полях Основные, Промежуточные и Подписи осей.
Для изменения точки, в которой горизонтальная ось (категорий) пересекает вертикальную (значений), в разделе Горизонтальная ось пересекает щелкните Значение оси и введите число в текстовое поле. Также можно щелкнуть Максимальное значение по оси, чтобы указать, что горизонтальная ось (категорий) пересекает вертикальную ось (значений) в самом большом значении.
Примечание При выборе параметра Максимальное значение по оси подписи категорий перемещаются на другую сторону диаграммы.
Имеем, в качестве примера, вот такую табличку с информацией по количеству сотрудников и стоимостью четырех известных 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) :
На выходе должно получиться что-то похожее:
В этом же окне можно настроить шаг делений по горизонтальной оси - для нашего примера я поменял параметр Единицы измерения с месяцев на дни и задал значение 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, наконец-то, добавила новые типы диаграмм (водопад, Парето, Маримекко и др.) в стандартный набор. Очень хочется верить, что "лед тронулся" и в следующих обновлениях мы увидим еще больше красивых и наглядных вариантов. Возможно, когда-нибудь и описанная выше гистограмма с переменной шириной столбцов будет строиться без "шаманских танцев" из этой статьи, а за два движения. Будем надеяться :)
Качественная визуализация большого объема информации – это почти всегда нетривиальная задача, т.к. отображение всех данных часто приводит к перегруженности диаграммы, ее запутанности и, в итоге, к неправильному восприятию и выводам.
Вот, например, данные по курсам валют за несколько месяцев:
Строить график по всей таблице, как легко сообразить, не лучшая идея. Красивым решением в подобной ситуации может стать создание интерактивной диаграммы, которую пользователь может сам подстраивать под себя и ситуацию. А именно:
- двигаться по оси времени вперед-назад в будущее-прошлое
- приближать-удалять отдельные области диаграммы для подробного изучения деталей графика
- включать-выключать отображение отдельных валют на выбор
Выглядеть это может примерно так:
Нравится? Тогда поехали.
Шаг 1. Создаем дополнительную таблицу для диаграммы
В большинстве случаев для реализации интерактивности диаграммы применяется простой, но мощный прием – диаграмма строится не по исходной, а по отдельной, специально созданной таблице с формулами, которая отображает только нужные данные. В нашем случае, в эту дополнительную таблицу будут переноситься исходные данные только по тем валютам, которые пользователь выбрал с помощью флажков:
В Excel 2007/2010 к созданным диапазонам можно применить команду Форматировать как таблицу ( Format as Table) с вкладки Главная ( Home) :
Это даст нам следующие преимущества:
- Любые формулы в таких таблицах автоматически транслируются на весь столбец – не надо «тянуть» их вручную до конца таблицы
- При дописывании к таблице новых строк в будущем (новых дат и курсов) – размеры таблицы увеличиваются автоматически, включая корректировку диапазонов в диаграммах, ссылках на эту таблицу в других формулах и т.д.
- Таблица быстро получает красивое форматирование (чересстрочную заливку и т.д.)
- Каждая таблица получает собственное имя (в нашем случае – Таблица1 и Таблица2), которое можно затем использовать в формулах.
Подробнее про преимущества использования подобных Таблиц можно почитать тут.
Шаг 2. Добавляем флажки (checkboxes) для валют
В Excel 2007/2010 для этого необходимо отобразить вкладку Разработчик ( Developer) , а в Excel 2003 и более старших версиях – панель инструментов Формы ( Forms) . Для этого:
- В Excel 2003: выберите в меню Вид – Панели инструментов – Формы (View –Toolbars –Forms)
- В Excel 2007: нажать кнопку Офис – ПараметрыExcel – Отобразить вкладку Разработчик на ленте (OfficeButton –Exceloptions –ShowDeveloperTabintheRibbon)
- В Excel 2010: Файл – Параметры – Настройка ленты – включить флаг Разрабочик (File –Options –CustomizeRibbon –Developer)
На появившейся панели инструментов или вкладке Разработчик ( Developer) в раскрывающемся списке Вставить ( Insert) выбираем инструмент Флажок ( Checkbox) и рисуем два флажка-галочки для включения-выключения каждой из валют:
Текст флажков можно поменять, щелкнув по ним правой кнопкой мыши и выбрав команду Изменить текст ( Edit text) .
Теперь привяжем наши флажки к любым ячейкам для определения того, включен флажок или нет (в нашем примере это две желтых ячейки в верхней части дополнительной таблицы). Для этого щелкните правой кнопкой мыши по очереди по каждому добавленному флажку и выберите команду Формат объекта ( Format Control) , а затем в открывшемся окне задайте Связь с ячейкой ( Cell link) .
Шаг 3. Транслируем данные в дополнительную таблицу
Теперь заполним дополнительную таблицу формулой, которая будет транслировать исходные данные из основной таблицы, если соответствующий флажок валюты включен и связанная ячейка содержит слово ИСТИНА (TRUE):
Заметьте, что при использовании команды Форматировать как таблицу ( Format as Table) на первом шаге, формула имеет использует имя таблицы и название колонки. В случае обычного диапазона, формула будет более привычного вида:
Обратите внимание на частичное закрепление ссылки на желтую ячейку (F$1), т.к. она должна смещаться вправо, но не должна – вниз, при копировании формулы на весь диапазон.
Шаг 4. Создаем полосы прокрутки для оси времени и масштабирования
Теперь добавим на лист Excel полосы прокрутки, с помощью которых пользователь сможет легко сдвигать график по оси времени и менять масштаб его увеличения.
Полосу прокрутки ( Scroll bar) берем там же, где и флажки – на панели инструментов Формы ( Forms) или на вкладке Разработчик ( Developer) :
Рисуем на листе в любом подходящем месте одну за другой две полосы – для сдвига по времени и масштаба:
Каждую полосу прокрутки надо связать со своей ячейкой (синяя и зеленая ячейки на рисунке), куда будет выводиться числовое значение положения ползунка. Его мы потом будем использовать для определения масштаба и сдвига. Для этого щелкните правой кнопкой мыши по нарисованной полосе и выберите в контекстном меню команду Формат объекта ( Format control) . В открывшемся окне можно задать связанную ячейку и минимум-максимум, в пределах которых будет гулять ползунок:
Таким образом, после выполнения всего вышеизложенного, у вас должно быть две полосы прокрутки, при перемещении ползунков по которым значения в связанных ячейках должны меняться в интервале от 1 до 307.
Шаг 5. Создаем динамический именованный диапазон
Чтобы отображать на графике данные только за определенный интервал времени, создадим именованный диапазон, который будет ссылаться только на нужные ячейки в дополнительной таблице. Этот диапазон будет характеризоваться двумя параметрами:
- Отступом от начала таблицы вниз на заданное количество строк, т.е. отступом по временной шкале прошлое-будущее (синяя ячейка)
- Количеством ячеек по высоте, т.е. масштабом (зеленая ячейка)
Этот именованный диапазон мы позже будем использовать как исходные данные для построения диаграммы.
Для создания такого диапазона будем использовать функцию СМЕЩ ( OFFSET) из категории Ссылки и массивы ( Lookup and Reference) - эта функция умеет создавать ссылку на диапазон заданного размера в заданном месте листа и имеет следующие аргументы:
В качестве точки отсчета берется некая стартовая ячейка, затем задается смещение относительно нее на заданное количество строк вниз и столбцов вправо. Последние два аргумента этой функции – высота и ширина нужного нам диапазона. Так, например, если бы мы хотели иметь ссылку на диапазон данных с курсами за 5 дней, начиная с 4 января, то можно было бы использовать нашу функцию СМЕЩ со следующими аргументами:
Хитрость в том, что константы в этой формуле можно заменить на ссылки на ячейки с переменным содержимым – в нашем случае, на синюю и зеленую ячейки. Сделать это можно, создав динамический именованный диапазон с функцией СМЕЩ ( OFFSET) . Для этого:
- В Excel 2007/2010 нажмите кнопку Диспетчер имен (NameManager) на вкладке Формулы (Formulas)
- В Excel 2003 и старше – выберите в меню Вставка– Имя– Присвоить(Insert – Name – Define)
Для создания нового именованного диапазона нужно нажать кнопку Создать ( Create) и ввести имя диапазона и ссылку на ячейки в открывшемся окне.
Сначала создадим два простых статических именованных диапазона с именами, например, Shift и Zoom, которые будут ссылаться на синюю и зеленую ячейки соответственно:
Теперь чуть сложнее – создадим диапазон с именем Euros, который будет ссылаться с помощью функции СМЕЩ ( OFFSET) на данные по курсам евро за выбранный отрезок времени, используя только что созданные до этого диапазоны Shift и Zoom и ячейку E3 в качестве точки отсчета:
Обратите внимание, что перед именем диапазона используется имя текущего листа – это сужает круг действия именованного диапазона, т.е. делает его доступным в пределах текущего листа, а не всей книги. Это необходимо нам для построения диаграммы в будущем. В новых версиях Excel для создания локального имени листа можно использовать выпадающий список Область.
Аналогичным образом создается именованный диапазон Dollars для данных по курсу доллара:
И завершает картину диапазон Labels, указывающий на подписи к оси Х, т.е. даты для выбранного отрезка:
Общая получившаяся картина должна быть примерно следующей:
Шаг 6. Строим диаграмму
Выделим несколько строк в верхней части вспомогательной таблицы, например диапазон E3:G10 и построим по нему диаграмму типа График ( Line) . Для этого в Excel 2007/2010 нужно перейти на вкладку Вставка ( Insert) и в группе Диаграмма ( Chart) выбрать тип График ( Line) , а в более старших версиях выбрать в меню Вставка – Диаграмма ( Insert – Chart) . Если выделить одну из линий на созданной диаграмме, то в строке формул будет видна функция РЯД ( SERIES) , обслуживающая выделенный ряд данных:
Эта функция задает диапазоны данных и подписей для выделенного ряда диаграммы. Наша задача – подменить статические диапазоны в ее аргументах на динамические, созданные нами ранее. Это можно сделать прямо в строке формул, изменив
=РЯД(Лист1!$F$3;Лист1! $E$4:$E$10 ;Лист1! $F$4:$F$10 ;1)
=РЯД(Лист1!$F$3;Лист1! Labels ;Лист1! Euros ;1)
Выполнив эту процедуру последовательно для рядов данных доллара и евро, мы получим то, к чему стремились – диаграмма будет строиться по динамическим диапазонам Dollars и Euros, а подписи к оси Х будут браться из динамического же диапазона Labels. При изменении положения ползунков будут меняться диапазоны и, как следствие, диаграмма. При включении-выключении флажков – отображаться только те валюты, которые нам нужны.
Таким образом мы имеем полностью интерактивную диаграмму, где можем отобразить именно тот фрагмент данных, что нам нужен для анализа.
Если у вас есть диаграмма в Excel, как показано на скриншоте ниже, и теперь вы хотите отформатировать метки осей как тысячи / миллионы, например 100K, 1M, как вы можете решить эту проблему?
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Отформатируйте метки осей как тысячи / миллионы
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Вы можете перейти в диалоговое окно «Формат оси» для форматирования оси.
1. Щелкните правой кнопкой мыши на оси, метки которой вы хотите отформатировать как тысячи / миллионы, выберите Ось формата в контекстном меню.
3. Закройте диалоговое окно, теперь вы видите, что метки осей отформатированы как тысячи или миллионы.
Читайте также: