Importrange mit Query und Datum
In diesem Tutorial zeige ich Euch wie Ihr mit Google Tabellen IMPORTRANGE mit QUERY und DATUM kombinieren könnt. Das Tutorial entstand aufgrund einer Anfrage. Ziel ist es, Daten einer Quelldatei mit Datum, in eine andere Tabelle zu ĂŒbertragen. Die Quelldatei enthĂ€lt allerdings nur Datumsangaben wenn auch ein Umsatz generiert wurde. Die Zieldatei enthĂ€lt die Datumsangaben fĂŒr den kompletten Monat.
Wer sich noch gar nicht mit GOOGLE Tabellen auskennt kann sich ja mal diese Seite hier anschauen:
Welche Google Tabellen Tutorials als AnfÀnger
Aufbau der Quell – Tabelle "Importrange mit Query und Datum"
Der Aufbau dieser Tabelle ist sehr simpel gehalten. Ich habe nur in der ersten Zeile eine Ăberschrift eingetragen. Darunter etwas Text und dann die Spaltentitel. Die richtigen Daten habe ich erst ab Zelle A4 bis D13.
Google Tabellen mit Importrange Query und Datum (Quelle)
Wie Ihr hier seht sind die DatumseintrÀge zwar fortlaufend aber es fehlen Tage wo kein Umsatz generiert wurde. Hier sind die EintrÀge auch von mehreren Monaten zu sehen.
Ziel ist es jetzt nur die EintrÀge von einem bestimmten Monat auszulesen.
Aufbau der Ziel – Tabelle "Importrange mit Query und Datum"
Der Aufbau der Zieldatei ist Ă€hnlich. Hier habe ich ebenfalls eine Ăberschrift benutzt, einen Text und die Spaltentitel in Zeile 3.
Google Tabellen Importrange Query Datum Ziel
Auf der linken Seite der Zieltabelle sind die Datumsangaben fortlaufend. Hier geht es nach unten natĂŒrlich weiter. Also stehen die Datumsangaben vom kompletten MĂ€rz drin.
Die EintrĂ€ge von der Quelldatei sollen jetzt ĂŒber eine Formel entsprechend zugeordnet werden. Das heiĂt das die Zellen B4 bis D34 zunĂ€chst komplett leer sind.
Die Formel "Importrange mit Query und Datum"
Wir markieren die erste Zelle wo die Daten rein geschrieben werden sollen, also B4 (Siehe Punkt 2) und tragen jetzt nachfolgende Formel ein:
=WENNFEHLER(
query(
importrange("Der komplette Link zu Deiner Quell Tabelle";
"Tabellenblatt1!A4:D1000");
"Select Col2, Col3,Col4 WHERE DAY(Col1)=("&TAG(A4)&") AND MONTH(Col1)=("&MONAT(A4)-1&") AND YEAR(Col1)=("&JAHR(A4)&")
");
"Kein Eintrag")
Ihr könnt die Formel natĂŒrlich auch in der Eingabezeile oben eintragen nachdem Ihr die Zelle B4 markiert habt (Punkt 1).
Die WENNFEHLER Funktion umschlieĂt die gesamten Funktionen damit wir keine Fehler zurĂŒck bekommen, falls ein Wert nicht gefunden wird. Wenn zum Beispiel in der Quelldatei ein Datum nicht gefunden wird erhalten wir sonst #NV als Ergebnis zurĂŒck. Jetzt ist es so dass wir entweder ein Ergebnis erhalten oder der Eintrag "Kein Eintrag" erhalten.
Die QUERY Funktion erlaubt uns Abfragen zu generieren die SQL Àhnlich aufgebaut sind. Wer sich ein einfaches Video Tutorial zu QUERY anschauen möchte sollte mal hier klicken:
Funktion query() Àhnliche Abfragen wie bei SQL (Google Tabellen)
Die Funktion QUERY benötigt einmal den Link zu Deine Quelltabelle (also den kompletten Link im Browser). Wer nicht genau weià wo er es findet kann ja mal hier schauen:
Wo steht die GOOGLE Tabellen ID
Der nĂ€chste Parameter gibt den Bereich an der die Daten enthĂ€lt die zurĂŒckgegeben werden könnten. Das muss man sich jetzt wie eine kleine Datenbank vorstellen. Hier habe ich den Tabellenblatt Namen sowie die Zellen A4 bis D1000 gewĂ€hlt.
Interessant wird jetzt natĂŒrlich der letzte Parameter von QUERY. Hier können wir unsere Abfrage starten.
Mit Select Col2, Col3, Col4 beschreibe ich welche Werte ich zurĂŒck möchte. Ich gebe hier die Daten der Spalte 2, 3 und 4 an. Das bedeutet Spalte B,C und D. Möchte ich zum Beispiel nur das Ergebnis von Spalte C, dann gebe ich nur SELECT Col3 an.
Ăber WHERE können wir jetzt die Bedingungen steuern. Hier nehme ich einfach den Tag, den Monat und das Jahr als Filter und verweise dabei auf die Zelle A4 aus der Ziel Tabelle. Also werden jetzt die Datums EintrĂ€ge von Spalte A aus der Quelldatei mit der Zelle A4 aus der Zieltabelle verglichen. Wenn die Abfrage einen Wert findet wird er entsprechend eingetragen.
Formel kopieren "Importrange mit Query und Datum"
Wir haben ja jetzt die Formel nur in Zelle B4 eingefĂŒgt. Wir können jetzt die Formel fĂŒr den kompletten Monat einfach ĂŒber den kleinen Anfasser (unten rechts in der markieren Zelle) herunter ziehen. Also von B4 bis nach B34. AbhĂ€ngig natĂŒrlich wie lang der Monat ist đ
ĂberprĂŒft Eure Ergebnisse aber unbedingt noch einmal und schaut ob sich das Datum um einen Tag verschoben hat. Ich bin mir nĂ€mlich nicht genau sicher warum es erst geklappt hat nachdem ich -1 in der Formel eingetragen habe.
Das war es auch schon wieder. Ich hoffe Euch hat das Tutorial weiter helfen können đ
Euch gefÀllt die Datei und Ihr wollt Euch unbedingt bedanken?
Hier ist meine AMAZON – Wunschliste [HIER KLICKEN] đ
2 Comments
Hallo Chris.
Zuerst: Ein groĂes Lob fĂŒr deine Seite und ein dickes Danke fĂŒr Deine MĂŒhen! (Sorry, das musste jetzt einfach mal raus! đ )
Zu dem Tutorial habe ich eine Frage, welche sich grundlegend auf die Query – Abfrage bezieht:
Wenn ich mehrere Abfragen untereinander stellen möchte, die sich aufgrund der Ausgabe ĂŒberschneiden wĂŒrden, bekommt man die Fehlermeldung:
"Array-Ergebnis wurde nicht erweitert, da dadurch Daten in XY ĂŒberschrieben wĂŒrden."
Bezogen auf dieses Tutorial: Was tun, wenn ich an einem Tag mehrere UmsÀtze habe?
Siehst Du da eine Möglichkeit?
Vielen Dank,
Vielen Dank fĂŒr das nette Kompliment đ
Die Fehlermeldung entsteht dadurch dass die QUERY Abfrage mehrere Zeilen generieren möchte und diese Zeilen aber nicht eingetragen werden können, weil darunter schon die nÀchste Formel steht.
Ich bin mir jetzt nicht sicher was Du durch die Eintragung von mehreren Formeln erreichen möchtest. Wenn mehrere Zeilen als Ausgabe herauskommen wĂŒrden, dann sehe ich keine Möglichkeit direkt unter einer QUERY Abfrage schon die nĂ€chste zu schreiben. Du kannst die Ausgabe zwar Limitieren mit LIMIT aber das wird Dir nicht viel bringen weil dann ein Teil der Ergebnisse nicht angezeigt wird. Du könntest also entweder die Abfragen nebeneinander eintragen, in verschiedenen Tabellen ausgeben lassen oder die Abfrage so erstellen dass nur eine Zeile als Ergebnis heraus kommt.
Viele GrĂŒĂe
Chris