新しいサービス【AwesomeQR】

【Excel】ピボットテーブルの立場で、COUNTIFS関数にツッコミをいれてみた

4 min 15,935 views

はじめに

自社のサイトのデータをGoogleのSearch Consoleでよく確認しているのですが、ExcelのCOUNTIFSでGoogle検索する人が圧倒的に多いということがわかりました。

そこで、もちろんCOUNTIFSの関数使ってもできますが、ピボットテーブルっていう便利な機能もあるよということで、ピボットテーブルを知らない人へ向けたツッコミ形式で解説を試みてみます。

あくまで提案で、関数使うのが悪いとは思いません。単純に選択肢の一つと考えます。

私も恥ずかしながら、ピボットテーブルというものを知ったのがExcelを仕事で使い始めて(17年くらい前)から8年くらい経った時でした。

ですので、今回は、ピボットテーブルって集計するのに便利ですよということ試みるための記事となります。

Excelは関数がすべてだ、仕事を自分だけで完了する、他の人とデータをシェアすることはないという方へ向けては意味のない記事かもしれません

テーマ

今回ツッコミを入れる題材は、弊社で出している以下の記事です。

当時のデータベースを再現するとこんな感じです。

COUNTIFS用データベースイメージ

学校の先生が生徒の成績を一覧で見るにはこういった表をExcelで作ることもあると思います。

まだ、ツッコミの部分はありません

さて、ここから、次の条件で人数をカウントしていくという方法からツッコミ形式でやっていきたいと思います。

ツッコミ(前半)

関数太郎(昔の自分)

関数太郎(昔の自分)

まず、得点が70以上の人を合格者数を数えるのを、COUNTIFSという関数でやってみせよう!

ほう、なぜわざわざ関数使うんだ?

ピボット怠け者

ピボット怠け者

関数太郎(昔の自分)

関数太郎(昔の自分)

なぜって何を言っているんだ?知らないのか君は?Excelの関数の中でも、多くの人が検索しているCOUNTIFSを使うのは当たり前じゃないか!

なるほど、ピボットテーブルは知ってるか?

ピボット怠け者

ピボット怠け者

関数太郎(昔の自分)

関数太郎(昔の自分)

ん?ピボットテーブル??知らないがそれが何だっていうんだ?ぼくはExcelの関数、マクロ結構詳しいんだぞ!

ああ、これは俺みたいなめんどくさがり屋がもう自分で何かを調べるのめんどくせーと思ったときに誰でも同じことを関数知らなくてもできるやつかな

ピボット怠け者

ピボット怠け者

関数太郎(昔の自分)

関数太郎(昔の自分)

なるほど、じゃあ質問だが、70点以上の合格者の人数をどうやって調べるのかね?

こうやってやるんだ

ピボット怠け者

ピボット怠け者

ピボットテーブルでの方法①

  1. STEP

    範囲指定

    まずは以下のように、範囲(ここではA2-D12)を指定して、挿入ピボットテーブルテーブルまたは範囲からをクリック

    Excelサンプル

    そうすると、以下のポップアップウィンドウがでますので、そのままOKと押すか、今回のように、既存のワークシート内に結果を表示したい場合は、既存のワークシートを選択して、表示したいセル(今回はF2セル)を選択します。

    テーブルからピボットテーブルへ

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

    ピボットテーブルイメージ

    右にピボットテーブルのフィールドというものが表示され、この辺りを操作していきます。

  2. STEP

    ピボットテーブルの操作

    ここからはやり方はいくつかありますが、まず、以下のように点数をクリックしたままΣ値というところにドラッグアンドドロップします。

    ピボットテーブルフィールド

    すると、Σ値というところに、合計/名前とでます。これは、表にある点数の合計がいくつかということを関数を以下のように使わずに表示してくれます。

    合計値の計算

    続いて、こちらもやり方はいろいろありますが、というところに同様に点数をドラッグアンドドロップします。

    点数ごとの合計

    すると、上記のように点数によって、合計された点数が表示されます。

    今回、点数の合計を計算したいわけではないので、合計を以下のように個数に変更します。

    ピボットテーブル値フィールドの設定

    上のようにΣ値合計/点数の右にある▼をクリックして、値フィールドの設定をクリックします。

    ピボットテーブル値フィールドの設定

    すると、点数が入っている個数、今回で言うとすべての行に点数が入っていますので、以下のようにその個数(10)となります。

    点数の合計

    ここまで、まだマウスしか使ってません

  3. STEP

    絞込

    最後に70点以上の人数をカウントします。

    これもやり方はいくつもありますが、以下のように列ラベルの右下の▼をクリックして、ラベルフィルターをクリックし、指定の値以上をクリックします。

    ラベルフィルター

    すると以下のようにポップアップウィンドウが出るので、ここでようやくキーボードを使って、70と入力します。

    ラベルフィルター

    これで、以下のように人数がカウントできます。

    ラベルフィルターの総計

ツッコミ(中盤)

関数太郎(昔の自分)

関数太郎(昔の自分)

おいおい、ピボット怠け者、こんな面倒なステップやらなくてもCOUNTIFS関数で一撃ですよ!

ほう、じゃあ聞くが、COUNTIFSで平均点とかもすぐにできるのかよ?

ピボット怠け者

ピボット怠け者

関数太郎(昔の自分)

関数太郎(昔の自分)

なにを言っているのですか?今は人数カウントしたいだけですよ。

なるほど、確かに人数カウントしたいだけなら関数を否定するつもりはないな。
ただ、俺だったらどうせなら平均点とか、場合によっては標準偏差とかも調べるがね。

ピボット怠け者

ピボット怠け者

関数太郎(昔の自分)

関数太郎(昔の自分)

平均なら、AVARAGE関数、STDEV.P関数使えばいいでしょうが。

そうなんだ、いくつかの情報知るのに、関数を組み合わせる必要が出てくるだろ?
関数覚えるの面倒だから、ピボットテーブルひとつ覚えりゃ十分なんだよ。

例えばデータ調べたければ、合格ライン決めるにしても平均とか、最高点、最低点、ばらつきくらいは調べたくなるだろう?

今はネットで調べりゃ関数の意味は出てくるけど、たったある条件の数を数える関数を調べるためにこれだけ多くの人がググる事態になるんだ。

ピボット怠け者

ピボット怠け者

関数太郎(昔の自分)

関数太郎(昔の自分)

待ってくれ、その前に、まだ条件があるじゃないか!

意味があるかは置いといて、性別での合格ラインの数を数えるにはどうすんですか?

ん?簡単だよ。こうやるんだ。

ピボット怠け者

ピボット怠け者

ピボットテーブルでの方法②

  1. STEP

    さらに絞込

    これもやり方はいろいろありますが、単に、性別をフィルタにかけて人数を知りたいだけの時は、以下のように、性別をフィルタにドラッグアンドドロップします。

    絞込フィルター

    続いて、以下のように、性別の右の▼をクリックして、下の、複数のアイテムを選択にチェックを入れ、例えば男のみに設定してOKをクリックします。

    絞込フィルター

    すると、以下のように、3人という結果がでます。

    絞込フィルター
  2. STEP

    おまけ

    今回の場合のように、フィルタをかけないでも、女性と男性の合格ラインの人数をすぐを以下のようにすぐに計算できます。

    フィルタ

    上図のように、単に、行に性別をドラッグアンドドロップします。

    それだけで、女性も、男性も合格ラインの合計人数を把握できます。

ツッコミ(後半)

関数太郎(昔の自分)

関数太郎(昔の自分)

うー--ん、少しだけ便利なようですね。

ただ、やはり、関数の方が早いですよね。

そうだな。お前みたいに何かの数だけ調べたい場合は、関数の方が早いだろうな。

だけど、条件が他にもたくさんあったり、合計や最大値、最小値、平均、標準偏差、こういったものを調べたい場合はどうだろうな。

ピボット怠け者

ピボット怠け者

関数太郎(昔の自分)

関数太郎(昔の自分)

うー-ん。うー-ん。(言い訳がみつからない。)

今回は、数値でいろいろ見てみたが、これはグラフにも一瞬でできるんだ。

例えば、下のようになる。

ピボットグラフ

こんなグラフも一瞬でできるぞ。

ピボット怠け者

ピボット怠け者

関数太郎(昔の自分)

関数太郎(昔の自分)

うー-ん。うー-ん。(言い訳がみつからない。)

(少し使ってみるか。)

さいごに

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

実は、私もまさに関数さえ知っていればなんでもできるでしょと思っていたものの、いろいろなデータを分析するのに、いくつかの関数を組み合わせる必要があって、やや複雑になるということは理解できました。

さらに、関数は便利な反面、セルを触るまで何をやっているのかが分からないということがあり、関数マスターのExcelを分析するのにいちいちセルを見るしかありませんでした。

ですが、ピボットテーブルを使うことで、どこにフィルタがかかっているか、どういう選択をしているのか、グラフもすぐにでるなどメリットが大きいので、多少な学習コストはかかるものの、無理やりピボットテーブルを使うようになりました。

正直、今回の例は、まだまだピボットテーブルの機能のほんの一部にすぎないので、今後、もっと素晴らしい使い方をシェアしたいと思います。

関連記事