Как сделать спарклайн в 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 позволяет изменить внешний вид спарклайнов таким образом, чтобы их можно было сравнивать друг с другом.

Как изменить диапазон отображения

1. Выделите спарклайны, которые необходимо изменить.
2. На вкладке Конструктор выберите команду Ось. Появится выпадающее меню.
3. В параметрах для максимального и минимального значений по вертикальной оси включите опцию Фиксированное для всех спарклайнов.
4. Спарклайны будут обновлены. Теперь их можно использовать, чтобы сравнить объемы продаж между торговыми представителями.

Оцените качество статьи. Нам важно ваше мнение:

Источник: https://office-guru.ru/excel/sparklainy-v-microsoft-excel-121.html

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

157417 02.10.2012 Скачать пример

Способ 1. Спарклайны в Excel 2010

Одним из революционных нововведений в крайней версии 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 двумя аргументами — диапазоном и кодом цвета графика. Если ее вставить в пустую ячейку, например, справа от числовой строки и скопировать затем, как обычно, на весь столбец, то получим весьма симпатичное отображение числовых данных в виде миниграфиков:

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

  

Источник: https://www.planetaexcel.ru/techniques/4/43/

Спарклайны или микрографики в ячейках Excel

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

Например, на рис. 1 показаны изменения среднемесячной температуры июля в Москве за период наблюдений с 1879 по 2013 г. Чтобы рисунок был более наглядным показаны не абсолютные значения, а колебания относительно среднего значения за весь период наблюдений (18,4⁰С). Красным выделен абсолютный максимум (2010 г., 26,1⁰С) и минимум (1904 г., 14,6⁰С).

Рис. 1. Динамика среднемесячной температуры июля в Москве

Скачать заметку в формате Word или pdf, примеры в формате Excel

Чтобы создать спарклайн перейдите на вкладку Вставить → область Спарклайны (рис. 2). Excel предлагает три типа спарклайнов: график, гистограмму и диаграмму Выигрыш/проигрыш (рис. 3). Последняя показывает столбиками одинаковой высоты значения + и –.

Рис. 2. Меню Спарклайны

Рис. 3. Типы спарклайнов: (а) график, (б) гистограмма, (в) Выигрыш/проигрыш

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

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

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

Рис. 4. Окно Создание спарклайнов

Рис. 5. Объединение ячеек

После создания спарклайна на ленте появится вкладка Работа со спарклайнами. Для настройки спарклайнов используйте команды на вкладке Конструктор (рис. 6).

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

Заметил небольшую нестыковку: первый тип спарклайна вкладки Вставка (см. рис. 2) называется График, а на вкладке Конструктор – Линия (см. рис. 6).

Рис. 6. Вкладка Конструктор ленты Excel

Рассмотрим подробнее элементы вкладки Работа со спарклайнами на примере динамики Индекса потребительских цен США (CPI) за период 2006–2014 (рис. 7).

На момент публикации заметки последний отчет относился к марту 2014 г.

Как и в предыдущем пример, чтобы данные были более рельефными используются не абсолютные значения индекса (область А1:М11), а изменения в процентах к предыдущему месяцу (область А13:М22).

Рис. 7. Индекс потребительских цен США (CPI) за период с января 2006 по март 2014 г. Для справки: значение CPI=10 это среднее значение по 1913 г.

Поскольку спарклайн не может быть создан одновременно в нескольких ячейках, создайте его в ячейке N14, а затем «протащите» вниз, как обычную формулу!

Меню Изменить данные позволяет (рис. 8):

1)      Вызвать окно Создание спарклайнов (как на рис. 4) и изменить Диапазон данных и/или Диапазон расположения, как для одного спарклайна, так и для всех спарклайнов в группе. Например, вы можете изменить Диапазон расположения и перенести все спарклайны из ячеек N14:N22 в ячейки О14:О22. Или не отображать данные за каждый декабрь, сократив Диапазон данных с А14:М22 до А14:L22.

2)      Задать режим отображения скрытых и пустых ячеек. Например, на рис. 9 скрыты столбцы F, G, H. Вызвав опцию Скрытые и пустые ячейки, можно выбрать два варианта отображения скрытых строк/столбцов: отображать или не отображать.

3)      Выбрать режим отображения данных в спарклайне: по строкам или по столбцам. Эта команда применяется при одинаковом количестве строк и столбцов в Диапазоне данных.

Рис. 8. Опции меню Изменить данные

Рис. 9. Режим отображения скрытых и пустых ячеек

Меню Тип позволяет изменить тип спарклайна на один из трех доступных: график (он же линия), гистограмма, Выигрыш/проигрыш.

Меню Показать позволяет задать элементы спарклайна, которые будут отражаться другим цветом (рис. 10). Например, на рис. 1 включены опции Максимальная точка и Минимальная точка, а на рис. 9 включена опция Отрицательные точки. Заметим, что опция Маркеры доступна только для типа спарклайна Линия (рис. 11).

Рис. 10. Опции меню Показать

Рис. 11. Спарклайн Линия с выключенной (а) и включенной (б) опцией Маркер

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

Рис. 12. Опции меню Стиль

Опции меню Группировать. По умолчанию (например, на рис. 9) спарклайны, созданные друг под другом, сгруппированы. Если вы хотите их разгруппировать, воспользуйтесь соответствующей опцией в этой группе меню.

В дальнейшем их опять можно будет сгруппировать. Группировка влияет на управление Диапазоном данных и Диапазоном расположения в меню Изменить данные.

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

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

Наконец, опция Ось позволяет управлять параметрами спарклайна, как по оси ординат, так и по оси абсцисс (рис. 13). Например, на рис. 3а я включил опцию Показать ось.

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

Рис. 13. Опции меню Ось

Поскольку спарклайны размещаются в отдельном слое, в ячейке Excel со спарклайном может находиться любое значение или текст (рис. 15).

Рис. 15. Текст в ячейках со спарклайнами

[1] Использованы официальные материалы Microsoft

Источник: http://baguzin.ru/wp/sparklajny-ili-mikrografiki-v-yachejka/

Спарклайны — отслеживаем тенденции в рядах данных в MS EXCEL

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

В этой статье рассмотрим спарклайны (sparklines), которые появились в MS EXCEL 2010.

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

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

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

После нажатия ОК, в указанные ячейки будут вставлены спарклайны. 

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

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

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

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

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

Совет: Размещения маленьких диаграмм в одной ячейке также можно добиться с помощью повторов спецсимволов (см. статью Простая гистограмма в MS EXCEL (без использования Диаграмм)).

Источник: https://excel2.ru/articles/sparklayny-otslezhivaem-tendencii-v-ryadah-dannyh-v-ms-excel

Искрографики (sparklines): особенности применения и способы создания

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

 
Сегодняшний пост о не совсем стандартном способе визуализации данных, которая носит «контекстный» характер.

 

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

На карте ума (mind map) показаны характеристики и возможности создания sparklines

Альтернативные названия: инфокривые, миниграфики, спарклайны.

Искрографики применяют в отчетности, dashboards и инфографиках.

Пересказ небольшой части главы «Sparklines: Intense, Simple, Word-Sized Graphics» из книги Эварда Тафти (автора термина искрографик) можно прочитать здесь.

Как создавать:

1. Создавать искрографики позволяет стандартный функционал MS Office (Excel начиная с 2010 версии).

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

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

Есть возможность форматировать искрографики по своему вкусу

Цвет, маркеры, максимальные и минимальные значения можно установить и раскрасить в закладке «Конструктор», которая появляется, если поставить курсор в ячейку с инфографиком. Дополнительно:

• Подробней об искрографиках от MS можно прочитать на специализированном блоге Sparklines for Excel®
• Процесс создания, настройки спарклайна в Excel от Microsoft подробно описан здесь.
• Статья, в которой подробно (на русском языке) рассказывается о инфографиках в Excel — здесь.

2. Создавать искрографики можно при помощи надстройки для Excel (2003 и более поздние версии) от BonaVista.

Скачать надстройку можно по ссылке

После установки надстройки становятся доступны такие графики:

 Пример визуализации, которую можно создать:

3. Очень простой построитель спарклайна для строки данных — здесь.

4. Если нужно создать url для искрографика на основе строки данных, это можно сделать здесь.

5. Плагин jQuery Sparklines, генерирующий искрокрафик — здесь.

Примеры отчетов с использованием искрографиков

Курс обмена

  

Колебания температуры

Если Вам понравилась статья, можете поделиться ею при помощи социальных закладок 

Для получения обновлений блога подпишитесь на рассылку RSS.

Источник: http://irina-chubukova.blogspot.com/2011/09/blog-post.html

Спарклайны в Excel for Mac 2011

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

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

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

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

• Для того чтобы создать спарклайн, сперва выделим диапазоны значений, которые будут отображаться в спарклайне, и назначим им имена.
• Как присвоить имя диапазону, мы рассмотрели в этой статье.
• Для значений выручки по каждому месяцу по горшечным и контейнерным культурам, цветочной рассаде, черенкам назначим имена «Разница_г_к», «Разница_к_к», «Разница_ц_р», «Разница_ч» соответственно.

Далее переходим во вкладку «Диаграммы», группа «Вставить спарклайны» и выбираем «Гистограмма».

Спарклайны в Excel for Mac 2011 – вставка спарклайна

В открывшемся окне в диапазоне данных указываем «Разница_г_к», и адрес куда следует разместить спарклайн.

Спарклайны в Excel for Mac 2011 – изменение диапазона спарклайнов

Проделываем аналогичные процессы для всех диапазонов. Устанавливаем в группе «Маркеры» отметку «Отрицательные».

Спарклайны в Excel for Mac 2011 – Маркеры спарклайнов

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

Спарклайны в Excel for Mac 2011 – Спарклайн «Гистограмма»

Существует три типа спарклайнов: «График», «Гистограмма» и «Выигрыш/проигрыш».

Спарклайны в Excel for Mac 2011 – спарклайн-график, спарклайн-гистограмма, спарклайн выигрыш/проигрыш

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

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

Спарклайны в Excel for Mac 2011 – Маркеры

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

Спарклайны в Excel for Mac 2011 – Группировка спарклайнов

Для того чтобы удалить спарклайн в группе «Изменить» нажмите кнопку «Очистить» и «Очистить выбранные спарклайны» или «Очистить выбранные группы спарклайнов».

Спарклайны в Excel for Mac 2011 – Удалить спарклайн

Нажмите на кнопку «Изменить», чтобы изменить отдельный спарклайн или группу.

Источник: https://naprimerax.org/posts/15/sparklainy-v-excel-for-mac-2011

Маленькие графики в ячейках: спарклайны + аналог для версий Excel старше 2010

У вас есть много рядов данных: допустим, данных по продажам книг (цифры в примере случайные, но это не столь важно). И вы хотите посмотреть динамику по каждому ряду, но не создавая отдельных диаграмм. Для этого подойдут спарклайны — мини-графики в ячейках, которые появились в версии Excel 2010.

• Спарклайн — очень простой инструмент для быстрого визуального сравнения рядов данных.
• 
• Выделяем те ячейки, в которых будут спарклайны и в разделе «Вставка» на ленте инструментов выбираем один из трех типов спарклайна (график и гистограмма подойдут для отображения динамики продаж, а «выигрыш/проигрыш» — для тех случаев, когда вы хотите визуально разделить отрицательные, положительные и нулевые значения — пример будет ниже):
• 
• Выбираем диапазон с данными:
• 
• На вкладке «Конструктор», которая появляется на ленте при выделении ячеек со спарклайнами, можно изменить их внешний вид:
• — Поменять цвет спарклайна и маркеров;
• — Выделить первую, последнюю, максимальную и минимальную точку;
• — Добавить ось;
• — Выделить отрицательные точки.
• 
• В ячейках со спарклайнами можно вводить текст.
• Пример с типом спарклайна «Выигрыш-проигрыш». Спарклайн показывает, были по соответствующему ряду возвраты (отрицательные «продажи») и нулевые продажи; и если были — то когда:
• 
• Кстати, поменять тип спарклайна можно и после создания — на той же вкладке «Конструктор» на ленте инструментов.

А что делать, если у вас excel 2003 или 2007?

1. Воспользоваться функцией ПОВТОР (REPT), которая повторяет текст определенное количество раз
2. Ее первый аргумент — сам текст, а второй — количество повторов.
3. Выберите подходящий символ — можно какую-нибудь красивую иконку из шрифта Windings.

   Чтобы посмотреть, какие символы есть в шрифте и какие у них коды, напишите в столбец числа от 1 до 255, а правее введите функцию СИМВОЛ (шрифт в правом столбце нужно, соответственно, поменять на Windings):

4. Для продаж книг подойдет символ с номером 38 ????
5. 

А в целом хорош обычный квадратик — у него номер 110 в шрифте Windings. Обратите также внимание на шрифты Webdings и на Wingdings 2 и 3.

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

Источник: https://shagabutdinov.ru/sparklines/

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

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

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

Почему так и какие преимущества спарклайнов перед обычными графиками? Ответим на этот вопрос конкретным примером.

1. Заполните таблицу данными так как показано ниже на рисунке:



2. Выделите диапазон B2:D6 и выберите инструмент: «Вставка»-«Спарклайны»-«График».



3. В появившемся диалоговом окне «Создание спарклайнов» укажите значение в полю «Диапазон расположения», выделив диапазон E2:E6. И нажмите ОК.



4. Теперь если выделить диапазон ячеек E2:E6 у нас появляется дополнительная панель инструментов «Работа со спаркалнйами». В ней выберите инструмент: «Конструктор»-«Цвет маркера»-«Маркеры»-«Оранжевый 25%». Это действие сделало мини-графики более читабельными и привлекательными.

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



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

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

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

Источник: https://exceltable.com/grafiki/sparklayn-grafik-v-yacheyke

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

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

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

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

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

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

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

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

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

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

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

Источник: https://userologia.ru/chto-takoe-sparklajny-v-excel-2010.html

12 простых приёмов для эффективной работы в Excel

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

1. Быстро добавить новые данные в диаграмму:

Если для вашей уже построенной диаграммы на листе появились новые данные, которые нужно добавить, то можно просто выделить диапазон с новой информацией, скопировать его (Ctrl + C) и потом вставить прямо в диаграмму (Ctrl + V).

2. Мгновенное заполнение (Flash Fill)

Эта функция появилась только в последней версии Excel 2013, но она стоит того, чтобы обновиться до новой версии досрочно. Предположим, что у вас есть список полных ФИО (Иванов Иван Иванович), которые вам надо превратить в сокращённые (Иванов И. И.).

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

3. Скопировать без нарушения форматов

Вы, скорее всего, знаете про «волшебный» маркер автозаполнения — тонкий чёрный крест в правом нижнем углу ячейки, потянув за который можно скопировать содержимое ячейки или формулу сразу на несколько ячеек.

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

Этого можно избежать, если сразу после протягивания чёрным крестом нажать на смарт-тег — специальный значок, появляющийся в правом нижнем углу скопированной области.

Если выбрать опцию «Копировать только значения» (Fill Without Formatting), то Microsoft Excel скопирует вашу формулу без формата и не будет портить оформление.

4. Отображение данных из таблицы Excel на карте

В последней версии Excel 2013 появилась возможность быстро отобразить на интерактивной карте ваши геоданные, например продажи по городам и т. п. Для этого нужно перейти в «Магазин приложений» (Office Store) на вкладке «Вставка» (Insert) и установить оттуда плагин Bing Maps.

Это можно сделать и по прямой ссылке с сайта, нажав кнопку Add. После добавления модуля его можно выбрать в выпадающем списке «Мои приложения» (My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий лист. Останется выделить ваши ячейки с данными и нажать на кнопку Show Locations в модуле карты, чтобы увидеть наши данные на ней.

5. Быстрый переход к нужному листу

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

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

6. Преобразование строк в столбцы и обратно

• Если вам когда-нибудь приходилось руками перекладывать ячейки из строк в столбцы, то вы оцените следующий трюк:
• Выделите диапазон.
• Скопируйте его (Ctrl + C) или, нажав на правую кнопку мыши, выберите «Копировать» (Copy).
• Щёлкните правой кнопкой мыши по ячейке, куда хотите вставить данные, и выберите в контекстном меню один из вариантов специальной вставки — значок «Транспонировать» (Transpose).
• В старых версиях Excel нет такого значка, но можно решить проблему с помощью специальной вставки (Ctrl + Alt + V) и выбора опции «Транспонировать» (Transpose)

7. Выпадающий список в ячейке

Если в какую-либо ячейку предполагается ввод строго определённых значений из разрешённого набора (например, только «да» и «нет» или только из списка отделов компании и т. д.), то это можно легко организовать при помощи выпадающего списка:

1. Выделите ячейку (или диапазон ячеек), в которых должно быть такое ограничение.
2. Нажмите кнопку «Проверка данных» на вкладке «Данные» (Data — Validation).
3. В выпадающем списке «Тип» (Allow) выберите вариант «Список» (List).
4. В поле «Источник» (Source) задайте диапазон, содержащий эталонные варианты элементов, которые и будут впоследствии выпадать при вводе.

8. «Умная» таблица

• Если выделить диапазон с данными и на вкладке «Главная» нажать «Форматировать как таблицу» (Home — Format as Table), то наш список будет преобразован в «умную» таблицу, которая (кроме модной полосатой раскраски) умеет много полезного:
• Автоматически растягиваться при дописывании к ней новых строк или столбцов.
• Введённые формулы автоматом будут копироваться на весь столбец.
• Шапка такой таблицы автоматически закрепляется при прокрутке, и в ней включаются кнопки фильтра для отбора и сортировки.
• На появившейся вкладке «Конструктор» (Design) в такую таблицу можно добавить строку итогов с автоматическим вычислением.

9. Спарклайны

Спарклайны — это нарисованные прямо в ячейках миниатюрные диаграммы, наглядно отображающие динамику наших данных. Чтобы их создать, нажмите кнопку «График» (Line) или «Гистограмма» (Columns) в группе «Спарклайны» (Sparklines) на вкладке «Вставка» (Insert). В открывшемся окне укажите диапазон с исходными числовыми данными и ячейки, куда вы хотите вывести спарклайны.

После нажатия на кнопку «ОК» Microsoft Excel создаст их в указанных ячейках. На появившейся вкладке «Конструктор» (Design) можно дополнительно настроить их цвет, тип, включить отображение минимальных и максимальных значений и т. д.

10. Восстановление несохранённых файлов

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

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

На самом деле, есть неслабый шанс исправить ситуацию. Если у вас Excel 2010, то нажмите на «Файл» — «Последние» (File — Recent) и найдите в правом нижнем углу экрана кнопку «Восстановить несохранённые книги» (Recover Unsaved Workbooks).

11. Сравнение двух диапазонов на отличия и совпадения

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

3. Выберите на вкладке «Главная» — «Условное форматирование» — «Правила выделения ячеек» — «Повторяющиеся значения» (Home — Conditional formatting — Highlight Cell Rules — Duplicate Values).
4. Выберите вариант «Уникальные» (Unique) в раскрывающемся списке.

12. Подбор (подгонка) результатов расчёта под нужные значения

Вы когда-нибудь подбирали входные значения в вашем расчёте Excel, чтобы получить на выходе нужный результат? В такие моменты чувствуешь себя матёрым артиллеристом, правда? Всего-то пара десятков итераций «недолёт — перелёт», и вот оно, долгожданное «попадание»!

Microsoft Excel сможет сделать такую подгонку за вас, причём быстрее и точнее. Для этого нажмите на вкладке «Вставка» кнопку «Анализ „что если“» и выберите команду «Подбор параметра» (Insert — What If Analysis — Goal Seek).

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

После нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтобы подобрать требуемый вами итог с точностью до 0,001.

lifehacker.ru/

Источник: https://hr-portal.ru/article/12-prostyh-priyomov-dlya-effektivnoy-raboty-v-excel