【Excel】結合データをデータテーブルに変換する！

Excelのデータを扱っていると、リテラシーの低い人が作成したデータでセルの結合が多用されているせいで、データテーブルとして使うことができず、困ることが多々あります。

今回はExcelの新関数であるWRAPROWS関数やTOROW関数を使ってを使って結合データをデータテーブルに変換する方法を紹介します。

今回は以下のデータを使用します。

セルの結合の問題点

Excelでセルを結合すると、確かに便利で見栄えが良くなることもありますが、データテーブルとしての使用にはいくつかの問題点があります。特にデータの分析や処理においては、セルの結合がさまざまな問題を引き起こします。

Excelは表計算ソフトであり、行と列に均一にデータが並んでいることで初めて十分に機能を活かすことができます。しかし結合されたセルは、データを均一な行と列のグリッドに配置するのを困難にします。具体的には関数やフィルター機能がうまく使えなかったり、コピーペーストの参照範囲がずれてしまったりします。

稀に結合が本当に必要なケースもありますが、そのほとんどはデータを使いにくくしてしまう、悪名高い機能と言えます。できるだけ、使わないようにしましょう。

データテーブルとは、情報を行と列の形式で整理したものです。これは、データベースのテーブルやExcelのスプレッドシートのような表形式のデータ構造を指します。

一般に、データテーブルの行と列には以下のようなデータが存在します。

行（Rows）: 表の各行は通常、個々のレコードまたはデータポイントを表します。例えば、顧客データベースの場合、各行は一人の顧客に関する情報を持ちます。

列（Columns）: 各列は特定の種類の情報[カテゴリー、要素など]を表し、全ての行に対して一貫したデータを含みます。例えば、顧客データベースでは、列には「名前」、「住所」、「電話番号」などが含まれることがあります。

そして、データテーブルになっていれば、Excelの機能で言えばピボットテーブルやグラフ化など、可視化や分析を実行することができるのです。

それでは、今回のデータをデータテーブルに変換するとは、具体的にはどういうことでしょうか。まずは元データを再掲します。

これを、以下のように変換します。

列方向に、カテゴリーが並び、行方向には各カテゴリーの情報が並んでいることがわかります。

では上の変換をどのように実行するかを解説します。

まずは、縦に繋がってしまっている品番と品名を横方向に並べます。ここではTOROW関数を使い、その後にWRAPROWS関数を使っています。

それぞれの関数の細かな使い方は別の記事で解説しますが、ここでも簡単に説明します。

TOROW関数は文字通り、あるデータの塊を1行に変換する関数です。元データのC列に対してTOROWを実行すると、以下のようになります。

WRAPROWS関数は、ある1行を、 X列の配列に変換します。X列の配列に変換というのは、今回のデータであれば以下のようなイメージです。(1列～4列のパターンの例)

今回の例で言えば、2列を指定することで、うまいこと品番と品名を分離することができます。

続いて、結合されている部分の処理です。ここでも①同様にTOROW関数とWRAPROWS関数を使い、更に加えてTAKE関数も使っています。

結合セルの値をそのまま使おうとすると、以下のように結合されているセルの値が”0″と出てきてしまい、データとしてうまく扱えません。

そこで、以下のようにTOROWとWRAPROWSを組み合わせて、まずは結合されているセルの0を分離します。すると以下のようになるので、あとはここから、右側の列を削除し、左側の列だけを残せば、数量のデータだけを抽出できます。

ここで使用するのが、TAKE関数です。TAKE関数も簡単にだけ使い方を説明しますが、配列(ひとまずデータテーブルと同じ意味と理解ください)の行と列の一部だけを抽出する時に使います。具体的には、ある配列の何行目まで使いますか、何列目前使いますか、というのを指定します。

例えば、上のTOROWとWRAPROWSの結果を、3行目まで使いたい時は以下のようにします。

今回は行方向には指定せず、列方向に関して、1列目だけを使うようにしたいので、以下のように指定します。

こうすることで、数量のデータだけを1列に抽出できました。単価も同じように抽出できます。