vlookupの集計について

質問

困っています。どなたかよろしくお願いいたします。

例えとして、学校の進路希望のデータを作っているとします。

生徒のデータ（通番・氏名）を入力し、別シートに学校のデータ
（学校コード番号・学校名）を入力しており、vlookupで生徒の
シートには手打ちで学校のコード番号を入力して、受験する
学校の情報を管理しています。

今、別シートに学校（私立・公立など）ごとに表が存在していて
そこには学校ごとに何名が受験するのか、合否などの項目が
あるのですが、今は手打ちで生徒の通番を入力しています。

この作業を省略し、生徒のデータに受験校のコード番号を
打ち込むだけで、学校ごとの表に生徒データが出てくるような
表にするにはどうすればよいでしょうか？

説明がわかっていただけるかどうか不安ですが、どなたか
お知恵をかしてください。よろしくお願いいたします。

回答No.4

imogasi · ベストアンサー

この質問は表現がまずくて判りにくい。
主語や対象が１文の中で変わり、読むほうは混乱する。
例　＞別シートに学校のデータ（学校コード番号・学校名）を入力しており、vlookupで生徒の
シートには手打ちで学校のコード番号を入力して・・
ーー
エクセル利用では「シートとセル」の利用方法の学習なのに、質問者はその意識が薄く、実例も挙げずに質問している。
ーー
下記に整理するから、下記で誤って居るかみて、誤り個所は正して、質問に表現しそれを踏まえて、再質問したらどうかな。
整理すると
（１）Ｓｈｅｅｔ１　　受験生徒シート（仮称）
ある生徒で２校受験はありえると思うが、
生徒ごとにまとまっているのか、ばらばらか？
１行は１生徒かつ１受験か？？
受験生徒番号　入力
受験生徒氏名　入力
受験学校コード　入力
受験学校情報　　ＶＬＯＯＫＵＰでＳｈｅｅｔ２？？から項目データを索引
（２）Ｓｈｅｅｔ２、（仮称　受験学校シート）　一般的公開学校情報シート？
学校番号
学校名
諸情報
（３）Ｓｈｅｅｔ３、Ｓｈｅｅｔ４、・・・（仮称　受験＋合否集積シート）
学校ごとに別シートらしい（常識的にはシート名を受験校各々の１学校名にするとか）
学校番号
学校名
何名本校受験ーーしかし誰が受験した（予定か）か、記録を残したいのでは。合計数で良いとは思えない。
何名本校合格ーー同上
ーー
質問で言いたいことは
Ｓｈｅｅｔ１の入力からＳｈｅｅｔ３、Ｓｈｅｅｔ４・・のシートへ連動（転記）したいのだろう。
＝＝＝
ここでエクセル関数で難しいのは、Ｓｈｅｅｔ１で入力したデータを、各々の学校受験シートに持って行くことが難しいのだ。
そういうＳｈｅｅｔ１から各シートへ「投げる（振り分ける）イメージのこと」は難しいのだ。
だから各シート側でそのシートに割り当てた学校のシート側で、Ｓｈｅｅｔ１から自校当てのデータを抜き出してくるほうが、まだ可能性はある。
その場合に、学校別の受験＋合否集積シートの各シートに関数を設定しないとならない。
式複写と言うわけには行かないので１工夫要る。
ーー
一方でエクセル関数で、条件（この場合以受験校を指定して）をつけてデータを抜き出してくる式を作るのは、相当エクセル関数の習熟者で無いと難しい。
エクセル関数での抜き出し問題は、Ｇｏｏｇｌｅで「imogasi方式」で照会すれば、沢山質問例と複数タイプの回答が出るし、回答者側で苦労のほどがわかるだろう。。
ーーー
私は仕事（業務）をエクセルで処理するなら、最低エクセルＶＢＡが必須と思っている。質問者はＶＢＡが出来ないようだから
無理だと思う。
むしろフリーソフトが無いか探すほうが良い。
本来本件はソフト会社に頼む課題だろう。その場合、最低でもＭＳアクセスなどデータベースで処理すると思う。
ーー
関数では受験校が増えると、シートとそこの関数式を増やすと言う作業なども発生して大変。
ーー
生徒データを入力して瞬時のデータ完成はあきらめて、１日データ入力終了や２－３日や１週間区切りや必要の都度集計
で（バッチ処理と言う）、我慢して
（１）Ｓｈｅｅｔ１に生徒受験データを１シートに上行から下へ詰めて入力
（２）Ｓｈｅｅｔ２に各列に受験校を割り当て、フィルタオプションの設定で下に該当者を表示する
（３）合否発表後において、覚醒との合否の入力は受験を記録した行を探し、合否項目列に入力する手間に耐えられるか。
自動で探して、合否をセットするよう、便利にするにはここでもシステム（ＶＢＡなど）の力が要るが。
＝＝
諸集計は上記のことが固まってから、関数式を考えることだ。
表題の
＞vlookupの集計について
なんて、質問の内容実態を現してないと思うよ。

お礼

回答ありがとうございました。
私のつたない説明から　よくぞ読み取っていただき
感謝いたします。

(1)Ｓｈｅｅｔ１　　受験生徒シート（仮称）
１行は１生徒で、
受験生徒番号　入力
受験生徒氏名　入力
受験学校コード　入力
受験学校情報　　ＶＬＯＯＫＵＰでＳｈｅｅｔ２から項目データを索引
(2)Ｓｈｅｅｔ２　　受験学校シート（仮称）　
学校番号
学校名
諸情報

(1)(2)はほとんど合っています。問題は↓
(3)Ｓｈｅｅｔ３、Ｓｈｅｅｔ４、・・・　受験＋合否集積シート（仮称）
なのですが、現在の形は私立・公立や専願か否かなどの諸条件によって
シートが分かれています。

imogasiさんのおっしゃる通り、私はVBAはできませんし、ご指摘のように
＜受験校が増えると、関数式を増やすと言う作業なども発生して大変＞
なので、今回はお手上げとしました。

質問の表現がわかりにくく、本当に申し訳ありませんでした。
もっとエクセルの勉強をして頑張りたいと思います。
また機会があればよろしくお願いいたします。

回答No.3

guchi_yama

”生徒データ”表や”学校データ”表がどういう構成なのか、縦割りなのか横割りなのか、
ハタマタ、”別シートの学校（私立・公立など）”表がどう存在しているのかが
さっぱり分からなかった為、私なりに勝手にサンプル表を作ってみましたので
参考になれば使って下さい。
また、不明点などあれば補足頂ければ回答したいと思います。

生徒名簿（生徒データ）
通番：連番フィル
氏名：手打ち
学校コード：手打ち
学校名：
D2=VLOOKUP(C2,大学一覧!$A$2:$B$33,2,0)　　　D3以降下へフィル

大学一覧（学校データ）※高校名は地域性があるので大学名でサンプルを作成しています
コード番号：手打ち
学校名：手打ち

国立大学一覧表（別シートの学校）
大学名：手打ち　※大学一覧から国立、私立、短大　などのフラグが立っているなら
　　　　　　　　　　　　VLOOKUPで自動表記可能）
受験者氏名1人目：
B2=INDIRECT("生徒名簿!B"&MATCH(A2,生徒名簿!$D$1:$D$21,0))　　　B3以降下へフィル
受験者氏名2人目：
C2=IF($H2>=COLUMN()-1,INDIRECT("生徒名簿!$B$"&MATCH($A2,INDIRECT("生徒名簿!$D$"&MATCH(B2,生徒名簿!$B$1:$B$21,0)+1&":$D$21"),0)+MATCH(B2,生徒名簿!$B$1:$B$21,0)),"")　　　D2及びC3以降　右下へフィル
受験者計：
H2=COUNTIF(生徒名簿!D2:D24,A2)　　　H3以降下へフィル

以上

お礼

回答ありがとうございます。
質問の表現がわかりにくく、申し訳ありませんでした。
サンプルまで作っていただき、感謝いたします。

なるほど この方法であれば、受験者名一覧はできますね。
ありがとうございました。

もっとエクセルの勉強をして頑張りたいと思います。
また機会があればよろしくお願いいたします。

回答No.2

KURUMITO

例えば生徒のデータがシート１にあるとしてA列には通番がB列には氏名が、それぞれ２行目から下方に入力されているとします。右横の列のC列には学校のコード番号が、D列には学校名が、E列には学校のコード番号が、F列には学校名がのように、２列を使って学校のコード番号と学校名は表示されるようになっているとします。
そこでコード番号を入力すれば学校名が表示されるようにするためには次のようにします。
その前にシート２では学校のコード番号と学校名が次のように入力されているとします。
A１セルにはコード番号、A２セルには学校名、A３セルには受験者数の文字をそれぞれ入力し、コード番号や学校名はB列から横に入力するとします。なお、学校のコード番号は数値ではなく文字列の形で入力します。例えば公立の学校では１A、２Aなどと、私立では１B,２Bなどと入力します。
そこでシート１で学校のコード番号をC列やE列に入力したときにD列やF列に学校名を表示されるためにはD２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(C2="","",HLOOKUP(C2,Sheet2!$1:$1000,2,FALSE))

F２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(E2="","",HLOOKUP(E2,Sheet2!$1:$1000,2,FALSE))

他の学校を表示させる列についても同様にします。
これでシート１では学校のコード番号を入力することで学校名を表示させることができます。ところでこれらの入力したデータを受けてシート２に生徒の通番を表示させるためには、シート１に作業列を設けて対応することにします。実際には学校のコード番号や学校名を表示させるための列は多くなることでしょうが、ここではJ列までが使われるとしてK列から右横の列を作業列とします。
K１セルから右の列にはシート２で使用されている学校のコード番号を入力します。
そのうえでK２セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。

=IF(OR($A2="",K$1=""),"",IF(COUNTIF($C2:$J2,K$1)=0,"",MAX(K$1:K1)+1))

シート２ではB３セルには次の式を入力して右横方向にオートフィルドラッグします。

=IF(B$1="","",COUNTIF(Sheet1!$C:$J,B$1))

次に４行目から下方にはそれぞれの学校への受験者の通番を表示させるのですが、そのためにはB４セルに次の式を入力し右横方向にオートフィルドラッグコピーしたのちに下方向にもオートフィルドラッグします。

=IF(OR(B$1="",ROW(A1)>COUNTA(Sheet1!$B:$B)-2),"",IF(COUNTIF(INDEX(Sheet1!$A:$O,1,MATCH(B$1,Sheet1!$A$1:$O$1,0)):INDEX(Sheet1!$A:$O,1000,MATCH(B$1,Sheet1!$A$1:$O$1,0)),ROW(A1))=0,"",INDEX(Sheet1!$A:$A,MATCH(ROW(A1),INDEX(Sheet1!$A:$O,1,MATCH(B$1,Sheet1!$A$1:$O$1,0)):INDEX(Sheet1!$A:$O,1000,MATCH(B$1,Sheet1!$A$1:$O$1,0)),0))))

式はシート１での作業列がO列までになっているのでそのようにしていますが、OをたとえばXXに変えても何ら問題はありません。一度ここに述べたようにシートを作って試験してみてください。

回答No.1

MackyNo1

同じ受験校のコード番号が複数入力されている生徒の一覧を出すにはVLOOKUP関数だけでは表示できません。

このようなケースでは、複雑な配列数式を使う必要がありますので、生徒データと、最終的に表示したい学校ごとの生徒データのシートのレイアウトを具体的に例示されたほうがよいと思います。