Microsoft Excel

Herbers Excel/VBA-Archiv

Summen bilden nach letzter Zeile

Betrifft: Summen bilden nach letzter Zeile von: Silke
Geschrieben am: 02.08.2020 15:29:00

Hallo zusammen,

ich bin auf der Suche nach einem Code bei dem er mir nach den Zellinhalten in Spalte G eine Summe aus den Werten der Spalte G bildet.

Des weiteren soll er mir in die selbe Zeile aber andere Spalte (F) die Anzahl der Zeilen eintragen.

Danke für eure Hilfe.

LG Silke


Betrifft: AW: wenn die Daten fortlaufend erweitern werden...
von: neopa C
Geschrieben am: 02.08.2020 16:09:17

Hallo Silke,

... dann ermittele doch die jeweilige Summe oberhalb des ersten Wertes. Dann kannst Du eine ganz einfache SUMME()-Formel einsetzen. Und wozu die Anzahl der Zeilen benötigst erschließt sich mir momentan nicht. Aber auch diese kannst Du dann oberhalb der Daten mittels Formel ermitteln.

Gruß Werner
.. , - ...

Betrifft: AW: Summen bilden nach letzter Zeile
von: Daniel
Geschrieben am: 02.08.2020 16:41:48

Hi
With Cells(Rows.count, 7).end(xlup).offset(1,0)
    .FormulaR1c1 = "=Sum(R1C:R[-1]C)"
    .offset(0, -1).Formula R1C1 = "=Count(R1C[1]:R[-1]C[1])"
End With
Wobei hie In Spalte F die Anzahl der Zahlen aus Spalte G angezeigt werden.

Gruß Daniel

Betrifft: Angenehme Besonderheit
von: lupo1
Geschrieben am: 02.08.2020 18:07:00

a) Wenn Du das SUMME-Icon direkt unter den Summenbereich anklickst, entsteht automatisch die Summe über den gesamten Bereich.

b) Filterst Du den Bereich sogar (indem Du ihn tatsächlich mittels Filter einschränkst), wird anstelle der allgemeinen Summenfunktion eine besondere eingetragen: =TEILERGEBNIS(9;...). Damit wird die Summe gemäß Filter automatisch angepasst.

Betrifft: AW: diese Funktionalität ...
von: neopa C
Geschrieben am: 02.08.2020 20:21:39

Hallo lupo1,

... setzt mW mindestens Excel2010 2007 voraus, damit man die Datentabelle als notwendige "intelligente" Tabelle formatieren kann und hat den Nachteil, dass man bei notwendigen Datenerweiterungen immer erst wieder Datenzeilen vor die Ergebniszeile einfügen muss.

Gruß Werner
.. , - ...

Betrifft: Nein.
von: lupo1
Geschrieben am: 03.08.2020 00:03:23

Sowohl a) als auch b) ging bei mir schon unter xl2000. Ich kann mich noch erinnern, wie ich vor ungefähr 18 Jahren über das =TEILERGEBNIS() unter meiner Tabelle erschrak, weil ich nicht wusste, was das da anstelle der =SUMME() dort sollte.

Das mit den Datenerweiterungen stimmt dagegen. Dafür hatte ich früher immer eine Dummy-Endzeile, an die sich die SUMME klammerte und vor der ich neue leere Zeilen einfügte.

Betrifft: Genauso, Lupo! ;-) owT
von: Luc:?
Geschrieben am: 03.08.2020 02:30:17

:-?

Betrifft: AW: ja, diese Funktionalität ...
von: neopa C
Geschrieben am: 03.08.2020 11:17:40

Hallo lupo1,

... die Du beschrieben hast, gab es auch in meiner XL2002 und war damit wohl ein "Vorläufer" der formatierten Tabellen, die man mW seit XL2007 (diese Version hatte ich nie im Einsatz) als sogenannte "intelligente" Tabelle bezeichnete. Aber sowohl bei diesen wie auch bei der heutigen XL-Versionen bleibt aber der schon bezeichnete Nachteil, wenn man die Datenliste permanent erweitern will.

Gruß Werner
.. , - ...

Betrifft: Ich ziehe die alte Methode vor, weil ...
von: lupo1
Geschrieben am: 03.08.2020 11:33:40

... ich mich design- und verhaltenstechnisch nicht gern fremdbestimmen lasse.

Und ganz entsetzlich finde ich die Formelschreibweise für intelligente Tabellen mit Klartexten und eckigen Klammern.

Die Summe unter der Tabelle ist sowieso nur so etwas wie eine "Sicht-Verprobung". Ansonsten halt an anderer Stelle für mich: Pivot.

Betrifft: AW: der Formelbezug auf "intelligente" Tab. ...
von: neopa C
Geschrieben am: 03.08.2020 11:44:14

Hallo,

... bringt schon einige Vorteile mit sich und vieles lässt sich mit Pivotauswertung lösen.
Ein Problem ist natürlich dann gegeben, wenn die Tab-Namen und die Spaltenbeschriftungen (zu)ang geschrieben werden. Dann wird jede Formel unübersichtlich.

Gruß Werner
.. , - ...

Betrifft: Die Spalten- und ZeilenTitelVerwendung ...
von: Luc:?
Geschrieben am: 03.08.2020 15:36:59

…(Bezeichnungen in Fmln) ist schon alt, Werner und Lupo,
und zwischenzeitig abgeschafft worden. Wohl, weil zu diesem ZeitPkt bereits die List- bzw TabObjekte angedacht waren. Die ursprüngliche Idee dahinter war aber nicht, Unübersichtlichkeit zu erzeugen, sondern das Ggteil - bessere Verständlichkeit (statt kryptischer Adressen)! Insofern würde ich das als den eigentlichen Vorläufer der TabObjekte betrachten, der allerdings leichter durchschaubar war als die (FmlBildungs-)Regeln der TabObjekte. Die halbautomatische SummenBildung ist ein weiteres altes Service-Feature zur Erleichterung der Handhabung, das ähnlich auch in den TabObjekten zum Einsatz kommt.
Ein Nachteil der TabObjekte ist weniger die Festlegung der Gestaltung als die NichtEinbeziehbarkeit komplexer Köpfe und die Inkonsequenz in Bezug auf Vorspalten, die nicht auf gleiche Weise behandelt wdn wie früher bei Bezeichnungen in Fmln. Es ist folglich ein neues, zusätzliches Indizierungssystem geschaffen worden, was eher abschreckende Wirkung haben kann. Begrüßenswert ist allerdings die Idee, ein eindeutiges Objekt unterhalb des Blattes, aber oberhalb einer Zelle zu schaffen. Ein ZellBereich ist zwar ebenfalls ein solches und ein TabObjekt quasi als eine Sonderform desselben (wie es ja auch eine stabile Matrix - aus pluraler MatrixFml - ist) zu betrachten, die allerdings über eine eigene Dynamik verfügt. Noch interessanter wird es allerdings, wenn man 3d-Objekte nicht nur über Blätter, sondern auch über ZellBereiche/TabObjekte anlegen kann. In Ansätzen ist das ja schon immer gegeben, aber eben nicht eindeutig und offiziell.
Ansonsten habe ich den Eindruck, dass Xl immer mehr zu einem Sammelsurium aller möglichen Techniken (und Sprachen) aufgebläht wird. Zur (immer noch vorgehaltenen) alten MakroSprache kam erst VBA, dann noch VB.net und schließlich M (PQ) dazu (von SQL-Verwendung und diversen PgmmierSprachEinschüben in dynHTML für Xl mal abgesehen). Zu Pivot und statistischer Analyse als nur extern steuerbare Pgmm gesellte sich schließlich noch PowerPivot. Man darf durchaus gespannt sein, was µS in der Zukunft noch so alles einfällt und ob das der ursprünglichen Genialität und Einheitlichkeit auf die Dauer bekommt. 2 Fktskerne (mit unterschiedlichen AufrufVerfahren) gibt's ja jetzt schon…
Gruß, Luc :-?

„Die universelle Befähigung zur Unfähigkeit macht jede menschliche Leistung zu einem unglaublichen Wunder.“ Stapps ironisches Paradoxon
Nichtsdestotrotz Durchblick verbessern mit …


Betrifft: AW: zu Deiner letzren Aussage nachgefragt ...
von: neopa C
Geschrieben am: 03.08.2020 19:13:11

Hallo Luc,

... was meinst Du mit: 2 "Fktskerne (mit unterschiedlichen AufrufVerfahren) gibt's ja jetzt schon…"?

Gruß Werner
.. , - ...

Betrifft: Damit meine ich, ...
von: Luc:?
Geschrieben am: 04.08.2020 01:33:04

…Werner,
dass ab Xl12/2007 neu hinzugekommene Fktt anscheinend in einem neuen Fktscluster zusammengefasst wdn. Das wäre dann der gemeinte 2.Fktskern, der wohl auch anders angesprochen wird. Ältere Fktt, die zuvor nur im AddIn AnalyseTools enthalten waren, scheinen dagg entweder in den alten 1.Kern integriert worden zu sein oder ihre Handhabung durch den Fktsmanager/-assistenten wurde verbessert.
Hintergrund:
Ich hatte vor Jahren eine UDF geschaffen, mit der man definierte Namen und ihre Bezüge auslesen kann. Da mich die Handhabung durch das entsprd Xl-Tool ärgerte (relative Adressen wdn auch bei dieser Doku an den Standort des FmlTextes angepasst und ggf sogar rotiert), hatte ich ihr noch ein Argument mitgegeben, das auf die Zelle des 1.Namensauftritts verweist, so dass ggf enthaltene relative Adressen angepasst wdn können. Dazu existiert auch eine vereinfachte Variante ohne diese Anpassung, über die Du ebenfalls verfügst, weil sie in meiner wahrscheinlich letzten Dir gesendeten, besonders umfangreichen Datei mit DokuTeil wohl auf dem letzten Blatt enthalten ist (wg dieser Doku). Sie heißt NCont und gibt ebenso wie o.g. VollVersion auch verborgene Namen zurück, was das erwähnte Xl-Tool nicht macht. Wenn Du diese UDF in der FmlForm =NCont(ZEILE(A1);1) anwendest, gibt sie den unter der lfdNr lt Arg1 registrierten Namen zurück. Sofern Du in Deiner Datei irgendwelche der neuen Fktt verwendet hast, wdn diese zu Beginn in der Form _xlfn.originalFktsname, also bspw _xlfn.AGGREGATE, aufgelistet. Ihr Bezug ist immer 0. Löschst Du alle Anwendungen einer solchen Xl-Fkt in der Datei, wird auch der defName gelöscht. (Das hatte ich aber schon des Öfteren erwähnt, u.a. wohl auch in besagter Datei!)
Es kann natürlich sein, dass diese Namen temporär auch für irgendwelche Berechnungen genutzt wdn, aber zumindest verweisen sie auf eine andere Regie als bei den alten Xl-Fktt (sonst müssten sie ja wohl auch nicht automatisch angelegt wdn!). Das führt dann auch zu dem Effekt, dass ggf für neue Fktt (falls diese Version schon entsprd vorbereitet ist, wie zB Xl12 für AGGREGAT ab Xl14), die in einer neueren Xl-Version (ab Xl12) noch nicht vorhanden sind, ein solcher Name angelegt wird, was dann natürlich nicht zur Berechnung, aber zur Anzeige des defNamens in der Anwendungszelle führt. Ein weiterer Hinweis darauf, dass diese Namen im Berechnungsprozess (durch den FmlText-Interpreter) benötigt wdn (was bekanntlich bei den alten Fktt nicht der Fall ist).
Fazit:
Du kannst hieraus entnehmen, dass sich µS alle Mühe gegeben hat, den Nutzer nicht mit solchen Informationen zu behelligen, ja, sie sogar vor ihm zu verbergen. Folglich hat jemand ohne oder mit nicht ausreichenden VBA-Kenntnissen bzw jemand mit diesen Kenntnissen, der sich aber nicht für die Fktsweise von Fmln interessiert, keine Chance, so etwas zu entdecken. Des weiteren ist das ein Hinweis darauf, dass µS nicht in die Funktion des alten Kerns eingreifen wollte, wohl um unkalkulierbare Risiken zu vermeiden oder weil rationeller pgmmiert wdn sollte, wie es zB zu einigen neuen Erweiterungen/Variationen alter Fktt geschrieben wurde, die dann ebenfalls im neuen Kern zu verorten sind und folglich über verborgene defNamen verfügen, zB _xlfn.RANK.AVG.
Morhn, Luc :-?

Betrifft: AW: danke, verständlich wie nachvollziehbar ...
von: neopa C
Geschrieben am: 04.08.2020 09:01:06

Hallo Luc,

... sind für mich sowohl für Deine Ausführungen aber auch die von Dir offenbarte Handlungsweise von MS.
Beides aber natürlich nur oberflächlich im "Großen und Ganzen", denn im Detail kann und möchte ich auch nicht tiefer eindringen.

Nochmals danke und Dir einen schönen Tag.

Gruß Werner
.. , - ...

Betrifft: Bitte sehr und dito! ;-) owT
von: Luc:?
Geschrieben am: 04.08.2020 13:45:03

:-?