エクセルの計算の仕方について

質問

生徒が複数の模試を受けます。
シートIに生徒名一覧と、各自が受ける模試あ～いに対してフラグが立っています。どれを受けるかは生徒によってバラバラです。

Ａ列　　　　　　Ｂ列　　　　　　　Ｃ列　　　　　Ｄ列　　　　E列
氏名　　　　　模試あ　　　　模試い　　　　模試う　　　代金合計
山田太郎　　　１　　　　　　　　　　　　　　　　　１　　　　　3000
田山花子　　　１　　　　　　　　　　　　　　　　　　　　　　　1300
海山次郎　　　１　　　　　　　　１　　　　　　　　１　　　　　4500

次に、シートIIには次のようにイメージの表を作りたいのです。

Ａ列　　　　　　Ｂ列　　　　　　　Ｃ列　　　　　Ｄ列　　　　Ｅ列
氏名　　　　　模試名　　　　　模試名　　　模試名　　代金合計
山田太郎　　模試あ　　　　　模試う　　　　　　　　　　3000
田山花子　　模試あ　　　　　　　　　　　　　　　　　　　1300
海山次郎　　模試あ　　　　　模試い　　　　模試う　　4500

つまり、シートIにフラグを立てると同時に、シートIIには「模試名」と合計金額が出るようにしたいのです。できるのでしょうか？シートIIの氏名の横にはどんな数式を入力したらいいのでしょうか？
よろしくお願いします。

回答No.2

tom04 · ベストアンサー

No.1です。

＞単純に列が二つずれるだけで問題ないと思ったのですが…？

前回の数式の場合列がずれるとどんでもないデータになるか、エラーになると思います。

前回の画像の配置で両Sheetとも、C列が「氏名」の列になる訳ですよね？

数式を↓のように変更してみてください。

Sheet2のD2セルに
=IF(COUNT(Sheet1!$D2:$F2)<COLUMN(A1),"",INDEX(Sheet1!$D$1:$F$1,,SMALL(IF(Sheet1!$D2:$F2=1,COLUMN($A$1:$C$1)),COLUMN(A1))))
これは前回同様配列数式です。
これをF2セルまでオートフィルでコピー！
G2セル（配列数式ではありません）には
=IF(C2="","",INDEX(Sheet1!G:G,MATCH(C2,Sheet1!C:C,0)))
という数式を入れ、D2～G2セルを範囲指定　→　G2セルのフィルハンドルで下へコピー！

これではどうでしょうか？

※　配列数式内の
＞SMALL(IF(Sheet1!$D2:$F2=1,COLUMN($A$1:$C$1))
の部分はINDEX関数で範囲指定した1列目からの列数を合わせる必要があります。

INDEX関数でSheet1のD1～F1セルを範囲指定していますので、D列に「1」がある場合は
1列目すなわち、「1」 がヒットしなくてはいけません（4列目ではありません）
ので、必ず
＞COLUMN($A$1:$C$1))
とA列から指定します。
おそらくここの範囲指定が違っているのではないかとおもいます。
＞COLUMN($D$1:$F$1)
といったようにすると滅茶苦茶なデータが表示されるはずです。

以上、長々と書きましたが
お役に立ちますかね？m(_ _)m

お礼

詳しく丁寧に教えて頂きありがとうございました。

エクセルでわからないことがあれば、また「教えてgoo」に質問すると思いますが

その時もぜひ、tom04さんに教えてもらいたいです！！

感謝感激です。本当にありがとうございました！！

回答No.3

tom04

No.1・2です！
たびたびごめんなさい。

No.1の
＞実は生徒氏名の前にクラス(Ａ列)と出席番号(Ｂ列)を、
＞両方のシートに後から追加したのですが・・・

の件で気になったのでまたまたおじゃましました。

結論として、数式内に「絶対参照」「複合参照」がなければ後から列（または行）を追加しても
数式のセル番地は自動でずれてくれますが、
前回の数式では「絶対参照」と列方向固定の「複合参照」の数式にしていますので
列挿入しても数式内の列は自動でずれてくれません。
すなわち挿入した列数だけ数式の列をずらす必要があります。

ただし、No.2で書いたように
配列数式内の
＞COLUMN($A$1:$C$1)
の部分はINDEX関数で範囲指定した「1列目～3列目」を参照するために
INDEX関数で範囲指定した列数分だけを　A列（すなわち「1」）　から列指定する必要があります。
これはA1でなくA100とかA10000でも問題ありません。A列であれば「1」・B列であれば「2」というコトになります。
同様のコトが行番号でもいえます。
ROW(A1)といった場合は
ROW(B1)　でも　ROW(Z1)　でも　「1」になります。

今回もダラダラと書いてしまいましたが、
余計に判り難くなってしまいましたかね？
どうも失礼しました。m(_ _)m

回答No.1

tom04

こんばんは！
おそらく模試の種類によって金額が異なるような気がするのですが・・・
質問ではそこまで判らないので、Sheet1の金額をそのままSheet2に表示するようにしてみました。

Sheet2のA列氏名はSheet1の氏名の順とまったく一緒だとします。

↓の画像でSheet2のB2セルに
=IF(COUNT(Sheet1!$B2:$D2)<COLUMN(A1),"",INDEX(Sheet1!$B$1:$D$1,,SMALL(IF(Sheet1!$B2:$D2=1,COLUMN($A$1:$C$1)),COLUMN(A1))))
これは配列数式になってしまいますので、Shift+Ctrl+Enterキーで確定します。
この画面からコピー＆ペーストする場合は、Sheet2のB2セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
このB2セルをD2セルまでオートフィルでコピー！

金額だけは数式が異なります。
E2セル（配列数式ではありません）に
=IF(A2="","",INDEX(Sheet1!E:E,MATCH(A2,Sheet1!A:A,0)))
という数式を入れ、最後にB2～E2セルを範囲指定　→　E2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

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

お礼

ありがとうございます！！うまくいきました。感謝します。

ただ一点だけ不明点が…実は生徒氏名の前にクラス(Ａ列)と出席番号(Ｂ列)を、両方のシートに後から追加したのですが、すると数式がおかしくなり、ｓｈｅｅｔ２に正しく表示されなくなりました。単純に列が二つずれるだけで問題ないと思ったのですが…？数式バーの列表示は自動的に変わるのでしょうか？それとも手入力でどこかをいじるのでしょうか？

いろいろ試したのですが、正常にならないので、後から追加したクラス・出席番号の列は今は削除しています。その状態であれば、教えて頂いたとおり、きれいなかたちで出力されます。