エクセルの関数について

質問

エクセルについて教えてください。
過去1年間の点数と達成率％の表があります。

　　　　　　　　　　　　　　　　　　　　　　　　　　　　○月

　　　1月 ％ 2月 ％ 3月 ％ 4月 ％　　3か月合計
上田 90 0.9 100 1 77 0.7 78 0.7
山中 100 1 85 0.8 84 0.8 88 0.8
村上 85 0.8 78 0.7 97 0.9 74 0.7
源 95 0.9 95 0.9 84 0.8 85 0.8

表の上部に月数を入れるセルを作り、そのセルに月を入れると合計セルにその月からさかのぼって3カ月（月数セルが5月なら、合計欄は3月、4月、5月）の合計の数値が反映されるようにしたいのです。

悩むのも時間の無駄だと思い、IF関数をつなげて、４月だったらSUM(2月,3月,4月）・・・のような式を１２ヶ月分つなげて入れたのですが、実際のデータは数がたくさんあり、「使用できる数を超えました」のようなエラーがでて、式が反映されませんでした。

何か良い数式はありますか？
また、IFでつなげた式が使えるようになる方法はあるのでしょうか？
（式の圧縮的な・・・）

よろしくお願い致します。

回答No.2

FEX2053 · ベストアンサー

単純に

１．特定の月（３月なら１，２，３月）の合計を計算するセルを
12個作る。

２．入力した月に従って、どれを選ぶかCHOOSE関数などを
上手く使って選択する（12個ならIFでも行けるかも）

１．のセルはAA列の後ろとか、とにかく「遥かかなたの先」に
設定して、一見して見えないようにするとか、列の非表示で
見えなくするとかすればオッケー。

#1さんのような、複雑な式で答えを導くのも良いのですが、
それをやると後でメンテがしづらくなります。なるべく、計算
するステップを全て、いちいちセル上に表示させておく方が
メンテナンスを考えると絶対に正解です。

そのために、Excelには広大なワークシートがあるんです。

お礼

回答ありがとうございます。
私もそれを考えました。
できないならできないなりに、難しく考えずシンプルにいくことにします。
メンテナンスを考えると、自分ですぐわかる方が良いですもんね。

回答No.3

KURUMITO

例えば表が次のようになっているとします。
A3セルから下方に氏名が並んでいるとします。
B2セルには1月点数、C2セルには１月達成率の文字を入力したのちにB2セルとC2セルを選択してから右横方向にY2セルまでドラッグコピーしてあるとします。
それぞれのデータが3行目から下方に入力されているとします。
そこでお求めの表ですがZ1セルに検索の月を例えば5と入力し、AA1セルには月の文字を入力します。
Z2セルには3カ月合計点数とでも入力します。
Z3セルには次の式を入力して、式を確定する段階でCtrlキーとShiftキーを押しながらEnterキーを押します。その後に式を下方にドラッグコピーします。

=IF($A3="","",SUM(IF(MOD(COLUMN(INDEX($B3:$Y3,$Z$1*2-5):INDEX($B3:$Y3,$Z$1*2)),2)=0,INDEX($B3:$Y3,$Z$1*2-5):INDEX($B3:$Y3,$Z$1*2),0)))

AA2セルには合計達成率と入力します。
AA3セルには次の式を入力してから上の場合と同じように式を確定する段階でCtrlキーとShiftキーを押しながらEnterキーを押します。その後に式を下方にドラッグコピーします。

=IF($A3="","",SUM(IF(MOD(COLUMN(INDEX($B3:$Y3,$Z$1*2-5):INDEX($B3:$Y3,$Z$1*2)),2)=1,INDEX($B3:$Y3,$Z$1*2-5):INDEX($B3:$Y3,$Z$1*2),0)))

ただ気になるのは3カ月間の平均達成率ではないのかなとも思います。その場合にはAA2セルには平均達成率と入力して
AA3セルには次の式を入力してから式を確定する段階でCtrlキーとShiftキーを押しながらEnterキーを押します。その後に式を下方にドラッグコピーします。

=IF(OR($A3="",COUNT(INDEX($B3:$Y3,$Z$1*2-5):INDEX($B3:$Y3,$Z$1*2))=0),"",SUM(IF(MOD(COLUMN(INDEX($B3:$Y3,$Z$1*2-5):INDEX($B3:$Y3,$Z$1*2)),2)=1,INDEX($B3:$Y3,$Z$1*2-5):INDEX($B3:$Y3,$Z$1*2),0))/COUNT(INDEX($B3:$Y3,$Z$1*2-5):INDEX($B3:$Y3,$Z$1*2))*2)

お礼

回答ありがとうございます。
すごいですね。こんなに長い式をサクッと作ってしまうと…
かっこいいです。
熟読し、勉強させて頂きます。
私もこんな式がサクッと作れるようになりたいです。

回答No.1

keithin

パーセントの列には「生数字で記入する」か，若しくは計算式で計算させているなら「値が無いときは""を計算する」式にしておくこと
パーセントは％書式で表示すること（１００倍してパーセントの値，とかで計算させない事）

Ａ列に名前，Ｂ列以降にデータとする

手順：簡易版
Ｚ１に３のように月数を記入する
Ｚ２に
=SUMIF(OFFSET(INDEX(A2:Y2,MATCH(MAX(A2:Y2)+1,A2:Y2,1)),0,0,1,-Z$1*2),">2")
以下コピー
と記入する。

お礼

回答ありがとうございます。
OFFSETやINDEX、こたらで良く見かけますが今一良くわからず…
これを参考に勉強させて頂ます。