Office: (Office 365) Adjazenzmatrix mit berechneten Werten

Liebe Gemeinde,

ein Neuling hier. Seit einigen Monaten sitze ich an einer (sozialen) Netzwerkanalyse für meine Abschlussarbeit und möchte dafür mit Ähnlichkeitswerten arbeiten. Sprich, wie ähnlich sind sich die Werte von Dateineintrag 1 und 2 (bei mir archäologische Fundorte).

Weist Fundort A z.B. den Wert 0,5 auf, Fundort B 0,9, so beträgt die Ähnlichkeit beider Fundorte 0,6(= Σ(1 - Differenzwert zwischen 0,9 und 0,5)).

Als Berechnungswerkzeug habe ich mich für Excel entschieden, da es mir benutzerfreundlicher als andere Tools vorkommt.

Auf einem weiteren Tabellenblatt habe ich also die Adjazenzmatrix angelegt und mir folgende Formel für die erste Abfrage in Zelle B2 überlegt (bei dem Fundort A mit Fundort A verglichen wird):

=WENN(ISTZAHL(Rohdatenliste!$B2);WENN(ISTZAHL(Rohdatenliste!$B$2);1-ABS(Rohdatenliste!$B$2-Rohdatenliste!$B2);"");WENN("FALSCH";"";""))

"Rohdatenliste" entspicht hier meiner Liste mit den einzelnen Werten eines jeden Fundortes.
Es klappt auch alles wunderbar, allerdings mit einem riesigen Haken. Ich muss einige Bezüge in der nächsten Spalte (wenn Fundort A mit B verglichen werden soll) immer händisch aktualisieren. Hier also je das "Rohdatenliste!$B2" zu "Rohdatenliste!$B3" in der folgenden Spalte.

Die so gewonnen Werte werden in anderen Tabellenblättern (auf Adjazenzmatrixbasis) mit anderen Formeln weiterverarbeitet - aber auch dort müssen einzelne Bezüge je folgender Spalte händisch geändert werden.

Ich würde mich wirklich freuen, wenn es da eine Lösung gibt, auf die ich einfach noch nicht gestoßen bin... das würde mir eine menge (wohl unnötige) Arbeit ersparen.

Viele Grüße und schonmal besten Dank!

PS: ich habe mal ein Beispieltabellendokument mit Fantasiewerten mit hochgeladen, in dem meine Art der Tabellenerstellung vielleicht anschaulicher deutlich wird.

 

Hola,

hier mal für die Tabelle FG-TYP 5:
=WENN(SVERWEIS($A2;Rohdatenliste!$A$1:$F$26;VERGLEICH($A$1;Rohdatenliste!$A$1:$F$1;0);0)="";"";1-ABS(SVERWEIS($A2;Rohdatenliste!$A$2:$F$26;VERGLEICH($A$1;Rohdatenliste!$A$1:$F$1;0);0)-SVERWEIS(B$1;Rohdatenliste!$A$2:$F$26;VERGLEICH($A$1;Rohdatenliste!$A$1:$F$1;0);0)))
Die Formel in B2 einfügen und nach rechts und nach unten ziehen.
Gruß,
steve1da

 

Danke! Leider gibt es jetzt das Problem, dass auch Werte verglichen werden, bei denen eine von beiden Ursprungszellen einen NULL-Wert hat. Dies soll eigentlich (aus der archäologischen) Logik her vermieden werden.

 

So?=WENN(UND(SVERWEIS($A2;Rohdatenliste!$A$1:$F$26;VERGLEICH($A$1;Rohdatenliste!$A$1:$F$1;0);0)="";SVERWEIS(C$1;Rohdatenliste!$A$2:$F$26;VERGLEICH($A$1;Rohdatenliste!$A$1:$F$1;0);0)="");"";1-ABS(SVERWEIS($A2;Rohdatenliste!$A$2:$F$26;VERGLEICH($A$1;Rohdatenliste!$A$1:$F$1;0);0)-SVERWEIS(C$1;Rohdatenliste!$A$2:$F$26;VERGLEICH($A$1;Rohdatenliste!$A$1:$F$1;0);0)))

 

Leider verfälscht das auch wieder die Werte... und um ehrlich zu sein, steige ich bei den vorgeschlagenen Formeln nicht durch, wie hierdurch bei bestimmten Bezügen automatisch eine Zeilennummer weitergesprungen wird, wenn sie auf die rechts stehende Spalte kopiert wird. Tut mir leid... das will mir gerade irgendwie nicht in den Kopf.

 

Hola,

neuer Versuch:
=WENN(ODER(SVERWEIS($A2;Rohdatenliste!$A$1:$F$26;VERGLEICH($A$1;Rohdatenliste!$A$1:$F$1;0);0)="";SVERWEIS(B$1;Rohdatenliste!$A$2:$F$26;VERGLEICH($A$1;Rohdatenliste!$A$1:$F$1;0);0)="");"";1-ABS(SVERWEIS($A2;Rohdatenliste!$A$2:$F$26;VERGLEICH($A$1;Rohdatenliste!$A$1:$F$1;0);0)-SVERWEIS(B$1;Rohdatenliste!$A$2:$F$26;VERGLEICH($A$1;Rohdatenliste!$A$1:$F$1;0);0)))
Somit erhalte ich im Blatt FG Typ 5 die gleichen Ergebnisse wie du.

Gruß,
steve1da

 

Es klappt wunderbar! Ganz herzlichen Dank! Da ich mich nun auch mit den SVERWEIS und VERGLEICH-Funktionen etwas auseinandergesetzt habe, stellt es kein Buch mit sieben Siegeln mehr für mich dar. Ein gutes Gefühl. :)