Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1700to1704
1700to1704
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Zeiträume zuordnen

Forumthread: Zeiträume zuordnen

Zeiträume zuordnen
19.07.2019 10:26:18
WalterK
Schönen Vormittag,
ich sollte eine Lösung (Formel oder VBA) für eine Zeitraumzuordnung haben.


Tabelle1
 ABCDEFGHIJ
1IDVonBisZeile  dieses Ergebnis suche ichIDVonBis
2A10.10.201703.09.20192  2A10.01.201710.01.2017
3B15.03.201820.03.20183  2A13.02.201813.02.2018
4B03.05.201831.08.20184  2A03.09.201903.09.2019
5B01.10.201801.10.20185  3B15.03.201820.03.2018
6B15.10.201831.12.20186  4B03.05.201820.06.2018
7C13.02.201818.02.20187  4B13.07.201820.07.2018
8C15.03.201820.03.20188  4B03.08.201810.08.2018
9C10.05.201803.09.20199  4B20.08.201831.08.2018
10D13.02.201818.02.201810  7C15.02.201815.02.2018
11D15.03.201820.03.201811  9C20.06.201931.07.2019
12D03.05.201803.09.201912      
13          
14Ich suche eine Lösung für die Spalte G:G
15Hier soll die Zeilennummer von der Spalte D:D angegeben werden, in der die ID + der Zeitraum der rechten Tabelle zu der ID + dem Zeitraum der linken Tabelle fällt.
16          
17          
18dazu:
19die linke Tabelle hat ca. 1000 Datensätze
20die rechte Tabelle hat ca. 12000 Datensätze
21jede ID + Zeitraum der rechten Tabelle fällt in ein Zeitraum + ID der linken Tabelle, ohne Ausnahme
22          
23          

 verbundene Zellen 
A14:J14
A15:J15
A18:J18
A19:J19
A20:J20
A21:J21

verwendete Formeln
Zelle Formel Bereich R1C1 für Add In
D2: D12=ZEILE()  =ROW()

http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip
http://hajo-excel.de/tools.htm
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 14.15 einschl 64 Bit

Und hier noch die Beispieltabelle:
https://www.herber.de/bbs/user/130990.xlsx
Besten Dank für euere Hilfe,
Servus, Walter
Anzeige

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: so mE noch nicht eindeutig, denn ...
19.07.2019 11:39:33
neopa
Hallo Walter,
... wieso ist G2=2, der Zeitraum I2:J2 liegt damit außerhalb des Zeitbereiches von B2:C2.
Die Hilfsspalte D ist nicht notwendig. Da die Ermittlung sehr wahrscheinlich eher einmalig vorgenommen wird, kann man auch eine Formel nutzen.
Mein Vorschlag dafür wäre momentan folgender für G2:

=WENNFEHLER(AGGREGAT(15;6;ZEILE(A$2:A$10)/(B$2:B$10&lt=I2)/(C$2:C$10&gt=J2);1);WENN(J2B2;"später";"")))
und Formel nach unten kopieren.
Gruß Werner
.. , - ...
Anzeige
AW: sorry, hatte da falsche Formel kopiert ...
19.07.2019 11:43:28
neopa
Hallo,
... richtig ist für G2 folgende:

=WENNFEHLER(AGGREGAT(15;6;ZEILE(A$2:A$1000)/(A$2:A$1000=H2)/(B$2:B$1000&lt=I2)/(C$2:C$1000&gt=J2);1); WENN(J2B2;"später";"")))
Gruß Werner
.. , - ...
AW: Fehlt da noch ein "=" zwischen J2B2? owT
19.07.2019 12:11:50
Daniel
AW: ja, Forumssoftware hat das "verschluckt" ...
19.07.2019 12:20:33
neopa
Hallo Daniel,
... ich hatte vergessen je ein "&lt" und "&gt" noch zu maskieren und da passiert das, ohne dass man darauf aufmerksam wir.
Eine neuer Versuch, Formel G2:
=WENNFEHLER(AGGREGAT(15;6;ZEILE(A$2:A$1000)/(A$2:A$1000=H2)/(B$2:B$1000&lt=I2)/(C$2:C$1000&gt=J2);1);WENN(J2&ltB2;"früher";WENN(J2&gtB2;"später";"")))
Gruß Werner
.. , - ...
Anzeige
AW: und vielen Dank für Deinen Hinweis owT
19.07.2019 12:22:16
neopa
Gruß Werner
.. , - ...
TipTop. Hallo Werner, genau ....
19.07.2019 13:39:16
WalterK
..so wollte ich es haben. Perfekt.
Besten Dank dafür.
Beim ersten Zeitraum sollte es 10.10.2017 bis 10.10.2017 lauten, da hatte ich mich verschrieben.
Nochmals Danke und Servus, Walter
AW: bitteschön; & interessehalber nachgefragt ...
20.07.2019 17:06:53
neopa
Hallo Walter,
... zu Deiner Anfrage im thread : https://www.herber.de/forum/archiv/1700to1704/t1702798.htm hatte ich auch eine reine Formellösung entwickelt. Die hatte ich dort nur nicht eingestellt, da Du zuvor schon eine passende VBA-Lösung erhalten hattest und meine Formellösung immerhin drei Hilfsspalten mit jeweils einer Matrixfunktion(alität)sformel und in den drei Ergebnisspalten auch mit jeweils einer Matrixfunktion(alität)sformel.
Wenn Du Interesse und Zeit zu deren Testung hättest, könnte ich Dir diese hier noch bereitstellen. Mich würde dann interessieren, ob bei Deinen ca. 1000 (?) Ergebnisdatenzeilen noch ein vertretbare Auswertungszeit gewährleistet wäre.
Die drei Hilfsspalten hab ich übrigens nur deswegen eingesetzt, weil ich auch von völlig unsortierten Daten in A:C ausgegangen war.
Gruß Werner
.. , - ...
Anzeige
AW: bitteschön; & interessehalber nachgefragt ...
20.07.2019 19:27:00
WalterK
Hallo Werner,
ja ich habe Interesse an Deiner Formellösung. Hilfsspalten sind kein Problem.
Ich werde Dir dann berichten, das kann allerdings bis Ende der Woche dauern.
Danke und Servus, Walter
AW: dann ...
21.07.2019 10:16:06
neopa
Hallo Walter,
... stelle ich hier mal die "klassische" Variante ein. Mit "klassisch" meine ich hier, dass weder die Daten- noch die Ergebnisliste "Als Tabelle formatiert" wurden. Dies deshalb, weil dies hier im Forum einfacher darstellbar ist und möglicherweise dadurch auch der von mir gewählte Lösungsweg etwas schneller überschaut werden kann. Dies hat jedoch u.a. den Nachteil, dass Du die in den Formeln von mir definierten Zellbereiche (hier immer nur bis Zeile 99 gesetzt) von Dir noch an Deine Erfordernis angepasst werden müssen.
Deine Beispieldaten habe ich auch etwas willkürlich abgeändert und ergänzt sowie "durcheinander gewürfelt", um die Lösung für den allgemeinen Fall besser aufzuzeigen (wenn Datenliste immer nach "ID" und "Von" sortiert ist, könnte man mindestens zwei Hilfsspalten einsparen).
Nachfolgende Formeln einfach weit genug nach unten ziehend und die Formel in K2 zusätzlich eine Spalte nach rechts kopieren.
Arbeitsblatt mit dem Namen 'Tab'
 ABCDEFGHIJKL
1IDVonBis  IDVonBis sort "ID"sort "Von"sort "Bis"
2C01.10.201801.10.2018  A10.10.201705.06.2018 A10.10.201731.12.2017
3A12.12.201720.05.2018  A10.08.201820.12.2018 A12.12.201718.02.2018
4A13.02.201818.02.2018  B15.03.201820.03.2018 A13.02.201820.05.2018
5C10.05.201810.06.2018  B15.05.201831.08.2018 A05.06.201805.06.2018
6A05.06.201805.06.2018  C03.05.201815.05.2018 A10.08.201820.12.2018
7A10.08.201820.12.2018  C01.10.201801.10.2018 B15.03.201820.03.2018
8B15.03.201820.03.2018  C11.10.201811.11.2018 B15.05.201831.08.2018
9C03.05.201815.05.2018      C03.05.201815.05.2018
10C15.05.201831.08.2018      C10.05.201810.06.2018
11A10.10.201731.12.2017      C15.05.201831.08.2018
12C15.10.201820.03.2019      C01.10.201801.10.2018
13B15.05.201831.08.2018      C11.10.201811.11.2018
14C11.10.201811.11.2018      C15.10.201820.03.2019
15            
16            

ZelleFormel
F2=WENN(F1="ID";J2;WENN(AGGREGAT(14;6;K$2:K$99/(J$2:J$99=F1);1)&gtH1;F1;WENNFEHLER(INDEX(J:J;VERWEIS(9;1/(J$1:J$99=F1);ZEILE(A$2:A$99)))&"";"")))
G2=WENN(F2="";"";WENN(F2>F1;INDEX(K:K;VERGLEICH(F2;J:J;0));AGGREGAT(15;6;K$2:K$99/(K$2:K$99&gtH1)/(J$2:J$99=F2);1)))
H2=WENN(F2="";"";AGGREGAT(15;6;L$2:L$99/(J$2:J$99=F2)/(K$2:K$99&gt=L$1:L$99*(J$1:J$99=F2));ZÄHLENWENN(F$2:F2;F2)))
J2=INDEX(A:A;1+VERGLEICH(AGGREGAT(14;6;ZÄHLENWENN(A$2:A$99;"&gt="&A$2:A$99);ZEILE(A1));INDEX(ZÄHLENWENN(A$2:A$99;"&gt="&A$2:A$99););0))&""
K2=WENN($J2="";"";AGGREGAT(15;6;B$2:B$99/($A$2:$A$99=$J2);ZÄHLENWENN($J$2:$J2;$J2)))
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Ergänzend angemerkt:
Hätte dann zusätzlich auch noch eine Lösungsvariante, wo sowohl die Quelldaten- als auch die Hilfsdaten- und die Ergebnisdatenliste "Als Tabelle formatiert" sind. Diese Variante kommt gänzlich ohne jeglichen Zelladressbezug aus. Die Auswertungslösung ist aber prinzipiell mit der hier aufgeziegten identisch. Nur die Länge deren Formeln verlängert sich durch die Tabellenfeldnamensbezüge natürlich. Dies hat aber nicht nur den Vorteil, dass bei Datenerweiterung keine Formeländerung mehr notwendig werden, sondern alle drei von einander abhänglegten Tabellen (Quell-, Hilfs- und Ergebnis-) innerhalb des Tabellenblattes oder sogar Tabellenblattübergreifend nachträglich jederzeit beliebig verschoben werden können.
Gruß Werner
.. , - ...
Anzeige
AW: dann ...
21.07.2019 11:03:28
WalterK
Hallo Werner,
wenn ich Deine Zeiträume her nehme komme ich allerdings auf folgendes Ergebnis:


Tabelle1
 ABCDEFGH
1IDVonBis  IDVonBis
2C01.10.201801.10.2018  A10.10.201720.05.2018
3A12.12.201720.05.2018  A05.06.201805.06.2018
4A13.02.201818.02.2018  A10.08.201820.12.2018
5C10.05.201810.06.2018  B15.03.201820.03.2018
6A05.06.201805.06.2018  B15.05.201831.08.2018
7A10.08.201820.12.2018  C03.05.201831.08.2018
8B15.03.201820.03.2018  C01.10.201801.10.2018
9C03.05.201815.05.2018  C11.10.201820.03.2019
10C15.05.201831.08.2018     
11A10.10.201731.12.2017     
12C15.10.201820.03.2019     
13B15.05.201831.08.2018     
14C11.10.201811.11.2018     

http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip
http://hajo-excel.de/tools.htm
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 14.15 einschl 64 Bit

Werner, bevor Du noch mehr Zeit und Arbeit in eine Formellösung investierst möchte ich nur nochmals erwähnen, dass ich bereits eine funktionierende VBA-Lösung habe. Wenngleich mich die Formellösung auch interessiert hätte.
Ich bedanke mich jedenfalls für Dein Interesse und wünsche Dir noch einen schönen Sonntag,
Servus Walter
Anzeige
AW: danke, hast Du richtig erkannt ...
21.07.2019 18:49:23
neopa
Hallo Walter,
... da war noch ein Fehler, den ich nicht bemerkt hatte. Untenstehend hab ich den korrigiert (ein zusätzliches "=" in Formel F2 und eine Teilformeländerung in Formel H2)
Das Du schon ein funktionierende Lösung hast, wusste ich. Ich hatte dewegen nachgefragt, weil mich interessiert, ob eine Formellösung für eine derartige Aufgabe noch vertretbar wäre/ist oder nicht (weil es in Summe in Deinem Original ja doch tausende Formeln werden könnten).
Wenn Dich (m)eine Formellösungen hierfür weiterhin interessieren sollten, dann hier nun eine korrigierte Fassung dessen:
Arbeitsblatt mit dem Namen 'Tab'
 ABCDEFGHIJKL
1IDVonBis  IDVonBis sort "ID"sort "Von"sort "Bis"
2C01.10.201801.10.2018  A10.10.201720.05.2018 A10.10.201731.12.2017
3A12.12.201720.05.2018  A05.06.201805.06.2018 A12.12.201718.02.2018
4A13.02.201818.02.2018  A10.08.201820.12.2018 A13.02.201820.05.2018
5C10.05.201810.06.2018  B15.03.201820.03.2018 A05.06.201805.06.2018
6A05.06.201805.06.2018  B15.05.201831.08.2018 A10.08.201820.12.2018
7A10.08.201820.12.2018  C03.05.201831.08.2018 B15.03.201820.03.2018
8B15.03.201820.03.2018  C01.10.201801.10.2018 B15.05.201831.08.2018
9C03.05.201815.05.2018  C11.10.201820.03.2019 C03.05.201815.05.2018
10C15.05.201831.08.2018      C10.05.201810.06.2018
11A10.10.201731.12.2017      C15.05.201831.08.2018
12C15.10.201820.03.2019      C01.10.201801.10.2018
13B15.05.201831.08.2018      C11.10.201811.11.2018
14C11.10.201811.11.2018      C15.10.201820.03.2019
15            

ZelleFormel
F2=WENN(F1="ID";J2;WENN(AGGREGAT(14;6;K$2:K$99/(J$2:J$99=F1);1)&gt=H1;F1;WENNFEHLER(INDEX(J:J;VERWEIS(9;1/(J$1:J$99=F1);ZEILE(A$2:A$99)))&"";"")))
G2=WENN(F2="";"";WENN(F2>F1;INDEX(K:K;VERGLEICH(F2;J:J;0));AGGREGAT(15;6;K$2:K$99/(K$2:K$99&gtH1)/(J$2:J$99=F2);1)))
H2=WENN(F2="";"";WENNFEHLER(AGGREGAT(15;6;L$2:L$99/(J$2:J$99=F2)/(L$2:L$99&gt=G2)/(L$2:L$99&ltK$3:K$20+(J$2:J$99>J$3:J$20)*9^5);1);MAX(L:L)))
J2=INDEX(A:A;1+VERGLEICH(AGGREGAT(14;6;ZÄHLENWENN(A$2:A$99;"&gt="&A$2:A$99);ZEILE(A1));INDEX(ZÄHLENWENN(A$2:A$99;"&gt="&A$2:A$99););0))&""
K2=WENN($J2="";"";AGGREGAT(15;6;B$2:B$99/($A$2:$A$99=$J2);ZÄHLENWENN($J$2:$J2;$J2)))
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
Danke Werner, ich werde berichten. Servus, Walter
21.07.2019 19:06:12
WalterK
AW: würde mich freuen owT
22.07.2019 09:22:53
neopa
Gruß Werner
.. , - ...
AW: würde mich freuen owT
22.07.2019 19:01:19
WalterK
Hallo Werner,
ich habe jetzt ein Beispiel mit 1000 Datensätzen erstellt. Siehe im Anhang.
https://www.herber.de/bbs/user/131029.xlsx
Ich habe die Formeln - so glaube ich jedenfalls - auf die 1000 Zeilen richtig umgestellt.
Besten Dank jedenfalls für Dein Interesse,
Servus, Walter
Anzeige
AW: hierzu nun ...
23.07.2019 09:42:11
neopa
Hallo Walter,
... Deine Datei hat mir nun gezeigt, dass ich in meiner Formel in Spalte H zwei Auswertungsbereiche noch falsch nur bis Zeile $20 zu stehen hatte und nicht wie sonst bis $99. Letztere hast Du für Dein Beispiel angepasst die erstere muss auch angepasst werden auf den auszuwertenden Datenbereich.
Dann waren meine Formeln (in F:H) so angelegt, dass diese im Auswertungsbereich mindestens 1 Zeile über den vorhandenen Datenbereich auswerten muss. In Deinem Datenbeispiel muss also in den Formeln nicht bis 1000 sondern bis mindestens 1001 ausgewertet werden.
Im Weiteren muss in der Formel in F2 doch nicht mit &gt=H1 sondern nur mit &gtH1 verglichen werden und
der Formel in Spalte H2 musste ich außerdem noch dem letzten Formelteil ... MAX(L:L) ein +1%% anfügen, um den "Spezialfall" gleicher Max-Datumswerte für eine ID bei der Ergebnisermittlung korrekt zu berücksichtigen.
Wenn das alles eingearbeitet ist, wird für Deine Beispieldatei bei mir auch eine mE korrekte Ergebnisliste bis Zeile 536 erstellt.
Die Rechenzeit (und darum ging es mir mit meiner Bitte für erstmalige Ergebniserstellung dauert natürlich einige Sekunden, bewegt sich aber mE noch im vertretbaren Grenzen, wenn man die Auswertung nicht häufig benötigt. Die Rechenzeit für Datenänderung einiger Datensätze fällt kaum auf.
Nochmal vielen Dank für Deine Zuarbeit und Dir einen schönen Tag.
Gruß Werner
.. , - ...
Anzeige

Beliebteste Forumthreads (12 Monate)

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