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

Herausfinden welche Zeile den 31. Text enthält

Herausfinden welche Zeile den 31. Text enthält
04.11.2016 11:58:20
Christian
Hallo an euch alle,
ich stehe vor einem Problem, wo ich nicht mehr weiterkomme. Ich befürchte leider auch, das wird recht kompliziert für euch, aber vlt. hat ja jemand eine Idee.
Ich habe bereits folgende Formel aber komme nicht mehr weiter:
=AGGREGAT(15;6;ZEILE(A$1:A$2000)/(VERGLEICH(H$1:H$2000&"";H$1:H$2000&"";)=ZEILE(H$1:H$2000));31)-1
Diese Formel gibt wie sie soll, bei der aktuellen Tabelle 47 aus. Jetzt möchte ich aber das wenn J47=J48 ist dass stattdessen 48 ausgegeben wird, wenn J47=J48=J49 soll 49 ausgegeben werden usw. Ist J47J48 soll weiterhin 47 ausgegeben werden.
Die Formel soll natürlcih auch genauso funktionieren, wen sie durch neue Datensätze und neu sortieren dann z.B. 55 ausgibt, dann halt auf J55 ff. bezogen.
Kann mir da jemand helfen?
Danke für euren Rat
Christian

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: AGGREGAT()-Formel anpassen, jedoch ...
04.11.2016 14:28:12
...
Hallo Christian,
... Deine Fragestellung ist mE nicht eindeutig genug. Was genau meinst Du mit 31. Text? Aus Deiner bisherigen Formel könnte man entnehmen, dass Du lediglich Unikate zählen willst. Was ist aber wenn Zahlenwerte im Bereich stehen? Auch könnte man Deiner Formel entnehmen, dass evtl. vorhandene Leerzellen als ein Text gezählt werden sollen. Oder?
Nachfolgende Formel ermittelt das von Dir gewünschte unter Ausschluss von evtl. vorhandenen Leerzellen und oder Zahlenwerten im Auswertungsbereich sowie unter der Voraussetzung, dass es mindestens ein weiteres Textunikat im ausgewerteten Zeilenbereich steht (ansonsten ergibt sich der Fehlerwert #Zahl!). Andernfalls müsste man weitere Angaben Deinerseits haben
=AGGREGAT(15;6;ZEILE(H$1:H$2000)/(VERGLEICH(H$1:H$2000;H$1:H$2000;)=ZEILE(H$1:H$2000))/ISTTEXT(H$1:H$2000);31+1)-1
Gruß Werner
.. , - ...
Anzeige
AW: AGGREGAT()-Formel anpassen, jedoch ...
04.11.2016 14:55:34
Christian
Hallo Werner,
ich versuche das Ganze mal ein wenig dann aufzubröseln.
Zuerst du hast recht, ich war mir mit dem Betreff ziemlich unsicher, habe nichts vernünftiges gefunden um das eigentliche Problem in ein paar kurze Worte zu fassen.
Die Tabelle hat u.a. Namen von Personen, Spalte H (wobei Personen mehrfach vorkommen können) und deren Geburtstage (Spalte J), wobei mehrere Personen am selben Tag Geburtstag haben können, also besteht Spalte H aus Text, Spalte J aus Zahlen. Leerzellen gibt es nicht.
Die Tabelle ist nach Spalte J sortiert.
Ich versuche es mal so zu erklären. Mein Ziel ist es einen bestimmten Bereich einzugrenzen, der in meinem Fall mit N1=47 (wo halt meine Formel steht) und N2=N1+30=77 eingegrenzt wird.
Mit dem Ziel dass meine andere Formel in der Tabelle
=WENN((ZEILE(A2)N$2); "";ZÄHLENWENN(B$1:INDEX(B:B;N$1); B2))
in den Zeilen 48 bis 77 etwas ausgibt, in den anderen nicht.
Mit anderen Worten, ich teile de Tabelle mit diesen Formeln in 3 Teile.
Der 2. Teil fängt also mit der 31. jüngsten Person an und erstreckt sich über 30 Zeilen.
Sollte allerdings die Person Nr. 30 und 31 am selben tag geboren sein, soll der zweite Teil erst mit der 32. jüngsten Person anfangen.
Hoffe jetzt war es etwas verständlicher.
Christian
Anzeige
so kann ichs vlt. besser erklären
04.11.2016 15:24:06
Christian
Hallo Werner,
ich denke so kann ichs ausdrücken
das ist gesucht:
=AGGREGAT(15;6;ZEILE(A$1:A$2000)/(VERGLEICH(H$1:H$2000&"";H$1:H$2000&"";)=ZEILE(H$1:H$2000));31)-1
+ZÄHLENWENN(J48:J2000;J47).
Das führt genau zu dem Ergebnis das ich will, mein einziges Problem ist jetzt, das im neuen Teil der Formel J47 und J48 halt variabel sein muss, wenn der ursprüngliche Teil der Formel z.B. 55 ausgibt,
muss die Formel
=AGGREGAT(15;6;ZEILE(A$1:A$2000)/(VERGLEICH(H$1:H$2000&"";H$1:H$2000&"";)=ZEILE(H$1:H$2000));31)-1
+ZÄHLENWENN(J56:J2000;J55) rechnen.
Kannst du es jetzt nachvollziehen?
Christian
Anzeige
AW: mE ist das nicht der Fall ...
04.11.2016 21:28:09
...
Hallo Christian,
... wenn keine Zahlen vorkommen kann mein Formelvorschlag verkürzt werden zu:
=AGGREGAT(15;6;ZEILE(H$1:H$2000)/(VERGLEICH(H$1:H$2000;H$1:H$2000;)=ZEILE(H$1:H$2000));31+1)-1
Allerdings, warum stellst Du keine Beispieldatei ein? An einer solchen lässt sich ein Anliegen immer einfacher erklären und vor allem auch einfacher nachstellen und falsche Interpretationen vermeiden.
Bei Rückfragen kann ich frühestens erst wieder Sonntag/Montag reagieren.
Gruß Werner
.. , - ...
Problem gelöst
06.11.2016 19:10:50
Christian
Hallo Werner,
habe das Problem gelöst, es gibt zwar sicherlich weniger umständliche und elegantere LÖsungen aber so geht es.
Die Formel in Spalte M
=WENN((ZEILE(A2)N$3); "";ZÄHLENWENN(B$1:INDEX(B:B;N$1); B2))
habe ich gelassen wie sie ist.
Die Formel
=AGGREGAT(15;6;ZEILE(A$1:A$2000)/(VERGLEICH(H$1:H$2000&"";H$1:H$2000&"";)=ZEILE(H$1:H$2000));31)-1
habe ich von N1 in N2 geschrieben damit die erste Formel nicht mehr darauf zugreift.
und dann in N1 meine Zählenwenn Formel, etwas abgeändert in
=N2+ZÄHLENWENN(INDIREKT(ADRESSE(N2+1;10)):J2000;INDIREKT(ADRESSE(N2;10)))
Das hat mein Problem gelöst,
falls dir jedoch noch eine einfachere Lösung einfällt, bin ich auch gerne dafür offen.
Danke auf jeden Fall für deine Mühe
Christian
Anzeige
sorry hab ausversehen das Kontrollkästchen akt.
06.11.2016 19:11:39
Christian
.
AW: das ist nun aber wieder anders ...
06.11.2016 19:17:40
...
Hallo Christian,
... als von Dir zuvor angegeben.
Aber sei es drum, Hauptsache Du hast jetzt Deine von Dir angestrebte Lösung.
Gruß Werner
.. , - ...
AW: das ist nun aber wieder anders ...
06.11.2016 19:27:57
Christian
Hallo Werner,
ja ich weiß, die erste Bitte die ich hatte, hatte auf dem Vergleich der Inhalte in Spalte J beruht.
Dann ist mir aber eingefallen, dass ich das ganze auch über Zählenwenn lösen kann, da die Tabelle nach J sortiert ist und daher später die Inhalte kein weiteres mal mehr auftauchen können.
Daher kam dann der große Unterschied, sorry aber auf die Idee mit Zählenwenn bin ich einfach zu spät gekommen.
Gruß
Christian
Anzeige
AW: Herausfinden welche Zeile den 31. Text enthält
05.11.2016 08:41:48
Christian
Hallo Werner,
ja du hast recht, tut mir leid, hier ist die Datei.
https://www.herber.de/bbs/user/109219.xlsx
Es geht darum, in welchem Bereich die Formel in Spalte M Ergebnisse liefert.
Die Formel fängt mit dem 31. unterschiedlichen Namen in Spalte H an und erstreckt sich über 30 Zeilen.
Das Ziel ist jetzt, wenn die 31. Person in der Liste am selben Tag Geburtstag hat wie die 30. soll die Formel in Spalte M erst bei der 32. Person anfangen, hat auch die 32. Person in der Liste am selben Tag Geburtstag wie die 30. und die 31. soll die Formel erst bei der 33. anfangen usw.
Haben Person 30 und 31 unterschiedliche Geburtstage soll dasselbe berechnet werden, wie bislang.
Ist es so verständlich?
Schönes Wochenende
Christian
Anzeige
AW: Aufgabenstellung ist nun aber eine andere, ...
06.11.2016 19:12:41
...
Hallo Christian,
... als ich diese aus Deinen bisherigen Angaben interpretiert hatte.
Sie ist nun eindeutig, auch wenn sich mir der Sinn dieser spez. Einschränkung nicht wirklich erschließt, zumal diese spez. Bedingung in Deiner Beispieldatei von Dir nicht aufgezeigt wird.
So wie Du jetzt schreibst, wird im Fall der Fälle die gesuchte erste Zellenr mE nur aufwendig zu ermitteln sein und zwar z.B. mit folgender Formel:

=AGGREGAT(15;6;ZEILE(INDEX(J:J;AGGREGAT(15;6;ZEILE(A$1:A$2000)/
(VERGLEICH(H$1:H$2000;H$1:H$2000;)=ZEILE(H$1:H$2000));31)-1):J2000)/
(INDEX(J:J;AGGREGAT(15;6;ZEILE(A$1:A$2000)/(VERGLEICH(H$1:H$2000;H$1:H$2000;)=
ZEILE(H$1:H$2000));31)-1):INDEX(J:J;AGGREGAT(15;6;ZEILE(A$1:A$2000)/
(VERGLEICH(H$1:H$2000;H$1:H$2000;)=ZEILE(H$1:H$2000));31)-1):J2000
INDEX(J:J;AGGREGAT(15;6;ZEILE(A$1:A$2000)/(VERGLEICH(H$1:H$2000;H$1:H$2000;)=
ZEILE(H$1:H$2000));31)-1));1)-1
Teste mal.
Gruß Werner
.. , - ...
Anzeige
AW: Aufgabenstellung ist nun aber eine andere, ...
06.11.2016 19:23:39
Christian
Hallo Werner,
danke erstmal nochmal für deine Hilfe,
schau dir mal meine Lösung an, wie ich es geschafft hatte in N1 mit Hilfe der Formel in N2 meine Lösung zu ermitteln.
https://www.herber.de/bbs/user/109232.xlsx
Dein Vorschlag funktioniert auch.
Sorry werde das nächste mal versuchen eindeutiger meine Probleme zu beschreiben und auch sofort eine Beispieldatei zur Verfügung stellen.
Danke trotzdem für deine Mühe und ein schönes Wochenende
Christian
AW: danke, wünsche Dir das ebenso owT
06.11.2016 20:02:55
...
Gruß Werner
.. , - ...
AW: danke, wünsche Dir das ebenso owT
06.11.2016 20:33:58
Christian
danke

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige