Excel - dynamische Abfrage ob je 3 Werte größer als...

[Surtia]

Aloha Community.

Meine Abfrage bezieht ich auf eine Spalte mit 20 Zellen (C56 bis C75).
Jede Zelle ist mit folgender Formel gefüllt, wobei $O$66 variiert.
=WENN($O$66="min 25";"min 25";WENN(ANZAHL($O$66)=0;"";$O$66))

Somit ist in jeder Zelle eine Formel hinterlegt, die entweder einen Text ("min 25") oder Nichts ("") oder eine Zahl ($...$...) ausgibt.

Jetzt möchte ich gern, dass in B47 "Hugo" ausgegeben wird, wenn die letzten 3 Zahlen der Spalte C56-C75 größer 1,67 sind, ansonsten gib Nichts ("") zurück. Die Anzahl der in der Spalte stehenden Werte variiert, wobei keine Leerzeilen existieren und sie von oben nach unten gefüllt wird.

Bisher habe ich eine Verkettung mehrer WENN-Bedingungen. Dies scheint mir wenig elegant und scheitert ohnehin am Text, welcher scheinbar immer größer 1,67 ist.

Ich bitte um Hilfe.

 

[vll]

Hallo,

Da ja in jeder Zeile von C56-C75 das selbe steht kannst du einfach die erste zum Vergleich nehmen:

=WENNFEHLER(WENN(C56*1>1,67;"Hugo";"");"")

 

[Surtia]

Deine Lösung umfasst leider nur eine Zelle und die Größe der Werte ist variabel. In jeder Zelle steht ein anderer, berechneter Wert.

Ich möchte die letzten 3 Zahlen bzw. Zellen in diesem Bereich überprüfen.
Jede dieser 3 letzten Zellen (Zahlenwert) muss größer 1,67 sein damit "Hugo" ausgegeben wird.

 

[johako]

Ich denke bei der Variabilität ist eine benutzerdefinierte Funktion per VBA die beste Lösung.

 

[DPXone]

Benutze =WENN(MIN(C73:C75)>1,63;"Hugo";"")

 

[johako]

Das funktioniert nur, wenn die letzten 3 Zahlen in den letzten 3 Zeilen sind. Das muss ja aber nicht sein.

 

[Surtia]

@DPXone
Damit würde nur 1 Wert (Minimalwert) abgefragt werden und nicht generell die letzten 3 Werte.

Danke an alle die geholfen haben.




Ich habe nun eine Lösung gefunden und sollte noch wer über dieses Problem stolpern, hier die Lösung:

Ich habe 3 zusätzliche Zellen angelegt in denen die letzten 3 Werte ausgegeben werden.
Jedoch nur dann, wenn kein bestimmter Text (hier: "min 25") enthalten oder die Zelle leer ist.

Zelle 1 (bei bei AP29): Anzeige des vor-vorletzten Wertes aus der Spalte C im Zellenbereich 56-75:
=WENN(ANZAHL($E$86:$CA$86)<75;"";WENN(ANZAHL($C$56:$C$75)=0;"";WENN(INDEX(C:C;SUMMENPRODUKT(MAX(($C$56:$C$75<>"")*ZEILE($C$56:$C$75))))="min 25";INDEX(C:C;SUMMENPRODUKT(MAX((($C$56:$C$75<>"")*ZEILE($C$56:$C$75))-3)));INDEX(C:C;SUMMENPRODUKT(MAX((($C$56:$C$75<>"")*ZEILE($C$56:$C$75))-2))))))

Zelle 2 (bei mir AP30): Anzeige des vorletzten Wertes aus der Spalte C im Zellenbereich 56-75:
=WENN(ANZAHL($E$86:$BB$86)<50;"";WENN(ANZAHL($C$56:$C$75)=0;"";WENN(INDEX(C:C;SUMMENPRODUKT(MAX(($C$56:$C$75<>"")*ZEILE($C$56:$C$75))))="min 25";INDEX(C:C;SUMMENPRODUKT(MAX((($C$56:$C$75<>"")*ZEILE($C$56:$C$75))-2)));INDEX(C:C;SUMMENPRODUKT(MAX((($C$56:$C$75<>"")*ZEILE($C$56:$C$75))-1))))))

Zelle 3 (bei mir AP31): Anzeige des letzten Wertes aus der Spalte C im Zellenbereich 56-75:
=WENN(ANZAHL($C$56:$C$75)=0;"";WENN(INDEX(C:C;SUMMENPRODUKT(MAX(($C$56:$C$75<>"")*ZEILE($C$56:$C$75))))="min 25";INDEX(C:C;SUMMENPRODUKT(MAX((($C$56:$C$75<>"")*ZEILE($C$56:$C$75))-1)));INDEX(C:C;SUMMENPRODUKT(MAX(($C$56:$C$75<>"")*ZEILE($C$56:$C$75))))))

Nun muss nur noch in einer Zelle (bei mir B47) die entsprechende Abfrage eingefügt werden:
WENN(UND($AP$29>1,67;$AP$30>1,67;$AP$31>1,67);"Hugo";"kein Hugo")


Im Fall eines Platzproblems könnten über ein ODER die 3 getrennten Abfragen in der Zelle B47 zusammengefasst werden.
Falls es Fragen gibt einfach eine PN schreiben oder hier nachfragen.

 

[johako]

Da erscheinen mir ein paar Zeilen VBA handlicher

Function Zahlenpruefung(Bereich As Range) As String

    ZahlenArray = Array(0, 0, 0)
    Treffer = 0
    Zellenanzahl = Bereich.Rows.Count
    Adresse = Bereich.Address
    Adressarray = Split(Adresse, ":")
    Suchwert = 1.67

    For ZellenZähler = 0 To Zellenanzahl
        Zelleninhalt = Range(Adressarray(1)).Offset(-ZellenZähler, 0)
        If IsNumeric(Zelleninhalt) Then
            Treffer = Treffer + 1
            ZahlenArray(Treffer - 1) = Zelleninhalt
            If Treffer = 3 Then Exit For
        End If
    Next ZellenZähler

    If ZahlenArray(0) > Suchwert And ZahlenArray(1) > Suchwert And ZahlenArray(2) > Suchwert Then
        Zahlenpruefung = "Hugo"
    Else
        Zahlenpruefung = ""
    End If

End Function

Ich habe eine entsprechende Exceldatei angehangen.

 

[DPXone]

Der Minimalwert ist doch genau das was du suchst?!
Mit der MIN Funktion gibst du den kleinsten Wert der drei Zellen aus. Wenn dieser nicht größer ist als 1,67, dann trifft die Bedingung nicht zu.

Bsp:
2,74
1,6
1,5
-> Kleinster Wert ist 1,5 und dieser ist nicht größer als 1,67
1,6 ist zwar auch nicht größer, aber das ist ja eh schon hinfällig, da die Bedingung bereits nicht mehr zutrifft.

Warum so umständlich mit einer UND-Bedingung?

Ergänzung (24. November 2017)

Das funktioniert nur, wenn die letzten 3 Zahlen in den letzten 3 Zeilen sind. Das muss ja aber nicht sein.

Wie soll ich das verstehen?
Die MIN-Funktion ist dynamisch (Param Array).
Kann dort einzelne Zellen oder Zellenbereich durch Strichpunkt getrennt angeben:

MIN(A1;C1:C90;Z1:AX1)

 

[johako]

Warum so umständlich mit einer UND-Bedingung?

Da muss ich dir recht geben. MIN passt da besser.

Wie soll ich das verstehen?

Laut der Beschreibung oben können in allen 20 Zellen entweder Text oder Zahlen oder "" auftauchen. Entscheidend für die Ausgabe sind ausschließlich die letzten 3 Zahlen, unabhängig in welcher der Zellen sie stehen. Das kann du ja mit dem MIN nicht dynamisch filtern, oder habe ich was falsch verstanden?

 

[Surtia]

Ja johako, genau so ist es gemeint.
Du hast quasi alles richtig verstanden.
MIN ist in diesem Fall also leider nicht praktikabel.

VBA fällt in diesem Fall leider aus, da es sich automatisch aktualisieren soll ohne, dass irgendwo geklickt werden muss um das Makro zu aktivieren. Ich selbst wüsste zwar, dass man für jede neue Eingabe das Makro klicken muss aber da ich die Datei für andere Mitarbeiter erstelle muss es so einfach und selbständig wie mögich sein.

Vielen dank für eure Mühen.

 

[johako]

So wie das Makro implementiert ist, aktualisiert es sich von allein bei Zelländerungen. Du musst es nicht jedes mal per Hand auslösen. Die einzige Voraussetzung ist, dass Makros aktiviert werden.
Du kannst die Werte in Spalte A meiner Datei anpassen und erhälst sofort die Antwort in C2.