диапазон расположения в excel
Спарклайн в Excel. Как настроить минидиаграммы в ячейках Excel?
Спарклайны можно создавать в Excel 2010 и более поздних версиях. Что это такое? Как настраивать спарклайны пустых и скрытых ячеек? Рассмотрим пример. Дана таблица результатов ЕГЭ по нескольким предметам за разные учебные годы.
Создавать большие диаграммы по всем предметам не очень удобно, поэтому построим небольшие диаграммы в ячейках столбца Динамика. Для этого выделим диапазон, содержащий все баллы. Перейдем на вкладку Вставка и в группе Спарклайны выберем График.
(Все изображения здесь и далее — кликабельны. Щелкните для увеличения)
Появится диалоговое окно Создание спарклайнов. В диапазоне данных будет выделенный нами диапазон значений баллов. Выберем диапазон расположения спарклайнов. Для этого щелкнем по кнопке с изображением стрелки.
Выделим ячейки, где будут располагаться спарклайны, и снова нажмем на кнопку со стрелочкой.
Нажмем ОК.
Мы получили в ячейках столбца Динамика спарклайны-линии, по которым мы можем видеть динамику изменения баллов по ЕГЭ по каждому предмету. Для спарклайнов можно менять Тип, устанавливать маркеры, выделять максимальное, минимальное, первое и последнее значения, отрицательные точки. Можно менять стиль. Эти параметры находятся на вкладке Конструктор в Работе со спарклайнами.
Мы рассмотрели спарклайны-графики, можно изменить тип Линия на тип Гистограмма. В ячейках появятся столбики значений.
Тип Выигрыш/Проигрыш нужен в том случае, если у нас дана таблица как отрицательных, так и положительных значений.
Рассмотрим таблицу, в которой отражается разница баллов по ЕГЭ между школьными результатами и областными показателями. Минус показывает, что результат школьный ниже областного, а плюс — выше.
В этом случае можно выбрать тип спарклайна Выигрыш/Проигрыш. Установим флажок Показать отрицательные точки. И мы видим, что показатели, которые ниже областных, выделены красным цветом. Цвета настраиваем в группе Стиль.
Спарклайны в Excel 2007 и более ранних версиях. Замена спарклайнам
В Excel 2003, 2007 спарклайнов нет, но для добавления диаграмм в ячейки можно использовать небольшую хитрость — функцию Excel ПОВТОР.
Рассмотрим пример, как с помощью этой функции построить минидиаграммы в ячейках.
Нам дан список учащихся и количество заданий, которые они выполнили на контрольной работе.
В столбце Динамика нужно построить диаграммы, отражающие число баллов учащихся.
Для начала перейдем на вкладку Вставка — Символ. Выберем шрифт Wingdings (можно другой) и любой символ этого шрифта, который более всего подойдет для наших диаграмм. Но вставлять символ не будем, выделим его и запомним Код символа. В нашем примере мы взяли черный ромб, его код 116.
Нажмем Закрыть. Выделим ячейку С2. Именно тут будет расположена первая диаграмма. На вкладке Главная выберем шрифт Wingdings (шрифт выбранного символа). И введем в ячейку С2 формулу: =ПОВТОР(СИМВОЛ(116);В2)
В данной формуле первым идет тот символ, который будет повторяться (в нашем случае ромб, его код 116). Символ мы вводим не с клавиатуры, а задаем с помощью кода. После точки с запятой указывается число повторений данного символа. В нашем случае это число равно числу набранных на контрольной работе баллов. После ввода формулы нажимаем Enter.
Мы получили 5 ромбов в ячейке С2. Копируем эту формулу вниз.
Таким образом, мы получили диаграммы в ячейках.
Если в качестве символа диаграммы мы хотим использовать символ с клавиатуры, то нужно ввести формулу: =ПОВТОР(«|»;В2). Команду Символ мы не используем, а в кавычках указываем нужный символ. Получим следующие диаграммы:
Для диаграмм, полученных таким способом, можно установить цвет шрифта. Если поменять число баллов в столбце В, то диаграммы автоматически изменятся. Такая таблица динамическая.
С помощью функции ПОВТОР можно настроить ручные спарклайны в Excel 2003, 2007.
Удаление спарклайнов
Кнопки клавиатуры Backspase и Delete не позволяют удалить из ячейки спарклайн. Для удаления нужно выделить ячейку, содержащую спарклайн, и выбрать Конструктор — Очистить (в группе Группировка). Можно удалить спарклайн с помощью контекстного меню. Для этого щелкнем правой кнопкой мыши по ячейке, выберем Спарклайны — Очистить выбранные спарклайны.
Настройка скрытых и пустых ячеек в спарклайнах
Скрытие ячеек с данными, содержащимися в спарклайне, может привести к его исчезновению. Чтобы избежать этого, нужно настроить параметры поведения спарклайна при скрытии ячеек с данными и при наличии пустых ячеек.
Выделим ячейку со спарклайном, на вкладке Конструктор выберем Изменить данные — Скрытые и пустые ячейки.
В открывшемся диалоговом окне настраиваем поведение спарклайна.
Спасибо за Вашу оценку. Если хотите, чтобы Ваше имя
стало известно автору, войдите на сайт как пользователь
и нажмите Спасибо еще раз. Ваше имя появится на этой стрнице.
Понравился материал?
Хотите прочитать позже?
Сохраните на своей стене и
поделитесь с друзьями
Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст
Ошибка в тексте? Мы очень сожалеем,
что допустили ее. Пожалуйста, выделите ее
и нажмите на клавиатуре CTRL + ENTER.
Кстати, такая возможность есть
на всех страницах нашего сайта
Создание спарклайнов
Спарклайны — это небольшие диаграммы внутри отдельных ячеек на листе. Благодаря своей компактности спарклайны выразительно и наглядно показывают закономерности в больших наборах данных. С помощью спарклайнов можно демонстрировать тренды в рядах значений (например, сезонные увеличения и уменьшения или экономические циклы) либо выделять максимальные и минимальные значения. Спарклайн выглядит понятнее всего, когда он расположен рядом с соответствующим набором данных. Чтобы создать спарклайн, нужно выделить диапазон значений, которые требуется проанализировать, а затем выбрать место для спарклайна.
Диапазон данных для спарклинов
Спарклины столбцов, отображают продажи с начала года в Москве, Сан-Франциско и Нью-Йорке
Создание спарклайнов
Выделите диапазон данных для спарклайна.
На вкладке Вставка нажмите кнопку Спарклайныи выберите нужный тип спарклайна.
В диалоговом окне Вставка спарклайнов обратите внимание на то, что первое поле уже заполнено с учетом того, что вы выбрали на шаге 1.
Выделите на листе ячейку или диапазон ячеек, куда нужно поместить спарклайн.
Важно: Размеры выбираемой области должны соответствовать диапазону данных. В противном случае Excel отобразит ошибки, свидетельствующие о том, что диапазоны не совпадают. Например, если в диапазоне значений три столбца и одна строка, следует выбрать смежный столбец и ту же строку.
Совет: При изменении данных на листе спарклайны автоматически обновляются.
Отметка точек данных на спарклайнах
С помощью маркеров можно выделять отдельные точки данных, такие как максимальные и минимальные значения.
На вкладке Конструктор спарклайнов в группе Показать выберите нужные маркеры, например высокий и низкий баллы. Вы можете настроить цвета маркера, нажав кнопку Цвет маркера.
Изменение стиля спарклайна
На вкладке Конструктор спарклайнов выберите нужный стиль.
Чтобы просмотреть другие стили, наведите курсор на интересующий вас элемент и щелкните 
.
Совет: Чтобы сразу же отменить примененный стиль, нажмите клавиши 
+Z.
Обработка пустых ячеек и нулевых значений
Удаление спарклайнов
Щелкните спарклайн, который нужно удалить.
На вкладке Конструктор спарклайнов щелкните стрелку рядом с кнопкой Очиститьи выберите нужный вариант.
Создание спарклайнов
Выделите диапазон данных для спарклайна.
На вкладке Диаграммы в области Вставка спарклайноввыберите нужный тип спарклайна.
В диалоговом окне Вставка спарклайнов обратите внимание на то, что первое поле уже заполнено с учетом того, что вы выбрали на шаге 1.
Выделите на листе ячейку или диапазон ячеек, куда нужно поместить спарклайн.
Важно: Размеры выбираемой области должны соответствовать диапазону данных. В противном случае Excel отобразит ошибки, свидетельствующие о том, что диапазоны не совпадают. Например, если в диапазоне значений три столбца и одна строка, следует выбрать смежный столбец и ту же строку.
Совет: При изменении данных на листе спарклайны автоматически обновляются.
Отметка точек данных на спарклайнах
С помощью маркеров можно выделять отдельные точки данных, такие как максимальные и минимальные значения.
На вкладке Спарклины в области Маркерывыберите нужные маркеры.
Изменение стиля спарклайна
На вкладке Спарклины в области Форматвыберите нужный стиль.
Чтобы просмотреть другие стили, наведите курсор на интересующий вас элемент и щелкните .
Совет: Чтобы сразу же отменить примененный стиль, нажмите клавиши +Z.
Обработка пустых ячеек и нулевых значений
Удаление спарклайнов
Щелкните спарклайн, который нужно удалить.
На вкладке Спарклины в области Правкащелкните стрелку рядом с кнопкой Очистить ивыберите нужный параметр.
Спарклайны или микрографики в ячейках Excel
Спарклайны — маленькие диаграммы, помещающиеся в одну ячейку листа. Используются для наглядного отображения изменения данных. Спарклайны позволяют обнаружить тенденции, периодические (сезонные) изменения, выделить максимальное (минимальное) значение. [1]
Например, на рис. 1 показаны изменения среднемесячной температуры июля в Москве за период наблюдений с 1879 по 2013 г. Чтобы рисунок был более наглядным показаны не абсолютные значения, а колебания относительно среднего значения за весь период наблюдений (18,4⁰С). Красным выделен абсолютный максимум (2010 г., 26,1⁰С) и минимум (1904 г., 14,6⁰С).
Рис. 1. Динамика среднемесячной температуры июля в Москве
Скачать заметку в формате Word или pdf, примеры в формате Excel
Чтобы создать спарклайн перейдите на вкладку Вставить → область Спарклайны (рис. 2). Excel предлагает три типа спарклайнов: график, гистограмму и диаграмму Выигрыш/проигрыш (рис. 3). Последняя показывает столбиками одинаковой высоты значения + и –.
Рис. 2. Меню Спарклайны
Рис. 3. Типы спарклайнов: (а) график, (б) гистограмма, (в) Выигрыш/проигрыш
Кликнув на один из видов графика, вы попадете в меню Создание спарклайнов (рис. 3). Задайте Диапазон данных и Диапазон расположения спарклайна. Помните, что спарклайн может размещаться только в одной ячейке Excel, или в нескольких ячейках, объединенных с помощью опции Объединить и поместить в центре (рис. 5). Попытка ввести в поле Диапазон расположения меню Создание спарклайнов несколько ячеек завершится сообщением об ошибке. Диалоговое окно Создание спарклайнов позволяет выбирать диапазоны и ячейки непосредственно на листе Excel, а не набирать их адреса с клавиатуры. При изменении размеров ячейки со спарклайном меняется масштаб последнего, что может улучшить визуализацию.
Рис. 4. Окно Создание спарклайнов
Рис. 5. Объединение ячеек
После создания спарклайна на ленте появится вкладка Работа со спарклайнами. Для настройки спарклайнов используйте команды на вкладке Конструктор (рис. 6). Если после создания спарклайна вы совершали еще какие-либо действия в Excel, вкладка Работа со спарклайнами может пропасть с ленты. Для того, чтобы вызвать эту вкладку, кликните на спарклайне, с которым вы планируете работать. Заметил небольшую нестыковку: первый тип спарклайна вкладки Вставка (см. рис. 2) называется График, а на вкладке Конструктор – Линия (см. рис. 6).
Рис. 6. Вкладка Конструктор ленты Excel
Рассмотрим подробнее элементы вкладки Работа со спарклайнами на примере динамики Индекса потребительских цен США (CPI) за период 2006–2014 (рис. 7). На момент публикации заметки последний отчет относился к марту 2014 г. Как и в предыдущем пример, чтобы данные были более рельефными используются не абсолютные значения индекса (область А1:М11), а изменения в процентах к предыдущему месяцу (область А13:М22).
Рис. 7. Индекс потребительских цен США (CPI) за период с января 2006 по март 2014 г. Для справки: значение CPI=10 это среднее значение по 1913 г.
Поскольку спарклайн не может быть создан одновременно в нескольких ячейках, создайте его в ячейке N14, а затем «протащите» вниз, как обычную формулу!
Меню Изменить данные позволяет (рис. 8):
1) Вызвать окно Создание спарклайнов (как на рис. 4) и изменить Диапазон данных и/или Диапазон расположения, как для одного спарклайна, так и для всех спарклайнов в группе. Например, вы можете изменить Диапазон расположения и перенести все спарклайны из ячеек N14:N22 в ячейки О14:О22. Или не отображать данные за каждый декабрь, сократив Диапазон данных с А14:М22 до А14:L22.
2) Задать режим отображения скрытых и пустых ячеек. Например, на рис. 9 скрыты столбцы F, G, H. Вызвав опцию Скрытые и пустые ячейки, можно выбрать два варианта отображения скрытых строк/столбцов: отображать или не отображать.
3) Выбрать режим отображения данных в спарклайне: по строкам или по столбцам. Эта команда применяется при одинаковом количестве строк и столбцов в Диапазоне данных.
Рис. 8. Опции меню Изменить данные
Рис. 9. Режим отображения скрытых и пустых ячеек
Меню Тип позволяет изменить тип спарклайна на один из трех доступных: график (он же линия), гистограмма, Выигрыш/проигрыш.
Меню Показать позволяет задать элементы спарклайна, которые будут отражаться другим цветом (рис. 10). Например, на рис. 1 включены опции Максимальная точка и Минимальная точка, а на рис. 9 включена опция Отрицательные точки. Заметим, что опция Маркеры доступна только для типа спарклайна Линия (рис. 11).
Рис. 10. Опции меню Показать
Рис. 11. Спарклайн Линия с выключенной (а) и включенной (б) опцией Маркер
Опции меню Стиль позволяют выбрать одну из многочисленных цветовых гамм (рис. 12), а также, если ни один вариант не подходит (что довольно странно :)), задать цвет самостоятельно в цветовой палитре Цвет спарклайна. Также можно установить пользовательские цвета для всех особых точек: отрицательных, максимального, минимального значений и др.
Рис. 12. Опции меню Стиль
Опции меню Группировать. По умолчанию (например, на рис. 9) спарклайны, созданные друг под другом, сгруппированы. Если вы хотите их разгруппировать, воспользуйтесь соответствующей опцией в этой группе меню. В дальнейшем их опять можно будет сгруппировать. Группировка влияет на управление Диапазоном данных и Диапазоном расположения в меню Изменить данные. Только сгруппированными спарклайнами можно управлять, как единым целым.
Ни Delete, ни Backspace не удаляют спарклайны. Для этого есть специальная кнопка Очистить. Также можно щелкнуть на ячейке правой кнопкой мыши, и в появившемся контекстном меню выбрать Спарклайны, а затем Очистить выбранные спарклайны или группы спарклайнов.
Наконец, опция Ось позволяет управлять параметрами спарклайна, как по оси ординат, так и по оси абсцисс (рис. 13). Например, на рис. 3а я включил опцию Показать ось. Параметры Минимального и максимального значений по вертикальной оси позволяют отображать спарклайны в одном масштабе (при включенной опции Фиксированное для всех значений), а также увеличить масштаб, если какое-то значение экстремально, и из-за него динамика других значений становятся плохо различимой (рис. 14).
Рис. 13. Опции меню Ось
Рис. 14. Выбор параметров минимального и максимального значений по вертикальной оси: (а) автоматическое, (б) фиксированное, (в) пользовательское ±1%. Опция Автоматическое позволяет наилучшим образом сравнивать значения в пределах одного спарклайна, но привносит искажения при сравнении нескольких спарклайнов между собой.
Поскольку спарклайны размещаются в отдельном слое, в ячейке Excel со спарклайном может находиться любое значение или текст (рис. 15).
Спарклайны в Microsoft Excel
Спарклайны впервые появились в Excel 2010 года и с тех пор набирают все большую популярность. Несмотря на то, что спарклайны очень похожи на миниатюрные диаграммы, это не одно и то же и у них немного разное назначение. В этом уроке мы познакомим Вас со спарклайнами и расскажем, как ими пользоваться в рабочей книге Excel.
Бывают ситуации, когда необходимо проанализировать и изучить зависимость в массиве данных Excel, не создавая полноценную диаграмму. Спарклайны – это небольшого размера диаграммы, которые помещаются в одну ячейку. Благодаря их компактности, можно включить сразу несколько спарклайнов в одну рабочую книгу.
В некоторых источниках спарклайны называют инфолиниями.
Типы спарклайнов
В Excel существует три типа спарклайнов: спарклайн-график, спарклайн-гистограмма и спарклайн выигрыша/проигрыша. Спарклайн-график и спарклайн-гистограмма работают так же, как и обычные графики и гистограммы. Спарклайн выигрыша/проигрыша похож на стандартную гистограмму, но отображает не величину значения, а каким оно является – положительным или отрицательным. Все три типа спарклайнов могут отображать маркеры в важных местах, например, максимумы и минимумы, благодаря этому их очень легко читать.
Для чего используются спарклайны?
Спарклайны в Excel имеют ряд преимуществ над обычными диаграммами. Представьте, что у Вас есть таблица, состоящая из 1000 строк. Стандартная диаграмма построила бы 1000 рядов данных, т.е. по одному ряду для каждой строки. Думаю, не трудно догадаться, что на такой диаграмме будет сложно что-либо найти. Куда эффективней для каждой строки в таблице Excel создать отдельный спарклайн, который будет располагаться рядом с исходными данными, позволяя наглядно увидеть взаимосвязь и тенденцию отдельно для каждого ряда.
На рисунке ниже можно увидеть довольно громоздкий график, в котором трудно что-либо разобрать. Спарклайны же позволяют четко отследить продажи каждого торгового представителя.
К тому же спарклайны выгодно применять, когда нужен простой обзор данных и нет необходимости использовать громоздкие диаграммы с множеством свойств и инструментов. При желании Вы можете использовать как обычные графики, так и спарклайны для одних и тех же данных.
Создание спарклайнов в Excel
Как правило, для каждого ряда данных строится один спарклайн, но при желании Вы можете создать любое количество спарклайнов и разместить их там, где необходимо. Первый спарклайн проще всего создать в самой верхней строке с данными, а затем, используя маркер автозаполнения, скопировать его во все оставшиеся ряды. В следующем примере мы создадим спарклайн-график, чтобы наглядно представить динамику продаж для каждого торгового представителя за определенный промежуток времени.
Изменение внешнего вида спарклайнов
Скорректировать внешний вид спарклайна довольно просто. Excel предлагает для этих целей целый ряд инструментов. Вы можете настроить отображение маркеров, задать цвет, изменить тип и стиль спарклайна, и многое другое.
Отображение маркеров
Вы можете акцентировать внимание на определенных участках спарклайн-графика с помощью маркеров или точек, тем самым увеличивая его информативность. Например, на спарклайне с множеством больших и малых значений очень трудно понять, какое из них является максимальным, а какое минимальным. С включенными опциями Максимальная точка и Минимальная точка сделать это намного проще.
Изменение стиля
Изменение типа
Каждый тип спарклайна предназначен для определенных целей. Например, спарклайн выигрыша/проигрыша больше подходит для данных, где имеются положительные или отрицательные значения (к примеру, чистая прибыль).
Изменение диапазона отображения
По умолчанию каждый спарклайн в Excel имеет такой масштаб, который соответствует максимальному и минимальному значению его исходных данных. Максимальное значение располагается вверху ячейки, а минимальное внизу. К сожалению, это не демонстрирует величину значения, если сравнивать его с другими спарклайнами. Excel позволяет изменить внешний вид спарклайнов таким образом, чтобы их можно было сравнивать друг с другом.