Excelで同一キー項目が複数行ある時にそれを1つにまとめる方法

「Excel Tips」ではExcelを使用していて気付いたことを取り上げています。

今回はExcelで1つのキー項目(例えば1つの地方)に複数レコード(例えば都府県)が存在する時に、それを1行に取りまとめる(例えば都or府/県…)方法をご紹介いたします。

やりたい事のイメージとしては下記のようになります。

基準リストの項目数やグループの数が少なければ全て手作業でも対応できる事と存じますが、今回想定しているのは全て手作業では時間がかかり過ぎて対応できない場合です。

なおマクロ(VBA)は使わないで対応しています。その代わりにMATCH関数、IF関数、FIND関数や「フィルタ適用」など利用しています。

32bit版Excel 2016の バージョン2204(ビルド 15128.20178)を使用して検証しています。

e-Statの令和２年市町村別農業産出額（推計）のデータを使う

冒頭でお話しした地方を都道府県でまとめるのは手作業でも可能であり得られる結果も目新しいものがないので、今回は「e-Stat(政府統計の総合窓口)」で公開されている「令和２年市町村別農業産出額（推計）」データを使ってご説明したいと思います。

統計表のなかの「全国」のEXCELをダウンロードして開くと下記のようなワークシートが開かれます。

今回は赤枠でくくった項目を使って「市町村別の個々の耕種・畜産のトップ20を取り出して、各市町村別にとりまとめる」作業をしながら、後段で本テーマをご説明したく存じます。

「全市町村」データに順位項目を追加する

本章と次の章で加工する作業の内容を順を追ってご説明いたしますが、本テーマの具体的な内容についてのみをお知りになりたい方は「同一キー項目が複数行あるデータとは?」の章まで読み飛ばしていただれば幸いです。

データを整形する

まずは今回使用しないデータ項目やヘッダーを取り除きます。

簡単に実施する作業を列挙いたします。

1. まずはタイトル行全体のA5:AC10のセル範囲を指定してから「ホーム」タブメニュの「セル結合して中央揃え」をクリックしてセル結合を解除します。
2. 赤枠で囲んだ項目以外の列は削除します。
3. 項目タイトルを1行で作り直し、それ以外のヘッダー行は削除します。
   • セル内の改行や不要な項目名間のスペースも取り除きます。
   • 都道府県や市町村のセルの文字の配置を「均等割り付け」から「標準」に変更します。
   • 項目名の「折り返して全体を表示」の設定も解除します。
4. 産出額が「-」や「x」の値になっているものは、そのままでは順位付けできないので「0」にすべて置換します。

作業後の「全市町村」ワークシートはつぎのようになります。

個々の耕種・畜産の順位を表すために必要な列を追加する

「市町村別の個々の耕種・畜産のトップ20を取り出す」にあたり個々の耕種・畜産に対してつぎの3列を追加して行きます。

1. 都道府県列
   • ワークシート上の表としてはA列にありますが、個々の耕種・畜産の産出額で並び替える必要があるので追加します。
2. 市町村列
   • 都道府県と同様の理由でワークシート上のC列とは別に追加します。
3. 順位列
   • 産出額の順位を表すための列を追加します。

なお都道府県コードと市町村コードについては、都道府県列と市町村列があれば後からVLOOKUP関数で参照設定することで付与することができるため、ここでは省略して列数が増える事を抑えます。

下記のようにF列に順位列、G列に都道府県、H列に市町村を追加した後で、追加した3列を列コピーして、各項目の後に「コピーしたセルの挿入」をして行くと少し作業の負荷が軽減されます。

順位列にRANK.EQ関数をセットして並べ替える

順位列にはRANK.EQ関数を入力します。次の数式はE2セルに対象とする産出額がセットされている場合のF2セルへの入力例です。

=RANK.EQ(E2,E$2:E$1720,0)

この関数は「E2セルの値が対象セル範囲を降順に並べ替えた時に上から何番目に該当するか?」を返します。

• 2番目の引数の「E$2:E$1720」セル参照はタイトル行を除いたデータ行の範囲を示しています。
  • 今回のワークシートには1,718市町村と1東京都特別区の計1,719のデータが掲載されています。
• 3番目の引数「0」は上記セル範囲のデータを降順に並べる事を指定しています。

F2セルに入力が終わったら、そのセルをコピーしてF3:F1720のセル範囲に貼り付けて、後はF列を列コピーして各順位列に「数式で貼り付け」をします。

最後に個々の耕種・畜産で「都道府県」から「順位」までをセル範囲で選択し、「順位」を「小さい順」で並び替えをします。少し大変ですがすべての品目で同様の作業を繰り返します。

以上でデータの整形は終了です。続いて個々の耕種・畜産のトップ20を取り出して加工する作業についてご説明いたします。

個々の耕種・畜産のトップ20を取り出して加工する

前章までの作業で個々の耕種・畜産の生産額の大きい市町村順にデータが並んでいますので、そこからトップ20位に該当する市町村を取り出して「別ワークシートに値貼り付け」します。

トップ20位と言っても必ず20位で20市町村になるか?と言うと、そう言う訳ではありません。

例えば17位が同じ値で5つの市町村になっている場合は17位の次は22位になり、取り出すのは17位までですが該当するのは22市町村になります。

そのため今回はタイトル行を含めてA1:BZ22のセル範囲をコピーして「TOP20」ワークシートに値貼り付けした後に20位を超える市町村を手作業で削除or値のクリアをして行きます。

なお「米」の品目にだけ存在する「都道府県コード」B列と「市町村コード」D列はここでは一旦削除して、他の品目と項目を揃えておきます。

そして各品目が縦一列に並ぶようにデータを切り取って貼り付けていきます。この作業は最後の品目まで繰り返して行います。(品目ごとに移動させる事も考えられますが、その場合は切り貼りした後に先頭行に戻ってくる必要があるので、その行き来が手数として増える事になります。)

このままだと各行が「何の品目か?」分からないので「順位」項目の後に「品目」欄を設定して、各々すべてのタイトル行に「品目」をセットします。(下図のように「都道府県」のフィルタで「都道府県」だけを選択しておくと操作がし易くなります。)

タイトル行から「品目」を各行の先頭にコピー貼り付けします。(下図のように「順位」列のファイタで「順位」と「1」のみ選択して表示しておくと作業がし易くなります。)

それをTOP20の対象行のセル範囲に貼り付けするかまたはオートフィル機能を使ってセットします。

すべての「品目」が終わったら、すべてのデータをセル範囲で選択して「都道府県」・昇順、「市町村」・昇順、「順位」・小さい順で並べ替えをします。

並べ替えまで終わるといままで残してあったタイトル行は不要になりますので、まとめて削除します。

以上で本テーマをご説明するためのデータを用意することができました。前段が長くなりましたが次の章から本題に入ります。

同一キー項目が複数行あるデータとは?

今回使用するデータは「都道府県・市町村別耕種・畜産19品目の産出額トップ20」になりますが、下図を見ていただくとお分かりになるように1つの都道府県・市町村がトップ20の耕種・畜産品目を複数持っています。

1,718市町村と1東京都特別区の計1,719の内、19品目の産出額TOP20として上図にでてくるのは240市町村つまり16%強の市町村になります。

また240の内の153市町村は単独品目であるのに対して、残りの87市町村は複数の品目がTOP20入りしています。

という事はこの87市町村が同一キー項目が複数行あるデータになります。

ではどのようにして複数行を1つにまとめて行くのか?順を追ってご説明いたします。

複数行を1つにまとめて行くには

まとめて行くためには次の3つのステップを踏んで行きます。

1. 対象となるキー項目が「単独である」にせよ「複数行ある」にせよ、キー項目の区切りが分かるようにする。
2. 単独の場合はそのまま、複数行ある場合は「ひとつ前の行」と「現在の行」を区切り文字を付与して結合する操作を繰り返して行く。
3. 単独の場合はそのまま、複数行ある場合はその中から最後の行だけを取り出す。

①キー項目の区切りの見える化

「見える化」といっても大した話ではなく列を1つ追加してそこに数式をセットするだけです。

F列は一列空けて(特に意味はありませんが)、G1セルにタイトル「印」と入力し、G2セルに次のIF関数をセットします。

=IF(B1<>B2,1,0)

• B列の「市町村」に対して現在のセルの値がひとつ前の行のセルの値と異なる場合は「1」をセットし、同じ場合は「0」をセットします。

この数式をG2セルをコピーして、G3:G386のセル範囲に貼り付けると下図のようになります。

ただしキー項目が1つの場合はこれで充分なのですが、今回のデータに関して言えば実は『「都道府県」をまたぐ同一の「市町村」名』が存在します。例えば「府中市」は東京都と広島県に存在します。

つまりこのデータにはキー項目が2つ存在しています。

そのため、同じ「市町村」である事を判断するためには同一の「都道府県」である事を確認する必要があるのですが、今回のTOP20のデータを見ると『「都道府県」をまたぐ同一の「市町村」名』は存在していません。

となると対応する必要はないのですが一応念のためにキー項目が2つ存在する場合の数式もご説明いたします。当然のことながら数式が少し複雑化します。

=IF(B1<>B2,1,IF(A1<>A2,1,0))

• B列の「市町村」だけではなく、A列の「都道府県」に対しても現在のセルの値がひとつ前の行のセルの値と異なる場合は「1」をセットし、同じ場合は「0」をセットします。

なお先ほどもご説明しましたが、この数式をセットしても結果は変わりませんので画面キャプチャは省略いたします。

以上でキー項目の区切りの見える化は完了します。

②同一キー項目の時は「ひとつ前の行」と「現在の行」を区切り文字を付与して結合する

①でG列にキー項目の区切りがセットされているので、それを見ながら条件に当てはまる場合に「ひとつ前の行」と「現在の行」を区切り文字を付与して結合すれば良い事になります。

なおキー項目が変わった時は「ひとつ前の行」は取り込まずに「現在の行」だけをセットします。

もう1列追加して今回はH1セルに「途中経過」と入力し、H2セルに次のIF関数をセットします。

=IF(G2=1,D2&"-"&E2,H1&","&D2&"-"&E2)

• G2セルが1の時は「現在の行」の「順位-品目」(産出額はごちゃごちゃするので省略しています)を書き出し、1以外の時は、H1セル(「ひとつ前の行」)にカンマ(,)を付けて「現在の行」の「順位-品目」を書き出しています。
  • 同一キー項目が複数行ある時は、H列にはそれぞれの行の値が積み重ねられて行きます。

この数式をH2セルをコピーして、H3:H386のセル範囲に貼り付けると下図のようになります。

③単独の場合はそのまま、複数行ある場合は最後の行だけを取り出す

複数行ある場合に最後の行を取り出すためには、「印」列に「1」が入っている一つ前の行を取り出せば良いはずです。

これをカレント行(現在の行)の視点で言い換えると『次の行の「印」列に「1」が入っている場合は取り出す』になります。直感的に分かり易いのは「一つ前の行」ですが、実際に数式化するのはこちらの視点になりますのでご注意ください。

ただこの場合に気を付けなければならないのは「一番最後の行の次の行に何か入っているか?」です。欄外のデータを気にするというのは意に添わないかもしれませんが最後まで正しい結果を得るためには必要な事です。

今回は「一番最後の行の次の行」は空文字であるとして、『「1」または空文字の場合は取り出す』という条件で実装します。

なお単独の時も「印」列には「1」が続けて入る事になるので、複数行と同様に一つ前の行を取り出せば良い事になります。

そこでもう1列追加して今回はI1セルに「品目TOP20」と入力し、I2セルに次のIF関数をセットします。

=IF(OR(G3=1,G3=""),H2,"")

• OR関数の構文は「OR(A,B)」で「A or B」を表します。
  • 次の行の「印」が「1」または空文字の場合はカレント行(現在の行)の途中経過を返します。
• 取り出さない場合は空文字(“”)にしています。

この数式をI2セルをコピーして、I3:I386のセル範囲に貼り付けると下図のようになります。

最終的な形に整形する

最終的な結果だけをまとめて取り出すには、最終結果にフィルタ機能を設定して「空白」を対象外にしてからセル範囲でA列、B列、I列を列コピーして別ワークシート(下図では「市町村別品目TOP20」)に値貼り付けをしてください。

なお「都道府県」及び「市町村」がこのままでは五十音順での並びになってしまうので、「都道府県コード」及び「市町村コード」の並びにするためには「都道府県＋市町村」と「都道府県コード＋市町村コード」の変換表を作成する必要があります。

「都道府県＋市町村」と「都道府県コード＋市町村コード」の変換テーブル

おおもとの「全市町村」シートのA列には「都道府県」、B列には「都道府県コード」、C列には「市町村」、D列には「市町村コード」がセットされています。

そこでF列とG列に次の数式を入力します。

=全市町村!A2&全市町村!C2

=全市町村!B2&全市町村!D2

それぞれ「全市町村」ワークシートのデータの終わり(この例では1720行)まで数式をコピー貼り付けします。

これで変換テーブルはできたので、D1セルに「コード」と入力して、D2セルに次の数式を入力し、D3:D240のセル範囲にコピー貼り付けします。

=VLOOKUP(A2&B2,$F$2:$G$1720,2,FALSE)

• A2セルとB2セルを結合した値が、変換テーブル($F2$2:$G$1720)にあるか?をVLOOKUP関数で完全一致で調べて存在する時は変換テーブル2列目のコードを返します。

後はA1:D240のセル範囲を選択して、コード・昇順に並べ替えをすれば完成ですが、「並び替えの前に」のメッセージがでますのでそのままOKボタンを押してください。

最後に

今回は「同一キー項目が複数行ある文字列をとりまとめる例」でご紹介しましたが、この取りまとめの方法を応用すると例えば今回のTOP20シートで同一「市町村」に連番を付番する事ができます。

下図ではB列に一行挿入をして、数式をセットしています。そのために「印」列がH列になっていますのでお含み置きください。

=IF(H2=1,1,IF(ISNUMBER(B1),B1,0)+1)

• ISNUMBER関数で数値である事を確認して、数値でなければ「0」を返しています。これはB列の先頭はタイトル行なので、そのために必要なロジックになります。

なお今回は先頭にタイトル行がある場合の数式をご紹介しましたが、タイトル行が無い場合は最初の行を「一つ前の行」で表す事ができません。

「他の行と同じ数式では実装できない」と言うかすべてを同じ数式で実装するためにはINDIRECT関数・ADDRESS関数・ROW関数・COLUMN関数などを駆使する必要があり複雑化しますので、今回は「タイトル行あり」でご説明いたしました。(「タイトル行が無い」場合の数式は別内容のテーマでご紹介したいと思います。)

以上最後までご一読いただき誠にありがとうございました。