Formeln ohne automatische Anpassung kopieren....
Thomas B.
ich denke für den Haus und Unigebrauch kenn ich mich mehr oder weniger gut
in Excel aus, aber auf der Arbeit (Job) sind die Dimensionen der Tabellen
doch gigantisch anders.......
Also es geht um folgendes:
Ich habe heute in einer Excel-Datei (eine von mehren hundert, mit gleichem
Aufbau) eine neue Spalte eingefügt, in der per Formel eine relativ
komplizierte Formel enthalten ist.....
Diese Spalte mit Formeln will ich nun in die anderen Dateien kopieren.
Blöderweise stellt Excel dann aber immer einen Bezug in den Formeln zu der
Datei her, aus der ich kopiert habe.
Er soll die Formeln aber so kopieren wie sie sind, unverändert, nur dann
stimmt es.
Über "Inhalte einfügen" als Formel, etc. hatte ich keinen erfolg...
Ich habe schon gegoogelt und einen Trick gefunden, indem man per
"suchen/ersetzen" das "="-Zeichen durch "#" ersetzt, die Formeln also in
einen Text umwandelt, dann kopiert und anschließend wieder umgekehrt zu
einer Formel vrewandelt.
Ich kann aber fast nicht glauben, dass das nicht etwas eleganter geht.
Soetwas brauchen doch gerade Firmen mit Auswertungstabellen (wie wir im
Chemielabor halt tausende Exceltabellen.....) ständig.....
Also ich hab heute mit Excel 2003 gearbeitet, es steht allerdings auch 2007
in der Firma zur Verfügung. Falls es für eine der beiden Versionen eine
Tastenkombination oder ähnliches gibt, wäre ich für eine Information
dankbar....
Gruß Thomas
Höcherl Thomas
hast Du schon mal folgendes versucht ?
1. Formelzelle markieren und bearbeiten
(F2 bzw. Doppelklick)
2. nur den Formeltext kopieren
3. in neue Tabelle die Zelle markieren und bearbeiten
(F2 bzw. Doppelklick)
4. die Formel einfügen
5. die Formel dann nach unten zu kopieren
(STRG+U bzw. Doppelklick rechts unten an der Formelzelle)
Gruß
Tom
Thomas B. schrieb:
Thomas B.
> (F2 bzw. Doppelklick)
> 2. nur den Formeltext kopieren
>
> 3. in neue Tabelle die Zelle markieren und bearbeiten
> (F2 bzw. Doppelklick)
>
> 4. die Formel einfügen
>
> 5. die Formel dann nach unten zu kopieren
> (STRG+U bzw. Doppelklick rechts unten an der Formelzelle)
Das geht in diesem Fall nicht. Die Formeln ( es sind ca. 30 Zeilen Formeln
in einer Spalte) können sind individuell angepasst und können nicht durch
einfaches herunterziehen der ersten Formel erstellt werden.
Es muss wirklich der Block mit Formeln so kopiert werden, wie er ist,
unverändert.
Hintergrund:
Die ca. 30 Formeln in einer Spalte der Tabelle 2, nehmen Bezug auf Daten in
Tabelle1, die dort allerdings vollkommen anders sortiert sind......
Ich könnte nun auch Makro aufzeichnen für diesen speziellen Falll....
Naja ich wollte aber mal fragen, ob's da nicht was einfacheres gibt, weil
ich mir eigentlich gar nicht vorstellen kann, dass eine so elementare
Funktion fehlt. Ich bin mir sicher, dass ich dies in Zukunft noch öfter
vermissen werde....
Gruß Thomas
Thomas Ramel
Thomas B. schrieb am 04.04.2008
> Ich habe heute in einer Excel-Datei (eine von mehren hundert, mit gleichem
> Aufbau) eine neue Spalte eingefügt, in der per Formel eine relativ
> komplizierte Formel enthalten ist.....
> Diese Spalte mit Formeln will ich nun in die anderen Dateien kopieren.
> Blöderweise stellt Excel dann aber immer einen Bezug in den Formeln zu der
> Datei her, aus der ich kopiert habe.
Ja, und das ist in aller Regel auch richtig so.
Bezüge zu anderen Tabellenblättern bleiben auf die Originaldaten erhalten
und werden nicht auf die neue Mappe umgelenkt.
Das kann zwar durch die Verwendung der Funktion INDIREKT() anders
gehandhabt werden, macht die Formeln aber nicht gerade einfacher.
Vielleicht nennst Du mal eine der Formeln, dann können wir uns Gedanken
über einen möglichen Umbau machen.
> Ich habe schon gegoogelt und einen Trick gefunden, indem man per
> "suchen/ersetzen" das "="-Zeichen durch "#" ersetzt, die Formeln also in
> einen Text umwandelt, dann kopiert und anschließend wieder umgekehrt zu
> einer Formel vrewandelt.
Ja, das ist ein gut gangbarer Weg, der auch nicht wirklich viel mehr Zeit
in Anspruch nimmt.
Du könntest auch mit Bearbeiten/Ersetzen in der neuen Mappe den Teil des
Bezuges entfernen, der auf die alte Mappe verweist.
Oder aber - und das ist meine Empfehlung- Du änderst nach dem Kopieren der
Formeln in der neuen Mappe über Bearbeiten/Verknüpfen die Quelldatei neu
fest und wählst dort als neue Datei die aktuelle in der die Formeln nun neu
zu finden sind.
> Ich kann aber fast nicht glauben, dass das nicht etwas eleganter geht.
> Soetwas brauchen doch gerade Firmen mit Auswertungstabellen (wie wir im
> Chemielabor halt tausende Exceltabellen.....) ständig.....
Ein bestehendes Berechnungs-System zu erweitern und umzubauen ist immer
eine grössere Herausforderung. Speziell dann wenn die Mappe mehrfach
kopiert oder mit einer Vorlage neu erstellt worden ist.
Da musst Du dann den Vorgang prinzipiell in jeder Mappe wiederholen.
Die Entwickler standen vor der Gretchenfrage ob ein Bezug zu einem anderen
Tabellenblatt derselben Mappe in die neue Mappe umgeleitet werden soll oder
ob er auf der Original-Zelle belassen werden soll.
Für das ein haben sie sich entschieden - wäre der Entscheid umgekehrt
ausgefallen hätte jemand anders einen anderen Wunsch.
Mit freundlichen Grüssen
Thomas Ramel
--
- MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2003 SP-2]
Microsoft Excel - Die ExpertenTipps
Thomas B.
> Bezüge zu anderen Tabellenblättern bleiben auf die Originaldaten erhalten
> und werden nicht auf die neue Mappe umgelenkt.
Macht auch oft Sinn, das stimmt schon, aber nicht immer.
> Das kann zwar durch die Verwendung der Funktion INDIREKT() anders
> gehandhabt werden, macht die Formeln aber nicht gerade einfacher.
>
> Vielleicht nennst Du mal eine der Formeln, dann können wir uns Gedanken
> über einen möglichen Umbau machen.
Die Formel lautet z.B.
=WENN(UND(Tabelle1!C5>=Tabelle1!A6;Tabelle1!C5<=Tabelle1!E5);"J";"")
(Da die Daten in Tabelle 1 aber anders sortiert sind, kann nicht einfach
eine Formel kopiert werden und per runterziehen die anderen vervollständigt
werden....)
(lustig, dabei ist mir grad mal aufgefallen, dass ich bei meiner Version zu
Hause, nicht "AND" benutzen kann, sondern "UND" schreiben muss, auf der
Arbeit konnte ich "WENN" und "AND" benutzen, wobei mir dort mein eigener
Sprachsalat gar nicht mal aufgefallen ist ;-) aber das nur am Rande....)
>> Ich habe schon gegoogelt und einen Trick gefunden, indem man per
>> "suchen/ersetzen" das "="-Zeichen durch "#" ersetzt, die Formeln also in
>> einen Text umwandelt, dann kopiert und anschließend wieder umgekehrt zu
>> einer Formel vrewandelt.
>
> Ja, das ist ein gut gangbarer Weg, der auch nicht wirklich viel mehr Zeit
> in Anspruch nimmt.
Naja, bei 10 Excel-Dateien mag das gehen, hier geht es leider um ca. 100
Stück. Wobei ich denke, dass man für diese Umwandlung halt auch ein kleines
Makro schreiben kann....
> Du könntest auch mit Bearbeiten/Ersetzen in der neuen Mappe den Teil des
> Bezuges entfernen, der auf die alte Mappe verweist.
Mmm, joa, aber das spart ja keine Arbeitsschritte....
> Oder aber - und das ist meine Empfehlung- Du änderst nach dem Kopieren der
> Formeln in der neuen Mappe über Bearbeiten/Verknüpfen die Quelldatei neu
> fest und wählst dort als neue Datei die aktuelle in der die Formeln nun
> neu
> zu finden sind.
Auch das ist bei knapp 100 Dateien sehr mühselig....
>> Ich kann aber fast nicht glauben, dass das nicht etwas eleganter geht.
>> Soetwas brauchen doch gerade Firmen mit Auswertungstabellen (wie wir im
>> Chemielabor halt tausende Exceltabellen.....) ständig.....
>
> Ein bestehendes Berechnungs-System zu erweitern und umzubauen ist immer
> eine grössere Herausforderung. Speziell dann wenn die Mappe mehrfach
> kopiert oder mit einer Vorlage neu erstellt worden ist.
> Da musst Du dann den Vorgang prinzipiell in jeder Mappe wiederholen.
Jap, leider, genauso ist es. Wir haben dort Projekte, die dann meist 200 bis
300 Proben umfassen. Da für jede Probe eine Excel-Datei angefertigt
wird.......
> Die Entwickler standen vor der Gretchenfrage ob ein Bezug zu einem anderen
> Tabellenblatt derselben Mappe in die neue Mappe umgeleitet werden soll
> oder
> ob er auf der Original-Zelle belassen werden soll.
> Für das ein haben sie sich entschieden - wäre der Entscheid umgekehrt
> ausgefallen hätte jemand anders einen anderen Wunsch.
Och, da könnte man aber nun wirklich ganz leicht einen kleinen Menüpunkt bei
"Inhalte einfügen" ergänzen---> "Formeln 1:1 kopieren" (benennung darf nach
belieben geändert werden :-)
Programmiertechnisch sollte das absolut keinen aufwand bedeuten.
Naja, ok, aber danke auf jeden Fall erstmal bis hier. Ich dachte halt, ich
hätte vielleicht etwas wesentliches übersehen. Ich denke ich werde dann am
Montag versuchen, mir ein paar Makros zu basteln, die das ganze hoffentlich
etwas automatisieren, wobei ich leider mit dem Makro-Rekorder gut zurecht
komme, beim selbst programmieren, dann aber doch der VBS-Sprache leider
nicht mächtig bin.......
Gruß Thomas
Thomas Ramel
Thomas B. schrieb am 05.04.2008
>> Ja, und das ist in aller Regel auch richtig so.
>> Bezüge zu anderen Tabellenblättern bleiben auf die Originaldaten erhalten
>> und werden nicht auf die neue Mappe umgelenkt.
>
> Macht auch oft Sinn, das stimmt schon, aber nicht immer.
Ja, wie das aktuelle Beispiel zeigt.
>> Das kann zwar durch die Verwendung der Funktion INDIREKT() anders
>> gehandhabt werden, macht die Formeln aber nicht gerade einfacher.
>>
>> Vielleicht nennst Du mal eine der Formeln, dann können wir uns Gedanken
>> über einen möglichen Umbau machen.
>
> Die Formel lautet z.B.
> =WENN(UND(Tabelle1!C5>=Tabelle1!A6;Tabelle1!C5<=Tabelle1!E5);"J";"")
>
> (Da die Daten in Tabelle 1 aber anders sortiert sind, kann nicht einfach
> eine Formel kopiert werden und per runterziehen die anderen vervollständigt
> werden....)
OK, mit folgender Anpassung ist die Formel dann unabhängig von der Datei in
der sie verwendet wird und Du kannst sie 1:1 kopieren.
=WENN(UND(INDIREKT("Tabelle1!C5")>=INDIREKT("Tabelle1!A6");INDIREKT("Tabelle1!C5")<=INDIREKT("Tabelle1!E5"));"J";"")
Ein kopieren nach unten/rechts wird allerdings auch die Bezüge nicht mehr
verändern, da diese nun 'hart' als Text in der Formel stehen.
> (lustig, dabei ist mir grad mal aufgefallen, dass ich bei meiner Version zu
> Hause, nicht "AND" benutzen kann, sondern "UND" schreiben muss, auf der
> Arbeit konnte ich "WENN" und "AND" benutzen, wobei mir dort mein eigener
> Sprachsalat gar nicht mal aufgefallen ist ;-) aber das nur am Rande....)
Vermutlich hast Du zu Hause eine 'reine' Deutsche Installation und in der
Firma verwendet ihr die MUI (Mulitlaguage User Interface) die im
wesentlichen eine Englische Installation mit 'aufgepfopfter' deutscher
Oberfläche ist.
>> Ja, das ist ein gut gangbarer Weg, der auch nicht wirklich viel mehr Zeit
>> in Anspruch nimmt.
>
> Naja, bei 10 Excel-Dateien mag das gehen, hier geht es leider um ca. 100
> Stück. Wobei ich denke, dass man für diese Umwandlung halt auch ein kleines
> Makro schreiben kann....
>
>> Du könntest auch mit Bearbeiten/Ersetzen in der neuen Mappe den Teil des
>> Bezuges entfernen, der auf die alte Mappe verweist.
>
> Mmm, joa, aber das spart ja keine Arbeitsschritte....
>
>> Oder aber - und das ist meine Empfehlung- Du änderst nach dem Kopieren der
>> Formeln in der neuen Mappe über Bearbeiten/Verknüpfen die Quelldatei neu
>> fest und wählst dort als neue Datei die aktuelle in der die Formeln nun
>> neu
>> zu finden sind.
>
> Auch das ist bei knapp 100 Dateien sehr mühselig....
Tja, das sind dann halt so die Möglichkeiten die mir (ausser der Verwendung
von INDIREKT) so eingefallen sind.
Vermutlich ist also ein VBA-Makro durchaus sinnvoll.
>>> Ich kann aber fast nicht glauben, dass das nicht etwas eleganter geht.
>>> Soetwas brauchen doch gerade Firmen mit Auswertungstabellen (wie wir im
>>> Chemielabor halt tausende Exceltabellen.....) ständig.....
>>
>> Ein bestehendes Berechnungs-System zu erweitern und umzubauen ist immer
>> eine grössere Herausforderung. Speziell dann wenn die Mappe mehrfach
>> kopiert oder mit einer Vorlage neu erstellt worden ist.
>> Da musst Du dann den Vorgang prinzipiell in jeder Mappe wiederholen.
>
> Jap, leider, genauso ist es. Wir haben dort Projekte, die dann meist 200 bis
> 300 Proben umfassen. Da für jede Probe eine Excel-Datei angefertigt
> wird.......
...sollte am Besten im Voraus klar sein welche Berechnungen denn
vorgenommen werden sollen und die Vorlage darauf abgestimmt werden...
>> Die Entwickler standen vor der Gretchenfrage ob ein Bezug zu einem anderen
>> Tabellenblatt derselben Mappe in die neue Mappe umgeleitet werden soll
>> oder
>> ob er auf der Original-Zelle belassen werden soll.
>> Für das ein haben sie sich entschieden - wäre der Entscheid umgekehrt
>> ausgefallen hätte jemand anders einen anderen Wunsch.
>
> Och, da könnte man aber nun wirklich ganz leicht einen kleinen Menüpunkt bei
> "Inhalte einfügen" ergänzen---> "Formeln 1:1 kopieren" (benennung darf nach
> belieben geändert werden :-)
> Programmiertechnisch sollte das absolut keinen aufwand bedeuten.
Offenbar gab es auch bei xl2007 andere Prioritäten, die diesem feature den
Rang abgelaufen haben. ;-(
> Naja, ok, aber danke auf jeden Fall erstmal bis hier. Ich dachte halt, ich
> hätte vielleicht etwas wesentliches übersehen. Ich denke ich werde dann am
> Montag versuchen, mir ein paar Makros zu basteln, die das ganze hoffentlich
> etwas automatisieren, wobei ich leider mit dem Makro-Rekorder gut zurecht
> komme, beim selbst programmieren, dann aber doch der VBS-Sprache leider
> nicht mächtig bin.......
Vielleicht versuchst Du es erst noch mit der INDIREKT()-Variante.
Ansonsten kennst Du ja nun die Anlaufstelle und fragst hier einfach wieder
nach.
Thomas B.
> in
> der sie verwendet wird und Du kannst sie 1:1 kopieren.
>
> =WENN(UND(INDIREKT("Tabelle1!C5")>=INDIREKT("Tabelle1!A6");INDIREKT("Tabelle1!C5")<=INDIREKT("Tabelle1!E5"));"J";"")
Dankeschön! :-)
> Ein kopieren nach unten/rechts wird allerdings auch die Bezüge nicht mehr
> verändern, da diese nun 'hart' als Text in der Formel stehen.
>
>> (lustig, dabei ist mir grad mal aufgefallen, dass ich bei meiner Version
>> zu
>> Hause, nicht "AND" benutzen kann, sondern "UND" schreiben muss, auf der
>> Arbeit konnte ich "WENN" und "AND" benutzen, wobei mir dort mein eigener
>> Sprachsalat gar nicht mal aufgefallen ist ;-) aber das nur am
>> Rande....)
>
> Vermutlich hast Du zu Hause eine 'reine' Deutsche Installation und in der
> Firma verwendet ihr die MUI (Mulitlaguage User Interface) die im
> wesentlichen eine Englische Installation mit 'aufgepfopfter' deutscher
> Oberfläche ist.
Das kann sein. Ich bleib jetzt einfach bei Deutsch, das passt dann ja
auch....
> Tja, das sind dann halt so die Möglichkeiten die mir (ausser der
> Verwendung
> von INDIREKT) so eingefallen sind.
> Vermutlich ist also ein VBA-Makro durchaus sinnvoll.
Hab heute eine Menge geschafft. Das meiste davon mit der Hilfe von
angepassten Makros.
>> Jap, leider, genauso ist es. Wir haben dort Projekte, die dann meist 200
>> bis
>> 300 Proben umfassen. Da für jede Probe eine Excel-Datei angefertigt
>> wird.......
>
> ...sollte am Besten im Voraus klar sein welche Berechnungen denn
> vorgenommen werden sollen und die Vorlage darauf abgestimmt werden...
Wie das eben so ist mit Kunden. Man spricht im Vorfeld ein bestimmtes
Prozedere ab, doch dann kommen im Nachhinein noch Extrawünsche.....
>>> Die Entwickler standen vor der Gretchenfrage ob ein Bezug zu einem
>>> anderen
>>> Tabellenblatt derselben Mappe in die neue Mappe umgeleitet werden soll
>>> oder
>>> ob er auf der Original-Zelle belassen werden soll.
>>> Für das ein haben sie sich entschieden - wäre der Entscheid umgekehrt
>>> ausgefallen hätte jemand anders einen anderen Wunsch.
>>
>> Och, da könnte man aber nun wirklich ganz leicht einen kleinen Menüpunkt
>> bei
>> "Inhalte einfügen" ergänzen---> "Formeln 1:1 kopieren" (benennung darf
>> nach
>> belieben geändert werden :-)
>> Programmiertechnisch sollte das absolut keinen aufwand bedeuten.
>
> Offenbar gab es auch bei xl2007 andere Prioritäten, die diesem feature den
> Rang abgelaufen haben. ;-(
Mmm, ob so eine Kleinigkeit mit großer Wirkung da wirklich nicht
berücksichtig wurde, aus Ressourcenmangel? Mmm, naja dann sollte das mal
fix nachgereiht werden ;-)
Naja, ansonsten ist Office 2007 bisher sehr genial. Auch wenn ich mich ganz
schön umgewöhnen muss. Ich hoffe nur, dass sich docx und xmlx noch etwas
schneller verbreiten. Die meisten Firmen kennen das Kompatibilitäspaket
nicht und scheuen sich deshalb hier komplett umzusteigen.... Das sollte per
Updatefunktion bei den alten Versionen einfah installiert werden (meine
Meinung). Aber das ist jetzt OT.....
>> Naja, ok, aber danke auf jeden Fall erstmal bis hier. Ich dachte halt,
>> ich
>> hätte vielleicht etwas wesentliches übersehen. Ich denke ich werde dann
>> am
>> Montag versuchen, mir ein paar Makros zu basteln, die das ganze
>> hoffentlich
>> etwas automatisieren, wobei ich leider mit dem Makro-Rekorder gut zurecht
>> komme, beim selbst programmieren, dann aber doch der VBS-Sprache leider
>> nicht mächtig bin.......
>
> Vielleicht versuchst Du es erst noch mit der INDIREKT()-Variante.
>
> Ansonsten kennst Du ja nun die Anlaufstelle und fragst hier einfach wieder
> nach.
Jup danke, ich schätze ich hab demnächst noch ein paar Fragen.
Also danke erstal soweit und einen schönen Abend.
Gruß Thomas