Microsoft Excel

Herbers Excel/VBA-Archiv

Formel gesucht


Betrifft: Formel gesucht von: Toni
Geschrieben am: 27.01.2019 11:32:46

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

  

Betrifft: mal ein grundsätzlicher Tip von: WF
Geschrieben am: 27.01.2019 12:51:19

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


  

Betrifft: AW: mal ein grundsätzlicher Tip von: Toni
Geschrieben am: 27.01.2019 13:01:46

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


  

Betrifft: offen ... von: Toni
Geschrieben am: 27.01.2019 13:27:19




  

Betrifft: mit VERGLEICH von: WF
Geschrieben am: 27.01.2019 13:39:59

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


  

Betrifft: AW: ermittelst 6 statt richtig 5 "Treffer" owT von: neopa C
Geschrieben am: 27.01.2019 13:44:22

Gruß Werner
.. , - ...


  

Betrifft: na und - er wollte WAHR und er hat WAHR von: WF
Geschrieben am: 27.01.2019 14:10:55

.


  

Betrifft: Vielen Dank WF! owT von: Toni
Geschrieben am: 27.01.2019 14:21:19




  

Betrifft: AW: 6 "Treffer" waren doch richtig; denn s. u. owT von: neopa C
Geschrieben am: 27.01.2019 16:33:17

Gruß Werner
.. , - ...


  

Betrifft: hat aber lang gedauert von: WF
Geschrieben am: 27.01.2019 16:46:33

.


  

Betrifft: AW: AGGREGAT() in einer klass. MatrixFormel ... von: neopa C
Geschrieben am: 27.01.2019 13:33:30

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
.. , - ...


  

Betrifft: AW: AGGREGAT() in einer klass. MatrixFormel ... von: Toni
Geschrieben am: 27.01.2019 14:20:34

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


  

Betrifft: AW: MMULT() führt hier nicht zum Listing, ... von: neopa C
Geschrieben am: 27.01.2019 16:31:49

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
.. , - ...


  

Betrifft: AW: MMULT() führt hier nicht zum Listing, ... von: Toni
Geschrieben am: 27.01.2019 18:07:58

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


  

Betrifft: AW: statt MMULT() mit geschachtelt. AGGREGAT() ... von: neopa C
Geschrieben am: 28.01.2019 11:00:58

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
.. , - ...


  

Betrifft: AW: statt MMULT() mit geschachtelt. AGGREGAT() ... von: Toni
Geschrieben am: 28.01.2019 18:31:35

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


  

Betrifft: AW: hierzu ... von: neopa C
Geschrieben am: 29.01.2019 19:00:00

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
.. , - ...


  

Betrifft: AW: hierzu ... von: Luschi
Geschrieben am: 29.01.2019 19:41:38

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ß
- 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.


  

Betrifft: AW: hierzu ... von: Toni
Geschrieben am: 30.01.2019 13:33:29

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 :)


  

Betrifft: AW: möglicherweise hast Du überlesen, ... von: neopa C
Geschrieben am: 31.01.2019 06:08:49

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
.. , - ...


  

Betrifft: Danke für die Info owT Grüße! von: Toni
Geschrieben am: 30.01.2019 13:13:48




Beiträge aus dem Excel-Forum zum Thema "Formel gesucht"