Office: Summenbildung nach Vorgaben in anderer Spalte

Hallo,

ich habe vorhin schon eine sehr komplexe Frage gestellt, die wohl nicht lösbar ist. Aber vielleicht doch ein Teil davon.
Ich möchte aus der Wertspalte sämtliche Werte addiert bekommen, die in der Prüfspalte mit einem Punkt versehen sind. Beispieltabelle habe ich angehängt.

Danke.

 

Hi,

=SUMMEWENN(A2:A24;".";B2:B24)

Bis später,
Karin

 

danke!

 

Hallo,

ich nehme an, dass Du Excel2007 besitzt (wegen der XLSX-Datei). Ich habe nämlich in der beigefügten Datei einige Funktionen verwendet, die Excel2007 voraussetzen (SUMMEWENNS(..), WENNFEHLER(..)).

Zuerst habe ich Dein Arbeitsblatt Tabelle2 kopiert ins Blatt Tabelle2_Formeln, um Deine ursprünglichen Daten unangetastet zu lassen.

1) In diesem Blatt habe ich im Bereich K4:K99, um mir das Leben wesentlich zu vereinfachen, die Formeln für die Bayr.Reg.Bezirke eingegeben (die identische Daten zu denen aus dem Bereich A102:A108 bzw. A111:A117 liefern).

2) Da ich nicht wusste, welche Details bei den Formeln Du benötigst,
2a) habe ich im Bereich M4:R99 die Detailformeln für die LK/SKs und Produktgruppen eingegeben.
2b) Im Bereich M102:R108 habe ich die Summenformeln für die Bayr.Reg.Bezirke und Produktgruppen eingefügt. Diese Formeln setzen die unter 2a) angeführten Formeln voraus.
2c) Im Bereich M101:R101 habe ich die Summenformeln für Bayern und Produktgruppen eingefügt. Diese sezten wiederum die Formeln unter 2b) voraus.

3) Da ich nicht wusste, welche Details Du benötigst, habe ich noch folgende Variante erstellt:
3a) Ich habe die Daten des Bereichs A101:H108 kopiert in den Bereich A110:H117
3b) Ich habe im Bereich J111:J117 die Summenformeln für die Hilfsgröße nach Reg.Bezirken und in J110 die Gesamtformel für Bayern eingefügt.
3c) Im Bereich M111:R117 habe ich die Summenformeln für die Reg.Bezirke und Produktgruppen eingefügt. Diese haben das gleiche Ergebnis wie die Formeln im Bereich M102:R108; die Formeln des Bereichs M111:R117 benötigen aber im Gegensatz zu denen aus dem Bereich M102:R108 nicht die Formeln im Bereich M4:R99.
3d) Im Bereich M110:R110 stehen wieder die Summenformeln für Gesamtbayern nach Produktgruppen, wobei diese die Formeln aus Punkt 3c) voraussetzen.

4) Was die Formeleingaben betrifft,
4a) habe ich die Formeln des Bereichs M4:R99 so konstruiert, dass man die Formel nur in M4 einzugeben braucht, diese dann nach N4:R4 kopieren kann und daraufhin den Bereich M4:R4 in die darunter liegenden Zeilen M5:R99 kopiert.
4b) Ebenso gibt man in M102 die Formel ein, kopiert sie nach N102:R102 und anschließend den Bereich M102:R102 in die darunter liegenden Zeilen M103:R108.
4c) Man gibt die Summenformel in M101 ein und kopiert sie nach N101:R101.
4d) Man gibt die Formel in J111 ein und kopiert sie in den Bereich J112:J117; ferner fügt man noch die Formel in J110 ein.
4e) In analoger Weise wie in Punkt 4b) und 4c) verfährt man mit der Formeleingabe im Bereich M110:R117.

5) Falls die bisherige Spalte L mit Deinen Demo-Formeln nicht mehr benötigt wird, kann man entweder ihren (Formel)Inhalt löschen oder aber gleich die gesamte Spalte. Alle anderen Formeln bleiben dadurch völlig intakt.

 

wow, ich bin platt.

erstmal tausend Dank, damit ist mir riesig geholfen. Ich brauche nur noch eine Weile, um das zu durchschauen. Deshalb finde ich es ganz toll, dass Du mir nicht nur die Formeln gebaut hast, sondern auch noch so ausführlich erläuterst.

Dieses Forum ist genial und Exc121150 ist supergenial.


Danke!!!!!!

 

Hallo,

falls es Dich noch interessiert, habe ich Dir die etwas verwirrenden Formeln insofern übersichtlicher gemacht, indem ich Namen im Namensmanager festgelegt habe. Die festgelegten Namen habe ich Dir in folgender Tabelle zusammengefasst:

	B	C	D	E
120	[b]Name[/b]	[b]Bereich[/b]		[b]Bezieht sich auf[/b]
121	Kreise	Tabelle2_Formeln		=$4:$99
122	RegBezirke	Tabelle2_Formeln		=$102:$108
123	RegBezirke1	Tabelle2_Formeln		=$111:$117
124	Nr	Tabelle2_Formeln		=$A:$A
125	Hilfsgröße	Tabelle2_Formeln		=Kreise $J:$J
126	KreisRegBezirk	Tabelle2_Formeln		=Kreise $K:$K

In diesen Bezugsangaben ist sorgsam auf die darin enthaltenen Leerzeichen zu achten!!

 

hoffentlich stelle ich mich jetzt nicht total blöd an. Aber ist es tatsächlich so, dass diese Tabelle in fortlaufenden Zeichen dargestellt wird. Wenn ja, wie kann ich sie dann entziffern?

Ich habe ein macbook mit Excel for mac 2008. Vielleicht liegt es ja auch daran. Ich habe von der Darstellung mal einen screenshot gemacht, um mal zu erfahren ob das so gemeint ist oder nicht.

Liebe Grüße

 

Hallo,

das ist natürlich nicht so gemeint, bei mir wird dieser HTML-Code als ganz normale Tabelle dargestellt mit vertikalen und horizontalen Linien zwischen den Daten.
Ich schicke Dir die Excel-Datei nochmals und habe im Arbeitsblatt Tabelle2_Formeln ab Zeile 120 diese kleine Tabelle nochmals eingefügt.

 

Puuh, da bin ich ja froh. Ich dachte schon, ein Profi müsste so etwas verstehen könne, nur ich stelle mich blöd an.

Aber danke nochmals für die Ergänzung. Damit kann ich das ganze jetzt leichter nachvollziehen und dann auch mal selbst anwenden (hoffentlich!)

Suuuper Hilfe! Ich habe auch ein Funktionenbuch, aber das behandelt immer nur die einzelne Funktion. Ich komme damit nicht weiter, wenn ich mehrere Funktionen verschachteln muss.

Aber wer braucht noch Bücher, wenn es dich und das Forum gibt!!

Ich hoffe, der Nikolaus stellt dir morgen einen großen gefüllten Stiefel vor die Tür!!

 

Hallo Excl121150,

ich habe jetzt noch an meinen Rohdaten gearbeitet und versucht, Deine formeln nachzuvollziehen. Jetzt hatte ich gedacht, dass ich es richtig habe, aber das war wohl nix. Kannst du mir sagen, wo mein Fehler ist? Ich habe jetzt die gesamte Datentabelle angehängt und mein Versuch war in der Spalte BH in den ersten paar Zeilen. Wenn in den Zellen Zahlen drin stehen, funktionierts, aber nicht mit den Punkten.

Kannst du mir helfen?

Danke und Grüße

P.S. Ich musste die Tabelle doch etwas verkleinern, da sie 256 KB überschritten hat, aber für die Lösung macht das nichts

 

Hallo,

es gibt 2 Problemzonen:

1) Du verwendest im Arbeitsblatt 2 verschiedene Punkte:
a) Der Punkt mit ANSI-Code 46: das ist der normale Punkt, wie er überall vorkommt. Dieser kam auch in Deiner ursprünglichen Datei vor - daher habe ich ihn auch in den Formeln eingebaut.
b) Im Arbeitsblatt der jetzigen Datei kommt zusätzlich noch ein weitere Punkt mit ANSI-Code 183 vor, der sich in halber Zeilenhöhe befindet.
c) Du musst Dich entscheiden, welchen Du verwenden willst - beide gemischt zu verwenden, wird schwer möglich sein.

2) Ein weiteres Problem stellen 2 der verwendeten Namen dar:
a) Der Name Inustriepot: Du hast nur den Bezug ='Ust 2007 mit geheim'!$K:$K eingegeben; aber korrekt muss dieser Bezug lauten: =Kreise 'Ust 2007 mit geheim'!$K:$K
b) Der Name KreisRegBezirk: Du hast nur den Bezug ='Ust 2007 mit geheim'!$C:$C eingegeben; aber korrekt muss dieser Bezug lauten: =Kreise 'Ust 2007 mit geheim'!$C:$C
c) Diese beiden Namen bestehen nämlich abgehen vom einleitenden =-Zeichen, aus 3 wesentlichen Bestandteilen: aus dem Namen Kreise, gefolgt von einem Leerzeichen und gefolgt von einer Bereichsangabe.
Das Leerzeichen dazwischen ist in diesem Fall ein Operator (so wie + und - in der Mathematik für Addition bzw. Subtraktion stehen), es ist der Schnittbereichsoperator: dieser Operator ermittelt den Bereich, der sich dort ergibt, wo sich der 1.Bereich (Bereich vor dem Leerzeichen) und der 2.Bereich (Bereich nach dem Leerzeichen) überschneiden.
Also am Beispeil des Namens Industriepot bedeutet das: dieser Name bezeichnet denjenigen Bereich, der sich aus der Überschneidung des Bereichs Kreise und des Bereichs 'Ust 2007 mit geheim'!$K:$K ergibt: der Bereich Kreise bezeichneit die Zeilen $4:$416, sodass sich im Endeffekt der Bereich 'Ust 2007 mit geheim'!$K$4:$K$416 ergibt.

3) Ich habe Dein ursprüngliches Blatt 'Ust 2007 mit geheim' kopiert ins Blatt 'Ust 2007 mit geheim2' und
a) habe in diesem im Bereich $N$4:$R$416 die Formeln eingefügt. Hier habe ich auch eine bedingte Formatierung verwendet, um die Zahlen unterschiedlich einzufärben.
b) habe ferner im Bereich $N$422:$R$470 die Formeln eingefügt; dabei möchte ich bemerken, dass sich in diesem Zeilenbereich in Spalte A einige 2-stellige bzw. 5-stellige Werte befinden, für die diese Formeln nicht geeignet sind.
c) habe die Namen für dieses Blatt so angepasst, dass sie funktionieren.
d) Weiters habe ich die Namen, die fürs ursprüngliche Arbeitsblatt bestimmt sind, so angepasst, dass sie korrekt funktionieren sollten. Am Arbeitsblatt selbst habe ich jedoch nichts geändert.

 

Hallo,

ich brauch noch ein wenig, um es vollends zu verstehen. Jedoch das mit den 2 bzw 5 stelligen Zahlen in Spalte A habe ich mit Absicht gemacht, damit ich, wenn es Regierungsbezirke in den Bundesländern gibt, nach den Regierungsbezirken die Relationen rechne und wenn nicht, dann eben nach den Bundesländern. Und habe so gedacht, dass die Formeln die 2 bzw 5-stelligen Zahlen dann eben "übersehen".

Und das mit den Namen (sowas habe ich bisher noch nie verwendet) habe ich folgendermaßen gemacht, ich habe im Menü "einfügen", "Namen" und definieren verwendet. da dachte ich, dass excel das dann schon richtig benennen würde. Das muss ich mir nochmals genauer anschauen.

Au weia, jetzt arbeite ich schon, aber immer nur sporadisch, seit ca. 20 Jahren mit Excel und merke, dass ich eigentlich fast nichts kann.

Dir nochmals tausend Dank, auch für die Erläuterungen, die bringen mich wirklich enorm weiter.

Bis zum nächsten mal
Herzliche Grüße

 

Hallo Exl121150,

es stimmt doch was nicht. Ich habe es jetzt alles angewendet und dann festgestellt, dass es irgendwo noch einen kleinen Fehler geben muss, ich weiß nur nicht wo. Denn wenn ich die endgültigen Werte aller Kreise zusammenzähle, dann müsste es die Gesamtsumme Deutschland ergeben, die ich zur Kontrolle in Zeile 419 stehen habe. In Zeile 420 steht die Summe der Kreise. Und da gibt es Differenzen. Es muss nicht ganz exakt dasselbe rauskommen, weil die Summe der Bundesländer auch minimalste Differenzen ergibt, aber das liegt bei -1 bis 3.
Und wenn man den Hintergrund der Rechnung anschaut, darf es keine Differenzen geben, denn die Werte die in die Lücken (also die Zellen mit Punkten) gefüllt werden, ist die Differenz aus dem Gesamtwert des Bundeslandes oder des Regierungsbezirkes abzüglich der bereits vorhandenen Werte. Und diese Differenz wird lediglich mithilfe der Relation über die Hilfsgröße (Industriepot) auf die Zellen mit Punkten verteilt. Also es wird nichts hinzugerechnet oder abgezogen, sondern nur die tatsächlichen Differenzen verteilt.

Das komische ist, dass es in meiner Originaltabelle, die ich Dir leider nicht schicken kann wegen der Größe, in manchen Spalten die Gesamtsumme Deutschland übereinstimmt und in manchen nicht. Aber in der Beispieltabelle, die Du bearbeitest hast, stimmen die Summen auch nicht.

Ich hänge sie nochmals an. Tut mir leid, Du hast in deiner ersten Antwort schon geschrieben, dass Du nicht weißt, welche Details ich genau brauche, und ich habe in meinen Versuchen, Deine Lösungen zu begreifen auch nicht mehr daran gedacht.

Ich hoffe, Du bist noch nicht zu genervt und wirfst nochmals einen Blick darauf. Ich habe schon den ganzen Nachmittag an der Lösung gesucht. Es muss nur eine Kleinigkeit sein, denn tendenziell stimmts ja.

Liebe Grüße

 

Hallo Exl121150,

vergiss meine vorige Frage. Es stimmt doch alles. Ich habe in meiner orginaldatei noch weitergeforscht und habe ein paar Dinge gefunden. Zum einen waren in den Kreis-/Regierungsbezirksnummern ein paar Fehler, die ich schon in den Originaldaten bekommen hatte und jetzt geändert habe und dann ist noch in einem Regierungsbezirk der Summenwert falsch, da gibt es ohne Lücken bei der Addition der Kreise zum RegBezirk bereits eine Differenz. Da muss ich bei meinem Datenlieferant nachhaken, wie das sein kann.

Also Deine Lösung ist super und ich hab´s auch hingekriegt.

Tausend Dank nochmals und Dir und allen anderen office-hilfe.com-lern schöne Weihnachten und das Beste für´s Neue Jahr.

Liebe Grüße

 

Hallo,

die korrekte Summenabstimmung scheitert an der korrekten Berechnung der Einzelpositionen im Bereich N4:R416.
Die Formeln in diesem Bereich stimmen, obwohl ihre Resultate manchmal falsch sind: sie sind insbesondere genau dann falsch, wenn auch in diesem Bereich ein Punkt als Resultat erscheint.

Wieso gibt es diese Merkwürdigkeit? Ganz einfach, weil die Daten im Bereich E4:I416 inkonsistent sind - nämlich die darin enthaltenen Punkte sind manchmal das Übel:
a) Ich habe Dir im letzten Posting geschrieben, dass Du 2 verschiedene Punkte verwendest und dass Du Dich auf 1 Punkt festlegen solltest.
b) Ich habe leider nicht noch weiter untersucht, denn sonst wäre zu Tage gekommen, dass es an den jetzt noch verbleibenden Problemstellen noch weitere Zeichen neben dem Punkt in den Zellen gibt, nämlich Leerzeichen (ich nehme an, solche vom Typ ANSI 32). In einer Reihe von Zellen gibt es links vom Punkt etliche Leerzeichen, in manchen gibt es sogar rechts vom Punkt 1 Leerzeichen.
Alle diese (überflüssigen) Leerzeichen müssen entfernt werden, denn die Formeln im Bereich N4:R416 fragen nur nach dem einen Punkt: ist ein solcher enthalten, werden die Summenformeln angewendet, ist etwas anderes enthalten (zB. eine Zahl), wird bloß diese wieder angezeigt.