Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1520to1524
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

Längste Kette zählen mit zwei Bedingungen

Längste Kette zählen mit zwei Bedingungen
21.10.2016 09:34:26
Daniel
Hallo Forum,
habe mir die Formel unter http://www.forum-echo.de/Excel/temp/20130314.htm für meine Bedürfnisse zusammengebaut.
Jetzt habe ich ein Problem an dem ich seit einer Stunde verzweifle.
Möchte die längste Gesamtkette zählen, also wie im Beispiel aber mit BEIDEN bedingungen.
Ich habs mal als Datei angehängt, hoffe es ist verständlich!
https://www.herber.de/bbs/user/108938.xlsx
vielen Dank und beste Grüße

22
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
unklares Resultat = 6
21.10.2016 09:52:30
EtoPHG
Hallo Daniel,
Es ist für mich nicht nachvollziehbar, wie das Resultat 6 entstehen soll! M.E. müsste es 4 sein!
Sonst einfach (Anzahl der Längsten A) + (Anzahl der Längsten B) in einer Formel summieren.
Gruess Hansueli
AW: Längste Kette zählen mit zwei Bedingungen
21.10.2016 09:55:58
Daniel
Hi
=9-SUMMENPRODUKT(ISTFEHLER(SUCHEN(WIEDERHOLEN(171;SPALTE(A2:I2)); WECHSELN(B2&C2&D2&E2&F2&G2&H2&I2;"172";"171")))*1)
Gruß Daniel
AW: Längste Kette zählen mit zwei Bedingungen
21.10.2016 10:50:22
Daniel
@Daniel - funktioniert absolut perfekt, tausend Dank!!!
Die Verkettungsorgie ist ja abartig
21.10.2016 10:53:18
WF
Hi,
bei (hier) 8 &-Verkettungen ist das ja noch OK - wenn es aber ein paar hundert sind.
Unsere
http://www.excelformeln.de/formeln.html?welcher=411
auf hier angewandt wäre:
{=MAX(KGRÖSSTE((B2:J2171)*(A2:I2=171)*SPALTE(A1:I1);SPALTE(A1:I1))-KGRÖSSTE((B2:J2=171) *(A2:I2171)*SPALTE(A1:I1);SPALTE(A1:I1))) }
Wie man die auf 2 Werte umfummelt hab' ich (noch) nicht rausbekommen.
WF
Anzeige
AW: geht auch für 2 Werte und auch ohne {} ...
21.10.2016 11:40:26
...
Hallo WF
... für viele Spaltenwerte (für 8 wie hier würde ich die Formel von Daniel belassen) folgende Formel bezogen auf das Beispiel einfach auch mit WECHSELN() und der Ergebnisformel erst mit eine Leerzelle nach dem letzten Zellwert; z.B. so:
=AGGREGAT(14;6;AGGREGAT(15;6;SPALTE(B2:I2)/(WECHSELN(B2:I2;172;171)="171")/(WECHSELN(C2:J2;172;171)"171");SPALTE(A1:H1))-AGGREGAT(15;6;SPALTE(B2:I2)/(WECHSELN(B2:I2;172;171)="171")/(WECHSELN(A2:H2;172;171)"171");SPALTE(A1:H1));1)+1
Natürlich wird es ohne AGGREGAT() als {} -Formel kürzer aber keinesfalls schneller.
Gruß Werner
.. , - ...
Anzeige
na ja: 4 mal Wechseln hatte ich auch
21.10.2016 11:47:20
WF
Hi,
{=MAX(KGRÖSSTE((WECHSELN(B2:J2;172;171)"171")*(WECHSELN(A2:I2;172;171)="171")*SPALTE(A1:I1); SPALTE(A1:I1))-KGRÖSSTE((WECHSELN(B2:J2;172;171)="171")*(WECHSELN(A2:I2;172;171)"171") *SPALTE(A1:I1);SPALTE(A1:I1))) }
Das muss man doch optimieren können ?
WF
AW: es geht auch ohne WECHSELN() ...
21.10.2016 12:22:13
...
Hallo,
... mit AGGREGAT() so:
=AGGREGAT(14;6;AGGREGAT(15;6;SPALTE(B2:I2)/(B2:I2>=171)/(B2:I2172));SPALTE(A1:H1))-AGGREGAT(15;6;SPALTE(B2:I2)/(B2:I2>=171)/(B2:I2172));SPALTE(A1:H1));1)+1
oder so:
=AGGREGAT(14;6;AGGREGAT(15;6;SPALTE(B2:I2)/((B2:I2=171)+(B2:I2=172))/((C2:J2172));SPALTE(A1:H1))-AGGREGAT(15;6;SPALTE(B2:I2)/((B2:I2=171)+(B2:I2=172))/((A2:H2172));SPALTE(A1:H1));1)+1
Gruß Werner
.. , - ...
Anzeige
das ist ja auch nur für diesen speziellen Fall
21.10.2016 12:48:24
WF
Hi,
... wo die Zahlen zusammenhängen: 171; 172
Bei 45; 201 bleibt nur die Wechselei.
WF
AW: dazu siehe meine 2. Variante owT
21.10.2016 14:21:45
...
Gruß Werner
.. , - ...
seh ich nicht
21.10.2016 15:41:02
WF
Hi,
tausche mal in den Beispielen und in Deiner Formel 172 in 80 - also:
=AGGREGAT(14;6;AGGREGAT(15;6;SPALTE(B2:I2)/((B2:I2=171)+(B2:I2=80))/((C2:J280)); SPALTE(A1:H1))-AGGREGAT(15;6;SPALTE(B2:I2)/((B2:I2=171)+(B2:I2=80))/((A2:H280)); SPALTE(A1:H1));1) +1
und man erhält für die 3 Beispiele 3 - 3 - 4,5 statt 6 - 3 - 3
Für Texte kann es sowieso nicht funktionieren.
WF
AW: geht sicherlich auch ...
21.10.2016 16:15:09
...
Hallo,
... allerdings hab ich jetzt auf die Schnelle auch keine befriedende Lösung gefunden. Ich schau es mir Morgen noch einmal an. Muss jetzt offline gehen.
Gruß Werner
.. , - ...
Anzeige
AW: nun denn, für zwei beliebige Suchwerte ...
22.10.2016 08:33:54
...
Guten Morgen WF,
... dafür musste die Formel natürlich entsprechend angepasst werden.
In Anlehnung an das vorliegende Beispiel hab ich das nachfolgend für eine beliebige 2er Kombination aus Zahl und/oder Text mit Zahl und/oder Text getan. Die WENNFEHLER()-Klammerung hab ich mir gespart. Da ich die Formel für eine Kopie sowohl nach unten als auch nach rechts aufgebaut habe, sind mehr $ vorhanden als normal notwendig.
Sollten mehr Suchwerte als zwei zur Diskussion stehen, würde ich wahrscheinlich noch VERGLEICH() oder auch ZÄHLENWENN() ins Spiel bringen.
 ABCDEFGHIJKLM
1          aba27
2 WertWertWertWertWertWertWertWert ca1151
3Beispiel 1abaaacaaaa 61#ZAHL!
4beispiel 2aca1111aa51a 221
5Beispiel 3a51272727caaa 3#ZAHL!4
6             

Formeln der Tabelle
ZelleFormel
K3=AGGREGAT(14;6;AGGREGAT(15;6;SPALTE($B3:$I3)/((($B3:$I3=K$1)+($B3:$I3=K$2))>0)/($C3:$J3<>K$1)/($C3:$J3<>K$2); SPALTE($A2:$H2))-AGGREGAT(15;6;SPALTE($B3:$I3)/((($B3:$I3=K$1)+($B3:$I3=K$2))>0)/($A3:$H3<>K$1)/($A3:$H3<>K$2); SPALTE($A2:$H2)); 1)+1


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
nee - da bleib ich bei 4 mal WECHSELN
22.10.2016 10:01:00
WF
Hi,
{=MAX(KGRÖSSTE((WECHSELN(B3:J3;K$2;K$1)K$1)*(WECHSELN(A3:I3;K$2;K$1)=K$1)*SPALTE(A2:I2); SPALTE(A2:I2))-KGRÖSSTE((WECHSELN(B3:J3;K$2;K$1)=K$1)*(WECHSELN(A3:I3;K$2;K$1)K$1)*SPALTE(A2:I2); SPALTE(A2:I2))) }
Für Dein Beispiel müssen die Zellen K1 bis M1 als Text formatiert sein.
Gib mal in K1 a ein und in K2 nichts
Das ergibt bei Dir 2 - 2 - 1 statt korrekt 3 - 2 - 2
AW: Du wolltest es zuerst anders und ...
22.10.2016 10:28:22
...
Hallo nochmal,
... bei mir ergibt sich auch bei K="a" und K2="" das richtige Ergebnis: 3 - 2 - 2
Du konntest jedoch nicht sehen, dass ich in J3:J5 ein Leerzeichen (stellvertretend für einen beliebigen Wert) zu stehen habe. Auch sind bei mir die Zellen K1:M1 nicht als Text sondern als Standard formatiert.
Gruß Werner
.. , - ...
Anzeige
Leerzeichen und Textformat
22.10.2016 10:45:48
WF
Hi,
mit Leerzeichen in Spalte J ist's Ok
Textformatierung betraf die Wechseln-Formel
WF
doch ne Macke bei Solisten
22.10.2016 11:10:23
WF
Hi,
in K1 steht a und in K2 nichts.
Lösche jetzt F3 bis H3 und Du erhältst 6 statt 2
Als ich oben schrieb:
"Das muss man doch optimieren können ?"
Meinte ich damit natürlich nicht, die Formel noch weiter zu verlängern.
WF
AW: dem ist aber nicht so ...
23.10.2016 09:51:18
...
Hallo WF,
... wenn in K2 nichts steht, ist das dann trotzdem ein zweiter Suchwert (="") und somit wird das folgerichtig auch so ausgewertet. Man erhält dann als Ergebnis eben auch richtig 6, wenn die Datenwerte F3 oder F3:H3 gelöscht wird.
Wenn mit vorgegebenen Konstrukt "Solisten" ausgewertet werden sollen, dann müsste in K2 eben ein zweites Mal "a" geschrieben werden.
Das die Formel nun länger geworden ist, als mein erste Formel mit dem viermaligen WECHSELN() ist auch nicht zutreffend. Mein erster Formelvorschlag ohne WECHSELN() war jedenfalls wesentlich kürzer, nur war dieser eben direkt auf die Aufgabenstellung zugeschnitten.
Die dann erfolgte Formelverlängerung war dem geschuldet, dass Du allgemeinere Bedingungen eingefordert hattest. Trotzdem ist die so entstandene Formel:
=AGGREGAT(14;6;AGGREGAT(15;6;SPALTE($B3:$I3)/((($B3:$I3=K$1)+($B3:$I3=K$2))>0)/($C3:$J3K$1)/ ($C3:$J3K$2);SPALTE($A2:$H2))-AGGREGAT(15;6;SPALTE($B3:$I3)/((($B3:$I3=K$1)+($B3:$I3=K$2))>0) /($A3:$H3K$1)/($A3:$H3K$2);SPALTE($A2:$H2));1)+1 noch etwas kürzer, als einer dieser bzgl. dem Ergebnis (fast) entsprechenden Formel mit WECHSELN():
=AGGREGAT(14;6;AGGREGAT(15;6;SPALTE($B3:$I3)/(WECHSELN($B3:$I3;K$2;K$1)=K$1&"")
/(WECHSELN($C3:$J3;K$2;K$1)K$1&"");SPALTE($A2:$H2))-AGGREGAT(15;6;SPALTE($B3:$I3)
/(WECHSELN($B3:$I3;K$2;K$1)=K$1&"")/(WECHSELN($A3:$H3;K$2;K$1&"")K$1&"")
;SPALTE($A2:$H2));1)+1
Mit letzterer Formel werden leere Zellwerte in K1:K2 nicht als Suchwert ausgewertet. Ob das so richtig ist oder nicht, ist sicherlich Ansichtssache.
Gruß Werner
.. , - ...
Anzeige
AW: für mehr als zwei beliebige Suchwerte ...
23.10.2016 12:47:53
...
Hallo,
... unter der Voraussetzung dass leere Zellen nicht als solche gezählt werden sollen, kann nachfolgende (zwar etwas längere) Formel sowohl für ein als auch mehrere (im Beispiel bis zu 8) Suchwerte auswerten.
 ABCDEFGHIJKLMNO
1längste Kette:      Suchwerte:    
2Beispiel 1:13        11    
3Beispiel 2:4        51    
4Beispiel 3:3        a    
5          ba    
6          ca    
7               
8               
9               
10 WertWertWertWertWertWertWertWertWertWertWertWertWertWert
11Beispiel 1abaaacaaaaca11aba1127
12Beispiel 2aca 11aa27a11 11ba5151
13Beispiel 3a51112727caaa271127512711

Formeln der Tabelle
ZelleFormel
B2=AGGREGAT(14;6;AGGREGAT(15;6;SPALTE(A1:ZY1)/(--ISTZAHL(VERGLEICH(A11:ZY11;K$2:K$9;)^0)=1)/(--ISTZAHL(VERGLEICH(B11:ZZ11;K$2:K$9;)^0)=0); SPALTE(A1:ZY1))-AGGREGAT(15;6;SPALTE(A1:ZY1)/(--ISTZAHL(VERGLEICH(A11:ZY11;K$2:K$9;)^0)=0)/(--ISTZAHL(VERGLEICH(B11:ZZ11;K$2:K$9;)^0)=1); SPALTE(A1:ZY1)); 1)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
AW: Die Verkettungsorgie ist ja abartig
21.10.2016 12:12:33
Daniel
gibt's da in der benannten Excelversion nicht mittlerweise eine Funktion, die solche Textketten aus einem Zellbereich bilden kann ohne dass man jede Zelle einzeln angeben muss?
Gruß Daniel
ich glaub in Excel-2016 (hab ich nicht)
21.10.2016 12:34:33
WF
gibt's die Funktion TEXTKETTE - oder ähnlich.
WF
Diese und TEXTVERKETTEN, wobei beide auch ...
21.10.2016 15:44:06
Luc:-?
…durch meine UDF VJoin (im Archiv, zuletzt Vs1.4) ersetzbar sind, die ab Xl9/2k fktioniert. ;-]
Gruß, Luc :-?
Besser informiert mit …
AW:Einfache Lösung
21.10.2016 13:20:52
Daniel
Wenn man keine Scheu vor Formeln in Hilfszellen hat, gibt's auch einfache Lösungen ohne Verkettungsorgien
https://www.herber.de/bbs/user/108947.xlsx
Gruß Daniel
Anzeige

320 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige