【Excel×kintone】テーブルデータの集計で複数行を1行へまとめる方法【powerQuery】

3 min 200 views

はじめに

今回は、サムネイル画像にあるように、ひとつの情報に対して、複数行で処理されるようなデータを1つの行に自動でまとめる方法を紹介します。

特に、kintoneのテーブルを使った場合でデータをダウンロードしたデータの変換を紹介していきます。

実装

さっそく実装していきます。

動画でご覧になる方はYouTube動画をご覧ください。

  1. STEP

    データの準備

    今回は、下図のように、例えば、ある商品を何種類かひとつのレコードで注文する、あるいは、注文されたような場合を想定しています。

    上図はkintoneの画面ですが、プラグインでテーブルが複数表示できるようにしてあります。

    こちらのkintoneのデータをダウンロードすると、下図のような形で、1回の注文に対して、複数行できてしまいます。

    今回は、こちらを1行に変換していきます。

  2. STEP

    データの取得

    それでは、新規のExcelを開いて、データテキストまたはCSVをクリックして、kintoneからダウンロードしたデータを選択して、インポートをクリックします。

    すると、下図のようになりますので、そのままデータの変換をクリックします。

    これで、powerQueryエディターが立ち上がります。

  3. STEP

    データの整形

    続いて、データを整形していきます。

    まずは、後々のためにデータの順序を保持しておきたいので、インデックス列というものをクリックして、データソート用の列を追加していきます。

    すでにkintoneのデータでシーケンスのようなものがあれば特段この作業はいりません。

    続いて、新しい列を用意したいので、新しい列の名前となるものを作成していきます。

    下図のように、Noという列を選択した状態で、列の追加列からの列をクリックして、下図のように、結合済みという列が生成されるので、その一番上の行に例えば商品1などと入力します。

    すると、上図のように自動で下のものが入力されていきます。

    今回は、下図のように、すでにテーブルに順番が振られているものを想定しております。

    続けて同様に個数についても下図のように設定します。

    次に、上で作った列を横並びにさせる処理をしていきます。

    下図のように、上記で作成した列を選択した状態で、変換列のピボットをクリックして、値列に、実際に横並びにする列(商品を、詳細設定オプション値の集計関数集計しないを設定しOKをクリック。

    すると下図のように、商品ごとに列が分割されます。

    これを、下図のように、個数についても同様の作業を行います。

    ここで、下図のように、最初に作ってインデックス列で昇順で並べ替えを選び、ソートさせます。

    そして、ここがポイントなのですが、下図のように、商品1を含めず、商品2~商品4を選択した状態で、変換フィルをクリックします。

    すると、下図のように、データが左のデータを基準に上方向につまる状態になります。

    これを同様に、下図のように、個数についてもおこないます。

    ようやく最後ですが、下図のように、レコードの開始行というフラグでフィルターをかけます。

    テーブル付きのkintoneのデータをダウンロードするとデフォルトでレコードの開始行という列が用意されております。

    すると、下図のように、1行にまとまりました。

    最後に、閉じて読み込むをクリックします。

    すると、下図の通り完成しました。

さいごに

いかがだったでしょうか?一見工程は多いように思いますが、今回もマクロや関数は一切使っておりません。

というか、プログラムが書けないだけですが…

しかし、このように最初に設定しておくことで、同じデータをダウンロードして、データを差し替えるだけで、同じように複数行をまとめることができるので、毎日とか毎週、毎月やる場合には便利なのではないかと思います。

また、操作した内容もpowerQueryで順番等も確認できるため、プログラムの知識とかがなくてもちょっとだけツールを理解するだけで、簡単に修正することできます。

今後も、powerQueryでも面白い使い方を紹介していきたいと思います。

関連記事