Hallo Zusammen,
Ich versuche gerade Flüge zu analysieren. Nun bin ich an den Punkt angelangt, wo ich die Anzahl Flüge pro Woche auf einer Strecke auswerten möchte.
Ich habe dazu 9 Spalten. Die Erste beinhaltet das Von-Datum (z.B. 1.1.2009). Die Zweite das Bis-Datum (31.1.2009) und danach für jeden Wochentag eine Spalte.
Nun habe ich zwei Fragen: Gibt es eine Formel, mit welcher ich Excel sagen kann, dass es falls es einen Eintrag in einer Zelle hat, diese Zelle als 1 zählen soll?
- Wie kann ich die Zeitdaten danach mit den Tagesdaten verknüpfen um so den Jahresschnitt herauszukriegen?
Ich schreibe euch unten kurz ein Beispiel, wie es momentan Aussieht und wie ich es möchte.
Von Bis d1 d2 d3 d4 d5 d6 d7
1.1.09 31.1.09 1 3 5
1.2.09 5.2.09 4
5.2.09 28.2.09 1 3 5
1.3.09 1.6.09 1 2 3 4 5 6 7
2.6.09 31.7.09 1 2 3 5 6 7
1.8.09 …
Wie oben erwähnt, möchte ich nun dass Excel erkennt ob in den Zellen d1-d7 etwas drin steht und diesen Wert dann als 1 Zählt. Danach möchte ich den Jahresdurchschnitt ausrechnen indem ich d1-d7 mit Von Bis verknüpfe.
Kann mir jemand helfen wie ich das machen soll???
Vielen Dank für eure Hilfe!
Pat
Von Bis d1 d2 d3 d4 d5 d6 d7
1.1.09 31.1.09 1 3 5
1.2.09 5.2.09 4
5.2.09 28.2.09 1 3 5
1.3.09 1.6.09 1 2 3 4 5 6 7
2.6.09 31.7.09 1 2 3 5 6 7
1.8.09 …
Sorry für die schlechte Darstellung der Tabelle… Sie sollte so aussehen:
Von______ Bis______ d1__d2__d3__d4__d5__d6__d7
1.1.09__31.1.09______1_______3_______5
1.2.09__5.2.09 _________________4
5.2.09__28.2.09______1_______3_______5
1.3.09__1.6.09_______1___2___3__4____5___6___7
2.6.09__31.7.09______1___2___3_______5___6___7
1.8.09 …
Moin,
Nun habe ich zwei Fragen: Gibt es eine Formel, mit welcher ich
Excel sagen kann, dass es falls es einen Eintrag in einer
Zelle hat, diese Zelle als 1 zählen soll?
Ja. Die Funktion heißt ANZAHL2
- Wie kann ich die Zeitdaten danach mit den Tagesdaten
verknüpfen um so den Jahresschnitt herauszukriegen?
Ich schreibe euch unten kurz ein Beispiel, wie es momentan
Aussieht und wie ich es möchte.
Wenn du das -Tag benutzt, siehrt die Tabelle so aus, wie du das möchtest:
Von Bis d1 d2 d3 d4 d5 d6 d7
1.1.09 31.1.09 1 3 5
1.2.09 5.2.09 4
5.2.09 28.2.09 1 3 5
1.3.09 1.6.09 1 2 3 4 5 6 7
2.6.09 31.7.09 1 2 3 5 6 7
1.8.09 ...
Danach möchte ich den Jahresdurchschnitt ausrechnen
indem ich d1-d7 mit Von Bis verknüpfe.
Dann spiele mal mit SUMMEWENN
Gruß
Kubi
Von Bis d1 d2 d3 d4 d5 d6 d7
1.1.09 31.1.09 1 3 5
1.2.09 5.2.09 4
5.2.09 28.2.09 1 3 5
1.3.09 1.6.09 1 2 3 4 5 6 7
2.6.09 31.7.09 1 2 3 5 6 7
1.8.09 …
Hi Kubi,
Vielen Dank für deine Antwort.
Ich habe jedoch ein Problem mit SUMMEWENN.
Ich kann Von/Bis und d1-d7 nicht verbinden damit es ein sofortiges resultat gibt.
Evtl. habe ich mich unverständlich ausgedrückt und du mich falsch verstanden oder ich krieg es einfach nicht hin.
Mein Ziel ist es den Jahresdurchschnitt der Flugtage pro woche (d1-d7) herauszukriegen. Da dieser jedoch z.T. wöchentlich wechselt und ich ca. 5000 Flugnummern zum analysieren habe würde das manuell Wochen dauern.
Erledigt die Formel SUMMEWENN mein Problem? Falls ja, wie muss ich das eingeben?
Falls nein: Gibt es eine Formel die den nach Zeitperiode gewichteten Mittelwert von jeder Zeile berechnet und danach den gesamt mittelwert eines Jahres berechnet?
Vielen Dank für deine Antwort!
Von Bis d1 d2 d3 d4 d5 d6 d7
1.1.09 31.1.09 1 3 5
1.2.09 5.2.09 4
5.2.09 28.2.09 1 3 5
1.3.09 1.6.09 1 2 3 4 5 6 7
2.6.09 31.7.09 1 2 3 5 6 7
Hallo Pat,
ich verstehe einfach nicht, was Du willst.
-
In d1 steht immer eine 1, in d2 immer eine 2 etc. Das sieht mir eher nach einem Index aus. Genausogut könnte in jeder Zelle ein X drinstehen, oder?
-
Die Daten in von/bis zeigen keine Wochen, sondern Monate (manchmal mehrere). Folglich können die Spalten d1:d7 keine Wochentage sein. Was sind sie dann?
Mein Ziel ist es den Jahresdurchschnitt der Flugtage pro woche (d1-d7) herauszukriegen. Da dieser jedoch z.T. wöchentlich wechselt znd ich ca. 5000 Flugnummern zum analysieren habe würde das manuell Wochen dauern.
Wo ist denn die Flugnummer in Deinem Beispiel? Mir scheint, Du müßtest die Tabelle neu aufbauen: von links nach rechts
Flugnummer | von | bis | Daten | Anzahl Flüge | Differenz bis-von | Quotient
Wenn Du die Anzahl der Tage hast, kannst Du eine Näherung an einen Wochendurchschnitt erreichen, wenn Du die Differenz bis-von durch sieben teilst. Reicht Dir das oder willst Du es wirklich kalenderwochengenau?
Am besten ist, Du lädst mal eine Beispielmappe hoch.
Gruß
Markus
Von Bis d1 d2 d3 d4 d5 d6 d7
1.1.09 31.1.09 1 3 5
1.2.09 5.2.09 4
5.2.09 28.2.09 1 3 5
1.3.09 1.6.09 1 2 3 4 5 6 7
2.6.09 31.7.09 1 2 3 5 6 7
Hallo Pat,
Hallo Markus
ich verstehe einfach nicht, was Du willst.
Verständlicherweise verstehst du mich nicht da es extrem schwer ist dies hier zu erklären.
- In d1 steht immer eine 1, in d2 immer eine 2 etc. Das sieht
mir eher nach einem Index aus. Genausogut könnte in jeder
Zelle ein X drinstehen, oder?
Richtig… es kommt nicht darauf an, was in diesen Zellen steht. Die Daten, welche ich gekriegt habe, sind 1-7. Sie stehen jedoch für Wochentage d.h 1 steht für Montag, 2 für Dienstag, usw.
- Die Daten in von/bis zeigen keine Wochen, sondern Monate
(manchmal mehrere). Folglich können die Spalten d1:d7 keine
Wochentage sein. Was sind sie dann?
Also… versuche es mal verständlich zu erklären. Die Daten in den Von / Bis spalten, stehen für die Zeitspanne in welchem d1-d7 gilt. d.h von 1.1 -31.1 fliegt dieser Flug 3x pro woche, an Tag 1, 3, 5.
Vom 1.2. - 5.2. fliegt er nur am 4 tag. Die oben gepostete Tabelle ist nur ein Beispiel, es sind keine wirklichen Daten.
Falls du willst kann ich dir jedoch einen Ausschnitt des ca. 45000 Zeilen grossen Dokumentes schicken.
Wo ist denn die Flugnummer in Deinem Beispiel? Mir scheint, Du
müßtest die Tabelle neu aufbauen: von links nach rechts
Flugnummer | von | bis | Daten | Anzahl Flüge | Differenz
bis-von | Quotient
Die Tabelle steht schon und besteht aus weit mehr Daten als nur die oben geposteten. Da ich mich jedoch momentan auf die Anzal Flüge pro Woche von A nach B konzentrieren möchte, habe ich diese Auswahl gewählt.
Am besten ist, Du lädst mal eine Beispielmappe hoch.
Kannst du mir erklären, wie ich das mache?
Vielen Dank.
Gruss,
Pat
Die Tabelle (einen Ausschnitt der Orginaltabelle)findest du hier:
http://www.file-upload.net/download-2741904/Test1.xl…
Und nun versuche ich es nochmals mit der Terminologie dieses Files zu erklären.
Das Dokument im Link beinhaltet verschiedene Informationen zu Flügen. Im Beispiel gibt es die Spalten: „carrier1“ für das Airlinekürzel, „carr1name“ für den Airlinenamen, „flightno1“ für die Flugnummer, „departairport“ für Abflugsort
„arrairport“ für Ankunftsort, „lcdeptime“ Abflugszeit lokal, „localarrtme“ lokale Ankunftszeit.
Die wirklich Wichtigen Spalten sind jedoch die „efffrom“ (von), „efffto“ (bis) und die lcdysofop 1-7, welche für je einen Wochentag stehen.
Die Tabelle ist wie folgt zu lesen: Jetstar Flug 37 von Sydney nach Bali fliegt vom 28.10.07 -31.10.07 an Tag 1, 3, 4, 7 (sprich Montag, Mittwoch, Freitag und Sonntag)
vom 25.3.07 - 29.4.07 jedoch nur Freitag und Sonntag.
Mein Ziel ist es nun die Durchschnittliche Anzahl Flüge pro Jahr herzuleitenl. Da ich vier Dokumente mit je ca. 60000 Zeilen habe und darin mehrere Tausend Flugnummern enthalten sind, wäre ich froh, wenn excel eine Formel hätte um mir diese Arbeit zu erleichtern.
Vielen Dank für eure Hilfe!
Hallo Reinhard,
Vielen Dank für deinen Tip, die Tabelle ist nun raufgeladen.
gruss Pat
http://www.file-upload.net/download-2741904/Test1.xl…
Die wirklich Wichtigen Spalten sind jedoch die „efffrom“
(von), „efffto“ (bis) und die lcdysofop 1-7, welche für je
einen Wochentag stehen.
Mein Ziel ist es nun die Durchschnittliche Anzahl Flüge pro
Jahr herzuleitenl. Da ich vier Dokumente mit je ca. 60000
Zeilen habe und darin mehrere Tausend Flugnummern enthalten
sind, wäre ich froh, wenn excel eine Formel hätte um mir diese
Arbeit zu erleichtern.
Hallo Pat,
müssen die Leerzeichen in den „leeren“ Zellen sein? Leere zellen einfach leer lassen geht nicht?
ich bin (auch) am Rumraten wie du das meinst, meinst du so etwas:
Tabellenblatt: [Mappe1]!Tabelle1
│ Q │ R │ S │ T │ U │ V │ W │ X │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
1 │ Tage │ l1 │ l2 │ l3 │ l4 │ l5 │ l6 │ l7 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
2 │ 4 │ 4 │ 0 │ 4 │ 0 │ 4 │ 0 │ 4 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
3 │ 29 │ 29 │ 0 │ 29 │ 0 │ 29 │ 0 │ 29 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
4 │ 30 │ 30 │ 0 │ 30 │ 0 │ 30 │ 0 │ 30 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
5 │ 36 │ 0 │ 0 │ 0 │ 0 │ 36 │ 0 │ 36 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
6 │ 66 │ 0 │ 0 │ 0 │ 0 │ 66 │ 0 │ 66 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
7 │ 3 │ 0 │ 0 │ 0 │ 0 │ 3 │ 0 │ 3 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
8 │ 29 │ 0 │ 0 │ 0 │ 0 │ 29 │ 0 │ 29 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
9 │ 29 │ 0 │ 0 │ 0 │ 0 │ 29 │ 0 │ 29 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
10 │ 22 │ 0 │ 0 │ 0 │ 0 │ 22 │ 0 │ 22 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
11 │ 12 │ 0 │ 0 │ 12 │ 0 │ 12 │ 0 │ 12 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
12 │ 24 │ 0 │ 0 │ 0 │ 0 │ 24 │ 0 │ 24 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
13 │ 24 │ 0 │ 0 │ 0 │ 0 │ 24 │ 0 │ 24 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
14 │ 22 │ 0 │ 0 │ 0 │ 0 │ 22 │ 0 │ 22 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
15 │ │ 0,17 │ 0,00 │ 0,21 │ 0,00 │ 0,90 │ 0,00 │ 0,90 │
───┴──────┴──────┴──────┴──────┴──────┴──────┴──────┴──────┘
Benutzte Formeln:
Q2 : =DATEDIF($H2;$I2;"d")+1
Q3 : =DATEDIF($H3;$I3;"d")+1
Q4 : =DATEDIF($H4;$I4;"d")+1
usw. in Q
R2 : =$Q2\*(J2" ")
R3 : =$Q3\*(J3" ")
R4 : =$Q4\*(J4" ")
usw. bis R14
R15: =SUMME(R2:R14)/365
S2 : =$Q2\*(K2" ")
S3 : =$Q3\*(K3" ")
S4 : =$Q4\*(K4" ")
usw. bis S14
S15: =SUMME(S2:S14)/365
T2 : =$Q2\*(L2" ")
T3 : =$Q3\*(L3" ")
T4 : =$Q4\*(L4" ")
usw. bis T14
T15: =SUMME(T2:T14)/365
U2 : =$Q2\*(M2" ")
U3 : =$Q3\*(M3" ")
U4 : =$Q4\*(M4" ")
usw. bis U14
U15: =SUMME(U2:U14)/365
V2 : =$Q2\*(N2" ")
V3 : =$Q3\*(N3" ")
V4 : =$Q4\*(N4" ")
usw. bis V14
V15: =SUMME(V2:V14)/365
W2 : =$Q2\*(O2" ")
W3 : =$Q3\*(O3" ")
W4 : =$Q4\*(O4" ")
usw. bis W14
W15: =SUMME(W2:W14)/365
X2 : =$Q2\*(P2" ")
X3 : =$Q3\*(P3" ")
X4 : =$Q4\*(P4" ")
usw. bis X14
X15: =SUMME(X2:X14)/365
Zahlenformate der Zellen im gewählten Bereich:
Q1:X1
haben das Zahlenformat: Standard
Q2:Q15,R2:R14,S2:S14,T2:T14,U2:U14,V2:V14,W2:W14,X2:X14
haben das Zahlenformat: 0
R15:X15
haben das Zahlenformat: 0,00
Tabellendarstellung erreicht mit dem Code in FAQ:2363
Gruß
Reinhard
Hallo Richard,
müssen die Leerzeichen in den „leeren“ Zellen sein? Leere
zellen einfach leer lassen geht nicht?
Die Leerzeichen müssen nicht sein, nein. die sind in dieser Version noch drin, habe Sie jedoch in andern rausgenommen.
Tabellenblatt: [Mappe1]!Tabelle1
│ Q │ R │ S │ T │ U │ V │ W │ X │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
1 │ Tage │ l1 │ l2 │ l3 │ l4 │ l5 │ l6 │ l7 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
2 │ 4 │ 4 │ 0 │ 4 │ 0 │ 4 │ 0 │ 4 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
3 │ 29 │ 29 │ 0 │ 29 │ 0 │ 29 │ 0 │ 29 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
4 │ 30 │ 30 │ 0 │ 30 │ 0 │ 30 │ 0 │ 30 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
5 │ 36 │ 0 │ 0 │ 0 │ 0 │ 36 │ 0 │ 36 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
6 │ 66 │ 0 │ 0 │ 0 │ 0 │ 66 │ 0 │ 66 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
7 │ 3 │ 0 │ 0 │ 0 │ 0 │ 3 │ 0 │ 3 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
8 │ 29 │ 0 │ 0 │ 0 │ 0 │ 29 │ 0 │ 29 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
9 │ 29 │ 0 │ 0 │ 0 │ 0 │ 29 │ 0 │ 29 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
10 │ 22 │ 0 │ 0 │ 0 │ 0 │ 22 │ 0 │ 22 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
11 │ 12 │ 0 │ 0 │ 12 │ 0 │ 12 │ 0 │ 12 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
12 │ 24 │ 0 │ 0 │ 0 │ 0 │ 24 │ 0 │ 24 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
13 │ 24 │ 0 │ 0 │ 0 │ 0 │ 24 │ 0 │ 24 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
14 │ 22 │ 0 │ 0 │ 0 │ 0 │ 22 │ 0 │ 22 │
───┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
15 │ │ 0,17 │ 0,00 │ 0,21 │ 0,00 │ 0,90 │ 0,00 │ 0,90 │
───┴──────┴──────┴──────┴──────┴──────┴──────┴──────┴──────┘
Grundsätzlich kommt es dem was ich möchte ziemlich nahe. Wie du in meinem Beispieldokument sehen kannst sind die efffrom - effto Zeitspannen unterschiedlich. Deswegen muss ich zuerst diese alle austrechnen (was in deiner Tabelle zu der Spalte Tage (Q) führen würde. Soweit ist also alles gut.
Danach müsste ich die Summe von Rn-Xn haben (alle Zellen mit Eintrag als 1 gerechnet) um die Anzahl Flüge pro Woche in dieser Zeitspanne herauszufinden.
Wenn ich dies für alle Zeilen einer Flugnummer gemacht habe, müsste ich dann den Durchschnitt aus dem Resultat aus der ersten Berechnung (Q) mal dem Resultat der zweiten Berechnung pro Zeile
sprich q1 * Summe r1:x1 + q2* Summe r2:x2 + qn * Summe rn:xn / 365
Somit hätte ich die durchschnittliche Anzahl Flüge pro Woche.
R2 : =$Q2*(J2" „)
R3 : =$Q3*(J3“ „)
R4 : =$Q4*(J4“ ")
usw. bis R14
Ich verstehe nicht ganz, was du hier genau gemacht hast, kannst du mir diesen Schritt auch noch erklären?
Vielen Dank.
Gruss Pat