Спарклайны в excel (примеры) - как создать excel sparklines?

Возможности инструмента

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

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

Параметры линии тренда можно условно поделить на четыре блока:

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

Выведем на исходный график уравнение линии и коэффициент достоверности.

Как видите, значение близко к 0,5, это говорит о низкой достоверности полученной линии тренда, и дальнейший прогноз будет ошибочным.

Шаг 2

Так как мы рассматриваем аддитивную модель вида:

Найдем оценки сезонной компоненты как разность между фактическими уровнями ряда и значениями скользящей средней St+Et = Yt-Tt, так как Yt и Tt мы уже знаем.

Используем оценки сезонной компоненты (St+Et) для расчета значений сезонной компоненты St. Для этого найдем средние за каждый интервал (по всем годам) оценки сезонной компоненты St.

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

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

Далее, заполняем нашу таблицу значениями сезонной составляющей дублируя ряд каждые 12 месяцев, то есть три раза:

Использование скользящих средних в Excel

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

Временной ряд – это множество значений X и Y, связанных между собой. Х – интервалы времени, постоянная переменная. Y – характеристика исследуемого явления (цена, например, действующая в определенный период времени), зависимая переменная. С помощью скользящего среднего можно выявить характер изменений значения Y во времени и спрогнозировать данный параметр в будущем. Метод действует тогда, когда для значений четко прослеживается тенденция в динамике.

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

Задача. Проанализировать выручку предприятия за 11 месяцев и составить прогноз на 12 месяц.

Сформируем сглаженные временные ряды методом скользящего среднего посредством функции СРЗНАЧ. Найдем средние отклонения сглаженных временных рядов от заданного временного ряда.

По значениям исходного временного ряда строим сглаженный временный ряд методом скользящего среднего по данным за 2 предыдущих месяца. Формула скользящей средней в Excel. Используя маркер автозаполнения, копируем формулу на диапазон ячеек С6:С14.
Аналогично строим ряд значений трехмесячного скользящего среднего. Формула:
По такому же принципу формируем ряд значений четырехмесячного скользящего среднего.
Построим график заданного временного ряда и рассчитанные относительно его значений прогнозы по данному методу. На рисунке видно, что линии тренда скользящего среднего сдвинуты относительно линии исходного временного ряда. Это объясняется тем, что рассчитанные значения сглаженных временных рядов запаздывают по сравнению с соответствующими значениями заданного ряда. Ведь расчеты базировались на данных предыдущих наблюдений.
Рассчитаем абсолютные, относительные и средние квадратичные отклонения по сглаженным временным рядам. Абсолютные отклонения:

Относительные отклонения:

Средние квадратичные отклонения:

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

После сопоставления таблиц с отклонениями стало видно, что для составления прогноза по методу скользящей средней в Excel о тенденции изменения выручки предприятия предпочтительнее модель двухмесячного скользящего среднего. У нее минимальные ошибки прогнозирования (в сравнении с трех- и четырехмесячной).

Прогнозное значение выручки на 12 месяц – 9 430 у.е.

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

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

Выберите ячейки, которые будут служить исходными данными для первого спарклайна. Мы выберем диапазон B2: G2.
Щелкните вкладку «Вставка» и выберите нужный тип спарклайна. Например, спарклайновая диаграмма.
Откроется диалоговое окно «Создание спарклайнов». С помощью мыши выберите ячейку для размещения спарклайна, затем нажмите «ОК». В нашем случае мы выберем ячейку H2, ссылка на ячейку появится в поле Position range.
В выбранной ячейке появится спарклайновая диаграмма.
Удерживая левую кнопку мыши, перетащите маркер автозаполнения, чтобы скопировать спарклайн в соседние ячейки.
Спарклайны появляются во всех строках таблицы. На диаграмме ниже вы можете увидеть, как спарклайны показывают тенденции продаж для каждого торгового представителя за шестимесячный период.

Логические функции

Данные конструкции используются для построения больших и сложных формул.

И – истина, если все условия истинные;
ИЛИ – истина, если хотя бы одно условия истинное;

Для анализа различных условий используются следующие функции:

ЕСЛИ – для проверки одного события;
УСЛОВИЯ – то же самое, только с огромным количеством условий.

Последняя из указанных выше появилась только в редакторе Excel 2016. Ранее использовался вариант «ЕСЛИМН».

В качестве примера можно привести следующую таблицу.

В данном случае использовались сразу две функции: «ЕСЛИ» и «ИЛИ».

=ЕСЛИ(ИЛИ(D3=»Первая»;D3=»Вторая»);100;0)

Для проверки работы формулы можно использовать конструкцию с «ЕСЛИОШИБКА». Если всё составлено корректно, то вы увидите результат вычислений. В противном случае увидите введенное значение в текстовом виде.

Добавление линии тренда на график

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

Построим на основе таблицы график. Выделим диапазон – перейдем на вкладку «Вставка». Из предложенных типов диаграмм выберем простой график. По горизонтали – год, по вертикали – цена.
Щелкаем правой кнопкой мыши по самому графику. Нажимаем «Добавить линию тренда».
Открывается окно для настройки параметров линии. Выберем линейный тип и поместим на график величину достоверности аппроксимации.
На графике появляется косая линия.

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

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

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

лепестковый;
круговой;
поверхностный;
кольцевой;
объемный;
с накоплением.

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

Выделите одну из ячеек, содержащую спарклайн, на ленте появится вкладка «Конструктор», которая содержит все опции по форматированию и преобразованию спарклайнов. Сразу скажу: если вы делали массив «спарков», то все изменения формата по умолчанию будут применяться к целому массиву, а не к выделенной ячейке.

Чтобы изменить тип диаграммы, найдите на ленте Конструктор – Тип , и нажмите кнопку, соответствующую нужному типу «спарка». Изменения сразу отобразятся на листе.

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

Максимальная точка – отобразит маркер на графике в наибольшей точке;
Минимальная точка – маркер на графике в наименьшей точке;
Отрицательные точки – маркер в точках с отрицательными значениями;
Первая точка – маркер впервой точке массива;
Последняя точка – маркеры во всех точках;
Маркеры – отобразить все маркеры;

Чтобы выбрать цвета графика, найдите блок Конструктор – Стиль . Здесь можно выбрать один из готовых стилей, или назначить свои параметры. Нажмите «цвет спарклайна», чтобы выбрать свой цвет графика. В этом же меню можно задать толщину линии.

В меню «Цвет маркера» можно выбрать цвета маркеров по группам: максимальная точка, минимальная, отрицательные и т.д.

Чтобы изменить исходные данные, или ячейки, в которых содержатся спарклайны – найдите на ленте команду Конструктор – Изменить данные .

В раскрывающемся меню команды можно выбрать:

Изменить данные и расположение группы – открывается окно, где можно снова определить исходные данные и расположение соответствующих им спарклайнов;

Изменить данные отдельного спарклайна – изменяем исходные данные только для спарклайна в отдельной ячейке

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

Для настройки осей спарклайна есть раскрывающееся меню Конструктор – Группировать – Ось .

В этом меню можно выполнить следующие настройки:

Выбрать тип оси («Тип общей оси» или «Тип оси дат»). Если вы изображаете данные, распределенные по датам, лучше выбрать второй вариант, тогда Эксель распределит график не равномерно по ячейке, а в соответствии с осью времени. В примере ниже – таблица данных с 1 по 14 число, но в ней отсутствует информация за 5, 6 и 9 число. Если выбрать «Тип общей оси» (см. ячейку С15 ) – столбики гистограммы распределились пропорционально по всей ячейке, как будто присутствуют все данные. Но ведь это некорректно! Выбираем «Тип оси дат» (см. ячейку С16 ), и видим, 3 пустых места там, где информация не указана. И это правильное отображение временных данных

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

В этом же блоке на ленте доступны еще 3 важные кнопки:

Сгруппировать – собрать в группу выделенные спарклайны для совместного форматирования;
Разгруппировать – разорвать группировку для индивидуального форматирования каждого графика
Очистить – удалить спарклайны из ячеек.

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

Как построить линию тренда в MS Excel

Щелкните правой кнопкой мыши по одному из «синих» столбцов, и в контекстном меню выберите пункт «Добавить линию тренда» .

На листе диаграммы теперь отображается пунктирная линия тренда. Как видите, она не совпадает на 100% со значениями диаграммы — построенная по средневзвешенным значениям, она лишь в общих чертах повторяет её направление. Однако это не мешает нам видеть устойчивый рост числа посещений сайта — на общем результате не сказывается даже «летняя» просадка.

Линия тренда для столбца «Посетители»

Теперь повторим тот же фокус с «оранжевыми» столбцами и построим вторую линию тренда. Как я и говорил раньше: здесь ситуация не так хороша. Тренд явно показывает, что за расчетный период число просмотров не только не увеличилось, но даже начало падать — медленно, но неуклонно.

Ещё одна линия тренда позволяет прояснить ситуацию

Мысленно продолжив линию тренда на будущие месяцы, мы придем к неутешительному выводу — число заинтересованных посетителей продолжит снижаться. Так как пользователи здесь не задерживаются, падение интереса сайта в ближайшем будущем неизбежно вызовет и падение посещаемости.

Следовательно, владельцу проекта нужно срочно вспоминать чего он такого натворил летом («весной» все было вполне нормально, судя по графику), и срочно принимать меры по исправлению ситуации.

Как построить график спроса и предложения в Excel

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

Величина спроса – число товаров и услуг, которое человек готов купить в данный момент, в данном месте, за данную цену.

На величину объема сбыта влияют прямо и косвенно множество факторов:

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

Зависимость между величиной спроса и факторами – это функция спроса. В экономической практике принято рассматривать функцию спроса от цены. В данном случае все определяющие величину спроса факторы считаются неизменными.

Графическая иллюстрация функции спроса от цены – кривая спроса. Основное свойство данного экономического параметра: уменьшение цены ведет к возрастанию сбыта продукта. И, напротив, высокая стоимость продукта ограничивает спрос на него.

Обратная зависимость имеет фундаментальный характер. Потому ее считают законом спроса. Изобразим его наглядно с помощью графика.

Внесем данные по ценам на товар и по количеству проданных единиц в шкалу спроса:
Переходим на вкладку «Вставка», инструмент «Диаграммы» — выбираем тип графика.
Для настройки делаем график активным, чтобы появилось дополнительная группа закладок под названием «Работа с диаграмами». Выбераем закладку «Конструктор», а в ней инструмент «Выбрать данные».
В окне «Выбор источника данных» из левой колонки «Элементы легенды (ряды)» удаляем данные «Продано».
В этом же окне в правой колонке «Подписи горизонтальной оси (категории)» жмем «Изменить».
Выделяем диапазон ячеек B2:B6 чтобы автоматически заполнить параметрами поле в появившимся окне «Подписи оси».

Обратите внимание! Количество продукции – ось абсцисс (горизонтальная). Цена – ось ординат (вертикальная)

Полноценный анализ ситуации на рынке невозможен без рассмотрения предложения. Это совокупность продуктов и услуг, которые присутствуют на рынке и предлагаются продавцом покупателю за определенную цену.

У данной экономической категории есть величина (число товаров и услуг, предлагаемых в конкретный временной промежуток, в конкретном месте, по определенной цене).

Цена предложения – прогнозируемый показатель. Это минимальная сумма, за которую продавец согласен предложить потребителю свой товар.

Объем предложения зависит, соответственно, от цены. Только в данном случае наблюдается обратная зависимость (ср.: объем): чем ниже цена, тем меньше предлагаемой продукции. Продавец лучше придержит часть товара на складе, чем отдаст за бесценок. Хотя на объем предложения влияет не только стоимость.

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

Добавим в демонстрационную табличку еще один столбец. Условно назовем его «Предложено»:

Теперь отобразим на графике сразу 2 показателя: «Спрос» и «Предложение». В одной области. Для этой цели подойдет точечная диаграмма.

Выделяем таблицу с исходными данными и выберем инструмент: «Вставка»-«Точечная»-«Точечная с гладкими кривыми и маркерами».

Снова выбираем «Конструктор»-«Выбрать данные» и задаем параметры в окне «Изменение ряда» для графиков:спрос:
предложение:

Следим, чтобы горизонталь показывала количество, а вертикаль – цену. Получаем результат:

Интерпретируем. Пересечение графиков иллюстрирует становление равновесной цены (50 рублей) и равновесного количества продаж (300 единиц). Область выше равновесной цены – избыток продукции. Производитель вынужден постепенно уменьшать стоимость. Область ниже равновесной цены – дефицит. Цены будут повышаться.

Как эффективно использовать спарклайны в Excel

Для того, чтобы использовать спарклайны наиболее эффективно, необходимо соблюдать следующие требования:

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

Эти требования не обязательны (можно и по одному объекту создать спарклайн), но их соблюдение помогает использовать спарклайны наиболее эффективно. Иначе они будут просто визуальным «шумом».

Рассмотрим на примере, как построить спарклайн.

Есть таблица данных с динамикой количества чеков по неделям.

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

Создать отчет с таблицей

У вас есть отчет о продажах за четыре года: 2015, 2016, 2017 соответственно. В эту таблицу включены сведения о стране, продукте и валовом объеме продаж.

Давайте выясним тренд продаж данного товара за разные годы.

Шаг 1 ) Создайте анализ столбцов рядом с валовыми продажами за 2018 год. На следующем шаге вы вставите спарклайн.

S #	Страна	Товар	Валовые продажи 2015 г.	Валовые продажи 2016 г.	Валовые продажи 2017 г.	Валовые продажи 2018
1	Канада	Карретера	32 370,00 долл. США	30 216 долларов США	$ 352 625,00	34 440 долларов США
2	Германия	Карретера	26 420,00 долл. США	$ 352 100,00	43 125,00 долл. США	7210,00 долл. США
3	Франция	Карретера	32 670,00 долл. США	4 404,00 $	600 300,00 долл. США	$ 4 473,00
4	Сингапур	Карретера	13 320,00 долл. США	6 181,00 долл. США	$ 34 056,00	9 282,00 долл. США
5	Соединенные Штаты Америки	Карретера	37 050,00 долл. США	8 235 долларов США	32 670,00 долл. США	22 296 долларов США
6	Мексика	Карретера	$ 529 550,00	236 400,00 долл. США	13 320,00 долл. США	423 500,00 долл. США
7	Китай	Карретера	$ 13 815,00	37 080,00 долл. США	534 450,00 долл. США	17 703,00 $
8	Испания	Карретера	30 216 долларов США	8 001,00 долл. США	645 300,00 долл. США	17 340,00 долл. США
9	Япония	Карретера	$ 37 980,00	603 750,00 долл. США	36 340,00 долл. США	41 250,00 долл. США
10	Корея, Республика)	Карретера	18 540 долларов США	10 944,00 долл. США	962 500,00 долл. США	32 052,00 $
11	Италия	Карретера	37 050,00 долл. США	32 280,00 долл. США	23 436 долларов США	9 192,00 долл. США
12	Коста-Рика	Карретера	$ 333 187,50	36 340,00 долл. США	$ 527 437,50	148 200,00 долл. США
13	Тайвань	Карретера	287 400,00 долл. США	$ 529 550,00	$ 37 980,00	488 950,00 долл. США
14	Аргентина	Карретера	15 022,00 долл. США	10 451,00 долл. США	$ 11 802,00	754 250,00 долл. США
15	Дания	Карретера	43 125,00 долл. США	$ 225 500,00	25 692,00 долл. США	33 210,00 долл. США
16	Бельгия	Карретера	9 225,00 долл. США	25 932,00 долл. США	8 001,00 долл. США	690 300,00 долл. США

Шаг 2) Выберите ячейку, в которую вы хотите вставить Sparkline. Перейдите в меню «Вставка» в строке меню. Выберите любой из Sparkline из списка Sparkline.

Шаг 3) Выберите любой из типов спарклайнов, которые вы хотите вставить. Он запросит диапазон ячеек. Выберите тип линии из доступного типа спарклайна.

Диапазон данных указывает, какие данные необходимо вставить в Sparkline. Диапазон расположения — это адрес ячейки, в которую вы хотите добавить Sparkline.

Шаг 4) Здесь диапазон данных взят из ячейки, в которой данные содержат «Валовые продажи с 2015 по 2018 год», а диапазон местоположений — от H3. После этого нажмите кнопку «ОК».

Шаг 5) Спарклайн будет вставлен в ячейку H3. Вы можете применить спарклайн ко всем данным, перетащив его вниз.

Теперь спарклайн создан.

Спарклайны в Excel 2007 и более ранних версиях. Замена спарклайнам

В Excel 2003, 2007 спарклайнов нет, но для добавления диаграмм в ячейки можно использовать небольшую хитрость — функцию Excel ПОВТОР.

Рассмотрим пример, как с помощью этой функции построить минидиаграммы в ячейках.

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

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

Нажмем Закрыть. Выделим ячейку С2. Именно тут будет расположена первая диаграмма. На вкладке Главная выберем шрифт Wingdings (шрифт выбранного символа). И введем в ячейку С2 формулу: =ПОВТОР(СИМВОЛ(116);В2)

Мы получили 5 ромбов в ячейке С2. Копируем эту формулу вниз.

Таким образом, мы получили диаграммы в ячейках.

Если в качестве символа диаграммы мы хотим использовать символ с клавиатуры, то нужно ввести формулу: =ПОВТОР(«|»;В2). Команду Символ мы не используем, а в кавычках указываем нужный символ. Получим следующие диаграммы:

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

С помощью функции ПОВТОР можно настроить ручные спарклайны в Excel 2003, 2007.

Информационные функции

Данные формулы в основном являются средством для анализа данных. Прописать их довольно просто. Их назначение следующее:

ЕПУСТО – проверка ячейки на наличие какого-нибудь значения;
ЕНД – проверка ячейки на наличие ошибки #Н/Д;
ЕЧИСЛО – проверка значения на соответствие числовому формату;
ЕОШИБКА – проверка на наличие любой ошибки;
ЕТЕКСТ – функция выдает истину, если в аргументе указано текстовое значение;
ЕНЕТЕКСТ – аналогичная проверка, только наоборот;
ЕОШ – функция вернет истинный результат, если в ячейке будет любая ошибка, отличная от #Н/Д;
для проверки четного или нечетного значения используются формулы ЕЧЁТН и ЕНЕЧЁТ;
ЕФОРМУЛА – проверка на наличие формулы в указанной ячейке.

Но есть и более сложная функция, о которой стоит поговорить отдельно.

ЯЧЕЙКА

Используя этот инструмент, вы сможете узнать всю нужную информацию об указанной клетке. При этом необходимо использовать обязательный параметр – «Тип сведений», при помощи которого вы сможете узнать:

цвет;
адрес;
столбец;
тип;
и многое другое.

Более подробно можно узнать на сайте Microsoft.

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

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

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

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

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

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

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

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

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

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

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

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

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

Настройка спарклайнов в Excel

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

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

Вы можете выбрать один из трех типов спарклайнов:

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

Решаем, что показывать

По умолчанию все точки на диаграмме Sparkline стилизованы одинаково. Однако вы можете изменить настройки, чтобы отличать поворотные точки от других.

Выберите график Sparkline .
На ленте перейдите на вкладку Дизайн .
В разделе « Показать » на вкладке «Дизайн» проверьте, какие точки вы хотите отобразить на графике спарклайнов.

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

Стилизация вашего спарклайна

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

Выберите свой график Sparklines.
Перейдите на вкладку « Дизайн » и в разделе « Стиль » щелкните « Цвет спарклайна» .
Выберите цвет для вашего спарклайна. На линейном графике это будет цвет линии, соединяющей точки. В столбчатых диаграммах или диаграммах выигрышей / проигрышей это будет цвет прямоугольников или квадратов соответственно.
Затем щелкните Цвет маркера , выберите точку, а затем выберите цвет . Вы можете установить разные цвета для каждой точки на графике.

Вы также можете сделать ось X видимой на графике спарклайна, отобразив горизонтальную линию, которая показывает ноль:

Выберите свой график Sparklines .
На ленте перейдите на вкладку Дизайн .
В разделах «Группа» щелкните « Ось» . Появится раскрывающееся меню.
В раскрывающемся меню нажмите « Показать ось» .

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

Сортировка данных в спарклайне

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

Выделите ячейки, в которые вы хотите ввести значения даты.
На вкладке « Главная » в разделе « Число » щелкните раскрывающееся меню.
В раскрывающемся меню выберите « Длинная дата» или « Краткая дата» .
В ячейки введите значения даты.
Выберите график Sparkline и перейдите на вкладку « Дизайн ».
В разделе « Группа » щелкните « Ось» .
В раскрывающемся меню выберите Тип оси даты . Откроется диалоговое окно » Диапазон дат» спарклайна.
В диалоговом окне » Диапазон дат» спарклайна выберите ячейки данных.

Теперь график будет построен заново, и данные будут отсортированы по дате, игнорируя порядок справа налево или вверх-вниз.

Еще одна корректировка, которую вы можете сделать, – это сделать так, чтобы график Sparkline отображал данные справа налево, а не слева направо. Это означает, что первое значение будет крайним левым, а последнее – крайним правым.

Выберите график Sparkline.
Перейдите на вкладку « Дизайн »> щелкните « Ось» .
В раскрывающемся меню выберите « Отобразить данные справа налево» .

Как использовать спарклайны в Excel 2010

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

Добавляем спарклайн в таблицу Excel

Откройте вкладку Insert (Вставка) и найдите там раздел Sparklines (Спарклайны).
Выделите одну или несколько ячеек для размещения спарклайнов.
Выберите тип спарклайна. Как Вы заметили, можно выбрать один из трёх типов – Line (График), Column (Гистограмма) и Win/Loss (Выигрыш/Проигрыш). Мы выберем Line (График).
В появившемся диалоговом окне Create Sparklines (Создание спарклайнов) Вам будет предложено указать диапазон данных на основании которых будет построен спарклайн. Поле Location Range (Диапазон расположения) уже содержит адреса ячеек, которые Вы выбрали на предыдущем шаге.Значение в поле Data Range (Диапазон данных) можно ввести вручную с клавиатуры или указать мышкой. Если все готово, нажмите ОК.
В выделенных ячейках появятся спарклайны.

Настройка спарклайна в Excel

Выделите одну или несколько ячеек, содержащих спарклайн, при этом на Ленте появится вкладка Design (Конструктор). В разделе Show (Показать) Вы можете настроить отображение маркеров для всех или некоторых точек спарклайн-графика, таких как минимум и максимум, начальное и конечное значение, положительные и отрицательные значения.
В разделе Style (Стиль) выберите подходящий дизайн и стиль спарклайна.
Стрелка в правом нижнем углу раздела Style (Стиль), указывающая вниз, откроет весь набор стандартных стилей для спарклайнов в Excel.
Если среди стандартных стилей Excel не нашлось подходящего Вам, то помогут опции Sparkline Color (Цвет спарклайна) и Marker Color (Цвет маркера), позволяющие задать настройки вручную.
Меню настроек Axis (Ось) открывает доступ к дополнительным параметрам, таким как Date Axis Type (Тип оси дат), Plot Data Righ-to-Left (Отобразить данные справа налево), а также Show Axis (Показать ось). Последний параметр позволяет отобразить горизонтальную ось при наличии на спарклайне данных пересекающих нулевую точку.

Спарклайн-гистограмма

Спарклайн-гистограмма, в отличие от спарклайн-графика, который мы рассмотрели ранее на примере, отображает Ваши данные в виде отдельных столбцов.

Спарклайн выйгрыша/проигрыша

Спарклайн выйгрыша/проигрыша показывает только положительный или отрицательный характер данных в выбранном диапазоне.

Как изменить тип спарклайна в Excel

Переключить вид отображения спарклайна с одного типа на другой очень просто. Для этого нужно выделить одну или несколько ячеек, содержащих спарклайн и выбрать желаемый тип отображения на вкладке Design (Конструктор).

Спарклайн станет отличным дополнением для Вашей таблицы Excel, которая содержит числовые данные, сделает её более наглядной и лёгкой для восприятия!

Оцените качество статьи

Нам важно ваше мнение: