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

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

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

Одним из революционных нововведений в крайней версии Microsoft Excel 2010 стали инфокривые sparklines - миниграфики, помещающиеся внутри ячеек и наглядно отображающие динамику числовых данных:

2010_sparklines_1.gif

Чтобы создать подобные миниграфики, нужно выделить ячейки, куда мы хотим их поместить и воспользоваться кнопками группы Спарклайны (Sparklines) с вкладки Вставка (Insert):

2010_sparklines_2.gif

 

В открывшемся диалоговом окне нужно задать диапазон исходных данных и диапазон вывода:

2010_sparklines_3.gif

 

Созданные миниграфики можно всячески форматировать и настраивать  с помощью динамической вкладки Конструктор (Design):

2010_sparklines_4.gif

 

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

2010_sparklines_5.gif

 

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

2010_sparklines_6.gif

Что делать, если у вас пока старая версия Excel? Или нужен тип графика, которого нет в наборе спарклайнов? Переходим к следующим способам!

Способ 2. Дополнительные надстройки для микрографиков

На самом деле, идея подобных графиков витала в воздухе уже достаточно давно. Еще для 2003 версии Excel существовало несколько надстроек с подобным функционалом, самым известными из которых были замечательная бесплатная Sparklines Эдварда Тафта и и платные BonaVista microcharts (169$) и Bissantz SparkMaker (200$). Да и я в свое время тоже пытался сделать что-то подобное в надстройке PLEX.

Если брать в качестве примера бесплатную надстройку Sparklines, то после ее подключения мы получим новую вкладку (или панель инструментов) примерно такого вида:

2010-sparklines-addin.png

Как легко заметить, эта надстройка умеет строить заметно больше трех типов диаграмм :)

Единственный минус - надстройку нужно будет установить на всех компьютерах, где вы планируете работать с файлом, содержащим такие диаграммы.

Способ 3. Повтор символов N-раз

"Бюджетным вариантом" одномерных микрографиков являются повторяющиеся однотипные символы, имитирующие линейчатую диаграмму. Для этого можно воспользоваться текстовой функцией ПОВТОР (REPT), которая умеет выводить в ячейку любой заданный символ нужное количество раз. Для вывода нестандартных символов (зная их код) можно использовать функцию СИМВОЛ (CHAR). В общем и целом это выглядит так:

microcharts2.gif

Символ с кодом 103 - черный прямоугольник шрифта Webdings, поэтому не забудьте установить этот шрифт для ячеек C2:C12. Также можно поиграться с символами других шрифтов, например в столбце Е использован символ с кодом 110 из шрифта Wingdings.

Способ 4. Макросы

Способ представляет собой улучшенный предыдущий вариант, где набор повторяющихся символов (используется знак "|") создает не формула, а простая пользовательская функция на VBA. Причем для каждой ячейки создается отдельный столбец, т.к. функция использует символ переноса строки после каждого числа - выглядит это примерно так:

microcharts4.gif

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

microcharts3.gif

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

  


18.07.2013 18:12:42
LineChart впечатляет
27.08.2013 09:59:42
Очень нравится, как выглядит NanoChart. Но у меня , наверное, Excel (2007) не поддерживает какой-то формат. Даже скопировав ваш пример, в ячейке с графиком пишет ИМЯ? Пробовала все форматы. Подскажите, как исправить)
28.08.2013 08:46:42
Видимо, забыли разрешить макросы (Файл - Параметры - Центр управления безопасностью - Параметры центра управления безопасностью - Макросы - Разрешить все макросы) и перезапустить файл.
10.09.2013 15:16:24
Добрый день.
Когда вставлял модуль текст макроса становится красным и в одну строчку.
Откорректировал, вроде все записалось, в меню функций появился LineChart., но все равно выдает #ЗНАЧ!.
Помогите)
10.09.2013 15:46:29
Работает!!
Не выбрал цвет))
02.02.2015 18:44:43
NanoChart красив, но высоту ячеек нужно регулировать под каждую строку значений, или это тлько на mac так?
30.03.2015 00:26:09
Здравствуйте, Николай.
Спасибо за многочисленные полезные советы!
Спасибо за макрос NanoChart, очень изящно получается!
21.10.2015 13:59:57
Здравствуйте, Николай!
меня очень заинтересовали ваши мини диаграммы, но к сожалению, мне необходимо их построить по четко заданным параметрам к примеру мне нужды данные только с "красным цветом" и разместить их с столбце G
ABCDEFG
1155436
2633223
3444344
4732482
5845175
6476264
7325342
Не могли бы обьяснить как сделать минидиаграммы с четко заданными параметрами,
Спасибо
20.05.2016 08:58:16
Доброго времени суток, Николай я только учусь, по этому подскажите пожалуйста записаный макрос только для книги куда его записал или эту функцию можно вызвать в другой книге. И на счёт цвета в спарклайне, его можно задать в функции. Спасибо.
04.07.2016 17:44:36
У меня при копировании таблицы в MS Word ячейки, в которых есть sparkline, пустые. Подскажите, пожалуйста, как можно скопировать данные из excel в word не рисунком, а таблицей так, чтобы sparkline не исчезали?
04.07.2016 19:24:08
Никак. Либо всю таблицу копировать и вставлять как картинку в Word, либо вставлять как живую таблицу, но спарклайнов там не будет - не поддерживает Word их абсолютно.
04.07.2016 20:16:41
Павел, Ирина методом научного тыка нашел одно шаманство,.. (все-таки спарклайн это таки картинка, хотя долго думал как мелкософт это реализовал ;-) итак :
1) делаем столбец спарклайнов не сразу за данными, а отступив хотя-бы один столбец вправо (зачем будет ясно ниже)
2) копируем столбец спарклайнов и вставляем (внимание:-) специальной вставкой как "связанный рисунок" - это принципиально
3) копируем таблицу, но без столбца спарклайнов, захватив только столбец связанных рисунков, и вуаля )))

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

P/S
Да и еще лучше копировать и вставлять "связанный рисунок" для каждой ячейки спарклайна а не весь столбец,
вручную это геморно, но если автоматизировать то вполне реально. Это позволит вставлять в ворд отдельные картинки для каждой ячейки,
что улучшит дальнейшее форматирование документа уже в ворде. ИМХО.

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

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


да, примеры файлов прикложить не знаю куда, вышлю Ирине отдельно, хотя тут и так все понятно.
05.07.2016 10:20:16
Спасибо огромное! Получилось, правда рисунки немного разъехались, но это поправимо
25.09.2016 12:00:45
Спасибо огромное за то, что тратите на нас своё время. Все уроки подробные и понятные даже для меня. Работать стало намного интересней когда используешь (или пытаешься использовать) эти плюшки!