エクセルで指定文字列の列全体を抽出する方法

エクセルの集計についての質問です。初心者のため簡単な関数しか使えません。出来ればVBAは用いずに関数を用いて解決できればと思います。

いくつかの施設から同じフォーマットのデータを集めて集計を行うのですが、列の情報が施設によって異なります。そのため、列の文字列によって列全体の情報を抽出することは可能でしょうか？

例)各科目のA判定のみ集計
⇒A～D組は各施設によって構成が異なるため、”C組”という文字列を認識して東高校ならD:D列を
西高校ならC:C列をCOUNTIFなどでA判定の集計したいと思っています。よろしくお願いします。

東高校
　　A　　　B　　C　　D　
１　科目　A組　B組　C組　　　　　　
２　国語　A　　B　　A
３　数学　B　　A 　 C
４　理科　A　　A　　A

西高校
　　A　　　B　　C　　D　
１　科目　A組　C組　D組　　　　　　
２　国語　A　　B　　A
３　数学　B　　A 　 A
４　理科　A　　A　　A


集計
　　　A　　　B　　　C　　　D　　　E
１　科目　　A組　　B組　　C組　　D組
２　国語　　２　　　０　　　１　　　１
３　数学　　０　　　１　　　１　　　１
４　理科　　２　　　１　　　２　　　１

No.1 ベストアンサー 回答者： 銀鱗 回答日時： 2016/05/25 08:50

MATCH関数とINDEX関数の組み合わせで可能です。

MATCH関数は指定した文字列が配列の何番目に出てきたのかを返します。
INDEX関数は指定した範囲内の行と列を指定して値を返します。

とは言えこの場合は、MATCH関数とOFFSET関数を使ったほうがよいと思います。
OFFSET関数は範囲を返すことができるので、集計を行う関数の範囲を指定することに使えます。

　OFFSET(A1,1,MATCH("C組",B1:E1,0),10,1)
こんな感じ。（未実証）
これで範囲を返すことができる。
SUM系の関数やCOUNT系の関数の範囲に使用してみてください。

なお、MATCH関数で一致する文字列が見つけられなかったときのエラー対策をしていませんので
その点は自身で対策をしてください。


・・・
INDIRECT関数で範囲を指定してもよいと思いますが、面倒です。
この場合、OFFSET関数のほうが扱いやすいと思います。

この回答へのお礼

ありがとうございます。上手く集計することが出来そうです。

追加で質問をさせて下さい。
MATCH関数の中に"C組"セルから参照できるようにOFFSET(A1,1,MATCH(T(D1),B1:E1,0),10,1)という具合にT関数を用いてみたのですが、
セルをコピーするためにT関数にT(＄D1)という入力するとエラーが出てしまいます。
何か解決方法はありますか？

お礼日時：2016/05/25 12:22

No.3 回答者： CoalTar 回答日時： 2016/05/25 18:13

COUNTIFとか範囲を返したいとか、説明とサンプルがかみ合ってないと思いました

サンプルを信じて L2セルに
=COUNT(1/(INDEX($B2:$D2,MATCH(L$1,$B$1:$D$1,0))="A"),
1/(INDEX($G2:$I2,MATCH(L$1,$G$1:$I$1,0))="A"))
右へ下へオートフィル

余談です。
エラーが出るならどのようなエラーなのか返答したほうが良いです
たぶん、＃N/Aだとは思うのですが。
また、OFFSET関数も範囲を返しますが、INDEX関数でも範囲を返せます。

この回答へのお礼

初めての質問でわかりにくくて申し訳なかったです。
今後も不明な点は質問させていただきたいと思いますのでよろしくお願いします。

お礼日時：2016/05/25 18:40

No.2 回答者： 銀鱗 回答日時： 2016/05/25 13:40

T関数は使わなくてもよいと思います。

参照先の値が文字列であれば、文字列として参照されます。

しかし…絶対参照の文字列に置き換えても普通に使えるはすですが…エラーになりますか。
エラーの原因は他にあるのではないでしょうか。
コピーしたことにより
　OFFSET(A1,1,MATCH(T($D1),B1:E1,0),10,1)
　OFFSET(A1,1,MATCH(T($D1),C1:F1,0),10,1)
のようにMATCH関数で参照する範囲がB1:E1→C1:F1とずれているとか。

この回答へのお礼

回答ありがとうございます。
ご指摘のとおり、MATCH関数の範囲していがずれていました。
問題解決できて本当に助かりました。

お礼日時：2016/05/25 18:39