構造化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」の場合と同様、［区切り位置指定ウィザード］を使ってデータを切り分ければよい。
［区切り位置指定ウィザード］を使ってデータを分割する
複数のデータが入ったセルを選択し、［データ］タブの［データツール］グループの［区切り位置］をクリックする。
［区切り位置指定ウィザード］が開くので、「データのファイル形式を選択してください」で「コンマやタブなど……」を選択する。
次の画面では、区切り文字を指定する。ここでは、「その他」を選択して入力欄に「、」を指定する。「データのプレビュー」で区切られた状態を確認して、問題がないのであれば、［次へ］ボタンをクリックする。
次の画面では、「列のデータ形式」として「標準」を選択し、［完了］ボタンをクリックする。これで、1つのセルに「＜データ＞（平成XX年度）」というデータが入った状態となる。
［区切り位置指定ウィザード］を使ってデータを複数のセルに分割する（3）
区切り文字を選択する。この例の場合は、「その他」だけにチェックを入れ、区切り文字として「、」を指定する。プレビューで確認できるので、思った通りにデータが分離しているか確認する。
［区切り位置指定ウィザード］を使ってデータを複数のセルに分割する（4）
通常は、「G/標準」を選んでおけばよい。データ形式が適切でない列があった場合は、その列を選択し、「列のデータ形式」を変更すればよい（日付が入ったセルがある場合は、「日付」を選択するなど）。
和暦の年度を入力する
最終形の表は、年度ごとに仕入額と出荷額が並んだものになるので、「全国」という項目目が入っている行に、年度を入力する。この際、年度を文字列で入れてもいいが、数が多い場合は面倒だ。
このような場合、Excelの「クイック分析」機能を使うと楽に入力できる。最初の列のセルに「2015/4/1」といった西暦を入力し、次の列のセルに「2016/4/1」を入れて、この2つのセルを選択する。すると、セルの右下隅に「クイック分析」というアイコンが表示されるので、セルの右下隅にカーソルを重ね（マウスポインターが太字の＋字に変わる）、年度を入力したい列までドラッグする。これで西暦が自動入力できる。「#####」という表示になっている場合は、セル幅が狭いだけなので、セル幅を広げればいい。
次に西暦表示を和暦表示に変更しよう。西暦が入っているセルを全て選択し、［Ctrl］＋［1］キーを押し、［セルの書式設定］ダイアログを開き、［表示形式］タブの「分類」で［日付］を選択し、「カレンダーの種類」で［和暦］を選択する。さらに、「種類」で「平成24年3月14日」を選択すると、西暦表示から和暦表示に切り替わる。
ただし、これでは年度表示にはならないので、引き続き「分類」で［ユーザー定義］を選択し、種類を「[$-ja-JP-x-gannen]ggge"年度"」に編集して、［OK］ボタンをクリックする。これで、西暦が和暦の年度表示になる。
和暦の年度を入力する（1）
西暦を入力する。ここでは年度なので、「2015/4/1」などとしておく。隣のセルに「2016/4/1」を入力し、2つのセルを選択する。「2016/4/1」を入力したセルの右下隅にマウスポインターを重ねるとポインターが＋字になるので、西暦を入力したいセルまでそのままドラッグする。
和暦の年度を入力する（3）
西暦が入力されているセルを選択し、［セルの書式設定］ダイアログを開く。［表示形式］タブを開き、「分類」で［日付］を選択し、「カレンダーの種類」で［和暦］を選択する。さらに、「種類」で「平成24年3月14日」を選択すると、西暦表示から和暦表示に切り替わる。
余計な文字列を削除する
まだセルには、「（）」で囲まれた和暦の年度表示が残っているので、これを取り除こう。データの入った行を選択し、［ホーム］タブの［編集］グループの［検索と選択］をクリックし、［置換］を選択する。［検索と置換］ダイアログが開くので、「検索する文字列」に「（平成??年度）」を入力し、「置換後の文字列」には何も入力せずに、［すべて置換］ボタンをクリックする。
昭和や令和などの年号が含まれている場合は、「検索する文字列」に「（昭和??年度）」「（令和?年度）」と指定すればよい。これで、データのセルに入っていた和暦の年度表示が全て削除され、切り分けたデータが数値のみになる。
余計な文字列を削除する（1）
分離したデータには、余分な年度表示が残っているので、置換を使ってこれを削除する。余計な文字が入っているセルを選択し、［ホーム］タブの［検索と選択］−［置換］を選択する。［検索と置換］ダイアログが表示されるので、「検索する文字列」に「平成??年度」を入力し、「置換後の文字列」には何も入力せずに、［すべて置換］ボタンをクリックする。
行と列を入れ替えて体裁を整える
年度が列方向に並ぶ表にしたいので、表全体を選択し、［Ctrl］＋［C］キーでコピーし、別のシートなどを開き、貼り付けたいセルを右クリックし、［形式を選択して貼り付け］−［形式を選択して貼り付け］を選択する。［形式を選択して貼り付け］ダイアログが開くので、「行/列の入れ替え」にチェックを入れて、［OK］ボタンをクリックすると、行と列を入れ替えて貼り付けできる。後は、表の体裁を整えればよい。
行と列を入れ替えて体裁を整える（1）
表の行と列を入れ替えるには、表全体を選択して、［Ctrl］＋［C］キーでコピーする。新しいシートを開き、貼り付けたいセルを右クリックし、［形式を選択して貼り付け］−［形式を選択して貼り付け］を選択する。
［区切り位置指定ウィザード］を利用すれば、このように複数のデータが入ったセルであっても、1セル1データに切り分けることができる。ポイントは、データが入っているセルをなるべく手動で操作しないことだ。セル内を手動で編集すると、誤ってデータの一部を削除してしまう、といった操作ミスを起こしがちである。
セルに入っているデータによっては、事前に文字を置換によって統一したり、区切り文字を工夫したりする必要もある。このあたりは各自工夫してほしい。また、データを分割した後の操作手順は、入っているデータに依存するので、上記の手順はあくまで参考としてほしい。
Copyright© Digital Advantage Corp. All Rights Reserved.