289 Aufrufe
Gefragt in Tabellenkalkulation von

Hallo,

ich habe folgendes Problem in einer Verbrauchsauflistung.

In der ersten Zeile habe ich folgende Spalten
A - Artikelnummer
B bis G die Jahreszahlen für die Verbrauchsjahre von 2018 bis 2023
in Spalte H möchte ich eine Berechnung durchführen, in welchem Jahr zuletzt ein Verbrauch stattgefunden hat.

mit folgender Formel in H2 kommt auch das richtige Ergebnis raus.

=WENN(G2<>0;$G$1;WENN(F2<>0;$F$1;WENN(E2<>0;$E$1;WENN(D2<>0;$D$1;WENN(C2<>0;$C$1;WENN(B2<>0;$B$1;""))))))

Ich arbeite die einzelnen Zellen in Zeile 2 so durch, dass wenn der Verbrauchswert <> 0 ist, die Spaltenüberschrift ausgegeben wird, also das Verbrauchsjahr.

Kann man diese Formel einfacher gestelten?

Danke für euer Feedback schon im Voraus!

8 Antworten

0 Punkte
Beantwortet von xlking Experte (1.7k Punkte)
Hallo,

gibt es dazwischen auch Jahre wo kein Verbrauch statt fand? Wenn du dort eine 0 einträgst und noch kommende Jahre leer lässt hättest du eine Chance:

=INDEX($B$1:$I$1;1;ANZAHL(B2:I2))

Gruß Mr. K.
0 Punkte
Beantwortet von
Hallo Mr.K.,

nein, leider steht in den Zellen ohne Verbrauch immer eine 0.
0 Punkte
Beantwortet von xlking Experte (1.7k Punkte)

Hallo nochmal,

OK, verstehe, dann hast du also in den Jahren 2024 bis 2030 auch immer eine 0 drinstehen? Dann kannst du das wirklich nur so machen, wie du es bereit aufgebaut hast. In Office 365 könnte man vielleicht noch mit den dort neuen Formeln Loop und XVerweis was machen, da kenn ich mich aber noch nicht aus. Ich habe erst auf Version 2019 geupdatet. Das bleibt jetzt auch erstmal so.

Was ich dir allerdings anbieten kann, ist eine benutzerdefinierte Makroformel. Die ist wesentlich leichter zu schreiben und somit übersichtlicher:

  • Starte den VBA-Explorer (z.B. mit Alt + F11)
  • Wähle links im Projektexplorer deine Arbeitsmappe (falls du mehrere geöffnet hast)
  • Füge über Menü Einfügen in deiner Arbeitsmappe ein neues Modul ein.
  • Gib dort den folgenden Code ein:
Function LastNumberBigger0(SearchRange As Range, ReturnRange As Range)
Dim i As Long
For i = SearchRange.Cells.Count To 1 Step -1
  If SearchRange.Cells(i).Value <> 0 Then
    LastNumberBigger0 = ReturnRange.Cells(i).Value
    Exit For
  End If
Next i

End Function

Anschließend kannst du deine Formel in H2 wie folgt schreiben:

=lastnumberbigger0(B2:G2;$B$1:$G$1) 

Die beiden in der Formel angegebenen Bereiche müssen exakt gleich breit sein. Aber das versteht sich von selbst.

Gruß Mr. K.

0 Punkte
Beantwortet von
Office 365 sollte es damit schaffen,

=XVERWEIS(0,00000001;--(ABS(B2:G2)>0);$B$1:$G$1;"";1;-1)

unter der Annahme, dass kein kleinerer Wert als 0,00000001; vorkommt

aber generell müsste eiegntlich auch =MAX(($B$1:$G$1)*(B2:G2<>0))

bzw.

=MAX(($B$1:$G$1)*(B2:G2>0))

(je nachdem ob der Wert auch unter 0 fallen darf)

funktionieren
0 Punkte
Beantwortet von
perfekt,

danke. Es funktionieren beide Antworten,

das Makro mit der eigenen Funktion und die Formel mit MAX.

Danke für eure Unterstützung!

lg

B.
0 Punkte
Beantwortet von
eine Frage habe ich jetzt trotzdem noch an Mr. K.,

das Makro funktioniert in der Datei super, aber ich hätte mir das gerne in die persönliche Arbeitsmappe geschrieben, damit immer verfügbar?

geht das?

Ich habe es in ein Modul in der personal.xlsb geschrieben, aber das funktioniert nicht?

Habe ich etwas falsch gemacht?

lg

B.
0 Punkte
Beantwortet von xlking Experte (1.7k Punkte)

Hallo B.

Du hast nichts falsch gemacht. Die Personal.xlsb ist genauso eine Arbeitsmappe wie jede andere auch. Wenn du Formeln aus einer anderen geöffneten Arbeitsmappe verwenden willst musst du deren Namen getrennt mit einem Ausrufezeichen mit angeben.

=PERSONAL.XLSB!lastnumberbigger0(B2:G2;$B$1:$G$1) 

Da du aber sicherlich keine Lust hast ständig den Namen der Personal.xlsb mit anzugeben, kannst du den Code stattdessen in ein Modul einer neuen Arbeitsmappe legen und diese dann als Excel-Addin (*.xlam) abspeichern. Das musst du dann natürlich noch irgendwann im Menü  Excel-AddIns (im Ribbon Entwicklertools oder unter Datei - Optionen - AddIns) bei einer beliebigen anderen Arbeitsmappe aktivieren. Anschließend kannst du dort abgelegte Makroformeln, wie ganz normale Formeln nutzen.

Aber nachdem die Matrixformel von Anonym auch gut funktioniert, schlage ich vor im aktuellen Fall lieber diese zu verwenden. Das dürfte sich bei vielen Zeilen auf die Performance auswirken, da Makroformeln erfahrungsgemäß etwas Berechnungszeit benötigen.

Gruß Mr. K.

0 Punkte
Beantwortet von
Danke Mr.K.,

danke für die Hinweise.

Ja, so werde ich das machen!

Ich Bedanke mich für die Unterstützung! :-)

lg B.
...