Office: (Office 2019) Aus Wurzel von Rechts nach links zweistellige Zahl Selektieren < 70

Guten Tag, ich hoffe ihr könnt mir bitte Helfen, ich möchte wie es der Titel schon sagt,VON RECHTS nach LINKS immer die Stelle suchen welche kleiner als 70 ist allerdings sollte die Formel die Bereits eingetragen Zahlen in den Obigen Zellen erkennen und wenn eine Zahl bereits vorkommt um eine Stelle weiterspringen Sofern die Zahl unter 70 ist ansonsten müsste die Formel nochmalig um eine Stelle nach links springen oder um 2 oder drei bist der Wert wenn noch nicht vorhanden unter 70 eingetragen werden kann von der Formel.

Da ich lediglich nur die Wurzel als Formel habe nehme ich an das ein Bild reicht. Bitte um eure Ideen bzw Hilfe.

 

Bei 8 Nachkommastellen sind nur 490 Zahlen überhaupt möglich (Stelle 7-8 ... bis 1-2). Aber Du möchtest keine Wiederholer, so dass sogar nur 70 Zahlen möglich sind (denn Du möchtest 00-69 ohne Wdh. ausgeben).

Beim Nachstellen fiel mir auf, dass Deine Anzahl Nachkommastellen nicht fix ist. Das geht ja nun mal überhaupt nicht! Oder soll die Anzahl der signifikanten Stellen 10 sein? Das hast Du dann vergessen zu sagen.

 

Guten Tag lupo1 Sollte es denn möglich sein das man das Koma unberücksichtigt lässt würde es nicht stören
299 = 17,29161647 47 wenn nicht vorhanden ansonsten 64 wenn bereits vorhanden 16 wenn bereits vorhanden 61 wenn bereits vorhanden 16, da vorhanden 29 wenn vorhanden 17 sofern das Formel Technisch möglich ist.

 

Die Aufgabe hatte ich verstanden und Kontrollfragen gestellt!

 

Prinzipiell sind die Nachkommastellen nicht das Problem da die Zahlen aus denen die Wurzel gezogen wird ein Datum sind.
31.07.1974 165,048477727000
27
25.04.1983 174,444833686756
56
18.01.1992 183,360301046873
68
12.10.2000 191,861929522248
48 und es sollen immer nur 9 Zeilen sein Sorry also wenn in den denn nächsten 9 Zellen Wiederholungen sind von der ersten Auflistung ist das egal.

 

Ich spreche die ganze Zeit von der Wurzel, nicht von der Ausgangszahl.

 

191,861929522248 bei Datum habe ich 12 Nachkommastellen das sollte theoretisch für 9 Zellen ohne Wiederholen einer Zahl möglich sein, also kein Variieren der Nachkommastellen.

 

Formel für XL365 oder XLWeb (kostenlos für jeden!) ONLY!
Möglicherweise nicht ganz richtig, weil die Regelung der Nachkommastellen oder signifikanten Stellen noch nicht beschlossen ist.

 

Hammer Stark ! lupo1 - was ich bislang daran getestet habe sieht es so aus als würde es wahrlich genau die Ergebnisse bringen die ich mir Gewünscht habe. Hab Dank

 

Hallo Hummel,
nicht nur weil es (fast immer) schön ist, wie lupo hier hilft, erlaube mir eine Frage: Wozu in aller Welt braucht man solch eine Berechnung? Erläuterst Du einem neugierigen Bastler ein bisschen den Kontext?
Lieben Dank vorab!

 

Hallo Hummel,

auch wenn die Aufgabenstellung schon gelöst war und ist, mich hat einfach interesiert, ob es für Deine Problemstellung auch eine (vertretbare) Formellösung für die von Dir angegebene Excelversion 2019 bzw. noch älteren XL-Versionen gibt. Das ist der Fall und der Volständigkeit halber zeige ich diese deshalb auch auf.

Um nachfolgende Zellformel E2 überschaubar lang zu halten, habe ich dazu zuvor die unten aufgezeigte benannte Formel: R_Ziff (darin wäre der Formelteil mit der Funktion REST() nicht notwendig, wenn immer nur Datumswerte auszuwerten sind) definiert und zwar bei aktivierter Zelle E2.

Die Auswertung mit der Formellösung erfolgt gemäß Deinen Vorgaben für exakt 12 Nachkommastellen der Wurzelwerte der auszuwertenden Datumswerte.

Wenn mehr Datumswerte als nur 9 in der Datumsspalte ausgewertet werden sollen und die Verhinderung von Wiederholungszahlen der Ergebniswerte (im nachfolgenden Beispiel z.B. in E7) nach den 9 Zeilen wieder neu beginnend gelten soll, dann müßte in der Zellformel nur noch der Zellbezug E$1 noch durch einen Formelteil ersetzt werden. Kann ich bei Bedarf noch ergänzen.

Formel E2 einfach ziehend nach unten kopieren. Die Werte der Spalte H mit deren Formel sind nicht notwendig, sie dient(e) lediglich zu visuellen Kontrollzwecken.

Arbeitsblatt mit dem Namen 'Tabelle1'
	D	E	F	G	H
1	Datum	Erg			Datums-Wurzelwert
2	22.07.2023	9			212,435872676909
3	11.07.2021	53			210,684598392953
4	01.07.2019	11			208,918644452811
5	20.06.2017	64			207,137635402164
6	10.06.2015	37			205,341179503771
7	30.05.2013	33			203,528867731337
8	20.05.2011	17			201,700272682017
9	09.05.2009	58			199,854947399358
10	29.04.2007	48			197,992424097489
11

Name	Bezug
R_Ziff	=TEIL(TEXT(REST(!C1^0,5;1);"0,000000000000");14-ZEILE(!C$1:C$11);2)

Zelle	Formel
E2	=INDEX((--R_Ziff<70)*R_Ziff;AGGREGAT(15;6;ZEILE(D$1:D$11)/(--R_Ziff<70)/(ZÄHLENWENN(E$1:E1;R_Ziff)=0);1))
H2	=D2^0,5

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

 

Mit ZÄHLENWENN war ich übrigens auch zuerst im Gange gewesen.

=LET(i;SEQUENZ(;14;14;-1);x;--TEIL(WECHSELN(B2;",";);i;2);y;WENN(x>69;;x);INDEX(y;VERGLEICH(99;WENNFEHLER(VERGLEICH(y;C1:C$1;);99);)))
noch etwas abgekürzt:
=LET(x;--TEIL(WECHSELN(B2;",";);SEQUENZ(;14;14;-1);2);y;(x<70)*x;INDEX(y;VERGLEICH(;WENNFEHLER(VERGLEICH(y;C1:C$1;););)))

 

Hallo Hummel,

habe durch den neuen Beitrag von lupo1 eben erst #3 (Deinen 2. Beitrag) richtig gelesen. Danach sollen ja bei Bedarf auch die Vorkomma-Zahlenwerten mit ausgewertet werden. Dies dürfte (aber nur) bei bestimmten Datumswerten aus dem Jahr 1900 (siehe untenstehend) von Belang sein. Habe deswegen nun trotzdem meine Formel entsprechend angepaßt und dabei auch gleich mit berücksichtigt, daß Wiederholungsergebnisse lediglich in einem Bereich von Datumswerten ausgewertet werden, die nicht durch Leerzellen getrennt sind (dafür habe ich eine 2. benannte Formel ergänzt, die ebenfalls bei aktivierter Zelle E2 zuvor zu definieren ist).

Arbeitsblatt mit dem Namen 'Tabelle1'
	D	E	F	G	H
1	Datum	ErgZ			Datums-Wurzelwert
2	22.07.2023	9			212,435872676909
3	12.09.1900	16			16,000000000000
4	11.07.2021	53			210,684598392953
5	01.07.1998	37			189,676039604374
6	20.06.2017	64			207,137635402164
7	10.06.2015	3			205,341179503771
8	30.05.2013	33			203,528867731337
9	01.01.1900	1			1,000000000000
10	12.09.1900	0			16,000000000000
11
12	22.07.2023	9			212,435872676909
13	04.01.1900	2			2,000000000000
14	04.01.1900	0			2,000000000000
15	16.01.1900	4			4,000000000000
16	12.09.1900	16			16,000000000000
17	30.05.2013	37			203,528867731337
18	01.07.1998	43			189,676039604374
19	20.06.1996	63			187,712546197637
20	12.09.1900	1			16,000000000000
21

Name	Bezug
Pr_Wh	=ZÄHLENWENN(INDEX(!D$1:D1;VERWEIS(9;1/NICHT(ISTZAHL(!C$1:C1));ZEILE(!D$1:D1048576))):!D1048576;R_Zn)=0
R_Zn	=TEIL(TEXT(WECHSELN(!C1^0,5;",";"");"000000000000000");15-ZEILE(!C$1:C$14);2)

Zelle	Formel
E2	=WENNFEHLER(INDEX((--R_Zn<70)*R_Zn;AGGREGAT(15;6;ZEILE(D$1:D$14)/(--R_Zn<70)/Pr_Wh;1));"")
H2	=D2^0,5

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

 

Hallo nochmal,

sah gerade, dass das Tool Tab2Html eine "verzerrte" Ausgabe der benannte Formeln ausgibt, wenn man die linke obere Ecke des dargestellten Bereichsausschnittes nicht identisch ist mit der Formelzelle, für die die Formeln definiert sind.
Die Definition der benannten Formeln für die Zelle E2 lautet dafür bei mir so:

Arbeitsblatt mit dem Namen 'Tabelle1'
	E
2	9

Name	Bezug
Pr_Wh	=ZÄHLENWENN(INDEX(!E$1:E2;VERWEIS(9;1/NICHT(ISTZAHL(!D$1:D2));ZEILE(!E$1:E1))):!E1;R_Zn)=0
R_Zn	=TEIL(TEXT(WECHSELN(!D2^0,5;",";"");"000000000000000");15-ZEILE(!D$1:D$14);2)

Zelle	Formel
E2	=WENNFEHLER(INDEX((--R_Zn<70)*R_Zn;AGGREGAT(15;6;ZEILE(D$1:D$14)/(--R_Zn<70)/Pr_Wh;1));"")

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

(Nur) die Zellformel ist nach unten ziehend zu kopieren.