Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10 Home : Excel 2013)
課別シートを集計シートにまとめる方法
投稿日時: 18/01/06 11:10:56
投稿者: ヤングコーン

1課から8課まで課別にシート分けした営業実績シートがあり、月ごとの集計を集計シートに出したい
と思い、例えば、1課の4月の売上を集計シートセルB3に出す場合、集計シートのセルA1に"4月"といれ、
セルB3に下記関数を入れるとうまく出ました。
 
    1課シート                
    A    B    C    D     M
1        4月    5月    6月・・・3月
2    売上    100    80    90 ・・・70
3    支払    70    60    80 ・・・50
4    利益    30    20    10 ・・・20
                    
    2課シート                
    A    B    C    D     M
1        4月    5月    6月・・・3月
2    売上    150    250    200・・・100
3    支払    100    150    150・・・80
4    利益    50    100    50 ・・・20
 
同様のシートが8課まであります。これを集計シートにまとめたいと思います。
単月集計表はA列からD列までで構成され、F列からI列には累計集計表があります。
    集計シート                
    単月                
    A    B    C    D    
1    指定月    指定月            
2        売上    支払    利益    
3    1課                
4    2課                
5    3課
・   ・
・   ・
・    ・            
10    8課                
セルA1=4月
セルB3=INDEX(1課!$A:$M,MATCH("売上",1課!$A:$A,0),MATCH(A$1,1課!$1:$1,0))
 
    累計            
    F    G    H    I
1                
2        売上    支払    利益
3    1課            
4    2課            
5    3課            
・   ・
・   ・
・    ・            
10    8課
                
仮に、4月から8月までの累計を出したい場合、セルA1に4月、セルB1に8月といれ、セルG3に1課の        
累計売上を表示する方法がありますでしょうか。        
ご存知の方はご教示願います。        
 

回答
投稿日時: 18/01/06 14:13:29
投稿者: んなっと

   A   B   C   D E  F   G   H   I
1 4月  6月                   
2    売上 支払 利益     売上 支払 利益
3 1課  100  70  30   1課  270  210  60
4 2課  150  100  50   2課  600  400  200
 
B3
=HLOOKUP($A$1,'1課'!$A:$M,COLUMN(B:B),FALSE)
右方向・→
G3
=SUM(INDEX('1課'!$A:$M,COLUMN(B:B),MATCH($A$1,'1課'!$1:$1,0)):INDEX('1課'!$A:$M,COLUMN(B:B),MATCH($B$1,'1課'!$1:$1,0)))
右方向・→
 
 
※揮発性関数のOFFSET,INDIRECTはなるべく使わないほうがいいということなので、下の式はやめましょう。
G3
=SUM(OFFSET(INDIRECT("'"&$F3&"'!A1"),COLUMN(A:A),MATCH($A$1,INDIRECT("'"&$F3&"'!1:1"),0)-1,,MOD(LEFTB($B$1,2)-LEFTB($A$1,2),12)+1))
下方向・↓右方向→

投稿日時: 18/01/07 00:38:55
投稿者: ヤングコーン

んなっと様
ご教示頂きありがとうございます。
教えて頂いた数式を参考にして
 
セルG3=SUM(INDEX(1課!$A:$M,MATCH("売上",1課!$A:$A,0),MATCH(A$1,1課!$1:$1,0)):
       INDEX(1課!$A:$M,MATCH("売上",1課!$A:$A,0),MATCH(B$1,1課!$1:$1,0)))
 
といれて試したところ、セルA1=4月・セルB1=5月、セルA1=6月・セルB1=7月といった
隣り合った2ヶ月間ではうまくいきましたが、セルA1=4月・セルB1=6月といった2ヶ月
以上の合計が#REF!となり求めている結果が得られませんでした。
 
連続する2ヶ月では結果が得られ、2ヶ月以上となるとエラーとなる理由がわかりません
ので、考えられる要因がありましたらご教示頂けないでしょうか。
よろしくお願いいたします。
 
 :(

回答
投稿日時: 18/01/07 08:12:31
投稿者: んなっと

例えば
G3
=SUM(INDEX('1課'!$A:$M,MATCH("売上",'1課'!$A:$A,0),MATCH(A$1,'1課'!$1:$1,0)):
     INDEX('1課'!$A:$B,MATCH("売上",'1課'!$A:$A,0),MATCH(B$1,'1課'!$1:$1,0)))
とすれば#REF!エラーを起こすことができます。
方法はいくらでもあります。頑張ってください。

回答
投稿日時: 18/01/07 12:55:32
投稿者: Mike

  A  B  C  D  E F G  H  I
1 4月          累計   
2   売上 支払 利益   売上 支払 利益
3 1課 100  70  30   340 260  80
4 2課 150 100  50   700 480 220
5 3課  …  …  …    …  …  …
〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜
10 8課  …  …  …    …  …  …
 
B3: =INDEX(OFFSET(INDIRECT($A3&"!$B$2"),COLUMN(A1)-1,,,12),MATCH($A$1,'1課'!$B$1:$M$1,0))
G3: =SUM(OFFSET(INDIRECT($A3&"!$B$2"),COLUMN(A1)-1,,,12))

回答
投稿日時: 18/01/07 14:01:54
投稿者: んなっと

Mikeさん、最初の質問文に次のように書かれていますよ。

引用:
仮に、4月から8月までの累計を出したい場合、セルA1に4月、セルB1に8月といれ、
セルG3に1課の累計売上を表示

回答
投稿日時: 18/01/07 14:13:20
投稿者: んなっと

 それからB3で揮発性関数のINDIRECTを使うなら、
 
B3
=HLOOKUP($A$1,INDIRECT("'"&$F3&"'!A:M"),COLUMN(B:B),FALSE)

回答
投稿日時: 18/01/07 15:12:41
投稿者: Mike

んなっと さんの引用:
Mikeさん、最初の質問文に次のように書かれていますよ。
んなっと さん、
いつもご指摘、ありがとうございます。
 
ヤングコーンさん、
私の怪答は無視してください。済みませんでした。

トピックに返信