Splaten paarweise substrahieren

[bioinformatiker]

Hallo,
ich habe eine ungefähr 10.000 x 100 große Tabelle. Diese Tabelle dient als Rohdatenmatrix für Clusteranalyse. Dabei geht es um Stresse (so wie kälte, dürre, salz usw), die auf einen Organismus wirken können.
Nun soll sie in Distanzmatrix überführt werden und zwar für die 100 Spalten (also Stress x Stress). Somit bekomme ich nun eine 100 x 100 große Tabelle, bei der nur untere Diagonale befüllt ist.
Folgendes habe ich bis jetz gemacht:
In einer neuen Sheet in die erste Zelle habe ich geschrieben: WENN(ZEILE( )<SPALTE( );"---";WENN(ZEILE( )=SPALTE( );0;"toDo")).
Man erkennt alle Werte die über Diagonale liegen werden mit Strichen befüllt. Da wo spalte = zeile sind die 0, da die Stresse gleich sind daher muss keine Distanz berechnet werden.
Da wo toDo steht muss eine Formel hin. Der Wert wird folgendermaßen berechnet: Angenommen für Stress1 und Stress2 werden die Werte der Zeilen aus Rohdatenmatrix paarweise substrahiert (also für alle 10000 Stück) und anschließend quadriert und summiert:
Also: Summe( (Wert1,1-Wert1,2)^2; (Wert2,1-Wert2,2)^2; (Wert3,1-Wert3,2)^2; ...)
Wobei Wert 1,1 steht für erste Zeile erste Spalte; Wert 3,2 für dritte Zeile 2 Spalte.
Man sieht sofort, dass die Formel selbst für eine Zelle bei 10000 Zeilen rieseig wäre es sind aber knapp 5000 solche zu berechnen.
Es muss bestimmt eine Möglichekeit geben, die Zeilen paarweise zu substrahieren. Quadrat ist kein Problem dafür ist QUADRATESUMME da.
Hat jemand eine Idee? Vieln Dank im Voraus

[karolus]

Hallo

Schreibe in A1 in einem neuen Blatt:

Code: Alles auswählen

=(INDEX(ref;ZEILE(A1:A10000);SPALTE(A1)*2-1)-INDEX(ref;ZEILE(A1:A10000);SPALTE(A1)*2))^2

Aktiviere diese Formel als Matrixfunktion ( [x]Matrix im Formelassistenten )
Kopiere die jetzt entstandene Matrix mit STRG+c , markiere B1:AX1 und füge mit STRG+v ein.


Ps. ref ist der 100*10000 grosse Daten-Bereich mit dem Namen ref (→Einfügen→Namen...)

Karo

[bioinformatiker]

vielen Dank für den Ansatz. Das funktioniert aber leider nicht ganz so wie gewünscht. Auf dem ersten Bild ist die Kurzversion der Matrix
Bild1
Den Bereich kompltett mit Überschriften habe ich smallTest genannt.
Weiterhin auf dem 2.Blatt habe ich in die A1 folgende Formel reingeschrieben

Code: Alles auswählen

WENN(ZEILE()<SPALTE();"---";WENN(ZEILE()=SPALTE();0;QUADRATESUMME(INDEX(smallTest;ZEILE(A$2:A$8);SPALTE(A1)*2-1)-INDEX(smallTest;ZEILE(A$2:A$8);SPALTE(A1)*2))))

Die Berechnung zumindest für die erste Zelle ist richtig (ja es wird natürlich 0 mit der Formel angezeigt - also das erste Problem, wenn ich meine Bedingungen nicht berücksichtige wird natürlich richtig berechnet)
Angenommen ich bilde die Distanzmatrix und wil die danach in die Tabelle StressxStress (Also 3x3 kopieren) wobei erste Zeile und erste Spalte jeweils die Überschriften sind. Also ich füge die Matrix ab B2 ein (B2 -> D4). Daher funktioniert die Formel schon mal nicht.
Außerdem soll es andersrum berechnet werden: in B2 kommt ja 0 (nach meiner Formel oben auch in C3 und D4). C2, D2 und D3 werden mit Strichen ("---") befüllt.
Die Berechnung nach der Formel für B3 wäre schon richtig (Also B2:B8 minus C2:C8 - also mit Berücksichtigung von Überschriften)
In B4 muss ABER B2:B8 minus D2:D8 kommen anstatt B2:B8 minus D2:D8 (!!!)
Wie kann man die Formel entsprechend korregieren?

[karolus]

Hallo

In B4 muss ABER B2:B8 minus D2:D8 kommen anstatt B2:B8 minus D2:D8 (!!!)

Der Unterschied zwischen dem roten und dem grünen Text ist der zwischen 'rot' und 'grün' !?

Häng mal ein .ods-Beispielsdokument an, ich hab keine Lust deine Zahlenmatrix mühsam nachzubauen.

Karo