Excel-Formel - SVERWEIS oder ähnliches?
Hallo an alle Excel-Spezialisten,
ich habe eine "kleines" Problem.
Ich möchte innerhalb einer Tabelle zwei Spalten und zwei Zeilen gleichzeitig vergleichen und dann in eine andere Spalte etwas aus einer der Zeilen reinschreiben lassen.
Beispiel:
Spalte E, Zeile 41 = SA2008630
Spalte E, Zeile 42 = SA2008630
Spalte C, Zeile 41 = Verbrauch
Spalte C, Zeile 42 = Istmeldung
Spalte F, Zeile 41 = 1010
Spalte F, Zeile 42 = 1030
Excel soll jetzt Spalte E, Zeile 41 + 42 vergleichen. Sind die Zahlen identisch und in Spalte C, Zeile 41 steht "Verbrauch", dann schreibe in Spalte G das rein, was in Spalte F, Zeile 41 steht (z.B. 1010). Steht da "Istmeldung", dann das aus Spalte F, Zeile 41 (z.B. 1030).
Und so soll sich das auf alle Zeilen wiederholen.
Ich habe mal ein Bild der Tabelle hochgeladen - ggf. ist es dann etwas besser zu verstehen :-)
Hoffe, jemand versteht das "Kauderwelsch" was ich hier von mir gegeben habe.
Schon mal ganz lieben Dank an alle Experten.
Viele Grüße
Anke
So sollte es dann aussehen.
3 Antworten
Also in Spalte G soll zu jeder BelegNr. immer die ArtikelNr. erscheinen, die als "Verbrauch" verbucht ist? Was, wenn es zu einer BelegNr. nur eine IstMeldung und keinen Verbrauch gibt? (Z.B. BelegNr. FA2019004 oder oder SA2008664)
Wenn immer abwechselnd Verbrauch und IstMeldung untereinander stehen, würde ich diese Formel nehmen:
=WENNS(C2="Verbrauch";E2;C1="Verbrauch";E1;WAHR;"")
Die schaut, ob in der aktuellen Zeile "Verbrauch" steht. Wenn ja, übernimmt sie den Wert aus Spalte E. Ansonsten schaut sie in der Zeile oben drüber, ob da "Verbrauch" steht und übernimmt die entsprechende Artikelnr. Wenn beides nicht zutrifft, wird die Zelle leer gelassen.
Ach so, dann so:
=WENNS(
UND(C2="Verbrauch";C3="IstMeldung");E3;
UND(C2="IstMeldung";C1="Verbrauch");E1;
WAHR;""
)
Die Formel funktioniert so einfach aber nur, wenn Verbrauch und IstMeldung für jede Belegnummer direkt untereinander stehen. Wenn die auch völlig durcheinander sein können, müsste man eine kompliziertere Formel mit der Filterfunktion bauen.
Lieben Dank, aber auch erstmal nur "nah dran" :-)
Ich habe die Formel jetzt mal auf meine Tabelle umgeschrieben (C2 in C41 geändert etc.) und dann würde die so aussehen:
=WENNS(UND(C41="Verbrauch";42="IstMeldung");E41;UND(C42="IstMeldung";C41="Verbrauch");F42;WAHR;"")
Dann sieht es fast gut aus - Excel schreibt jetzt in die Zeile "Verbrauch" die Artikel-Nr. der "Istmeldung" - jetzt fehlen dann nur umgekehrt die Einträge in der Zeile "Istmeldung". Da soll ja dann die Artikel-Nr. von "Verbrauch" rein.
Da sind ein paar kleine Fehler in deiner Übersetzung.
So müsste es passen:
=WENNS(
UND(C41="Verbrauch";C42="IstMeldung");E42;
UND(C41="IstMeldung";C40="Verbrauch");E40;
WAHR;""
)
Beachte die 40 gar nicht. Die wird in deiner Tabelle wahrscheinlich auf eine Überschriftenzeile verweisen, aber beim runterkopieren werden die Zahlen richtig erhöht.
gerne 😊
Falls du nicht weiterkommst, kannst du ja gerne nochmal eine Frage stellen. Ich hab noch eine Idee für eine andere Formel, die aber deutlich rechenintensiver ist und dadurch länger braucht.
auch wenn ich @daCypher und @gfntom seine Lösung bevorzugen würde hier nochmal eine Variante die in einigen wenigen Anwendungsfällen Vorteile hätte:
=wennfehler(ZEILENWAHL(SPALTENWAHL(
FILTER(Tabelle1[[Postenart]:[AritkelNr.]];
([BelegNr]=[@BelegNr])*
(WENN([@Postenart]="Istmeldung";[Postenart]="Verbrauch";[Postenart]="Istmeldung"));"")
;3);1);"keine gültigen Daten gefunden")
Ich hab hier mal nur die letzten 3 Belge genommen, da erst dort Unterscheidungen (bei dir F, bei mir C) kamen.
Vorteil hier ist, dass wenn du mehrere Meldungen hast, die einem Verbrauch zugeordnet werden können, auch wenn die nicht direkt untereinander stehen.
Wie man in Zeile 8 erkennen kann. wird automatisch der passende Wert der Postenart Verbrauch mit der Belegnummer ****34 verknüpft.
In Zeile 9 habe ich mal eine Eintragung gemacht die es noch nicht gibt, dann bei mir definierten Text.
Filter
Der Filter filtert den angegebenen Bereich der Tabelle auf bestimmte Kriterien in deinem Falle auf Gleichheit der Belegnummer und vergleicht ob in der Postenart Istmeldung steht oder nicht (wennformel). Als Datensatz käme hier also für die Zeile 2 raus:
Istmeldung;SA20008634;1030
Spaltenwahl
durch Spaltenwahl lasse ich mir das Ergebnis der 3. Spalte ausgeben also wird der og. Datensatz zu 1030
Zeilenwahl
Ist nur dann Notwendig wenn Daten mehr als 2x vorhanden sind, wie z.B. in Zeile 8, hier würde der Filter sonst 2 Zeilen ausgeben wollen was zu dem Fehler #Überlauf führt, daher Zeilenwahl 1, somit zwinge ich ihn immer nur eine Zeile auszugeben.
Wennfehler
Definiert nur eine Text wenn der Filter keine Daten finden konnte, sprich der Fehler #wert oder #nv oder ähnliches ausgegeben werden würde.
Gerne hat es dein Problem vollumfänglich gelöst oder hängt es noch irgendwo?
Wenn ich dich richtig verstanden habe, reicht dafür eine Einfache WENN-Funktion in Spalte G:
=WENN(UND(E41=E42;C41="Verbrauch");F41;WENN(UND(E41=E42;C41="Istmeldung");F42;""))
Die Formel dann nach unten ziehen
Passt leider nicht wirklich. Zum besseren Verständnis habe ich jetzt mal "zu Fuß" das in Spalte G reingeschrieben, was dann eigentlich automatisch gehen soll und als Bild nochmal in die Frage geladen.
Das ist aber nicht, was du beschrieben hast.
Einmal vergleichst du E41 und E42 und schaust in C41 nach, einmal vergleichst du E41 und E42 und schaust in C42 nach.
Ist mir nicht klar, wovon das abgeleitet werden soll, kann dir also deswegen nicht weiterhelfen.
Auch nicht ganz. In Spalte G soll zu bei "Verbrauch" die Artikel-Nr. von "Istmeldung" stehen und umgekehrt - soweit die Belegnummern von "Vebrauch" und "Istmeldung" identisch sind.