成績表で特定の科目の単位だけを合計するには？

エクセルで大学の成績表を作ってみました。こんな感じです。
A 　　　　　　　 　B
科目　　　　　　　単位
心理学　　　　　　2
哲学　　　　　　　2
経済学　　　　　　4


ここで質問なのですがこの中で必修科目の単位だけを合計しようとしているのですがやり方がわかりません。必修科目は哲学、経済学、法学、西洋史、マーケティング論・・・・などかなり沢山あります。SUMIF関数やSUMIFS関数を使ってみましたがうまくいきませんでした。
哲学、経済学、法学、西洋史、マーケティング論・・・・のいずれかに該当する科目の単位を合計するという関数(式？)が知りたいです。
また、Sheet2に必修科目はすべて入力してあるのでできればそれを参照するやり方を教えてください。どなたかお願いします。

No.4 ベストアンサー 回答者： tom04 回答日時： 2012/05/10 23:29

こんばんは！

手元のExcel2003での一例です。

↓の画像で右側がSheet2でA列に「該当科目」を羅列しておきます。

Sheet1のE2セルに
=SUMPRODUCT((COUNTIF(Sheet2!A1:A100,A1:A100)),B1:B100)

F2セルに
=SUMPRODUCT((COUNTIF(Sheet2!A1:A100,A1:A100))*(C1:C100<>"不可"),B1:B100)

という数式を入れています。

参考になりますかね？m(_ _)m

この回答へのお礼

ご回答ありがとうございました。
大変わかりやすい画像まで貼っていただいて助かりました。

お礼日時：2012/05/11 01:05

No.6 回答者： KURUMITO 回答日時： 2012/05/11 14:10

難しい式を考える前に発想の転換を図りましょう。

お示しのデータがシート1のＡおよびＢ列にあるとして必須科目の表がシート2のＡ2セルから下方にあるとします。
シート2のＢ2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNTIF(Sheet1!A:A,A2)=0,"",SUMIF(Sheet1!A:A,A2,Sheet1!B:B))

必須科目で取得した単位の合計は　=SUM(B2:B100)
この式はＢ1セルに入力してもよいですね。
このような表にすることで必須科目のうちでまだ済んでいない科目も一目瞭然にみることができますね。シート1での単位の合計だけではどの教科が必須であるのかさえもわかりませんね。

この回答へのお礼

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

お礼日時：2012/05/13 14:52

No.5 回答者： keithin 回答日時： 2012/05/11 00:07

＞しかし正しい合計はでませんでした。

データの方をミスっています。
たとえばどっちかのセルで「哲学」と「哲学□」（□はスペース）などのように。

あるいはもしかすると、回答の数式をご自分のエクセル用にやりかえることができなかったのかもしれません。
試しにまっさらのブックを用意、あなたもあなたがご質問で書いたとおりのサンプルをまっさらエクセルに作成し直し、そのうえで寄せられた回答の通りに数式を使って練習してみたらどうでしょう。





ご自分ではどうしても間違いを見つけられない場合は。
１．あなたが実際に記入した数式を、あなたのエクセルからコピーして掲示してください。
２．あなたが実際にセルに記入しているデータを、あなたのエクセルからコピーして掲示してください。
　　その際に、どこ番地のセルにそれらのデータが記入されているのかも、しっかり判るように情報提供してください。
３．それらのデータから、正しくはどんな結果が欲しいのか、手計算で求めて示してください。

補足を投稿する前に、まっさらエクセルにご自分が書かれた補足情報を見ながら再現を作成し、情報提供が漏れていないか確認してから投稿してください。

この回答へのお礼

ご回答ありがとうございました。
データが間違っているという可能性は全く考えていなかったのでチェックしてみます。

お礼日時：2012/05/11 01:03

No.3 回答者： multiface 回答日時： 2012/05/10 23:29

補足です。

すでに他の方からも書き込みされていますが、私の書き込みの追加版です。
式は
=DSUM(A1:C10,2,Sheet2!A1:B5)

C1には、判定と入れておいてください。

＞なお、この場合sheet2には、A1に「科目」、A2以下は必修科目名を入力しておいてください。

さらに、B1には「判定」、B2には「="<>不可"」と追記してください。

これで、科目がsheet2で指定した必修科目、判定が＜＞不可（＝不可ではない）の単位を合計できます。

この回答へのお礼

ご回答ありがとうございました。
勉強になります。

お礼日時：2012/05/11 00:59

No.2 回答者： keithin 回答日時： 2012/05/10 23:13

=SUMPRODUCT(SUMIFS(B1:B10,A1:A10,Sheet2!A1:A10,C1:C10,"<>不可"))

などのように。

この回答へのお礼

ご回答ありがとうございました。
しかし正しい合計はでませんでした。
自分でも調べてみます。

お礼日時：2012/05/10 23:25

No.1 回答者： multiface 回答日時： 2012/05/10 22:30

DSUM関数でできますよ。

質問のデータが10行あり、sheet2の必修科目データが5行あったとき、合計を表示したいセルに次の計算式を入れてください。

=DSUM(A1:B10,2,Sheet2!A1:A5)

なお、この場合sheet2には、A1に「科目」、A2以下は必修科目名を入力しておいてください。

この回答へのお礼

早速のご回答ありがとうございました。
友人に聞いてもわからないと言われ困窮していたので本当に助かりました。

恐縮ですがもしよろしければ以下のような場合に「必修科目かつ不可でない単位」の合計を求める方法も教えていただけないでしょうか？

心理学　2　不可
哲学　　2　良
経済学　4　優
　

お礼日時：2012/05/10 22:39