Berechnung Koordinaten

[Kim-Ole95]

Servus zusammen,

ich benötige Hilfe bei folgendem/r Problem/Fragestellung:

Ich habe eine Excel Liste mit Kunden- und Dealerdaten (PLZ, Koordinaten). Hier habe ich bereits anhand der Koordinaten berechnet wie groß die Distanz zwischen Kunde und Dealer ist. Im weiteren Schritt möchte ich nun wissen, ob der Kunde immer beim nächstgelegenen Händler gekauft hat oder nicht.

Hierzu wäre meine Idee eine neue Spalte (im Fotoanhang rot markiert) zu erstellen und dort die jeweils kürzeste Distanz vom Kunden zum nächstmöglichen Dealer anzeigen zu lassen. So könnte ich gut vergleichen, ob es einen näheren Dealer gab oder nicht.
Allerdings fehlt mir an der Stelle das Know How..

Ich freue mich über jede Hilfe

LG,
Kim-Ole

[lupo1]

Prämisse: Die Erde ist eine Scheibe, keine Kugel (zulässig bei geringen Entfernungen). Dann:

C1: =((A1-$D$1)^2+(B1-$E$1)^2)^0,5 zeigt die Entfernung eines Kunden. MIN kannst Du selbst.

In A:B stehen (bei mir) die Kunden, in D1:E1 der Dealer. Dein Bild interessiert mich nicht.

[Kim-Ole95]

Prämisse: Die Erde ist eine Scheibe, keine Kugel (zulässig bei geringen Entfernungen). Dann:

C1: =((A1-$D$1)^2+(B1-$E$1)^2)^0,5 zeigt die Entfernung eines Kunden. MIN kannst Du selbst.

In A:B stehen (bei mir) die Kunden, in D1:E1 der Dealer. Dein Bild interessiert mich nicht.

Hier berechnest du doch aber nur die Distanz zwischen Händler in Zeile X und Kunden in Zeile Y. Hier hast du ja keinen Bezug von Position Kunde X zu Distanz aller Händler - Oder sehe ich das falsch?

Vielen Dank schon einmal!

Viele Grüße,
Kim-Ole

[lupo1]

{=VERGLEICH(MIN(
((A1:A3-$D$1)^2+(B1:B3-$E$1)^2)^0,5);
((A1:A3-$D$1)^2+(B1:B3-$E$1)^2)^0,5;)} für die Zeile der kürzesten Strecke

=VERGLEICH(MIN(
((A$1:A$3-$D$1)^2+(B$1:B$3-$E$1)^2)^0,5);
((A$1:A$3-$D$1)^2+(B$1:B$3-$E$1)^2)^0,5;)=ZEILE(A1) als bedingte Formatierung von A1 aus bis A3 erweitern

[Kim-Ole95]

{=VERGLEICH(MIN(
((A1:A3-$D$1)^2+(B1:B3-$E$1)^2)^0,5);
((A1:A3-$D$1)^2+(B1:B3-$E$1)^2)^0,5;)} für die Zeile der kürzesten Strecke

=VERGLEICH(MIN(
((A$1:A$3-$D$1)^2+(B$1:B$3-$E$1)^2)^0,5);
((A$1:A$3-$D$1)^2+(B$1:B$3-$E$1)^2)^0,5;)=ZEILE(A1) als bedingte Formatierung von A1 aus bis A3 erweitern

Was mache ich falsch? (Siehe Anhang)

Danke für deine Hilfe!

[lupo1]

Dein Bild interessiert mich nicht.

http://xxcl.de/bitteBeachten (Nr. 5)

Bitte höre auf, im Zwiegespräch zu zitieren. Fullquotes sind besonders dämlich.

[Kim-Ole95]

Vielen Dank Lupo1 - die Regeln werde ich in Zukunft beachten.

Ich habe die Matrixformel nun verwendet und scheinbar ist bei mir noch ein Bezug falsch. Nun habe ich mal die Excel rangehängt statt eines Fotos. Leider erkenne ich meinen Fehler trotz der Matrix Formel noch nicht.

Danke im Voraus.

Liebe Grüße,
Kim-Ole

[lupo1]

1. Warum hat denn jeder Kunde (916) bitte seinen Dealer (916)? Sind das traute 1:1-Beziehungen?

2. Wieso setzst Du denn E2 und F2 absolut? Nur weil ich das getan habe? Du musst mich doch auch korrigieren ...

3. Der Wortlaut der Überschrift in M1 lässt mich stark daran zweifeln, ob zum Dealer der nächstgelegene Kunde gesucht werden soll. Also stattdessen (korrigiert Punkt 2. gleich mit):

M2: {=VERGLEICH(MIN(
(((B2-E$2:E$917)*70)^2+((C2-F$2:F$917)*111,32)^2)^0,5);
(((B2-E$2:E$917)*70)^2+((C2-F$2:F$917)*111,32)^2)^0,5;)}

4. VERGLEICH gibt den Datensatz zurück, der zutrifft. Nicht die Distanz. Die bekommst Du nun erst so:

N2: =(((B2-INDEX(E:E;M2+1))*70)^2+((C2-INDEX(F:F;M2+1))*111,32)^2)^0,5

5. 111,32 und 70 habe ich gratis (also ohne Deine Nachfrage) verwendet, um aus dem Netz der Breiten- und Längengrade Kilometer zu machen. Das geht aber nur, wenn/weil die Erde eine Scheibe ist - näherungsweise dabei in Europa. Liest Du Dich dazu denn jetzt noch ein, oder übernimmst Du das stumpf? Wäre mit 23 doch ein wenig früh.

[Kim-Ole95]

Hallo Lupo,

Zu Punkt 1: In der Liste siehst du immer bei welchem Dealer der jeweilige Kunde einen Kauf abgeschlossen hat. Dazu habe ich mir dann die Entfernung berechnet zwischen Dealer und Kunde. Die Frage die nun im Raum ist: Gibt es einen Händler der für den Kunden in einer kürzeren Distanz zu erreichen wäre oder hat er den Kauf schon beim nächstgelegenen Händler getätigt? In diesem Zuge möchte ich nun die Distanz haben zum nächstgelegenen Händler, um dies vergleichen zu können. Dies habe ich eingangs in meiner Frage aber auch so gestellt und ist in deiner Formel ja auch so berechnet.

Zu Punkt 4: Bekomme ich in M1 nun also einen Wert (z.B 43) der die Zeile angibt in der der Datensatz vorhanden ist?

Zu Punkt 5: Da alle Koordinaten in UK sind, ist die Annahme "Die Erde ist eine Scheibe" hier in Ordnung.

Du hast mir wirklich sehr geholfen. Ich werde mich in einer freien Minute bei der Arbeit mehr mit den Matrixformeln beschäftigen müssen, um deine Rechnung besser verstehen zu können.

Liebe Grüße,
KH

[lupo1]

Zur Frage: Yes, Sir (M2 statt M1).