Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1568to1572
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
Inhaltsverzeichnis

Versch. Werte unter Bedingungen ausgeben

Versch. Werte unter Bedingungen ausgeben
08.07.2017 16:56:50
Joerschi
Hallo liebes Forum,
ich komme an einem Problem nicht weiter und würde mich über Hilfe freuen.
Genaugenommen gibt es vier Teilformeln die ich suche, welche aber m. E. alle Teil des gleichen "Hauptproblems" sind.
Als Bildchen und darunter die Problembeschreibung:
Userbild
Musterdatei:

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


Tabelle A3:P5
Zeile 3 sind beliebige Überschriften.
Zeile 4 sind vorgegebene Prozentvorgaben.
Zeile 5 sind vorgegebene und den jeweiligen Prozentvorgaben darüber zugeordnete Zahlenwerte.
Gesucht (Konkretes Beispiel mit Bild folgt darunter):
Es werden die Formeln von B12:B15 gesucht, welche man nach rechts ziehen kann.
Es sollen in der Lösungstabelle nacheinander alle Intervalle der Vorgabetalle ausgegeben werden, wo in
Zeile 12 (=1.): absteigend die Summe der Prozentvorgaben aus Zeile 4 kleinergleich des Manuellen Vorgabewertes aus Zelle D7 ist.
Zeile 13 (=2.): Gibt die Summe der Werte aus Zeile 5 aus. (Hinweis: Null-Werte aus Zeile 4 müssten hier mitbeachtet werden; ergo deren Werte darunter mitgezählt!).
Zeile 14 (=3.): Gibt die Überschrift (Wert aus Zeile 3) aus, welche das Intervall von Zeile 12 beschreibt.
Zeile 15 (=4.): Gleich wie vorher, nur gibt rechte Überschrift aus.
Hinweis: die Zahlen in Zeile 11 könnten als Hilfsnummern absteigend für Aggregat oder KGroesste etc verwendet werden, falls mit diesen Befehlen gearbeitet wird.
Am konkreten Beispiel und händisch die ersten Werte eingetragen (und ich hoffe, die händischen Lösungen sind richtig!):
Userbild
Am ersten Beispiel B12:B15 erklärt:
Der erste Wert, welcher kleiner gleich der Vorgabe 80% ist (bzw. am nächsten darunter liegt), müsste die Summe der Zeile 4 der Überschrift A bis H sein. Dies wären in Summe 79% (Lösung in Zelle B12).
Die zugehörige Summe der Werte in Zeile 5 ergibt 200 (Lösung in Zelle B13).
Die linksseite Überschrift des Lösungsintervalls wäre "A", die rechtsseitige Überschrift "H".
Hnweis zur Lösung C12:C15:
Die Lösung in Zelle C13 (Summe Werte aus Zeile 5) muss den Wert 7 aus Zelle N5 mit beinhalten, obwohl der Prozentwert darüber=0% ist. Wichtig ist die Summe der Werte des gesamten Intervalls in seiner Breite zu erfassen.
Anders sieht es aus, wenn wie in Lösung Nr. 4 das ProzentIntervall nur von der Überschrift E bis L geht. Da ist es für die Lösungen in Zeile 12 natürlich wichtig, die =%-Werte zu ignorieren.
Für jede Hilfe freue und danke ich im Voraus
Herzliche Grüße
Joerschi

28
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: iziemlich komplex ...
08.07.2017 18:20:27
...
Hallo Joerschi,
... ich hab deshalb erst mal nur einen ersten Wurf an Lösungsformeln konstruiert, ohne diese zu optimieren. Die Hilfszeile in Zeile 7 könnte ich bei Bedarf noch ersparen. Die Formeln in B12 und B14 werden dann entsprechend komplexer. Das hab ich mir momentan aber erspart, weil Du zunächst erst mal die Lösung testen solltest. Denn meine Berechnungsergebnisse weichen von Deinen händisch ermittelten teilweise ab. Nach meiner oberflächliche Prüfung würde ich jedoch Excel Recht geben.
Formeln einfach nach rechts kopieren:
 ABCDEFGHIJKLMNOP
3ÜberschriftABCDEFGHIJKLMNO
4%-Vorgabe1%2%8%11%14%19%13%11%8%4%5%3%0%1%0%
5Werte481428355033282315139761
6                
7Manuelle Vorgabe Maximal %-Vorgabe:  80%            
10Gesuchte Lösung(en)               
11Lösungsnr (absteigend)12345678910     
121. zugehör. Prozentsumme:80%79%78%78%76%64%45%32%21%13%     
132. zugehör. Wertesumme:2122001962201881851351027451     
143. zugeh. Überschrift LinksDABECFGHIJ     
154. zugeh. Überschrift RechtsJHHOHOOOOO     
16                
17Hilfszeile79%78%76%80%78%64%45%32%21%13%     

Formeln der Tabelle
ZelleFormel
B12=KGRÖSSTE($B17:$K17;SPALTE(A1))
B13=SUMME(INDEX(5:5;VERGLEICH(B14;3:3;0)):INDEX(5:5;VERGLEICH(B15;3:3;0)))
B14=INDEX(3:3;AGGREGAT(15;6;SPALTE($B3:$P3)/($B17:$K17=B12); ZÄHLENWENN($B12:B12;B12)))
B15=INDEX(3:3;VERGLEICH(B14;3:3;0)+AGGREGAT(14;6;TEILERGEBNIS(2;BEREICH.VERSCHIEBEN(INDEX(4:4;VERGLEICH(B14;3:3;0)); ;;;SPALTE(INDEX(3:3;VERGLEICH(B14;3:3;0)):P3)-1))/(TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(INDEX(4:4;VERGLEICH(B14;3:3;0)); ;;;SPALTE(INDEX(3:3;VERGLEICH(B14;3:3;0)):P3)-1))<=0,8); 1)-1)
B17=AGGREGAT(14;6;TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(B4;;;;SPALTE(B1:P1)-1))/(TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(B4;;;;SPALTE(B1:P1)-1))<=0,8); 1)

Gruß Werner
.. , - ...
Anzeige
... habe vergessen die 0,8 durch D7 zu ersetzen...
08.07.2017 18:28:16
...
Hallo,
... sorry, aber deswegen muss ich die Formeln sicherlich nicht noch einmal einstellen.
Gruß Werner
.. , - ...
AW: ... habe vergessen die 0,8 durch D7 zu ersetzen...
08.07.2017 18:32:32
Joerschi
Hallo Werner,
Du bist wieder mal Spitze in Sachen Geschwindigkeit und Lösungskreativität.
Dafür vorab ein herzliches Danke!
Hilfszeilen waren diesmal kein Problem und machen bei den ohnehin schon komplexen Lösungen der Übersichtlichkeit halber Sinn.
Du hast auch Recht: Die ausgerechneten Lösungen sind auf jeden Fall richtig (genau deswegen sind Formeln so wichtig, weil der menschliche Faktor einfach Fehler macht...ein paar der Lösungen hatte ich gar nicht gesehen).
Nochmals Danke und nun muss ich mal versuchen, wenigstens einen Teil der Formeln zu verstehen und dann auch noch (auf die viel größeren "Realtabellen" zu adaptieren).
Herzliche Grüße
Joerschi
Anzeige
AW: bitte, gerne und Dir dann viel Erfolg owT
08.07.2017 19:19:02
...
Gruß Werner
.. , - ...
AW: Minikorrektur, falls möglich...
08.07.2017 18:52:38
Joerschi
Hallo Werner,
Wenn in der Zeile 4 immer mal ein 0%-Wert angegeben ist, dann sollte (der Optimierung für die Lösung in Zeile 13 wegen) dieser immer insofern beachtet werden, als dass er Einfluss auf das Lösungsintervall hat (z.B. wenn das Lösungsintervall bis zur Spalte links davor geht; siehe meine Beispiellösung Nr. 2, wo der Rahmen bei E-N gesetzt ist / oder meine Beispiellösung Nr. 4 von E-L).
Bis O braucht in dem Beispiel oben also kein einziges Intervall zu gehen, weil 0% als Außengrenze "nicht beachtet" werden sollen.
(Wahrscheinlich habe ich das oben noch zu ungenau ausgedrückt)
Herzliche Grüße
Joerschi
Anzeige
AW: die ist dank zusätzlichemVERWEIS() möglich ...
08.07.2017 19:17:16
...
Hallo Joeschi,
... ich gehe nachfolgend davon aus, dass diese Dich wirklich nur in Zeile 13 interessiert.
Dann in B13 folgende Formel:
=SUMME(INDEX(5:5;VERGLEICH(B14;3:3;0)):
INDEX(5:5;VERWEIS(9;1/($A4:INDEX(4:4;VERGLEICH(B15;3:3;0))>0);SPALTE($A3:$P3))))
Gruß Werner
.. , - ...
AW:Lösungen für Zeile 14+15 wären auch interessant
08.07.2017 19:28:19
Joerschi
Hallo Werner,
vielen Dank wieder.
Kannst Du mir auch "obligatorisch" die Lösungen für Zeile 14 & 15 dazu geben?
Denn auch dort soll die richtige Überschriftsbegrenzung letztlich korrekt ausgelesen werden.
Danke vorab und beste Grüße
Joerschi
@Lupo1: Danke Dir auch, schaue ich mir dann gleich an :-)
Anzeige
AW: wohl am einfachsten mit ner Monsterformel ...
09.07.2017 08:22:20
...
Guten Morgen Joerschi,
... indem ich die VERWEIS()- Teilformel für B13 wieder nutze. Also so:
=VERWEIS(9;1/($A4:INDEX(4:4;VERGLEICH([bisherige Formel B15];3:3;0))>0);SPALTE($A3:$P3)) 
Eine Änderung der Formel für B14 ist mE (bisher noch) nicht notwendig, weil die 0%-Werte sich ja nur am jeweiligen rechten Auswertungsbereichsende befinden können. Sollten jedoch 0%-Werte z.B. auch in A4 bis J4 befinden, bedarf es einer entsprechenden Bedingungspräzisierung Deinerseits, wie mit diesen verfahren werden soll.
Die komplette Formel für B15 neu:
 BCDEFGHIJK
15JHHNHNNNNN

Formeln der Tabelle
ZelleFormel
B15=INDEX(3:3;VERWEIS(9;1/($A4:INDEX(4:4;VERGLEICH(INDEX(3:3;VERGLEICH(B14;3:3;0)+AGGREGAT(14;6;TEILERGEBNIS(2;BEREICH.VERSCHIEBEN(INDEX(4:4;VERGLEICH(B14;3:3;0)); ;;;SPALTE(INDEX(3:3;VERGLEICH(B14;3:3;0)):P3)-1))/(TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(INDEX(4:4;VERGLEICH(B14;3:3;0)); ;;;SPALTE(INDEX(3:3;VERGLEICH(B14;3:3;0)):P3)-1))<=$D7); 1)-1); 3:3;0))>0); SPALTE($A3:$P3)))

Gruß Werner
.. , - ...
Anzeige
AW: wohl am einfachsten mit ner Monsterformel ...
09.07.2017 08:50:02
Joerschi
Hallo Werner,
vielen Dank (wieder einmal).
Ja, die 0%-Werte können sich (und tun das in den "Realtabellen") auch linksseitig befinden.
Mit ihnen soll jedoch exakt gleich verfahren werden wie auf der rechten Seite (nur eben alles seitenverkehrt).
Herzliche Grüße
Joerschi
PS: Wäre es als "Krücke" für eine schlankere Formel hilfreich, wenn man alle außenstehenden 0%-Werte mit einem Zeichen ersetzt?
Zum Beispiel so:
Wertetabellenbeispiel vorher: 0;0;1;5;0;8;2;0;3;0;0
Werte mit Zeichen ersetzt: X;X;1;5;0;8;2;0;3;X;X
AW: dazu ...
09.07.2017 09:43:49
...
Hallo Joerschi,
... der Ersatz der 0% durch als alphanumerisches Zeichen wäre für meinen momentanen Lösungsansatz eher hinderlich.
Aber nachgefragt zu 0%-Werten im linken Datenbereich. Interpretiere ich Deine Vorstellung richtig, dass wenn z.B. die Zellwerte B4 und D4 0%-Werte sind in Zeile 14 nicht "A" und "C" erscheinen darf? Oder?
In diesem Zusammenhang noch nachgefragt: Es sind auch dann weiterhin stets die Prozentwerte ab dem jeweiligen Startwert plus den kontinuierlich folgenden Werten rechts von diesen zu addieren?
Evtl. kann ich auf Deine zu erwartende Antwort erst Morgen reagieren.
Gruß Werner
.. , - ...
Anzeige
AW: Beides korrekt interpretiert...
09.07.2017 10:01:28
Joerschi
Hallo Werner,
Zitat
"Interpretiere ich Deine Vorstellung richtig, dass wenn z.B. die Zellwerte B4 und D4 0%-Werte sind in Zeile 14 nicht "A" und "C" erscheinen darf? Oder?"
Ja, richtig. Wobei deren zugehörige Werte in Zeile 5 dennoch weiter mitgezählt werden, insofern das Intervall sie mit umfasst (also wenn der größerNullProzent-Wert des Intervalls links davon steht).
Ergo analog wie auf der rechten Seite schon umgesetzt.
Zitat
"In diesem Zusammenhang noch nachgefragt: Es sind auch dann weiterhin stets die Prozentwerte ab dem jeweiligen Startwert plus den kontinuierlich folgenden Werten rechts von diesen zu addieren?"
Ja, ebenfalls korrekt.
Herzliche Grüße, Joerschi
Anzeige
AW: nun dann ...
09.07.2017 11:17:48
...
Hallo Joerschi,
... ergibt sich aber auch eine Wiederholung gleicher Ergebnisbereichswerte und zwar genau so oft, wie es im vorderen Auswertungsbereich 0%-Werte gibt. Sieh dazu mein nachfolgend willkürlich gewähltes Beispiel mit den bisherigen Formeln und der neu angepassten Formel in B14 und teilweise geänderten Beispieldaten:.
Sollten die Wiederholungsbereiche nicht gewollt sein, bedarf es Deinerseits entsprechender Aussagen. Dann kann es aber mit meinem Lösungsansatz evtl. noch komplizierter werden.
Übrigens Mein gestrige Erstaussage, wonach ich die Hilfszeile 17 noch einsparen könnte war wohl etwas zu voreilig. Ein Test hat ergeben, dass ich dazu momentan keine vertretbare Lösung gefunden habe. In sofern ist es gut, dass Du nach Deinen Aussagen kein Problem mit der Hilfszeile hast.
 ABCDEFGHIJKLMNOP
3ÜberschriftABCDEFGHIJKLMNO
4%-Vorgabe0%10%0%11%14%19%15%11%8%4%14%15%0%4%0%
5Werte481428355033282315139761
6                
7Manuelle Vorgabe Maximal %-Vorgabe:  80%            
10Gesuchte Lösung(en)               
11Lösungsnr (absteigend)12345678910     
121. zugehör. Prozentsumme:80%80%78%78%71%71%71%56%45%37%     
132. zugehör. Wertesumme:1961961971971841621341017350     
143. zugeh. Überschrift LinksBBDDEFGHIJ     
154. zugeh. Überschrift RechtsHHIIJKNNNN     
16                
17Hilfszeile80%80%78%78%71%71%71%56%45%37%     

Formeln der Tabelle
ZelleFormel
B14=INDEX(3:3;AGGREGAT(15;6;SPALTE($A1:P1)/(INDEX(4:4;AGGREGAT(15;6;SPALTE($B3:$P3)/($B17:$K17=B12); ZÄHLENWENN($B12:B12;B12))):P4>0); 1)-1+AGGREGAT(15;6;SPALTE($B3:$P3)/($B17:$K17=B12); ZÄHLENWENN($B12:B12;B12)))

Gruß Werner
.. , - ...
Anzeige
AW:In Hilfszeile stockt es bei Realwerten(Fehler?)
09.07.2017 17:07:19
Joerschi
Hallo Werner,
ich habe nun die Formeln soweit auf die "Realwerte" adaptiert (war nicht schwer).
Allerdings habe ich den Effekt, dass ab einem bestimmten Punkt die Hilfszeile aufhört, weiter Werte absteigend anzuzeigen.
Anliegend eine Beispieltabelle mit den Realwerten.
Konkret meine ich ab Zelle CY17, wo eigentlich der nächstkleinere Wert der Hilfszeile folgen müsste - allerdings kommt danach nichts mehr. Ich komme einfach nicht dahinter, warum die Listung hier aufhört (Formatierungsproblem?).
Vielleicht kannst Du mal schauen, wenn Zeit.
https://www.herber.de/bbs/user/114782.xlsx
Danke Dir im Voraus und beste Grüße!
Joerschi
Anzeige
AW: die Hilfszeile ermittelt nicht absteigend ...
09.07.2017 17:58:38
...
Hallo Joerschi,
... sondern da werden lediglich die Prozent-Summen ab dem Spaltenwert ermittelt bis max zum vorgegebenen.
Lösche mal Deine Kontrollsummenbildung in GV4, dann wird auch wieder bis zum Ende berechnet.
Allerdings verwirren mich Deine Prozentangaben in Zeile 3.
Zu meiner Aussage von heute Vormittag (wiederholte Bereichsangabe) hatte ich heute Nachmittag beim "Gassi gehen" noch eine Idee, die ich anschließend gleich noch mal testen werde.
Bis dahin.
Gruß Werner
.. , - ...
AW: ... das war es
09.07.2017 18:06:00
Joerschi
Hallo Werner,
die Kontrollsummenbildung war es - bei der hätte ich im Leben nicht nachgeschaut :-/.
Vielen Dank dafür.
Die Prozentangaben in Zeile 3 sind richtig. In der Realtabelle sind das meine Überschriften, was allerdings wieder mit anderen Dingen zu tun hat.
Beste Grüße
Joerschi
AW: ... nein, das war es noch nicht ...
09.07.2017 20:20:40
...
Hallo Joerschi,
... in der Formel war noch ein Fehler, der sich erst bei Deiner größeren Anzahl von Daten auswirkt. Aber ich denke ich hab jetzt die "Kuh vom Eis". Bevor ich es jetzt auf die Schnelle einstelle, schau ich es mir aber später bzw. Morgen noch mal an und poste die entsprechend angepassten Formeln erst danach.
Einen schönen Abend Dir dann trotzdem noch.
Gruß Werner
.. , - ...
AW: nun Fehler lokalisiert und eliminiert ...
10.07.2017 08:40:46
...
Guten Morgen Joerschi,
... erst nachdem ich Dir gestern den Hinweis mit löschen der Formel in GV4 gegeben hatte (ausreichend ist natürlich dafür das Verschieben dieser Formel aus der Zeile 4 an einen anderen Ort) und ich meine angesprochene "Idee" testen wollte, stellte ich fest, dass in Deiner Datei zwischen BM17:CR17 die dortigen Ergebnisse sich zu ="" ergaben.
Erst nach einer etwas frustriert eingelegten längeren Pause, kam ich dahinter warum das so ist. Der Fehler lag in meiner relativen Definition des [Breite]-Arguments von BEREICH.VERSCHIEBEN(), welches sich erst ab der 64. Spalte derartig auswirkt und somit mir in der Beispieldatei bisher nicht aufgefallen war.
Nun musste ich nur noch dieses Argument absolut definieren, was gleichzeitig den Vorteil hat, dass sich die Formel in B15 diesbzgl. etwas vereinfacht.
Meine angesprochene "Idee" ist dagegen ganz simpel. Ich prüfe nun in der Hilfszeile einfach ab, ob der "Basiswert" der Zeile 3 ein 0%-Wert ist. Damit entfallen die doppelten/mehrfachen Ergebnisbereiche.
Nachfolgend stelle ich meine für B14 und B17 korrigierten sowie ergänzten Formeln gemeinsam mit den in B12:B13 bewusst nur für den bisherigen Beispieldatenbereich ein (für Deine Originaldaten musst Du dann nur den Spaltenbezug von Spalte P auf Spalte GT ändern).
 ABCDEFGHIJKLMNOP
3ÜberschriftABCDEFGHIJKLMNO
4%-Vorgabe10%0%0%11%14%19%15%11%9%4%14%19%0%8%0%
5Werte481428355033282315139761
6                
7Manuelle Vorgabe Maximal %-Vorgabe:  80%            
10Gesuchte Lösung(en)               
11Lösungsnr (absteigend)123456789101112131415
121. zugehör. Prozentsumme:80%80%79%72%72%65%54%45%41%27%8%    
132. zugehör. Wertesumme:200134197184162101735035226    
143. zugeh. Überschrift LinksAGDEFHIJKLN    
154. zugeh. Überschrift RechtsHNIJKNNNNNN    
16                
17Hilfszeile80%  79%72%72%80%65%54%45%41%27% 8% 

Formeln der Tabelle
ZelleFormel
B12=WENN(SPALTE(A1)>ZÄHLENWENN($B17:$P17;">0"); "";KGRÖSSTE($B17:$P17;SPALTE(A1)))
B13=WENN(B12="";"";SUMME(INDEX(5:5;VERGLEICH(B14;3:3;0)):INDEX(5:5;VERWEIS(9;1/($A4:INDEX(4:4;VERGLEICH(B15;3:3;0))>0); SPALTE($A3:$P3)))))
B14=WENN(B12="";"";INDEX(3:3;AGGREGAT(15;6;SPALTE($A1:P1)/(INDEX(4:4;AGGREGAT(15;6;SPALTE($B3:$P3)/($B17:$P17=B12); ZÄHLENWENN($B12:B12;B12))):P4>0); 1)-1+AGGREGAT(15;6;SPALTE($B3:$P3)/($B17:$P17=B12); ZÄHLENWENN($B12:B12;B12))))
B15=WENN(B12="";"";INDEX(3:3;VERWEIS(9;1/($A4:INDEX(4:4;VERGLEICH(INDEX(3:3;VERGLEICH(B14;3:3;0)+AGGREGAT(14;6;TEILERGEBNIS(2;BEREICH.VERSCHIEBEN(INDEX(4:4;VERGLEICH(B14;3:3;0)); ;;;SPALTE($B3:$P3)-1))/(TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(INDEX(4:4;VERGLEICH(B14;3:3;0)); ;;;SPALTE($B3:$P3)-1))<=$D7); 1)-1); 3:3;0))>0); SPALTE($A3:$P3))))
B17=WENN(B4=0;"";AGGREGAT(14;6;TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(B4;;;;SPALTE($B1:$P1)-1))/(TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(B4;;;;SPALTE($B1:$P1)-1))<=$D7); 1))

Gruß Werner
.. , - ...
AW: nun Fehler lokalisiert und eliminiert ...
11.07.2017 10:47:51
Joerschi
Hallo Werner,
kam leider erst jetzt dazu, drüber zu schauen (Arbeit, Kinder, allgemeine Leben :-) ).
Vielen Dank für die erneuerten Formeln.
Sie scheinen zu funktionieren und auch nach der Umstellung liefern sie die gewünschten Ergebnisse.
Dennoch habe ich ein paar Fragen dazu (nicht dringend!!!, aber interessant).
Die Fragen würde ich direkt an meiner Realtabelle orientieren, wo in Tabelle 1 die bisherige Lösung und in Tabelle 2 die aktualisierte Lösung
implementiert ist.
Die ausgefüllten Dateien dazu sind:
1. Bisher: https://www.herber.de/bbs/user/114805.xlsx
2. Aktualisiert: https://www.herber.de/bbs/user/114804.xlsx
1. In der aktualisierten Tabelle gibt es "nur noch" 49 Lösungsvorschläge, in der bisherigen Tabelle hingegen noch 69 Lösungen.
1b) Die Summenwerte weichen zusätzlich ab, d. h. es gibt "neue" Summen.
Zum Beispiel beträgt in der alten Version der höchste Summenporzentwert noch 78,20513% und in der neuen Version 79,48718%.
Hat das einen Grund, dass die Summenprozente jetzt anders sind?
2. Eher allgemein gefragt....
Normalerweise müsste es ja in der Realtabelle Hunderte "Abstufungen" der Summenprozente geben in der Realtabelle.
Denn der erste Wert in Zeile 4 beginnt bei Spalte Nr. 63 (BK) und endet bei Spalte Nr. 140 (EJ).
Auch wenn nicht jede Spalte in Zeile 4 einen %Wert besitzt, so dürfte es doch dabei zig Summenwerte geben, die man als Intervall zusammenaddieren kann (z. B. willkürlich gewählt das Intervall von Spalte CX:DC beträgt die Summe der %Werte in Zeile 4 = 10,256%. Diese 10,256 tauchen in der Lösung nie auf, obwohl möglich).
Könntest Du grob erklären, welche Intervalle die Formel der Hilfszeile sich da "bevorzugt" auswählt? Hilft letztlich bestimmt auch den stillen
Mitlesern zum Formelverständnis.
Herzliche Grüße und Danke vorab!
Joerschi
AW: dazu vielleicht heute Abend ...
11.07.2017 12:45:09
...
Hallo Joerschi,
... ich muss nämlich gleich offline gehen.
Du brauchst Dich nicht zu entschuldigen, wenn Du mal nicht gleich antworten kannst. Bei Dir weiß ich, dass Du auf jeden Fall antwortest, wenn es Dir möglich ist. Damit bist Du aus meiner Erfahrung aber leider schon fast eine Ausnahme. Daran und Deinen Fragestellungen könnten sich die meisten Fragesteller ein Beispiel nehmen.
Gruß Werner
.. , - ...
AW: dazu ...
11.07.2017 19:43:55
...
Hallo Joerschi,
... ich beginne mit meiner Antwort zu 2):
Nach meiner aktuellsten Interpretation Deiner Aufgabenstellung, kann es nur genau so viele Ergebnisintervalle geben, wie es in Deiner Datenzeile 3:3 Prozentwerte größer 0% gibt. Und das sind nun in Deiner Datei genau 49, was schnell mit ZÄHLENWENN(4:4;"größer 0") ermittelt ist. Die 49 deshalb, weil die in der Hilfszeile ermittelten Prozentsummen immer die Maxima-Summen gemäß Deiner Bedingung (im Beispiel kleiner =80%) sind. Und zwar dies beginnend ab jeweils jeden Prozentwert in Zeile 3, der größer 0 % ist.
Die jeweils kleineren "Zwischenintervallsummen" sind von mir bewusst nicht ermittelt. Erstens war dessen Notwenigkeit bisher für mich nicht ersichtlich, zweitens weil da wohl schnell mehrere Hundert, vielleicht sogar tausende Intervalle entstehen könnten, drittens ergeben diese momentan (aus meiner Sicht) keinen Sinn und viertens und wohl entscheidend ist, dass mir momentan dafür eine reine Formellösung nicht mehr mit vertretbaren Aufwand lösbar erscheint.
Wenn die Zwischenintervalle jedoch für Dich notwendig sein sollten, dann begründe bitte kurz warum. Vielleicht kann ich dann daraus evtl. erkennen, ob dafür evtl. doch eine Vereinfachung des momentan scheinbar drohenden noch möglich sein könnte.
Zu 1) wie ich gestern schrieb, hatte ich ja bis Sonntag noch einen "versteckten" Fehler in der Hilfszeilen-Formel (relativ definiertes letztes Argument für BEREICH.VERSCHIEBEN()-Formelteil) und hatte außerdem auch wie geschrieben noch "Dupletten-"Ergebnisse ermittelt (indem ich da die 0%-Werte als Intervallgrenzen noch mit eingerechnet hatte).
Gruß Werner
.. , - ...
AW: Das passt alles so wie es ist :-)
12.07.2017 09:29:39
Joerschi
Hallo Werner,
Danke für Deine ausführliche Antwort und die Begründungen/Erklärungen.
Das macht so absolut Sinn für mich und reicht in Umfang meiner beabsichtigten Auswertungen auch völlig aus. Es muss da nicht zwangsläufig jedes mögliche Intervall angegeben sein, weil das zudem - wie von Dir richtig angemerkt - schnell in die Tausende oder schlimmer gehen kann, je größer der Wertebereich wird.
Mit Deinen Lösungen komme ich bei der Lösung des Problemes prima zurecht und werde sie auch so verwenden.
Beste Grüße und einen schönen Restmittwoch :-)
Joerschi
AW: bitte, gern und ...
12.07.2017 12:25:33
...
Hallo Joerschi,
... Deinen neuen thread hab ich eben gesehen. Werde ihn mir aber wohl erst später genauer ansehen.
Gruß Werner
.. , - ...
Bisschen getrickst
08.07.2017 19:16:35
lupo1
https://www.herber.de/bbs/user/114772.xlsx
Ich habe in Zeile 1 die kumulierten % aufgetragen.
Außerdem habe ich die 80% sinnvoll versetzt.
Bitte Werte überprüfen! Soll mit den % in Zeile 12 gerechnet werden, müssen sie mit KÜRZEN(...;2) behandelt werden.
Statt mittels Datei hier mit CODETab dargestellt
09.07.2017 07:37:59
lupo1

__|_________________A_________________|__B_|___C__|___D__|___E__|___F__|___G__|___H__|___I__|
 1|                                   |0,01|  0,03|  0,11|  0,22|  0,36|  0,55|  0,68|  0,79|
 2|                                   |    |      |      |      |      |      |      |      |
 3|Überschrift                        |A   |B     |C     |D     |E     |F     |G     |H     |
 4|%-Vorgabe                          |0,01|  0,02|  0,08|  0,11|  0,14|  0,19|  0,13|  0,11|
 5|Werte                              |4,00|  8,00| 14,00| 28,00| 35,00| 50,00| 33,00| 28,00|
 6|                                   |    |      |      |      |      |      |      |      |
 7|Manuelle Vorgabe Maximal %-Vorgabe:|    |      |      |      |      |      |      |      |
 8|                                   |    |      |      |      |      |      |      |      |
 9|                                   |    |      |      |      |      |      |      |      |
10|Gesuchte Lösung(en)                |    |      |      |      |      |      |      |      |
11|Lösungsnr (absteigend)             |    |  1,00|  2,00|  3,00|  4,00|  5,00|  6,00|  7,00|
12|1. zugehör. Prozentsumme:          |0,80|  0,79|  0,78|  0,77|  0,76|  0,74|  0,69|  0,68|
13|2. zugehör. Wertesumme:            |    |200,00|220,00|213,00|197,00|197,00|184,00|174,00|
14|3. zugeh. Überschrift Links        |    |A     |E     |E     |D     |E     |E     |D     |
15|4. zugeh. Überschrift Rechts       |    |H     |O     |M     |I     |K     |J     |H     |
Zellformeln:
B1: =B4+A1
C12: {=MAX(WENN(
$B$1:$P$1-MTRANS($A$1:$O$1)&ltKÜRZEN(B12;2);(
$B$1:$P$1-MTRANS($A$1:$O$1))+SPALTE($B$1:$P$1)%%%+ZEILE($A$2:$A$16)%%))}
C13: =SUMME(INDEX(5:5;KÜRZEN(REST(C$12;1%)/1%%)):INDEX(5:5;REST(C$12;1%%)/1%%%))
C14: =ZEICHEN(KÜRZEN(REST(C$12;1%)/1%%)+63)
C15: =ZEICHEN(REST(C$12;1%%)/1%%%+63)
______________________________________________________
Was bedeutet hier {=Formel} statt nur =Formel?
Die { } sind nicht manuell einzugeben, sondern die Zellformel ist
mittels Strg-Umschalt-Enter abzuschließen, statt nur mit Enter.
Dadurch wird eine {=Matrixformel} erzeugt.
______________________________________________________
Unten habe ich den Block eine Spalte nach rechts versetzt, damit der Parameter "80%" in B12 gepackt werden und so als Vorläuferzelle für die darunter liegenden Maxima dienen kann.
AW: mE hast Du die Aufgabe fehlinterpretiert ...
09.07.2017 08:39:35
...
Hallo lupo1.
... gestern Abend hab ich auf Deinen ersten Beitrag nicht geschrieben, weil ich annahm, dass Du nach nochmaligen Lesen der Beitrage von Joerschi und mir dies selbst feststellen wirst.
Eine andere auch evtl. einfachere Lösung als die von mir vorgetragene ist durchaus möglich, aber Deine bisher aufgezeigt, ergibt ein völlig anderes Ergebnis, als das von mir aufgezeigte, welches wiederum offensichtlich auch den Vorstellungen von Joerschi entspricht.
Gruß Werner
.. , - ...
Fehlinterpretation ist nicht gleich ...
09.07.2017 08:55:15
lupo1
... "grundsätzlich richtige Lösung mit evtl. noch enthaltenen Fehlern".
Deshalb bat ich ja auch ausdrücklich um Überprüfung meiner Werte.
Dass es zusammenhängende Zellen sein sollen, darüber sind wir uns aufgrund der grafischen Aufgabenstellung sicher einig.
Das Problem bei (nur?) meiner Lösung: Da die %-Werte "rund" sind, ist die Wkt. groß, dass der gleiche %-Wert mehrfach auftritt. Dann ist eine eindeutige Reihenfolge natürlich nicht gegeben, und bei mir könnten die Wiederholer "unter den Tisch fallen".
Das lässt sich aber - wie bei RANG - wohl noch mit ZEILE()% o.ä. heilen. Ich warte aber erst mal ab.

301 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige