Berechneter Median der letzten 10 Messwerte unterschiedlicher Regionen
Wissensstand: Level 2 ⇒ Solides Basiswissen in PQ, etwas Erfahrung
Im Herber-Excel-Forum wurde in diesem Beitrag die Frage gestellt, wie aus einer Reihe von Messwerten unterschiedlicher Region und mehrerer Messergebnisse jeweils der Median berechnet werden kann; dabei ist zu berücksichtigen, dass ausschließlich die neuesten 10 kalendarischen Daten für die Berechnung herangezogen werden sollen. So weit mein Verständnis der etwas „verschwommenen” Fragestellung.
Das hört sich kompliziert an und so wirklich einfach ist es auch nicht. Ich habe Ihnen hier noch einmal die originale Muster-Tabelle für den Download bereitgestellt. Die farblichen Markierungen sind in dem Fall für das Verständnis recht hilfreich. Die Diskussion zeigte, dass die Beschreibung der Forderung durchaus interpretierfähig ist. Darum versuche ich an dieser Stelle noch einmal mit meinen Worten zu erklären, welche Daten für das Ergebnis berechnet werden sollen:
- Jede Region, hier BSK und NTG soll eigenständig berechnet werden.
- Für jede dieser Regionen sollen die kalendarisch neuesten 10 erfassten Messwerte berücksichtigt werden.
- Es gibt 3 unterschiedliche Stoffe, die mit einem Messwert erfasst werden: As, Pb und Cd.
- Für jede dieser 3 Substanzen soll der Median-Wert getrennt berechnet werden.
Die beispielhafte Markierung in der Muster-Tabelle gilt hier für die Region BKS, umfasst die letzten 10 Einträge (aus kalendarischen Sicht) und es wurde hier der Messwert für Pb gekennzeichnet. Ich habe bereits im Foren-Beitrag darauf hingewiesen, dass die Lösung (nach meinem derzeitigen Wissensstand) mit Power Query machbar ist, jedoch ein höheres Maß an Vorarbeit bei der Einrichtung erfordert als manch ein anderes Projekt.
Beginnen Sie damit, die Daten in eine Intelligente Tabelle umzuwandeln und dann in den Abfrage-Editor zu importieren. Prinzipiell hätten sie natürlich die Hinweise in den Zellen F24:F28 zuvor löschen können, aber da die beiden letzten Spalten sowieso in einer der ersten Schritte entfernt werden, ist das nicht erforderlich.
Nach dem Import sollten Sie die 1. Zeile löschen, sie enthält keine verwertbaren Daten und würde auch dem Wesen einer ordentlichen Tabelle/Abfrage widersprechen. In eine Spalte gehören ausschließlich gleichartige Daten. Idealerweise geht das über den Weg des Symbols Zeilen verringern. Im nächsten Schritt löschen Sie die beiden letzten Spalten As2 und Pb3, dort werden später neue Spalten für die Median-Berechnungen erstellt.
Im Beitrag ist die Rede davon, dass die Sortierung in den Quelldaten stets eine andere sein kann. Der Fragesteller hat dann auch darauf hingewiesen, dass ungeachtet dessen immer die letzten 10 Tage mit Einträgen für die Berechnung herangezogen werden sollen. So verstehe zumindest ich den Beitrag. Sortieren Sie darum als erstes die Spalte Tag WE aufsteigend. Wenn es für sie „augenschonend” 😉 ist, dann können Sie den Datentyp auch gerne auf (nur) Datum ändern. Schließen & laden in… und wählen Sie die Option Nur Verbindung erstellen.
Da es doch einige Abfragen mehr werden, gebe ich dieser Abfrage einen neuen Namen: Quell-Daten (zugegeben, ich verwende in vielen Fällen den Namen Source-Data). Öffnen Sie nun wieder die einzige Abfrage und erstellen daraus einen Verweis. Das geht entweder über den Menüpunkt Verwalten oder per Rechtsklick im linken Seitenfenster auf den Namen der Abfrage. Im Prinzip ist das ein Duplikat, aber Änderungen in dieser Abfrage werden direkt in die Verweis-Kopie übernommen. Idealerweise erstellen Sie gleich noch einen solchen Verweis. Beachten Sie dabei unbedingt, dass Sie wiederum von der Abfrage mit den Quelldaten den Verweis erstellen. – Die Namen der Abfrage sind ja nicht so wirklich aussagekräftig, darum benennen Sie die Abfrage Quell-Daten (2) so um: Quell-Daten (NTG) und die 2. eben erstellte Abfrage bekommt den Namen Quell-Daten (BSK).
Jetzt wird Ihnen wahrscheinlich auch klar sein, was in diesem beiden neuen Abfragen geschehen wird. 😎 Filtern Sie jede der beiden Abfragen so, dass nur noch die entsprechenden Daten erhalten bleiben. Um die Abfrage auf die letzten 10 Werte (Zeilen) zu begrenzen, gehen Sie den Weg über Zeilen verringern | Zeilen beibehalten | Letzte Zeilen beibehalten und geben Sie bei Anzahl von Zeilen den Wert 10 ein. Nach einem OK bleiben noch genau 10 Zeilen übrig. Und daraus soll und wird der Median berechnet werden. – Bei der anderen Verweis-Abfrage gehen Sie gleichermaßen vor.
Aktivieren Sie nun die Abfrage Quell-Daten (NTG) und im Register Start Klicken Sie auf das Symbol Kombinieren. Erweitern Sie hier im Untermenü Abfragen anfügen und wählen den Punkt Abfragen als neu anfügen. Im Dialog-Fenster belassen Sie es bei Zwei Tabellen und wählen Sie als Anzufügende Tabelle die andere eben erstellte Abfrage: Quell-Daten (BSK). Da die Überschriften beider Abfragen identisch sind, werden die Daten der 2. Abfrage direkt angefügt/angehängt. Wie vorgegeben wird eine neue Abfrage erstellt, die den Namen Append1 hat. Wenn Sie möchten, können Sie diese umbenennen; ich belasse es bei diesem Namen, denn diese Abfrage dient wiederum als Basis für 3 weitere Verweis-Abfragen. Erstellen Sie also diese 3 Verweise. Als Namen für die Abfragen vergeben sie beispielsweise Median As, Median Pb und Median Cd.
Am Beispiel der Abfrage Median As zeige ich Ihnen auf, wie sie bei allen dieser 3 Abfragen vorgehen werden, um den Median je Bereich und (vermutlich) Schadstoff zu berechnen:
- Achten sie darauf, dass die Spalte Region markiert ist.
- Gruppieren nach Region und bei Neuer Spaltenname geben Sie Median As (oder auch nur die chemische Kurzbezeichnung für das die jeweilige Element) ein.
- Bei Vorgang wählen Sie im DropDown Median.
- Bei Spalte wählen Sie die passende Überschrift, also im ersten Fall As aus.
- Gehen Sie bei den restlichen 2 Abfragen gleichermaßen vor, natürlich auf die chemischen Elemente angepasst.
Das Ergebnis ist eine 2‑spaltige Abfrage mit je einem Eintrag für die beiden Regionen, die sich in der Abfrage Median As so darstellt:
Die erste Berechnung des Median
Immer noch im Register Start aktivieren Sie die Abfrage Median As. Im Menüband ein Klick auf Kombinieren, erweitern Sie den Punkt Abfragen zusammenführen 
und wählen dort Abfragen als neue Abfrage zusammenführen. Im Dialog werden sie im mittigen schmalen Textfeld die Auswahl Median Pb treffen und anschließend jeweils in das Feld Region Klicken:
Zusammenführen der beiden ersten Median-Abfragen
Immer noch in der Abfrage Merge1 werden Sie diesen Vorgang wiederholen, nur dass Sie dieses Mal den direkten Weg Kombinieren | Abfragen zusammenführen wählen und natürlich die Abfrage Median Cd mit der zuletzt erstellten Abfrage zusammenführen. Und das sieht jetzt auch schon sehr gut aus. Von allen 3 gemessenen Stoffen wurden für jede Region die Median-Werte berechnet.
Jetzt Schließen & laden und sie werden vielleicht erstaunt sein, denn sie sehen, dass sie nichts sehen. 🙄 Es wurde keine Tabelle in der Excel-Arbeitsmappe erstellt. Das liegt daran, dass auch die zuletzt erstellte Abfrage Merge1 im Modus Nur Verbindung erstellt worden ist. Die Lösung: Klicken Sie im rechten Seitenfenster mit der rechten Maustaste auf diese Abfrage und wählen Sie Laden in … Hier Klicken Sie auf die Option Tabelle und wählen Bestehendes Arbeitsblatt und Klicken sie dann in das Zielfeld bzw. tragen Sie die Adresse von Hand ein, wo die Daten eingetragen werden sollen.
Das Ziel ist erreicht und wenn die Quell-Daten später ergänzt oder geändert werden genügt ein Klick auf Aktualisieren und die Daten der Abfrage werden automatisch angepasst. Dieser Weg ist in jedem Falle zielführend. Insbesondere bei großen Datenmengen lohnt sich der etwas höhere Aufwand in jedem Fall. – Meine Lösung sende ich Ihnen gerne per E‑Mail zu, wenn Sie mir den Dateinamen PQ-Herber-Median-127995.xlsx angeben.
Ergänzung: In einem Schluss-Statement legte der Fragesteller dar, dass es auch denkbar sei, dass ein anderer Zeitbereich mit (wahrscheinlich) früherem Ende-Datum ausgewertet werden soll. In einem solchen Fall wäre es ein guter Weg die Quell-Daten im ersten Schritt so zu filtern, dass nur noch kalendarische Daten bis zum letzten Tag erhalten bleiben.