Excel: Jeden vorhand. Wert aus Bereich 1x ausgeben; mit Bedingungen

[nue_trav]

Hallo liebe Excel-Experten,

ich brauche bitte eure Unterstützung bei einem Problem aufbauend auf einem vorherigen Bericht von mir, welcher zwischenzeitlich geschlossen wurde.
Hier ist nun eine Bedingung hinzu gekommen, die ich in einen Formelvorschlag einfach nicht erfolgreich eingebaut bekomme:

Das Problem: Ich habe in etwas um die 80 Zeilen untereinander verschiedenste Längenangaben (Spalte B) stehen und davor jeweils eine Kennzahl 1, 2 oder 3. Hier ein bespielhafter Ausschnitt:

Ich möchte nun jeden in Spalte B vorkommenden Wert nur einmal ausgeben, WENN in Spalte A entweder 1 oder 2 steht.

Der Vorschlag von Tobias aus meinem vorherigen (inzwischen geschlossenen) Beitrag funktioniert bereits ohne die Prüfung:

C1: =WENN(ZÄHLENWENN(A$2:A2;A2)=1;A2;„“)
D1: =WENN(ZEILE(A1)>ANZAHL(C$2:C$22);„“;KGRÖSSTE(C$2:C$22;ZEILE(A1)))

Mein Problem besteht in Spalte C. Wie baue ich die Bedingung richtig mit ein? Er soll nur Werte berücksichtigen, bei denen in Spalte A eine 1 oder 2 steht.
Das Bild oben zeigt in Spalte C händisch zu Veranschaulichungszwecken eingetragene Werte, wie es in diesem Beispiel richtig ausgegeben werden müsste.

Zwei meiner erfolglosen Versuche, die Spalte A als Bedingung mit einzubauen:

=WENN((A$2:A22)=3;„“;WENN(ODER((A$2:A22)=1;(A$2:A22)=2);WENN(ZÄHLENWENN($B$2:B22;B2)=1;B2;„“)))
=WENN((ZÄHLENWENN(A$2:A22;1)=1)+(ZÄHLENWENN(A$2:A22;2)=1)+(ZÄHLENWENN($B$2:B22;B2)=1)>0;B2;„“)

Vielen Dank im Voraus für eure Unterstützung!!

[littlepinguin]

hi,

Als Hilfsspalte C =WENN(A2=3;"";WENN(ZÄHLENWENN(C2:C$2;B2)=0;B2);"")

sollte doch reichen.
Wenn keine 3, dann guggn ob schon da, sonst setzen.

Edit:
Möglich, dass excel sich affig hat, wegen dem Zirkelbezug.
Der ist aber unnötig und dient nur dem leichteren füllen. Der Bereich kann eine Zeile darüber enden, dann weicht die erste Formel aber vom Rest ab (die hat ja noch nix, was zu prüfen wäre).

grüße
lipi

[Tobias]

Hallo,

von deiner Beschreibung ausgehend, sollte das für C1 bereits ausreichen:

=WENN((A2<3)*(ZÄHLENWENN(C$1:C1;B2)=0);B2;"")

---

---

Bezüglich der eigentlichen Frage war der vorherige Teil bereits alles wichtige, ich würde aber gerne noch auf deine gescheiterten Versuche eingehen. Man erkennt auf jeden Fall ganz gut dass du in die richtige Richtung denkst und auch schon sehr nah dran warst, du versuchst es nur noch zu umständlich umzusetzen:

1. Da nur die Werte 1, 2 und 3 vorkommen können, muss man nur auf die Werte 1 und 2 prüfen oder auf den Wert 3, da das eine das andere ausschließt.

2. Ich bin mir nicht sicher was dein Gedanke mit den Bezügen war. Warum benutzt du irgendwelche Bereiche wenn du eine einzelne Zelle prüfen willst?

3. Wenn du den Inhalt einer einzelnen Zelle vergleichst, benötigst du kein ZÄHLENWENN(), dann kannst du auch gleich einen Vergleichsoperator verwenden. Also z.B. statt ZÄHLENWENN(A2;1)=1) einfach (A2=1)=1) wobei dabei allerdings sowieso ein Wahrheitswert herauskommt, weshalb du auch noch einfacher A2=1 verwenden kannst.

@nue_trav

=WENN((A$2:A22)=3;„“;WENN(ODER((A$2:A22)=1;(A$2:A22)=2);WENN(ZÄHLENWENN($B$2:B22;B2)=1;B2;„“)))

Wenn man die Prüfung auf 1 und 2 weglässt, wäre das bereinigt und mit korrekten Bezügen das:

=WENN(A2=3;"";WENN(ZÄHLENWENN(C$1:C1;B2)=0;B2;""))

Wenn man stattdessen die Prüfung auf 3 weglässt, wäre es das:

=WENN((A2=1)+(A2=2);WENN(ZÄHLENWENN(C$1:C1;B2)=0;B2;"")

Hinweis: ([Funktion] Plus [Funktion]) entspricht einer Oder-Funktion, so lange entsprechende Werte herauskommen. ([Funktion] Mal [Funktion]) würde einer Und-Funktion entsprechen.

@nue_trav

=WENN((ZÄHLENWENN(A$2:A22;1)=1)+(ZÄHLENWENN(A$2:A22;2)=1)+(ZÄHLENWENN($B$2:B22;B2)=1)>0;B2;„“)

Würde dann bereinigt, mit korrekten Bezügen und leichter Korrektur fast die selbe Formel liefern:

=WENN(((A2=1)+(A2=2))*(ZÄHLENWENN(C$1:C1;B2)=0);B2;"")

Die Formeln des unteren Teils habe ich jetzt nicht komplett geprüft und kann deshalb Flüchtigkeits- und Tippfehler nicht ausschließen, grundsätzlich sollten sie aber auch funktionieren.

Gruß
Tobias

[nue_trav]

llittlepinguin und Tobias,

ich danke euch beiden vielmals für eure schnelle und zielführende Hilfe!!! Ihr habt mir schon beim letzten Mal geholfen und ich hatte gehofft, dass ihr auch über diesen Beitrag stolpert und helfen könnt!

Mit der Formel von Tobias und Anpassung der Spalte (Matrix nicht in Spalte C, sondern B) funktioniert es.

Zu Tobias’ Frage #2: Stimmt, völlig unnötig, dass ich da eine Matrix anstelle einzelne Zelle für die Prüfspalte eingegeben haben. Da war der Knoten im Kopf und vor dem Blick auf das große Ganze wohl einfach schon zu groß!
Danke auch für die zusätzlichen Erklärungen!

Vielen Dank euch beiden und euch eine gute Zeit!

Enzo

[Tobias]

@nue_trav

Mit der Formel von Tobias und Anpassung der Spalte (Matrix nicht in Spalte C, sondern B) funktioniert es.

Du meinst in der Formel benutzt du jetzt ZÄHLENWENN(B$1:B1;B2)=0) ? Das sollte aber nicht korrekt funktionieren.
Ursprünglich hat sich der Bereich auf die Länge-Spalte bezogen um zu prüfen ob der Eintrag der aktuellen Zeile bereits vorher irgendwo in der Länge-Spalte vorkam. Das funktioniert aber mit dem Filter nicht mehr, weil ein Wert in der Länge-Spalte nicht mehr zwangsläufig in der Hilfsspalte auftaucht. Bei deinen angegebenen Beispielwerten sollte dann der Wert 3,1 nicht mehr auftauchen, da Zeile 5 zwar dem Filter entspricht, der Wert aber bereits in der Länge-Spalte vorkam.
Deshalb habe ich den Bezug von der Länge-Spalte auf die Hilfsspalte geändert um nun zu prüfen ob die Länge der aktuellen Zeile irgendwo vorher in die Hilfsspalte übertragen wurde.
Entweder habe ich dich missverstanden und du meinst etwas anderes oder Spalte C sollte korrekt sein. Wenn ich dich richtig verstanden habe und es mit Spalte C nicht funktioniert, beschreibe bitte das Problem.