読者です 読者をやめる 読者になる 読者になる

studies

いろいろ勉強用備忘録的な感じ

excelでちょっと複雑なソート

excel

今日は金曜日である。

来る休日に向けて心も軽やかになる素晴らしい曜日である。

 

さて、今日は少しだけ複雑なソートをexcelで行った。

 

どんなソートをしたかを説明してみよう。

とりあえず、例としてデータを作ってみたので、見てほしい。

f:id:developingskills1110:20160916223130p:plain

A列に市区町村、B列にその市区町村が所属する都道府県が入っている。

これを、次の条件でソートするのが本日の課題であった。

 1.基本的な順番は変えない。

 2.同じ都道府県どうしをまとめる。

たとえば、上の例のデータでいえば、次のようにソートすることになる。

 1.第4行まではすべて異なる都道府県なので、とりあえずここまでは何もしない。

 2.第1行で京都府京都市がでてきたあと、第5行で京都府宮津市が出てくる。同じ都道府県同士をまとめるので、第1行と第2行との間に、この第5行目のデータを挿入する。

 3.そのあとさらに下を見ていくと、長野県諏訪市が出てくる。このデータを長野県松本市のすぐ下に移動する。

 ・・・以下略。

 

賢いやり方もあるのだろうが、時間もないので、簡単に対応することにした。

次のような対応である。

 

C1 に1をとりあえず書く。つぎに、C2に次の式を書く。

 iferror(match(b2, $b$1:b1, 0),  row())

これを一番下までオートフィルする。

(たぶん固めなくてもできるけど)いちおうC列を値で固めてから、C列をキーにして、「並び替え」をする。

 

上の式の意味は、つまりこういうことである。

 今見てるレコードより上を見て、都道府県に同じ値を持つレコードがないかを先頭から順に探す。

 発見できなければ、レコード番号をそのまま優先度とする。

 発見できたなら、最初に見つかったレコードの優先度(=そのレコードの番号=先頭行からの位置)を、今見てるレコードの優先度とする。

 

そして、「並び替え」によって、この式によって設定した優先度でソートする。

 

というわけである。

 

結果はこんな風になる。

f:id:developingskills1110:20160917075754p:plain

 

ちなみに、この手順では次の事実を使っている。

 1.match関数は、検索範囲の先頭から順にレコードを探し、最初にヒットしたレコードの位置を返す。

 2.excelの「並び替え」は安定ソートである。

 

match関数が重く、何万行もあるデータのソートにはそれなりのストレスがあった。

 

面白い問題だと思うので、効率的なアルゴリズムがないかとか、C言語での書き方だとか、今後考えてみたい。