Office: Filterkriterium erstellen

Hallo zusammen,

hat einer von Euch eine Idee wie ich folgendes Problem lösen kann?
In einem Excelsheet habe ich Messwerte (in Spalte A  Zeitstempel, in Spalte B  Messwerte) die gefiltert werden müssen und folgendes Filterkriterium muss genutzt werden:

-In einem 10 min Intervall darf der maximalste Messwerte nicht mehr als 10 % vom minimalsten Messwert abweichen

Nun wollte ich das irgendwie in der Spalte C darstellen, bloß habe ich keine Idee wie ich das lösen soll.
Dazu sei noch erwähnt, dass die Zeitstempel Lücken aufweisen können.

Zeit Messwert
08:57:17 400
08:57:47 402
08:58:17 403
08:58:47 404
08:59:17 406
08:59:47 409
09:00:16 410
09:00:47 411
09:01:17 414
09:01:47 414
09:02:17 417
09:02:47 420
09:03:17 422
09:03:47 424
09:04:17 426
09:04:47 427
09:05:17 429
09:05:47 432
09:06:17 433
09:06:47 435
09:07:17 437
09:07:47 438
09:08:17 439
09:08:47 441
09:09:17 444
09:09:47 445

Hat einer eine Idee?

Schöne Grüße

 

Es gibt bestimmt eine Formel-Lösung die irgendwas ganz Wildes aus Index, Indirekt, Vergleich, sVerweis und Konsorten über eine Matrix-Formel erledigt. ;-) Da mir irgendwie überhaupt nix in der Richtung einfallen wollte, hab ich mir das mal per VBA angesehen:

Code:

Option Explicit

Public Function Intervall_Minimum(c As Range, i1 As Date, i2 As Date) As Variant

    Dim cell As Range
    Dim r As Variant

    r = 999

    For Each cell In c

        If ((cell.Value >= i1) And (cell.Value <= i2)) Then
            If (cell.Offset(0, 1).Value < r) Then
                r = cell.Offset(0, 1).Value
            End If
        End If

    Next cell

    Intervall_Minimum = r

End Function

Public Function Intervall_Maximum(c As Range, i1 As Date, i2 As Date) As Variant

    Dim cell As Range
    Dim r As Variant

    r = 0

    For Each cell In c

        If ((cell.Value >= i1) And (cell.Value <= i2)) Then
            If (cell.Offset(0, 1).Value > r) Then
                r = cell.Offset(0, 1).Value
            End If
        End If

    Next cell

    Intervall_Maximum = r

End Function

Public Function Intervall_Abweichung_Prozent(c As Range, i1 As Date, i2 As Date) As Variant

    Dim r As Variant
    Dim v1 As Variant
    Dim v2 As Variant

    v1 = Intervall_Minimum(c, i1, i2)
    v2 = Intervall_Maximum(c, i1, i2)

    r = 100 / v1 * (v2 - v1)

    Intervall_Abweichung_Prozent = r

End Function

Wenn Du das in ein "allgemeines" Modul packst, hast Du die 3 Routinen

zur Verfügung. Als Parameter erwarten die alle

Die Formel

Code:

C2: =intervall_abweichung_prozent(A$2:A$27;A2-(10/1440);A2)

berechnet z.B. über die Zeitstempel in A2:A27 und die dazugehörigen Meßwerte in B2:B27 die Abweichung zwischen Min. und Max. wobei nur die Werte zwischen dem aktuellen Meßwert und den Werten, die 10 Minuten davor lagen, berücksichtigt werden.

"10 Minuten davor" = A2-(10/1440) weil der Tag 1440 Minuten hat und ich vom Zeitstempel des aktuellen Meßwerts eben 10/1440 abziehe. Soll der aktuelle Meßwert "mittig" im +/- 5 Minuten Intervall liegen, geht auch:

Code:

C2: =intervall_abweichung_prozent(A$2:A$27;A2-(5/1440);A2+(5/1440))

Gruß, Michael

 

Hallo miriki,

vielen Dank für deine Antwort und den Code.
Nun möchte ich dich trotzdem fragen, ob sich dieses Filterkriterium irgendwie in eine Formel gießen lässt, da ich ähnliche variable Filterkriterien für andere Werte erstellen muss und ich ehrlich gesagt nicht gerade fit in VBA bin.

Ich hoffe sehr das du mir hier nochmal helfen könntest.

Schöne Grüße
Tauchmann

 

Was meinst Du jetzt damit? Eine Lösung ohne VBA? Da muß ich weiter reichen an z.B. Schatzi.

Gruß, Michael

 

Mein Ansatz ist vielleicht ja völlig verkehrt, aber ich scheiter bereits im Ansatz, wenn ich es per Excel-Formeln versuche. Wie ich versucht habe, an die Sache zu gehen:

Um die Abweichung in % zu berechnen, muß ich Min und Max bestimmen. Um die zu bekommen, muß ich bestimmen, in welchen Zeilen (von..bis) ich die Werte suchen muß. Dazu wiederum muß ich die Zeit-Grenzen bestimmen.

Also habe ich mal, Deine Werte mit Überschriften in A1:B27 angenommen, in D2 und E2 Unter- und Ober-Grenze des Zeitraums bestimmt:
D2: =A2-(5/1440)
E2: =A2+(5/1440)
In Zeile 15 kommen dabei heraus:
08:58:47 und 09:08:47
Manuell mal durchgeschaut sieht man, daß die gewünschten Werte in den Zeilen 5 bis 25 stehen, vorausgesetzt, die Zeitgrenzen sind "inklusive".

Tja, und jetzt der Versuch, die Werte 5 und 25 per Formel zu bekommen... Mein erster Gedanke war Vergleich(), aber der scheitert leider aus mindestens einem Grund:
G2: =VERGLEICH(D2;$A$2:$A$27;-1)
H2: =VERGLEICH(E2;$A$2:$A$27;1)
Diese Formeln ergeben in Zeile 15 die Werte #NV und 23.

Das #NV kommt primär daher, daß bei -1 Vergleich die Liste absteigend sortiert sein muß. Also wird man damit nix. Und auch die 23 ist nicht ganz korrekt, da der gewünschte Wert 24 wäre. Das scheint aber vielleicht an einem Rundungs-Problem zu liegen, denn auch in Zeile 14 kommt 23 heraus.

Auch ein Versuch mit
J2: =VERGLEICH(D2;$A$2:$A$27;0)
K2: =VERGLEICH(E2;$A$2:$A$27;0)
scheitert, weil es immer wieder #NV gibt, selbst wenn die Werte vermeintlich vorhanden sind. Also auch hier wohl wieder ein Rundungs-Problem. Aber da die Werte auch Lücken haben können, scheidet der 0 Vergleich sowieso aus.

Von daher... fehlt mir da auch eine Idee für weiteres Vorgehen. Aber da gibt's ganbz bestimmt irgendwas...

Gruß, Michael

 

Grüezi zusammen

Ich würde hier auch ein paar Hilfsspalten (3 um genau zu sein) einrichten um das Intervall individuell zu bestimmen und dann auch das Minimum pro Intervall und anschliessend das Krtierium für den Filter.
Die Zelle H1 gibt das Zeitintervall vor, der Rest geschieht mit den angegebenen Formeln dann von 'alleine':

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H	I
1	Zeit	Messwert	Zeit_Intervall	Min/Intervall	Filter		Intervall	00:10
2	08:57:17	400	08:50:00	400	WAHR
3	08:57:47	402	08:50:00	400	WAHR
4	08:58:17	403	08:50:00	400	WAHR
5	08:58:47	404	08:50:00	400	WAHR
6	08:59:17	406	08:50:00	400	WAHR
7	08:59:47	409	08:50:00	400	WAHR
8	09:00:16	410	09:00:00	410	WAHR
9	09:00:47	411	09:00:00	410	WAHR
10	09:01:17	414	09:00:00	410	WAHR
11	09:01:47	414	09:00:00	410	WAHR
12	09:02:17	417	09:00:00	410	WAHR
13	09:02:47	420	09:00:00	410	WAHR
14	09:03:17	422	09:00:00	410	WAHR
15	09:03:47	424	09:00:00	410	WAHR
16	09:04:17	426	09:00:00	410	WAHR
17	09:04:47	427	09:00:00	410	WAHR
18	09:05:17	429	09:00:00	410	WAHR
19	09:05:47	432	09:00:00	410	WAHR
20	09:06:17	433	09:00:00	410	WAHR
21	09:06:47	435	09:00:00	410	WAHR
22	09:07:17	437	09:00:00	410	WAHR
23	09:07:47	438	09:00:00	410	WAHR
24	09:08:17	439	09:00:00	410	WAHR
25	09:08:47	441	09:00:00	410	WAHR
26	09:09:17	444	09:00:00	410	WAHR
27	09:09:47	460	09:00:00	410	FALSCH
28

Zelle	Formel
C2	=ABRUNDEN(A2/$H$1;0)*$H$1
D2	{=MIN(WENN($C$2:$C$101=$C2;$B$2:$B$101;""))}
E2	=B2<=D2*110%

<table><tr><td>Achtung, Matrixformel enthalten!</td></tr><tr><td><span>Die geschweiften Klammern{} werden </span><span>nicht</span><span> eingegeben.</span></td></tr><tr><td><span>Verlassen Sie den Zelleneditor mit </span><span>Strg+Shift + Enter</span><span>, statt Enter alleine.</span></td></tr></table>[/parsehtml]

 

Hmmm... Das Zeitintervall in festen Blöcken vorgeben... So hätte ich das nicht herausgelesen / verstanden.

Ich war davon ausgegangen, daß die 10 Minuten "gleitend" um den jeweiligen Meßwert herum liegen. Und mit der Sichtweise hab ich's nicht hinbekommen.

Bei diesen "Blöcken" seh ich ein bißchen das Problem, daß "Sprünge" nicht erkannt werden:
0:00:00 100
0:01:00 101
[...]
0:09:00 109
0:10:00 115
[...]
0:19:00 124
[...]
Im ersten Intervall 0:00:00..0:09:00 würde nur eine Steigerung von 9% gesehen werden. Würde man aber die 10-Minuten-Spanne 0:01:00..0:10:00 betrachten, würde ein Sprung um fast 14% erkennbar, auch wenn in der Spanne 0:10:00..0:19:00 wieder nur eine Abweichung von knapp 8% wäre.

tricky...

Gruß, Michael

 

Grüezi Michael

Meiner Ansicht nach kann das nur dann klappen, wenn die Intervalle fix in Blöcken vorgegeben werden.

Sind sie gleitend, dann ist ein einzelner Wert ja in mehreren Intervallen enthalten - für welches soll denn dann die Abweichung von >110% des minimalen Wertes gelten - für eines oder für alle?


Das alleine kann aber nur Tauchmann beantworten.

 

Hallo zusammen,

vielen Dank für eure Antworten und Ideen zu dem Thema. Sorry für meine späte Antwort, war unterwegs. Basierend auf euren Ideen habe ich folgenden Vorschlag produziert, der leider nicht ganz funktioniert aber gut ausschaut (aus meiner Sicht jedenfalls). Könnt Ihr mir bitte eure Meinung und Idee schicken?

Aber erst mal möchte ich Eure Fragen beantworten und warum ich das brauche:

Ich habe Einstrahlungsdaten die gefiltert werden müssen, um einen Referenzwert zu bestimmen. Dazu gibt es insgesamt 6 Filterkriterien, wovon 4 einfachste WENN Bedingungen sind und eine Zeitabfrage-1 (Thomas kennt die schon) ( http://www.office-hilfe.com/support/topic,16701,-excel-macht-komische-berechnung.html#65552 ). Das Problem mit der Zeitabfrage-1 wurde gelöst.

Das Problem ist nun die Zeitabfrage-2, die hier diskutiert wird. Das Tool was ich bauen möchte soll universell einsetzbar sein, d.h. die Zeitstempel sollen individuell sein. Ich möchte 10 sec Werte sowie Minutenwerte verwenden können und das Tool soll das erkennen.

Der Wortlaut des krassen Filterkriteriums lautet:

Alle Daten sind zu verwerfen:
- In einem Zeitraum von 10 min nach einer Schwankung zwischen dem in diesen 10 min aufgezeichneten Höchst- und Kleinstwert der Bestrahlungsstärke um mehr als 10%.

@ Thomas: Die Intervalle können nicht fix sein, weil die anderen Filter vorgeschaltet sind und somit ich nicht weiß welche Werte rausfliegen (hat sich erst heute ergeben).

Die folgende Tabelle ist das Ergebnis der ersten notwendigen Filterung.
Meine Idee dazu ist:
1. Schritt: das ich den Zeitstempel + 10 min nehme, um den Zeitbereich zu bestimmen.
2. Schritt: Bestimmung der Zellenposition des Zeitstempels Anfang und Zeitstempels Ende.
3. Schritt: Mit BEREICH.VERSCHIEBEN den Min und Max Wert bestimmen mittels den Zellenpositionen aus Schritt 2

Leider habe ich das Problem, das wenn der rechnerische Zeitstempel +10 min bereits am Anfang herausgefiltert wurde und nicht in der Tabelle auftaucht ein #NV erscheint. Es muss also nun noch irgendwie geschafft werden, wenn der Wert #NV erscheint, soll die Abfrage den nächst kleineren Wert <10 min nehmen (z.B. siehe grün markierte Zeilen in Zeile 4 und 12).

Hat einer eine Idee wie man das machen kann?

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Uhrzeit

Datum

Datetime

ENG

Duration Min 10 min

Duration Max 10 min

Zelle Min

Zelle Max

Min

Max

2

10,00

3

08:57:47

26.07.2011

26.07.2011 08:57:47

402

26.07.2011 08:57:47

26.07.2011 09:07:47

$C$3

$C$10

402

438

8,22%

Okay

4

08:59:17

26.07.2011

26.07.2011 08:59:17

406

26.07.2011 08:59:17

26.07.2011 09:09:17

$C$4

#NV

5

09:01:47

26.07.2011

26.07.2011 09:01:47

414

26.07.2011 09:01:47

26.07.2011 09:11:47

$C$5

#NV

6

09:04:17

26.07.2011

26.07.2011 09:04:17

420

26.07.2011 09:04:17

26.07.2011 09:14:17

$C$6

#NV

7

09:05:17

26.07.2011

26.07.2011 09:05:17

429

26.07.2011 09:05:17

26.07.2011 09:15:17

8

09:05:47

26.07.2011

26.07.2011 09:05:47

432

26.07.2011 09:05:47

26.07.2011 09:15:47

9

09:07:17

26.07.2011

26.07.2011 09:07:17

437

26.07.2011 09:07:17

26.07.2011 09:17:17

10

09:07:47

26.07.2011

26.07.2011 09:07:47

438

26.07.2011 09:07:47

26.07.2011 09:17:47

11

09:08:17

26.07.2011

26.07.2011 09:08:17

439

26.07.2011 09:08:17

26.07.2011 09:18:17

12

09:08:47

26.07.2011

26.07.2011 09:08:47

441

26.07.2011 09:08:47

26.07.2011 09:18:47

13

09:12:17

26.07.2011

26.07.2011 09:12:17

452

26.07.2011 09:12:17

26.07.2011 09:22:17

14

09:14:17

26.07.2011

26.07.2011 09:14:17

460

26.07.2011 09:14:17

26.07.2011 09:24:17

15

09:14:47

26.07.2011

26.07.2011 09:14:47

462

26.07.2011 09:14:47

26.07.2011 09:24:47

16

09:15:17

26.07.2011

26.07.2011 09:15:17

464

26.07.2011 09:15:17

26.07.2011 09:25:17

17

09:15:47

26.07.2011

26.07.2011 09:15:47

466

26.07.2011 09:15:47

26.07.2011 09:25:47

18

09:17:17

26.07.2011

26.07.2011 09:17:17

472

26.07.2011 09:17:17

26.07.2011 09:27:17

Zelle	Formel
C3	=DATUM(JAHR(B3);MONAT(B3);TAG(B3))+ZEIT(STUNDE(A3);MINUTE(A3);SEKUNDE(A3))
F3	=C3
G3	=F3+ZEIT(;$G$2;)
H3	=ZELLE("adresse";INDEX($C:$C;VERGLEICH($F3;$C:$C;0)))
I3	=ZELLE("adresse";INDEX($C:$C;VERGLEICH($G3;$C:$C;0)))
J3	=MIN(BEREICH.VERSCHIEBEN(INDIREKT(H3):INDIREKT(I3);;1;;))
K3	=MAX(BEREICH.VERSCHIEBEN(INDIREKT(H3):INDIREKT(I3);;1;;))
L3	=ABS(100%-J3/K3)
M3	=WENN(L3<=10;"Okay";"Bad")
C4	=DATUM(JAHR(B4);MONAT(B4);TAG(B4))+ZEIT(STUNDE(A4);MINUTE(A4);SEKUNDE(A4))
F4	=C4
G4	=F4+ZEIT(;$G$2;)
H4	=ZELLE("adresse";INDEX($C:$C;VERGLEICH($F4;$C:$C;0)))
I4	=ZELLE("adresse";INDEX($A:$A;VERGLEICH(RUNDEN($G4;10);$A:$A;0)))
C5	=DATUM(JAHR(B5);MONAT(B5);TAG(B5))+ZEIT(STUNDE(A5);MINUTE(A5);SEKUNDE(A5))
F5	=C5
G5	=F5+ZEIT(;$G$2;)
H5	=ZELLE("adresse";INDEX($C:$C;VERGLEICH($F5;$C:$C;0)))
I5	=ZELLE("adresse";INDEX($A:$A;VERGLEICH($G5;$A:$A;0)))
C6	=DATUM(JAHR(B6);MONAT(B6);TAG(B6))+ZEIT(STUNDE(A6);MINUTE(A6);SEKUNDE(A6))
F6	=C6
G6	=F6+ZEIT(;$G$2;)
H6	=ZELLE("adresse";INDEX($C:$C;VERGLEICH($F6;$C:$C;0)))
I6	=ZELLE("adresse";INDEX($A:$A;VERGLEICH($G6;$A:$A;0)))
C7	=DATUM(JAHR(B7);MONAT(B7);TAG(B7))+ZEIT(STUNDE(A7);MINUTE(A7);SEKUNDE(A7))
F7	=C7
G7	=F7+ZEIT(;$G$2;)
C8	=DATUM(JAHR(B8);MONAT(B8);TAG(B8))+ZEIT(STUNDE(A8);MINUTE(A8);SEKUNDE(A8))
F8	=C8
G8	=F8+ZEIT(;$G$2;)
C9	=DATUM(JAHR(B9);MONAT(B9);TAG(B9))+ZEIT(STUNDE(A9);MINUTE(A9);SEKUNDE(A9))
F9	=C9
G9	=F9+ZEIT(;$G$2;)
C10	=DATUM(JAHR(B10);MONAT(B10);TAG(B10))+ZEIT(STUNDE(A10);MINUTE(A10);SEKUNDE(A10))
F10	=C10
G10	=F10+ZEIT(;$G$2;)
C11	=DATUM(JAHR(B11);MONAT(B11);TAG(B11))+ZEIT(STUNDE(A11);MINUTE(A11);SEKUNDE(A11))
F11	=C11
G11	=F11+ZEIT(;$G$2;)
C12	=DATUM(JAHR(B12);MONAT(B12);TAG(B12))+ZEIT(STUNDE(A12);MINUTE(A12);SEKUNDE(A12))
F12	=C12
G12	=F12+ZEIT(;$G$2;)
C13	=DATUM(JAHR(B13);MONAT(B13);TAG(B13))+ZEIT(STUNDE(A13);MINUTE(A13);SEKUNDE(A13))
F13	=C13
G13	=F13+ZEIT(;$G$2;)
C14	=DATUM(JAHR(B14);MONAT(B14);TAG(B14))+ZEIT(STUNDE(A14);MINUTE(A14);SEKUNDE(A14))
F14	=C14
G14	=F14+ZEIT(;$G$2;)
C15	=DATUM(JAHR(B15);MONAT(B15);TAG(B15))+ZEIT(STUNDE(A15);MINUTE(A15);SEKUNDE(A15))
F15	=C15
G15	=F15+ZEIT(;$G$2;)
C16	=DATUM(JAHR(B16);MONAT(B16);TAG(B16))+ZEIT(STUNDE(A16);MINUTE(A16);SEKUNDE(A16))
F16	=C16
G16	=F16+ZEIT(;$G$2;)
C17	=DATUM(JAHR(B17);MONAT(B17);TAG(B17))+ZEIT(STUNDE(A17);MINUTE(A17);SEKUNDE(A17))
F17	=C17
G17	=F17+ZEIT(;$G$2;)
C18	=DATUM(JAHR(B18);MONAT(B18);TAG(B18))+ZEIT(STUNDE(A18);MINUTE(A18);SEKUNDE(A18))
F18	=C18
G18	=F18+ZEIT(;$G$2;)

Schöne Grüße
Tauchmann

 

Örks...

Ich hab den jetzt mehrmals gelesen, aber irgendwie... hab ich den Eindruck, daß da noch eine Spezifikation fehlt...

Wenn ich mal versuch, das Ding auseinander zu pflücken:

"Zeitraum von 10 min nach einer Schwankung" - Ok, also muß ich zuerst herausfinden, wann genau die Schwankung aufgetreten ist.

"Schwankung zwischen dem in diesen 10 min ..." - Ähm... in welchen 10 Min. jetzt? Ich würde es erstmal so verstehen:
a) nehme den aktuellen Zeitstempel
b) bestimme Zeitstempel "max. -10 Minuten"
c) bestimme Min und Max der Meßwerte zwischen den beiden Zeitstempeln
--> Deine Beispiel-Liste sagt es nicht so ganz aus, aber die Meßwerte können wirklich schwanken, oder? In Deiner Liste waren die recht konstant ansteigend. Die können aber auch abfallen? Sprich: Der "älteste" Wert ist nicht zwangsläufig auch der "kleinste" Wert, oder?
d) wenn Abweichung größer 10%, dann wird was verworfen, sonst geht's normal weiter mit dem nächsten Meßwert.

Tja, und dann steht da wieder der erste Teil: "Alle Daten sind zu verwerfen: - In einem Zeitraum von 10 min nach einer Schwankung [...]"
Also werden, wenn im aktuellen Meßwert eine Schwankung innerhalb der letzten 10 Minuten von mehr als 10% festgestellt wurde, die Meßwerte der nächsten 10 Minuten verworfen.

Hmmm... Scheint mir irgendwie erstmal nicht logisch... Aber anders krieg ich diese Formulierung nicht verstanden. Allerdings ist bei dieser Interpretation auch wiederum "10 Minuten in die Vergangenheit" nicht wirklich sauber abgedeckt. Mir fehlt irgendwie die Spezifikation für "in diesen 10 min".

Gruß, Michael

 

--------------------------------------------------------------------------------
Hallo Michael,

genau so sehe ich das auch. Einfach nur eine blöde Formulierung aus einer DIN.
Wie auch immer, ich habe jetzt einen Filter so gebaut das ich was brauchbares rausbekomme und nicht 100% der Beschreibung passt.

Vielen Dank für die Hilfen
Tauchmann