Qlik Trainingブログ

  • QlikView
  • 開発者
  • データロード
2013.07.16

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

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

Excelでデータを管理している場合、下図のように年や月ごとにデータが複数列に分かれていることがあると思います。

このままデータを取り込むと、数値項目が年ごとに分かれてしまいます。
QlikViewではピボットテーブルを使用すれば、簡単にデータを縦横方向に展開できるため、もとのデータは一列にまとまっていた方が使いやすいです。

変換後のイメージは下図のイメージです。

ファイルのダウンロード

ここでは以下のファイルを使用しますので、ダウンロードしてください。
multiple-columns.zip
Zip形式ですので、ダウンロード後解凍してください。
以下ファイルが含まれています。

売上実績.xls
今回使用するデータです。
年ごとに列が分かれているのに加えて、「分類名1」を見ると空白のセルがあります。「分類名1」はおなじ値が続く場合は、二つ目以降が省略されています。
この例のように、見やすさを重視して作成されたExcelでは、このようなデータの持ち方もよくあると思いますので、空白のセルを埋める設定も一緒に確認してみましょう。

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

それではデータを取り込んでみましょう。
まずはファイルを新規に作成し、[ロードスクリプトの編集]画面を起動します。

ロードスクリプトの最下行にカーソルをあわせて、[テーブルファイル]ボタンをクリックします。

「売上実績.xls」を選択します。

[列見出し]を[先頭行]に変更して、[次へ]ボタンをクリックします。

まず、空白のセルを埋める指定をしてみましょう。
[変換オプションを有効にする]ボタンをクリックします。

[セルの置換え]タブを選択します。

今回変換したい「分類名1」を選択します。
「分類名1」のラベルの上にある「1」の部分を、マウスでクリックします。

[セルの置換え]ボタンをクリックします。

[セルの置換え]画面が表示されます。
[セルの条件]ボタンをクリックします。

[値なし]が選択されていることを確認して、[OK]ボタンをクリックします。

[値なし]は空白のセルを置き換えることをあらわします。

[置換え元データ]が[上]になっていることを確認し[OK]ボタンをクリックします。

[置換え元データ]は置き換えるもとのデータが、上下左右どの位置にあるのかをあらわします。

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

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

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

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

複数列に分かれたデータを一列にまとめるには、クロステーブル機能を使用します。
[クロステーブル]ボタンをクリックします。

[クロステーブル]画面が表示されます。
画面下部の[行見出し項目][列見出し項目][データ項目]に指定をおこないます。

ここでは以下のように指定してください。
行見出し項目:2
列見出し項目:年
データ項目:売上金額
設定後[OK]ボタンをクリックしてください。

各指定の意味は下図を確認してください。

クロステーブルの設定は以上で完了です。
画面下部で変換後のイメージを確認できます。
[終了]ボタンをクリックしてください。

ロードスクリプトは以上で完成です。[OK]ボタンをクリックします。

データを取り込んでみましょう。
[リロード]ボタンをクリックします。

ファイルを保存してください。

データが取り込まれました。
[閉じる]ボタンをクリックしてください。

もとのデータは15行あり、それが5年分(5列分)あるため、15x5で75件のデータが取り込まれています。

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

結果の確認

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

テーブルボックスを使用して、データを確認します。
シートの余白を右クリックし[シートオブジェクトの追加]→[テーブルボックス]を選択します。

[すべて追加]ボタンをクリックして、すべての項目を選択します。

[ロード順]ボタンをクリックして、項目の順序を調整します。
[OK]ボタンをクリックします。

テーブルボックスが作成されました。
イメージどおりにデータが取り込まれていることが確認できます。

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

振り返り

今回は複数列に分かれたデータを一列にまとめる方法をご紹介しました。
また、空白のセルを埋める方法についてもご紹介しました。

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

最後に…

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

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

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

お疲れ様でした。

関連している記事

  • Qlik Sense
  • Qlik Cloud
  • 開発者
  • データロード
2024.03.08

GoogleスプレッドシートのデータをQlik Senseに読み込みたい!

GoogleスプレッドシートのデータをQlik Senseで分析したいということはありませんか?Qlik SenseではGoogle Drive & Spreadsheetsコネクターを利用してGoogleスプレッドシートを読み込むことができます。本記事では、Qlik Sense SaaSのデータロードエディタでGoogleスプレッドシートを読み込む方法を紹介します。

  • Qlik Cloud
  • Qlik Sense
  • QlikView
  • 運用者
  • 開発者
  • 利用者
2024.02.19

無料会員サービス Qmeet Mateのご案内

Qmeet Mate(きゅーみーとめいと)についてご案内します!

  • Qlik Cloud
  • Qlik Sense
  • 運用者
  • 開発者
2024.02.16

Excelレポートをメール配信してみよう!

今回は作成したExcelレポートをメール配信する方法をご紹介します。 レポートの作成手順は別記事でご紹介しておりますので、合わせてご確認ください。 Qlik Sense SaaSでExcelレポートを作成してみよう!

ページの先頭へ戻る