Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1416to1420
1416to1420
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Zwischenwert Sverweis

Forumthread: Zwischenwert Sverweis

Zwischenwert Sverweis
21.03.2015 19:09:35
Andreas
Bei der Eingabe von einem OEE Jahresdurchschnitt von z.B.89 hätte ich gerne die genaue Zielereichng in %. Also sozusagen den Zwischenwert erechnet die zu dem Bereich Sverweis passt. Ist so etwas möglich? Welche Formel müsste dann in C6 stehen? Danke für Eure Hilfe!
https://www.herber.de/bbs/user/96539.xlsx

Anzeige

19
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zwischenwert Sverweis
21.03.2015 19:25:50
Alexander
Hallo Andreas,
was meinst du mit der genauen Zielerreichung in % und mit dem Zwischenwert?
Gruß Alex

AW: Zwischenwert Sverweis
21.03.2015 19:35:48
Andreas
Hallo, und danke das du mir helfen möchtest. Der normale Sverveis gibt ja in diesem Fall 100 als Ergebnis aus. Bei der Eingabe von OEE 89 würde ich mir erhoffen das Excel mir das genaue Ergebnis ausrechnet. (z.B 116). Ich weis aber selbst leider nicht wie mathematisch die "Abstufungen" zwischen z.B.85 und 100 OEE zu Errechnen wären. Ich hoffe diese Erklärung hilft weiter.

Anzeige
wenn ich Dein Ziel richtig interprtetiere ...
21.03.2015 19:51:17
neopa
Hallo Andreas,
... dann sind Deine Werte in B8:B13 schon Prozentwerte nur das diese bei Dir momentan noch kein Prozent-Zahlenformat aufweisen? Oder?
In dem Fall dann z.B. mit folgender Formel in C6 so:

=(SVERWEIS(B3;A8:B13;2)+WENNFEHLER((AGGREGAT(15;6;B8:B13/(A8:A13>=B3);1)-SVERWEIS(B3;A8:B13;2)) /(B3-AGGREGAT(14;6;A8:A13/(A8:A13
Gruß Werner
.. , - ...

Anzeige
AW: wenn ich Dein Ziel richtig interprtetiere ...
21.03.2015 19:58:59
Andreas
Ja richtig es sind schon % -Werte. Das von mir erwartete Ergebnis in C6 müsste ja zwischen 100 und 125 liegen.(Bei der Eingabe89). Die lange Formel von Werner gibt ja auch 100 als Ergebnis aus. Aber Danke für den Versuch.

habe übersehen, dass Du "nur" XL2007 hast ...
21.03.2015 20:12:45
neopa
Hallo Andreas,
... in der Version gab es die Funktion AGGREGAT() noch nicht und deshalb kannst Du das korrekte Ergebnis (106,25 nicht damit ermitteln. Ist aber auch ohne AGGREGAT() ermittelbar. Allerdings gehe ich jetzt erst einmal für heute wieder offline. Meinerseits dann Morgen wieder.
Gruß Werner
.. , - ...

Anzeige
AW: habe übersehen, dass Du "nur" XL2007 hast ...
21.03.2015 20:16:51
Andreas
OK, Danke für deine Hilfe. Würde mich über einen Weiteren Versuch morgen freuen.

AW: habe übersehen, dass Du "nur" XL2007 hast ...
21.03.2015 20:22:32
Andreas
Das wäre genau das von mir erwartete Ergebnis. würde mich also besonders auf morgen freuen Danke

mal aufgedröselt
22.03.2015 00:28:37
WF
Hi,
in D3 steht:
=SVERWEIS(B3;A8:B13;1)
in E3 steht:
=SVERWEIS(B3;A8:B13;2)
in D4 steht die Arrayformel:
{=INDEX(A8:A13;VERGLEICH(MIN(WENN(A8:A13>=B3;A8:A13));A8:A13;0))}
in E4 steht:
=SVERWEIS(D4;A8:B13;2;0)
das Ergebnis:
=WENN(B3 WF
Ich nehme an, Werte über 150 können nicht vorkommen.

Anzeige
D4 natürlich nur
22.03.2015 00:40:49
WF
in D4 steht die Arrayformel:
{=MIN(WENN(A8:A13>=B3;A8:A13))}
WF

AW: D4 natürlich nur
22.03.2015 10:42:14
Andreas
Danke an euch beide. Damit komme ich Prima weiter.

bei Tageslicht betrachtet ...
22.03.2015 08:59:48
neopa
Hallo Andreas,
... lag ich viel mehr daneben. Nicht nur, dass ich übersehen habe Du XL2007 im Einsatz hast, sondern meine Formel war auch nicht korrekt. Sorry, hatte gestern leider nicht getestet.
Und es gibt auch für XL2007 eine {}-freie hilfsspaltenfreie wie schnelle und sogar etwas kürzere Lösung. Siehe nachfolgende Formel in C6. Der Lösungsvorschlag von WF kann auch für frühere Excelversionen genutzt werden.
Und der Vollständigkeit halber noch die korrekte AGGREGAT()-Formellösung (ab Excel 2010) in D6:
 CD
6106,6666667106,6666667

Formeln der Tabelle
ZelleFormel
C6=SVERWEIS(B3;A8:B13;2)+WENNFEHLER((B3-SVERWEIS(B3;A8:B13;1))*(SVERWEIS(D4;A8:B13;2;0)-SVERWEIS(B3;A8:B13;2))/(INDEX(A8:A13;VERGLEICH(1;HÄUFIGKEIT(B3;A8:A13); ))-SVERWEIS(B3;A8:B13;1)); 0)
D6=WENNFEHLER(SVERWEIS(B3;A8:B13;2)+(B3-SVERWEIS(B3;A8:B13;1))*(AGGREGAT(15;6;B8:B13/(A8:A13>=B3); 1)-SVERWEIS(B3;A8:B13;2))/(AGGREGAT(15;6;A8:A13/(A8:A13>=B3); 1)-SVERWEIS(B3;A8:B13;1)); 0)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
doch noch nicht munter genug gewesen ...
22.03.2015 09:24:31
neopa
Hallo Andreas,
... korrekter so:
 CD
6200200

Formeln der Tabelle
ZelleFormel
C6=WENN(B3<30;0;SVERWEIS(B3;A8:B13;2)+WENNFEHLER((B3-SVERWEIS(B3;A8:B13;1))*(SVERWEIS(D4;A8:B13;2;0)-SVERWEIS(B3;A8:B13;2))/(INDEX(A8:A13;VERGLEICH(1;HÄUFIGKEIT(B3;A8:A13); ))-SVERWEIS(B3;A8:B13;1)); 0))
D6=WENN(B3<30;0;SVERWEIS(B3;A8:B13;2)+WENNFEHLER((B3-SVERWEIS(B3;A8:B13;1))*(AGGREGAT(15;6;B8:B13/(A8:A13>=B3); 1)-SVERWEIS(B3;A8:B13;2))/(AGGREGAT(15;6;A8:A13/(A8:A13>=B3); 1)-SVERWEIS(B3;A8:B13;1)); 0))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: doch noch nicht munter genug gewesen ...
22.03.2015 10:43:40
Andreas
Danke Für Eure Mühe. Jetzt komme ich weiter. Da wäre ich selbst nicht drauf gekommen!

dann hast Du die Formel C6 nicht getestet....
22.03.2015 15:50:26
neopa
Hallo Andreas,
... denn sonst hätte Dir auffallen sollen, dass in der Formel sich auch auf die Zelle D4 bezieht, wo nichts oder was ganz anderes steht und somit die Formel ein fehlerhaftes Ergebnis liefert.
Die Formel C6 nun hoffentlich endlich korrekt (die Ergebniswertanzeige von heute Morgen war einmal für B3=89 dann für B3=175 und nun wieder für B3=89) ist nun auch länger als meine AGGREGAT()-Formellösung für Excel 2010 (nur deshalb habe ich mir die Formel jetzt eben nochmal angeschaut, weil mir beim Nachmittagskuchenessen eingefallen war, das es eigentlich nicht sein kann, dass die Formel ohne AGGREGAT() kürzer ist, wie heute Morgen geschrieben):
 C
6106,6666667

Formeln der Tabelle
ZelleFormel
C6=WENN(B3<30;0;SVERWEIS(B3;A8:B13;2)+WENNFEHLER((B3-SVERWEIS(B3;A8:B13;1))*(SVERWEIS(INDEX(A8:A13;VERGLEICH(1;HÄUFIGKEIT(B3;A8:A13); )); A8:B13;2;0)-SVERWEIS(B3;A8:B13;2))/(INDEX(A8:A13;VERGLEICH(1;HÄUFIGKEIT(B3;A8:A13); ))-SVERWEIS(B3;A8:B13;1)); 0))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
Häufigkeit statt MIN(WENN bei mir eingebaut
22.03.2015 20:22:33
WF
ergibt dann in einer Formel kürzere:
=WENN(B3A13;B13;SVERWEIS(B3;A8:B13;2)+(B3-SVERWEIS(B3;A8:B13;1))*(SVERWEIS(D4;A8:B13;2; 0)-SVERWEIS(B3;A8:B13;2))/(INDEX(A8:A13;VERGLEICH(1;HÄUFIGKEIT(B3;A8:A13);))-SVERWEIS(B3;A8:B13;1) +1/9^9)))
WF
ergibt nicht wirklich das angestrebte, denn ...
23.03.2015 09:02:22
neopa
Guten Morgen WF,
... auch Dir ist das gleiche Versehen unterlaufen, wie mir gestern Morgen.
Du hast in ... *(SVERWEIS(D4;A8:B13;2;0) ... das D4 nicht durch den entsprechenden Formelteil ersetzt. Wenn Du das noch vornimmst, wird die End-Formel länger als die AGGREGAT()-Formellösung. Sie ist dann zwar etwas kürzer als meine HÄUFIGKEIT()-Formel, aber dafür fängt sie Eingaben in B3 von kleiner 30 und größer 150 nicht korrekt ab (wobei man natürlich darüber streiten könnte, ob es richtig ist, wenn ich für Eingaben in B3 größer 150 stets 200 als Ergebnis listen lasse.)
Gruß Werner
.. , - ...

Anzeige
stimmt - hab ich übersehen
23.03.2015 09:55:59
WF
.

AW: dann hast Du die Formel C6 nicht getestet....
23.03.2015 16:00:29
Josef
Hallo
Noch eine Variante.
Gruss Sepp
Tabelle2

 ABC
1 OEE Jahres DurchschnittZielerreichung Per System mi
2
3Masch 1149125
4   
5Masch1Zielereichung genau: ?
6Vorgabe Tabelle Zielerreichung198,5
7OEEZielerreichung 
8300 
94510 
106070 
1185100 
12100125 
13150200 

Formeln der Tabelle
ZelleFormel
C3=SVERWEIS(B3;A8:B13;2)
C6=WENN(B3<30;0;WENNFEHLER(INDEX(B8:B14+(B3-A8:A13)*(B9:B14-B8:B13)/(A9:A14-A8:A13); VERGLEICH(B3;A8:A14;1)); 200))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4,8

Anzeige
das ist echt die Hammerformel, ...
23.03.2015 16:34:34
neopa
Hallo Sepp,
... die Du hier gezeigt hast. Knapper und einfacher kann man die Aufgabe nun wirklich nicht mehr lösen. Und mir hast Du damit u.a. INDEX() als noch wertvoller aufgezeigt, als ich die Funktion bisher eh schon gehalten habe. Wieder was gelernt :-)
Gruß Werner
.. , - ...
;

Beliebteste Forumthreads (12 Monate)

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