エクセルの並べ替えについて

質問

お世話になっております。
エクセルでデータの並び替えをしたいのですが、
うまくいかず困っています。

　Ａ　　　　Ｂ
都道府県　会社名

というデータがあります。

どの項目も重複があります。
例）Ａ社は北海道・沖縄・福岡にもあったりする

基本的に都道府県別（ユーザーリストで北から順に並び替え出来ます）
会社順　にしたいのですが、複数の件に存在する企業がうまく合体出来ません。
北海道　Ａ社の部分に　福岡・沖縄のＡ社も入れたいのです。

作業列も作成するのは構わないので、方法を教えていただけたら
幸いです。

データが1000件以上あるので、会社順にしてからの手作業とかは無理なので
大変お手数ですが宜しくお願い致します。

回答No.6

chie65536（@chie65535） · ベストアンサー

要は「都道府県別に、北から順に並べたいが、複数の都道府県に重複して存在する会社は、一番北の都道府県にまとめたい」と言う事ですね。

例えば

北海道　A社
東京　A社
北海道　B社
北海道　C社
岩手　D社
東京　D社
大阪　D社
北海道　E社
北海道　F社
（以下略）

のように「同じ会社を、ひとまとめにして、都道府県ごとに並べたい」と言う事ですよね（A社とD社を、ひとまとめにしておきたい）

本来であれば「一番北の都道府県」ではなく「本社所在地」にすべきですが、そこは無視します。

であれば「会社ごとに、キーになる項目（例えば、本社所在地など）」を作って、それをもとに、並び替えする必要があります。

例えば「本社所在地」という項目を作り

都道府県　会社名　本社所在地
北海道　　A社　　　東京
東京　　　A社　　　東京
北海道　　B社　　　北海道
北海道　　C社　　　北海道
岩手　　　D社　　　東京
東京　　　D社　　　東京
大阪　　　D社　　　東京
北海道　　E社　　　北海道
北海道　　F社　　　北海道
（以下略）

というデータにして、並び替えを「本社所在地」「会社名」「都道府県」をキーにして並び替えします。すると

都道府県　会社名　本社所在地
北海道　　B社　　　北海道
北海道　　C社　　　北海道
北海道　　E社　　　北海道
北海道　　F社　　　北海道
北海道　　A社　　　東京
東京　　　A社　　　東京
岩手　　　D社　　　東京
東京　　　D社　　　東京
大阪　　　D社　　　東京
（以下略）

のように、期待通り（本社所在地が北から順）に並び替えできます。

なお「都道府県」や「本社所在地」のソートの際には、並び順は「ユーザー設定リスト」を使用します。

並び順の「ユーザー設定リスト」の使い方
http://www.asahi-net.or.jp/~ef2o-inue/kihon/sub01_05_24.html

上記ページに「北からの都道府県の一覧」がありますから、このページに記載された順で「ユーザー設定リスト」を作成すると良いでしょう。

なお、都道府県には以下ページのような「コード番号」が付いているので、この「コード番号順」に並べるのが良いでしょう。

都道府県コード表
http://www.kenchikushikai.or.jp/touroku/documents/code-todoufuken.pdf

お礼

その通りです。解決しました！
都道府県は、コード表を基にユーザーの並べ替えに
追加していたので、質問がうまくなくて申し訳なかったです。

ありがとうございます。助かりました！

回答No.14

kagakusuki

回答No.7、11です。
　済みません、回答No.4に対する補足コメントを見ていなかったので、御質問文の

>北海道　Ａ社の部分に　福岡・沖縄のＡ社も入れたいのです。

という箇所の意味を「Ａ社の拠点がある都道府県のデータを、全て1行にまとめてしまいたい」という意味であるものと勘違いをしておりました。
　それで、シートのレイアウト等の基本的な条件は回答No.11とほぼ同じであるものとした場合、お求めになられている事を自動的に実現する方法は以下の様なものとなります。

　まず、Sheet3のD2セルに次の関数を入力して下さい。

=IF(OR(INDEX(Sheet1!$A:$A,ROW())="",INDEX(Sheet1!$B:$B,ROW())=""),"",IFERROR(MATCH(INDEX(Sheet1!$A:$A,ROW()),$A:$A,0)+COUNTIF(Sheet1!$B:$B,"<"&INDEX(Sheet1!$B:$B,ROW()))*100,""))

　次に、Sheet3のE2セルに次の関数を入力して下さい。

=IF(ISNUMBER($D2),($D2+MOD(LARGE($D:$D,COUNTIF($D:$D,">"&ROUNDDOWN($D2,-2))),100)*1000000000)*1000+COUNTIF($D$1:$D2,$D2),"")

　次に、Sheet3のD2～E2のセル範囲をコピーして、同シートのD列～E列の3行目以下に貼り付けて下さい。

　次に、Sheet2のA3セルに次の関数を入力して下さい。

=IF(ROWS($3:3)>COUNT(Sheet3!$E:$E),"",INDEX(Sheet1!$A:$C,MATCH(SMALL(Sheet3!$E:$E,ROWS($3:3)),Sheet3!$E:$E,0),COLUMNS($A:A)))

　次に、Sheet2のA3セルをコピーして、同じ行のA3セルよりも右の方にあるセルに対して、必要な列数分(元データの表の列数分)だけ貼り付けて下さい。
　次に、Sheet2の3行目をコピーして、同シートの4行目以下に貼り付けて下さい。

　以上です。

お礼

ありがとうございます。

皆様のおかげで助かりました！
お礼が遅くなり申し訳ありません。

また宜しくお願い致します。

回答No.13

bunjii

回答No.9です。
補足が無いので勝手解釈の模擬データを作成してExcel 2013で検証しました。
Sheet2に都道府県コード一覧表を作成し、Sheet1のC列へソート用の都道府県のコードをVLOOKUP関数で抽出します。
C2=VLOOKUP(A2,Sheet2!A$1:B$47,2,FALSE)
C2セルを下へ必要数コピーしました。
Sheet1の全体を選択して「並べ替えとフィルター」で優先されるキーを「会社名」、次に優先されるキーを「コード」として並べ替えを行い添付画像のような結果を得ました。

お礼

ありがとうございます。解決しました。
お礼が遅くなり申し訳なかったです。

皆様のおかげで作業がはかどりました（涙）

回答No.12

tsubu-yuki

ほとんど読んでいませんが、
都道府県には（市区町村にも）コードがついていますので、
　　http://www.soumu.go.jp/denshijiti/code.html
とりあえず、コレを一列追加して、
エクセル一般機能の並べ替えで、優先される順に
　・会社名、値、昇順
　・都道府県コード、値、昇順
で並べ替えてやれば簡単にできるはずですよ。

お礼

ありがとうございます。
解決しました！

回答No.11

kagakusuki

今仮に、元データの

　Ａ　　　　Ｂ
都道府県　会社名

という表が存在しているシートのシート名がSheet1であるものとし、その中で「都道府県」や「会社名」などといった項目名が入力されている行が1行目であるものとします。
　そして、Sheet3のA列に都道府県の名称が北にあるものから順番に並べられている表が作表されていて、Sheet3のD列～F列を作業列として使用して、Sheet2のA3以下に「拠点が北にあるものの順に並べ替えられた会社名」が、同じくB3以下に「A列に名称が表示されている会社の拠点が存在している全ての都道府県名」が自動的に表示される様にするものとします。(尚、この回答文の下に表示されている添付画像中には、「どの様な順番で都道府県が並べられているのか」を示すためだけのためにSheet3のB列の所にJIS X 0401の都道府県コードのリストも併記されているところも写ってはいますが、本回答の方法による処理ではこのコードを必要とはしておりません)

　その場合、まずSheet3のD2セルに次の関数を入力して下さい。

=IF(OR(INDEX(Sheet1!$A:$A,ROW())="",INDEX(Sheet1!$B:$B,ROW())=""),"",IF(COUNTIFS(Sheet1!$A$1:INDEX(Sheet1!$A:$A,ROW()-1),INDEX(Sheet1!$A:$A,ROW()),Sheet1!$B$1:INDEX(Sheet1!$B:$B,ROW()-1),INDEX(Sheet1!$B:$B,ROW())),"",IFERROR(MATCH(INDEX(Sheet1!$A:$A,ROW()),$A:$A,0)+COUNTIF(Sheet1!$B:$B,"<"&INDEX(Sheet1!$B:$B,ROW()))*100,"")))

　次に、Sheet3のE2セルに次の関数を入力して下さい。

=IF(ISNUMBER($D2),IF(RANK($D2,$D:$D,1)=COUNTIF($D:$D,"<"&ROUNDDOWN($D2,-2))+1,MOD($D2,100)*10000000+INT($D2/100),""),"")

　次に、Sheet3のF2セルに次の関数を入力して下さい。

=IF(ISNUMBER($D2),INDEX(Sheet1!$A:$A,ROW())&IF(COUNTIFS($D:$D,">"&$D2,$D:$D,"<"&ROUNDUP($D2,-2)),","&INDEX(F:F,MATCH(LARGE($D:$D,RANK($D2,$D:$D)-1),$D:$D,0)),""),"")

　次に、Sheet3のD2～F2のセル範囲をコピーして、同シートのD列～F列の3行目以下に貼り付けて下さい。

　次に、Sheet2のA3セルに次の関数を入力して下さい。

=IF(ROWS($3:3)>COUNT(Sheet3!$E:$E),"",INDEX(Sheet1!$B:$B,MATCH(SMALL(Sheet3!$E:$E,ROWS($3:3)),Sheet3!$E:$E,0)))

　次に、Sheet2のB3セルに次の関数を入力して下さい。

=IF($A3="","",INDEX(Sheet3!$F:$F,MATCH(SMALL(Sheet3!$E:$E,ROWS($3:3)),Sheet3!$E:$E,0)))

　次に、Sheet2のA3～B3のセル範囲をコピーして、同シートのA列～B列の4行目以下に貼り付けて下さい。

　以上です。

お礼

難しい関数を使いこなされていて尊敬します。
思うような結果が得られました。
ありがとうございます。
助かりました。

回答No.10

mt2015

こんな感じでどうでしょう。
添付の図を見てください。
F:G列を都道府県と、「南から順」に並べた時の順番のリストに使用します（北海道が48～沖縄県が1）

C列とD列を作業列として使用。

C1に「都道府県コード」
C2に↓の式
=VLOOKUP(A2,F:G,2,FALSE)

D1に「最北都道府県」
D2に↓の式
=SUMPRODUCT(MAX((C$2:C$1000)*(B$2:B$1000=B2)))

を入れてC2:D2を下までコピーします。
＃データが1000行以上あるときは式中の1000を変更してください

次に並び替え。
A:D列を選択してデータ→並び替えで、
「先頭行をデータの見出しとして使用する」にチェックをつけ、
最優先されるキー：最北都道府県、順序：降順
次に優先されるキー：会社名、順序：昇順
次に優先されるキー：都道府県コード、順序：降順
で、並び替えて出来上がり。

お礼

いただいたものでも解決しました。
ありがとうございます。

助かりました！

回答No.9

bunjii

>基本的に都道府県別（ユーザーリストで北から順に並び替え出来ます）
「ユーザーリストで北から順に並び替え」はどのようなキー（指標）で実行していますか？
そのキーの存在を隠して回答を求めるのは落とし穴付きのクイズと同じです。

>会社順　にしたいのですが、複数の件に存在する企業がうまく合体出来ません。
「複数の件に存在」は「複数の都道府県に存在」と読み替えて回答します。
Excelの「並べ替え」で簡単にできるはずです。
並べ替えのキーの優先順位を「会社名」を第1とし、都道府県の並べ替え用のキー（提示されていない指標）を第2順位にすれば目的通りになります。
尚、Excelのバージョンも提示された方が良いでしょう。

お礼

都道府県は、コード表を基にユーザーの並べ替えに
追加していたので、質問がうまくなくて申し訳なかったです。

ありがとうございます。助かりました！

回答No.8

imogasi

>北海道　Ａ社の部分に　福岡・沖縄のＡ社も入れたいのです
A社でソートすると、北海道、福岡、沖縄などが、近く（会社名がつらなった行）のエクセルシートの行に出てくるのでしょうが、その「北海道、福岡、沖縄・・」の中で、どういう基準で北海道を選ぶ基準が決まるのですか。これを書かないで質問するのは質問として至らないですよ。
「北の方から」なんて考えても、コンピュターには判らないよ。
府県名で府県コードを何とか索引して持ってきて（VLOOKUPのテーブルなど）そのコード順でよいと決めれば手はある。しかし府県コード（JISコードです）大体北から降られているのだが、個別にはよくわからない順ですが。
自社で同一会社名の中で、府県間の優先順位にするコードを、VLOOKUPのテーブルで振って、そのコードを第２順位（第１順位は会社名コード）にして、会社ごとの塊の最初のコードに統一すればできる。
会社名の並び順も普通は注文がつくはずだが、そのことのむつかしさを、気づいているのかな。データベースなど考えたこともないようなレベルに見えるが。
ーー
私ならその後はVBAでやるなあ。関数では複雑になるようなので。
コンピュターは、すべてデータ基準を必要とする。なければ付与しなければならない。頭で決めていても何にもならない。昔からこの点は悩ましく、注意深くデータを作る段階からコード（手作業やコード引きなどで）を与えたりしたものだ。
地域分析で線路の北側と南側で顧客を分けたいときはｍ手数でも顧客住所登録をするとき、人間がどちらかのコードを判別して、顧客マスターファイルに入れたりして手間なことだった。住所町名で区分コードを振る手もあるが、正確となるケースばかりではない。

お礼

ありがとうございます。説明が足りなくて申し訳ありません。
都道府県はコード別にユーザ設定で並び替えを入れていたので
その順にするつもりでした。
会社の順番については、エクセルの並び替えの昇順でいいと
言われていたので、そのままにしてあります
（本来は色々あるのはわかっております）

VBAはまだ簡単なものしか作れないので、勉強します。
ありがとうございました。

回答No.7

kagakusuki

質問者様がやりたいと考えておられる事に関して確認したいのですが、

>例）Ａ社は北海道・沖縄・福岡にもあったりする
>北海道　Ａ社の部分に　福岡・沖縄のＡ社も入れたいのです。

という事は、つまり「本社の所在地が北にある会社から順番に並べて行く」のではなく、「支社や営業所、工場などの『"その会社の拠点"の中で最も北にある拠点』が北にある会社から順番に並べて行く」様にすれば宜しいのでしょうか?

補足

ありがとうございます。その通りです。
「支社や営業所、工場などの『"その会社の拠点"の中で最も北にある拠点』が北にある会社から順番に並べて行く」
です。
本来であれば本社の所在地が北にある会社から・・・と思ったのですが
会社名の一覧しかなく、本社所在地の一覧が無い状態であったため
一旦その方向で並び替えをすることになりました。
会社の件数もユニーク数で500ほどあるので…。

回答No.5

Safe_Mode

No2,3です。
質問の内容を勘違いしてました。
先頭が都道府県名ですね。
これで一旦並べ替えてから会社名をVlookupで読み込むといった処理でできませんかね？

お礼

ありがとうございます。他の回答でなんとか解決しました！
説明が下手で申し訳なかったです。

回答No.4

ランラン（@arashiblast）

ちょっとよくわからないんですが、
1.A列を都道府県の北から順に並びかえる
2.B列を会社ごとにまとめる？
ということですか？

補足

ありがとうございます。
A列は北から順番、B列は会社順なのですが、
同じ会社が複数にわたった場合、（北海道A社　埼玉A社など）
北海道A社の次に埼玉A社が入って、その次はまた北海道B社・・・
になるようにしたいのですが…わかりにくくて申し訳ありません。