Anzeige
Archiv - Navigation
1516to1520
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

eine zufällige Haltestelle

eine zufällige Haltestelle
26.09.2016 06:37:29
Christian
Hallo an euch alle,
bitte helft mir.
Habe in der einen Tabelle eine Liste von 19 Städten und in der anderen Tabelle eine Liste der dortigen Bushaltestellen (ich weiß Liste ist noch nicht vollständig aber bevor die Gesamtgröße überschritten wird, die hier erlaubt ist an KB...)
Suche eine Möglichkeit, in Städte! Spalte C für jede Stadt eine zufällige Haltestelle ausgeben zu lassen. Bitte helft mir dabei.
Gruß
Christian
https://www.herber.de/bbs/user/108399.xlsx

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: eine zufällige Haltestelle
26.09.2016 07:09:20
ransi
Hallo,
Versuch mal sowas:
Haltestellen

 ABCDEF
1Alanus Hochschule, AlfterAlfter0,261190267   
2Alfter-WitterschlickAlfter0,52733177   
3Duisdorf Bahnübergang, BonnAlfter0,547660096   
4Gielsdorf Am Wurmerich, AlfterAlfter0,627355151   
5Gielsdorf Prinzgasse, AlfterAlfter0,647439701   
6Gielsdorf Wasserturm, AlfterAlfter0,866411491   
7Gielsdorf, AlfterAlfter0,869909921   
8Hertersplatz, AlfterAlfter0,019963843   
9Hüffelweg, AlfterAlfter0,327490147   
10Im Benden, AlfterAlfter0,540495696   
11Impekoven Nettekoven, AlfterAlfter0,946775666   
12Impekoven Steingasse/Waldstraße, AlfterAlfter0,05799429   
13Impekoven Unterdorf, AlfterAlfter0,018565724Impekoven Unterdorf, Alfter  
14Jägerstr., AlfterAlfter0,471808168   
15Landgraben, AlfterAlfter0,597593299   
16Lohheckenweg, AlfterAlfter0,125467528   
17Nettekoven Am Wormshof, AlfterAlfter0,60063948   
18Oedekoven Buchenweg, AlfterAlfter0,164821667   
19Oedekoven Chateauneufstr., AlfterAlfter0,684285888   
20Oedekoven Ginggasse, AlfterAlfter0,676479306   
21Oedekoven Kapelle, AlfterAlfter0,728512704   
22Oedekoven Kramersbruch, AlfterAlfter0,124676062   
23Oedekoven Mühlenstr., AlfterAlfter0,635954357   
24Oedekoven Rathaus, AlfterAlfter0,802131734   
25Oedekoven Wegscheid, AlfterAlfter0,581065881   
26Roisdorfer Weg, AlfterAlfter0,872718386   
27Stadtbahn (Bus), AlfterAlfter0,755744291   
28Stühleshof, AlfterAlfter0,772155853   
29Witterschlick Am Bockshof, AlfterAlfter0,687149215   
30Witterschlick Bahnhof, AlfterAlfter0,077942533   
31Witterschlick Duisdorfer Str., AlfterAlfter0,209979555   
32Witterschlick Friedhof, AlfterAlfter0,070007654   
33Witterschlick Kirche, AlfterAlfter0,15647164   
34Witterschlick Kottenforst Bf Abzw., AlfterAlfter0,467542814   
35Witterschlick Sägewerk, AlfterAlfter0,307283028   
36Witterschlick Schmale Allee, AlfterAlfter0,709781214   
37Witterschlick Volmershoven Mehrzweckhalle, AlfterAlfter0,36406994   
38Witterschlick Volmershoven Ort, AlfterAlfter0,811271667   
39Witterschlick Wilde Str., AlfterAlfter0,310548945   
40Aegidienberg Aegidiusplatz, Bad HonnefBad Honnef0,580005228   
41Aegidienberg Einsiedel, Bad HonnefBad Honnef0,748130631   
42Aegidienberg Grundschule, Bad HonnefBad Honnef0,108337882   
43Aegidienberg Kohlstr., Bad HonnefBad Honnef0,839009626   
44Aegidienberg Logebachstr., Bad HonnefBad Honnef0,777063384   
45Aegidienberg Servatiushof, Bad HonnefBad Honnef0,017654271Aegidienberg Servatiushof, Bad Honnef  
46Aegidienberg Siefenhoven, Bad HonnefBad Honnef0,257314309   

Formeln der Tabelle
ZelleFormel
D1{=WENN(C1=MIN(WENN($B$1:$B$534=B1;C$1:C$534)); A1;"")}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
ransi
Anzeige
AW: eine zufällige Haltestelle
26.09.2016 07:18:12
Christian
Hallo Ransi,
danke erstmal.
Notfalls geht das auch, aber ich muss dann immer noch die Ergebnisse in Städte - Spalte C bekommen.
Und Ich hab jetzt bislang noch keine andere Lösung gefunden als sie einzeln zu kopieren, wegen den ganzen Leerzellen.
Gruß
Christian
zufällige Haltestelle pro Stadt auflisten
26.09.2016 09:15:40
WF
Hi,
zuerst die verschiedenen Städte:
in E1 steht
=B1
in E2 steht die Arrayformel:
{=WENNFEHLER(INDEX(B:B;VERGLEICH(1;(ZÄHLENWENN(E$1:E1;B$1:B$999)=0)*(B$1:B$999"");0));"")}
runterkopieren
ab F1 folgende Arrayformel für die zufälligen Haltestellen:
{=WENN(E1="";"";INDEX(A:A;VERGLEICH(MAX(WENN(B$1:B$999=E1;C$1:C$999));C$1:C$999;0)))}
runterkopieren
Salut WF
Anzeige
AW: zufällige Haltestelle pro Stadt auflisten
26.09.2016 09:32:18
Christian
Hallo WF,
dankeschön, hab allerdings deinen Vorschlag ein wenig abgeändert.
Habe mir die erste Formel gespart, dann die zweite Formel abgeändert in
{=WENN(A1="";"";INDEX(Haltestellen!A:A;VERGLEICH(MAX(WENN(Haltestellen!B$1:B$1198=A1; Haltestellen!C$1:C$1198));Haltestellen!C$1:C$1198;0))) }
damit die Liste in Tabelle Städte steht.
Danke
Werde mir dann mal noch den anderen Vorschlag anschauen.
AW: alternativ ohne Hilfsspalten und ohne {] ...
26.09.2016 10:11:51
...
Hallo Christian,
... auch bei beliebiger Anordnung der Daten in "Haltestellen"
so: =WENNFEHLER(INDEX(Haltestellen!A:A;AGGREGAT(15;6;ZEILE(A$1:A$1999)/(Haltestellen!B$1:B$1999=A1);ZUFALLSBEREICH(1;ZÄHLENWENN(Haltestellen!B:B;A1))));"")
Gruß Werner
.. , - ...
Anzeige
AW: alternativ ohne Hilfsspalten und ohne {] ...
26.09.2016 10:14:53
Christian
Hallo Werner,
super, das ist natürlich die optimale Formel für mich. Danke für deine Mühe.
Nur da ich hier überhaupt nicht mehr verstehe, ist was die Formel macht, vor allem der Aggregat Teil.
Wenn du mal ein wenig Zeit hast, bist du mal so nett und erklärst es mir?
Danke
Christian
AW: eine Erklärung dazu ...
26.09.2016 11:08:22
...
Hallo Christian,
... genutzt wird in der Formel die Matrixversion der Funktion AGGREGAT() wobei insbesondere das 2. Argument der Fkt.: die 6 eine entscheidende Rolle spielt. Dadurch werden bei der Auswertung der Matrix: ZEILE(A$1:A$1999)/(Haltestellen!B$1:B$1999=A1) alle Fehlerwerte die durch die Division durch 0 infolge des Bedingungsvergleiches (Haltestellen!B$1:B$1999=A1) auftreten einfach ignoriert. Dadurch bleiben nur an den entsprechenden Positionen die Zeilenwerte "übrig" (weil ja bei eingehaltener Bedingung: Haltestellen!B$1:B$1999=A1 sich ein WAHR und damit eine Division durch 1 ergibt.
Durch das erste Argument von AGGREGAT() der 15 wird nun aus der Zeilenwertematrix der x-kleinste Wert ermittelt und dies der INDEX()-Funktion übergeben. Welcher x-kleinste Wert zur Anwendung kommt wird mit dem Formelteil ZUFALLSBEREICH(1;ZÄHLENWENN(Haltestellen!B:B;A1)) ermittelt.
Die Wirkungsweise der Formel entspricht einer "echten" Matrixformel (mehr dazu siehe mal hier: http://www.online-excel.de/excel/singsel.php?f=26) die mit KLEINSTE(WENN( generiert wird aber zwingend den spez. Matrixformelabschluss benötigt. Die Wirkungsweise ist identisch mit der einer Formel wie z.B. dieser: =VERWEIS(2;1/(A1:A99"");A:A) womit der letzte Wert im Bereich von A1:A99 ermittelt wird. Nur das die VERWEIS()-Formel Fehlerwerte immer ignoriert und die AGGREGAT()-Formel nur wenn das zweite Argument der Funktion dies der Formel mit auf dem Weg gibt.
Diese spez. Formeln nenne ich deshalb auch Matrixfunktion(altät)sformeln, weil diese eben ohne den spez. Matrixformelabschluss auskommen und auf der jeweiligen Matrixversion von Funktionen wie VERWEIS() und oder AGGREGAT() und oder INDEX() und oder MMULT() ... aufbauen
Gruß Werner
.. , - ...
Anzeige
AW: eine Erklärung dazu ...
26.09.2016 11:14:34
Christian
Hallo Werner,
ja konnte es nachvollziehen, auch wenn du mit MMULT schon wieder eine Formel ins Spiel gebracht hast, von der ich noch nie gehört habe.
Danke
AW: eine zufällige Haltestelle
26.09.2016 09:19:46
Daniel
Hi
wenn die Liste der Haltestellen nach Stadt sortiert ist, mit folgender Formel in C2:
=INDEX(Haltestellen!A:A;VERGLEICH(A2;Haltestellen!B:B;0)-1+ZUFALLSBEREICH(1; ZÄHLENWENN(Haltestellen!B:B;A2))) Gruß Daniel
AW: eine zufällige Haltestelle noch ne Variante
26.09.2016 09:25:34
Daniel
oder so:
=INDEX(Haltestellen!A:A;ZUFALLSBEREICH(VERGLEICH(A2;Haltestellen!B:B;0);VERGLEICH(A2; Haltestellen!B:B;1))) 
Gruß Daniel
AW: eine zufällige Haltestelle noch ne Variante
26.09.2016 09:35:59
Christian
Hallo Daniel,
danke, aber nicht ganz so gut wie das was ich aus WF's Formel gemacht habe, das funktioniert auch, wenn die Tabelle Haltestellen anders sortiert ist.
Außerdem mir ist im Moment nicht ganz klar, was ich mit C1 machen soll, außerdem wird mir in C19 #NV bei dir ausgegeben.
Danke
Christian
Anzeige
AW: eine zufällige Haltestelle noch ne Variante
26.09.2016 09:50:15
Daniel
Hi
das NV bei C19 teilt dir mit, dass es diese Stadt in er Haltestellenliste nicht gibt (Tippfehler).
meine erste Formel funktioniert auch dann, wenn die Liste nicht sortiert, sondern nur gruppiert ist (dh alle Einträge zu einer Stadt stehen direkt untereinander, aber nicht zwingend in alphabetischer Reichenfolge)
ausserdem sparst du dir mit meinen Lösungen die Hilfsspalte mit den Zufallszahlen.
Gruß Daniel
AW: eine zufällige Haltestelle noch ne Variante
26.09.2016 09:52:42
Christian
Hallo Daniel,
sorry der Fehler bei C19 kam daher dass ich bei C1 gestartet hatte nicht bei C2.
ABer darum geht es mir, die Liste nach Haltestellen, also Spalte A aufsteigend sortieren zu können, das vernichtet die Gruppierung. Dann muss ich wohl die Zufallszahlen in Kauf nehmen.
Gruß
Christian
Anzeige
AW: eine zufällige Haltestelle noch ne Variante
26.09.2016 10:20:20
Daniel
HI
ja, wenn die List nur nach Haltestellen und nicht zusätzlich noch nach Ort sortiert haben willst, dann musst du die Zufallszahlen und die Array-Formeln in kauf nehmen.
Gruß Daniel

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige