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

Suche Formel

Forumthread: Suche Formel

Suche Formel
07.02.2022 15:55:49
Rabusch
https://www.herber.de/bbs/user/150967.xlsx
Ist evtl. trivial, aber ich verzweifele daran.
Ich suche eine Formel zur Ermittlung der Anzahl der maximalen Kombinationen aus zwei Spalten mit einer Bedingung und ohne leere Zelle zu berücksichtigen.
Eine Beispieldatei liegt bei.
Anzeige

32
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Suche Formel
07.02.2022 16:30:30
{Boris}
Hi,
die Zahlen 1 bis 4 stehen in E10:E13.
In F10:
=SUMMENPRODUKT((VERGLEICH(A$6:A$32&B$6:B$32;A$6:A$32&B$6:B$32;)=ZEILE($1:$27))*(A$6:A$32=E10) *(B$6:B$32""))
und runterkopieren bis F13.
VG, Boris
AW: Suche Formel
07.02.2022 16:52:15
Rabusch
Herzlichen Dank für die schnelle Antwort.
Leider ist das noch nicht die Lösung.
Berechnung ohne Hilfsspalten, -zeilen, das ist eine der Bedingeungen. Die Formel muss unbedingt in einer Zelle stehen.
lg
Anzeige
AW: Suche Formel
07.02.2022 17:29:07
onur
"In diesem Beispiel wäre das Ergebnis 3 (4B,4C,4V)" ? Häääh?
Für die ganzen Spalten A und B einfach nur 3 ?
AW: Suche Formel
07.02.2022 18:05:57
Rabusch
Häääh, musste nicht sein,
Das Ergebnis 3 reicht, steht auch oben in der Tabelle, die Werte in den Klammer dienten nur zur Erklärung.
AW: Suche Formel
07.02.2022 18:11:29
onur
Und was ist mit 2B,23,3a, 3C usw? Unter "Anzahl der maximalen Kombinationen aus zwei Spalten" verstehe ich was Anderes.
Deine seltsame Logik bedarf einer korrekten Erklärung, deshalb "Hääääh?" !
Anzeige
AW: Suche Formel
07.02.2022 18:19:15
Rabusch
nun gut, in der Aufgabenstellung steht: Anzahl der maximalen Kombinationen NR/KOMBI je NR
also, je NR die maximal vorhandenen Kombinationen. das sind hier bei 1 = 0, bei 2 = 2, bei 3 = 2, bei 4 = 3, bei 5 = 2, das ergibt als Ergebnis max. 3 bei einer Nr., bei welcher ist in diesem Fall egal. Eigentlich bei meiner Aufgabenstellung ist auch der Wert egal, entscheidend ist nur, ob ein Wert grösser 1 ist.
Alse gibt es bei einer Nr. mehr als eine Kombi, wobei die Leerzellen nicht betrachtet werden sollen.
Anzeige
AW: Suche Formel
07.02.2022 18:19:35
Rabusch
nun gut, in der Aufgabenstellung steht: Anzahl der maximalen Kombinationen NR/KOMBI je NR
also, je NR die maximal vorhandenen Kombinationen. das sind hier bei 1 = 0, bei 2 = 2, bei 3 = 2, bei 4 = 3, bei 5 = 2, das ergibt als Ergebnis max. 3 bei einer Nr., bei welcher ist in diesem Fall egal. Eigentlich bei meiner Aufgabenstellung ist auch der Wert egal, entscheidend ist nur, ob ein Wert grösser 1 ist.
Alse gibt es bei einer Nr. mehr als eine Kombi, wobei die Leerzellen nicht betrachtet werden sollen.
Anzeige
Deutungsversuch
07.02.2022 18:58:11
KlausFz
Hallo Rabusch,
erst mal eine Deutung Deiner Wort in der anhängenden Datei.
https://www.herber.de/bbs/user/150973.xlsx
Wie das aber in eine einzige Formel zu packen ist ... vielleicht was für Werner (neopaC).
Gruß!
Klaus
Anzeige
AW: Deutungsversuch
07.02.2022 19:07:03
Rabusch
die Tabelle stimmt, bis auf die Addition, da sollte nur der Max-Wert stehen. Also, gelb = 3
Ja, das mit den maximalen Kombinationen kann man auch natürlich anders verstehen. Da gebe es aber eine Funktion für.
Ich meinte nicht die max. möglichen, sondern die in der Tabelle aufgeführten Kombinationen als max-Wert für je einer NR. und davon den max-Wert der Spalte.
Anzeige
AW: mit Hilfe von HÄUFIGKEIT() und AGGREGAT() ...
07.02.2022 19:52:13
HÄUFIGKEIT()
Hallo,
... geht es für Deine Beispieldatei sowie für ganzzahlige positive Zahlen als Nummern und viele Leerzellen im Auswertungsbereich mit folgender Formel:
=AGGREGAT(14;6;(HÄUFIGKEIT((VERGLEICH(A6:A99&B6:B99;A6:A99&B6:B99;0)=ZEILE(A6:A99)-5)*(B6:B99"")*A6:A99;ZEILE(A1:INDEX(A:A;MAX(A6:A99)))));2)
Gruß Werner
.. , - ...
Anzeige
Irgendwas passt nicht...
07.02.2022 20:12:54
{Boris}
Hi Werner,
...siehe Beispieldatei. https://www.herber.de/bbs/user/150976.xlsx
Ich würde MMULT nehmen:
=MAX(MMULT(MTRANS(((VERGLEICH(A$6:A$32&B$6:B$32;A$6:A$32&B$6:B$32;)=ZEILE($1:$27)) *(A$6:A$32=MTRANS(ZEILE(A1:INDEX(A:A;MAX(A6:A99))))) *(B$6:B$32"")));ZEILE(1:27)^0))
VG, Boris
Anzeige
AW: Irgendwas passt nicht...
07.02.2022 20:24:09
Rabusch
Hallo Werner, Hallo Boris, ich nehme meine Aussage von gerade mal wieder zurück. Habe die Formel von Werner mal auf die Bereiche angepasst.
Es scheinen beide Formeln zu funktionieren, die von Boris als Matrixformel, die von Werner als normale Formel.
Ich werde das mal ausgiebig testen.
Herzliche Dank euch allen.
lg
Anzeige
Schau Dir einfach...
07.02.2022 20:30:24
{Boris}
Hi,
...die Datei an, die ich hochgeladen habe und finde heraus, wo sich die Formeln (im konkreten Ergebnis) unterscheiden.
VG, Boris
AW: ja, aber die Ursache ist eine andere ...
08.02.2022 08:21:59
neopa
Hallo Boris,
... nicht meine Verwendung von AGGREGAT() ist dafür verantwortlich, sondern ich.
Meine Formel bedarf lediglich einer kleinen Ergänzung (nachfolgend fett markiert):
=AGGREGAT(14;6;(HÄUFIGKEIT((VERGLEICH(A6:A99&B6:B99;A6:A99&B6:B99;0)=ZEILE(A6:A99)-5)*(B6:B99"") *(A6:A99+1);ZEILE(A1:INDEX(A:A;MAX(A6:A99)))));2)
und kommt weiterhin in auch ohne {} aus, wie in den neueren Versionen. Die {} werden in den älteren Versionen lediglich (unnötigerweise) angezeigt, wenn die Formeln in der neueren Version erstellt wurde.
In noch älteren XL-Versionen als XL2010 ohne AGGREGAT() dann jedoch wirklich als Matrixformel so:
{=KGRÖSSTE(HÄUFIGKEIT((VERGLEICH(A6:A99&B6:B99;A6:A99&B6:B99;0)=ZEILE(A6:A99)-5)*(B6:B99"")*(A6:A99+1);ZEILE(A1:INDEX(A:A;MAX(A6:A99))));2)}
Gruß Werner
.. , - ...
Anzeige
AW: ja, aber die Ursache ist eine andere ...
08.02.2022 09:59:51
Rabusch
Hallo, ich probiere das mal aus.
Folgende Feststellung habe ich bisher gemacht.
Auf meine Arbeitstabellen angewandt, kommt bei der Formel von Boris die Fehlermeldung, keine ausreichenden Ressorcen schon bei einer Spalte.
ich benutze einen i7-9700k 32GB-RAM SSD
Die Formel von Werner läuft, aber braucht unheimlich Rechen-Power, Bei einer Tabelle mit 2.000 Spalten und 30.000 Zeilen ungefährt 15 Minuten bei 100% Last.
ist egal, da kann ich warten.
lg
Anzeige
AW: dafür wohl besser PQ & Pivot einsetzen, ...
08.02.2022 12:33:56
neopa
Hallo,
... wenn ich davon ausgegeben kann, das Du jede Deiner 2.000 Spalte in Kombination mit Spalte A auswerten willst, oder?
Mit PQ einfach die Spalten außer SpalteA entpivotieren, dann noch die Duplikate entfernen lassen und anschließend das Ergebnis in das Datenmodel abspeichern, welche Du danach mit Pivot auswertest.
Mich würde nun interessieren, wie lange Dein PC dafür an Zeit benötigt.
Gruß Werner
.. , - ...
Anzeige
AW: dafür wohl besser PQ & Pivot einsetzen, ...
08.02.2022 13:06:49
Rabusch
Hallo, werde ich wohl nicht machen, da ich nur alle 2-3 Monate das Ergebnis brauche. Da kann ich auch warten...
Es ist ja nicht nur eine Tabelle, sondern derzeit 12.
Überall wo man manuell eingreifen muss können Fehler entstehen.
Habe bei diesen Tabellen eh die automatische Berechnung ausgeschaltet.
Wenn einmal berechnet, werden nur die Ergebnisse anstelle der Formeln vorgehalten.
Daher kann man mit der langen Berrechnungszeit sehr gut leben.
Vielen Dank nochmal...
lg
Anzeige
AW: bitteschön, es hätte mich nur interessiert ...
08.02.2022 16:55:01
neopa
Hallo,
... ob bei Deiner auszuwertenden Datenmenge PQ und Pivot eine günstigere Alternative wäre.
Hab selbst keine Erfahrung mit Auswertung derartiger Datenmengen. Hab mir inzwischen auch die Frage gestellt, ob Du dazu möglicherweise auch eine 64Bit Version benötigst. Denn durch das entpivotieren in PQ würde ja zwischenzeitlich intern 60 Mio Datensätze generiert. Übrigens manuell ist lediglich die Definition (wie auch für die Formel) bei Datenänderung bedarf es nur eines Mausklick.
Auf jeden Fall hätte PQ+Pivot aus meiner Sicht den evtl. zusätzlichen Vorteil, dass Du nicht nur die max. Kombinationsanzahl ermittelst sondern alle je Nummer und Du auch sofort die Zuordnung der jeweiligen max Kombinationsanzahl zur Nummer auslesen könntest.
Gruß Werner
.. , - ...
Anzeige
AW: bitteschön, es hätte mich nur interessiert ...
08.02.2022 17:14:21
Rabusch
habe alles soweit in 64bit...
ansonsten, alles gut...
lg
AW: mit Hilfe von HÄUFIGKEIT() und AGGREGAT() ...
07.02.2022 20:15:40
HÄUFIGKEIT()
Hallo Werner, herzlichen Dank für die Mühen, leider funktioniert das nicht.
NR ist numerisch, KOMBI kann nummerisch oder Text sein. Das scheint aber nicht der Grund zu sein.
lg
AW: mit Hilfe von HÄUFIGKEIT() und AGGREGAT() ...
07.02.2022 20:32:27
HÄUFIGKEIT()
ok, die Formel von Boris funktioniert.
Die von Werner, funktioniert auch, aber nur wenn in NR keine 1 steht.
Nochmals HERZLICHEN DANK
lg
Anzeige
AW: bitteschön, doch sieh mein heutig. Beitrag owT
08.02.2022 08:23:08
neopa
Gruß Werner
.. , - ...
Hier noch was für XL365/XLWeb (ohne Hilfszellen)
09.02.2022 16:50:44
lupo1
=MAX(NACHZEILE(SEQUENZ(5);LAMBDA(arr;ZEILEN(EINDEUTIG(WENNFEHLER(FILTER(A6:B32;(A6:A32=arr)*(B6:B32""));))))))
(seit heute kann auch neopa LAMBDA in XLWeb testen)
etwas ingenieurmäßiger dargestellt
09.02.2022 17:10:49
lupo1
Die neuen Formeln ermöglichen es, dass nur anfangs noch mit der Außenwelt gesprochen wird. Im eigentlichen Verlauf sind nur noch interne Variablen am Werk.
Hier bedeutet "Außenwelt" einmalig A6:B32 und 1bis5. Sogar die Einzelspalten A6:A32 und B6:B32 müssen nicht mehr von der AUßenwelt kommen, da ja klar ist, wo sie sind.
=LET(
NrVonBis;SEQUENZ(5;;1);
Bereich;A6:B32;
a;INDEX(Bereich;;1);
b;INDEX(Bereich;;2);
MAX(NACHZEILE(NrVonBis;LAMBDA(arr;ZEILEN(EINDEUTIG(WENNFEHLER(FILTER(Bereich;(a=arr)*(b""));)))))))
Mit (zusätzlich äußerem) LAMBDA (benannt) würde nun eine eigene Funktion draus, an welcher der Name noch das komplizierteste wäre:
=MEISTE_VERSCHIEDENE_B_PRO_A(A6:B32;5)
Anzeige
AW: Hier noch was für XL365/XLWeb (ohne Hilfszellen)
09.02.2022 17:13:15
Rabusch
Danke sehr, funktioniert nicht in Excel 2019 (Filter-Funktion)
XLWeb ist für jeden offen (kostenlos)
09.02.2022 17:43:58
lupo1
AW: XLWeb ist für jeden offen (kostenlos)
09.02.2022 22:43:58
Luschi
Hallo lupo1,
habe mir Deine Lambda-Lösung angesehen und es schon erstaunlich, waß die neue Rechenlogik da möglich macht. Aber mit dem kostenlosen XLWeb ist das Entwickeln solch verschachtelter Formeln ein Graus, denn
- F9-Taste zum Kontrollieren von Zwischenergebnissen ist nicht drin
- kein Namensmanager, den lt. MS soll man die Lambda-Fkt. in den Namensmanager verfrachten,
  um sie noch allgemeingültiger zu machen
- die Bearbeitungszeile ist nicht vergrößerbar
- die ellenlangen Formeln werden oft aus unerfindlichen Gründen beim Schreiben in einer Zelle so dargestellt,
  daß sich die Zeilenhöhe stark vergrößert, aber nicht in die leere Nachbarzelle hineinreicht.
- die angebotene Hilfe ist noch sehr stark von den englischen Funktionsnamen geprägt
  wie ByRow() statt Nachzeilen() und Komma statt Semikolon zur Parametertrennung.
- sollte M$ die Lambda-Funktion nicht wenigstens in Excel 2021 nachziehen, dann sind solche
  Fortschritte nur marginales Stückwerk.
Gruß von Luschi
aus klein-Paris
Anzeige
AW: Suche Formel
09.02.2022 17:17:21
Rabusch
Herzlichen Dank an alle, ihr habt mir sehr geholfe.
Noch eine kleine Anmerkung zu den Formeln, die funktionieren nicht, wenn ein zu hoher Wert in NR (Spalte A) eingegeben wird. z.B 8-stellig.
Ich habe mir aber dabei selbst helfen können.
lg
Ein Klacks als Anpassung (Inhalt A:A völlig egal)
09.02.2022 17:43:10
lupo1
=LET(
Bereich;A6:B32;
a;INDEX(Bereich;;1);
b;INDEX(Bereich;;2);
aa;EINDEUTIG(a);
MAX(NACHZEILE(aa;LAMBDA(arr;ZEILEN(EINDEUTIG(WENNFEHLER(FILTER(Bereich;(a=arr)*(b""));)))))))
spart sogar noch den einen der beiden "Außenwelt-Kontakte" ein.
Anzeige
AW: bitteschön und ...
10.02.2022 10:46:08
neopa
Hallo Rabusch,
... prima, dass Du Dir schon selbst über das Problem mit sehr hohen Werten in Deiner Spalte A geholfen hast. (vermute ich richtig, dass Du nur große Zahlenwerte da zu stehen hast?)
Das Problem mit meiner oder auch Boris Formel bei einem sehr großen Wert in Spalte A erklärt sich ganz einfach. Mit dem Formelteil:
=ZEILE(A1:INDEX(A:A;MAX(A:A))) wird ja lediglich eine Zahlenbereich von 1 bis 10485576 (die max Zeilennummer in Excel) ausgewertet.
Gruß Werner
.. , - ...
Anzeige
AW: bitteschön und ...
10.02.2022 13:17:22
Rabusch
Die Vermutung stimmt, Bereich ist 1-11-stellig, Betrifft von meinen Tabellen nur 2, mit z.Z. 8 Werten. Die kann man auch temporär händisch ändern. Das geht schon.
Hatte aber auch erst dumm aus der Wäsche geguckt als das die Fehlermeldung #BEZUG! kam.
Herzlichen Dank für die Erklärung.
lg
Anzeige
AW: gerne owT
10.02.2022 14:14:30
neopa
Gruß Werner
.. , - ...
;

Beliebteste Forumthreads (12 Monate)

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