Интерполация в Excel: функции, процедури и примери

Excel табличен процесор позволява не само бързо изпълнение на различни изчисления, но и решаване на сравнително сложни задачи. Например, той може да се използва за извършване на математическо моделиране въз основа на набор от дискретни стойности на функция, включително намиране на междинната стойност на функциите чрез интерполация. В Excel за тази цел са осигурени различни инструменти, които тази статия ще преподава.

Метод на интерполация: какво е това?

В изчислителната математика, това е името на метода за намиране на междинните неизвестни стойности на функцията Y (X) от отделен набор от вече известни.

Интерполирането на функцията Y (X) може да се извърши само за онези аргументи, които са в интервала [X₀, X_п], така че стойностите на Y (X₀) и Y (X_п).

Ако X не принадлежи към [X₀, X_п], тогава може да се използва методът на екстраполация.

В класическата формулировка на интерполационния проблем е необходимо да се намери приблизителна аналитична функция phi- (X), за които стойностите в възловите точки на X_аз съвпадат със стойностите на Y (X_аз) на оригиналната таблица, т.е. състояние phi- (X_аз) = Y_аз (i = 0,1,2, ..., п).

Линейна интерполация в Excel

В най-известния процесор от Microsoft, има много полезен оператор PRESCASE.

Обмислете данните, посочени в таблицата по-долу.

В първата колона има аргументи x, а във втората - съответните стойности на някои линейни функции f (x). Да предположим, че искаме да знаем стойността на аргумента x = 28. За да направите това:

• разпределя всяка празна клетка върху листа на табличния процесор, където резултатът ще се изведе от изпълнените действия, например C1;
• Кликнете върху иконата "fx" ("Вмъкване на функция"), разположена отляво на формулата;
• в прозореца "Магьосници на функции" въведете категорията "Математически";
• намерете оператора "PRESCASE" и кликнете върху "OK".

В прозореца с аргументи има 3 полета. В първата въведете стойността на аргумента от клавиатурата (в конкретна задача това е 28). За да попълните полето "Известни стойности _", кликнете върху иконата с червената стрелка отляво на съответния прозорец и изберете съответната област в листа. В конкретен случай това е част от колона Б с адреси от диапазона B2: B7.

По същия начин попълнете поле "Известни стойности _ x" и кликнете върху бутона "OK".

В резултат на това стойността в маркираната клетка С1 е 176, което е резултат от интерполационната процедура.

Графичен метод: подготовка

Интерполацията в Excel, чийто пример е представен по-горе, по никакъв начин не е единственият начин да се установят междинните неизвестни стойности на функцията Y (X) от отделен набор от вече известни. По-специално може да се приложи графичен метод. Може да е полезно, ако таблицата към един от аргументите не указва съответната стойност на функцията, както е показано по-долу (виж клетката с адрес B9).

Интерполацията в Excel в този случай започва с графиката. За да направите това:

• в раздела "Вмъкване" задайте табличен диапазон;
• В полето "Диаграми" изберете иконата "Графика";
• В списъка, който се показва, изберете този, който е най-подходящ за конкретна задача.

Тъй като клетката B9 е празна, графиката е счупена. Освен това има допълнителна линия X, която не е необходима, а на хоризонталната ос вместо аргументите стойностите са изброени в ред.

Интерполация в Excel: графично решение

Ще се занимаем с обработката на графиката. За да направите това, изберете синя синя линия и я изтрийте, като натиснете бутона Изтриване на клавиатурата.

след това:

• разпределя равнината, на която е разположена графиката;
• в контекстното меню изберете бутона "Select datahellip;";
• в прозореца "Избор на източник на данни" в десния блок кликнете върху "Промяна";
• кликнете върху иконата с червена стрелка вдясно от полето "Обхват на подписване на ос";
• разпределете диапазона A2: A11;
• кликнете върху бутона "OK";
• Отново се отваря прозорецът "Избор на източник на данни";
• кликнете върху бутона "Скрити и празни клетки" в долния ляв ъгъл;
• в реда "Показване на празните клетки" превключвателят се премества в позиция "Линия" и кликнете върху "OK";
• потвърдете тези действия по същия начин.

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

Използване на специална LP функция

Сега, когато знаете как да направите интерполация в Excel, като използвате графичния метод или използвате оператора PRESCASE, решаването на много практически задачи за вас няма да бъде голяма работа. Това обаче не е всичко. Един табличен процесор от Microsoft представя възможността за намиране на неизвестна стойност на функция, използвайки функцията ND.

Да предположим, че графиката вече е изградена, вече са зададени правилните подписи на скалата. Нека се опитаме да запълним празнината. За да направите това:

• изберете в таблицата клетка, в която липсва стойността на функцията;
• изберете иконата "Вмъкване на функция";
• в "Помощ за функциите" в прозореца "Категории" намирате низа "Пълен азбучен списък" (в някои версии на процесора "Проверка на свойства и стойности");
• кликнете върху записа "ND" и натиснете бутона "OK".

След това в клетка B9 се показва стойността на грешката "# N / D". Прекъсването на графиката обаче автоматично се елиминира.

Можете да направите още по-лесно: от клавиатурата в клетка B9 знаците "# N / D" (без кавичките).

Билинеарна интерполация

Диапазонът от задачи, за които е възможно да се използва моделиране чрез функциите на една променлива, е доста ограничен. Следователно, има смисъл да се обмисли как се използва двойната формула за интерполация в Excel. Примерите могат да бъдат много различни. Например: има таблица (виж по-долу).

Необходимо е да се изчисли налягането на вятъра в диапазон от 300 m на височина 25 m.

Добавете нови записи към таблицата, както е показано на фигурата (вижте по-долу).

Както можете да видите, клетки за височина и обхват в J1 и J2 се добавят към него.

Чрез обратното последователно заместване "мега-формулата", необходима за намиране на налягането на вятъра при определени параметри, се "събира". За да направите това:

• копирайте текста от формулата от клетката с адрес J17 към клетка J19;
• заменете отправката към J15 със стойността в клетка J15: J7 + (J8-J7) * J11 / J13;
• повторете тези действия, докато се получи необходимата формула.

Използване на сплайн

Предишният метод е доста тромав, така че в някои случаи за предпочитане е сплайн интерполация. В Excel, неговата същност се крие в намирането на интерполационната функция phi- (X) чрез формули от същия тип за различни подгрупи на аргумента. След това стойностите се картографират phi- (X) и неговите производни на граничните стойности на аргументите на всяка от подгрупите. В Excel за тези цели са предвидени специални функции и е възможно да се напишат макроси на VBA. Те обаче трябва да бъдат създадени за конкретна задача, така че тяхното изучаване в обща форма няма смисъл.

Сега знаете как да напишете правилно формулата за двойна интерполация в Excel или да откриете неизвестната стойност на линейна функция чрез вградени оператори или графика. Надяваме се, че тази информация ще ви помогне в решаването на много практически проблеми.