Office: Wegeketten herausbekommen

Werte Excel-Experten,

Ich habe für meine DA einen Fragebogen bekommen, den ich gerne mit Excel auswerten möchte. Dabei wurden u.a. die zurückgelegten Wege von Personen von einer Quelle zu einem Zielort für einen Tag erfasst. Die Quellen und die einzelnen Ziele sind als Zahlen zwischen 1 bis 29 codiert.

In Spalte A steht eine Personennummer, die bestimmte Merkmale enthält (erstmal nicht so wichtig).

In der Spalte B steht die Quelle (=Ausgangsort) als Nummer z.B. 28 als Code für die Wohnung.
Dann steht in Spalte C das erste Ziel des Tages, in Spalte D das nächste - also das zweite Ziel des Tages,... bis zum maximal 10ten Ziel des Tages in Spalte L.


Das könnte dann für eine Person für einen Tag z.B. so aussehen:
Person1: Wohnung-Arbeitsort-Kneipe-Wohnung (bzw. 28-1-10-28)
Person2: Wohnung-Schule-Wohnung-Spielplatz-Wohnung (28-2-28-11-28)
Person3: Wohnung-Kindergarten-Einkaufen-Wohnung-Kindergarten-Wohnung (28-3-4-28-3-28)
Person4: Wohnung-Arbeitsort-Wohnung (28-1-28)

In diesem einfachen Fall würden von den insgesamt 14 Wegen (jeder "-" steht für einen Weg) genau 12 Wege mit der Wohnung zu tun haben. Im Ergebnis sind dann 86% "heimgebundene" Wege am Tag. Ich suche also die Gesamtwegeanzahl und den Anteil der "heimgebundenen" Wege für meine Daten.



Außerdem möchte ich in meiner Tabelle noch wissen, wieviele unterschiedliche Wegearten es gibt und wie oft diese Wegearten jeweils vorkommen:

Wegart1: Wohnung-Arbeitsort (28-1) = 2 mal
Wegart2: Wohnung-Schule (28-2) =1 mal
Wegart3: Wohnung-Kindergarten (28-3) = 2 mal
Wegart4: Wohnung-Spielplatz (28-11) = 1 mal
Wegart5: Arbeitsort-Kneipe (1-10) = 1 mal
Wegart6: Arbeitsort-Wohnung (1-28) = 1 mal
Wegart7: Kneipe-Wohnung (10-28) = 1 mal
usw.

Könnte mir einer erklären wie ich das formeltechnisch mit Excel bearbeiten könnte?


LG, Mat

 

mir ist grad selber noch was eingefallen... vielleicht kann man die Sache vereinfachen, indem man alles in eine 29*29 Matrix (oder mehrer) presst...mal überlegen...

 

Hallo!

Check mal die angehängte Datei, ob es so passt.

 

Ok vielen vielen dank! Das sieht super aus :-D ... aber gibt es auch die Möglichkeit, sich die Anzahl der Wegearten anstelle der Listenform gleich in einer Matrix ausgeben zu lassen? (siehe Anhang)

 

Klar!

Das wäre dann diese Formel in B24:

=SUMMENPRODUKT(N($B$2:$Y$5&"#"&$C$2:$Z$5=$A24&"#"&B$23))

nach unten und nach rechts kopieren.

 

Perfekt! Das ist es! Mich würde nur noch interessieren, wofür immer das "N" und die "#" in der Formel steht und wie die Formel überhaupt arbeitet...

Ist es denn später auch noch möglich deine SUMMENPRODUKT-Formel nach mehreren Kriterien arbeiten zu lassen z.B. die Matrix nur nach bestimmten Personen der Spalte A (Person1=Arbeiter, Person2=Kleinkinder) und z.B. nach Verkehrsmitteln der Spalte X (Fuß, Rad, Pkw) zusammenstellen zu lassen? Ich hätte da an die klassische "SUMMENPRODUKT(($A$2:$A$5="Person1")*($X$2:$X$5="Fuß"))" - Formel gedacht...

 

Hallo!

Die Funktion N wandelt WAHR und FALSCH in die Zahlenwerte 1 und 0, so dass sie summiert werden können.
Die Raute (#) habe ich als Trenner benutzt, da ich die Werte ja verkette:
="Hallo"&"Du"
ergibt "HalloDu"
Bei dir handelt es sich um Zahlen, das macht die Sache komplizierter, denn
1&21
ergibt dasselbe wie
12&1
nämlich
121

Deshalb der Trenner
1&"#"&21 ergibt 1#21
12&"#"&1 ergibt 12#1

Mit deiner Vermutung zum SUMMENPRODUKT liegst du richtig:

=SUMMENPRODUKT(($B$2:$Y$5&"#"&$C$2:$Z$5=$A24&"#"&B$23)*($A$2:$A$5="Person1"))
gibt nur die Ergebnisse für Person1.

Hier kann die Funktion N weggelassen werden, da die Multiplikation bereits die Wahrheitswerte in Zahlen umwandelt.

 

Ausgezeichnet... das funktioniert genau nach meinem Geschmack und das mit dem Wahrheitswert und der Raute finde ich auch sehr nützlich... nochmals vielen Dank!

 

Hallo,

tut mir leid, dass ich schon wieder vor einem mir unlösbaren Problemchen stehe:

ich würde gerne bei meinem o.g. Fragebogen noch etwas weiter differenzieren, nämlich nach Verkehrsmittel und Entfernungsklassen. Ich habe zum besseren Verständnis eine Datei in den Anhang getan.

Die eigentliche Struktur der Daten des Fragebogens findet sich im Arbeitsmappe "Fragebogen".

Die Auswertung, wie ich sie mir gerne wünsche befindet sich in der Arbeitsmappe "Verkehrsmittel und Entfernung".

Dort soll in den Feldern D6 bis D22 eine Formel stehen, die mir ausgibt, wie oft jeweils eine der häufigsten 17 Wegeketten (siehe linke Tabelle) innerhalb des eingegebenen Entfernungsbereichs in F2 und H2 mit dem Verkehrsmittel in B2 durchgeführt werden.

Hab schonmal mit meiner geliebten Summenproduktformel rumgespielt, aber ich kam zu keiner brauchbaren Lösung. Ist sowas überhaupt formeltechnisch lösbar? :roll:

LG. Mat

 

Hallo!

Der Tabellenaufbau deines Fragebogens ist natürlich denkbar ungeeignet für eine Auswertung dieser Art.
Ich habe daher die entscheidenden Daten im Blatt "Hilfe" neu strukturiert.
Damit hält sich die Formellänge erstaunlicherweise sogar sehr in Grenzen.

Check mal, ob es so passt.
(Falls nicht, dann hast du auf jeden Fall was zum weiterbasteln...)

 

Erstmal vielen vielen dank für deine erstklassige Lösung, schatzi. Das funktioniert so super mit der SUMMENPRODUKT-Formel...
Das der Fragebogen nicht so toll aufgebaut ist, war mir schon irgendwie bewusst, daher hatte ich den schon beim ersten Bsp. nur auf die nötigsten Spalten verkürzt...

Zurück zur Auswertung:
Ich würde gerne noch im Feld E20 jeweils alle Wege von "28" zu den übrig gebliebenen Wegen gleich zusammenfassen? Ebenso im Feld E21 alle übrig gebliebenen Wege nach "28" zusammenfassen?
Im Feld E22 sollen noch alle sonstigen Wegebeziehungen zusammengefasst werden, die nichts mit "28" zu tun haben.

Wenn das nicht ginge, müsste ich ansonsten über 600 Zeilen untereinander schreiben und manuell per Summe zusammenfassen?

 

Hallo!

Ungetestet:

Diese Abfrage müsste dir alle Wege 28 --> X zählen:
=SUMMENPRODUKT((Hilfe!B$3:F$16=28)*(Hilfe!C$3:G$16>0)*(...
Davon müssen dann noch die bereits explizit aufgeführten abgezogen werden.

Alle Wege X --> 28 sollte dies bringen:
=SUMMENPRODUKT((Hilfe!B$3:F$16>0)*(Hilfe!C$3:G$16=28)*(...
(und ebenfalls minus die bereits genannten)

Die Gesamtzahl der Wege müsste dies sein:

=ZÄHLENWENN(Hilfe!3:16;">0")-ZEILEN(3:16)

 

Nochmals vielen Dank.

Die ersten beiden Formeln klappen perfekt... bei der 3. Formel mit Zählenwenn würde ich stattdessen lieber alle Wege ungleich von 28 darstellen lassen, damit ich als Filter eben auch die Entfernung und wieder das Verkehrsmittel berücksichtigen kann wie bei den Summenproduktformeln.
Ginge das?

 

Stimmt, das hatte ich nicht bedacht...

Aber das müsste doch so gehen, oder nicht?

=SUMMENPRODUKT((Hilfe!B$3:F$16<>28)*(Hilfe!C$3:G$16<>28)*(Hilfe!B$3:F$16>0)*(Hilfe!C$3:G$16>0)*(...

Vielleicht sind die Abfragen >0 gar nicht nötig; musst du mal ausprobieren.

 

Okay klappt bestens... jetzt lasse ich dich lieber mal in Ruhe, du hast mir wirklich schon sehr geholfen, thx :-)

Gute Nacht^^