複数列に分かれたデータを一列にまとめる方法(と空白のセルを埋める方法)

複数列に分かれたデータを一列にまとめる方法(と空白のセルを埋める方法)

Pocket

前々回前回は、複数ファイルに分かれたExcelデータを一ファイルにまとめる方法をご紹介しました。
今回は、複数列に分かれたExcelデータを一列にまとめる方法をご紹介します。
Excelでおなじことをするには、複雑な関数の指定が必要だったり、マクロが必要だったりしますが、QlikViewならごく簡単な設定をするだけです。
いつもよりも短いチュートリアルですが、よくある例だと思いますのでぜひご覧ください。

まだQlikViewをお持ちでない方は、ぜひこれを機にダウンロードしてみてください。
QlikViewは以下のページから無料でダウンロードできます。(要ユーザー登録)
QlikView Personal Editionのダウンロード


Excelでデータを管理している場合、下図のように年や月ごとにデータが複数列に分かれていることがあると思います。
multiple-columns-into-single-column-102このままデータを取り込むと、数値項目が年ごとに分かれてしまいます。
QlikViewではピボットテーブルを使用すれば、簡単にデータを縦横方向に展開できるため、もとのデータは一列にまとまっていた方が使いやすいです。

変換後のイメージは下図のイメージです。
multiple-columns-into-single-column-103



ファイルのダウンロード

ここでは以下のファイルを使用しますので、ダウンロードしてください。
multiple-columns.zip
Zip形式ですので、ダウンロード後解凍してください。
以下ファイルが含まれています。
multiple-columns-into-single-column-101
売上実績.xls
今回使用するデータです。
年ごとに列が分かれているのに加えて、「分類名1」を見ると空白のセルがあります。「分類名1」はおなじ値が続く場合は、二つ目以降が省略されています。
この例のように、見やすさを重視して作成されたExcelでは、このようなデータの持ち方もよくあると思いますので、空白のセルを埋める設定も一緒に確認してみましょう。
multiple-columns-into-single-column-104


空白のセルを埋める(セルの置換え)

それではデータを取り込んでみましょう。
まずはファイルを新規に作成し、[ロードスクリプトの編集]画面を起動します。
multiple-columns-into-single-column-105
ロードスクリプトの最下行にカーソルをあわせて、[テーブルファイル]ボタンをクリックします。
multiple-columns-into-single-column-106
「売上実績.xls」を選択します。
multiple-columns-into-single-column-107
[列見出し]を[先頭行]に変更して、[次へ]ボタンをクリックします。
multiple-columns-into-single-column-108
まず、空白のセルを埋める指定をしてみましょう。
[変換オプションを有効にする]ボタンをクリックします。
multiple-columns-into-single-column-109
[セルの置換え]タブを選択します。
multiple-columns-into-single-column-110
今回変換したい「分類名1」を選択します。
「分類名1」のラベルの上にある「1」の部分を、マウスでクリックします。
multiple-columns-into-single-column-111
[セルの置換え]ボタンをクリックします。
multiple-columns-into-single-column-112
[セルの置換え]画面が表示されます。
[セルの条件]ボタンをクリックします。
multiple-columns-into-single-column-113
[値なし]が選択されていることを確認して、[OK]ボタンをクリックします。
multiple-columns-into-single-column-114[値なし]は空白のセルを置き換えることをあらわします。

[置換え元データ]が[上]になっていることを確認し[OK]ボタンをクリックします。
multiple-columns-into-single-column-115[置換え元データ]は置き換えるもとのデータが、上下左右どの位置にあるのかをあらわします。

ここまでの指定により、空白のセルがあった場合は上の行にあるデータに置き換えられます。つまり今回のように、重複する値が一行目にしか入力されていないときに、二行目以降を自動で埋めてくれます。

空白のセルを埋める指定は、以上で完了です。
画面上で変換後のイメージを確認できます。
[次へ]ボタンをクリックします。
multiple-columns-into-single-column-116


複数列を一列にまとめる(クロステーブル)

こんどは、複数列に分かれたデータを一列にまとめる指定をしてみましょう。

複数列に分かれたデータを一列にまとめるには、クロステーブル機能を使用します。
[クロステーブル]ボタンをクリックします。
multiple-columns-into-single-column-117
[クロステーブル]画面が表示されます。
画面下部の[行見出し項目][列見出し項目][データ項目]に指定をおこないます。
multiple-columns-into-single-column-118ここでは以下のように指定してください。
行見出し項目:2
列見出し項目:年
データ項目:売上金額
設定後[OK]ボタンをクリックしてください。
multiple-columns-into-single-column-119
各指定の意味は下図を確認してください。
multiple-columns-into-single-column-120
クロステーブルの設定は以上で完了です。
画面下部で変換後のイメージを確認できます。
[終了]ボタンをクリックしてください。
multiple-columns-into-single-column-121
ロードスクリプトは以上で完成です。[OK]ボタンをクリックします。
multiple-columns-into-single-column-122
データを取り込んでみましょう。
[リロード]ボタンをクリックします。
multiple-columns-into-single-column-123
ファイルを保存してください。
multiple-columns-into-single-column-124multiple-columns-into-single-column-125
データが取り込まれました。
[閉じる]ボタンをクリックしてください。
multiple-columns-into-single-column-126もとのデータは15行あり、それが5年分(5列分)あるため、15x5で75件のデータが取り込まれています。

データの取り込みは以上で完了です。


結果の確認

さいごに、データがただしく取り込まれているか確認してみましょう。

テーブルボックスを使用して、データを確認します。
シートの余白を右クリックし[シートオブジェクトの追加]→[テーブルボックス]を選択します。
multiple-columns-into-single-column-127
[すべて追加]ボタンをクリックして、すべての項目を選択します。
multiple-columns-into-single-column-128
[ロード順]ボタンをクリックして、項目の順序を調整します。
[OK]ボタンをクリックします。
multiple-columns-into-single-column-129
テーブルボックスが作成されました。
イメージどおりにデータが取り込まれていることが確認できます。
multiple-columns-into-single-column-130

以上で今回のチュートリアルは終了です。


振り返り

今回は複数列に分かれたデータを一列にまとめる方法をご紹介しました。
また、空白のセルを埋める方法についてもご紹介しました。
  • 空白のセルを埋めるには、セルの置換え機能を使用する。
    • ファイルウィザードの二画面目で、[変換オプションを有効にする]ボタンをクリックする。
    • [セルの置換え]タブで、条件などを設定する。
  • 複数列に分かれたデータを一列にまとめるには、クロステーブル機能を使用する。
    • ファイルウィザードの三画面目で、[クロステーブル]ボタンをクリックする。
    • 以下の三つの設定により、どのように変換するかを指定する。
      [行見出し項目]:行見出し(もともと一列になっている列)の数
      [列見出し項目]:列見出しのデータに付ける項目名
      [データ項目]:各セルの数値データに付ける項目名


最後に…

複数列に分かれたデータを一列にまとめたり、空白のセルを埋めたりといった作業は、普段の業務でもよくおこなわれているのではないでしょうか。
こういったデータ変換の処理は、本来ETL(Extract/Transform/Load)と呼ばれる領域で、ETL専門のツールもあるぐらいです。

QlikViewはもともとBI(ビジネス・インテリジェンス)に位置づけられるツールですが、ETL専門のツールに負けないぐらい、データ変換の機能も充実しています。
データの変換/取り込みからデータの分析まで、単体でできるというのも他のBIツールには、なかなかないQlikViewの特長と言えます。

データの変換には、ほかにもさまざまな例がありますので、機会があればまたこのブログで紹介したいと思います。

お疲れ様でした。