Excel für ein gesmates Tabellenblatt den Bezug ändern
Hallo,
ich habe folgendes Problem:
Habe in Excel eine Tabelle (Teilwerte) mit WENN Funktionen deren Werte sich auf ein anders Tabellenblatt (Januar) beziehen. Summen von Ergebnissen dieser WENN Formeln werden in einem anderen Tabellenblatt (Endergebnisse) in Spalte C angezeigt. Nun möchte ich die Teilwertetabelle, die sich auf Januar bezieht, genau so noch einmal haben, nur, dass sie sich auf ein dann neu enstandenes Tabellenblatt Februar bezieht. Die Summen sollten dann in der Endergebnisstabelle in Spalte D erscheinen (dass lässt sich aber noch per Hand machen)
Bsp. für eine Formel(in der Teilwertetabelle): =WENN(UND(ODER(Januar!A1=56;Januar!A1=234;Januar!A1=99);B1=678422);Januar!C1;0) Davon habe ich hunderte. (mit teilweise 30 Oderwerten) ändern soll sich eben nur das Januar in Februar...
Kenne mich mit excel nicht so besonders aus, wäre also für einfach gehaltene Erklärungen dankbar.
Vielen, vielen Dank schon mal im vorraus...
2 Antworten
geht über suchen und ersetzen (alle Zellen auswählen, Strg+h):
suchen nach: Januar!
ersetzen durch: Februar!
ersetzen in: Formeln
alle ersetzen, kannst sie aber auch einzeln kontrollieren (wahrscheinlich überflüssig)
Das ganze Blatt kannst Du auswählen, indem Du es am Blattkopf (Schnittstelle von Buchstaben- und Zahlenleiste) markierst (glaube, mit Strg+a gehts auch)
freut mich, dass es sich derart gelohnt hat!
(selbst wenn man 3 Nullen abzieht)
Nur: prüfe auch noch Texteinträge, ob nicht zuviel ersetzt wurde: ein Satz wie:
"die größte Kälte herrscht im Januar!" wird -obwohl es heißt "in Formeln" auch geändert und hat danach die Falschaussage:
"die größte Kälte herrscht im Februar!"
Die Option müsste korrekt heissen: AUCH in Formeln!
(man könnte das umgehen, indem man zuerst etwas nur in Werten durch xyzuvw ersaetzt und dann in Formeln und am schluss in Texten wieder rückwärts ersetzt...
danke für den Hinweis aber sowas habe ich zum glück in der gesamten Tabelle nicht
Hallo,
wennn du das öfter machen möchtest, kannst du mit der Indirekt-Funktion eine Zelle Festlegen, wo du die Tabellennamen hinschreibst und er sucht dann in der entsprechenden Tabelle.
=WENN(UND(ODER(INDIREKT(E3&"!A1")=56;INDIREKT(E3&"!A1")=234;INDIREKT(E3&"!A1")=99);B1=678422);INDIREKT(E3&"!C1");0)
So kannst du in E3 deinen Tabellennamen eingeben und Excel nimmt automatisch die jeweils andere Tabelle. Wenn es allerdings die Tabelle nicht gibt, zeigt er überall fehler an Und bei knapp 500.000 Einträgen da kann das schon ma dauern...
Grüße
ein guter Tipp für die Neuanlage von Tabellen, nachträglich und bei komplexen Formeln: "hunderte. (mit teilweise 30 Oderwerten)" allerdings etwas aufwändig.
Da sollte man allerdings nach prinzipiell anderen Formeln suchen, da sollte sich mal ein Fachmann reinvertiefen, und auch der braucht seine Zeit für eine gute Lösung!
@Fragqk: ich kenne ja nicht Deine Aufgabenstellung, aber ich hab hier mal (in Kombination mit Fleischtesters Vorschlag) eine alternative Formel geschrieben. Basis: in M13 stehe Januar:
=WENN(WENNFEHLER(VERGLEICH(INDIREKT(M13&"!A1");{56.234.99};0)>0;0)*(B1=678422);INDIREKT(M13&"!C1");0)
Bei 3 Oders ist diese Formel noch ein wenig länger, ab 5 immer erheblicher kürzer. Sie geht ab xl2007,
für frühere Versionen:
=WENN(WENN(ISTFEHLER(VERGLEICH(INDIREKT(M13&"!A1");{56.234.99};0));0;VERGLEICH(INDIREKT(M13&"!A1");{56.234.99};0)>0)*(B1=678422);INDIREKT(M13&"!C1");0)
sieht nach Ungetüm aus, bis man's begriffen hat:
Vergleich vergleicht den (indirekt-)Wert in M13=Januar&!A1 mit den (hier nur 3) Werten der Matrix (die auch als Listenbereich eingegeben werden kann) Ist Januar!A1 dabei, liefert Vergleich die Position in der Matrix, also einen Wert >=1 oderkürzer >0. Doese 1 wird mit >0 getestzet und liefert WAHR=1, womit dann das Und-Argument (wenn WAHR, ebenfalls =1) multipliziert wird. Das Produkt dann =1, dadurch wird die Wenn-Bedingung wahr und deshalb C1 geholt (ginge auch ohne das äussere Wenn durch direkte Multiplikation mit indirekt(Januar!C1), aber so wirds vllt verständlicher.
Wenn Du's nicht gleich verstehst: es ist nicht leicht, und man muss sich solchen Formeln mit der Zeit langsam nähern, ging mir auch so.
Ich wollte Dir lediglich zeigen, dass es oft ganz andere Lösungen gibt, jenseits von langatmigen Unds und Oders: Bei 30 Oders wird obige Formel um vllt 2-3 Zeilen länger, die Deinige wahrscheinlich um 6-10. Und wenn man die Matrix als Zellbereich angibt, spielt die Länge überhaupt keine Rolle mehr:
Z1:Z99 ist nicht länger als Z1:Z10 !
"wenn man die Matrix als Zellbereich angibt", das hieße dann ganz einfach so:
=WENN(WENNFEHLER(VERGLEICH(INDIREKT(M13&"!A1");Z1:Z10;0)>0;0)*(B1=678422);INDIREKT(M13&"!C1");0)
in Z1:Z10 stünden dann
Z1: 56
Z2: 234
Z3: 99
Z4..Z10 (wenn überhaupt benötigt) würde ich vorläufig mit 999.9 füllen, damit nicht durch versehentlich sich ergebende Einsen oder Nullen die Existenz von Werten vorgetäuscht werden, die es nicht gibt.
Anm: der einzige Nachteil von indirekt ist (neben der zusätzlichen Länge), dass sich Zelladressen nicht selbsttätig anpassen, wenn zB nachträglich eine Zeile eingefügt wird. Auch beim Kopieren ergibt sich kein angepasster Bezug.
Hallo,
der Vollständigkeit halber,
Anm: der einzige Nachteil von indirekt ist (neben der zusätzlichen Länge), dass sich Zelladressen nicht selbsttätig anpassen, wenn zB nachträglich eine Zeile eingefügt wird. Auch beim Kopieren ergibt sich kein angepasster Bezug.
Man kann das allerdings mit der Formel "Bereich.verschieben" ausgleichen. Das macht die Formel aber furchtbar lang und auch noch unübersichtlicher.
Grüße
Guter Hinweis, an Bereich.verschieben denk ich nie.
ich hätts in diesem Fall mit indirekt(Zeichen(Spalte(Zellbezug)+64)&":"&Zeile(Zellbezug)) versucht, aber das 2-3x hätte das Ganze auch vollständig unüberschaubar gemacht. Man baut ja leichter eine Formel auf, als dass man die Aufgebaute versteht, sogar, wenns eine alte eigene ist...
Hat super funktioniert hätte sonst 491000 mal selbst ersetzen dürfen