Wegeketten herausbekommen :: Office-Hilfe.com - Wir lösen Ihre Probleme mit Microsoft Office

Einloggen, um private Nachrichten zu lesen

Downloads

[Excel 2007] Wegeketten herausbekommen

Office-Hilfe.com - Wir lösen Ihre Probleme mit Microsoft Office Foren-Übersicht » Microsoft Excel Hilfe

Gehe zu Seite 1, 2 Weiter

Autor

Nachricht

MatStorm
Newbie
Newbie

Anmeldedatum: 23.01.2008
Beiträge: 43

Verfasst am: 19.02.2008, 19:48

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

MatStorm
Newbie
Newbie

Anmeldedatum: 23.01.2008
Beiträge: 43

Verfasst am: 19.02.2008, 20:37

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...

schatzi
Moderator

Anmeldedatum: 09.12.2006
Beiträge: 5729

Verfasst am: 19.02.2008, 20:43

Hallo!

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

Mappe1.xls

Beschreibung:

Download

Dateiname:

Mappe1.xls

Dateigröße:

24 KB

Heruntergeladen:

26 mal

_________________

Viele Grüße vom Schatzi
------------------------
Ich bin nur noch sporadisch erreichbar!
Bitte hofft nicht auf eine schnelle Beantwortung einer Rückfrage meinerseits!
Jeder andere Helfer darf Rückfragen gerne übernehmen und hilft sicher gerne weiter!

MatStorm
Newbie
Newbie

Anmeldedatum: 23.01.2008
Beiträge: 43

Verfasst am: 19.02.2008, 21:05

Ok vielen vielen dank! Das sieht super aus Very Happy

... aber gibt es auch die Möglichkeit, sich die Anzahl der Wegearten anstelle der Listenform gleich in einer Matrix ausgeben zu lassen? (siehe Anhang)

Matrixdarstellung.xls

Beschreibung:

Download

Dateiname:

Matrixdarstellung.xls

Dateigröße:

19 KB

Heruntergeladen:

24 mal

schatzi
Moderator

Anmeldedatum: 09.12.2006
Beiträge: 5729

Verfasst am: 19.02.2008, 21:11

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.

MatStorm
Newbie
Newbie

Anmeldedatum: 23.01.2008
Beiträge: 43

Verfasst am: 19.02.2008, 22:30

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...

schatzi
Moderator

Anmeldedatum: 09.12.2006
Beiträge: 5729

Verfasst am: 19.02.2008, 22:56

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.

MatStorm
Newbie
Newbie

Anmeldedatum: 23.01.2008
Beiträge: 43

Verfasst am: 19.02.2008, 23:15

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

MatStorm
Newbie
Newbie

Anmeldedatum: 23.01.2008
Beiträge: 43

Verfasst am: 27.02.2008, 18:38

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? Rolling Eyes

LG. Mat

Wegeketten_Verkehrsmittel_Entfernung.xls

Beschreibung:

Download

Dateiname:

Wegeketten_Verkehrsmittel_Entfernung.xls

Dateigröße:

31.5 KB

Heruntergeladen:

18 mal

schatzi
Moderator

Anmeldedatum: 09.12.2006
Beiträge: 5729

Verfasst am: 27.02.2008, 19:39

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...)

Wegeketten_Verkehrsmittel_Entfernung-1.xls

Beschreibung:

Download

Dateiname:

Wegeketten_Verkehrsmittel_Entfernung-1.xls

Dateigröße:

60 KB

Heruntergeladen:

31 mal

MatStorm
Newbie
Newbie

Anmeldedatum: 23.01.2008
Beiträge: 43

Verfasst am: 27.02.2008, 23:46

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?

schatzi
Moderator

Anmeldedatum: 09.12.2006
Beiträge: 5729

Verfasst am: 28.02.2008, 00:01

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)

MatStorm
Newbie
Newbie

Anmeldedatum: 23.01.2008
Beiträge: 43

Verfasst am: 28.02.2008, 00:32

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?

schatzi
Moderator

Anmeldedatum: 09.12.2006
Beiträge: 5729

Verfasst am: 28.02.2008, 00:41

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.

MatStorm
Newbie
Newbie

Anmeldedatum: 23.01.2008
Beiträge: 43

Verfasst am: 28.02.2008, 00:52

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

Gute Nacht^^

Office-Hilfe.com - Wir lösen Ihre Probleme mit Microsoft Office Foren-Übersicht » Microsoft Excel Hilfe

Gehe zu Seite 1, 2 Weiter

Gehe zu:

Du kannst keine Beiträge in dieses Forum schreiben.
Du kannst auf Beiträge in diesem Forum nicht antworten.
Du kannst deine Beiträge in diesem Forum nicht bearbeiten.
Du kannst deine Beiträge in diesem Forum nicht löschen.
Du kannst an Umfragen in diesem Forum nicht mitmachen.
Du kannst Dateien in diesem Forum posten
Du kannst Dateien in diesem Forum herunterladen

Powered by phpBB © 2001, 2002 phpBB Group :: FI Theme :: Alle Zeiten sind GMT + 1 Stunde
Deutsche Übersetzung von phpBB.de