EXCEL関数について

質問

EXCEL関数について

EXCEL2003を使用しております。
お知恵を拝借願います。

Sheet1、A列、A2～A100セルに「1～10」の数字がランダムに入力されています。
数字は「グループ1」、「グループ2」・・・「グループ10」という意味です。

Sheet1、B列、A2～A100セルに「1～20」及び、「OK」、「NG」がランダムに入力されています。

Sheet2、A列、A2～A21セルに「1～10」までの数字（グループ）が順に入力されており、
Sheet2、1行目、B1～W1セルに、「1～20」及び「OK」、「NG」が順に入力されております。

ここで、Sheet2のB2セルに、Sheet1にて「グループ1」は
「1」をいくつ選択しているかを返す式を入れたいのですが、
SUMIF？COUNTIF？等でいろいろと試してみたのですが、うまくいきません。

同じように、C2セルであれば、「グループ1」は「2」をいくつ選んでいるか。
W5であれば、「グループ4」は「NG」をいくつ選んでいるかの式を入力したいのです。

拙文乱文、大変恐縮ですが、ご教示頂きたく何卒宜しくお願い致します。

回答No.4

kagakusuki · ベストアンサー

私も最初、

=SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!$B$2:$B$100=B$1))

という数式を自力で作成して試した際に、#N/Aエラーが出たため、投稿する事を控えていたのですが、色々試行錯誤している内に、数式中のSheet2の$A2とB$1の部分を、一旦、1に変えて

=SUMPRODUCT((Sheet1!$A$2:$A$100=1)*(Sheet1!$B$2:$B$100=1))

という数式にしてから、Sheet2のB2～W11の範囲に貼り付けた後、B2セルに入力した数式を再度

=SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!$B$2:$B$100=B$1))

に入力し直してから、ドラッグ機能で他のセルにコピーした処、何故か正常にカウントする様になりました。
　本来は、数式中の$A2とB$1の部分を、一旦、1に変えてから、再度元に戻す操作は、単なる二度手間であり、何の意味も無い操作の筈なのですから、何故それで正常にカウントする様になるのかは全く解りませんし、質問者様のパソコンで同じ操作を行ったからといって、エラーではなくなる保証もありませんが、駄目元で試してみられては如何でしょうか。

　尚、どうしても駄目だった場合には、SUMPRODUCT関数を使用する方法と比べればスマートではありませんが、作業列を設けるという方法もあります。
　今仮に、Sheet3のA列を作業列に使用するものとします。
　まず、Sheet3のA2セルに次の数式を入力して下さい。

=Sheet!1$A2&"-"&Sheet!1$B2

　次に、Sheet3のA2セルをコピーして、Sheet3のA3～A100の範囲に貼り付けて下さい。
　そして、Sheet2のB2セルに次の数式を入力して下さい。

=COUNTIF(Sheet3!$A:$A,"="&$A2&"-"&B$1)

　続いて、Sheet2のB2セルをコピーして、Sheet2のB2～W11の範囲に貼り付けて下さい。
　以上の方法でも、同様にカウントする事が出来ます。

お礼

ご回答有難うございます。

kagakusuki様の「二度手間式」で試してみたのですが、
私のPCでは残念ながら反映されませんでした。

Sheet3、作業列を設けての方法で作成したところ、
無事カウントすることが出来ました！
助かりました。本当有難うございます。

貴重なお時間を割いてのご回答、誠に感謝致しております。

また機会がありましたら宜しくお願い致します。

回答No.3

KURUMITO

シート2のＢ2セルには次の式を入力し、Ｗ2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=SUMPRODUCT((Sheet1!$A$2:$A$1000=$A2)*(Sheet1!$B$2:$B$1000=B$1))

補足

早速のご回答有難うございます。

コピペさせて頂いたのですが、なぜか「#N/A」になります。
列、入力セル等間違いありません。

どうしてでしょうか・・・？

回答No.2

grumpy_the_dwarf

たとえばSheet2!B2には、「Sheet1!$A$1:$A$100が$A2に等しく、か
つ、Sheet1!$B$1:$B$100がB$1に等しい」もののカウントが欲しいの
ですね。複数条件なので、2003ではsumproductを使うのがセオリー
です。sumproduct関数の中身は上に書いたとおりなので、
=sumproduct((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$B$1:$B$100=B$1))
これだけ。絶対参照や相対参照の違いは、簡単にドラッグコピーで
W11まで複製するための手管です。

2007以降なら、countifs関数あたりでもっと直感的に扱えるように
なっているはずです。

補足

早速のご回答有難うございます。

コピペさせて頂いたのですが、なぜか「#N/A」になります。
列、入力セル等間違いありません。

どうしてでしょうか・・・？

回答No.1

NMZ1985

Sheet2からSheet1を参照する時に
=COUNTIF(Sheet1!A1:A22,1)

と記述することで
Sheet1のA1:A22の範囲にある1の数
を取得することができます。

お礼

ご回答有難うございました。