Спарклайны в excel что это
Если у вас есть Excel в версии с 2010 года, то вы часто использовали график курса во времени в ячейках листа, известный в разговорной речи как Sparkline. Спарклайны особенно полезны при построении панелей мониторинга или графически расширенных отчетов. Рассмотрим еще один интересный метод использования спарклайнов, который будет обогащен сигналом положительной/отрицательной динамики изменения показателей. Такое решение позволит не только отслеживать тенденции данных, но и быстро находить области, требующие срочных дополнительных изменений.
Подготовка данных для спарклайнов калькулятора форекс-стратегий
Для примера работы со спарклайнами составим простейший калькулятор анализа торговой стратегии на рынке Forex одновременно по 16-ти валютным парам на протяжении целых 6-ти лет. Калькулятор позволить пользователю быстро визуально определить наиболее подходящие валютные пары и вместе с тем же исключить убыточные валюты для данной стратегии.
В качестве источника данных для построения графиков в ячейках мы будем использовать результаты теста стратегии, заранее предоставленные в форме отчета о балансе средств на торговом счете по состоянию на текущий год с разбивкой по отдельным валютным парам. Созданные нами спарклайны графики позволят вам быстро определить тенденцию успешности торговли и определить те валютные пары, для которых баланс средств стал ниже, чем в 2014 году. Следующая таблица подготовлена на втором листе под названием «Данные»:
Как видно из данных отчета условием тестирования было определено, что в 2014-ом году был открыт торговый счет на, который был внесен депозит на сумму 10 000. Далее баланс депозита изменялся то в плюс, то в минус на протяжении 6-ти лет в зависимости от того по какой валюте совершались торги с использованием одной и той же торговой стратегии.
Так же калькулятор будет содержать информацию об общей картине если торговать сразу по всем валютным парам на протяжении всего времени. Такой подход позволит трейдеру существенно диверсифицировать риски.
Создание шаблона интерактивной аналитической таблицы валют в Excel
Сам калькулятор – очень прост и будет обладать только лишь одним элементом управления. Для этого перейдите на первый лист «Калькулятор» и в ячейке D5 создайте выпадающий список со ссылкой на диапазон =Данные!$C$1:$H$1. Выберите инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных». В появившемся окне «Проверка вводимых значений» на вкладке «Параметры» из группы опций «Тип данных:» выберите опцию «Список». А в поле ввода «Источник» введите ссылку на используемые значения из ячеек второго листа как показано ниже на рисунке:
Также заполните заголовки таблицы. А затем заполняем табличную часть формулами:
- Ссылаясь на ячейки первого столбца таблицы на листе «Данные» получаем все наименования валютных пар заполняя таким образом формулой со ссылкой =Данные!A2 диапазон ячеек B6:B21 на первом листе «Калькулятор»:
- Диапазон ячеек D6:D21 на первом листе заполняем формулой с функцией =ГПР() для выборки значений со второго листа:
- Диапазон E6:E21 на первом листе заполняем формулой для вычисления уровня разницы изменения в процентном соотношении между начальным сальдо торгового счета в 2014-ом году – 10 000 и текущим по состоянию на указанный год пользователем:
- Не спешите устанавливать процентный формат, а вместо этого зададим свой пользовательский более информативный формат ячеек для диапазона E6:E21 вызвав диалоговое окно клавишами CTRL+1 где необходимо указать параметры как на рисунке:
- Условным форматированием экспонируем красным цветом отрицательные изменения в процентах для удобочитаемости анализа. Для этого предварительно выделите диапазон E6:E21 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило». В появившемся окне «Диспетчер правил условного форматирования» нажмите на кнопку «Создать правило» после чего отмечаем опцию «Форматировать только ячейки, которые содержат» и указываем параметры: 1-значение ячейки; 2-меньше; 3-=0. Так же не забудем указать формат для ячеек, которые соответствуют данному условию:
Теперь в калькулятор необходимо добавить саму информативную часть анализа, которая является определяющим фактором данного аналитического инструмента для тестирования торговой стратегии – это спарклайны.
Создание спарклайна для технического анализа курсов валют в Excel
Мы будем использовать особенные, умные спарклайны, которые должны иметь возможность динамически изменятся в зависимости от выбранного года пользователем в калькуляторе. Также все спарклайны в столбце E6:E21 должны автоматически изменять свой цвет в зависимости от положительной тенденции (синий) или отрицательной (красный).
Поэтому для умных спарклайнов нам необходимо сначала подготовить и должным образом обработать входящие данные. На втором листе с названием «Данные» создаем дополнительную таблицу заполненной одной и той же формулой в диапазоне J2:P17, как на рисунке:
=J$1;B2;НД())' >
Добавляем к таблице еще один столбец с формулой в ячейках Q2:Q17, которая буде определять убыточные валютные пары по состоянию на выбранный год в калькуляторе:
И в итоговой строке K18:P18 данной таблицы будем использовать формулу для главного спарклайна отображающего динамику общего баланса одновременно по всем валютным парам:
Теперь, когда все данные подготовлены переходим на лист «Калькулятор» и создаем спарклайны. Для этого выделите диапазон G6:G21 и выберите инструмент: «ВСТАВКА»-«Спарклайны»-«График»:
В окне «Создание спарклайнов» укажите в поле «Диапазон данных:» внешнюю ссылку Данные!J2:P17 на ранее созданную дополнительную таблицу на втором листе и нажмите ОК.
Изменение цвета спарклайнов по условию
Чтобы заставить спарклайны автоматически изменять свой цвет в зависимости от убыточности анализируемых валют придется использовать макрос. Чтобы создать такой макрос откройте редактор макросов выбрав инструмент: «РАЗРАБОТЧИК»-«Код»-«VisualBasic» или нажмите комбинацию клавиш ALT+F11:
Далее создайте новый модуль выбрав инструмент: «Insert»-«Module» и в созданный Module1 вставьте код:
Sub controlcolor()
Dim spkline As SparklineGroup
Dim lRow As Long
lRow = Cells(Rows.Count, "B" ). End (xlUp).Row
Range( "G6:G" & lRow).SparklineGroups.Ungroup
For Each spkline In Range( "G6:G" & lRow).SparklineGroups
With Application.Range(spkline.Item(1).SourceData)
If .Offset(, .Columns.Count).Resize(1, 1).Value = 1 Then
spkline.SeriesColor.Color = 255
Else
spkline.SeriesColor.ThemeColor = 5
End If
End With
Next
End Sub
Описание действий программы в коде макроса:
- Указать номер последней непустой строки в столбце B для автоматического определения количества спарклайнов.
- Определить адрес диапазона ячеек со спарклайнами вместе с тем получить доступ к их группе и разгруппировать.
- Методом перебора получить доступ к адресу внешней ссылки на диапазон данных для каждого спарклайна и проверить, имеет ли соседняя ячейка значение 1 (то есть в столбце loss на листе «Данные»).
- Если соседняя ячейка по отношению к адресу ссылки данных для спарклайна содержит число 1, тогда линия на графике окрашивается в красный цвет, в противном случае - в синий.
А теперь сделайте двойной щелчок по Лист1 (Калькулятор) в редакторе VBA-макросов VisualBasic и вставьте в него код для вызова макроса controlcolor из модуля 1:
Private Sub Worksheet_Change( ByVal Target As Range)
Dim rng As Range: Set rng = [D5] 'диапазон изменяемых ячеек
If Not Intersect(rng, Target) Is Nothing Then controlcolor
End Sub
В этом коде вызова все просто: при каждом изменении значений в ячейке D5 выполняется макрос controlcolor на текущем листе 1 (Калькулятор).
Теперь в ячейку G4 добавим главный спарклайн для отображения динамики общего баланса:
В главном спарклайне использем ссылку на ячейки в итоговой строке дополнительной таблице второго листа: Данные!J18:P18.
Также в ячейку E4 добавим формулу для вычисления изменения общего баланса торгового счета в процентном соотношении. При этом не забудем скопировать пользовательский формат с нижних ячеек.
И последняя формула в ячейке B3 для вычисления количества убыточных валютных пар в процентах для торгового счета в том или ином году:
После добавления текста с описанием калькулятора и выполнения небольших манипуляций с форматом значений для стильного оформления с удобным анализом получаем готовый результат:
Как видно на рисунке несмотря на то что графики в ячейках небольшого размера в некоторых случаях при некоторых задачах спарклайны наиболее правильное и удобное решение, которые сложно заменить другими диаграммами.
Спарклайны — это небольшие диаграммы внутри отдельных ячеек на листе. Благодаря своей компактности спарклайны выразительно и наглядно показывают закономерности в больших наборах данных. С помощью спарклайнов можно демонстрировать тренды в рядах значений (например, сезонные увеличения и уменьшения или экономические циклы) либо выделять максимальные и минимальные значения. Спарклайн выглядит понятнее всего, когда он расположен рядом с соответствующим набором данных. Чтобы создать спарклайн, нужно выделить диапазон значений, которые требуется проанализировать, а затем выбрать место для спарклайна.
Диапазон данных для спарклинов
Спарклины столбцов, отображают продажи с начала года в Москве, Сан-Франциско и Нью-Йорке
Создание спарклайнов
Выделите диапазон данных для спарклайна.
На вкладке Вставка нажмите кнопку Спарклайныи выберите нужный тип спарклайна.
В диалоговом окне Вставка спарклайнов обратите внимание на то, что первое поле уже заполнено с учетом того, что вы выбрали на шаге 1.
Выделите на листе ячейку или диапазон ячеек, куда нужно поместить спарклайн.
Важно: Размеры выбираемой области должны соответствовать диапазону данных. В противном случае Excel отобразит ошибки, свидетельствующие о том, что диапазоны не совпадают. Например, если в диапазоне значений три столбца и одна строка, следует выбрать смежный столбец и ту же строку.
Совет: При изменении данных на листе спарклайны автоматически обновляются.
Отметка точек данных на спарклайнах
С помощью маркеров можно выделять отдельные точки данных, такие как максимальные и минимальные значения.
На вкладке Конструктор спарклайнов в группе Показать выберите нужные маркеры, например высокий и низкий баллы. Вы можете настроить цвета маркера, нажав кнопку Цвет маркера.
Изменение стиля спарклайна
На вкладке Конструктор спарклайнов выберите нужный стиль.
Чтобы просмотреть другие стили, наведите курсор на интересующий вас элемент и щелкните .
Совет: Чтобы сразу же отменить примененный стиль, нажмите клавиши +Z.
Обработка пустых ячеек и нулевых значений
На вкладке Конструктор спарклайнов нажмите кнопку Изменить данные ,выберите скрытые и пустые ячейки, а затем выберите нужный параметр.
Удаление спарклайнов
Щелкните спарклайн, который нужно удалить.
На вкладке Конструктор спарклайнов щелкните стрелку рядом с кнопкой Очиститьи выберите нужный вариант.
Создание спарклайнов
Выделите диапазон данных для спарклайна.
На вкладке Диаграммы в области Вставка спарклайноввыберите нужный тип спарклайна.
В диалоговом окне Вставка спарклайнов обратите внимание на то, что первое поле уже заполнено с учетом того, что вы выбрали на шаге 1.
Выделите на листе ячейку или диапазон ячеек, куда нужно поместить спарклайн.
Важно: Размеры выбираемой области должны соответствовать диапазону данных. В противном случае Excel отобразит ошибки, свидетельствующие о том, что диапазоны не совпадают. Например, если в диапазоне значений три столбца и одна строка, следует выбрать смежный столбец и ту же строку.
Совет: При изменении данных на листе спарклайны автоматически обновляются.
Отметка точек данных на спарклайнах
С помощью маркеров можно выделять отдельные точки данных, такие как максимальные и минимальные значения.
На вкладке Спарклины в области Маркерывыберите нужные маркеры.
Изменение стиля спарклайна
На вкладке Спарклины в области Форматвыберите нужный стиль.
Чтобы просмотреть другие стили, наведите курсор на интересующий вас элемент и щелкните .
Совет: Чтобы сразу же отменить примененный стиль, нажмите клавиши +Z.
Обработка пустых ячеек и нулевых значений
На вкладке Спарклины в области Данныещелкните стрелку рядом с кнопкой Изменить ,выберите скрытые и пустые ячейки, а затем выберите нужный параметр.
Удаление спарклайнов
Щелкните спарклайн, который нужно удалить.
На вкладке Спарклины в области Правкащелкните стрелку рядом с кнопкой Очистить ивыберите нужный параметр.
Спарклайны — маленькие диаграммы, которые помещаются внутри ячеек листа и используются для наглядного представления данных и выявления тенденций в них. С помощью спарклайнов можно привлечь внимание к важным моментам, таким как сезонные колебания или экономические циклы, и выделить максимальные и минимальные значения цветом. Отображение тенденций в данных на листе может быть полезно, особенно если вы делитесь своими данными с другими людьми.
Выделите пустую ячейку рядом с данными, которые требуется отобразить в спарклайне.
На вкладке Вставка в группе Спарклайны нажмите кнопку График, Гистограмма или Выигрыш/проигрыш.
В поле Диапазон данных введите диапазон ячеек, содержащий данные, которые нужно отобразить в спарклайне.
Например, если данные хранятся в ячейках A, B, C и D строки 2, введите A2:D2.
Если вы выбираете ячейки на листе, щелкните , чтобы временно свернуть диалоговое окно, выберите ячейки на листе, а затем щелкните , чтобы развернуть диалоговое окно.
На ленте появится вкладка Работа со спарклайнами. Для настройки спарклайнов используйте команды на вкладке Конструктор.
Так как спарклайн внедрен в ячейку, любой введенный в нее текст будет отображаться на его фоне, как показано на рисунке ниже.
При выборе только одной ячейки у вас всегда будет возможность скопировать спарклайн в другие ячейки столбца или строки, перетащив их или использовав команду Заполнить вниз (Ctrl+D).
Настройка спарклайнов
После создания спарклайнов вы в любое время можете изменять их тип, стиль и формат.
Выберите спарклайны, которые требуется настроить, чтобы на ленте появилась вкладка Работа со спарклайнами.
На вкладке Конструктор выберите нужные параметры. Вы можете:
отобразить маркеры для выделения отдельных значений в спарклайнах-графиках;
изменить стиль или формат спарклайнов;
отобразить и изменить параметры оси;
Если в этом перетасковке выбрать параметр Тип оси дат, Excel откроется диалоговое окно Диапазон дат спарклайна. Здесь можно выбрать в книге диапазон, содержащий значения дат, которые вы хотите использовать для данных спарклайна.
Если вы щелкнули параметры настраиваемого значения в этом Excel откроется диалоговое окно Параметры вертикальной оси спарклайна. Здесь можно ввести минимальное или максимальное значение (в зависимости от выбранного параметра) для вертикальной оси данных спарклайна. По умолчанию Excel определяет, как отобразить данные спарклайна, чтобы с помощью этих параметров можно было контролировать минимальное и максимальное значения.
изменить способ отображения данных.
Если в этом drop-down выбрать параметр Изменить данные спарклайна, Excel откроется диалоговое окно Изменение данных спарклайна. Здесь можно выбрать в книге диапазон, содержащий данные, которые вы хотите использовать для данных спарклайна. Используйте этот параметр, если вы хотите изменить только один спарклайн.
Если в этом & выбрать параметр Скрытые пустые ячейки, Excel диалоговое окно Скрытые и Параметры ячейки. Используйте этот параметр, чтобы изменить способ Excel и значений NULL для данных спарклайна.
Пустые ячейки можно отбирать в качестверазрывов, нулейили Подключение точек данных линией.
Выберите параметр Показывать данные в скрытых строках и столбцах, чтобы включить данные Excel в скрытые строки и столбцы в данные спарклайна. Если этот параметр не Excel игнорировать данные в скрытых строках и столбцах.
Одним из революционных нововведений в крайней версии Microsoft Excel 2010 стали инфокривые sparklines - миниграфики, помещающиеся внутри ячеек и наглядно отображающие динамику числовых данных:
Чтобы создать подобные миниграфики, нужно выделить ячейки, куда мы хотим их поместить и воспользоваться кнопками группы Спарклайны (Sparklines) с вкладки Вставка (Insert) :
В открывшемся диалоговом окне нужно задать диапазон исходных данных и диапазон вывода:
Созданные миниграфики можно всячески форматировать и настраивать с помощью динамической вкладки Конструктор (Design) :
В частности, можно легко поменять цвет линий и столбцов спарклайна и выделить особыми цветами минимальные и максимальные значения:
Поскольку спарклайн не является отдельным графическим объектом, а выступает, по сути, в роли фона ячейки, то он нисколько не помешает ввести в ячейку текст, числа или другую информацию. Это открывает простор для фантазии - если немного пошаманить с выравниванием введенного текста, шириной и высотой ячейки, то можно достичь интересных результатов:
Что делать, если у вас пока старая версия Excel? Или нужен тип графика, которого нет в наборе спарклайнов? Переходим к следующим способам!
Способ 2. Дополнительные надстройки для микрографиков
На самом деле, идея подобных графиков витала в воздухе уже достаточно давно. Еще для 2003 версии Excel существовало несколько надстроек с подобным функционалом, самым известными из которых были замечательная бесплатная Sparklines Эдварда Тафта и и платные BonaVista microcharts (169$) и Bissantz SparkMaker (200$). Да и я в свое время тоже пытался сделать что-то подобное в надстройке PLEX.
Если брать в качестве примера бесплатную надстройку Sparklines, то после ее подключения мы получим новую вкладку (или панель инструментов) примерно такого вида:
Как легко заметить, эта надстройка умеет строить заметно больше трех типов диаграмм :)
Единственный минус - надстройку нужно будет установить на всех компьютерах, где вы планируете работать с файлом, содержащим такие диаграммы.
Способ 3. Повтор символов N-раз
"Бюджетным вариантом" одномерных микрографиков являются повторяющиеся однотипные символы, имитирующие линейчатую диаграмму. Для этого можно воспользоваться текстовой функцией ПОВТОР (REPT) , которая умеет выводить в ячейку любой заданный символ нужное количество раз. Для вывода нестандартных символов (зная их код) можно использовать функцию СИМВОЛ (CHAR) . В общем и целом это выглядит так:
Символ с кодом 103 - черный прямоугольник шрифта Webdings, поэтому не забудьте установить этот шрифт для ячеек C2:C12. Также можно поиграться с символами других шрифтов, например в столбце Е использован символ с кодом 110 из шрифта Wingdings.
Способ 4. Макросы
Способ представляет собой улучшенный предыдущий вариант, где набор повторяющихся символов (используется знак "|") создает не формула, а простая пользовательская функция на VBA. Причем для каждой ячейки создается отдельный столбец, т.к. функция использует символ переноса строки после каждого числа - выглядит это примерно так:
Чтобы использовать этот трюк у себя в файле, открываем редактор VBA (Alt+F11), добавляем новый модуль в книгу (меню Insert - Module) и копируем туда код функции NanoChart:
Затем вставляем функцию NanoChart в нужные ячейки, указывая в качестве аргументов числовые данные, как на рисунке выше. Для получившихся ячеек с микрографиками необходимо включить перенос по словам и поворот на 90 градусов через меню Формат - Ячейки - Выравнивание (Format - Cells - Alignment) . Константа MaxSymbols задает длину максимально высокого столбца в минигистограмме.
Теперь в мастере функций в категории Определенные пользователем появилась новая функция LineChart c двумя аргументами - диапазоном и кодом цвета графика. Если ее вставить в пустую ячейку, например, справа от числовой строки и скопировать затем, как обычно, на весь столбец, то получим весьма симпатичное отображение числовых данных в виде миниграфиков:
Спарклайн в Excel – это график в ячейке (можно назвать мини-графиком). Спарклайн очень удобный дополнительный инструмент для презентации тенденции общего тренда в отчетах. Он эффектно дополняет общее графическое представление о поведении конкретной ситуации. Но определенно его нельзя использовать для детального анализа данных. Почему так и какие преимущества спарклайнов перед обычными графиками? Ответим на этот вопрос конкретным примером.
Как сделать спарклайн в Excel
Разберем на конкретном примере, что такое спарклайны в Excel и как их использовать. Предприятие имеет 5 магазинов в разных районах города. Возле таблицы с показателями оборотов в месяцах первого квартала разместите мини-графики в ячейках отображающие тенденцию изменения оборотов за текущий период:
- Заполните таблицу данными так как показано ниже на рисунке:
- Выделите диапазон B2:D6 и выберите инструмент: «Вставка»-«Спарклайны»-«График».
- В появившемся диалоговом окне «Создание спарклайнов» укажите значение в полю «Диапазон расположения», выделив диапазон E2:E6. И нажмите ОК.
- Теперь если выделить диапазон ячеек E2:E6 у нас появляется дополнительная панель инструментов «Работа со спаркалнйами». В ней выберите инструмент: «Конструктор»-«Цвет маркера»-«Маркеры»-«Оранжевый 25%». Это действие сделало мини-графики более читабельными и привлекательными.
Несмотря на то, что спарклайны – это маленькие графики в ячейках их значения все равно легче читать, чем из таблицы. Мы не можем детально проанализировать ситуацию по каждому магазину. Но нам легко быстро оценить общую ситуацию по всех магазина одновременно. Которые магазины хорошо развиваются, а которые нет.
Чтобы оценить преимущества спарклайнов, создайте обычных график и сравните:
- Выделите диапазон A1:D6. Потом выберите инструмент: «Вставка»-«Диаграммы»-«График».
- Так как у нас категорий (месяцев) меньше чем рядов (магазинов) нужно поменять значения в строках и столбцах. Для этого выберите инструмент: «Работа с диаграммами»-«Конструктор»-«Строка/столбец».
В результате у нас получилась картинка, которая не требует лишних комментариев.
Читайте также: