見出し画像
Photo by satomigoro

Tableau でできる表現をGoogle スプレッドシートでもやってみた その3 ◯ピボットテーブルで日付を年に / △ 複数の条件でユニークな値の数を数える / ◯数式で文字列を結合

Teeda

前回の記事（その2）に引き続き、Tableau では簡単にできることをGoogle スプレッドシートでも実現できるのかを試してみます。

一体何のことやらわからん、という方は、このシリーズの最初の記事を読んでみてください。

Ord 1のQ3 の問題を解いてみる

DATA Saber のウェブサイト内の課題、
「1.HandsOn - Fundamental」の「設問」をクリックすると、
Tableau Public で公開されている問題ページを表示します。

DATA Saber プログラムでの課題。
画像出所：https://public.tableau.com/app/profile/data.saber/viz/1_HandsOn-Fundamental/1_HandsOn-Fundamental

この問題の中から、いくつかをTableau で解いた後で、Google スプレッドシートでも解いてみる、ということをしてみます。

Tableau で解いてみる

DATA Saber プログラムでの課題。
画像出所：https://public.tableau.com/app/profile/data.saber/viz/1_HandsOn-Fundamental/1_HandsOn-Fundamental

  1. フィルタに「オーダー日」をドラッグ&ドロップ。「年」を2014年に指定する

  2. 「行」に見出しとなる項目（ディメンション という）である「顧客 ID」と「カテゴリ」をドラッグ＆ドロップ

  3. 画面最下部の「◯個のマーク」を目視

完成。

フィルタに「オーダー日」をドラッグ&ドロップ。
「年」を2014年に指定する
「行」に見出しとなる項目（ディメンション という）である
「顧客 ID」と「カテゴリ」をドラッグ＆ドロップ
画面最下部の「◯個のマーク」を目視

まで、3手をもちまして、Tableau での実装となります。

Ord 1のQ3 の問題を解いてみる（Part2）

  1. 新しい計算フィールドを作成する
    名前：「顧客 ID + カテゴリ」
    計算式：[顧客 Id]+[カテゴリ]
    ※年ごとに、この計算式による文字列の一意の数を求めればよい

  2. 「行」に見出しとなる項目である「オーダー日」をドラッグ＆ドロップ
    集計単位を「年」にする

  3. 「行」に集計値を求めたい項目である「顧客 ID + カテゴリ」をドラッグ＆ドロップ。

  4. データ型を「ディメンション」→「メジャー」＞「カウント(個別)」に変更。

  5. データ型を「不連続」→連続に変更

新しい計算フィールドを作成する
名前：「顧客 ID + カテゴリ」
計算式：[顧客 Id]+[カテゴリ]
「行」に見出しとなる項目である「オーダー日」をドラッグ＆ドロップ
集計単位を「年」にする
データ型を「ディメンション」→「メジャー」＞「カウント(個別)」に変更。
データ型を「ディメンション」→「メジャー」＞「カウント(個別)」に変更。
データ型を「不連続」→連続に変更

まで、3手をもちまして、Tableau での別手順による実装となります。

Google スプレッドシートで解いてみる

Google スプレッドシートのピボットテーブル内では文字列を結合したものを集計する機能はない（ように思うの）です。
年ごとに、カテゴリごとに、顧客 ID の一意の値の数を集計し、カテゴリごとに合計することで今回の問題の答えを得られます。

  1. データの範囲を広めに指定して
    「挿入」＞「ピボットテーブル」＞新しいシート
    ピボットテーブルエディタの「行」に「オーダー日」をドラッグ＆ドロップ

  2. オーダー日のどれかのセルをクリックして右クリック。
    ＞「ピボット日付グループを作成」＞ 「年」をクリック

  3. ピボットテーブルエディタの「列」に「カテゴリ」をドラッグ&ドロップ

  4. ピボットテーブルエディタの「値」に「顧客 ID」をドラッグ＆ドロップして、集計方法を「COUNTUNIQUE」

  5. 「2014年」のすべてのカテゴリの集計値をドラッグ&ドロップで範囲指定。画面右下のデータの集計をクリックして、合計値を確認。
    ※行・列の「総計」はすべてのカテゴリでの「顧客 ID」の総計であるため、行のすべてのセル、列のすべてのセルの合計とは異なる。

  6. 全カテゴリの顧客 ID やすべての年の顧客 ID のユニークな値の数を合計するようにSUM 関数で定義する

完成

「挿入」＞「ピボットテーブル」＞新しいシート
ピボットテーブルエディタの「行」に「オーダー日」をドラッグ＆ドロップ
オーダー日のどれかのセルをクリックして右クリック。
＞「ピボット日付グループを作成」＞ 「年」をクリック
ピボットテーブルエディタの「列」に「カテゴリ」をドラッグ&ドロップ
ピボットテーブルエディタの「列」に「カテゴリ」をドラッグ&ドロップ
ピボットテーブルエディタの「値」に「顧客 ID」をドラッグ＆ドロップして、
集計方法を「COUNTUNIQUE」
「2014年」のすべてのカテゴリの集計値をドラッグ&ドロップで範囲指定。
画面右下のデータの集計をクリックして、合計値を確認。
全カテゴリの顧客 ID やすべての年の顧客 ID のユニークな値の数を合計するようにSUM 関数で定義する

まで、標準機能6手によるGoogle スプレッドシートでの実装となります。

Google スプレッドシートで解いてみる（Part2）

ソースデータの「顧客 ID」列の左に1列を追加して、
「顧客ID + カテゴリ」という列を作成し、数式で「顧客 ID」と「カテゴリ」を結合してみましょう。
「列を追加」による操作をしないで右端の空白列に入力しても、すでに作成したピボットテーブルの範囲には含まれないので、列を追加の操作をしました。

数式で「顧客 ID」と「カテゴリ」を結合
=ArrayFormula(IF(G2:G<>"",G2:G&O2:O,""))
=ArrayFormula(IF(G2:G<>"",G2:G&O2:O,""))

作成した列「顧客ID + カテゴリ」をピボットテーブルエディタの「値」の領域にドラッグ&ドロップして、集計方法をCOUNTUNIQUE にすることで、今回の問題への答えを求めることもできます。

値：「顧客ID + カテゴリ」に変更
集計方法：COUNTUNIQUE

複数の列のテキストを結合してユニークな値を求めるには、元データを変更したり、集計方法に工夫が必要

設問で問われている、ある年の「顧客 ID」「カテゴリ」を結合したユニークな値の合計を算出するための操作は、基本機能だけではTableau だと簡単にできて、Google スプレッドシートだと手順がややこしいものでした。

結合した新しいデータを定義するには、Tableau でもGoogle スプレッドシートでも数式をしようすると、ちょっと簡単に実現できます。
作成した数式をしようすると、Tableau でもGoogle スプレッドシートのピボットテーブルでも一瞬で実現できました。

引き続き、このシリーズではDATA Saber の課題にGoogle スプレッドシートでも取り組んでみて、Google スプレッドシートでもなんとかなるのか？という挑戦をしていきます。

いいなと思ったら応援しよう！