MS-Office-Forum
Google
   

Zurück   MS-Office-Forum > Microsoft Office > Microsoft Excel
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads Der Renner, 11 Entwicklertools für Access, Tipps & Trick und offene Datenbanken zum einzigartigen Preis.
Themen-Optionen Ansicht
Alt 11.01.2017, 09:32   #1
sammel
Neuer Benutzer
Neuer Benutzer
Standard Excel2010 - SVerweis mit Datumsvergleich?

Hallo,

ich bin schon ewig dabei zu recherchieren, finde aber nicht wirklich was mit meinem Problem und hoffe ihr könnt mir helfen.

Konkretes Beispiel:
- In Tabelle1 habe ich zwei Spalten (Personennummer, Anwesenheitsdatum)
- In Tabelle2 habe ich drei Spalten (Personennummer, Startdatum, Abteilung)

Nun möchte ich Tabelle1.Personennummer in Tabelle2.Personennummer suchen und dort wo die Personennummer übereinstimmt abgleichen wo das Tabelle1.Anwesenheitsdatum größer bzw. gleich dem Tabelle2.Startdatum ist und wenn dies gefunden worden ist, dann soll die Spalte Tabelle2.Abteilung übergeben werden.

Hinweis:
Bei der Prüfung wo Tabelle1.Anwesenheitsdatum größer bzw. gleich dem Feld Tabelle2.Startdatum ist, soll natürlich die Zeile gefunden werden, die am nächsten am Tabelle1.Anwesenheitsdatum liegt. Hierzu habe ich bereits in der Tabelle2, erst nach Personennummer und innerhalb der Personennummer das Startdatum(absteigend) sortiert. Somit sollte der erst gefundene Eintrag (wenn Anwesenheitsdatum größer bzw. gleich Startdatum) der richtige sein.

Hier mit ein paar Beispieldaten:
1.) Tabelle1
Personennummer | Anwesenheitsdatum
0815 | 01.10.2015 | Ergebnis: 6
0815 | 10.12.2016 | Ergebnis: 5
0815 | 11.01.2017 | Ergebnis: 7
0816 | 07.01.2017 | Ergebnis: 5
....
2.) Tabelle2
Personennummer | Startdatum | Abteilung
0815 | 01.01.2017 | 7
0815 | 01.11.2016 | 5
0815 | 01.01.2014 | 6
0816 | 01.01.2015 | 1
0816 | 31.05.2016 | 5
0818 | 01.01.2017 | 7
...

Besten Dank vorab.

Grüße
sammel ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.01.2017, 09:45   #2
Superingo2
MOF Koryphäe
MOF Koryphäe
Standard

Hallo .........,

wenn Du hier eine Beispieldatei hochlädst kann ich Dir viel einfacher und effektiver helfen. An und für sich ist das ein einfaches Problem.

LG Ingo

__________________

Viel Spaß


.....ein Feedback wäre nett.....
Superingo2 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.01.2017, 10:14   #3
sammel
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Hallo Ingo,

besten Dank vorab. Ich habe dir mal die Beispiele als Datei angehangen.

Grüße
Angehängte Dateien
Dateityp: xlsx Beispieldatei.xlsx (8,8 KB, 11x aufgerufen)
sammel ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.01.2017, 10:23   #4
EarlFred
MOF Guru
MOF Guru
Standard

Hallo,

Zitat:

Hierzu habe ich bereits in der Tabelle2, erst nach Personennummer und innerhalb der Personennummer das Startdatum(absteigend) sortiert.

Die Formel setzt das voraus, ist in Deinem Beispiel allerdings nicht durchgängig umgesetzt.

Tabelle1!C2:
Code:

{=INDEX(Tabelle2!C:C;MIN(WENN((A2=Tabelle2!$A$2:$A$7)*(Tabelle1!B2>=Tabelle2!$B$2:$B$7);ZEILE(Tabelle2!$B$2:$B$7))))}
Geschweifte Klammern nicht mit eingeben, sondern die Formeleingabe mit Strg+Shift+Enter abschließen (Matrixformel)

Bei sehr großen Datenmengen wird die Formel sicher langsam werden.

Grüße
EarlFred
EarlFred ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.01.2017, 10:38   #5
Superingo2
MOF Koryphäe
MOF Koryphäe
Standard

Hallo .....,

und hier eine Lösungsformel, falls die Daten in Tabelle2 nicht sortiert sein sollten (ansonsten reicht EarlFreds Formel!):

Tabelle1!C2:
PHP-Code:

{=INDEX(Tabelle2!$C:$C;REST(MAX(WENN((Tabelle2!$A$2:$A$7=$A2)*(Tabelle2!$B$2:$B$7<=$B2);Tabelle2!$B$2:$B$7+ZEILE($B$2:$B$7)/10^6));1)*10^6)} 
Achtung Matrixformel!!! Geschweifte Klammern nicht mit eintippen oder mitkopieren, sondern durch STRG+SHIFT+ENTER erzeugen. Das gilt bei jedem reinklicken in die Zellformel!

LG Ingo

__________________

Viel Spaß


.....ein Feedback wäre nett.....
Superingo2 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.01.2017, 13:38   #6
sammel
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Vielen Dank an euch, funktioniert bestens. Einziges Manko (bedingt durch meine Datei) ist, dass die Berechnung meiner ganzen Zeilen natürlich immens lange brauch.

Ich habe in Tabelle1 7140 Zeilen und in Tabelle2 325 Zeilen. Dazu noch viele andere Tabellenblätter.

Berechnungen der anderen Tabellenblätter brauch einiges an Zeit. Aber die Berechnung mit Hilfe der Indexformel macht die Datei etwas zäh. Aber gut, ist nun mal so, wenn ich den Bereich in Teilbereiche prüfen und darin noch einmal eine Datumsprüfung vornehme.

Aber ich werde dies einmal berechnen lassen (Start Datei) und das Ergebnis über "Zellen einfügen" einfügen. Dann wird bei jeder Filterung nicht wieder neu berechnet und die Datei wird in alter Geschwindigkeit bearbeitet. Die Formel kopiere ich mir separat als Kommentar und kann sie bei Bedarf einfügen.

Aber die Formel an sich funktioniert super. Vielen Dank.
sammel ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.01.2017, 14:09   #7
lupo1
MOF Koryphäe
MOF Koryphäe
Standard

Tabelle1!E2: =A2+B2%%% entlangkopiert
Tabelle2!D2: =A2+B2%%% entlangkopiert

Tabelle1!C2: =INDEX(Tabelle2!C$1:C$999;VERGLEICH(E2;Tabelle2!D$1:D$999)) entlangkopiert

Voraussetzungen:
- Beide Tabellen sind sortiert nach 1. A:A 2. B:B
- keine Abneigung gegen die beiden Hilfsspalten

Gewinn:
- Zeitersparnis um Größenordnungen
- keine Matrixformeln

__________________

MfG Lupo - 11.10.2012-> {MSO 2000 SP3;Win 7} - 28.02.2013-> {MSO 2010;Win 8.1} (beide vorhanden)
lupo1 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.01.2017, 14:38   #8
Frank Furter
MOF Koryphäe
MOF Koryphäe
Standard

hallo, mit AGGREGAT() bekomme ich die gleichen ergebnisse wie EarlFred, zu SuperIngos habe ich eine abweichung... ... aber auch zu einer deiner vorgaben..:

=INDEX($L$2:$L$7;AGGREGAT(15;6;ZEILE(Tabelle2!$C$2:$C$7)-1/(Tabelle2!$A$2:$A$7=A2)/(Tabelle2!$B$2:$B$7<=B2);1))

__________________

gruß vom Frank Furter mit hiob's botschaften
Frank Furter ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.01.2017, 14:40   #9
Superingo2
MOF Koryphäe
MOF Koryphäe
Standard

Hallo ........,

also bei mir braucht die Berechnung von 7140 Formeln (meine Langversion) deutlich unter 1 Sekunde, wenn ich den Berechnungsbereich auf die 325 Zeilen beschränke. Dein Problem muss dann wohl in den "fremden" Datenblättern liegen oder aber in einem extrem langsamen Rechner aus den 90ern.

LG Ingo

__________________

Viel Spaß


.....ein Feedback wäre nett.....
Superingo2 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.01.2017, 15:00   #10
Superingo2
MOF Koryphäe
MOF Koryphäe
Standard

Hallo Frank,

oder darf ich Mister Aggregat sagen?
Deine Formel hat das Problem, nicht mit unsortierten Datensätzen klar zu kommen. Daher die Abweichung zu mir.
Aber ich habe Deine Formel mal mit der Meinigen gekreuzt:
PHP-Code:

=INDEX(Tabelle2!$C:$C;REST(AGGREGAT(14;6;(Tabelle2!$B$2:$B$325+ZEILE($B$2:$B$325)/10^6)/(Tabelle2!$A$2:$A$325=A2)/(Tabelle2!$B$2:$B$325<=B2);1);1)*10^6
Der Vorteil ist, dass ich nun ohne die Matrixformel auskomme.
Bei der Berechnungszeit kann ich keine Unterschiede feststellen (bei 50.000 Formeln brauche ich beidemal eine gute Sekunde).

LG Ingo

__________________

Viel Spaß


.....ein Feedback wäre nett.....
Superingo2 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.01.2017, 16:44   #11
Frank Furter
MOF Koryphäe
MOF Koryphäe
Standard kompliment... !

hallo Ingo, da mir aber deine kreuzung ist mir nindestens 10^6 zu hoch ist, würde ich so tun ..:

=INDEX(Tabelle2!$C$2:$C$7;VERGLEICH(AGGREGAT(14;6;Tabelle2!$B$2:$B$7/(Tabelle2!$B$2:$B$7<=B2)/(Tabelle2!$A$2:$A$7=A2);1);Tabelle2!$B$2:$B$7);0)

__________________

gruß vom Frank Furter mit hiob's botschaften
Frank Furter ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 12.01.2017, 10:03   #12
Superingo2
MOF Koryphäe
MOF Koryphäe
Standard

Hallo Frank,

schade, dass Dir 10^6 zu hoch sind
Denn bei Deiner neuer Formel hast Du nun noch viel mehr Probleme. Lass die 818 in Zelle B7 mal am 31.05.2016 anfangen. Nun schau Dir Dein Formelergebnis in C3 und C5 an.

Liebe Grüße
Ingo

__________________

Viel Spaß


.....ein Feedback wäre nett.....
Superingo2 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


Alle Zeitangaben in WEZ +1. Es ist jetzt 11:42 Uhr.


Partner und Co.
Access-Paradies -Alles rund um die Datenbank Microsoft Access -Code -Programme-Tools -Tipps   Kostenlose Tipps & Tricks, Downloads und Programme   www.kulpa-online.com - Tipps - Tricks - Tutorials - Meinungen - Downloads uvm...   vb@rchiv · Willkommen in der Welt der VB Programmierung   Access-Garhammer - Hier finden Sie jede Menge Beispiel-Datenbanken zu Access und mehr ...   mcseboard.de   Die Top Seite für Excel-VBA-Makros uvm.

Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

Copyright ©2000-2010 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günther Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.
Beachten Sie bitte auch unsere Nutzungsbedingungen.