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

Formeln die bestimmte Zeilen ausgeben

Formeln die bestimmte Zeilen ausgeben
18.01.2018 15:13:19
Christian
Hallo alle zusammen,
ich bitte euch um Hilfe bei 3 Formeln.
Jeweils Bereich A1:A3359
Die erste Formel soll alle Texte auflisten, die mit einer deutschen Postleitzahl anfangen (alle mit 50xxx 51xxx oder 53xxx andere kommen nicht vor) danach steht jeweils noch der Ort in derselbe Zelle, daher ist das ganze als Text formatiert.
Die zweite Formel soll alle Texte auflisten die jeweils eine Zelle über den Texten stehen die die erste Formel auflistet.
Die dritte Formel soll alle Texte auflisten, die jeweils 2 Zellen über den Texten stehen die die erste Formel auflistet.
Ist das möglich?
Viele Grüße und danke
Christian

27
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Beispielmappe?
18.01.2018 15:18:32
Rudi
Hallo,
lade mal ein Beispiel hoch.
Gruß
Rudi
AW: Beispielmappe?
18.01.2018 15:29:09
Christian
gerne doch:
https://www.herber.de/bbs/user/119078.xlsx
allerdings es bleibt im weiteren Verlauf nicht bei dieser schönen Regelmäßigkeit der Tabelle, man kann also nicht sagen, geb ab A43 jede 8. Zelle aus, daher meine Idee es von den PLZ abhängig zu machen.
Da das ganze auf einer Datenverbindung zu clever-tanken.de beruht, lässt sich an der Datenstruktur, so wie sie über die Datenverbindung eingelesen wird nichts ändern, ich kann also nicht mal eben die ersten 36 Zeilen, die natürlich Werbung sind, löschen.
Gruß
Christian
Anzeige
AW: Formeln die bestimmte Zeilen ausgeben
18.01.2018 15:26:01
Rudi
die Zeilennummern bekommst du mit
{=KKLEINSTE(WENN(LINKS(A$1:A$100;1)="5";ZEILE($1:$100);"");ZEILE(A1))}
und runter kopieren.
Matrixformel!!! {} nicht eingeben. Formel mit shift+strg+enter abschließen.
Den Rest kannst du per INDEX() erledigen.
Gruß
Rudi
AW: Formeln die bestimmte Zeilen ausgeben
18.01.2018 15:30:30
Christian
Hallo Rudi,
wenn ich jetzt mich noch mit der Index Formel auskennen würde... aber schau mal hab ne Bsp Datei zur Verfügung gestellt.
Danke schonmal für deine Mühe
Christian
AW: so findet man auch "5 km" owT
18.01.2018 17:17:09
...
Gruß Werner
.. , - ...
AW: mit nur 1ner Formel und ohne {} möglich ...
18.01.2018 17:17:02
...
Hallo Christian,
... in D1:
=WENNFEHLER(INDEX($A:$A;AGGREGAT(15;6;ZEILE(A$1:A$999)/
(-LINKS(WECHSELN($A$1:$A$999;".";"!");5)5);ZEILE(A1))-SPALTE(A1)+1);"") 
Formel nach rechts und unten kopieren.
Formel sieht etwas komplizierter aus, aber so sollten die allermeisten möglichen ungewollten Datenwerte (nicht nur "5 km"), die keine Orte mit deutscher PLZ sind, als "Ergebniswerte" auch nicht gelistet werden.
Gruß Werner
.. , - ...
Anzeige
AW: mit nur 1ner Formel und ohne {} möglich ...
18.01.2018 17:56:04
Christian
Hallo Werner,
so leid es mir tut, aber bin in ner halben Stunde zu einer Geburtstagsfeier (meiner Mutter) eingeladen. Ich werde erst morgen zum ausgiebigen Testen kommen. Vielen Dank schonmal
Gruß
Christian
AW: dann wünsch Euch eine schöne Feier owT
18.01.2018 18:39:05
...
Gruß Werner
.. , - ...
AW: dann wünsch Euch eine schöne Feier owT
19.01.2018 09:59:30
Christian
Hallo Werner,
wenn ich dich richtig verstehe, reichen die Ergebnisse deiner Formel um die Unterschiede festzustellen oder?
Werde heute Nachmittag dir 2 Ergebnisse deiner Formel hochladen, die dann verglichen werden können. Aber jetzt ruft erstmal die Arbeit.
Gruß
Christian
AW: kann dann aber erst am WE antworten owT
19.01.2018 10:18:39
...
Gruß Werner
.. , - ...
AW: kann dann aber erst am WE antworten owT
19.01.2018 16:46:43
Christian
Hallo Werner,
mir fällt grad ein, da die Straßen ja eindeutig sind, kann ich ja einfach mit Zählenwenn suchen und wo ne 0 steht kommt es in dem anderen Datensatz nicht vor.
Danke für deine Hilfe.
Christian
Anzeige
AW: kann dann aber erst am WE antworten owT
19.01.2018 18:53:50
Christian
Hallo Werner,
durch meine Auswertung mit der ZÄHLENWENN Formel ist blöderweise doch noch ein Problem aufgetreten.
Mir ist dann doch aufgefallen, dass es die Kölner Str. 79 zweimal gibt, in 2 verschiedenen Städten.
Jetzt habe ich das Problem, dass meine Formel, die die Benzinpreise sucht (Tabelle 1 Spalte I) nicht zwischen diesen beiden Kölner Straßen unterscheiden kann.
Gibt es auch da eine Abhilfe?
In allen anderen Fällen ist die Straße eindeutig, es geht nur um die Kölner Str. 79
Gruß
Christian
https://www.herber.de/bbs/user/119108.xlsx
Anzeige
AW: dann wieder mit Hilfe von AGGREGAT() ...
22.01.2018 17:56:46
AGGREGAT()
Hallo Christian,
... sorry wenn ich Deine Nachfrage erst jetzt gelesen habe.
Folgende Formel in I1:
=WENN(G1="";"";WENNFEHLER(INDEX(Tabelle2!A:A;AGGREGAT(15;6;ZEILE(A$1:A$39)/(Tabelle2!A$7:A$39=G1);1));"?"))
und Formel nach unten kopieren.
Gruß Werner
.. , - ...
AW: dann wieder mit Hilfe von AGGREGAT() ...
23.01.2018 09:00:35
AGGREGAT()
Guten Morgen Werner,
kein Problem,
nur wo sind die ganzen Berechnungen hin, die ich in der bisherigen Formel hatte?
Oder wolltest du mir sagen, ich soll das Fragezeichen durch meine bisherige Formel ersetzen?
Gib mir doch bitte die komplette Formel, bevor das wieder zu ewigem Rumprobieren und Nachfragen führt.
Viele Grüße
Christian
Anzeige
und leider noch ein Fehler
23.01.2018 09:15:16
Christian
deine Formel gibt für die Kölner Str. 79 in BGL einen Preis von 1,329 aus, laut Tabelle 2 ist der Preis aber 1,319
Gruß
Christia
AW: ja, da fehlte noch eine Bedingungsprüfung ...
23.01.2018 10:22:48
...
Hallo Christian,
... in der eingestellten Formel. Dies hatte ich gestern in I3 bemerkt und die Formel entsprechend angepasst aber nicht aber oben in I1 kopiert bevor ich die Formel eingestellt habe. Die Division durch 1000 hatte ich mir gespart nun wieder hereingenommen.

=WENN(G1="";"";WENNFEHLER(INDEX(Tabelle2!A:A;AGGREGAT(15;6;ZEILE(A$1:A$999)/(Tabelle2!A$6:A$999=F1) /(Tabelle2!A$7:A$999=G1);1))/1000;"?"))

Falls wirklich ein Punkt in den Daten-Zahlenwerten vorhanden sein soll, so wird dieser bereits durch die Operation eliminiert. Das Fragezeichen in der Formel kannst Du auch wieder weglassen wenn Du willst.
Gruß Werner
.. , - ...
Anzeige
AW: ja, da fehlte noch eine Bedingungsprüfung ...
23.01.2018 10:47:53
Christian
Hallo Werner,
leider funktioniert es immer noch nicht.
https://www.herber.de/bbs/user/119185.xlsx
Siehe Zelle J2, habe meine Formel in Spalte I stehen, deine in Spalte J
ich habe jetzt einfach mal 0.989 als Wert eingetragen, der für diese Tankstelle in Tabelle2 gefunden wird. (also in Tabelle2!A11 eingetragen)
Gruß
Christian
AW: das war mir nicht aufgefallen, weil ...
23.01.2018 11:25:38
...
Hallo Christian,
... ich erstens nicht mit Preisen unter 1,00 gerechnet hatte und zweitens mir bisher nicht klar war, dass 0.### Werte durch eine mathematische Operation nicht in ein Zahlenwert gewandelt werden kann. Da hab ich dank Dir wieder dazugelernt.
Neue Formel:
=WENN(G1="";"";WENNFEHLER(WECHSELN(INDEX(Tabelle2!A:A;AGGREGAT(15;6;ZEILE(A$1:A$999)
/(Tabelle2!A$6:A$999=F1)/(Tabelle2!A$7:A$999=G1);1));".";"")/1000;"?"))

Gruß Werner
.. , - ...
Anzeige
AW: das war mir nicht aufgefallen, weil ...
23.01.2018 14:06:18
Christian
Hallo Werner,
soweit funktioniert die Formel, danke.
Habe nur noch eine Verständnisfrage.
In meiner alten Formel hatte das -5 ja gesagt, nimm den Betrag der 5 Zeilen über dem Straßennamen steht. Welcher Teil der Formel hat das jetzt übernommen?
Keine Ahnung wie es dazu kommt, aber manchmal also alle 2 Monate mal werden die Daten aus dem Internet so zur Verfügung gestellt, dass bei ner einzelnen Tankstelle der Preis nicht 5 sondern 3 Zeilen obendrüber steht da hab ich halt vorübergehend händig die 5 in die 3 geändert.
Gruß
Christian
AW: der Bereichversatz in der Formel ...
23.01.2018 14:40:53
...
Hallo Christian,
... realisiert das momentan, damit das Ergebnis 5 Zeilen oberhalb gesucht wird.
Wenn Du meine Formel z.B. wie folgt einsetzt:
=WENN(G2="";"";WENNFEHLER(WECHSELN(INDEX(A:A;AGGREGAT(15;6;(ZEILE(A$6:A$999)-K$1)
/(A$6:A$999=F2)/(A$7:A$999=G2);1));".";"")/1000;""))
kannst Du den Zeilenversatz mit Änderung des Wertes in K1 (im Beispiel 5) steuern, ohne die Formel zu ändern.
Gruß Werner
.. , - ...
Anzeige
AW: der Bereichversatz in der Formel ...
24.01.2018 17:43:54
Christian
Hallo Werner,
habe mich entschieden, nicht K1 zu benutzen sondern die Zahl direkt in die Formel zu schreiben, da es nie alle Tankstellen sondern nur vereinzelte betrifft.
Jedoch gibt die Formel
=WENN(G1="";"";WENNFEHLER(WECHSELN(INDEX(A:A;AGGREGAT(15;6;(ZEILE(A$6:A$3500)-5)/(A$6:A$3500=F2) /(A$7:A$3500=G1);1));".";"")/1000;""))
in allen 4 Zeilen der Beispielmappe sowie in meiner Originalmappe "" aus.
Was ich nicht verstehe ist, warum du prüfst ob G1 leer ist, G1 ist nie leer.
Gruß
Christian
AW: kann ich nicht nachvollziehen, weil ...
24.01.2018 18:48:37
...
Hallo Christian,
... in Deiner Beispieldatei die Formel die richtigen Ergebnisse ermittelt. Wenn das in Deiner Originaldatei nicht der Fall ist,kann das verschieden Ursachen haben. Kannst Du daraus einen Auszug hier wieder hochladen?
Möglicherweise ist Dein thread Morgen nicht mehr aktiv in der Forumsliste zu sehen. Du kannst notfalls einen neuen thread aufmachen und in Betreff schreiben: @neopa Fortsetzung ... un im Text folgenden Link: https://www.herber.de/forum/archiv/1600to1604/t1603107.htm einkopieren.
=WENN(G2="";""; ...) steht in meiner Formel nur für den Fall, falls die Formel zu weit nach unten kopiert wird.
Gruß Werner
.. , - ...
Anzeige
etwas ausführlicher, was ich meine
23.01.2018 09:09:37
Christian
Mein Excel ist so eingestellt, dass der Punkt die 1000er Trennung ist,
daher hat der erste Teil der Formel in Spalte I die Zahl die in Tabelle 2 gefunden wurde noch durch 1000 geteilt, damit als Benzinpreis nicht 1329, sondern 1,329 übernommen wird.
Der zweite Teil der Formel in Spalte I ist zwar da ich jetzt Super tanke sehr unwahrscheinlich dass er nochmal gebraucht wird, aber ich würde ihn gerne in der Formel lassen.
Als die Diesel Preise unter 1 € waren standen in Tabelle2 Preise wie 0.989 was für Excel ja Text ist, daher der zweite Teil der Formel, der in diesem Fall dn Punkt durch das Komma ersetzt und durch das mal 1 eine Zahl daraus macht.
Gruß
Christia
AW: mit nur 1ner Formel und ohne {} möglich ...
19.01.2018 07:11:58
Christian
Hallo Werner,
vielen Dank, die schöne Feier hatten wir.
Und die Formel funktioniert auch.
Leider ist mir noch ein anderes Problem aufgefallen.
Als ich die Datenverbindungen erstellt hatte, sind wohl 2 Tankstellen untergegangen, fehlen also in der Liste (kann passieren, wenn sich genau in dem Moment die Preise geändert haben).
Wenn ich in Spalte B noch einmal so eine Liste erstelle, siehst du eine Möglichkeit, herauszufinden, welche diese beiden Tankstellen sind? Die dann also in Spalte B stehen aber nicht in Spalte A.
Danke
Christian
AW: die Möglichkeit besteht ...
19.01.2018 08:50:29
...
Hallo Christian,
... wenn für beide Listen die Tankstellen ermittelt wurden, können diese beiden Ergebnislisten wieder mit einer Formel ausgewertet werden und so die Differenz beider gefunden werden. Wozu dies aber? Die aktuellste Liste sollte doch ausreichend sein. oder?
Gruß Werner
.. , - ...
AW: die Möglichkeit besteht ...
19.01.2018 09:31:45
Christian
Hallo Werner,
ich benutze seit Jahren eine Tabelle, die unter Berücksichtigung von Preisen, Spritverbrauch und Entfernung zu den Tankstellen, die wirklich günstigste Tankstelle berechnet, also auch mit einbezieht wieviel Liter ich für den Hin und Rückweg wieder verbrauche, von dem was ich tanke.
Ich habe jetzt lediglich ein neues Auto mit anderem Kraftstoff, daher habe ich die Liste ändern müssen.
Dafür ist diese Liste die Grundlage und dafür hätte ich natürlich auch gerne dass sie vollständig ist.
Gruß
Christian
AW: Dein Wunsch ist nachvollziehbar, aber ...
19.01.2018 09:42:35
...
Hallo Christian,
... ich kann Dir nur bei einer Excelauswertung helfen. Ich weiß nicht woher Du Deine Liste zur Auswertung bezogen hast bzw. beziehst. In der bisher von Dir aufgezeigten, stehen ja die zwei Dir fehlenden ja nicht drin, oder? Hast Du noch eine andere Liste?
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige