Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.04.2024 08:56:18
Textbox in Tabellenblatt ohne Bestätigung in Zelle eintragen
20.04.2024 07:57:54
Tablle fürs E-Auto - Preis wenn kürzer als 60 Min Ladezeit
20.04.2024 00:49:19
Excel Sheet in einen Excel Kalender einfügen
20.04.2024 00:08:11
In Ordner nur das Speichern von Dateien erlauben
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1508to1512
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1508to1512
1508to1512
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Zellbezug trotz $-Fixierung nicht konstant

Zellbezug trotz $-Fixierung nicht konstant
18.08.2016 09:38:18
Juliane
Guten Morgen,
ich habe in einer Zelle eine Zählenwennfunktion, die mir über eine Spalte etwas auszählt.
Was ist das Problem?
Die Tabelle ist recht lang und in der Länge variabel. Um sicherzustellen, dass immer bis zum letzten Eitnrag gezählt wird, habe ich den Bereich auf $C$15:$C$5000 festgelegt. Mit wäre lieber gewesen es ginge soetwas wie "C:C", aber ich kann halt leider erst in C15 starten.
Jetzt kommt es aber vor, das Zeilen zwischendurch mal gelöscht werden (Zeile anklicken und dann "Strg." und "-". Das fürht leider dazu, dass sich in meiner Zählenwenn-Formel der eigentlich fixierte Bereich von $C$15:$C$5000 verkleinert.
Kann man das verhindern? Denn wenn ich das ein paar mal mache, dass wird der Bereich ja immer kleiner und irgendwann besteht das Risiko, dass ich in meiner formel nicht mehr alls Daten der Tabelle erfassen.
Ich hoffe, es gibt eine Lösung.
Danke vorab
und viele Grüße

20
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zellbezug trotz $-Fixierung nicht konstant
18.08.2016 09:48:50
JoWE
Hallo Juliane,
vllt. so:
=SUMME(INDIREKT("$C$15:$C$5000"))
Gruß
Jochen
AW: geht zwar auch, aber INDIREKT() ...
18.08.2016 09:50:34
...
Hallo Jochen,
... ist eine volatile Funktion. Diese sollte man vermeiden wo möglich. Hier wäre es möglich.
Gruß Werner
.. , - ...
AW: geht zwar auch, aber INDIREKT() ...
18.08.2016 09:55:38
Juliane
Oh, ok. siehe bitte meine Antwort und Rückfrage auf Jochen...
danke
$-Fixierung ist bei INDIREKT in dieser Form ...
18.08.2016 12:25:16
Luc:-?
…nicht erforderlich, Jochen,
denn die AdressAngabe ist unveränderlicher Text!
INDIREKT kann auch so gestaltet wdn, Juliane,
dass sich die Adresse anpassen kann.
Außerdem kann man jede Fml volatil anlegen, falls erforderlich, wenn man eine volatile Fkt geschickt hinzufügt.
Gruß, Luc :-?
Besser informiert mit …
Anzeige
AW: $-Fixierung ist bei INDIREKT in dieser Form ...
18.08.2016 13:36:51
Juliane
Hallo Luc,
ok, und was heißt das konkret? Wie kann ich INDIREKT anlegen, dass siech die Adresse autoamtisch anpasst, z. B. beim Einfügen von Spalten?
Würdest du INDIREKT der INDEX-Variante vorziehen?
AW: $-Fixierung ist bei INDIREKT in dieser Form ...
18.08.2016 14:11:54
Daniel
Hi
es kommt darauf an, welche Art der Anpassung du meinst.
da gibt es zwei verschiedene:
a) die Anpassung, die stattfindet, wenn du die Formel kopierst und in eine andere Zelle einfügst.
ob diese Anpassung stattfindet oder nicht, regelst du über absolute und relative Zellbezüge in der Formel. (A1 oder $A$1)
Wenn du diese Art der Anpassung in der Indirektfunktion haben willst, müsstest du die Zellbezüge nicht in der A1-Schreibweise beschreiben, sondern in der Z1S1-Schreibweise.
b) die Anpassung, die stattfindet, wenn du die Zellen, auf welche sich die Formel bezieht, verschiebst.
hier verwendet man Indirekt, wenn man diese Anpassung vermeiden will.
Wenn man hier haben will, dass sich die Formeln anpassen, kann man mit den normalen Zellbezügen arbeiten.
Wenn man haben will, dass sich Spalten beim Verschieben anpassen und Zeilen fix bleiben (oder umgekehrt), nimmt man Index.
Gruß Daniel
Anzeige
AW: $-Fixierung ist bei INDIREKT in dieser Form ...
19.08.2016 10:29:18
Juliane
Hallo,
ich habe zu b) eine Rückfrage:
Ich habe unter anderem folgende Formel
=SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT("H"&ZEILE(15:94028)))*(H15:H94028>=600)*(AC15:AC94028="x"))
Als ich eine Spalte eingefügt habe, hat sich diese Formeln (INDIREKT) nicht automatisch angepasst.
Was meinst du bei b) mit deiner Aussage "Wenn man hier haben will, dass sich die Formeln anpassen, kann man mit den normalen Zellbezügen arbeiten"?
Wie kann ich meine INDIREKT-Formel (s.o.) anpassen, damit es eine Z1S1-Schriebweise ist? Und dann würde sie sich automatisch anpassen?
AW: INDIREKT() mit Z1S1-Schreibweise ...
19.08.2016 16:57:59
...
Hallo Juliane,
... bei dieser Formel ist INDIREKT() zwingend notwendig.
Um auch dann eine korrekte Auswertung ohne Formelanpassung der notwendigen Daten vornehmen zu können, wenn Du vor Spalte H Spalten löschst oder einfügst, dann muss die Formel so geschrieben werden:
=SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT("Z"&ZEILE(H15:H94028)&"S"&SPALTE(H15);))*(H15:H94028>=600) *(AC15:AC94028="x"))
Gruß Werner
.. , - ...
Anzeige
AW: INDIREKT() mit Z1S1-Schreibweise ...
22.08.2016 08:03:40
Juliane
Guten Morgen,
Danke für deine Antwort. Ich denke, jetzt habe ich das mit dem automatisch anpassen verstanden. Nur hilft mir das dann leider mit meinem Ausgangsproblem nicht weiter. Ich hätte ja gern eine Formel, in der ich nicht mehr die Bereichsbegrenzung (Anzahl Zeilen, hier: "94028") möglihst lang angeben muss, sondern eine Fixierung vornehmen kann. So dass, sichd iese nicht mehr anpasst, selbst wenn ich Zeilen lösche.
werner hatte ja als Beispiel "INDEX(C:C;15):INDEX(C:C;5000)" vorgeschlagen. Du sagst ja, dass meine Formel (s.o.) nur per INDIREKT geht.
Gibt es eine Möglichkeit diese INDIREKT-Formel in Z1S1-Schreibweise in der Zeilenlänge zu fixieren?
Besten Dank und liebe Grüße
Anzeige
AW: INDEX() in INDIREKT() eingebettet ,,,
22.08.2016 08:20:36
...
Guten Morgen Juliane,
... auf vorgenannte Formel bezogen so:
=SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT("Z"&ZEILE(H15:INDEX(H:H;94028))&"S"&SPALTE(H15);))
*(H15:INDEX(H:H;94028)>=600)*(AC15:INDEX(AC:AC;94028)="x"))
Gruß Werner
.. , - ...
AW: $-Fixierung ist bei INDIREKT in dieser Form ...
18.08.2016 13:52:54
JoWE
Hey, Du bist ja auch da!!
OK, war mir bekannt :-)
War dem Kopiervorgang geschuldet,hatte mir die Änderung gespart.
Gruß
Jochen
AW: mit INDEX() und konstanter Zeilennummer ...
18.08.2016 09:49:04
...
Hallo Juliane,
... so: INDEX(C:C;15):INDEX(C:C;5000)
Gruß Werner
.. , - ...
AW: mit INDEX() und konstanter Zeilennummer ...
18.08.2016 09:54:42
Juliane
Hallo,
gehen INDIREKT oder INDEX-Formeln nicht zu Lasten der Perfromance? Mir war so....
Ist dann angepasst so richtig:
aus
=ZÄHLENWENNS(B15:B5000;"";C15:C5000;"")/7
wird
=ZÄHLENWENNS(INDEX(B:B;15):INDEX(C:C;5000);"";INDEX(C:C;15):INDEX(C:C;5000);"")/7
Anzeige
AW zu INDIREKT:ja; AW zu INDEX: teils; AW:JA; owT
18.08.2016 10:07:20
...
Gruß Werner
.. , - ...
AW: AW zu INDIREKT:ja; AW zu INDEX: teils; AW:JA; owT
18.08.2016 10:27:12
JoWE
Hallo,
der Einwand, man solle mit volatilen Funktion vorsichtig agieren ist völlig richtig.
Ist aber nur bei wirklich "GROßEN" Kalkulationstabellen entscheidend. Nun ist mir nicht bekannt, wie riesig die Tabelle tatsächlich ist und ob daher eine besondere Vorsicht angebracht sein sollte.
Gruß
Jochen
AW: AW zu INDIREKT:ja; AW zu INDEX: teils; AW:JA; owT
18.08.2016 10:33:19
Juliane
Hm,
also wenn ich das mit dem INDEX einfüge, dann erhalte ich einfach nur #Wert.
Kann ich INDEX und INDIREKT, so wie ihr es vorgeschlagne habt, in jede beliebige Formel einfügen?
Mal gucken, vielleicht kriege ich raus, warum es nicht klappt ...
Anzeige
AW: AW zu INDIREKT:ja; AW zu INDEX: teils; AW:JA; owT
18.08.2016 10:42:40
Daniel
Hi
das mit dem Index ist schon richtig.
du hast nur beim zweiten Index die falsche Spalte gewählt und der Zellbereich geht über zwei Spalten.
so hast du es
=ZÄHLENWENNS(INDEX(B:B;15):INDEX(C:C;5000);"";INDEX(C:C;15):INDEX(C:C;5000);"")/7
so wäre es richtig
=ZÄHLENWENNS(INDEX(B:B;15):INDEX(B:B;5000);"";INDEX(C:C;15):INDEX(C:C;5000);"")/7
bei Zählenwenns müssen die durchsuchten Zellbereiche gleich groß sein.
dein ersten Zellbereich umfasst zwei Spalten (B und C), dein zweiter Zellbereich nur eine (c), daher der #Wert-Fehler.
Gruß Daniel
Anzeige
AW: AW zu INDIREKT:ja; AW zu INDEX: teils; AW:JA; owT
18.08.2016 11:00:41
Juliane
Ok,
ich habe beide Varianten ausprobiert und Sie scheinen zu funktionieren. Gibt es Nachteile bei einer der Formeln? Mir ist insbesodnere die "Performance" wichtig, da ich in Summe eine sehr große Datei (also viele Reiter und Makros) habe.
Wie ist das denn mit den "" bei INDIREKT? Ich dachte immer ein Nachteil wäre, dass sich diese Formeln nicht automatisch anpassen, wenn z. B. eine Spalte hinzugefügt wird.
=ZÄHLENWENNS(INDEX(B:B;15):INDEX(B:B;5000);"";INDEX(C:C;15):INDEX(C:C;5000);"")/7
=ZÄHLENWENNS(INDIREKT("$B$15:$B$5000");"";INDIREKT("$C$15:$C$5000");"")/7
Viele Grüße und vielen Dank soweit.
Anzeige
AW: AW zu INDIREKT:ja; AW zu INDEX: teils; AW:JA; owT
18.08.2016 11:25:37
Daniel
Hi
normale Formeln werden immer dann neu berechnet, wenn sich im Zellbereich, den die Formel verwendet, ein Zellwert ändert.
Volatile Funktionen wie Indirekt werden bei JEDER Zellwertänderung neu berechnet, egal wo.
Bei Indirekt ist es halt so, dass Excel die verwendeten Zellen nicht direkt aus der dem Formeltext ermitteln kann, sondern hierzu erst das Indirekt berechnen muss, da wird dann gleich die ganze Formel berechnet.
Problematsich wird's, wenn du viele Formeln mit Indirekt hast, und diese Formeln Rechenzeitaufwendige Funktionen wie ZählenWenns verwenden.
bei Index wird der verwendete Zellbezug auch erst in der Formel berechnet, aber er ist immer eine Teilmenge des ersten Parameters der Indexfunktion, daher ist es nicht notwendig diese Funktion volatil zu machen, wenn man eben diesen ersten Parameter als Auslöser für die Berechnung verwendet.
ob es ein Vorteil oder Nachteil ist, dass sich die Formeln anpassen wenn man Zeilen oder Spalten hinzufügt oder löscht, kommt darauf an was du haben willst.
normale Zellbezüge passen sich immer an.
mit Indirekt erstellte Zellbezüge passen sich nie an und bleiben auf den selben Zellen.
bei mit Index erstellten Zellbezügen kann man selber bestimmen, ob sich die Zeilen anpassen und die Spalten fix bleiben, oder ob die Spalten fix bleiben und die Zeilen sich anpassen.
Gruß Daniel
Gruß Daniel
Anzeige
AW: AW zu INDIREKT:ja; AW zu INDEX: teils; AW:JA; owT
18.08.2016 11:38:52
Juliane
Hi,
ok, alles klar. Danke für die Erklärung. Dann gehe ich mal sicherheitshalber mit der Index-Formel.
VG

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige