Office: Doppelte Einträge in Formel einer Zelle finden

Hallo, ich habe da ein Problem, mit dem ich mühsam versuche manuell Fehler zufinden. Ich hoffe da auf Hilfe von Euch...

Ich habe ein Excel file mit 72 Blättern, in welchem in den letzten drei Blättern die Auswertung der vorangehenden stattfindet.
Jetzt ist es so, das im Bsp. verschiedene Summen "über kreuz" zu verschiedenen Fehlerkategorien aus den Tabellen ausgelesen werden und diese dann wiederum summiert werden. Das beinhaltet dann, das eine Formel teilweise sehr viele Zellen parallel in einer Zelle summiert. Dadurch werden diese Formeln ziemlich lang und auch unübersichtlich und man kann nur mit viel Mühe und Fleiss prüfen, ob man teilweise den Wert einer Zelle doppelt ausliesst oder nicht. Das möchte ich gern vereinfachen, indem ich einem Makro diese Arbeit zuweise (z.B.) aber leider fehlen mir dafür doch einige Vorraussetzungen.

Bsp:

Zelle D4 hat folgenden Inhalt

Code:

=SUMME(D234:D252)+D6+D11+D15+D19+D23+D27+D32+D53+D63+D83+D88+D97+D115+D133+D138+D144+D159+D164+D169+D173+D180+D186+D197+D203+D206+D212+D217+D225

das setzt sich dann fort bis in Zelle BN5

der Inhalt der einzelnen Zellen ist nicht immer gleich, leider...
vielleicht weis hierzu jemand Rat

-------------


um keinen zusätzlichen Beitrag zu öffnen würde ich gern zur eben dieser Tabelle eine weitere Frage stellen:
gibt es Möglichkeiten oder auch Autotools, welche unnötigen Ballast aus einer Excel Tabelle wie dieser entfernen können (woher auch immer dieser kommt). Im Vergleich habe ich einmal getestet, das eine ähnliche Tabelle mit nur einem Blatt die aber ständig benutzt und aktualisiert wurde, beim umkopieren der Daten, um ein drittel reduziert werden konnte..., und das wäre bei einer Ladezeit bis zu 30sek bei dieser angesprochenen Datei schon einiges (Filesize 9MB steigend)

Alles in allem bedanke ich mich schon im vorraus für die Hilfe!![/code]

 

Hallo andysmith,

auch mir fehlen dazu alle Voraussetzungen. Um ein VBA-Makro zu konstruieren, muss ich unbedingt die präzisen Regeln für die von Dir angeführte Funktion kennen. Denn bisher kann ich nur diese eine Funktion, die Du angeführt hast, programmieren, was vermutlich für Dich unbefriedigend sein dürfte.

Was Deine 2. Frage betrifft: Ohne Angabe, was da bereinigt werden soll, kann man überhaupt nichts sagen. Aber auch bei genauerer Kenntnis der Aufgabenstellung wäre ich an Deiner Stelle sehr vorsichtig beim Einsatz solcher Fremdtools, die (irgendwelche) Bereinigungen machen (in einer Excel-Datei, im Dateisystem, in der Windows-Registrierung, ...)

 

Hi Andy,

zu Frage 1
wenn du das Ganze mittels Makro lösen wolltest, müsstest du jede Zelle einzeln im Code angeben, wobei du wieder vor dem Problem stündest prüfen zu müssen, ob nicht Zellen doppelt aufgeführt sind - wäre also keine sinnvolle Lösung. Ich würde es folgender Maßen lösen: fasse die Zellen sozusagen in überschaubare "Teilsummen" zusammen und bilde am Ende die Summe über diese Teilsummen. Markiere dazu bei gedrückter Strg-Taste alle benötigten Zellen im sichtbaren Bereich des Tabellenblattes, gehe auf Einfügen -> Namen -> Definieren und vergib für diesen Bereich einen Namen (z.B. Teilsumme1). Unter "Bezieht sich auf" sind bereits alle markierten Zellen aufgeführt. Zur Sicherheit kannst du noch die Füllfarbe der Zellen verändern, sodass sichtbar ist, welche Zellen bereits berücksichtigt wurden. Verschiebe dann den Tabellenausschnitt, sodass du das selbe mit den nächsten Zellen machen kannst, für die du einen weiteren Namen vergibst (z.B. Teilsumme2). Deine Formel für die Gesamtsumme würde dann heißen =SUMME(Teilsumme1;Teilsumme2). Die Füllfarbe der Zellen musst du natürlich wieder zurücksetzen.

zu Frage 2
Excel "merkt" sich die Adressen aller irgendwann einmal benutzten Zellen, auch wenn deren Inhalt oder Formatierung wieder gelöscht wurde. Mit der Tastenkombination Strg+Ende gelangst du in die letzte benutzte Zelle. Sollte diese außerhalb der Zeilen und Spalten deines aktuell benutzten Tabellenbereichs liegen, dann lösche alle Zeilen und/oder Spalten außerhalb. Beachte dabei: nicht Inhalte löschen durch Drücken von Entf., sondern über Rechtsklick -> Zellen löschen! Speichere die Mappe anschließend.
Weiterhin "merkt" sich Excel z.B. alle in Codes irgendwann definierten Variablen und Einträge, auch wenn sie gelöscht wurden. Deshalb hilft oft, wenn man die Arbeitsmappe mit deaktivierten Makros öffnet, speichert und wieder schließt.

Bis später,
Karin

 

Vielen Dank für die Antwort. Das Prinzip "Aufräumen" hat etwas gebracht wenn auch nur 100kb bei 9,5MB aber immerhin. Wenn man unnütze Spalten in den einzelnen Blättern ganz rausnehmen könnte, wäre das mit Sicherheit ein Gewinn, aber soweit ich mich auskenne geht bis auf das ausblenden der Spalten (was schon eingebaut ist) nichts.

 

Hi Andy,

wenn du "unnütze" Spalten hat, weshalb löschst du sie dann nicht anstatt sie auszublenden?

Formatiere die Zellen nur so weit wie unbedingt notwendig und erweitere die Formatierungen erst bei Erfordernis. Formatiere zusammenhängende Zellen nie einzeln sondern immer im Block. Schreibe Formeln ebenfalls nur bis zu den absolut notwendigen Zellen und ziehe sie erst bei Bedarf weiter. Verwende wenig unterschiedliche Schriftformate.

Bis später,
Karin

 

Nun bin ich etwas verwirrt, weil Zeilen und Spalten die nicht benötigt werden zu löschen, also ganz und gar zu entfernen ist mir völlig neu, es gibt anscheinend Sachen die ich noch nicht weiss, weil wenn ich Zeilen und Spalten lösche dann sind sie sofort wieder da oder habe ich da etwas verpasst??

 

Hi Andy,

was meinst du mit "...dann sind sie sofort wieder da"? Hast du nur auf "Entf." gedrückt? Damit wird nur der Zellinhalt gelöscht, nicht jedoch Zeile/Spalte an sich. Ich meinte mit Zeilen/Spalten löschen indem du sie markierst -> Rechtsklick -> Zellen löschen ausführst. Dann rücken die benutzten Zeilen/Spalten nach.

Wenn du dich noch sehr wenig mit Excel auskennst, dann solltest du dir vor derartigen Veränderung in der Tabelle eine Kopie der Arbeitsmappe als Sicherheit anlegen. Zumindest können Änderungen auch rückgängig gemacht werden, wenn du die Arbeitsmappe schließt und sie dabei nicht speicherst.

bis später,
Karin

 

Hallo!

Wenn du eine Zeile/Spalte löschst, dann wird von Excel ein neue (jungfräuliche) Zeile ganz am Ende wieder eingefügt, so daß jede Excel-Tabelle IMMER über 256 Spalten x 16384 Zeilen verfügt (ab XL07: 16384 Spalten x 1Mio Zeilen).
An diesen Zahlen kannst du nix ändern.
Aber Excel zählt jede Zelle, die irgendwann einmal belegt oder formatiert war, zum "benutzten Bereich" (UsedRange) dazu. Dies ändert sich erst beim Speichern der Datei, daher kann der bisher beschriebene Weg für dich durchaus hilfreich sein.

PS: Formatierungen (Farbe, Rahmen, usw.) erfordern eine Menge Speicherplatz (können es zumindest).