新しいサービス【AwesomeQR】

【Excel×kintone】テーブルデータの複数レコードを1レコードにする方法(中~上級者向け)

5 min 2,065 views

はじめに

kintoneに限った話ではないですが、kintoneのテーブルデータのように1つのIDに対して複数の情報を含むデータベースを扱うことがあると思います。

例えば、購買行動で考えると、以下のようなイメージでしょうか。

  • Aさんは、1回の買い物で、みかん3個、リンゴ4個を購入しました。
  • Bさんは、1回の買い物で、ブドウ2房、スイカ1玉、イチゴ1パック購入しました。
  • Cさんは、1回の買い物で、パイナップル1個、メロン1玉、バナナ1房、キウイ3個購入しました。

上記をたとえばkintone表現すると以下のようなデータベースになると思います。

これをダウンロードすると、以下のような形になってしまいます。

今回は、このように1レコードが複数行にわたってしまう状態を1レコードに戻すという方法をpowerQueryを使って紹介したいと思います。

以前に、以下の記事を上げておりますが、今回は、テーブルの行にシーケンスが入っていない、または、テーブルの行が異なる行数の場合でも1行にできる方法を紹介したいと思います。

方法

動画でもご確認いただけます。

  1. STEP

    テーブルデータの整形

    データの読み込みは割愛いたします。気になる方は上記の記事を参考にしてください。

    まずは、テーブルデータとそのままのデータを下図のように分割しておきます。

    左のクエリのデータを右クリックして、参照左クリックします。

    これ自体に特段の意味はありませんが、こうすることで、元々のデータで文字列の変換処理をするなど共通して行う作業が一括で済むメリットがあります。

    続いて、上記でできた新しいクエリに対して、下図のようにテーブルのグループ化と行数のカウントをしておきます。

    ホーム変換グループ化をクリックし、上のラジオボタンは詳細設定を選択します。

    グループ化のキーはレコード番号(これはユニーク値であればなんでもいいです)で、新しく作る列の操作は、すべての行行数のカウントとします。新しい列名は好きな名前を設定してください。

    続いて、これが最大のポイントなのですが、下図のように列の追加をします。

    列の追加全般カスタム列で、以下の関数を入力します。

    Table.AddIndexColumn([カウント], "index", 1, 1)

    簡単に説明しますと、カウントというテーブルデータに1からプラス1ずつインデックス番号を付与します。

    関数に関して詳しい説明を知りたい方は、以下を参照ください。

    続いて、テーブル内の情報を下図のように展開します。

    カスタムという名前の列の右側にあるボタンを左クリックして、テーブル内の情報だけにを入れ、OKを左クリックします。

    元の列名をプレフィックスとして使用しますへのチェックはどちらでも構いません。

    すると、下図のようになります。

    続いて、商品列群を下図のように1列にまとめます。

    商品列群を選択した状態で、変換テキストの列列のマージを左クリックし、区切り記号を選択して、新しい列名を入力し、OKをクリックします。

    この時、区切り記号はこのデータのどこにも使われていない記号を使用するようにしてください。後でこの記号を元に分割します。

    続いて、ここもポイントなのですが、商品の購入数量群の新しい列を下図のように作成します。

    列の追加全般カスタム列を左クリックし、以下の関数を入力します。

    "商品購入数量" & Text.From([index], "ja-JP")

    上記の関数の意味は、indexという数値を文字列として扱うということです。

    続いて、ピボット化するためにテーブルになっている列を削除します。

    Tableになっているカウント列を選択した状態で、ホーム列の管理列の削除を左クリックします。

    これをしないと、次のステップにいけません。

    さて、また重要なポイントなのですが、次に、下図のように、列のピボット化をします。

    商品購入数量のカスタム列を選択した状態で変換任意の列列のピボットを左クリックします。

    値列は結合済みを選択し、詳細設定オプションを展開し、値の集計関数は集計しないにして、OKをクリックします。

    すると、下図のように、商品購入数量が列ごとに分解されました。

    あともう一息です。

    ここで、またもやポイントなのですが、下図のように、行の並べ替えをします。

    行数の右側にある▼ボタンを左クリックして、降順で並べ替えを左クリックします。(ここでは要するに、行数が多い順に並べています。)

    続いて、同じステップで、下図のように、レコード番号(これはユニーク値であればそれで構いません)の右側にある▼ボタンを左クリックして、昇順で並べ替え(降順で並べ替えでも可)を左クリックします。

    続いて、同じステップで、下図のように、indexの右側にある▼ボタンを左クリックして、昇順で並べ替えを左クリックします。

    ここの行の並べ替えは順番が非常に大事なので、上から順番に操作するようにしてください。

    上記を行うと、下図のようになります。

    次に、それぞれのユニークレコードへデータをコピーするために、下図のようにします。

    ここも重要なポイントなのですが、商品購入数量の2番目以降を選択した状態で、変換任意の列フィル右の▼ボタンからを左クリックします。

    続いて、ユニークレコードだけを下図のように抽出します。

    indexの右側の▼ボタンを左クリックして、1だけに☑をいれた状態で、OKをクリックします。

    すると、下図のようにようやく購入した人ごとに横並びでデータが抽出できます。

    このまま終わってもいいですが、最後に、商品購入数量の列を下図のように分解していきます。

    商品購入数量の列を選択した状態でホーム変換列の分割の▼ボタンをクリックし、区切り記号による列の分割を選択します。

    区切り記号をコロン、分割を区切り記号の出現ごとにしてOKをクリックします。

    これを、あと3回繰り返すと、下図のようになります。

    お疲れ様でした。これでテーブルを1行にまとめることができました。

  2. STEP

    元のデータと結合

    STEP1が鬼のように長かったですが、残りは非常に簡単です。

    下図のように、ホーム結合クエリのマージの右の▼ボタンを左クリックして、新規としてクエリをマージを左クリックします。

    下図のように、上のデータを元のデータに設定して、下のデータをSTEP1で整形したデータを設定します。照合するキーをレコード番号(ユニーク値)とし、OKをクリックします。

    続いて、下図のように、不要な列(商品テーブルの列)を削除します。

    削除したい列を選択した状態で、ホーム列の管理列の削除を左クリックします。

    続いて、下図のように、テーブルのデータを展開します。

    テーブルの列の右のボタンを左クリックして、不要な列のチェックを外し、OKをクリックします。

    最後に、下図のように、行の絞込をします。

    レコードの開始行の右の▼ボタンを左クリックして、*に☑を入れてOKをクリックします。

    これを閉じて読み込むと、下図のように、1行のレコードにまとめることができました。

さいごに

いかがだったでしょうか。

正直、ここまでやる意味があるのかという声が聞こえてきそうですが、上の作業において、人間のコピペというもっとも危ない手作業がない状態が作れます。

上記はたかだか購入商品を1行にしているだけですが、例えばこれが世帯主がユーザーの固有のレコードで、扶養家族がテーブルになっているケースなどの個人情報を扱うような場合手作業でコピペして個人情報を誤って違う人へ流出させた場合その責任は非常に重大ものになるでしょう。

このように、何をやっているかわからない複雑な関数、あるいは、誰も変更できないマクロなどを使わないでも、上記のステップで、だれでも簡単に同じ操作ができるというのがこのpowerQueryのメリットだと思いますので、これからもこういった情報をシェアできればと思います。

関連記事