Office: SVERWEIS mit Zeitraum-Suche

Helfe beim Thema SVERWEIS mit Zeitraum-Suche in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; Hallo, ich suche nach einer Möglichkeit, wie man folgende Abfrage machen kann: Erste Liste enthält Spalte A: Personennummern (1000, 1001, 1005,... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von mobe99, 7. Juni 2010.

  1. mobe99 User

    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 :-)
     
    mobe99, 7. Juni 2010
    #1
  2. silex1
    silex1 Super-Moderator
    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))))
    <table><tr><td>Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></table>[/parsehtml]
     
    Zuletzt von einem Moderator bearbeitet: 30. November 2020
    silex1, 7. Juni 2010
    #2
  3. schatzi Super-Moderator
    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)
     
    Zuletzt von einem Moderator bearbeitet: 30. November 2020
    schatzi, 7. Juni 2010
    #3
  4. mobe99 User

    SVERWEIS mit Zeitraum-Suche

    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!
     
    mobe99, 7. Juni 2010
    #4
  5. mobe99 User
    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!
     
    mobe99, 8. Juni 2010
    #5
  6. mobe99 User
    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!
     
    mobe99, 8. Juni 2010
    #6
  7. mobe99 User
    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!
     
    mobe99, 8. Juni 2010
    #7
  8. schatzi Super-Moderator

    SVERWEIS mit Zeitraum-Suche

    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)
     
    Zuletzt von einem Moderator bearbeitet: 30. November 2020
    schatzi, 8. Juni 2010
    #8
  9. Exl121150 Erfahrener User
    Hallo,

    wenn ich mich einmischen darf:

    @mobe99:
    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.

    [parsehtml]
     BC
    205.02.201040214
    330.01.201040208
    46 
    <tr><td valign='top'>
    ZelleFormatWert
    B2TT.MM.JJJJ05.02.2010
    B3TT.MM.JJJJ30.01.2010
    </td><td valign='top'>
    ZelleFormel
    C2=B2
    C3=B3
    B4=B2-B3
    </td></tr></table>[/parsehtml]
     
    Zuletzt von einem Moderator bearbeitet: 30. November 2020
    Exl121150, 8. Juni 2010
    #9
  10. mobe99 User
    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
     
    mobe99, 9. Juni 2010
    #10
  11. schatzi Super-Moderator
    Hallo!

    Wenn deine Datümer vorher "Text" gewesen sein sollten, dann nützt dir die einfache Umformatierung nichts.
    Teste deine Werte mal mit der Funktion ISTZAHL. Sollte das Ergebnis FALSCH lauten, dann sind es nach wie vor Texte statt Zahlen.

    Abhilfe:
    Kopiere irgendeine leere Zelle in die Zwischenablage (Strg+c).
    Markiere deine Datümer, die eigentlich "Text" sind.
    Wähle Menü Start > Einfügen > Inhalte einfügen > Vorgang: Addieren > OK

    Nun sollten deine Datümer "echte" Zahlen sein.
     
    schatzi, 9. Juni 2010
    #11
  12. mobe99 User
    Schatzi - der Name ist Programm!

    Genau das war das Problem. Jetzt sind es endlich "echte Zahlen" und die Funktion von Dir kann ihren Dienst tun. Tut mir leid, dass ich das nicht wusste, dass man die Dinge nicht per "Zellen formatieren" umändern kann.

    Herzlichen Dank für die super (Nach)Hilfe von vorne bis hinten!
     
    mobe99, 9. Juni 2010
    #12
  13. Thema:

    SVERWEIS mit Zeitraum-Suche

    Die Seite wird geladen...
    1. SVERWEIS mit Zeitraum-Suche - Similar Threads - SVERWEIS Zeitraum Suche

    2. SVERWEIS

      in Microsoft Excel Hilfe
      SVERWEIS: Hej! bin ein absoluter Excel Anfänger und vermutlich wird hier nun über meine Frage geschmunzelt. Sicher findet sich trotzdem ein schlauer Kopf der mir helfen kann. Ich möchte die fehlende PNR...
    3. Bedingte Formatierung funktioniert nicht

      in Microsoft Excel Hilfe
      Bedingte Formatierung funktioniert nicht: Hallo, ich habe eine Excel Datei erstellt und es funktioniert alles, bis auf eine bedingte Formatierung. Ich verstehe einfach nicht wieso. Habe in der Liste schon andere Formatierungen, die sehr...
    4. Access mit Sverweis,???

      in Microsoft Access Hilfe
      Access mit Sverweis,???: =WENN(ISTNV(SVERWEIS(Eingang[@Vorgang];NrDrei2;2;0));"";SVERWEIS(Eingang[@Vorgang];NrDrei2;2;0)) Gesetz dem Falle mich versteht gerade jemand mit dem o. A. Anliegen!!! Ich möchte von Excel mal...
    5. Tabellenformatierung: Effekt auf Verweise

      in Microsoft Excel Hilfe
      Tabellenformatierung: Effekt auf Verweise: Hallo zusammen, ich habe eine Excel, in der sehr viele Produktdaten in einer Listenform gesammelt sind. Viele weitere Dateien sind über S- und X-Verweise mit dieser Produktliste verknüpft. Gerne...
    6. Sverweis mit zwei Intervallen

      in Microsoft Excel Hilfe
      Sverweis mit zwei Intervallen: Hello again, mein Vorsatz für nächstes Jahr ist auf jeden Fall eine Excel-Fortbildung um euch hier nicht immer wieder zu belästigen :oops::rolleyes: Ich steh (mal wieder) etwas auf dem Schlauch....
    7. Sverweis nach Zeitraum suche

      in Microsoft Excel Hilfe
      Sverweis nach Zeitraum suche: Hallo, Ich habe zurzeit das Problem, das ich nicht weiß, wie ich etwas aus einer Tabelle die aus der db kommt auslese. Die Tabelle hat ein Start und End Datum mit Uhrzeit. Beispiel A2 = 01.02.2022...
    8. SVERWEIS Formel für Übernahme von Zeiträume in anders Tabellenblatt

      in Microsoft Excel Hilfe
      SVERWEIS Formel für Übernahme von Zeiträume in anders Tabellenblatt: Hallo, ich melde mich heute noch mal. Habe schon im Forum gesucht aber noch nicht das passende gefunden und hoffe wieder auf Hilfe. Dann habe ich es geschafft und meine Tabelle steht. Nun zu...
    1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
      Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
      Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
      Information ausblenden