Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.04.2024 17:05:40
Zählen von Zeilen, in denen in bestimmten Bereich Daten sind
19.04.2024 16:34:52
gemeinsamer Filter über drei Spalten
19.04.2024 15:39:42
Exceltabelle daten per combobox zu word Datei
19.04.2024 14:04:05
PDF per VBA ausgeben - Seitenskalierung
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1668to1672
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1668to1672
1668to1672
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formel gesucht

Formel gesucht
27.01.2019 11:32:46
Toni
Hi Excelgemeinde,
einen schönen Sonntag Euch!
Mich treibt heute eine Matrixformel um, und ich weiß, hier habe ich etwas grundsätzlich noch nicht verstanden und zwar:
Es geht mal wieder um einen Listenabgleich, diesmal innerhalb einer Formel. Die erste Liste wird innerhalb einer M-Formel erzeugt und soll dann mit einem anderem Listenbereich (z.B: über =$G$2:$G$15) abgeglichen werden. Ersteres klappt wunderbar, im Abgleich stellt die Formel aber dann den geregelten Betrieb ein.
Ich vermute es liegt an der unterschiedlichen Dimensionierung der beiden Arrays. Doch wie würde man lösen, dass die Formel WAHR ausspuckt, wenn sie ein Datum aus Liste2 in der ersten findet? Der zweite Teil ist im Beispiel anbei beschränkt auf G2:G15. Die Endformel sollte jedoch auch andere Bereichsbegrenzungen zulassen (G2:G4, G2:G199) ...
Habt Ihr dazu eine Idee?
https://www.herber.de/bbs/user/127176.xlsx
lGrüße
Toni

20
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
mal ein grundsätzlicher Tip
27.01.2019 12:51:19
WF
Hi,
ich blicke zwar nicht durch, was Du berechnen willst; - statt aber:
... (14*{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26}) ...
schreibt man:
... (14*ZEILE(1:26)) ...
WF
AW: mal ein grundsätzlicher Tip
27.01.2019 13:01:46
Toni
Hallo WF,
Danke für den Tip, das macht es überschaubarer und da die Formel in real nochmal länger ist, ist das sehr hilfreich!
zu meiner Fragestellung nochmal:
aus der Formel in C2 erhalte ich (F9) diese Liste hier:
{0.43488.0.43490.43491.43492.43493;0.43502.0.43504.43505.43506.43507;0.43516.0.43518.43519.43520.43521;0.43530.0.43532.43533.43534.43535;0.43544.0.43546.43547.43548.43549;0.43558.0.43560.43561.43562.43563;0.43572.0.43574.43575.43576.43577;0.43586.0.43588.43589.43590.43591;0.43600.0.43602.43603.43604.43605;0.43614.0.43616.43617.43618.43619;0.43628.0.43630.43631.43632.43633;0.43642.0.43644.43645.43646.43647;0.43656.0.43658.43659.43660.43661;0.43670.0.43672.43673.43674.43675;0.43684.0.43686.43687.43688.43689;0.43698.0.43700.43701.43702.43703;0.43712.0.43714.43715.43716.43717;0.43726.0.43728.43729.43730.43731;0.43740.0.43742.43743.43744.43745;0.43754.0.43756.43757.43758.43759;0.43768.0.43770.43771.43772.43773;0.43782.0.43784.43785.43786.43787;0.43796.0.43798.43799.43800.43801;0.43810.0.43812.43813.43814.43815;0.43824.0.43826.43827.43828.43829;0.43838.0.43840.43841.43842.43843}
Wie die Daten da rein kommen habe ich bereits geprüft. Das stimmt soweit.
Nun soll einfach in dieser Liste nach Daten gesucht werden, die in Liste2 (Bereich G2:G15) stehen. Wenn ein Wert Liste1 in Liste2 gefunden wird, dann WAHR ...
Grüße
Toni
Anzeige
offen ...
27.01.2019 13:27:19
Toni
mit VERGLEICH
27.01.2019 13:39:59
WF
Hi,
ohne die Formel jetzt einzudampfen:
{=ANZAHL(VERGLEICH(WENNFEHLER(($A$2+6)+(14*{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22; 23;24;25;26})+WECHSELN((WENN((TEIL(WECHSELN(RECHTS(B2;7);"k";0);{1.2.3.4.5.6.7};1)*{1.2.3.4.5.6.7}) >0;(TEIL(WECHSELN(RECHTS(B2;7);"k";0);{1.2.3.4.5.6.7};1)*{1.2.3.4.5.6.7});" "));".";";");0); $G$2:$G$15;0))> 0}
WF
AW: ermittelst 6 statt richtig 5 "Treffer" owT
27.01.2019 13:44:22
neopa
Gruß Werner
.. , - ...
na und - er wollte WAHR und er hat WAHR
27.01.2019 14:10:55
WF
.
Vielen Dank WF! owT
27.01.2019 14:21:19
Toni
AW: 6 "Treffer" waren doch richtig; denn s. u. owT
27.01.2019 16:33:17
neopa
Gruß Werner
.. , - ...
hat aber lang gedauert
27.01.2019 16:46:33
WF
.
AW: AGGREGAT() in einer klass. MatrixFormel ...
27.01.2019 13:33:30
neopa
Hallo Toni,
... diese Besonderheit (meist bedarf eine AGGREGAT()-Formel nicht des spez. Abschlusses einer klassischen Matrixformel) wird hier wegen der spezif. Funktionskombination im 3. Argument der AGGREGAT()-Formel notwendig (u.a. auch um die Formel möglichst kurz zu halten).
Du erhältst mit folgender Matrixformel, die Du ziehend nach unten kopieren kannst, eine Auflistung der Datumswerte aus G2:G99 die zutreffend sind.
{=WENNFEHLER(AGGREGAT(15;6;G$2:G$15/(MMULT(0+
ISTZAHL(VERGLEICH(A$2+6+WENNFEHLER(14*ZEILE(A$1:A$26)+TEIL(RECHTS($B$2;7);{1.2.3.4.5.6.7};1)
*{1.2.3.4.5.6.7};0);G$2:G$99;0));{1;2;3;4;5;6;7})>0);ZEILE(A1));"")
Gruß Werner
.. , - ...
Anzeige
AW: AGGREGAT() in einer klass. MatrixFormel ...
27.01.2019 14:20:34
Toni
Hallo WF und Werner!
... und er hat nicht nur WAHR:
Datenliste Treffer:
43493 x
43502 x
43521 x
43577 x
43586 x
43615
43625
43626
43741
43769
43789
43824 x
43825
43834
Das Wichtigste aber, und dafür meinen größten Dank an Euch beide!!!, ist der Ansatz mit Vergleich. Damit kann ich bestens weiter rechnen und bleib in der Logik, der meine Datei bislang folgt (die ganze Formel wollte ich hier nicht einstellen, dann wärt Ihr vermutlich aus den Socken gekippt, weil zu lang zu unsinnig etc.)
Also: herzlichen Dank, dass Ihr Euch da reingedacht habt und dass es ein brauchbares Ergebnis geworden ist.
@Werner, Zeilen nach unten kopieren geht nicht, weil die Liste am Ende zig Zeilen hat. Konntest du nicht wissen ... Trotzdem genau was ich gesucht habe mit ein wenig Anpassungen! Und MMult bzw. generell die Aggregat werde ich mir mal in einer ruhigen Minute genauer ansehen. Das ist noch Neuland für mich.
lGrüße
na und :))))
Toni
Anzeige
AW: MMULT() führt hier nicht zum Listing, ...
27.01.2019 16:31:49
neopa
Hallo Toni,
... wie ich es anstreben wollte und dies jetzt feststellte. Dies ist mit weniger Datenwerten leicht nachvollziehbar.
Die Teilmatrixgröße von VERGLEICH() wird ja durch die Matrix von ZEILE(A1:A26) bestimmt und damit wiederum die Größe des MMULT()-Ergebnismatrix, die dadurch für ein Ergebnislisting der Datumswerte ungeeignet ist.
Dazu müsste man dann dafür anders vorgehen. Dazu würde mich zunächst interessieren, ob es überhaupt notwendig ist, eine derartige zweidimensionale Matrix überhaupt aufzubauen, wie von Dir vorgegeben und ich diese eingekürzt hatte.
Welche Datumswerte willst Du denn auf welcher Bedingungsgrundlage (bitte mal verbal definieren), denn mit der vorhandenen Datumsmatrix in Spalte G vergleichen?
Zur ausschließlichen Ermittlung von WAHR oder FALSCH ist allerdings ein Teil meiner Formel natürlich nutzbar und dies auch für ein größere matrix in Spalte G, z.B. für G2:G98:
{=ANZAHL(VERGLEICH(A$2+6+WENNFEHLER(14*ZEILE(A$1:A$26)+TEIL(RECHTS($B$2;7);{1.2.3.4.5.6.7};1)
*{1.2.3.4.5.6.7};0);G$2:G$99;0))>0}
Gruß Werner
.. , - ...
Anzeige
AW: MMULT() führt hier nicht zum Listing, ...
27.01.2019 18:07:58
Toni
Hallo Werner,
bin gerade erst rein gekommen und habe Deinen Beitrag erst jetzt gelesen. Vorweg: ich bin jetzt schon vollauf glücklich und zufrieden über die Ergebnisse des Threads, ist also nichts mehr offen ...
Hintergrund des Ganzen ist, mit dem ersten Formelteil ein ganzes Jahr aufzubauen (in dem Fall hier 2-wöchentlich, es kommen später noch weitere Prüfungen hinzu ...) und diese Tage dann gegen eine Stichtagsliste (in Spalte G) zu stellen. Mit Eurer Hilfe ist das jetzt super möglich.
Die Dimensionierung in 7 (Woche) und 26 (Anz. Wochen 2-wöchentlich) ist nicht zwingend notwendig, stört aber aber auch nicht, weil ich am Ende des Tages das Ganze in eine Summen-Matrix packe, um zu berechnen, wie viele Tage eingedenk aller Bedingungen dann fürs Jahr noch übrig bleiben.
Eventuell muss ich später die Performance der Gesamtdatei berücksichtigen, aber bis jetzt bleibt noch alles im Rahmen. Diese Fragestellungen würde ich gern vorerst vertagen, weil ich noch nicht weiß, wie das Endprodukt dann konkret aussehen wird. Damit sind all meine Unklarheiten soweit beseitigt.
und ich bedanke mich für Deine/Eure Hilfe!
Grüße
Toni
Anzeige
AW: statt MMULT() mit geschachtelt. AGGREGAT() ...
28.01.2019 11:00:58
neopa
Hallo Toni,
... auch wenn Du Dein thread schon als für Dich zufriedenstellend geklärt siehst, werde ich nun hier doch noch meinen Fehler bei der Ergebnisdatumsauflistung korrigieren.
Mit folgender Formel in E2 kann man auch bei Deinen bisher getroffenen Vorgaben (die mir nach wie vor noch etwas unklar sind) ein Ergebnislisting vornehmen:
Arbeitsblatt mit dem Namen 'Tabelle1'
 ABE
1JahrersterVat-Wert6
201.01.2019Deto/23iiq_k1k111128.01.2019
3  06.02.2019
4  25.02.2019
5  22.04.2019
6  01.05.2019
7  25.12.2019
8   

ZelleFormel
E1{=ANZAHL(VERGLEICH(A$2+6+WENNFEHLER(14*ZEILE(A$1:A$26)+TEIL(RECHTS($B$2;7);{1.2.3.4.5.6.7};1)*{1.2.3.4.5.6.7};0);G$2:G$15;0))}
E2{=WENNFEHLER(AGGREGAT(15;6;G$2:G$15/ISTZAHL(VERGLEICH(G$2:G$15;AGGREGAT(15;6;A$2+6+WENNFEHLER(14*ZEILE(A$1:A$26)+TEIL(RECHTS($B$2;7);{1.2.3.4.5.6.7};1)*{1.2.3.4.5.6.7};0);ZEILE(A$1:A$399));0));ZEILE(A1));"")}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
AW: statt MMULT() mit geschachtelt. AGGREGAT() ...
28.01.2019 18:31:35
Toni
Hallo Werner,
hat ne Weile gedauert bei mir mit dem Verständnis ...
Da ich mir die Fehlerwerte in der Praxis immer wieder mal rausstreiche ist die Aggregat sehr nützlich bzw. 'zuvorkommend'. Ich habe die Funktion bisher gemieden, weil ich vermutete, dass sie bei großen Datenmengen eher behebig ist weil sie ja so viele Funktionen in sich vereint - das werde ich mir ggfs noch genauer ansehen.
Dass man aden ersten Teil meiner Formel mit der Aggregat umdimensionieren kann macht die Umkehrung der Parameter innerhalb der Vergleichsfunktion möglich - ausgesprochen spannend und für meinen Fall gut wiederverwertbar :)) Die Begrenzung der Zeile() auf 399 ist etwas Vorratshaltung, wie mir scheint.
Ich danke Dir für diese Lehrstunde und dass du nicht locker gelassen hast. War's wert!
lG
Toni
Anzeige
AW: hierzu ...
29.01.2019 19:00:00
neopa
Hallo Toni,
... ich teile Deine Meinung, das der Einsatz von AGGREGAT() sehr nützlich ist bzw. sein kann. Sie ermöglichte mir bisher jedenfalls Formeldefinitionen, die mir ohne diese Funktion sonst wesentlicher schwerer fallen würden. Mit Hilfe dieser Funktion kommt man auch in den überwiegenden "Normal"anwendungen auch ohne den spez. Matrixformelabschluss aus. Damit ist eine solche Auswertung auch oft schneller als vergleichbare klassische Matrixformeln. Denn diese Funktion ist bereits für derartige Auswertungen programmiert. Genauso wie z.B, eine mögliche SUMMENPRODUKT()-Formel schneller auswertet als eine vergleichbare {SUMME()}-Formel.
Trotzdem bedürfen derartige Formeln, ich nenne diese Matrixfunktion(alität)sformeln, viele Ressourcen und sind deshalb auch nicht für Massendatenauswertung zu empfehlen.
Und ja, die "Zuordnung" von max 399 auszuwertenden Daten, hab ich einfach auf das doppelte des mind. erforderlichen also =26*7*2 ausgewiesen
Gruß Werner
.. , - ...
Anzeige
AW: hierzu ...
29.01.2019 19:41:38
Luschi
Hallo Toni & Werner,
schön, daß ihr eine Formel-Lösung für das aktuelle Problem gefunden habt, aber so ganz NORMAL kann ich diese Formel-Lösungen nicht finden:
- Aggregat mit 1. Parameter >=14
- und dann den speziellen Matrix-Abschluß Userbild
- solche statischen Array-Konstrukte {1.2.3.4.5.6.7}
- Multiplikationen wie 14*ZEILE(A$1:A$26)
sind doch nur für den Momentzustand zufriedenstellend und jede Änderung des Ausgangszustands macht doch Einiges an Nachjustierung erforderlich.
Deshalb ist eine professionellere Lösung angesagt, entweder:
- PowerQuery mit der integrieren M-Sprache
- AdoDB mit SQL-Statements
- oder gleich Datenbestand in Access/SQL-Server
Gruß von Luschi
aus klein-Paris
Mir ist bewußt, daß beide Varianten einen enormen Lernaufwand für den User bedeuten, aber sich von Formel zu Formel per Excel-Forum zu hangeln und erst dann, wenn daß Problem größere Ausmaße annimmt, sich nach Alternativen umzuschauen ist doch wohl reichlich spät.
Anzeige
AW: hierzu ...
30.01.2019 13:33:29
Toni
Hi Luschi,
... einen der Ansätze, die ich bislang verfolgt habe (UDF, Code, M-Formel) wird es (vielleicht sogar im Mix) geben, das Ganze ist gut beherrschbar. Prinzipiell geht es mir neben der beruflichen Verwertbarkeit aber mehr noch um den Erkenntnisgewinn bzw. den Spaß an der Sache. Das war im Fall der Formel vom Werner wieder mal ganz klar der Fall.
Trotzdem interessieren mich deine bzw. immer interessieren mich neue und ggfs. Ansätze, zielkonformere Herangehensweisen sehr!
Schwebt Dir da schon eine konkretere Lösung vor oder empfiehlst Du es auf Grund der schieren Datenmenge? Wenn Letzteres - also eher allgemein gehalten - : kannst Du spezielle Seiten zu diesen Themen empfehlen , vllt sogar tendentiell Einstiegskost? Ich schau natürlich selbst parallel immer, habe nur die Erfahrung gemacht, dass hier im Forum oft gute Seiten empfohlen werden, die mir in meiner eigenen Suche vorher nicht untergekommen sind. ...
Wäre sehr nett!
beste Grüße in die Stadt der etwas kleineren (aber der Größeren in nichts nachstehenden?) Liebe!! ...
Toni :)
AW: möglicherweise hast Du überlesen, ...
31.01.2019 06:08:49
neopa
Guten Morgen Luschi,
... was ich hier im thread sowohl zur Problemstellung. wie zu meinem ersten Lösungsvorschlag geschrieben hab. Beides gilt aus meiner Sicht hier auch weiterhin. Zu AGGREGAT()-Formeln jedenfalls meine ich, aussagefähiger zu sein.
Ich bezweifele nicht, dass auch Lösungen mit anderen Methoden möglich wären. Aber dabei sollte man auch beachten, dass man diese zur Verfügung haben und auch beherrschen sollte und diese dann auch verhältnismäßig sein. Wer hat hat wirklich z.B. Access und einen SQL-Server gleich zur Verfügung. Eine PQ-Lösung würde mich schon auch interessieren, wenn mir die Aufgabenstellung 100% klar wäre.
Wenn Du damit also eine derartige Lösung siehst, hättest Du sie auch aufzeigen sollen.
oT. Du hast Dich übrigens noch nicht zu meinen letzter Aussage hier: https://www.herber.de/forum/archiv/1668to1672/t1669736.htm geäußert.
Wie auch immer, ich könnte jetzt für ein paar Tage auf keine Aussage und oder Frage reagieren.
Gruß Werner
.. , - ...
Danke für die Info owT Grüße!
30.01.2019 13:13:48
Toni

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige