Office: (Office 2016 Mac) Bestimmte Zellen nach Relationen automatisch ausfüllen, ich verzweifle

Hallo,

Ich muss in meiner angehängten Tabelle die Zellen, die nur Punkte enthalten mit berechneten Werten füllen. Die Werte sollen sich aus der Relation des Regierungsbezirks oder Ländersumme abzüglich den bereits aufgeführten Werten zu den Werten aus der Berechnungshilfsspalte ergeben. Wie das genau gemeint ist, habe ich händisch in der angehängten Tabelle drin. Allerdings musste ich die Tabelle ziemlich zusammenstreichen wegen der 100KB Hochladbarkeit

Vor ein paar Jahren habe ich schonmal für diese Berechnung von euch Hilfe bekommen. Nur ganz verstanden habe ich es nicht. Jetzt will ich es wieder anwenden, ich habe die Formeln auch in meiner Tabelle eingefügt, aber es funktioniert nicht. Und ich habe keine Ahnung, woran es liegen kann.

 

Hallo,

ich habe die Formeln deiner Datei etwas modifiziert und zwar habe ich in Zelle R5 folgende Formel gegeben:
=WENN(F5=".";(SUMMEWENNS(RegBezirke F:F;RegBezirke Nr;$C5)-SUMMEWENNS(Kreise F:F;KreisRegBezirk;$C5))/SUMMEWENNS(Kreise Berechhilfe;KreisRegBezirk;$C5;Kreise F:F;".")*$J5;F5)

und diese Formel auch in die restlichen Zellen des Bereiches R5:S35 kopiert. Ich hoffe, die Formel entspricht deinen Erwartungen.

Nachdem deine Datei, obwohl sie bescheiden klein an Größe ist, enorme Performance-Probleme hatte, habe ich entdeckt, dass die bedingte(n) Formatierung(n) für den Bereich Q5:S35 auch sehr viel einfacher (und schneller) gestaltbar sind, sodass die Datei jetzt annehmbare Rechengeschwindigkeit besitzt.

 

Hallo Anton Exl,

danke für Deine Antwort. Die Formel ist die Lösung. Tausend Dank. Ich hoffe, wenn ich mal wieder so eine Aufgabe habe, dass ich es wieder hinbekomme, denn ganz verstehen tu ichs leider nicht, Dazu fehlt mir der Hintergrund. Aber ich werde mich genau an deine Formel halten.

Und wenn das mit der geänderten bedingten Formatierung auch noch die Tabelle schneller macht, bin ich total happy, denn meine Originaltabelle ist weitaus größer und manchmal braucht es zum reinen scrollen Minuten.

vielen Dank nochmals

 

Hallo,

die Formel in Zelle R5 der Beispieldatei lautet:

=WENN(F5=".";(SUMMEWENNS(RegBezirke F:F; RegBezirke Nr;$C5) - SUMMEWENNS(Kreise F:F; KreisRegBezirk;$C5)) / SUMMEWENNS(Kreise Berechhilfe; KreisRegBezirk;$C5; Kreise F:F;".") *$J5; F5)

1. Die äußerste WENN(..)-Funktion besitzt 3 Bestandteile: Trifft es im 1. (blauen) Abschnitt zu, dass in Zelle F5 ein "." enthalten ist, so wird der rote Abschnitt der Formel errechnet. Ist jedoch etwas anderes in Zelle F5 als ein Punkt enthalten, gibt die WENN()-Funktion den grünen Abschnitt zurück - d.h. wiederum den Wert der eh schon in der Zelle steht.
2. Uns interessiert also der rote Abschnitt der Formel und dieser zerfällt wiederum in 4 Bestandteile:
   (SUMMEWENNS(RegBezirke F:F;RegBezirke Nr;$C5) - SUMMEWENNS(Kreise F:F;KreisRegBezirk;$C5)) / SUMMEWENNS(Kreise Berechhilfe;KreisRegBezirk;$C5;Kreise F:F;".")*$J5
   • Dabei kommt 3 mal die SUMMEWENNS()-Funktion vor. Diese hat folgenden Aufbau:
     SUMMEWENNS(Summenbereich; Kriterien1Bereich;Kriterien1Wert; Kriterien2Bereich;Kriterien2Wert;...)
     Wie der Funktionsname schon sagt, wird eine Summe von Zahlen ermittelt, die im "Summenbereich" enthalten sein müssen. Aber es werden nicht alle Zahlen in diesem Bereich summiert. Es gibt nämlich einen gleich großen 2.Bereich, den "Kriterien1Bereich" und weiters eine Zahl, der "Kriterien1Wert". Es werden nur die Zahlen aus dem "Summenbereich" summiert, die im "Kriterien1Bereich" eine Zahl stehen haben, die gleich dem "Kriterien1Wert" sind.
     Eine solche SUMMEWENNS()-Funktion kann nur 1 Summenkriterium haben (wie die beiden blauen Funktionen), sie kann aber auch 2 Summenkriterien haben (wie die violette Funktion), oder auch noch mehr Kriterien.
   • Die 1. (dunkelblaue) Funktion hat folgenden Aufbau: SUMMEWENNS(RegBezirke F:F;RegBezirke Nr;$C5), wobei der violette Abschnitt "RegBezirke F:F" der "Summenbereich" ist, der braune Abschnitt "RegBezirke Nr" der "Kriterien1Bereich" und der grüne Abschnitt "$C5" der "Kriterien1Wert" ist.
     - Der "Summenbereich" hat drei Bestandteile: den benannten Bereich "RegBezirke" (Laut Namensmanager bezieht sich dieser auf die ganzen Zeilen $44:$60), ferner den Bereich "F:F" (dieser besteht aus der ganzen Spalte F) und als dritter Bestandteil das Leerzeichen zwischen beiden. Dieses Leerzeichen bedeutet, dass der "Summenbereich" nur aus denjenigen Zellen besteht, in denen sich der Bereich "RegBezirke" und die F-Spalte überschneiden. Somit besteht der "Summenbereich" aus denjenigen Zellen der F-Spalte, die im Bereich "RegBezirke" liegen, im konkreten Fall ist das somit der Bereich F44:F60.
     - Der "Kriterien1Bereich" (braun) hat ebenfalls 3 Bestandteile: "RegBezirke", "Nr" und dazwischen " ". Der benannte Bereich "RegBezirke" umfasst wieder die ganzen Zeilen $44:$60, der benannte Bereich "Nr" befindet sich in der ganzen Spalte $A:$A, womit sich der "Kriterien1Bereich" in den Zellen der Spalte A befindet, soweit sie innerhalb der Zeilen $44:$60 sind, also im Bereich A44:A60.
     - Der "Kriterien1Wert" (grün) ist der Wert der Zelle C5.
     - Somit werden durch die 1. (blaue) SUMMEWENNS()-Funktion die Zahlen aus dem Zellbereich F44:F60 summiert, soweit die Zellen im Bereich A44:A60 den gleichen Wert enthalten wie die Zelle C5.
   • Die 2. (hellblaue) Funktion hat folgenden Aufbau: SUMMEWENNS(Kreise F:F;KreisRegBezirk;$C5)
     - Der "Summenbereich" lautet "Kreise F:F", womit die Überschneidung der ganzen Zeilen $5:$35 mit der F-Spalte gemeint ist.
     - Der "Kriterien1Bereich" besteht aus dem benannten Bereich "KreisRegBezirk", der laut Namensmanager den Bereich "Kreise $C:$C" umfasst und damit die ganzen Zeilen $5:$35 geschnitten von der C-Spalte.
     - Der "Kriterien1Wert" (grün) ist der Wert der Zelle C5.
     - Somit werden durch die 2. (hellblaue) SUMMEWENNS()-Funktion die Zahlen aus dem Zellbereich F5:F35 summiert, soweit die Zellen im Bereich C5:C35 den gleichen Wert enthalten wie die Zelle C5.
   • Die 3. (violette) Funktion hat folgenden Aufbau: SUMMEWENNS(Kreise Berechhilfe; KreisRegBezirk;$C5; Kreise F:F;".")
     - Der "Summenbereich" lautet "Kreise Berechhilfe", womit die Überschneidung der ganzen Zeilen $5:$35 mit der ganzen Spalte $J:$J gemeint ist - also J5:J35
     - Der "Kriterien1Bereich" besteht wieder aus dem Bereich "KreisRegBezirk", somit aus C5:C35
     - Der "Kriterien1Wert" (grün) ist der Wert der Zelle C5.
     - Der "Kriterien2Bereich" besteht wieder aus dem Schnittbereich "Kreise F:F", somit aus F5:F35
     - Der "Kriterien2Wert" (grün) ist der konstante Wert "."
     - Somit werden durch die 3. (violette) SUMMEWENNS()-Funktion die Zahlen aus dem Zellbereich J5:J35 summiert, soweit die Zellen im Bereich C5:C35 den gleichen Wert enthalten wie die Zelle C5 und soweit die Zellen im Bereich F5:F35 einen "." enthalten.
   • Als 4. Bestandteil des roten Formelabschnittes ist noch Zelle $J5 zu erwähnen.
3. Damit haben wir alle 4 Bestandteile des roten Formelabschnittes beisammen: Dein Wunsch war es, dass
   
   womit sich die Formel ergibt: (SummeWenns1 - SummeWenns2) / SummeWenns3 * $J5
   

 

wow, danke danke danke, dass du dir diese Mühe gemacht hast. Ich glaube, ich habe es jetzt auch verstanden, muss aber noch ein paarmal drübergehen, bis es sitzt.
Super, jetzt habe ich nicht nur eine Formel mit der Lösung, sondern weiß auch, was passiert in der Formel.

Diese Hilfe hier ist unglaublich!!
Danke!!!!