Нелинейный масштаб график эксель
Построение диаграммы на большом объеме данных чаще всего на выходе дает загроможденный график с огромным количеством точек, на котором можно отследить только общие тенденции и тренды, но разглядеть, что происходит на конкретном небольшом участке - нереально.
Например, на графике ниже отлично видна общая тенденция изменения стоимость конкретной акции и всего пакета на отрезке с 1-ой по 500-ую сессию.
Построение диаграммы
Для начала нужно построить обычную диаграмму, которую мы потом сделаем масштабируемой. Никаких секретов здесь нет. Стройте ровно также, как обычно. Выбирайте тип, настраивайте форматирование. Для примера мы построили комбинированную диаграмму со вспомогательной осью.
Подробно разбирать этот процесс не будем, так как он не относится к теме статьи. Пример построения комбинированной диаграммы можно посмотреть в этом уроке .
Добавляем и настраиваем полосы прокрутки
Увеличение масштаба и сдвиги диаграммы вправо/влево мы будем осуществлять с помощью элемента управления "Полоса прокрутки". Это удобно, красиво и вполне интерактивно.
На вкладке "Разработчик" (если ее нет - здесь показано, как ее отобразить) выберите " Вставить " - " Элементы управления форм " - " Полоса прокрутки ".
Разместите рядом с диаграммой две горизонтальные полосы прокрутки. Можете сразу их подписать. Одна будет отвечать за выбор сессии, с которой начинается график, а вторая - за количество сессий на графике (то есть за масштаб). У нас получилось вот так.
Теперь нужно настроить полосы прокрутки. Кликните на первой правой кнопкой мыши и выберите " Формат объекта ". Откроется окно " Формат элемента управление ". Выберите вкладку " Элемент управления ".
- Текущее значение можно не задавать, или поставить 1;
- Минимальное значение - укажите 1. Это номер сессии, с которой допустимо начинать построение графика (а еще это количество ячеек, на которое будет сдвигаться наш динамический диапазон из исходной точки);
- Максимальное значение - зависит от количества точек данных на графике (строк в исходной таблице). У нас в таблице 500 строк, поэтому правильно будет задавать максимальное значение не больше 500 (ибо сдвиг более чем на 500 точек ничего не даст - закончатся данные). Но если впоследствии на полосе будет выбрано 500, то на графике будет всего одна точка (последняя). Это будет не слишком красиво. Условимся, что на графике может быть не менее 10 точек одновременно. Поэтому вместо 500 укажем максимальное значение 491.
- Шаг изменения - это величина, на которую будет изменять значение полосы прокрутки при клике на стрелочки по обеим ее сторонам. Оставим 1, чтобы имелась возможность максимально точно настраивать значение;
- Шаг изменения по страницам - это величина, на которую будет изменяться значение полосы прокрутки при клике на полосе справа или слева от бегунка (так сказать, быстрая перемотка). Установим тут значение 10.
- Связь с ячейкой. Самый важный момент. Укажите ссылку на ячейку, в которую будет выводиться число, выбранное полосой прокрутки. Впоследствии эта ячейка будет задействована в создании именованного диапазона для графика. Очень удобно будет задать этой ячейке какое-то имя, чтобы было проще к ней обращаться. Но в данном примере мы оставим обычные ссылки. Для первой полосы связанной ячейкой укажем &F&1 (Вы, разумеется, можете указать любую).
Вот так выглядят итоговые настройки:
Теперь настроим вторую полосу. Она, если помните, отвечает за количество точек (масштаб) диаграммы. Для нее укажем минимальным значением - 10 (мы условились, что на графике может быть не менее 10 точек). Максимальное значение укажем 500 (по количеству строк в таблице). Шаги изменения также зададим как 1 и 10. А свяжем всё это с ячейкой &G&1.
Теперь наши полосы прокрутки оживают. Двигая ползунок, мы меняем значения в связанных ячейках.
Перед тем как приступить к созданию динамических диапазонов, применим еще один небольшой трюк. Если мы выберем на первой полосе значение 491 (максимальное), а на второй - например, 50, то получим очень некрасивый график. Он будет начинаться с 491-й сессии и содержать 50 точек, в то время как наши данные заканчиваются на 500-й строке. Оставшиеся 40 точек будут просто пустыми и график будет кривой и непрезентабельный. Обойдем это следующей хитростью. В ячейку H1 введем формулу =МИН(G1;501-F1). Это формула будет всегда отображать в ячейке меньшее из значений: либо количество точек по второму ползунку, либо количество оставшихся до конца таблицы точек. Именно на эту ячейку мы будем ссылать при указании высоты именованного диапазона.
Создание динамических именованных диапазонов
Ключевая идея всего трюка - создание именованных диапазонов, размеры которых зависят от значений в ячейках, связанных с полосой прокрутки. Для каждого ряда нужно будет создать свой динамический диапазон. У нас таких рядов будет два: отдельная акция и стоимость всего пакета. Также нужно будет создать отдельный динамический диапазон для подписей данных, если Вы планируете их использовать на диаграмме.
Создадим диапазон для ряда единственной акции. Выберите команду " Данные " - " Диспетчер имен " - " Создать ". Введите имя ряда (например, "Акция"), область действия оставьте "Книга", а в поле "Диапазон" укажите формулу:
Чтобы Вы поняли, что она делает - покажем наши исходные данные. Они выглядят так:
Лист1!$A$1 - стартовая ячейка (шапка первого столбца с подписями данных);
Лист1!$F$1 - смещение по строкам - величина, выбранная первым ползунком;
1 - смещение по столбцам (нужно попасть из столбца A в столбец с Курсом акции "ФинИнт);
Лист1!$H$1 - высота диапазона - величина, выбранная вторым ползунком и скорректированная нашей формулой;
1 - ширина диапазона (всегда равна единице, так как для одного ряда диаграммы можно указать только один столбец данных).
На рисунке выше зеленым цветом выделен диапазон, построенный из 4-ой строки и масштабом в 12 точек данных (если функция СМЕЩ все же непонятна, то почитайте вот эту статью - мы подробно ее разбираем).
Итак, первый создаваемый именованный диапазон должен выглядеть так:
Аналогично создадим еще два - для второго ряда и для подписей данных. Мы назовём их "Пакет" и "Подписи". При создании формулы отличие будет только в третьем аргументе функции СМЕЩ (смещение по столбцам). Для второго ряда укажите 2 (чтобы попасть из столбца А в столбец С), а для подписей данных - 0 (они и так расположены в столбце А).
Получиться должно примерно вот это:
Перенос динамических диапазонов на диаграмму
Мы соединили полосы прокрутки и динамические диапазоны в единое целое. Осталось подключить к ним созданную в начале диаграмму. Кликните на график с единственной акцией один раз и в строке формул появится функция РЯД.
Первый ее аргумент - это название ряда. Его можно не трогать. Нам нужны второй и третий аргументы. Второй отвечает за подписи данных, а третий - за сами значения. Нам нужно заменить статичные ссылки на созданные диапазоны. Замените Лист1!$A$2:$A$501 на Лист1!Подписи , а Лист1!$B$2:$B$501 на Лист1!Акция и нажмите Enter .
Функция РЯД в строке формул заменит название листа на название файла (так как мы создавали имена уровня книги), а график тут же перестроится в зависимости от того, что выбрано в данный момент Вашими полосами прокрутки. Проделайте ту же операцию для второго ряда (указав его имя в третьем аргументе). В принципе, всё готово. Осталось навести немного красоты.
Введите в любую пустую ячейку (мы вводили в I1) формулу:
Эта формула будет формировать нам заголовок диаграммы в зависимости от того, какие значения выбраны на ползунках. Теперь кликните на заголовок диаграммы, чтобы выделить его, затем кликните в строке формул и сошлитесь на ячейку с новым динамическим названием. Нажмите Enter. При работе с полосой прокрутки, название на диаграмме будет меняться автоматически.
Осталось отключить сетку, скрыть исходные данные и вспомогательные ячейки, чтобы ничего не отвлекало от графика.
Кстати, чтобы скрытые исходные данные отображались на диаграмме, сделайте следующее. Выделите диаграмму, выберите " Конструктор " - " Выбрать данные " - " Скрытые и пустые ячейки " и поставьте галочку " Показывать данные в скрытых строках и столбцах ".
Получим вот такую прекрасно масштабируемую диаграмму, с которой не стыдно явиться на любое совещание.
У 3-D column, 3-D cone или 3-D pyramid chart (3-D pyramid chart) есть глубина (ряд) ось, также известная как ось z, которую можно изменить. Вы можете задать интервал между делениями, настроить подписи оси, изменить их положение относительно оси и вывести данные в обратном порядке.
Чтобы изменить шкалу других осей на диаграмме, см. изменение шкалы горизонтальной оси (категорий) или Изменение шкалы вертикальной оси (значений) на диаграмме.
На 3-D диаграмме щелкните ось глубины, которую вы хотите изменить, или выберите ее в списке элементов диаграммы сделайте следующее:
Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем элементов диаграммы, а затем щелкните Ось Z (рядов значений).
На вкладке Формат в группе Текущий фрагмент нажмите кнопку Формат выделенного.
В диалоговом окне Формат оси щелкните Параметры оси, если она не выбрана, а затем сделайте следующее:
Важно Следующие параметры масштабирования доступны только в том случае, если выбрана ось глубины.
Чтобы изменить интервал между делениями, в поле Интервал между делениями введите нужное число.
Примечание. Это число определяет количество рядов данных, которые отображаются между делениями.
Чтобы изменить интервал между подписями делений оси, в разделе Интервал между подписями выберите параметр Укажите единицу интервала, а затем в текстовом поле введите нужное число.
Совет Введите 1, чтобы отображалась подпись для каждого ряда данных, 2, чтобы вывести подпись для каждого второго ряда, 3 — для каждого третьего и т. д.
Чтобы изменить порядок рядов, установите флажок Обратный порядок рядов.
Примечание. При этом ряды данных будут отображаться на оси глубины в обратном порядке.
Чтобы изменить положение делений и подписей делений оси, выберите нужный параметр в полях Основные, Промежуточные и Подписи оси.
После изменения масштаба оси может потребоваться изменить способ ее форматирования. Дополнительные сведения об этом см. в статье Изменение отображения осей диаграммы.
Масштабирование не меняет глубину зазора или ширину зазора между рядами данных. Чтобы изменить глубину или ширину зазора, щелкните ряд данных правой кнопкой мыши и выберите формат ряда данных. Укажите нужные параметры в параметрах ряда.
На горизонтальной оси диаграммы, также называемой осью категорий и осью X, вместо числовых интервалов отображаются текстовые метки; кроме того, она поддерживает меньше параметров масштабирования, чем вертикальная ось диаграммы (ось значений, или ось Y).
Тем не менее вы можете задать следующие параметры оси:
Интервал между делениями и подписями.
Порядок отображения категорий.
Тип оси (даты или текст).
Точка пересечения горизонтальной и вертикальной осей.
Примечание: Следующая процедура относится к Office 2013 и более поздним версиям. Ищете по шагам для Office 2010?
На диаграмме щелкните ось категорий, которую вы хотите изменить, или выберите ее в списке элементов диаграммы сделайте следующее:
Щелкните в любом месте диаграммы.
Откроется вкладка Работа с диаграммами с дополнительными вкладками Конструктор и Формат.
На вкладке "Формат" в группе "Текущий выделение" щелкните стрелку в поле вверху и выберите горизонтальную ось (категорий).
На вкладке Формат в группе Текущий фрагмент нажмите кнопку Формат выделенного фрагмента.
В области "Формат оси" сделайте следующее:
Важно: Эти параметры масштабирования доступны только в том случае, если выбрана ось категорий.
Чтобы изменить порядок категорий, раз проверьте параметры оси, а затем выберите их в обратном порядке.
Чтобы изменить тип оси на ось текста или ось дат, разведите параметры оси, а затем в области "Тип оси" выберите ось текста или ось дат. Текст и точки данных распределены на оси текста равномерно. На оси дат даты отображаются в хронологическом порядке через заданные интервалы (единицы измерения), такие как количество дней, месяцев или лет, даже если даты на листе представлены не в последовательном порядке или с использованием разных единиц измерения.
Примечание: Заданное по умолчанию значение Автоматически выбирать на основе данных определяет тип оси, который наилучшим образом подходит для выбранного типа данных.
Чтобы изменить точку, в которой вертикальная ось (значений) пересекает горизонтальную ось (категорий), разведите параметры оси, а затем в области "Вертикальная ось" выберите номер категории и введите нужное число в текстовом поле или выберите пункт "По максимальной категории", чтобы указать, что вертикальная ось (значений) пересекает горизонтальную ось (категорий) после последней категории на оси x.
Чтобы изменить интервал между делеаниями, разведите делеания,а затем введите нужное число в поле "Интервал между деленами".
Примечание: Это число определяет количество категорий, которые отображаются между делениями.
Чтобы изменить положение делений оси, разведите делений, а затем выберите нужные параметры в полях "Основной" и "Незначительный".
Чтобы изменить интервал между подписями осей, разведите подписи, а затем в поле "Интервал между подписями" выберите "Единица интервала" и введите нужное число в текстовое поле.
Совет: Введите 1, чтобы отображалась подпись для каждой категории, 2, чтобы отображалась подпись для каждой второй категории, 3, чтобы отображалась подпись для каждой третьей категории и т. д.
Чтобы изменить расположение подписей оси, разведите подписи, а затем в поле "Расстояние от оси" введите нужное число.
Совет: Чтобы расположить подписи ближе к оси, введите меньшее число. Чтобы увеличить расстояние между подписью и осью, введите большее число.
Изменение масштаба горизонтальной оси (категорий) на диаграмме (Office 2010)На диаграмме щелкните ось категорий, которую вы хотите изменить, или выберите ее в списке элементов диаграммы с помощью указанных ниже действий.
Щелкните в любом месте диаграммы.
Откроется панель Работа с диаграммами с дополнительными вкладками Конструктор, Макет и Формат.
На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, затем щелкните Горизонтальная ось (ось категорий).
На вкладке Формат в группе Текущий фрагмент нажмите кнопку Формат выделенного фрагмента.
В диалоговом окне Формат оси в разделе Параметры оси выполните одно или несколько из указанных ниже действий.
Важно: Эти параметры масштабирования доступны только в том случае, если выбрана ось категорий.
Чтобы изменить интервал между делениями, в поле Интервал между делениями введите нужное число.
Примечание: Это число определяет количество категорий, которые отображаются между делениями.
Чтобы изменить интервал между подписями оси, в разделе Интервал между подписями выберите Единица измерения интервала, а затем в текстовом поле введите нужное число.
Совет: Введите 1, чтобы отображалась подпись для каждой категории, 2, чтобы отображалась подпись для каждой второй категории, 3, чтобы отображалась подпись для каждой третьей категории и т. д.
Чтобы изменить расположение подписей оси, в поле Расстояние до подписи от оси введите нужное число.
Совет: Чтобы расположить подписи ближе к оси, введите меньшее число. Чтобы увеличить расстояние между подписью и осью, введите большее число.
Чтобы изменить порядок категорий, установите флажок Обратный порядок категорий.
Чтобы изменить тип оси на ось текста или ось дат, в поле Тип оси выберите вариант Ось текста или Ось дат. Текст и точки данных распределены на оси текста равномерно. На оси дат даты отображаются в хронологическом порядке через заданные интервалы (единицы измерения), такие как количество дней, месяцев или лет, даже если даты на листе представлены не в последовательном порядке или с использованием разных единиц измерения.
Примечание: Заданное по умолчанию значение Автоматически выбирать на основе данных определяет тип оси, который наилучшим образом подходит для выбранного типа данных.
Чтобы изменить положение делений и подписей оси, выберите нужные параметры в полях Основные, Промежуточные и Подписи осей.
Чтобы изменить точку пересечения вертикальной оси (значений) и горизонтальной оси (категорий), в разделе Вертикальная ось пересекает выберите параметр В категории с номером, а затем введите нужное число в текстовом поле или выберите параметр В наибольшей категории, чтобы указать, что вертикальная ось (значений) должна пересекать горизонтальную ось (категорий) за последней категорией оси X.
После изменения шкалы оси может потребоваться настроить ее форматирование. Дополнительные сведения см. в статье Изменение отображения осей диаграммы.
Масштабирование никак не влияет на перекрытие рядов или ширину зазора между ними. Чтобы изменить параметры перекрытия рядов или ширину зазора между ними, щелкните ряд данных правой кнопкой мыши и выберите пункт Формат ряда данных. В разделе Параметры ряда укажите нужные значения.
По умолчанию минимальное и максимальное значения масштаба для каждой оси на диаграмме рассчитываются автоматически. Однако масштаб можно настроить в соответствии со своими потребностями. Например, если все точки данных в таблице данных находятся в интервале от 60 до 90, для оси значений (у) можно задать диапазон от 50 до 100, а не от 0 до 100. Если ось значений охватывает очень широкий диапазон, ее также можно изменить на логарифмическую шкалу.
Этот шаг относится только к Word для Mac: в меню "Вид" выберите пункт "Разметка печати".
На вкладке "Формат" в списке выберите горизонтальную ось (категорий) и нажмите кнопку "Формат области".
В области "Формат оси" сделайте следующее:
Важно: Эти параметры масштабирования доступны только в том случае, если выбрана ось категорий.
Чтобы изменить порядок категорий, раз проверьте параметры оси, а затем выберите их в обратном порядке.
Чтобы изменить тип оси на ось текста или ось дат, разведите параметры оси, а затем в области "Тип оси" выберите ось текста или ось дат. Текст и точки данных распределены на оси текста равномерно. На оси дат даты отображаются в хронологическом порядке через заданные интервалы (единицы измерения), такие как количество дней, месяцев или лет, даже если даты на листе представлены не в последовательном порядке или с использованием разных единиц измерения.
Примечание: Заданное по умолчанию значение Автоматически выбирать на основе данных определяет тип оси, который наилучшим образом подходит для выбранного типа данных.
Чтобы изменить точку, в которой вертикальная ось (значений) пересекает горизонтальную ось (категорий), разведите параметры оси, а затем в области "Вертикальная ось" выберите номер категории и введите нужное число в текстовом поле или выберите пункт "По максимальной категории", чтобы указать, что вертикальная ось (значений) пересекает горизонтальную ось (категорий) после последней категории на оси x.
Чтобы изменить интервал между делеаниями, разведите делеания,а затем введите нужное число в поле "Интервал между деленами".
Примечание: Это число определяет количество категорий, которые отображаются между делениями.
Чтобы изменить положение делений оси, разведите делений, а затем выберите нужные параметры в полях "Основной" и "Незначительный".
Чтобы изменить интервал между подписями осей, разведите подписи, а затем в поле "Интервал между подписями" выберите "Единица интервала" и введите нужное число в текстовое поле.
Совет: Введите 1, чтобы отображалась подпись для каждой категории, 2, чтобы отображалась подпись для каждой второй категории, 3, чтобы отображалась подпись для каждой третьей категории и т. д.
Чтобы изменить расположение подписей оси, разведите подписи, а затем в поле "Расстояние от оси" введите нужное число.
Совет: Чтобы расположить подписи ближе к оси, введите меньшее число. Чтобы увеличить расстояние между подписью и осью, введите большее число.
Чтобы расположить метку в нужномместе, разметите список "Подписи" и выберите вариант в списке.
Редкий менеджер в своей практике не сталкивается с необходимостью наглядного представления достигнутых результатов по сравнению с запланированными изначально. В разных компаниях я встречал много подобных диаграмм, называющихся "План-Факт", "Actual vs Budget" и т.д. Иногда их строят примерно так:
Неудобство такой диаграммы в том, что зритель должен попарно сравнивать столбцы плана и факта друг с другом, пытаясь удержать всю картину в голове, и гистограмма здесь, по-моему, не лучший вариант. Если уж и строить такую визуализацию, то однозначно нагляднее использовать графики для плана и факта. Но тогда перед нами встает задача наглядного попарного сравнения точек за одинаковые периоды и выделения разницы между ними. Давайте попробуем применить несколько удобных техник для этого.
Способ 1. Полосы повышения-понижения
Это наглядные прямоугольники, соединяющие попарно точки графиков плана и факта на нашей диаграмме. Причем их цвет зависит от того, выполнили мы план или нет, а размер показывает - на сколько:
Включаются такие полосы на вкладке Конструктор - Добавить элемент диаграммы - Полосы повышения/понижения (Design - Add Chart Element - Up/Down Bars) в Excel 2013 или на вкладке Макет - Полосы повышения-понижения (Layout - Up-Down Bars) в Excel 2007-2010. По умолчанию они будут черно-белые, но можно легко изменить их цвет, щелкнув по ним правой кнопкой мыши и выбрав команду Формат полос повышения/понижения (Format Up/Down Bars) . Очень рекомендую использовать полупрозрачную заливку, т.к. сплошная закрывает сами исходные графики.
К сожалению нет легкого встроенного способа регулировать ширину полос - для этого придется воспользоваться небольшим трюком.
Само собой, параметром (30) можно поиграться, чтобы получить нужную вам ширину экспериментальным путем.
Способ 2. Диаграмма с заливкой зоны между линиями плана и факта
Этот способ предполагает наглядную заливку (можно со шриховкой, например) области между графиками плана и факта:
Весьма эффектно, не так ли? Давайте попробуем это реализовать.
Для начала добавьте к нашей таблице еще один столбец (назовем его, допустим, Разница), где посчитаем формулой разность между фактом и планом:
Теперь выделим одновременно столбцы с датами, планом и разницей (удерживая Ctrl) и построим диаграмму с областями с накоплением, используя вкладку Вставка (Insert) :
На выходе должно получиться примерно так:
Следующим шагом выделим ряды План и Факт, скопируем их (Ctrl+C) и добавим в нашу диаграмму вставкой (Ctrl+V) - в нашем "бутерброде в разрезе" сверху должны появиться два новых "слоя":
Теперь переключим тип диаграммы для этих двух добавленных слоев в график. Для этого выделите по очереди каждый ряд, щелкните по нему правой кнопкой мыши и выберите команду Изменить тип диаграммы для ряда (Change Series Chart Type) . В старых версиях Excel 2007-2010 дальше можно выбрать нужный тип диаграммы (График с маркерами), а в новом Excel 2013 появится диалоговое окно со всеми рядами, где нужный тип выбирается для каждого ряда из выпадающих списков:
После нажатия на ОК увидим уже похожую на то, что нам нужно картину:
Легко сообразить, что осталось только выделить синюю область и поменять у нее цвет заливки на прозрачный Нет заливки (No Fill) . Ну, и навести общий блеск: добавить подписи, заголовок, удалить лишние элементы в легенде и т.д.
Читайте также: