Fehlerbehebung Umsortierung (mit Index/Vergleich)

Bild

Betrifft: Fehlerbehebung Umsortierung (mit Index/Vergleich)
von: Joerschi
Geschrieben am: 21.07.2015 10:15:38

Hallo liebes Forum,
ich komme bei einer "Adaption" einer Formel absolut nicht weiter... :-(
Und zwar möchte ich Daten aus einer Quelltabelle umsortieren. Die Grundformel dazu kommt von hier (http://www.excelformeln.de/formeln.html?welcher=345).
Allerdings gibt es die Schwierigkeit, dass einige Bedingungen der Quelltabelle wechseln, so dass stets andere und meist auch zwei Spalten angesprochen werden müssen (wo gewisse Werte größer- oder kleiner als raussortiert werden müssen).
Dies habe ich per Index/Vergleich in der Formel zu berücksichtigen versucht. Allerdings klappt es nicht :-(
Als Beispiel die Formeln:
So klappt die Umsortierung:

=INDEX(BEI$3:BEI$2002;KKLEINSTE(WENN(($BEI$3:$BEI$2002>=10)*($B$3:$B$2002=$LP$9);ZEILE($1:$2000)); ZEILE()-3)) 

Es sind zwei Bedingungen vorgegeben, die ähnlich bei der Zählenwenn-Funktion klar definiert sind.
Dies ist nun ein Auszug der Formel (nur 2 Bedingungen hier im Beispiel), wo bestimmt wird, welche Spalten der Quelltabelle ($BEI$4:$BQC$2002) angesteuert werden.
Kern dabei ist, dass die Spalten durch andere Bedingungen im Formular definiert werden und somit variieren.
=SUMME((INDEX($BEI$4:$BQC$2002;;VERGLEICH($BDV$3;$BEI$3:$BQC$3;))>=10)*(INDEX($BEI$4:$BQC$2002;; VERGLEICH($BDX$3;$BEI$3:$BQC$3;))<>"")) 
Letztgenannter Part soll in der ersten Formel in den "Bedingungsteil" übertragen werden.
Allerdings kann man scheinbar die einzelnen Bedingungsaufzählung nicht eins zu eins durch die zweite Formel ersetzen.
Und zwar gedacht so (wass halt nicht funktioniert):
=INDEX(BEI$3:BEI$2002;KKLEINSTE(WENN(SUMME((INDEX($BEI$4:$BQC$2002;;VERGLEICH($BDV$3;$BEI$3:$BQC$3;)) >=10)*(INDEX($BEI$4:$BQC$2002;;VERGLEICH($BDX$3;$BEI$3:$BQC$3;))<>""));ZEILE($1:$2000));ZEILE()-3)) 
Mit ist auch klar, dass in der zweiten Formel oben die "Summen"-Funktion zwar die Anzahl wiedergibt, aber ohne diese wiederum wird nur ein Wahr/Falsch-Wert generiert, der irgendwie auch nicht funktioniert.
Hat jemand einen Tip?
Danke vorab und liebe Grüße
Joerschi
PS: hoffe, es ist verständlich. Ist ein wenig schwierig darzustellen...

Bild

Betrifft: eine kleine relevante Beispieldatei ...
von: der neopa C
Geschrieben am: 21.07.2015 10:31:11
Hallo Joerschi,
... hast Du doch sonst immer eingestellt. Warum diesmal nicht?
Gruß Werner
.. , - ...


Bild

Betrifft: AW: eine kleine relevante Beispieldatei ...
von: Joerschi
Geschrieben am: 21.07.2015 10:33:34
Hast Du Recht. Ich bau mal eine. Ist sonst echt schwierig.

Bild

Betrifft: AW: eine kleine relevante Beispieldatei ...
von: Joerschi
Geschrieben am: 21.07.2015 11:03:01
Hallo Werner,
hab jetzt mal was dazu gebaut.
Skizze:
Userbild
Musterdatei dazu: https://www.herber.de/bbs/user/98961.xlsx
Kurze Beschreibung dazu:
Quelltabelle ist C3:M59 mit verschiedenen Überschriften.
manuelle Vorgaben kann(muss) man in P5:P12 treffen.
Die Vorgaben in P5:P8 sind selbsterklärend, da immer auf die gleiche Spalte der Quelldatei bezogen.
Komplizierter wird es mit den Vorgaben in P10:P13. Hier können die Namen (Überschriften) der Spalten, welche ausgelesen werden, definiert werden - sie ändern sich also und es muss ggf. immer eine andere Spalte angesteuert/ausgelesen werden.
Außerdem ist je Name ein "Mindestwert" definiert.
Was soll passieren?
In die Zieltabelle (gelber Bereich) sollen nun untereinander (und unter den richtigen Überschriften) ausschließlich diejenigen Zeilen mit ihren Werten gelistet werden, welche die Bedingungen erfüllen.
Das heißt, dass Zeilen, welche eine der Bedingungen in der Quelltabelle NICHT erfüllen, in der Ausgabe der Zieltabelle ausgelassen werden.
Außerdem sollen am Ende - also wenn eine Liste in der Zieltabelle unten fertig ist - leere Zellen statt ein Fehlerwert angezeigt werden.
Als Beispiel hab ich mal die Ergebnisse für die Beispielbedingungen reinkopiert (via Autofilter und dann kopiert).
Bei Fragen bitte fragen.
Wie gesagt, ist irgendwie "doof" zu erklären...
Liebe Grüße
joerschi

Bild

Betrifft: dazu noch nachgefragt ...
von: der neopa C
Geschrieben am: 21.07.2015 11:32:13
Hallo Joerschi,
... ich finde momentan keine explizite Bedingungsvorgabe Deinerseits für P10:P13. Gehe ich Recht in der Annahme, dass Du dazu noch schreiben wolltest: P11 und P13 dürfen nicht unterschritten werden?
Noch ein Bitte. In den thread wo ich nachfrage, musst Du den thread nicht unbedingt auf offen setzen, wenn Du darauf antwortest. Ich nehme mich dieser auf jeden Fall wieder an. Es kann nur manchmal auch etwas dauern, weil ich ja meine Brötchen ;-) anderweitig verdienen muss und will.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: dazu noch nachgefragt ...
von: Joerschi
Geschrieben am: 21.07.2015 11:43:20
ganz genau :-)
P11 und P13 sind jeweils das Minimum oder auch Mindesthöhe.
(im Gegensatz dazu sind P5:P8 komplett statisch, sprich der Wert muss exakt jenem der Spalte darunter entsprechen)
Liebe Grüße
joerschi

Bild

Betrifft: INDEX() von AGGREGAT() und dies u.a. von INDEX()
von: der neopa C
Geschrieben am: 21.07.2015 11:56:07
Hallo Joerschi,
... kombiniert mit VERGLEICH() und natürlich WENNFEHLER() und das ganze ohne {}-MATRIXformel, allerdings in einer MATRIXfunktion(alität)sformel
Die Formel einfach ziehend nach rechts und unten kopieren:

 CDEFGHIJKLMNOPQRSTUVWXY
3  HQ_NQ  102F5F2F3F4F1F6F7 Vorgaben manuell   F5F2F3F4F1F6F7
4HQ6072511,5434,620,9111,5416,6711,54     21,0525,6623,0122,8922,1226,0924,78
5HQ90434,4819,5111,5922,553,0513,6415,85 Spalte C:HQ  25,0020,7421,4822,8928,1550,0029,63
6HQ80516,6715,938,8522,5521,242029,2 Spalte D:9  100,0050,0050,0014,6950,00100,0050,00
7HQ12013015,459,7622,5525,22822,76 Spalte E:0  19,235,4922,568,3114,6325,0013,41
8HQ90410,344,8822,565,243,0546,1 Spalte F:4  15,798,8523,0114,693,5439,1315,04
9HQ110242,860,8528,214,331,7114,292,56     12,5021,2421,2420,1621,2411,5429,20
10HQ90421,0525,6623,0122,8922,1226,0924,78 Name 1F3  28,5717,6522,3522,5523,538,3336,47
11HQ90437,512,397,0822,8916,812524,78 Wert zu Name 1:15,50  10,2619,5121,3422,5531,1020,4530,49
12NQ90422,7318,8222,3514,6922,35025,88 Name 2F7  25,0026,2221,3422,8914,6313,6413,41
13HQ9042520,7421,4822,8928,155029,63 Wert zu Name 2:8,00  15,3832,3223,7822,5531,1020,4530,49
14HQ904100505014,695010050     23,0826,2222,5622,8931,1036,0030,49
15NQ9045,2623,9314,530,918,5502,56     73,3326,2222,5622,5531,1025,6430,49
16HQ90410,5312,3914,1622,8921,2423,0829,2     7,6919,5121,3422,5531,1020,5130,49
17HQ90419,235,4922,568,3114,632513,41            

Formeln der Tabelle
ZelleFormel
S4=WENNFEHLER(INDEX($A:$M;AGGREGAT(15;6;ZEILE($C$1:$C$99)/($C$1:$C$99=$P$5)/($D$1:$D$99=$P$6)/($E$1:$E$99=$P$7)/($F$1:$F$99=$P$8)/(INDEX($A$1:$M$99;;VERGLEICH($P$10;$3:$3;))>=$P$11)/(INDEX($A$1:$M$99;;VERGLEICH($P$12;$3:$3;))>=$P$13); ZEILE(A1)); VERGLEICH(S$3;$3:$3;)); "")


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

Bild

Betrifft: meld mich
von: Joerschi
Geschrieben am: 21.07.2015 12:05:29
wow. Dank Dir Werner,
Ich versuche das mal zu adaptieren und melde mich :-)
Liebe Grüße
Joerschi

Bild

Betrifft: AW: INDEX() von AGGREGAT() und dies u.a. von INDEX()
von: Joerschi
Geschrieben am: 21.07.2015 14:41:56
also ich habe die Formel jetzt eine ganze Zeitlang adaptiert (muss man ja doch schon ordenlich mitdenken beim Zeile-/Spaltenverschieben usw.) und sie funktioniert einwandfrei.
Vielen herzlichen Dank noch einmal
Joerschi
PS: Danke auch an Daniel - die Lösung klappt zwar, aber ich brauchte wirklich die Formellösung, da mit dieser Zusammenstellung eine ganze Menge automatisierter Rechnungen erfolgen sollen

Bild

Betrifft: AW:alternativ zur Formel: Spezialfilter
von: Daniel
Geschrieben am: 21.07.2015 12:27:18
Hi
lässt sich ohne Formeln einfach mit dem Spezialfilter (unter DATEN - SORTIEREN UND FILTERN - ERWEITERT) erstellen.
schaust du Beispiel. Die gefärbten Felder musst du von Hand eingeben.
Der Filter merkt sich die Zellbereiche, so dass du relativ einfach mit verschiedenen Kriterien experimentieren kannst.
https://www.herber.de/bbs/user/98970.xlsx
Gruß Daniel

Bild

Betrifft: AW:alternativ zur Formel: Spezialfilter
von: Daniel
Geschrieben am: 21.07.2015 12:27:19
Hi
lässt sich ohne Formeln einfach mit dem Spezialfilter (unter DATEN - SORTIEREN UND FILTERN - ERWEITERT) erstellen.
schaust du Beispiel. Die gefärbten Felder musst du von Hand eingeben.
Der Filter merkt sich die Zellbereiche, so dass du relativ einfach mit verschiedenen Kriterien experimentieren kannst.
https://www.herber.de/bbs/user/98970.xlsx
Gruß Daniel

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Arbeitsblätter zusammenkopieren"