Microsoft Excel

Herbers Excel/VBA-Archiv

Summe aus ganz vielen Registern (Formel zu lang)

Betrifft: Summe aus ganz vielen Registern (Formel zu lang) von: Holger
Geschrieben am: 14.11.2014 10:03:53

Hallo,
ich habe eine Datei aus ganz vielen Registern. Zeilen und Spalten sind jeweils identisch, nur die Werte in den Zellen sind unterschiedlich.
In einem Register will ich die Summen aufaddieren.

Die Schwierigkeit liegt darin, dass die aufzusummierenden Registern nicht alle hintereinander liegen, was sich auch nicht ändern lässt. Ich kann also nicht mit einer Formel wie "Register1:Register235!S38" arbeiten.

Auch eine Umbenennung der Register ist nicht möglich (Register sind mit Städtenamen belegt, und deshalb relativ lang).

Durch die Anzahl der Register und die langen Registernamen wird die einfache Addition mit "+" oder auch mit "Summe" zu lang, die Formellänge ist in Excel begrenzt.
Hat hier einer eine Idee, wie ich das Problem lösen kann?
Gruß an alle

  

Betrifft: AW: Summe aus ganz vielen Registern (Formel zu lang) von: Rudi Maintaire
Geschrieben am: 14.11.2014 10:24:02

Hallo,
hol dir die zu summierenden Werte auf ein separates Blatt und summiere die.
A1: =Oberhausen!S38
A2: =Köln!S38
A3: =Frankfurt!S38
etc.
=Summe(A1:A200)

Gruß
Rudi


  

Betrifft: AW: Summe aus ganz vielen Registern (Formel zu lang) von: Holger
Geschrieben am: 14.11.2014 11:43:35

Hallo Rudi,
das ist leider schwer umzusetzen, da die Tabellen aus mehreren Zeilen und Spalten bestehen. Insgesamt pro Tabellenblatt 518 Zeilen und 14 Spalten, das macht dann 7.252 ausgefüllte Zellen pro Tabellenblatt.
Es wäre daher schon hilfreich, die Summe pro Zelle exakt zu bilden ohne Umweg, also die Zelle S38 bildet die Summe aus Oberhausen!s38+Köln!s38+Frankfurt!s38 u.s.w.

Gibt es noch eine "elegantere" Lösung???


  

Betrifft: AW: Summe aus ganz vielen Registern (Formel zu lang) von: Rudi Maintaire
Geschrieben am: 14.11.2014 11:48:30

Hallo,
gibt es ein Kriterium, welche Blätter summiert werden sollen?
Möglicherweise kann man eine UDF schreiben.

Gruß
Rudi


  

Betrifft: AW: Summe aus ganz vielen Registern (Formel zu lang) von: Holger
Geschrieben am: 14.11.2014 12:58:22

Hallo Rudi,
solch ein Kriterium gibt es nicht einheitlich, aber die zu summierenden Register enden immer auf folgenden Begriffen:
Overhead
Vertrieb
Werkstatt
Lager
Ergebnis
HV

Davor ist fast immer der Städtenamen zu finden.

VG Holger


  

Betrifft: AW: Summe aus ganz vielen Registern (Formel zu lang) von: Rudi Maintaire
Geschrieben am: 14.11.2014 13:19:39

Hallo,
immer alle addieren oder nur alle Lager, alle Werkstatt etc?

Function BlattSumme(strZelle As String)
  Dim wks As Worksheet, arrSheets, i As Integer
  arrSheets = Array("*overhead", "*vertrieb", "*werkstatt", "*lager", "*ergebnis", "*hv")
  For Each wks In Worksheets
    For i = 0 To UBound(arrSheets)
      If LCase(wks.Name) Like arrSheets(i) Then
        BlattSumme = BlattSumme + wks.Range(strZelle)
      End If
    Next i
  Next wks
End Function

Function BlattSumme2(strZelle As String, strTyp As String)
  Dim wks As Worksheet
  strTyp = "*" & LCase(strTyp)
  For Each wks In Worksheets
    If LCase(wks.Name) Like strTyp Then
      BlattSumme = BlattSumme + wks.Range(strZelle)
    End If
  Next wks
End Function

In der Tabelle
=Blattsumme("A1") oder wenn immer die Summe in der die Formel steht: =Blattsumme(adresse(zeile(); Spalte()))
oder =Blattsumme2("A1";"Lager") oder wenn immer die Summe in der die Formel steht: =Blattsumme(adresse(zeile();Spalte());"Lager")

Gruß
Rudi


  

Betrifft: AW: Summe aus ganz vielen Registern (Formel zu lang) von: Holger
Geschrieben am: 14.11.2014 15:13:54

Hallo Rudi,

wie starte ich denn den Code? Ich habe den oberen Code in ein Modul kopiert und wollte mit F8 die Schritte beobachten.

Jedes aufzusummierende Blatt hat in den Spalten A, B und C pro Zeile eine Beschreibung, die in den Registern immer identisch ist. Ab Spalte D sind ie Monate hinterlegt. Also z. B.
SPalte A Spalte B Spalte C D: Januar E: Februar F: März u.s.w.
100 *300 Umsatz Inland Material
110 *300 Umsatz Inland Leistung
400 *600 Materialaufwand Fremde 19%
u.s.w.

In Blattsumme alle *Lager, *Werkstatt, *Vertrieb, *Overhead, *Ergebnis addieren.

Viele Grüße und vielen, vielen Dank schon im Voraus und bisher.
Ich werde heute nicht mehr dazu kommen, das Ganze weiter zu testen. Aber Montag früh geht es weiter.


  

Betrifft: AW: Summe aus ganz vielen Registern (Formel zu lang) von: Holger
Geschrieben am: 17.11.2014 09:28:19

Hallo an alle,

bei dem Vorschlag von Rudi habe ich das Problem, dass ich nicht weiß, wie ich eine Function starten muss. Kann mir jemand helfen??


  

Betrifft: AW: Summe aus ganz vielen Registern (Formel zu lang) von: Rudi Maintaire
Geschrieben am: 17.11.2014 11:06:08

Hallo,
hab ich doch geschrieben!
In der Tabelle =Blattsumme("A1") etc.

Gruß
Rudi


  

Betrifft: AW: Summe aus ganz vielen Registern (Formel zu lang) von: Holger
Geschrieben am: 17.11.2014 11:31:20

Hallo Rudi,
vielen Dank für Deine Rückmeldung. Für mich war das gar nicht so klar. VBA mit Standardschleifen ist für mich OK, weitergehende Programmierungen aber nicht. Und ich kann nur mit "sub" arbeiten, nicht mit "Function". Aber ich versuche mal, mich hier durchzuhangeln. Deswegen ein großes Danke für Deine Hilfe.
Leider klappt das noch nicht bei mir. Ich habe die Formel =Blattsumme("D5") in die Zelle D5 des Registers Blattsumme geschrieben. Als Ergebnis kommt #NAME?
D5 deswegen, da ab da Werte in den Registern auftauchen.
Den Code habe ich ein klein wenig bei der Bezeichnung der Register geändert, da der Standort nach der Bezeichnung Overhead u.s.w. kommt:

Sub BlattSumme(strZelle As String)
  Dim wks As Worksheet, arrSheets, i As Integer
  arrSheets = Array("Overhead*", "Vertrieb*", "Werkstatt*", "Lager*", "Ergebnis*", "HV*")
  For Each wks In Worksheets
    For i = 0 To UBound(arrSheets)
      If LCase(wks.Name) Like arrSheets(i) Then
        BlattSumme = BlattSumme + wks.Range(strZelle)
      End If
    Next i
  Next wks
End Sub
Hast Du eine Idee, woran das liegt? Ich könnte Dir gerne eine Datei als PN zukommen lassen. Dann wäre es vielleicht einfacher. Als link hier offiziell für alle sichtbar eher ungerne, da hier ein Kontenschema gezeigt wird, was evtl. vertraulich wäre.

Grüße mit einem Fragezeichen vor den Augen
Holger


  

Betrifft: AW: Summe aus ganz vielen Registern (Formel zu lang) von: Holger
Geschrieben am: 18.11.2014 09:11:28

Hallo Rudi,
der Code gestern von mir war natürlich Quatsch. Sorry, ich hatte einen falschen Code kopiert.

Diesen Code von Dir habe ich in das Blatt Blattsumme kopiert. Als Ergebnis kommt aber trotzdem nur #NAME!
Weißt Du, woran das liegt?

Damit es verständlicher wird, habe ich die Datei auf das Nötigste reduziert und hochgeladen. Wäre toll, wenn ich noch einmal eine Hilfe bekomme. Mit der hochgeladenen Datei hoffe ich, dass Du oder jemand anderes eine Lösung hat.
https://www.herber.de/bbs/user/93845.xlsm

Function BlattSumme(strZelle As String)
      Dim wks As Worksheet, arrSheets, i As Integer
      arrSheets = Array("overhead*", "vertrieb*", "werkstatt*", "lager*", "ergebnis*", "hv*")
      For Each wks In Worksheets
        For i = 0 To UBound(arrSheets)
          If LCase(wks.Name) Like arrSheets(i) Then
            BlattSumme = BlattSumme + wks.Range(strZelle)
          End If
        Next i
      Next wks
    End Function



  

Betrifft: AW: Summe aus ganz vielen Registern (Formel zu lang) von: Holger
Geschrieben am: 19.11.2014 13:07:21

Hallo,

mein Dank an Rudi, aber auch an Hans. Ich habe es hinbekommen. Falls jemand das hier liest: Der Code gehört in ein Standardmodul, nicht in ein Klassenmodul.
Danke, Rudi, für den Code. Echt geil!!!
Gruss


 

Beiträge aus den Excel-Beispielen zum Thema "Summe aus ganz vielen Registern (Formel zu lang)"