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
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
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
...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...
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
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
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).