Excel · letzten ausgewiesene Wert in einer Spalte finden und ausgeben

Question

Hallo in die Expertenrunde

Ich benötige in einem Exceltabellenblatt eine Formel, welche mir in einer bestimmten Zelle den letzten Wert aus einer Spalte wiedergibt.
In allen Zellen stehen Formeln, welche als Ergebnisse entweder leer also ""; oder "S" oder "x" anzeigen.
Der zu überprüfende Spaltenbereich ist im aktuellen Fall CY28:CY200.
Ich habe es schon mit zig Formeln wie z.B. die nachfolgen probiert - aber leider kein weiterverwertbares Ergebnis angezeigt bekommen:
es funktioniert nicht mit:
=INDEX(CY:CY;MAX(ISTZAHL(CY28:CY200)*ZEILE(CY28:CY200))) als Array => Erg.: immer 0
=INDEX(CY:CY;SUMMENPRODUKT(MAX(($CY:$CY<>"")*ZEILE($CY:$CY)))) => Erg.: immer 0 egal ob als Array oder Formel ohne die geschw. Klammern
=BEREICH.VERSCHIEBEN(CY28;0;VERGLEICH(MAX(CY28:CY200)+1;CY28:CY200;1)-1) => Erg. ""
=MAX(NICHT(ISTLEER(CY28:CY999))*ZEILE(28:999)) => Erg.: 200
=INDEX(CY:CY;SUMMENPRODUKT(KGRÖSSTE(($CY:$CY<>"")*ZEILE($CY:$CY);1))) => Erg. 0
alle Formeln mit Array und auch ohen probiert.

Wer hat einen Tipp, bei dem die Formel die ""-Ergebnisse ignoriert und nur "S" oder "x" Ergebnisse z.B. in Zelle CY24 ausgibt. Falls das nicht in der gleichen Spalte anzuzeigen geht, kann ich auch 7 Spalten weiter nach rechts also in Spalte DE ausweichen.

Ciao der Revanchist

Answer 1

Moin Revanchist,

das geht mit VBA. Die Spalte CY  ist numerisch benannt Spalte = Column 103. Dann kannst Du mit 

Sub LastValue()

Dim r as long, sValue as String

r = cells(Rows.Count,103).End(xlUp).Row

sValue = cells(r,103)

MsgBox sValue

End Sub

den letzten Wert in der Spalte auslesen. Wenn Du den Wert lieber in CY24 haben möchtest, nimm statt MsgBox Cells(24, 103) = sValue. Der Code gehört in das Worksheet.

Grüße d'r Bastler von den VBAsteleien.de

Answer 2

Hi ihr Beiden,

End(xlup) ist hier leider nicht immer das Maß aller Dinge. Insbesondere hier nicht, da die durch Formeln verursachten Leerergebnisse nicht ignoriert werden. Außerdem wird das ganze als Formel benötigt, womit eine benutzerdefinierte Formel in Frage kommt. Ich würde im VBA also folgenden Code nutzen.

Füge dazu im VBA-Editor (Alt+F11) ein neues Modul (Modul1) ein. und gib dort diesen Code an.

Function Lastvalue(r As Range)
  Lastvalue = r.Find("*", LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlPrevious).Value
End Function

Anschließend brauchst du in deine Zelle nur noch die Formel =Lastvalue(CY28:CY200) einzugeben.

Wenn alle Zellen im Bereich leer sind, wird #WERT! zurückgegeben, das kannst du mit =WENNFEHLER(Lastvalue(CY28:CY200);"") unterdrücken

Gruß Mr. K.

Answer 3

Guten Morgen,

sofern es keine ganz alte Excel-Version ist sollte auch

=XVERWEIS("?";CY28:CY200;CY28:CY200;#NV;2;-1)

funktionieren

falls man auch Einträge sucht die mehr als ein Zeichen haben dann "?" mit "?*" ersetzen

und 

=INDEX(CY:CY;SUMMENPRODUKT(MAX(($CY:$CY<>"")*ZEILE($CY:$CY))))

funktioniert bei mir; also wenn ich es nicht in Spalte CY  eingebe (Zirkelbezug) 

in CY so 

=INDEX(CY:CY;SUMMENPRODUKT(MAX(($CY28:$CY200<>"")*ZEILE($CY28:$CY200))))

Answer 4

Danke d'r Bastler von den VBAsteleien.de und dir Mr.K

vielen Dank für eure schnellen Lösungsvorschläge.
Eine vba-gestützte Lösung hilft mir in diesem Fall leider nicht weiter, weil in dieser Arbeitsmappe einfach zu viele Abhängigkeiten bestehen und sehr viele händische Eingaben erfolgen, welche das "Anwerfen eine VBA-Routine hier als weniger zweckmäßig erscheint.

Ich habe noch ein wenig gegrübelt und die Ausgaben "S" und "x" auf Zahlen umgestellt.
Kann diese ja nach Ermittlung wieder in ein "S" oder ein "x" transformieren.
Und da klappt es mit der Formel: =WENNFEHLER(VERWEIS(2;1/(CY28:CY200);CY28:CY200);0).

Ungeachtet dessen, wäre es natürlich für zukünftige Projekte prima eine Formel zu kennen, welche auch Buchstaben oder Sonderzeichen (als letzten, vorletzten usw.Eintrag in Spalten o. Zeilen) erkennt und ausgeben kann.

Ciao der Revanchist
 

Answer 5

Hallo LET

=INDEX(CY:CY;SUMMENPRODUKT(MAX(($CY28:$CY200<>"")*ZEILE($CY28:$CY200))))

nehme ich. Funktioniert einwandfrei !!! Vielen herzlichen Dank

Ciao der Revanchist

Answer 6

Moin allerseits,

@xlking: Danke für Deinen Hinweis zu den Einschränkungen von End(xlup) Mr. K. 

Das war mir in der Form tatsächlich nicht bewusst, was aber vermutlich daran liegt, dass ich kaum mit Formeln arbeite.

Es wäre auch schlauer (als mein Vorschlag oben) gewesen, einfach den definierten Bereich von unten nach "s" bzw. "X" zu durchsuchen und beim ersten Wert zu reagieren. z.B. so: (Code ins Sheet)

Sub Versiv()
Dim i As Integer, sValue As String

For i = 200 To 28 Step -1
    If Cells(i, 103) = "s" Or Cells(i, 103) = "X" Then
        sValue = Cells(i, 103)
        Cells(i, 104) = sValue
        MsgBox sValue & " in Zeile " & i & " gefunden!"
        Exit Sub
    End If
Next i

End Sub

Grüße vom Bastler von den VBAsteleien.de

Answer 7

Hallo Mr. K.
Sorry für meine vorschnelle Antwort an Dich.
Aber als ich deine Empfehlung, ein neues Modul einzurichten, gelesen habe, habe ich gar nicht richtig weitergelesen und damit auch übersehen, dass ja die Formel  =WENNFEHLER(Lastvalue(CY28:CY200);"") doch zu einer formelbasierenden Lösung führt.

hab es gerade ausprobiert und es führt sehr elegant und einfach zum gewünschten Ergebnis.
Diesen Lösungsweg werde ich sicherlich noch öfters bei ähnlichen Aufgaben wählen.
Vielen herzlichen Dank und einen schönen Sonntag

Ciao der Revanchist  .