SUMMEWENNS über mehrere Tabellenblätter

[Tatjana]

Liebe Forumsmitglieder,

leider komme ich trotz Grübeln und Internetsuche nicht weiter...

Ich habe folgende Datei:
jeweils ein Tabellenblatt für Kostenstelle 1, Kostenstelle 2 etc.;
innerhalb dieser Blätter sind zeilenweise Kostenarten (Spalte A) mit Planwerten (Spalte AG) dargestellt;
die Nummer der Kostenstelle steht in C5.

Für einen SAP-Upload möchte ich diese Informationen nun in der gleichen Datei in einem separaten Blatt zusammenführen, d. h.
Kostenstelle 1, Kostenart 1, Wert
Kostenstelle 1, Kostenart 2, Wert
Kostenstelle 2, Kostenart 1, Wert
Kostenstelle 2, Kostenart 2, Wert
... es handelt sich übrigens um mehr als 60 Kostenstellen und 140 Kostenarten ...

Bisher habe ich folgendes ausprobiert:
Namen aller Tabellenblätter in das separate Blatt eingetragen und dieser Liste den Bereichsnamen "Blattnamen" gegeben;
einen ersten Block für Kostenstelle 1, d. h. 140 Zeilen für Kostenstelle 1 mit allen Kostenarten (diesen Block würde ich manuell für jede Kostenstelle ergänzen);
Formel
=SUMMENPRODUKT(SUMMEWENNS(INDIREKT("'"&Blattnamen&"'!AG:AG");INDIREKT("'"&Blattnamen&"'!C5");B2;INDIREKT("'"&Blattnamen&"'!A:A");H2))
eingefügt, wobei B2 die Kostenstelle und H2 die Kostenart enthält.

Leider bekomme ich damit den Fehler "#WERT!" ... wer weiß, was da schief gelaufen sein könnte?

Vielen lieben Dank vorab.

Grüße
Tatjana

[steve1da]

Hola,

ich tippe mal auf Überschriften in Zeile 1 der jeweils angesprochenen Spalten.
Verwende Bereich an Stelle von ganzen Spalten.

Gruß,
steve1da

[Tatjana]

Hallo

Ich habe mal die Formel in
=SUMMENPRODUKT(SUMMEWENNS(INDIREKT("'"&Blattnamen&"'!AG17:AG1000");INDIREKT("'"&Blattnamen&"'!C5");B2;INDIREKT("'"&Blattnamen&"'!A17:A1000");H2))
geändert - das hilft leider auch nicht.

Diese Blätter stellen jeweils die GuV einer Kostenstelle dar, sprich einzelne Kostenarten für Umsatz, Material etc. und hier wiederum Zwischensummen - kann es sein, dass Excel sich an den verbundenen Zellen bei der Bezeichnung für die Zwischensumme stört? In Spalte A steht ja die Kostenart; das Wort "Umsatzerlöse" steht quasi in A bis C.

Grüße
Tatjana

[Jockel]

Hallo, ist dieser Kriterienbereich denn richtig..?

Code:

INDIREKT("'"&Blattnamen&"'!C5")

Hallo

... kann es sein, dass Excel sich an den verbundenen Zellen ... stört?
Grüße
Tatjana

Hallo, verundene Zellen erschweren in sller Regel Auswertungen. (Wenige) Ausnahmen bestätigen die Regel...

[Peter]

Hallo,

vielleicht ist diese benutzerdefinierte Funktion von Thomas eine Alternative:

Code:

Public Function SummeWennSTabellen(Tab1 As String, _
                                 Tab2 As String, _
                                   Summe_Bereich As Range, _
                                   KritBereich1 As Range, _
                                   Suchkriterium1 As String, _
                                   KritBereich2 As Range, _
                                   Suchkriterium2 As String) As Variant

'© Thomas Ramel / 27.09.2010
'Funktion zur Anwendung von SUMMEWENNS() über mehrere Tabellenblätter
'Mit angegeben werden die Tabellenblattnamen von...bis,
'sowie die üblichen Parameter für SUMMEWENNS()
'Zur automatischen Aktualisierung im Tabellenblatt den folgenden Term
'anhängen: +(0*JETZT()) und F9 drücken um zu aktualisieren
'Also z.B. wie folgt: SummeWennSTabellen("Tab1";"Tab8";C1:C10;A1:A10;A1;B1:B10;B1)+(0*JETZT())

Dim intI                      As Integer
Dim intJ                      As Integer
Dim intTab                    As Integer
Dim Summe                     As Double

    If Val(Application.Version) < 12 Then
        SummeWennSTabellen = "Nur ab xl2007 einsetzbar"
        Exit Function
    End If

    If Suchkriterium1 = "" Then
        SummeWennSTabellen = 0
        Exit Function
    End If

    intI = Worksheets(Tab1).Index
    intJ = Worksheets(Tab2).Index

    For intTab = intI To intJ
        Set KritBereich1 = ActiveWorkbook.Worksheets(intTab) _
                           .Range(KritBereich1.Address)

        Set KritBereich2 = ActiveWorkbook.Worksheets(intTab) _
                           .Range(KritBereich2.Address)

        Set Summe_Bereich = ActiveWorkbook.Worksheets(intTab) _
                            .Range(Summe_Bereich.Address)

        Summe = Summe + Application.WorksheetFunction.SumIfs _
                (Summe_Bereich, KritBereich1, Suchkriterium1, KritBereich2, Suchkriterium2)

    Next intTab

    SummeWennSTabellen = Summe
End Function

Die Erläuterung zur Anwendung bzw. dem Aufbau der Formel findet sich im Kommentar des Codes:

'Funktion zur Anwendung von SUMMEWENNS() über mehrere Tabellenblätter
'Mit angegeben werden die Tabellenblattnamen von...bis,
'sowie die üblichen Parameter für SUMMEWENNS()
'Zur automatischen Aktualisierung im Tabellenblatt den folgenden Term
'anhängen: +(0*JETZT()) und F9 drücken um zu aktualisieren
'Also z.B. wie folgt: SummeWennSTabellen("Tab1";"Tab8";C1:C10;A1:A10;A1;B1:B10;B1)+(0*JETZT())

[Käpt'n Blaubär]

Hallo Tatjana,

Tatjana schrieb:

... kann es sein, dass Excel sich an den verbundenen Zellen ... stört? 
Grüße
Tatjana

tue Dir selber einen großen Gefallen und löse die verbundenen Zellen auf.
Du wirst damit niemals glücklich werden.
Da man als Anfänger(in) verbundene Zellen eigentlich immer nur einsetzt,
um eine gewisse Optik zu erzeugen, sei versichert, das kriegt man auch anders hin.
Verbundene Zellen machen gewöhnlich mehr Ärger als das sie Nutzen.

[Tatjana]

Hallo,

erstmal danke für die Rückmeldungen

Ich habe die Formel nun in
=SUMMENPRODUKT(SUMMEWENNS(INDIREKT("'"&Blattnamen&"'!AG1:AG10000");INDIREKT("'"&Blattnamen&"'!C1:C10000");B2;INDIREKT("'"&Blattnamen&"'!A1:A10000");H2))
geändert; jetzt erscheint zumindest "0,00" und nicht mehr "#WERT!".

Die Formate stimmen überein:
Kostenstelle/-art sind als Standard, der Planwert als Zahl hinterlegt (sowohl in der Übersicht als auch in den einzelnen Blättern).

Ich habe auch schon die verbundenen Zellen wieder aufgelöst und den Befehl "Text in Spalten" für die Kostenart durchlaufen lassen - ohne Erfolg

Hat noch jemand eine Idee?

Vielen lieben Dank.
Tatjana

[steve1da]

Hola,

dann lade doch bitte eine Beispieldatei hoch.

Gruß,
steve1da

[Tatjana]

Hallo,

anbei eine stark vereinfachte Beispiel-Datei - aber auch da erscheint 0  


  Test.xlsx (Größe: 12,26 KB / Downloads: 30)

Viele Grüße
Tatjana

[steve1da]

Hola,

warum nennst du die Kostenstellen-Tabellen nicht genauso wie die abzufragenden Kostenstellen (also ohne Leerzeichen)? Dann ginge das so:

Code:

=SUMMEWENNS(INDIREKT(A2&"!C:C");INDIREKT(A2&"!B:B");B2)

Gruß,
steve1da