エクセル、条件分岐参照

質問

セルに入力した値により参照するセルが異なるような数式を探しています。
こんな感じです。
対象年についてＡに２００１～２００３を入力すると、
２００４年と対象年の差について、各国ごとに数値がでるようにしたいです。
INDEXだと対象セルの上から何番目、左から何番目となり、
年次を変えると使えなくなるので、年を入れると直接「年」で参照する数式を希望します。

対象年＿（　Ａ　）
＿＿＿＿２００１＿２００２＿２００３＿２００４＿＿２００４年と対象年の差
Ａ国＿＿＿５００＿＿６００＿５００＿＿４００＿＿（　　　）
Ｂ国＿＿＿４００＿＿３４０＿２３０＿＿３００＿＿（　　　）
Ｃ国＿＿＿３００＿＿５００＿２４０＿＿２００＿＿（　　　）
Ｄ国＿＿＿４００＿＿５５０＿１６０＿＿１００＿＿（　　　）

回答No.4

KURUMITO

INDEXだと対象セルの上から何番目、左から何番目となり、
年次を変えると使えなくなるので、年を入れると直接「年」で参照する数式を希望します。
INDEX関数である行だけを指定した場合には、列の番号だけが問題となるので上から何番目などは必要なくなります。また、絶対参照のセル番号などを使うことであるセルの式をドラッグコピーしても問題はなくなります。
お示しの表が例えばA1セルに「対象年」、B1セルに２００１、C1セルに「年から」、D1セルに２００３、E1セルに「年まで」と入力します。
B2セルに２００１、C2セルに２００２、・・・・G2セルに2005のように年数が入力されているとします。
A3セルから下方にはA国、B国…と入力されており、それらの右側の列には各年のデータが入力されているとします。
H3セルには2004年のデータを基準にするのでしたら2004と入力し、I3セルには「年との差」とでも入力します。
H4セルには次の式を入力して下方にドラッグコピーします。

=IF(INDEX(B3:G3,MATCH(H$2,B$2:G$2,0))="","",ROUND(INDEX(B3:G3,MATCH(H$2,B$2:G$2,0))-AVERAGE(INDEX(B3:G3,MATCH(B$1,B$2:G$2,0)):INDEX(B3:G3,MATCH(D$1,B$2:G$2,0))),0))

この式ではB1セルに入力された年からD1セルに入力された年までのデータの平均を計算し、指定した年のデータからその値をマイナスした数値を小数点以下四捨五入して表示されることにしています。

回答No.3

K Kazz（@JazzCorp）

年データはB～Z列まで、上昇順で歯抜け不可、
年を追加するときは、最新年（今の基準年）の列をコピーし、その前に追加する。そして最新年のデータを書き換える。

F3に、

=$F3-INDIRECT(MID("BCDEFGHIJKLMNOPQRSTUVWXYZ",MATCH($B$1,$B$2:$F$2),1)&ROW())

以下、コピー

回答No.2

tom04

こんばんは！
一例です。

↓の画像でF3セルに

=IF($B$1="","",E3-INDEX(B3:D3,,MATCH($B$1,$B$2:$D$2,0)))

という数式を入れオートフィルで下へコピーしています。m（_ _）m

回答No.1

keithin

たとえばＦ３に
=IF($B$1="","",E3-HLOOKUP($B$1,B$2:E$6,ROW(F2),FALSE))
と記入し、Ｆ６までコピー貼り付けておきます。

同じことは
＞INDEXだと対象セルの上から何番目、左から何番目となり、
＞年次を変えると使えなくなる

○左から何列目が該当なのか、MATCH関数で調べます
○上から何番目、は対象年を変えることとは無関係ですが、上述のようにROW関数などで自セルが何行目にあるのか調べます。

のようにして計算しても出来ます。