構造化Excelテク：複数のデータが含まれたセルを1セル1データに分割する：Tech TIPS
Excelの表で、1つのセルに複数のデータが含まれていると、計算や並べ替えが行えないなど、集計や再利用に不具合が生じる。新規に作成する場合は、1セル1データにするように注意すればよい。しかし、既に作成してしまった1つのセルに複数のデータが入った表を活用する場合、1セル1データに分割する必要がある。その方法を紹介しよう。
対象：Excel 2016／2019／365
セルに複数のデータが含まれていると集計などが行えない
1つのセルに複数のデータが含まれた表は、集計や並べ替えなどが行えず、再利用などが難しい。そこで、1つのセルに複数のデータが含まれた表を、1セル1データに変換しよう。
「Microsoft Excel（エクセル）」で作成した表の中には、1つのセルに複数のデータを入れているものがある。こうした表は見やすい場合もあるが、セル内の値が文字列となってしまい、計算や並べ替えなどが行えないなど、活用する際に余計な工数がかかることになる。
総務省統計局が公開した各府省が政府統計の総合窓口（e-Stat）に掲載する統計表の表記方法の統一ルール「統計表における機械判読可能なデータの表記方法の統一ルールの策定」の2ページ目「チェック項目1-2 1セル1データとなっているか」でも、「1セルに複数のデータが入力されていると、計算や昇順・降順の並べ替え、コピー＆ペーストやグラフ化などの加工編集する場合に多くの手作業やプログラムの作成が必要となり、すぐにデータとして利用できない」として、1つのセルに1つのデータを入れること、としている。
もちろん新しく作成する表は、そのような方針で作っていけばいい。しかし、そうした表に追記して更新しているような場合や、相手先からこうしたExcelの表が送られてきてしまった場合には、1セル1データに作り直す必要がある。この作業を手動で行うのは面倒だし、操作ミスにより値が変わってしまうこともある。
そこで本Tech TIPSでは、1つのセルに複数のデータが含まれた表を、1セル1データにする方法を紹介しよう。「統計表における機械判読可能なデータの表記方法の統一ルールの策定」に掲載されている例を使って、手順を解説していく。
■関連記事
- Tech TIPS「構造化Excelテク：セルの結合を使わずに複数セルの中央に文字列を表示する」
- Tech TIPS「Excel定型業務自動化への第一歩 Power Queryでリスト→◯×表（マトリックス表）変換」
- Tech TIPS「【Excel】元表の変更を別表にも自動反映するなら、コピペじゃなくて『データの取得と変換』」
1つのセルに2つのデータが含まれている場合
1つのセルに複数のデータが含まれている場合、「、（てん）」や「，（カンマ）」「（）（かっこ）」などでデータが区切られていることが多いはずだ。これらの記号を使って1つのセルに含まれている複数のデータを切り分けることができる。
「統計表における機械判読可能なデータの表記方法の統一ルールの策定」の「チェック項目1-2 1セル1データとなっているか」の「例1」は、少し手順が複雑なので、単純な「例2」からデータを切り分ける方法を解説しよう。
［区切り位置指定ウィザード］を使ってデータを分割する
複数のデータが入っている列の右側にも数値が入っているようならば、入っているデータの数だけ列を挿入しておく。例えば、1つのセルに2つのデータが入っているようならば、切り分けたデータが入る列を1つ挿入しておけばよい。
列を挿入したところで、複数のデータが入っているセルを選択し、［データ］タブの［データツール］グループの［区切り位置］をクリックする。
［区切り位置指定ウィザード］が開くので、「データのファイル形式を選択してください」で「コンマやタブなど……」を選択する（固定長のデータの場合は、「スペースによって……」の方を選択する）。
次の画面では、区切り文字を指定する。今回の場合は、ここで「スペース」を選んで、「連続した区切り文字は……」にチェックを入れてもよいが、「その他」を選択して入力欄に「(」を指定しておく（後で余分な文字列を削除する手間が1つ省ける）。「データのプレビュー」で区切られた状態が確認できるので、その後の処理が行いやすい区切り文字を試行錯誤で選択するとよい。
次の画面では、列のデータ形式や表示先（出力先）を選択し、［完了］ボタンをクリックする。これで、「（）」で示されていた「費用総額」は隣の列に切り分けられた。
［区切り位置指定ウィザード］を使ってデータを分割する（3）
区切り文字を選択する。この例の場合は、「その他」だけにチェックを入れ、区切り文字として「(」を指定する。すぐに「データのプレビュー」に反映されるので、思った通りにデータが分離しているか確認する。
［区切り位置指定ウィザード］を使ってデータを分割する（4）
通常は、「G/標準」を選んでおけばよい。データ形式が適切でない列があった場合は、その列を選択し、「列のデータ形式」を変更すればよい（日付が入ったセルがある場合は、「日付」を選択するなど）。
余分な文字を削除して体裁を整える
ただ、切り分けたデータには、「)」が残っているため、数値となっていない。そこで、切り分けたデータ列を選択し、［ホーム］タブの［編集］グループの［検索と選択］をクリックし、［置換］を選択する（［Ctrl］＋［H］キーを押してもよい）。［検索と置換］ダイアログが開くので、「検索する文字列」に「)」を入力し、「置換後の文字列」には何も入力せずに、［すべて置換］ボタンをクリックする。これで、「)」が削除され、切り分けたデータが数値のみになる。後は、表の項目名などの体裁を整えればよい。
余分な文字を削除して体裁を整える（1）
分離したデータには、余分な「)」が残っているので、置換を使ってこれを削除する。余計な文字が入っているセルを選択し、［ホーム］タブの［検索と選択］−［置換］を選択する。［検索と置換］ダイアログが表示されるので、「検索する文字列」に「)」を入力し、「置換後の文字列」には何も入力せずに、［すべて置換］ボタンをクリックする。
1つのセルに複数のデータが含まれている場合
「統計表における機械判読可能なデータの表記方法の統一ルールの策定」の「チェック項目1-2 1セル1データとなっているか」の例1は、3つ以上のデータが1つのセルに詰め込まれているため、少し手順が複雑となる。ただ基本的には、「例2」の場合と同様、［区切り位置指定ウィザード］を使ってデータを切り分ければよい。
［区切り位置指定ウィザード］を使ってデータを分割する
複数のデータが入ったセルを選択し、［データ］タブの［データツール］グループの［区切り位置］をクリックする。
［区切り位置指定ウィザード］が開くので、「データのファイル形式を選択してください」で「コンマやタブなど……」を選択する。
Copyright© Digital Advantage Corp. All Rights Reserved.