Office: Schwierige Formel?

Hallo zusammen!

folgendes Problem:

ich benötige mal wieder eine Formel. Diese Formel soll mir in Spalte A eine Summe errechnen. Sollte diese Summe jedoch einen Variablen Wert der z.b. in B1 steht übersteigen sollte mir die Formel die Zeile ( noch besser Zelladresse) und zusätzlich vieleicht noch den Wert zurück geben ab dem die Summe den Wert in B1 übersteigt. Also es sollte nicht mehr die eigentliche Summe angezeigt werden sonder eben "wo" die Summe bzw. ab welcher Zeile Zelle die Summe überschritten wurden!

Viel Dank schon mal!

 

Hallo!

Schnellschuss:

	A	B	C	D
1	1		Grenzwert	20
2	2		wird überschritten in Zeile	6
3	3		Summe bis Zeile 6	21
4	4
5	5
6	6
7	7
8	8
9	9

Zelle	Formel
D2	{=MIN(WENN(TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(A1;;;ZEILE(1:999)))>D1;ZEILE(1:999)))}
C3	="Summe bis Zeile "&D2
D3	=SUMME(INDIREKT("A1:A"&D2))

<table><tr><td>Achtung, Matrixformel enthalten!</td></tr><tr><td><span>Die geschweiften Klammern{} werden </span><span>nicht</span><span> eingegeben.</span></td></tr><tr><td><span>Verlassen Sie den Zelleneditor mit </span><span>Strg+Shift + Enter</span><span>, statt Enter alleine.</span></td></tr></table>[/parsehtml]

 

Hey Super !!!

Aber ich wusste das ich die Formel für die Zeilenfindung nicht verstehen würde^^

Falls es dir nicht allzuviel ausmacht wäre ich für eine Erklärung der Formel SEHR DANKBAR Schatzi.
Wenn der Wert in D1 > "die gesamt Summe" wäre sollte die Formel die letzte Zeile ausgeben. Dann wäre alles perfekt!

Vielen Dank soweit

Gruß

 

Hallo!

Ich versuch's mal (von innen nach außen, wie immer):

BEREICH.VERSCHIEBEN(A1;;;ZEILE(1:999))
kreiert ein Array mit den Bereichen
{A1:A1;A1:A2;A1:A3;A1:A4;...;A1:A999}

TEILERGEBNIS(9;...
macht eigentlich nix anderes als die Funktion SUMME, nur dass sie hier den Vorteil hat, das eben genannte Array aufzunehmen, während SUMME immer nur einen einzigen Wert ausgeben kann.
Das ergibt (quasi):
{SUMME(A1:A1);SUMME(A1:A2);SUMME(A1:A3);...;SUMME(A1:A999)}

WENN checkt nun diese einzelnen Summen und vergleicht sie mit D1.
Solange diese Summen also kleinergleich D1 sind, ergibt die WENN-Abfrage FALSCH, ab dann aber WAHR.

MIN ermittelt also das kleinste/erste Vorkommen von WAHR und gibt wegen ZEILE(1:999) die entsprechende Zeile zurück.

Ich hoffe, das war einigermaßen verständlich.

 

Super danke,

das war verständlich soweit. Das ganze brauche ich aber nur deswegen als Matrix Formel das BereichVerschieben() bzw. dann ja Teilergebnis mir ein Array zurückliefert ?

Ansonsten würde man keine Matrixformel benötigen richtig?

Falls der Wert in D2 größer als alle vorhanden Werte in Spalte A ist, müsste ich die Formel so anpassen um trotzdem die letzte Zeile und somit die komplette Summe zu erhalten ?

{=WENN(D1>=KGRÖSSTE(A1:A20;1);ANZAHL(A:A);MIN(WENN(TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(A1;;;ZEILE(1:1000000)))>D1;ZEILE(1:1000000))))}

Vielen Dank soweit!

 

Hallo!

Als Matrix-Formel brauchst du's hauptsächlich wegen WENN und ZEILE , denn WENN und ZEILE sind sonst nicht matrixfähig!

Den Fehler würde ich einfach so abfangen:
WENN(SUMME(A:A)<D1;...

 

hi Schatzi, oder mit WENNFEHLER()...

 

Hallo!

Ich muss hier oft auch abwärts-kompatibel arbeiten, daher habe ich WENNFEHLER (fast) nie auf der Rechnung...

Aber du hast natürlich völlig recht! Seit Excel2007 vereinfacht diese neue Funktion so einiges!