Как построить спарклайны в excel

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Спарклайны в Excel — создание микрографиков

C приходом Excel 2010 появилась новая возможность создавать спарклайн-графики. Спарклайн – это небольшая диаграмма, помещенная в одну ячейку. Она позволяет быстро отслеживать динамически изменяющиеся данные. Поскольку спарклайны умещаются в одну ячейку, их принято использовать группами.

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

На рисунке отображены три различных вида спарклайнов, столбец H:

Для каждого спарклайна соответствует шесть точек данных (отображены слева). Всего их доступно три разных вида:

График – схож с диаграммой в виде графика. Для данного типа спарклайнов возможен вариант отображения маркера для каждой точки данных. Первая группа показывает спарклайн с маркерами.

Гистограмма – как и обычная гистограмма. Вторая группа показывает те же данные, в самом правом столбце.

Выигрыш/проигрыш – делит все данные на два типа: отрицательные и положительные и отображает их в виде верхнего и нижнего блока. Данный вид спарклайнов реализован в третьей группе.

Чтобы создать спарклайн-графику, выберите данные, которые вы хотите визуализировать. Затем перейдите во вкладку Вставка в группу Спарклайны и выберите один из трех типов спарклайнов: гистограмма, график или выигрыш/проигрыш.

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

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

Excel предоставляет достаточный набор инструментов для изменения внешнего вида спарклайна. Для этого вам необходимо перейти по вкладке Работа со спаркайнами -> Конструктор (данная вкладка появляется, когда вы выбираете ячейку со спарклайном).

На рисунке ниже отображена группа спарклайнов в диапазоне H3:H13 в сравнении с обычным графиком. Каждый из этих видов визуализаций хорош по-своему. Если цель стоит — сравнение значений в различных городах в определенный месяц – оптимальным выбором будет график. Спарклайны, с другой стороны, полезны для отображения тенденций отдельных городов.

Создание спарклайнов

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

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

Диапазон данных для спарклайнов

Расположение для спарклайнов

Спарклайны гистограммы, отображающие продажи с начала года для Портленде, Сан-Франциско и Нью Йорк

Создание спарклайнов

Выделите диапазон данных для спарклайна.

На вкладке Вставка нажмите кнопку спарклайныи выберите нужный вариант спарклайна.

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

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

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

Нажмите кнопку ОК.

Совет: При изменении данных на листе спарклайны автоматически обновляются.

Отметка точек данных на спарклайнах

С помощью маркеров можно выделять отдельные точки данных, такие как максимальные и минимальные значения.

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

Изменение стиля спарклайна

На вкладке конструктор спарклайнов выберите нужный стиль.

Чтобы просмотреть другие стили, наведите курсор на интересующий вас элемент и щелкните .

Совет: Чтобы сразу же отменить примененный стиль, нажмите клавиши +Z.

Обработка пустых ячеек и нулевых значений

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

Удаление спарклайнов

Щелкните спарклайн, который нужно удалить.

На вкладке конструктор спарклайнов щелкните стрелку рядом с кнопкой очиститьи выберите нужный параметр.

Создание спарклайнов

Выделите диапазон данных для спарклайна.

На вкладке диаграммы в группе Вставить спарклайнывыберите нужный вариант спарклайна.

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

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

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

Нажмите кнопку ОК.

Совет: При изменении данных на листе спарклайны автоматически обновляются.

Отметка точек данных на спарклайнах

С помощью маркеров можно выделять отдельные точки данных, такие как максимальные и минимальные значения.

На вкладке спарклайны в группе маркерывыберите нужные маркеры.

Изменение стиля спарклайна

На вкладке спарклайны в разделе Форматвыберите нужный стиль.

Чтобы просмотреть другие стили, наведите курсор на интересующий вас элемент и щелкните .

Совет: Чтобы сразу же отменить примененный стиль, нажмите клавиши +Z.

Обработка пустых ячеек и нулевых значений

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

Удаление спарклайнов

Щелкните спарклайн, который нужно удалить.

На вкладке спарклайны в разделе Правкащелкните стрелку рядом с кнопкой очиститьи выберите нужный параметр.

Спарклайны в Microsoft Excel

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

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

В некоторых источниках спарклайны называют инфолиниями.

Типы спарклайнов

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

Для чего используются спарклайны?

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

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

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

Создание спарклайнов в Excel

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

1. Выделите ячейки, которые будут служить исходными данными для первого спарклайна. Мы выберем диапазон B2:G2.
2. Перейдите на вкладку Вставка и укажите нужный тип спарклайна. Например, спарклайн-график.
3. Появится диалоговое окно Создание спарклайнов. Используя мышь, выделите ячейку для размещения спарклайна, а затем нажмите ОК. В нашем случае мы выберем ячейку H2, ссылка на ячейку появится в поле Диапазон расположения.
4. Спарклайн появится в выбранной ячейке.
5. Нажмите и, удерживая левую кнопку мыши, перетащите маркер автозаполнения, чтобы скопировать спарклайн в соседние ячейки.
6. Спарклайны появятся во всех строках таблицы. На следующем рисунке видно, как спарклайны наглядно демонстрируют тенденции продаж для каждого торгового представителя за полгода.

Изменение внешнего вида спарклайнов

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

Отображение маркеров

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

1. Выделите спарклайны, которые необходимо изменить. Если они сгруппированы в соседних ячейках, то достаточно выделить любой из них, чтобы выбрать сразу всю группу.
2. На вкладке Конструктор в группе команд Показать включаем опции Максимальная точка и Минимальная точка.
3. Внешний вид спарклайнов будет обновлен.

Изменение стиля

1. Выделите спарклайны, которые необходимо изменить.
2. На вкладке Конструктор нажмите на стрелку выпадающего меню, чтобы увидеть еще больше стилей.
3. Выберите необходимый стиль.
4. Внешний вид спарклайнов будет обновлен.

Изменение типа

1. Выделите спарклайны, которые необходимо изменить.
2. На вкладке Конструктор выберите желаемый тип спарклайна. Например, Гистограмма.
3. Внешний вид спарклайнов будет обновлен.

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

Изменение диапазона отображения

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

Блог Мачула Владимира

• Home »
• Редактор Excel »
• Построение спарклайнов в Excel

Начиная с 2010 в Excel появилась очень интересная возможность, о которой ранее только мечтали. Стало возможным визуально представить динамику изменения данных в строке таблицы при помощи так называемых спарклайнов или информационных кривых. И самое главное, эти кривые можно размещать непосредственно в ячейках таблицы в той же строке.

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

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

1. Берем таблицу с данными.

2. Добавляем столбец Динамика (можно строку), в котором нужно отобразить графики.

3. Выделяем диапазон ячеек (наш столбец), куда требуется вставить инфокривую.

4. На вкладке Вставить в группе Спарклайны указываем тип кривой. Обычно это линейный график, как наиболее наглядный.

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

В результате в выбранном диапазоне отобразятся инфокривые соответствующего типа.

При установке курсора в столбец с спарклайнами на ленте меню появляется вкладка Работа со спарклайнами|Конструктор, которая позволяет улучшить их информативность для пользователя.

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

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

Как видно, возможно указать один из трех вариантов обработки пустой ячейки для такого ряда значений (пропущено одно значение ряда):

• отображать как пустое значение. Отобразится:
  
• принять за нулевое значение, Отобразится:
  
• обозначить как соединительная линия между соседними значениями. Отобразится:
  

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