メインコンテンツへスキップ
見出し画像

Excelで「この列、何種類あるんだっけ?」を解消する方法

    Excelで

    「この列、何種類あるんだろう?」

    という疑問を持つときは少なくないと思います。

    そんなときに、フィルタ機能を使って何種類あるか目視で数える…

    なんて非効率的なことしてませんか?

    今回はそんな悩みを解消する方法を教えます!

    Excelのバージョンに関係なく使える方法!

    悩みを解消してくれるのが、以下の関数です。

    =SUMPRODUCT(1/COUNTIF(見る範囲, 見る範囲))

    見た瞬間そっと閉じたくなりますが、実は中身はめちゃくちゃ素直です。

    解説します!

    この式、何してるん?

    この関数は、

    「同じ値が何回出てきても、1回として数える」

    という発想で、ユニークな値の数をカウントしています。中身を詳細に見ていきましょう!

    例で何をしているか見る!

    例えば、イヌの名前アンケートを取って、10件結果が得られたとします。

    画像

    まずは、COUNTIF関数をB列に適用してみましょう!

    ①:COUNTIFは指定範囲で同じものの登場回数を求めている!

    =COUNTIF(B2:B11, B2:B11)

    画像

    「おまえ、B2からB11までの中で何回出てきたの?」と自己申告させるのです!

    ②:1 ÷ 出てきた回数をする

    =1/COUNTIF(B2:B11, B2:B11)

    画像

    「なにこれ?」と思われる方は居ますがポイントは以下です。

    同じ値を全部足すと、

    • ポチ:1/3+1/3+1/3=1

    • いっぬ:1/3+1/3+1/3=1

    • しば:1

    • しば丸:1

    • マックス:1

    • みぃ:1

    と、同じものの数値を全て足すと全て1になるのです!

    言い換えれば、「ポチが1、いっぬが1、しばが1、しば丸が1、マックスが1,みぃが1」と言う結果になり、これはそれぞれのカテゴリが1ずつあると言う事を意味していますね!

    ③:全部足す!

    =SUMPRODUCT(1/COUNTIF(B2:B11,B2:B11))

    画像
    6種類!

    …

    「たすだけなら、SUM関数でよくね?」という疑問を持たれた人は少なくないと思います。

    それは、SUMは配列が苦手で、SUMPRODUCTは配列も得意だからです!

    SUMがダメな理由

    今回の式の中身はこれ👇

    1 / COUNTIF(B2:B11, B2:B11)

    これは、{1/3, 1/3, 1, 1/3, 1, 1/3, 1/3, 1, 1, 1/3}のように、配列(ズラッと並んだ値)を返し、それをセルに順番に出力するのです。

    なので、範囲を扱うSUMが扱えないデータなのです…

    そこで、パワーアップしたSUMPRODUCT関数が出てきて、それを採用しているのです!

    まとめ:この関数の発想

    ここまで読んで着いてこられた方は、「同じグループの人達で必ず1に成るように割り勘しており、全部足しても同じグループの和が1になる事を巧みに使っているんだな…!」と言われなくても理解できていると思います!

    Excelの世界では、こんな感じで関数を組み合わせる事で面白い処理であったり、複雑な関数をいくらでも組めてしまいます!

    これからも仕事で使っていく中で出てきた産物をいくつかシェアします!


     
     
    うさぎ大好き人間&ITエンジニア アイコンはこかげ様( https://note.com/fair_lion8834 )🐰 ※ 本アカウントの投稿は所属組織とは無関係です。 ※ 今は週1投稿してます🐰

    あなたへのおすすめ