Jak interpolować dane w zakresie w Arkuszach Google

Mam tablicę z danymi:

   X      Y
   3     50
   5     60
   9    120
  11    130
  18     90
  20    150

Dane są całkowicie nieliniowe. X gwarantuje posortowanie.

Teraz dla dowolnej wartości chciałbym mieć liniową interpolację między liczbami (na przykład 3 => 50, 4 => 55, 5 => 60). Interpolacja dwuliniowa byłaby jeszcze ładniejsza, ale moje oczekiwania są niskie.

Ten skrypt zrobi to samo (plus trochę więcej).

Kod

function myInterpolation(x, y, value) {
  if(value > Math.max.apply(Math, x) || value < Math.min.apply(Math, x)) {
    throw "value can't be interpolated !!";
    return;
  }

  var check = 0, index;
  for(var i = 0, iLen = x.length; i < iLen; i++) {
    if(x[i][0] == value) {
      return y[i][0];
    } else {      
      if(x[i][0] < value && ((x[i][0] - check) < (value - check))) {
        check = x[i][0];
        index = i;
      }
    }
  }

  var xValue, yValue, xDiff, yDiff, xInt;
  yValue = y[index][0];
  xDiff = x[index+1][0] - check;
  yDiff = y[index+1][0] - yValue;
  xInt = value - check; 

  return (xInt * (yDiff / xDiff)) + yValue;
}

Wyjaśnione

Na początku skryptu istnieje niewielka obsługa błędów. Następnie znajdzie pierwszy najniższy wpis w porównaniu do wartości wejściowej. Po znalezieniu zrobi trochę matematyki i przedstawi wynik.

Uwaga

Jeśli wybrana wartość jest równa 20, skrypt zwraca 150 tak, jak daje formuła #DIV/0.

Zrzut ekranu

Formuła

Użyj poniższej formuły, aby uwzględnić wszystkie wartości

=IF(
   ISNA(
     MATCH(C2,A2:A7,0)),
   FORECAST(
     $C$2,
     OFFSET(B$2,MATCH($C$2,A$2:A$7,1)-1,0,2,1),
     OFFSET(A$2,MATCH($C$2,A$2:A$7,1)-1,0,2,1)), 
   INDEX(
     B2:B7,
     MATCH(C2,A2:A7,0)
     ,0)
 )

 copy / paste
 =IF(ISNA(MATCH(C2, A2:A7, 0)), FORECAST($C$2,OFFSET(B$2,MATCH($C$2,A$2:A$7,1)-1,0,2,1),OFFSET(A$2,MATCH($C$2,A$2:A$7,1)-1,0,2,1)), INDEX(B2:B7, MATCH(C2, A2:A7, 0), 0))

Przykład

Dodaj skrypt w menu Narzędzia> Edytor skryptów i naciśnij przycisk Zapisz (nie wymaga uwierzytelnienia).

Utworzyłem dla Ciebie przykładowy plik: Jak interpolować dane w zakresie w Arkuszach Google

Znalazłem sposób na zrobienie tego - może być lepszy sposób, ale oto co wymyśliłem:

Zakładając, że dane są w A1: B10, a $ C $ 1 zawiera klucz do poszukiwania:

=FORECAST($C$1,
    OFFSET(B$1,MATCH($C$1,A$1:A$10,1)-1,0,2,1),
    OFFSET(A$1,MATCH($C$1,A$1:A$10,1)-1,0,2,1))

Szczegółowo:

FORECAST dokonuje interpolacji liniowej, ale zakłada linię prostą. Musimy więc znaleźć dwie wartości, które zawierają szukaną wartość.

Używamy MATCH, aby znaleźć pierwszą liczbę, która jest równa lub wyższa od tego, czego szukamy.

FORECAST oczekuje zakresu danych, dlatego używamy OFFSET, aby utworzyć odniesienie do zakresu danych. PODAJNIK jest indeksowany jako jeden, więc najpierw musimy go odjąć. Tworzymy zakres, który jest jeden szeroki i dwa wysokie. Ta wartość z pewnością obejmuje $ 1 $, naszą wartość wyszukiwania.

Jest to niewielka modyfikacja skryptu Jacoba Jana Tuinstry , pozwalająca na przyjęcie tablicy lub wartości jako trzeciego argumentu, dzięki czemu funkcja interpolowana może być obliczana w wielu miejscach jednocześnie. Jedyna różnica polega na dodaniu kilku wierszy na początku; jest to szybki sposób na przekształcenie dowolnej funkcji niestandardowej w funkcję niestandardową, która akceptuje tablicę.

function myInterpolation(x, y, value) {
  if (value.map) {
    return value.map(function(v) {
      return myInterpolation(x, y, v);
    });
  }
  //  the rest stays the same

  if (value > Math.max.apply(Math, x) || value < Math.min.apply(Math, x)) {
    throw "value can't be interpolated !!";
    return;
  }

  var check = 0, index;
  for(var i = 0, iLen = x.length; i < iLen; i++) {
    if(x[i][0] == value) {
      return y[i][0];
    } else {      
      if(x[i][0] < value && ((x[i][0] - check) < (value - check))) {
        check = x[i][0];
        index = i;
      }
    }
  }

  var xValue, yValue, xDiff, yDiff, xInt;
  yValue = y[index][0];
  xDiff = x[index+1][0] - check;
  yDiff = y[index+1][0] - yValue;
  xInt = value - check; 

  return (xInt * (yDiff / xDiff)) + yValue;
}