Live-Forum - Die aktuellen Beiträge
Datum
Titel
23.04.2024 22:08:10
Auflisten in Spalte VBA
23.04.2024 21:33:46
Nur farbige/nicht farbige Zellen markieren
23.04.2024 21:29:56
Bestimmte Zellen auf andere Arbeitsblätter kopieren
23.04.2024 17:41:19
Link erstellen mit Formel oder VBA?
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1628to1632
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
1628to1632
1628to1632
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

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

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
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
.. , - ...
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
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
.. , - ...
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
.. , - ...
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
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
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
.. , - ...
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
.. , - ...

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige