![見出し画像](https://assets.st-note.com/production/uploads/images/129578520/rectangle_large_type_2_dadc203a206169f67822374324a229dd.png?width=1200)
Tableau でできる表現をGoogle スプレッドシートでもやってみた その3 ◯ピボットテーブルで日付を年に / △ 複数の条件でユニークな値の数を数える / ◯数式で文字列を結合
前回の記事(その2)に引き続き、Tableau では簡単にできることをGoogle スプレッドシートでも実現できるのかを試してみます。
一体何のことやらわからん、という方は、このシリーズの最初の記事を読んでみてください。
Ord 1のQ3 の問題を解いてみる
DATA Saber のウェブサイト内の課題、
「1.HandsOn - Fundamental」の「設問」をクリックすると、
Tableau Public で公開されている問題ページを表示します。
![](https://assets.st-note.com/img/1706857471206-9BgEbz6Uq4.png?width=1200)
画像出所:https://public.tableau.com/app/profile/data.saber/viz/1_HandsOn-Fundamental/1_HandsOn-Fundamental
この問題の中から、いくつかをTableau で解いた後で、Google スプレッドシートでも解いてみる、ということをしてみます。
Tableau で解いてみる
![](https://assets.st-note.com/img/1706857572426-xpbSHvd8ig.png?width=1200)
画像出所:https://public.tableau.com/app/profile/data.saber/viz/1_HandsOn-Fundamental/1_HandsOn-Fundamental
フィルタに「オーダー日」をドラッグ&ドロップ。「年」を2014年に指定する
「行」に見出しとなる項目(ディメンション という)である「顧客 ID」と「カテゴリ」をドラッグ&ドロップ
画面最下部の「◯個のマーク」を目視
完成。
![](https://assets.st-note.com/img/1706857948788-3nU0xcOQMq.png?width=1200)
「年」を2014年に指定する
![](https://assets.st-note.com/img/1706858081374-eXut18EAJ3.png?width=1200)
「顧客 ID」と「カテゴリ」をドラッグ&ドロップ
![](https://assets.st-note.com/img/1706858246055-GcuwvssUy0.png)
まで、3手をもちまして、Tableau での実装となります。
Ord 1のQ3 の問題を解いてみる(Part2)
新しい計算フィールドを作成する
名前:「顧客 ID + カテゴリ」
計算式:[顧客 Id]+[カテゴリ]
※年ごとに、この計算式による文字列の一意の数を求めればよい「行」に見出しとなる項目である「オーダー日」をドラッグ&ドロップ
集計単位を「年」にする「行」に集計値を求めたい項目である「顧客 ID + カテゴリ」をドラッグ&ドロップ。
データ型を「ディメンション」→「メジャー」>「カウント(個別)」に変更。
データ型を「不連続」→連続に変更
![](https://assets.st-note.com/img/1706858690945-S8nWgC42iN.png?width=1200)
名前:「顧客 ID + カテゴリ」
計算式:[顧客 Id]+[カテゴリ]
![](https://assets.st-note.com/img/1706858760467-zEkiwMIiiY.png?width=1200)
集計単位を「年」にする
![](https://assets.st-note.com/img/1706858995583-hNv0mGB4PB.png?width=1200)
![](https://assets.st-note.com/img/1706859260317-N8kvxOjjFt.png?width=1200)
データ型を「不連続」→連続に変更
まで、3手をもちまして、Tableau での別手順による実装となります。
Google スプレッドシートで解いてみる
Google スプレッドシートのピボットテーブル内では文字列を結合したものを集計する機能はない(ように思うの)です。
年ごとに、カテゴリごとに、顧客 ID の一意の値の数を集計し、カテゴリごとに合計することで今回の問題の答えを得られます。
データの範囲を広めに指定して
「挿入」>「ピボットテーブル」>新しいシート
ピボットテーブルエディタの「行」に「オーダー日」をドラッグ&ドロップオーダー日のどれかのセルをクリックして右クリック。
>「ピボット日付グループを作成」> 「年」をクリックピボットテーブルエディタの「列」に「カテゴリ」をドラッグ&ドロップ
ピボットテーブルエディタの「値」に「顧客 ID」をドラッグ&ドロップして、集計方法を「COUNTUNIQUE」
「2014年」のすべてのカテゴリの集計値をドラッグ&ドロップで範囲指定。画面右下のデータの集計をクリックして、合計値を確認。
※行・列の「総計」はすべてのカテゴリでの「顧客 ID」の総計であるため、行のすべてのセル、列のすべてのセルの合計とは異なる。全カテゴリの顧客 ID やすべての年の顧客 ID のユニークな値の数を合計するようにSUM 関数で定義する
完成
![](https://assets.st-note.com/img/1706859934887-aNaoVzvSrR.png?width=1200)
ピボットテーブルエディタの「行」に「オーダー日」をドラッグ&ドロップ
オーダー日のどれかのセルをクリックして右クリック。
>「ピボット日付グループを作成」> 「年」をクリック
ピボットテーブルエディタの「列」に「カテゴリ」をドラッグ&ドロップ
![](https://assets.st-note.com/img/1706860122401-nj0MYF0i6m.png?width=1200)
![](https://assets.st-note.com/img/1706860219563-QpWFedFu9l.png?width=1200)
集計方法を「COUNTUNIQUE」
![](https://assets.st-note.com/img/1706860394880-ry0cgExQsj.png?width=1200)
画面右下のデータの集計をクリックして、合計値を確認。
![](https://assets.st-note.com/img/1706860626816-Numwr63YDI.png?width=1200)
まで、標準機能6手によるGoogle スプレッドシートでの実装となります。
Google スプレッドシートで解いてみる(Part2)
ソースデータの「顧客 ID」列の左に1列を追加して、
「顧客ID + カテゴリ」という列を作成し、数式で「顧客 ID」と「カテゴリ」を結合してみましょう。
「列を追加」による操作をしないで右端の空白列に入力しても、すでに作成したピボットテーブルの範囲には含まれないので、列を追加の操作をしました。
![](https://assets.st-note.com/img/1706861020242-P6yeIxjvNY.png?width=1200)
=ArrayFormula(IF(G2:G<>"",G2:G&O2:O,""))
=ArrayFormula(IF(G2:G<>"",G2:G&O2:O,""))
作成した列「顧客ID + カテゴリ」をピボットテーブルエディタの「値」の領域にドラッグ&ドロップして、集計方法をCOUNTUNIQUE にすることで、今回の問題への答えを求めることもできます。
![](https://assets.st-note.com/img/1706861189345-oOshQOZKNA.png?width=1200)
集計方法:COUNTUNIQUE
複数の列のテキストを結合してユニークな値を求めるには、元データを変更したり、集計方法に工夫が必要
設問で問われている、ある年の「顧客 ID」「カテゴリ」を結合したユニークな値の合計を算出するための操作は、基本機能だけではTableau だと簡単にできて、Google スプレッドシートだと手順がややこしいものでした。
結合した新しいデータを定義するには、Tableau でもGoogle スプレッドシートでも数式をしようすると、ちょっと簡単に実現できます。
作成した数式をしようすると、Tableau でもGoogle スプレッドシートのピボットテーブルでも一瞬で実現できました。
引き続き、このシリーズではDATA Saber の課題にGoogle スプレッドシートでも取り組んでみて、Google スプレッドシートでもなんとかなるのか?という挑戦をしていきます。
この記事が気に入ったらサポートをしてみませんか?