Hallo zusammen,
kleine Aufgabe zum Wochenende:
Ich möchte eine Fahrtkostenberechnung für eine Fahrgemeinschaft anlegen. Der Aufbau ist der Tabelle zu entnehmen.
Bedingungen sind:
Es fahren immer eine unterschiedliche Anzahl an Mitfahrern mit, daher berechnen sich die Fahrtkosten dynamisch in Abhängigkeit der Mitfahrer. Weiterhin sollen die Fahrtkosten, die für jeden einzelnen entstanden sind, berechnet werden - gleichzeitig soll allerdings gegengerechnet werden (Wenn einmal A bei B mitgefahren ist und dann wieder B bei A, stehen sie kostenmäßig neutral zueinander).
Ich denke, das Problem wird sich relativ leicht mit einer Pivot-Tabelle lösen lassen, da kenne ich mich aber überhaupt nicht aus. Versuche im Selbststudium sind leider gescheitert. :(
Rechts habe ich schon einmal eine Kreuztabelle angelegt, die das Endergebnis widergibt, allerdings noch ohne Funktion ist.
Wer kann mir helfen? :43:
I12: =WENNFEHLER($C$6/H12;)
L12: =SUMMENPRODUKT(($K12=$C$12:$C$24)*($K12<>L$11)*(L$11=$C$12:$G$24)*$I$12:$I$24)
Optionen - Erweitert - Nullwerte ausblenden
Wow, das war einfacher als gedacht!
Bleibt nur noch, die Gesamtsumme darzustellen. Zelle O20 soll nun die Differenz der Tabelle in Abhängigkeit vom eingestellten Mitfahrer ausrechnen.
Wie stelle ich das am Geschicktesten an?
edit:
Die Formel für eine Kombination lautet
Code:
=WENN(UND((C8=K20);(D8=N20));SUMME(M12-L13))
Wie bringe ich da alle Kombinationen unter, ohne ein Formelmonster zu kreieren?
Das ist Quatsch. Man kann zwar die Anzahl 10 der Minus- oder Plus-Zahlungen eines jeden an jeden anderen durch Wegkürzen um 6 auf 4 verringern. Aber mach's Dir einfach:
L22: =SUMME(L12:L16) ist das, was die einzelnen zu zahlen haben
R12: =SUMME(L12:P12) ist das, was die einzelnen bekommen
Die Zahlungen kommen auf den Tisch. Dann bekommt jeder davon das, was ihm zusteht. Ist einfach transparenter.
Bei 20 Beteiligten würde sich das hingegen vielleicht lohnen, aus 40 Bewegungen 19 zu machen.
Ich habe ein Beispiel mit der einen funktionierenden Berechnung mal ergänzt. So stelle ich mir das vor, dadurch spart man sich die ganzen Möglichkeiten und kann einfach die entsprechenden Paarungen einstellen.
Und für jeden anderen Nettoausgleich wird eine andere komplizierte Formel nötig. Nein, danke!
Das ist eine Sache für eine VBA-Array-Funktion, die zwei gleichgroße und gleichsummierende Range-Argumente hat und einen wiederum gleichgroßen Ergebnisrange der Nettoflüsse zurückgibt. Oder - als genaue Anweisung für die zu leistenden Zahlungen von wem an wen - sogar wieder eine Kreuztabelle.
Bei einer Anzahl n Beteiligter ergeben sich immer maximal notwendige n-1 Ausgleichszahlungen.
Hallo,
meiine Vorschlag ist, benutzt doch eine einfache Summenformel, mehr brauche ich nicht.
Die simpelsten Lösungen sind, einen Button drücken - FERTIG !! (ohne Kreuztabelle)
mfg Gast 123
Ich möchte noch nicht ganz aufgeben...
Ich habe eine zweite Kreuztabelle erstellt, welche nicht nur die aufgelaufenen Kosten anzeigt, sondern gleich die Summe berechnet (um mir erstmal einen Zwischenschritt zu sparen).
Nun müsste es doch die Möglichkeit geben, die Suche mittels Index zu gestalten. Als Zeile und Spalte würde ich dann gerne die Übereinstimmung der Namen aus der Auswahlzelle mit denen in der Tabelle prüfen.
Verdeutlichung:
Das Feld N7 (Ergebniszelle der 'Auswertung') liefert mir den Wert aus der Tabelle 'Differenz', der sich aus den beiden Namen in J7 und M7 ergibt.
Als Code dachte ich mir etwas wie:
Code:
=INDEX(K19:O23);*Übereinstimmung zwischen J7 und J19:J23*;*Übereinstimmung zwischen M7 und K18:O18*)
An SVERWEIS bin ich gescheitert, geht es damit? Oder habe ich mich einfach zu dumm angestellt?
Hallo Ratsuchender
eine ganz höfliche Frage, ist die von mir vorgeschlagene Makro Lösung jemals getestet worden??
Wenn ja erlaube ich mir nur aus fachlichem Interesse die Frage, warum sie nicht in Frage kommt??
mfg Gast 123
Hallo Gast 123,
ja, natürlich darfst du fragen.
Es gibt drei Gründe, warum diese Lösung für mich nicht in Frage kommt:
- Ich kann kein VBA, kann daher die Formeln selbst nicht verändern
- Ich finde, in diesem Fall mit VBA zu arbeiten, ist ein wenig so, als würde man mit Kanonen auf Spatzen schießen (sorry, einfach nur meine persönliche Meinung)
- Der IT-Bereich, in welchem die Datei verwendet werden soll, erlaubt keine Skripte
Sorry also, dass deine Lösung für mich nicht zielführend ist.
---
Ich habe selbst mit ein wenig Herumprobieren und Suchen die Lösung gefunden. Die Formel lautet
Code:
INDEX(K19:O23;VERGLEICH($J$7;J19:J23);VERGLEICH($M$7;K18:O18))
Danke für alle Antworten!