Excelの関数を簡素化する方法（質問1・2）

質問

Excelの関数で質問があります。できるだけ一つのセルに計算をまとめたいのですが、

■質問1：データの最も優勢な値をカウントする計算
例えば、下のようなデータがあったとします。（a～cはデータ項目。1～5はデータ番号。）

　　a　　b　　 c
1　50　100　 84
2　37　11　　 56
3　64　39　　 61
4　87　47　　 70
5　73　71　 　44

同じデータ番号の系列で、a～cの中で最も高い数値をカウント「1」とします。それ以外は「0」とします。
例えば、aの1・bの1・cの1、の中の最高値という具合です。
そして、最終的に「同じデータ項目の最高値のカウント数/データ番号のトータルカウント数」の値を計算します。
例えばaの場合、「同じデータ項目の最高値のカウント数」は、データ番号の3、4、5が最高値なので「3」となり、「データ番号のトータルカウント数」はデータ番号が5個あるので「5」となり、「3/5」となります。
このような計算を一つのセルにする関数はあるでしょうか？
今は、最高値のカウント数を計算するセル、
「=IF(MAX(a1:c1)=a1,1,0)」
「同じデータ項目の最高値のカウント数/データ番号のトータルカウント数」を計算するセル、
「=SUM(a1:a5)/COUNT(a1:a5)」
に分かれています。（ここでいう「a1」「c1」「a5」とは例にあるデータ項目とデータ番号のことです。）

■質問2：あるデータ区間に当てはまるデータをカウントする計算
例えば、例のa1を30～70に当てはまった場合、「1」とし、それ以外を「0」とする計算なんですが、今は
「=IF(30<=a1,IF(a1<=70,1,0),0)」
という関数を使っていますが、これ以上簡単にできませんか？

回答よろしくお願いします。

回答No.4

maron--5 · ベストアンサー

◆お役に立てばいいのですが
>=SUMPRODUCT((COUNTIF(OFFSET($A1,ROW(1:5)-1,,,4),">"&A1:A5)=0)*(A1:A5<>""))/COUNT(A1:A5)
★OFFSET関数は、　OFFSET(基準,行数,列数[,高さ][,幅])　です
★よって、回答しましたこの式はご指摘のように、「幅」ですから「3」ですね。訂正します、ごめんなさい。
◆なお、ROW(1:5)-1 の部分は、OFFSET($A1,{0;1;2;3;4},,,3)　このようにも書けます。

>COUNTIF(OFFSET($A1,ROW(1:5)-1,,,3),">"&A1:A5)
・数式バーでの式の上の部分をマウスで指定して、F9　を押してください
・{2;1;0;0;0}　と表示されます。
・これは、1行目では50,100,84　とあり、A列の50より大きい数字が2つあるという意味です。
・「0」はその数字より大きい数字がない。すなわち、その行で一番大きな数字となります。

>COUNTIF(OFFSET($A1,ROW(1:5)-1,,,3),">"&A1:A5)=0
・この部分を指定して、F9　を押してください
・{FALSE;FALSE;TRUE;TRUE;TRUE}　行のうちの最大値はTRUE表示の、3,4,5行目になります
・(A1:A5<>"")　は、A1:A5　の内、今回はありませんが未入力の行を排除しています。

>=(30<=A1)*(A1<=70)
・この式の部分も数式バーで(30<=A1)と(A1<=70)を別々に範囲指定して、F9　を押してください。
・TRUEとTRUEなら「1」、TRUEとFALSEなら「0」、FALSEとFALSEなら「0」になります
・ご推察のとおり、AND条件になります
・ついでに、*　を　+　にして同じように確認してください。OR条件になることが分かります。
・-　は確認していませんので、回答は控えさせていただきます。

お礼

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

補足

>◆なお、ROW(1:5)-1 の部分は、OFFSET($A1,{0;1;2;3;4},,,3)　このようにも書けます。

なるほど。データ番号の行数が1から5まであるので、それから-1を引いた値の間に「;」を入力し、「{}」で囲めば同じ意味になるんですね。

>・数式バーでの式の上の部分をマウスで指定して、F9　を押してください
・{2;1;0;0;0}　と表示されます。
・これは、1行目では50,100,84　とあり、A列の50より大きい数字が2つあるという意味です。
・「0」はその数字より大きい数字がない。すなわち、その行で一番大きな数字となります。

よく分かりました。「F9」を押すとその計算の結果が出るんですね。
こんな技があるとは知りませんでした。

>・この部分を指定して、F9　を押してください
・{FALSE;FALSE;TRUE;TRUE;TRUE}　行のうちの最大値はTRUE表示の、3,4,5行目になります
・(A1:A5<>"")　は、A1:A5　の内、今回はありませんが未入力の行を排除しています。

「=0」というのは「COUNTIF(OFFSET($A1,ROW(1:5)-1,,,3),">"&A1:A5)
」で計算された値「{2;1;0;0;0}」の「0」の個数をカウントするためのものなんですね。

>・この式の部分も数式バーで(30<=A1)と(A1<=70)を別々に範囲指定して、F9　を押してください。
・TRUEとTRUEなら「1」、TRUEとFALSEなら「0」、FALSEとFALSEなら「0」になります
・ご推察のとおり、AND条件になります
・ついでに、*　を　+　にして同じように確認してください。OR条件になることが分かります。
・-　は確認していませんので、回答は控えさせていただきます。

なるほど。「*」は「AND（かつ）」で「+」は「OR（または）」なんですね。
それとCOUNTIF関数の「検索条件」では必ず左側に符号（「<」など）がこなければならないんですね。
非常に分かりやすく説明していただき、ありがとうございました。大変勉強になり、これから活かしていきたいと思います。
もう一つだけ質問なんですが、a1（データ項目とデータ番号）からa5（データ項目とデータ番号）の中で30から70の値に当てはまる数値をカウントする場合、「(30<=A1)*(A1<=70)」で説明していただいた「*（AND）」を利用することはできないでしょうか？
「A1」の部分は「A1:A5」のようにデータ範囲を指定することはできないでしょうか。
今のところは、
「COUNTIF(A1:A5,">=" & 30)-COUNTIF(A1:A5,">" & 70)」
というやり方をとっているのですが、
「=COUNTIF(A1:A5,"(" & 30 & "<=" & A1:A5 & ")*(" & A1:A5 & "<=" & 70 & ")" )
とすると関数のルールに反しているようですし、正しい結果が出ません。今の計算方法が一番妥当なのでしょうか？
よろしくお願いします。

回答No.6

Wendy02

#5で回答した者です。
少し補足させてください。

直接、関係のない話ですが、こういう関数は最初はさっぱり分からずに、しつこく理屈っぽく聞いて、私は嫌われてしまいました。そこで、配列の達人さんの掲示板でお聞きしたときに、その達人さんも、やっぱり分からないし、やってみなければ分からないものがあるのだ、と教わったのです。ただ、仕様が一様ではない理由は、Microsoft の会社の、段階的な開発と、他社製の関数は、あまり手を付けない傾向にあるようです。本当に、Microsoft 社で開発したものは、そんなに多くないような気がします。基本的な、SUMとか、COUNTとかは、MS製ではありません。

>関数本来の使い方は、参考書などに載ってるけど応用というか自分がやりたいことは、そのまま載っているものではないようですからね。

最初は、人まねなのです。分からないけれども、人のをまねるうちにやり方を覚え、その内に、なんとなく、自分自身で作れるようになっているものなのですね。

特に、私は、maron--5さんのコードに出会って、本当に驚かされることが多いし、maron--5さんの数式で教わることが多いのは事実です。私のオリジナルなんていうものは皆無に等しいのです。みんな誰かが開発したワザなんです。

お礼

>直接、関係のない話ですが、こういう関数は最初はさっぱり分からずに、しつこく理屈っぽく聞いて、私は嫌われてしまいました。そこで、配列の達人さんの掲示板でお聞きしたときに、その達人さんも、やっぱり分からないし、やってみなければ分からないものがあるのだ、と教わったのです。

確かにそれは感じています。あまり何度も質問しても最後には嫌がられてしまうことがあると思います。あまり質問攻めにするのはやめようとは思っています。

>最初は、人まねなのです。分からないけれども、人のをまねるうちにやり方を覚え、その内に、なんとなく、自分自身で作れるようになっているものなのですね。

そうですね。最初は人の真似をして、だんだん覚えていって関数を自由自在に操れるようになっていくんですね。
ただ、私事ですが現状の環境では人の真似をできる状況にはないんです。事情がありまして、人と接したりお金を使うといったことが全くできない状態なので、ここでなら回答をいただいて理解を深めることができるかと思いました。
もちろん、自分で調べて吸収することは最も重要なことだとは思いますが。

補足をいただいて、ありがとうございました。

回答No.5

Wendy02

こんばんは。

私から言えることは、そう多くないですね。

あまり、理屈で考えても、解決しないのではないでしょうか？
こういうものは、経験と偶然の産物だと思います。それを一気に、理屈で覚えようと思っても無理だと思います。ひとつずつ、経験を重ねていくしかないと思います。

しかし、これは、あくまでも、実務とは関係のない頭の体操のだと思います。

>■質問1：「--」について
>マイナス×マイナスというのは分かりましたが、なぜマイナスとマイナスを掛けなければならないのか？

TRUE/FALSE を数値化するためですが、「VALUE(数字）,*1 ,-- 」の三つがあります。
レイアウト的に、今回は、「--」を選んだだけです。Excel特有のものかもしれません。

>■質問2：「">"&$A$1:$A$5」について
>最高値か判断する場所だとは思いますが、つじつまが合ってないので分からないのですが。

>■質問3：「=0」について
>なぜ、「=0」を使わなければならないのか全く分かりません。

一度、分解してみればよいです。
配列数式ではなく、個別の数式に換えてみればよいです。

=COUNTIF(A1:C1,">" &A1)

これで、0　が返るものが最大値です。

MAX という関数は、戻り値に配列がないようなので、COUNTIFによって、戻り値を分散させているだけです。繰り返しますが、こういうものは、偶然の産物で、その関数の組み立てや仕様自体は、一切公開されていませんから、実験的な繰り返しで、戻り値が戻ってくるか試してみるだけです。

お礼

なるほど。確かに自分で関数の戻り値を確認して、どのような状況で使えるか自分でやって見ることが大切ですね。
関数本来の使い方は、参考書などに載ってるけど応用というか自分がやりたいことは、そのまま載っているものではないようですからね。

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

回答No.3

maron--5

■質問2：あるデータ区間に当てはまるデータをカウントする計算
>これ以上簡単にできませんか？
◆少しは簡単になっていませんか？
=(30<=A1)*(A1<=70)

お礼

今更ですが、お礼の記載が大変遅くなり、申し訳ありません。

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

補足

回答ありがとうございます。結構分かってきたのですが補足があります。

=SUMPRODUCT((COUNTIF(OFFSET($A1,ROW(1:5)-1,,,4),">"&A1:A5)=0)*(A1:A5<>""))/COUNT(A1:A5)

OFFSET関数の「4」というのはなぜでしょうか？a、b、cとしたとき幅は「3」ではないのでしょうか？
また、「">"&A1:A5」、「=0」、「*(A1:A5<>"")」についてもちょっと意味が分かりません。

=(30<=A1)*(A1<=70)

この式は一番シンプルでいいですね。ありがとうございます。
これは、データ区間を指定するときは大なり小なりの式（<、>、<=、>=）を「()」で囲えばいいわけですね。
「()」と「()」の間の「*」は、「かつ」という意味でしょうか？
「+」や「-」で「または」や「含まない」になるということでしょうか？

恐縮ですが、もう一度回答よろしくお願いします。

回答No.2

maron--5

◆こんな方法もありますよ
■質問1：データの最も優勢な値をカウントする計算
=SUMPRODUCT((COUNTIF(OFFSET($A1,ROW(1:5)-1,,,4),">"&A1:A5)=0)*(A1:A5<>""))/COUNT(A1:A5)
★右にコピー

■質問2：あるデータ区間に当てはまるデータをカウントする計算
=SUM(COUNTIF(A1,{">=30",">70"})*{1,-1})

お礼

今更ですが、お礼の記載が大変遅くなり、申し訳ありません。

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

回答No.1

Wendy02

こんにちは。

■質問1：データの最も優勢な値をカウントする計算
ちょっとExcelの名称が使われていなかったので、戸惑ってしまいました。

行の中から最大値を出して、それが、A列にあるものを1とするということだと思います。

実務として、一行ずつすれば済みますね。まとめるというのは、もう実務的な範囲を越えていると思いました。回答者にとっては、頭の体操には良いとは思います。これは、簡単だとは思いません。

=SUMPRODUCT(--(COUNTIF(OFFSET($A$1:$A$1,ROW($A$1:$A$5)-1,,,3),">"&$A$1:$A$5)=0))/ROWS($A$1:$C$5)

このようなスタイルになると思います。ROWの中の引数は、単にインクリメントですから、かならず、A1 でも、1でも、必ず、1から始まるように作ります。割り算の商を出すのでなければ、両方の数式をつなぐのは、「式　&"/"&　式」となります。

■質問2：あるデータ区間に当てはまるデータをカウントする計算
単純に行うなら、COUNTIF 関数でよいと思います。

データ範囲を、A1:G25　として、

=COUNTIF(A1:G25,"<="&70)-COUNTIF(A1:G25,"<"&30)

もしくは、MODE関数で、たとえば、I列に区間を作って

I列
----
29
70

=FREQUENCY(A1:G25,I1:I2)
一旦式を入力したら、F2を押して、範囲を3行選択して、『ShiftとCtrlを押しながらEnterキー』を押して、再確定する。

とします。2番目のものが、その数です。

もしくは、以下のようにすれば、出るはすです。

=INDEX(FREQUENCY(A1:G25,{29;70}),2)

お礼

今更ですが、お礼の記載が大変遅くなり、申し訳ありません。

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

補足

回答ありがとうございます。大変参考になります。
SUMPRODUCT、COUNT、OFFSET、ROWの個々の関数は大体意味が分かりました。
「あるデータ区間に当てはまるデータをカウントする計算」については分かりました。
また、以下のような解釈で合ってるでしょうか？

ROW($A$1:$A$5)-1
■A1からA5まで検索し、そのときの行数の「行数目-1」の数値を返す。
解釈：これは、データ番号1から5まで検索するめですね。

OFFSET($A$1:$A$1,……,,,3)
■A1のセルを基準とし、「A1の行数目-1」の数値分、行を移動したセルから行を移動したセルから幅3個分のセルのデータを調べる。
■～～～
■A5のセルを基準とし、「A5の行数目-1」の数値分、行を移動したセルから行を移動したセルから幅3個分のセルのデータを調べる。
解釈：これは、A1からC1の中で、最高値をカウントするためのデータ範囲ですね。同様にA2からC2、……、A5からC5。

COUNTIF(OFFSET(……),">"&$A$1:$A$5)
■A1から幅3個分のセル、C1のデータの中で「">"&$A$1:$A$5」に当てはまれば「1」、それ以外は「0」を返す。
■～～～
■A5から幅3個分のセル、C5のデータの中で「">"&$A$1:$A$5」に当てはまれば「1」、それ以外は「0」を返す。
解釈：これは、A1からC1の中で「">"&$A$1:$A$5」に当てはまるものをカウントするものですね。同様にA2からC2、……、A5からC5。

また、一部理解ができないものがあるので新たに質問があります。

■質問1：「--」について
マイナス×マイナスというのは分かりましたが、なぜマイナスとマイナスを掛けなければならないのか？

■質問2：「">"&$A$1:$A$5」について
これは、A1からA5の数値を超える？という意味でしょうか？そうだとするとちょっと理解できません。
最高値か判断する場所だとは思いますが、つじつまが合ってないので分からないのですが。

■質問3：「=0」について
なぜ、「=0」を使わなければならないのか全く分かりません。

申し訳ありませんがもう一度回答よろしくお願いします。