Диаграммы позволяют нам комфортно воспринимать информацию. Excel обладает широкими возможностями для создания диаграмм и графиков. А если добавить к диаграммам формулы, то тогда появляется дополнительная возможность для создания динамических отчетов и презентаций.
Рассмотрим, как применять формулы и условное форматирование в диаграммах Excel.
Примеры формул в диаграммах
Построим на основе ряда данных простой график с маркерами:
Если щелкнуть по любой точке графика, то в строке формул появится функция РЯД. Именно с ее помощью генерируются ряды данных всех диаграмм. Эта функция применяется только для определения значений точек на графиках. Просто использовать ее на рабочем листе невозможно.
Аргументы функции РЯД:
- Имя (название ряда данных, отображается в легенде; не обязательный аргумент);
- Подписи категорий (метки, появляющиеся на оси категорий; не обязательный аргумент);
- Значения (которые применяются для построения графика; обязательный параметр);
- Порядок (порядок значений в ряду данных; обязательный параметр).
Аргументы функции РЯД можно найти и изменить в диалоговом окне «Выбрать данные»:
Выделим элемент легенды «y» и щелкнем по кнопке изменить. В поле «Имя ряда» содержится аргумент функции «Имя»:
Название ряда данных – «y». Его можно менять.
В поле «Значения» — аргумент значений ряда данных.
Подписи горизонтальной оси – это аргумент функции РЯД «Подписи категорий»:
Так как наш график построен на основе одного ряда данных, то порядок равняется единице. Данный аргумент отражается в списке «Элементы легенды».
Аргументы функции РЯД допускают применение именованных диапазонов. Если воспользоваться данной возможностью, то можно создать динамическую диаграмму, быстро переключаться между данными одного ряда.
Присмотримся поближе к применению именованных динамических диапазонов при построении диаграмм.
Создание динамических диаграмм
Для имеющейся исходной таблицы с данными создадим именованные диапазоны: для первого столбца – категорий – «х»; для второго – точек данных – «у».
Открываем вкладку «Формулы» — нажимаем кнопку «Диспетчер имен».
В диалоговом окне жмем «Создать». Откроется окно «Создание имени». В поле «Имя» вводим имя диапазона. В поле «Диапазон» — формулу для ссылки на данные в первом столбце (=СМЕЩ(Лист1!$A$1;1;0;СЧЁТЗ(Лист1!$A$1:$A$20)-1;1)).
Чтобы заголовок ряда данных не включался в именованный диапазон, за аргументами функции СЧЕТЗ ставим «-1». В качестве диапазона можно указывать весь столбец А – Excel быстро определяет пустые ячейки. В примере мы поставили лишь первые 20 ячеек.
Создаем именованный диапазон для второго столбца. По такому же принципу.
Теперь поменяем ссылки на ряд данных в графике именами динамических диапазонов. Вызываем диалоговое окно «Выбор источника данных». Выделяем элемент легенды и нажимаем «Изменить». Меняем ссылки в поле «Значения» на имя диапазона.
Далее жмем «Изменить подписи горизонтальной оси». Задаем для диапазона назначенной имя.
График остается прежним. Но если мы добавим в имеющуюся таблицу новые данные, они тут же попадут на диаграмму.
При работе с огромным массивом данных иногда нужно создать диаграмму только на основе некоторого количества последних значений в ряду. Чтобы формула выбирала только их, при формировании динамического именованного диапазона прописываем следующее: =СМЕЩ(Лист1!$A$1;СЧЁТЗ(Лист1!$A$1:$A$1000)-40;0;40;1). По такому же принципу – для столбца В.
Сколько бы данных мы ни добавляли в исходную таблицу, на графике будет показано только последние 40 значений.
Условное форматирование в диаграмме
Данный инструмент достаточно просто используется для обычных данных. Для диаграмм в Excel применить встроенное условное форматирование невозможно. Нужно идти другим путем.
Зачем это? Для улучшения восприятия информации. При изменении значений в исходных ячейках автоматически будет меняться цветовое исполнение диаграммы.
Выполнить условное форматирование в диаграммах можно с помощью макросов и формул. Рассмотрим второй способ.
На основании тех же исходных данных составим гистограмму:
Так выглядит диаграмма без форматирования. Нужно сделать следующим образом: отдельные столбики должны закрашиваться в определенный цвет в зависимости от значения.
Для условного форматирования требуется формула, которая определяет отформатированные ячейки.
Для каждого условия создадим отдельный ряд данных. Значения в исходной таблице находятся в диапазоне от 0,06 до 5,7. Создадим ряд для периодов 0-0,6; 0,6-1,6; 1,6-3; 3-4,6; 4,6-6.
Сформируем данные для гистограммы с условным форматированием. Диапазон условий внесем в строки 1 и 2. Заголовки – в строку 3. Формулы для заголовков:
Заполним колонки для диаграммы с условным форматированием. Воспользуемся формулой, которая будет отображать значения, находящиеся в диапазонах заголовков.
Источник данных для гистограммы – столбцы А и В. Нужно исключить колонку В и добавить вновь созданный диапазон С:F.
Теперь столбики диаграммы окрашены в разные цвета в зависимости от значения.
Часто на диаграмме необходимо отобразить не все значения из исходной таблицы, а только те, которые удовлетворяют определенным критериям. Для построения такой диаграммы можно использовать формулы. В этом случае необходимо предварительно создать отдельную таблицу для данных, удовлетворяющих заданным условиям.
Пусть имеется таблица поставок фруктов разными поставщиками (см.
файл примера
):
Построим диаграмму суммарных поставок определенного фрукта по каждому поставщику.
Выборку данных из исходной таблицы будем осуществлять функцией
СУММПРОИЗВ()
:
=СУММПРОИЗВ($D$3:$D$100*($B$3:$B$100=G3)*($C$3:$C$100=$H$1))
Фрукт, поставки которого нужно отобразить, можно, например, выбирать из
Выпадающего (раскрывающегося) списка
(ячейка
H1
), реализованного на основе инструмента
Проверка данных
.
В принципе, аналогичного результата можно добиться функцией
СУММЕСЛИМН()
,
формулами массива
или другими функциями (см., например статью
Сложение с несколькими критериями (Часть 1. Введение)
).
С другими динамическими диаграммами можно познакомиться в статье
Динамические диаграммы. Общие замечания
.
СОВЕТ
: Для начинающих пользователей EXCEL советуем прочитать статью
Основы построения диаграмм в MS EXCEL
, в которой рассказывается о базовых настройках диаграмм, а также
статью об основных типах диаграмм
.
Добавление линии тренда или скользящего среднего на диаграмму
Добавьте линию тренда на диаграмму, чтобы показать визуальные тенденции изменения данных.
Примечание: Эти действия применимы Office 2013 и более новых версиях. Ищете по Office 2010 г.?
Добавление линии тренда
Щелкните значок «+» в правом верхнем углу диаграммы.
Выберите пункт Линия тренда.
Примечание: Excel отображает параметр Линия тренда только в том случае, если выбрана диаграмма с более чем одним рядом данных без выбора ряда данных.
В диалоговом окне Добавление линии тренда выберите нужные параметры рядов данных и нажмите кнопку ОК.
Форматирование линии тренда
Щелкните в любом месте диаграммы.
На вкладке Формат в группе Текущий выделение выберите в списке вариант линии тренда.
Нажмите кнопку Формат выделения.
В области Формат линии тренда выберите параметр Линия тренда, чтобы выбрать линию тренда для диаграммы. Форматирование линии тренда — это статистический способ измерения данных: 
Задайте значение в полях «Вперед» и «Назад», чтобы проецируемые данные в будущем.
Добавление линии скользящего среднего
Линию тренда можно отформатирование на линию скользящего среднего.
Щелкните в любом месте диаграммы.
На вкладке Формат в группе Текущий выделение выберите в списке вариант линии тренда.
Нажмите кнопку Формат выделения.
В области Формат линии тренда в области Параметры линии трендавыберите Скользящие средниезначения . При необходимости укажите баллы.
Примечание: Число точек в линии тренда скользящего среднего равно общему числу точек в ряду, которое меньше числа, заданная для точки.
Добавление линии тренда или скользящего среднего на диаграмму в Office 2010 г.
На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните ряд данных, для которого требуется добавить линию тренда или линейную фильтрацию, или выполните указанные ниже действия, чтобы выбрать ряд данных из списка элементов диаграммы.
Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
Примечание: Если выбрана диаграмма с несколькими рядами данных, но сам ряд данных не выбран, откроется диалоговое окно Добавление линии тренда. В поле со списком выберите нужный ряд данных, а затем нажмите кнопку ОК.
На вкладке Макет в группе Анализ выберите пункт Линия тренда.
Выполните одно из указанных ниже действий.
Выберите подходящий предопределенный параметр линии тренда.
Примечание: Линия тренда будет применена без возможности выбора конкретных параметров.
Нажмите Дополнительные параметры линии тренда, а затем в категории Параметры линии тренда в разделе Построение линии тренда (аппроксимация и сглаживание) выберите нужный тип линии тренда.
Создание прямой линии тренда путем расчета по методу наименьших квадратов с помощью следующего уравнения:
где m — это наклон, а b — смещение.
Построение логарифмической линии тренда путем расчета точек методом наименьших квадратов с помощью следующего уравнения:
где c и b — константы, а «ln» — натуральный логарифм.
Построение полиномиальной или криволинейной линии тренда путем расчета точек методом наименьших квадратов с помощью следующего уравнения:
где b и 
являются константами.
Построение степенной линии тренда путем расчета точек методом наименьших квадратов с помощью следующего уравнения:
где c и b — константы.
Примечание: При наличии нулевых или отрицательных значений данных этот параметр недоступен.
Построение экспоненциальной линии тренда путем расчета точек методом наименьших квадратов с помощью следующего уравнения:
где c и b — константы, e — основание натурального логарифма.
Примечание: При наличии нулевых или отрицательных значений данных этот параметр недоступен.
Построение линии тренда с линейной фильтрацией по следующей формуле: 
Примечание: Число точек, образующих линию тренда с скользящее среднее, равно общему числу точек ряда за вычетом числа, указанного для параметра «Точки».
Величина достоверности аппроксимации
Построение линии тренда с указанием на диаграмме величина достоверности аппроксимации, вычисляемой по следующей формуле:
Этот параметр линии тренда располагается на вкладке Параметры диалогового окна Добавление линии тренда или Формат линии тренда.
Примечание: Отображаемая вместе с линией тренда величина достоверности аппроксимации не является скорректированной. Для логарифмической, степенной и экспоненциальной линий тренда в Excel используется видоизмененная модель регрессии.
Если выбран тип Полиномиальная, введите в поле Степень наибольшую степень для независимой переменной.
Если выбран тип Линейная фильтрация, введите в поле Точки число точек, используемых для расчета линейного фильтра.
При добавлении скользящего среднего на точечная диаграмма скользящие средние значения основаны на порядке, за исключением значений X, относящегося к диаграмме. Чтобы получить нужный результат, перед добавлением скользящего среднего может потребоваться отсортировать значения X.
При добавлении линии тренда в график, гистограмму, диаграмму с областями или линейчатую диаграмму линия тренда рассчитывается исходя из предположения, что значения x равны 1, 2, 3, 4, 5, 6 и т. д. Такое допущение делается вне зависимости от того, являются ли значения x числовыми или текстовыми. Чтобы рассчитать линию тренда на основе числовых значений x, следует использовать точечную диаграмму.
В Excel название линии тренда назначается автоматически, но его можно изменить. В диалоговом окне Формат линии тренда в категории Параметры линии тренда в разделе Название аппроксимирующей (сглаженной) кривой выберите параметр Другое, а затем укажите название в поле Другое.
Кроме того, можно добавить линейную фильтрацию, которая сглаживает отклонения в данных и более четко показывает форму линии тренда.
Если изменить диаграмму или ряд данных таким образом, что они больше не будут поддерживать соответствующую линию тренда (например, если изменить тип диаграммы на объемную диаграмму или изменить представление отчет сводной диаграммы или связанный отчет сводной таблицы), линия тренда больше не будет отображаться на диаграмме.
Для данных в строке (без диаграммы) наиболее точные прямые или экспоненциальные линии тренда можно создать с помощью автозаполнения или статистических функций, таких как РОСТ() или ТЕНДЕНЦИЯ().
На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, которую необходимо изменить, или выполните следующие действия, чтобы выбрать ее из списка элементов диаграммы.
Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда.
Чтобы изменить параметры цвета, типа или тени линии тренда, выберите категорию Цвет линии, Тип линии или Тень и задайте нужные значения.
На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, которую необходимо изменить, или выполните следующие действия, чтобы выбрать ее из списка элементов диаграммы.
Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда.
Чтобы указать число периодов для включения в прогноз, в разделе Прогноз укажите число периодов в поле вперед на или назад на.
На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, которую необходимо изменить, или выполните следующие действия, чтобы выбрать ее из списка элементов диаграммы.
Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда.
Установите флажок пересечение кривой с осью Y в точке, а затем в поле пересечение кривой с осью Y в точке введите значение, чтобы задать точку пересечения линии тренда с вертикальной осью (осью значений).
Примечание: Это можно сделать только при использовании экспоненциальной, прямой или полиномиальной линии тренда.
На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, которую необходимо изменить, или выполните следующие действия, чтобы выбрать ее из списка элементов диаграммы.
Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда.
Чтобы показать на диаграмме уравнение линии тренда, установите флажок показывать уравнение на диаграмме.
Примечание: Уравнения линии тренда нельзя показать для скользящего среднего.
Совет: Формула линии тренда округлена, чтобы сделать ее более понятной. Однако вы можете изменить количество цифр для выбранной подписи линии тренда в поле Число десятичных разрядов на вкладке Число в диалоговом окне Формат подписи линии тренда. ( Вкладка Формат, группа Текущий выделение, кнопка Формат выделения).
На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, для которой требуется показать величина достоверности аппроксимации, или выполните указанные ниже действия, чтобы выбрать линию тренда из списка элементов диаграммы.
Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда.
На вкладке Параметры линии тренда установите флажок поместить на диаграмму величину достоверности аппроксимации (R^2).
Примечание: Для скользящего среднего величину достоверности аппроксимации отобразить нельзя.
На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, которую необходимо удалить, или выполните следующие действия, чтобы выбрать линию тренда из списка элементов диаграммы.
Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
Выполните одно из указанных ниже действий.
На вкладке Макет в группе Анализ нажмите Линия тренда, а затем выберите пункт Нет.
Нажмите клавишу DELETE.
Совет: Вы также можете удалить линию тренда сразу после ее добавления на диаграмму, нажав кнопку Отменить 
панели быстрого доступа или нажав CTRL+Z.
Добавление линии тренда
В меню Вид выберите пункт Разметка страницы.
На диаграмме выберите ряд данных, к который вы хотите добавить линию тренда, а затем перейдите на вкладку Конструктор диаграмм.
Например, щелкните одну из линий графика. Будут выделены все маркер данных этого ряд данных.
На вкладке Конструктор нажмите кнопку Добавить элемент диаграммыи выберите линия тренда.
Выберите вариант линии тренда или нажмите кнопку Дополнительные параметры линии тренда.
Вы можете выбрать один из следующих вариантов:
Вы также можете дать линии тренда имя и выбрать параметры прогноза.
Удаление линии тренда
В меню Вид выберите пункт Разметка страницы.
Щелкните диаграмму с линией тренда и перейдите на вкладку Конструктор диаграмм.
Нажмите кнопку Добавить элемент диаграммы,выберите линия трендаи щелкните Нет.
Также можно щелкнуть линию тренда и нажать кнопку DELETE.
Щелкните в любом месте диаграммы, чтобы отдемонстрировать вкладку Диаграмма на ленте.
Нажмите кнопку Формат, чтобы открыть параметры форматирования диаграммы.
Откройте ряд, который вы хотите использовать в качестве основы для линии
тренда 
Настройте линию тренда в нужном вам направлении.
Используйте переключатель, чтобы показать или скрыть линию тренда.
Важно: Начиная с Excel 2005 г., Excel скорректировал способ расчета значения R2 для линейных линий тренда на диаграммах, для которых для перехватить линию тренда установлено значение 0 (0). Эта корректировка исправит вычисления, которые дают неправильные значения R 2, и выровняет вычисление R2 с функцией LINEST. В результате на диаграммах, созданных в предыдущих Excel, могут отображаться разные значения R2. Дополнительные сведения см. в таблице Изменения внутренних вычислений линейных линий тренда на диаграмме.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Линия тренда в Excel. На разных графиках
Существует множество сфер, где может использоваться трендовая линия. Но наиболее популярной из них, безусловно, являются финансовые рынки. В бизнесе тоже она применяется довольно часто для составления отчетов и прогнозирования возможных перспектив развития проекта или компании в целом.
Также она будет полезным инструментом в тех областях человеческой деятельности, где можно составлять прогнозы, ориентируясь на статистические данные. А таких сфер значительно больше, чем может показаться на первый взгляд. В частности, к ним относятся социология, психология, программирование (использование математических моделей позволяет автоматизировать процесс прогнозирования) и так далее. Особенно это полезно в сочетании с качественным анализом информации.
Основная проблема почти любого графика с большим количеством информации в том, что в нем показания очень вариативны. Поэтому возможно появление большого количества данных, которые мешают понять, какая действительно тенденция наблюдается в этой отрасли.
Трендовая линия – это классический статистический инструмент, позволяющий без особого опыта предсказать, в каком направлении будет двигаться целевой показатель через некоторое время и в какой точке будет. Также он дает возможность косвенно определить факторы, которые влияют на образование данного тренда. Правда, этого уже чисто статистическими методами сделать не получится, нужно понимать фундаментальные закономерности, которые влияют на формирование тенденции.
Как добавить линию тренда на график Excel
Трендовая линия дает возможность понять действующие тенденции определенного показателя. И поскольку особенно часто сей инструмент сейчас используется на финансовых рынках, то мы будем приводить на этом примере.
Трендовая линия на финансовых рынках является классическим инструментом анализа динамики котировок, которая используется еще с момента создания первой фондовой биржи. Но одной ее недостаточно, и сейчас разрабатываются все более новые технические индикаторы, которые позволяют значительно улучшить качество прогнозов. Существует еще отдельная категория индикаторов, которая позволяет непосредственно предвидеть разворот тенденции. В то же время трендовая линия дает возможность лишь констатировать имеющееся направление цены.
Поэтому рекомендуется использовать трендовую линию совместно с осцилляторами. Это категория технических индикаторов, которая успевает заметить изменение тренда до того, как это сделает трендовая линия.
Создание трендовой линии возможно стандартными средствами Excel. Она еще до сих пор активно используется, поэтому уметь ее строить надо. Давайте разберемся, как это сделать.
Процесс построения графика
Чтобы процесс построения трендовой линии был успешным, график должен соответствовать математической функции. Чтобы создать график, необходимо следовать этому алгоритму:
- Выделить интересующий набор ячеек.
- Зайти на панель инструментов (или, как нередко говорится, ленту) на вкладку «Главная», и там найти кнопку «Диаграммы».
- Затем нужно выбрать «Точечная, а затем «Точечная с гладкими линиями и маркерами».
После того, как пользователь сделает клик по графику, он сможет воспользоваться еще одной панелью, через которую и осуществляется добавление линии тренда на график.
Создание линии тренда
Итак, давайте перейдем на панель «Работа с диаграммами». Там находится вкладка «Макет», через которую и осуществляется добавление линии тренда на график. После того, как мы нажмем одноименную кнопку, появится возможность выбрать способ приближения. Нужно выбрать линейный тип. Затем появится черная линия, которая будет выражать действующую тенденцию.
Важно понимать, что некоторые типы графиков не поддерживают линию тренда. Поэтому рекомендуется использовать самые стандартные.
Все остальные разновидности графиков дают возможность добавлять линию тренда на них.
Настройка линии
Следующий этап – настройка. Сначала нужно добавить уравнение в график. Это делается путем двухкратного нажатия левой кнопкой мыши по диаграмме. Затем пользователю необходимо выбрать опцию «Показать уравнение на диаграмме».
Если на диаграмме находится больше одного графика, то нужно выбрать наиболее подходящий для прогнозирования.
Нужно запомнить следующие пункты, если нужно отрегулировать определенные параметры линии тренда:
- Чтобы назвать диаграмму, необходимо по ней сделать двойной клик мышью. Заголовок записывается в небольшом поле ввода. Чтобы определить местонахождение заголовка графика надо снова открыть вкладку «Работа с диаграммами», после чего выбрать «Макет» и «Название диаграммы». Всплывет перечень возможных расположений заголовка.
- В данном разделе находится настройка, позволяющая задать заголовки осей и их место на диаграмме. Важно то, что пользователь имеет право располагать название прямо по оси X или Y. Это дает возможность сделать внешний вид максимально удобным для чтения.
- Для внесения изменений сразу в линии, необходимо открыть вкладку «Макет» и там перейти по пунктам «Анализ – Прямая тренда». А если нажать на «Дополнительные параметры», то откроется окно, в котором можно настраивать линию тренда – регулировать ее формат, редактировать окрас, сглаживание и ряд других параметров.
- Также можно проверить трендовую линию на правдивость. Чтобы это сделать, нужно включить опцию «Разместить на график величину достоверности аппроксимации» и закрыть окно. Самое оптимальное значение – 1. Если наблюдаются отклонения, это говорит о степени достоверности построенной статистической модели.
Прогнозирование
Самые точные прогнозы возможны, если сделать тип графика «гистограмма». Это дает возможность сопоставить уравнения. Чтобы это сделать, надо следовать такому алгоритму:
- Открыть контекстное меню. В этом перечне нажать по пункту «Изменить тип диаграммы».
- Откроется окно настроек. Там выбирается тип «Гистограмма», а далее выбираем разновидность.
Теперь пользователь может видеть оба графика. Они содержат одну и ту же информацию, но дают возможность составлять более точные прогнозы, поскольку каждый тип графика использует свое уравнение для построения трендовой линии.
Далее перед нами стоит задача сопоставить разные диаграммы. Чтобы это сделать, надо включить визуальное отображение. Для этого выполняются следующие шаги:
- Формат гистограммы меняется на простой точечный график, какой мы делали ранее. Чтобы это сделать, нужно воспользоваться пунктом «Изменить тип диаграммы».
- Нажать два раза по линии тренда изменить параметры прогноза на 12 и сохранить внесенные правки.
С помощью таких параметров можно заметить, что угол наклона линии тренда отличается в зависимости от используемого графика, но все равно направление не меняется. Из этого можно сделать вывод, что недостаточно использовать лишь одну линию тренда, чтобы составлять надежные прогнозы на финансовых рынках.
Если речь идет о торговле на финансовых рынках, то использования технических индикаторов (коим трендовая линия и является) недостаточно. Нужно еще понимать фундаментальные законы экономики и общие качественные тенденции развития отрасли. Иначе даже при высоком коэффициенте достоверности реальная точность составляемых прогнозов будет очень страдать.
Уравнение линии тренда в Excel
Только лишь добавить линию тренда недостаточно, нужно еще уметь выбирать правильное уравнение, чтобы данные отображались максимально корректно. Определить, какое из них правильное, можно, ориентируясь на число достоверности. Если величина отличается от единицы, то диаграмма была настроена неверно. Соответственно, нужно по-другому отобразить график или использовать другие уравнения для расчета.
Линейная аппроксимация
Давайте приведем такой кейс: сотрудник заключал сделки в течение десяти месяцев, и каждый месяц им было совершено определенное их количество.
Построение диаграммы будет осуществляться с опорой на эти данные. Нужно просто выполнить некоторые действия, а именно:
- Добавление линии тренда на диаграмму.
- Добавление к диаграмме уравнения, которое используется для анализа тенденции и величины достоверности. Оба эти значения играют огромную роль в прогнозировании динамики любых показателей.
Предположим, у нас есть график, где введены данные и выбран тот тип отображения графика, при котором достоверность равна 0,9929, а исходя из данных диаграммы, можно сделать вывод, что сотрудник работает больше, чем до того. В таком случае с помощью графика можно определить, сколько сделок ожидается совершить в будущем. Чтобы это сделать, нужно в используемую формулу вставить номер периода.
Экспоненциальная линия тренда
Этот тип линии тренда знаком сейчас многим. По этому сценарию развиваются все пандемии, а также ряд других процессов. Характерная особенность этого вида линии тренда – цифры постоянно возрастают в геометрической прогрессии. Например, 1,2,4,8 и так далее. По похожему сценарию как раз и развиваются эпидемии, поскольку чем больше больных, тем большему количеству людей может передаться заболевание.
Мы попробуем абстрагироваться от неприятных вещей и перейдем на другие сферы, например, бизнес.
Допустим, вы являетесь менеджерами, которым надо проанализировать, сколько энергии было отпущено за определенный период. Использоваться будут условные значения, которые могут расходиться с действительностью. Просто для демонстрации примера.
Теперь, основываясь на этих данных, выполняем построение графика. Далее добавляем экспоненциальную линию тренда.
2
Достоверность линии тренда в рассматриваемом нами примере составляет 0,938, что говорит, что вероятность ошибки довольно низкая. Следовательно, прогнозам можно доверять.
Логарифмическая линия тренда
Она применяется в ситуациях, когда показатели могут резко меняться. Например, сперва наблюдается стремительный рост, после чего наступает период стабильности. С помощью логарифмической линии тренда можно попытаться предсказать, насколько удачными будут продажи товара, который только появился.
Сперва компании нужно привлекать новых клиентов. Поэтому рост будет стремительный. Дальше опора идет в первую очередь на то, чтобы сделать лояльными к себе тех, кого уже удалось привлечь. Следовательно, точка приложения усилий меняется и соответственно, корректируется прирост клиентской базы.
Давайте сделаем к этому примеру такой график.
3
В этом случае также ошибка аппроксимации минимальная, поэтому полученным сведениям можно доверять. Теперь давайте попробуем предположить, насколько интенсивными будут продажи в будущем. Чтобы это сделать, необходимо подставить номер соответствующего периода в качестве значения переменной x.
Как вариант, возможна следующая таблица с прогнозами.
4
В нашем кейсе для того, чтобы приблизительно понять, как в будущем будет реализовываться продукция, была применена такая формула: =272,14*LN(B 18)+287,21. Где В18 – номер периода.
Полиномиальная линия тренда в Эксель
Эта линия тренда характерна для волатильных (изменчивых) показателей. Очень хорошо его использовать для торговли криптовалютами или другими высоко рисковыми активами.
Для линии тренда этого типа характерно то увеличение, то уменьшение показателей в достаточно широком диапазоне. Его использование возможно в том числе и для обработки огромных массивов информации количественного типа. Особенно эта линия тренда часто используется при наличии большого количества экстремумов на графике (то есть, минимумов и максимумов).
График цены на нефть является наиболее удобным способом демонстрации того, как работает эта модель. Чтобы величина достоверности была близкой к единице, пришлось выставить шестую степень. Но такая линия тренда дает возможность составлять достаточно верные прогнозы.
5
Выводы
Построение линии тренда – это довольно простая процедура. А вот анализ данных с ее использованием – уже задачка посложнее. Но с помощью встроенных средств Excel можно составлять эффективные прогнозы развития самых разных показателей из разных сфер. И хотя использование трендовой линии по большей части автоматизировано в современных программах, иногда может потребоваться использование Excel для этой цели.
Вообще, для составления эффективных прогнозов одной трендовой линии недостаточно. Это не панацея, и не стоит надеяться, что настолько элементарная математическая модель способна творить чудеса. Тем не менее, это один из самых простых элементов статистического анализа. И теперь вы знаете, как его использовать в реальной работе.
Линия тренда в Excel на разных графиках
Для наглядной иллюстрации тенденций изменения цены применяется линия тренда. Элемент технического анализа представляет собой геометрическое изображение средних значений анализируемого показателя.
Рассмотрим, как добавить линию тренда на график в Excel.
Добавление линии тренда на график
Для примера возьмем средние цены на нефть с 2000 года из открытых источников. Данные для анализа внесем в таблицу:
- Построим на основе таблицы график. Выделим диапазон – перейдем на вкладку «Вставка». Из предложенных типов диаграмм выберем простой график. По горизонтали – год, по вертикали – цена.
- Щелкаем правой кнопкой мыши по самому графику. Нажимаем «Добавить линию тренда».
- Открывается окно для настройки параметров линии. Выберем линейный тип и поместим на график величину достоверности аппроксимации.
- На графике появляется косая линия.
Линия тренда в Excel – это график аппроксимирующей функции. Для чего он нужен – для составления прогнозов на основе статистических данных. С этой целью необходимо продлить линию и определить ее значения.
Если R2 = 1, то ошибка аппроксимации равняется нулю. В нашем примере выбор линейной аппроксимации дал низкую достоверность и плохой результат. Прогноз будет неточным.
Внимание. Линию тренда нельзя добавить следующим типам графиков и диаграмм:
- лепестковый;
- круговой;
- поверхностный;
- кольцевой;
- объемный;
- с накоплением.
Уравнение линии тренда в Excel
В предложенном выше примере была выбрана линейная аппроксимация только для иллюстрации алгоритма. Как показала величина достоверности, выбор был не совсем удачным.
Следует выбирать тот тип отображения, который наиболее точно проиллюстрирует тенденцию изменений вводимых пользователем данных. Разберемся с вариантами.
Линейная аппроксимация
Ее геометрическое изображение – прямая. Следовательно, линейная аппроксимация применяется для иллюстрации показателя, который растет или уменьшается с постоянной скоростью.
Рассмотрим условное количество заключенных менеджером контрактов на протяжении 10 месяцев:
На основании данных в таблице Excel построим точечную диаграмму (она поможет проиллюстрировать линейный тип):
Выделяем диаграмму – «добавить линию тренда». В параметрах выбираем линейный тип. Добавляем величину достоверности аппроксимации и уравнение линии тренда в Excel (достаточно просто поставить галочки внизу окна «Параметры»).
Обратите внимание! При линейном типе аппроксимации точки данных расположены максимально близко к прямой. Данный вид использует следующее уравнение:
y = 4,503x + 6,1333
- где 4,503 – показатель наклона;
- 6,1333 – смещения;
- y – последовательность значений,
- х – номер периода.
Прямая линия на графике отображает стабильный рост качества работы менеджера. Величина достоверности аппроксимации равняется 0,9929, что указывает на хорошее совпадение расчетной прямой с исходными данными. Прогнозы должны получиться точными.
Чтобы спрогнозировать количество заключенных контрактов, например, в 11 периоде, нужно подставить в уравнение число 11 вместо х. В ходе расчетов узнаем, что в 11 периоде этот менеджер заключит 55-56 контрактов.
Экспоненциальная линия тренда
Данный тип будет полезен, если вводимые значения меняются с непрерывно возрастающей скоростью. Экспоненциальная аппроксимация не применяется при наличии нулевых или отрицательных характеристик.
Построим экспоненциальную линию тренда в Excel. Возьмем для примера условные значения полезного отпуска электроэнергии в регионе Х:
Строим график. Добавляем экспоненциальную линию.
Уравнение имеет следующий вид:
- где 7,6403 и -0,084 – константы;
- е – основание натурального логарифма.
Показатель величины достоверности аппроксимации составил 0,938 – кривая соответствует данным, ошибка минимальна, прогнозы будут точными.
Логарифмическая линия тренда в Excel
Используется при следующих изменениях показателя: сначала быстрый рост или убывание, потом – относительная стабильность. Оптимизированная кривая хорошо адаптируется к подобному «поведению» величины. Логарифмический тренд подходит для прогнозирования продаж нового товара, который только вводится на рынок.
На начальном этапе задача производителя – увеличение клиентской базы. Когда у товара будет свой покупатель, его нужно удержать, обслужить.
Построим график и добавим логарифмическую линию тренда для прогноза продаж условного продукта:
R2 близок по значению к 1 (0,9633), что указывает на минимальную ошибку аппроксимации. Спрогнозируем объемы продаж в последующие периоды. Для этого нужно в уравнение вместо х подставлять номер периода.
| Период | 14 | 15 | 16 | 17 | 18 | 19 | 20 |
| Прогноз | 1005,4 | 1024,18 | 1041,74 | 1058,24 | 1073,8 | 1088,51 | 1102,47 |
Для расчета прогнозных цифр использовалась формула вида: =272,14*LN(B18)+287,21. Где В18 – номер периода.
Полиномиальная линия тренда в Excel
Данной кривой свойственны переменные возрастание и убывание. Для полиномов (многочленов) определяется степень (по количеству максимальных и минимальных величин). К примеру, один экстремум (минимум и максимум) – это вторая степень, два экстремума – третья степень, три – четвертая.
Полиномиальный тренд в Excel применяется для анализа большого набора данных о нестабильной величине. Посмотрим на примере первого набора значений (цены на нефть).
Чтобы получить такую величину достоверности аппроксимации (0,9256), пришлось поставить 6 степень.
Зато такой тренд позволяет составлять более-менее точные прогнозы.
источники:
http://office-guru.ru/excel/diagrammy-grafika/liniya-trenda-v-excel-na-raznyx-grafikax.html
http://exceltable.com/grafiki/liniya-trenda-v-excel
Большая доля людей легче и проще воспринимают информацию, когда она представлена наглядно. Сделать это самостоятельно можно с помощью презентаций и отчётов с использованием диаграмм и графиков. Это незаменимая составляющая практически любой аналитической деятельности.
При этом с построением диаграмм и графиков в программе Excel может справиться даже новичок. Это не так сложно, как изначально кажется.
Но потребуется освоить некоторые нюансы, разобраться с принципами построения, внимательнее изучить функционал программы.
Графики и диаграммы требуют отдельного рассмотрения.
Построение графиков
Начать стоит с того, как можно построить графики функции и данных в Excel.
График отображает изменения данных. Чаще всего изменения связаны со временем. К примеру, компания получила за определённый временной промежуток ту или иную прибыль. Вариантов много. Но суть везде одинаковая.
Чаще всего осуществляется построение графиков, руководствуясь данными из таблицы программы Excel. Ведь Excel и является главным инструментом для работы с таблицами.
Чтобы создать график, нужно взглянуть на то, какие возможности предлагает Excel, и как ими правильно воспользоваться.
Самый простой график
Поскольку графики отображают изменения тех или иных данных, в них должна быть заложена определённая информация.
Есть исходная информация. И по ней нужно разобраться, как правильно построить график в Excel по этим данным. Он будет отображать события, происходящие на разных временных этапах.
К примеру, каждый год в вуз поступает определённое число абитуриентов. Теперь поэтапно о том, как добавить простейших график, и что вообще для этого предлагает программа Excel.
Здесь последовательность действий следующая:
Подтвердите действие, и дождитесь результата. Синяя горизонтально расположенная линия не нужна. Для её удаления достаточно выделить, и удалить.
Тут будет использоваться только одна кривая. Потому смело удаляйте информацию справа от графика. Она называется «легенда». Для уточнений сделайте подписи к маркерам. Для этого нужно открыть вкладку «Макет», и выбрать «Подписи данных». Здесь выберите место, где будут располагаться цифры.
Ещё больше усовершенствовать график можно через меню «Макет», и раздел «Название осей».
Заголовок перемещайте по своему усмотрению. Можно поменять стиль шрифта, выполнить заливку, изменить размеры. Всё это делается через вкладку «Название диаграммы».
Две и более кривых
Также можно вставить и видоизменить график через программу Excel, в котором будет от 2 и более кривых.
К примеру, нужно показать не только число поступивших в вуз, но ещё и количество выпускников за тот же период. То есть появляется уже больше исходной информации в таблице. Хотя принцип создания графика не меняется. Зато есть смысл не удалять легенду.
Добавить при необходимости дополнительную ось при одинаковых используемых единицах измерения можно по инструкции, которая уже была представлена выше. Если представленные данные разного типа, потребуется вспомогательная ось.
В этом случае рисовать соответствующие графики через приложение Excel нужно по такому алгоритму:
У этого варианта есть альтернативное решение. Другой способ предусматривает, что можно изменить тип используемой диаграммы.
Тут нужно:
Подтвердите изменения, и наслаждайтесь видоизменённым графиком.
Как видите, здесь достаточно выполнить несколько кликов, и дополнительная ось сделана.
График функций
Есть и более сложный вариант, когда есть функция, и по ней нужно нарисовать или начертить соответствующий график.
Действуя пошагово, всю процедуру можно разделить на 2 этапа:
- создание таблицы;
- формирование графика.
Сначала нужно составить таблицу. Есть условно 2 столбца. Это X и Y.
Есть условная формула Y=X(X-2) с шагом 0,5.
В первом столбце будет X. А значение для первой ячейки составит 1. Каждая последующая ячейка на 0,5 больше предыдущей. Нужно вставить формулу = (имя ячейки) + 0,5. Выделите теперь нижний правый угол ячейки, где формула, и тяните вниз. При этом ячейки будут автоматически заполняться значениями.
Второй столбец Y. Тут прописывается расчётная формула, которая используется для формирования графика функции. Поэтому нужно кликнуть на Enter. Программа посчитает все заданные значения. Формулу можно размножить по столбцу, потянув вниз угол ячейки. Всё, таблица есть.
Дальнейший алгоритм такой:
Возможно, на Х оси не будет значений, а только номера на точках. Чтобы исправить эту недоработку, потребуется сделать подписи оси вашего графика. Просто кликните правой кнопкой, и нажмите на меню «Выбрать данные», после чего изменить для горизонтальной оси подписи. Тут выделите нужный диапазон. Теперь график обретает полноценный завершённый внешний вид.
Комбинирование и наложение
Нет ничего сложного в том, чтобы сделать сразу пару графиков в Excel. Для этого требуется на одном поле совместить 2 разных графика функций через приложение для работы с таблицами. Добавьте новую формулу к предыдущей. Допустим, это будет формула Z=X(X-3).
Выделите эти данные и вставьте в поле имеющейся диаграммы. Через соответствующее меню «Выбрать данные» можно отредактировать информацию, если есть какие-то неточности или несовпадения.
Всё, теперь пара графиков функций гармонично оказались в рамках одного поля.
Как сделать график зависимости
В этом случае информация из одной строки или столбца будет зависеть от исходных данных из другого столбца или строки.
Для начала выберите подходящий тип диаграммы. Хорошо смотрится точечный вариант с маркерами и гладкими кривыми. Но выбирать можете то, что вам больше нравится или подходит для конкретной задачи.
Теперь выполняется выбор данных, нажмите на кнопку «Добавить». Здесь можно придерживаться такого принципа:
- имя у ряда будет А;
- значения X будут значениями А;
- для Y значение Е;
- ещё раз нажмите «Добавить»;
- теперь имя ряда будет В, а значение Х это данные из столбца В;
- для Y значениями будут данные из E столбца.
По соответствующему принципу строится вся остальная таблица.
Насколько это легко или сложно, каждый пользователь определяет самостоятельно. Стоит попробовать, попрактиковаться, рассмотреть несколько наглядных примеров. Чем больше опыта и знаний, тем проще работать с графиками через программу Excel.
Построение диаграмм
Помимо графиков, существуют ещё и диаграммы, также упрощающие восприятие той или иной информации. Особенно это касается данных с числовыми значениями. Их можно сравнивать между собой, сопоставлять.
Есть несколько вариантов того, как можно создать диаграммы в Excel. Они могут отображать разные значения в том виде, в котором их удобно воспринимать пользователям. Потому применяются всевозможные варианты. Включая точечные и динамические графические диаграммы в программе для работы с таблицами Excel.
Диаграмма по исходной таблице
Для начала о том, как сделать наглядную диаграмму, используя данные из таблицы Excel.
Здесь нужно выполнить несколько пошаговых процедур:
- для начала создать таблицу, где будут представлены нужные данные, на своё усмотрение;
- далее выделить область со значениями, на основе которой сформируется диаграмма;
- выбрать вкладку «Вставка» и нажать на один из понравившихся типов диаграммы;
- как вариант, выберите гистограмму, а затем один из её вариантов;
- после выбрать автоматически появляется презентация.
Вообще сделать самостоятельно такую диаграмму в Excel, работая по таблице, не сложно. Но автоматически созданная презентация может не устраивать в полной мере. Нужно внести изменения.
Для этого:
Диаграмма полностью редактируется, что позволяет придать ей тот вид, который нужен пользователю. При желании можно сделать круговую диаграмму, точечную, линейчатую и пр. В этом плане программа Excel довольно мощная и функциональная.
Добавление данных в диаграмму
Бывает такая ситуация, когда есть готовая диаграмма, но в неё через программу Excel нужно добавить новые данные.
Это не сложно. Инструкция выглядит следующим образом:
Поскольку новые данные в графике не совсем понятные, тут на помощь приходит легенда. Для этого нужно открыть вкладку «Макет», нажать на меню «Легенда», и здесь добавить её справа, снизу, сверху или использовать иной подходящий вариант.
Легенда в сочетании с разными цветами гистограммы даёт наглядно понять, где какие данные, как они связаны друг с другом. Всё понятно и визуально легко воспринимается.
Хотя есть и альтернативный способ, чтобы добавить данные в уже созданную диаграмму. Он более сложный. Тут используется меню «Выбор источника данных». Чтобы открыть его, нужно кликнуть правой кнопкой мышки, и нажать на «Выбрать данные». Нажимая на кнопку «Добавить», появится строка, через которую можно будет выбрать диапазон данных.
Как оси поменять местами
Бывают и такие ситуации, когда требуется поменять местами разные оси в созданной диаграмме через программу Excel.
Сделать это не сложно. Просто нужно отталкиваться от следующей инструкции:
- выберите диаграмму, и кликните по ней правой кнопкой;
- в контекстном меню нажмите на «Выбрать данные»;
- появится новое меню, где кликните по варианту «Строка/столбец».
Теперь параметры для категорий и рядов автоматически поменяются между собой местами.
Элементы управления
Также Excel позволяет закрепить элементы управления, которые будут располагаться на созданной диаграмме.
Актуально в тех случаях, когда в гистограмму регулярно добавляются новые сведения. И всякий раз менять диапазон не особо удобно.
Самым оптимальным решением в такой ситуации станет динамическая диаграмма. То есть она сможет автоматически обновляться. Чтобы за гистограммой были закреплены элементы управления, нужно преобразовать область данных в так называемую умную таблицу.
Тут от пользователя потребуется сделать следующее:
Так используются готовые данные, преобразуемые в умную таблицу. Либо можно сделать всё с нуля, выбрав меню «Вставка», и далее через раздел «Таблица» создать новые параметры.
Диаграмма в процентах
Некоторая информация легче и удобнее воспринимается, если она представлена не просто в цифрах, а в процентах.
Для таких задач лучше использовать круговой тип диаграммы.
Взяв определённые исходные данные, необходимо:
Если проценты маленькие, эти сектора могут плохо просматриваться. Для их выделения через конструктор нужно поменять тип диаграммы, и выбрать круговую со вторичной.
Когда процентная диаграмма создаётся автоматически, зачастую она в полной мере не устраивает пользователя. Потому тут можно кликнуть по любому из секторов правой кнопкой, после чего появляются точки-границы. Через контекстное меню выберите «Формат ряда данных». Теперь для ряда задаются нужные параметры, и изменения сохраняются.
Диаграмма Гфуанта
Отдельного внимания заслуживает диаграмма, с помощью которой можно отобразить в виде столбцов данные многоэтапного события. Красиво и совсем не сложно.
Допустим, что диаграмма для учителя, который следит за сроками сдачи отчётов. Сделать нужно следующее:
Проверяйте, устраивает ли вас результат в полной мере.
Выполнить такую задачу не сложно. Но свои нюансы в работе с графиками и диаграммами через программу Excel есть.
Приходилось ли вам работать с графиком или диаграммой? Для чего они нужны были? Какие трудности возникли? Что можете посоветовать новичкам в этом деле?
Делитесь опытом и оставляйте свои комментарии. Подписывайтесь, задавайте вопросы и рассказывайте о нашем проекте своим друзьям!
Содержание
- Построение диаграммы в Excel
- Вариант 1: Построение диаграммы по таблице
- Работа с диаграммами
- Вариант 2: Отображение диаграммы в процентах
- Вариант 3: Построение диаграммы Парето
- Вопросы и ответы
Microsoft Excel дает возможность не только удобно работать с числовыми данными, но и предоставляет инструменты для построения диаграмм на основе вводимых параметров. Их визуальное отображение может быть совершенно разным и зависит от решения пользователя. Давайте разберемся, как с помощью этой программы нарисовать различные типы диаграмм.
Построение диаграммы в Excel
Поскольку через Эксель можно гибко обрабатывать числовые данные и другую информацию, инструмент построения диаграмм здесь также работает в разных направлениях. В этом редакторе есть как стандартные виды диаграмм, опирающиеся на стандартные данные, так и возможность создать объект для демонстрации процентных соотношений или даже наглядно отображающий закон Парето. Далее мы поговорим о разных методах создания этих объектов.
Вариант 1: Построение диаграммы по таблице
Построение различных видов диаграмм практически ничем не отличается, только на определенном этапе нужно выбрать соответствующий тип визуализации.
- Перед тем как приступить к созданию любой диаграммы, необходимо построить таблицу с данными, на основе которой она будет строиться. Затем переходим на вкладку «Вставка» и выделяем область таблицы, которая будет выражена в диаграмме.
- На ленте на вкладе «Вставка» выбираем один из шести основных типов:
- Гистограмма;
- График;
- Круговая;
- Линейчатая;
- С областями;
- Точечная.
- Кроме того, нажав на кнопку «Другие», можно остановиться и на одном из менее распространенных типов: биржевой, поверхности, кольцевой, пузырьковой, лепестковой.
- После этого, кликая по любому из типов диаграмм, появляется возможность выбрать конкретный подвид. Например, для гистограммы или столбчатой диаграммы такими подвидами будут следующие элементы: обычная гистограмма, объемная, цилиндрическая, коническая, пирамидальная.
- После выбора конкретного подвида автоматически формируется диаграмма. Например, обычная гистограмма будет выглядеть, как показано на скриншоте ниже:
- Диаграмма в виде графика будет следующей:
- Вариант с областями примет такой вид:
Работа с диаграммами
После того как объект был создан, в новой вкладке «Работа с диаграммами» становятся доступными дополнительные инструменты для редактирования и изменения.
- Доступно изменение типа, стиля и многих других параметров.
- Вкладка «Работа с диаграммами» имеет три дополнительные вложенные вкладки: «Конструктор», «Макет» и «Формат», используя которые, вы сможете подстроить ее отображение так, как это будет необходимо. Например, чтобы назвать диаграмму, открываем вкладку «Макет» и выбираем один из вариантов расположения наименования: по центру или сверху.
- После того как это было сделано, появляется стандартная надпись «Название диаграммы». Изменяем её на любую надпись, подходящую по контексту данной таблице.
- Название осей диаграммы подписываются точно по такому же принципу, но для этого надо нажать кнопку «Названия осей».
Вариант 2: Отображение диаграммы в процентах
Чтобы отобразить процентное соотношение различных показателей, лучше всего построить круговую диаграмму.
- Аналогично тому, как мы делали выше, строим таблицу, а затем выделяем диапазон данных. Далее переходим на вкладку «Вставка», на ленте указываем круговую диаграмму и в появившемся списке кликаем на любой тип.
- Программа самостоятельно переводит нас в одну из вкладок для работы с этим объектом – «Конструктор». Выбираем среди макетов в ленте любой, в котором присутствует символ процентов.
- Круговая диаграмма с отображением данных в процентах готова.
Вариант 3: Построение диаграммы Парето
Согласно теории Вильфредо Парето, 20% наиболее эффективных действий приносят 80% от общего результата. Соответственно, оставшиеся 80% от общей совокупности действий, которые являются малоэффективными, приносят только 20% результата. Построение диаграммы Парето как раз призвано вычислить наиболее эффективные действия, которые дают максимальную отдачу. Сделаем это при помощи Microsoft Excel.
- Наиболее удобно строить данный объект в виде гистограммы, о которой мы уже говорили выше.
- Приведем пример: в таблице представлен список продуктов питания. В одной колонке вписана закупочная стоимость всего объема конкретного вида продукции на оптовом складе, а во второй – прибыль от ее реализации. Нам предстоит определить, какие товары дают наибольшую «отдачу» при продаже.
Прежде всего строим обычную гистограмму: заходим на вкладку «Вставка», выделяем всю область значений таблицы, жмем кнопку «Гистограмма» и выбираем нужный тип.
- Как видим, вследствие осуществленных действий образовалась диаграмма с двумя видами столбцов: синим и красным. Теперь нам следует преобразовать красные столбцы в график — выделяем эти столбцы курсором и на вкладке «Конструктор» кликаем по кнопке «Изменить тип диаграммы».
- Открывается окно изменения типа. Переходим в раздел «График» и указываем подходящий для наших целей тип.
- Итак, диаграмма Парето построена. Сейчас можно редактировать ее элементы (название объекта и осей, стили, и т.д.) так же, как это было описано на примере столбчатой диаграммы.
Как видим, Excel представляет множество функций для построения и редактирования различных типов диаграмм — пользователю остается определиться, какой именно ее тип и формат необходим для визуального восприятия.
Еще статьи по данной теме: