Seite 1 von 2 1 2 LetzteLetzte
Ergebnis 1 bis 10 von 12
  1. #1
    Office-Hilfe.com - Neuling
    Registriert seit
    07.06.2010
    Beiträge
    8

    SVERWEIS mit Zeitraum-Suche

    Hallo,
    ich suche nach einer Möglichkeit, wie man folgende Abfrage machen kann:

    Erste Liste enthält
    Spalte A: Personennummern (1000, 1001, 1005, 1010 usw.).
    Spalte B: Startdatum Aufenthalt (z.B. 1.1.2010)
    Spalte C: Endedatum Aufenthalt (z.B. 5.1.2010)
    Spalte D: Status während des Aufenthalts (z.B. X, Y, Z)


    Zweite Liste enthält
    Spalte A: Personennummern
    Spalte B: Datum
    Nun soll in der Spalte C ausgegeben werden, welchen Status die Person zu dem Datum von Spalte B hatte.

    Beispiel:
    In der ersten Liste steht
    Person 1000 hatte Aufenthalt vom 1.1.2010 bis 5.1.2010 mit Status X
    Person 1000 hatte Aufenthalt vom 17.1.2010 bis 20.1.2010 mit Status Z

    Das Problem ist also, dass eine Person zu unterschiedlichen Zeiträumen da war und einen unterschiedlichen Status hatte.

    In der zweiten Liste steht
    Spalte A: Person 1000,
    Spalte B: Datum 19.1.,
    Spalte C: hier soll stehen Status Z.

    Ich dachte, dass ich mit einer kombinierten SVERWEIS (für die Personennummern) und WENN Funktion (für den Datumszeitraum) arbeiten kann, kriege das aber einfach nicht hin....

    Hat jemand eine Idee? Vielen lieben Dank für jede Hilfe :-)

  2. #2
    Super-Moderator Avatar von silex1
    Registriert seit
    18.12.2007
    Ort
    dort wo ich geduldet werde
    Beiträge
    1.281
    Hallo,

    (m)ein Vorschlag:

    Leerzeile nach der Überschrift einfügen (wg. Fehlerabfang!) und dann könnte dies funktionieren:

    *ABCDEFGH
    1Nr.vonbisStatus*Nr.DatumStatus
    2***Fehler*101010.01.2010Fehler
    3100001.01.201021.01.2010a****
    4100102.01.201022.01.2010b****
    5100203.01.201023.01.2010c****
    6100304.01.201024.01.2010d****
    7100405.01.201025.01.2010e****
    8100506.01.201026.01.2010f****
    9100607.01.201027.01.2010g****
    10100708.01.201028.01.2010h****
    11100809.01.201029.01.2010i****
    12100910.01.201030.01.2010j****
    13101011.01.201031.01.2010k****
    14101112.01.201001.02.2010l****
    15101213.01.201002.02.2010m****
    16101314.01.201003.02.2010n****
    17101415.01.201004.02.2010o****
    18101516.01.201005.02.2010p****
    19101617.01.201006.02.2010q****
    20101718.01.201007.02.2010r****

    ZelleFormel
    H2=INDEX(D:D;SUMMENPRODUKT(($A$3:$A$22=F2)*($B$3:$B$22<=G2)*($C$3:$C$22>=G2)*(ZEILE($A$3:$B$22))))
    Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
    LG, René der "User mit gefährlichem Halbwissen"
    "Was nutzt der Baum der Erkenntnis, wenn das Blatt der Erfahrung fehlt!"

  3. #3
    Hallo!

    Teste mal:

    ABCDEFGH
    1NrStartEndeStatus NrDatumStatus
    2100001.01.201005.01.2010X 100019.01.2010Z
    3100102.02.201003.03.2010Y
    4100504.04.201005.05.2010Y
    5101006.06.201007.07.2010Y
    6100017.01.201020.01.2010Z

    ZelleFormel
    H2=VERWEIS(2;1/((A2:A99=F2)*(B2:B99<=G2)*(C2:C99>=G2));D2:D99)

    Viele Grüße vom Schatzi

    ------------------------
    Dir wurde geholfen? Dann gib doch bitte ein Feedback! Du hilfst damit automatisch anderen Usern, die ein ähnliches Problem haben!

    Ich arbeite mit: Excel 2010, Windows 7
    Ein Tool, um Tabellen(ausschnitte) in diesem Forum posten zu können, findet ihr z.B.
    hier
    .

  4. #4
    Office-Hilfe.com - Neuling
    Registriert seit
    07.06.2010
    Beiträge
    8
    Wow, super! Beide Ansätze funktionieren fabelhaft! Herzlichen Dank silex 1 und schatzi für Eure Hilfe (und das noch so spät am Abend!)
    Gute Nacht!

  5. #5
    Office-Hilfe.com - Neuling
    Registriert seit
    07.06.2010
    Beiträge
    8
    Hallo,
    bin ganz traurig. Es funktioniert leider doch noch nicht immer...
    Nr Start Ende Status Nr Datum Status
    1000 01.01.2010 15.01.2010 a 1000 02.01.2010 a
    1001 05.01.2010 10.01.2010 b 1001 10.01.2010 b
    1005 02.01.2010 03.01.2010 b 1000 05.01.2010 #NV
    1000 17.01.2010 20.01.2010 b 1001 12.01.2010 c
    1001 12.01.2010 12.01.2010 c 1000 10.01.2010 #NV

    Ausgehend von der Formel von Schatzi funktioniert es immer dann nicht, wenn ich zum zweiten oder öfteren Mal in einem Datumszeitraum suche. Beispielsweise habe ich für Nr. 1000 einmal den 2.1.2010 und einmal den 5.1.2010 abgefragt (beide Datum im Zeitraum 1.1. bis 15.1.2010). Bei der zweiten oder öfteren Nachfrage kommt ein NV. Kann man das noch irgendwie beheben? Herzlichen Dank für jede Hilfe!

  6. #6
    Office-Hilfe.com - Neuling
    Registriert seit
    07.06.2010
    Beiträge
    8
    Ich bins nochmal. Hatte keinen festen Zellenbezug per $ gesetzt (*schäm*). Die Frage hat sich damit erledigt. Danke an alle, die evtl. über der Lösung des Problems gegrübelt hatten!

  7. #7
    Office-Hilfe.com - Neuling
    Registriert seit
    07.06.2010
    Beiträge
    8
    Hallo nochmal,

    wie kann dich der Funktion von Schatzi noch beibringen, dass das Datum 05.02.2010 "größer" ist als 30.01.2010, so dass die Abfrage funktioniert?

    Bisher arbeitet die Abfrage sehr gut bei allen Start- und Endedaten, wo die Startdatumzahl kleiner ist als die Endedatumzahl. Es funktioniert aber nicht, wenn wie im Beispiel oben die Endedatumzahl (hier: "05...") kleiner ist als die Startdatumzahl (hier: "31...") Vielen lieben Dank für Eure Hinweise!

  8. #8
    Hallo!

    Warum sollte das nicht funktionieren??

    ABCDEFGH
    1NrStartEndeStatus NrDatumStatus
    2100001.01.201005.01.2010X 100002.02.2010Z
    3100102.02.201003.03.2010Y
    4100504.04.201005.05.2010Y
    5101006.06.201007.07.2010Y
    6100031.01.201005.02.2010Z

    ZelleFormel
    H2=VERWEIS(2;1/((A2:A99=F2)*(B2:B99<=G2)*(C2:C99>=G2));D2:D99)

    Viele Grüße vom Schatzi

    ------------------------
    Dir wurde geholfen? Dann gib doch bitte ein Feedback! Du hilfst damit automatisch anderen Usern, die ein ähnliches Problem haben!

    Ich arbeite mit: Excel 2010, Windows 7
    Ein Tool, um Tabellen(ausschnitte) in diesem Forum posten zu können, findet ihr z.B.
    hier
    .

  9. #9
    Office-Hilfe.com - Profi
    Registriert seit
    24.11.2008
    Beiträge
    1.909
    Hallo,

    wenn ich mich einmischen darf:

    @mobe99:
    wie kann dich der Funktion von Schatzi noch beibringen, dass das Datum 05.02.2010 "größer" ist als 30.01.2010
    Du musst ein Datum als Datum eingeben und nicht als Text.
    Wie dem folgenden Beispiel zu entnehmen ist, müssen die Inhalte der beiden Zellen B2 und C2 gleich sein, denn in Zelle C2 steht ja die Formel =B2. Ferner muss in den beiden Zellen B3 und C3 aus demselben Grund auch der gleiche Wert stehen - nur die Zellformatierung des angezeigten Wertes ist in Spalte B die Datumsformatierung TT.MM.JJJJ , während in Spalte C die Standardzahlenformatierung enthalten ist.
    Wie unschwer zu erkennen ist, ist der Wert in Zelle C2 größer als der in Zelle C3, sodass folglich auch das Datum in B2 größer sein muss als jenes in B3. Aus diesem Grund ist auch die Datumsdifferenz in Zelle B4 gleich 6 , weil genau 6 Tage Differenz zwischen beiden Datumsangaben liegen, was sich genauso aufgrund der beiden Zellwerte in C2 und C3 ergeben würde.

    Ich würde dringend raten, grundlegendes Excel-Wissen aufzufrischen, was die Datumsarithmetik bzw. die Datumsspeicherung betrifft.

    BC
    205.02.201040214
    330.01.201040208
    46
    ZelleFormatWert
    B2TT.MM.JJJJ05.02.2010
    B3TT.MM.JJJJ30.01.2010
    ZelleFormel
    C2=B2
    C3=B3
    B4=B2-B3
    Liebe Grüße
    Anton Exl

    Windows 10 (x64)
    Office 2016 Professional Plus

  10. #10
    Office-Hilfe.com - Neuling
    Registriert seit
    07.06.2010
    Beiträge
    8
    Hallo Exl121150,
    danke fürs einmischen, bin für jeden Tipp dankbar :-) Das mit dem Datum ist ein guter Hinweis, daran hatte ich auch schon gedacht. Die betreffenden Spalten (B, C und G) habe ich auch als Datum formatiert (unter "Zellen formatieren", Auswahl: Datum). Aber trotzdem erscheint folgendes:

    A B C D E F G H
    Nr Status Start Ende Nr Datum Status
    1111 a 28.03.2009 28.03.2009 1111 28.03.2009 #NV
    1111 b 07.08.2008 01.01.2009 1111 07.08.2008 #NV
    2222 a 07.01.2009 07.01.2009 2222 07.01.2009 a
    3333 b 09.01.2009 09.01.2009 3333 09.01.2009 b
    4444 b 25.02.2009 25.02.2009 4444 25.02.2009 b
    5555 b 11.10.2008 12.02.2009 5555 11.10.2008 b
    6666 d 29.01.2009 29.01.2009 6666 29.01.2009 d
    7777 e 27.09.2008 17.01.2009 7777 27.09.2008 #NV
    7777 a 17.01.2009 10.02.2009 7777 17.01.2009 #NV
    8888 b 19.01.2009 28.01.2009 8888 19.01.2009 b
    9999 b 21.01.2009 21.01.2009 9999 21.01.2009 b
    10000 a 26.08.2009 15.12.2009 10000 26.08.2009 #NV
    100000 a 25.01.2009 04.05.2009 100000 25.01.2009 #NV


    Was könnte ich noch probieren bzw. was muss ich anders machen?
    Danke und beste Grüße

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •  
Excel Ticker - News, Tipps und Tricks zu Microsoft Excel | SMS kostenlos versenden | Forenuser - Die Foren Findmaschine