Excel Formel (index)?
Hallo zusammen! Ich möchte gerne in excel jeweils den Umsatz und das Quartal mit Hilfe einer Excel-Formel berechnen.(siehe Bild) Ich denke es müsste eine Index oder Vergleich Formel sein Danke im Vorraus :) GLG
3 Antworten
Zwei Lösungsansätze, die aber nur funktionieren, wenn der Tabellenaufbau symmetrisch ist (also pro Jahr immer alle Quartale aufgeführt sind).
Man addiert einfach die Zeilenverschiebung für das Jahr und das Quartal. Das ist das VERGLEICH(...)+VERGLEICH(...)
Wenn die Tabelle nicht symmetrisch wäre, müsste man vermutlich noch mit Bereich.verschieben hantieren.
Da in Deinem Screenshot nicht sichtbar ist welche Zeilen und Spalten das sind, meine Annahme wie im Bild gezeigt: Spalte A das Jahr, Spalte B die Quartale (C=Kunden, D=Umsatz)
G2 = gesuchtes Jahr
G3 = gesuchtes Quartal
1. Die Bezeichnungen bleiben wie sie sind und man passt die Suchbegriffe an
=INDEX(A1:D20;VERGLEICH("Jahr "&G2;A1:A20;0)+VERGLEICH(G3&". Quartal";B1:B20;0)-1;3)
statt der fetten 3 eine 4 liefert den Umsatz
2. Statt "Jahr 2011" reinzuschreiben kann man 2011 eintragen und über die benutzerdefinierte Zellformatierung "Jahr "#
bzw. # ". Quartal" die Anzeige beibehalten. Als Inhalt steht aber eine normale Zahl in der Zelle. Dann geht der Vergleich ohne Klimmzüge
=INDEX(A1:D20;VERGLEICH(G2;A1:A15;0)+VERGLEICH(G3;B1:B15;0)-1;3)
=INDEX(BEREICH.VERSCHIEBEN(A1:D20;VERGLEICH(G2;A1:A20;0)-1;0);VERGLEICH(G3;BEREICH.VERSCHIEBEN(B1:B5;VERGLEICH(G2;A1:A20;0)-1;0);0);3)
damit sollte es egal sein, ob die Anzahl Zeilen pro Jahr gleich ist. Ob man die Länge des Bereichs noch kürzen muss, hängt etwas vom Tabellenaufbau ab.
Ich würde das aber bleiben lassen, wenn nicht unbedingt notwendig, zuviel Risiko, dass man beim hin- und herschieben einen Denkfehler einbaut.
Hallo,
Du suchst das hier: http://www.excelformeln.de/formeln.html?welcher=30
Damit das funktioniert, muss aber die Tabelle auch richtig aufgebaut sein. In einer Spalte darf es keine leeren Zellen geben, außer es gibt wirklich keinen Inhalt dazu.
Das heißt in deinem Fall, du musst in der ersten Spalte in jeder Zeile das Jahr haben.
Weiterhin muss das Suchkriterium natürlich auch so in der Suchmatrik gefunden werden. In deinem Fall wirst du nicht fündig, wenn du beim Quartal nach "2" suchst, aber in der Matrix "2. Quartal" steht.
Außerdem brauchst du auch keine 4 Tabellen machen, nur weil es 4 Jahre sind. Spalte 1 "Jahr", Spalte 2 "Quartal"...
Dann kannst du auch suchen.
Hier mal ein Bild, wie eine Matrix aufgebaut gehört mit Formellösung entsprechend meinem Link.
LG, Chris
Die Fromel ist im Bild leider nicht zu erkennen und aus deiner Beschreibung werd ich nicht schlau. Was heißt Quartal berechnen? Summe? Min? Max? Durchschnitte?
Bevor ich das excel beibringe, woher weiß Mensch was das richtige Feld ist und was wird gesucht? Sobald du weißt was du willst lass es mich wissen.
Es wird mit Hilfe einer Vergleichs-Index-Kombi Formel versucht durch die Jahres und Quartals Angabe auf den richtigen Umsatz bzw. die Kundenanzahl anzugeben. Es war die Frage was die richtige Formel ist bzw. in welcher Reihenfolge ich die Funktionen aneinanderreihen muss.
Ich habe jetzt eine Weile auf dein Bild gesehen. Du möchtest in die Zelle oben rechts ein jahr eintragen, dann eine Zelle darunter die Quartalsnummer und dann soll in den zeilen 3 und 4 der Umsatz und die Kundenzahl genannt werden.
Also die Zeile nach Auswahl in eine Spalte gekippt werden. Dafür fällt mir leider keine Formel ein.
Die Formel ist nur ein Versuch von mir (bitte nicht beachten). Mit Quartal berechnen meine ich, dass ich eine Formel brauche, die das richtige Feld findet was gesucht ist.