Live-Forum - Die aktuellen Beiträge
Datum
Titel
23.04.2024 08:59:38
Bedingte Formatierung
23.04.2024 08:56:21
Mehrzeilige Zellen in einzelne Zeilen aufteilen & auswer
23.04.2024 08:26:15
Rückgabewert mehrere Suchkriterien Datumsbereich
23.04.2024 08:20:37
VBA: Blatt als pdf per Mail. Blatt mit anderen pdfs vereinen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1564to1568
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1564to1568
1564to1568
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Ungenauen Wert auslesen und zuordnen

Ungenauen Wert auslesen und zuordnen
06.07.2017 21:10:38
Reinhard
Hallo zusammen,
nach Tagen der frustranen Suche nach einer Lösung möchte ich auf diesem Weg Hilfe suchen.
Ich habe das Geburtsgewicht und das Alter (in Form von Schwangerschaftswochen) eines Neugeborenen und möchte anhand einer vorgegebenen Tabelle mit Werten den dazugehörigen ungefähren Perzentilenwert ablesen. Wie muss die Excel-Formel lauten? (bitte kein VBA)
Die Tabelle mit einem Beispiel findet Ihr hier:

Die Datei https://www.herber.de/bbs/user/114731.xlsx wurde aus Datenschutzgründen gelöscht

Viele Grüße
Reinhard

35
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: z.B. mit INDEX() und AGGREGAT() ...
06.07.2017 21:41:47
...
Hallo Reinhard,
... so: 

=INDEX(11:11;AGGREGAT(15;6;SPALTE(C1:P1)/(C13:J43>=B6)/(A13:A43=B5);1))
Gruß Werner
.. , - ...
da ganzzahlig und aufsteigend viel einfacher
06.07.2017 21:47:32
WF
Hi,
=INDEX(11:11;VERGLEICH(B6-0,1;23:23;1)+1)
WF
AW: da ganzzahlig und aufsteigend viel einfacher
06.07.2017 21:54:31
Luschi
Hallo WF,
Voraussetzungen für Formel-Lösungen sind immer (sortiert usw.) sind immer ein Killer für
Gesamtlösungen, denn das ist einfach nicht realisierbar.
Gruß von Luschi
aus klein-Paris
Ich antworte entsprechend Frage/Datei
06.07.2017 22:02:06
WF
.
Sortiert ist Pflicht
07.07.2017 07:13:06
lupo1
... sonst kann man nicht arbeiten.
Oder hast Du alle Deine Tabellen auf manuelle Neuberechnung? Ich brauche die fast nie.
Ein bisschen EDV-Voraussetzungen (wie ordentliche Struktur) kann JEDER vertragen oder man von ihm fordern. Sonst ist er des Rechners nicht würdig und soll auf Papier arbeiten.
Anzeige
AW: dann aber mit 2. INDEX() und VERGLEICH() ...
06.07.2017 22:30:31
...
Hallo WF,
... denn momentan hast Du einfach das Alter auf 30 Wochen fixiert.
=INDEX(11:11;VERGLEICH(B6-0,1;INDEX(A:P;VERGLEICH(B5;A:A;0););1)+1)
Gruß Werner
.. , - ...
stimmt - das Alter ist ja ne Variable
07.07.2017 05:11:27
WF
.
AW: da ganzzahlig und aufsteigend viel einfacher
06.07.2017 22:36:44
Reinhard
Vielen Dank für die superschnellen Antworten!
Das Problem mit Eurem Lösungsansätzen ist, dass die Formel den jeweils höheren Perzentilenwert ausgeben.
Wenn ich z.B. ein Neugeborenens der 30. Schwangerschaftswoche habe, das 1450 g wiegt, wird mir die 75. Perzentile angezeigt und nicht die 50.Perzentile, die sehr viel näher liegt (1449 g = 50. Perzentile und 1670 g = 75. Perzentile). Gibt es eine Möglichkeit, den jeweils nähere Perzentilenwert auszulesen?
Herzlichen Dank
Reinhard
Anzeige
AW: dann INDEX() und geschachteltes AGGREGAT() ...
06.07.2017 22:51:49
...
Hallo Reinhard,
... so: 
=INDEX(11:11;AGGREGAT(15;6;SPALTE(C1:P1)/(AGGREGAT(15;6;ABS(C13:P43-B6)
/(A13:A43=B5);1)=ABS(C13:P43-B6))/(A13:A47=B5);1))
Gruß Werner
.. , - ...
ohne AGGREGAT für die älteren Excels
07.07.2017 08:17:32
lupo1
B7: =INDEX(11:11;VERGLEICH(B6;INDEX(13:36;B5-19;))) für die normalerweise korrekte "25." ohne neopa's Doppelvergleich um 22:30
B7:
<b>=INDEX(11:11;VERGLEICH(B6;INDEX(13:36;B5-19;))+
(B6-VERWEIS(B6;INDEX(13:36;B5-19;))>INDEX(13:36;B5-19;VERGLEICH(B6;INDEX(13:36;B5-19;))+1)-B6))</b>
für die nachgereichte Aufgabenstellung
immer dieses ~f~
07.07.2017 08:19:37
lupo1
B7: =INDEX(11:11;VERGLEICH(B6;INDEX(13:36;B5-19;))+
(B6-VERWEIS(B6;INDEX(13:36;B5-19;))>INDEX(13:36;B5-19;VERGLEICH(B6;INDEX(13:36;B5-19;))+1)-B6))
Anzeige
AW: ist hier eine gute alternatLösungsvariante ...
07.07.2017 10:11:40
...
Hallo lupo1,
... und die auch für die Auswertung sehr großer Datenmengen (tausende Datensätze mit gleicher Datenstruktur) auch schneller in der Auswertung ist. Allerdings hätte ich für deren Entwicklung wohl das dreifache der Zeit gegenüber der für meine AGGREGAT()-Formel benötigt. Aber das ist halt nur subjektiv.
Gruß Werner
.. , - ...
AW: da ganzzahlig und aufsteigend viel einfacher
07.07.2017 11:26:20
Daniel
Hi
ich würde diese Formel nehmen: 
=INDEX(11:11;VERGLEICH(B6;INDEX(A:P;VERGLEICH(B5;A:A;0););1))
wenn jede Schwangerschaftswoche ab der 20. aufgelistet ist auch so etwas einfacher: 
=INDEX($B$11:$P$11;1;VERGLEICH(B6;INDEX($B$13:$P$36;B5-19;0);1))

die Tabelle sollte so aufgebaut sein, dass das jeweilige Gewicht immer das kleinste Gewicht ist, ab dem der jeweilige Perzentilwert zugeordnet werden soll.
Gruß Daniel
Anzeige
AW: damit berücksichtigst Du nicht die Vorgabe ...
07.07.2017 15:17:15
...
Hallo Daniel,
... Du ermittelst mit Deinen Formeln den Vorgängerwert. Gesucht ist aber der Wert, der den jeweils nähere Perzentilenwert zugeordnet ist.
Gruß Werner
.. , - ...
AW: damit berücksichtigst Du nicht die Vorgabe ...
07.07.2017 15:29:16
Daniel
wie ich schon schrieb:
fürs Arbeiten mit Vergleich/Sverweis muss die Tabelle immer als AB-Tabelle strukturiert sein.
dh in der Liste muss der kleinste Gewichtswert stehen, ab dem der zugeordnete Perzentilwert gilt.
Gruß Daniel
AW: dem ist aber hier nicht so owT
07.07.2017 15:39:47
...
Gruß Werner
.. , - ...
aber ich hoffe du stimmst mir zu,
07.07.2017 16:22:14
Daniel
dass es sinvoll wäre die Tabelle so aufzubauen, weil dann nicht nur das maschinelle, sondern auch das manuelle Auslesen wesentlich einfacher wäre.
Gruß Daniel
Anzeige
AW: dem kann ich/man zustimmen owT
07.07.2017 16:48:15
...
Gruß Werner
.. , - ...
Wer weiß: ...
07.07.2017 18:47:28
lupo1
... vielleicht ist das ja sogar schon so! Dass nämlich die Unterkanten-Werte der Perzentile in der Tabelle stehen. Inhaltlich können wir allesamt die Werte nämlich nicht würdigen.
Und die normale VERGLEICH-Formel reicht aus ... bzw. ist sogar anzuwenden. Und wir verschieben die Aussage mit den längeren Formeln verbotenenerweise um eine "Perzentil-Spalten-Hälfte".
Frag doch mal den Gynäkologen oder den Tabellenersteller Deines Vertrauens.
AW: Wer weiß: ...
07.07.2017 19:14:41
Daniel
Anforderung vom Fragesteller:
"den jeweils nähren Perzentilwert"
Gruß, Daniel
Bitte, Daniel:
07.07.2017 19:51:49
lupo1
Bitte lies meine Beiträge etwas genauer!
Es kann sein, dass der Ersteller keine Ahnung von seinen Daten hat, weil er die Tabelle nicht erstellt hat, und sie evtl. auch nicht interpretieren kann. Möglich ist das. Nur diese Möglichkeit sprach ich an! Glaub mir: Ich hatte Statistik in meinem Studium, und wir wurden dazu erzogen, die Statistik zuallererst auf ihre Erstellung abzuklopfen.
Nun ist es im übrigen so, dass Perzentile einseitig definierte Intervalle abbilden. Man benötigt nur die eine Seite, da man zwei benachbarte Perzentile voneinander subtrahieren kann, um ein abgeschlossenes Intervall zu bekommen. Deshalb besteht für meine Vermutung durchaus Anlass.
Und was ein Fragesteller will, ist für mich nie Gesetz. Dort, wo ich erfahrener bin, weise ich auf mögliche Fehler hin. So wie hier.
Anzeige
AW: Bitte, Daniel:
07.07.2017 20:36:14
Daniel
wenn du da soviel Ahnung hast...
ich weiß dass nicht und muss davon ausgehen, dass der Fragessteller sich mit seinen Tabellen besser auskennt als ich.
Wenn in der Tabelle Untergrenzen angegeben wären, sollte dann nicht idealerweise in der ersten Spalte eine 0 als Startwert stehen?
Gruß Daniel
Ich vermute jetzt sogar Fehlinterpretation
07.07.2017 20:54:15
lupo1
durch den TE, denn das Perzentil 50 beginnt (oder endet) bei 1449. Fällt ein Wert raus, dann gehört es klipp und klar zum umgreifenden Perzentil (welches mehr umfasst, als das 50er). Dann darf nicht angenähert werden!
Bei kritischen Anwendungen wie Nukleartechnik spielt so etwas schon eine Rolle.
Unter 1 und über 99 können ja auch absichtlich Fehler erzeugen sollen!
Anzeige
AW: der Vollständigkeit halber ...
06.07.2017 22:55:18
...
... such wenn diese Formel nicht mehr benötigt wird.
In vorgenannter Formel war noch ein Schreibfehler. Es musste darin anstelle C13:J43 richtig natürlich C13:P43 lauten.
Gruß Werner
.. , - ...
AW: zu der geschachtelten AGGREGAT() Formel ...
06.07.2017 23:06:12
...
Hallo Reinhard,
... die ich vorhin eingestellt hatte, muss ich anmerken, dass es natürlich korrekter wäre, den auszuwertenden Datenbereich in der Formel auf den wirklich vorhandenen exakt abzustimmen. Die Ergebnisse beider Formeln bleiben aber die gleichen.
Die Formel sollte dann so lauten:
=INDEX(11:11;AGGREGAT(15;6;SPALTE(C1:P1)/(AGGREGAT(15;6;ABS(C13:P36-B6)/(A13:A36=B5);1)=ABS(C13:P36-B6))/(A13:A36=B5);1))
Gruß Werner
.. , - ...
Anzeige
AW: zu der geschachtelten AGGREGAT() Formel ...
07.07.2017 21:50:04
Reinhard
Hallo Werner,
vielen Dank für diese Ergänzung. Super!
Viele Grüße
Reinhard
AW: freut mich, danke owT
08.07.2017 08:41:15
...
Gruß Werner
.. , - ...
AW: Ungenauen Wert auslesen und zuordnen
07.07.2017 16:23:20
Daniel
Hi
wenn immer das näherliegende Perzentil ausgwählt werden soll, dann vielleicht so:

Die Datei https://www.herber.de/bbs/user/114748.xlsx wurde aus Datenschutzgründen gelöscht


Gruß Daniel
Das sind zu viele Hilfsspalten, da hier ...
07.07.2017 18:52:00
lupo1
... doch nur ein einziger Wert ermittelt werden soll.
Es gibt aber andere Fragestellungen, wo das Auslagern von Tabellenwerten auf diese Deine Weise sinnvoll ist.
AW: aber nur eine Hilfszeile!
07.07.2017 19:04:04
Daniel
also alles gut, dafür ein einfaches, leicht verständliches Formelwerk.
und gerade weil hier nur ein einziger Wert ermittelt werden soll, ist die Hilfszeile gut verwendbar.
Gruß Daniel
Anzeige
stimmt, meinte Hilfszeile! owT
07.07.2017 20:12:52
lupo1
AW: dann mit weniger Hilfsspalten
07.07.2017 19:19:29
Daniel
vielleicht gefällt dir das ja besser (jetzt muss halt die Rechnung aus der Hilfszeile in der Formel abgebildet werden).
ich finde das aber deutlich komplizierter.
https://www.herber.de/bbs/user/114751.xlsx
Gruß Daniel
Die ist sehr schön
07.07.2017 20:06:18
lupo1
Da hast Du meine Formel um zwei VERGLEICHe gekürzt. Schöne Idee! Die geht übrigens als
B7: =INDEX(C11:Q11;1;VERGLEICH(C6;INDEX(
INDEX(C13:Q36;$C$5-19;0)-(
INDEX(C13:Q36;$C$5-19;0)-
INDEX(B13:P36;$C$5-19;0))/2;);1))
sogar ohne {}.
Die Hilfsspalte kann vermutlich auch weg, da bei einem Perzentil <1 der TE dafür eine eigene Spalte gehabt hätte. Dann verschieben sich die o.g. Koordinaten entsprechend.
... und noch kürzbar
07.07.2017 20:09:46
lupo1
B7: =INDEX(C11:Q11;1;VERGLEICH(C6;INDEX((
INDEX(C13:Q36;$C$5-19;0)+
INDEX(B13:P36;$C$5-19;0))/2;);1))
AW: ... und noch kürzbar
07.07.2017 20:37:48
Daniel
Schön
Ärgerlich, dass ich nicht selber auf die kürzere Methode zur Mittelwertbildung gekommen bin.
Gruß Daniel

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige