エクセル・関数、数える対象

質問

●シート１で出席人数表を作っています。
●数えてくるもとのデータは隣のシート、２行目からにあります。
●「SUMPRODUCT」で「Ａ2からＡ100」の「出席で１班で女」を数えています。
●5行目の子どもを削除したくて、行の削除をしたら　数えてくる対象が「Ａ99」までに自動的に変わってしまいます。

対象はいつも「Ａ2～Ａ100」までにしたいです。子どもを削っても足しても、対象を変えたくないのですが、良い方法はないでしょうか？

回答No.7

NIWAKA_0 · ベストアンサー

エクセルの範囲参照は、最初のセルから最後のセルまで、という見方をしているようです。
例えばＡ２：Ａ１００という範囲だったら、　Ａ２セルとＡ１００セルを監視（？）しています。
コレが挿入や切り取り貼付けなどでずれたりすると、自動で追跡・修正してくれます。
ただし、コピーの場合は修正されません。

また、９９行目（Ａ９９）を削除しても式がＡ２：Ａ９９と修正されるだけですが、
１００行目（Ａ１００）を削除するとエラーとなります。

以上を踏まえて。
・表は多めに作っておきます。範囲の目いっぱいデータを入れない、
　範囲指定をＡ２：Ａ１０１などにしておき、１０１行目は常に隠しておく、と良いです。
・行を削除する場合、削除した行数分だけ行を最終行の上に追加しておく。
　　例えば、範囲指定をＡ２：Ａ１０１としていて、Ａ５～Ａ７を削除する場合
　　(1)Ａ５～Ａ７を削除　（式中の範囲指定がＡ２：Ａ９８に変化）
　　(2)Ａ９７とＡ９８の間に３行挿入。
　　　　または、９８行目（＝元の最終行）を[コピー]→[コピーしたセルを挿入]
　　　２つ目のやり方なら、なにか式が入っていてもコピーされます。

　　これで式中の範囲指定はＡ２：Ａ１００に戻っているハズ。

・行を挿入する場合は上と逆です。
　挿入したあと、挿入した分だけ削除して帳尻を合わせます。

[切り取り]→[貼付け]と[コピー]→[貼付け]の違いは理解しておいた方が良いです。

で、ご質問の「出席で１班で女」ですが、
私ならばＤ列にでも集計用の欄を増やして、
=A2&B2&C2
とし、これを最終行までコピーします。
すると、「出席１班女」というようになるはずです。

で、集計のほうでは
=COUNTIF($D$2:$D$100,"出席１班女")
とすれば数えられるはずです。

さらに、例えばＡ１０２セルに「出席」、Ｂ１０２に「１班」、Ｃ１０２セルに「女」と入力して
=COUNTIF($D$2:$D$100,A102&B102&C102)
とすれば、１班じゃなく２班を数えたい、となってもＢ１０２を２班に変えるだけですからカンタンです。

お礼

ありがとうございました。エクセルの特徴みたいなものを説明していただいて、エクセル初級者にはすご～く参考になりました。こうやって説明していただくと、いろいろ工夫できて他のことにも応用できそうです。

回答No.10

wisemac21

範囲に名前の定義して
名前を「出欠」　範囲　=INDIRECT("データ!A2:A100")
　　　「班」　　範囲　=INDIRECT("データ!B2:B100")
　　　「性別」　範囲　=INDIRECT("データ!C2:C100")

=SUMPRODUCT((出欠="出席")*(班="1班")*(性別="女"))

お礼

エクセルって奥が深いのですね。いろいろなアイデアを教えていただけて、本当に嬉しいです。ありがとうございました。

回答No.9

Nouble

A2:A100とせず
A:Aじゃ駄目なのですか？
これだといくら削除しても変わりません

但しエクセル２００２(xpかも)以降でないと対応してませんが

お礼

そんなのありなんですか？　私のパソコンもＸＰです。この方法も勉強してみたいと思います。ありがとうございました。

回答No.8

noname#204879

「隣のシート」のＡ列、Ｂ列、Ｃ列がそれぞれ出欠、班名、性別の項目列であると仮定して、セル A2 に ref という名前を付けておけば、「シート１」に次式を入力してみてください。
=SUMPRODUCT((OFFSET(ref,1,0,99)="出席")*(OFFSET(ref,1,1,99)="１班")*(OFFSET(ref,1,2,99)="女"))

「5行目の子どもを削除したくて、行の削除をし」ても「数えてくる対象」は２行目から１００行目と変わらないはずです。

お礼

ありがとうございます。いろいろな方法があるのですね。勉強になります！

回答No.6

taocat

いやはや、4度目の登場なり～。
ダブルコーティションを抜かしてました。(^^;;;
ま、結論として最初の回答の　Sheet2　を　データ　に替えるだけですが・・・

=SUMPRODUCT(
　(　INDIRECT(　"データ!$A$2:$A$100"　)="出席"　)*
　(　INDIRECT(　"データ!$B$2:$B$100"　)="１班"　)*
　(　INDIRECT(　"データ!$C$2:$C$100"　)="女"　　)
)

今度はＯＫ。(^^;;;
以上。

お礼

ありがとうございました！　教えていただいた式で上手くいきました。勉強になりましたm(__)m。

回答No.5

taocat

３度目の登場
No4の回答、ミスあり。右括弧が足りませんでした。

=SUMPRODUCT(
　(　INDIRECT(　データ!$A$2:$A$100　)="出席"　)*
　(　INDIRECT(　データ!$B$2:$B$100　)="１班"　)*
　(　INDIRECT(　データ!$C$2:$C$100　)="女"　　)
)

以上です。

回答No.4

taocat

ANo3,再度の登場です。(^^;;

＞式エラーになってしまいます

こういう場合は、そのエラーになった式をアップした方がどこそこが間違ってるよ、と指摘できますよね。(^^;;;

=SUMPRODUCT(
　(　INDIRECT(　データ!$A$2:$A$100="出席"　)*
　(　INDIRECT(　データ!$B$2:$B$100="１班"　)*
　(　INDIRECT(　データ!$C$2:$C$100="女"　　)
)

尚、スペースは分かりやすくする為に入れています。
以上です。

回答No.3

taocat

こんにちは。
そういう場合は、INDIRECT関数を使う方法もあります。

例えば、女性の出席者カウント

=SUMPRODUCT(
　(　INDIRECT("Sheet2!A2:A10")="女性"　)*
　(　INDIRECT("Sheet2!B2:B10")="出席"　)
　)

以上です。

補足

ありがとうございます。早速試してみたのですが、データが隣のシートにある場合はINDIRECTをどこに入れれば良いのでしょうか？　実は、関数式の作り方さえ良く解っていなくて、「挿入」機能で関数式をエクセルに作ってもらっている程度なので、ちょっと手がこんでくると、どこにつければ良いのか解らなくて、式エラーになってしまいます。お手数かけてすみませんが、教えていただけるとありがたいです。

回答No.2

ryuujiok2205

「$Ａ$2～$Ａ$100」のように「$」をつければ式のセル位置に関わらず「いつも「Ａ2～Ａ100」まで」になります。

補足

ご回答ありがとうございました。実はそれかなと思って、試してみたことがあるのです。でも、やっぱり99に変わってしまいました。どこが間違っているのでしょうか？　もしかして、隣のシートなので、他にも「$」を付けるべきところがありますか？

SUMPRODUCT(('データ'!$A$2:$A$100="出席")*('データ'!$B$2:$B$100="１班")*('データ'!$C$2:$C$100="女"))

回答No.1

noname#22067

「A2:A100」を
「$A$2:$A$100」としてみればどうでしょう？
行の挿入や削除をしても変わらないはずです。

補足

ご回答ありがとうございました。実はそれかなと思って、試してみたことがあるのです。でも、やっぱり99に変わってしまいました。どこが間違っているのでしょうか？　もしかして、隣のシートなので、他にも「$」を付けるべきところがありますか？

SUMPRODUCT(('データ'!$A$2:$A$100="出席")*('データ'!$B$2:$B$100="１班")*('データ'!$C$2:$C$100="女"))