Microsoft Excel

Herbers Excel/VBA-Archiv

noch ein Aggregat problem


Betrifft: noch ein Aggregat problem von: Thomas
Geschrieben am: 02.10.2017 17:43:25

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]<$I$6))); ZEILE()-10);1;1);"")

https://www.herber.de/bbs/user/116698.xlsx

habt schon mal recht vielen vielen dank für euer Interesse.

liebe grüsse thomas

  

Betrifft: AW: ... ist keins ... von: ... neopa C
Geschrieben am: 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
.. , - ...


  

Betrifft: AW: dazu nun noch ... von: ... neopa C
Geschrieben am: 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]<=$I$6);ZEILE(A1));1;1);"")


und diese nach rechts und unten kopieren.

Gruß Werner
.. , - ...


  

Betrifft: AW: noch ein Aggregat problem von: Frank Furter
Geschrieben am: 02.10.2017 18:06:10

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



  

Betrifft: AW: und hierzu ... von: ... neopa C
Geschrieben am: 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
.. , - ...


  

Betrifft: AW: und hierzu ... von: Frank Furter
Geschrieben am: 02.10.2017 18:42:39

Hallo Werner, hauptsächlich geht's (mir) hierbei um eine Hilfsspaltenlösung ohne AGGREGAT() und die hab ich bei lupo1 so zuerst gesehen...


  

Betrifft: AW: egal ob mit oder Hilfspalte ... von: ... neopa C
Geschrieben am: 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<=I$6);ZEILE(A1))

Ich würde allerdings das WENNFEHLER() um diese ranken und den INDEX()-Formeln in ein =WENN(H11="";"";INDEX(...) )einbinden.

Gruß Werner
.. , - ...


  

Betrifft: AW: egal ob mit oder Hilfspalte ... von: Frank Furter
Geschrieben am: 02.10.2017 19:17:21

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


  

Betrifft: AW: nun ... von: ... neopa C
Geschrieben am: 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
.. , - ...


  

Betrifft: "intelligente" Tabellen ... von: Frank Furter
Geschrieben am: 02.10.2017 18:48:02

Hallöchen, ... haben aber nicht nur Vorteile...


  

Betrifft: AW: im vorliegendem Beispiel ... von: ... neopa C
Geschrieben am: 02.10.2017 18:59:17

Hallo Frank,

... würden aber bei richtigen Einsatz mE die Vorteile überwiegen.

Gruß Werner
.. , - ...


  

Betrifft: AW: im vorliegendem Beispiel ... von: Frank Furter
Geschrieben am: 02.10.2017 19:02:54

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


  

Betrifft: AW: aber einige "intelligente" Tabellen ... von: ... neopa C
Geschrieben am: 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
.. , - ...


  

Betrifft: AW: aber einige "intelligente" Tabellen ... von: Luschi
Geschrieben am: 02.10.2017 19:29:59

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


  

Betrifft: AW: also ... von: ... neopa C
Geschrieben am: 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
.. , - ...


  

Betrifft: Kann ich. Ich hatte ... von: lupo1
Geschrieben am: 03.10.2017 10:24:57

... es möglicherweise vorher gesehen, aber nie näher hingeguckt und nicht selbst verwendet.


  

Betrifft: AW: noch ein Aggregat problem von: Daniel
Geschrieben am: 02.10.2017 18:10:12

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


  

Betrifft: AW: noch ein Aggregat problem von: Luschi
Geschrieben am: 02.10.2017 18:33:12

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


  

Betrifft: habt alle recht vielen dank von: Thomas
Geschrieben am: 02.10.2017 19:38:57

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


Beiträge aus den Excel-Beispielen zum Thema "noch ein Aggregat problem"