Neue Einträge |
|
|
von chiseil86 vom 09.06.2017, 10:44
|
|
|
Excel: zwei Spalten nach Datum sortieren (723 Hits)
Hallo in die Runde,
ich habe eine Tabelle, die mich schier wahnsinnig macht ;-) und hoffe, dass mir jemand dabei behilflich sein kann.
Ich habe eine Tabelle, die grundlegend folgendermaßen aufgebaut ist:
Name | Geburtsdatum (TT MM YYYY) | Todesdatum (TT MM YYYY)
Nun möchte ich, dass die Tabelle so sortiert ist, dass mir an jedem Tag des Jahres angezeigt ist, wer geboren und wer gestorben ist, also was ist zum Beispiel alles am 01.01. passiert, was am 02.01. und so weiter (Wer ist am 01.01. geboren und dann wer ist am 01.01. gestorben, dann wer ist am 02.01. geboren und dann wer ist am 02.01. gestorben) Dabei gibt es noch folgende zwei Hürden: - Es sind nicht alle Daten für jede Zeile vorhanden, bei manchen Personen ist das Geburtsdatum nicht bekannt, andere leben beispielsweise noch. Deswegen kommt eine Pivot-Tabelle nicht in Frage (das Gruppieren ist nicht möglich), die eigentlich ein gutes Mittel wäre. - Die Daten liegen zu einem großen Teil vor 1900, ich musste also das Add-Ins "xdate" einsetzen.
Hat jemand eine Idee, wie ich das lösen könnte?
Viele Grüße chriseil
|
|
|
Antwort 1 von Nighty__ vom 09.06.2017, 13:35
|
|
|
Hallo chriseil .-)
Deine Tools und die Option 1904 sind überflüssig!
Gruss Nighty
Autofilter in Wechselwirkung! Spale A ab Zeile 2 Datumswerte
Die letzten beiden Spalten dienen als HilfsSpalte!
Sub DatumsSuche()
Dim Qarr As Variant, Darr As Variant, Marr As Variant
Dim ZeilenZähler As Long
If ActiveSheet.AutoFilterMode = False Then
Qarr = Range("A2:A" & ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row)
Darr = Range("A2:A" & ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row)
Marr = Range("A2:A" & ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row)
For ZeilenZähler = 1 To UBound(Qarr)
Darr(ZeilenZähler, 1) = Day(Qarr(ZeilenZähler, 1))
Marr(ZeilenZähler, 1) = Month(Qarr(ZeilenZähler, 1))
Next ZeilenZähler
Range(Cells(2, Columns.Count - 1), Cells(UBound(Qarr) + 1, Columns.Count - 1)) = Darr
Range(Cells(2, Columns.Count), Cells(UBound(Qarr) + 1, Columns.Count)) = Marr
ActiveSheet.Range(Cells(1, Columns.Count - 1), Cells(1, Columns.Count)).AutoFilter
ActiveSheet.Cells(1, Columns.Count - 1).AutoFilter Field:=1, Criteria1:=Day(Date)
ActiveSheet.Cells(1, Columns.Count).AutoFilter Field:=2, Criteria1:=Month(Date)
Else
ActiveSheet.Range(Cells(1, Columns.Count - 1), Cells(1, Columns.Count)).AutoFilter
Columns(Columns.Count - 1).Clear
Columns(Columns.Count).Clear
End If
End Sub
|
|
Antwort 2 von paul_1 vom 09.06.2017, 18:02
|
|
|
Hallo,
hier wäre eine Datenbanklösung z.B. Access (Abfrage-Filterkriterien) ein echte Alternative
Gruß Paul1
|
|
Antwort 3 von Nighty__ vom 10.06.2017, 11:58
|
|
|
Hallo Paul1 :-)
Normalerweise geht das auch nicht in Excel 1.1.1701 und 1.1.2017 gemischt zu bearbeiten .-) Day und Month von den Formeln unterscheiden sich sehr zu Day und Month von Vba! Die alten Werte werden linksbündig zugeordnet(Stringformat),diese könnten aber rechtsbündig unter Format ausgerichtet werden.
Gruss Nighty
|
|
Antwort 4 von paul_1 vom 10.06.2017, 12:56
|
|
|
Hallo Nighty,
Was Excel betrifft hast Du sicher recht Ich habe mich noch nicht näher beschäftigt, da ich nicht weiß ob dem Fragesteller überhaupt Access zur Verfügung steht. manuell die beiden Filter zu setzen geht jedenfalls.
schöne Grüße Paul1
|
|
Antwort 5 von Nighty__ vom 10.06.2017, 13:25
|
|
|
Hallo chriseil und Paul1 .-)
Mit 2 Spalten kommt die Tage! Zum Testen war es erstmal eine Spalte .-) Bei 2 Spalten haben wir dann 4 Hilfsspalten,ohne Hilfsspalten habe ich noch keine Idee da das 1904 Problem immer auf der Lauer liegt .-)
Gruss Nighty
|
|
Antwort 6 von Nighty__ vom 11.06.2017, 13:07
|
|
|
Hallo Paul1 .-)
Ich schliesse mih dich erstmal an,ob sich der Fragesteller wieder meldet!
Obiges Beispiel reicht für die Datenbank
Gruß Nighty
|
|
Antwort 7 von Nighty__ vom 11.06.2017, 14:41
|
|
|
Hallo Community .-)
Noch für Interessierte,eine Function die Tag und Monat beliebiger Datumswerte ermittelt! Ohne 1904 Option!
Gruss Nighty
Zelle=Range Modus=0=Tag Modus=1=Monat
Function DMDate(Zelle As Range, Modus As Boolean) As Integer
Dim DArr As Variant, MArr As Variant
If Modus = False Then
DArr = Zelle
DMDate = Day(DArr)
Else
MArr = Zelle
DMDate = Month(MArr)
End If
End Function
|
|
Antwort 8 von chriseil86 vom 12.06.2017, 15:24
|
|
|
Lieber Nighty_,
erst einmal ganz herzlichen Dank für deine Hilfe!!! Ich habe den Code am Wochenende ausprobiert, leider hat es nicht das gewünschte Ergebnis gebracht (siehe Fehlermeldung https://www.dropbox.com/s/ewju0caqd51vlsi/Fehlermeldung_Excel_Geburtstage.JPG?dl=0 ). Dann habe ich das ganze mal als Datenbank mit Access probiert, aber auch das hat nicht hingehauen und mich mal wieder an den Rand des Wahnsinns gebracht. Bin aber weder ein Excel- und noch viel weniger ein Access-Profi ;-)
Das xdate-Add-Ins habe ich verwendet, weil mir meine Tabelle als "immerwährender Kalender" alle runden und halbrunden Geburtstage errechnen soll. (einen Screenshot der Tabelle: https://www.dropbox.com/s/ewju0caqd51vlsi/Fehlermeldung_Excel_Geburtstage.JPG?dl=0 ). Die Erklärunghatte ich anfangs der Einfachheit halber weggelassen, weil ich das Sortierproblem erst einmal im Blick hatte ;-) ...
Viele Grüße chriseil
|
|
Antwort 9 von Nighty__ vom 12.06.2017, 15:53
|
|
|
Hallo Chriseil .-)
Es musste ein Fehler kommen,da ich im ersten Test das Datum in Spalte A hatte :-) Ich erstell das die Tage auf Spalte B+C
Gruss Nighty
|
|
Antwort 10 von chriseil86 vom 12.06.2017, 17:59
|
|
|
Lieber Nighty,
oh man, da bin ich ja aber erstmal beruhigt, dass es nicht an mir lag ;-)))
Viele Grüße Chriseil
|
|
Antwort 11 von paul_1 vom 12.06.2017, 20:02
|
|
|
Hallo Chriseil,
mit Access kann ich es mir so vorstellen:
Tabelle: Feld 1 Name> Felddatentyp Text Feld 2 geboren> Felddatentyp Datum/Uhrzeit Feld 3 gestorben> Felddatentyp Datum/Uhrzeit
erstelle eine Abfrage aus obiger Tabelle:
in der Entwurfsansicht unter Kriterien gib folgende Verknüpfung ein:
Name Wie "*" & [SUCHE] & "*"
ein Zeile darunter geboren: Wie "*" & [SUCHE] & "*"
und wieder eine Zeile darunter Wie "*" & [SUCHE] & "*"
Beim Öffnen der Abfrage wird der Parameterwert (Datum oder auch Namen) verlangt, es erscheinen dann alle gestorbenen und geborenen Personen aufgelistet.
Gruß Paul1
|
|
Antwort 12 von chriseil86 vom 17.06.2017, 13:14
|
|
|
Lieber paul1,
ich habe wieder stundenlang probiert, aber es will einfach nicht :-/ Und ich muss sagen, dass eine Datenbanklösung eigentlich auch für mich nicht so ganz praktikabel ist, denn wie in diesem Screenshot zu sehen https://www.dropbox.com/s/0uvatk1zpwi2u85/ScreenshotTabelle_Kalende..., liegt der Sinn der Tabelle für mich darin, zu sehen, welche runden und halbrunden Geburtstage bzw. Todestage zum jeweiligen Datum anstehen. In Access funktionierte diese Formel nicht ...
Vielleicht kann Nighty ja mit der Excel-Lösung nochmal weiterhelfen :-)
Hätte ich ja nie gedacht, dass das mit der Tabelle so kompliziert werden könnte ...
Viele Grüße chriseil
|
|
Antwort 13 von Nighty__ vom 17.06.2017, 14:10
|
|
|
Hallo Community und chriseil .-)
Ok ... Wollt nur abwarten wie sich die Dinge entwickeln .-)
Der Auto Filter wird übrigens entlassen oder besser noch ,wir begraben Ihn unter einer Pyramide *g*
Mit 2 Datumsspalten zickt der nur rum!
Ich mach es nun über ein Array ,übers Wochenende Fertig !
Die Problematik war die Wechselwirkung der Filterung bei 2 Spalten,nicht die leeren Zellen
Gruss Nighty
|
|
Antwort 14 von Nighty__ vom 17.06.2017, 17:46
|
|
|
Hallo Community .-)
Gültiges Datum,Jahr 0 bis Heute Aktuelles Tagesdatum mit Aktuellen Monatsdatum mit Jahr X werden angezeigt Zwar auch mit Autofilter,aber die Daten sind aufbereitet! 1 Hilfsspalte wird genutzt und ist immer die letzte Spalte Genutzte Bereich wird als Datum Formatiert nach DD MM JJJJ Genutzte Bereich wird rechtsbündig Formatiert
Gruß Nighty
Sub DatumFilter()
Dim DatenDatum As Variant
Dim ZeilenIndex As Long
If ActiveSheet.AutoFilterMode = False Then
Range(Cells(2, 1), Cells(ActiveSheet.Range(Cells(Rows.Count, 1), Cells(Rows.Count, 1)).End(xlUp).Row, 2)).NumberFormat = "dd/mm/yyyy"
Range(Cells(2, 1), Cells(ActiveSheet.Range(Cells(Rows.Count, 1), Cells(Rows.Count, 1)).End(xlUp).Row, 2)).HorizontalAlignment = xlRight
DatenDatum = Range(Cells(2, 1), Cells(ActiveSheet.Range(Cells(Rows.Count, 1), Cells(Rows.Count, 1)).End(xlUp).Row, 2))
For ZeilenIndex = 1 To UBound(DatenDatum, 1)
If Day(Date) = Day(DatenDatum(ZeilenIndex, 1)) And Month(Date) = Month(DatenDatum(ZeilenIndex, 1)) Or Day(Date) = Day(DatenDatum(ZeilenIndex, 2)) And Month(Date) = Month(DatenDatum(ZeilenIndex, 2)) Then Cells(ZeilenIndex + 1, Columns.Count) = True
Next ZeilenIndex
ActiveSheet.Range(Cells(1, Columns.Count), Cells(ActiveSheet.Range(Cells(Rows.Count, 1), Cells(Rows.Count, 1)).End(xlUp).Row, Columns.Count)).AutoFilter Field:=1, Criteria1:=True
Else
ActiveSheet.Cells(1, Columns.Count).AutoFilter
Columns(Columns.Count).Clear
End If
End Sub
|
|
Antwort 15 von Nighty__ vom 17.06.2017, 17:48
|
|
|
Hallo Community .-)
Das Makro arbeitet in Wechselwirkung bzw wiederholter Start schaltet den Filter wieder aus
Gruss Night
|
|
Antwort 16 von Nighty__ vom 17.06.2017, 18:26
|
|
|
Hallo Commuity .-)
Sollte die Anzahl der Datumswerte 5-6 stellig sein,könnte ich es noch beschleunigen!
Gruß Nighty
|
|
Antwort 17 von Nighty__ vom 18.06.2017, 08:01
|
|
|
Hallo Community
Bisl beschleunigt!
Spalte A + B sind Datumswerte
Gruss Nighty
Sub DatumFilter()
Dim DatenDatum As Variant, Puffer As Variant
Dim ZeilenIndex As Long
If ActiveSheet.AutoFilterMode = False Then
Range(Cells(2, 1), Cells(ActiveSheet.Range(Cells(Rows.Count, 1), Cells(Rows.Count, 1)).End(xlUp).Row, 2)).NumberFormat = "dd/mm/yyyy"
Range(Cells(2, 1), Cells(ActiveSheet.Range(Cells(Rows.Count, 1), Cells(Rows.Count, 1)).End(xlUp).Row, 2)).HorizontalAlignment = xlRight
DatenDatum = Range(Cells(2, 1), Cells(ActiveSheet.Range(Cells(Rows.Count, 1), Cells(Rows.Count, 1)).End(xlUp).Row, 2))
Puffer = Range(Cells(2, 1), Cells(ActiveSheet.Range(Cells(Rows.Count, 1), Cells(Rows.Count, 1)).End(xlUp).Row, 1))
For ZeilenIndex = 1 To UBound(DatenDatum, 1)
If Day(Date) = Day(DatenDatum(ZeilenIndex, 1)) And Month(Date) = Month(DatenDatum(ZeilenIndex, 1)) Or Day(Date) = Day(DatenDatum(ZeilenIndex, 2)) And Month(Date) = Month(DatenDatum(ZeilenIndex, 2)) Then Puffer(ZeilenIndex, 1) = True
Next ZeilenIndex
Range(Cells(2, Columns.Count), Cells(ActiveSheet.Range(Cells(Rows.Count, 1), Cells(Rows.Count, 1)).End(xlUp).Row, Columns.Count)) = Puffer
ActiveSheet.Range(Cells(1, Columns.Count), Cells(ActiveSheet.Range(Cells(Rows.Count, 1), Cells(Rows.Count, 1)).End(xlUp).Row, Columns.Count)).AutoFilter Field:=1, Criteria1:=True
Else
ActiveSheet.Cells(1, Columns.Count).AutoFilter
Columns(Columns.Count).Clear
End If
End Sub
|
|
Antwort 18 von chriseil86 vom 22.06.2017, 22:30
|
|
|
Lieber Nighty,
ich bekomme es einfach nicht hin, irgendetwas scheine ich falsch zu machen :-(
Ob du mir mal eine Beispieltabelle hochladen könntest?
Viele Grüße chriseil
|
|
Antwort 19 von Nighty__ vom 24.06.2017, 14:46
|
|
|
Hallo chriseil .-)
Datumswerte sind nun Spalte B+C,hatte ich wohl vergessen
Einzufügen Alt+F11/Projektexplorer/Allgemeines Modul
Gruß Nighty
Sub DatumFilter()
Dim DatenDatum As Variant, Puffer As Variant
Dim ZeilenIndex As Long
If ActiveSheet.AutoFilterMode = False Then
Range(Cells(2, 2), Cells(ActiveSheet.Range(Cells(Rows.Count, 2), Cells(Rows.Count, 2)).End(xlUp).Row, 3)).NumberFormat = "dd/mm/yyyy"
Range(Cells(2, 2), Cells(ActiveSheet.Range(Cells(Rows.Count, 2), Cells(Rows.Count, 2)).End(xlUp).Row, 3)).HorizontalAlignment = xlRight
DatenDatum = Range(Cells(2, 2), Cells(ActiveSheet.Range(Cells(Rows.Count, 2), Cells(Rows.Count, 2)).End(xlUp).Row, 3))
Puffer = Range(Cells(2, 2), Cells(ActiveSheet.Range(Cells(Rows.Count, 2), Cells(Rows.Count, 2)).End(xlUp).Row, 3))
For ZeilenIndex = 1 To UBound(DatenDatum, 1)
If Day(Date) = Day(DatenDatum(ZeilenIndex, 1)) And Month(Date) = Month(DatenDatum(ZeilenIndex, 1)) Or Day(Date) = Day(DatenDatum(ZeilenIndex, 2)) And Month(Date) = Month(DatenDatum(ZeilenIndex, 2)) Then Puffer(ZeilenIndex, 1) = True
Next ZeilenIndex
Range(Cells(2, Columns.Count), Cells(ActiveSheet.Range(Cells(Rows.Count, 2), Cells(Rows.Count, 2)).End(xlUp).Row, Columns.Count)) = Puffer
ActiveSheet.Range(Cells(1, Columns.Count), Cells(ActiveSheet.Range(Cells(Rows.Count, 2), Cells(Rows.Count, 2)).End(xlUp).Row, Columns.Count)).AutoFilter Field:=1, Criteria1:=True
Else
ActiveSheet.Cells(1, Columns.Count).AutoFilter
Columns(Columns.Count).Clear
End If
End Sub
|
|
|
Diese Frage ist schon etwas älter, Sie können daher nicht mehr auf sie antworten. Sollte Ihre Frage noch nicht gelöst sein, stellen Sie einfach eine neue Frage im Forum.
|
|
MACHEN SIE IHRE WEBSITE ATTRAKTIVER |
Sie haben eine eigene Website und wollen Ihre Besucher auf den Supportnet-Service aufmerksam machen?
Kopieren Sie einfach den Quellcode in Ihre Seite und jeder Besucher Ihrer Seite kann direkt auf die
Supportnet-Datenbank zugreifen.
|
|
|
Gruppen im Forum |
Betriebsysteme
Software
Hardware
Netzwerk
Programmierung
Sonstiges
|
|
|