Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.04.2024 00:49:19
Excel Sheet in einen Excel Kalender einfügen
20.04.2024 00:08:11
In Ordner nur das Speichern von Dateien erlauben
19.04.2024 21:26:15
mehrfache Texte finden
19.04.2024 19:41:34
Bei VBA Ausführung Anzeige einfrieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1724to1728
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1724to1728
1724to1728
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Konkordenz-Matrix über Zeitreihe berechnen

Konkordenz-Matrix über Zeitreihe berechnen
03.12.2019 19:22:32
Alex
Hallo zusammen,
ich würde gerne eine Konkordenz Matrix über eine Zeitreihe berechnen und kann das leider nicht "automatisieren". Bitte findet eine Beispieldatei anbei: https://www.herber.de/bbs/user/133570.xlsx
Wie zu sehen, versuche ich über verschiedene Reihen (A,B,C) und deren Zeiten (als Spalten dargestellt - t) immer rauszufinden, wenn die Einträge in den Zellen übereinstimmen und das in einer Matrix darzustellen. Dafür verwende ich folgende Formel : =(1/ANZAHL(Data!$B$1:$E$1))*((Data!$B$2*Data!B2+(1-Data!$B$2)*(1-Data!B2))+(Data!$C$2*Data!C2+(1-Data!$C$2)*(1-Data!C2))+(Data!$D$2*Data!D2+(1-Data!$D$2)*(1-Data!D2))+(Data!$E$2*Data!E2+(1-Data!$E$2)*(1-Data!E2)))
Nunmehr würde ich das aber gerne nicht händisch machen (wie in diesem Beispiel) sondern automatisch, da ich 500 Reihen habe und die Spalten (Zeit) auch länger ist als im Beispiel. Am Ende hätte ich gerne eine 500x500 Matrix, die dann symmetisch ist. Kann mir bitte jemand helfen? Leider ist es mir nicht möglich das zu automatisieren.
Im Voraus vielen Dank!
Beste Grüße
Alex

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
KONKORDANZ
03.12.2019 20:07:04
lupo1
1. Warum verwendest Du nicht existierende Begriffe?
2. Warum nervst Du uns mit "Data!"? Lass es weg!
=(1/ANZAHL($B$1:$E$1))*(
($B$2*B2+(1-$B$2)*(1-B2))+
($C$2*C2+(1-$C$2)*(1-C2))+
($D$2*D2+(1-$D$2)*(1-D2))+
($E$2*E2+(1-$E$2)*(1-E2)))
3. Was sollen die $? Der Sinn ist nicht zu erkennen.
4. Warum quadrierst Du nicht?
=(1/ANZAHL(B1:E1))*(
(B2^2+(1-B2)^2)+
(C2^2+(1-C2)^2)+
(D2^2+(1-D2)^2)+
(E2^2+(1-E2)^2))
5. Am Ende steht dann SUMMENPRODUKT:
=(1/ANZAHL(B1:E1))*SUMMENPRODUK(B2:E2^2+(1-B2:E2)^2)
6. Ich habe keine Ahnung von der Aussage und habe mich an Deiner Formel orientiert.
Anzeige
AW: KONKORDANZ
03.12.2019 20:24:53
Alex
Hallo @lupo1,
danke für deine Rückmeldung und entschuldige bitte den Schreibfehler.
Leider ist der von dir vorgeschlagene Weg nicht der den ich Suche, da man zwei verschiedene Reihen miteinander vergleichen und dann einen Index bilden muss. Hier die Formel im Original:
Userbild
Damit hilft mir deine Antwort leider nicht weiter. Trotzdem vielen lieben Dank für deine Mühe!
Beste Grüße
Alex
dann halt
03.12.2019 23:05:27
lupo1
=(1/ANZAHL(B$1:E$1))*SUMMENPRODUKT(B$1:E$1*B2:E2+(1-B$1:E$1)*(1-B2:E2))
die korrekten Vergleichsreihen bekommst Du selbst hin
Anzeige
AW: FRAGE IMMER NOCH OFFEN
03.12.2019 23:29:55
Alex
Hallo lupo1,
danke für deine Rückmeldung.
Leider ist es immer noch falsch. Erstens müsste die Summenproduktformel wie folgt geschrieben sein (=(1/ANZAHL(B$1:E$1))*SUMMENPRODUKT(B$2:E$2*B2:E2+(1-B$2:E$2)*(1-B2:E2))), da die erste Zeile die Zeitachse darstellt, und zweitens löst dein Vorschlag das Zweidimensionalitätsproblem nicht. Was du vorschlägst fixiert die Reihen, im Beispiel habe ich aber deutlich gemacht, dass es mir um den rollierenden Bezug geht, deshalb die Matrixzuordnung mit den 1sen auf der Spur der Matrix.
Ich würde mich weiterhin sehr über andere Vorschläge freuen!
Danke!
Beste Grüße
Alex
Anzeige
Niemand?
04.12.2019 14:29:34
Alex
Hallo zusammen,
könnte mir bitte jemand weiterhelfen?
Im Voraus vielen Dank!
Beste Grüße
Alex
AW: Gelöst
04.12.2019 18:07:57
Alex
Hab das Problem mit dem folgenden Code in Python gelöst:
data = pd.read_excel("...", index_col=[0], header_col=[0])
import time
start_time = time.time()
results = []
for i in range(0,len(data)):
for j in range(0,len(data)):
result_i =(1/len(data.columns))*(sum(data.iloc[i]*data.iloc[j]+(1-data.iloc[i])*(1-data. _
iloc[j])))
results += [result_i]
matrix = []
while results != []:
matrix.append(results[:1680])
results = results[1680:]
matrix_df = pd.DataFrame(matrix)

Anzeige
AW: Niemand?
04.12.2019 18:59:17
Matthias
Moin!
Du solltest ggf. nochmal erklären, was du genau brauchst. Theoretisch brauchst du ja nur das rechte obere Dreieck deiner Ergebnismatrix. Die Diagonale ist 1 und darunter ist es ja nur gespiegelt, da reicht ja ein Verweis. Meines Erachtes gebe es 2 Möglichkeiten:
1. mittels VBA die Formeln eintragen lassen
oder
2. mit Formeln. Da wäre wieder die Frage, ob du eine Formel haben willst, die du nach rechts bzw. nach untern ziehst. Dann könntest du das hier nutzen: 
=WENN(SPALTE(A$1)=ZEILE($A1);1;1/$H$1*SUMMENPRODUKT(INDEX($B$2:$E$4;ZEILE($A1);0)*INDEX($B$2:$E$4; SPALTE(A$1);0))+SUMMENPRODUKT(INDEX(1-$B$2:$E$4;ZEILE($A1);0)*INDEX(1-$B$2:$E$4;SPALTE(A$1);0)))

Damit werden aus der Matrix die entsprechenden Zeilen miteinander Multipliziert. Der Index richtet sich an Hand der Zeile bzw. Spalte aus. Durch das Ziehen der Formel ändert sich der Wert für Zeile und Spalte. Damit bist du flexibel und je nach Zeile/Spalte wird ein anderer INdex genuttz. Im Beispiel hat das dein Ergebnis ergeben.
VG
Anzeige

Links zu Excel-Dialogen

Frame

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige