PLZ: 5 andere mit kleinster Entfernung

[Fennek]

Hallo,

ich habe mich wohl zuviel mit VBA und zuwenig mit Excel beschäftigt:

Vorhanden: Matrix aller Postleitzahlen mit den jeweiligen Entfernungen

Gesucht: zu einer gegebenen PLZ die 5 nächsten

=KKLEINSTE($8:$8;ZEILE()-23)
=VERGLEICH(B25;$8:$8;0)
=INDEX($1:$1;;C25)

liefert zwar die gesuchten PLZ, aber ich konnte die Übergabe der Zeilen-Nummer, hier 8, nicht dynamisch formulieren. Auch Tests mit Aggregate zeigten nur Fehler.

Mit VBA könnte ich das wohl hinbiegen, aber eine Formel wäre besser.

Datenstruktur:

PLZ in Spalte A und Zeile 1

Gibt es einen Tipp?

Danke und mfg

[steve1da]

Hola,
für die Entfernungen:

Code:

=KKLEINSTE(WENN(($A$2:$A$16=$B$21)*($B$2:$BT$16>0);$B$2:$BT$16);ZEILE(A1))

Diese Formel mit Strg-Shift-Enter abschließen und dann nach unten ziehen.
Für die PLZ:

Code:

=INDEX($B$1:$BT$1;AGGREGAT(15;6;SPALTE($B$1:$BT$1)-1/(($A$2:$A$16=$B$21)*($B$2:$BT$16=B25));1))

Gruß,
steve1da

[Fennek]

Hallo steve1da,

vielen Dank, das hätte ich so nicht geschafft.

Keine Frage, sondern ein Feedback:

Die Übertragung der Formel für 2 Sheets, Tabelle "Entfernung" und Tabelle "Auswerung" war nich so einfach

Code:

=KKLEINSTE(WENN((Entfernungsliste!$A$2:$A$16=$A$1)*(Entfernungsliste!$B$2:$LFV$16>0);Entfernungsliste!$B$2:$LFV$16);ZEILE(A1))

(als Matrix-Formel) ging nicht, In "Entfernungsliste" in freien Spalten rechts der Liste ging es sehr gut. Da die Matrix aber 8.900 x 8.900 gross ist, bekommt mein Excel Speicherprobleme.

mfg

[snb]

oder:

PHP-Code:

=INDEX($B$1:$BT$1;1;MATCH(SMALL(INDEX($B$2:$BT$16;MATCH($B$21;$B$1:$BT$1;1);0);ROW($A1));INDEX($B$2:$BT$16;MATCH($B$21;$B$1:$BT$1;1);0);0)) 


[Ralf A]

...oder...

als Tabelle formatieren, in gewünschter PLZ Spalte Zahlenfilter, Top 10, die untersten 6 wählen...

oder mit PQ:
Bsp.: Diese formatierte Tabelle als Quelle nehmen und für PLZ 10115 die nächsten 5 suchen:

Code:

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Gefilterte Zeilen" = Table.SelectRows(Quelle, each ([10115] <> 0)),
    #"Sortierte Zeilen" = Table.Sort(#"Gefilterte Zeilen",{{"10115", Order.Ascending}}),
    Unterste5 = Table.FirstN(#"Sortierte Zeilen",5),
    #"Andere entfernte Spalten" = Table.SelectColumns(Unterste5,{"10115", "PLZ"})
in
    #"Andere entfernte Spalten"

Statt statisch 10115 kann selbstverständlich auch ein Parameter verwendet werden...

[Fennek]

Mit der 8.900 x 8.900 Matrix aller Entfernungen zwischen 2 PLZ (ca 1GB als xlsm) dauert der VBA-Code für eine beliebige PLZ 0,02 Sekunden.

Code:

Sub T_2()
Dim WSF As WorksheetFunction: Set WSF = Application.WorksheetFunction

Dim rPLZ As Range, PLZ As String, Sp As Integer
Start = Timer

PLZ = "10245"

Set rPLZ = Columns(1).Find(PLZ, , xlValues, xlWhole)

Set rPLZ = rPLZ.Resize(, 8290)

For i = 2 To 10

    Sp = Application.Match(WSF.Small(rPLZ, i), rPLZ, 0)
    Debug.Print i, rPLZ.Row, Sp, Cells(1, Sp)
Next i
Debug.Print Timer - Start
End Sub

[snb]

Code:

Sub M_snb()
  sn = Cells(1).CurrentRegion
 
  For j = 1 To UBound(sn)
    If sn(j, 1) = 10245 Then Exit For
  Next
 
  For jj = 2 To 10
      y = Application.Small(Application.Index(sn, 0, j), jj)
      For jjj = 1 To UBound(sn, 2)
        If sn(j, jjj) = y Then Exit For
      Next

      Debug.Print "row: " & j & vbTab & "value: " & y & vbTab & "column: " & jjj & vbTab & "columnhead: " & sn(1, jjj)
  Next
End Sub

[Fennek]

Hallo snb,

vielen Dank, der Code verspricht sehr schnell zu sein.

Aber in dieser Zeile

Code:

y = Application.Small(Application.Index(sn, 0, j), jj)

reicht mein RAM bei der 8.900 x 8.900 Matris nicht mehr.

mfg

[LCohen]

Mengenbetrachtung:

Variant = 15 (weiß es jemand richtig?)
8900^2 = 79.210.000

ergibt 1.188.150.000 Byte für den Wertebereich ohne Header.

Vielleicht kannst Du Deine Zahlen noch transformieren in LONG (da INTEGER wohl nichts bringt, da intern angeblich mit LONG gerechnet würde)?

Oder Du verwendest Strings. Dann landest Du mit 3 Byte bei 256^3 = 16.777.216 Zuständen, so dass Du sogar metergenau mit Entfernungen bis 16.777 km rechnen kannst. 3 Byte x 8900 = 26700, was unter der maximalen Stringlänge von 32766 ist.

Das Modell wiese dann statt 1.188.150.000 nur ein Fünftel davon aus: 237.630.000 Byte.

Bei 100 Meter Genauigkeit könnte man auch Entfernungen bis 6553 km auswerten: 2 Byte = 256^2 = 65536 => 158.420.000

Mit diesen 151 MB Modellgröße konnte sogar XL 2000 schon rechnen.

---

Ein anderer Ansatz wäre:

Du musst ja gar nicht 8900 x 8900 auf einmal betrachten. Minimal reicht der Vergleich von 1 PLZ mit den 8899 anderen. Das müsstest Du halt 8900 mal hintereinander machen (was jegliche andere Rechenaufgabe auch nicht anders tut).

[snb]

Dann vielleicht so ?

Code:

Sub M_snb()
  sn = Cells(1).CurrentRegion
  ReDim sq(UBound(sn))
 
  For j = 1 To UBound(sn)
    If sn(j, 1) = 10245 Then Exit For
  Next
 
  For jj = 2 To UBound(sn)
    sq(jj - 1) = sn(jj, j)
  Next
 
  For jj = 2 To 10
      y = Application.Small(sq, jj)
      For jjj = 1 To UBound(sn, 2)
        If sn(j, jjj) = y Then Exit For
      Next
      Debug.Print "row: " & j & vbTab & "value: " & y & vbTab & "column: " & jjj & vbTab & "columnhead: " & sn(1, jjj)
  Next
End Sub