Formel-Anpassung

Question

Hallo,

ich habe in einem Excel-Sheet eine Formel, die u.a. folgende Zellzuweisung beinhaltet:

....ZEILE($1:$400)...
Gesamte Formel:
=VERKETTEN(WENN(ZEILEN(59:60)>ZÄHLENWENN(Veränderungen!$A:$A;$B59);"";INDEX(Veränderungen!$L:$L;KKLEINSTE(WENN(Veränderungen!$A$1:$A$400=$B59;ZEILE($1:$400));ZEILE(A$2))));" / ";WENN(ZEILEN(59:60)>ZÄHLENWENN(Veränderungen!$A:$A;$B59);"";INDEX(Veränderungen!$H:$H;KKLEINSTE(WENN(Veränderungen!$A$1:$A$400=$B59;ZEILE($1:$400));ZEILE(A$2))));" / ";WENN(ZEILEN(59:60)>ZÄHLENWENN(Veränderungen!$A:$A;$B59);"";INDEX(Veränderungen!$I:$I;KKLEINSTE(WENN(Veränderungen!$A$1:$A$400=$B59;ZEILE($1:$400));ZEILE(A$2)))))

Obwohl ich mit "$" verhindern möchte, dass sich die Formel verändert passiert dies wenn ich in der entsprechenden Tabelle Zeilen einfüge: aus zeile($1:$400) wird dann zeile($1:$402), wodurch die Formel dann einen Fehler erzeugt. Hat jemand einen Tipp wie ich das umgehen kann?
Danke und Gruß!
Andreas

Answer 1

Hallo Rainer,

ich sehe jetzt eigentlich klar und habe meine Datei dank deiner Hilfe jetzt aufgeräumt und vor allem viel schneller gemacht. Nur über ein kleines Problem komme ich nicht weg, hab schon alles probiert...
Die Formel im Feld F4 bringt in meiner "Masterdatei" als Ergebnis immer "1.1.1900" obwohl ich die Bereichsdefinition und die Formel 1:1 übernommen habe, auch das Feldformat ist das gleiche wie in der Testdatei..... Hast du da noch einen Tipp was ich machen könnte?
Danke und Gruß
Andreas

Answer 2

Hallo Andreas,

das Zauberwort lautet:

Strg+Umschalt+Enter (Matrixformel)

Gruß
Rainer

Answer 3

...das war's! Danke und Gruß
Andreas

Answer 4

Hallo Rainer,

ich bin dabei auch meine anderen Anwendungen nach diesem Prinzip zu verbessern und vor allem schneller zu machen. Dabei bin ich auf ein Problem gestoßen, wo ich momentan nicht weiterkomme. Ich will den Bereichen Namen geben und diese möglichst dynamisch gestalten. Das betrifft insbesondere eine Anwendung bei der ich auf verschiedene Tabellen mit unterschiedlichen Zeilenanzahlen zurückgreifen muss.
Ich habe eine testfile mal zum Hochladen beigefügt.

http://www.file-upload.net/download-9374197/Test-Rechnung.xlsx.html

Im Sheet Rechnung wähle ich den jeweiligen Monat und werte die einzelnen Trainingsstunden je Teilnehmer aus. Da die Anzahl der Datensätze jeden Monat verschieden ist, gehe ich von insgesamt 800 aus, was viel zu hoch aber sicher ist. Das wollte ich jetzt mit einer dynamischen Anpassung nach obigem Beispiel besser machen. Hast du dafür noch einen Tipp?
Danke und Gruß
Andreas

Answer 5

Hallo Rainer,

ich versuche in einer anderen Anwendung deinen Tipp der dynamischen Bereichsdefinition anzuwenden um die Tabelle zu verbessern und schneller zu machen. Dabei stoße ich auf ein Problem: Ich habe in "B28" begonnen die bestehende Formel durch diese dynamische Bereichsdefinition zu verbessern. Sie liefert mir auch das richtige Ergebnis, nur wenn ich diese Formel wie vorher nach unten kopieren will, funktioniert es nicht mehr...haben keine Erklärung. Zum besseren Verständnis, hier meine Testdatei:


http://www.file-upload.net/download-9375447/Test-Rechnung.xlsx.html

Hat du wieder einen guten Tipp?
Gruß Andreas

Answer 6

Hallo Andreas,

nur zufällig habe ich noch mal in den Beitrag geschaut, da Du bereits seit längerer Zeit Dein Problem als "Gelöst" dokumentiert hast.

Stelle bitte, auch in Deinem Interesse, neue Fragen immer in einem neuen Beitrag.

Anbei die angepasste Mappe

Gruß
Rainer

Answer 7

Hallo Rainer,
danke für deine Antwort und den Hinweis. Ich habe die letzten Tage ewig probiert um mit deiner "Methode" meine Datei etwas aufzufrischen und bin eigentlich auch soweit gekommen, dass ich glaubte am Ziel zu sein - bis ich durch Zufall einen Datensatz gefunden habe, bei dem die Formeln nicht mehr das richtige Ergebnis bringen.

Jetzt habe ich in deinem aktuellen download festgestellt, dass der gleiche Fehler auch dort auftritt, ohne dass ich dafür eine Erklärung habe.
Es handelt sich um kopperschmidt, j im Juli. Hier stimmen die einzelnen Tage noch aber am 4. und 11.7. stimmen die Beträge und auch die Gruppenstärke und Anwesenheit nicht mehr ...?????
Hast du hierfür eine Erklärung?
Danke und Gruß
Andreas

Answer 8

Hallo Andreas,

sorry, da war bei allen Namensbereichen außer "Tag" und "Teilnehmer" der falsche Startpunkt eingetragen.

Beispiel:

Definitionsformel "Beginn" (falsch)

=BEREICH.VERSCHIEBEN(INDIREKT("'"&Rechnung!$E$2&"'!A1");1;2;VERGLEICH("";INDIREKT("'"&Rechnung!$E$2&"'!A:A");-1)-1;)

Definitionsformel "Beginn" (richtig)

=BEREICH.VERSCHIEBEN(INDIREKT("'"&Rechnung!$E$2&"'!A1");;2;VERGLEICH("";INDIREKT("'"&Rechnung!$E$2&"'!A:A");-1);)

Beim Vergleichen bitte genau hinschauen!!!

Die Datei habe ich geändert, kannst sie aus dem letzten Beitrag noch mal downloaden.

Gruß
Rainer

Answer 9

Hallo,

ja jetzt passt das, vielen Dank. Eigentlich wollte ich ja selbst dorthin kommen.....aber irgendetwas hat bei mir noch gehakt. Vlt. kann ich dir das ja in einem neuen Beitrag noch mal schicken um den Fehler zu finden und was dazuzulernen.......
Viele Grüße
Andreas

Answer 10

Hallo Andreas,

Dein Problem lässt sich natürlich auch noch erheblich performancefreudiger lösen, indem statt der bisherigen Matrixformeln, nur der einfache SVERWEIS() verwendet wird.
Außerdem sind weniger Namensdefinitionen nötig.

Du musst wissen die Funktionen BEREICH.VERSCHIEBEN() und INDIREKT() gehören u.a. zu den volatilen Tabellenfunktionen die die Performance des Systems stark ausbremsen das sie bei jeder Wertänderung einer Zelle neu berechnet werden.

Für diese Art der Auswertung ist allerdings für jedes Monatsblatt eine Hilfsspalte erforderlich.

Habe das im Anhang mal dokumentiert.

Gruß
Rainer