При работе с вычислениям в программе Excel иногда требуется узнать результат функции, значения которой находятся за рамками известной области (например для прогнозирования). Рассмотрим как это сделать с помощью нескольких способов.
Метод экстраполяции позволяет найти результат функции, значения которой могут находится за пределами конкретных рамок. Зачастую это используется в прогнозировании различных экономических процессов. В этом методе можно работать как с значениями в таблицах так и в работе с данными в графиках.
Пример работы с табличными данными
Имеется таблица с конкретным диапазоном аргументов от 5 до 50, которые относятся к функции (f(x)). В данном примере надо вычислить результат для числа, которое находится за рамкой изветсных аргументов. В данном случае это число 55. Чтобы это сделать надо работать с функцией ПРЕДСКАЗ.
Выбираем ту ячейку, которая в конечном итоге будет показывать результат. После этого нужно нажать на кнопку в строке формул, которая отвечает за вставку функций.
Появится новое окно, в нём нужно выбрать среди категорий именно категорию «Статистические» и после этого ниже в списке надо выбрать «ПРЕДСКАЗ» и в конце подтвердить действие.
Далее откроется новое окно аргументов функции. Там где указание аргумента Х, вводим адрес нужной ячейки. Это наиболее оптимально, так как потом адрес ячейки можно выбрать другой и провести уже другие вычисления. Следующее поле называется «Известные значения Y». В нём надо выделить все ячейки с числами, с которыми ведется работа. В последнем поле известных значений Х, нужно выделить все значения которые относятся к аргументу X (первый столбец с примера). Когда эти действия произведены, подтверждаем изменения.
В конечном итоге в нужной ячейке появится результат, который относится к числу 55.
Пример работы с данными в графиках линией тренда
Для начала важно построить правильный график, предварительно выделив таблицу, с которой будут проводится вычисления. Далее переходим в раздел «Вставка» в верхней панели программы Excel и выбираем в этом разделе пункт «Диаграммы» и кликаем на кнопку обозначающую построение графика, после этого нажимаем на самый оптимальный для вас график.
Далее будет отображен график по выбранными ранее данным. Важное примечание : нужно удалить в нём линию обозначающую аргумент (указана стрелкой на изображении).
После этих действий, есть также горизонтальная шкала, но в нам надо чтобы было отображены данные об аргументах. Чтобы именно эти данные были показано, нажимаем по графику ПКМ и в контекстном меню кликаем на «Выбрать данные».
Появится новое окно, в нём кликаем соответствующую кнопку для изменения данных.
Далее кликаем на «Диапазон подписей оси» и после этого нужно выделить столбец значений, которые надо ЛКМ выделить числа, которые нам нужны, в данном примере это столбец с значениями x, после этого подтверждаем действие. Также не забудьте подтвердить действие в окне выбора источника данных, которое было открыто ранее.
Этим действиями мы подготовили график для работы. А теперь построим линию тренда. Для этого нужно нажать по графику, который мы подготовили и выше, в главном меню разделов, будет активирован нужным нам раздел «Макет», в нем надо выбрать пункт «Анализ» и выбрать кнопку линии тренда. Далее кликаем на «Линейное приближение», это наиболее оптимальный вариант. После этих действий линия тренда будет добавлена в график.
Чтобы сделать корректное отображение линии тренда, вновь нужно перейти с соответствующий пункт как на изображении выше, но в списке нажать на последний вариант, который позволит задать дополнительные параметры в линии тренда.
Далее будет открыто новое окно, в котором можно задать параметры линии тренда. Ищем в окне настройки прогноза, и задаем число 1 (период), так как пять единиц значений = одному периоду, это было сделано так как значение за пределами 50 возьмем вновь 55.
Результатом будет удлинение длины графика соответственно к параметрам линии тренда.
Применение интерполяции в Microsoft Excel
Бывает ситуация, когда в массиве известных значений нужно найти промежуточные результаты. В математике это называется интерполяцией. В Excel данный метод можно применять как для табличных данных, так и для построения графиков. Разберем каждый из этих способов.
Использование интерполяции
Главное условие, при котором можно применять интерполяцию – это то, что искомое значение должно быть внутри массива данных, а не выходить за его предел. Например, если мы имеем набор аргументов 15, 21 и 29, то при нахождении функции для аргумента 25 мы можем использовать интерполяцию. А для поиска соответствующего значения для аргумента 30 – уже нет. В этом и является главное отличие этой процедуры от экстраполяции.
Способ 1: интерполяция для табличных данных
Прежде всего, рассмотрим применения интерполяции для данных, которые расположены в таблице. Для примера возьмем массив аргументов и соответствующих им значений функции, соотношение которых можно описать линейным уравнением. Эти данные размещены в таблице ниже. Нам нужно найти соответствующую функцию для аргумента 28. Сделать это проще всего с помощью оператора ПРЕДСКАЗ.
-
Выделяем любую пустую ячейку на листе, куда пользователь планирует выводить результат от проведенных действий. Далее следует щелкнуть по кнопке «Вставить функцию», которая размещена слева от строки формул.
Активируется окошко Мастера функций. В категории «Математические» или «Полный алфавитный перечень» ищем наименование «ПРЕДСКАЗ». После того, как соответствующее значение найдено, выделяем его и щелкаем по кнопке «OK».
В первое поле нам просто нужно вручную с клавиатуры вбить значения аргумента, функцию которого следует отыскать. В нашем случае это 28.
В поле «Известные значения y» нужно указать координаты диапазона таблицы, в котором содержатся значения функции. Это можно сделать вручную, но гораздо проще и удобнее установить курсор в поле и выделить соответствующую область на листе.
Аналогичным образом устанавливаем в поле «Известные значения x» координаты диапазона с аргументами.
После того, как все нужные данные введены, жмем на кнопку «OK».
Способ 2: интерполяция графика с помощью его настроек
Процедуру интерполяции можно применять и при построении графиков функции. Актуальна она в том случае, если в таблице, на основе которой построен график, к одному из аргументов не указано соответствующее значение функции, как на изображении ниже.
-
Выполняем построение графика обычным методом. То есть, находясь во вкладке «Вставка», выделяем табличный диапазон, на основе которого будет проводиться построение. Щелкаем по значку «График», размещенному в блоке инструментов «Диаграммы». Из появившегося списка графиков выбираем тот, который считаем более уместным в данной ситуации.
Как видим, график построен, но не совсем в таком виде, как нам нужно. Во-первых, он разорван, так как для одного аргумента не нашлась соответствующая функция. Во вторых, на нем присутствует дополнительная линия X, которая в данном случае не нужна, а также на горизонтальной оси указаны просто пункты по порядку, а не значения аргумента. Попробуем исправить все это.
Для начала выделяем сплошную синюю линию, которую нужно удалить и жмем на кнопку Delete на клавиатуре.
Выделяем всю плоскость, на которой размещен график. В появившемся контекстном меню щелкаем по кнопке «Выбрать данные…».
Запускается окно выбора источника данных. В правом блоке «Подписи горизонтальной оси» жмем на кнопку «Изменить».
Открывается небольшое окошко, где нужно указать координаты диапазона, значения из которого будут отображаться на шкале горизонтальной оси. Устанавливаем курсор в поле «Диапазон подписей осей» и просто выделяем соответствующую область на листе, в которой содержаться аргументы функции. Жмем на кнопку «OK».
Теперь нам осталось выполнить основную задачу: с помощью интерполяции устранить разрыв. Вернувшись в окно выбора диапазона данных жмем на кнопку «Скрытые и пустые ячейки», расположенную в нижнем левом углу.
Открывается окно настройки скрытых и пустых ячеек. В параметре «Показывать пустые ячейки» выставляем переключатель в позицию «Линию». Жмем на кнопку «OK».
Как видим, график скорректирован, а разрыв с помощью интерполяции удален.
Способ 3: интерполяция графика с помощью функции
Произвести интерполяцию графика можно также с помощью специальной функции НД. Она возвращает неопределенные значения в указанную ячейку.
-
После того, как график построен и отредактирован, так как вам нужно, включая правильную расстановку подписи шкалы, остается только ликвидировать разрыв. Выделяем пустую ячейку в таблице, из которой подтягиваются данные. Жмем на уже знакомый нам значок «Вставить функцию».
Открывается Мастер функций. В категории «Проверка свойств и значений» или «Полный алфавитный перечень» находим и выделяем запись «НД». Жмем на кнопку «OK».
У данной функции нет аргумента, о чем и сообщает появившееся информационное окошко. Чтобы закрыть его просто жмем на кнопку «OK».
Можно сделать даже проще, не запуская Мастер функций, а просто с клавиатуры вбить в пустую ячейку значение «#Н/Д» без кавычек. Но это уже зависит от того, как какому пользователю удобнее.
Как видим, в программе Эксель можно выполнить интерполяцию, как табличных данных, используя функцию ПРЕДСКАЗ, так и графика. В последнем случае это осуществимо с помощью настроек графика или применения функции НД, вызывающей ошибку «#Н/Д». Выбор того, какой именно метод использовать, зависит от постановки задачи, а также от личных предпочтений пользователя.
Мы рады, что смогли помочь Вам в решении проблемы.
Добавьте сайт Lumpics.ru в закладки и мы еще пригодимся вам.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Экстраполяция в excel как сделать
Существуют случаи, когда требуется узнать результаты вычисления функции за пределами известной области. Особенно актуален данный вопрос для процедуры прогнозирования. В Экселе есть несколько способов, с помощью которых можно совершить данную операцию. Давайте рассмотрим их на конкретных примерах.
Использование экстраполяции
В отличие от интерполяции, задачей которой является нахождения значения функции между двумя известными аргументами, экстраполяция подразумевает поиск решения за пределами известной области. Именно поэтому данный метод столь востребован для прогнозирования.
В Экселе можно применять экстраполяцию, как для табличных значений, так и для графиков.
Способ 1: экстраполяция для табличных данных
Прежде всего, применим метод экстраполяции к содержимому табличного диапазона. Для примера возьмем таблицу, в которой имеется ряд аргументов (X) от 5 до 50 и ряд соответствующих им значений функции (f(x)). Нам нужно найти значение функции для аргумента 55, который находится за пределом указанного массива данных. Для этих целей используем функцию ПРЕДСКАЗ.
- Выделяем ячейку, в которой будет отображаться результат проведенных вычислений. Кликаем по значку «Вставить функцию», который размещен у строки формул.
- Запускается окно Мастера функций. Выполняем переход в категорию «Статистические» или «Полный алфавитный перечень». В открывшемся списке производим поиск наименования «ПРЕДСКАЗ». Найдя его, выделяем, а затем щелкаем по кнопке «OK» в нижней части окна.
- Мы перемещаемся к окну аргументов вышеуказанной функции. Она имеет всего три аргумента и соответствующее количество полей для их внесения.
В поле «X» следует указать значение аргумента, функцию от которого нам следует вычислить. Можно просто вбить с клавиатуры нужное число, а можно указать координаты ячейки, если аргумент записан на листе. Второй вариант даже предпочтительнее. Если мы произведем внесение именно таким способом, то для того, чтобы просмотреть значение функции для другого аргумента нам не придется менять формулу, а достаточно будет изменить вводную в соответствующей ячейке. Для того, чтобы указать координаты этой ячейки, если был выбран все-таки второй вариант, достаточно установить курсор в соответствующее поле и выделить эту ячейку. Её адрес тут же отобразится в окне аргументов.
В поле «Известные значения y» следует указать весь имеющийся у нас диапазон значений функции. Он отображается в колонке «f(x)». Следовательно, устанавливаем курсор в соответствующее поле и выделяем всю эту колонку без её наименования.
В поле «Известные значения x» следует указать все значения аргумента, которым соответствуют внесенные нами выше значения функции. Эти данные находятся в столбце «x». Точно так же, как и в предыдущий раз выделяем нужную нам колонку, предварительно установив курсор в поле окна аргументов.
После того, как все данные внесены, жмем на кнопку «OK».
Урок: Мастер функций в Excel
Способ 2: экстраполяция для графика
Выполнить процедуру экстраполяции для графика можно путем построения линии тренда.
- Прежде всего, строим сам график. Для этого курсором при зажатой левой кнопке мыши выделяем всю область таблицы, включая аргументы и соответствующие значения функции. Затем, переместившись во вкладку «Вставка», кликаем по кнопке «График». Этот значок расположен в блоке «Диаграммы» на ленте инструментов. Появляется перечень доступных вариантов графиков. Выбираем наиболее подходящий из них на свое усмотрение.
- После того, как график построен, удаляем из него дополнительную линию аргумента, выделив её и нажав на кнопку Delete на клавиатуре компьютера.
- Далее нам нужно поменять деления горизонтальной шкалы, так как в ней отображаются не значения аргументов, как нам того нужно. Для этого, кликаем правой кнопкой мыши по диаграмме и в появившемся списке останавливаемся на значении «Выбрать данные».
- В запустившемся окне выбора источника данных кликаем по кнопке «Изменить» в блоке редактирования подписи горизонтальной оси.
- Открывается окно установки подписи оси. Ставим курсор в поле данного окна, а затем выделяем все данные столбца «X» без его наименования. Затем жмем на кнопку «OK».
- После возврата к окну выбора источника данных повторяем ту же процедуру, то есть, жмем на кнопку «OK».
- Теперь наш график подготовлен и можно, непосредственно, приступать к построению линии тренда. Кликаем по графику, после чего на ленте активируется дополнительный набор вкладок – «Работа с диаграммами». Перемещаемся во вкладку «Макет» и жмем на кнопку «Линия тренда» в блоке «Анализ». Кликаем по пункту «Линейное приближение» или «Экспоненциальное приближение».
- Линия тренда добавлена, но она полностью находится под линией самого графика, так как мы не указали значение аргумента, к которому она должна стремиться. Чтобы это сделать опять последовательно кликаем по кнопке «Линия тренда», но теперь выбираем пункт «Дополнительные параметры линии тренда».
- Запускается окно формата линии тренда. В разделе «Параметры линии тренда» есть блок настроек «Прогноз». Как и в предыдущем способе, давайте для экстраполяции возьмем аргумент 55. Как видим, пока что график имеет длину до аргумента 50 включительно. Получается, нам нужно будет его продлить ещё на 5 единиц. На горизонтальной оси видно, что 5 единиц равно одному делению. Значит это один период. В поле «Вперед на» вписываем значение «1». Жмем на кнопку «Закрыть» в нижнем правом углу окна.
- Как видим, график был продлен на указанную длину с помощью линии тренда.
Урок: Как построить линию тренда в Excel
Итак, мы рассмотрели простейшие примеры экстраполяции для таблиц и для графиков. В первом случае используется функция ПРЕДСКАЗ, а во втором – линия тренда. Но на основе этих примеров можно решать и гораздо более сложные задачи прогнозирования.
Мы рады, что смогли помочь Вам в решении проблемы.
Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
При работе с вычислениям в программе Excel иногда требуется узнать результат функции, значения которой находятся за рамками известной области (например для прогнозирования). Рассмотрим как это сделать с помощью нескольких способов.
Метод экстраполяции позволяет найти результат функции, значения которой могут находится за пределами конкретных рамок. Зачастую это используется в прогнозировании различных экономических процессов. В этом методе можно работать как с значениями в таблицах так и в работе с данными в графиках.
Пример работы с табличными даннымиИмеется таблица с конкретным диапазоном аргументов от 5 до 50, которые относятся к функции (f(x)). В данном примере надо вычислить результат для числа, которое находится за рамкой изветсных аргументов. В данном случае это число 55. Чтобы это сделать надо работать с функцией ПРЕДСКАЗ.
Выбираем ту ячейку, которая в конечном итоге будет показывать результат. После этого нужно нажать на кнопку в строке формул, которая отвечает за вставку функций.
Появится новое окно, в нём нужно выбрать среди категорий именно категорию «Статистические» и после этого ниже в списке надо выбрать «ПРЕДСКАЗ» и в конце подтвердить действие.
Далее откроется новое окно аргументов функции. Там где указание аргумента Х, вводим адрес нужной ячейки. Это наиболее оптимально, так как потом адрес ячейки можно выбрать другой и провести уже другие вычисления. Следующее поле называется «Известные значения Y». В нём надо выделить все ячейки с числами, с которыми ведется работа. В последнем поле известных значений Х, нужно выделить все значения которые относятся к аргументу X (первый столбец с примера). Когда эти действия произведены, подтверждаем изменения.
В конечном итоге в нужной ячейке появится результат, который относится к числу 55.
Пример работы с данными в графиках линией трендаДля начала важно построить правильный график, предварительно выделив таблицу, с которой будут проводится вычисления. Далее переходим в раздел «Вставка» в верхней панели программы Excel и выбираем в этом разделе пункт «Диаграммы» и кликаем на кнопку обозначающую построение графика, после этого нажимаем на самый оптимальный для вас график.
Далее будет отображен график по выбранными ранее данным. Важное примечание : нужно удалить в нём линию обозначающую аргумент (указана стрелкой на изображении).
После этих действий, есть также горизонтальная шкала, но в нам надо чтобы было отображены данные об аргументах. Чтобы именно эти данные были показано, нажимаем по графику ПКМ и в контекстном меню кликаем на «Выбрать данные».
Появится новое окно, в нём кликаем соответствующую кнопку для изменения данных.
Далее кликаем на «Диапазон подписей оси» и после этого нужно выделить столбец значений, которые надо ЛКМ выделить числа, которые нам нужны, в данном примере это столбец с значениями x, после этого подтверждаем действие. Также не забудьте подтвердить действие в окне выбора источника данных, которое было открыто ранее.
Этим действиями мы подготовили график для работы. А теперь построим линию тренда. Для этого нужно нажать по графику, который мы подготовили и выше, в главном меню разделов, будет активирован нужным нам раздел «Макет», в нем надо выбрать пункт «Анализ» и выбрать кнопку линии тренда. Далее кликаем на «Линейное приближение», это наиболее оптимальный вариант. После этих действий линия тренда будет добавлена в график.
Чтобы сделать корректное отображение линии тренда, вновь нужно перейти с соответствующий пункт как на изображении выше, но в списке нажать на последний вариант, который позволит задать дополнительные параметры в линии тренда.
Далее будет открыто новое окно, в котором можно задать параметры линии тренда. Ищем в окне настройки прогноза, и задаем число 1 (период), так как пять единиц значений = одному периоду, это было сделано так как значение за пределами 50 возьмем вновь 55.
Результатом будет удлинение длины графика соответственно к параметрам линии тренда.
Табличный процессор Excel позволяет не только быстро производить различные вычисления, но и решать достаточно сложные задачи. Например, с его помощью можно осуществлять математическое моделирование на основе набора дискретных значений той или иной функции, в том числе находить промежуточное значение функций методом интерполяции. В Excel для этого предусмотрены различные инструменты, пользоваться которыми научит эта статья.
Метод интерполяции: что это такое?
В вычислительной математике так называют способ нахождения промежуточных неизвестных значений функции Y(X) по дискретному набору уже известных.
Интерполяция функции Y(X) может осуществляться только для тех ее аргументов, которые находятся внутри интервала , такого, что известны значения Y(X0) и Y(Xn).
Если X не принадлежит , то можно использовать метод экстраполяции.
В классической постановке интерполяционной задачи требуется найти приближенную аналитическую функцию φ(X), у которой значения в узловых точках Xi совпадают со значениями Y(Xi) исходной таблицы, т. е. соблюдается условие φ (Xi)=Yi (i = 0,1,2,…,n).
Линейная интерполяция в Excel
В самом известном табличном процессоре от Microsoft присутствует крайне полезный оператор «ПРЕДСКАЗ».
Рассмотрим данные, размещенные в в таблице, представленной ниже.
Интерполяция графика и табличных данных в Excel
Интерполяция – это своего рода «латание» графиков в тех местах, где возникают обрывы линий из-за отсутствия данных по отдельным показателям. Термин интерполяция подразумевает «латание» внутренних обрывов на графике. А если бы «латались» внешние обрывы, то это была-бы уже экстраполяция графика.
Как построить график с интерполяцией в Excel
При работе в Excel приходится сталкиваться с интерполяцией графиков различной сложности. Но для первого знакомства с ней рассмотрим сначала самый простой пример.
Если в таблице еще нет всех значений показателей, но уже нужно сформировать по ним отчет и построить графическое представление данных. Тогда на графике мы наблюдаем обрывы в местах, где отсутствуют значения показателей.
Заполните таблицу как показано на рисунке:
Выделите диапазон A1:B4 и выберите инструмент: «Вставка»-«Диаграммы»-«График»-«График с маркерами».
Чтобы устранить обрывы на графике, то есть выполнить интерполяцию в Excel, можем использовать 2 решения для данной задачи:
- Изменить параметры в настройках графика выбрав соответствующую опцию.
- Использовать функцию: =НД() – возвращает значение ошибки #Н/Д.
Оба эти способа рассмотрим далее на конкретных примерах.
- Сделайте график активным щелкнув по нему левой кнопкой мышки и выберите инструмент: «Работа с диаграммами»-«Конструктор»-«Выбрать данные».
- В появившемся диалоговом окне «Выбор источника данных» кликните на кнопку «Скрытые и пустые ячейки»
- В появившемся диалоговом окне «Настройка скрытых и пустых ячеек» выберите опцию «линию». И нажмите ОК во всех открытых диалоговых окнах.
Как видно на рисунках сразу отображены 2 варианта опций «линию» и «нулевые значения». Обратите внимание, как ведет себя график при выборе каждой из них.
Методы интерполяции табличных данных в Excel
Теперь выполним интерполяцию данных в таблице с помощью функции: =НД(). Для этого нужно предварительно сбросить выше описанные настройки графика, чтобы увидеть как работает данный способ.
Способ 2 . В ячейку B3 введите функцию =НД(). Это автоматически приведет к интерполяции графика как показано на рисунке:
Примечание. Вместо функции =НД() в ячейку можно ввести просто значение: #Н/Д!, результат будет тот же.
MATCH
In this course:
MATCH function
Tip: Try using the new XMATCH function, an improved version of MATCH that works in any direction and returns exact matches by default, making it easier and more convenient to use than its predecessor.
The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range.
Tip: Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself. For example, you might use the MATCH function to provide a value for the row_num argument of the INDEX function.
Syntax
MATCH(lookup_value, lookup_array, [match_type])
The MATCH function syntax has the following arguments:
lookup_value Required. The value that you want to match in lookup_array. For example, when you look up someone’s number in a telephone book, you are using the person’s name as the lookup value, but the telephone number is the value you want.
The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
lookup_array Required. The range of cells being searched.
match_type Optional. The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.
The following table describes how the function finds values based on the setting of the match_type argument.
MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: . -2, -1, 0, 1, 2, . A-Z, FALSE, TRUE.
MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.
MATCH finds the smallest value that is greater than or equal to lookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, . 2, 1, 0, -1, -2, . and so on.
MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH(«b», <"a»,»b»,»c «>,0) returns 2, which is the relative position of «b» within the array <"a","b","c">.
MATCH does not distinguish between uppercase and lowercase letters when matching text values.
If MATCH is unsuccessful in finding a match, it returns the #N/A error value.
If match_type is 0 and lookup_value is a text string, you can use the wildcard characters — the question mark ( ?) and asterisk ( *) — in the lookup_value argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (
) before the character.
Example
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.
