[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『集計シートに複数のシートの特定のセルの合計を表示したい』（初心者）
お世話になります。
複数の依頼用シート(全て書式が同じ)のA4に「4/1」と日付が入り、B16にそのシートの合計数が入っています。
集計用のシートに4月分、5月分、というように月ごとの合計を出したいのですが、上手くいきません。
「全部のシートの内から,A4が○月の,B16セル の合計」を関数で出すことは可能でしょうか？
ご教示いただけますと助かります。
＜ 使用 Excel：Excel2013、使用 OS：Windows8 ＞
マクロ向きだと思いますけど・・・
１．集計シートは、全シートの左端に位置させるものとします。(重要）
２．名前定義をします。 Ctrlキー + F3キーを押下して、名前定義ダイアログを出し、 名前ボックスに 「Sh」 参照範囲ボックスに「=GET.WORKBOOK(1)&T(NOW())」
３．数式を入力
(1) I2セル =IFERROR(TEXT(INDIRECT(INDEX(REPLACE(Sh,1,FIND("]",Sh),"'")&"'",ROW(A2))&"!A4"),"yyyy/m")*1,"")
(2) J2セル =IFERROR(INDIRECT(INDEX(REPLACE(Sh,1,FIND("]",Sh),"'")&"'",ROW(A2))&"!B16"),"")
それぞれ、下にフィルコピー
(3) B2セル =MIN(I:I)
(4) C2セル =IF(B2="","",SUMIF(I:I,B2,J:J)) 下にフィルコピー
<集計用 結果図> 行 _A_ _____B_____ __C__ _D_ _E_ _F_ _G_ _H_ ___I___ ___J___ 1 年月 合計 作業１ 作業２ 2 2017年4月 501 42826 100 3 2017年5月 200 42856 200 4 2017年10月 7000 42826 401 5 43009 7000 6
※B列のセルの書式(表示形式）は、「yyyy"年"m"月";@」に設定してください。 ※ブックのファイル形式はマクロ可能な「.xlsm」にしてください。(重要)
（半平太） 2017/03/18(土) 22:06
恥ずかしながら教えていただいた方法を集計用シートにコピーペーストしましたが、
シートごとの合計がJ列に表示されるのみで各月の合計は導けませんでした。理解不足で申し訳ありません。
自分自身はこの方法を勉強しながら、関数では出来ない旨の返答で様子を見ようと思います。ありがとうございました。
（初心者） 2017/03/19(日) 13:26
＞A4に「4/1」と日付が入り、
その「4/1」と言うデータが、私の推測するデータではないのかも知れません。
A4のセルの書式を標準にすると、A4セルどんな表示に変わりますか？
(1)42826に変わる ← 私の想定するデータ (2)4/1のまま (3)それ以外に変わる
＞マクロの件ですが上司が理解できないものは使用が禁止されています。
まぁ、数式でも理解できない虞れはありますけどね。
（半平太） 2017/03/19(日) 16:07
(1)この表記にはならず
(2)既に入力済みの欄は4/1のまま
(3)新しく入力した際は4月1日という表記 と変化します。
依頼先の希望で4/1という表記のようですが(1)のような表記でないとだめだったのでしょうか。
（初心者） 2017/03/19(日) 23:56
＞依頼先の希望で4/1という表記のようですが(1)のような表記でないとだめだったのでしょうか。
ダメと言うことはないですが、「4/1」だけの情報では、 何年の4月1日であるのか分からないですよね。
事務に使うデータとしては、間違いを誘発し易いデータであるとは言えます。
ただ、こちらで実際に「4/1」と言う文字を入力してテストすると、 (年号の正確性は別として）集計はされています。
と言うことは、実際のデータは「4/1」でもないことになります。 人間の目には「そう見えてしまうような何か」です。
そこで、実際は何なのか知りたいので、以下の関数をどこか空いているセルに入れて、 何が返るか教えてください。
D4セル =CODE(MID($A$4,ROW(A1),1))
<下図は、D4セルに入力して、下にコピーした場合の例>
行 _A_ _B_ _C_ ___D___ 4 4/1 52 5 47 6 49 7 #VALUE! 8 #VALUE! 9 #VALUE!
（半平太） 2017/03/20(月) 08:58
D4セル =CODE(MID($A$4,ROW(A1),1)) 入力しますと日付ごとに異なりますが挙げて下さった例同様に52,53,47等の二ケタの数字が3行分返ります
現状、私の集計用シートは
行 _A_ _____B_____ __C__ _D_ _E_ _F_ _G_ _H_ ___I___ ___J___ 1 年月 合計 作業１ 作業２ 2 1990年1月 0 42826 30000 3 1990年1月 0 42826 30000 4 1990年1月 0 42826 10000 5 1990年1月 0 42826 15000 となっています。(この作業2の部分が各シートごとのB16の値) 集計に関しては依頼が月10シートとなり数が多くなるため、1期ごとにブックを分けており年号は重視しておらずこのような設定のようです。 （初心者） 2017/03/20(月) 15:23
１．済みません。ひとつ数式が足りなかったです。m(__)m
B3セル =IF(B2="","",IFERROR(SMALL(I:I,COUNTIF(I:I,"<="&B2)+1),"")) 下にコピー
その数式を追加すると、そちらのデータであれば、以下の図になります。(B3以下は空白になり、目には見えない）
<結果図>
行 _A_ ____B____ __C__ _D_ _E_ _F_ _G_ _H_ ___I___ ___J___
1 年月 合計 作業１ 作業２
2 2017年4月 85000 42826 30000
3 42826 30000
4 42826 10000
5 42826 15000
２．B列に「1990年1月」と表示されたのだとすると、その式は、空白データを見に行っています。
B列の式が「I列」を見に行っているか、再確認してください。
（半平太） 2017/03/20(月) 16:25
行 _A_ ____B____ __C__ _D_ _E_ _F_ _G_ _H_ ___I___ ___J___ 1 年月 合計 作業１ 作業２ 2 2017年4月 85000 42826 30000 3 0 42826 30000 4 0 42826 10000 5 0 42826 15000
試しに5月分のシートを追加したところきちんと4月の下に足されて行きました。ありがとうございます！
2.B列はI列を参照していました。依頼用の原紙シートが入っていたことが原因のようです。削除しました。
やはりとても便利ですね。なんとか働きかけ今後に活かしたいと思います。
ご丁寧、ご親切にありがとうございました！
（初心者） 2017/03/20(月) 18:47
＞依頼用の原紙シートが入っていたことが原因のようです。削除しました。
１．「原紙」シートがあったんですか？
それが無かったら、仕事がやりにくいですよね？
以下、原紙シートは温存する数式です。
(1) I2セル =IFERROR(TEXT(INDIRECT(INDEX(REPLACE(Sh,1,FIND("]",Sh),"'")&"'",ROW(A2))&"!A4"),"[>9]yyyy/m;;;")*1,"")
(2) J2セル =IF(I2="","",IFERROR(INDIRECT(INDEX(REPLACE(Sh,1,FIND("]",Sh),"'")&"'",ROW(A2))&"!B16"),""))
それぞれ、下にフィルコピー
※「原紙」シートは、全シートの右端に位置させてください。
２．年情報は不確かなので、年情報部分を表示させなくする為に、 B列の書式を以下に変更してください。
B列のセルの書式(表示形式）を「m"月";@ 」に設定
<集計用 結果図>
行 _A_ __B__ __C__ _D_ _E_ _F_ _G_ _H_ ___I___ ___J___ 1 年月 合計 作業１ 作業２ 2 4月 501 42826 100 3 5月 200 42856 200 4 10月 7000 42826 401 5 43009 7000 6
（半平太） 2017/03/20(月) 19:30
原紙シートの件は私の伝え漏れでした、大変申し訳ありません。
無くても以前のシートをコピーして使用すればいいと現場と話していましたが、
このように便利な方法を教えていただけて助かりました。ありがとうございました！
（初心者） 2017/03/20(月) 22:34
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.