検索
連載

構造化Excelテク:目視前提で省略された項目名を補完して、表を構造化データにするTech TIPS

Excelで作成された表の中には、項目名の一部が省略されており、機械判読をしようとすると前処理が必要になるものがある。こうした表は、並べ替えも正しく行えず、活用に支障をきたしてしまう。そこで、省略されている項目名を補完し、機械処理が可能な形式にしよう。その手順を紹介する。

PC用表示 関連情報
Share
Tweet
LINE
Hatena
「Tech TIPS」のインデックス

連載目次

対象:Excel 2013/2016/2019/2021/365


Excelの表で項目名が省略されていると機械処理が難しくなる
Excelの表で項目名が省略されていると機械処理が難しくなる
Excelで作成された表の中には、項目名の一部が省略されている場合がある。こうした表は、機械処理や並べ替えが難しく、データの活用に支障をきたしてしまう。そこで、省略されている項目名を補完し、活用可能な形式にしよう。

 「Microsoft Excel(エクセル)」で作成された表の中には、一部の項目名が省略されており、機械判読を行うとすると前処理が必要になるものがある。こうした表は、人間ならば、セルの前後や左右の関係から判読できても、機械で処理を行う際には補完が必要になり、自動化を難しくしてしまう。また、項目名の一部が省略されていると、並べ替えも正しく行えず、データの活用も困難になってしまう。

 総務省統計局が公開した各府省が政府統計の総合窓口(e-Stat)に掲載する統計表の表記方法の統一ルール「統計表における機械判読可能なデータの表記方法の統一ルールの策定」の11ページ「チェック項目1-6 項目名等を省略していないか」においても、「ヒトであれば省略されている部分の意味を判断できるが、ソフトウェアなどのプログラムでは判断ができない」としており、「項目名などを省略しないこと」としている。

 既に項目名などに省略された部分がある表は、機械処理が可能なように項目名を補完しておくとよい。逆に人間が見やすいように余計な文字列などが挿入されているような表を数値のみにする方法は、Tech TIPS「Excel構造化テク:単位などの文字列混入で泥沼化しているセルを数値のみにする」を参照してほしい。

省略された表の項目名を補完する

 下画面のように項目名が省略されていても、人間が見る場合は、セルの上下関係から省略されていることが分かる。これを機械でも自動処理できるように、省略された部分を補完し、各行の項目名が省略のない状態にしよう。

 それには、表の一番右側を作業用の列として使い、この列に「="<補完したい文字列>"&<データの始まるセル位置>」を入力する。例えば、画面の例では、補完したい文字列は「鎮静剤A-」なので、省略されているセルの先頭行である「D3」セルに「="鎮静剤A-"&A3」を入力する。

 入力した後、「D3」セルの右下部分にマウスポインターを重ねポインターが太い「+」になったら下側に表の最後までドラッグする。マウスを離すと、D列に補完した状態の項目名が並ぶ。

省略された表の項目名を補完する(1)
省略された表の項目名を補完する(1)
一部が省略されている表の項目名を補完しよう。
省略された表の項目名を補完する(2)
省略された表の項目名を補完する(2)
「D3」セルに「="鎮痛剤"&A3」と入力する。
省略された表の項目名を補完する(3)
省略された表の項目名を補完する(3)
「D3」セルを選択し、右下にマウスポインターを重ねるとポインターが[+]になるので、これをコピーしたいセル位置までドラッグする。
省略された表の項目名を補完する(4)
省略された表の項目名を補完する(4)
「D3」セルの数式がコピーされる。これで項目名が補完できる。

 ただし、この項目名は数式になっているので、このままA列にコピーしてしまうと、エラーになってしまう(参照しているセルを上書きしようとするため)。

単純にセル範囲のコピーではエラーになる
単純にセル範囲のコピーではエラーになる
補完した項目名を単純にコピーすると、参照先が上書きされてしまうためエラー(#REF!)になる。

 そこで、D列を選択したら、A列にコピーする際に「貼り付けのオプション」で[数値]を選択する。セルの書式設定を維持したい場合は、[形式を選択して貼り付け]を選択し、表示された[形式を選択して貼り付け]ダイアログで「値と数値の書式」を選んで、[OK]ボタンをクリックすればよい。これでA列に補完された項目名が入力できる。不要になったD列は、列全体を選択して、削除すればよい。

セル範囲を数値としてコピーする(1)
セル範囲を数値としてコピーする(1)
作成した項目名のセル範囲をコピーし、貼り付け先のセルの右クリックメニューで「貼り付けのオプション」で[数値]を選択する。
セル範囲を数値としてコピーする(2)
セル範囲を数値としてコピーする(2)
これで、A列に補完した項目名が文字列として貼り付けられる。不要になったD列は削除すればよい。
セル範囲を数値としてコピーする(3)
セル範囲を数値としてコピーする(3)
これで各行の項目名が完全な形になる。

表示や印刷時のみに文字列を表示したい場合

 下画面のような表の場合、項目名はデータ上「A-1」「A-2」としておき、表示や印刷時は、人間がひと目で分かりやすいように「鎮静剤A-1」「鎮静剤A-2」としておきたいということもあるだろう。

 このような場合、セルの書式設定の表示形式を使い、項目名を変更せず、表示や印刷時のみ「鎮静剤A-1」などになるようにするとよい。それには、項目名のセル範囲を選択し、右クリックメニューの[セルの書式設定]を選択するか、[Ctrl]+[1]キーを押して、[セルの書式設定]ダイアログを開く。[セルの書式設定]ダイアログの[表示形式]タブを開き、「分類」で[ユーザー定義]を選択し、右側の「種類」で「"鎮静剤"@」とすればよい。ただし、項目名が「1」「2」などのように数値の場合は、「"鎮静剤A-"#」とすること。どのような表示になるかは、「サンプル」欄に示されるので、ここを見て、思った通りになっているかを確認するとよい。

 これで、セル内の値は、「A-1」などとなっているものの、表示や印刷の際は「鎮静剤A-1」となる。

表示や印刷時のみ完全な項目名が表示されるようにする(1)
表示や印刷時のみ完全な項目名が表示されるようにする(1)
項目名のセル範囲を選択し、[Ctrl]+[1]キーなどで[セルの書式設定]ダイアログを開く。[セルの書式設定]ダイアログの[表示形式]タブを開き、「分類」の[ユーザー定義]を選択する。
表示や印刷時のみ完全な項目名が表示されるようにする(2)
表示や印刷時のみ完全な項目名が表示されるようにする(2)
「種類」に「"鎮静剤"@」と入力する。「サンプル」欄に表示状態が示されるので、ここで確認するとよい。
表示や印刷時のみ完全な項目名が表示されるようにする(3)
表示や印刷時のみ完全な項目名が表示されるようにする(3)
表示や印刷時のみ項目名が完全な形になる。ただし、データ自体は「A-1」などのままとなっている。

Copyright© Digital Advantage Corp. All Rights Reserved.

ページトップに戻る