Adres-DB mit Excel

[rainer.minnerop]

Hallöchen.

Bevor ich mir in den Formeln von excel einen Wolf suche, frage ich lieber. Erstmal die Erklärung was ich bauen muss.

Ich habe mir bei Google-Docs eine Tabelle gemacht. In der Haupttabelle sind endlos Listen aus Abschriften von Kirchenbüchern. Die werden der Reihe nach abgeschrieben. Ziel ist die Erstellung von Stammbäumen und Ortsfamilenbüchern (OFB)

Dann habe ich mir Tabellen gemacht, die aus dieser Gesamtliste jeweils die Nachnamen nach Anfangsbuchstaben sortiert rausfiltern.

Also Tabellen mit den Namen A-Z
In jeder Tabelle fragen ich per query ab, welche Familiennamen es mit A,B,C usw gibt und lasse sie ausgeben. Klappt alles wunderbar, aber halt nur online bei Google. Wenn ich das runterladen will, kann ich das zwar als Excel-Tabelle runterladen, aber die enthält die Formeln nicht, da die ja nicht klappen. Es wird zwar die komplette Liste ausgegeben, aber der neue Besitzer kann sie nicht weiterführen.

Jetzt wäre mir lieber, das in Excel zu machen.

Geht das? Wenn ja, wie geht ungefähr der Einstieg. Ich habe das per SQL hinbekommen, wenn ich den Einstieg habe, fummele ich mich schon da rein.

Ich häng mal einen Screenshot der Tabelle in Google-Docs dran, falls die Erklärung undeutlich ist.


   

Liebe Grüße .... Rainer

[schauan]

Hallo Rainer,
Eventuell kannst Du die Datei auch im Google-Format hier ins Forum laden - dann kann man eventuell mehr damit anfangen als mit dem Bild.

[rainer.minnerop]

Hi.

sorry, geht so nicht, weil da zum Testen diverse Daten einer anderen DB mit zig Adressen und Kontoverbindungen drin sind. Die müsste ich erst alle löschen und hinterher wieder reinfügen. Ginge zur Not auch, aber wirklich nur, wenn meine Erklärung garnicht funktioniert.

Hier mal der Query mit dem ich das mache. Ist sehr an SQL angelehnt, und jeder kann eigentlich verstehen was die Formel macht.
Hier die Abfrage für die Nachnamen mit Buchstabe "M"

Code:

=query(Gesamtliste!A11:CC247; " select * where (B starts with 'M') or (P starts with 'M') or (U starts with 'M') order by P";0)

Also das heißt:
Schau in die Tabelle Gesamtliste, ziehe aus den Feldern A11 bis CC247 alles, wenn der Eintrag im Feld B oder P oder M mit dem Buchstaben M beginnt, und sortieren es nach Feld P

Das liefert dann die gesamte Zeile aus, in der ein Nachname des Kindes, des Vaters oder der Mutter mit M beginnt.

Diese Zeile in jede Tabelle geschrieben, und für den Anfangsbuchstaben jeweils angepasst liefert die Infos aus. Eine recht einfache Sache mit Datenbanken. Aber die hat nunmal nicht jeder zu Hause, und ich brauche etliche Helfer bei der Arbeit. Die will ich aber nicht alle in diese Originaltabelle als Bearbeiter reinlassen.
Also für jeden eine Exceltabelle, die füllt er/sie aus, und ich füge dann die Sachen in die Haupttabelle.

.... wenn Excel das kann.

[atilla]

Hallo,

das könnte in Excel sehr leicht mit einer Pivottabelle funktionieren.
Du müsstest für jeden Buchstaben eine eigene Pivottabelle erstellen.
Das ist aber sehr schnell erledigt.

Ich persönlich würde es vielleicht per VBA lösen.
Das sähe dann so aus, dass beim Aktiviere der einzelnen Tabellen diese automatisch neu eingelesen werden. Mit dem Spezialfilter wären das nur ein paar Zeilen Code.

[rainer.minnerop]

Hi Atilla.

Danke für deine Antwort, das hört sich doch sehr zielführend an.

Oki, dann werde ich mich mal mit Pivot beschäftigen.

Zu der Lösung mit VBA noch eine Frage.
Geht das dann auch so, dass ich für jeden Buchstaben eine Tabelle anlege, und in dieser dann das Script ausgeführt wird und die Haupttabelle abfragt?
Wenn ja, wäre das für dich eine große Sache meine Abfrage zu "übersetzen"?

Ich hänge mal die Grundtabelle zur Erfassung der Geburten an. So ähnlich sind auch die Tabellen für Heiraten, Sterbefälle etc.

  Template_Geburten_1_1.xls (Größe: 42,5 KB / Downloads: 20)

[atilla]

Hallo Rainer,

Geht das dann auch so, dass ich für jeden Buchstaben eine Tabelle anlege, und in dieser dann das Script ausgeführt wird und die Haupttabelle abfragt?
Wenn ja, wäre das für dich eine große Sache meine Abfrage zu "übersetzen"?

Das ist mit dem Spezialfilter kein Problem.
Ob Pivot oder Spezialfilter, entscheidend ist, dass alle Spalten eine Überschrift haben. Das ist bei SQL denke ich auch so. Ich erkenne jetzt nicht, aus welcher Spalte die Namen ausgelesen werden sollen.

Du schreibst:

=query(Gesamtliste!A11:CC247; " select * where (B starts with 'M') or (P starts with 'M') or (U starts with 'M') order by P";0)

Also Spalte B, P und U

dann schreibst Du:

Schau in die Tabelle Gesamtliste, ziehe aus den Feldern A11 bis CC247 alles, wenn der Eintrag im Feld B oder P oder M mit dem Buchstaben M beginnt, und sortieren es nach Feld P

Das sind die Spalten B, P und M

Schreib bitte genau, in welchen Spalten soll gesucht werden?

Das wäre eine mit dem Spezialfilter eine "Oder" Filterung.

Ich würde die Filterkriterien in einem separaten Blatt ablegen und dann die Daten in die Tabellen einlesen, oder spricht etwas gegen eine weitere Tabelle in der Datei? Die kann auch ausgeblendet sein.

[rainer.minnerop]

Hi.

Erstmal Danke für deine Mühen.

In der Spalte Q der obigen Tabelle (meine Tabelle mit der SQL-Abfrage ist anders aufgebaut) steht der Familienname des Kindes. Der soll rausgefiltert werden. In dem Tabellenblatt A sollen alle Familiennamen angezeigt werden, die mit A anfangen. Wenn die Bedingung erfüllt ist, soll aber die gesamte Zeile ausgegeben werden, nicht nur dieses eine Feld.

Oben drüber müssen natürlich erst die Zeilen eingefügt werden mit den allgemeinen Angaben. Also die Abfrage erst ab Zeile 12 beginnen, oder die vorherigen Daten auch mit einfügen, wenn das geht. Also das über jedem Tabellenblatt auch steht, welches Buch von welchem Ort das ist. Zeilen 1-11 glaube ich.

Ich würde die Filterkriterien in einem separaten Blatt ablegen und dann die Daten in die Tabellen einlesen, oder spricht etwas gegen eine weitere Tabelle in der Datei? Die kann auch ausgeblendet sein.

Nein dagegen spricht nichts, denn die Tabellenblätter mit der alphabetischen Sortierung werden ja per Auge ausgelesen.

Vielleicht noch eine Erklärung warum.
In den alten Kirchenbüchern ( so vor 1900 )gab es noch keine festgelegte Schreibweise der Namen. Also jemand konnte ohne weiteres Minnerop, Minrop, Minnerup, Minnirop usw schreiben. Es gab auch keine festgelegte Schrift wie heute, die war regional unterschiedlich. Die Namen richtig wiederzugeben ist also Glücksache. Denn a, o und u sind in der alten Schrift sehr ähnlich. Wenn ich jetzt eine Suchabfrage machen, nach Name Minnerop, findet er alle falsch geschriebenen Worte nicht. Also muss ich alles was mit M, N oder ähnlich geschriebenen Buchstaben beginnt per Auge durchsuchen.
Das soll dann in den Tabellen geschehen, die die Abfrage erzeugt. Die Abfrage muss mir also auch alle Informationen die die Haupttabelle enthält ausgeben. Auch leere Felder, denn kein Eintrag kann auch eine Information sein.


...und eine letzte Frage, damit wir nicht so eine Sache starten, und dann ist Excel mit der Datenmenge überfordert.

Wieviele Zeilen kann so eine Tabelle enthalten? Das werden für die Gesamttabelle "Geburten" schon einige tausend Zeilen werden, die dann auf die Tabellenblätter A-X ausgegeben werden. Mal so geschätzt 15-20.000 Zeilen. Also das werden recht große Dateien!
Der Ort mit dem ich anfange hatte 1945 als die Russen kamen so um 5000 Einwohner. Die Tabelle wird alle Geburten der ca. 200 Jahre davor enthalten, die in diesem Ort stattgefunden haben. Das gleiche wird dann für Taufen, Eheschließungen, Todesfälle angelegt in separaten Dateien.

Wenn das ein Hindernis ist, das die Dateien so groß werden, ist es dann vielleicht besser, die Daten extern in eine DB einzugeben, und mit dieser Tabelle nur auslesen zu lassen?

Liebe Grüße ..... Rainer

[atilla]

Hallo Rainer,

unten ein Beispielcode, der in das Code Modul DieseArbeitsmappe hinein gehört.

Folgendes ist Vorausgesetzt:

Es gibt in der Datei eine weitere Tabelle mit dem Namen: "Filter"
In dieser sind im Bereich A1:C1 die Überschriften der Spalten vorgetragen, in denen gesucht werden soll.
So sähe das aus:

Arbeitsblatt mit dem Namen 'Filter'
	A	B	C
1	Quelle: Seite	Kind: Vorname	Vater: Vorname
2

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Der Code filtert in den angegebenen Spalten und liest die Ergebnisse in die mit Buchstaben benannten Tabellen, sobald diese aktiviert wird.
Mit den im Code enthaltenen Kommentaren solltest du weiterkommen.

Code:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   Dim lngZ As Long
  
   If Sh.Name Like "[A-Z]" Then     'wenn Blattname "A-Z"
  
      Sh.Cells.ClearContents        'Blatt leeren zum neu Einlesen
      
      With Sheets("Geburten")       'In Blatt Geburten die letzte Belegte Zeile in Spalte A feststellen
         lngZ = .Cells(.Rows.Count, 1).End(xlUp).Row
      End With
      
      'Spezialfilter "Oder" Filterung
      'Der Bereich A13:CC bis zur letzten belegten Zelle  wird nach Kriterien A1:C4 in Blatt Filter gefiltert
      'und die Filterergebnisse werden in das aktivierte Blatt ab A1 eingetragen
      With Sheets("Filter")               'Mit Blatt Filter
         .Range("A2") = Sh.Name & "*"     ' in Zelle A2 den Blattnamen und  plus "*" als Platzhalter
         .Range("B3") = Sh.Name & "*"     ' in Zelle B3 den Blattnamen und  plus "*" als Platzhalter
         .Range("C4") = Sh.Name & "*"     ' in Zelle C4 den Blattnamen und  plus "*" als Platzhalter
          Sheets("Geburten").Range("A13:CC" & lngZ + 1).AdvancedFilter Action:=xlFilterCopy, _
              CriteriaRange:=Sheets("Filter").Range("A1:C4"), CopyToRange:=Sh.Range("A1"), Unique:=False
      End With
      
'      sortieren nach Spalte P
      With Sh
         lngZ = .Cells(.Rows.Count, 1).End(xlUp).Row
          .Range("A1:CC" & lngZ + 1).Sort Key1:=.Range("P1"), Order1:=xlAscending, Header:=xlYes, _
              OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
              DataOption1:=xlSortNormal
      End With
      
   End If
  
End Sub

[atilla]

Hallo Rainer,

hab jetzt erst gesehen, dass Du zwischenzeitlich schon geantwortet hattest.
Mit der Datenmenge von 15-20000 Zeilen sollte kein Problem sein.
Die Gesamtmenge ist in Excel, soweit ich weiß, vom Arbeitsspeicher abhängig.

Teste doch den eingestellten Code erst einmal in einer Kopie Deiner Datei.
Die Feinheiten können dann leicht eingearbeitet werden.

[rainer.minnerop]

Hi.

Oki, du bist ja superschnell. Ich werde das heute Abend testen.

Noch eine kurze Frage vorab. Wird das als Makro in die Tabelle Filtern eingestetzt, oder als Modul, gültig für alle Tabellen?

Hallo Rainer,

unten ein Beispielcode, der in das Code Modul DieseArbeitsmappe hinein gehört.

Folgendes ist Vorausgesetzt:

Es gibt in der Datei eine weitere Tabelle mit dem Namen: "Filter"
In dieser sind im Bereich A1:C1 die Überschriften der Spalten vorgetragen, in denen gesucht werden soll.
So sähe das aus:

Arbeitsblatt mit dem Namen 'Filter'
	A	B	C
1	Quelle: Seite	Kind: Vorname	Vater: Vorname
2

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Der Code filtert in den angegebenen Spalten und liest die Ergebnisse in die mit Buchstaben benannten Tabellen, sobald diese aktiviert wird.
Mit den im Code enthaltenen Kommentaren solltest du weiterkommen.

Code:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   Dim lngZ As Long
  
   If Sh.Name Like "[A-Z]" Then     'wenn Blattname "A-Z"
  
      Sh.Cells.ClearContents        'Blatt leeren zum neu Einlesen
      
      With Sheets("Geburten")       'In Blatt Geburten die letzte Belegte Zeile in Spalte A feststellen
         lngZ = .Cells(.Rows.Count, 1).End(xlUp).Row
      End With
      
      'Spezialfilter "Oder" Filterung
      'Der Bereich A13:CC bis zur letzten belegten Zelle  wird nach Kriterien A1:C4 in Blatt Filter gefiltert
      'und die Filterergebnisse werden in das aktivierte Blatt ab A1 eingetragen
      With Sheets("Filter")               'Mit Blatt Filter
         .Range("A2") = Sh.Name & "*"     ' in Zelle A2 den Blattnamen und  plus "*" als Platzhalter
         .Range("B3") = Sh.Name & "*"     ' in Zelle B3 den Blattnamen und  plus "*" als Platzhalter
         .Range("C4") = Sh.Name & "*"     ' in Zelle C4 den Blattnamen und  plus "*" als Platzhalter
          Sheets("Geburten").Range("A13:CC" & lngZ + 1).AdvancedFilter Action:=xlFilterCopy, _
              CriteriaRange:=Sheets("Filter").Range("A1:C4"), CopyToRange:=Sh.Range("A1"), Unique:=False
      End With
      
'      sortieren nach Spalte P
      With Sh
         lngZ = .Cells(.Rows.Count, 1).End(xlUp).Row
          .Range("A1:CC" & lngZ + 1).Sort Key1:=.Range("P1"), Order1:=xlAscending, Header:=xlYes, _
              OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
              DataOption1:=xlSortNormal
      End With
      
   End If
  
End Sub