Live-Forum - Die aktuellen Beiträge
Datum
Titel
14.10.2025 15:55:14
wenn zwei Werte aus Liste übereinstimmen, dann wahr
14.10.2025 14:57:29
Wenn Datum in Ferienzeit dann Name angeben
14.10.2025 09:39:44
VBA - Excel speichern und schließen (Laufzeitfehler 91)
13.10.2025 19:31:31
VBA Formel mit Bezug auf Überschrift anstatt Spalte
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1440to1444
1440to1444
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Absoluter Zellbezug ändert sich automatisch

Forumthread: Absoluter Zellbezug ändert sich automatisch

Absoluter Zellbezug ändert sich automatisch
20.08.2015 11:37:13
Major
Hallo zusammen,
ich habe eine Summewenn-Formel die in einem Bereich sucht, den ich mit absoluten Zellbezügen fixiert habe. Dieser Bereich wird per Makro mit Daten aus Access gefüttert. Diese Daten ändern sich ständig, auch in ihrer Anzahl, mal sind es 5000 Zeilen, mal 5634, mal 6038 usw.
Mein absoluter Bezug legt den Bereich auf 8000 fest.
Nun wenn ich das Makro ausführe, ändert sich meine Formel trotz absoluten Bezuges.
Ich könnte mir ein Makro vorstellen dass einfach den Wert in der Formel im ganzen Arbeitsblatt durch 8000 ersetzt, aber dieser Wert ist nicht fix, darum weiss ich nicht wie es geht.
Welche Lösungsvorschläge gibt es?
Danke für eure Hilfe!

Anzeige

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Absoluter Zellbezug ändert sich automatisch
20.08.2015 11:48:11
Rudi
Hallo,
das liegt wahrscheinlich an Zeile 23 deines Makros.
Gruß
Rudi

AW: Absoluter Zellbezug ändert sich automatisch
20.08.2015 12:32:15
Herbert
Hallo Rudi,
also ich glaube, dass es an Zeile 19 liegt! ;o)=)
Servus

AW: ihr täuscht Euch möglicherweise beide, ...
20.08.2015 12:49:56
...
Hallo Herbert, hallo Rudi,
... denn es wird an der eingebundenen Formel liegen ;-)
Gruß Werner
.. , - ...

Anzeige
Absoluter Zellbezug ändert sich automatisch
20.08.2015 13:29:56
Major
Ooookay habs verstanden.
Damit das nerdige Aufschaukeln hier ein Ende hat, hier das Makro.

Range("A2:E6000").Select
Selection.ClearContents
Range("C5").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F6000"), Type:=xlFillDefault
Range("F2:F6000").Select
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H6000"), Type:=xlFillDefault
Range("H2:H6000").Select
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G6000"), Type:=xlFillDefault
Range("G2:G6000").Select
im Bsp. noch mit 6000 statt 8000.
Danke euch!

Anzeige
AW: Absoluter Zellbezug ändert sich automatisch
20.08.2015 15:48:02
Herbert
Hallo,
lade doch mal eine Beispieldatei hoch und beschreibe es darin noch einmal detailliert.
Servus

AW: Absoluter Zellbezug ändert sich automatisch
20.08.2015 17:26:29
Rudi
Hallo, 
    Range("A2:E10000").ClearContents
Range("C5").ListObject.QueryTable.Refresh BackgroundQuery:=False
LastRow=Range("A1").currentregion.rows.count
Range("F2:F"&LastRow).Formula=Range"F2").Formula
Range("G2:G"&LastRow).Formula=Range"G2").Formula
Range("H2:H"&LastRow).Formula=Range"H2").Formula
Gruß
Rudi

Anzeige
AW: Absoluter Zellbezug ändert sich automatisch
20.08.2015 18:02:48
Herbert
Hi Rudi,
hast Du es gemerkt? Ein Schnellschuss "aus'm Pott" ;o)=):
   Range("A2:E10000").ClearContents
Range("C5").ListObject.QueryTable.Refresh BackgroundQuery:=False
LastRow = Range("A1").CurrentRegion.Rows.Count
Range("F2:F" & LastRow).Formula = Range("F2").Formula
Range("G2:G" & LastRow).Formula = Range("G2").Formula
Range("H2:H" & LastRow).Formula = Range("H2").Formula
Und LastRow müsste er wahrscheinlich noch Dim-ensionieren.
Servus

Anzeige
AW: Absoluter Zellbezug ändert sich automatisch
21.08.2015 09:43:24
Major
Hallo zusammen,
das sieht schon gut aus, danke euch hierfür.
Mein Formel in "H" ist ein Arrayformel, die leider ohne Array eingefügt wird.
FormulaArray löst auch nicht das Problem.
Habt ihr da eine Lösung?
Danke euch!
   Range("A2:E10000").ClearContents
Range("C5").ListObject.QueryTable.Refresh BackgroundQuery:=False
LastRow = Range("A1").CurrentRegion.Rows.Count
Range("F2:F" & LastRow).Formula = Range("F2").Formula
Range("H2:H" & LastRow).Formula = Range("H2").FormulaArray
Range("G2:G" & LastRow).Formula = Range("G2").Formula

Anzeige
AW: Absoluter Zellbezug ändert sich automatisch
21.08.2015 11:07:54
Rudi
Hallo,
dann evtl. doch per FillDown.
Range("H2").AutoFill Destination:=Range("H2:H" &LastRow), Type:=xlFillDefault
Gruß
Rudi

AW: Absoluter Zellbezug ändert sich automatisch
21.08.2015 17:23:32
Major
Hallo zusammen,
sorry Leute, habe mich zu unklar ausgedrückt...ich versuche es noch einmal.
ich habe beispielsweise diese Formel...
=WENN(F2=MIN(WENN(($A$1:$A$8000=A2);$F$1:$F$8000));C2;"")
Die Formel sucht in der Fläche 1:8000 nach den gewünschten Werten.
Diese Matrixfläche wird durch ein Makro bei jedem Start neu erzeugt(aktualisiert) mit Daten aus Access.
Die Menge der Daten die aus Access kommen variiert. Obwohl die Fläche $A$1:$A$8000 mit absoluten Zellbezügen fixiert ist, kommt es vor, dass sich die Zahl 8000 ändert.
Je nachdem wieviel Daten aus Access übernommen werden.
Dadurch dass sich die 8000 ändert habe ich falsche Zahlen. Habe daneben nämlich weitere Spalten mit Formeln, die bis 8000 suchen, welche ohne Daten aus Access auskommen.
Die 8000 müsste einach unveränderlich fix sein, dann dürfte es keine Probleme geben, aber wie?
Ich hoffe jetzt war es etwas verständlicher und ich danke euch für eure Kommentarte!
Gruss Major

Anzeige
AW: FormelBereichsbezüge unveränderl. fixieren ...
22.08.2015 09:37:14
...
Hallo Major,
... kannst Du mit z.B. mit INDEX() vornehmen.
Denn ohne dies werden bei nachträglichen Zeileneinfügungen oder auch Löschungen die Formelbereichsbezüge durch Excel automatisch erweitert oder reduziert, egal ob diese relativ oder absolut definiert wurden.
Fixiert auf Deine Vorgabe können die Bereiche in Deiner Formel z.B. wie folgt werden:
=WENN(F2=MIN(WENN((INDEX($A:$A;1):INDEX($A:$A;8000)=A2);INDEX($F:$F;1):INDEX($F:$F;8000)));C2;"")
Gruß Werner
.. , - ...

Anzeige
AW: FormelBereichsbezüge unveränderl. fixieren ...
24.08.2015 15:06:24
Major
Hallo neopa C und Co.
mit der Formel oben hat das schon einmal geklappt, vielen Dank hierfür.
Habe noch mehrere andere mit SUMMEWWENNS.
Habe hier versucht den INDEX einzubauen, aber vergebens. Kann mir jemand helfen und schreiben, wie das hier aussehen müsste?
=(WENNFEHLER(SUMMEWENNS(Mappe2!$C$2:$C$8000;
Mappe2!$A$2:$A$8000;Mappe1!$C$9;Mappe2!$F$2:$F$8000;Mappe1!CE6);"0"))
+WENN(CE6=$A$3;SVERWEIS($C$9;Mappe2!$A$2:$D$8000;4;1);0)
Danke euch!

Anzeige
AW: das Schema ist immer das Gleiche ...
24.08.2015 16:17:16
...
Hallo Major,
... also z.B. anstelle $F$2:$F$8000) so: INDEX(F:F;2):INDEX(F:F;8000) zuvor eben noch den Tabellenblattnamen.
Gruß Werner
.. , - ...

AW: das Schema ist immer das Gleiche ...
28.08.2015 17:24:22
Major
Hallo zusammen,
nein sorry funktioniert bei mir nicht.
So sieht das bei mir aus. Bekomme die Standardfehlermeldung "Die eingegebene Formel enthält einen Fehler".
=(WENNFEHLER(SUMMEWENNS(Mappe2!INDEX($C:$C;2):INDEX($C:$C;8000);
Mappe2!INDEX($A:$A;2):INDEX($A:$A;8000);Mappe1!$C$11;
Mappe2!$F$2:$F$8000;Mappe1!CJ6);"0"))
+WENN(CJ6=$A$3;SVERWEIS($C$11;Mappe2!INDEX($A:$D;2):INDEX($A:$D;8000);4;1);0)
Kann jemand einen Fehler entdecken?
$F$2:$F$8000

.... ist absichtlich ohne INDEX.
Danke euch für euer Bemühen!
Gruss Major
Anzeige
;
Anzeige
Anzeige

Infobox / Tutorial

Absoluter Zellbezug in Excel fixieren: Lösungen und Tipps

Schritt-für-Schritt-Anleitung

  1. Formel mit INDEX() anpassen: Wenn Du mit variablen Datenmengen arbeitest, solltest Du die Bereiche in Deinen Formeln dynamisch gestalten. Nutze die INDEX()-Funktion, um die automatische Anpassung der Zellreferenzen zu verhindern. Der Syntax sieht folgendermaßen aus:

    =WENN(F2=MIN(WENN((INDEX($A:$A;1):INDEX($A:$A;8000)=A2);INDEX($F:$F;1):INDEX($F:$F;8000)));C2;"")

  2. Makros zur Aktualisierung verwenden: Wenn Du die Daten per Makro aktualisierst, stelle sicher, dass diese auch die Formeln entsprechend anpassen. Hier ist ein Beispiel, wie Du das im VBA-Code umsetzen kannst:

    LastRow = Range("A1").CurrentRegion.Rows.Count
Range("F2:F" & LastRow).Formula = Range("F2").Formula

  3. Verwenden von FormulaArray: Wenn Du Array-Formeln hast, stelle sicher, dass Du FormulaArray verwendest, um die gesamte Spalte zu füllen:

    Range("H2:H" & LastRow).FormulaArray = Range("H2").FormulaArray

Häufige Fehler und Lösungen

  • Formel enthält einen Fehler: Überprüfe, ob Du beim Einsatz von INDEX() die richtigen Zellreferenzen verwendest. Oft kann ein einfacher Syntaxfehler solche Meldungen hervorrufen.

  • Zellbezüge ändern sich: Wenn sich die Zahl 8000 in Deinen Formeln ändert, überprüfe die Logik der Formeln. Verwende stattdessen die INDEX()-Methode, um die Bereiche dynamisch zu definieren.

Alternative Methoden

  • Datenüberprüfung mit dynamischen Bereichen: Anstelle harter Zellreferenzen kannst Du in Excel auch benannte Bereiche verwenden, die sich dynamisch anpassen, wenn sich die Daten ändern.

  • Pivot-Tabellen nutzen: Wenn Du regelmäßig Berichte aus variablen Datenmengen erstellen musst, könnten Pivot-Tabellen eine bessere Lösung sein, um Daten zu aggregieren und zu analysieren.

Praktische Beispiele

  1. SUMMEWENNS mit INDEX():

    =SUMMEWENNS(INDEX(Mappe2!$C:$C;2):INDEX(Mappe2!$C:$C;8000); Mappe2!INDEX($A:$A;2):INDEX($A:$A;8000); Mappe1!$C$9)

  2. Verwendung von WENNFEHLER(): 

    =WENNFEHLER(SUMMEWENNS(Mappe2!INDEX($C:$C;2):INDEX($C:$C;8000); Mappe2!INDEX($A:$A;2):INDEX($A:$A;8000); Mappe1!$C$11);0)

Tipps für Profis

  • Verwende die LET()-Funktion: In Excel 365 kannst Du die LET()-Funktion verwenden, um Berechnungen zu optimieren und die Lesbarkeit Deiner Formeln zu verbessern.

  • Eingehende Tests: Teste Deine Formeln stets mit unterschiedlichen Datenmengen, um sicherzustellen, dass sie unter allen Bedingungen korrekt funktionieren.

FAQ: Häufige Fragen

1. Warum ändert sich mein absoluter Zellbezug?
Absolut Zellbezüge können sich ändern, wenn Du Zeilen oder Spalten hinzufügst oder löschst. Um dies zu verhindern, nutze die INDEX()-Funktion.

2. Was ist der Vorteil von INDEX()?
Die INDEX()-Funktion ermöglicht es, dynamische Bereiche zu definieren, die nicht automatisch angepasst werden, wenn sich die Struktur Deiner Daten ändert.

3. Wie kann ich eine Array-Formel einfügen?
Um eine Array-Formel in Excel einzufügen, wähle den Zielbereich und drücke Strg + Shift + Enter, nachdem Du die Formel eingegeben hast.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige