Clever-Excel-Forum

Hallo Gemeinde,

bisher habe ich meine Probleme mit Excel immer mit dem stillen Lesen lösen können. Allerdings bin ich seit vielen vielen gescheiterten Versuchen mit meinem Latein am Ende.
Das stumpfe kopieren von irgendwelchen Formeln ist manchmal eine Lösung, aber hilft mir hier nicht, daher bitte ich euch im Hilfe.
Es geht hier um eine ziemlich lange Datenliste, die ich auch einem System exportiert bekomme und die will ich im Endergebnis in bestimmter Form sortiert und angezeigt bekommen (einfach gesagt). Ich habe mehrere Personen, die Dinge verkauft haben und zu jedem Verkauf gibt es eine Zeile.

Ich habe mal die Datei aus Datenschutzgründen geändert und hier beigefügt.

In dem Tabellenblatt "Rohdaten" sind die exportierten Rohdaten.
In dem Tabellenblatt "Daten sortiert" sind nur die Spalten, die für mich relevant sind und in sich noch sortiert (soweit sehr einfach).
In dem Tabellenblatt "Zuordnung Mane u. Nr." findet nur eine Umschlüsselung von Nummern zu Personennamen statt, weil die Namen nicht in den Rohdaten stehen.
In dem Tabellenblatt "Ergebnisliste" ist die finale Liste. Hier soll nur von einer Person aus der Liste seine zugehörigen Zeilen angezeigt werden.

Die Sortierung der Rohdaten mache ich nur, weil ich das so schnell überprüfen kann. Muss aber eigentlich nicht in den Formeln drin sein, im Moment bezieht sich aber alles darauf.

Das hier ist im Prinzip meine Formel, die ich in den Ergebniszellen nutze:
=WENN(ZEILE('Daten sortiert'!1:1)>ZÄHLENWENN('Daten sortiert'!$B$1:$B$12;$A$1);#NV;INDEX('Daten sortiert'!C:C;KKLEINSTE(WENN('Daten sortiert'!$B$1:$B$12=$A$1;WENN('Daten sortiert'!$C$1:$C$12=$I$1;ZEILE('Daten sortiert'!$1:$12)));ZEILE(B1))))

Ich hoffe, das ist irgendwie verständlich, was ich meine. Die Formel ist mir so kompliziert, dass ich mehrmals dachte verstanden zu haben, was die besagt und was richtig oder falsch ist, aber ständig kommt etwas falsches und das Verständnis ist wieder weg.

Also bitte bitte helft mich doch bei der Lösungsfindung 

Hallo Andreasexcel,

also deine Formel ist prinzipiell schon richtig, allerdings hast du einige Fehler bei der Anwendung dieser Formel.
Und auch sonst stimmen so einige Dinge nicht in deiner Datei.

1.
Die SVERWEIS-Formel in Spalte B auf dem Tabellenblatt "Daten sortiert" liefert in den Zeilen 11 und 12 einen NV-Fehler, weil in A11 und A12 eine 0 steht.
Und dieser NV-Fehler führt dazu, dass deine Formel auf dem Tabellenblatt "Ergebnisliste" nicht richtig arbeiten kann.

Also entweder auf dem Tabellenblatt "Daten sortiert" deine Formeln nur soweit nach unten ziehen, wie auch Daten in "Rohdaten" vorhanden sind, oder den NV-Fehler in Spalte B mit WENNFEHLER() unterdrücken:
=WENNFEHLER(SVERWEIS(A1;'Zuordnung Name u. Nr.'!$A$1:$B$32;2;0);"")

2.
Die Formel
=WENN(ZEILE('Daten sortiert'!1:1)>ZÄHLENWENN('Daten sortiert'!$B$1:$B$12;$A$1);#NV;INDEX('Daten sortiert'!C:C;KKLEINSTE(WENN('Daten sortiert'!$B$1:$B$12=$A$1;WENN('Daten sortiert'!$C$1:$C$12=$I$1;ZEILE('Daten sortiert'!$1:$12)));ZEILE(B1))))
ist schon richtig, du musst aber in deiner Excel-Version die Formel als Matrixformel eingeben, d.h. die Eingabe mit Strg+Schift+Enter abschliessen, so dass um die Formel die geschweiften Klammern erzeugt werden.

Der Formelteil
=WENN(ZEILE('Daten sortiert'!1:1)>ZÄHLENWENN('Daten sortiert'!$B$1:$B$12;$A$1);#NV …
prüft, ob die aktuelle Zeilenzahl größer als die Anzahl der Treffer (wie oft der gesuchte Name in Spalte B auf "Daten sortiert" vorkommt) ist und gibt dann in den "überschüssigen" Zeilen #NV zurück.
Das macht so eigentlich keinen Sinn, das kannst du auch gleich weglassen, dann liefert die Formel in den überschüssigen Zeilen halt den Fehler #ZAHL statt dem #NV

Also ich würde das dann eher so machen:
=WENN(ZEILE('Daten sortiert'!1:1)>ZÄHLENWENN('Daten sortiert'!$B$1:$B$12;$A$1);"" …
so dass in den überschüssigen Zeilen ein Leerstring ausgegeben wird.
Oder Alternativ auch so:
=WENNFEHLER(INDEX('Daten sortiert'!C:C;KKLEINSTE(WENN('Daten sortiert'!$B$1:$B$12=$A$1;WENN('Daten sortiert'!$C$1:$C$12=$I$1;ZEILE('Daten sortiert'!$1:$12)));ZEILE(B2)));"")

Und dann brauchst du nicht mit der bedingten Formatierung die (pauschal alle) Fehlerwerte wegretuschieren.
Das ist nicht so geschickt und macht man so eigentlich nicht, weil so siehst du ja gar nicht, was deine Formel macht, ob sie richtig arbeitet und welche Fehlerart sie produziert - und hast so keine Chance, einem "echten" Fehler in der Formel auf die Spur zu kommen.

3.
Der Formelteil
… WENN('Daten sortiert'!$C$1:$C$12=$I$1 …
prüft, ob in der Spalte C auf "Daten sortiert" der (von dir versteckte - sowas macht man eigentlich auch nicht...) Begriff in Zelle I1 auf "Ergebnisliste" vorkommt.
Diesen Suchbegriff in I1 musst du aber dann schon exakt gleich schreiben, wie er auch in Spalte C auf "Daten sortiert" vorkommt. Bei dir steht nämlich in I1 "Gebrauchtfahrzeug", und dass kommt so ja nirgendwo in Spalte C auf "Daten sortiert" vor.

Generell ist es halt immer schwierig, eine Formel zu verwenden, die man nicht richtig versteht….
Alternativ kannst du auch diese (etwas "modernere") Formel mit INDEX/AGGREGAT verwenden:
=WENN(ZEILE('Daten sortiert'!1:1)>ZÄHLENWENN('Daten sortiert'!$B$1:$B$12;$A$1);"";INDEX('Daten sortiert'!C:C;AGGREGAT(15;6;ZEILE('Daten sortiert'!$B$1:$B$12)/(('Daten sortiert'!$B$1:$B$12=$A$1)*('Daten sortiert'!$C$1:$C$12=$I$1));ZEILE(B1))))
(Formel in B4 auf "Ergebnisliste")
Der rote Formelteil sind hier deine beiden Bedingungen/Kriterien, nach denen du die Daten auflisten möchtest.
Diese Formel musst du nicht als Matrixformel eingeben und dazu gibt es z.B. hier gute Erklärvideos:
https://www.youtube.com/watch?v=He3dblboncw
https://www.youtube.com/watch?v=fDB1Ktyhp3Y

Ode sieh hier:
https://www.clever-excel-forum.de/Thread...l-Aggregat

Gruß
Fred

noch ein ergänzender Hinweis:
Deine Datenaufbereitung im Tabellenblatt "Daten sortiert" mit deinen vielen Formeln / verlinkten Zellen ist schon ein bisschen speziell. Bei großen Datenmengen könnte auch die Performance darunter leiden.
Sowas sollte heutzutage theoretisch doch auch eleganter mit Power Query zu lösen sein.
Überhaupt generell der Import von deinen Daten aus dem anderen System sollte doch mit Powerquery machbar sein (vermutlich...)
Da kenne ich mich aber nicht aus und kann dir daher diesbezüglich auch nicht weiterhelfen.

Dazu findest du aber viele Infos im Internet, z.B. hier:
https://www.tabellenexperte.de/einfuehru...ry-teil-1/

Gruß
Fred

Hallo Fred,

vielen Dank für diese wirklich ausführliche Antwort.
Das mit der Aggregat-Funktion finde ich sehr gut. Ich habe es auch schon zum Teil hinbekommen.
Jetzt weiß ich nur noch nicht, wie ich sage, dass 2 Möglichkeiten ok sein sollen:

=WENNFEHLER(INDEX(Tabelle1[Fz.-Art];AGGREGAT(15;6;ZEILE(Tabelle1[Fahrzeug])/((Tabelle1[Person]=$D$1))*((Tabelle1[Art]="D"));ZEILE())-1;1);"")

Diese Formel funktioniert soweit. Jetzt möchte ich nur, dass im letzten Teil nicht nur bei der Spalte "Art" ein "D" ok ist, sondern ein "D" und/oder "G" ok ist.
Mit oder() bekomme ich das nicht hin.


=WENNFEHLER(INDEX(Tabelle1[Fz.-Art];AGGREGAT(15;6;ZEILE(Tabelle1[Fahrzeug])/((Tabelle1[Person]=$D$1))*((ODER(Tabelle1[Art]="D";Tabelle1[Art]="G")));ZEILE())-1;1);"")

Hier werden mir jetzt alle Ergebnisse mit G,N,V angezeigt, aber die mit D nicht.


Zum Thema Tabelle mit sortierten Daten, die habe ich jetzt entfernt und arbeite nur noch mit den Rohdaten und der Ergebnisliste.

Hallo,

ich habe jetzt viel experimentiert und habe es fast hinbekommen.

Jetziges Problem:
In der Aggregat Formel habe ich eine Bedingung, die erfüllt sein muss und zusätzlich muss Bedingung 2 oder Bedingung 3 erfüllt sein.

Das ist die Formel insgesamt:
=WENNFEHLER(INDEX(Tabelle1[Art];AGGREGAT(15;6;ZEILE(Tabelle1[Fahrzeug])/((Tabelle1[Name]=$D$1)*(Tabelle1[Art]=$D$2));ZEILE()-3)-1;1);"")

Und in dem Teil - (Tabelle1[Art]=$D$2) - müsste jetzt sowas kommen wie (ODER(Tabelle1[Art]=$D$2;[b]Tabelle1[Art]=$E$2) [/b](Also entweder = D2 oder E2).
Das funktioniert aber nicht, denn jetzt werden nicht nur die 2 Inhalte aus D2 und E2 angezeigt, sondern alle.

Wie kann ich also in der Aggregat Formel eine entweder oder Bedingung einbauen. Im Internet habe ich jetzt schon hoch und runter geschucht.

Vielen Dank für eure Hilfe.

Hallo Andreasexcel,

es kann sein, dass innerhalb der AGGREGAT-Formel ODER nicht funktioniert.

Versuch mal so:
*((Tabelle1[Art]=$D$2)+(Tabelle1[Art]=$E$2)))

Also die beiden Oderbedingungen mit einem + zusammenzufassen (Klammer drumrum nicht vergessen)

Gruß
Fred

Hi,

das war der fehlende Hinweis, mit dem + klappt es! Vielen vielen Dank  .