Excelで文字列データを基準リストに基づき並び替えるための2つの方法

ExcelのTips
この記事は約15分で読めます。

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

今回はExcelでバラバラの文字列データのグループを決められた並び順で一覧表形式で並び替えるための方法をご紹介いたします。

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

基準リスト
洋書
パソコン・周辺機器
楽器・音響機器
大型家電
家電&カメラ
服&ファッション小物
グループA
楽器・音響機器
洋書
家電&カメラ

左側(←)の「グループA」を「基準リスト」に基づき右側(→)の形に並び変える。

グループA
洋書
 
楽器・音響機器
 
家電&カメラ
 

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

なおマクロ(VBA)は使わないで対応する事にしますが、MATCH関数やIF関数の他にピボットテーブルや重複削除の機能なども利用いたします。

Excelバージョン2110(ビルド 14527.20276)を使用しています。

スポンサーリンク

方法1:ピボットテーブルを使って集計した結果を文字列に置き換える

まずご説明で使用するサンプルとしてのExcelファイルをご紹介いたします。

下記ExcelシートはMicrosoftサポートの「OneDrive から Web ページやブログに Excel ブックを埋め込む」に基づきOneDrive「https://onedrive.live.com」にアクセスして表示しています。

※iframeを使用していますので、iframeがご利用になれない環境の場合は、別途ファイルをダウンロードしてご確認ください。

基準リストの項目数は21項目で、グルーブは4つになります。シート名は「Data」です。

このくらいのデータ量であれば時間を掛ければ全て手作業でも対応できる事と存じますが、説明の都合上データ量を少なくしていますのでお含み置きいただければ幸いです。

ピボットテーブルで処理するためにすべてのグループの項目を列に並べる

並べるのは同じシートの別の場所でも別シートでも大丈夫ですが、並べる「項目の列」の右隣りに「グループ名の列」と「並び順の列」の2つの列を作る必要がありますので合計で3列の空きスペースが必要になりますのでご注意ください。

ここでは元となる「Data」シートとは別に「LineUp」シートを作っています。

「項目の列」と「グループ名の列」での作業

①DataシートのグループAの項目すべてをセル範囲でコピーしてLineUpシートのA1セルに値貼り付けします。
 
 
 
 
 

②LineシートのB列にグループ名をセットするのですが、実際のグループ名ではなく、数字を連番で附番します。
この理由はピボットテーブルではグループ名でソートされるので、実際のグループ名では並び順が変わってしまうためです。
この時、B1セルに数字「1」を入力した後、B1セル右下にカーソルを当てるとポインターが「+」に変わるので、オートフィル機能を使って数字を入力します。
 
 

③ポインターが「+」に変わたところでダブルクリックすると、A列の項目数に合わせてB列に数字「1」が自動的にセットされます。
 
 
 
 
 

④同様にグループBからDまで処理をします。

その後最後にDataシートの基準リストの項目をすべてセル範囲でコピーしてグループDの後に続けて値貼り付けします。

この時のグループ名は続けて連番を附番しても良いのですが、空白セルのままでも大丈夫です。ここでは空白セルのままにしています。

なお最後に基準リストの項目をすべて貼り付ける理由は、グループの中にすべての項目が存在していないはずなので、その項目が欠落する事を防ぐためです。

「並び順の列」での作業

次にLineUpシートのC列「並び順の列」にMATCH関数をセットします。

MATCH関数を使ってやりたい事は「項目の並び順の数値化」で、言い換えると次のようになります。

A列「項目の列」にセットされた項目が、基準リストの項目では上から何番目にあるのか?を数値化する

①LineUpシートのC1セルをクリックして、数式バーにMATCH関数を入力します。
 
最初のパラメータの検査値は LineUpシートの A1セルになります。
 
 
 
 

②2番目のパラメータの検査範囲は、DataシートのA2セルからA22のセル範囲になります。
 
Dataシートを選択してからセル範囲をドラッグで指定します。
 
 
 
 

③最後のパラメータ「照合の種類」は「0」の感染一致を選択します。
 
 
 
 
 
 
 

④以上でMATCH関数の入力は終わります。最後に左カッコを入力して改行キーで入力を確定させます。
 
左図ではC1セルの値は3になっていますが、「おもちゃ」の項目は基準リストでは上から3番目になるので正しく機能しています。

MATCH関数の入力は終わったのですが、このままではDataシートのA2セルからA22のセル範囲が相対参照のなっているために数式をコピーすると参照範囲が変わってしまいます。

そこで下記のようにセルのアドレスにそれぞれ「$」を付けて絶対参照に変更します。

=MATCH(A1,Data!$A$2:$A$22,0)

⑤絶対参照に修正した数式をオートフィル機能で入力されている項目の終わりまでコピーする事ができます。
先ほどと同じようにC1セルの右下でカーソルが「+」になる位置にセットしてダブルクリックします。
すると左図のように最後まで数式がコピーされます。

以上でピボットテーブルに渡すために必要なデータ加工は終わりになるのですが、ここでデータの内容に関して1点確認していただきたいポイントがあります。

それは「MATCH関数の結果が0になっている項目が存在していないか?」という点です。

と言うのも「MATCH関数の結果が0になっているという事は、グループに含まれている項目の中に基準リストに存在していないものがある」という事になります。

従ってMATCH関数の結果が0になった項目はエラー項目として何かしらの対処が必要になりますのでご注意ください。

並べられた項目一覧をピボットテーブルで集計する

ここからはピボットテーブルでの作業になります。

ピボットテーブルで扱うデータにはヘッダー行が設定されている必要があります。前章では各グループの項目をLineUpシートに値貼り付けする際にヘッダー行は設定していなかったので、ここで設定をします。

①まずは1行目に行を挿入してA1セルは「項目」、B1セルは「列順」、C1セルは「行順」と入力します。つぎにLineUpシートに入力されているすべてのセルを範囲指定します。
この時A1セルをクリックしてからショートカットキー「ctrlキー+shiftキー+endキー」を(同時に)押すと入力されているすべてのセルが範囲指定されます。
 
 

②「挿入」タブのテーブルリボングループにあるピボットテーブルのアイコンをクリックします。
 
 
 
 

③ピボットテーブルの作成ウインドウが開くので、ここでは新規ワークシートが選択されている事を確認してOKボタンをクリックします。
 
 
 
 
 
 

④Sheet1シートが追加されてピボットテーブルの初期画面が表示されます。
ここでまず最初にピボットテーブルのフィールドに表示されている「行順」を「行のボックス」にドラッグします。
 
 
 
 

⑤ワークシート上に行順に設定されている値が「ソートされて」かつ「重複削除」されて表示されます。
 
 
 
 
 
 

⑥同じ操作で「項目」を「行ボックス」にドラッグします。
これによりワークシート上に項目の値が表示されます。
 
 
 
 
 

⑦次に「列順」を「列ボックス」にドラッグします。
これによりワークシート上に列順の値がヘッダー行の中に表示されます。
この時、基準リストの項目は列順に何も入力していないので、ヘッダー列には「(空白)」と表示されます。
 
 
 
 

⑧最後に「項目」を「値ボックス」にドラッグします。
「項目」は「行ボックス」にもセットしていますが、再度「値ボックス」にもセットします。
値ボックスに普通は数値情報をセットするのですが、ここではあえて文字列情報をセットしています。
そのために値ボックスには「個数/項目」、つまり項目の値ではなく項目の数をピボットテーブルに表示する設定に自動的になります。

以上で一通りのピボットテーブルの設定は終わりになりますが、デフォルトのピボットテーブルの表示形式では下記の2点が問題になります。

  1. 「行順」と「項目」が一行に並んでいない。
  2. 小計が表示されている。

そこで続けてこの問題を解消するための設定をしますが、実はこの2つはピボットテーブルのレイアウトを変更するだけで済みます。

⑨ピボットテーブルにフォーカスが当たっている状態でタブメニューの「デザイン」→レイアウトグループの「レポートのレイアウト」→「表形式で表示」をクリックします。
 
 
 
 

⑩それぞれの項目が1行で表示されるようになります。
合わせてヘッダー行のラベルの表示がLineUpシートに入力した項目名に変わっています。

以上でピボットテーブルの設定は終了になるので、ピボットテーブルのフィールドの閉じるボタンをクリックします。

ここでまたまたデータの内容に関して確認していただきたいポイントがあります。

それは「列順に表示されている個数/項目の数値が1を超えるものがあるか?」という点です。

と言うのも「1を超えるという事は当該グループの中に同じ項目が2つ以上存在している」からです。

単純にダブっているのであればそのままでも良いかもしれませんが、別な項目であった可能性が無きにしも非ずなので確認が必要です。

ピボットテーブルを元に決められた並び順で一覧表形式に並び替えた文字列に変換する

いよいよ最後の仕上げになりますが、ピボットテーブルに表示されている個数/項目の数値を1ではなく項目名に変換すればすべての作業は完了します。

そのためにピボットテーブルの枠外のI5セルに次のIF文を入力します。なぜ5行目なのか?と言うと、それはピボットテーブルで行順の最初の項目が今回の場合は5行目から始まるためです。

それとともに、IF文の条件式に当てはまる場合に項目名を返すのですが、その時のセル参照のB列は絶対参照にします。
そうしないと行順の各列に数式をコピーした時に参照先の列が動いてしまうためです。

=IF(C5>=1,$B5,"")

この数式を入力する際に注意していただきたいのは、ピボットテーブル内の個数/項目の数値の範囲を参照するためにクリックするとGETPIVOTDATA関数が貼り付いてしまう事です。

従って上記数式で「C5」のセルの相対参照のアドレスは手入力する必要がありますのでご注意ください。

またIF文の条件式を「C5=1」ではなく「C5>=1」にしている理由は、前章の最後でご説明したようにグループの中に同じ項目が2つ以上存在している場合に個数/項目の数値は1ではなく2以上になるためです。

後はI5セルをコピーしてから、I5:L25のセル範囲を選択して「貼り付け」を実行します。それにより対象のセル範囲に数式がセットされます。

以上でグループを決められた並び順で一覧表形式で並び替えるためのデータ作成作業は終了となります。

最後にI5:L25のセル範囲をコピーしてから、DataシートのB2セルを選択して「値貼り付け」をすれば冒頭でご説明した並び替えは完了します。

方法2:ソートと重複削除を使って文字列を並び替える

方法1ではピボットテーブルを使いましたが、今回はピボットテーブルを使わずに並び替える方法になります。

方法2は手数としては多くなるのですが、ある意味オーソドックスなやり方であり、ピボットテーブルの結果を文字列に置き換えるような奇抜な手法は使っていないので解り易い方法である事と存じます。

ソートと重複削除の操作対象となるデータの作成

方法2では大きく分けると、ソートと重複削除をするための「データを作成するステップ」と実際にソートと重複削除を「実施するステップ」の2つになります。

まずはデータを作成するステップをご説明いたします。

※このステップの中でMATCH関数を方法1とまったく同じ数式で使用します。

①各グループのデータを加工するために方法1と同じくLineUpシートを追加した上で、Dataシートの各グループのデータをすべて範囲指定してコピーをします。
 
 
 
 
 

②コピーしたデータをLineUpシートのA1セルを選んで値貼り付けをします。
 
方法1では一列に縦方向にグループデータを並べたのですが、今回はそのまま横方向に並べて加工して行きます。
 
 
 

③貼り付けられたデータ列の間に1列づつ列を挿入します。
 
 
 
 
 
 
 

④B1セルに方法1の時とまったく同じMATCH関数を入力します。目的も同じです。

=MATCH(A1,Data!$A$2:$A$22,0)

 
 
 
 

⑤B1セルに入力した数式をオートフィル機能でA列に入力されている項目の終わりまでコピーします。
 
 
 
 
 
 
 
⑥B1セルをコピーしてD1セルに貼り付けてオートフィル機能でコピーします。
この時、前列よりも項目の数が少ないと数式が「#N/A」のエラーになりますがそのままにして大丈夫です。
 
 
 
 

⑦同様にF1セル、H1セルにそれぞれ貼り付けてオートフィル機能でコピーします。
 
この時方法1と同様に、MATCH関数の結果が0になった項目はエラー項目として何かしらの対処が必要になります。
 
次に、項目の終わりまでコピーした数式の最後のセルのすぐ下のセルに数字の1を入力します。

⑧他の列も同様に入力しますが、項目の終わりの次の数式が「#N/A」のエラーになっていても、かわまず上書きして1を入力してください。
すべての列に入力が終わったらB12セルをクリックしてホームタグメニュー→編集グループのフィルリボンアイコンから「連続データの作成」をクリックします。

ここで各項目の最後のセルのすぐ下に1を入力する目的は、グループの中にすべての項目が存在していないはずなので、その足りない項目を補填するためです。

なお方法1の時は基準リストの項目をすべて最後に追加しましたが、今回は項目を追加する分けにはいかないので1から21の行順を追加します。

この時1から21の数字を順番に縦方向にセットする必要がありますが、1つづつ入力するのも大変なので「連続データの作成」機能を利用します。

⑨連続データのウインドウが開くので、範囲を「列」に指定して、停止値に「21」と入力します。
 
それ以外はデフォルトのままで大丈夫です。
 
終わりましたらOKボタンをクリックします。
 
 

⑩するとB12セルからB32セルまで1から21までの連続データがセットされます。
同様の操作をC10セル、F6セル、H9セルでも実施するのですが、Excelには直前の操作を繰り返す機能があります。
実は今回の連続データのセットは繰り返し操作の対象になりますので、対象セルをクリックして「F4キー」 or 「Ctrlキーを押しながらY」を押すと連続データがセットされます。
※なお繰り返し機能は対象となる操作が限定されているので実際に動作確認するのが手っ取り早いです。

⑪最後に連続データをセットした左隣のセルに半角スペースをすべてセットします。
A12セル、C10セル、E6セル、G9セルにそれぞれ半角スペースを入力した後、オートフィル機能ですべてのセルにコピーします。

なぜ半角スペースをセットするのか?

次のステップでデータを並べ替えするのですが、(空文字のセルは一見項目の文字列よりも小さく思えるのですが)実はExcelの並べ替えは空文字のセルは処理対象にしません。

このことについてMicrosoft Docsでは何も言及していないようなのですが、実際に空白セルを対象に並べ替えを実施すると、昇順を指定しても降順を指定しても結果は変わりません。なお今回の場合は恐らく出現順に並び替えられているものと思われます。

そのため半角スペースを入力してもしなくても(今回の場合は)得られる結果は同じになるかもしれませんが、並び替えの対象になるように空白セルには半角スペースをセットすることにいたします。

ソートと重複削除の操作を実施する

前章まででソートと重複削除の操作に必要なデータの準備は完了したので、次のステップに進みます。

①まず最初のグループをセル範囲(A1:B32)で選択します。
 
続けてデータリボンタブ→並び替えとフィルターグループから並べ替えリボンアイコンをクリックします。
 
なおC列のMATCH関数が返す値は全体がソートされるのであればA列の値に従って計算されているので問題はありません。
 
②並べ替えウインドウで「先頭行をデータの見出しとして使用する」のチェックは外し、優先されるキーのリストボックスから列B(行順)を選び、順序は小さい順にします。

③レベルの追加ボタンをクリックして、次に優先されるキーの行を表示させます。
 

④次に優先されるキーで列Aを選択し、順序は降順を選び、OKボタンをクリックします。
 
 

⑤選択したセル範囲が、行順が同じ値の時に「項目」が先で、半角スペースが後にしている事を確認します。
更に「項目」が2行以上続けて表示されていない事を確認します。
方法1の時はピボットテーブルの値エリアが1を超える値で確認しましたが、今回は目視確認する事になります。確認する理由は方法1と同じです。

選択したセル範囲がそのままの状態でデータリボンタブ→データツールグループの中の「重複の削除」リボンアイコンをクリックします。

⑥重複の削除ウインドウで列Aのチェックを外して、OKボタンをクリックします。
 
 
 

⑦左図のようなメッセージが表示されますので確認してOKボタンをクリックします。

⑧選択したセル範囲で行順が一意になっていて項目が表示されてい事を確認します。
 
 
 
 
 
 

⑨残りのグループも同様に操作をします。

後は項目列だけを残してコピーをしてDataシートのB2セルを選択して「値貼り付け」をすれば冒頭でご説明した並び替えは完了します。

まとめ

今回はExcelでバラバラの文字列データのグループを決められた並び順で一覧表形式で並び替えるために、方法1:ピボットテーブルを使用する場合と、方法2:ソートと重複削除を使用する場合に分けてご説明いたしました。

方法1はトリッキーなやり方をしているところがありますが、各グループを列にまとめる作業が終わればあまり人手は使わないで処理できる事と存じますので、ある程度データ量が増えたとしても何とか対応できるのでは無いでしょうか?

方法2につきましては、Excelの並び替えで空白セルが処理対象にならない事象をご紹介しましたが、重複削除の処理に関しては下記Miccrosoft サポートのページにある「重複する値を削除する」のプルダウンに「重複が削除されると、リスト内の値の最初の出現は保持されますが、他の同じ値は削除されます。」という記述がありますので、正しくソートされていれば最初に出現する値が保持される認識です。

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

ExcelのTips
スポンサーリンク
スポンサーリンク
シェアする
∞ワークスKenをフォローする
∞ワークス