Одним из революционных нововведений в крайней версии 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:
re>Function NanoChart(rng As Range) As String Const MaxSymbols = 10 For Each cell In rng outstr = outstr |", cell / WorksheetFunction.Max(rng) * MaxSymbols) Points(, i) Then j = i End If If k = 0 Then k = i ElseIf Points(, k) < Points(, i) Then k = i End If Next dblMin = Points(, j) dblMax = Points(, k) With rng.Worksheet.Shapes For i = 0 To Points.Count — 2 Set shp = .AddLine( _ cMargin + rng.Left + (i * (rng.Width — (cMargin * 2)) / (Points.Count — 1)), _ cMargin + rng.Top + (dblMax — Points(, i + 1)) * (rng.Height — (cMargin * 2)) / (dblMax — dblMin), _ cMargin + rng.Left + ((i + 1) * (rng.Width — (cMargin * 2)) / (Points.Count — 1)), _ cMargin + rng.Top + (dblMax — Points(, i + 2)) * (rng.Height — (cMargin * 2)) / (dblMax — dblMin)) On Error Resume Next j = 0: j = UBound(arr) + 1 On Error GoTo 0 ReDim Preserve arr(j) arr(j) = shp.Name Next With rng.Worksheet.Shapes.Range(arr) .Group If Color >0 Then .Line.ForeColor.RGB = Color Else .Line.ForeColor.SchemeColor = -Color End With End With LineChart = "" End Function Sub ShapeDelete(rngSelect As Range) Dim rng As Range, shp As Shape, blnDelete As Boolean For Each shp In rngSelect.Worksheet.Shapes blnDelete = False Set rng = Intersect(Range(shp.TopLeftCell, shp.BottomRightCell), rngSelect) If Not rng Is Nothing Then If rng.Address = Range(shp.TopLeftCell, shp.BottomRightCell).Address Then blnDelete = True End If If blnDelete Then shp.Delete Next End Sub
Теперь в мастере функций в категории Определенные пользователем появилась новая функция LineChart c двумя аргументами — диапазоном и кодом цвета графика. Если ее вставить в пустую ячейку, например, справа от числовой строки и скопировать затем, как обычно, на весь столбец, то получим весьма симпатичное отображение числовых данных в виде миниграфиков:
Ссылки по теме
- Микрографики в надстройке PLEX
- Что такое макросы, куда вставлять код макросов на VBA, как их использовать.
- Бесплатная надстройка Sparklines Эдварда Тафта (англ.)
Как сделать спарклайн в ворде
Excel предлагает широкий спектр инструментов визуализации данных, включая спарклайны и традиционные диаграммы. В отличие от занимающих много места традиционных диаграмм, спарклайны Excel предлагают краткую визуальную сводку данных в ячейках, напрямую отображая тенденции и закономерности. Эти различия делают спарклайны идеальным выбором для компактного представления данных, позволяя немедленно визуально сравнивать строки и столбцы без пространственных требований полноразмерных диаграмм. В этом подробном руководстве мы углубимся в спарклайны Excel и узнаем, как их вставлять, группировать, настраивать и эффективно использовать для мгновенного и визуального улучшения ваших данных.
- Вставка спарклайнов в ячейки
- Группировать и разгруппировать спарклайны
- Настройка спарклайнов
- Изменение типов спарклайнов
- Выделите конкретные точки данных и покажите маркеры
- Изменение цвета, стиля и т. д. спарклайнов.
- Настройка оси спарклайна
- Изменение размера спарклайнов
Вставка спарклайнов в ячейки
Предположим, у вас есть таблица квартальных продаж для разных магазинов, как показано на следующем рисунке. Если вы хотите визуализировать тенденцию продаж для каждого магазина в одной ячейке, лучшим вариантом будет добавление спарклайна. В этом разделе объясняется, как вставлять спарклайны в ячейки Excel.
- Для начала выберите ячейку, в которой вы хотите, чтобы появилась спарклайн. В данном случае я выбираю ячейку F2.
- Затем перейдите к Вставить вкладке выберите тип спарклайна, который вы хотите вставить (линия, Columnили Победа/Проигрыш). Здесь я выбираю линия Спарклайн.
Советы:
- к вставлять спарклайны в несколько ячеек одновременно, выполните действия, описанные выше, чтобы открыть Создать спарклайны диалоговое окно. Затем вам нужно выбрать диапазон, содержащий наборы данных, на основе которых вы не можете создавать спарклайны, выбрать диапазон ячеек, в которых вы хотите, чтобы спарклайны отображались, и нажать OK.
Разблокируйте магию Excel с помощью Kutools AI
- Умное исполнение: выполнение операций с ячейками, анализ данных и создание диаграмм — и все это осуществляется с помощью простых команд.
- Пользовательские формулы: Создавайте индивидуальные формулы для оптимизации рабочих процессов.
- VBA-кодирование: Пишите и внедряйте код VBA без особых усилий.
- Интерпретация формулы: Легкое понимание сложных формул.
- Перевод текста: преодолевайте языковые барьеры в своих таблицах.
Расширьте свои возможности Excel с помощью инструментов на базе искусственного интеллекта. Скачать сейчас и испытайте эффективность, как никогда раньше!
Группировать и разгруппировать спарклайны
Группирование спарклайнов позволяет применять одинаковое форматирование (например, цвет линии, стиль маркера и т. д.) ко всем спарклайнам в группе. Когда спарклайны сгруппированы, любые изменения, вносимые в одну спарклайны, автоматически применяются ко всем спарклайнам в группе. Это обеспечивает единообразный внешний вид, что имеет решающее значение для объективного сравнения и поддержания профессионального вида ваших отчетов. В этом разделе показано, как группировать и разгруппировать спарклайны в Excel.
Внимание: если в пакет одновременно вставлено несколько спарклайнов, они автоматически группируются вместе. Если спарклайны вставляются одна за другой, вы можете сделать следующее, чтобы сгруппировать их вместе.
Групповые спарклайны
Выберите две или более спарклайны, которые хотите сгруппировать вместе, перейдите к Спарклайн вкладка, выберите В группе.
Затем выбранные спарклайны группируются вместе. При выборе любой спарклайна в группе все спарклайны в этой группе будут выбраны одновременно.
Разгруппировать спарклайны
Чтобы разгруппировать спарклайны, выберите любую спарклайн в группе, перейдите к Спарклайн вкладку и нажмите Ungroup .
Заметки:
- Если в пакет одновременно вставлено несколько спарклайнов, они автоматически группируются вместе.
- Все спарклайны в группе должны быть одного типа. Если сгруппировать разные типы спарклайнов, они автоматически преобразуются в единый тип.
Настройка спарклайнов
После создания спарклайнов вы можете в любой момент изменить их тип, стиль и формат. В этом разделе показано, как настроить спарклайны в Excel.
Изменение типов спарклайнов
В Excel существует три типа спарклайнов: линия, столбец и выигрыш/проигрыш. Различные типы спарклайнов могут быть более эффективными при визуализации определенных видов данных, а настройка позволяет выбрать наиболее подходящий тип.
Чтобы быстро изменить существующий тип спарклайна на желаемый новый тип, выполните следующие действия:
Выделите одну или несколько спарклайнов или всю группу, перейдите к Спарклайн вкладке выберите нужный вам тип из Тип группа. Смотрите скриншот:
Результат: выбранные спарклайны линий заменяются спарклайнами столбцов.:
Функции: При вставке спарклайнов в Excel рекомендуется выбрать соответствующий тип спарклайнов в зависимости от типа ваших данных. Обычно линия Sparkline показывает общую тенденцию продаж, столбец Sparkline больше подчеркивает изменения количества, а Sparkline выигрышей/проигрышей эффективно отображает ежемесячные колебания прибыли, различая прибыли и убытки. На снимке экрана ниже показана спарклайн выигрыша/проигрыша.
Выделите конкретные точки данных и покажите маркеры
Благодаря настройке вы можете выделить важные точки данных, такие как пики (максимумы) или спады (минимумы), что позволит зрителям сосредоточиться на наиболее важных аспектах данных. Для линейных спарклайнов вы можете отображать маркеры для каждой точки данных, чтобы сделать их более интуитивно понятными.
Выделите спарклайны, перейдите к Спарклайн вкладку, а затем выберите нужные параметры на вкладке Показать группа.
- Высота точки – Выделите самые высокие точки данных в выбранной группе спарклайнов.
- Низкая точка – Выделите самые низкие точки данных в выбранной группе спарклайнов.
- Отрицательные моменты – Выделите отрицательные значения в выбранной группе спарклайнов другим цветом или маркером.
- Первые очки – Выделите первую точку данных в выбранной группе спарклайнов.
- Последняя точка – Выделите последнюю точку данных в выбранной группе спарклайнов.
- Маркеры – Добавьте маркеры к каждой точке данных в выбранной группе спарклайнов.
Заметки:
- Выделив определенные точки данных, выберите Цвет маркера раскрывающийся список и укажите разные цвета для точки данных.
Изменение цвета, стиля и т. д. спарклайнов
В этом разделе будет показано, как изменить внешний вид спарклайнов, например настроить их цвет и стиль в соответствии с общим дизайном вашей электронной таблицы или презентации, чтобы обеспечить визуальное единообразие.
Изменение цвета спарклайна
Выберите спарклайны, затем перейдите к Спарклайн , нажмите Цвет спарклайна раскрывающийся список и укажите для него новый цвет.
Функции: Толщину линии можно настроить, указав новую ширину в Вес опцию.
Изменение стиля спарклайна
Выберите спарклайны под Спарклайн вкладке выберите нужный стиль в Стиль галерея.
Настройка оси спарклайна
После создания спарклайнов вы можете столкнуться с некоторыми неожиданными явлениями. Например, незначительные отклонения в исходных данных могут выглядеть на диаграмме преувеличенными; столбцы могут отображать одинаковую высоту, несмотря на разные максимальные значения в данных; и различение положительных и отрицательных значений в спарклайнах может оказаться сложной задачей. В этом разделе вы узнаете, как настроить ось спарклайна для решения этих проблем.
Разница между точками данных кажется преувеличенной
Как показано на скриншоте ниже, весь набор данных находится между 80 и 85 (отклонение составляет всего 5 пунктов), но изменение спарклайна выглядит огромным. Это связано с тем, что Excel автоматически устанавливает ось, которая начинается с наименьшего значения 80. В этом случае настройка оси может помочь более точно представить данные в виде спарклайнов.
- Выделите спарклайны или всю группу спарклайнов, перейдите к Спарклайн меню.
- Выберите Ось >Пользовательская ценность.
Результат
После установки минимального значения для вертикальной оси, равного 0, спарклайн представляет данные более точно, как показано на снимке экрана ниже.
Непоследовательное представление максимальных значений
Как показано на рисунке ниже, максимальные значения в каждом наборе данных разные, но все красные полосы, представляющие максимальные значения в спарклайнах, имеют одинаковую высоту. Это затрудняет нам поиск различий в максимальных значениях между наборами данных с графиков. Для решения этой проблемы можно использовать следующий подход.
- Выделите спарклайны или всю группу спарклайнов, перейдите к Спарклайн меню.
- Нажмите Ось , чтобы развернуть раскрывающийся список, а затем выберите То же самое для всех спарклайнов соответственно от обоих Параметры минимального значения вертикальной оси и Параметры максимального значения вертикальной оси.
Внимание: Когда То же самое для всех спарклайнов опция включена, перед ней будет отображаться галочка.
Результат
Теперь вы можете видеть, что все красные полосы, представляющие максимальные значения спарклайнов, имеют разную высоту.
Трудности в различении положительных и отрицательных ценностей
Как показано на снимке экрана ниже, исходные данные включают как отрицательные, так и положительные значения, однако отличить их в спарклайнах непросто. Отображение горизонтальной оси в спарклайнах может быть эффективным способом различать положительные и отрицательные значения.
- Выделите спарклайны или всю группу спарклайнов, перейдите к Спарклайн меню.
- Нажмите Ось , чтобы развернуть раскрывающийся список, а затем выберите Показать ось в Параметры горизонтальной оси .
Результат
Затем в спарклайнах появляется горизонтальная ось, которая помогает визуально отделить положительные значения от отрицательных. Значения выше линии являются положительными, а значения ниже — отрицательными.
Внимание: горизонтальная ось появится только в том случае, если спарклайн содержит данные, пересекающие нулевую ось.
Изменение размера спарклайнов
Спарклайны в Excel содержатся внутри ячейки. Чтобы изменить размер спарклайна, вам необходимо изменить размер ячейки, в которой она находится. Это можно сделать, изменив высоту строки или ширину столбца, в котором расположена спарклайн. См. следующую демонстрацию.
Изменение набора данных для существующих спарклайнов
Чтобы изменить диапазон данных существующих спарклайнов, сделайте следующее.
- Выделите спарклайны или всю группу спарклайнов.
- Перейдите в Спарклайн вкладку нажмите Изменить данные .
- Чтобы изменить местоположение и источник данных для выбранной группы спарклайнов, выберите значок Изменить местоположение и данные группы… вариант;
- Чтобы изменить только источник данных для выбранной спарклайна, выберите значок Редактировать данные одной спарклайна… опцию.
Удаление спарклайнов
Чтобы удалить спарклайны, выберите спарклайны или группы спарклайнов, перейдите к Спарклайн вкладку нажмите Сбросить, а затем выберите нужный вам вариант:
- Очистить выделенные спарклайны: выберите эту опцию, чтобы удалить все выбранные спарклайны.
- Очистить выбранную группу спарклайнов: выберите эту опцию, чтобы очистить все группы спарклайнов.
