Office: Zeilen mit Zahlen zwischen #NV-Werten finden

Hallo, allerseits!

Ich habe ein Blatt, das sich aus Werten eines anderen Blatts u.a. mit WENN-Formeln zusammensetzt. Dabei werden #NV-Werte gesetzt, wenn keine passenden Daten gefunden werden. Das ganze sieht dann grob so aus:

Code:

   |  A    B
---+----+----
 3 |  0   #NV
 4 |  1   #NV
 . |
12 |  9   #NV
13 | 10   4,2
 . |
20 | 17   0,5
21 | 18   #NV
 . |
56 | 53   #NV

In Spalte A sind die Kalenderwochen aufgelistet, in den Spalten B bis AS rechts daneben sind diverse Zahlen als Zusammenfassung aus tagesaktuellen Zahlen in einem anderen Blatt zu finden. Aus dem ganzen Geraffel resultiert dann ein Wust an Grafiken.

Um die Grafiken dynamisch anzupassen, bräuchte ich jetzt eine Funktion, die den jeweils 1. und letzten Werte in Spalte B findet, der nicht #NV ist. Im obigen Beispiel wären das die Zeilen 13 bis 20 (bzw. KW 10 bis 17).

Geht das überhaupt ohne VBA? Bislang habe ich die Mappe VBA-frei gehalten und würde das nach Möglichkeit auch gerne weiterhin tun.

Ach so: Das Zusammenfassungs-Blatt ist ausgeblendet und nur das Blatt mit den Tages-Zahlen ist für den Aufrufenden sicht- und editier-bar.

Die Wahrscheinlichkeit eines #NV-Werts innerhalb des Blocks mit normalen Zahlen ist als verschwindend gering anzusehen. Das würde bedeuten, daß eine komplette Woche lang niemand auch nur eine einzige Messung gemacht hätte.

Gruß, Michael

 

Hallo Michael,

vieleicht so?

	D	E	F
1	#NV
2	30
3	#NV
4	#NV
5	20
6	#NV
7
8		30	20

Zelle	Formel
E8	=VERWEIS(9^9;INDIREKT("D1:D"&VERGLEICH(9^9;D:D)-1))
F8	=VERWEIS(9^9;D:D)

lg, rene

 

Hmmm... Nicht ganz... Wenn die Formeln die Werte 2 und 5 ausgeben würden, dann ja... ;-)

Ich hab mich vielleicht etwas undeutlich ausgedrückt: Ich brauche die Nummer der Zeilen, nicht die Werte an sich. Mit den Angaben der Zeilen kann der Bereich des Diagramms dann gesetzt werden.

Gruß, Michael

 

Na dann spiele mal mit VERWEIS und VERGLEICH!

VERWEIS gibt den WERT zurück und VERGLEICH die Zellnummer!

Dies wäre dann für F8 die Formel

Code:

=INDIREKT("D"&VERGEICH(9^9;D:D))

lg, rene

 

Ok, fast... ich hab jetzt in

Code:

E9 =VERGLEICH(9^9;INDIREKT("D1:D"&VERGLEICH(9^9;D:D)-1))
F9 =VERGLEICH(9^9;D:D)

In der Tat ergeben sich jetzt die Werte 2 und 5. Wir nähern uns... Aber:

Jetzt ändere ich mal den Wert #NV in D3 in den Wert 30... Schwupps, gibt mir E9 jetzt den Wert 3 aus, womit Zeile 2, in der sich ebenfalls der Wert 30 befindet, unterschlagen wird. Hmmm...

Das Problem mit der Liste der Werte ist, daß sie weder sortiert ist (MIN, MAX fallen aus) noch aus eindeutigen Werten besteht, sondern auch Dubletten beinhalten kann.

Allerdings: Die Spalte A mit der Auflistung der Kalenderwochen ist sowohl sortiert wie auch mit eindeutigen Werten gefüllt. Ich könnte mir was mit Bereich.Verschieben() oder so vorstellen. Aber so der rechte Ansatz will mir dazu nicht einfallen.

Kriegt man das trotzdem hin?

Gruß, Michael

 

Hallo Michael!

Ginge es vielleicht so?

	A	B
1	#NV	2
2	30	8
3	30
4	#NV
5	#NV
6	#NV
7	10
8	10
9	#NV

Zelle	Formel
B1	{=MIN(WENN(ISTZAHL(A1:A1000);ZEILE(A1:A1000)))}
B2	{=MAX(WENN(ISTZAHL(A1:A1000);ZEILE(A1:A1000)))}

<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]

 

oder für F9

Code:

=VERGLEICH(WAHR;ISTZAHL(D1:D100);0)

Matrixformel!!!!
lg, rene

 

Hmmm... Zumindest bei allen mir gerade so einfallenden Kombinationen aus Zahlen, die ich im Bereich der Zeilen 2 bis 8 verteilt habe, scheint das stur gleichbleibend auch die Werte 2 und 8 auszugeben.

Eine Lösung ohne Matrix-Formel wäre mir zwar lieber, weil ich die Dinger immer recht unübersichtlich und verwirrend finde, aber wenn es das Mittel der Stunde ist, dann soll es nunmal so sein.

Wow, eine nette Abkürzung, sehr stilvoll. Die kkleinste ist zwar verständlicher, gerade im Paar mit dem kgrösste, aber das Ding hier hat echt auch was.

Danke euch beiden für eure Mühe! Ich denke, ich hab jetzt was, was ich entsprechend adaptieren kann. Und in einer lauschigen Stunde versuch ich auch mal genauer, das mit dem 9^9 zu verstehen. ;-)

Gruß, Michael

 

...und wenn Du als Ergebnis die Kalenderwoche aus Spalte A haben möchtest, dann so:

	A	B	C
1	23	#NV	24
2	24	30	30
3	25	30
4	26	#NV
5	27	#NV
6	28	#NV
7	29	10
8	30	10
9	31	#NV

Zelle	Formel
C1	{=INDEX(A1:A1000;MIN(WENN(ISTZAHL(B1:B1000);ZEILE(A1:A1000))))}
C2	{=INDEX(A1:A1000;MAX(WENN(ISTZAHL(B1:B1000);ZEILE(A1:A1000))))}

<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]

 

In diesem Fall nicht, da die Ergebnisse in B..AS ja bereits in Abhängigkeit der Kalenderwoche, also Spalte A, gebildet werden. Spalte A ist dann auch die Beschriftung der x-Achse im Diagramm.

Aber danke trotzdem für den Hinweis.

Gruß, Michael