Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1760to1764
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
Inhaltsverzeichnis

Formel soll konstant bleiben

Formel soll konstant bleiben
05.06.2020 18:40:55
Christian
Hallo,
mal eine kurze Frage, es geht um folgende Formel
=INDEX(Ergebnis!C:C;KKLEINSTE(WENN(Ergebnis!K$1:K$20000
bekomme ich ihr irgendwie beigebracht, dass der Bereich 1:20000 selbst dann konstant bleibt, wenn ich zeilen lösche oder hinzufüge?
hab da eben 1000 Zeilen in der Tabelle Ergebnis gelöscht und da stand dann trotz de $ Zeichen K$1:K$19000 statt K$1:K$20000.
das selbe mit ZEILE(1:20000), gibt es auch da die Möglichkeit, dass dies konstant bleibt, wenn ich in der Tabelle in der die Formel steht eine Zeile zufüge oder lösche?
Danke
Christian

28
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel soll konstant bleiben
05.06.2020 18:52:13
onur
Schreibe statt
K$1:K$20000

das hier
INDIREKT("K$1:K$20000")

AW: Formel soll konstant bleiben
05.06.2020 19:58:48
Christian
Hallo Onur
habe versucht folgendes daraus zu machen:

INDEX(Ergebnis!C:C;KKLEINSTE(WENN(INDIREKT("Ergebnis!K$1:K$20000")
ist das das was du gemeint hast?
Danke
Christian
AW: Formel soll konstant bleiben
05.06.2020 20:07:28
onur
Genau.
AW: mit INDEX(), AGGREGAT() und INDIREKT() ...
05.06.2020 19:59:20
neopa
Hallo Christian,
... solange Du nur im Tabellenblatt Ergebnis Zeilen löschst und/oder einfügst könntest Du dies z.B. wie folgt tun:
{=INDEX(Ergebnis!C:C;KKLEINSTE(WENN(INDIREKT("Ergebnis!K1:K20000")&lt31;ZEILE(C1:C20000)); ZÄHLENWENN(Ergebnis!K$1:K$20000;"&lt"&"31")-29)) }
Doch sobald Du auch in Deinem Tabellenblatt wo Du die Formel zu stehen hast auch evtl. Zeilen löschst und/oder einfügst hat Du damit nichts gekonnt.
Ich würde deshalb und auch sonst folgender Formel nutzen:
=INDEX(Ergebnis!C:C;AGGREGAT(15;6;ZEILE(INDEX(C:C;1):C99999)/(INDIREKT("Ergebnis!K$1:K$20000")&lt31);ZÄHLENWENN(Ergebnis!K$1:K$20000;"&lt31")-29))
Gruß Werner
.. , - ...
Anzeige
AW: mit INDEX(), AGGREGAT() und INDIREKT() ...
05.06.2020 20:06:55
Christian
Hallo Werner,
habe es jetzt mit Onurs Vorschlag mit folgender Formel probiert:
INDEX(Ergebnis!C:C;KKLEINSTE(WENN(INDIREKT("Ergebnis!K$1:K$20000")
damit konnte ich so wie es aussieht in beiden Blättern Zeilen löschen und einfügen.
Deine Variante funktioniert aber auch.
Jetzt stehe ich nur vor der Frage, funktionieren beide Varianten auch wirklich oder es funktioniert nur deine und ich übersehe was.
Falls nur deine funktioniert, habe ich jetzt ein weiteres Problem. Nämlich, es gibt noch ne zweite Formel, bei ONURS Lösung konnte ich seinen Vorschlag einfach auf die zweite Formel übertragen, da ich deine Lösung nicht nachvollziehen kann, wie sie entstanden ist, kann ich es bei deiner Lösung nicht.
bist du dann bitte so nett und passt auch die Formel
=KKLEINSTE(WENN('Personen Archiv'!E2:E300"";ZEILE(INDIREKT("2:300")));30)-1
so an, dass sie sich nicht ändert wenn ich Zeilen zufüge oder lösche? Wobei das im Personen Archiv nie der Fall ist.
Gruß
Christian
Anzeige
AW: hierzu ...
05.06.2020 20:26:57
neopa
Hallo Christian,
... zunächst festgestellt, ich hatte in meinen Formeln tatsächlich vergessen auch den ZÄHLENWENN()-Formelteil zu "fixieren".
Meine Formel müsste dann natürlich so lauten:
=INDEX(Ergebnis!C:C;AGGREGAT(15;6;ZEILE(INDEX(C:C;1):C99999)/(INDIREKT("Ergebnis!K$1:K$20000")&lt31);ZÄHLENWENN(INDIREKT("Ergebnis!K1:K20000");"&lt31")-29))
Ich hab das ausnahmsweise mal mit INDIREKT() fixiert, weil ich annahm, dass es nur die eine Formel betrifft. Ansonsten nutze ich grundsätzlich zum fixieren INDEX() weil INDIREKT() volatil ist.
Mehr dazu sieh mal hier: https://www.online-excel.de/excel/singsel.php?f=24
Mit INDEX() wird zwar die Formel länger aber diese ist nicht nur volatil, sondern ich brauch die Formel nicht ändern, wenn ich z.: auch Spalten lösche und oder einfüge. Bei Einsatz mit INDIREKT() müsste ich dazu die schwer nachvollziehbare Z1S1-Schreibweise definieren
Außerdem nutze ich anstelle KKLEINSTE() in einer klassischen Matrixformel grundsätzlich AGGREGAT().
Für heute geh ich jetzt aber erst einmal offline.
Gruß Werner
.. , - ...
Anzeige
AW: hierzu ...
05.06.2020 20:35:03
Christian
Hallo Werner,
hatte aber auch ehrlich gesagt bei deiner ersten Formel keinen Fehler feststellen können.
Liegt vielleicht auch daran dass nicht alle 20000 Zeilen gefüllt sind, ich bei dieser Dimensionierung nur vorgesorgt habe für den Fall wenn.
Gruß
Christian
AW: hierzu ...
06.06.2020 10:52:04
Luschi
Hallo Werner,
wer immer wieder bei INDIREKT(), BEREICH.VERSCHIEBEN() u.ä. Excel-Funktionen auf deren Volatilität hinweist, der müßte doch die 'Bedingte Formatierung' meiden wie die Pest; oder habe ich folgenden Artikel:
https://www.online-excel.de/excel/singsel.php?f=172
da falsch verstanden?
Wer seine Excel-Tabellen mit Hilfe der 'bedingten Formatierung aufpimmt, bis die Grafikkarte glüht, der muß sich auch vor volatilen Funktionen nicht fürchten.
Gruß von Luschi
aus klein-Paris
Anzeige
AW: den Hinweis werde ich auch weiterhin geben ...
06.06.2020 15:44:19
neopa
Hallo Luschi,
.... d.h. aber nicht, dass ich die beiden Funktionen und bedingte Formatierungen nicht anwende.
Für die bedingte Formatierung gibt es meist keine wirkliche Alternative für INDIREKT() und BEREICH.VERSCHIEBEN() jedoch gibt es in den meisten Fällen sehr wohl gute bzw. teils auch bessere alternative Möglichkeiten durch Einsatz der Funktion INDEX(), wenn auch nicht immer und auch nicht immer sinnvoll. Es gibt jedoch durchaus eine Reihe von Anwendungsfälle, wo weder INDIREKT() noch BEREICH:VERSCHIEBEN() durch keine Standardfunktion ersetzbar ist. Doch verhältnismäßig betrachtet, ist INDEX() die "universellere" Funktion, also zumindest für mich.
Gruß Werner
.. , - ...
Anzeige
Das mit INDEX ist schon richtig, ...
06.06.2020 20:48:13
Luc:?
…Werner,
nur wissen die Allermeisten nicht, was damit alles möglich ist, auch im Ggsatz zu anderer Calc-Software. Steht ja auch nicht alles in der Fktshilfe, weshalb man experimentieren muss.
Und was INDIREKT betrifft, das kann teils mehr, teils weniger als zumeist bekannt ist:
Nicht nur kein Zugriff auf geschlossene Dateien, sondern weder diskontinuierliche noch SchnittmengenBereiche wdn verarbeitet (Bereichsoperatoren ; und Leerzeichen). Dafür kann aber mit Z1S1-Angaben gearbeitet wdn, was dann allerdings (als Text) nicht automatisch in andere Sprachen übertragen wird. Aber ebenso wie INDEX liefert sie ZellBezüge*.
* Das macht auch meine UDF TinRange, die außerdem noch die genannten Bereichsoperatoren (also alle 3) verarbeiten kann. Auf Z1S1 hatte ich aber verzichtet. ADRESSE ist besser und kann auch übersetzt wdn.
Gruß+schöWE, Luc :-?
„Die universelle Befähigung zur Unfähigkeit macht jede menschliche Leistung zu einem unglaublichen Wunder.“ Stapps ironisches Paradoxon
Nichtsdestotrotz Durchblick verbessern mit …

Anzeige
bedF ist volatil, daher INDIREKT() bzw. B.V() egal
07.06.2020 07:59:50
lupo1
Volatilität allein ist noch kein Problem
06.06.2020 22:19:38
Daniel
Erst wenn man Formeln, die ansich schon eine lange Berechnungszeit haben, in großer Anzahl volatil macht, wird es kritisch (z.B. SummeWenn, Summenprodukt, Matrixformeln).
Ein weiteres Problem von Indirekt ist, dass es nicht nur die Zellbezüge fixiert, sondern auch den Tabellenblattnamen, dh. es findet keine Anpassung statt, wenn das Tabellenblatt des Bezugs umbenannt wird und das dürfte aber in den meisten Fällen unerwünscht sein.
AW: mit AGGREGAT() und INDEX() ...
06.06.2020 15:54:40
neopa
Hallo Christian,
... einfach so: =AGGREGAT(15;6;ZEILE(A1:A1000)/('Personen Archiv'!E2:INDEX(E:E;300)"");30)
Gruß Werner
.. , - ...
AW: mit AGGREGAT() und INDEX() ...
06.06.2020 18:35:57
Christian
Hallo Werner,
diese Formel gibt bei mir #ZAHL! aus
Gruß
Christian
Anzeige
hab das Häkchen vergessen owT
06.06.2020 18:38:31
Christian
.
AW: dann gibt es bei Dir keine 30 Daten owT
06.06.2020 19:44:07
neopa
Gruß Werner
.. , - ...
AW: dann gibt es bei Dir keine 30 Daten owT
06.06.2020 19:48:38
Christian
die bisherige Formel
=KKLEINSTE(WENN('Personen Archiv'!E2:E300"";ZEILE(INDIREKT("2:300")));30)-1
gibt 226 aus, was auch korrekt ist. Insgesamt sind es 44 Daten im bereich E2:E300
Gruß
Christian
AW: beide Formeln ...
06.06.2020 20:06:39
neopa
Hallo Christian,
... sowohl mit KLEINSTE() als auch mit AGGREGAT() geben bei mir die gleichen Ergebniswerte aus.
Kannst Du bitte mal verbal beschreiben, was Du für Daten in Spalte E zu stehen hast. Texte? Datenwerte die mit Formeln ermittelt werden, wo auch ="" stehen kann? Und welches Ergebnis Du genau ermitteln willst. Ich schau es mir dann morgen Vormittag wieder an. Für heute geh ich jetzt gleich offline.
Gruß Werner
.. , - ...
Anzeige
AW: nun verständlich ...
07.06.2020 09:09:23
neopa
Hallo Christian,
... ich hatte die Formeln nur im selben Tabellenblatt wie die Daten geprüft, sorry.
Richtig müsste die Formel für unterschiedliche Tabellenblätter natürlich so lauten:
=AGGREGAT(15;6;ZEILE(A1:A999)/('Personen Archiv'!E2:INDEX('Personen Archiv'!E:E;300)"");30)
Dies jedoch nur dann so wenn Du Zeilen vor Zeile 300 einfügen oder löschen willst und der auszuwertende Datenbereich konstant bleiben soll.
Ansonsten reicht
=AGGREGAT(15;6;ZEILE(A1:A999)/('Personen Archiv'!E2:E300"");30)
Gruß Werner
.. , - ...
Anzeige
AW: nun verständlich ...
07.06.2020 10:03:15
Christian
Hallo Werner,
jetzt funktioniert es. Danke.
Wie gesagt im PA werden keine Zeilen gelöscht / zugefügt
Christian
AW: dann ist ja nun alles gut owT
07.06.2020 10:15:35
neopa
Gruß Werner
.. , - ...
AW: Bsp. Datei
07.06.2020 09:53:56
Luschi
Hallo Christian,
und trotzdem kann Deine Formel nur funkionieren, wenn Du daraus eine Matrixfunktion machst und die Formeleingabe mit abschließt!
In der Eingabezeile steht dann aber:
{=KKLEINSTE(WENN('Personen Archiv'!E2:E300"";ZEILE(INDIREKT("2:300")));30)-1}
Gruß von Luschi
aus klein-Paris
AW: Bsp. Datei
07.06.2020 10:04:43
Christian
Hallo Luschi,
danke für den Hinweis.
Nutze sie auch als Matrixfunktion. War ehrlich gesagt schlichtweg zu faul die {} mitzuposten, sie werden ja nicht mitkopiert.
Gruß
Christian
AW: soll ja in Office365 nicht mehr nötig sein owT
07.06.2020 10:17:11
neopa
Gruß Werner
.. , - ...
AW: soll ja in Office365 nicht mehr nötig sein owT
07.06.2020 10:37:58
Christian
Naja habe kein Office 365 gemietet sondern Office 2019 gekauft.
AW: ein Grund mehr AGGREGAT() zu nutzen ;-) owT
07.06.2020 10:52:38
neopa
Gruß Werner
.. , - ...
K1:INDEX(K:K;20000)
05.06.2020 20:42:21
lupo1
... reicht meistens aus, da das Einfügen vor (oder das Löschen von) K1 nur eine Wahrscheinlichkeit von 1/20000 hat.
Die Leute mit der Versicherungspolice unterm Kopfkissen beruhigen sich dann gern noch mit
INDEX(K:K;1):INDEX(K:K;20000)

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige