Как по графику в экселе определить значение точки

Примеры использования функции ОТРЕЗОК в Excel на оси ординат

Функция ОТРЕЗОК в Excel используется для прогнозирования некоторого события. Она находит координаты точки пересечения графика с осью ординат (OY), построенного по координатам точек X (независимая переменная) и Y (зависимая переменная).

Поиск зависимости значений оси ординат по функции ОТРЕЗОК в Excel

Пример 1. Два массива данных характеризуют показатели одной величины (Y) относительной другой (X). Предположить, каким будет значение величины Y, если X примет значение 0 (нуль).

Таблица исходных данных:

Для нахождения координаты пересечения с осью Ординат введем следующую формулу:

1. B2:B10 – диапазон ячеек, со значениями для изменяемого параметра Y;
2. A2:A10 – диапазон ячеек, с исходными значениями неизменяемых величин X.

В результате получим:

То есть, координата Y точки пересечения графика с осью Ординат равна примерно 3,29.

Расчет потребления энергии отопления в Excel в зависимости от температуры

Пример 2. Потребление энергии отопительного прибора зависит от температуры окружающей среды. Известны значения энергопотребления при плюсовой температуре окружающей среды. Определить, сколько энергии будет потреблять котел, если температура на улице снизится до 0 °C и показать это на графике.

Запишем данные в таблицу:

Определим значение энергопотребления при 0 °C:

• B2:B6 – массив значений энергопотребления для определенных температур;
• A2:A6 – массив значений температур, для которых была определена характеристика энергопотребления.

Построим график на основе известных значений:

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

Прогноз затрат с учетом посещаемости и потребления в Excel

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

Запишем данные в таблицу:

Для расчетов используем метод линейной регрессии. То есть, нам необходимо получить уравнение типа y=kx+b, где:

• y – количество учеников (посещаемость);
• x – номер дня, в который учитывалась посещаемость;
• b – некоторый свободный член уравнения;
• k – угловой коэффициент регрессии.

Для решения поставленной задачи запишем следующую формулу:

Описание логики формулы:

1. Запись НАКЛОН(B3:B7;A3:A7)*A8+ОТРЕЗОК(B3:B7;A3:A7) соответствует записи kx+b, где угловой коэффициент регрессии вычисляется с помощью функции НАКЛОН, а свободный член b – с использованием функции ОТРЕЗОК.
2. Обе функции принимают массивы значений посещаемости (Y) и номеров дней (X).
3. Значение Y (посещаемость) округляется до целого значения функцией ОКРУГЛ, поскольку посещаемость должна быть целым числом.

Аналогично вычисляем посещаемость для следующего дня. В результате получим:

Примечание: представленный в Примере 3 способ не применима для моделирования процессов с высокой точностью, поскольку функция ОТРЕЗОК позволяет создавать линии тренда только линейного типа. Наибольшая точность достигается при изучении процесса с пропорциональным приростом/уменьшением величин.

Как по графику в экселе определить значение точки

Интересует вопрос как в Ексель узнать значение функции в промежуточных точках.

Я построил график в Ексель по экспериментальным данным, например — в момент времени 1 величина принимает значение y1, а в момент времени 2 величина принимает значение y2 (y1 и y2 получены экспериментально). А мне нужно узнать чему равна величина в момент времени 1.5 — хочу определить это по графику, но не на глаз, а точно. В Екселе это можно сделать?

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

Я построил график в Ексель по экспериментальным данным

Хороши экспериментальные данные из двух измерений. 🙂

С чего выдумал, что у меня два измерения?
Читайте внимательнее.

Не я написал, что
нельзя по двум точкам построить функцию

И прокомментировал я, как несложно догадаться, ответ Andriy,

Rendido, прокомментировали Вы именно мой пост, якобы у меня всего два измерения. Прочитайте внимательно свой же пост #7. Вы писали "Хороши экспериментальные данные из двух измерений". Это было про меня, не оправдывайтесь.

А сейчас Вы ответите в эту тему ещё много сообщений, "я этого не писал", "это не сложно догадаться", "меня не так поняли", "читайте внимательно, это не я писал", "Lexander иди учи Ексель" и так далее, и тому подобное, и прочее, и тема перерастёт во флуд и оскорбления. До свидания.

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

Well, OK:
Lexander иди учи Ексель
читайте внимательно
До свидания

Lexander, все-таки, настаиваю на том, что Вам нужно осилить, хотя бы, интерполяцию, а лучше аппроксимацию. Так как вопрос у Вас из серии: "как, имея три рубля, получить шесть?"
Вкраце: узнать значения функции в промежуточных точках по таблично заданной функции НЕЛЬЗЯ. Можно узнать значения ДРУГИХ функций, которые будут проходить через заданные точки (интерполяция) или проходить очень близко к заданным точкам (аппроксимация — на практике предпочтительней).
То есть Вы заменяете неизвестную Вам функцию (о которой известно лишь конкретные ее точки — результаты эксперимента) на другую функцию, и уже берете значения этой другой функции в промежуточных точках. Через заданные точки (сколько бы их ни было) можно провести сколько угодно функций. Именно поэтому Ваш вопрос изначально некорректен. Ексель воспользуется одним методом интерполяции, матлаб другим — значение функции в промежуточных точках будет разным.
Более того, лучше воспользоваться не интерполяцией (когда нужно построить функцию, строго проходяющую через заданные точки), а аппроксимацией. Так как если исходные точки получены экспериментально, то в них обязательно будет погрешность, поэтому проходить строго через них не нужно — это лишь увеличит общую разницу между полученной функцией и неизвестной реальной.

Ему всего-навсего нужно знать закон, по которому ексель (ред. авт. сохр.) строит график. И все.

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

1. Автор хочет знать Y в промежуточной точке ГРАФИКА — читаем исходный пост.

2. Последняя фраза, особенно "или" — впечатлила. Мда.

При построении графика Эксель тупо соединяет каждые две соседние точки прямой.

Ой. Значит, мне показалось, что при выборе нестандартных типов диаграмм есть "гладкие графики". А там как тупо? Сплайнами? Кубическими?

Вляпалась. да какими сплайнами? Просто прямую строит по двум точкам, да и то, чтобы только соединить эти точки (подозреваю, что только в процессе вывода на экран "графика"). Нигде она (прямая) в "аналитическом" виде в системе "не оседает". Так что узнать там значения не представляется возможным, если искусственно эту прямую не организовать. Но смысла в этом нет никакого — лучше нормальную интерполяцию или аппроксимацию.

Добавлено через 6 минут
Hogfather, Посмотрите выше я приводил ссылку на библиотеку на php. Если excel соединяет точки, причем, ЧСХ, гладко, значит в нем какая-то математика для этого зашита
Где в ссылке про php говорится, что Excel плавно точки соединяет?

gav, Ви таки пойдите на привоз, купите селедку и морочьте ей голову. Ну не знаете Excel, так и скажите

Где в ссылке про php говорится, что Excel плавно точки соединяет?
Нигде. Там рассказывается на пальцах, как соединяют точки.
А плавно Excel соединяет точки по определению. Вот пример, график одного и того же (два способа построения графика).

Ребята, у графических тулбоксов цель — строить графики. Красивые, симпатишные, с прибамбасами и т.п. Но мне кажется несколько самонадеянным считать, что графические тулбоксы снабжены еще сложными математическими методами, которые прежде, чем построить график решают еще поставленные математические задачи (причем, мы конкретную математическую задачу им и не ставили, вообще говоря). Поэтому графические тулбоксы отдельно, математические методы обработки данных отдельно. Это мое мнение.

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

Добавлено через 2 минуты
А плавно Excel соединяет точки по определению.
То есть я так понимаю, что Ексель делает интерполяцию перед построением графика? Возможно я чего-то не понимаю.

Hogfather, так там поди второй график как сплайн выбран?

Добавлено через 1 минуту
Uzanka, да, есть там в типе графиков "плавная кривая". Но опять же что за метод ее построения — загадка.
В любом случае, это просчитывается на этапе вывода графика на экран. И нигде в OLE не фигурирует..

gav, вот и я о том. Конкретная математическая задача нами не была еще поставлена, а Ексель уже за нас всё решил ))

Вляпалась.
Hogfather,
Ребята, я посмотрела сейчас, построила графики в Ексель. Должна признать, что не права. При выборе опции "Гладкая" там однозначно работает какой-то алгоритм. Вопрос только какой метод стоит там по умолчанию.

Чё делается .
Да, пионэрам надо бы хоть чуть-чуть знать матчасть. "Апроксимация" посредством экселя — дикое убожество, конечно. Особенно, если не понимать как этот магический продукт работает. И не знать основ математики.

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

И нигде в OLE не фигурирует..
Причем тут OLE-то ?

Добавьте к графику линию тренда. Выберите по величине R^2(?) тип, наиболее точно соответствующий графику (линейный, степенной, экспоненциальный) и в уравнение этого тренда подставляйте значение абсциссы.
Графически это выглядит как
http://rghost.ru/40891857/image.png
дикое убожество, конечно
😀

График 1 — исходная функция (уравнение которой написано под графиком)

График 2 — сглаженная функция.

График 3 — аппроксимация средствами Excel, (судя по величине R^2 — убогая 🙂 ).

В Эксель имеется таблица вида:

Фактор 1 — 2
Фактор 2 — 3
Фактор 3 — 67
.
Фактор 25 — 32

Не могу сообразить: как отсортировать чтобы стало так:

Фактор 25 — 32
Фактор .
Фактор 3 — 67
Фактор 2 — 3
Фактор 1 — 2

Снова вопрос. Дано следующее:

1). В таблице Excel перечислены различные факторы, которые по-разному влияют на продуктивность кроликов.
2). Предстоит оценить силу влияния каждого фактора (см. фиолетовую строку).
3). Вариантов ответа — пять:
— не влияет — столбец под цифрой "1"
— слабо влияет — под цифрой 2
— умеренно влияет — под цифрой 3
— сильно влияет — под цифрой 4
— очень сильно влияет — под цифрой 5.
См. фиолетовую строку.
4). Ответы каждого респондента забивались в горизонтальные строки.
5). Ответы кодировались по двоичному принципу: если мы считаем, что данный фактор не влияет (см. респонденты №1, №2 и №3), то в колонку 1 забиваем "1", а в остальные колонку — нули. А вот респондент №4 посчитал, что первый фактор "сильно влияет", поэтому ставим единицу в колонку 4, а в колонки 1,2,3 и 5 забиваем нули.

Как сделать так, чтобы:

Если единица стоит в первой колонке ("фактор не влияет"), то в желтом вертикальном столбце отобразился 0 ?
Если единица стоит во второй колонке ("фактор слабо влияет"), то в желтом вертикальном столбце отобразилась цифра 4 ?
Если единица стоит в третьей колонке ("фактор умеренно влияет"), то в желтом вертикальном столбце доотобразилась цифра 6 ?
Если единица стоит в четвертой колонке ("фактор сильно влияет"), то в желтом вертикальном столбце доотобразилась цифра 8 ?
Если единица стоит в пятой колонке ("фактор чрезвычайно сильно влияет"), то в желтом вертикальном столбце дотобразилась цифра 10 ?

Эти цифры (0,4,6,8 и 10), которые должны появиться в желтом вертикальном столбце, представляют силу влияния данного фактора по условной десятибалльной шкале. Ну а дальше все просто — лепестковая диаграмма и полная красота.

Димитриадис, картинка мягко говоря не очень соответствует описанию из #34. 🙂
Надо блоки местами поменять что ли, то есть "фактор 1" это колонки с AP до AU и так далее?
Если это будет один раз делаться, проще выделить по очереди каждый нужный блок и перетащить мышкой на нужное место.

Неактуально. Предыдущий пост был изменён после того, как я написал этот.

Jacky, извините, то был вчерашний вопрос, там все просто, Дядюшка подсказал.
А картинка относится к сегодняшнему вопросу. 😮

Я думаю, что это должно делаться через "Логические функции" Excel-я, в частности "Если" или "Если, То"
но что-то у меня не получается.

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

Описание: У меня есть 6 значений на оси абсцисс и 6 соответствующих значений на оси ординат. Я беру миллиметровку, карандаш и линейку и строю график, тупо соединяя точки. Затем у меня появляется известное значение на одной из осей( не важно на какой) и мне нужно найти соответствующее значение на другой оси. Я определяю это по бумаге линейкой.
Вопрос: Есть ли программа, которая упростит мою жизнь, избавя от ежедневного черчения на миллиметровке? Чтобы она просто выдавала значения, допустим абсциссы при известном значении ординаты на куске графика, который является прямой. Или формула в екселе или что угодно другое, лишь бы не чертить. а то глаза выпадут скоро.

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

Описание: У меня есть 6 значений на оси абсцисс и 6 соответствующих значений на оси ординат. Я беру миллиметровку, карандаш и линейку и строю график, тупо соединяя точки. Затем у меня появляется известное значение на одной из осей( не важно на какой) и мне нужно найти соответствующее значение на другой оси. Я определяю это по бумаге линейкой.
Вопрос: Есть ли программа, которая упростит мою жизнь, избавя от ежедневного черчения на миллиметровке? Чтобы она просто выдавала значения, допустим абсциссы при известном значении ординаты на куске графика, который является прямой. Или формула в екселе или что угодно другое, лишь бы не чертить. а то глаза выпадут скоро.

Ну, кусочно-линейная функция, в вашем случае n=4

Дальше вычисления примерно на уровне математики начальной школы (см. задачу 2) (http://ege-ok.ru/2012/04/03/lineynaya-funktsiya-i-ee-grafik/)

Делаете таблицу в Excel

X Y K B
1 10 =C3 =D3
2 20 =(B3-B2)/(A3-A2) =B3-C3*A3
3 -9 =(B4-B3)/(A4-A3) =B4-C4*A4
4 10 =(B5-B4)/(A5-A4) =B5-C5*A5
5 11 =(B6-B5)/(A6-A5) =B6-C6*A6
6 11 =(B7-B6)/(A7-A6) =B7-C7*A7

Дальше считаете по коэффициентам:
=1,1*ВПР(1,1;A2: D7;3)+ВПР(1,1;A2: D7;4)

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

Добавлено через 2 минуты
Сделать все то же самое, но в Экселе. Включив отображение основных и промежуточных линий сетки, получите ту же "миллиметровку", но в электронном виде
:facepalm:

Как найти точку данных в точечной диаграмме Excel

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

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

Что-то вроде того, что показано ниже:

Этот метод позволяет мгновенно определить позицию компании на точечной диаграмме.

Точечная точка данных в точечной диаграмме Excel

1. Перейдите в Вставка -> Диаграммы -> Точечная диаграмма.

1. Щелкните пустую диаграмму и перейдите в Дизайн -> Выбрать данные.

1. В диалоговом окне «Выбор источника данных» нажмите «Добавить».

1. В диалоговом окне «Редактировать серию» выберите диапазон для оси X и оси Y.
2. Щелкните ОК.

Это создаст для вас простую диаграмму разброса. А теперь самое интересное — создание маркера для определения выбранной вами компании. Это состоит из 3 частей:

2.1 — Создание раскрывающегося списка с названиями компаний (точек данных)

1. Перейдите в ячейку, в которой вы хотите создать раскрывающийся список.
2. Перейдите в Data -> Data Validation.
3. В диалоговом окне «Проверка данных» выберите «Список» (в качестве критерия проверки), выберите весь диапазон, содержащий названия компаний (в данном случае список находится в B3: B22), и нажмите «ОК».

2.2 — Извлечение значений для выбранной компании

1. Выберите ячейку и укажите ее в раскрывающемся списке. Например, в этом случае раскрывающийся список находится в F3, а в B25 у меня есть формула = F3.
   • Ячейка B25 будет меняться всякий раз, когда я изменяю раскрывающийся список.
2. В ячейке C25 используйте формулу ВПР, чтобы извлечь значение дохода (ось X) для компании в ячейке B25:
   = ВПР (B25; $ B $ 3: $ D $ 22,2,0)
3. В ячейке D25 используйте формулу ВПР, чтобы извлечь значение прибыли (ось Y) для компании в ячейке B25:
   = ВПР (B25; $ B $ 3: $ D $ 22,3,0)

2.3 — Создание корректировщика

1. Выберите уже созданную точечную диаграмму.
2. Перейдите в Дизайн -> Выбрать данные.
3. В диалоговом окне «Выбор источника данных» нажмите «Добавить».
4. Выберите ячейку C25 в качестве значения оси x.
5. Выберите ячейку D25 в качестве значения оси Y.
6. Точка данных будет иметь цвет и форму, отличные от других точек данных. Выберите эту точку данных, щелкните правой кнопкой мыши и выберите «Форматировать ряд данных».

1. В диалоговом окне «Форматирование ряда данных»
   • Выберите параметр «Маркер» -> «Встроенный» -> «Тип» (выберите круглую форму и увеличьте размер до 11).
   • Заливка маркера -> Без заливки.
   • Цвет линии маркера -> Сплошная линия (красный или любой другой цвет).
   • Стиль линии маркера -> Ширина (сделайте 1 или выше).

Вот и все. У тебя все готово. Теперь вы можете выбрать компанию, и она будет обведена и выделена. Круто, не правда ли?

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

Как по графику в экселе определить значение точки

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

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

Что-то вроде того, что показано ниже:

Этот метод позволяет мгновенно определить позицию компании на точечной диаграмме.

Точечная точка данных в точечной диаграмме Excel

1. Перейдите в Вставка -> Диаграммы -> Точечная диаграмма.

1. Щелкните пустую диаграмму и перейдите в Дизайн -> Выбрать данные.

1. В диалоговом окне «Выбор источника данных» нажмите «Добавить».

1. В диалоговом окне «Редактировать серию» выберите диапазон для оси X и оси Y.
2. Щелкните ОК.

Это создаст для вас простую диаграмму разброса. А теперь самое интересное — создание маркера для определения выбранной вами компании. Это состоит из 3 частей:

2.1 — Создание раскрывающегося списка с названиями компаний (точек данных)

1. Перейдите в ячейку, в которой вы хотите создать раскрывающийся список.
2. Перейдите в Data -> Data Validation.
3. В диалоговом окне «Проверка данных» выберите «Список» (в качестве критерия проверки), выберите весь диапазон, содержащий названия компаний (в данном случае список находится в B3: B22), и нажмите «ОК».

2.2 — Извлечение значений для выбранной компании

1. Выберите ячейку и укажите ее в раскрывающемся списке. Например, в этом случае раскрывающийся список находится в F3, а в B25 у меня есть формула = F3.
   • Ячейка B25 будет меняться всякий раз, когда я изменяю раскрывающийся список.
2. В ячейке C25 используйте формулу ВПР, чтобы извлечь значение дохода (ось X) для компании в ячейке B25:
   = ВПР (B25; $ B $ 3: $ D $ 22,2,0)
3. В ячейке D25 используйте формулу ВПР, чтобы извлечь значение прибыли (ось Y) для компании в ячейке B25:
   = ВПР (B25; $ B $ 3: $ D $ 22,3,0)

2.3 — Создание корректировщика

1. Выберите уже созданную точечную диаграмму.
2. Перейдите в Дизайн -> Выбрать данные.
3. В диалоговом окне «Выбор источника данных» нажмите «Добавить».
4. Выберите ячейку C25 в качестве значения оси x.
5. Выберите ячейку D25 в качестве значения оси Y.
6. Точка данных будет иметь цвет и форму, отличные от других точек данных. Выберите эту точку данных, щелкните правой кнопкой мыши и выберите «Форматировать ряд данных».

1. В диалоговом окне «Форматирование ряда данных»
   • Выберите параметр «Маркер» -> «Встроенный» -> «Тип» (выберите круглую форму и увеличьте размер до 11).
   • Заливка маркера -> Без заливки.
   • Цвет линии маркера -> Сплошная линия (красный или любой другой цвет).
   • Стиль линии маркера -> Ширина (сделайте 1 или выше).

Вот и все. У тебя все готово. Теперь вы можете выбрать компанию, и она будет обведена и выделена. Круто, не правда ли?

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

Точки пересечения графиков в Excel

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

Строим графики с точками пересечений

Имеются две функции, по которым нужно построить графики:

Выделяем диапазоны данных, на вкладке «Вставка» в группе «Диаграммы» подбираем нужный тип графика. Как:

1. Нужно найти точки пересечения графиков со значением Х, поэтому столбчатые, круговые, пузырьковые и т.п. диаграммы не выбираем. Это должны быть прямые линии.
2. Для поиска точек пересечения необходима ось Х. Не условная, на которой невозможно задать другое значение. Должна быть возможность выбирать промежуточные линии между периодами. Обычные графики не подходят. У них горизонтальная ось – общая для всех рядов. Периоды фиксированы. И манипулировать можно только с ними. Выберем точечную диаграмму с прямыми отрезками и маркерами.

Для данного типа диаграммы между основными периодами 0, 2, 4, 6 и т.д. можно использовать и промежуточные. Например, 2,5.

Находим точку пересечения графиков в Excel

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

Первый способ. Найти общие значения в рядах данных для указанных функций.

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

Значения Y одинаковые при Х = 4. Следовательно, точка пересечения двух графиков имеет координаты 4, 5.

Изменим график, добавив новые данные. Получим две пересекающиеся линии.

Второй способ. Применение для решения уравнений специального инструмента «Поиск решения». Кнопка вызова инструмента должна быть на вкладке «Данные». Если нет, нужно добавить из «Надстроек Excel».

Преобразуем уравнения таким образом, чтобы неизвестные были в одной части: y – 1,5 х = -1; y – х = 1. Далее для неизвестных х и y назначим ячейки в Excel. Перепишем уравнения, используя ссылки на эти ячейки.

Вызываем меню «Поиск решения» — заполняем условия, необходимые для решения уравнений.

Нажимаем «Выполнить» — инструмент предлагает решение уравнений.

Найденные значения для х и y совпадают с предыдущим решением с помощью составления рядов данных.

Точки пересечения для трех показателей

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

По условию задачи показатель В имеет постоянную величину на протяжении всех периодов. Это некий норматив. Показатель А зависит от показателя С. Он то выше, то ниже норматива. Строим графики (точечную диаграмму с прямыми отрезками и маркерами).

Точки пересечения имеются только у показателей А и В. Но их точные координаты нужно еще определить. Усложним задачу – найдем точки пересечения показателя C с показателями А и В. То есть в какие временные периоды и при каких значениях показателя А линия показателя С пересекает линию норматива.

Точек у нас будет две. Их рассчитаем математическим путем. Сначала найдем точки пересечения показателя А с показателем В:

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

Теперь рассчитаем точки, найденных значений по оси Х с показателем С. Используем близкие формулы:

На основе новых данных построим точечные диаграммы на том же поле (где наши графики).

Получается такой рисунок:

Для большей информативности и эстетики восприятия добавим пунктирные линии. Их координаты:

Добавим подписи данных – значения показателя C, при которых он пересечет линию норматива.

Можно форматировать графики по своему усмотрению – делать их более выразительными и наглядными.

Как определить функцию по графику в excel

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

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

Простейший график изменений

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

Допустим, у нас есть данные по чистой прибыли предприятия за 5 лет:

Год	Чистая прибыль*
2010	13742
2011	11786
2012	6045
2013	7234
2014	15605

* Цифры условные, для учебных целей.

Заходим во вкладку «Вставка». Предлагается несколько типов диаграмм:

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

Выбрали – скопировали таблицу с данными – вставили в область диаграммы. Получается вот такой вариант:

Прямая горизонтальная (синяя) не нужна. Просто выделяем ее и удаляем. Так как у нас одна кривая – легенду (справа от графика) тоже убираем. Чтобы уточнить информацию, подписываем маркеры. На вкладке «Подписи данных» определяем местоположение цифр. В примере – справа.

Улучшим изображение – подпишем оси. «Макет» – «Название осей» – «Название основной горизонтальной (вертикальной) оси»:

Заголовок можно убрать, переместить в область графика, над ним. Изменить стиль, сделать заливку и т.д. Все манипуляции – на вкладке «Название диаграммы».

Вместо порядкового номера отчетного года нам нужен именно год. Выделяем значения горизонтальной оси. Правой кнопкой мыши – «Выбрать данные» — «Изменить подписи горизонтальной оси». В открывшейся вкладке выбрать диапазон. В таблице с данными – первый столбец. Как показано ниже на рисунке:

Можем оставить график в таком виде. А можем сделать заливку, поменять шрифт, переместить диаграмму на другой лист («Конструктор» — «Переместить диаграмму»).

График с двумя и более кривыми

Допустим, нам нужно показать не только чистую прибыль, но и стоимость активов. Данных стало больше:

Но принцип построения остался прежним. Только теперь есть смысл оставить легенду. Так как у нас 2 кривые.

Добавление второй оси

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

Сначала строим график так, будто у нас одинаковые единицы измерения.

Выделяем ось, для которой хотим добавить вспомогательную. Правая кнопка мыши – «Формат ряда данных» – «Параметры ряда» — «По вспомогательной оси».

Нажимаем «Закрыть» — на графике появилась вторая ось, которая «подстроилась» под данные кривой.

Это один из способов. Есть и другой – изменение типа диаграммы.

Щелкаем правой кнопкой мыши по линии, для которой нужна дополнительная ось. Выбираем «Изменить тип диаграммы для ряда».

Определяемся с видом для второго ряда данных. В примере – линейчатая диаграмма.

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

Строим график функций в Excel

Вся работа состоит из двух этапов:

1. Создание таблицы с данными.
2. Построение графика.

Пример: y=x(√x – 2). Шаг – 0,3.

Составляем таблицу. Первый столбец – значения Х. Используем формулы. Значение первой ячейки – 1. Второй: = (имя первой ячейки) + 0,3. Выделяем правый нижний угол ячейки с формулой – тянем вниз столько, сколько нужно.

В столбце У прописываем формулу для расчета функции. В нашем примере: =A2*(КОРЕНЬ(A2)-2). Нажимаем «Ввод». Excel посчитал значение. «Размножаем» формулу по всему столбцу (потянув за правый нижний угол ячейки). Таблица с данными готова.

Переходим на новый лист (можно остаться и на этом – поставить курсор в свободную ячейку). «Вставка» — «Диаграмма» — «Точечная». Выбираем понравившийся тип. Щелкаем по области диаграммы правой кнопкой мыши – «Выбрать данные».

Выделяем значения Х (первый столбец). И нажимаем «Добавить». Открывается окно «Изменение ряда». Задаем имя ряда – функция. Значения Х – первый столбец таблицы с данными. Значения У – второй.

Жмем ОК и любуемся результатом.

С осью У все в порядке. На оси Х нет значений. Проставлены только номера точек. Это нужно исправить. Необходимо подписать оси графика в excel. Правая кнопка мыши – «Выбрать данные» — «Изменить подписи горизонтальной оси». И выделяем диапазон с нужными значениями (в таблице с данными). График становится таким, каким должен быть.

Наложение и комбинирование графиков

Построить два графика в Excel не представляет никакой сложности. Совместим на одном поле два графика функций в Excel. Добавим к предыдущей Z=X(√x – 3). Таблица с данными:

Выделяем данные и вставляем в поле диаграммы. Если что-то не так (не те названия рядов, неправильно отразились цифры на оси), редактируем через вкладку «Выбрать данные».

А вот наши 2 графика функций в одном поле.

Графики зависимости

Данные одного столбца (строки) зависят от данных другого столбца (строки).

Построить график зависимости одного столбца от другого в Excel можно так:

Условия: А = f (E); В = f (E); С = f (E); D = f (E).

Выбираем тип диаграммы. Точечная. С гладкими кривыми и маркерами.

Выбор данных – «Добавить». Имя ряда – А. Значения Х – значения А. Значения У – значения Е. Снова «Добавить». Имя ряда – В. Значения Х – данные в столбце В. Значения У – данные в столбце Е. И по такому принципу всю таблицу.

Готовые примеры графиков и диаграмм в Excel скачать:

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

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

Учитывая, что функция – зависимость одного параметра от другого, зададим значения для оси абсцисс с шагом 0,5. Строить график будем на отрезке [-3;3]. Называем столбец «х» , пишем первое значение «-3» , второе – «-2,5» . Выделяем их и тянем вниз за черный крестик в правом нижнем углу ячейки.

Будем строить график функции вида y=х^3+2х^2+2. В ячейке В1 пишем «у» , для удобства можно вписать всю формулу. Выделяем ячейку В2 , ставим «=» и в «Строке формул» пишем формулу: вместо «х» ставим ссылку на нужную ячейку, чтобы возвести число в степень, нажмите «Shift+6» . Когда закончите, нажмите «Enter» и растяните формулу вниз.

У нас получилась таблица, в одном столбце которой записаны значения аргумента – «х» , в другом – рассчитаны значения для заданной функции.

Перейдем к построению графика функции в Excel. Выделяем значения для «х» и для «у» , переходим на вкладку «Вставка» и в группе «Диаграммы» нажимаем на кнопочку «Точечная» . Выберите одну из предложенных видов.

График функции выглядит следующим образом.

Теперь покажем, что по оси «х» установлен шаг 0,5. Выделите ее и кликните по ней правой кнопкой мши. Из контекстного меню выберите пункт «Формат оси» .

Откроется соответствующее диалоговое окно. На вкладке «Параметры оси» в поле «цена основных делений» , поставьте маркер в пункте «фиксированное» и впишите значение «0,5» .

Чтобы добавить название диаграммы и название для осей, отключить легенду, добавить сетку, залить ее или выбрать контур, поклацайте по вкладкам «Конструктор» , «Макет» , «Формат» .

Построить график функции в Эксель можно и с помощью «Графика» . О том, как построить график в Эксель, Вы можете прочесть, перейдя по ссылке.

Давайте добавим еще один график на данную диаграмму. На этот раз функция будет иметь вид: у1=2*х+5. Называем столбец и рассчитываем формулу для различных значений «х» .

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

В поле «Элементы легенды» кликаем на кнопочку «Добавить» .

Появится окно «Изменение ряда» . Поставьте курсор в поле «Имя ряда» и выделите ячейку С1 . Для полей «Значения Х» и «Значения У» выделяем данные из соответствующих столбцов. Нажмите «ОК» .

Чтобы для первого графика в Легенде не было написано «Ряд 1» , выделите его и нажмите на кнопку «Изменить» .

Ставим курсор в поле «Имя ряда» и выделяем мышкой нужную ячейку. Нажмите «ОК» .

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

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

Думаю теперь, Вы сможете построить график функции в Excel, и при необходимости добавлять на диаграмму нужные графики.

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

Процесс подбора эмпирической формулы P(x) для опытной зависимости F(x) называется аппроксимацией (сглаживанием). Для зависимостей с одним неизвестным в Excel используются графики, а для зависимостей со многими неизвестными – пары функций из группы Статистические ЛИНЕЙН и ТЕНДЕНЦИЯ, ЛГРФПРИБЛ и РОСТ .

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

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

Excel предоставляет 5 видов аппроксимирующих функций:

1. Линейная – y=cx+b. Это простейшая функция, отражающая рост и убывание данных с постоянной скоростью.

2. Полиномиальная – y=c +c₁x+c₂x 2 +…+c₆x 6 . Функция описывает попеременно возрастающие и убывающие данные. Полином 2-ой степени может иметь один экстремум (min или max), 3-ей степени – до 2-х экстремумов, 4-ой степени – до 3-х и т.д.

3. Логарифмическая – y=clnx+b. Эта функция описывает быстро возрастающие (убывающие) данные, которые затем стабилизируются.

4. Степенная – y=cx b , (х>0и y>0). Функция отражает данные с постоянно увеличивающейся (убывающей) скоростью роста.

5. Экспоненциальная – y=ce bx , (e – основание натурального логарифма). Функция описывает быстро растущие (убывающие) данные, которые затем стабилизируются.

Для всех 5-ти видов функций используется аппроксимация данных по методу наименьших квадратов (см. справку по F1 "линия тренда").

В качестве примера рассмотрим зависимость продаж от рекламы, заданную следующими статистическими данными по некоторой фирме:

Реклама (тыс. руб.)	1,5	2,5	3,5	4,5	5,5
Продажи (тыс. руб.)

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

Приступим к решению. В первую очередь введите эти данные в Excel и постройте график, как на рис. 38. Как видно, график построен на основании диапазона B2:J2. Далее, щелкнув правой кнопкой мыши по графику, добавьте линию тренда, как показано на рис. 38.

Чтобы подписать ось Х соответствующими значениями рекламы (как на рис. 38), следует в ниспадающем меню (рис. 38) выбрать пункт Исходные данные. В открывшемся одноименном окне, в закладке Ряд, в поле Подписи оси Х, укажите диапазон ячеек, где записаны значения Х (здесь $B$1:$K$1):

В открывшемся окне настройки (рис. 39), на закладке Тип выберите для аппроксимации логарифмическую линию тренда (по виду графика). На закладке Параметры установите флажки, отображающие на графике уравнение и коэффициент детерминации.

После нажатия ОК Вы получите результат, как на рис. 40. Коэффициент детерминации R 2 =0.9846, что является неплохой степенью близости. Для подтверждения правильности выбранной функции (поскольку других теоретических соображений нет) спрогнозируйте развитие продаж на 10 периодов вперед. Для этого щелкните правой кнопкой по линии тренда – измените формат – после этого в поле Прогноз: вперед на: установите 10 (рис. 41).

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

Вычисление по полученной формуле =237,96*LN(6)+5,9606 в Excel дает значение 432 тыс. руб.

В Excel имеется функция ПРЕДСКАЗ(), которая вычисляет будущее значение Y по существующим парам значений X и Y значениям с использованием линейной регрессии. Функция Y по возможности должна быть линейной, т.е. описываться уравнением типа c+bx. Функция предсказания для нашего примера запишется так: =ПРЕДСКАЗ(K1;B2:J2;B1:J1). Запишите – должно получится значение 643,6 тыс. руб.

Часть11. Контрольные задания

Последнее изменение этой страницы: 2016-08-26; Нарушение авторского права страницы