タスデータエクセルで一番近い店舗を求める方法 — 顧客と複数拠点の距離を一括計算
顧客リストと、自社の店舗や営業所が複数ある。顧客ごとに「一番近い拠点はどこで、何km離れているか」を付けたい。あるいは「この拠点から半径5km以内に顧客が何件いるか」を数えたい。ここでは、緯度経度の入った顧客リストと拠点リストから、エクセルの関数だけでこの2つを出す手順を、論点ごとに整理します。先に全体像を書くと、顧客が数百件・拠点が数か所の規模なら、式を3種類書いてコピーするだけで完結します。
土台になるのは、エクセルで2地点の距離を計算する方法で扱った式です。この記事はその続きで、1組の距離を出す式を、顧客×拠点の表全体に広げていきます。
この記事で使う表
例として、拠点を3か所（東京駅・新宿駅・横浜駅）、顧客を5件（渋谷駅・上野駅・品川駅・川崎駅・池袋駅の位置にいるものとします）置いた表を使います。緯度経度は、国土地理院の地名検索で得た各駅の値を小数第4位に丸めたものです。
拠点は横に並べ、1行目に名前、2行目に緯度、3行目に経度を入れます。顧客は縦に並べ、A列に名前、B列に緯度、C列に経度を入れます。色の付いたE4〜G8が、これから距離を入れていく範囲です。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | 拠点名 | 東京駅 | 新宿駅 | 横浜駅 | |||
| 2 | 拠点の緯度 | 35.6813 | 35.6882 | 35.4656 | |||
| 3 | 顧客 | 緯度 | 経度 | 拠点の経度 | 139.7662 | 139.6988 | 139.6201 |
| 4 | 渋谷駅 | 35.6588 | 139.7029 | ||||
| 5 | 上野駅 | 35.7112 | 139.7770 | ||||
| 6 | 品川駅 | 35.6281 | 139.7359 | ||||
| 7 | 川崎駅 | 35.5310 | 139.6971 | ||||
| 8 | 池袋駅 | 35.7332 | 139.7078 |
このあとH列に「一番近い拠点までの距離」、I列に「一番近い拠点の名前」、J列に「5km以内かどうか」を足していきます。
論点1：2地点の式を、1つの拠点に対して全顧客へ広げる
前の記事の式は、2地点の緯度経度がB2・C2とB3・C3に入っている前提でした。
=6371*ACOS(COS(RADIANS(B2))*COS(RADIANS(B3))*COS(RADIANS(C3)-RADIANS(C2))+SIN(RADIANS(B2))*SIN(RADIANS(B3)))6371は地球の半径（km）。結果はkmで、2点を一直線に結んだ直線距離です。
この式をそのまま下の行へコピーすると、参照しているセルが4つとも1行ずつ下へずれます。顧客の側はずれてほしく、拠点の側は動いてほしくありません。そこで、拠点のセルだけ列と行の前に$を付けて固定します（絶対参照）。
東京駅（E2・E3）と各顧客の距離を出す場合、E4に次の式を入れます。
=6371*ACOS(COS(RADIANS(B4))*COS(RADIANS($E$2))*COS(RADIANS($E$3)-RADIANS(C4))+SIN(RADIANS(B4))*SIN(RADIANS($E$2)))
元の式との対応は、B2が顧客の緯度（B4）、C2が顧客の経度（C4）、B3が拠点の緯度（$E$2）、C3が拠点の経度（$E$3）です。E4をE5〜E8へコピーすると、B4・C4だけがB5・C5、B6・C6…と変わり、$E$2と$E$3はそのまま残ります。結果は次の通りです（表示は小数第2位で四捨五入）。
| セル | 顧客 | 東京駅までの距離（km） |
|---|---|---|
| E4 | 渋谷駅 | 6.24 |
| E5 | 上野駅 | 3.46 |
| E6 | 品川駅 | 6.52 |
| E7 | 川崎駅 | 17.84 |
| E8 | 池袋駅 | 7.82 |
COS関数とSIN関数は、角度をラジアンで受け取ります。Microsoftサポートにも、COS関数について「角度はラジアン単位で指定する必要があります」と書かれています。緯度経度は度の単位なので、式の中でRADIANS関数を通してから渡しています。RADIANSを外すと、正しい距離とは違う数字になります。
拠点が1か所だけなら、ここまでで作業は終わりです。
論点2：顧客×拠点の距離表を、式1つのコピーで作る
拠点が複数あるときは、論点1の式を右のF列・G列にもコピーしたくなります。ところが$E$2と$E$3は列も固定しているので、右へコピーしても東京駅を指したままです。
そこで$を付ける場所を片側だけにします（複合参照）。
- 顧客の緯度経度は列だけ固定する（
$B4・$C4）。右へコピーしてもB列・C列のまま、下へコピーすると行が変わる - 拠点の緯度経度は行だけ固定する（
E$2・E$3）。下へコピーしても2行目・3行目のまま、右へコピーすると列が変わる
E4の式を次のように書き換えます。
=6371*ACOS(COS(RADIANS($B4))*COS(RADIANS(E$2))*COS(RADIANS(E$3)-RADIANS($C4))+SIN(RADIANS($B4))*SIN(RADIANS(E$2)))
このE4を、E4〜G8の範囲全体にコピーします。たとえば右下のG8は、参照が$B8・$C8（池袋駅）とG$2・G$3（横浜駅）に変わります。15個のセルすべてに同じ形の式が入り、距離表ができあがります。
| A | E | F | G | |
|---|---|---|---|---|
| 1 | 東京駅 | 新宿駅 | 横浜駅 | |
| 4 | 渋谷駅 | 6.24 | 3.29 | 22.75 |
| 5 | 上野駅 | 3.46 | 7.51 | 30.77 |
| 6 | 品川駅 | 6.52 | 7.48 | 20.89 |
| 7 | 川崎駅 | 17.84 | 17.48 | 10.07 |
| 8 | 池袋駅 | 7.82 | 5.07 | 30.79 |
拠点が増えたときは、G列の右に列を足して1〜3行目に名前・緯度・経度を入れ、同じ式をコピーします。論点3以降で使うH〜J列は、その分だけ右へずれます。顧客が増えたときは、行を足して同じ式を下へコピーします。
出てきた数字の確かめ方
式の写し間違いは、信頼できる別の計算と1組だけ突き合わせると見つけられます。国土地理院の測量計算サイトには「距離と方位角の計算」があり、2点の緯度経度から距離（測地線長）と方位角を返します。
この計算は地球を楕円体として扱うので、地球を半径6371kmの球とみなすエクセルの式とは、数字が少しだけ違います。渋谷駅から新宿駅の例では、エクセルの式が3.290km、測量計算サイト（楕円体はGRS80）が3,283.068mで、差は約7mです。上の表の15組すべてを突き合わせた結果、差は最大で約58m、割合ではどの組も0.3%未満でした。桁が違う、何倍もずれている、という場合は式か緯度経度の入力に誤りがあります。
もう一つ、出てくるのは直線距離です。道路を通った道のり距離とは別の指標で、車や徒歩の移動距離を表す数字ではありません。
論点3：一番近い拠点までの距離と、その拠点名を取り出す
距離表ができれば、顧客ごとの「一番近い拠点」は、その行の最小値と、最小値がある列の見出しです。
最小の距離（H列）
H4に次の式を入れ、H8までコピーします。
=MIN(E4:G4)
MIN関数は、指定した範囲の中で一番小さい数値を返します。E4:G4はその顧客の行に並んだ3つの拠点までの距離なので、行内で一番小さい距離が返ります。
その拠点の名前（I列）
I4に次の式を入れ、I8までコピーします。
=INDEX($E$1:$G$1,1,MATCH(H4,E4:G4,0))
内側のMATCH関数は、H4の値（最小の距離）がE4:G4の左から何番目にあるかを返します。3つ目の引数の0は「完全に一致する最初の値を探す」という指定です。外側のINDEX関数はINDEX(配列, 行番号, 列番号)の形で、拠点名の並び（$E$1:$G$1）の1行目・その番号の列にある値を返します。拠点名の範囲は、下へコピーしても動かないように$で固定しています。
INDEX関数はMicrosoft 365・Excel 2024・2021・2019・2016で使えます。
| A | E | F | G | H | I | |
|---|---|---|---|---|---|---|
| 3 | 顧客 | （東京駅） | （新宿駅） | （横浜駅） | 最寄りの距離 | 最寄りの拠点 |
| 4 | 渋谷駅 | 6.24 | 3.29 | 22.75 | 3.29 | 新宿駅 |
| 5 | 上野駅 | 3.46 | 7.51 | 30.77 | 3.46 | 東京駅 |
| 6 | 品川駅 | 6.52 | 7.48 | 20.89 | 6.52 | 東京駅 |
| 7 | 川崎駅 | 17.84 | 17.48 | 10.07 | 10.07 | 横浜駅 |
| 8 | 池袋駅 | 7.82 | 5.07 | 30.79 | 5.07 | 新宿駅 |
読み取るときの注意が2つあります。
- 同じ距離の拠点が2つあった場合、MATCH関数は最初に見つけたほうを返すので、左側の列の拠点名が入ります
- 川崎駅の行のように、最寄りでも10kmを超える顧客がいます。I列の拠点名だけを見ると「近い」と読み違えるので、H列の距離とセットで見る形になります
論点4：半径◯km以内の顧客を数える・印を付ける
距離表とH列があれば、「半径5km以内」は「5以下」という条件に置き換えられます。数え方は目的によって3通りあります。
拠点ごとに、5km以内の顧客数を数える
東京駅の列（E4:E8）のうち5以下のセルを数える式です。E10に入れて、F10・G10へコピーします。
=COUNTIFS(E4:E8,"<=5")
結果は東京駅が1件（上野駅）、新宿駅が1件（渋谷駅）、横浜駅が0件です。条件を"<=10"に変えると、東京駅4件・新宿駅4件・横浜駅0件になります。池袋駅は新宿駅から5.07kmなので、5km以内には入りません。
この数え方では、2つの拠点のどちらからも5km以内にいる顧客は、両方の拠点で1件ずつ数えられます。拠点ごとの件数を足しても、顧客の実数にはなりません。
「一番近い拠点がここで、かつ5km以内」を数える
顧客を拠点に重複なく割り振って数えたいときは、条件を2組にします。COUNTIFS関数は、すべての条件を満たした行だけを数えます。
=COUNTIFS(I4:I8,"東京駅",H4:H8,"<=5")
結果は1件（上野駅）です。"東京駅"を"新宿駅"に変えると1件（渋谷駅）、"横浜駅"に変えると0件です。条件に使う2つの範囲（I4:I8とH4:H8）は、同じ行数・列数にそろえる必要があります。
COUNTIFS関数はMicrosoft 365・Excel 2024・2021・2019・2016で使えます。
該当する顧客に印を付ける
件数ではなく「どの顧客が該当するか」を知りたいときは、J4に次の式を入れてJ8までコピーします。
=H4<=5
最寄りの拠点までの距離が5km以内ならTRUE、そうでなければFALSEが入ります。例の表では、渋谷駅と上野駅がTRUE、品川駅・川崎駅・池袋駅がFALSEです。J列がTRUEの行が、いずれかの拠点から5km以内にいる顧客の一覧になります。
どの拠点からも5km以内に入らない顧客の件数は、次の式で数えられます。例の表では3件です。
=COUNTIFS(H4:H8,">5")
論点5：件数が増えたときに起きることと、緯度経度が無い場合
式の数は「顧客数×拠点数」で増える
距離表のセルの数は、顧客数と拠点数の掛け算です。1つのセルの式には、RADIANSが6回、COSが3回、SINが2回、ACOSが1回、合わせて12回の関数が入っています。
| 顧客数 | 拠点数 | 距離の式の数 | 距離表の列の範囲 |
|---|---|---|---|
| 5件 | 3か所 | 15個 | E〜G列 |
| 300件 | 5か所 | 1,500個 | E〜I列 |
| 1,000件 | 20か所 | 20,000個 | E〜X列 |
| 10,000件 | 50か所 | 500,000個 | E〜BB列 |
顧客が数百件・拠点が数か所の規模なら、この記事の方法で十分です。式の数は数千個で、作業の中身は5件×3拠点の例と変わりません。式を1つ書いて、コピーする範囲が広がるだけです。
拠点が数十か所になると、表が横に長くなります。50か所なら距離表はE列からBB列までになり、H列・I列に置いていた式は距離表の右側に移して、範囲をE4:BB4のように書き換える形になります。式の数も50万個になり、1つのブックに入れる計算の量が、例の表の3万倍を超えます。
長い式に名前を付けて短くする（Microsoft 365・Excel 2024）
距離の式は長く、セルごとに同じものが入ります。LAMBDA関数を使うと、この式に名前を付けて、自作の関数として呼び出せます。LAMBDA関数の適用先はExcel for Microsoft 365とExcel 2024（どちらもMac版を含む）で、Excel 2021は入っていません。
Windows版の手順は次の通りです。
- 「数式」→「名前の管理」→「新規」を選ぶ（Mac版は「数式」→「名前の定義」）
- 「新しい名前」ダイアログの「名前」に関数名を入れる。ここでは
KYORIとする - 「参照先」に下のLAMBDA式を入れ、「OK」→「閉じる」の順に押す
=LAMBDA(ido_a,keido_a,ido_b,keido_b,6371*ACOS(COS(RADIANS(ido_a))*COS(RADIANS(ido_b))*COS(RADIANS(keido_b)-RADIANS(keido_a))+SIN(RADIANS(ido_a))*SIN(RADIANS(ido_b))))ido_a・keido_aが1地点目の緯度・経度、ido_b・keido_bが2地点目の緯度・経度です。
登録すると、そのブックの中ではもとからある関数と同じように呼び出せます。論点2のE4の式は、次の1行に置き換わります。$の付け方は論点2と同じです。
=KYORI($B4,$C4,E$2,E$3)
計算の中身は同じなので、結果も論点2の表と同じ数字になります。短くなるのは式の見た目で、式の数そのものは減りません。
緯度経度が無い場合
ここまでの式は、顧客と拠点の両方に緯度経度が入っていることが前提です。手元にあるのが住所だけの場合、先に住所を緯度経度に変える工程が要ります。距離の式には住所の文字列を入れられないからです。
件数が少なければ、地図で1件ずつ位置を調べて、緯度と経度を手で入力する方法で足ります。拠点は数が限られるので、この方法が現実的です。手間が大きくなるのは顧客の側です。数百件、数千件の住所を1件ずつ調べる作業が、距離の式を書く作業よりずっと長くなります。
この工程をまとめて済ませる選択肢の一つが、タスデータです。住所の入ったCSVをアップロードすると、緯度経度・最寄り駅・市区町村コード・半径内の人口などの列が、元の表の右側に追加されて返ります。Freeプランは月100行までです。地図の画面は持っていません。返ってきた表の緯度と経度の列を、この記事のB列・C列に当てはめると、論点1から先の式がそのまま使えます。住所から緯度経度に変える方法の全体は、住所を緯度経度に一括変換する方法で整理しています。
住所リストに緯度経度を一括で付けられます。
メール登録だけ・月100行まで無料。距離計算の土台となる緯度経度を、まとめて用意できます。
まとめ
- 2地点の距離の式は、拠点のセルを
$E$2のように固定すると、1つの拠点に対して全顧客へコピーできる - 顧客を
$B4（列だけ固定）、拠点をE$2（行だけ固定）にすると、式1つのコピーで顧客×拠点の距離表ができる - 一番近い拠点までの距離はMIN関数、その拠点名はINDEX関数とMATCH関数で取り出せる
- 半径◯km以内の件数はCOUNTIFS関数で数えられる。拠点ごとに数える方法と、最寄りの拠点に割り振って数える方法では、結果の意味が違う
- 出てくるのは直線距離。国土地理院の測量計算サイトの値との差は、例の15組ではどれも0.3%未満
- 式の数は顧客数×拠点数で増える。緯度経度が無い場合は、先に住所から緯度経度への変換が要る