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

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

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

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

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

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

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

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

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

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



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

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

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

10 простых приёмов сделать диаграммы Excel более привлекательными

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

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

1. Очистите фон диаграммы

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

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

В нашем случае нужно выделить любую линию сетки (кроме верхней – иначе будет выделена вся область построения) и вызвать диалоговое окно форматирования. В настройках выберите Цвет линии > Нет линий (Line color > No line).

2. Переместите легенду

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

Чтобы переместить легенду, просто откройте параметры форматирования (как только что научились делать!) и в разделе Параметры легенды (Legend Options) выберите нужную позицию.

Пока легенда выделена, изменяем размер шрифта на 12. Для этого не нужно выделять текст, достаточно выделить рамку легенды. Как выглядит лучше – решайте сами…

3.

Удалите легенду, если на диаграмме один ряд данных

Если на диаграмме показан только один ряд данных, то нет смысла оставлять легенду, которую Excel вставляет автоматически. Достаточно указать название ряда в заголовке диаграммы.

4. Придумайте содержательный заголовок

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

Для диаграммы, показанной на рисунке ниже, было бы недостаточно написать в заголовке только слово Impressions (Показы) вместо Impressions for Top Query Terms for Oct 2012 (Количество показов по самым популярным запросам за октябрь 2012).

Чтобы добавить к диаграмме название, выделите ее и нажмите Работа с диаграммами | Конструктор > Название диаграммы (Chart Tools | Layout > Chart Title). Я всегда выбираю положение Над диаграммой (Above Chart).

5. Сортируйте данные перед созданием диаграммы

Считаю этот момент очень важным. Диаграммы, построенные по неотсортированным данным, гораздо труднее читать и понимать.

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

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

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

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

6. Не заставляйте людей наклонять голову

Видели когда-нибудь диаграмму вроде этой?

Или ещё хуже… вот такую?

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

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

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

7. Очистите оси

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

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

Отсутствуют разделители групп разрядов

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

Чтобы добавить разделители групп разрядов, выделите весь столбец и на вкладке Главная (Home) в разделе Число (Number) нажмите кнопку формата с разделителями (с изображением трёх нулей). Excel всегда добавляет два десятичных разряда, от которых нужно избавиться, нажав здесь же кнопку уменьшения разрядности, вторая справа от кнопки формата с разделителями.

Другой способ – открыть диалоговое окно Формат ячеек (Format Cells) и настроить форматирование в нем.

Загромождение осей

В показанной выше диаграмме вертикальная ось загромождена и перегружена. Чтобы привести её в порядок, выделите ось и откройте диалоговое окно форматирования оси. В разделе Параметры оси (Axis Options) измените параметр Основные деления (Major unit). Я изменил значение основного деления с 20000 на 40000 – результат показан на картинке ниже.

Если требуется большая детализация графика – отрегулируйте настройки соответствующим образом.

Ненужные десятичные дроби

Никогда не указывайте в подписях осей десятичные разряды, если только 1 (единица) не является максимальным значением (другими словами, кроме тех случаев, когда для построения графика используются дробные числа). Такую ошибку не редко допускают, когда имеют дело с валютами. Часто можно встретить такие подписи: $10 000.00, $20 000.00, $30 000.00 и так далее. Все ненужные символы мешают восприятию графика.

Десятичные дроби вместо процентов

Если на вертикальной оси нужно показать проценты – форматируйте их как проценты, не превращайте проценты в десятичные дроби. Чем меньше человек тратит время на то, чтобы понять данные – тем более убедительными они становятся. И снова: работая с процентами, отбросьте дробную часть числа. Другими словами, не оставляйте подписи в виде 10.00%, 20.00% и так далее. Запишите просто 10%, 20%.

Нелепое форматирование нуля

Ещё одна неприятная деталь – дефис вместо нуля возле начала вертикальной оси. Это встречается очень часто. Узнать больше о пользовательском форматировании чисел можно из множества статей по этой теме. Существует масса интересных возможностей при форматировании, например, возможность добавлять текст к числу, сохраняя при этом числовое значение записи.

В нашем случае нужно просто изменить форматирование нуля. Для этого выделите столбец с исходными данными, откройте диалоговое окно форматирования и на вкладке Число (Number) в списке Числовые форматы (Category) выберите Все форматы (Custom). Найдите дефис и замените его нулём.

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

8. Пробуйте разные темы

В Excel есть достаточно выразительные темы оформления диаграмм, но большинство пользователей почему-то не идут дальше стандартной темы.

В Excel 2010 для PC предложено 53 темы, а в Excel 2011 для MAC – 57 тем оформления. Каждая тема содержит собственный уникальный набор оформления для 48 типов диаграмм. То есть 2544 варианта диаграмм для PC Excel 2010 и 2736 – для MAC Excel 2011.

Тему документа можно изменить, выбрав подходящую в выпадающем меню на вкладке Разметка страницы (Page Layout) в разделе Темы (Themes). Для MAC: Главная > Темы (Home > Themes).

Пробуйте различные темы, не будьте как все!

9. Создайте фирменную диаграмму

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

Предположим, требуется разработать план маркетинга для фирмы «Toys R Us» (автор никак не связан с такой фирмой, если она существует, все совпадения случайны), и для презентации решено построить круговую диаграмму с использованием фирменных цветов. В Excel 2010 (PC) можно использовать палитры RGB или HSL, в Excel 2011 (MAC) доступны палитры RGB, CMYK или HSB.

Так как я недостаточно близко знаком с этими палитрами, то использовал инструмент Color Picker, чтобы определить цвета логотипа фирмы «Toys R Us», и затем при помощи конвертера перевёл кодировку цветов в значения палитры RGB.

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

Далее необходимо выбрать один сектор круговой диаграммы, для этого один раз кликните по диаграмме и ещё раз – по нужному сектору. Затем измените оформление этого сектора при помощи инструмента заливки на вкладке Главная (Home) в разделе Шрифт (Font) или в диалоговом окне настройки форматирования.

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

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

PC:

Чтобы сохранить диаграмму как шаблон на PC, выделите её, откройте вкладку Работа с диаграммами | Конструктор (Chart Tools | Design), нажмите Тип > Сохранить шаблон (Type > Save as Template).

Чтобы из шаблона создать новую диаграмму, выделите данные, на базе которых требуется построить диаграмму, и нажмите Вставка (Insert) > Диаграммы (Charts) > Другие диаграммы (Other Charts) > Все диаграммы (All Chart Types) > Шаблоны (Templates) и выберите нужный шаблон.

Mac:

Кликните правой кнопкой в любом месте диаграммы и выберите Сохранить как шаблон (Save as Template). Шаблон диаграммы будет сохранён в файле .crtx в папке с шаблонами диаграмм.

Чтобы из шаблона создать новую диаграмму, выделите данные, на базе которых требуется построить диаграмму, и нажмите Диаграммы (Charts) > Вставить диаграмму (Insert Chart) > Другие (Other) > Шаблоны (Templates) и выберите нужный шаблон.

10. Сделайте название диаграммы динамическим

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

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

Я хочу показать отчёт о доходе компании «PPC», который обновляется каждый день. В заголовке будет показана общая сумма за месяц на сегодняшний день. Вот как это можно сделать шаг за шагом:

Шаг 1:

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

Кроме того, размер таблицы автоматически увеличивается, присоединяя новые данные, введённые в граничащие с таблицей строку снизу или столбец справа.

Шаг 2:

В ячейку ниже 31 строки таблицы (чтобы поместился целый месяц) введите функцию СУММ (SUM), суммирующую все строки таблицы – даже если некоторые из них в данный момент пустые.

Шаг 3:

Если оба столбца нашей таблицы используются для создания рядов данных, то достаточно выделить любую ячейку таблицы и на вкладке Вставка (Insert) нажать Диаграммы > Гистограмма (Charts > Column).

Мы же выберем только заголовок и ячейки столбца, содержащего данные о прибыли. А сделаем так потому, что не хотим создавать ещё один ряд данных из дней месяца. Для настройки оформления диаграммы в нашем распоряжении большой выбор стилей в разделе Стили диаграмм (Chart Styles) на вкладке Работа с диаграммами | Конструктор (Chart Tools | Design).

Шаг 4:

Добавьте к диаграмме название, указывающее на то, что есть текущий итог. Я назвал диаграмму: PPC прибыль за Октябрь. Подробнее о добавлении названия к диаграмме смотрите Совет 4 ранее в этой статье.

Шаг 5:

По умолчанию для области построения диаграмм используется белая заливка, и, как правило, диаграмма показывается на белом листе (и я рекомендую так делать). Мы уберём заливку вовсе, и это будет мудрым шагом.

Для этого выделите диаграмму и нажмите Ctrl+1, затем выберите Заливка > Нет заливки (Fill > No Fill). В завершение нужно отключить линии сетки, впрочем, это следует сделать в любом случае. Данный параметр находится на вкладке Вид (View) в разделе Показ (Show).

Шаг 6:

Выделите ячейку над графиком справа от названия диаграммы и создайте в этой ячейке ссылку на ячейку с суммой. Для этого нужно просто ввести знак равенства (=) и затем кликнуть по ячейке с суммой или вписать адрес этой ячейки вручную. Excel выделит ячейку, на которую создана ссылка, голубой рамкой. Затем настройте для этой ячейки такое же форматирование, как и для названия диаграммы.

Шаг 7:

Осталось только сдвинуть диаграмму вверх так, чтобы выровнять название с ячейкой, в которой располагается сумма. Потребуется немного ловкости, чтобы всё выровнять идеально. Далее удалим легенду, поскольку на диаграмме показан только один ряд данных. Все готово! Название стало динамическим.

Шаг 8:

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

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

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

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

Настройка диаграмм в Excel: добавляем название, оси, легенду, подписи данных и многое другое

О чём мы думаем в первую очередь после создания диаграммы в Excel? О том, как придать диаграмме именно такой облик, какой мы себе представляли, когда брались за дело!

В современных версиях Excel 2013 и 2016 настройка диаграмм – это просто и удобно. Корпорация Microsoft приложила немало усилий, чтобы сделать процесс настройки простым, а необходимые параметры легко доступными. Далее в этой статье мы покажем несколько простых способов, как добавить и настроить все основные элементы диаграммы в Excel.

3 способа настраивать параметры диаграмм в Excel

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

1. Выделить диаграмму и использовать вкладки из группы Работа с диаграммами (Chart Tools) – Конструктор (Design) и Формат (Format).
2. Кликнуть правой кнопкой мыши по элементу диаграммы, который необходимо настроить, и выбрать нужную команду из контекстного меню.
3. Использовать специальные иконки, которые появляются возле правого верхнего угла диаграммы, если кликнуть по ней мышью.

Ещё больше параметров находится в панели Формат области диаграммы (Format Chart), которая появляется в правой части рабочего листа, если нажать пункт Дополнительные параметры (More options) в контекстном меню диаграммы или на вкладках группы Работа с диаграммами (Chart Tools).

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

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

Как добавить название к диаграмме Excel

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

Добавляем название к диаграмме в Excel 2013 и Excel 2016

В Excel 2013 и Excel 2016 при создании диаграммы в её названии автоматически вставляется текст “Название диаграммы“. Чтобы изменить этот текст, просто выделите его и введите свой вариант названия:

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

Если название по каким-то причинам не было добавлено автоматически, то кликните в любом месте диаграммы, чтобы появилась группа вкладок Работа с диаграммами (Chart Tools). Откройте вкладку Конструктор (Design) и нажмите Добавить элемент диаграммы (Add Chart Element) > Название диаграммы (Chart Title) > Над диаграммой (Above Chart) или По центру (наложение) (Centered Overlay).

Либо нажмите иконку Элементы диаграммы (Chart Elements) возле правого верхнего угла диаграммы и отметьте галочкой параметр Название диаграммы (Chart Title).

Рядом с параметром Название диаграммы (Chart Title) можно нажать стрелку, направленную вправо (см. рисунок выше), и выбрать один из предложенных вариантов:

• Над диаграммой (Above Chart) – название размещается над областью построения диаграммы, размер графика при этом уменьшается; этот вариант используется по умолчанию.
• По центру (наложение) (Centered Overlay) – выровненное по центру название накладывается поверх области построения, размер графика при этом не изменяется.

Чтобы найти больше параметров, откройте вкладку Конструктор (Design) и нажмите Добавить элемент диаграммы (Add Chart Element) > Название диаграммы (Chart Title) > Дополнительные параметры заголовка (More Options). Или нажмите иконку Элементы диаграммы (Chart Elements), затем Название диаграммы (Chart Title) > Дополнительные параметры (More Options).

Нажатие кнопки Дополнительные параметры (More Options), как в одном, так и в другом случае, открывает панель Формат названия диаграммы (Format Chart Title) в правой части рабочего листа, где можно найти нужные параметры.

Добавляем название к диаграмме в Excel 2010 и Excel 2007

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

1. Кликните в любом месте диаграммы Excel, чтобы на Ленте меню появилась группа вкладок Работа с диаграммами (Chart Tools).
2. На вкладке Макет (Layout) нажмите Название диаграммы (Chart Title) > Над диаграммой (Above Chart) или По центру (наложение) (Centered Overlay).

Связываем название диаграммы с ячейкой рабочего листа

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

Чтобы связать название диаграммы с ячейкой рабочего листа:

1. Выделите название диаграммы.
2. В строке формул введите знак равенства (=), кликните по ячейке, содержащей нужный текст, и нажмите Enter.

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

Перемещаем название в диаграмме

Если нужно переместить название диаграммы в другое место, выделите его и перетащите при помощи мыши:

Удаляем название диаграммы

Если для диаграммы Excel не нужно название, то его можно удалить двумя способами:

• На вкладке Конструктор (Design) нажмите Добавить элементы диаграммы (Add Chart Element) > Название диаграммы (Chart Title) > Нет (None).
• Кликните правой кнопкой по названию диаграммы и в контекстном меню нажмите Удалить (Delete).

Изменяем шрифт и оформление названия диаграммы

Чтобы изменить шрифт названия диаграммы в Excel, кликните по нему правой кнопкой мыши и нажмите Шрифт (Font) в контекстном меню. Откроется одноименное диалоговое окно, в котором можно настраивать различные параметры шрифта.

Если нужны более детальные настройки, выделите название диаграммы, откройте вкладку Формат (Format) и поиграйте различными параметрами. Вот как, например, можно преобразить название диаграммы при помощи Ленты меню:

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

Более подробно об этом рассказано в статье Как добавить название к диаграмме в Excel.

Настраиваем оси диаграммы в Excel

Для большинства типов диаграмм в Excel вертикальная ось (она же – ось значений или ось Y) и горизонтальная ось (она же – ось категорий или ось Х) добавляются автоматически при создании диаграммы.

Чтобы скрыть или показать оси диаграммы, кликните по иконке Элементы диаграммы (Chart Elements), затем нажмите стрелку в строке Оси (Axes) и отметьте галочкой те оси, которые нужно показать, или уберите галочку рядом с теми, которые нужно скрыть.

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

При создании объёмных диаграмм, можно отобразить ось глубины:

Для каждого элемента осей диаграммы в Excel можно настраивать различные параметры (далее поговорим об этом подробнее):

Добавляем названия осей в диаграмме

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

1. Кликните в любом месте диаграммы Excel, затем нажмите по иконке Элементы диаграммы (Chart Elements) и поставьте галочку в строке Названия осей (Axis Titles). Если нужно показать название только для одной из осей (либо вертикальной, либо горизонтальной), нажмите стрелку справа и уберите одну из галочек.
2. Кликните на диаграмме по текстовому полю названия оси и введите текст.

Чтобы настроить оформление названия оси, кликните по нему правой кнопкой мыши и в контекстном меню нажмите Формат названия оси (Format Axis Title). При этом откроется одноимённая панель с большим выбором настраиваемых параметров оформления. Можно также использовать параметры, предложенные на вкладке Формат (Format) Ленты меню, как мы делали при настройке параметров названия диаграммы.

Связываем названия осей с заданными ячейками рабочего листа

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

Чтобы создать такую ссылку, выделите название оси и в строке формул введите знак равенства (=), затем кликните по ячейке, с которой нужно связать название оси, и нажмите Enter.

Изменяем масштаб оси диаграммы

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

1. Выделите вертикальную ось диаграммы и щелкните по иконке Элементы диаграммы (Chart Elements).
2. Нажмите стрелку в строке Оси (Axis) и в появившемся меню выберите Дополнительные параметры (More options). Откроется панель Формат оси (Format Axis).
3. В разделе Параметры оси (Axis Options) сделайте одно из следующих действий:
   • Чтобы задать начальное и конечное значения вертикальной оси, введите соответствующие значения в поля Минимум (Minimum) или Максимум (Maximum).
   • Чтобы изменить масштаб оси, введите значения в поля Основные деления (Major) и Промежуточные деления (Minor).
   • Чтобы отложить значения по оси в обратном порядке, отметьте галочкой параметр Обратный порядок значений (Values in reverse order).

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

Изменяем числовой формат у подписей оси

Если нужно, чтобы числа в подписях оси отображались как валюты, проценты, время или в каком-либо другом формате, кликните по подписям правой кнопкой мыши и в контекстном меню нажмите Формат оси (Format Axis). В открывшейся панели перейдите в раздел Число (Number) и выберите один из доступных числовых форматов:

Совет: Чтобы установить для чисел формат исходных данных (тот, что в ячейках рабочего листа), поставьте галочку Связь с источником (Linked to source). Если не получается найти раздел Число (Number) в панели Формат оси (Format Axis), проверьте, что на диаграмме выделена ось значений (это, как правило, вертикальная ось).

Добавляем подписи данных в диаграмме Excel

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

1. Кликните по ряду данных, для которого нужно добавить подписи. Чтобы добавить подпись только к одной точке данных, кликните ещё раз по этой точке.
2. Нажмите по иконке Элементы диаграммы (Chart Elements) и поставьте галочку в строке Подписи данных (Data Labels).

Например, вот так выглядит наша диаграмма Excel с подписями для одного из рядов данных.

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

Как изменить данные, отображаемые в подписях

Чтобы изменить содержание подписей данных на диаграмме, кликните по иконке Элементы диаграммы (Chart Elements) > Подписи данных (Data Labels) > Дополнительные параметры (More Options). Откроется панель Формат подписей данных (Format Data Labels) в правой части рабочего листа. На вкладке Параметры подписей (Label Options) в разделе Включать в подпись (Label Contains) выберите нужное из предложенных вариантов.

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

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

Советы для работы с подписями данных:

• Чтобы изменить положение одной подписи, просто перетащите её мышью в нужное место.
• Чтобы изменить цвет шрифта и заливку подписей данных, выделите их, затем откройте вкладку Формат (Format) и настройте нужные параметры форматирования.

Добавляем, убираем, перемещаем и настраиваем формат легенды диаграммы

При создании диаграммы в Excel 2013 и Excel 2016 легенда по умолчанию добавляется в нижней части области диаграммы. В Excel 2010 и более ранних версиях – справа от области построения.

Чтобы убрать легенду, нажмите иконку Элементы диаграммы (Chart Elements) возле правого верхнего угла диаграммы и уберите галочку в строке Легенда (Legend).

Чтобы переместить легенду диаграммы в другое место, выделите диаграмму, откройте вкладку Конструктор (Design), нажмите Добавить элемент диаграммы (Add Chart Element) > Легенда (Legend) и выберите новую позицию для легенды. Чтобы удалить легенду, нажмите Нет (None).

Ещё один способ переместить легенду – кликнуть по ней дважды мышью и выбрать нужное положение в разделе Параметры легенды (Legend Options) панели Формат легенды (Format Legend).

Для настройки форматирования легенды существует множество параметров на вкладках Заливка и границы (Fill & Line) и Эффекты (Effects) панели Формат легенды (Format Legend).

Показываем и скрываем сетку в диаграмме Excel

В Excel 2013 и 2016 показать или скрыть сетку – дело нескольких секунд. Просто кликните по иконке Элементы диаграммы (Chart Elements) и поставьте или уберите галочку в строке Сетка (Gridlines).

Microsoft Excel автоматически определяет, какие линии сетки лучше подойдут для данного типа диаграммы. Например, для линейчатой диаграммы будут показаны основные вертикальные линии, в то время как для гистограммы – основные горизонтальные линии сетки.

Чтобы настроить отображаемый тип линий сетки, кликните стрелку вправо в строке Сетка (Gridlines) и выберите из предложенных вариантов подходящий, или нажмите Дополнительные параметры (More Options), чтобы открыть панель Формат основных линий сетки (Major Gridlines).

Скрываем и редактируем ряды данных в диаграмме Excel

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

Для этого справа от графика нажмите иконку Фильтры диаграммы (Chart Filters) и уберите галочки с тех рядов данных и/или категорий, которые нужно скрыть.

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

Изменяем тип и стиль диаграммы

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

Другой способ – кликните правой кнопкой мыши в любом месте диаграммы и в контекстном меню нажмите Изменить тип диаграммы (Change Chart Type).

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

Или выберите один из стилей в разделе Стили диаграмм (Charts Styles) на вкладке Конструктор (Design):

Изменяем цвета диаграммы

Чтобы изменить цветовую тему диаграммы в Excel, нажмите иконку Стили диаграмм (Chart Styles), откройте вкладку Цвет (Color) и выберите одну из предложенных цветовых тем. Выбранные цвета тут же будут применены к диаграмме, и Вы сразу же сможете оценить хорошо ли она выглядит в новом цвете.

Чтобы выбрать цвет для каждого ряда индивидуально, выделите ряд данных на диаграмме, откройте вкладку Формат (Format) и в разделе Стили фигур (Shape Styles) нажмите кнопку Заливка фигуры (Shape Fill).

Как поменять местами оси X и Y диаграммы

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

Если выбранное по умолчанию расположение строк и столбцов на графике не устраивает, то можно без труда поменять местами горизонтальную и вертикальную оси. Для этого выделите диаграмму и на вкладке Конструктор (Design) нажмите кнопку Строка/Столбец (Switch Row/Column).

Как развернуть диаграмму в Excel слева направо

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

Кликните правой кнопкой мыши по горизонтальной оси диаграммы и нажмите Формат оси (Format Axis) в контекстном меню.

Если Вам привычнее работать с Лентой, откройте вкладку Конструктор (Design) и нажмите Добавить элемент диаграммы (Add Chart Element) > Оси (Axes) > Дополнительные параметры оси (More Axis Options).

В любом случае появится панель Формат оси (Format Axis), где на вкладке Параметры оси (Axis Options) нужно отметить галочкой параметр Обратный порядок категорий (Categories in reverse order).

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

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

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

Условное форматирование и гистограмма в ячейке Excel

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

Пример гистограммы в условном форматировании

Для наглядного примера возьмем отчет в таблице по прибыльности магазинов за последние 2 года:

Чтобы в столбце D добавить правило основано на гистограмме выделите диапазон ячеек D2:D12 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Гистограммы»-«Градиентная заливка»-«Зеленая гистограмма».

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

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

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



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

1. Выделите диапазон D2:D12 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами».
2. Появится окно «Диспетчер правил условного форматирования». В нем выделите текущее правило и нажмите на кнопку «Изменить».
3. В появившемся окне «Изменение правила форматирования» из группы параметров «Внешний вид столбца» из выпадающего списка «Граница» выберите опцию «Нет границы». Так мы немного изменим вид шкал под свой вкус.
4. Нажмите на кнопку «Отрицательные значения и ось».
5. Теперь в новом окне «Настройка отрицательных значений оси» укажите на самую нижнею опцию «Нет (показать отрицательные и положительные значения в одном направлении)». И нажмите ОК на всех открытых окнах.

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

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

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

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

Как сопоставить цвет диаграммы и исходных данных макрос для Excel

Автор Дмитрий Якушев На чтение 4 мин. Просмотров 1.4k.

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

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

Как макрос работает

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

Код макроса

Sub SopostavitCvetDiagrammiIIshodnihDannih()
'Шаг 1: Объявляем переменные
Dim oChart As Chart
Dim MySeries As Series
Dim FormulaSplit As Variant
Dim SourceRangeColor As Long

'Шаг 2: Наведите курсор на активный график
On Error Resume Next
Set oChart = ActiveChart

'Шаг 3: Выход не был выбран ни один график
If oChart Is Nothing Then
    MsgBox "График не выбран."
Exit Sub
End If

'Шаг 4: Цикл через серию диаграмм
For Each MySeries In oChart.SeriesCollection

    'Шаг 5: Получить диапазон исходных данных для целевой серии
    FormulaSplit = Split(MySeries.Formula, ",")(2)
    
    'Шаг 6: Захват цвета в первой ячейке
    SourceRangeColor = Range(FormulaSplit).Item(1).Interior.Color

    'Шаг 7: Применить окраску
    On Error Resume Next
    MySeries.Format.Line.ForeColor.RGB = SourceRangeColor
    MySeries.Format.Line.BackColor.RGB = SourceRangeColor
    MySeries.Format.Fill.ForeColor.RGB = SourceRangeColor
    If Not MySeries.MarkerStyle = xlMarkerStyleNone Then
        MySeries.MarkerBackgroundColor = SourceRangeColor
        MySeries.MarkerForegroundColor = SourceRangeColor
    End If

    'Шаг 8: Переход к следующей серии
Next MySeries

End Sub

Как этот код работает

1. Шаг 1 объявляет четыре переменные. Мы используем oChart в качестве контейнера памяти для нашего графика, MySeries как контейнер памяти для каждой серии в нашей диаграмме, FormulaSplit для захвата и сохранения диапазона исходных данных и SourceRangeColor для захвата и хранения индекса цвета исходного диапазона.
2. Этот макрос разработан так, что мы выводим целевой график на основе выбора графика. Другими словами, для запуска этого макроса должна быть выбрана диаграмма. Предполагается, что мы хотим выполнить действие макроса на графике, на котором мы щелкнули. На шаге 2 мы устанавливаем переменную oChart в ActiveChart. Если диаграмма не выбрана, то выдается ошибка. Именно поэтому мы используем On Error Resume Next Statement. Он говорит Excel продолжить макрос, если есть ошибка.
3. Шаг 3 проверяет, заполняется ли переменная oChart объект диаграммы. Если переменная oChart устанавливается в Nothing, ни один график не был выбран перед запуском макроса. Если это так, то мы говорим пользователю в окне сообщения, а затем выходим из процедуры.
4. Шаг 4 запускает цикл через все активные графики SeriesCollection. Каждая диаграмма имеет формулу серии. Формула серии содержит ссылки на таблицу, указывая на ячейки, используемые для её создания. Типичная серия формула выглядит следующим образом:
   = SERIES (Лист1 $ F $ 6, Лист1 $ D $ 7:! $ D $ 10, Лист1 $ F $ 7: $ F $ 10,2)
   Следует отметить, что существуют три различных диапазона в формуле. Первая точка диапазона на имена серии, вторая точка диапазона на этикетка данных серии, а точки третьего диапазона — это значения данных серии.
5. Шаг 5 использует функцию Split, чтобы извлечь из диапазона значения рядов данных.
6. Шаг 6 захватывает индекс цвета первой ячейки в диапазоне исходных данных. Мы предполагаем, что первая ячейка будет отформатирована так же, как и все остальные части диапазона.
7. После того, как у нас есть индекс цвета, мы можем применить цвет к различным свойствам серии.
8. На последнем этапе, мы делаем цикл, чтобы получить следующую серию. После того, как мы прошли через все ряды данных в таблице, макрос заканчивается.

Как использовать

1. Активируйте редактор Visual Basic, нажав ALT + F11.
2. Щелкните правой кнопкой мыши имя проекта / рабочей книги в окне проекта.
3. Выберите Insert➜Module.
4. Введите или вставьте код.

Что такое спарклайны и как они разгружают рабочий лист Excel

Здравствуйте, друзья. Представьте, если бы можно было нарисовать диаграмму прямо в ячейке Эксель, вместо построения диаграммы. Cколько места сразу освободилось бы на рабочем листе и насколько легче стало восприятие информации? Это особенно актуально, когда график должен дать лишь общее представление о тенденции, и подробными данными можно пренебречь. Оказывается, начиная с Excel 2010 появился такой инструмент, и называется он «спарклайн».

Cпарклайны

– это мини-диаграммы, построенные внутри ячейки Эксель

. Они позволяют вписать диаграмму в ячейку и тем самым сэкономить рабочее пространство. Вот как это выглядит:

Разработчики предлагают нам выбрать один из трех видов «спарков», схожих с видами классических диаграмм:

1. Спарклайн-график – это кривая линия, отражающая изменение величины во времени

2. Спарклайн-гистограмма – набор столбиков для оценки соотношения величин
3. Выигрыш/проигрыш – набор цветных блоков, соответствующих значениям. Цветом и направлением указывают, как изменилась величина в ячейке, по сравнению с предыдущей

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

После нажатия на кнопке вставки спарклайна, откроется диалоговое окно «Создание спарклайнов». Здесь нужно будет выбрать в какие ячейки вставлять диаграмму. Для вставки выберите размер диапазона, равный соответствующему размеру массива данных. Если у вас таблица размером 10х12 ячеек, укажите для вставки графиков массив 1х10, или 12х1. Жмите ОК и спарклайн будет создан!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Анализируйте тенденции данных с помощью спарклайнов

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

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

2. На вкладке Insert в группе Sparklines щелкните Line , Column или Win / Loss .

3. В поле Data Range введите диапазон ячеек с данными, которые вы хотите отобразить в спарклайне.

   Например, если ваши данные находятся в ячейках A, B, C и D строки 2, введите A2: D2 .

   Если вы предпочитаете выбрать диапазон ячеек на листе, нажмите

   чтобы временно свернуть диалоговое окно, выберите ячейки на листе и нажмите

   чтобы полностью отобразить диалоговое окно.

4. Щелкните ОК .

   Инструменты Sparkline Tools появятся на ленте. Используйте команды на вкладке Design , чтобы настроить спарклайны.

Подсказки

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

• Если вы выберете одну ячейку, вы всегда можете скопировать спарклайн в другие ячейки в столбце или строке позже, перетащив или используя Fill Down (Ctrl + D).

Настройте свои спарклайны

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

1. Выберите спарклайны, которые нужно настроить, чтобы на ленте отображались инструменты Sparkline Tools .

2. На вкладке Design выберите нужные параметры. Вы можете:

   • Показать маркеры для выделения отдельных значений в виде спарклайнов.

   • Измените стиль или формат спарклайнов.

   • Показать и изменить настройки оси.

     Если в этом раскрывающемся списке выбрать вариант Date Axis Type , Excel откроет диалоговое окно Sparkline Date Range .Отсюда вы можете выбрать диапазон в своей книге, который содержит значения дат, которые вы хотите использовать для данных Sparkline.

     Если вы выберете опцию Custom Value в этом раскрывающемся списке, Excel откроет диалоговое окно Sparkline Vertical Axis Setting . Отсюда вы можете ввести минимальное или максимальное значение (в зависимости от того, какой вариант вы выбрали) для вертикальной оси для данных спарклайна. По умолчанию Excel определяет, как отображать данные спарклайна, поэтому с помощью этих параметров вы можете контролировать минимальные и максимальные значения.

   • Измените способ отображения данных.

     Если вы выберете опцию Edit Single Sparkline’s Data в этом раскрывающемся списке, Excel откроет диалоговое окно Edit Sparkline’s Data . Отсюда вы можете выбрать в своей книге диапазон, содержащий данные, которые вы хотите использовать для данных Sparkline. Используйте эту опцию, если вы хотите изменить только один спарклайн.

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

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

     Выберите Показать данные в скрытых строках и столбцах , чтобы Excel включал данные в скрытые строки и столбцы в данные Sparkline.Снимите этот флажок, чтобы Excel игнорировал данные в скрытых строках и столбцах.

Спарклайны Excel — Полное руководство с примерами

Функция

Спарклайнов была представлена ​​в Excel 2010.

В этой статье вы узнаете все о спарклайнах Excel и увидите несколько полезных примеров.

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

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

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

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

В отличие от обычных диаграмм, спарклайны не являются объектами.Они находятся в ячейке на заднем плане этой ячейки.

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

В Excel есть три типа спарклайнов:

На изображении ниже я создал пример всех этих трех типов спарклайнов.

Первый в G2 — это спарклайн линейного типа, в G3 — тип столбца, а в G4 — тип выигрыша-проигрыша.

Вот несколько важных вещей, которые нужно знать о Excel Sparklines:

1. Sparklines являются динамическими и зависят от базового набора данных.Когда базовый набор данных изменяется, спарклайн обновляется автоматически. Это делает его полезным инструментом при создании панелей мониторинга Excel.
2. Размер спарклайнов зависит от размера ячейки. Если вы измените высоту или ширину ячейки, спарклайн изменится соответствующим образом.
3. Если в ячейке есть спарклайн, вы также можете ввести в него текст.
4. Вы можете настроить эти спарклайны, например изменить цвет, добавить ось, выделить максимальные / минимальные точки данных и т. Д.Позже в этом руководстве мы увидим, как это сделать для каждого типа спарклайнов.

Примечание : Спарклайн выигрыш-проигрыш похож на спарклайн столбца, но не показывает величину значения. Его лучше использовать в ситуациях, когда результат является двоичным, например, Да / Нет, Истина / Ложь, Голова / Хвост, 1 / -1 и т. Д. Например, если вы строите график, шел ли дождь за последние 7 дней или нет, вы можете построить график выигрыша-проигрыша с 1 для дней, когда шел дождь, и с -1 для дней, когда его не было. В этом руководстве все, что описано для спарклайнов столбцов, также можно применить к спарклайнам выигрыша-проигрыша.

Теперь давайте рассмотрим каждый из этих типов спарклайнов и все настройки, которые вы можете с ним сделать.

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

Допустим, вы хотите вставить спарклайн в виде линии (как показано ниже).

Вот шаги, чтобы вставить спарклайн в Excel:

1. Выберите ячейку, в которой вы хотите создать спарклайн.
2. Щелкните вкладку «Вставка».
3. В группе «Спарклайны» выберите параметр «Линия».
4. В диалоговом окне «Создать спарклайны» выберите диапазон данных (в данном примере A2: F2).
5. Нажмите ОК.

Это вставит спарклайн в ячейку G2.

Чтобы вставить спарклайн «Столбец» или «Выигрыш-проигрыш», необходимо выполнить те же шаги, описанные выше, и выбрать Столбцы или Выигрыш-проигрыш вместо Строки (на шаге 3).

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

Когда вы выбираете ячейку со спарклайном, вы заметите, что становится доступной контекстная вкладка — Sparkline Tools Design .На этой контекстной вкладке вы найдете все параметры настройки для выбранного типа спарклайна.

Редактирование набора данных существующих спарклайнов

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

• Редактировать расположение и данные группы : используйте это, если вы сгруппировали несколько спарклайнов и хотите изменить данные для всей группы (группировка покрывается далее в этом руководстве).
• Редактировать данные одного спарклайна : используйте это, чтобы изменить данные только для выбранного спарклайна.

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

Обработка скрытых и пустых ячеек

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

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

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

Вы можете указать, как вы хотите обрабатывать эти пустые ячейки.

Вот шаги:

1. Щелкните ячейку со спарклайном.
2. Щелкните вкладку «Дизайн» (контекстная вкладка, которая становится доступной только при выборе ячейки со спарклайном).
3. Щелкните параметр «Редактировать данные» (щелкните текстовую часть, а не ее значок).
4. В раскрывающемся списке выберите параметр «Скрытые и пустые ячейки».
5. В открывшемся диалоговом окне выберите, хотите ли вы отображать
   • Пустые ячейки как пробелы
   • Пустые ячейки как нулевые
   • Соедините точки данных до и после линией [этот параметр доступен только для спарклайнов линии].

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

Ниже приведен пример всех трех вариантов спарклайна линии:

1. Ячейка G2 — это то, что происходит, когда вы выбираете отображение зазора в спарклайне.
2. Ячейка G3 — это то, что происходит, когда вы выбираете вместо этого показывать ноль.
3. Ячейка G2 — это то, что происходит, когда вы выбираете отображение непрерывной линии, соединяя точки данных.

То же самое можно сделать и с спарклайнами столбцов и выигрышей-проигрышей (но не при подключении точки данных).

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

Если вы хотите быстро изменить тип спарклайна — от строки к столбцу или наоборот, вы можете сделать это, выполнив следующие действия:

• Щелкните спарклайн, который нужно изменить.
• Перейдите на вкладку «Дизайн спарклайнов».
• В группе «Тип» выберите нужный спарклайн.

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

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

Например, вы можете выделить максимальную и минимальную точки данных, первую и последнюю точку данных, а также все отрицательные точки данных.

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

Эти параметры доступны на вкладке «Инструменты спарклайна» (в группе «Показать»).

Вот различные доступные варианты:

1. Верхняя / нижняя точка : Вы можете использовать любой из них или оба, чтобы выделить максимальную и / или минимальную точку данных.
2. Первая / Последняя точка : Вы можете использовать любой из них или оба, чтобы выделить первую и / или последнюю точку данных.
3. Отрицательные точки : Если у вас есть отрицательные точки данных, вы можете использовать эту опцию, чтобы выделить их все сразу.
4. Маркеры : этот параметр доступен только для спарклайнов. Он выделит все точки данных маркером. Вы можете изменить цвет маркера, используя параметр «Цвет маркера».

Цвет и стиль спарклайнов

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

Позволяет изменять цвет спарклайнов (линий или столбцов), а также маркеров.

Вы также можете использовать готовые варианты стилей.Чтобы получить полный список опций. щелкните значок раскрывающегося списка в правом нижнем углу поля стиля.

Совет от профессионалов : Если вы используете маркеры для выделения определенных точек данных, рекомендуется выбрать светлый цвет линии и яркий и темный маркер (в большинстве случаев лучше всего подходит красный ).

Добавление оси

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

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

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

Эта разница намного более заметна в спарклайне столбца:

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

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

Вот как это сделать:

1. Выберите ячейку со спарклайном (ами).
2. Перейдите на вкладку «Дизайн спарклайновых инструментов».
3. Щелкните на опции Axis.
4. В раскрывающемся списке выберите «Пользовательское значение» (в параметрах минимального значения вертикальной оси).
5. В диалоговом окне «Настройки вертикальной оси спарклайна» введите значение 0.
6. Нажмите «ОК».

Это даст вам результат, как показано ниже.

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

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

Вы также можете сделать ось видимой, выбрав параметр «Показать ось».Это полезно только тогда, когда у вас есть числа, пересекающие ось. Например, если у вас установлена ​​ось на 0 и есть как положительные, так и отрицательные числа, тогда ось будет видна.

Группировать и разгруппировать спарклайны

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

Чтобы сгруппировать спарклайны:

1. Выберите те, которые вы хотите сгруппировать.
2. Перейдите на вкладку «Дизайн спарклайнов».
3. Щелкните значок «Группа».

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

Вы можете разгруппировать эти спарклайны с помощью параметра «Разгруппировать».

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

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

Чтобы удалить спарклайн, выполните следующие действия:

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

Вам также могут понравиться следующие руководства по Excel:

Как создавать интерактивные диаграммы Excel

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

Показать серию диаграмм с флажками

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

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

Создать повторяющийся диапазон данных

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

В этом примере исходные данные находятся в ячейках B2: H5, а дубликаты
диапазон, показанный ниже, находится в ячейках B8: h21

Для создания заголовков столбцов «Месяц»:

1. В ячейке C8 введите следующую формулу: = C2
2. Скопируйте формулу в ячейку H8

Для создания заголовков строк «Регион»:

1. В ячейке B9 введите следующую формулу: = C3
2. Скопируйте формулу в ячейку B11

Для создания данных серии:

1. В ячейке C9 введите следующую формулу: = ЕСЛИ ($ A9 = ИСТИНА, C3, NA ())
2. Скопируйте формулу в ячейку H9, а затем скопируйте в строку
   11.

Добавьте флажки

Затем вы добавите флажок слева от названия каждого региона в
исходные данные. Когда флажок установлен, его данные появятся в
Диаграмма.

Чтобы добавить флажок:

1. На ленте щелкните вкладку Разработчик. (Если вкладка не отображается,
   следуйте инструкциям здесь, чтобы добавить
   вкладка Разработчик)
2. Щелкните команду «Вставить» и в разделе «Элементы управления формой» щелкните
   флажок

3. На листе щелкните ячейку A3, чтобы добавить флажок.
4. Удалите текст из заголовка флажка и установите флажок
   коробка более узкая, чтобы поместиться в столбец A.

5. Скопируйте флажок и вставьте копии в ячейки A4 и A5.

Свяжите флажки с ячейками рабочего листа

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

1. Щелкните правой кнопкой мыши флажок в ячейке A3 и выберите Управление форматом
2. Щелкните поле Ссылка на ячейку, а затем на листе щелкните
   ячейка A9, чтобы связать флажок с этой ячейкой.

3. Щелкните OK, чтобы закрыть окно управления форматом.
4. Свяжите оставшиеся флажки с соответствующими ячейками в
   повторяющийся диапазон данных.
5. Щелкните каждый из флажков, чтобы добавить флажок и добавить
   ИСТИНА для связанной ячейки.

Создать линейную диаграмму

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

1. Выберите данные и заголовки в повторяющемся диапазоне данных — ячейки
   B8: h21
2. На ленте щелкните вкладку Вставка
3. Щелкните команду «Вставить линейную диаграмму», затем щелкните «Двухмерная линия».

4. При необходимости переместите диаграмму и измените ее размер по размеру рабочего листа.
5. СОВЕТ. Расположите диаграмму над диапазоном повторяющихся данных, чтобы скрыть
   Это.

Форматирование линейной диаграммы

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

Чтобы удалить легенду диаграммы:

1. Щелкните легенду диаграммы, чтобы выбрать ее.
2. На клавиатуре нажмите клавишу Delete

Чтобы связать заголовок диаграммы с ячейкой:

1. Щелкните заголовок диаграммы, чтобы выбрать его.
2. Щелкните строку формул и введите знак равенства (=)
3. Щелкните ячейку B1, которая содержит заголовок рабочего листа
4. Нажмите Enter, чтобы заполнить формулу

Для добавления меток данных:

1. Установите флажки, чтобы показать все серии.
2. Щелкните первую серию, чтобы выбрать ее.
3. Щелкните последнюю точку в выбранной серии, чтобы выбрать данные.
   точка.
4. Щелкните правой кнопкой мыши выбранную точку данных и выберите Добавить метку данных,
   затем нажмите Добавить метку данных

5. Дважды щелкните метку данных — при первом щелчке выбираются все
   метки данных для серии, а второй щелчок выбирает только
   одна метка данных.
6. Щелкните правой кнопкой мыши выбранную метку данных и выберите «Форматировать данные».
   Этикетка
7. На панели «Форматирование метки данных» в разделе «Параметры метки» добавьте флажок
   отметьте название серии и снимите отметку с поля «Значения».

8. Повторите шаги 2-7 для остальных рядов данных.
9. СОВЕТ: Если метки данных расположены слишком близко к линиям, нажмите на
   График области диаграммы и сузьте ее.

Проверьте интерактивную линейную диаграмму

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

Значение в связанной ячейке изменится на ЛОЖЬ, а данные для
эта серия исчезнет с графика.

Показать серию диаграмм с отметками X

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

Следуйте инструкциям по установке для интерактивного примера флажка.
выше, но не устанавливайте флажки в ячейках A3: A5.

Затем добавьте формулу в столбец A повторяющегося диапазона данных, чтобы увидеть
если есть отметка в основных данных. Если есть отметка, то результат
ИСТИНА, а в противном случае — ЛОЖЬ.

1. В ячейке A9 введите следующую формулу: = A3 <> «»
2. Скопируйте формулу в ячейку A11.

Протестируйте интерактивную онлайн-диаграмму

Ниже представлена ​​интерактивная онлайн-версия диаграммы и рабочего листа.
(Это может быть не во всех браузерах.)

Флажки недоступны в интерактивном интерактивном представлении, поэтому
в этом примере в столбце A используются отметки «X».

• Введите X в столбце A, чтобы отобразить регион.
• Очистите ячейку в столбце A, чтобы скрыть область.

Скачать рабочую тетрадь

Чтобы увидеть обе версии настройки диаграммы, вы можете скачать образец
файл.Файл заархивирован и в формате xlsx без
макросы. Образец интерактивного
Файл диаграммы

Дополнительные ресурсы для диаграмм

Интерактивный график — всего

Круговая диаграмма

Диаграмма Ганта в Excel

Диаграммы

, кластерный стек
Утилита

Диаграммы, линия-столбец 2 оси

Графики

, Панель

Графики

, Водопад

Код

VBA, скопировать в книгу

Как использовать мини-диаграммы Excel Sparklines

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

Чтобы создать ячейку Sparklines, вам понадобится таблица Excel с некоторыми данными.

Как создать спарклайны из данных

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

ПРИМЕЧАНИЕ. Одна строка или столбец = 1 ячейка для спарклайнов.

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

Щелкните вкладку Вставка . Затем щелкните Спарклайны и выберите тип спарклайна. Для нашего примера мы выбрали Line .

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

Нажмите OK .

Используйте вкладку Sparkline , чтобы настроить графики Sparklines. Маркеры полезны при создании стилей линий графиков спарклайнов. Вы также можете изменить Style и Color графиков Sparklines.

Кнопка Axis на вкладке Sparkline предоставляет дополнительные параметры настройки.

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

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

ПРИМЕЧАНИЕ. Когда вы выбираете одну из ячеек, выбранных для спарклайнов, они соединяются.Когда вы выбираете одну из ячеек спарклайнов, все они выбираются. Любые изменения, которые вы вносите в одно, применяются ко всем.

Щелкните другой тип в разделе Тип на вкладке Sparkline .

Как настроить спарклайновую диаграмму Excel с помощью цвета

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

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

Существует третий тип спарклайнов, называемый Win / Loss . Давайте взглянем на этот тип диаграммы Sparkline.

Связано: Как вставлять и настраивать спарклайны в Google Таблицах

Как использовать спарклайны выигрышей / проигрышей для просмотра тенденций данных

Проще говоря, спарклайн Win / Loss отображает прибыль vs.Потери или положительные или отрицательные, которые могут помочь вам увидеть тенденции в ваших данных. Вы также можете настроить внешний вид спарклайна Win / Loss так же, как вы настраиваете типы Line и Column .

Сделайте ваши данные особенными

Спарклайны доступны для версий Microsft Excel как для Windows, так и для Mac. Стандартные диаграммы в Microsoft Excel отлично подходят для презентаций, но никогда не бывает достаточно места, когда вы пытаетесь уместить их в электронную таблицу вместе со всеми другими данными.Вот почему мы так любим Sparklines. Они занимают всего одну ячейку и могут отображать большие объемы данных, тенденций, результатов. Для еще большего эффекта добавьте немного цвета, и вы действительно сможете произвести впечатление на свою аудиторию!

Как создать линейчатую диаграмму ячеек с функцией REPT [Совет по построению диаграмм]

Диаграммы внутри ячеек — это здорово.

И сегодня я покажу вам, как создать гистограмму в ячейке с функцией REPT.

Но действительно ли они стоят вашего времени?

Но действительно ли они стоят вашего времени?

Да.

Вот единственная важная причина:

Их легко создать и просто использовать.

Впереди, в этом посте, я покажу пошаговый процесс создания хорошей гистограммы с функцией REPT.

Но знаете ли вы о функции REPT?

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

В основном эта функция используется для создания диаграмм в ячейках.

Синтаксис

 = REPT (text, number_times) 

Шаги по созданию гистограммы с функцией REPT

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

• Назовите обе кнопки «Только процент» и «Бары». И затем ссылку на ячейку. (Я связал затем с ячейкой A1)

• Теперь вернитесь к своей таблице данных и вставьте следующую формулу в ячейку.

 = IF (A $ 1 = 1, REPT («|», B3) & »« & TEXT (B3 / 100, «0%»), B3 / 100) 

• Не забудьте изменить стиль шрифта на «PlayBill ».
• Теперь перетащите формулу на всю таблицу, и гистограмма готова.

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

Как работает эта формула

В этой формуле вы использовали функцию ЕСЛИ для условной формулы.

Когда вы выбираете кнопку параметров «Полосы», вы получаете 1 в ячейке A1, а затем функция ПОВТОР показывает полосу и процент в ячейке.

И, когда вы выбираете «Only Percentage», будет отображаться только процент.

Образец файла

Загрузите этот образец файла отсюда, чтобы узнать больше.

Заключение

Используя эту технику, вы можете запустить ваши скучные данные.

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

А теперь скажите мне одну вещь.

Вам нравится этот совет?

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

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

Спарклайнов в Excel — Easy Excel Tutorial

Вставить спарклайны | Настроить спарклайны | Сравнить спарклайны | Спарклайны побед / поражений

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

Вставить спарклайны

Чтобы вставить спарклайны, выполните следующие действия.

1. Выберите ячейки, в которых должны появиться спарклайны. В этом примере мы выбираем диапазон G2: G4.

2. На вкладке «Вставка» в группе «Спарклайны» щелкните «Линия».

3. Щелкните поле «Диапазон данных» и выберите диапазон B2: F4.

4. Щелкните OK.

Результат:

5.Измените значение в ячейке F2 на 1186.

Результат. Excel автоматически обновляет спарклайн .

Настроить спарклайны

Чтобы настроить спарклайны, выполните следующие действия.

1. Выберите спарклайны.

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

Результат:

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

3. На вкладке «Дизайн» в группе «Стиль» выберите красивый визуальный стиль.

Результат:

Примечание: высокие точки теперь окрашены в зеленый цвет, а низкие точки — в красный.

Чтобы удалить спарклайн, выполните следующие действия.

4. Выберите 1 или несколько спарклайнов.

5. На вкладке «Дизайн» в группе «Группа» нажмите «Очистить».

Сравнить Sparklines

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

Чтобы сравнить спарклайны, выполните следующие действия.

1. Выберите спарклайны.

2. На вкладке «Дизайн» в группе «Тип» щелкните «Столбец».

Результат:

Примечание: все зеленые полосы имеют одинаковую высоту, но максимальные значения (B2, E3 и F4) разные!

3.Выберите спарклайны.

4. На вкладке «Дизайн» в группе «Группа» щелкните «Ось».

5. В разделе «Параметры минимального значения вертикальной оси» и «Параметры максимального значения вертикальной оси» выберите «Одинаковый для всех спарклайнов».

Результат:

Примечание: теперь отчетливо видно, что заработок Джека намного выше.

Спарклайны побед / поражений

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

Примечание: попробуйте сами. Загрузите файл Excel и выберите спарклайны. Затем на вкладке «Дизайн» щелкните «Столбец» (вместо «Выигрыш / проигрыш»), чтобы четко увидеть, насколько высокие и низкие значения.

Шаблон