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

noch ein Aggregat problem

noch ein Aggregat problem
02.10.2017 17:43:25
Thomas
Hallo excelfreunde,
nachdem Ihr mir hier heut schon richtig heftig bei meinen Formeln geholfen habt, habe ich noch eine Frage zur Anpassung an eine Formel.
Es geht wieder um Aggregat
mit den folgenden Formeln ( nur eine geht ) kann man eine Liste nach Art "sverweis mit mehreren Ergebnissen" erstellen. Die erste Formel funktioniert aber die zweite leider noch nicht.
Besser ist es in der Datei erklärt.
Es geht um die Formeln in i11 und in j11 ( i11 funktioniert schon)
könnt ihr hier auch nochmal rüberschauen? Ich denke es geht so ähnlich wie das vorherige aber ich rafe es einfach nicht.
Formeln:
mit einer Bedingung:
=WENNFEHLER(INDEX(Personendaten[wert1];AGGREGAT(15;6;ZEILE(Personendaten[wert1]) -1/(Anschriften[Anschrift]>$I$5);ZEILE()-10);1;1);"")
mit mein reingesetztes und ( zwei Bedingungen )
=WENNFEHLER(INDEX(Personendaten[wert2];AGGREGAT(15;6;UND((ZEILE(Personendaten[wert2]) -1/(Anschriften[Anschrift]>$I$5));(ZEILE(Personendaten[wert2])-1/(Anschriften[Anschrift]

Die Datei https://www.herber.de/bbs/user/116698.xlsx wurde aus Datenschutzgründen gelöscht

habt schon mal recht vielen vielen dank für euer Interesse.
liebe grüsse thomas

18
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: ... ist keins ...
02.10.2017 18:03:01
...
Hallo Thomas,
.. das ist die gleiche Formel wie für den Nachnamen. Nur eine andere Zuweisung.

=WENNFEHLER(INDEX(Personendaten[wert2];AGGREGAT(15;6;ZEILE(Personendaten[wert1])
-1/(Anschriften[Datum]>$I$5);ZEILE(A1));1;1);"")

Gruß Werner
.. , - ...
AW: dazu nun noch ...
02.10.2017 18:32:32
...
Hallo Thomas,
... sorry, ich hatte mich vorhin auf Deine Aussage verlassen, dass die Formel I würde schon funktionieren.
Du willst aber ja zwei Bedingungen berücksichtigen und die hast Du in I11 noch nicht.
Also in I11:
=WENNFEHLER(INDEX(Personendaten[wert1];AGGREGAT(15;6;ZEILE(Personendaten[Geburtort])
-1/(Anschriften[Datum]>=$I$5)/(Anschriften[Datum]
und diese nach rechts und unten kopieren.
Gruß Werner
.. , - ...
Anzeige
AW: noch ein Aggregat problem
02.10.2017 18:06:10
Frank
Hallo, ohne AGGREGAT(), dafür mit lupo1-Power..:
Arbeitsblatt mit dem Namen 'Personendaten'
 HIJKL
5von02.01.2015 
6bis04.01.2015 
7 Wohnen zur Zeit die folgenden Personen   
8     
9     
10 NameVornameGeb.-DatumGeb.-Ort
113Nachname_2Vorname_202.01.2000Ort_2
124Nachname_3Vorname_303.01.2000Ort_3
135Nachname_4Vorname_404.01.2000Ort_4
14#NV    
15#NV    
16#NV    
17#NV    
18#NV    
19#NV    
20#NV    

ZelleFormel
H11=VERGLEICH(1;INDEX(--(A$999:INDEX($A$1:$A$27;H10+1)>=$I$5)*(A$999:INDEX($A$1:$A$27;H10+1)=$I$6););)+H10
I11=WENNFEHLER(INDEX(B:B;$H11);"")
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Anzeige
AW: und hierzu ...
02.10.2017 18:34:39
...
Hallo Frank,
... ich nehme an, dass Lupo bestätigen dürfte, dass er den entsprechende Einsatz von INDEX() als Matrixformelersatz mal bei mir "aufgeschnappt" hat.
Egal ob mit Hilfe AGGREGAT() oder mit Hilfe von INDEX(), diesen Typ Formeln der ohne klasische Matrixformeln das Gleiche wie diese in ähnlicher Art ermittelt, bezeichne ich als Matrixfunktion(alität)sformel.
Auch in der INDEX()-Formelvariante beiden kann man übrigens auf die Verwendung der Feldnamen der "intelligente" Tabelle zurückgreifen, was eine unnötig zu großen Zelldefinitionsbereich in der Formel erspart und sich bei Erweiterung der Tabelle gleichzeitig automatisch an diese anpasst.
Gruß Werner
.. , - ...
Anzeige
AW: und hierzu ...
02.10.2017 18:42:39
Frank
Hallo Werner, hauptsächlich geht's (mir) hierbei um eine Hilfsspaltenlösung ohne AGGREGAT() und die hab ich bei lupo1 so zuerst gesehen...
AW: egal ob mit oder Hilfspalte ...
02.10.2017 19:06:25
...
Hallo Frank,
... in beiden Fällen können beide Versionen eingesetzt werden.
Und für die Hilfsspaltenlösung wäre in Deiner Schreibweise (ohne Bezug auf "intelligente" Tabelle) würde ich auch hier die AGGREGAT()formel sogar bevorzugen.
=AGGREGAT(15;6;ZEILE(A$2:A$99)/(A$2:A$99>=$I$5)/(A$2:A$99 Ich würde allerdings das WENNFEHLER() um diese ranken und den INDEX()-Formeln in ein =WENN(H11="";"";INDEX(...) )einbinden.
Gruß Werner
.. , - ...
Anzeige
AW: egal ob mit oder Hilfspalte ...
02.10.2017 19:17:21
Frank
Hallo Werner, ob du das jetzt glaubst oder nicht (wahrscheinlich nicht, sonst würdest du das ja so nicht schreiben), aber ich kann das nachvollziehen - ganz so blöde bin ich nicht!!! ... aber wenn man nicht eine andere Meinung vertreten darf, bin ich hier wohl falsch. Ich schrieb ja bereits, ich will nicht streiten. Was ist schlimm daran eine Alternative zu posten..? Auch will ich nicht das letzte Wort haben, (also) ist der Thread für mich hier erledigt...
AW: nun ...
02.10.2017 20:18:28
...
Hallo Frank,
... da haben wir uns wohl beide etwas missverstanden. Natürlich hab ich keine Einwände gegen Alternativen, ich biete ja oft selbst welche an. Ich hatte auf Deinen ersten Beitrag hier im thread nicht wegen Deiner Alternative sondern wegen dessen begleitender Bezeichnung geschrieben. Der weitere Beiträge hier war meinerseits wiederum nur das Aufzeigen (m) einer Alternative zu Deiner.
Ich hoffe, dass ich mit diesen Beitrag etwas zur Aufklärung Dir gegenüber beigetragen habe.
Gruß Werner
.. , - ...
Anzeige
"intelligente" Tabellen ...
02.10.2017 18:48:02
Frank
Hallöchen, ... haben aber nicht nur Vorteile...
AW: im vorliegendem Beispiel ...
02.10.2017 18:59:17
...
Hallo Frank,
... würden aber bei richtigen Einsatz mE die Vorteile überwiegen.
Gruß Werner
.. , - ...
AW: im vorliegendem Beispiel ...
02.10.2017 19:02:54
Frank
Hi Werner, ja, sicher, wenn man z.B. keine benutzerdefinierten Ansichten braucht - klar...!!! Alles gut. Ich will mich ja auch nicht streiten (und irgendetwas konstruieren)...
AW: aber einige "intelligente" Tabellen ...
02.10.2017 19:15:52
...
Hallo nochmal,
... haben mich auch schon so manches mal beim Erstellen von Formeln geärgert. Besonders wenn die Feldnamen ellenlang sind verliert man viel leichter den Überblick als sonst und besonders dann, wenn man eine Formel ziehend kopieren will aber einen Fixbezug in der Tabelle hatte. Dann weich ich aus Bequemlichkeit auch aus auf die klassische "Notation"
Hab jedenfalls bisher noch nicht heraus gefunden wie man einen Feldnamen in Formeln fix setzen kann. Also analog wie ich A2:A99 klassisch einfach durch $A2:$A99 fixire, wenn ich die Formel mit den Bereich nach rechts kopiere.
Gruß Werner
.. , - ...
Anzeige
AW: aber einige "intelligente" Tabellen ...
02.10.2017 19:29:59
Luschi
Hallo Werner,
für fixe Bezüge in formatierten Tabellen kenne ich z.Z. auch nur diese Variante:
=INDIREKT("Tabelle[Spalte4]")
Aber das ist ja nun total keine Variante für Dich - ich sehe schon Deine zur Faust geballte Hand. Ansonsten erstelle ich dafür einen Namen, so wie ich es in meinem Beispiel zu diesem Problem gemacht habe.
Ansonsten benutze ich die Indirekt-Funktion auch bei Gültigkeitslisten, deren Quelle eine formatierte Tabelle ist, weil ich zu faul bin, noch einen zusätzlichen Namen zu definieren. Da hat M$ wohl irgendwas vergessen.
Gruß von Luschi
aus klein-Paris
Anzeige
AW: also ...
02.10.2017 20:23:05
...
Hallo Luschi,
... bevor ich die Faust balle verwende ich ich wohl schon eher INDIREKT() und das will wirklich schon was heißen, wie Du weißt ;-) Deine Datei schau ich mir demnächst an.
Gruß Werner
.. , - ...
Kann ich. Ich hatte ...
03.10.2017 10:24:57
lupo1
... es möglicherweise vorher gesehen, aber nie näher hingeguckt und nicht selbst verwendet.
AW: noch ein Aggregat problem
02.10.2017 18:10:12
Daniel
Hi
was wurde dir den über das UND in Matrixformeln gesagt (und hier handelt es sich um eine solche)
du solltest das was dir im Forum sagt, schon versuchen zu verstehen, vorallem wenn man es dir ausführlich erklärt.
nochmal zur erläuterung:
mit der Aggregat-Funktion triffst du eine Auswahl aus einer Zahlenreihe, indem du quasi einen Bruch bildest mit der Zahl im Zähler und die einzelnen Bedingungen, die erfüllt sein müssen, stehen hierbei im Nenner.
Dieser Nenner muss so aufgebaut sein, dass wenn er ausmultipliziert ist, die Zahl 1 oder 0 raus kommt (1: Zahl im Zähler wird übernommen, 0: Zahl im Zähler wird ignoriert)
Es wurde dir doch erklärt, dass bei der Verknüpfung von meherern Bedingungen hier UND und ODER nicht angewendet werden können, sondern diese einzelnen Bedingungen entweder miteineander multipliziert (UND) oder miteinander addiert werden müssen (ODER)
ums nochmal klar zu machen: x sind die Werte, a, b und c die bedingungen, die Erfüllt sein müssen.
sie stehen für einen Ausdruck, der WAHR oder FALSCH ergibt:
UND x/(a*b*c) bzw auch anders geschrieben x/a/b/c
ODER x/(a+b+c) wenn sich die Bedingungen gegenseitig ausschließen
ODER x/((a+b+c)&gt0) wenn die Bedingungen gleichzeitig erfüllt sein können.
um jetzt nochmal auf dein Problem einzugehen:
1. solltest du, wenn du ein Problem mit Formeln hast, nicht nur deine nicht funktionierenden Formeln zeigen, sondern immer auch erklären, was du berechnen willst und was du dir als Ergebnis vorstellst.
2. so wie das aussieht, gelten doch für alle Spalten die gleichen Bedingungen, weils es ja immer der Wert aus der gleichen Zeile ist. Nur die Spalte, aus der der Wert gelesen wird, ändert sich.
Gruß Daniel
Anzeige
AW: noch ein Aggregat problem
02.10.2017 18:33:12
Luschi
Hallo Thomas,
da Du ja eine von-bis Datumsgrenze vorgibst. habe ich auch die bis-Grenze eingebaut.
So wie es neopa schon erklärte, bleiben die Bedingungen für jede Spalate gleich, nur die Spalte in der Index-Funktion ändert sich. Weil das so ist, habe ich die Bedingung für die Aggregat-Funktion als Name definiert (mit dem sinnigen Namen 'Bedingung'!)
https://www.herber.de/bbs/user/116703.xlsx
Gruß von Luschi
aus klein-Paris
habt alle recht vielen dank
02.10.2017 19:38:57
Thomas
hallo Excelfreunde,
ich bin völlig sprachlos.
Habt vielen vielen dank für eure Unterstützung und vor allem für eure
sehr geduldigen Erklärungen. Ich habe es jetzt wirklich verstanden ( zumindest wie ich zum Ergebnis komme )und verspreche euch ich bekomme diese Form von Aggregat in Zukunft komplett allein hin.
Auch die verschiedenen Wege sind sehr interessant für mich. Ich brauch nur manchmal ein wenig länger als ihr.
Die Idee die Aggregat-Funktion als Name zu setzen ist super.
Das ist schon eine ganz schön mächtige Geschichte man muss sie nur richtig verstehen.
ihr habt mir alle richtug gut geholfen
vielen vielen dank
liebe grüße thomas
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige