Office: (Office 2013) String parsen und Teildaten aus anderer Tabelle importieren und addieren

Ich hoffe, ich kann meine Frage überhaupt verständlich formulieren...

Gegeben sei in Spalte A ein String ähnlich dem folgenden:

LEIB-LDPL-EDDF-EDFH-LEIB

Mit der Luftfahrt vertraute User werden erkennen, dass es sich hier um ICAO-Codes (sind immer vierstellig) von Flughäfen und somit um eine Flugstrecke handelt.

Des weiteren sei gegeben, dass sich in einem weiteren Arbeitsblatt derselben Arbeitsmappe die Großkreisdistanz ("Luftlinie") der Einzelstrecken befindet, also:

LEIB-LDPL (1234,14)
LDPL-EDDF (698,73)
EDDF-EDFH (93,91)
EDFH-LEIB (1316,83)

Sowie hunderte/tausende weiterer Einzelstrecken. Die Einzelstrecke in Spalte A, die Distanz in Spalte B des weiteren Arbeitsblatts.

Der Plan ist jetzt, für die Gesamtflugstrecke "LEIB-LDPL-EDDF-EDFH-LEIB" die Gesamtdistanz aus den Einzelstrecken des weiteren Arbeitsblatts auszulesen und in Spalte B des eigentlichen Arbeitsblatts zu addieren.

Alle meine Versuche mit SUMMEWENNS() sind bisher fehlgeschlagen und erscheinen mir auch viel zu kompliziert. Ich scheitere schon daran, dass es sich um eine beliebige Anzahl von Einzelflugstrecken handeln kann (im obigen Beispiel sind es 4, aber es können auch 1, 2 oder theoretisch auch beliebig viele sein).

Ich wäre schon für einen irgendwie brauchbaren Ansatz dankbar.

Gruß, Michael

:)

 

=SVERWEIS(TEIL(A1;1;9);Tabelle2!A:B;2;FALSCH)+WENN(LÄNGE(A1)>9;SVERWEIS(TEIL(A1;6;9);Tabelle2!A:B;2;FALSCH);0)+WENN(LÄNGE(A1)>14;SVERWEIS(TEIL(A1;11;9);Tabelle2!A:B;2;FALSCH);0)+WENN(LÄNGE(A1)>19;SVERWEIS(TEIL(A1;16;9);Tabelle2!A:B;2;FALSCH);0)

Und entsprechende Reserven für Flugverbindungen mit mehr Teilstrecken einfügen.

Oder Aber mit WENNFEHLER ohne Längenabfrage:

=SVERWEIS(TEIL(A1;1;9);Tabelle2!A:B;2;FALSCH)+WENNFEHLER(SVERWEIS(TEIL(A1;6;9);T abelle2!A:B;2;FALSCH);0)+WENNFEHLER(SVERWEIS(TEIL(A1;11;9);Tabelle2!A:B;2;FALSCH );0)+WENNFEHLER(SVERWEIS(TEIL(A1;16;9);Tabelle2!A:B;2;FALSCH);0)+WENNFEHLER(SVER WEIS(TEIL(A1;21;9);Tabelle2!A:B;2;FALSCH);0)+WENNFEHLER(SVERWEIS(TEIL(A1;26;9);T abelle2!A:B;2;FALSCH);0)

 

Oh, danke für die schnelle Antwort! Werde es in Ruhe testen, mir zu Gemüte führen und mich wieder zurückmelden.

 

F1: Flugstreckensumme
E1: Flugplan
A1[:A9999]: Flugstreckenbezeichnungen
B1[:B9999]: Flugstreckenentfernungen

a) mit Doppik, also:

EDDF-EDFH 93,91
EDDF-LDPL 698,73
EDFH-EDDF 93,91
EDFH-LEIB 1316,83
LDPL-EDDF 698,73
LDPL-LEIB 1234,14
LEIB-EDFH 1316,83
LEIB-LDPL 1234,14

F1: =SUMME((TEIL(E1;SPALTE(A1:Z1)*5-4;9)=A$1:A$9999)*B$1:B$9999)

b) ohne Doppik:

F1: =SUMME(((
TEIL(E1;SPALTE(A1:Z1)*5-4;9)=A$1:A$9999)+(
TEIL(E1;SPALTE(A1:Z1)*5+1;4)&"-"&
TEIL(E1;SPALTE(A1:Z1)*5-4;4)=A$1:A$9999))*B$1:B$9999)

 

Sieht genial aus, aber funktioniert so leider nicht (vermutlich verstehe ich die Formel auch nicht, aber das liegt an meinen dürftigen Excel-Kenntnissen).

Insbesondere "SPALTE(A1:Z1)" ist mir unklar, eigentlich spielen nur die Spalten A und B eine Rolle.

Wir haben zwei Arbeitsblätter:

1. Hier stehen in Spalte A ab Zeile 2 (wegen Überschrift) je Zeile die einzelnen "city pairs" (z.B. "LEIB-LDPL", "LDPL-EDDF" usw.) beliebig sortiert, und in Spalte B die jeweilige Distanz.
2. Hier stehen in Spalte A die kompletten Flugstrecken , also z.B. "LEIB-LDPL-EDDF-EDFH-LEIB", und in Spalte B soll die Summe der Distanzen aller Einzelstrecken addiert werden, die aus dem ersten Arbeitsblatt ausgelesen werden müssen.

Ich bin es mit der obigen Formal lediglich geschafft, die Distanz des jeweils ersten city pairs auszugeben, und auch nur dann, wenn es in beiden Arbeitsblättern zufällig in derselben Zeile steht.

Die von mir umgemodelte Formel sieht so aus:

=SUMME((TEIL(A1;SPALTE(Flugstrecken!A2:Z2)*5-4;9)=Flugstrecken!A$2:A$9999)*Flugstrecken!B$2:B$9999)

Aber auch dann, wenn ich wie von Dir vorgesehen alles in einem Arbeitsblatt teste, komme ich zu keinem anderen Resultat.

 

Das hingegen funktioniert, nur ist es eben auf die Anzahl der Einzelstrecken begrenzt, für die die Formel "gebaut" ist. Aber mehr als 10 sollten es in der Realität sowieso nie werden.

Damit komme ich erstmal weiter, danke!

Einen Sonderfall würde ich gerne noch behandeln: Wenn eine Einzelstrecke im Arbeitsblatt nicht vorhanden ist, wird #NV ausgegeben, das ist OK. Aber wenn statt des Bindestrichs z.B. in "LEIB-LDPL" ein Multiplikationssternchen "*" steht (also "LEIB*LDPL"), dann soll für diese Strecke Null addiert werden, auch wenn die Strecke gar nicht in Tabelle2 enthalten ist. Wie baue ich das am besten ein...?

 

Für billige Excels ist die Formel als Matrixformel abzuschließen. Das hatte ich als bekannt vorausgesetzt. Oder man nimmt die SVERWEIS-Formel; die geht doch auch.

Auf Salamitaktiken bei Problembeschreibungen hin allerdings lade ich meine freundlichen Mit-Antworter (nicht: Mitesser) zur weiteren Hilfe ein ...

 

Ah! Ich hatte keine geschweiften Klammern gesehen, deshalb bin ich als Einäugiger darauf nicht gekommen. Werde ich probieren.

EDIT: {=SUMME((TEIL(A1;SPALTE(Flugstrecken!A2:Z2)*5-4;9)=Flugstrecken!A$2:A$9999)*Flugstrecken!B$2:B$9999)} als Matrixformel funktioniert leider auch nicht (#WERT!).

Aber warum "SPALTE(A1:Z1)"?

War keine Taktik, ist mir erst später eingefallen, wie ich diesen Fall am liebsten behandeln will. Notfalls schreibe ich diese Strecken aber auch in die Tabelle als Datensatz, weil es - wenn - nur wenige Einzelfälle bei innerdeutschen Strecken betrifft (also wenn beide Flughäfen mit "ED" oder "ET" beginnen, aber auch dann eben nicht immer).

Aber falls doch jemand 'ne Idee hat, wie man es pauschal erschlagen kann... *wink.gif*

 

SPALTE(A1:Z1) für den 1.-26. Eintrag (oder eben nichts) Deiner ICAO-Kette.

 

Hmm. Die "Kette" steht als String in Spalte A:

Und in Spalte B kommt die Formel, die die Gesamtdistanz aus den Einzelstrecken des anderen Arbeitsblatts auslesen und addieren soll. Ab Spalte C steht gar nix mehr (oder irgendwas Beliebiges, was damit nichts zu tun hat).

 

Ich habe nicht gesagt, dass SPALTE(A1:Z1) auch nur das geringste mit A1:Z1 zu tun hat. Ich habe Dir erklärt, dass es für Eintrag 1 bis 26 steht. SPALTE() (und ZEILE() auch) ist der laufende Zähler in einer Tabellenkalkulationsformel, da man dort sonst kein Programmiersprachenschleifenäquivalent hat.

Außerdem hatte ich in meiner ersten Antwort geschrieben, wo genau etwas zu stehen hat.

 

Sorry, wenn ich mich ein wenig dämlich anstelle...

Aus irgendeinem Grund funktioniert diese Matrixformel jetzt plötzlich:

{=SUMME((TEIL($A1;SPALTE($A1:$Z1)*5-4;9)=Flugstrecken!$A$2:$A$9999)*Flugstrecken!$B$2:$B$9999)}

Leider addiert sie aber "0" statt #NV auszuwerfen, wenn eine der Teilstrecken (noch) nicht in der Tabelle "Flugstrecken" existieren sollte. Da ich sie nicht wirklich verstehe, werde ich das leider auch nicht lösen können.

Aber danke für die Hilfe, sehr interessant.

Bis auf weiteres habe ich die SVERWEIS-Formel jetzt so umgebaut und erweitert, dieses Ungetüm tut es für den Moment, auch wenn's sicher nicht sehr elegant ist - ausgelegt auf max. 10 Teilstrecken:

=WENN(LÄNGE($A1)>=9;WENN(TEIL($A1;5;1)="*";0;SVERWEIS(TEIL($A1;1;9);Flugstrecken!$A:$C;2;FALSCH));0)+WENN(LÄNGE($A1)>=14;WENN(TEIL($A1;10;1)="*";0;SVERWEIS(TEIL($A1;6;9);Flugstrecken!$A:$C;2;FALSCH));0)+WENN(LÄNGE($A1)>=19;WENN(TEIL($A1;15;1)="*";0;SVERWEIS(TEIL($A1;11;9);Flugstrecken!$A:$C;2;FALSCH));0)+WENN(LÄNGE($A1)>=24;WENN(TEIL($A1;20;1)="*";0;SVERWEIS(TEIL($A1;16;9);Flugstrecken!$A:$C;2;FALSCH));0)+WENN(LÄNGE($A1)>=29;WENN(TEIL($A1;25;1)="*";0;SVERWEIS(TEIL($A1;21;9);Flugstrecken!$A:$C;2;FALSCH));0)+WENN(LÄNGE($A1)>=34;WENN(TEIL($A1;30;1)="*";0;SVERWEIS(TEIL($A1;26;9);Flugstrecken!$A:$C;2;FALSCH));0)+WENN(LÄNGE($A1)>=39;WENN(TEIL($A1;35;1)="*";0;SVERWEIS(TEIL($A1;31;9);Flugstrecken!$A:$C;2;FALSCH));0)+WENN(LÄNGE($A1)>=44;WENN(TEIL($A1;40;1)="*";0;SVERWEIS(TEIL($A1;36;9);Flugstrecken!$A:$C;2;FALSCH));0)+WENN(LÄNGE($A1)>=49;WENN(TEIL($A1;45;1)="*";0;SVERWEIS(TEIL($A1;41;9);Flugstrecken!$A:$C;2;FALSCH));0)+WENN(LÄNGE($A1)>=54;WENN(TEIL($A1;50;1)="*";0;SVERWEIS(TEIL($A1;46;9);Flugstrecken!$A:$C;2;FALSCH));0)

 

Eine kurze Formel verunstaltet man EIGENTLICH nicht mit Fehlerbehandlung, sondern man sorgt dafür, dass alle denkbaren Flugrouten im Nachschlagewerk aufgenommen sind. Davon durfte ich ausgehen in Deinem Erstbeispiel, in welchem ja sogar noch nicht einmal überflüssige Strecken genannt sind. Du gewährst exakte 1:1-Information (das ist immer schlecht für ein Beispiel).

Ich hoffe, dass Du wenigstens das Thema "Doppik" beherzigt hast? Denn nur dann ist meine (kürzere) Formel zulässig.

Ich gebe Dir recht, dass der Summand 0 gefährlicher ist als #NV, da dann das Flugzeug abstürzen kann, weil nicht genug getankt wurde. #NV ist in den Kurzlösungen aber unmöglich, da die starre Formel mit TEIL bewusst und absichtlich 0 addiert. Die Formel würde länger (ist bestimmt noch zu kürzen):

(für Doppik!) F1: =SUMME((TEIL(E1;SPALTE(A1:Z1)*5-4;9)=A$1:A$9999)*B$1:B$9999)/(SUMME(ZÄHLENWENN(A$1:INDEX(A:A;ANZAHL2(A:A));TEIL(E1;SPALTE(A1:INDEX(1:1;(LÄNGE (E1)-4)/5))*5-4;9)))=(LÄNGE(E1)-4)/5)

(ergibt #DIV/0! für ungenannte Strecken, aber auch - was Du nicht willst - für doppelt geflogene Strecken)

 

Doppik ist natürlich beherzigt, alle denkbaren Flugrouten aufzunehmen ist schier unmöglich (und würde aufgrund der Millionen von möglichen Kombinationen auch Excel sprengen). Das ist ein "lebendes" Praxiswerk, das quasi täglich auf Grundlage der tatsächlich geflogenen Strecken erweitert wird.

*biggrin.gif* Das ist weniger das Problem, es geht mir nur darum erkennen zu können, ob eine oder mehrere Einzelstrecken des Gesamtumlaufs fehlen, weil sonst die Zahlen halt nicht stimmen.

Hintergrund ist übrigens nicht die Betankung, sondern das Finanzamt, MwSt.-Befreiung auf Auslandsstrecken und so... *frown.gif*

Ich danke Dir und natürlich auch @WS-53 für die Hilfe. Da ich mir Anpassungen und Erweiterungen der Matrix-Formel nicht zutraue, werde ich wohl bei der SVERWEIS-Variante bleiben, an der ich noch selbst basteln kann, auch wenn sie vielleicht "umständlich" und nicht so elegant ist. Aber da könnte ich mir im Falle #NV sogar noch eine Ausgabe bauen, welche der Einzelstrecken eines Gesamtumlaufs noch genau fehlen - glaube ich wenigstens (noch ungetestet).

 

Alternativ gibt es auch die Möglichkeit, über die Geokoordinaten der Flughäfen, die Luftliniendistanzen immer bei Bedarf (Formel > benutzerdefinierte Funktion) zu berechnen.

Das nach und nach sammeln der benutzten Streckenkombinationen wäre bei dieser Variante nicht notwendig und ein #NV könnte nur durch neue Flughäfen entstehen, deren Geokoordinaten noch nicht gepflegt wurden.

Von www.abflug.info/downloads kannst du dir eine Liste mit 6200 Flughäfen und deren Geokoordinaten runterladen.