ピボットテーブルで、重複を除いた合計を集計する方法

図のA-B列を元に、ピボットテーブルをD列に作りました。
a社では、佐藤さんが1回、高橋さんが3回ですが、重複を除いて、a社では佐藤さんと高橋さんの2名がいます。
a社は2名だということを集計させるにはどうやればよいでしょうか？

環境は、Office365です。

No.2 ベストアンサー 回答者： goomania 回答日時： 2021/06/06 17:18

No.1です。

(1)関数の中の&と=の意味
(2)なぜ1を掛けているのでしょうか。
(3)試しに1を掛けないと0になります。なぜ0に1を掛けると1以上の整数になるのでしょうか。
という追加のご質問をいただきましたので、説明します。

添付画像をご覧ください。
C1に「A&B」という見出しをつけ、C2に
=A2&B2
を記述し、下方向へコピーしてあります。
つまり(1）のご質問の「&の意味」はA列とB列を結合して新しいデータを生成するということです。作業列をC列とすればこのようになるものを数式の中で実現しようてしたものです。
次に、C列で生成した結合データC2～C9を行と列を入替えてD1～K1に配置してあります。
さらに、D2に
=$C2=D$1
を記述し、下方向、右方向へコピーしてあります。
その上で、D10に
=SUMPRODUCT(D2:D9)
を記述し、右方向へコピーし、
また、D11に
=SUMPRODUCT((D2:D9)*1)
を記述し、右方向へコピーしてあります。

さて、ここで
=SUMPRODUCT((D2:D9)*1)･･････①
の数式のD2:D9の部分ですが、これは「C2:C9のデータとD1とが『等しいかどうか』」を各行ごとに「TRUE」「FALSE」で表したものです。
ではC2:C9のデータとは何かというとA2&B2、A3&B3、A4&B4･･････つまり、$A$2:$A$9&$B$2:$B$9と同じです。
またD1はC2と同じですので、C2つまりA2&B2と同じです。
従って①の数式は「C2:C9のデータとC2とが『等しいかどうか』」を変形したものであり、
=SUMPRODUCT(($A$2:$A$9&$B$2:$B$9=A2&B2)*1)
と同じ意味だと判ります。
つまり(1)のご質問の「=の意味」は上記の『等しいかどうか』を数式として表すためのものです。
ご質問者は
>SUMPRODUCT関数の仕様は理解している
とおっしゃっています。
添付画像のD10に記述した、「*1」のない数式
=SUMPRODUCT(D2:D9)
で結果が「０」になってしまうのは「TRUE」「FALSE」を集計しようとしているためです。数値でないものを集計しても数値としての集計結果は得られません。
そこで、「*1」を用いることにより数値にしているのです。「TRUE」「FALSE」を加減乗除計算式に用いるとTRUEは1、FALSEは0、というのは「EXCELの約束事」と理解しておいてください。
※EXCELの指南書的なものではあまり見かけませんが、前回回答は=1/SUMPRODUCT(($A$2:$A$9&$B$2:$B$9=A2&B2)+0)や=1/SUMPRODUCT(($A$2:$A$9&$B$2:$B$9=A2&B2)/1)という数式にしても結果は同じだということになります。

これが、(2)(3)のご質問の解説になります。

A列とB列を結合して、「〇〇会社の□□」という、新しいデータを生成し、該当行と同じデータが、生成された全データ中に幾つ存在するのかをSUMPRODUCT関数を使って数えます。その逆数を計算して集計したときに1になるようにしているという訳です。

この回答へのお礼

御親切に、詳しく説明してくださってありがとうございます。

=の意味、理解しました。２つのセルが同じならTRUE、違っていればFALSEを返すのですね。
会社と個人を合体させたもの(会社&個人)が、全体の中でいくつあるか(TRUEがいくつなのか）を調べてたのですね。
SUMPRODUCTが返す結果で、例えば内部的にTRUE、TRUE、TRUEという結果だとして、そこに1を掛けることで数字の3になるのですね。

すっきりしました。
ありがとうございます。

お礼日時：2021/06/06 18:02

No.1 回答者： goomania 回答日時： 2021/06/04 17:49

方法はいろいろあると思いますが、その一つとして以下のような方法はいかがでしょう

C1に「社員数」という見出しをつけC2に以下の数式を記述します。
=1/SUMPRODUCT(($A$2:$A$9&$B$2:$B$9=A2&B2)*1)
これをオートフィル等で下方向へコピーします。
添付画像①のような表ができます。
これを作成したうえで、ピボットテーブルを作成します。
会社を行へ、社員数を∑値へ各々プロットすると添付画像➁のような社員数の表ができます。

この回答へのお礼

回答ありがとうございます。
お教えくださったとおりにしたら、希望どおりのものができました。
なるほど、重複をすべて足すと1になるように仕掛けておくのですね。

とりあえずはこれで解決なのですが、追加で質問させてください。
私のスキル不足のため、何故、お教えくださった式でこの動作になるのかが理解できません。

■以下は理解しています。
・SUMPRODUCT関数の仕様
・「$A$2:$A$9」は、A列2行からA列9行までを絶対値で選択の意味
・A2はその行を相対的に参照。下にコピーすると、参照先がA3、A4、A5と変化する。

■お聞きしたいこと
・関数の中の&と=の意味
・なぜ1を掛けているのでしょうか。
・試しに1を掛けないと0になります。なぜ0に1を掛けると1以上の整数になるのでしょうか。

お礼日時：2021/06/06 06:47