Das Archiv des Excel-Forums

Differenz zweier Summe im dynamischen Bereich

Bild

Betrifft: Differenz zweier Summe im dynamischen Bereich
von: eddie

Geschrieben am: 04.07.2008 21:59:49

Hallo zusammen,
ich habe ein kleines Problem mit einer Differenz von 2 Summen.
Ich arbeite mit einer Anwendung mit mehreren Tabellenblättern, wichtig sind hier aber nur 2.
In dem Tabellenblatt „Eins“ soll die gesuchte Funktion erstellt werden, die mir das gewünschte Ergebnis bringt. Dabei bezieht sich die Funktion ausschließlich auf Daten des Tabellenblattes „Zwei“.
Dort stehen in der Spalte A (von A2 bis A200) 4stellige Zahlen, beginnend mit der 3000 und endend mit der 3290, und 3310 bis 3500, jeweils in 10 Abständen, also 3000, 3010, 3020, usw. jeweils untereinander. Diesen 4stelligen Zahlen in der Spalte A sind in Spalte D jeweils in der gleichen Zeile Eurobeträge zugeordnet.
Ich möchte jetzt Folgendes:
Excel soll alle Eurobeträge addieren, die den Ziffern 3000 bis 3290 zugeordnet sind.
Von dieser Summe soll Excel die Summe aller Eurobeträge abziehen, die den Ziffern 3310 bis 3500 zugeordnet sind.
Jetzt kommt die Schwierigkeit::
In der Spalte A stehen nicht immer die gleiche Anzahl von Ziffern zwischen 3300 und 3290, bzw. 3310 bis 3500.
D.h., ich kann nicht genau einen festen Bereich zuordnen.
Meine Funktion funktioniert deshalb immer nur dann, wenn ich den Bereich genau angebe:
RUNDEN(SUMMEWENN(Aufgabe1!$A2:$A22;"<3300";Aufgabe1!$D2:$D22)-SUMMEWENN(Aufgabe1!$A2:$A22;">3300";Aufgabe1!$D2:$D22);2)
Excel soll aber einfach die Spalte A ab A2 lesen, dort alle Ziffern zwischen 3000 und 3290 suchen, die zugeordneten Eurobeträge addieren, danach alle Ziffern zwischen 3310 bis 3500 suchen, wieder die Eurobeträge addieren und von obiger Summe abziehen.
Es kann dabei durchaus vorkommen, dass von dem ersten Zahlenbereich 7 Ziffern untereinander stehen, von dem zweiten nur drei oder umgekehrt.
Wer kann mir helfen ?
Grüße
Manfred

Bild

Betrifft: AW: Differenz zweier Summe im dynamischen Bereich
von: Matthias L
Geschrieben am: 05.07.2008 00:03:16
Hallo eddi


{=SUMME(SUMME(WENN((A2:A11>3000)*(A2:A11<3290);D2:D11))-SUMME(WENN((A2:A11>3310)*(A2:A11<3500); D2:D11))) }


Achtung MatrixFormel
geschweifte Klammern nicht mit eingeben.
sondern die Formel mit STRG+Shift+Enter abschließen.
Dadurch werden die Klammern erzeugt.
A2:A11 entsprechen an Deinen Bereich anpassen
Beispiel:
https://www.herber.de/bbs/user/53611.xls
Gruß Matthias

Bild

Betrifft: AW: Differenz zweier Summe im dynamischen Bereich
von: eddie

Geschrieben am: 05.07.2008 11:18:47
Hallo Matthias,
danke zunächst einmal für deine schnelle Antwort. Es ergibt sich noch ein Problem, wie du in der Beispieldatei sehen wirst. Dort schaue dir bitte den Kommentar zu D29 an.
Ich möchte bei der Matrixfunktion nur einmal den Bereich A2 bis A190 angeben, auch wenn ich ihn nicht mit besagten Ziffern ausfülle, sondern mit anderen. Bleiben einige Ziffern leer, bekomme ich NV, das soll nicht sein. Außerdem wirst du sehen, dass beispielsweise nach A2 mit der Ziffer 3010 in A3 und A4 jeweils die Ziffer um eine erhöht ist.
Schaue einfach rein und stelle Rückfragen, wenn mein Anliegen noch nicht klar ist.
Danke
Manfred
https://www.herber.de/bbs/user/53614.xls

Bild

Betrifft: AW: Differenz zweier Summe im dynamischen Bereich
von: Matthias L
Geschrieben am: 05.07.2008 21:44:37
Hallo Manfred
Kann mit Deiner Tabelle leider nicht viel anfangen.
Die MatrixFormel funktioniert doch oder ?
hier nochmal ein Beispiel:
https://www.herber.de/bbs/user/53619.xls
Die Umsetzung in Deiner Datei ist mir zu komplex, das mußt Du selbst in die Hand nehmen.
Gruß Matthias

Bild

Betrifft: AW: Differenz zweier Summe im dynamischen Bereich
von: eddie

Geschrieben am: 06.07.2008 00:52:58
Hallo Matthias,
danke, sie funktioniert.
Ich habe nur eine Frage:
a)Das Ergebnis dieser Mastrixfunktion hole ich mir per einfachem Zellbezug in eine andere Zelle (z.B. Erstes Arbeitsblatt E25). Dort zeigt mir Excel den Wert 0,00€ an, obwohl das Ergebnis weit über 500000,00€ liegt. Woran liegt das?
b) Hole ich mir jetzt den Wert dieser Zelle (E25) aus dem zweiten Arbeitsblatt per sverweis, zeigt Excel mir den Fehler "Zirkelbezug" trotzdem erhalte ich den korrekten Wert über 500000,00€
Gibt es dafür eine Erklärung?
Außerdem bekomme ich jetzt immer beim Öffnen die Meldung Zirkelbezug., was mich nicht stört, da es ja funktioniert.
Das Ergebnis der M-funktion ist nämlich nichts anderes als der Wert, der der Ziffer 3300der Spalte A in der Spalte D zugeordnet ist.
Danke
Manfred

Bild

Betrifft: den Excel Hinweis auf Zirkelbezug ...
von: neopa
Geschrieben am: 06.07.2008 17:48:05
Hallo Manfred,
... sollte man nicht einfach abtun, denn infolge dessen kommt es zu nicht nachvollziehbaren fehlerhaften Berechnungen. Mit Hilfe der von Excel angebotene spez. Symbolmenüs bekommt man jedoch eine brauchbare Hilfe die Zellen aufgelistet zu bekommen, deren Formeln den Zirkelbezug herbeiführen. Dazu siehe auch die OnlineHilfe. (Ein Beipiel eines einfachsten Zirkelbezuges wäre z.B.: A:=B1 und B1=A1+1)
Wenn man die Zirkelbezugszellen lokalisiert hat, muss man die Formeln entsprechend abändern, dass Excel eben keinen Zirkelbezug mehr anzumeckern hat.
Deine eingestellte Tabelle enthält jedoch jede Menge Verknüpfungen zu anderen uns unbekannten Arbeitsmappen, so dass dies hier nicht nachvollziehbar ist und auch kein entsprechend konkreterer Tipp gegeben werden kann. Natürlich naheliegend ist, dass Deine Formeln in Spalte D in Kombination mit Deinen weiteren uns unbekannten Auswertungen eine Ursache des Zirkelbezugs sein kann.
Noch eine kleine Anmerkung: Matthias Formel ist noch bzgl. der äußeren SUMME()-"Formelklammerung" zwar nicht falsch aber doppelt gemoppelt und somit einsparbar. Ausreichend wäre z.B.:


=SUMME(WENN((A2:A99>3000)*(A2:A99<3290);D2:D99))-SUMME(WENN((A2:A99>3310)*(A2:A99<3500);D2:D99))


Gruß Werner
.. , - ...

Bild

Betrifft: AW: den Excel Hinweis auf Zirkelbezug ...
von: eddie

Geschrieben am: 06.07.2008 18:29:51
Hallo Werrner,
danke für deine Antwort. Dass ich mit dem Zirkelbezug nicht spaßen kann, weiß ich, ich helfe mir dann mit einem kleinen Umweg. Meine Tabelle enthält dermaßen viel Verknüpfungen, dass ich allmählich selbst den Überblick zu verlieren drohe.
Grund: Ich wollte anfangs nur ein kleines Problem gelöst bekommen, habe dann im forum super Hilfe bekomme, größtenteils mit Makroprogrammierung, die ich nicht beherrsche.
Und kurz vor Schluss, wenn alles fast perfekt gelaufen wäre, hat mich mein Helfer verlassen (jetzt funktioniert die Anwendung zu 70% automatisch, den Rest muss ich per Hand machen). Wenn Ihr also mal drüberschauen wollt, dann sende ich euch mein "Päckchen". Würde mir sicher helfen.
P.S. Die Formel habe ich entsprechend gekürzt.
Danke
Manfred

 Bild

Excel-Beispiele zum Thema "Differenz zweier Summe im dynamischen Bereich"

Datumsdifferenz in der Form "12 Jahre, 3 Monate, 4 Tage" download Differenz aus Start- und Endzeit in Sekunden download
Zeitdifferenz in Schulstunden (45 Minuten) errechnen download Werte summieren, in erste freie Zeile eintragen, Differenz errechnen download
Zeitdifferenzen auf Viertelstunden ab- und aufrunden download Daten in jeweils einer Spalte zweier Tabellen vergleichen download
Dynamischen Dialog erstellen download Summe über dynamischen Bereich bilden download
Leerzeichen aus einem Bereich löschen download Befindet sich die aktive Zelle in einem bestimmten Bereich? download
Mehrbereichsauswahl auf eine Seite drucken. download Spalten einer Mehrbereichsauswahl ausblenden. download
Bereich mit Maximalwert markieren download Benannten Bereich erweitern download
Bereich in RefEdit-Element eingeben und auslesen download Benutzerdefinierte SVERWEIS-Funktion über mehrere Bereiche download
Letzter Wert aus einem Bereich download Bereiche bei Mehrfachauswahl prüfen download
Auswahl eines Zellbereiches verhindern download Bereiche in Abhängigkeit eines Zellinhaltes drucken download
Den selben Bereichsnamen in mehreren Tabellenblättern download Leere Zeilen und Spalten in ausgewähltem Bereich löschen download
URLs eines ausgewählten Bereiches in HyperLinks umwandeln download Werte aus Zellbereich in eine Matrix übernehmen und auslesen download
Zellauswahl auf den ausgewählten Bereich beschränken download Berechnung des ausgewählten Bereiches in der Statusleiste download
Bereiche auf Übereinstimmung vergleichen download Scrollbereich begrenzen download
Tabellenblattbereich als Email versenden download Serie von Bereichsnamenteilen einer Arbeitsmappe ändern download
Worte in einem vorgegebenen Bereich zählen download Bereichsnamen ermitteln und als Kommentare anzeigen download
Bereichsnamen in UserForm-ListBox auflisten und editieren download Einen vorgegebenen Tabellenblattbereich in neue Arbeitsmappe download
Werte aus einem nicht zusammenhängendem Bereich auslesen download Druckbereich variabel gestalten download
Tabellenblattbereich sortiert als Zellkontextmenü download Überschriften aus Spalte C in Spalte D in den relevanten Bereich download
Druckbereich bis zum ersten Nullwert festlegen download Werte eines bestimmten Wochentages und eines Zeitbereiches addieren download
Markierten Bereich mit jeweiligem Mwst-Satz multiplizieren download Cursor in bestimmtem Zellbereich in Eieruhr verwandeln download
Maximalwert aus einem Bereich ermitteln und in Spalte A eintragen download Formelbereich in Einzelelemente aufschlüsseln download
Datenreihenbeschriftungs-Quellbereich ausserhalb des Datenbereiches download Diagramm-Datenbereich über Schaltfläche festlegen download
Abfrage zum Kopierbereich von Startzelle, Zeilen- und Spaltenanzahl download Einen von einer Schlüsselziffer abhängigen Bereich kopieren download
Namen eines ausgewählten Bereiches auslesen download Den in einer Zelle genannten Bereich eines Blattes drucken download
Zugriff auf Zellen unter Verwendung eines Bereichsnamens download Bereich aus anderer Tabelle für 2 Sekunden anzeigen download
Nur die sichtbaren Zellen eines AutoFilter-Bereiches einlesen download Aktuellen Bereich in den Spalten A:F als Druckbereich download
Über UserForm-RefEdit-Steuerelement Bereich auswählen download Über InputBox festzulegenden Bereich kopieren download
Anzahl der Übereinstimmungen in zwei Bereichen ermitteln download