Excel。SUMPRODUCT関数は複数の数値の組を掛け合わせて合計をする【SUMPRODUCT】

Excel。SUMPRODUCT関数は複数の数値の組を掛け合わせて合計をする

＜関数辞典：SUMPRODUCT関数＞

SUMPRODUCT関数

読み方： サムプロダクト　　

分類： 数学/三角 

SUMPRODUCT(配列1,[配列2],[配列3],…)

複数の数値の組を掛け合わせて合計を行います　

Excel。前回以上の良い数値の件数を手早く求めたい。【last time】

Excel。前回以上の良い数値の件数を手早く求めたい。

＜SUMPRODUCT関数＞

ある競技の結果表があります。

1回目よりも2回目の成績がいい件数は何件あるのかを求めたい。

このような場合、D列とかに、IF関数をつかって、2回目の値が大きければ、○とかを表示させて、その結果を数えるという方法で、求めたりします。

それでもいいのですが、SUMPRODUCT関数だけで、求めることができます。

では、C9にSUMPRODUCT関数をつかった数式を設定します。

C9に設定した数式は、

=SUMPRODUCT((B2:B7<C2:C7)*1)

これだけで、3件と算出することができました。

SUMPRODUCT関数は、「総和」を求めることができるSUM関数と、乗算のPRODUCT関数が合わさった関数です。

行ごとに、B2:B7<C2:C7の条件が成立しているならば、TRUE。

成立していなければ、FALSEと算出されます。

TRUEとFALSEでは、合算することができません。

Excelでは、TRUEが1で、FALSEが0と定義されています。

そこで「×1」することで、数値化することができます。

TRUEは1となります。

この値を合算することで、2回目の方が大きい件数を求めることができるという仕組みです。

Excel。同じ列内のOR条件件数を、手早く数えるには、どうしたらいいの【count】

Excel。同じ列内のOR条件件数を、手早く数えるには、どうしたらいいの

＜SUMPRODUCT関数＞

複数条件で件数を求めるには、COUNTIFS関数をつかいますが、このCOUNTIFS関数は、別々の列内の条件ならば対応しています。

商品名が、ボールペンで　色が、赤　とかならば、COUNTIFS関数で対応できるというわけです。

しかし、今回求めたいのは、色が　赤　か　青　の件数です。

この場合、単一条件で件数を求めることができる、COUNTIF関数で、一つずつ、求めてた後に、合算すると求めることができます。

では、

F4をクリックして、COUNTIF関数をつかった数式を設定します。

=COUNTIF(C2:C11,E2)+COUNTIF(C2:C11,F2)

これで、4件と算出されました。

問題は無いといえば、無いのですが、この条件が増えれば、増えるたびに、COUNTIF関数の数式が増えていくわけです。

となると、かなり面倒ですし、条件が10あれば、COUNTIF関数も10必要な訳ですから、可読性も悪化します。

そこで、次のような方法もあります。

その方法は、SUMPRODUCT関数をつかいます。

では、F4の数式を削除して、改めて、SUMPRODUCT関数をつかった数式を設定していきます。

=SUMPRODUCT((C2:C11=E2:F2)*1)

これで、算出結果は先ほどのCOUNTIF関数と同じ、4件と算出されています。

もし条件が増えたとしても、E2:F2の範囲選択が広がるだけで、対応することができます。

それでは、数式を確認しておきましょう。

SUMPRODUCT関数は、掛けた結果を合計する関数で、配列数式の関数です。

内部的に配列処理を行って計算しています。

C2:C11のデータが、E2:F2。

すなわち、赤か青と合致していたら、TRUEを合致していなければFALSEを返してくれます。

TRUEやFALSEを算出してくれても、内部的に合計はできません。

そこで、「*1」することで、TRUEは1に、FALSEは0と数値化してくれるので、あとは合計することで、件数を求めることができるというわけです。

Excel。SUMPRODUCT関数なら、前回よりも数値がいい件数を一発で算出できます。【Second time】

Excel。SUMPRODUCT関数なら、前回よりも数値がいい件数を一発で算出できます。

＜SUMPRODUCT関数＞

数値が1回目よりも2回目の方がいい件数を算出したい場合、SUMPRODUCT関数をつかえば、途中計算がなくても、一発で算出することができます。

次の表をつかって、確認します。

B列に1回目、C列に2回目に得点が入力されています。

2回目の得点が大きい件数を知りたいわけです。

その件数を算出してあるのが、C9です。

C9の数式は、

=SUMPRODUCT((B2:B7<C2:C7)*1)

たった、これだけで、2回目のほうが大きい件数を算出することができます。

IF関数をつかって、2回目の方が大きいのかを算出させて、その件数を数えるというのが、普通思い浮かべるやり方だと思います。

これを、SUMPRODUCT関数だけで手早く算出することができます。

この数式を説明します。

SUMPRODUCT関数は、総和を算出するSUM関数と、乗算のPRODUCT関数が合体した関数です。

D列に2回目が大きいかわかるように、B2<C2という数式を作成して、オートフィルで数式をコピーします。

成立していれば「TRUE」。

不成立なら「FALSE」が表示されます。

TRUE・FALSEではわかりにくいので、「×1」します。

1を乗算することで、数値に変更することができます。

TRUEなら「1」。

FALSEなら「0」と数値にすることができます。

この数値を合算すれば、2回目のほうが大きい件数を算出することができます。

D列・E列を算出しないで、手早く算出できるというわけです。

Excelの様々な関数の読み方や引数などを紹介。今回は、SUM関数～SUMSQ関数です。【dictionary】

Excelの様々な関数の読み方や引数などを紹介。今回は、SUM関数～SUMSQ関数です。

＜Excel関数辞典：VOL.77＞

今回は、SUM関数～SUMSQ関数までをご紹介しております。

SUM関数

読み方： サム　　

分類： 数学/三角 

SUM(数値1,[数値2],…)

数値の合計します　

SUMIF関数

読み方： サムイフ　　

分類： 数学/三角 

SUMIF(範囲,検索条件,[合計範囲])

条件付きで数値の合計を行います　

SUMIFS関数

読み方： サムイフズ

読み方： サムイフエス

分類： 数学/三角 

SUMIFS(合計対象範囲,条件範囲1,条件1,…)

複数の条件付きで数値の合計を行います　

SUMPRODUCT関数

読み方： サムプロダクト　　

分類： 数学/三角 

SUMPRODUCT(配列1,[配列2],[配列3],…)

複数の数値の組を掛け合わせて合計を行います　

SUMSQ関数

読み方： サムスクウェア　　

分類： 数学/三角 

SUMSQ(数値1,[数値2],…)

数値の2乗の合計を算出します　

Excel。複数列置きのデータを手早く合算するには、どうしたらいいの【Column total】

Excel。複数列置きのデータを手早く合算するには、どうしたらいいの

＜SUMPRODUCT+(MOD+COLUMN関数＞

連続しているデータならば、SUM関数で簡単に合計値を算出できますが、2列おきにあるデータの合計値を算出したい場合、どのようにしたら手早く算出することができるのでしょうか。

次の帳票の場合で説明していきます。

B列とE列のそれぞれの販売金額の合計をH列に算出したいわけです。

このケースのように2か所ならば、Ctrlキーをつかうことで、容易に範囲選択でるので算出すること自体面倒というわけでもありません。

ただ、さらに多くのデータだった場合は、数式を作るのも面倒になっていきます。

このような場合、何かしらの「法則」がないのかが見つかれば、数式を作成するヒントになります。

今回は、合計したい列が、2列置きにあります。

2列置きの数値だけを合算する方法を考えいけばいいということになります。

そこで、SUMPRODUCT関数をつかうことで、解決することができます。

H3には、SUMPRODUCT関数をつかった数式を設定しました。

=SUMPRODUCT((MOD(COLUMN(B3:G3),3)=2)*B3:G3)

オートフィルで数式をコピーしています。

この数式で2列置きの数値を合算することができたわけですが、どのような仕組みなのかを説明していきます。

SUMPRODUCT関数は、PRODUCT＝「掛け算」とSUM＝「総和」が組み合わさった関数です。

MOD関数は、除算した余りを算出する関数です。何の余りを算出するのかというと、COLUMN関数。つまり列番号を除算するわけですね。

「MOD(COLUMN(B3:G3),3)」

今回は、3で列番号を除算した余りを算出させるわけです。

その結果が、

「MOD(COLUMN(B3:G3),3)=2」

2と等しいのかとします。

B列は、余り2ということで合致しますから、「TRUE」となるわけです。合致しなければ「FALSE」となるわけです。

Excelでは、「TRUE」は「1」で「FALSE」が「0」となっていますから、その値を、セルに入力されている値と乗算「*B3:G3」します。

すると、販売金額の列以外は、「0」に置換されるので、販売金額だけを合計することができるというわけです。

ただ、ちょっと動きがわかりにくいので、このような数式を確認するには、数式タブにある「数式の検証」をつかってみましょう。

 

途中計算が視覚として理解することができます。

Excel。1列おきごとの合計を楽に算出するには、どうしたらいいの【every other row】

Excel。1列おきごとの合計を楽に算出するには、どうしたらいいの

＜SUMPRODUCT+MOD+COLUMN関数＞

Excelは基本的に上から下へ流れていくテーブル（表）でつくれば、様々なExcelの機能を使うことができます。

ただ、どうしても帳票と同じように表をつくってしまうと、簡単に算出できない場合があります。

例えば、次の表。

来店客数と売上高が一組になったデータが列方向に拡張されている表。

このような表、帳票としてはいいのですが、単純に、来店客数の合計や、売上高の合計を算出する場合、1列おきで範囲を設定する必要があります。

要するに、列が増えれば増えるほど、面倒な作業というわけです。

そこで、来店客数の合計B8には次の数式を設定することで、手早く算出することができます。

=SUMPRODUCT((MOD(COLUMN($B$3:$G$5),2)=0)*$B$3:$G$5)

また、売上高の合計C8には、次の数式を設定してあります。

=SUMPRODUCT((MOD(COLUMN($B$3:$G$5),2)=1)*$B$3:$G$5)

数式の引数を確認しましょう。

最初のSUMPRODUCT関数ですが、SUMは、和算。

PRODUCTは乗算で、乗算した結果を和算する関数です。

そして引数のMOD関数とCOLUMN関数は何をやっているのかというと、1列おきで範囲選択したいわけです。

1列おきということは、列番号をつかって、偶数か奇数なのかを判定させればいいわけです。

MOD関数は、除算した、あまりを算出する関数です。

また、COLUMN関数は列番号を算出する関数です。

よって「MOD(COLUMN($B$3:$G$5),2)」で、列番号を2で除算するという数式ですから、結果「0」だったら余りが0ということで、偶数列ということがわかります。

「MOD(COLUMN($B$3:$G$5),2)=0」と「=0」とすれば、「MOD関数の結果が0と等しいか」と判断させています。

「等しい」ならば「TRUE」、「等しくない」ならば「FALSE」と判定されます。

「TRUE=1」で「FALSE=0」とExcelでは定義されていますから、「MOD(COLUMN($B$3:$G$5),2)=0」が成立しているならば、偶数列は「1」。

奇数列は「0」と算出されるわけです。

ここで、SUMPRODUCT関数の出番。

「*$B$3:$G$5」と乗算していますが、偶数の「1」を掛ければ、その値は残り、奇数の「0」を掛ければ、「0」となるわけです。

その結果を和算すれば、偶数列のみの合計値を算出できるというわけです。

1列おきとか1行おきとかで、合計を算出したい場合にはSUMPRODUCT関数をつかってみるといいかもしれませんね。

Excel。手早く一行おきで合計値を算出したいけど、どうしたらいいの。【Total】

Excel。手早く一行おきで合計値を算出したいけど、どうしたらいいの。

＜SUMPRODUCT+MOD+ROW関数＞

帳票をExcelにそのまま移行した場合、算出する方法は簡単でも、それをどのように数式として表現したらいいのか、難しくなることがあります。

例えば次の表。

B列の売上高の合計を一行おきに算出したいというのが、目的です。

なんで一行おきなのかというと、2行1組になっていて、1行目が2022年で2行目が2023年になっているというわけです。

年のフィールド（列）があれば、SUMIF関数がつかえるのですが、条件につかえる列がないので、つかえません。

SUM関数で、一行おきに、範囲選択するしかないのでしょうか。

それでは、ミスも発生する可能性が上がってしまうし、何よりも面倒です。

そこで、SUMPRODUCT関数をつかうことで、手早く算出することができます。

F1に次の数式を設定します。

=SUMPRODUCT((MOD(ROW($B$2:$B$7),2)=0)*$B$2:$B$7)

F2には、次の数式を設定します。

=SUMPRODUCT((MOD(ROW($B$2:$B$7),2)=1)*$B$2:$B$7)

この数式で、算出することができるわけですが、少し複雑な数式なので、説明をします。

SUMPRODUCT関数は、「積の結果」を合計する関数です。

余計な計算列を作らなくて一発で、算出したい時に使うと便利な関数です。

SUMPRODUCT関数の引数から確認していきます。

C1に、

=MOD(ROW(C2:C7),2)=0

と引数のところを抽出してみました。スピル機能で、C7まで算出されています。

ROW関数は、行番号を算出する関数です。C2の行番号は「2」です。

MOD関数は、除算した余りを算出しますので、ROW関数で算出した結果を2で除算した余りを算出しています。

C2は、2÷2なので、余り「0（ゼロ）」です。

その結果が「0」と等しいならば「TRUE」と算出され、等しくなければ「FALSE」と算出される仕組みになっています。

算出結果をみると、TRUEとFALSEが一行おきになっていることがわかります。

これで、一行おきという条件に対応することができるというわけです。

また、Excelは、「TRUE」が1で「FALSE」が0と設定されています。

PRODUCTは掛け算を意味しますので、売上高にFALSE=0を掛ければ「0」になるので、TRUEのところだけをSUMするので、一行おきに合計値を算出できます。

いままで複雑で、いくつか計算列を経由して算出していた数値も、今回使用した、SUMPRODUCT関数をつかうと、コンパクトになるかもしれませんので、つかえないか検討してみるのもいいかもしれませんね。

Excel。何らかの重み付けした平均である「加重平均」を算出する関数ってあるの？【weighted average】

Excel。何らかの重み付けした平均である「加重平均」を算出する関数ってあるの？

＜SUMPRODUCT関数・SUM関数＞

「平均」といえば、AVERAGE関数と思うかもしれませんが、平均には色々な種類の平均があります。

オートSUMボタンにある、お馴染みのAVERAGE関数は、平均を算出しますが、単なる平均ではなくて、「算術平均」とか「相加平均」といったりします。

Excelには、

上位と下位から一定の割合を除外した平均を算出する、TRIMMEAN関数。

成長率などの倍率の平均を算出する、GEOMEAN関数。

時速の平均などの単位当たりの数値の平均を算出する、HARMEAN関数。

というように、色々な平均を算出する関数が用意されているのです、残念ながら、何らかの重み付けした平均である「加重平均」を算出するための関数はありません。

なぜ、加重平均が必要なのか、次の表を使って確認していきましょう。

ある商品の販売価格と販売数の表です。

仙台店は、セール期間ということで割引して販売したところ、割引の効果もあって、販売数が伸びているのがわかります。

そして、B列の販売価格の平均値を算出するにあたり、お馴染みの平均を算出するAVERAGE関数をつかって、B6に算出してみたのがこの表です。

販売価格の平均ではあるのですが、販売数は全く考慮していません。

しかしよく考えてみると、

新宿店の販売金額は、1500×477= 715500

仙台店の販売金額は、 700×1587 1110900

平均は、合算値をそのデータ件数で除算するわけです。

ということから、販売数を考慮したほうが合理的ですね。

安い販売価格の販売数は、高い販売価格よりも多いので全体としては、平均値は下がるはずです。

このような時につかうのが、「加重平均」です。

「加重平均」を算出するには、販売数を重み付けして、販売価格に積算してからその総和を販売数の総和で除算することで算出することができます。

このことから、今回使用する関数は、SUMPRODUCT関数とSUM関数で算出していきます。

B7に「加重平均」を算出していきます。

最初に、販売価格に積算してからその総和をするために、SUMPRODUCT関数をつかった数式を作っていきます。

そして、SUM関数で販売数の合算値で除算するわけですから、

B7の数式は、

=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)

とします。

算出結果は、1052。

AVERAGE関数で算出した数値よりも、予想通り下がる結果となりましたね。

ご覧のように、加重平均を一発で算出するための関数は今のところありませんが、SUMPRODUCT関数とSUM関数を使うことで、算出することができますので、会議資料などで加重平均が必要な時には、覚えておくと便利かもしれませんね。

最近いろいろな関数が追加されているので、もしかしたら、登場するかもしれませんね。

Excel。前回よりも数値がいい件数を求めるのに簡単な方法はないの？【SUMPRODUCT】

Excel。前回よりも数値がいい件数を求めるのに簡単な方法はないの？

＜SUMPRODUCT関数＞

やりたいことは簡単そうなのに、意外と手間暇がかかったりする帳票というが現場にはあったりします。

例えば、次の表。

B列の1回目の得点とC列の2回目の得点を比べて、2回目の得点のほうが高い人は何人いるのか算出したいとします。

考え方として、B列とC列を比べて、C列のほうが高ければ、「○」と表示させて、その「○」の数を数えるという方法が、思いつきます。

この場合、D2の数式は、

=IF(B2<C2,”○”,””)

とすれば、いいわけです。

そのあとに、COUNTIF関数をつかって「○」を数えれば算出できます。

注意しないといえないのは、COUNTA関数では、算出結果の空白「””」も数える対象になってしまうので、COUNTIF関数を使う必要があります。

F1にそのCOUNTIF関数の数式をつくるとしたら、

=COUNTIF(D2:D11,"○")

と設定します。

COUNTIF関数をつかって件数を数えてもいいのですが、件数が知りたいわけなので、「○」ではなくて、「1」にすれば、SUM関数で合算値を算出するほうが、もっと楽になるわけですね。

=IF(B2<C2,1,””)

このように、判定結果を数値にして、SUM関数で合算することで件数を求めるという方法も楽なのですが、もっと楽に算出する方法があるのです。

それができるのが「SUMPRODUCT関数」です。

このSUMPRODUCT関数を使えば、D列のような一時的に算出させる列やセルを用意しないで、一発で算出することができます。

まずは、F1にSUMPRODUCT関数をつかった数式を設定してみましょう。

=SUMPRODUCT((B2:B11<C2:C11)*1)

確認してみましょう。

先程と同じように、算出されたことが確認できました。

数式について、説明していきます。

SUMPRODUCT関数は、SUM関数+PRODUCT関数の二つの関数が一つになった関数で、SUM関数は、和算。

PRODUCT関数は、乗算の関数です。

これを踏まえたうえで、

引数の(B2:B11<C2:C11)を説明してきます。

これは、B2<C2～B11<C11まで成立しているか否かを算出させています。

実際にD列をつかって、算出してみます。

条件が満たされていれば「TRUE」。そうでなければ「FALSE」が算出されます。

この「TRUE」「FALSE」に「*1」すると、数値に変換することができます。

なぜ、「1」と「0」なのかというと、Excelでは、TRUEが「1」。

FALSEが「0」と設定されているからです。

この結果を合計したのが、F1の値というわけです。

SUMPRODUCT関数は、メジャーというほどの関数ではないかもしれませんが、ちょっと知っていると、意外と使える関数なのかもしれませんね。

今回のSUMPRODUCT関数のように、意外と知っていると便利という関数が、それぞれの現場であるかと思いますので、探して、試してみるといいかもしれませんね。

Excel。SUMPRODUCT関数は便利と聞くけど、どんな時につかうの？【SUMPRODUCT】

Excel。SUMPRODUCT関数は便利と聞くけど、どんな時につかうの？

＜SUMPRODUCT関数＞

SUMPRODUCT関数は、SUM＝足し算とPRODUCT＝掛け算を組み合わせた関数なのですが、どのような時につかったらいいのかを今回はご紹介します。

次の表をつかって、説明します。

 

A1:B6の表は生年月日を管理している表です。

この中から、8月生まれの人が何名いるのかを算出している表です。

なお、8月生まれと入力されている、D1には、ユーザー定義の書式を設定してあります。

 

これは、SUMPRODUCT関数で使用するためです。

さて、どうのようにしたら、8月の誕生日の人を算出したらいいのでしょうか？

考え方としては、誕生月を算出して、その件数を数えれば、8月の誕生日の人が何名いるのかを算出することができます。

もし、SUMPRODUCT関数をつかわないとしたら、2段階方式で算出することになります。

たとえば、MONTH関数をつかって、誕生月を抽出させます。

 

C2には、

=MONTH(B2)

というMONTH関数をつかって、誕生日から誕生月を算出させています。

C6までオートフィルで数式をコピーしています。

そして、D2の数式は、

=COUNTIF(C2:C6,D1)

条件を指定して件数を求めるには、COUNTIF関数をつかいます。

 

範囲には、C2:C6

検索条件には、D1

D1の数値を変えることで、別の月の件数を容易に算出することができます。

ここで使用するために、D1には、表示形式を設定したわけです。

8月生まれの人を結果的には算出することができたので、目的は達成することができたわけですが、数式を2つ作らないといけないわけですね。

今回は、算出できる列があったので、よかったのですが、資料によっては、難しい場合もあります。

ところが、SUMPRODUCT関数をつかうと、一発で算出することができます。

SUMPRODUCT関数をつかって作成した数式は、

=SUMPRODUCT((MONTH(B2:B6)=D1)*1)

です。

 

SUMPRODUCT関数の引数は、配列という形で、範囲を設定していきます。

パッと見た目、「？」になってしまうので、この数式について、解説していきます。

最初の

MONTH(B2:B6)=D1

ですが、この数式だけの結果を算出してみます。

 

C2には、

=MONTH(B2:B6)=D1

と設定しております。

範囲に、絶対参照が必要なのではと思われるかもしれませんが、Office365の「Office Insider」版には、『スピル』という機能が加わったことにより、絶対参照は不要です。

なお、通常のExcelでは、絶対参照が必要になりますので、数式は、

MONTH($B$2:$B$6)=D1

と設定します。

 

条件と合致しているならば、「TRUE」を算出し、合致していないと「FALSE」を算出しますが、このままでは、結局件数を数えなければなりません。

そこで、「×1」をこの式に追加します。

 

「TRUE」や「FALSE」に「×1」すると数値に変更することができます。

ちなみに、Excelでは、TRUEを1。FALSEを0と設定されています。

ここで、算出された結果の合計値が、「２」と算出される仕組みなのが、SUMPRODUCT関数です。

Excel。一覧表に重複除いて何人いるの？目視確認では大変なんです。【Deduplication count】

Excel。一覧表に重複除いて何人いるの？目視確認では大変なんです。

＜COUNTIF関数・IFERROR関数・SUMPRODUCT関数＞

複数のスタッフで店舗を回している一覧表があります。

今回知りたいことは、重複しているスタッフを除いて、いったい何名のスタッフで店舗を回しているのかを確認したいわけです。

一列だったらば、重複を除く方法は色々あるのですが、今回のように、縦横の表になってしまっていると、うまくいきません。

当然、人間による、『目視』なんて、大変以外の何物でもありません。

では、どうやったらいいのでしょうか？
数式一発で算出する方法もありますが、知っている関数を使って算出してきます。

最初に算出するのは、そのスタッフが何回登場しているのかを算出していきます。

F2にCOUNTIF関数をつかって数式を作っていきますので、COUNTIF関数ダイアログボックスを表示しましょう。

範囲には、$B$2:$D$4　と設定します。
範囲は固定しておきたいので、絶対参照を忘れずに設定します。

検索条件には、B2　と設定します。
あとは、オートフィルで数式をコピーします。

F2の数式は、
=COUNTIF($B$2:$D$4,B2)

するとこのような結果になりました。

1でないところが、重複しているわけですね。

2だったら、1というような条件を付けた場合、場合によっては3ということも想定されるので、2だったらというような固定的な考え方では対応できません。

ここからがアイディア。

仮に、全部のデータが1だとしたら、データ全部を合算すれば、人数が算出されますよね。

だったら、2のところを、0.5にすれば、データ全部を合算すればいいわけです。

仮に4だったら、0.25にすればいいわけです。

そのようにするには、算出された値を「1」で除算すればいいわけです。
つまり、
=1/F2という式を作ればいいので、その隣に、改めて表をつくります。

このデータ全部を合算したら、6となるわけです。
つまり6名で3店舗を回していることがわかります。

算出することはできましたが、途中計算を出すのを繰り返すのは、ちょっとスマートではありません。

そこで、COUNTIF関数を使う方法もあります。

F2の数式を次のように変更してみました。
=IFERROR(1/COUNTIF($B$2:$D$4,B2),0)
オートフィルで数式をコピーした結果が次の通りです。

ついでなので、式をまとめただけでなく、空白だった場合エラーになってしまう欠点もIFERROR関数を使って防いでいます。

どうしても、現場ではExcelにとって都合のいい表ばかりではありませんので、様々アイディアを投入して解決ことになりますね。

ちなみに、一発で算出する場合には、SUMPRODUCT関数を使う方法もあります。

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

ただ、SUMPRODUCT関数はあまり、なじみがないのと、配列関数なので、ちょっとわかりにくいところがありますね。

Excel。会員の誕生月ごとの人数を算出したい。【Birthday Month】

Excel。会員の誕生月ごとの人数を算出したい。

＜SUMPRODUCT関数＆MONTH関数とCOUNTIF関数＞

会員が誕生月を迎えたらDMやメールを送りたいけど、その人数を事前に把握しておきたいなど、日付から月ごとの件数を把握するにはどうしたらいいのでしょうか？

次のような顧客名簿があります。

B列には日付が入力されていて、1月だったら何名という結果をF列に算出したいわけです。
最初に、E列の誕生月を説明しておきます。

1月と表示されていますが、表示形式のユーザー定義をつかって、「月」を表示させているだけです。

セルの値は、1から12の数値が入力されています。

今回は、単純にG/標準のうしろに、"月"を追記しています。

最初に紹介する方法は、手間はかかりますが、わかりやすいかと思う方法です。

【MONTH関数とCOUNTIF関数】

日付の状態では何月なのかがわかりませんので、C列に月だけを算出していきます。

日付を抽出するのは、MONTH関数を使えばOKですね。

C2をクリックして、数式を入力していきます。

=MONTH(B2)
あとは、オートフィルを使って数式をコピーします。

これで、月を抽出することができましたので、F列に月ごとに何件あるのか、COUNTIF関数を使って算出していきます。

F2をクリックして、COUNTIF関数ダイアログボックスを表示します。

範囲には、$C$2:$C$16と設定します。オートフィルを使って数式をコピーしますので、絶対参照を設定しておきます。

検索条件には、E2を設定します。
仮にE2に「1月」と入力していると、合致しないので件数を算出することができません。

そのために、先程、表示形式のユーザー定義を使って「～月」と設定したわけです。

このように、月ごとの件数を算出することができました。
しかしこの方法は、一度月を算出させる必要があるので、わかりやすい反面、少々面倒です。

【SUMPRODUCT関数＆MONTH関数】

一発で算出する方法もあることはあるのですが、ちょっとヤヤコシイ。
G2に次の数式を作成します。

=SUMPRODUCT((MONTH($B$2:$B$16)=E2)*1)

オートフィルを使って数式をコピーしてみると、同じ結果になります。

数式を説明してきます。

SUMPRODUCT関数は、範囲の積を合計した結果を算出する関数なのですが、この説明は後回しとして、引数で使用している、MONTH関数から説明していきます。

I2に引数のところだけで数式をつくって確認してみましょう。

B2:B16のそれぞれのデータが、E3すなわち、2月と合致しているのかを算出しているのがI列です。

合致しているなら、「TRUE」。合致していないなら「FALSE」と算出されるわけです。

ここまでが、MONTH($B$2:$B$16)の処理。

なぜ、MONTH($B$2:$B$16)に「×1」しているのかというと、TRUEやFALSEのままでは合算することができないので、「×1」をすると、TRUEは、1。FALSEは、0と算出されます。

それが、J列。
あとは、SUMPRODUCT関数をつかうことで、この範囲を合算してくれるという仕組みです。

ということで、
=SUMPRODUCT((MONTH($B$2:$B$16)=E2)*1)
という数式でも、算出することができます。

Excel。シート間のデータの違いを見つけて、マークをつけたい【Difference in data】

Excel。シート間のデータの違いを見つけて、マークをつけたい

＜条件付き書式とIF+SUMPRODUCT関数＞

訂正前というシートに次のようなデータがあります。

そして、訂正後というシートに次のようなデータがあります。

訂正前と訂正後で、どのレコードが修正されたのかが、
わかるように、H列に「修正あり」と表示したいとします。

その場合はどのようにしたらいいのでしょうか？

当然、目視で確認するというのでは、大変なことになります。

数値ならば、別シートなどに、
例えば、=訂正前!C2－訂正後!C2　という数式を作り、
その結果が、0（ゼロ）だったならば、
修正していないということがわかりますが、

文字の場合だと、IF関数を使わないといけないといけませんし、
データ量が膨大になると、数式のコピーも大変な作業となってしまいます。

そこで、次のようにしていくには、どのようにしたらいいのでしょうか？

H列に「修正あり」と表示することができました。

こうすることで、オートフィルターなどで、
「修正あり」という文字だけを抽出してあげれば、
どのデータが変わったのかが分かりやすくなります。

では、H2の数式はどのようになっているのかを先にご紹介します。

=IF(SUMPRODUCT((訂正前!C2:G2=訂正後!C2:G2)*1)=5,"","修正あり")

IF+SUMPRODUCT関数のネストになっています。

SUMPRODUCT関数は、
掛け算をして、その合計を算出する関数ですが、
この数式がどのような動きをしているのかを確認したいので、
4行目に空白行を用意して、
合計しないで掛け算だけを算出するPRODUCT関数を使って、算出してみます。

C4の数式は、
=PRODUCT(訂正前!C3=訂正後!C3)

合致していると、TRUEなので、1を返します。
合致しないとFALSEなので0を返します。

この算出された数値を合算すると、4になりますよね。

全部合致していれば、5なので、4と算出されていれば、
合致していない箇所。

すなわち、「修正あり」ということがわかるという仕組みです。

なので、掛け算と合計を求める、SUMPRODUCT関数を使っています。

そして、条件式が一つで、掛け算の値を合計しないで、
セルの数を数える場合は、×1とすると、
0と1の数値に変換した数式を作成する必要がありますので、×1しています。

=IF(SUMPRODUCT((訂正前!C2:G2=訂正後!C2:G2)*1)=5,"","修正あり")

という数式を使うことで、比較することができます。

なお、視覚的にわかるようにするだけならば、
条件付き書式を使うと、簡単に比較することが出来ますよ。

C2:G5を範囲選択して、条件付き書式の新しいルールを選択します。

数式を使用して、書式設定をするセルを決定をクリックして、
数式には、
=訂正前!C2<>訂正後!C2
として、書式を設定してOKボタンをクリックしましょう。

このように、変更したセルを見つけたい場合には、
条件付き書式を使うといいですね。