Работа с рядами данных excel

Рассмотрим построение в

MS

EXCEL

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

Построение диаграмм с одним рядом данных рассмотрено в статье

Основы построения диаграмм в MS EXCEL

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

Основные типы диаграмм

.

ГИСТОГРАММА

Построим

Гистограмму с группировкой

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

Выделите любую ячейку таблицы (см.

файл примера

), на вкладке

Вставка

, в группе

Диаграммы

нажмите кнопку

Гистограмма

, в выпавшем меню выберите

Гистограмма с группировкой

.

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

Формат ряда данных

на вкладке

Параметры ряда

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

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

Вспомогательной вертикальной оси

.

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

Если не настроить ширину столбцов, то эту диаграмму можно спутать с диаграммой

Гистограмма с пополнением

(столбцы, относящие к одной категории «ставятся» друг на друга).

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

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

Теперь изменим подписи по горизонтальной оси (категории).

В окне

Выбор источника данных

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

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

При желании это также можно изменить. В окне

Выбор источника данных

выделите ряд Прибыль, нажмите кнопку

Изменить

расположенную справа, удалите ссылку на ячейки. Теперь у ряда Прибыль вместо названия категорий будут просто порядковые числа 1, 2, 3,… Однако, они не будут отображаться на диаграмме, т.к. отображается пока только

Основная горизонтальная ось

.

Теперь в меню Оси (вкладка

Макет

, группа

Оси

) выберите

Вспомогательная горизонтальная ось

и установите ее

Слева направо

. В окне формата

Вспомогательной вертикальной оси

измените точку пересечения оси (установите

Автовыбор

). Получим вот такую диаграмму.

Хотя техническая возможность отображения 2-х различных категорий существует, конечно, таких диаграмм нужно избегать, т.к. их сложно воспринимать. Категории должны быть одинаковыми для всех рядов на диаграмме. Естественно, такой трюк сработает только для двух групп рядов, т.к. имеется всего 2 типа оси: основная и вспомогательная.

ГРАФИК

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

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

Примечание

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

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

ТОЧЕЧНАЯ

Визуально Точечная диаграмма похожа на диаграмму типа График (если конечно у Точечной диаграммы точки соединены линиями).

Различие состоит в том, что для построения Графика в качестве координаты Х всегда используется последовательность 1, 2, 3, … (с подписями — категориями), а для Точечной значения по горизонтальной оси м.б. любыми числами (см. статью

График vs Точечная

).

Примечание

. Если для построения Точечной диаграммы не указана ссылка на значения Х (или ссылка указывает на текстовые значения), то в качестве координат по Х будет использована та же последовательность 1, 2, 3, …, что и для Графика.

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

Теперь о совмещении разных типов диаграмм с Точечной. Если для Точечной диаграммы не используется координата Х, то на диаграмме она выглядит как График.

Подписи по горизонтальной оси берутся от Графика. В окне

Выбор источника данных

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

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

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

Это связано с тем, что при совмещении с Точечной диаграммой (на одной оси и когда у Точечной указаны значения по Х), диаграмма типа График становится как бы главной:

• на горизонтальной оси отображаются подписи только для Графика;
• вертикальная сетка не отображается для отрицательных значений Х (т.к. График строится только для Х=1, 2, 3, …);
• у Графика невозможно изменить Ось с Основной на Вспомогательную (у Точечной можно).

Если Точечную построить на вспомогательной оси, то диаграмма изменится.

Подписи для Точечной (значения по Х) теперь отображаются сверху.

Совет

. Диаграмма типа Точечная используется для

построения окружностей, эллипсов

и других

замкнутых фигур на плоскости

.

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

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

Примечание

. Фактически на диаграмме 4 ряда данных: точка центра представляет отдельный ряд.

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

Теперь координаты Y для бордового эллипса откладываются по правой вертикальной оси (можно ее для наглядности выделить также бордовым цветом).

Добавим Вспомогательную горизонтальную ось (в меню Оси (вкладка

Макет

, группа

Оси

) выберите

Вспомогательная горизонтальная ось

и установите ее

По умолчанию

).

Наверное, для диаграммы типа

Точечная

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

КРУГОВАЯ

В статье

Основные типы диаграмм

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

Совмещение разных типов диаграмм

Про совмещение Графика и Точечной диаграммы рассказано выше (см. раздел Точечная).

Совмещением Линейчатой и Гистограммы не стоит заниматься, т.к. выглядит это достаточно странно, почти также странно, как и совмещение Гистограммы с Круговой (см. также статью

Основные типы диаграмм, раздел Круговая

).

Совет

. Также можно посмотреть статью

Совмещаем разные типы диаграмм

.

Оригинальностью совмещения могут также похвастаться

Гистограмма

и

Нормированная линейчатая с накоплением

.

Наверное, единственными типами диаграмм, которые стоит совмещать, являются

График

и

Гистограмма

(категории должны быть одинаковыми для обоих рядов).

Для такой диаграммы постройте сначала

Гистограмму с группировкой

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

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

(вкладка

Конструктор

). Выбрав

График

или

График с Маркерами

нажмите ОК.

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

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

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

Временные ряды в Excel

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

Сделаем анализ временных рядов в Excel. Пример: торговая сеть анализирует данные о продажах товаров магазинами, находящимися в городах с населением менее 50 000 человек. Период – 2012-2015 гг. Задача – выявить основную тенденцию развития.

Внесем данные о реализации в таблицу Excel:

На вкладке «Данные» нажимаем кнопку «Анализ данных». Если она не видна, заходим в меню. «Параметры Excel» — «Надстройки». Внизу нажимаем «Перейти» к «Надстройкам Excel» и выбираем «Пакет анализа».

Подключение настройки «Анализ данных» детально описано здесь.

Нужная кнопка появится на ленте.

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

Заполняем диалоговое окно. Входной интервал – диапазон со значениями продаж. Фактор затухания – коэффициент экспоненциального сглаживания (по умолчанию – 0,3). Выходной интервал – ссылка на верхнюю левую ячейку выходного диапазона. Сюда программа поместит сглаженные уровни и размер определит самостоятельно. Ставим галочки «Вывод графика», «Стандартные погрешности».

Закрываем диалоговое окно нажатием ОК. Результаты анализа:

Для расчета стандартных погрешностей Excel использует формулу: =КОРЕНЬ(СУММКВРАЗН(‘диапазон фактических значений’; ‘диапазон прогнозных значений’)/ ‘размер окна сглаживания’). Например, =КОРЕНЬ(СУММКВРАЗН(C3:C5;D3:D5)/3).



Прогнозирование временного ряда в Excel

Составим прогноз продаж, используя данные из предыдущего примера.

На график, отображающий фактические объемы реализации продукции, добавим линию тренда (правая кнопка по графику – «Добавить линию тренда»).

Настраиваем параметры линии тренда:

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

R2 = 0,9567, что означает: данное отношение объясняет 95,67% изменений объемов продаж с течением времени.

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

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

Получаем достаточно оптимистичный результат:

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

Для прогнозирования экспоненциальной зависимости в Excel можно использовать также функцию РОСТ.

Для линейной зависимости – ТЕНДЕНЦИЯ.

При составлении прогнозов нельзя использовать какой-то один метод: велика вероятность больших отклонений и неточностей.

Время на прочтение
12 мин

Количество просмотров 14K

Не жди поездку в Гагры!
Давид Маркович, сериал «Ликвидация»

Всем привет!

За время карантина все, наверное, не по разу прочитали о том, как плодотворно А.С. Пушкин провел свой период «заточения», названный впоследствии «Болдинской осенью», поэтому не буду объяснять, что именно меня сподвигло написать следующую статью об инфографике средствами MS Office.

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

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

Я — легенда

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

Вариантов замены легенды значками несколько. Один мы рассматривали в предыдущей статье, в этой — будем рассматривать еще два. Первый – замена маркеров графика значками, а второй – заполнение рядов значками.

Начнем с первого. Он наиболее простой.

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

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

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

Данные можете придумать и свои, а символ с изображением стопки книжек из шрифта Webdings вам в помощь! 

Властелин колец

Моя прелесть…

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

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

Первый — для визуализации одного итогового значения, а второй — для нескольких.

По традиции начнем с первого.

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

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

А теперь к следующему примеру.

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

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

Миссия невыполнима

Выполнима или невыполнима, не нам решать, но наблюдать за процессом очень интересно…

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

Вот вам пара примеров. Такие диаграммы еще называют диаграммами прогресса, а первый вариант очень часто называют термометром.

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

А второй пример диаграммы будем строить с небольшой хитростью. Тут нам понадобится функционал PowerPoint.

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

Иллюзия обмана

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

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

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

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

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

Эта команда позволит нам создавать целые информационные панели, не обращаясь к специализированным надстройкам. 

Вот пример простого размещения снимков диаграмм на одном листе.  

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

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

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

Теперь на вкладке Главная есть кнопка

Самое время разобраться, как она работает.

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

Приступим.

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

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

Теперь перейдем на лист со снимком…

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

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

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

А тех, кто хочет узнать о визуализации данных в Excel и PowerPoint больше, ждем на нашем онлайн-курсе по инфографике!

До встречи в сети или на наших курсах!