Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Спарклайны в Excel — создание микрографиков
C приходом Excel 2010 появилась новая возможность создавать спарклайн-графики. Спарклайн – это небольшая диаграмма, помещенная в одну ячейку. Она позволяет быстро отслеживать динамически изменяющиеся данные. Поскольку спарклайны умещаются в одну ячейку, их принято использовать группами.
Хотя спарклайны во многом схожи с обычными диаграммами, его функциональность полностью отделена от функциональности диаграмм в Excel. К примеру, размещение диаграммы требует отдельного слоя и на диаграмме можно построить несколько рядов данных. Спарклайн отображается внутри ячейки и показывает только один ряд данных.
На рисунке отображены три различных вида спарклайнов, столбец H:
Для каждого спарклайна соответствует шесть точек данных (отображены слева). Всего их доступно три разных вида:
График – схож с диаграммой в виде графика. Для данного типа спарклайнов возможен вариант отображения маркера для каждой точки данных. Первая группа показывает спарклайн с маркерами.
Гистограмма – как и обычная гистограмма. Вторая группа показывает те же данные, в самом правом столбце.
Выигрыш/проигрыш – делит все данные на два типа: отрицательные и положительные и отображает их в виде верхнего и нижнего блока. Данный вид спарклайнов реализован в третьей группе.
Чтобы создать спарклайн-графику, выберите данные, которые вы хотите визуализировать. Затем перейдите во вкладку Вставка в группу Спарклайны и выберите один из трех типов спарклайнов: гистограмма, график или выигрыш/проигрыш.
Excel отобразит диалоговое окно Создание спарклайнов, где необходимо будет ввести диапазон данных и диапазон расположения. Как привило, графики располагаются рядом с данными для лучшего восприятия, но это не обязательное требование. Большую часть времени вы будете использовать пустой диапазон для хранения спарклайнов.
Однако Excel не мешает вам вставлять спарклайны в ячеки, которые уже содержат данные. Расположение спарклайнов, которое вы указываете, должно соответствовать источнику данных по количеству строк или количеству столбцов.
На рисунке ниже отображена группа спарклайнов в диапазоне H3:H13 в сравнении с обычным графиком. Каждый из этих видов визуализаций хорош по-своему. Если цель стоит — сравнение значений в различных городах в определенный месяц – оптимальным выбором будет график. Спарклайны, с другой стороны, полезны для отображения тенденций отдельных городов.
Спарклайн в Excel. Как настроить минидиаграммы в ячейках Excel?
Спарклайны можно создавать в Excel 2010 и более поздних версиях. Что это такое? Как настраивать спарклайны пустых и скрытых ячеек? Рассмотрим пример. Дана таблица результатов ЕГЭ по нескольким предметам за разные учебные годы.
Создавать большие диаграммы по всем предметам не очень удобно, поэтому построим небольшие диаграммы в ячейках столбца Динамика. Для этого выделим диапазон, содержащий все баллы. Перейдем на вкладку Вставка и в группе Спарклайны выберем График.
(Все изображения здесь и далее — кликабельны. Щелкните для увеличения)
Появится диалоговое окно Создание спарклайнов. В диапазоне данных будет выделенный нами диапазон значений баллов. Выберем диапазон расположения спарклайнов. Для этого щелкнем по кнопке с изображением стрелки.
Выделим ячейки, где будут располагаться спарклайны, и снова нажмем на кнопку со стрелочкой.
Нажмем ОК.
Мы получили в ячейках столбца Динамика спарклайны-линии, по которым мы можем видеть динамику изменения баллов по ЕГЭ по каждому предмету. Для спарклайнов можно менять Тип, устанавливать маркеры, выделять максимальное, минимальное, первое и последнее значения, отрицательные точки. Можно менять стиль. Эти параметры находятся на вкладке Конструктор в Работе со спарклайнами.
Мы рассмотрели спарклайны-графики, можно изменить тип Линия на тип Гистограмма. В ячейках появятся столбики значений.
Тип Выигрыш/Проигрыш нужен в том случае, если у нас дана таблица как отрицательных, так и положительных значений.
Рассмотрим таблицу, в которой отражается разница баллов по ЕГЭ между школьными результатами и областными показателями. Минус показывает, что результат школьный ниже областного, а плюс — выше.
В этом случае можно выбрать тип спарклайна Выигрыш/Проигрыш. Установим флажок Показать отрицательные точки. И мы видим, что показатели, которые ниже областных, выделены красным цветом. Цвета настраиваем в группе Стиль.
Спарклайны в Excel 2007 и более ранних версиях. Замена спарклайнам
В Excel 2003, 2007 спарклайнов нет, но для добавления диаграмм в ячейки можно использовать небольшую хитрость — функцию Excel ПОВТОР.
Рассмотрим пример, как с помощью этой функции построить минидиаграммы в ячейках.
Нам дан список учащихся и количество заданий, которые они выполнили на контрольной работе.
В столбце Динамика нужно построить диаграммы, отражающие число баллов учащихся.
Для начала перейдем на вкладку Вставка — Символ. Выберем шрифт Wingdings (можно другой) и любой символ этого шрифта, который более всего подойдет для наших диаграмм. Но вставлять символ не будем, выделим его и запомним Код символа. В нашем примере мы взяли черный ромб, его код 116.
Нажмем Закрыть. Выделим ячейку С2. Именно тут будет расположена первая диаграмма. На вкладке Главная выберем шрифт Wingdings (шрифт выбранного символа). И введем в ячейку С2 формулу: =ПОВТОР(СИМВОЛ(116);В2)
В данной формуле первым идет тот символ, который будет повторяться (в нашем случае ромб, его код 116). Символ мы вводим не с клавиатуры, а задаем с помощью кода. После точки с запятой указывается число повторений данного символа. В нашем случае это число равно числу набранных на контрольной работе баллов. После ввода формулы нажимаем Enter.
Мы получили 5 ромбов в ячейке С2. Копируем эту формулу вниз.
Таким образом, мы получили диаграммы в ячейках.
Если в качестве символа диаграммы мы хотим использовать символ с клавиатуры, то нужно ввести формулу: =ПОВТОР(«|»;В2). Команду Символ мы не используем, а в кавычках указываем нужный символ. Получим следующие диаграммы:
Для диаграмм, полученных таким способом, можно установить цвет шрифта. Если поменять число баллов в столбце В, то диаграммы автоматически изменятся. Такая таблица динамическая.
С помощью функции ПОВТОР можно настроить ручные спарклайны в Excel 2003, 2007.
Удаление спарклайнов
Кнопки клавиатуры Backspase и Delete не позволяют удалить из ячейки спарклайн. Для удаления нужно выделить ячейку, содержащую спарклайн, и выбрать Конструктор — Очистить (в группе Группировка). Можно удалить спарклайн с помощью контекстного меню. Для этого щелкнем правой кнопкой мыши по ячейке, выберем Спарклайны — Очистить выбранные спарклайны.
Настройка скрытых и пустых ячеек в спарклайнах
Скрытие ячеек с данными, содержащимися в спарклайне, может привести к его исчезновению. Чтобы избежать этого, нужно настроить параметры поведения спарклайна при скрытии ячеек с данными и при наличии пустых ячеек.
Выделим ячейку со спарклайном, на вкладке Конструктор выберем Изменить данные — Скрытые и пустые ячейки.
В открывшемся диалоговом окне настраиваем поведение спарклайна.
Спасибо за Вашу оценку. Если хотите, чтобы Ваше имя
стало известно автору, войдите на сайт как пользователь
и нажмите Спасибо еще раз. Ваше имя появится на этой стрнице.
Понравился материал?
Хотите прочитать позже?
Сохраните на своей стене и
поделитесь с друзьями
Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст
Ошибка в тексте? Мы очень сожалеем,
что допустили ее. Пожалуйста, выделите ее
и нажмите на клавиатуре CTRL + ENTER.
Кстати, такая возможность есть
на всех страницах нашего сайта
Как использовать Sparklines в Excel
В Excel 2010, 2013 и 2016 есть классная функция под названием спарклайн, которая в основном позволяет создавать мини-диаграммы внутри одной ячейки Excel. Вы можете добавить спарклайны в любую ячейку и держать их рядом с вашими данными. Таким образом, вы можете быстро визуализировать данные построчно. Это просто еще один отличный способ анализа данных в Excel.
Прежде чем мы начнем, давайте взглянем на быстрый пример того, что я имею в виду. В приведенных ниже данных у меня есть доход от десяти магазинов за последние шесть кварталов. Используя спарклайны, я могу быстро увидеть, какие магазины увеличивают доход, а какие работают плохо.
Очевидно, что вы должны быть осторожны при просмотре данных с использованием спарклайнов, поскольку они могут вводить в заблуждение в зависимости от того, какие числа вы анализируете. Например, если вы посмотрите на Магазин 1, вы увидите, что выручка выросла с 56 тыс. Долл. До 98 долл., А линия тренда идет прямо вверх.
Однако, если вы посмотрите на Магазин 8, линия тренда очень похожа, но выручка выросла только с 38 до 44 тысяч долларов. Таким образом, спарклайны не позволяют просматривать данные в абсолютном выражении. Созданные графики относятся только к данным в этой строке, что очень важно понимать.
Для сравнения я создал нормальную диаграмму Excel с теми же данными, и здесь вы можете ясно увидеть, как работает каждое хранилище по отношению к другим.
На этом графике Магазин 8 в значительной степени является плоской линией по сравнению с Магазином 1, который по-прежнему находится в тренде. Таким образом, вы можете увидеть, как одни и те же данные могут интерпретироваться по-разному в зависимости от того, как вы решили их отображать. Регулярные диаграммы помогают вам видеть тренды между множеством строк или данных, а спарклайны позволяют вам видеть тренды в одном ряду данных.
Я должен отметить, что есть также способ настроить параметры так, чтобы линии спарклайна могли также сравниваться друг с другом. Ниже я расскажу, как это сделать.
Создать Спарклайн
Итак, как мы можем создать спарклайн? В Excel это действительно легко сделать. Сначала нажмите на ячейку рядом с вашими точками данных, затем нажмите на Вставить а затем выбирать между Линия, колонка, а также Win / Loss под Sparklines,
Выберите любой из трех вариантов в зависимости от того, как вы хотите, чтобы данные отображались. Вы всегда можете изменить стиль позже, поэтому не беспокойтесь, если вы не уверены, какой из них лучше всего подойдет для ваших данных. Win / Loss Тип действительно имеет смысл только для данных, имеющих положительные и отрицательные значения. Должно появиться окно с просьбой выбрать диапазон данных.
Нажмите на маленькую кнопку справа и выберите одну строку данных. После того, как вы выбрали диапазон, нажмите кнопку еще раз.
Теперь нажмите OK, и ваша искровая линия или крошечный график должен появиться в этой одной ячейке. Чтобы применить спарклайн ко всем другим строкам, просто возьмите нижний правый край и перетащите его вниз, как если бы вы были ячейкой с формулой в нем.
Настройка Sparklines
Теперь, когда у нас есть наши спарклайны, давайте настроим их! Во-первых, вы всегда можете увеличить размер ячеек, чтобы графики были больше. По умолчанию они довольно крошечные и их трудно увидеть правильно. Теперь нажмите на любую ячейку со спарклайном, а затем нажмите на дизайн вкладка под Инструменты Sparkline,
Начиная слева, вы можете редактировать данные, если хотите включить больше столбцов или меньше. Под Тип, вы можете изменить тип мини-диаграммы, которую вы хотите. Опять же, выигрыш / проигрыш предназначен для данных с положительными и отрицательными числами. Под ПоказатьВы можете добавить маркеры на графиках, как Высокая точка, Низкая точка, Отрицательные очки, Первый & Прошлой точка а также Маркеры (маркер для каждой точки данных).
Под Стиль, вы можете изменить стиль для графика. По сути, это просто изменяет цвета линии или столбцов и позволяет выбирать цвета для маркеров. Справа от этого вы можете настроить цвета для спарклайна и маркеров индивидуально.
Единственным другим важным аспектом спарклайнов является Ось опции. Если вы нажмете на эту кнопку, вы увидите Параметры минимального значения вертикальной оси а также Параметры максимального значения вертикальной оси.
Если вы хотите сделать спарклайны относительно всех других строк, а не только для своей собственной строки, выберите То же самое для всех Sparklines под обоими заголовками. Теперь, когда вы посмотрите на данные, вы увидите, что вы можете сравнить графики в терминах абсолютных значений. Я также обнаружил, что просмотр диаграмм в виде столбцов облегчает просмотр данных при сравнении всех спарклайнов.
Как вы можете видеть сейчас, столбцы в Магазине 1 намного выше, чем столбцы для Магазина 8, который имел небольшую тенденцию к росту, но с гораздо меньшим значением выручки. Голубые столбцы — это низкие и высокие точки, потому что я проверил эти параметры.
Это все, что нужно знать о спарклайнах. Если вы хотите сделать для своего босса шикарную таблицу Excel, это способ сделать это. Если у вас есть какие-либо вопросы, не стесняйтесь оставлять комментарии. Наслаждайтесь!
Microsoft Excel
трюки • приёмы • решения
Как в таблице Excel создать спарклайн-графики
Одной из новых функций в Excel 2010 является спарклайи-графика. Спарклайн — маленькая диаграмма, отображаемая в одной ячейке. Она позволяет быстро отслеживать изменяющиеся с ходом времени данные. Поскольку спарклайн-графики очень компактны, их часто используют группами.
Хотя спарклайн выглядит как миниатюрный график (и иногда может занять место диаграммы), его функциональность полностью отделена от функциональности диаграмм в Excel. Например, диаграммы размещаются в графическом слое листа, и отдельная диаграмма может отображать несколько рядов данных. Спарклайн отображается внутри ячейки и показывает только один ряд данных. На рис. 150.1 приведены примеры каждого из трех типов графики спарклайн, отображенных в столбце Н.
Рис. 150.1. Примеры спарклайн-графиков
Каждый спарклайн-график отображает шесть точек данных (приведены на рисунке слева). В Excel 2010 доступны три типа спарклайн-графиков.
Обратите внимание, что данные отличаются. Каждая ячейка отображает изменение по сравнению с предыдущим месяцем. В спарклайнах каждая точка данных показана как верхний блок (выигрыш) или нижний блок (проигрыш). В этом примере положительные изменения по сравнению с предыдущим месяцем являются выигрышем, а отрицательные изменения по сравнению с предыдущим месяцем — проигрышем.
Чтобы создать спарклайн-графику, выберите данные, которые будут изображены на ней. Затем перейдите к группе Вставка ► Спарклайны и выберите один из трех типов спарклайнов: график, гистограмма или выигрыш/проигрыш. Excel отобразит диалоговое окно создания спарклайна, где вы выберете расположение графика. Как правило, вы должны указать спарклайн рядом с данными, но это необязательное требование. Большую часть времени вы будете использовать пустой диапазон для хранения спарклайнов.
Однако Excel не мешает вам вставлять спарклайны в ячейки, которые уже содержат данные. Расположение спарклайнов, которое вы указываете, должно соответствовать источнику данных по количеству строк или количеству столбцов. После создания нескольких спарклайнов у вас будет достаточно возможностей управления их внешним видом.
Рис. 150.2. Группа спарклайнов и график, который показывает те же данные
Как строить диаграммы в отдельных ячейках Excel и таблицах Google
Когда приходится анализировать большие таблицы или делать выводы сразу по нескольким строкам с данными, классические диаграммы не спасают. Они громоздкие и неудобные. Но в Excel и Google таблицах есть элегантное решение вопроса — спарклайны (мини-диаграммы в отдельных ячейках). В этой статье я расскажу, какие бывают спарклайны и как их создавать.
Что такое спарклайны?
Спарклайны (еще переводят как искрографики) — мини-диаграммы в отдельных ячейках рабочего листа. Каждая из них визуализирует строку данных в указанном диапазоне. Спарклайны — вспомогательный элемент, который помогает проанализировать цифры, понять их контекст и тенденции.
В представленной ниже таблице приведен пример использования спарклайнов для сравнения каналов трафика. Если подать эту информацию в виде классических диаграмм, они займут достаточно большой участок рабочего листа и тяжело будет сопоставить, какой строке данных соответствует конкретная диаграмма.
Как построить спарклайны в Excel
Спарклайны появились в Excel 2010, поэтому в более старой версии этого редактора электронных таблиц не удастся построить мини-диаграммы. Excel поддерживает три типа спарклайнов:
Создавать спарклайны в Excel легко. 1. Для начала необходимо вставить таблицу данных. Если у вас еще нет набора данных для визуализации, можете скачать этот csv файл и строить спарклайны на его основе. 2. Далее перейдем на вкладку «Вставка», где находится группа «Спарклайны».
Построим, для примера, спарклайн-гистограмму, которая будет отображать динамику изменения количества сеансов по различным группам каналов трафика. Для этого жмем кнопку «Гистрограмма», которая находится в группе «Спарклайны». 3. В открывшемся диалоговом окне «Создание спарклайнов» указываем диапазон данных (в нашем случае выбираем ячейки B2:M10, именно в них содержится информация об объеме трафика). В поле «Диапазон расположения» прописываем, куда поместить спарклайны. Для отображения мини-графиков наиболее удачно подходит диапазон N2:N10.
4. После нажатия «OK» в диапазон N2:N10 будут встроены спарклайны, отражающие данные в ячейках B2:M10. Также в ленте активируется новая вкладка «Работа со спарклайнами» — «Конструктор», с ее помощью можно изменить параметры диаграмм.
4.1. С помощью команд в группе «Спарклайны» можно изменить диапазон данных, на основе которых построены мини-графики, и место расположения визуализаций. 4.2. Группа команд «Тип» используется, когда необходимо изменить тип спарклайна. Например, мы построили гистограмму, а хотим поменять на график. 4.3. Группа «Показать» дает возможность отметить на спарклайне максимальную и минимальную точку, выделить первое и последнее значение, показать другим цветом отрицательные значения, а также проставить маркеры, если вы используете спарклайн с типом «Линия». 4.4. Группа «Стиль» содержит набор из 36 готовых стилей спарклайнов, при этом любой из них можно изменять на свое усмотрение, используя команды «Цвет спарклайна» и «Цвет маркера». 
4.5. Группа команд «Группировать» позволяет изменять масштаб и видимость оси для каждого спарклайна, а также группировать и разгруппировывать мини-графики.
Как построить спарклайн в таблицах Google
Процесс построения спарклайнов в таблицах Google несколько сложнее, чем в Excel, но по функциональности идентичный. При построении диаграмм в Google Spreadsheets вам доступен тип «Спарклайн». Но это не то, что нам надо. На самом деле это всего лишь тип диаграммы, который позволяет добавить много графиков, но разместить их в одной конкретной ячейке нельзя.
Аналог спарклайнов, описанных в этой статье, в таблицах Google можно построить с помощью функции SPARKLINE, относящейся к группе функций Google. Синтаксис функции SPARKLINE:
Данные и опции могут как располагаться на диапазоне рабочего листа, так и описываться в виде массива. Как правило, данные удобнее использовать, когда они расположены на рабочем листе, а опции — прописывать в виде массива внутри функции.
Что такое массив и как с ним работать
Массив — это виртуальная таблица, которая содержит строки и столбцы.
Массив всегда описывается внутри фигурных скобок, при этом необходимо соблюдать следующую пунктуацию:
Пример подходит для описания массива, если у вас российские региональные настройки. Для тех, у кого по умолчанию установлены американские настройки, для разделения столбцов будет использоваться запятая. Теперь разберемся с опциями, доступными для каждого типа спарклайнов. Первый и, наверное, самый важный параметр — это Charttype. С его помощью вы можете задать один из четырех доступных типов диаграмм:
Теперь рассмотрим дополнительные параметры, доступные для каждого типа диаграмм. Параметры линейных графиков (line):
Пример спарклайна типа «line»:
Пример формулы для спарклайна с типом «line»:
Параметры для гистограмм (bar):
Пример спарклайна типа «bar»:
Пример формулы для спарклайна с типом «bar»:
Параметры для столбчатой диаграммы и для диаграммы выигрыш / проигрыш (Column, Winloss):
Пример спарклайна типа «column»:
Пример формулы для спарклайна с типом «column»:
Пример спарклайна типа «winloss»:
Пример формулы для спарклайна с типом «winloss»:
Как я говорил ранее, необязательно описывать параметры в формуле в виде массива. Можно описать параметры спарклайна на рабочем листе, а в качестве второго аргумента функции SPARKLINE просто указать ссылку на массив, в котором находится описание параметров.
В ячейке N2 введена формула «=sparkline(B2:M2;$O$2:$P$5)». В качестве первого параметра прописан диапазон B2:M2, в котором содержится информация о данных для визуализации с помощью спарклайна. В качестве второго параметра указана ссылка на диапазон O2:P5, где содержится описание параметров спарклайна. В данном случае там будут следующие параметры:
Используя эти рекомендации, вы сможете легко создавать спарклайны в таблицах Google.
Выводы
Спарклайны — это вспомогательный элемент в работе с таблицами. Подобрав нужный тип спарклайна, вы сможете анализировать информацию с помощью емких и компактных визуализаций. Создать мини-диаграмму можно:
Научившись работать со спарклайнами, вы не только сэкономите пространство на рабочем листе, но и время вашего шефа, потраченное на изучение данных.
