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

質問

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

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

回答No.4

tom04 · ベストアンサー

こんばんは！
手元の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

お礼

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

回答No.6

KURUMITO

難しい式を考える前に発想の転換を図りましょう。
お示しのデータがシート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での単位の合計だけではどの教科が必須であるのかさえもわかりませんね。

お礼

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

回答No.5

keithin

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

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

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

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

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

お礼

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

回答No.3

multiface

補足です。

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

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

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

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

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

お礼

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

回答No.2

keithin

=SUMPRODUCT(SUMIFS(B1:B10,A1:A10,Sheet2!A1:A10,C1:C10,"<>不可"))
などのように。

お礼

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

回答No.1

multiface

DSUM関数でできますよ。

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

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

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

お礼

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

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

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