MATCH関数 INDEX関数について教えてくださ

質問

シート1には1日ごとの売上が入ってる年間の集計表が縦に数年分あります

2011年
　　　1月　　　　　　　　2月　　　　　　　…
　　 りんご　みかん　　　　りんご　　みかん
1日　100　　　110　　　1日　200　　　210　　　　　　　　　　　　　
2日　100　　　110　　　2日　200　　　210　　　　　　　　　　　　　
3日　100　　　110　　　3日　200　　　210　

月計 3000　 　3300　　　　　6000　　　6300

2012年
　　　1月　　　　　　　　2月　　　　　　　　　…
　　りんご　みかん　　　　りんご　　みかん
1日　300　　　310　　　1日　400　　　410　　　　　　　　　　　　　
2日　300　　　310　　　2日　400　　　410　　　　　　　　　　　　　
3日　300　　　310　　　3日　400　　　410　

月計 9000　 　9300　　　　　12000　　12300

シート2には年単位での集計が縦にあります。

2011年
　　 りんご　みかん
1月　3000　　3300
2月　6000　　6300
3月

2012年
　 　りんご　 みかん
1月　9000　　9300
2月　12000　12300　
3月

シート1の月の合計をシート2の月単位に集計に反映させたくINDEX Match関数を試みたのですが上手く行きません…

Excel初心者です。どなたか力添えになっていただけると助かります。

説明文がわかりにくかもしれませんが、どうぞよろしくお願いいたします。

回答No.4

KURUMITO

非常に複雑な関数式となってしまいますので作業行を作って対応します。
シート１ではA１セルに2011年などの文字が、A2セルに1月の文字が、A4セルから下方には1日から31日までの文字がA34セルまでに入力されており、A36セルに月計の文字が入力されているとします。
B3セルにはりんご、C3セルにはみかん、などの文字列が横に入力され、その後に例えばG2セルに2月の文字列、H3セルから横にはりんごなどの文字列が入力されているとします。G4セルから下方には1日から日付が入力されているとします。
他の月についても上記と同様にします。

36行目に作業用の行を1行挿入します。月計はA37セルに移動します。
A36セルには次の式を入力して右横方向にドラッグコピーします。

=IF(COLUMN(A1)=1,OFFSET(A36,-35,0)&OFFSET(A36,-34,0),IF(OFFSET(A36,-33,0)<>"",LEFT(OFFSET(A36,0,-1),FIND("月",OFFSET(A36,0,-1)))&OFFSET(A36,-33,0),IF(OFFSET(A36,-34,0)<>"",OFFSET($A36,-35,0)&OFFSET(A36,-34,0),"")))

2012年のデータについても2011年と同じ行数を使ってデータが入力されているとします。例えばA41セルから下方に2012年のデータが入力されているとします。
月計の前に1行挿入して、その行のA列のセル（例えばA80)にはA36の式をコピーして貼り付けます。そのまま例えばA80セルが選択されている状態で「ホーム」タブの「検索と選択」から「置換」をクリックします。
検索する文字列にA36と入力して置換後の文字列にはA80と入力して「置換」をクリックします。
この操作で式が変わりますので、その式を右横方向にドラッグコピーします。

上のような作業行の挿入と式のコピー、式の変換の操作などを他の年度についても行います。

お望みの表ですがシート2に作成するとして例えばA1セルには2011年、B2セルにはみかんなど横方向の列に果物名を入力します。
A3セルから下方には1月からA14セルの12月までを入力します。
B3セルには次の式を入力して右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(OFFSET(B3,-ROW(A1),0)="","",IFERROR(INDEX(Sheet1!$A:$XX,MATCH(OFFSET(B3,-ROW(A2),-COLUMN(A1))&"1月",Sheet1!$A:$A,0)+1,MATCH(OFFSET(B3,-ROW(A2),-COLUMN(A1))&OFFSET(B3,0,-COLUMN(A1))&OFFSET(B3,-ROW(A1),0),INDEX(Sheet1!$A:$XX,MATCH(OFFSET(B3,-ROW(A2),-COLUMN(A1))&"1月",Sheet1!$A:$A,0),1):INDEX(Sheet1!$A:$XX,MATCH(OFFSET(B3,-ROW(A2),-COLUMN(A1))&"1月",Sheet1!$A:$A,0),250),0)),""))

なお、エクセル2007より古いバージョンのエクセルでは次の式を入力します。

=IF(OFFSET(B3,-ROW(A1),0)="","",IF(ISERROR(INDEX(Sheet1!$A:$XX,MATCH(OFFSET(B3,-ROW(A2),-COLUMN(A1))&"1月",Sheet1!$A:$A,0)+1,MATCH(OFFSET(B3,-ROW(A2),-COLUMN(A1))&OFFSET(B3,0,-COLUMN(A1))&OFFSET(B3,-ROW(A1),0),INDEX(Sheet1!$A:$XX,MATCH(OFFSET(B3,-ROW(A2),-COLUMN(A1))&"1月",Sheet1!$A:$A,0),1):INDEX(Sheet1!$A:$XX,MATCH(OFFSET(B3,-ROW(A2),-COLUMN(A1))&"1月",Sheet1!$A:$A,0),250),0))),"",INDEX(Sheet1!$A:$XX,MATCH(OFFSET(B3,-ROW(A2),-COLUMN(A1))&"1月",Sheet1!$A:$A,0)+1,MATCH(OFFSET(B3,-ROW(A2),-COLUMN(A1))&OFFSET(B3,0,-COLUMN(A1))&OFFSET(B3,-ROW(A1),0),INDEX(Sheet1!$A:$XX,MATCH(OFFSET(B3,-ROW(A2),-COLUMN(A1))&"1月",Sheet1!$A:$A,0),1):INDEX(Sheet1!$A:$XX,MATCH(OFFSET(B3,-ROW(A2),-COLUMN(A1))&"1月",Sheet1!$A:$A,0),250),0))))

2012年についても表を作るのでしたら例えばA16セルに2012年と入力して2011年の場合と同様に果物名や1月から12月までを配置します。その後にB3セルをコピーして例えばB18セルに貼り付け、その式の中のB3を置換操作によってB18に変え、右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

お礼

KURUMITO様

非常にお手間な回答ありがとうございますm(_ _)m

数式を入れてやってみます。

補足

KURUMITO様

Excelは2003を使用しています。

新たにブックを作って教えて頂いた関数を入れてみました。
B3には商品名［りんご］C3には［みかん］を入れ B4からB34までに1の数字を入れてC4からC34には2を入れてA36には上記のシート1の関数を入れて右横にコピーしました。

A36には2011年1月と参照されB36 C36には＃WALUE！のエラーが出てしまします
何度も関数は確認したので入れ間違いはないと思うのですが何か間違って入力しているのでしょうか

シート2の関数をコピーして貼り付けしたのですがうまくいきません。

合わせてご教授お願いいたします

回答No.3

K Kazz（@JazzCorp）

項目の見出し等、シートの構成の条件：
Sheet1：
１年度はブランク含めて35行（最初の合計値は行34の位置）
年（Ａ列）と月（Ｂ列～）は同じ行１（以下年度でサイクリック）にする
品名は次の行に
Sheet2：
１年度はブランク含めて15行（合計値はシート内でSUM）
年（行１）と月は同じＡ列にする
品名は行１（年と同じ）に

Sheet2の数式：
Ｂ２：
=INDEX(OFFSET(Sheet1!$B$1:$AJ$1,INT((ROW()-1)/15)*35+33,0),MATCH($A2,Sheet1!$B$1:$AJ$1,0))
Ｃ２：
=INDEX(OFFSET(Sheet1!$B$1:$AJ$1,INT((ROW()-1)/15)*35+33,0),MATCH($A2,Sheet1!$B$1:$AJ$1,0)+1)
Ｂ２，Ｃ２まとめて下にコピー
次の年分についてもコピーできる

お礼

JazzCorp様

ありがとうございます。

教えて頂いた関数を入れてやってみます。

回答No.2

shintaro-2

参考にしてください

参考URL：

http://pc.nikkeibp.co.jp/article/knowhow/20110628/1032668/

回答No.1

prius770c

どのような運用なのかわかりませんが、単純にシート２の表はシート１の該当するセルを参照させるだけでよいような気がしますが・・・。

【参考となるページ】
http://www.excel-jiten.net/formula/ref_other_sheets.html

これで、別シートにある値を参照させる事は出来ます。
参照セルが移動してしまうなどの条件で保守上の問題がない限り無理にmatchやindex関数を
用いなくてもよいのではないでしょうか？

補足

prius770cさん

早速の回答ありがとうございます。説明文が不足していたと思いますので付け加えさせてもらいます。シート1の商品名ですが30品目程あります。

参照ですがシート1の商品の数があり一年分となるとちょと大変かなと…

シート2の1月分にシート1の1月 月計を参照させた後にコピーを下にすると参照先の一つ下のセルを参照してしまいます。