進路希望の志望別人数

質問

中学校の教師をしております。
３年生の進路希望を集計しているのですが、以下のように希望をとって入力しています。

　　　公立第一希望　公立第二希望　私立第一希望　私立第二希望
Ａ男　　　１０１　　　　１０２　　　　１０３　　　　１０４
Ｂ男　　　１０２　　　　２０１　　　　５０１　　　　１０５
　　　　　　　　　　　　　・
　　　　　　　　　　　　　・
　　　　　　　　　　　　　・

学校名は、コードを振って整理しています。上の数字は、コード番号です。

そこで、希望ごとに人数を集計したいのですが、高校に振ったコードが２００以上もあるため、
　　(1)希望のある学校のみ、抽出してカウントしたい
　　(2)公立第一、公立第二、私立第一、私立第二それぞれで希望している人数をカウントしたい

　　　　　　　　　第１希望　　　　　第２希望　　　
Ａ校（１０１）　　　４　　　　　　　　　１　　　　　　　
Ｂ校（１０２）　　　０　　　　　　　　　３

Countifでは行数が増えすぎて使いにくい。DCOUNTでも作業が面倒走なので・・・。

いい方法がありましたら、是非教えていただきたいのです。

よろしくお願いします。

回答No.1

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

ピボットテーブルで各希望ごとの集計ならできそうです
添付図参照

お礼

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

まさに、自分がやりたかったことです！ピボットテーブルも試してみたのですが、設定の方法が間違えていたんですね。

本当に助かりました！感謝します。

回答No.5

noname#204879

質問を理解するのにお示しのようなサンプル表は必須ですが、書いたからには矛盾しないようにお願いしたいものです。回答者に対してそのくらいの気遣いはしてください。
最初の表では「公立第一希望」に１０２があるのに、２番目の表では「Ｂ校（１０２）」が０となっています。「何じゃこりゃ！」です。

それはさておき、ピボットテーブルで作成したものが下記のようになればよろしいのでしょうか？尤も、これを得るためには最初の表を加工するか、根本的に作り直す必要がありますが。

データの個数/志望校　　　　　優先順
公私　　　　　　　　 志望校　　　 1　　　 2　総計
公　　　　　　　　　 101　　　　　4　　　 1　　 5
　　　　　　　　　　 102　　　　　1　　　 3　　 4
　　　　　　　　　　 201　　　　　3　　　 4　　 7
　　　　　　　　　　 202　　　　　1　　　 1　　 2
私　　　　　　　　　 103　　　　　3　　　　　　 3
　　　　　　　　　　 104　　　　　　　　　3　　 3
　　　　　　　　　　 105　　　　　1　　　 2　　 3
　　　　　　　　　　 501　　　　　2　　　 2　　 4
　　　　　　　　　　 502　　　　　3　　　 2　　 5
総計　　　　　　　　　　　　　　 18　　　18　　36

回答No.4

KURUMITO

ごめんなさい。シート2でのＣ2セルへの入力の式は次のようにしてください。
=IF($B2="","",COUNTIF(Sheet1!B:B,$B2))

回答No.3

KURUMITO

COUNTIF関数でどうして行数が増えて困るのでしょう。
シート1にお示しの第1の表があるとします。
Ａ2セルから下方に氏名が、Ｂ2セルから下方に公立第1希望が、Ｃ2セルから下方に公立第2希望が・・・・
シート２にお求めの表を作るとしてＡ2セルから下方にＡ校などの名前が、Ｂ2セルから下方にコード番号が、Ｃ1セルからＦ1セルまでには公立第１希望・・・私立第2希望までの文字が入力されているとします。
Ｃ2セルには次の式を入力しＦ2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグすればよいでしょう。
=IF(B2="","",COUNTIF(Sheet1!B:B,$B2))

回答No.2

tom04

こんばんは！
すでに回答は出ていますが・・・
他の方法の一例です。

無理矢理関数を使ってやってみました。

学校コードに基づいて第1希望・第2希望を集計するようにしています。
尚、学校コードを抽出する際に重複を避けるために↓の画像のように
A・B列を作業列とさせてもらっています。
（作業列が目障りであれば、数式を入れた後に、非表示にしてみてください。）

A3セルに（配列数式になります）
=IF(COUNTA($E$3:$H$1000)>=ROW(A1),INDEX($E$3:$H$1000,IF(MOD(ROW(A1),COUNTA($E$3:$E$1000))=0,COUNTA($E$3:$E$1000),MOD(ROW(A1),COUNTA($E$3:$E$1000))),INT((ROW(A1)+1)/COUNTA($E$3:$E$1000))),"")

という数式を入れていますので、この画面から数式をコピー＆ペーストした後に、F2キーを押すか、数式バー内を一度クリックして
編集可能にします。
そして、Shift＋Ctrl＋Enterキーを押してみてください。
数式の前後に｛　｝マークが入り配列数式になります。

そして、B3セルに
=IF(COUNTIF($A$3:A3,A3)=1,ROW(A1),"")
という数式を入れ、A3・B3セルを範囲指定した後に、
B3セルのオートフィルハンドルで下へずぃ～～～！とコピーします。
とりあえず1000行まで対応できる数式にしていますので
そのくらいまでコピーしてもかまいません。

そして、K2セルに
=IF(COUNT($B$3:$B$1000)>=ROW(A1),INDEX($A$3:$A$1000,SMALL($B$3:$B$1000,ROW(A1))),"")
L2セルに
=IF($K2="","",COUNTIF(E$3:E$1000,$K2)+COUNTIF(G$3:G$1000,$K2))
とし、L2セルの数式をM2セルまでオートフィルでコピーします

最後に、K2～M2セルを範囲指定し
M2セルのオートフィルハンドルでこれまた、下へずぃ！ずぃ～～！っと
コピーすると↓の画像のような感じの表になります。
（学校コードのみ参照し、学校名は無視しています）

尚、学校コードは元データの左上から新出順に表示されますので、
ちょっと、希望とは食い違うかもしれませんね。

以上、参考になれば幸いですが、
他に良い方法があれば読み流してくださいね。
そして、たぶん画像が小さくて判りづらいと思います。

どうも長々と失礼しました。m(__)m

お礼

ありがとうございました。
配列数式は難しくて、避けて通っていたところです。

勉強しなければいけませんね。

貴重な時間を割いていただいて、ありがとうございました。