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

Helfe beim Thema Bestimmte Zellen nach Relationen automatisch ausfüllen, ich verzweifle in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; 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... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von iga, 20. März 2018.

  1. IGA
    iga Erfahrener User

    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.
     
  2. Exl121150 Erfahrener User
    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.
     
    Exl121150, 23. März 2018
    #2
  3. IGA
    iga Erfahrener User
    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
     
  4. Exl121150 Erfahrener User

    Bestimmte Zellen nach Relationen automatisch ausfüllen, ich verzweifle

    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
     
    Zuletzt bearbeitet: 23. März 2018
    Exl121150, 23. März 2018
    #4
  5. IGA
    iga Erfahrener User
    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!!!!
     
Thema:

Bestimmte Zellen nach Relationen automatisch ausfüllen, ich verzweifle

Die Seite wird geladen...
  1. Bestimmte Zellen nach Relationen automatisch ausfüllen, ich verzweifle - Similar Threads - Bestimmte Zellen Relationen

  2. Bestimmte Ziffern in Zelle entfernen

    in Microsoft Excel Hilfe
    Bestimmte Ziffern in Zelle entfernen: Hallo zusammen, Ich habe z.B. in meiner Spalte A/ Zelle 146 folgendes stehen "P-100-301783-10001". Ich möchte gerne in Spalte B/ Zelle 146 den gleichen Text von Spalte A/ Zelle 146 kopieren und...
  3. Wenn in Zelle bestimmter Wert dann kopieren

    in Microsoft Excel Hilfe
    Wenn in Zelle bestimmter Wert dann kopieren: Hallo, ich bin absolut neu was Excel angeht und suche eine Lösung für mein Problem. Ich habe zwei Arbeitsblätter ("Übersicht Zimmer & "Bereiche") mit Haushaltsaufgaben und deren Turnus. Nun...
  4. Über Eingabefelder bestimmte Zellen füllen, wohl VBA

    in Microsoft Excel Hilfe
    Über Eingabefelder bestimmte Zellen füllen, wohl VBA: Hallo, suche eine Möglichkeit wenn ich am Monatsende den Stromzählerstand ablese und Datum in C1 und Zählerstand in C2 eintrage, das dann C1 (Datum) in B27 übertragen wird. Außerdem Wert in C2...
  5. Zählen wenn Wert von bestimmter Zelle vorkommt

    in Microsoft Excel Hilfe
    Zählen wenn Wert von bestimmter Zelle vorkommt: Hallo zusammen, ich benutze Excel 2013 und stehe vor einen Problem. Ich möchte eine Datei erstellen die als Spielplan für Sportereignisse dienen soll. Für die zu berechnende Tabelle möchte ich die...
  6. Druckverbot, wenn Zelle einen bestimmten Inhalt haben.

    in Microsoft Excel Hilfe
    Druckverbot, wenn Zelle einen bestimmten Inhalt haben.: Hallo Zusammen, ich brauch mal wieder etwas Hilfe bei VBA. Ich benötige ein Druckverbot, wenn in verschiedenen Zellen ein "T" eingetragen ist. Die Zellen sind an verschiedenen Stellen. Dies habe...
  7. Wenn Zellen bestimmtes Wort enthalten dann...

    in Microsoft Excel Hilfe
    Wenn Zellen bestimmtes Wort enthalten dann...: Huhu, Habe in Spalte A-J verschiedene Wörter oder gar keine wie Z.Bsp. "Aufklärungsbogen CT vom 12.06.2023" oder "Aufklärungsbogen CT vom 23.06.2023". Nun möchte ich in Zelle K ein "J", wenn das...
  8. Wenn in Zeile bestimmter Wert dann ganze Zelle kopieren

    in Microsoft Excel Hilfe
    Wenn in Zeile bestimmter Wert dann ganze Zelle kopieren: Hallo, ich bin auf der Suche nach einer Lösung für folgendes Problem: In Tabelle 1 habe ich eine gesamte Stückliste, auf Tabelle 2 hätte ich gerne die Daten aus Tabelle 1 nach einem bestimmten...
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden