Зачем нужны спарклайны

Что такое спарклайны в excel 2010?

27 августа, 2015 GoodUser

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

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

Что такое спарклайны в Excel 2010

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

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

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

Найти sparkline в Excel версии 2010 очень просто:

1. В горизонтальном меню программы откройте вкладку «Вставка», там есть целый блок, который называется «Спарклайны».
2. Пользователь может воспользоваться тремя типами спарклайнов: графиком, гистограммой, выигрышем/проигрышем. Забегая наперед, отмечу, что выбранный тип не является окончательным, в любой момент его можно будет заменить другим. Для этого необходимо будет перейти во вкладку «Работа со спарклайнами» – «Конструктор».
3. Вкратце о каждом типе: график напоминает обычную диаграмму. Он позволяет отображать маркер для каждой точки данных. Гистограмма показывает те же самые данные, но в крайнем правом столбце. И, наконец, выигрыш/проигрыш разделяет все данные на две категории: положительные и отрицательные, отражая их в виде верхнего и нижнего блоков.

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

Опубликовано в Excel Метки: Excel, диаграмма, спарклайн

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

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

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

Спарклины столбцов, отображают продажи с начала года в Москве, Сан-Франциско и Нью-Йорке

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

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

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

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

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

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

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

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

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

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

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

1. Щелкните спарклайн.
2. На вкладке Конструктор спарклайнов нажмите кнопку Изменить данные ,выберите скрытые и пустые ячейки, а затем выберите нужный параметр.

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

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

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

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

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

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

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

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

1. Щелкните спарклайн.
2. На вкладке Спарклины в области Маркерывыберите нужные маркеры.

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

1. Щелкните спарклайн.
2. На вкладке Спарклины в области Форматвыберите нужный стиль. Чтобы просмотреть другие стили, наведите курсор на интересующий вас элемент и щелкните .

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

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

1. Щелкните спарклайн.
2. На вкладке Спарклины в области Данныещелкните стрелку рядом с кнопкой Изменить ,выберите скрытые и пустые ячейки, а затем выберите нужный параметр.

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

1. Щелкните спарклайн, который нужно удалить.
2. На вкладке Спарклины в области Правкащелкните стрелку рядом с кнопкой Очистить ивыберите нужный параметр.

Спарклайн позволяет создать мини график в ячейке Excel

Спарклайн в Excel – это график в ячейке (можно назвать мини-графиком). Спарклайн очень удобный дополнительный инструмент для презентации тенденции общего тренда в отчетах. Он эффектно дополняет общее графическое представление о поведении конкретной ситуации. Но определенно его нельзя использовать для детального анализа данных. Почему так и какие преимущества спарклайнов перед обычными графиками? Ответим на этот вопрос конкретным примером.

Как сделать спарклайн в Excel

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

1. Заполните таблицу данными так как показано ниже на рисунке:
2. Выделите диапазон B2:D6 и выберите инструмент: «Вставка»-«Спарклайны»-«График».
3. В появившемся диалоговом окне «Создание спарклайнов» укажите значение в полю «Диапазон расположения», выделив диапазон E2:E6. И нажмите ОК.
4. Теперь если выделить диапазон ячеек E2:E6 у нас появляется дополнительная панель инструментов «Работа со спаркалнйами». В ней выберите инструмент: «Конструктор»-«Цвет маркера»-«Маркеры»-«Оранжевый 25%». Это действие сделало мини-графики более читабельными и привлекательными.

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

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

1. Выделите диапазон A1:D6. Потом выберите инструмент: «Вставка»-«Диаграммы»-«График».
2. Так как у нас категорий (месяцев) меньше чем рядов (магазинов) нужно поменять значения в строках и столбцах. Для этого выберите инструмент: «Работа с диаграммами»-«Конструктор»-«Строка/столбец».

В результате у нас получилась картинка, которая не требует лишних комментариев.

Минидиаграммы в ячейках листа

Одним из революционных нововведений в крайней версии 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:

Function NanoChart(rng As Range) As String Const MaxSymbols = 10 For Each cell In rng outstr = outstr & WorksheetFunction.Rept("|", cell / WorksheetFunction.Max(rng) * MaxSymbols) & Chr(10) Next cell NanoChart = outstr End Function

Затем вставляем функцию NanoChart в нужные ячейки, указывая в качестве аргументов числовые данные, как на рисунке выше. Для получившихся ячеек с микрографиками необходимо включить перенос по словам и поворот на 90 градусов через меню Формат — Ячейки — Выравнивание (Format — Cells — Alignment) . Константа MaxSymbols задает длину максимально высокого столбца в минигистограмме. Еще один подобный способ честно подсмотрен на сайте http://www.dailydoseofexcel.com/. Заключается в добавлении в файл пользовательской функции на VBA для автоматического построения спарклайнов — миниатюрных графиков внутри ячеек. Открываем редактор VBA (Alt+F11), добавляем новый модуль в книгу (меню Insert — Module) и копируем туда этот код на Visual Basic:

Function LineChart(Points As Range, Color As Long) As String Const cMargin = 2 Dim rng As Range, arr() As Variant, i As Long, j As Long, k As Long Dim dblMin As Double, dblMax As Double, shp As Shape Set rng = Application.Caller ShapeDelete rng For i = 1 To Points.Count If j = 0 Then j = i ElseIf Points(, j) > Points(, i) Then j = i End If If k = 0 Then k = i ElseIf Points(, k) < Points(, i) Then k = i End If Next dblMin = Points(, j) dblMax = Points(, k) With rng.Worksheet.Shapes For i = 0 To Points.Count - 2 Set shp = .AddLine( _ cMargin + rng.Left + (i * (rng.Width - (cMargin * 2)) / (Points.Count - 1)), _ cMargin + rng.Top + (dblMax - Points(, i + 1)) * (rng.Height - (cMargin * 2)) / (dblMax - dblMin), _ cMargin + rng.Left + ((i + 1) * (rng.Width - (cMargin * 2)) / (Points.Count - 1)), _ cMargin + rng.Top + (dblMax - Points(, i + 2)) * (rng.Height - (cMargin * 2)) / (dblMax - dblMin)) On Error Resume Next j = 0: j = UBound(arr) + 1 On Error GoTo 0 ReDim Preserve arr(j) arr(j) = shp.Name Next With rng.Worksheet.Shapes.Range(arr) .Group If Color >0 Then .Line.ForeColor.RGB = Color Else .Line.ForeColor.SchemeColor = -Color End With End With LineChart = "" End Function Sub ShapeDelete(rngSelect As Range) Dim rng As Range, shp As Shape, blnDelete As Boolean For Each shp In rngSelect.Worksheet.Shapes blnDelete = False Set rng = Intersect(Range(shp.TopLeftCell, shp.BottomRightCell), rngSelect) If Not rng Is Nothing Then If rng.Address = Range(shp.TopLeftCell, shp.BottomRightCell).Address Then blnDelete = True End If If blnDelete Then shp.Delete Next End Sub

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

Ссылки по теме

• Микрографики в надстройке PLEX
• Что такое макросы, куда вставлять код макросов на VBA, как их использовать.
• Бесплатная надстройка Sparklines Эдварда Тафта (англ.)