Excel (aktuelle Version): Die Daten 2er Dateien in 3. Datei zusammenführen

[StefanW.]

Ich arbeite zwar häufiger mit Excel, aber jeweils nur in einer Datei.

Mir liegt ein Fahrplan vor (analog Zeitplan.xlsx auf dem Screenshot), an dem sich eigentlich nie etwas ändert.
Daneben bekomme ich jeden Tag für den Folgetag eine Mitfahrertabelle (analog Mitfahrer.xlsx). In der Mitfahrernummer ist nur die Nummer des Busses vermerkt, aber nicht die Zeit. Die Zeit ist über die Bus-Nr. eindeutig definiert. Damit die Mitfahrer weniger Fehler machen, würde ich gerne aus den beiden Dateien eine dritte Datei generieren, in der nicht mehr die Nummern, sondern nur noch die Zeiten stehen.

Wie kann ich aus Zeitplan.xlsx und Mitfahrer.xlsx eine Datei Zusammenführung.xlsx erzeugen? Die Ursprungsdateien liegen immer im selben Verzeichnis und auch die Zieldatei soll in dieses Verzeichnis. Der Zeitplan ist fix. Bei der Mitfahrerliste variieren die Namen und Anzahl der Personen (3 bis max. 15) und natürlich die genutzten Verbindungen. Es kann auch sein, dass für eine Verbindung der Bus genutzt wird und der andere Weg sonstwie zurückgelegt wird (in der Tabelle so bei Stefan).

Ich gehe davon aus, dass das Problem für Euch keine grosse Herausforderung ist und würde mich über eine Lösung oder um einen Hinweis zur Lösungsfindung sehr freuen.

Danke
Stefan

PS: Leider kann ich hier meine beispielhaften Zeitplan.xlsx und Mitfahrer.xlsx nicht hochladen.

 

[LisaE]

PS: Leider kann ich hier meine beispielhaften Zeitplan.xlsx und Mitfahrer.xlsx nicht hochladen.

Magst Du mal versuchen, aus Deinen Beispieldateien eine ZIP-Datei zu machen und die hochzuladen oder mir zu schicken? Dann versuch ich mich da mal dran. Wäre dann allerdings per Excel-Makro, auf der Oberfläche bin ich eine Niete
Mein VBA ist zwar ein bisschen eingerostet, aber ich kann es gerne probieren.

 

[matt-eagle]

Powered by ChatGPT

Hallo Stefan,

Da Sie die Dateien nicht hochladen können, werde ich Ihnen eine allgemeine Anleitung geben, wie Sie die gewünschte Zusammenführung in Excel durchführen können:

1. Vorbereitung:
   • Öffnen Sie beide Dateien (Zeitplan.xlsx und Mitfahrer.xlsx) in Excel.
   • Erstellen Sie eine neue Excel-Datei namens Zusammenführung.xlsx.
2. VLOOKUP-Funktion verwenden:
   • In der Datei Zusammenführung.xlsx können Sie die VLOOKUP-Funktion (in Deutsch: SVERWEIS) verwenden, um die entsprechende Zeit basierend auf der Busnummer aus Zeitplan.xlsx zu holen.
3. Schritte:
   • Kopieren Sie alle Daten aus Mitfahrer.xlsx und fügen Sie sie in Zusammenführung.xlsx ein.
   • Fügen Sie eine neue Spalte neben der Spalte "Bus-Nr." ein und benennen Sie sie "Zeit".
   • In der ersten Zelle dieser neuen Spalte (angenommen es ist Zelle B2), geben Sie folgende Formel ein:
     excelCopy code
     =WENNFEHLER(SVERWEIS(A2, [Zeitplan.xlsx]Tabelle1!$A$2:$B$100, 2, FALSCH), "")
     Hierbei wird angenommen, dass in Zeitplan.xlsx die Busnummern in Spalte A und die zugehörigen Zeiten in Spalte B stehen und dass es maximal 100 Zeilen Daten gibt. Passen Sie den Bereich $A$2:$B$100 entsprechend an, wenn Ihr Zeitplan mehr oder weniger Zeilen hat.
   • Ziehen Sie die Formel nach unten, um sie auf alle Zeilen anzuwenden.
4. Abschluss:
   • Speichern Sie die Datei Zusammenführung.xlsx.
   • Sie sollten nun eine Tabelle haben, die sowohl die Namen der Mitfahrer als auch die zugehörigen Zeiten basierend auf den Busnummern zeigt.

Einige Hinweise:

• Stellen Sie sicher, dass beide Dateien (Zeitplan.xlsx und Mitfahrer.xlsx) geöffnet sind, wenn Sie die VLOOKUP-Funktion in Zusammenführung.xlsx verwenden.
• Wenn Sie die Dateien schließen und später wieder öffnen, müssen Sie möglicherweise den Dateipfad in der VLOOKUP-Formel aktualisieren, insbesondere wenn Sie die Dateien verschieben.

Ich hoffe, diese Anleitung hilft Ihnen weiter! Wenn Sie weitere Fragen oder Anforderungen haben, lassen Sie es mich bitte wissen.

 

[StefanW.]

Hallo @LisaE
Danke für Deine Antwort.
Stimmt, ZIP-Dateien sind ja erlaubt. Ich habe dem ersten Beitrag eine ZIP mit den Beiden .xlsx und dem Screenshot beigefügt.

@matt-eagle
Danke auch für Deine Antwort. Die Ergebnisdatei muss täglich erstellt werden, deswegen wäre mir eine Lösung lieber, bei der der tägliche Aufwand sehr übersichtlich ist.

 

[LisaE]

Stimmt, ZIP-Dateien sind ja erlaubt. Ich habe dem ersten Beitrag eine ZIP mit den Beiden .xlsx und dem Screenshot beigefügt.

Sorry, ich sehe da nach wie vor nur das JPG ... magst Du mir das ZIP per PN schicken?

Beitrag automatisch zusammengeführt: 21 Sep. 2023

Sorry, ich sehe da nach wie vor nur das JPG ... magst Du mir das ZIP per PN schicken?

Vergiss es, ich sehe es jetzt

Beitrag automatisch zusammengeführt: 21 Sep. 2023

Probier mal, ob Du damit klarkommst: Du öffnest Deine beiden Mappen und die neu hier mit dem Namen 'Zusammen' und dem Makro 'Zusammen'. Dann führst Du das Makro aus und bekommst eine neue Tabelle in der Arbeitsmappe mit dem Makro, Blattname des neuen Tabellenblatts ist das Tagesdatum.

 

[matt-eagle]

Wenn du es automatisieren willst, ist Python doch dein Freund hierfür

Ja, Sie können diesen Prozess mit Python automatisieren, insbesondere mit Hilfe der Bibliothek `pandas`. Hier ist ein einfaches Skript, das die gewünschte Zusammenführung durchführt:

```python
import pandas as pd

def zusammenfuehrung(zeitplan_path, mitfahrer_path, output_path):
# Dateien einlesen
df_zeitplan = pd.read_excel(zeitplan_path)
df_mitfahrer = pd.read_excel(mitfahrer_path)

# VLOOKUP-ähnliche Operation in pandas
df_merged = df_mitfahrer.merge(df_zeitplan, on='Bus-Nr.', how='left')

# Unnötige Spalten entfernen, falls gewünscht
# df_merged = df_merged[['Name', 'Zeit', ...]] # Fügen Sie hier die gewünschten Spaltennamen ein

# Ergebnis speichern
df_merged.to_excel(output_path, index=False)

# Pfade zu Ihren Dateien
zeitplan_path = 'Pfad_zu_Ihrem_Verzeichnis/Zeitplan.xlsx'
mitfahrer_path = 'Pfad_zu_Ihrem_Verzeichnis/Mitfahrer.xlsx'
output_path = 'Pfad_zu_Ihrem_Verzeichnis/Zusammenführung.xlsx'

zusammenfuehrung(zeitplan_path, mitfahrer_path, output_path)
```

**Anleitung**:

1. Installieren Sie die benötigten Bibliotheken, falls noch nicht geschehen:
```
pip install pandas openpyxl
```

2. Passen Sie die Pfade `zeitplan_path`, `mitfahrer_path` und `output_path` in dem obigen Skript an, um auf die entsprechenden Dateien in Ihrem Verzeichnis zu verweisen.

3. Führen Sie das Skript aus.

Das Skript liest die beiden Excel-Dateien, führt eine Zusammenführung basierend auf der Busnummer durch und speichert das Ergebnis in einer neuen Datei.

Bitte beachten Sie, dass Sie die Spaltennamen in den Dateien und im Skript entsprechend anpassen müssen, falls sie von meinem Beispiel abweichen.

 

[LisaE]

Wenn du es automatisieren willst, ist Python doch dein Freund hierfür

Um Himmels willen, warum sollte man denn Python installieren und mit Python-Scripten rumfummeln, wenn man etwas in Excel automatisieren will?

P.S. die von Dir geposteten Script-Stücke können selbst im Ansatz nicht funktionieren

 

[Guxtu]

Ich mache einen anderen Vorschlag, der a) funktioniert und b) nur Standard-Funktionen braucht.

Wenn ich das richtig sehe, dann besteht das Problem darin, daß es zwei Quelldateien und eine Zieldatei gibt.
Das Problem ist dann gelöst, wenn der Inhalt der Quelldateien in der Zieldatei landet und einfach aktualisiert werden kann.

Du nimmst also die Zieldatei her.
Du legst drei Blätter an.
Blatt 1: Deine Auswertelogik
Blätter 2 und 3: Inhalte der beiden Quelldateien. So, wie das Problem beschrieben ist, reicht je ein Blatt.
Im Blatt 2 legst Du einfache Zellbezüge an, die auf die Quelldatei 2 referenzieren.
Das kannst Du so machen, daß Du Zieldatei und Quelldatei 1 gleichzeitig öffnest, in der Zieldatei Blatt 2 Zelle A1 ein simples "=" eintippst und dann auf die Zelle A1 in der Quelldatei 1 klickst. Schon hast Du den Zellbezug. Die anderen Zellen füllst Du durch Ziehen über den benötigten Bereich in der Zieldatei. Das ist einfach. Voila, Inhalt da. Gleiches Vorgehen auf Blatt 3 und Quelldatei 2.
Fertig.

Nun änderst Du was in einer der Quelldateien und willst die Inhalte in der Zieldatei aktualisieren.
Dazu öffnest Du die Zieldatei und gehst auf den Reiter "Daten".
Du suchst und findest "Verknüpfungen bearbeiten". Du klickst drauf. Es öffnet sich ein Fenster, in dem Deine beiden Quelldateien angezeigt werden. Du markierst die geänderte Datei und wählst rechts "Werte aktualisieren" aus. Fertig.
Wenn Du eine andere Quelldatei hernehmen willst, mußt Du statt "Aktualisieren" "Quelle ändern" nehmen.

Problem gelöst.
Vielleicht gibt es elegantere Lösungen, aber so geht's und so mache ich das.

 

[matt-eagle]

Um Himmels willen, warum sollte man denn mit Python installieren und mit Python-Scripten rumfummeln, wenn man etwas in Excel automatisieren will?

P.S. die von Dir geposteten Script-Stücke können selbst im Ansatz nicht funktionieren

Excel Automatisierungen sind heutzutage dank der ausgereiften Bibliotheken mit Python so ein Kinderspiel geworden. Wenn man diesen Use-Case damit nicht automatisieren sollte, wäre ich sehr überrascht.

 

[LisaE]

Excel Automatisierungen sind heutzutage dank der ausgereiften Bibliotheken mit Python so ein Kinderspiel geworden. Wenn man diesen Use-Case damit nicht automatisieren sollte, wäre ich sehr überrascht.

Selbstverständlich kann man etwas ähnliches, was ich in VBA gemacht habe, auch in Python machen. Ich programmiere nach Jahrzehnten in VBA jetzt seit ein paar Jahren in Python und natürlich geht das.

Aber halt nicht so, wie beschrieben, weil da von einer gleichnamigen Spalte in beiden Dateien ausgegangen wird, über die dann verknüpft wird. Und diese gleichnamige Spalte gibt es ganz simpel nicht.

Und vor allem fehlt mir die Idee, warum man mühselig mit externen Python-Scripts rumwerkeln sollte, wenn doch Excel mit VBA eine einfache interne Möglichkeit mitbringt, Scripte zu erstellen.

 

[Guxtu]

Freunde, das Problem ist mit Excel-Standard-Funktionen einfach und nachvollziehbar lösbar.
Ich mache das regelmäßig so.

Klar kann man auch MatLab, Python, VBA und wahrscheinlich auch Fortran nehmen. Aber warum nur?
Das Ergebnis ist dann ein Kündigungsschutz-Programm.

Wenn einer meiner Kollegen mit sowas ankommt, dann lasse ich den Runden um den Block laufen, bis er/sie glaubhaft verspricht, das nie wieder zu tun.

Man merkt, ich bin allergisch auf die Skripter, die aus einem einfachen Problem ein kompliziertes machen.
Oder: wer nur einen Hammer hat, für den sieht jede Schraube wie ein Nagel aus.
Wer MatLab kann, wird doch keine Excel-Standard-Funktion nehmen.

 

[LisaE]

Freunde, das Problem ist mit Excel-Standard-Funktionen einfach und nachvollziehbar lösbar.

Würdest Du das bitte mal hochladen? Würde ich mir gerne mal anschauen, wie das in Excel selber funktioniert. Ich mache alles mit Makros, weil das für mich am einfachsten ist. Aber das heißt natürlich nicht, das mich nicht interessieren würde, wie man das "richtig" macht. Immer her mit dem Lernstoff, bitte.

 

[boletusmaximus]

Also als Excel-Praktiker mit nur geringen VBA-Kentnissen, der aber regelmäßig für Kollegen Excelauswertungen/-zusammenfassungen erstellt, würde ich zu der sverweis-Lösung tendieren. Wie man das ganzen dann anordnet ist sicher Geschmackssache.
Grundsätzlich würde ich empfehlen alle relevanten Informationen in einer Datei zu speichern und auf dateiübergreifende Beziehungen zu verzichten. Auch würde ich alle Datenbereiche grundsätzlich in Tabellen umwandeln, allein schon deshalb, weil sich Formeln dann automatisch in alle notwendigen Zeilen weiterkopieren.
Vorteil einer nicht über Programmierung gelösten Vorgehensweise ist, dass es auch für Personen ohne Programmierkenntnisse ohne allzu großen Aufwand nachvollziehbar ist und bei ähnlichen weiteren Problemen dann eine Lösung selbstständig gefunden werden kann.
Aufgrund der Kompaktheit der Daten würde auch ein Register pro Tag ausreichen, dass man sich dann immer für den folgenden Tag als Vorlage weiterkopiert. Für die Ansicht nicht mehr notwendige Quellinformationen könnte man dann einfach ausblenden.
Stefans erste Tabelle mit den Busnummern und den Zeiten kann man z.B. auf eine zweispaltige, für den sverweis völlig ausreichende Quelle reduzieren, da die Nummer eindeutig zu einer bestimmten Uhrzeit gehören. Eine Aufteilung in A -> B und B -> A ist nicht notwendig.
Diese Tabelle beginnt in A1 und kann bei Bedarf um weitere Nummer/Uhrzeit-Kombinationen erweitert werden.
Stefans zweite Tabelle beginnt dann in D1 und geht bis Spalte F. In diese Tabelle werden täglich die Informationen der an diesem Tag mitfahrenden Personen eingetragen.
An diese Tabelle können gleich zwei weitere Spalten G und H angefügt werden. In diese werden per sverweis die Informationen zu den Buszeiten aus Tabelle 1 gezogen.
Die Spalten A bis C können dauerhaft ausgeblendet werden, so lange keine neuen Busnummern eingefügt werden müssen.
Die Spalten E und F können nach dem Einkopieren der täglichen Personenliste ausgeblendet werden.
Übrig bleibt aus Stefans gewünschte Tabelle 3 als Teilbereich dieser zweiten Tabelle.

 

[Guxtu]

Würdest Du das bitte mal hochladen? Würde ich mir gerne mal anschauen, wie das in Excel selber funktioniert. Ich mache alles mit Makros, weil das für mich am einfachsten ist. Aber das heißt natürlich nicht, das mich nicht interessieren würde, wie man das "richtig" macht. Immer her mit dem Lernstoff, bitte.

Was soll ich da hochladen?
Wie man in Excel einen Zellbezug macht?
Der Witz ist, daß die Zelle auch in einem anderen File stecken darf. Und daß man die Bezüge mit einer Standard-Funktion aktualisieren kann. Das sieht man aber nicht, wenn ich ein File hochlade. Dafür die Anleitung.

Also als Excel-Praktiker mit nur geringen VBA-Kentnissen, ...

So kann man das auch machen, alles in ein File packen. Volle Zustimmung.
Ich würde vielleicht den Fahrplan in ein eigenes Blatt stecken und dann das Blatt ausblenden.

 

[LisaE]

Da mein Weg ja augenscheinlich absolut falsch ist, habe ich das Makro wieder gelöscht. Spaß hat es trotzdem gemacht

 

[boletusmaximus]

Ich würde vielleicht den Fahrplan in ein eigenes Blatt stecken und dann das Blatt ausblenden.

Da stimme ich zu, das ist die "professionellere" Lösung und reduziert natürlich auch die Datenmenge (auch wenn sie in diesem Beispiel nicht groß ist), weil diese Tabelle dann nur einmal vorhanden ist und nicht immer mitkopiert wird, wenn man für ein neues Datum durch Kopieren ein neues Blatt (Register) anlegt.

Da mein Weg ja augenscheinlich absolut falsch ist, habe ich das Makro wieder gelöscht. Spaß hat es trotzdem gemacht

Nicht doch. Ist doch spannend die Sache auf unterschiedliche Weise anzugehen.

 

[smartworld]

Ich werfe hier nur Mal kurz eine Ergänzung zum Thema sverweis ein: inzwischen* gibt es eine neue Funktion namens xverweis. Diese ist aus meiner Sicht noch intuitiver zu nutzen und lässt auch die Rückgabe einer Matrix zu. Damit sollte sich das Problem recht elegant lösen lassen.

*Leider ist diese Funktion erst ab Office 2021 bzw. bei Office 365 enthalten. Wenn das aber kein Problem darstellen sollte, so kann ich diese nur empfehlen.

XVERWEIS (Funktion) - Microsoft-Support

Verwenden Sie die XVERWEIS-Funktion, wenn Sie Elemente in einer Tabelle oder einem Bereich nach Zeile suchen müssen. Sie können z. B. nach dem Preis eines Kfz-Teils anhand der Artikelnummer suchen oder nach einem Mitarbeiternamen anhand seiner Mitarbeiter-ID. Mit XVERWEIS können Sie in einer...

support.microsoft.com

 

[StefanW.]

DANKE!
Ich hatte nicht damit gerechnet, eine solche Diskussion mit meiner Anfrage anzuzetteln.
Ich werde mich am Sonntag in Ruhe mit Euren Beiträgen beschäftigen. Feedback bekommt Ihr dann natürlich auch noch.
Stefan

PS: Die Lösung von LisaE macht schonmal, was ich wollte. Glücklicherweise habe ich sie gestern im Zug aufs Smartphone geladen. Android-Excel mag VBA-Makros nicht, aber es soll auf dem Desktop (davor sitze ich gerade) genutzt werden und nicht auf dem Smartphone.

 

[LisaE]

Glücklicherweise habe ich sie gestern im Zug aufs Smartphone geladen.

Nicht doch, ich hätte sie Dir auch per PN geschickt, ich war nur die Diskussionen leid.

 

[Guxtu]

Würdest Du das bitte mal hochladen? Würde ich mir gerne mal anschauen, wie das in Excel selber funktioniert. Ich mache alles mit Makros, weil das für mich am einfachsten ist. Aber das heißt natürlich nicht, das mich nicht interessieren würde, wie man das "richtig" macht. Immer her mit dem Lernstoff, bitte.

So, um auch den Punkt abzuschließen:
zum Beispiel zieht man aus dem Ziel-Excel namens Dateiname.xlsx die Zelle A1 mit folgendem Zellbezug
='G:\Pfadname\[Dateiname.xlsx]Blattname'!A1

Bei Libre Office Calc ist die Syntax leicht anders.

Sorry, falls ich etwas drastisch formuliert habe. Scripten da, wo Excel-Befehle ausreichen, erzeugt bei mir gerne Riesenresonanzen.
Ich arbeite in einem beruflichen Umfeld, in dem viel mit Excel gearbeitet wird und die Leute stark unterschiedliche Kompetenzen haben. Deshalb gilt es: immer so einfach wie möglich.