Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender Navigationstipps
Inhaltsverzeichnis

Erste Zelle mit Zahl dann Summe

Erste Zelle mit Zahl dann Summe
26.06.2015 11:11:08
Avino
Hallo,
In dieser Zeille soll ausgegeben werden.
In welcher Zelle beim Projekt2 die erste Zahl steht. Dann nur die rsten beiden Zahlen Summieren
Lösung wäre F7 erste Zahl + Summe 2 Zahlen, also 471
Datei: https://www.herber.de/bbs/user/98499.xlsx
Danke sehr!

33
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
INDEX(), AGGREGAT() und VERGLEICH() ...
26.06.2015 11:32:21
der
Hallo Avino,
... so: =INDEX(A:Z;VERGLEICH(C12;C:C;);AGGREGAT(15;6;SPALTE(D1:H1)/((INDEX(D:D;VERGLEICH(C12;C:C;)):INDEX(H:H;VERGLEICH(C12;C:C;)))"");1))
Gruß Werner
.. , - ...

das ist natürlich nur die erste Zahl owT
26.06.2015 11:35:50
der
Gruß Werner
.. , - ...

AW: Erste Zelle mit Zahl dann Summe
26.06.2015 11:40:53
Avino
Danke Werner,
aber bei mir kommt die Fhler Meldung #NAME!
Gruß Arwin

hast Du wirklich Excel 2010? und in deutsch? owT
26.06.2015 11:54:37
der
Gruß Werner
.. , - ...

gleich noch eine Frage nachgeschoben ...
26.06.2015 11:57:27
der
Hallo Arwin ,
... was verstehst Du genau unter "die ersten beiden Zahlen"? Was ist z.B. wenn G7 leer sein sollte? Soll da F7+H7 gerechnet werden, oder?
Gruß Werner
.. , - ...

AW: Erste Zelle mit Zahl dann Summe
29.06.2015 14:45:41
Avino
Hallo Zusammen,
vielen Dank an die zahlreichen Lösungen.
Die Formel von Rudi funktioniert, ich habe immer ein Anfangs Jahr (welcher flexibel ist) mit einer Zahl, und immer ein folge Jahr oder Jahre mit defenitiv einer Zahl.
Daher klappt die Formel von Rudi. Und das mit Bereich.verschieben ist genial, war mir noch nicht so bewusst.
Ich hatte alles mit Index Vergleich probiert, war aber alles viel schwieriger.
Danke noch mals.
Gruß
Avino

Anzeige
dann vergleiche noch mal ...
29.06.2015 15:12:39
der
Hallo Avwin,
... die Ergebnisse mit meinem Formel-Vorschlag (in meinem Beitrag vom 26.06.15 um 21:32 und dort Formel E12) und zwar:
=SUMMENPRODUKT(D6:H11*(C6:C11=C12)*(SPALTE(D5:H5)"")*(C6:C11=C12)*SPALTE(D1:H1)>0); 2)))
Diese Formel benötigt keine MATRIXFormel und auch nicht die volatile Funktion BEREICH.VERSCHIEBEN().
Gruß Werner
.. , - ...

AW: Erste Zelle mit Zahl dann Summe
26.06.2015 11:57:24
Rudi
Hallo,
 Umsatz
 20152016201720182019
Projekt1 25623458156
Projekt2  15456896
Projekt325632656  
      
      
      
Projekt2471    

ZelleFormel
D12{=SUMME(BEREICH.VERSCHIEBEN(INDEX(C6:C8;VERGLEICH(C12;C6:C8;));;MIN(WENN(ISTZAHL(INDEX(D6:H8;VERGLEICH(C12;C6:C8;);));SPALTE(A:E);""));;2))}
Achtung, Matrixformel!
Die geschweiften Klammern{} nicht eingeben,
sondern die Zelle mit
Shift + Strg + Enter
verlassen statt Enter alleine.

Gruß
Rudi

Anzeige
dazu siehe mal meine nachgeschobene Frage. owT
26.06.2015 12:00:29
der
Gruß Werner
.. , - ...

Warum nicht einfach so, ...
26.06.2015 14:27:30
Luc:-?
…Folks;
die offenen Fragen mal beiseite gelassen:
D12: {=SUMME(INDEX(D6:H8;VERGLEICH(C12;C6:C8;0);1):INDEX(D6:H8;VERGLEICH(C12;C6:C8;0);
VERGLEICH(1;--INDEX(ISTZAHL(D6:H8);VERGLEICH(C12;C6:C8;0);0);0)+1))}
Gruß, Luc :-?
Besser informiert mit …

weil noch nicht meine Zusatzfrage geklärt ist ...
26.06.2015 14:51:03
der
Hallo Luc,
... Deine Formel ermittelt genau wie die Formel von Rudi nur die Addition des 1. Wertes plus den Wert aus der Folgezelle. Wenn diese jedoch wieder leer ist dann werden genau genommen nicht zwei Zahlen addiert. Kann so gewollt sein oder eben auch nicht. Deshalb ja meine Zusatzfrage. Ich hatte/habe eine Lösung für die Addition für zwei Zahlen.
Gruß Werner
.. , - ...

Anzeige
Das wäre aber bei Zeitreihen, um die es sich ...
26.06.2015 16:20:26
Luc:-?
…hierbei sicher handelt, wenig sinnvoll, Werner,
zumal ich davon ausgehe, dass die Zeitreihe dadurch gekürzt wdn soll. Allerdings erhebt sich dann die Frage, warum das nicht fix für alle Zeilen gleich gemacht wdn soll, aber evtl sollen ja immer nur die 1.beiden Jahre ab ProjektStart zusammengefasst wdn. Es gibt da nämlich so eine 2-Jahresregelung für Forschungsprojekte… ;-)
Und was Rudi mit einer volatilen Fkt gemacht hat, kann man auch mit einer anderen volatilen, nämlich INDIREKT, machen, während INDEX ja bekanntlich nicht-volatil ist… ;-)
Gruß, Luc :-?

ist so aber nur Deine Vermutung ... und ...
26.06.2015 21:32:10
der
Hallo Luc,
... Deine Vermutung ist zugegebenermaßen wahrscheinlicher als die andere Variante.
Allerdings betrachte ich meine Frage an Arwin nach wie vor als nicht unberechtigt.
Bevor wir jedoch gegenseitig weiter vermuten, zeige ich meine Lösungsformeln für beide Varianten auf. Der Unterschied im Formelkonstrukt ist marginal, der des Ergebnisses natürlich entscheidend.
Meine Formeln sind natürlich ;-) mit AGGREGAT() gestrickt und somit ist meinerseits keine MATRIXformel notwendig. Nicht volatil sowie kürzer und vor allem schneller (wenn auch hier nicht wirklich messbar) in der Auswertung sind sie allemal.
Formel E12 ergibt die gleichen Ergebnisse wie Deine und Rudis Formel und mit Formel D12 werden die ersten zwei Zahlenwerte addiert (egal wo diese stehen; lösche z.B. mal G7):
 CDEFGH
4 Umsatz
5 20152016201720182019
6Projekt1 25623458156
7Projekt2  15456896
8Projekt325632656  
9      
10      
11      
12Projekt2471471471471 
13 neopaRudiLuc 

Formeln der Tabelle
ZelleFormel
E5=+D5+1
F5=+E5+1
G5=+F5+1
H5=+G5+1
D12=SUMMENPRODUKT(D6:H11*(C6:C11=C12)*(SPALTE(D5:H5)<=AGGREGAT(15;6;SPALTE(D1:H1)/((D6:H11<>"")*(C6:C11=C12)*SPALTE(D1:H1)>0); 2)))
E12=SUMMENPRODUKT(D6:H11*(C6:C11=C12)*(SPALTE(D5:H5)<=AGGREGAT(15;6;SPALTE(D1:H1)/((D6:H11<>"")*(C6:C11=C12)*SPALTE(D1:H1)>0); 1)+1))
F12{=SUMME(BEREICH.VERSCHIEBEN(INDEX(C6:C8;VERGLEICH(C12;C6:C8;)); ;MIN(WENN(ISTZAHL(INDEX(D6:H8;VERGLEICH(C12;C6:C8;); )); SPALTE(A:E); "")); ;2))}
G12{=SUMME(INDEX(D6:H8;VERGLEICH(C12;C6:C8;0); 1):INDEX(D6:H8;VERGLEICH(C12;C6:C8;0); VERGLEICH(1;--INDEX(ISTZAHL(D6:H8); VERGLEICH(C12;C6:C8;0); 0); 0)+1))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
C12Liste =$C$6:$C$8 


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
Das ist wieder so eine Gelegenheit, ...
26.06.2015 22:34:33
Luc:-?
…die demonstriert, dass SUMMENPRODUKT in seiner Pgmierung zwar manches Datenfeld „nachrechnet“ bzw nachverfolgt, Werner,
aber beileibe nicht alle, denn zumindest bei meiner Fml wäre auch mit SUMMENPRODUKT die MatrixFml-Form (als Anstoß von außen, s.nflgd) erforderlich gewesen. AGGREGAT stellt demggüber schon ein komplettes Datenfeld zV, so dass die SUMMENPRODUKT-Pgmierung damit auch ohne Anstoß von außen (Xl-Steuerung!) zurecht kommt.
Luc :-?

nun, nicht immer ...
27.06.2015 13:48:35
der
Hallo Luc,
... auch mit AGGREGAT() kann man bzw. muss man manchmal Datenfelder erzeugen, die eine Matrixformelauswertung bedürfen. Meist versuch ich es dann anderweitig noch zu umgehen. Nicht immer gelingt mir das jedoch.
Aber dann brauch bzw. nutze ich auch kein SUMMENPRODUKT() sondern einfach gleich SUIMME(). Zu (meiner) klaren Abgrenzung nutze ich bewusst nie SUMMENPRODUKT() mit Matrixklammerung.
Gruß Werner
.. , - ...

Anzeige
Ich auch nicht, da dann idR SUMME ausreicht, ...
27.06.2015 14:28:20
Luc:-?
…Werner,
vor allem dann, wenn man SUMMENPRODUKT nicht klassisch (mit mehreren, semikolon­getrennten Argumenten) benutzt.
Das AGGREGAT dieses „äußeren Anstoßes“ mitunter auch bedarf, dürfte den jeweils von ihr benutzten BasisFktt u/o der Art der Erzeugung eines alternativ übergebbaren Datenfeldes geschuldet sein.
Luc :-?

einer MATRIXFormel bedarf AGGREGAT() ...
28.06.2015 17:53:12
der
Hallo Luc,
... meist nur dann, wenn die MATRIXversion genutzt wird und das Argument k auch eine MATRIX oder ein Bezugsbereich ist. Eine genauere Eingrenzung ist mir momentan noch nicht möglich. Eine Rolle spielt auf jeden Fall zusätzlich noch, wie die Matrix des ersten Arguments des AGGREGAT()-Formelteils gebildet wird.
Gruß Werner
.. , - ...

Anzeige
Ja, das erstaunt mich auch nicht, ...
29.06.2015 15:23:07
Luc:-?
…Werner,
denn hier gilt mit Sicherheit das, was für alle primär skalaren Argumente aller XlFktt gilt, die Xl-Steuerung variiert das Argument über den angegebenen Bereich, wenn's sich mit den anderen Angaben verträgt, evtl auch über ein Datenfeld. Von der Pgmmierung hängt dann ab, ob diese Variation auch als Datenfeld akzeptiert wird, was normalerweise nicht der Fall ist; hier läuft stattdessen ein fmlStandort-abhängiger Index durch einen angegebenen Bereich und bestimmte (idR nicht alle Arten von) Datenfelder(n), wie man es sehr gut bei INDEX beobachten kann. Das führt dann übrigens dazu, dass in VBA die jeweils auf die XlFkt zugreifende WorksheetFunction.… mit einer solchen Angabe nicht fktioniert, weil hierbei die Xl-Steuerungs­Unterstützung fehlt.
Fazit: Soll jedes Argument einer beliebigen Fkt mit einem Datenfeld bzw Bereich fktionieren, muss der Pgmierer das auch vorgesehen haben. Mithilfe der XlSteuerung ist aber auch im Negativfall eine größere Variabilität erreichbar. Darauf haben sich die einzelnen Pgmierer in unterschiedlichem Ausmaß oft verlassen. Aber wenigstens wurde mit AGGREGAT im Ggsatz zu TEILERGEBNIS der Anschluss an die ganz alten XlFktt geschafft und damit generell auch die Arbeit mit Datenfeldern, nicht nur mit ZellBereichen, ermöglicht. Damit können dann die Möglichkeiten der integrierten Fktt voll ausgeschöpft wdn, was ja auch schon ein (bisher MS-unüblicher Riesen-)Fortschritt ist… ;-)
Gruß, Luc :-?
Besser informiert mit …

Anzeige
AW: Ja, das erstaunt mich auch nicht, ...
29.06.2015 15:34:21
Daniel
Hi Luc
die spezialitäten von Teilergebnis sind doch ausgeblendte Zeilen und die Ergebnisse von anderen Teilergebnisformeln im Wertebereich zu ignorieren.
Dh das sind Dinge, die nur in Zellbereichen vorkommen können, aber nicht allgemeinen Datenfeldern.
Insofern ist die Auslegung auf die Verwendung mit Datenfeldern unnötig, weil diese Dinge bei Datenfeldern nicht vorkommen können und man dann sowieso die "normalen" Funktionen verwenden kann und nicht auf Teilergebnis zurückgreifen muss.
Unverständlicher ist eher, dass die ZählenWenns/SummeWenns-Funktionen hier immer noch nicht berücksichtigt sind.
Gruß Daniel

Anzeige
Ja, auch damit hast du im Prinzip recht, ...
29.06.2015 16:28:48
Luc:-?
…Daniel,
und TEILERGEBNIS ist hier ein etwas unpassender Vgl (wie es auch BEREICH.VERSCHIEBEN wäre), der jetzt AGGREGAT und der damit vglbaren FmlAuswahl geschuldet ist. Der Grundgedanke bei der Schaffung von TEILERGEBNIS war also ein etwas anderer und einfacherer, der nun von AGGREGAT aber erweitert wurde.
Man kann eine Fkt auch so pgmmieren, dass sie beides kann, ausgeblendete Zellen und Fmln mit sich selbst ignorieren, aber außerdem noch Fehlerwerte und Anderes und trotzdem auch Datenfelder verarbeiten kann. Die Nichtberücksichtigung ausgeblendeter Zellen stünde dann für Datenfelder natürlich nicht zV, ganz so, wie es auch INDEX handhabt → Datenfelder als HptArgument liefern keinen ZellBezug, womit alle darauf basierenden (Folge-)Operationen hinfällig wdn.
Ich vermute eher, dass der Hptgrund dafür, dass TEILERGEBNIS nicht so flexibel ausgelegt wurde, der gleiche ist wie bei den anderen von dir genannten Fktt → FremdPgmmierg ohne vollen Einblick in das Xl-System. Hinzu dürfte kommen, zumindest ist das mal von Sprachkundigen erwähnt worden, dass C keinen Variant-Datentyp kennt, diese Fktionalitäten also alle separat pgmiert wdn müssten. Da ist es dann schon einfacher, sich/das auf Ranges zu beschränken, der EndNutzer wird's schon schlucken (müssen)… ;-|
Gruß, Luc :-?

Anzeige
AW: Ja, auch damit hast du im Prinzip recht, ...
29.06.2015 16:50:54
Daniel
Für mich als Excelanwender stellt sich natürlich die Frage, ob es auch ein Nachteil sein kann, wenn die Funktion "universal" programmiert ist, weil sie dadurch vielleicht langsamer wird, ohne dass ich deren andere Vorteile nutzen kann.
Gruß Daniel

AW: Ja, auch damit hast du im Prinzip recht, ...
29.06.2015 17:49:37
Avino
Hallo Zusammen,
vielen Dank für eure vielen Lösungen.
Ich habe letz ednlich die Formel von Rudi verwendet, hat optimal geklappt.
Sie sucht den Wert im ersten Jahr und nimmt dann die Summe von den ersten beiden Jahren.
Nach dem ersten Wert kommt immer ein Wert. Es wird den Fal nicht geben das nach dem ersten Wert im folge Jahr kein Wert steht. Bereich.Verschieben ist eine super Sache wäre ich so nicht drauf gekommen, habe es ganze Zeit mit Index und Vergleich probiert, war deutlich schwieriger umzusetzten.
Danke Euch.
Gruß Avino

AW: Ja, auch damit hast du im Prinzip recht, ...
30.06.2015 14:06:36
Avino
Hallo Zusammen,
noch eine Frage die Formel von Rudi funktioniert zwar aber welchen Zweck hat SPALTE(A:E) in der Formel
ist dadurch die Formel immer noch flexibel?
=SUMME(BEREICH.VERSCHIEBEN(INDEX(C6:C8;VERGLEICH(C12;C6:C8;));;MIN(WENN(ISTZAHL(INDEX(D6:H8; VERGLEICH(C12;C6:C8;);));WENN(INDEX(D6:H8;VERGLEICH(C12;C6:C8;);)>0;SPALTE(A:E);"")));;2))
Danke Gruß Avino

dazu ...
30.06.2015 14:26:03
der
Hallo Avino,
... gib mal in eine beliebige leere Zelle =SPALTE(A:E) ein und markiere diese in der Eingabezeile und betätige dazu die [F9]-Taste. Damit erhältst Du als Ergebnisanzeige ={1.2.3.4.5} und mit diesem wird in der Formel intern auch weiter gerechnet.
Ein gleiches Ergebnis aber ressourcenschonender als =SPALTE(A:E) ist =SPALTE(A1:E1)
Und noch mehr ressourcenschonender ist gleich die Verwendung des Formelvorschlages aus meinem Beitrag vom 26.06.15 um 21:32 und dort Formel E12 und zwar:
=SUMMENPRODUKT(D6:H11*(C6:C11=C12)*(SPALTE(D5:H5)"") *(C6:C11=C12)*SPALTE(D1:H1)>0);2)))
Diese Formel benötigt keinen MATRIXFormelabschluß und auch nicht die volatile Funktion BEREICH.VERSCHIEBEN().
Gruß Werner
.. , - ...

AW: Ja, auch damit hast du im Prinzip recht, ...
30.06.2015 16:55:17
Avino
Danke Euch für die Erklärungen,
ich doktore noch an der Formel von Luc:
=SUMME(INDEX(D6:H8;VERGLEICH(C12;C6:C8;0);1):INDEX(D6:H8;VERGLEICH(C12;C6:C8;0); VERGLEICH(1; --INDEX(ISTZAHL(D6:H8); VERGLEICH(C12;C6:C8;0); 0); 0)+1))
Warum ist for dem INDEX -- "--INDEX(" ? KOnnte ich nicht nachvollziehen!
Und wie ändere ich die Formel ab wenn ich statt erste 2 Werte 3 haben will?
Nicht einfach zu ändern;)
Gruß Avino

ISTZAHL liefert Wahrheitswerte, ...
30.06.2015 17:52:41
Luc:-?
…die mit -- (entspricht 1*) in die Zahlen 0 bzw 1 gewandelt wdn, mit denen der VERGLEICH erfolgen soll, Avino;
da das HptArgument von INDEX jeweils ein ZellBereich ist und somit auch ein ZellBezug geliefert wird, wird durch INDEX(…):INDEX(…) ein ZellBereich von der 1.Zelle lt 1.INDEX bis zur Zelle lt 2.INDEX ermittelt. Diese wird in der gleichen Zeile auf der 1.Zelle mit einer Zahl lokalisiert und dann mit +1 in der SpaltenAngabe des 2.INDEX auf deren NachfolgeZelle gesetzt. Folglich muss das für die 3 ersten Zahlen auf +2 geändert wdn.
Gruß, Luc :-?
Besser informiert mit …

AW: Ja, auch damit hast du im Prinzip recht, ...
02.07.2015 16:01:54
Avino
Hallo Zusammen,
Noch mals Danke für eure Hilfe.
Letz ednlich habe ich die Formel von Luc genommen. Vorteil von dieser ist füge ich vor der Umsatz Matrix noch weitere Spalten an z.B. mit Preisen. Dann hat es keine Auswirkungen auf die Formel.
Bei der Formel von Rudi hat sie dann in den neu eingefügten Spalten die Zahlen gezogen und nicht mehr von der Umsatz Matrix.
Also vielen Dank nochmals. Ohne euch wäre ich nicht so leicht drauf gekommen, und habe viel gelernt.
Gruß
Avino

Na, denn iss' ja jut...! ;-) owT
02.07.2015 17:15:22
Luc:-?
:-?

Kaum, denn es wird ja niemals alles ...
29.06.2015 18:21:21
Luc:-?
…durchlaufen, Daniel,
und 1-2 Abfragen* mehr dürften kaum schaden. Anderenfalls wird die Entscheidung darüber, was geht oder eben nicht, auf den EndAnwender abgeschoben, der das in praxi ausprobieren muss, falls er mit der Hilfe nicht klarkommt oder diese gar nicht erst liest…
* Das hängt natürlich auch davon ab, wie geschickt das Pgm(/der Pgmmierer) das (ge-)handhabt (hat).
Luc :-?

AW: Kaum, denn es wird ja niemals alles ...
29.06.2015 18:29:38
Daniel
jemand, der Makros schreibt, sollte in der Lage sein die Hilfe zu lesen und dies auch tun, dafür ist sie da.
Gruß Daniel

Was hat das jetzt mit dem Problem zu tun, ...
29.06.2015 19:04:01
Luc:-?
…Daniel?
Um's Makro-Schreiben ging's ja nicht, sondern um die Anwendung von Fktt, egal ob Standard oder UDF. Nur in diesem Zusammenhang kam die UDF-/FktsPgmmierung ins Spiel. Deine Meinung dazu entspricht dem pgmmierungs-/er-üblichen Einsatz von Fktt als Pgmierungs­hilfsmittel. Hier geht's aber um zellFml-taugliche UDFs! Man sollte dem EndNutzer dann ja wohl eher nicht eine Vielzahl ähnlicher Fktt mit ganz speziellen EinsatzZwecken* zumuten. Schon jetzt blicken viele da nicht durch…
* In einem offiziell angebotenen AddIn müsste ich auch aussortieren…
Luc :-?

AW: Was hat das jetzt mit dem Problem zu tun, ...
29.06.2015 19:19:53
Daniel
mir gings grundsätzlich um die Anwendung in Zellformeln und obs da notwendig ist, deine Funktion zu haben die auch Datenfelder verarbeiten kann wenn ich die Formel sowieso nur mit Zellbezügen "füttern" kann.
Ob jetzt jetzt sinnvoller ist, viele Funktionen mit spezialisierten, aber beschränkten Fähigkeiten zu haben oder wenige Funktionen mit vielen Fähigkeiten, die dann über nicht-sprechende Parameter gesteuert werden, lässt sich so pauschal nicht sagen.

Naja, zumindest haben die ursprünglichen ...
29.06.2015 19:30:05
Luc:-?
…Xl-Pgmmierer da weiter gedacht und dankenswerterweise über den Sichtkreis ihres Pgmmierer-„Kirchturms“ hinausgeschaut. Die Xl-Fml-Freaks danken's ihnen bis dato… ;-)
Luc :-?

314 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige