02.02.2020, 14:40 (Dieser Beitrag wurde zuletzt bearbeitet: 02.02.2020, 14:45 von ImC.)
Lieber Andre,
Zitat:Wenn dort der Bereich eingegeben wird wo in Gruppenzuordnung noch nicht ausgefüllt ist, kommt als Ergebnis 0, statt der aktuelle Wert mit dem Datum in Spalte D.
Es ist so gemeint: dort zeigt es auch 0 für die Daten (Datum) wo Werte drin stehen. z.B. in J22, Wenn dort in der Formel ...Summenprodukt, der Bereich Gruppenzuordnung über Zeile 19 hinaus z.B. bis 99 geändert wird zeigt es in J22 0, obwohl für dieses Datum Werte drin stehen.
z.B. J23: =WENNFEHLER(VERWEIS(2;1/((Basis_f_Geschenke!$C$6:$C$100=J$21)*(Basis_f_Geschenke!$B$6:$B$100<=$D23)*(ZEICHEN(SUMMENPRODUKT((Gruppenzuordnung!$C$5:$C$99=J$21)*($D23>=Gruppenzuordnung!$A$5:$A$99)*($D23<=Gruppenzuordnung!$B$5:$B$99)*Gruppenzuordnung!$E$5:$E$99))=$D$2));Basis_f_Geschenke!$E$6:$E$100);0)
==> Ergebnis 0
Wenn hier statt 99, 16 drin steht, geht es, da in Gruppenzuordnung bis Zeile 19 gefüllt ist, die anderen Zeilen sind noch ! leer.
Es wäre sinnvoll eine Formel, wo der Bereich, der noch nicht gefüllt angegeben werden kann, z.B. bis 99 oder 999, damit nicht bei jedem neuen Eintrag die Formel zu erweitern ist.
05.02.2020, 20:29 (Dieser Beitrag wurde zuletzt bearbeitet: 05.02.2020, 20:29 von ImC.)
Lieber Andre,
idiese in L22: (fett und grösser ist ergänzt) =WENNFEHLER(VERWEIS(2;1/((Basis_f_Geschenke!$C$6:$C$100=L$21)*(Basis_f_Geschenke!$B$6:$B$100<=$D22)*(Gruppenzuordnung!$A$1:$E$99<>"")*(ZEICHEN(SUMMENPRODUKT((Gruppenzuordnung!$C$5:$C$16=L$21)*($D22>=Gruppenzuordnung!$A$5:$A$16)*($D22<=Gruppenzuordnung!$B$5:$B$16)*Gruppenzuordnung!$E$5:$E$16))=$D$2));Basis_f_Geschenke!$E$6:$E$100);0)
Bringt leider in den ersten beiden Zeilen 0 danach stimmen die Ergebnisse.
Womöglich gibt es noch eine Formel die die Ergebnisse ganz liefert.
Vielen DANK.
LIEBE C
Korrektur: wenn es über Zeile 16 hinaus geht stimmen die Ergebnisse nicht, bis Einträge Zeile 16 stimmt es.
eine einfache Variante für eine unterschiedlich lange Liste wäre die Nutzung einer intelligenten Tabelle. Schaue mal die Formel an und die Tabelle1 auf dem anderen Blatt.
. \\\|/// Hoffe, geholfen zu haben. ( ô ô ) Grüße, André aus G in T ooO-(_)-Ooo (Excel 97-2019+365)
Lieber Andre, das ist ja ein sehr feine Idee. :97: Vielen DANK.
Da ist nicht mehr auf die gleiche Anzahl der Zeilen in jedem Term zu achten, sondern einfach die Spaltenüberschrift anzugeben. Sehe ich das richtig?
Ein paar Anmerkungen bzw. Fragen:
Formel lässt sich nicht nach rechts kopieren? D.h. z.B. 1 Spalte nach rechts kopieren, dann wird 1 Spalte rechts in der Quell-Tabelle verwendet. Statt [Name] steht dann [Gruppe] Wie lassen sich die Spalten der Quelltabelle in der Formel fixieren?
die Tabelle Gruppenzuordnung (Tabelle1 ist so benannt) erweitert sich nicht bei neuen Einträgen. Ist da noch was einzustellen? Bei der intelligenten Tabelle Basis_Geschenke erweitert es sich bei neuen Einträgen von selbst. Das sollte doch bei Gruppenzuordnung auch gehen.
zu dieser Formel Verweis: a) Sortierung: es muss der Reihe nach aufsteigend sortiert sein, dies geht mit der intelligenten Tabelle leicht, sollte auch für Anwender leicht gehen. Frage: welche Spalte ist aufsteigend zu sortieren? Es sind ja mehrere Kriterien in der Formel.
Es können an einem Datum mehrere Einträge gegeben sein. Das dürfte für Verweis o.k. sein oder?
b) es ist gut, wenn mehrere Zeichen als Gruppenname anwendbar sind. z.B. AA, AB, AC, oder A01, A02
Folgende Formel bringt #Wert, in J29; Bei Max ist nicht auf Sortierung zu achten, daher wurde diese verwendet:
=MAX(WENN((Basis_Geschenke[Datum]<=A_Zwischenstand_Verlauf_Gruppe!$D29)*(Basis_Geschenke[Datum]<>"")*(Basis_Geschenke[Name]=A_Zwischenstand_Verlauf_Gruppe!J$21)*(Gruppenzuordnung[Name]=J$21)*($D29>=Gruppenzuordnung[von])*($D29<=Gruppenzuordnung[bis])*Gruppenzuordnung[Gruppe]=$D$2;Basis_Geschenke[akt_Stand bis zum jeweiligen Datum]))
#Wert: Womöglich liegt es an Gruppe und Datum. Gibt es eine Idee wie diese funktioniert oder auch gerne eine andere?
mal noch eine Zwischenfrage. Sollen die Gruppen immer zwei Buchstaben / Zeichen enthalten? Falls nicht, müsste ich wohl eine Fallunterscheidung verformeln, was ich ansonsten sparen könnte
. \\\|/// Hoffe, geholfen zu haben. ( ô ô ) Grüße, André aus G in T ooO-(_)-Ooo (Excel 97-2019+365)
zu 2. die Erweiterung der intelligenten Tab. geht jetzt bei neuen Einträgen von selbst. Es war vorher für 1 Spalte ein Dropdownfeld bis Zeile 99 zur Verfügung gestellt, welches nicht leer als Default hatte. Es sieht so aus als ob wirklich alles drum herum oder zumindest unterhalb Leer sein sollte wenn so ein Bereich als so eine Tabelle formatiert wird. Das Feld wurde gelöscht bzw. als leer angeboten. Die Tabelle nochmal angelegt, jetzt geht es.
zu 3a) zu dieser Formel Verweis: Sortierung: aufsteigend. ja habe ich gelesen. In einem Forum mit dunkel blauem Hintergrund - weiss den Link nicht mehr, ist schon bischen länger her. Ist ein Forum, das es schon lange gibt. Der Autor hat es als wesentliche Info mitgeteilt, dass die Formel so wie sie hier angewendet wird, anzuwenden ist für: Nur wenn ich Werte einordnen will und und die Suchwerte aufsteigend sortiert sind!
Es könnte excelformeln.de gewesen sein. Die Seite ist im Moment nicht online. Mehr weiss ich grad nicht, sorry.
Für alle die hier mitlesen und mehr zu "Verweis /den nächst kleineren Wert des Suchkriteriums anzeigen" wissen möchten: eine hilfreiche Beschreibung der Formel gibt es hier: https://www.herber.de/forum/archiv/1224t...ktion.html
