Summewenn und Bereich.Verschieben

Forumthread: Summewenn und Bereich.Verschieben

QMGünter

Hallo Excel-Profis'
ich habe ein komplexeres Problem und bitte um Hilfe.
Ich möchte eine Summe unter der Verwendung von zwei gleichzeitig zutreffenden Bedingungen bilden. Die Tabelle ändert sich dadurch, dass über ein Makro eine neue Zeile an die bestehende Tabelle angehängt werden kann. Die Summe habe ich bereits erfolgreich mit Summewenn bilden können, aber die dynamische Änderung wurde dabei nicht berücksichtigt.
Hier ist die Formel: {=SUMME(WENN($D7:$D79="Dev";WENN($G7:$G79=100;$N7:$N79);0))}
Die dynamische Änderung wollte ich mit Name für die Bereich und mittels Bereich.Verschieben umsetzen. Hier ist die Formel: {=SUMME(WENN(B_Phase=Dev;WENN(B_Eintrittswahrscheinlichkeit=100;B_Corrective_Risk_Costs);0))}.
Leider erhalte ich die Meldung "#BEZUG!" Warum?
Die Namen habe ich wie folgt definiert: B_Phase: =BEREICH.VERSCHIEBEN('Aktuelles Update 2010'!$D$7;;;ANZAHL2('Aktuelles Update 2010'!$D:$D);ANZAHL2('Aktuelles Update 2010'!$7:79))
B_Eintrittswahrscheinlichkeit: =BEREICH.VERSCHIEBEN('Aktuelles Update 2010'!$G$7;0;0;ANZAHL2('Aktuelles Update 2010'!$G:$G);ANZAHL2('Aktuelles Update 2010'!$7:$79))
B_Corrective_Risk_Costs: =BEREICH.VERSCHIEBEN('Aktuelles Update 2010'!$N$7;;;ANZAHL2('Aktuelles Update 2010'!$N:$N);ANZAHL2('Aktuelles Update 2010'!$7:79))
Meine Datei ist unten verlinkt.
Ich hoffe auf eure Hilfe und sag schon mal Danke für die Gehirnknoten.
mfg.
QMGünter
https://www.herber.de/bbs/user/67725.xls

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

AW: Summewenn und Bereich.Verschieben

03.02.2010 15:31:28

Renee

Hi Günter (QualityManager ?);
Deine Bereich.Verschieben Formeln sind viel zu kompliziert angelegt.
Da in Spalte A immer eine ID vorkommt, zähle diese und gib einfach nur die Höhe (Anzahl der ID's) des Bereichs an, somit als Beispiele:

B_Corrective_Risk_Costs
=BEREICH.VERSCHIEBEN(Tabelle1!$N$7;0;0;ANZAHL(Tabelle1!$A$7:$A$10000);1)
B_Eintrittswahrscheinlichkeit
=BEREICH.VERSCHIEBEN(Tabelle1!$G$7;0;0;ANZAHL(Tabelle1!$A$7:$A$10000);1)
B_Phase
=BEREICH.VERSCHIEBEN(Tabelle1!$D$7;0;0;ANZAHL(Tabelle1!$A$7:$A$10000);1)

Du siehst, so musst einen einzige Zellenbezug anpassen!
Dann statt komplizierte Matrix Wenn/Summenformeln, setzt SUMMENPRODUKT ein, also z.B. für N83

=SUMMENPRODUKT((B_Eintrittswahrscheinlichkeit=100)*(B_Phase="Dev")*(B_Corrective_Risk_Costs))

GreetZ Renée
P.S. Ich hab die Tabellennamen in den Formeln bewusst verändert, um die Darstellung im Forum übersichtlicher zu machen!

AW: Summewenn und Bereich.Verschieben

04.02.2010 14:15:09

QMGünter

Hallo Renée,
vielen Dank für deine schnelle Hilfe. Habe einen Teil deiner Lösung mit Erfolg umgesetzt, den zweiten Teil mit dem Summenprodukt habe ich nicht verwenden können. Diese Funktion ist nur gut, wenn keine Text in der Zelle steht, da dann immer mit "0" gerechnet wird. D.h. die Summe ist dann immer Null!
Mit deinem Vorschlag zum Bereich.Verschieben hat mein Problem eine Lösung erhalten.
Übrigens, QM=Quality Manager hat auch gepunktet.
Thanks, Buddy.
Ciao, Günter

AW: Summewenn und Bereich.Verschieben

03.02.2010 16:06:20

Luschi

Hallo QMGünter,
ich glaube, mit der Funktion Bearbeiten.Verschieben() baust Du Dir ein Monster auf, da Du vielleicht jetzt bei der Erstellung mal durchblickst: sind aber in 2-3 Monaten Nacharbeiten , Spaltenveränderungen usw. nötig, könnte das schon anders aussehen. So ergeben schon jetzt schon der mehrere definierte Namen z.B. 'B_Corrective_Risk_Costs' keinen Zellbezug.
Überprüfen kann man das im Namensmanager. Dort den Namen auswählen und mit den Cursor in die untere Bezugszeile klicken.
Gibt es einen Referenzbezug, wird die entsprechende Tabelle angezeigt und der Tabellenbereich erhält eine gestrichelte unlaufende Linie - so wie es beim Namen 'Corrective_Risk_Costs' der Fall ist.
Zudem hast Du bei Bereich.Verschieben() die Vorraussetzung, das es innerhalb des Bereiches keine Leerzeilen geben darf und Anzahl2() nur auf Spalten mit Textinhalt verwendet werden kann.
Ich würde Dir vorschlagen, das Problem wie folgt zu vereinfachen:
- die 1. Zeile die in die Berechnung einfließen kann, steht ja fest (Zeile7)
- die letzte Zeile steht indirekt fest, nämlich 2 Zeilen vor der Zelle, in der folgender Text steht
"Summe Gesamtrisiken, alle Phasen"
und das ja z.Z. M81
- deshalb würde ich dieser Zelle einen Namen gegen (oder auch A81)
- in der Namensdefinition wird mit der Indirekt-Funktion auf diesen Namen verwiesen und die entsprechende Zeile (minus 2) errechnet
- Da du ja auch Filter definiert hast, wird die Teilergebnis-Funktion herangezogen, und die Summe zu ermitteln.
Hier mal ein kleines Beispiel dazu: https://www.herber.de/bbs/user/67733.xls
Gruß von Luschi
aus klein-Paris

AW: Summewenn und Bereich.Verschieben

04.02.2010 14:28:32

QMGünter

Hallo Luschi,
vielen Dank für deine interessanten Ideen. Du hast sicher Recht mit dem Monster, aber meine Unbeholfenheit rührt auch von der fehlenden Übung und der Unkenntnis vieler Funktionen, um die einfachere Lösung zu kreeiren. Deshalb nochmals danke für deine neue Inspiration bezüglich Indirekt-Funktion und Teilergebnis sowie der anderen Art den dynamischen Bereich zu definieren.
Nur die Beschreibung der Anzahl2 habe ich anders verstanden, als du sie mir beschrieben hast. Die Einschränkung der Funktion nur auf Spalten mit Text anwenden zu dürfen stimmt meineserachtens nicht. Es ist jeder Wert erlaubt, nur eben keine leeren Zellen.
Habe inzwischen auch mit Hilfe von Renée mein Problem lösen können. Deine Vorschläge werden ich in Richtung Vereinfachung nun auch mal überprüfen.
Ciao, Günter

Infobox / Tutorial

Dynamische Summen mit Bereich.Verschieben in Excel erstellen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Bereiche definieren: Zunächst musst Du die benötigten Bereiche definieren, die sich dynamisch anpassen sollen. Nutze dafür die Funktion BEREICH.VERSCHIEBEN. Hier sind einige Beispiele für die Definition der Bereiche:

B_Phase: =BEREICH.VERSCHIEBEN('Aktuelles Update 2010'!$D$7;;;ANZAHL2('Aktuelles Update 2010'!$D:$D);1)
B_Eintrittswahrscheinlichkeit: =BEREICH.VERSCHIEBEN('Aktuelles Update 2010'!$G$7;;;ANZAHL2('Aktuelles Update 2010'!$G:$G);1)
B_Corrective_Risk_Costs: =BEREICH.VERSCHIEBEN('Aktuelles Update 2010'!$N$7;;;ANZAHL2('Aktuelles Update 2010'!$N:$N);1)

SUMME.WENN verwenden: Nutze die SUMME.WENN-Funktion, um die Summe mit Bedingungen zu bilden. Eine Beispiel-Formel könnte so aussehen:
```
{=SUMME(WENN(B_Phase="Dev";WENN(B_Eintrittswahrscheinlichkeit=100;B_Corrective_Risk_Costs;0);0))}
```
Dynamische Summen bei Filter anwenden: Um eine dynamische Summe bei Filtern zu erstellen, kannst Du die Funktion SUMMENPRODUKT verwenden. Diese funktioniert auch, wenn Text in den Zellen steht:
```
=SUMMENPRODUKT((B_Eintrittswahrscheinlichkeit=100)*(B_Phase="Dev")*(B_Corrective_Risk_Costs))
```

Häufige Fehler und Lösungen

#BEZUG! Fehler: Dieser Fehler tritt häufig auf, wenn die definierten Namen nicht korrekt auf die Zellen verweisen. Überprüfe die Definitionen im Namensmanager und stelle sicher, dass sie korrekt sind.
Formeln funktionieren nicht nach Zeilenänderungen: Wenn Du Zeilen hinzufügst oder löschst, müssen die Bereiche möglicherweise manuell aktualisiert werden. Verwende ANZAHL statt ANZAHL2, um sicherzustellen, dass leere Zellen nicht gezählt werden.

Alternative Methoden

INDIREKT-Funktion: Du kannst die INDIREKT-Funktion verwenden, um auf dynamische Zellreferenzen zuzugreifen, die sich bei Änderungen in der Tabelle anpassen.
Teilergebnis: Wenn Du Filter verwendest, kann die TEILERGEBNIS-Funktion eine ausgezeichnete Alternative sein, um nur die sichtbaren Daten zu summieren.

Praktische Beispiele

Beispiel für eine einfache Summe:
```
=SUMME(WENN(A1:A10>10; B1:B10; 0))
```
Diese Formel summiert die Werte in B1:B10, wenn die entsprechenden Werte in A1:A10 größer als 10 sind.
Dynamische Summe bei Filter:
```
=TEILERGEBNIS(9; B1:B100)
```
Diese Formel summiert nur die sichtbaren Werte im Bereich B1:B100.

Tipps für Profis

Benutze die Namensverwaltung: Definiere klare und verständliche Namen für Deine Bereiche, damit Du leichter mit komplexen Formeln arbeiten kannst.
Vermeide komplexe Formeln: Halte Deine Formeln so einfach wie möglich, um die Fehlersuche zu erleichtern. Verwende SUMMENPRODUKT, wenn Du mit mehreren Bedingungen arbeitest.
Teste Deine Formeln: Überprüfe Deine Formeln, indem Du sie Schritt für Schritt aufbaust und sicherstellst, dass jeder Teil funktioniert, bevor Du zur nächsten Funktion übergehst.

FAQ: Häufige Fragen

1. Wie kann ich sicherstellen, dass meine dynamischen Bereiche immer aktuell sind? Stelle sicher, dass Du die ANZAHL- oder ANZAHL2-Funktion richtig verwendest, um die Anzahl der relevanten Zellen zu ermitteln.

2. Warum funktioniert SUMMENPRODUKT nicht, wenn Text in den Zellen steht? SUMMENPRODUKT kann Text nicht in Berechnungen einbeziehen. Stelle sicher, dass Du die Bedingungen so formulierst, dass sie nur numerische Werte berücksichtigen.

3. Kann ich BEREICH.VERSCHIEBEN auch auf Mac verwenden? Ja, die Funktion BEREICH.VERSCHIEBEN ist auch in der Mac-Version von Excel verfügbar. Achte jedoch darauf, dass die Syntax korrekt ist.

Beliebteste Forumthreads (12 Monate)

Summewenn und Bereich.Verschieben

Forumthread: Summewenn und Bereich.Verschieben

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Dynamische Summen mit Bereich.Verschieben in Excel erstellen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Summewenn und Bereich.Verschieben

Forumthread: Summewenn und Bereich.Verschieben

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Dynamische Summen mit Bereich.Verschieben in Excel erstellen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread