letzte 3 Werte in Zeile finden

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
MsgBox
Bild

Betrifft: letzte 3 Werte in Zeile finden von: Micha.hal
Geschrieben am: 18.03.2005 08:50:32

Hallo Excelperten,

ich habe folgende Tabelle:

A_____B___C___D___E___F___G___H___I___J___K
Z1___20_______30______40__________50
Z2___10___________20__________30__________40

Ich möchte für jede Zeile die Summe der letzten 3 Werte berechnen. So soll also für Zeile 1 120 und für Zeile 2 90 berechnet werden. Die letzten 3 Werte können aber an beliebiger Stelle stehen und die Anzahl der Werte pro Zeile ist auch beliebig.

Gibt es dafür eine Lösung?

Danke.

Micha

Bild


Betrifft: AW: letzte 3 Werte in Zeile finden von: Andi
Geschrieben am: 18.03.2005 09:03:23

Hi Micha,

so sollte es gehen (als Beispiel für die Zeilen 1 und 2, musst Du noch anpassen):


Sub summe_in_zeile()
Dim zeile As Integer
Dim spalte As Integer
Dim zaehler As Integer
Dim summe As Double
For zeile = 1 To 2
  summe = 0
  zaehler = 0
  For spalte = 256 To 1 Step -1
    If Cells(zeile, spalte).Value <> "" Then
      summe = summe + Cells(zeile, spalte).Value
      zaehler = zaehler + 1
      If zaehler = 3 Then Exit For
    End If
  Next spalte
  MsgBox summe
Next zeile
End Sub


Der Code funkionoiert aber nur, wenn in Deinen Zeilen ausschliesslich Zahlen (oder gar nix) stehen; bei Zeichen oder Strings steigt er aus.

Schönen Gruß,
Andi


Bild


Betrifft: AW: letzte 3 Werte in Zeile finden von: IngoG
Geschrieben am: 18.03.2005 09:05:21

Hallo Micha,

so sollte es funzen

=SUMMENPRODUKT(1:1*(SPALTE(1:1)>=KGRÖSSTE(SPALTE(1:1)*(1:1<>"");3)))

Gruß Ingo


Bild


Betrifft: oder auch so.... von: Volker
Geschrieben am: 18.03.2005 09:35:00

Hallo Micha,

Ingo geht davon aus, das die Werte von li nach re größer werden.
Dann kann man seine Formel auch ganz primitiv darstellen:
=KGRÖSSTE(A1:G1;1)+KGRÖSSTE(A1:K1;2)+KGRÖSSTE(A1:K1;3)
nach unten ziehen
fertig

Ich fürchte aber, unsere ganzen Lösungen versagen auf die eine oder andere Art, wenn weniger als 3 Werte in der Zeile stehen.

Gruß
Volker


Bild


Betrifft: AW: oder auch so.... von: IngoG
Geschrieben am: 18.03.2005 09:44:06

Hallo Volker,
ich glaube du hast meine Formel nicht richtig gelesen.

es werden immer die am weitesten rechts stehenden 3 werte addiert.
(zumindest bei mir) ;-)
wenn die zeile leer ist gibt die formel 0 zurück

kgrößte gibt ja nur die 3 Größte Spaltennumer nund nicht den 3größten wert zurück...


Bild


Betrifft: @Ingo von: Volker
Geschrieben am: 18.03.2005 09:49:54

Sorry, war ein Schnellschuß.
Hab nur kgrösste gesehen und die Simpelformel erstellt.

Gruß
Volker


Bild


Betrifft: AW: letzte 3 Werte in Zeile finden von: Micha.hal
Geschrieben am: 18.03.2005 14:52:51

Hallo IngoG,

ich dachte schon, mein Problem sei gelöst, ist es aber doch noch nicht. Kannst Du mir bitte mal Deine Formel zerpflücken und kurz erläutern?

=SUMMENPRODUKT(1:1*(SPALTE(1:1)>=KGRÖSSTE(SPALTE(1:1)*(1:1<>"");3)))

Was bewirkt z. B. das *(1:1<>"") bei KGRÖSSTE?

Danke.

Micha


Bild


Betrifft: AW: letzte 3 Werte in Zeile finden von: IngoG
Geschrieben am: 18.03.2005 15:46:52

Hallo Micha,

die formel arbeitet folgendermassen:
alle zellen in spalte 1 werden durchlaufen und es wird für jede Zelle einzeln geprüft, ob die aktuelle spalte >= der drittgrößten spalte ist, die werte beinhalten.

Du darfst natürlich die Formel nicht in die selbe Zeile schreiben, da ansonsten die spalte mitgezählt wird und ein zirkelbezug entsteht.

am einfachsten stellst Du sonst mal eine testdatei mit dem Fehler ins netz

Gruß ingo


Bild


Betrifft: AW: letzte 3 Werte in Zeile finden von: Micha.hal
Geschrieben am: 21.03.2005 10:07:40

Hallo Ingo,

vielen Dank für Deine Unterstützung. Hier habe ich mal das Beispiel in eine Datei gepackt.

Link=https://www.herber.de/bbs/user/19918.xls

Im Idealfall würde ich gern die Ergebnisspalte hinter den Werten stehen haben, aber das scheint ja nicht so zu funktionieren. Wenn Du Deine Formel in diese Datei einbauen könntest, dann wäre mir sicher geholfen.

Danke.

Micha


Bild


Betrifft: Datei wird nicht gefunden!!! von: IngoG
Geschrieben am: 21.03.2005 12:25:59

.


Bild


Betrifft: AW: letzte 3 Werte in Zeile finden von: Micha.hal
Geschrieben am: 22.03.2005 11:55:31

Sorry, habe dem Link noch "link=" hinzugefügt und dadurch konnte man die Datei nicht per Klick finden. Hier also noch einmal der Link:

https://www.herber.de/bbs/user/19918.xls

Micha


Bild


Betrifft: AW: letzte 3 Werte in Zeile finden von: IngoG
Geschrieben am: 22.03.2005 16:57:04

Hallo Micha,
in L2 trägst Du folgende Formel ein und kopierst sie nach unten

=SUMMENPRODUKT($B2:$K2*(SPALTE($B2:$K2)>=KGRÖSSTE(SPALTE($B2:$K2)*($B2:$K2<>"");3)))

Gruß Ingo


Bild


Betrifft: Ingo, Du bist ein Held von: Micha.hal
Geschrieben am: 23.03.2005 07:32:58

Hallo Ingo,

Du bist ein Held. Jetzt funktioniert es und nun blicke ich auch durch (ich kam mit "Spalte(1:1)" nicht klar).

Vielen Dank für die Lösung und für Deine ausdauernde Unterstützung.

Ein schönes Osterfest wünsche ich.

Micha


Bild


Betrifft: oder so... von: Volker
Geschrieben am: 18.03.2005 09:27:55


Sub summe_in_zeile()
Dim zeile As Integer
Dim spalte As Integer
Dim zeile_max As Integer
Dim zaehler As Integer
Dim summe As Double
zeile_max = ActiveSheet.UsedRange.Rows.Count
For zeile = 1 To zeile_max
  summe = 0
  spalte = Cells(zeile, 255).End(xlToLeft).Column
  For zaehler = 1 To 3
    summe = summe + Cells(spalte, zeile)
    If Cells(zeile, spalte - 1) <> "" Then
        spalte = spalte - 1
    Else
        spalte = Cells(zeile, spalte).End(xlToLeft).Column
    End If
  Next
  MsgBox summe
Next
End Sub



Gruß
Volker


Bild


Betrifft: AW: letzte 3 Werte in Zeile finden von: Micha.hal
Geschrieben am: 18.03.2005 13:59:27

Vielen Dank allen Helfern. Ich habe mit der Funktion KGRÖSSTE gearbeitet und mir was zurecht gebastelt. Jetzt funktioniert es.

Danke nochmal und ein schönes Wochenende.

Micha


 Bild

Beiträge aus den Excel-Beispielen zum Thema "letzte 3 Werte in Zeile finden"