Live-Forum - Die aktuellen Beiträge
Datum
Titel
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
13.10.2025 15:10:17
Überlappende Zeiträume über mehrere Tabellen mit leeren Zell
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
1628to1632
1628to1632
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

INDIREKT mit flexibler Spalte()

Forumthread: INDIREKT mit flexibler Spalte()

INDIREKT mit flexibler Spalte()
17.06.2018 09:30:40
erichm
Hallo,
ich übertrage aus einer Tabelle „Basis“ verschiedene Werte in die Tabelle „Ausw“.
Hierzu verwende ich die Formel in G4:
=WENN($A4>625;" ";ZÄHLENWENNS(INDIREKT("'Basis'!"&$B4&SPALTE(B3)&":"&$D4&SPALTE(B3));">="&$B$2; INDIREKT("'Basis'!"&$B4&SPALTE(B3)&":"&$D4&SPALTE(B3));"<="&$B$3))
Damit werden die Werte aus „Basis“ in Zeile 2 ermittelt ( = vom Datum 16.06.2018). Die Formel ist nach rechts und unten kopiert und funktioniert.
Jetzt muss ich folgende Änderung vornehmen:
Die Werte aus Basis von Zeile 3 ermitteln ( = vom Datum 15.06.2018) und in G4 eintragen, Formel wieder nach rechts und unten kopieren.
Mein Problem: Die flexible Verwendung aus o.g. Formel von SPALTE(B3) ? Bei der Änderung müsste quasi aus B3 dann C3 werden.
Anbei eine Musterdatei; in der Tabelle „Ausw“, Zelle F1 ändere ich mit der Zahl jeweils die neue Datumsreihenfolge.
https://www.herber.de/bbs/user/122145.xlsx
Besten Dank für eine Hilfe.
mfg
Anzeige

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

Betreff
Datum
Anwender
Anzeige
Ich kapier`s nicht ganz...
17.06.2018 12:01:07
{Boris}
Hi,
...was genau Deine "geänderte" Anforderung ist.
Mein Problem: Die flexible Verwendung aus o.g. Formel von SPALTE(B3) ? Bei der Änderung müsste quasi aus B3 dann C3 werden.
Allgemein: Das Ergebnis von SPALTE(Bezug) verändert sich beim horizontalen Kopieren entsprechend. Willst Du vertikal eine Veränderung erreichen, kannst Du ZEILE(Bezug) verwenden.
Aber wie gesagt: Ich hab`s (noch) nicht ganz kapiert...
VG, Boris
Anzeige
AW: Ich kapier`s nicht ganz...
17.06.2018 12:19:33
erichm
Ich habe jetzt folgende Lösung gefunden:
=WENN($A4>625;" ";ZÄHLENWENNS(INDEX(INDIREKT("Basis!$"&$B4&":$"&$D4);H$1;0);">="&$B$2; INDEX(INDIREKT("Basis!$"&$B4&":$"&$D4);H$1;0);"<="&$B$3))
Zelle H1: wird wie folgt geändert, wenn
Datum = 6, dann 2
Datum = 5, dann 3
Datum = 4, dann 4 usw.
Zellen ab I1 = H1+1
Damit klappt es.
mfg
Anzeige
AW: so wie Du schreibst, z.B. mit VERGLEICH() ...
17.06.2018 12:22:10
neopa
Hallo Erich,
... ersetze in G4 ... SPALTE(B3)... durch ... VERGLEICH(G$2;Basis!$A:$A;0) ...
Unabhängig davon könnte man in Deiner Datei die volatile Funktion INDIREKT() durch INDEX() ersetzen. Die Formel würde dann zwar etwas länger aber durch zusätzlichen Einsatz von 5*ZEILE(G1)-2 kann man dafür auch Deine Hilfszellen A4:D## einsparen.
Gruß Werner
.. , - ...
Anzeige
AW: INDIREKT mit flexibler Spalte()
17.06.2018 13:05:09
Barbaraa
Kann mich Boris nur anschließen: Ich verstehe auch nicht, was Du meinst.
Habe mir Deine Formeln angesehen und festgestellt, dass die Berechnung auf Manuell gestellt ist. Willst Du das wirklich?
Kannst Du umschalten bei Formeln - Berechnen - Automatisch.
Zur Erleichterung der Überprüfung:
Welche Formel liefert richtige Ergebnisse, welche falsche, und warum?
Vielleicht ein Tipp zum Überprüfen von Teilergebnissen einer verschachtelten Formel:
Wenn Du in der Formel eine Teilfunktion markierst und F9 drückst, wird sie durch ihr Ergebnis ersetzt.
Achtung: Aus der Formel mit Esc aussteigen, sonst bleibt das Ergebnis erhalten.
LGB
Anzeige
AW: die Änderung des Wertes in F1 ...
17.06.2018 13:31:59
neopa
Hallo Barbaraa,
... ergibt in Zeile 2 eine andere Datumsreihenfolge. Also für F1 =5 anstelle bisher 6 fängt diese mit dem 15.06.2018 anstelle dem 16.06.2018 an. Und dieser "neue" Datumswert steht in Basis!A:A in A3 also in Zeile 3, während der für den 16.06.2018 in Basis!A2 steht.
So hab jedenfalls ich Erichs Vorgaben interpretiert. Deshalb mein Vorschlag mit VERGLEICH().
Die manuelle Berechnungsoption hat Erich evtl./ möglicherweise u.a. wegen den vielen INDIREKT()-Formeln gesetzt. Deshalb ja auch mein Hinweis auf den Ersatz von INDIREKT() durch INDEX().
Mein Formelvorschlag für G4 lautet komplett mit INDEX() somit so:
=WENN($A4>625;" ";ZÄHLENWENNS(INDEX(Basis!$A:$BZ;VERGLEICH(G$2;Basis!$A:$A;0); 5*ZEILE(G1)-2):INDEX(Basis!$A:$BZ;VERGLEICH(G$2;Basis!$A:$A;0);5*ZEILE(G1)+2);">="&$B$2; INDEX(Basis!$A:$BZ;VERGLEICH(G$2;Basis!$A:$A;0);5*ZEILE(G1)-2):INDEX(Basis!$A:$BZ; VERGLEICH(G$2;Basis!$A:$A;0);5*ZEILE(G1)+2);" Gruß Werner
.. , - ...
Anzeige
nochmals Hilfe notwendig
17.06.2018 18:33:52
erichm
Hallo Werner,
diese Lösung ist natürlich besser und wesentlich schneller als meine INDIREKT-Lösung.
Bei der Umsetzung habe ich jetzt folgendes Problem, wenn ich mehr Flexibilität einbaue:
Die Wertermittlung zwischen den Spalten (im Beispiel C bis G, also 5 Spalten) soll flexibel sein, also auch mal mehr oder weniger Spalten.
Ich dachte, dass bei der INDEX-Formel die "5" für die Differenz der Spalten steht?
Wenn ich die 5 aber auf z.B. 9 abändere, dann erhalte ich andere Ergebnisse wie bei der Formel mit INDIREKT. Eine Anpassung der INDEX-Formel gelingt mir nicht.
Zum besseren Verständnis, habe ich eine neue Datei hochgeladen.
Eine Tabelle mit der Formel INDEX und eine Tabelle mit der Formel INDIREKT.
Die "Spaltenvariable" ist in B1 (Änderung nur jeweils in der Tabelle INDEX notwendig).
https://www.herber.de/bbs/user/122152.xlsx
Besten Dank nochmal.
mfg
Anzeige
AW: nochmals Hilfe notwendig
17.06.2018 19:54:53
Barbaraa
Probier mal folgende Formel für G4 

=WENN($A4>625;" ";ZÄHLENWENNS(INDEX(Basis!$A:$BZ;VERGLEICH(G$2;Basis!$A:$A;0);
ZEILE()*$B$1+3-4*$B$1):INDEX(Basis!$A:$BZ;VERGLEICH(G$2;Basis!$A:$A;0);
ZEILE()*$B$1+2-3*$B$1);">="&$B$2;INDEX(Basis!$A:$BZ;VERGLEICH(G$2;Basis!$A:$A;0);
ZEILE()*$B$1+3-4*$B$1):INDEX(Basis!$A:$BZ;VERGLEICH(G$2;Basis!$A:$A;0);
ZEILE()*$B$1+2-3*$B$1);"
LGB
Anzeige
AW: nochmals Hilfe notwendig
18.06.2018 20:28:17
erichm
Hallo Barbaraa,
danke – funktioniert prima! Werner hat nochmals eine Optimierung eingestellt, die werde ich verwenden.
Danke für die weiteren Hinweise im ersten Post; eine manuelle Berechnung habe ich eingestellt, weil die Dateien sehr groß sind, da ist insbesondere in einer Testphase die sofortige Berechnung eher lästig.
mfg
Anzeige
AW: dazu bedarf es nur einer kleinen Änderung ...
17.06.2018 20:19:03
neopa
Hallo Erich,
... ersetze in der Formel (2mal): ...5*ZEILE(G1)-2... durch: ... $B$1*(ZEILE(G1)-1)+3...
und der Vollständigkeit halber sollte die Formel dann wie folgt beginnen: 

=WENN((ZEILE(G1)-1)*$B$1>625;"";...
um auch wirklich die Hilfszellen A4:A### einzusparen.
Gruß Werner
.. , - ...
Anzeige
AW: und im anderen Formelteil ...
17.06.2018 20:26:39
neopa
... und zwar in ... 5*ZEILE(G1)+2... lediglich die 5 durch $B$1 ersetzen.
Die gesamte Formel lautet dann in G1:
=WENN((ZEILE(G1)-1)*$B$1>625;"";ZÄHLENWENNS(INDEX(Basis!$A:$BZ;VERGLEICH(G$2;Basis!$A:$A;0);
$B$1*(ZEILE(G1)-1)+3):INDEX(Basis!$A:$BZ;VERGLEICH(G$2;Basis!$A:$A;0);$B$1*ZEILE(G1)+2);
">="&$B$2;INDEX(Basis!$A:$BZ;VERGLEICH(G$2;Basis!$A:$A;0);$B$1*(ZEILE(G1)-1)+3):
INDEX(Basis!$A:$BZ;VERGLEICH(G$2;Basis!$A:$A;0);$B$1*ZEILE(G1)+2);"
Gruß Werner
.. , - ...
Anzeige
AW: und im anderen Formelteil ...
18.06.2018 20:29:42
erichm
Hallo Werner,
vielen Dank für diese Lösung. Ich denke, jetzt habe ich die Formel verstanden, habe bereits „Verschiebungen“ wie anderer Zeilenbeginn, anderer Spaltenbeginn probiert und hat funktioniert. Zusammen mit dem Wegfall der Hilfsspalten gehen die Berechnungen jetzt sehr schnell.
Besteht hier evtl. noch die Möglichkeit, dass die Formel auf verschiedene „Basistabellen“ zugreifen kann? Also Tabellen
Basis1
Basis2
Basis3
Grundsätzlich könnte ich das so einstellen, dass ich die Formel dreimal in eine Zelle eingebe und über eine Zusatzzelle mit 1 oder 2 oder 3 dann die richtige Tabelle „gezogen“ wird. Aber das dürfte nicht effizienter sein, wie wenn ich einfach für jede Basistabelle eine eigene Rechentabelle erstelle.
Bei einer Lösung mit INDIREKT (Bezug zum jeweiligen Tabellennamen) lässt die Performance natürlich drastisch nach und scheidet dadurch wieder aus.
Besten Dank, falls es da noch eine Idee gibt. Ich stelle den Thread deswegen wieder offen.
Mit freundlichen Grüßen
Anzeige
AW: sehe es momentan so ...
18.06.2018 20:59:00
neopa
Hallo Erich,
... wie Du es hier bereits selbst vermutet hast, wenn Deine 3 Tabellen getrennt sein müssen.
Was spricht aber dagegen, dass diese 3 Tabellen auf einem Tabellenblatt zusammengeführt werden? Und zwar so, dass die 2. und 3. „Basistabelle“ jeweils ab einer exakt definierten Zeilennummer beginnen?
Dann könnte man die bestehende Formel diesbzgl. in Bezug auf eine zusätzliche variable Eingabezelle (mit 1;2;3) entsprechend erweitern.
Gruß Werner
.. , - ...
Anzeige
AW: sehe es momentan so ...
19.06.2018 18:53:15
erichm
Ja logisch!!
Die Zeilenanzahl "pro Tabelle" kann ich fixieren. Es sind maximal 750 Zeilen. Da derzeit in Zeile 4 begonnen wird beginnt die nächste bei 754, dann bei 1504 usw...
Wenn eine "Tabelle" dann mal weniger Zeilen hat, dürfte dies ja kein Problem sein.
Diese Fixierung müsste doch auch über eine entsprechende Änderung von "Zeile(G1)" möglich sein? Das habe ich heute mal probiert, aber ohne Erfolg.
Das mit der Eingabezelle ist mir in Bezug auf eine Formeländerung aber völlig unklar.
Insofern bitte nochmals eine Hilfestellung.
Besten Dank.
mfg
Anzeige
AW: dies verwirrt mich nun doch etwas ...
19.06.2018 19:13:51
neopa
Hallo Erich,
... denn in Deiner bisherige Basistabelle war dessen erste Datensatz immer in Zeile2. In Zeile 4 dagegen hat die Auswertungstabelle begonnen.
Aber Du wolltest doch neu zusätzlich in dieser variabel auf drei verschiedene Basistabellen Bezug nehmen. Oder? Demzufolge würde ich die drei (oder doch mehr?) Basistabellen in einem Tabellenblatt (Basisdaten) zusammenstellen. Die erste beginnt weiter bei Datenzeile 2, die zweite z.B. bei 1002 und die 3. bei 2002.
Wie auch immer, bereite dafür mal einen Auszug aus einer neuen Beispieltabelle auf und stelle diese dann hier ein.
Gruß Werner
.. , - ...
Anzeige
AW: dies verwirrt mich nun doch etwas ...
19.06.2018 19:54:54
erichm
Hallo Werner,
das war missverständlich; ich meinte "in welcher Zeile die neue Berechnung begonnen werden muss". Aber da liegt vielleicht mein Denkfehler bei der Formeländerung.
Habe jetzt die letzte Datei aktualisiert:
1. aktuellste Formel
2. Basistabelle mit "drei Tabellen":
erste beginnt in Zeile 2
zweite beginnt in Zeile 1002
dritte beginnt in Zeile 2002
https://www.herber.de/bbs/user/122188.xlsx
Jetzt bin ich gespannt.
Danke.
mfg
Anzeige
AW: dazu nachgefragt ...
19.06.2018 20:16:12
neopa
Hallo Erich,
... Deine drei "Basistabellen" scheinen identisch zu sein. In Wirklichkeit sicherlich nicht? Oder sind die Datumswerte in Spalte A je "Basistabelle" immer identisch?
Ich schau es mir dann morgen an.
Gruß Werner
.. , - ...
AW: dazu nachgefragt ...
19.06.2018 20:42:35
erichm
Hallo Werner,
bei der Basistabelle sind die Datumswerte "pro Tabelle" immer identisch.
Die Werte sind immer unterschiedlich, ich habe das im Beispiel nur "aus Faulheit" nicht geändert.
Keine Eile - Danke.
mfg
Anzeige
AW: dann reicht wohl eine einfache Erweiterung ...
20.06.2018 08:01:54
neopa
Guten Morgen Erich,
... und zwar zum VERGLEICHS()Formelteil wird einfach +$F$2*1000 addiert, wobei in F2 die "Basistabellennummer" reduziert um 1 steht (ohne dies dann eben +($F$2-1)*1000;)
 ABCDEFGHI
1Tr5  Datum:6   
2von-2  BasisTab216.06.201815.06.201814.06.2018
3bis7       
4      112
5      212
6      101
7      131

Formeln der Tabelle
ZelleFormel
G2=INDEX(Basis!$A$2:$A$75;SPALTE()-$F$1;ZEILE($A$1)+$AR2)
G4=WENN((ZEILE(G1)-1)*$B$1>625;"";ZÄHLENWENNS(INDEX(Basis!$A:$BZ;VERGLEICH(G$2;Basis!$A:$A;0)+$F$2*1000;$B$1*(ZEILE(G1)-1)+3):INDEX(Basis!$A:$BZ;VERGLEICH(G$2;Basis!$A:$A;0)+$F$2*1000;$B$1*ZEILE(G1)+2); ">="&$B$2;INDEX(Basis!$A:$BZ;VERGLEICH(G$2;Basis!$A:$A;0)+$F$2*1000;$B$1*(ZEILE(G1)-1)+3):INDEX(Basis!$A:$BZ;VERGLEICH(G$2;Basis!$A:$A;0)+$F$2*1000;$B$1*ZEILE(G1)+2); "<="&$B$3))

Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
F2Liste 0;1;2 

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
G41. / Formel ist =UND(G4<>" ";G4=H4;G4=I4;G4=J4)Abc
G42. / Formel ist =UND(G4<>" ";G4=H4;G4=I4)Abc
G43. / Formel ist =UND(G4<>" ";G4=H4)Abc


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
AW: Klasse gelöst!
20.06.2018 17:12:49
erichm
Hallo Werner,
habe das schon intensiv getestet. Alles bestens. Trotz umfangreicher Daten hat die Formel wirklich eine gute Performance, da machen die Analysen richtig Spaß.
Die "Datengültigkeit" ist auch eine sehr gute Idee.
Vielen Dank für die wiederum tolle Hilfe!
mfg
AW: danke, freut mich auch owT
20.06.2018 19:18:14
neopa
Gruß Werner
.. , - ...
Anzeige
;
Anzeige
Anzeige

Infobox / Tutorial

Flexible Verwendung von INDIREKT in Excel

Schritt-für-Schritt-Anleitung

  1. Formel vorbereiten: Beginne mit deiner Basisformel, die die Funktion INDIREKT verwendet. Diese Formel sollte den Bezug zu deiner Basis-Tabelle herstellen. Ein Beispiel könnte so aussehen:

    =WENN($A4>625; " "; ZÄHLENWENNS(INDIREKT("'Basis'!" & $B4 & SPALTE(B3) & ":" & $D4 & SPALTE(B3)); ">=" & $B$2; INDIREKT("'Basis'!" & $B4 & SPALTE(B3) & ":" & $D4 & SPALTE(B3)); "<=" & $B$3))

  2. Flexibilität einbauen: Um die Formel flexibler zu gestalten, kannst du SPALTE(B3) durch eine dynamische Bezugnahme ersetzen, wie z.B. VERGLEICH(G$2;Basis!$A:$A;0), damit sich die Formel beim Kopieren automatisch anpasst.

  3. Anpassungen vornehmen: Wenn du die Zeile oder Spalte ändern möchtest, stelle sicher, dass deine Bezüge entsprechend angepasst werden. Du kannst die Zeilen- und Spaltennummern dynamisch gestalten, indem du die Funktionen ZEILE() oder SPALTE() verwendest.

  4. Testen der Formel: Überprüfe die Formel auf Fehler, insbesondere wenn du sie kopierst. Du kannst die Teilergebnisse einer Formel überprüfen, indem du Teile der Formel markierst und F9 drückst.

Häufige Fehler und Lösungen

  • Fehler bei der Bezugnahme: Wenn die Formel nicht die erwarteten Werte liefert, überprüfe, ob die Referenzen korrekt gesetzt sind. Eine häufige Quelle sind manuelle Berechnungsoptionen. Stelle sicher, dass du auf „Automatisch“ eingestellt hast.

  • Verwendung von INDIREKT: INDIREKT kann die Performance beeinträchtigen, besonders bei großen Datenmengen. Ersetze es durch INDEX und VERGLEICH, um die Geschwindigkeit zu erhöhen.

Alternative Methoden

  • INDEX und VERGLEICH: Diese Funktionen sind oft effizienter als INDIREKT. Du kannst sie verwenden, um auf Daten in einer Tabelle zuzugreifen, ohne die Performance zu beeinträchtigen. Eine Beispiel-Formel könnte so aussehen:

    =ZÄHLENWENNS(INDEX(Basis!$A:$BZ; VERGLEICH(G$2;Basis!$A:$A;0); $B$1*(ZEILE(G1)-1)+3):INDEX(Basis!$A:$BZ; VERGLEICH(G$2;Basis!$A:$A;0); $B$1*ZEILE(G1)+2); ">=" & $B$2; INDEX(Basis!$A:$BZ; VERGLEICH(G$2;Basis!$A:$A;0); $B$1*(ZEILE(G1)-1)+3):INDEX(Basis!$A:$BZ; VERGLEICH(G$2;Basis!$A:$A;0); $B$1*ZEILE(G1)+2); "<=" & $B$3)

  • Daten zusammenführen: Statt mehrere Basis-Tabellen zu verwenden, kannst du alle Daten in einer Tabelle zusammenfassen und durch eine zusätzliche Eingabezelle die gewünschte Tabelle auswählen.

Praktische Beispiele

  • Beispiel 1: Du hast eine Tabelle Basis, in der die Daten für verschiedene Tage stehen. Verwende VERGLEICH, um den richtigen Wert für ein bestimmtes Datum zu finden:

    =INDEX(Basis!$A:$A; VERGLEICH(DATUM(2018;6;15); Basis!$A:$A; 0))

  • Beispiel 2: Wenn du eine dynamische Auswahl zwischen verschiedenen Datenquellen benötigst, kannst du eine Zelle für die Auswahl der Grundtabelle nutzen:

    =INDEX(INDIREKT("Basis" & $F$2 & "!A:A"); VERGLEICH(G$2;INDIREKT("Basis" & $F$2 & "!A:A"); 0))

Tipps für Profis

  • Verwende bedingte Formatierungen: Um deine Daten visuell hervorzuheben, nutze bedingte Formatierungen, um bestimmte Werte zu markieren. Dies kann dir helfen, schnell zu erkennen, ob die Berechnungen korrekt sind.

  • Berechnungsoptionen anpassen: Bei großen Datenmengen kann es sinnvoll sein, die Berechnung auf „Manuell“ zu stellen, um die Performance zu verbessern, während du Änderungen an Formeln vornimmst.

FAQ: Häufige Fragen

1. Was ist der Unterschied zwischen INDIREKT und INDEX? INDIREKT erstellt einen Bezug auf eine Zelle oder einen Bereich auf Basis eines Textstrings, während INDEX direkt einen Wert aus einem Bereich basierend auf Zeilen- und Spaltennummern zurückgibt. INDEX ist oft schneller und effizienter.

2. Wie kann ich meine Formel flexibler gestalten? Nutze SPALTE() oder ZEILE() in Kombination mit VERGLEICH, um dynamische Bezugnahmen zu erstellen, die sich beim Kopieren der Formel anpassen.

3. Was kann ich tun, wenn meine Formeln sehr langsam sind? Überprüfe, ob du INDIREKT verwendest. Versuche, stattdessen INDEX und VERGLEICH zu nutzen, da diese Funktionen weniger rechenintensiv sind und die Performance verbessern können.

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