[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『該当月に貼り付け』（akio）
いつもお世話になり有難うございます。
下記に記載の、一つのファイルに１０のシートがあります。から以降をお読み
くださり２、の当月が、１２月分の収支表であった場合に、１、の各
部門（各シート）の１２月実績に数字を貼り付ける方法を教えて下さい。
式が入っている行は、貼り付けの際にも式のままです。（１、と２、の行は、
同じ式が入っています。）
１、の９つのシート名をsheet1,からsheet9とします。
２、のシート名を”月次”とします。
コードの実行時のイメージは、マクロボタンを押すと何月分ですか？と聞いて
きます。私は、月の数字を入れます。 ＸＸ月でよろしいか？ＹＥＳ ｏｒ ＮＯ のボタンが表示されます。正しければＹＥＳのボタンを押せばＶＢＡが
実行されます。
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
一つのファイルに１０のシートがあります。
１、その内、９つのシートは、９つの部門を表しています。縦に勘定科目、
横に４月実績、５月実績−−−年間合計の表です。９つのシートは、列、行と
も同じフォームです。
２、残り一つのシートは、当月の部門別の収支表です。（会計ソフトのデータ
をエクセルに落としたデータです。）縦に勘定科目（９つのシートと同じ勘定
科目で行の位置も同じですが、列はＡ列目からです。）
横にＢ列 全社計、Ｃ列からＫ列までが９つの部門です。
１、の詳細は、下記の通りです。
横列に、Ｙ５（科目）Ｚ５（4月実績）ＡＡ５（5月実績）ＡＢ５（6月実績）
ＡＣ５（7月実績）ＡＤ５（8月実績）ＡＥ５（9月実績）ＡＦ５（上半期実
績）ＡＧ５（10月実績）ＡＨ５（１１月実績）ＡＩ５（１２月実績）ＡＪ５
（１月実績）ＡＫ５（２月実績）ＡＬ５（３月実績）ＡＭ５（下半期実績）Ａ
Ｎ５(年間合計）の順に並んでいます。
縦列に
Ｙ５（科目）
Ｙ６（この行は、空白です）
Y7（売上高）
Y8（この行は、空白です）
Y9(売上原価）
Ｙ１０（売上総利益、１０行目は、式=+Z7-Z9が入っています）
Y11（営業費用）
Y12（人件費）、１２行目は式が入っています=SUM(Z13:Z24)
Y13（給与）
Y14（顧問料）
Y15(手当）
Y16（賞与）
Y17(退職金）
Y18(法定福利）
Y19（福利厚生費）
Y20(アルバイト料）
Y21(研修費）
Y22(通勤交通費）
Y23（報酬）
Y24（ｘｘ引当金）
Y25(経費、２５行目には式が入っています=SUM(Z26:Z48)）
Y26（広告費）
Y27（水道光熱費）
Y28(外注加工費）
Y29(図書新聞代）
Y30（交際費）
Y31(寄付金）
Y32（調査費）
Y33（旅費交通費）
Y34（衛生費）
Y35（制服費）
Y36(保険料）
Y37（家賃）
Ｙ３８（リース料）
Y39(管理維持費）
Y40(通信費）
Y41(保安費）
Y42（減価償却費）
Y43(会費等）
Y44（租税公課）
Y45(修繕費）
Y46（消耗品費）
Y47(会費）
Y48(雑費）
Y49(営業経費）、４９行目は式が入っています=+Z12+Z25
Y50（営業損益）、５０行目は式が入っています=+Z10-Z49
以上ですが、説明が細かくなって帰ってわかりずらくしているかもしれません
が、よろしくお願いいたします。
＜ 使用 Excel：Excel2013、使用 OS：Windows10 ＞
上記、＞１の９つのシート名をsheet1,からsheet9とします。
sheet 1→sheet 9をsheetＡ から sheetＩに変更します。
それに伴い９つの部署名を やはりＡからＩにします。
以上です。お願いいたします。
（akio） 2017/12/06(水) 00:14
と記載していますが、私の記載間違いで、１２月実績に数字を貼り付ける方法を教えて下さい。と書いて
いますのは、１２月のみではなく（例にあげただけです）任意の月（４月から３月迄）をさします。
すみません。
（akio） 2017/12/06(水) 08:04
そして、部門毎のシート構成は判りましたが、収支表シートのレイアウトは判りません。 マクロなど作らなくとも、A部門の12月分売上高ならば「=SheetA!AI7」のように参照式を書くだけなので、これを全部門分並べるだけではないでしょうか？（何月分を、なんて聞く必要も無く、12ヶ月分のシートを最初から用意するだけ） これなら、マクロなんて使わずにできますよ。
（???） 2017/12/06(水) 09:40
＞マクロなど作らなくてもA部門の12月分売上高ならば「=SheetA!AI7」のように参照式を書くだけなので、
＞これを全部門分並べるだけではないでしょうか？
２の収支表は、月が変わっても常にＡ列から貼り付けをします。したがって、１２月のみの売上ならおっしゃ
るように収支表のＳｈｅｅｔ参照でマクロを使わず出来ることはわかるのですが、追記記載で書きましたよう
に、１２月のみではなく毎月、帳簿が締まってから１月、２月、３月と、順次１、の各部門（各シート）の当
該月の実績欄に貼り付けをしなければなりません。
例えば、２、の収支表が、１２月分で、Ａ部門の（Ｃ７）の売上高が１，０００とした場合に、１、の
Ａ部門の１２月の売上欄に「＝sheet"月次"ｃ７」の参照式を書きます。ところが２、の収支表のセル位置が
同じところですから、次に２、の収支表が１月分に変わってＡ部門の（Ｃ７）の売上高が、
２，０００となった場合には、元々１、のＡ部門の１２月売上欄に「＝sheet"月次"ｃ７」の参照式をしている為、１２月の売上は、１月の２，０００に変わるのではないでしょうか？
１２月の売上は１，０００、１月の売上は２，０００にしたいのです。当然後の月で変わっても前の月の
数字はそのままにしたいのです。
＞収支表シートのレイアウトは判りません。
最後に収支表のレイアウトを下記に記載します。
収支表のレイアウトは、
横の行の項目名は、Ａ５は、科目 Ｂ５は、全社計 Ｃ列からＫ列までは、Ａ部門からＩ部門の９つの部門名です。
（Ｂ５の全社計の式は、Ｃ列からＫ列の９つの部門の合計を計算しています。）
縦列に
Ａ５（科目）
Ａ６（この行は、空白です）
Ａ7（売上高）
Ａ8（この行は、空白です）
Ａ9(売上原価）
Ａ１０（売上総利益、１０行目は、式=+Z7-Z9が入っています）
Ａ11（営業費用）（この行は、Ａ１１のみ営業費用の文字だけで他の列は空白です）
Ａ12（人件費）、１２行目は式が入っています=SUM(Z13:Z24)
Ａ13（給与）
Ａ14（顧問料）
Ａ15(手当）
Ａ16（賞与）
Ａ17(退職金）
Ａ18(法定福利）
Ａ19（福利厚生費）
Ａ20(アルバイト料）
Ａ21(研修費）
Ａ22(通勤交通費）
Ａ23（報酬）
Ａ24（ｘｘ引当金）
Ａ25(経費、２５行目には式が入っています=SUM(Z26:Z48)）
Ａ26（広告費）
Ａ27（水道光熱費）
Ａ28(外注加工費）
Ａ29(図書新聞代）
Ａ30（交際費）
Ａ31(寄付金）
Ａ32（調査費）
Ａ33（旅費交通費）
Ａ34（衛生費）
Ａ35（制服費）
Ａ36(保険料）
Ａ37（家賃）
Ａ３８（リース料）
Ａ39(管理維持費）
Ａ40(通信費）
Ａ41(保安費）
Ａ42（減価償却費）
Ａ43(会費等）
Ａ44（租税公課）
Ａ45(修繕費）
Ａ46（消耗品費）
Ａ47(会費）
Ａ48(雑費）
Ａ49(営業経費）、４９行目は式が入っています=+Z12+Z25
Ａ50（営業損益）、５０行目は式が入っています=+Z10-Z49
以上です。
（akio） 2017/12/06(水) 12:16
下記の事は手動で行っている と言うことで
その作業を自動記録とって 整理して
それから 自動記録では出来ない部分を質問されたらどうですか？
今までは、２、の収支表が決まれば、各部門の各科目の数字を１、の９つの部門にコピペをしていました
（Q::） 2017/12/06(水) 13:16
まず、収支表シートの1行目を、作業領域＆元情報格納のために利用します。文字を見せたくないなら文字色を白にでもするとか、グループ化して見えなくするとか、工夫してください。1行目は既に何か入力されていて壊したくない、とかならば、好きな行に変えて、数式を修正してください。
A1セルは、対象月を数字で入力してください。セルの書式設定で、"月" を追加表示しても良いでしょう。
B1セルは、「=INDEX({"AJ","AK","AL","Z","AA","AB","AC","AD","AE","AG","AH","AI"},A1)」としてください。
C1セルは、A部門のシート名を入力。 以降、D1セルはB部門、E1セルはC部門…と、シート数分横に並べてください。
後は、例えばC7セルはA部門の売上高ですね？ ここには「=INDIRECT(C$1&"!"&$B$1&ROW())」という数式を入力してください。
この式を縦横にドラッグして、必要分コピー。 6行目のように代入不要な行は式を消してください。
（???） 2017/12/06(水) 14:14
逆ならば、収支表を12シートに分けて、各部門毎シートはそれぞれの収支表シートを参照する式で良いと思います。必要な命令は、上で書いたINDIRECT関数を利用すれば良いでしょう。未来分の収支表は空欄にしておけば良いですが、シート自体存在しないようにするならば、IFERROR関数も利用してください。
（???） 2017/12/06(水) 14:31
あとは、A1セルを利用し、ここに列名を書いておきます。A部門ならば、「C」ですね。 そして、4月分売上高ならば Z7セルに「=IFERROR(INDIRECT("収支表"&Z$5&"!"&$A$1&ROW()),"")」と書いてから、縦横にコピーしてください。
（???） 2017/12/06(水) 15:02
下記はこのコメント以前でのアップ出来なかった記載分です。
（???） 2017/12/06(水) 14:14 （???)様がアップされるときに私も下記記載をアップしようとしましたら
重なりアップ出来ませんとのメッセージとなりました。
一応下記に記載しておきます。
＞他人が余計な時間を使うのは気にしない、
気にしないのは逆で、いつもこんな理解しがたい私に、時間をかけて親切に教えて頂き感謝しています。
事をはっきりお伝えしておきます。
と言いながら（？？？）様の（???） 2017/12/06(水) 14:31を手掛けているところです。
有難うございます。
＞例えばC7セルはA部門の売上高ですね？ ここには「=INDIRECT(C$1&"!"&$B$1&ROW())」という数式を入力してください。
という事で式（ =INDIRECT(C$1&"!"&$B$1&ROW() ）をいれましたが、＃ＲＥＦ！ になります。
どこがわるいのでしょうか？
（？？？）様からコメントを頂く前に、Q様より 2017/12/06(水) 13:16頂いたことで、
じっくり考えていました。そして各月、記録をするのであれば、同じシートの中で１２の収支表を
作り各月を９つのシートに貼り付けるしかない。
ただ月が変わるところで列を開けずに続けて表を貼り付けすれば、最初の参照する式をドラッグすれば
やりやすいと思いました。
（akio） 2017/12/06(水) 15:52
「=INDIRECT(C$1&"!"&$B$1&ROW()」これは、部門別シートがinputの場合ですね。C1セルにシート名、B1セルに対象列が書かれていなかったのでしょう。 実際に必要な処理は、「（???） 2017/12/06(水) 14:31」と「（???） 2017/12/06(水) 15:02」 になるかと思いますので、試行錯誤してみてください。
月別については、収支表を12シート作る方法と、収支表1シート内に12ヶ月分の情報全てを書き込む方法があるでしょう。後者は現状の収支表作成手順と変わってしまうので、シートは増えますが、前者を薦めた訳です。
式のコピーについては、inputもoutputも同じ行なので、これを利用すればドラッグしてコピーするだけで済むようになるので、ミス無く作業できるでしょう。セルの指定に $ を利用しているのも、ドラッグした際に増加させるかさせないかを指定しています。Excelの計算式の基本なので、マスターしてください。
（???） 2017/12/06(水) 16:54
＞具体例も書いておくと、部門毎シートの5行目Z列以降は「4月実績」ではなく、「4」と入力しておき、セル＞の書式設定で「0"月実績"」とでもしておきます。 収支表シートは「収支表4」のように、後ろに月を追加＞し、12ヶ月分作成してください。
ここは、全て言われたように作成しました。
＞あとは、A1セルを利用し、ここに列名を書いておきます。
Ａ１セルを利用しここに列名を書くとは、具体的にどのシートのどこのセルですか？
何故ならＡ１に値するのは、収支表のＡ１かないはずです。Ａ１は、月を入れているでしょう？
＞A部門ならば、「C」ですね。 そして、4月分売上高ならば Z7セルに「=IFERROR(INDIRECT("収支
＞表"&Z$5&"!"&$A$1&ROW()),"")」と書いてから、縦横にコピーしてください。
コピーすれば＃ＲＥＦになります。自分では、一生懸命考えてしているのですが、わからなくなってきました。
収支からのデータを受ける部門毎の各式、及び収支の各式の配置がわからなくなってきました。
（akio） 2017/12/06(水) 20:42
後の指示では、A1セルは月ではなく、該当部門のデータがある列を書いておくように変えました。収支表シートは、C列が部門Aで、D列は部門Bのように、1部門1列なんですよね？ だったら、部門AシートのA1セルは「C」、部門Bシートならば「D」のように書いてください。
そうすれば、「=IFERROR(INDIRECT("収支表"&Z$7&"!"&$A$1&ROW()),"")」の式は、「収支表4!C7]のセル（部門A、4月分売上高の場合）を参照する感じになりますよね？
（???） 2017/12/07(木) 09:15
収支表シートのA1セルに月を書いても、それが12個に増やしたどの収支表シートに書いてあるのか判らない訳で、だったらシート名に月を付加してしまえ、という考えです。
（???） 2017/12/07(木) 09:19
＞後の指示では、A1セルは月ではなく、該当部門のデータがある列を書いておくように変えました。
有難うございます。
＞収支表シートは、C列が部門Aで、D列は部門Bのように、1部門1列なんですよね？
はい。そうです。
＞部門AシートのA1セルは「C」、部門Bシートならば「D」のように書いてください。
はい、その様に書きます。
＞あと、A1セルと言っても、最初の指示では収支表シート、後の指示では部門毎シートのA1セルですので、お＞間違えなく。
はい、承知いたしました。
ただ、冒頭に、
横列に、Ｙ５（科目）Ｚ５（4月実績）ＡＡ５（5月実績）ＡＢ５（6月実績）
ＡＣ５（7月実績）ＡＤ５（8月実績）ＡＥ５（9月実績）ＡＦ５（上半期実 績）ＡＧ５（10月実績）ＡＨ５（１１月実績）ＡＩ５（１２月実績）ＡＪ５ （１月実績）ＡＫ５（２月実績）ＡＬ５（３月実績）ＡＭ５（下半期実績）Ａ Ｎ５(年間合計）の順に並んでいます。
と書いていますので、部門毎シートのA1セルでななくＹ１セルでよろしですね。
＞収支表シートのA1セルに月を書いても、それが12個に増やしたどの収支表シートに書いてあるのか判らない＞訳で、だったらシート名に月を付加してしまえ、という考えです。
理解しました。それであれば、収支表のA１の月は、消去してもいいですよね。
（akio） 2017/12/07(木) 13:43
> ただ、冒頭に、 > > 横列に、Ｙ５（科目）Ｚ５（4月実績）ＡＡ５（5月実績）ＡＢ５（6月実績） > > ＡＣ５（7月実績）ＡＤ５（8月実績）ＡＥ５（9月実績）ＡＦ５（上半期実 > 績）ＡＧ５（10月実績）ＡＨ５（１１月実績）ＡＩ５（１２月実績）ＡＪ５ > （１月実績）ＡＫ５（２月実績）ＡＬ５（３月実績）ＡＭ５（下半期実績）Ａ > Ｎ５(年間合計）の順に並んでいます。 > > と書いていますので、部門毎シートのA1セルでななくＹ１セルでよろしですね。
部門AシートのZ7: =INDIRECT("収支表"&Z$5&"!"&$A$1&ROW())
（余分な情報を削って、判りやすくしました）
こうなので、$A$1 セルに書かれた文字列を、そのまま使っています。ドラッグコピーしても$A$1ですよね。 だから、式を変えないなら、部門AシートのA1セルを「C」です。 Y1セルなんて、一切使いませんよ。
もしY1セルに「C」と書くように変えたならば、式も $Y$1 に変えてください。
（???） 2017/12/07(木) 15:54
＞こうなので、$A$1 セルに書かれた文字列を、そのまま使っています。ドラッグコピーしても$A$1ですよ。
＞だから、式を変えないなら、部門AシートのA1セルを「C」です。 Y1セルなんて、一切使いませんよ
これは、理解できます。絶対指定ですから。
＞式を変えないなら、部門AシートのA1セルを「C」です。 Y1セルなんて、一切使いませんよ。
私：部門毎シートのA1セルでななくＹ１セルでよろしですね。
−−−落着いて考えればわかるものを時間を取らせましてすみませんでした。
今夜、実行ます。
これぞ時間を取らせてしまい申し訳ございませんでした、と共に有難うございました。
（akio） 2017/12/07(木) 16:49
収支表４、と部門Ａシートの私が入れました式を下記のように提示します。
間違いを教えて頂けますか？
シート名：収支表４ （以降のシート名は、収支表５から収支表３迄作っています）の中で
Ａ１に何を入れたらよろしいか？（色々試しましたがうまく行きませんでした。）
＞後の指示では、A1セルは月ではなく、該当部門のデータがある列を書いておくように変えました。
＞収支表シートは、C列が部門Aで、D列は部門Bのように、1部門1列なんですよね？
＞だったら、部門AシートのA１セルは「C」、部門Bシートならば「D」のように書いてください。
その部門ＡシートのＡ１セルとは収支表のＡ１ですね、そこに、「Ｃ」「Ｄ」−−のように書いて下さい
一つのセルにどうやってＣ、Ｄ−−を入れることが出来るのでしょうか？
試しに収支表Ａ１にＣを入れても＃ＲＥＦになります。
Ｂ１は、=INDEX({"AJ","AK","AL","Z","AA","AB","AC","AD","AE","AG","AH","AI"},A1)
を入れています。（インプットが逆になったので、いらなくなったのでしょうか？）
Ｃ１は、部門Ａ、Ｄ１は、部門Ｂ、−−−Ｋ１は、部門Ｉを入れています
今度は、部門Ａシートの中で
Ｚ５からＡＬ５迄、数値４から３を入力しています。（書式で”実績”の文字を入れています）
ｚ７に=INDIRECT("収支表"&Z$5&"!"&$A$1&ROW())を入れて右にコピーをＡＬの３月迄しています。
しかし全てが＃ＲＥＦとなっています。
以上です。
（akio） 2017/12/08(金) 01:59
エラー原因ですが、シート名が「収支表4」になっているか、よく確認してみてください。数字が全角になっていたり、余分な空白が付いていたりしませんか？ 全角半角の違いや、大文字小文字の違いは、コンピュータにとっては大事です。（文字コードが違うと、別物と判断されます） だから、回答者はこの辺りを曖昧に答えることはありませんので、ちゃんと一字一句従ってみてください。（たまに書き込みミスはあるのはご容赦…）
（???） 2017/12/08(金) 09:09
収支表で、4月のA1は、B1と同様に何も入れな
くていいのですか？
部門AシートZ7の式は、合っていますか？
（akio） 2017/12/08(金) 16:18
部門AシートZ7の式は、改めて貼り直すと、以下です。（途中、手打ちを間違えたときがありましたが、5行目の月の数字を見るのが正解です）
=IFERROR(INDIRECT("収支表"&Z$5&"!"&$A$1&ROW()),"")
試しに、部門毎シートのどこかに「="収支表"&Z$5」と式を入力してみて、「収支表4」と表示されているか、これがシート名と一致しているかを確認してみてください。
（???） 2017/12/08(金) 17:36
＞部門AシートZ7の式は、改めて貼り直すと=IFERROR(INDIRECT("収支表"&Z$5&"!"&$A$1&ROW()),"")
式は、このとおりにしまして入りました。結果は””です。
＞試しに、部門毎シートのどこかに「="収支表"&Z$5」と式を入力してみて、「収支表4」と表示されている
＞か
「収支表4」と表示されました。
＞これがシート名と一致しているかを確認してみてください。
シート名：収支表4と一致しました。
Ａ１に何を入れるのですか？を聞く前に、前に、
インプットが収支表と勘違いされた時に、私が、部門別毎の$Ａ$１とはどこですか？
４月は、Ｚ列ですからＡ１がわかりません、と言いましたら？？？様は、部門別毎ではなく
絶対指定＄＄だからどこへ移動しても収支表の$Ａ$１になるのですと言われました。
でも今は、
＞収支表の1行目は、一切記入なしでOKです。
といわれていますから、改めて$Ａ$１には、何を入れるのかがわかりません。
しかしこの件で、本当に時間を取って頂き申し訳なく思っています。
（akio） 2017/12/08(金) 20:02
（akio） 2017/12/09(土) 00:17
１、各部門シートに=IFERROR(INDIRECT("収支表"&Z$5&"!"&$A$1&ROW()),"") を入れました。
２、各部門シートのＡ１に収支表の各部門の列を入れました。
３、収支表のシートに収支表4〜収支表３迄入れました。
改めて、上記１、の関数の意味は、各部門シートのＺ５から4567---123（書式で実績記載）と右に数字を入れ
たことによって、その数字と"!"シートをひっつけて例えば4実績だと""収支表4のシートを選ぶことになるのですね。
そして各部門シートの$A$1に入れた収支表の部門の列の行分の数字を引っ張てくる ということなんですね。
以上、本当に有難うございます。感謝です。
（akio） 2017/12/09(土) 23:31
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.