[[20200522141944]]　『別シートにある該当月の数字だけを抽出したい』（MB）　ページの最後に飛ぶ

『別シートにある該当月の数字だけを抽出したい』（MB）

 当月予算、当月実績、前年同月の3つと、前述3つの4月～当月までの累計比較表を作成しています。

 （シート1）
 　　前年同月　　　予算　　　実績　　前年累計　　予算累計　　実績累計
 あ

い

う

え

お

 その他に、(シート2)は予算、(シート3)は実績、(シート4)は昨年度の月毎の数字が入力されています。

 （シート2～4）共通
 　　　　　4月　　5月　　6月　　7月　　8月　　・・・
 あ

い

う

え

お

 例えばシート1に5月と入力したら、シート2～4の5月の数字を表示させて
 累計の欄には4～5月の合計を出したいです。

 初心者なのでマクロは使用できません。
 お知恵を貸してください。

＜使用 Excel：unknown、使用 OS：Windows10 ＞

 シート2～シート4のレイアウトが
	Ａ	Ｂ	Ｃ	Ｄ	Ｅ	Ｆ
1		4月	5月	6月	7月	8月
2	あ					
3	い					
4	う					
5	え					
6	お

 である場合、まずシートの名前を「予算」、「実績」、「前年同月」と変えておきます。
 つまり、シート1のB1、C1、D1　とスペースの有無などを含めて完全に一致させておきます。

 シート1のレイアウトが	
	Ａ	Ｂ	  Ｃ	Ｄ	Ｅ	  Ｆ	        Ｇ
1	5月	前年同月	  予算	実績	前年累計	  予算累計	実績累計
2	あ						
3	い						
4	う						
5	え						
6	お

 である場合、例えば5月のデータを表示するときには、A1には「5」と入力して、表示形式で「5月」と表示させます。
 B2に
 =INDEX(OFFSET(INDIRECT(B$1&"!A1"),0,0,COUNTA($A:$A),13),MATCH($A2,$A:$A,0),$A$1+IF($A$1<4,10,-2))

 と入力してD列までコピー
 E2に
 =SUM(OFFSET(INDIRECT(B$1&"!A1"),MATCH(A2,A:A,0)-1,1,1,$A$1-3+IF($A$1<4,12)))

 と入力してG列までコピー
 それぞれ下にコピー

 これで希望通りになりますでしょうか。

（tora） 2020/05/22(金) 19:16

toraさま

 ありがとうございました。
 教えて頂いた通りの表を作成したところ理想通りに完璧に再現できました。

 しかし、シート1と昨年同月・予算・実績の4シートを選択し、行と列を挿入したところエラーとなってしまいます。
 形の異なる表にしたい場合は、一旦シート1を同じ形の表で作成し、新たに別の表で転記するようにしたら良いですか？

 また、昨年同月と予算の間に【前月実績】も追加したいです。
 お手数ですが再度ご教授ください。
（MB） 2020/05/25(月) 14:21

 とりあえず、前回の回答でE2に入力した式に一部あやまりがありましたので訂正します。

 E2=SUM(OFFSET(INDIRECT(B$1&"!A1"),MATCH($A2,$A:$A,0)-1,1,1,$A$1-3+IF($A$1<4,12)))

 そのうえで、追加の質問について。

 前年同月・予算・実績のシートは、行の挿入はともかく列の挿入もあるのですか?
 前回の回答ではA2から下にあいうえお・・・、B2～M2に4月～3月と入力されているものと考えています。
 列の挿入はない前提で作っていますが、もしあるならどのようになるかお知らせください。

 この表には行の挿入をした場合、一番下の行でエラーになりますが、
 挿入したA列の項目名(例えば「か」など)を入力するとエラーは解消されます。
 いや、最初からエラーを出したくないなら

 B2 =INDEX(OFFSET(INDIRECT(B$1&"!A1"),0,0,COUNTA($A:$A)+10,13),MATCH($A2,$A:$A,0),$A$1+IF($A$1<4,10,-2))
                                                       ~~~                                                                                                  
 などとすればいいです。とりあえず10行挿入しても大丈夫でしょう。
 また、A列の項目名を入力すればさらに10行・・・というふうに増やせます。

 また、列の挿入は提示してある前月実績だけでしょうか。
 その値は、実績シートの前月欄を参照すればいいのでしょうか。
 その場合、4月には前月実績がないので表示できませんがそれでいいですか。

 今後のレイアウト変更の可能性も含めてできるだけ詳しくお知らせください。
 もっともすべてのレイアウト変更に対応するのは難しいかもしれませんが・・

（tora） 2020/05/25(月) 17:23

toraさま

 ありがとうございます。
 こんなに複雑な式は自分では作れないので、本当に助かります。

 実際の表ですが、

      B         C        D       E         F      G     H      I        J

 5       　　前年実績　  比    前月実績   予算　実績　  比   前年比  予算比

 6   収益(ｲ+ﾛ)
 7   イ
 8   ロ
 9   費用(sum(ﾊ:ﾁ))
 10  ハ
 11  ニ
 12  ホ
 13  ヘ
 14  ト
 15  チ
 16  損益(6-7)
 17  収益2
 18  費用2
 19  損益2(16+17-18)

 同じように　K～Rは累計で同じ項目が続いています。
 4月の前月実績及び累計は表示されなくても構いません。
（MB） 2020/05/26(火) 10:49

 追記

 ちなみにA3：R19と同じつくりの表が、別事業所分としてA24:R40まであります。
 A3:B5、A24:B26はセルを結合し0月で表示されるようにしました。

（MB） 2020/05/26(火) 10:55

 前年同月、予算、実績のシートレイアウトはどうなっていますか?
 B1～M1に4月～3月、A2～A9にイ～チが入力されたものですか。

 そうでなければどうなっているかお知らせください。

（tora） 2020/05/26(火) 13:01

 あれ? 今回提示されたシートには前年累計、予算累計、実績累計の欄がないけどいいのかな。

（tora） 2020/05/26(火) 13:15

 先ほどの表の修正です。

      A     B         C        D       E         F      G      H      I        J　　　
 5       　　       前年実績　  比    前月実績   予算　実績　  比   前年比  予算比 
 6         イ
 7         ロ
 8   収益(ｲ+ﾛ)
 9         ハ
 10        ニ
 11        ホ
 12        ヘ
 13        ト
 14        チ
 15  費用(sum(ﾊ:ﾁ))
 16  損益(6-7)
 17  収益2
 18  費用2
 19  損益2(16+17-18)

 でした。　予算・実績・前年シートはA列B列はまったく同じ作りです。
 C3:C5がｾﾙ結合されており4月、D3:D5が5月･･･と続きます。

 またK～Rはすべて累計となっています。

         K         L      M          N      O     P     Q        R　　　　
  5     前年実績　  比    前月実績   予算　実績　  比   前年比  予算比 
 ・
 ・
 ・
 19

（MB） 2020/05/26(火) 13:38

 前年実績 C6 =INDEX(OFFSET(前年同月!$A$1,0,0,10,13),MATCH($B6,前年同月!$A$1:$A$10,0),$A$1+IF($A$1<4,10,-2))
 予算 (E6)、実績 (G6)は C6 をコピー、数式中の「前年実績」を「予算」「実績」に修正。

 前月実績 E6 =IF($A$1=4,0,INDEX(OFFSET(実績!$A$1,0,0,10,13),MATCH($B6,実績!$A$1:$A$10,0),$A$1+IF($A$1<4,10,-2)-1))

 前年累計 K6 =SUM(OFFSET(前年同月!$A$1,MATCH($B6,前年同月!$A$1:$A$10,0)-1,0,1,$A$1-2+IF($A$1<4,12)))
 予算累計 (N6)、実績累計 (O6)は K6 をコピー、数式中の「前年実績」を「予算」「実績」に修正。

 前月累計 M6 =SUM(OFFSET(実績!$A$1,MATCH($B6,実績!$A$1:$A$10,0)-1,0,1,$A$1-2+IF($A$1<4,12)-1))

 参照するシート名を関数ではなく具体的に記入してありますので前よりはわかりやすいかと。

（tora） 2020/05/26(火) 16:33

 >予算・実績・前年シートはA列B列はまったく同じ作りです。
 >C3:C5がｾﾙ結合されており4月、D3:D5が5月･･･と続きます。

 この部分、見逃していました。訂正です。

 前年実績 C6 =INDEX(OFFSET(前年同月!$A$5,0,0,10,13),MATCH($B6,前年同月!$A$5:$A$15,0),$A$1+IF($A$1<4,10,-2))
 予算 (E6)、実績 (G6)は C6 をコピー、数式中の「前年実績」を「予算」「実績」に修正。

 前月実績 E6 =IF($A$1=4,0,INDEX(OFFSET(実績!$A$5,0,0,10,13),MATCH($B6,実績!$A$5:$A$15,0),$A$1+IF($A$1<4,10,-2)-1))

 前年累計 K6 =SUM(OFFSET(前年同月!$A$5,MATCH($B6,前年同月!$A$5:$A$15,0)-1,0,1,$A$1-2+IF($A$1<4,12)))
 予算累計 (N6)、実績累計 (O6)は K6 をコピー、数式中の「前年実績」を「予算」「実績」に修正。

 前月累計 M6 =SUM(OFFSET(実績!$A$5,MATCH($B6,実績!$A$5:$A$15,0)-1,0,1,$A$1-2+IF($A$1<4,12)-1))

（tora） 2020/05/26(火) 17:26

 すみません、無駄に難しく考えすぎていました。

 前年実績 C6 =VLOOKUP($B6,前年同月!$A:$M,$A$1+IF($A$1<4,10,-2),FALSE)
 予算 (E6)、実績 (G6)は C6 をコピー、数式中の「前年実績」を「予算」「実績」に修正。

 前月実績 E6 =VLOOKUP($B6,実績!$A:$M,$A$1+IF($A$1<4,10,-2)-1,FALSE)

 これで十分ですね。

（tora） 2020/05/27(水) 10:09

 toraさま

 vlookupだとA24:R40の別事業所の表とE6がうまく表示できませんでした。
 indexを使用した関数の方で作っていきたいと思います。

 こんなにいろいろな関数が使えるなんてすごいですね。
 1人ではお手上げ状態でしたので本当に助かりました。
 お知恵を貸して頂き、ありがとうございました。

（MB） 2020/05/27(水) 12:19

コメント返信：

[ 一覧(最新更新順) ]

[[20200522141944]] 『別シートにある該当月の数字だけを抽出したい』（MB） ページの最後に飛ぶ

[[20200522141944]]　『別シートにある該当月の数字だけを抽出したい』（MB）　ページの最後に飛ぶ