• ベストアンサー

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)」 という関数を使っていますが、これ以上簡単にできませんか? 回答よろしくお願いします。

質問者が選んだベストアンサー

  • ベストアンサー
  • maron--5
  • ベストアンサー率36% (321/877)
回答No.4

◆お役に立てばいいのですが >=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条件になることが分かります。 ・- は確認していませんので、回答は控えさせていただきます。

miya_HN
質問者

お礼

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

miya_HN
質問者

補足

>◆なお、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 & ")" ) とすると関数のルールに反しているようですし、正しい結果が出ません。今の計算方法が一番妥当なのでしょうか? よろしくお願いします。

その他の回答 (5)

  • Wendy02
  • ベストアンサー率57% (3570/6232)
回答No.6

#5で回答した者です。 少し補足させてください。 直接、関係のない話ですが、こういう関数は最初はさっぱり分からずに、しつこく理屈っぽく聞いて、私は嫌われてしまいました。そこで、配列の達人さんの掲示板でお聞きしたときに、その達人さんも、やっぱり分からないし、やってみなければ分からないものがあるのだ、と教わったのです。ただ、仕様が一様ではない理由は、Microsoft の会社の、段階的な開発と、他社製の関数は、あまり手を付けない傾向にあるようです。本当に、Microsoft 社で開発したものは、そんなに多くないような気がします。基本的な、SUMとか、COUNTとかは、MS製ではありません。 >関数本来の使い方は、参考書などに載ってるけど応用というか自分がやりたいことは、そのまま載っているものではないようですからね。 最初は、人まねなのです。分からないけれども、人のをまねるうちにやり方を覚え、その内に、なんとなく、自分自身で作れるようになっているものなのですね。 特に、私は、maron--5さんのコードに出会って、本当に驚かされることが多いし、maron--5さんの数式で教わることが多いのは事実です。私のオリジナルなんていうものは皆無に等しいのです。みんな誰かが開発したワザなんです。

miya_HN
質問者

お礼

>直接、関係のない話ですが、こういう関数は最初はさっぱり分からずに、しつこく理屈っぽく聞いて、私は嫌われてしまいました。そこで、配列の達人さんの掲示板でお聞きしたときに、その達人さんも、やっぱり分からないし、やってみなければ分からないものがあるのだ、と教わったのです。 確かにそれは感じています。あまり何度も質問しても最後には嫌がられてしまうことがあると思います。あまり質問攻めにするのはやめようとは思っています。 >最初は、人まねなのです。分からないけれども、人のをまねるうちにやり方を覚え、その内に、なんとなく、自分自身で作れるようになっているものなのですね。 そうですね。最初は人の真似をして、だんだん覚えていって関数を自由自在に操れるようになっていくんですね。 ただ、私事ですが現状の環境では人の真似をできる状況にはないんです。事情がありまして、人と接したりお金を使うといったことが全くできない状態なので、ここでなら回答をいただいて理解を深めることができるかと思いました。 もちろん、自分で調べて吸収することは最も重要なことだとは思いますが。 補足をいただいて、ありがとうございました。

  • Wendy02
  • ベストアンサー率57% (3570/6232)
回答No.5

こんばんは。 私から言えることは、そう多くないですね。 あまり、理屈で考えても、解決しないのではないでしょうか? こういうものは、経験と偶然の産物だと思います。それを一気に、理屈で覚えようと思っても無理だと思います。ひとつずつ、経験を重ねていくしかないと思います。 しかし、これは、あくまでも、実務とは関係のない頭の体操のだと思います。 >■質問1:「--」について >マイナス×マイナスというのは分かりましたが、なぜマイナスとマイナスを掛けなければならないのか? TRUE/FALSE を数値化するためですが、「VALUE(数字),*1 ,-- 」の三つがあります。 レイアウト的に、今回は、「--」を選んだだけです。Excel特有のものかもしれません。 >■質問2:「">"&$A$1:$A$5」について >最高値か判断する場所だとは思いますが、つじつまが合ってないので分からないのですが。 >■質問3:「=0」について >なぜ、「=0」を使わなければならないのか全く分かりません。 一度、分解してみればよいです。 配列数式ではなく、個別の数式に換えてみればよいです。 =COUNTIF(A1:C1,">" &A1) これで、0 が返るものが最大値です。 MAX という関数は、戻り値に配列がないようなので、COUNTIFによって、戻り値を分散させているだけです。繰り返しますが、こういうものは、偶然の産物で、その関数の組み立てや仕様自体は、一切公開されていませんから、実験的な繰り返しで、戻り値が戻ってくるか試してみるだけです。

miya_HN
質問者

お礼

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

  • maron--5
  • ベストアンサー率36% (321/877)
回答No.3

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

miya_HN
質問者

お礼

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

miya_HN
質問者

補足

回答ありがとうございます。結構分かってきたのですが補足があります。 =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) この式は一番シンプルでいいですね。ありがとうございます。 これは、データ区間を指定するときは大なり小なりの式(<、>、<=、>=)を「()」で囲えばいいわけですね。 「()」と「()」の間の「*」は、「かつ」という意味でしょうか? 「+」や「-」で「または」や「含まない」になるということでしょうか? 恐縮ですが、もう一度回答よろしくお願いします。

  • maron--5
  • ベストアンサー率36% (321/877)
回答No.2

◆こんな方法もありますよ ■質問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})

miya_HN
質問者

お礼

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

  • Wendy02
  • ベストアンサー率57% (3570/6232)
回答No.1

こんにちは。 ■質問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)

miya_HN
質問者

お礼

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

miya_HN
質問者

補足

回答ありがとうございます。大変参考になります。 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」を使わなければならないのか全く分かりません。 申し訳ありませんがもう一度回答よろしくお願いします。

関連するQ&A

  • Excel関数:データの中から最高値をカウントしてその割合を求める方法

    例えば、   A B  C 1 50 25 50 2 40 80 20 3 0  0  0 4 30 45 45 5 90 25 35 このようなデータがあったとします。ここで1の列の最高値、2の列の最高値というように見ていきます。 ただし、最高値が複数ある場合、A(最も優先度が高い)←→C(最も優先度が低い)とし、同じ列の中でカウントされるのは1つだけにします。 また、0は除きます(総数からも省く)。 例の場合、1を見るとAとCが最高値ですが優先度はAの方が高いため、Aがカウントされます。 2は、B、3はカウントなし、4はBとCが最高値ですがBの方が優先度が高いためBがカウントされます。 このようにカウントしていって、「Aのカウント数/総数」のような計算をして、2/4となります。 同じようにBやCも計算し、パーセントで表します。 結果は、  A B C 6 % % % というように、計算式や関数はできるだけ少ないセルに収めたいのですが(計算式は「A6」「B6」「C6」の部分)、このような方法はあるでしょうか? 回答よろしくお願いします。

  • Excel関数:最高値をカウントして総数を求める計算方法

    例えば、以下のようなデータがあったとします。   A   B   C 1 50  50  25 2 80  80  80 3 45  50  60 4 35  70  45 5 50  40  30 このデータから1から5行目まで見て、「A」「B」「C」の中で最高値をカウントします。 「1」の場合、最高値は「A」と「B」になります。 「2」の場合、最高値は「A」「B」「C」全てになります。 「3」の場合、最高値は「C」になります。 これをカウント数にすると、   A   B   C 1  1   1   0 2  1   1   1 3  0   0   1 4  0   1   0 5  1   0   0 となり、カウントの総数は「8」となります。 このカウントの総数を一つのセルで計算する方法を教えてください。 関数は詳しくないのでまた質問するかもしれませんが、解説もよろしくお願いします。 回答よろしくお願いします。

  • エクセル関数について

    現在エクセル関数を勉強しだしたところですが、 一つのセルに条件付の計算式をいれたい場合に どうすればよいか困っています。 たとえば、A,B,Cという数値が1行に並んでおり、 A、B、Cの数をカウントして、合計のセルに (Aの数×5)+(Bの数×4)+(Cの数×3) という答えを関数で出したい場合はどうすれば よいのでしょうか? 本やヘルプで調べてもいまいちわかりません。 IF関数を使う? 初心者なので詳しく教えていただけたらと 思います。

  • Excel関数:Excel関数:0を除いたデータから最高値をカウントして優勢率を求める計算方法

    例えば、以下のようなデータがあったとします。   A   B   C 1 50  50  25 2 80  80  80 3 45  50  60 4 35  70  45 5  0   0   0 このデータから1から5行目まで見て、「A」「B」「C」の中で最高値をカウントします。 「1」の場合、最高値は「A」と「B」になります。 「2」の場合、最高値は「A」「B」「C」全てになります。 「3」の場合、最高値は「C」になります。 「5」の場合、データ「0」なので除きます これをカウント数にすると、   A   B   C 1  1   1   0 2  1   1   1 3  0   0   1 4  0   1   0 となり、カウントの総数は「7」となります。 優勢率を求めるのに「Aの優勢率=Aのカウント数/カウントの総数」で、3/7となります。 結果、見た目では、   A     B     C 1 50    50    25 2 80    80    80 3 45    50    60 4 35    70    45 5  0     0     0 6 42.8%   42.8%  28.5% このようになります。このような見た目にしたいのですが、 条件は、 ・0を除いたデータ ・「A6」「B6」「C6」以外は計算式や関数を使わない ・1から5は実際のデータでは1から1000以上になることもある ということです。 関数は詳しくないのでまた質問するかもしれませんが、解説もよろしくお願いします。 回答よろしくお願いします。

  • EXCEL 関数の質問です。

    A1セルに1もしくは2があり、 1のときB1セルにある値をD1に引いてきます。 2のときC1セルにある値をD1に引いてきます。 D1にセットする関数を知りたいです。 IF関数ですと、1とその他になってしまいうまくいきません。 もしA1が1であるならばB1を、もしA1が2であるならばC1をセットする関数を教えてください。

  • エクセル 理論関数について。

    エクセル理論関数の質問です  a1セルの値-10・10・10・-10  b1セルの値50・-50・50・-50 上記のような数が変動します、a1・b1セルを参照させて数幅を計算させたいのですが?。 例1 a1セルに-10とb1セルに50の幅は=abs(a1)+abs(b1)=60 例2 a1セル10・b1セル50の場合の幅は40 例3 a1セル-10・b1セル-50の場合は-40 c1セルに上記のような数値になる理論式を入力したいのでっすが、上手にできません、教えて頂けないでしょうか?

  • EXCEL、文字の数をカウントしたい。

    エクセル関数を使って、文字(100文字以上)の数をカウントしたいのですが、可能でしょうか。 例えば:セルに一つずつ、ABCAAACDを入力した場合に、A=4、B=1、C=2、D=1 のように表示したいんです。 ※Aを数える場合に、IF関数を使ってAのとき1となるように IF(A1="A",1)+IF(A2="A",1)+・・・+・・・のように順次足していけば短い文字であれば可能ですが、文字数が多くなってくると、セル内に関数がはいりきらずに計算できなくなります。別の関数をつかってより簡単に計算できないでしょうか?

  • エクセル(excel)の計算式(関数)について

    エクセル(excel)の計算式(関数)でよいアイディアがありましたら教えてください。 1行目は項目行です。 セルA1から右に15列=セルO1まで、 a | b | c | d | e | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 と入っています。 2行目からデータとして、 セルA2から右に5列=セルE2まで、 4 | 2 | 2 | 1 | 1 と入力したとします。(データ例(1)) あるいは、 セルA3から右にセルE3まで、 6 | 3 | 0 | 0 | 0 と入力したとします。(データ例(2)) 1つのデータの5個の数字のルールは2つで、 「合計で10以下である。」 「左から順に小さくなるか、同じ数字となる。」 です。 (目的は、) このとき、F列からO列にかけて、 データ例(1)のケースでは、 a | a | a | a | b | b | c | c | d | e データ例(2)のケースでは、 a | a | a | a | a | a | b | b | b | と表示されるように、 つまり、項目行の下にある数だけ、その列の1行目の記号を 1(F列)から右に向かって順に埋めていくような、 F列からO列までの2行目以下に入れる適当な計算式(関数)は ないでしょうか。 拙い説明で申し訳ありません。どなたかよい考えをお持ちの方がいらっしゃいましたらと存じます。 どうぞよろしくお願い致します。

  • エクセル 2003 関数

    エクセル 2003 関数 お世話様です。 XPのエクセル2003にてセルA1に数値の3を入れております。 これをセルC5の文字間に関数値として挿入する事は可能でしょうか? 例: セルA1に数字の3、セルC5に【カウントです。】と入力されていたら    【カウント3です。】となります。 セルA1の数値によってセルC5の入力内容が変わるようにしたいのですが 可能でしょうか? お手数ですが、ご存知の方がいましたらご教授下さい。 以上、何卒宜しくお願い致します。

  • エクセル 関数で出た値を別の関数で使う場合

    エクセルでのセルである関数で出た値が出ています。 別のセルでそこに出ている値を使って関数を入れたいのですが、その方法がわかりません。 例えば  あるセルにAと入っています。しかし、それはある関数の結果として出ています。別のせるに =if(A4=A,3,if(A4=B,2,if(A4=C,1,"")))と入れています。 でも結果は空欄です。どうしたらいいのでしょうか?

専門家に質問してみよう