Как изменить значения на рабочем листе, используя диаграмму

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

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

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

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

3. С помощью мыши перетащите верхний маркер точки данных в нужное положение. По мере перемещения маркера всплывающая подсказка будет отражать меняющееся числовое значение маркера соответствующее его новым положениям. На оси значений (Y) скользящий маркер будет также показывать изменяющееся значение. При необходимости маркер можно вытащить за пределы верхнего конца оси значений (Y).

4. Когда маркер займет нужное положение, отпустите кнопку мыши.

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

1. В текстовом поле Изменяя значение ячейки с помощью кнопки свертывания окна, расположенной на правом конце этого поля укажите ячейку (либо введите ссылку на нее), значение в которой следует изменить для получения нужного результата на диаграмме. Эта ячейка должна содержать в себе формулу.

2. Нажмите кнопку ОК.

Средство подбора параметра осуществляет пошаговое вычисление значения и выводи на экран решение.

3. В появившемся диалоговом окне с результатами подбора параметра нажмите кнопку ОК для ввода нового входного значения или кнопку Отмена для возврата к первоначальному варианту рабочего листа.

Когда команда Подбор параметра будет выполнена. Excel снова активизирует диаграмму.

 

 

Использование линий тренда и планок погрешностей
на диаграммах

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

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

 

Линии тренда

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

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

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

Чтобы добавить линию тренда к ряду данных:

1. Активируйте диаграмму, данные которой нужно проанализировать.

2. Выполните команду Диаграмма, Добавить линию тренда или переместите указатель на ряд данных и щелкните правой кноп мыши, а затем в контекстном меню выберите команду Добавить линию тренда. В появившемся диалоговом окне Линия тренда раскройте вкладку Тип.

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

4. В группе Построение линии тренда (аппроксимации и сглаживание) выберите один из шести типов аппроксимации (сглаживания). В табл. 1 даны описания каждого типа аппроксимации и соответствующие им уравнения.

Таблица 1

Типы аппроксимации

 

Тип	Описание
Линейная	Аппроксимирующая прямая: y = mx + b, где m - тангенс угла наклона, а b - точка пересечения прямой с осью Y
Логарифмическая	Логарифмическая аппроксимация: у = clnx + b, где с и b - константы, a ln - натуральный логарифм
Полиномиальная	Полиномиальная аппроксимация: y = b + с1х1 + с2x2 +... + c6x6, где b и ci — константы, В текстовом поле Степень укажите нужную степень полинома. Максимальная степень полинома – 6
Степенная	Степенная аппроксимация: y = сxb, где с и b - константы
Экспоненциальная	Экспоненциальная аппроксимация: у = сеbx где с и b - константы, а е - основание натуральных логарифмов
Скользящее среднее	Скользящее среднее. Каждая точка данных на линии тренда строится на основании среднего значения указанного числа точек данных (периодов). Чем больше число периодов пользуемых для вычисления скользящего среднего, тем более гладкой, но менее точной становится линия тренда. Для вычисления скользящего среднего используется уравнение: Ft = (At+At-1 + … +Аt-n+1)/n

 

5. Чтобы установить параметры линии тренда раскройте вкладку Параметры диалогового окна Линия тренда. Описание этих параметров приведено в табл 2.

Таблица 2

Параметры линий тренда

 

Параметр	Описание
Группа – Название аппроксимирующей (сглаженной) кривой
Автоматической	Используется для линии тренда название ряда данных
Другое	Используется для линии тренда название, введенное в текстовое поле другое
Группа – Прогноз
Вперед на	Прогнозируются данные вперед с помощью линии тренда на число периодов, указанное в текстовом поле
Назад на	Определяется история данных назад с помощью линии тренда на число периодов, указанное к текстовом поле
Другие параметры
Пересечение кривой с осью Y в точке	По умолчанию пересечение с осью Y вычисляется на основе данных, но можно задать другое значение
Показывать уравнение на диаграмме	Осуществляется вывод уравнения аппроксимации на диаграмму в виде несвязанного текстового поля
Поместить на диаграмму величину достоверности аппроксимации	Осуществляется вывод на диаграмму достоверность аппроксимации в виде несвязанного текстового поля

 

Планки погрешностей

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

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

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

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

1. Активизируйте диаграмму, данные которой нужно проанализировать.

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

3. Выполните команду Формат, Выделенный ряд или переместите указатель на ряд данных и щелкните правой кнопкой, а затем в контекстном меню выберите команду Формат рядов данных, а в появившемся диалоговом окне Формат рад данных раскройте вкладку Y-погрешности.

При работе с точечной диаграммой в диалоговом окне Формат ряда данных имеется еще и вкладка Х-погрешности.

4. В группе Показать планки погрешностей по Y выберите нужны тип планок погрешностей.

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

 

Таблица 3