Wis­sens­stand: Lev­el 2 ⇒ So­li­des Basiswis­sen in PQ, et­was Er­fah­rung   

Im Her­ber-Excel-Forum wur­de in die­sem Bei­trag die Fra­ge ge­stellt, wie aus ein­er Rei­he von Mess­werten unter­schiedlich­er Re­gi­on und meh­re­rer Messergeb­nisse jew­eils der Medi­an berech­net wer­den kann; da­bei ist zu berück­sichti­gen, dass auss­chließlich die neu­es­ten 10 kalen­darischen Dat­en für die Berech­nung herange­zo­gen wer­den sol­len. So weit mein Ver­ständ­nis der et­was „ver­schwomme­nen” Fragestel­lung.

Das hört sich kom­pliziert an und so wirk­lich ein­fach ist es auch nicht. Ich habe Ih­nen hier noch ein­mal die orig­i­nale Mus­ter-Ta­bel­le für den Down­load bere­it­gestellt. Die far­blichen Markierun­gen sind in dem Fall für das Ver­ständ­nis recht hil­fre­ich. Die Diskus­sion zeig­te, dass die Beschrei­bung der For­de­rung dur­chaus inter­pretier­fähig ist. Dar­um ver­suche ich an die­ser Stel­le noch ein­mal mit mei­nen Wor­ten zu erk­lären, wel­che Dat­en für das Ergeb­nis berech­net wer­den sol­len:

• Jede Re­gi­on, hier BSK und NTG soll eigen­ständig berech­net wer­den.
• Für jede die­ser Regio­nen sol­len die kalen­darisch neu­es­ten 10 er­fass­ten Mess­werte berück­sichtigt wer­den.
• Es gibt 3 unter­schiedliche Stof­fe, die mit ei­nem Mess­wert er­fasst wer­den: As, Pb und Cd.
• Für jede die­ser 3 Sub­stanzen soll der Medi­an-Wert getren­nt berech­net wer­den.

Die beispiel­hafte Mar­kie­rung in der Mus­ter-Ta­bel­le gilt hier für die Re­gi­on BKS, um­fasst die let­zten 10 Ein­träge (aus kalen­darischen Sicht) und es wur­de hier der Mess­wert für Pb gekennze­ich­net. Ich habe bere­its im Fo­ren-Bei­trag dar­auf hin­ge­wie­sen, dass die Lö­sung (nach mei­nem derzeit­i­gen Wis­sens­stand) mit Pow­er Que­ry mach­bar ist, je­doch ein hö­he­res Maß an Vorar­beit bei der Ein­rich­tung er­for­dert als manch ein an­de­res Pro­jekt.

▲ nach oben …

Begin­nen Sie da­mit, die Dat­en in eine Intel­li­gente Ta­bel­le umzuwan­deln und dann in den Abfrage-Edi­tor zu im­por­tie­ren. Prinzip­iell hät­ten sie natür­lich die Hin­weise in den Zel­len F24:F28 zu­vor lö­schen kön­nen, aber da die bei­den let­zten Spal­ten so­wie­so in ein­er der ers­ten Schrit­te ent­fer­nt wer­den, ist das nicht erforder­lich.

Nach dem Im­port soll­ten Sie die 1. Zei­le lö­schen, sie ent­hält kei­ne ver­w­ert­baren Dat­en und wür­de auch dem We­sen ein­er or­dent­li­chen Ta­bel­le/Ab­fra­ge wider­sprechen. In eine Spal­te ge­hö­ren auss­chließlich gle­ichar­tige Dat­en. Ide­al­er­weise geht das über den Weg des Sym­bols Zei­len ver­ringern. Im näch­sten Schritt lö­schen Sie die bei­den let­zten Spal­ten As2 und Pb3, dort wer­den spä­ter neue Spal­ten für die Medi­an-Berech­nun­gen er­stellt.

Im Bei­trag ist die Rede da­von, dass die Sor­tie­rung in den Quell­dat­en stets eine an­de­re sein kann. Der Fra­ge­stel­ler hat dann auch dar­auf hin­ge­wie­sen, dass un­ge­ach­tet des­sen im­mer die let­zten 10 Tage mit Ein­trä­gen für die Berech­nung herange­zo­gen wer­den sol­len. So ver­ste­he zumin­d­est ich den Bei­trag. Sor­tie­ren Sie dar­um als ers­tes die Spal­te Tag WE auf­steigend. Wenn es für sie „augen­scho­nend” 😉 ist, dann kön­nen Sie den Daten­typ auch ger­ne auf (nur) Da­tum än­dern. Schlie­ßen & la­den in… und wäh­len Sie die Op­ti­on Nur Ver­bin­dung er­stel­len.

Da es doch ei­ni­ge Abfra­gen mehr wer­den, gebe ich die­ser Ab­fra­ge ei­nen neu­en Na­men: Quell-Dat­en (zu­ge­ge­ben, ich ver­wende in vie­len Fäl­len den Na­men Source-Da­ta). Öff­nen Sie nun wie­der die ein­zi­ge Ab­fra­ge und er­stel­len dar­aus ei­nen Ver­weis. Das geht entwed­er über den Me­nü­punkt Ver­wal­ten oder per Recht­sklick im lin­ken Seit­en­fen­ster auf den Na­men der Ab­fra­ge. Im Prin­zip ist das ein Dup­likat, aber Änderun­gen in die­ser Ab­fra­ge wer­den di­rekt in die Ver­weis-Kopie über­nom­men. Ide­al­er­weise er­stel­len Sie gle­ich noch ei­nen sol­chen Ver­weis. Beacht­en Sie da­bei unbe­d­ingt, dass Sie wie­der­um von der Ab­fra­ge mit den Quell­dat­en den Ver­weis er­stel­len. – Die Na­men der Ab­fra­ge sind ja nicht so wirk­lich aus­sagekräftig, dar­um benen­nen Sie die Ab­fra­ge Quell-Dat­en (2) so um: Quell-Dat­en (NTG) und die 2. eben er­stell­te Ab­fra­ge be­kommt den Na­men Quell-Dat­en (BSK).

Jet­zt wird Ih­nen wahrschein­lich auch klar sein, was in die­sem bei­den neu­en Abfra­gen ge­sche­hen wird. 😎 Fil­tern Sie jede der bei­den Abfra­gen so, dass nur noch die entsprechen­den Dat­en erhal­ten blei­ben. Um die Ab­fra­ge auf die let­zten 10 Wer­te (Zei­len) zu begren­zen, ge­hen Sie den Weg über Zei­len ver­ringern | Zei­len beibehal­ten | Let­zte Zei­len beibehal­ten und ge­ben Sie bei An­zahl von Zei­len den Wert 10 ein. Nach ei­nem OK blei­ben noch ge­nau 10 Zei­len üb­rig. Und dar­aus soll und wird der Medi­an berech­net wer­den. – Bei der an­de­ren Ver­weis-Abfrage ge­hen Sie gle­icher­maßen vor.

Ak­ti­vie­ren Sie nun die Ab­fra­ge Quell-Dat­en (NTG) und im Reg­is­ter Start Klick­en Sie auf das Sym­bol Kom­binieren. Erweit­ern Sie hier im Unter­menü Abfra­gen anfü­gen und wäh­len den Punkt Abfra­gen als neu anfü­gen. Im Dia­log-Fen­ster be­las­sen Sie es bei Zwei Ta­bel­len und wäh­len Sie als Anzufü­gende Ta­bel­le die an­de­re eben er­stell­te Ab­fra­ge: Quell-Dat­en (BSK). Da die Über­schriften bei­der Abfra­gen iden­tisch sind, wer­den die Dat­en der 2. Ab­fra­ge di­rekt an­ge­fügt/an­ge­hängt. Wie vor­ge­ge­ben wird eine neue Ab­fra­ge er­stellt, die den Na­men Ap­pen­d1 hat. Wenn Sie möcht­en, kön­nen Sie die­se umbe­nen­nen; ich be­las­se es bei die­sem Na­men, denn die­se Ab­fra­ge dient wie­der­um als Ba­sis für 3 weit­ere Ver­weis-Abfra­gen. Er­stel­len Sie also die­se 3 Ver­weise. Als Na­men für die Abfra­gen ver­ge­ben sie beispiel­sweise Medi­an As, Medi­an Pb und Medi­an Cd.

Am Bei­spiel der Ab­fra­ge Medi­an As zei­ge ich Ih­nen auf, wie sie bei al­len die­ser 3 Abfra­gen vorge­hen wer­den, um den Medi­an je Bere­ich und (ver­mut­lich) Schad­stoff zu berech­nen:

• Acht­en sie dar­auf, dass die Spal­te Re­gi­on mar­kiert ist.
• Grup­pieren nach Re­gi­on und bei Neu­er Spal­tenname ge­ben Sie Medi­an As (oder auch nur die chemis­che Kurzbeze­ich­nung für das die jew­eilige Ele­ment) ein.
• Bei Vor­gang wäh­len Sie im Drop­Down Medi­an.
• Bei Spal­te wäh­len Sie die pas­sen­de Über­schrift, also im ers­ten Fall As aus.
• Ge­hen Sie bei den rest­li­chen 2 Abfra­gen gle­icher­maßen vor, natür­lich auf die chemis­chen Ele­mente an­ge­passt.

Das Ergeb­nis ist eine 2‑spal­ti­ge Ab­fra­ge mit je ei­nem Ein­trag für die bei­den Regio­nen, die sich in der Ab­fra­ge Medi­an As so dar­stellt:

Die ers­te Berech­nung des Medi­an

Im­mer noch im Reg­is­ter Start ak­ti­vie­ren Sie die Ab­fra­ge Medi­an As. Im Men­üband ein Klick auf Kom­binieren, erweit­ern Sie den Punkt Abfra­gen zusam­men­führen  und wäh­len dort Abfra­gen als neue Ab­fra­ge zusam­men­führen. Im Dia­log wer­den sie im mit­ti­gen schma­len Text­feld die Aus­wahl Medi­an Pb tre­f­fen und an­schlie­ßend jew­eils in das Feld Re­gi­on Klick­en:

Zusam­men­führen der bei­den ers­ten Medi­an-Abfra­gen

Im­mer noch in der Ab­fra­ge Mer­ge1 wer­den Sie die­sen Vor­gang wieder­holen, nur dass Sie die­ses Mal den direk­ten Weg Kom­binieren | Abfra­gen zusam­men­führen wäh­len und natür­lich die Ab­fra­ge Medi­an Cd mit der zulet­zt erstell­ten Ab­fra­ge zusam­men­führen. Und das sieht jet­zt auch schon sehr gut aus. Von al­len 3 gemesse­nen Stof­fen wur­den für jede Re­gi­on die Medi­an-Werte berech­net.

Jet­zt Schlie­ßen & la­den und sie wer­den vielle­icht er­staunt sein, denn sie se­hen, dass sie nichts se­hen. 🙄 Es wur­de kei­ne Ta­bel­le in der Ex­cel-Ar­beits­map­pe er­stellt. Das liegt dar­an, dass auch die zulet­zt er­stell­te Ab­fra­ge Mer­ge1 im Mo­dus Nur Ver­bin­dung er­stellt wor­den ist. Die Lö­sung: Klick­en Sie im recht­en Seit­en­fen­ster mit der recht­en Maus­taste auf die­se Ab­fra­ge und wäh­len Sie La­den in … Hier Klick­en Sie auf die Op­ti­on Ta­bel­le und wäh­len Beste­hen­des Arbeits­blatt und Klick­en sie dann in das Ziel­feld bzw. tra­gen Sie die Adres­se von Hand ein, wo die Dat­en einge­tra­gen wer­den sol­len.

Das Ziel ist erre­icht und wenn die Quell-Dat­en spä­ter er­gänzt oder geän­dert wer­den ge­nügt ein Klick auf Aktu­al­isieren und die Dat­en der Ab­fra­ge wer­den automa­tisch an­ge­passt. Die­ser Weg ist in je­dem Fal­le ziel­füh­rend. Ins­beson­dere bei gro­ßen Daten­men­gen lohnt sich der et­was hö­he­re Auf­wand in je­dem Fall. – Mei­ne Lö­sung sen­de ich Ih­nen ger­ne per E‑Mail zu, wenn Sie mir den Dateina­men PQ-Her­ber-Me­di­an-127995.xlsx an­ge­ben.

Er­gän­zung: In ei­nem Schluss-State­ment leg­te der Fra­ge­stel­ler dar, dass es auch denk­bar sei, dass ein ander­er Zeit­bere­ich mit (wahrschein­lich) frü­he­rem En­de-Da­tum aus­gew­ertet wer­den soll. In ei­nem sol­chen Fall wäre es ein gu­ter Weg die Quell-Dat­en im ers­ten Schritt so zu fil­tern, dass nur noch kalen­darische Dat­en bis zum let­zten Tag erhal­ten blei­ben.

▲ nach oben …

Hat Ih­nen der Bei­trag ge­fal­len?
Er­leich­tert die­ser Bei­trag Ihre Ar­beit?

Dann wür­de ich mich über ei­nen Bei­trag Ihrer­seits freu­en …