Formeln zusammenfassen -> Hilfsspalten sparen

Bild

Betrifft: Formeln zusammenfassen -> Hilfsspalten sparen
von: Jenny
Geschrieben am: 19.07.2015 20:57:17

Hallo an euch alle,
ich weiß ich hab hier eine Bitte für euch Formeltüftler, die mit sicherheit recht kompliziert wird.
Hier erstmal ein Beispiel, lasst euch von den Zeilen 1 bis 30 nicht stören, die hab ich nur eingefügt, damit M31 ein Ergebnis hat und nicht leer bleibt.
https://www.herber.de/bbs/user/98927.xlsx
Frage Nummer eins: Lässt sich E31 ohne D31 berechnen?
das war noch einfach denke ich, vermute mal man muss nur die Wenn formel aus E31 um die Formel in D31 rumbauen (glaub ich zumindest).
Jetzt kommt das wohl eigentliche zum Tüfteln, lässt sich M31 und N31 ohne K31 und L31 berechnen?
Jetzt stellt ihr euch sicher noch die Frage wozu das ganze, damit alle Spalten auf meinen Monitor passen, ausblenden ist blöd, da ich diese Formeln jedes mal kopieren muss wenn Daten hinzukommen, also ich müsste sie jedes mal wieder ein und ausblenden.
Im Vorfeld die Formeln kopieren geht auch nicht, da in der Originalmappe noch ein Makro liegt, dass u.a. die lezte Zeile sucht und dann die falsche Zeile nehmen würde, da ich nicht die letzte Zeile mit Formeln sondern die letzte mit Daten bräuchte.
Danke und Gruß
Jenny

Bild

Betrifft: falsche Formel
von: Jenny
Geschrieben am: 19.07.2015 20:58:36
sorry da ist was beim erstellen der Bsp.-Datei schiefgelaufen
die Formel in K31 heißt


=WENN(ZÄHLENWENN(F$1:F31;F31)>1;"";H31)
, nicht

=WENN(ZÄHLENWENN(F$31:F31;F31)>1;"";H31)


Bild

Betrifft: korrekte Datei
von: Jenny
Geschrieben am: 19.07.2015 21:01:26
genauso wie N31


=RANG(H31;K$1:K31;0)
heißen muss statt
=RANG(H31;K$31:K31;0)
https://www.herber.de/bbs/user/98929.xlsx

Bild

Betrifft: AW: korrekte Datei
von: Jenny
Geschrieben am: 19.07.2015 21:21:56


=WENN(P1="";C1;WENN(UND(ISTZAHL(P1);P1>2050);P1;WENN(LÄNGE(P1)-LÄNGE(WECHSELN(P1;" ";""))=1; DATUM(JAHR(WECHSELN(P1;LINKS(P1;LÄNGE(P1)-5);"1."&SUCHEN(LINKS(P1;3); "xxjanfebmaraprmayjunjulaugsepoctnovdec")/3&"."));MONAT(WECHSELN(P1;LINKS(P1;LÄNGE(P1)-5); "1."&SUCHEN(LINKS(P1;3);"xxjanfebmaraprmayjunjulaugsepoctnovdec")/3&"."))+1;0);WENN(LÄNGE(P1) -LÄNGE(WECHSELN(P1;" ";""))=0;WENN(UND(ISTZAHL(P1);LÄNGE(P1)=4);DATUM(P1;12;31);"");WENN(LÄNGE(P1) -LÄNGE(WECHSELN(P1;" ";""))=2;WENN(ISTZAHL(P1);P1;DATUM(RECHTS(P1;4)+0;SUCHEN(TEIL(P1;SUCHEN(" ";P1) +1;3);"xxjanfebmaraprmayjunjulaugsepoctnovdec")/3;LINKS(P1;SUCHEN(" ";P1)-1)));""))))) 
mit anderen Worten das erste habe ich selbst geschafft

Bild

Betrifft: AW: korrekte Datei
von: AlexG
Geschrieben am: 20.07.2015 16:51:56
Hallo Jenny,
Es ist geschickter eine Datei mit ausreichend Daten zur Verfügung zu stellen und genau zu beschreiben was du vor hast. Ich blick da grad nicht durch, was genau das Ziel der Berechnungen in M31 und N31 ist.
Gruß
Alex

Bild

Betrifft: AW: korrekte Datei
von: Jenny
Geschrieben am: 20.07.2015 17:20:48
Hallo Alex,
die neue Datei kommt noch, versprochen
ich denke was die Formeln im einzelnen machen, ist ja kein Hexenwerk,
M gibt die 30 jüngste Person aus H aus, jedoch mit 2 Einschränkungen, für die ich die Formeln in K und L habe, damit nicht alle Daten aus H mit einberechnet werden, sondern nur die die ich brauche.
N gibt den Rang von H in K aus, da Personen doppelt in der Tabelle vorkommen und nur einmal gezählt werden sollen, ist die Spalte K dafür das dass Geburtsdatum ein und derselben Person nur einmal zählt.
Gruß
Jenny

Bild

Betrifft: Noch offen
von: AlexG
Geschrieben am: 20.07.2015 18:36:40
Hallo Jenny,
Mir fehlt grad ein wenig die Zeit, deshalb stelle ich wieder auf offen. Ich werde erst am Freitag wieder Zeit finden.
Gruß
Alex

Bild

Betrifft: AW: korrekte Datei
von: Jenny
Geschrieben am: 19.07.2015 21:22:06


=WENN(P1="";C1;WENN(UND(ISTZAHL(P1);P1>2050);P1;WENN(LÄNGE(P1)-LÄNGE(WECHSELN(P1;" ";""))=1; DATUM(JAHR(WECHSELN(P1;LINKS(P1;LÄNGE(P1)-5);"1."&SUCHEN(LINKS(P1;3); "xxjanfebmaraprmayjunjulaugsepoctnovdec")/3&"."));MONAT(WECHSELN(P1;LINKS(P1;LÄNGE(P1)-5); "1."&SUCHEN(LINKS(P1;3);"xxjanfebmaraprmayjunjulaugsepoctnovdec")/3&"."))+1;0);WENN(LÄNGE(P1) -LÄNGE(WECHSELN(P1;" ";""))=0;WENN(UND(ISTZAHL(P1);LÄNGE(P1)=4);DATUM(P1;12;31);"");WENN(LÄNGE(P1) -LÄNGE(WECHSELN(P1;" ";""))=2;WENN(ISTZAHL(P1);P1;DATUM(RECHTS(P1;4)+0;SUCHEN(TEIL(P1;SUCHEN(" ";P1) +1;3);"xxjanfebmaraprmayjunjulaugsepoctnovdec")/3;LINKS(P1;SUCHEN(" ";P1)-1)));""))))) 
mit anderen Worten das erste habe ich selbst geschafft

Bild

Betrifft: wäre schon realisierbar, doch ...
von: der neopa C
Geschrieben am: 21.07.2015 10:54:30
Hallo Jenny,
... für Deine momentan eingestellte Daten ergibt es keinen wirklichen Sinn, denn danach sind nur 30 Datensätze vorhanden und davon sollen nach Deinen Vorgaben noch welche doppelt sein? Außerdem können sehr wohl zwei verschiedene Personen das identische Geburtsdatum haben.
Mit anderen Worten, stelle mal einen relevanten Datenauszug (mit anonymisierten Namensangaben wie z.B. Name_01 etc. ein) hier ein und präzisiere daran Deine Aufgabenstellung bzgl. "30."Jüngster etc. Dann sehen wir weiter.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: wäre schon realisierbar, doch ...
von: Jenny
Geschrieben am: 21.07.2015 11:34:43
Hallo Werner,
dann versuchs mal hiermit:
https://www.herber.de/bbs/user/98964.xlsx
zur Info, da ich es ja selbst geschafft habe 2 Formeln zusammenzuführen fehlt jetzt die Spalte D aus der ersten Beispieltabelle. Somit ist jetzt die alte Spalte E die neue Spalte D usw.
Gespart werden sollen jetzt die Formeln in J und K.
J1 ff. geht hin und überprüft ob E1 ff. schonmal in einer vorigen Zeile stand und wenn ja lässt die Formel die Zelle leer, damit wie gesagt keine Personen doppelt berechnet werden.
K1 ff. gibt den Geburtstag nur noch aus, wenn die Person zum Zeitpunkt D1 ff. zwischen 14 und 25 Jahre alt war und das erste mal in der Tabelle vorkommt (daher der Bezug auf J1 ff.)
L1 ff. berechnet unter denen die K1 ff. noch übrig gelassen hat die 30 jüngste Person zum Zeitpunkt D1 ff. und gibt erst etwas aus, wenn überhaupt 30 Einträge in Spalte K stehen.
M1 ff. berechnet die wievielt jüngste Person E1 ff. zum Zeipunkt D1 ff. war, bezieht sich jedoch auch hier auf J1 ff. damit auch hier keine Personen doppelt gezählt werden.
Gruß und danke fürs tüfteln
Jenny

Bild

Betrifft: nun ist es für mich nicht klarer geworden ...
von: der neopa C
Geschrieben am: 22.07.2015 08:19:16
Hallo Jenny,
... Die schreibst nicht nur von Daten, die in Spalte E verglichen werden, sondern wertest mit Formel in Spalte J die Spalte E aus obwohl diese komplett leer ist. Sorry, aber darin kann ich keinen Sinn feststellen und somit Dein Anliegen momentan noch nicht wirklich durchschauen.
Und was hat es eigentlich mit den Daten in O41:O95 auf sich?
Gruß Werner
.. , - ...

Bild

Betrifft: AW: nun ist es für mich nicht klarer geworden ...
von: Jenny
Geschrieben am: 22.07.2015 08:38:10
Hallo Werner,
die Formeln J und K überprüfen jeweils Bedingungen und geben aus was in G steht, wenn die Bedingungen erfüllt sind.
J gibt G aus, wenn die Person das erste mal in der Tabelle auftritt
K gibt G aus, wenn die Person das erste mal in der Tabelle auftritt (daher der Bezug der Formel auf Spalte J) und zu dem Zeitpunkt D zwischen 14 und 25 Jahre alt war (daher die DATEDIF FORMELN in H und I).
L soll den Geburtstag der 30 jüngsten Person zum jeweiligen Zeitpunkt ausgeben,
also normalerweise die Formel


=WENN(ANZAHL(G$1:G1)>30;KGRÖSSTE(G$1:G1;30);"") 
es sind jedoch nur die Personen relevant die im eben genannten Alter waren, daher der Bezug auf K in

=WENN(ANZAHL(K$1:K1)>30;KGRÖSSTE(K$1:K1;30);"")
statt auf G, da K genau diese Daten übrig lässt.
M soll mir sagen die wievielt jüngste Person diese Person zum Zeitpunkt G war, ohne _ Alterseinschränkung unter normalen Umständen

=RANG(G1;G$1:G1;0) 
, da allerdings Personen mehrfach in der Liste vorkommen bezieht sich die Formel auf J

=RANG(G1;J$1:J1;0)
da J überprüft ob die Person schonmal in der Tabelle stand.
Zu deinem Einwand mit Spalte E, der ist berechtigt, hab beim Erstellen der Beispieltabelle das was in Spalte F steht in die falsche Spalte kopiert, das gehört eigentlich in E. Sorry.
Zu O, die kannst du eigentlich beiseite lassen, die Daten in D haben ihren Ursprung aus dem Internet und sind dort in genau diesem Format wie in O geschrieben, die Formel in D, die O umrechnet existiert für den einzigen Sinn, Tippfehler beim Eintragen der Daten in die Tabelle zu vermeiden.
Gruß
Jenny

Bild

Betrifft: Deine Auswertung kann ich nicht nachvollziehen ...
von: der neopa C
Geschrieben am: 22.07.2015 15:22:30
Hallo Jenny,
... und zwar betrachte ich zunächst mal nur Spalte M.
Du weist in Zeile 24 mit Formel einen "Rang" zu, obwohl Du richtiger weise das Datum in J24 durch "" ersetzt hast. Warum? Es müsste somit hier auch kein rang ausgegeben werden.
Um Deinen Rang zu ermitteln, reicht es also (ohne Formeln in Spalte J) in M1=1 zu schreiben und ab M2:

=WENN(ZÄHLENWENN(E$1:E2;E2)>1;"";SUMMENPRODUKT(N(G$1:G2>=G2)))

Formel ziehend nach unten kopieren.
Ist es das was Du hierfür gesucht hast?
Gruß Werner
.. , - ...


Bild

Betrifft: oh, natürlich noch abzgl. der Dupletten ...
von: der neopa C
Geschrieben am: 22.07.2015 15:36:38
Hallo,
... in M2 dann also so:


=WENN(ZÄHLENWENN(E$1:E2;E2)>1;"";SUMMENPRODUKT(N(G$1:G2*(ZÄHLENWENN(E$1:E2;E$1:E2)<2)>=G2)))
Gruß Werner
.. , - ...

Bild

Betrifft: Nachtrag
von: Jenny
Geschrieben am: 21.07.2015 11:39:19
dass 2 unterschiedliche Personen denselben Geburtstag haben können spielt für die Formeln keine Rolle, da Spalte J ja in diesem Fall für beide Personen das Datum ausgibt und alle anderen Formeln die sich auf Spalte J beziehen damit auch beide Personen berücksichtigen.

Bild

Betrifft: mit einfachen Worten...
von: Jenny
Geschrieben am: 21.07.2015 11:48:47
die Formeln in L und M sollen sich nicht auf alle Daten in Spalte G beziehen, sondern nur auf einen gewissen Teil, der durch die Formeln in J und K eingegrenzt wird.

Bild

Betrifft: dazu Morgen ...
von: der neopa C
Geschrieben am: 21.07.2015 18:01:32
Hallo Jenny,
... sorry, wenn Dein thread bei mir ins Hintertreffen geraden ist. Ich schau dann Morgen mir Deine Antworten an.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: dazu Morgen ...
von: Jenny
Geschrieben am: 21.07.2015 18:03:27
falls du Ahnung von Makros hast, schau bitte erstmal nach dem Thread,
https://www.herber.de/forum/messages/1437292.html
das Problem müsste dringender gelöst werden, es geht um dieselbe Tabelle.
Gruß
Jenny

Bild

Betrifft: und hierzu ...
von: der neopa C
Geschrieben am: 22.07.2015 08:21:02
Hallo Jenny,
... aus VBA-Lösungen halte ich mich mangels Kenntnissen grundsätzlich außen vor.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: und hierzu ...
von: Jenny
Geschrieben am: 22.07.2015 08:42:43
hätte ja sein können, kein Problem
zu dem anderen Beitrag von dir hab ich grad was geschrieben.

Bild

Betrifft: mit geschachteltem AGGREGAT() und kombiniert ...
von: der neopa C
Geschrieben am: 22.07.2015 17:03:51
Hallo Jenny,
... für Deine Ermittlung des 30ig Jüngsten ohne die Hilfsspalten K und J mit ZÄHLENWENN() und WENNFEHLER() über Deine Bedingungen dann z.B. in Spalte N so:

 N
1 

Formeln der Tabelle
ZelleFormel
N1=WENNFEHLER(AGGREGAT(14;6;AGGREGAT(14;6;G$1:G1/((H$1:H1=25)*(I$1:I1=0)+(H$1:H1>=14)*(H$1:H1<25))/(ZÄHLENWENN($E$1:E1;E$1:E1)<2); ZEILE(A$1:A1)); 30); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
und Formel ziehend nach unten kopieren.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: mit geschachteltem AGGREGAT() und kombiniert ...
von: Jenny
Geschrieben am: 23.07.2015 23:56:29
Hallo Werner,
entschuldige bitte dass ich jetzt erst antworte habe die ganze Zeit auf eine Nachricht per Mail gewartet und irgendwie hat googlemail alles seit meiner letzten Antwort als Spam identifiziert.
Zuerst zu deiner Frage zu Spalte M.
Die Formel soll, wenn es keine doppelten Einträge der Personen gäbe, berechnen die wievielt jüngste Person die jeweilige Person zum Zeitpunkt D war.
Daher ist es so gewollt, dass wenn eine Person mehrmals in der Tabelle vorkommt auch genauso oft ein Rang berechnet wird.
Nur Personen vierteilen sich nicht, die Rang-Formel geht normalerweise hin und zählt jeden Wert egal ob sie nur einmal oder mehrfach vorkommen, das heißt ohne die Hilfsspalte J würde eine Person die 4mal in der Tabelle vorkommt auch 4mal gezählt werden.
J sorgt dafür dass wenn eine Person zum wiederholten male vorkommt dass dann das Feld leergelassen wird, sodass das Geburtsdatum von ein und derselben Person nur einmal berechnet wird.
Aber auch in dem Fall M24 hat die Frage die wievielt jüngste Person nm0001256 am 23.11.1934 war ja trotzdem eine Antwort, auch wenn J24 leer ist.
Also um deine Frage zu beantworten, eine Formel die M24 leer lässt ist nicht das was ich gesucht habe, die Ausgaben in den Spalten L und M müssen so bleiben wie sie vorher waren, sonst macht es keinen Sinn.
Die zweite Formel hab ich jetzt mal getestet, sie lässt auch M leer, wenn J leer ist und berechnet z.B. M25 falsch.
Die 3. Formel für Spalte L da stimmt leider auch was nicht, die Formel gibt schon in L63 eine Zahl aus obwohl in K1:K63 nur 29 Werte stehen, wie kann man den 30. größten Wert berechnen, wenn nur 29 Werte da sind? Oder hast du eventuell übersehen, dass ich da ich mich auf Spalte K beziehe indirekt auch Spalte J benutze um doppelte Personen beim berechnen außen vor zu lassen weil wenn die Zeilen 20 und 24 beide berechnet würden wäre klar, dass dann auch 30 Werte zum rechnen da sind.
LG und danke schonmal Jenny

Bild

Betrifft: sehe ich momentan noch etwas anders ...
von: der neopa C
Geschrieben am: 24.07.2015 08:11:20
Hallo Jenny,
... allerdings muss ich voranstellen, dass ich das Heute auch nicht mehr abschließend klären kann, weil ich jetzt ein paar Tage offline sein werde.
Nun zu zunächst zu Deinen Formelauswertungen. Deine Formeln in J weisen ein "" dort aus, wo der Name in Spalte E zum zweiten Mal vorkommt. Z.B. in Zeile 24 weil in E20 schon mal vorkommt. In G24 und G20 stehen aber auch die gleichen Geburtstage. Für mich sind es somit die gleichen Personen in Zeile 20 und Zeile 24 und somit darf G24 mE nicht berücksichtigt werden. Deine Formel in M24 würde auch sofort ein #NV auswerfen, wenn in G20 ein anderes Datum als in G24 stünde. Und die Wahrscheinlichkeit ist ja auch gegeben.
Insofern hatte ich die Auswertung in meiner ersten und zweiten (korrigierte Fassung der ersten) Formel. Hier besteht also noch Klärungsbedarf.
Meine dritte Formel beginnt schon mit dem 30ig Jüngsten. Deine mE mit dem 31. (in Deiner Formel steht als Vergleichszeichen ein Größer und nicht ein Größer gleich)
Die Ergebnisunterschiede in den unteren Ergebnissen erklären sich mir momentan aus meiner anderen Sichtweise, die ich zur Formel in Spalte M dargelegt habe. Aber das könnte ich noch mal prüfen.
Wenn Du kannst und magst, kannst Du ab dem 03.08 einen neuen thread aufmachen und auf diesen hier https://www.herber.de/forum/archiv/1436to1440/t1437277.htm verweisen. Dann schau ich es mir noch mal näher an. Allerdings eine Klärung zu der oben von mir aufgezeigter Problematik wäre schon noch notwendig.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: sehe ich momentan noch etwas anders ...
von: Jenny
Geschrieben am: 24.07.2015 09:41:28
zu deinem Einwand, gleicher Name unterschiedliches Datum, hast du recht, da macht meine Formel in Spalte M schlapp.
Selbst wenn es passieren sollte, dass 2 unterschiedliche Personen mit demselben realen Namen jedoch dann 2 unterschiedlichen Geburtstagen in der Tabelle auftauchen.
Die realen Namen stehen (in der Bsp.-Tabelle unberücksichtigt) in Spalte F. Die Kürzel in Spalte E sind eindeutig, da hat wirklich jede Person unabhängig von Namen und Geburtstag sein eigenes Kürzel, daher beziehen sich die Formeln alle direkt oder indirekt auf Spalte E nicht Spalte F, damit auch diese Problematik im Keim erstickt wird.
Gruß
Jenny

Bild

Betrifft: AW: sehe ich momentan noch etwas anders ...
von: Jenny
Geschrieben am: 24.07.2015 09:43:53
ich hätte vielleicht dazuschreiben sollen am Anfang das alle Formeln so funktionieren wie sie sollen, dass ihr euch keine Gedanken über Sinn, Unsinn und Zweck der Formeln machen müsst, auch nicht großartig die Syntax verändern, sondern schlicht und einfach versuchen sollt sie zusammenzufassen.

Bild

Betrifft: AW: Formeln zusammenfassen -> Hilfsspalten sparen
von: Jenny
Geschrieben am: 24.07.2015 08:12:41
Hallo Werner,
ich befürchte du wirst mich hassen dafür dass mir die Idee nicht schon eher kam.
Ich wollte die Spalten ja einsparen, damit bei einer Präsentation alle Spalten auf eine Anzeige passen, ohne zu zoomen und ohne zu scrollen. Ausblenden ging nicht da ich sie für jede zusätzlliche Zeile wieder einblenden müsste um die Formel zu kopieren. Im Vorfeld soweit kopieren wie ich irgendwann mal brauche ging nicht da es ein Makro gibt dass sich auf die letzte genutzte Zeile in Tabelle1 bezieht und sich dann auf die falsche Zeile beziehen würde.
Aber was ich nicht bedacht hatte, diese beiden Berechnungen die in J und K stattfinden einfach zukünftig in Tabelle5 stattfinden zu lassen (ich weiß Tabelle5 gibt es nur in der Oroginaltabelle) da es dort kein Makro gibt, kann ich da sehr wohl vorsorglich die Formel über 100.000 Zeilen kopieren und in Tabelle1 hab ich sie mir gespart.
Ich meld mich nochmal wenn ich Hilfe bei den Zellbezügen brauche.
LG und trotzdem danke
Jenny

Bild

Betrifft: ntue ich nicht; aber schade ist ...
von: der neopa C
Geschrieben am: 24.07.2015 08:23:52
Hallo Jenny,
... das Du nicht von Anfang an etwas mehr zu Deine Rahmenbedingungen genannt hast. Da hätten wir uns beide wohl viel Zeit sparen können.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: tue ich nicht; aber schade ist ...
von: Jenny
Geschrieben am: 24.07.2015 09:33:09
Hallo Werner,
das war meine Begründung vom Anfang des Threads
Jetzt stellt ihr euch sicher noch die Frage wozu das ganze, damit alle Spalten auf meinen Monitor passen, ausblenden ist blöd, da ich diese Formeln jedes mal kopieren muss wenn Daten hinzukommen, also ich müsste sie jedes mal wieder ein und ausblenden.
Im Vorfeld die Formeln kopieren geht auch nicht, da in der Originalmappe noch ein Makro liegt, dass u.a. die lezte Zeile sucht und dann die falsche Zeile nehmen würde, da ich nicht die letzte Zeile mit Formeln sondern die letzte mit Daten bräuchte.
Ich dachte als Laiin das reicht aus um mein Anliegen verständlich zu machen, tut mr leid dass ich mich offensichtlich geirrt hatte. Du hast schon recht war sehr viel Arbeit für uns beide.
Danke trotzdem
Jenny

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Formeln zusammenfassen -> Hilfsspalten sparen"