[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『マクロで合計したいのですが・・・・・・・』（ブニセ）
Sub 更新()
Range("AE4:AK25").Value = Range("Q4:W25").Value '「先週までの累計」に「累計」数値を入れる
Range("AE4:AK18").Insert Shift:=xlToRight
Range("C4:I18").ClearContents 'C4:I18をクリアする
Range("C3").Value = Range("C3").Value + 7 '週の最初の日付を7日進める
End Sub
("X4:AD25")のセルに合計を表示したいのですが、データーが次ぎ次ぎと増えていくので、増えた時に合計をしたいのですが、関数ではできないので、マクロではと思い、質問しています。
増えていくと、AL４〜AS4.AZ4.BG4と７つずつの合計を出そうと関数を考えましたが、うまくいかないので、マクロだったらできるのかと思いました。初心者・素人ですが、教えて下さい。
Range("AE4:AK18").Insert Shift:=xlToRightこれでデーターがその都度増えるのですが、起点はAL４であり、FO4までになります。ただ、データーが増えていくと、SUM関数ではずれてしまって、その都度役にたちません。
よろしくお願いします。
＜ 使用 Excel：Excel2003、使用 OS：Windows7 ＞
書き不足がありました。X4:AD25の各セルに合計したいのです。
（ブニセ） 2016/05/03(火) 19:52
コードをポンと掲示して、要件、レイアウトを理解してほしいというのは、いささか手抜きですねぇ。
どうやら４行目〜２５行目の領域を相手にする処理のようですが、
Range("AE4:AK18").Insert Shift:=xlToRight
Range("C4:I18").ClearContents 'C4:I18をクリアする
このあたりは 4行目〜18行目を相手にしていて、いったんどんなものがどこにあるのかなと。 C列〜I列にはどんんあものがあるのかもわかりません。
Q列〜W列をコピペしたAE列〜AK列は、どんなデータなのか、 そもそも、X列〜AD列には、どこの数字を合計したいのか。
手を抜かず、しっかりと、正しいレイアウト説明と条件を説明してください。
（β） 2016/05/03(火) 20:08
こんばんわ。
合計範囲を可変にしたいと言う事ですね。 でもその時々に対象にする合計範囲を決める法則性が質問内容からは全く分かりません。
＞AL４〜AS4.AZ4.BG4と７つずつの合計 これってセルは9つですよね？
後、X4〜AD25に合計を表示するとの事ですけど、それぞれのセルはどの範囲を参照してるんですか？
範囲を可変にして合計を求めると言うなら、法則性が分からないとマクロでも求められません。
逆に法則性が分かれば関数でも求められます。
まずβさんからも言われてる通りレイアウトを提示して、どのような時にはどこの範囲を集計対象にするか明確にして下さい。
（sy） 2016/05/03(火) 21:25
すみません、言われる通りで大変失礼しました。
＞このあたりは 4行目〜18行目を相手にしていて、いったんどんなものがどこにあるのかなと。
C列〜I列にはどんんあものがあるのかもわかりません。
実は、出席簿をExcelで処理しようとしたものです。
月 火 水 木 金 土 日
C列 〜 H列
出停・忌引等 欠 席 遅 刻 早 退 大 会
遅延による公欠 イフルエンザ等 公欠
J列 〜 P列
＞Q列〜W列をコピペしたAE列〜AK列は、どんなデータなのか、
週ごとの合計です。
＞そもそも、X列〜AD列には、どこの数字を合計したいのか。
例えば１週から２０週までの合計なのですが、週ごとに増えていきま
す。１週目ははAL４は２週目AS４３週目ZB4４週目BG4・・・・とFO4
まで。
X列〜AD列の
例えばX列は１週目ははAL４は２週目AS４３週目ZB4４週目BG4・・・・
X４に合計でるようにしたい。
書きこみは編集からではなく、下のコメント欄に記入してアップしてくださいね。
で、４行目〜２５行目が対象領域なんですか？ それとも、４行目〜１８行目なんですか？
（β） 2016/05/03(火) 22:48
３週目ZB4というのがよくわかりませんが、1週目が AL4から始まる、2週目が AS4から始まる、3週目が AZ4から始まる、・・・・ 最後の20週目が FO4 から始まる ということであれば、
>>増えた時に合計をしたいのですが、関数ではできないので
なぜ、できないのですか？
たとえば X4 : =AL4+AS4+AZ4+・・・・+FO4
これを右にフィルコピーし、下にフィルコピーでは、だめなんですか？
（β） 2016/05/03(火) 23:00
↑ もちろん Insert方式ではなく、たとえば
Range("AS4:AS25").Resize(, 8 * 19).Value = Range("AL4:AL25").Resize(, 8 * 19).Value
といった転記コードが前提ですが。
余談：現在のInsert方式だと、20週目の右に押し出された領域をクリアしておかないと ４０年ほどしたらエラーになるでしょうね。 もっとも、その時も、このコードが動いているという可能性は低いでしょうし、何より、４０年後も 今と同じエクセルが動いているとは思えませんけど。
（β） 2016/05/04(水) 06:04
Sub 更新()
Range("AE4:AK25").Value = Range("Q4:W25").Value '「先週までの累計」に「累計」数値を入れる
Range("AE4:AK18").Insert Shift:=xlToRight
Range("AS4:AS25").Resize(, 8 * 19).Value = Range("AL4:AL25").Resize(, 8 * 19).Value
ここに入れました？（また、２０週分できれば完成できます）
Range("C4:I18").ClearContents 'C4:I18をクリアする
Range("C3").Value = Range("C3").Value + 7 '週の最初の日付を7日進める
End Sub
このようなマクロでいいのでしょうか。
＞たとえば X4 : =AL4+AS4+AZ4+・・・・+FO4
これだと、新しいデーターを送る度にセルの値も移動しませんか。
＞ これを右にフィルコピーし、下にフィルコピーでは、だめなんですか？
面倒だなと思い、次のような関数を教えてもらいました。
SUMPRODUCT(AE4:HD4*(MOD(COLUMN(AE4:HD4)-30,7)=1))
30.31.32.33.34.35.36.37 30〜セルこどに変更して関数を教えてもらいましたが、できませんでした。
＞Range("AE4:AK18").Insert Shift:=xlToRight
それと、修正出来るものなら、あたらしいデーターができたら、先に送るようにはできていますが、どうしても空欄を作って先に送ることになっています。それも解消できればとも思っています。
わがままでもう訳ありません。
（ブニセ） 2016/05/04(水) 09:56
関数の部分だけ、
SUMPRODUCT(AE4:HD4*(MOD(COLUMN(AE4:HD4)-30,7)=1))
を
=SUM(INDEX(INDIRECT("RC38:RC177",0)*(MOD(COLUMN(INDIRECT("A1:EJ1"))-1,7)=COLUMN(A1)-1),0))
AD列および25行までコピー
に変えてみて下さい。 挿入などがあった場合でも、常にAL列から7列置きで同じ行の20週分のデータを合計します。 セル毎に数式を変更する必要もありません。
ただ上の式ではAE〜HDまでの26週分合計になってますが、AL〜FO4までと記載されていたので、私の提示では20週分になってますけど、 どちらが本当なんですか？
（sy） 2016/05/04(水) 10:09
すでにコメントしていますが、コードを（しかも、うまくいかない？コードを）ぽんと投げかけられて どうでしょうといわれても、検討できません。
どういうレイアウトかという具体的な説明と、そのレイアウト上で、何をどうして、新しくどんなレイアウトになるのか そこのところを、【言葉】で具体的に説明いただけないのはなぜなんでしょうか？
>>これだと、新しいデーターを送る度にセルの値も移動しませんか
数式の参照が移動するということですか？ 移動しないように提案しているつもりです。 【移動しませんか？】と聞く前にやってみて、移動するかどうかを確認してはいかが？
>>どうしても空欄を作って先に送ることになっています。それも解消できればとも思っています。
コメントしたようにレイアウトがどうなっていて、そこで、どんなことをしたいのかが見えませんので 想像しているだけですが、その想像で、Insertして先に送る ということが発生しないように 値コピーを提案しているのですが、やってみて だめだったということですか？
>>面倒だなと思い、次のような関数を教えてもらいました。
？？？？ どんな数式でも、フィルコピーになりますよね？ それとも１つのセルにいれるだけで、X4から始まる領域全体のセル計算をさせたい なんてことを言ってます？
>>このようなマクロでいいのでしょうか
繰り返しになりますが、レイアウトと要件がわからないのでコメントできません。 できませんが、少なくとも、私は Insert はやめましょう と提言してます。
（β） 2016/05/04(水) 11:32
このようなマクロでいいのでしょうか
繰り返しになりますが、レイアウトと要件がわからないのでコメントできません。 できませんが、少なくとも、私は Insert はやめましょう と提言してます。
ありがとございます、連休でどうしてできなかったことができて、ほんとにうれしいです、２０週入れてみました、きちんと累積できました。また、計算してみたら２０週で充分でした、余裕をみて２５週としたと思います、失礼しました。
SUM(INDEX(INDIRECT("RC38:RC177",0)*(MOD(COLUMN(INDIRECT("A1:EJ1"))-1,7)=COLUMN(A1)-1),0))
AD列および25行までコピー
（ブニセ） 2016/05/04(水) 11:53
Range("AL19") = "=SUM(AL4:AL18)"
上記のような、コードを入力して合計を出しました。というのも、新しいデーターを右方向にずらすことを
やると、普通に関数を使うとずれてしまいました。原因がわからないので、このコードをいれたらできました。そこで、これが起点で７つごとに同じように繰り返して計算をさせたいとしたら、どのようなコードにしたらよいでしようか。教えて貰えませんか。
（ブニセ） 2016/05/04(水) 14:51
AL4+AS4+AZ4+・・・・+FO4
（ブニセ） 2016/05/04(水) 14:55
勘違いしてましたので削除しました。 すいません。
（sy） 2016/05/04(水) 15:52
どうも、ピリっとしませんねぇ。 レイアウトと、そこで、どこから何をどこに展開していきたいのか、何度も教えてくださいとお願いしているのですが なぜ、無視されるのかなぁ・・・
さらに、数式がずれないように対応したつもりで、そちらから
>>２０週入れてみました、きちんと累積できました
と、レスがあって、やれやれと思いきや、再び
>>新しいデーターを右方向にずらすことを やると
？？？？ また、データを右にInsertで動かしたんですか？？
そもそも、AL列から始まる ２０ブロックの15行（あるいは 22行？ これも、どちらが正しいのか何度か聞いているのに返事はないですね？）、8列の領域。 これが、どんな値なのか、あるいは数式なのか、数式だとしたらどんな式をいれたいのか。
式といっても =SUM(AL4:AL18) といったものではなく、どの領域の何をどうしたい（合計とか）のかを【言葉】で説明していただきたい。
>>言い忘れました。７つずつの場所です。AL4+AS4+AZ4+・・・・+FO4
言い忘れて追記しているのかもしれませんが、この意味もわかりません。
★こちらが疑問に思って質問していることに対しては、きちんと回答してください。 で、その上で、そちらの質問は、だれが読んでもわかる文章で書いてください。
これは、【関数】がよくわからないからとか【ＶＢＡ】がよくわからないからということとは 全く関係がありません。
（β） 2016/05/04(水) 17:11
やりたい事はこうですか？ 1、X4〜AD25までのセルに、同じ行のAL4から7行置きに20週分、AL4+AS4+AZ4+・・・・+FO4のセルを相対参照して合計する。 2、AL19からFU19までのセルに、AL4からAL18のセルを相対参照で合計する。 3、AE4からAK18を毎週1回、右方向にセルを挿入してずらしてから、何かデータを記録する。
問題になってるのは、こうですか？ 1、X4からAD25の計算式の参照先が、挿入のたびにずれる。 2、AL19からFU19の計算式の参照先が、挿入のたびにずれる。
1に関しては私の提示した式でも良いですが、2も同じようにINDIRECTを使えば回避できますが、 そもそもそれ以前に挿入を行わなくても良い方法をβさんから提示されていると思います。
挿入を行わなければ参照先がずれる心配も無いし関数自体も簡単になるし、 更新作業もマクロで行えば一石二鳥になるので、βさんのマクロ案を積極的にお勧めします。
（sy ） 2016/05/04(水) 17:35
＞どうも、ピリっとしませんねぇ。
レイアウトと、そこで、どこから何をどこに展開していきたいのか、何度も教えてくださいとお願いしているのですが なぜ、無視されるのかなぁ・・・
＞これは、【関数】がよくわからないからとか【ＶＢＡ】がよくわからないからということとは
全く関係がありません。
すみません、おっしゃるとおりですね。自分の早とちりというか、説明が下手というか、大変ご迷惑をおかけして恐縮です。自分の知っている範囲でやってみます。レイアウトが画像などで示せればわかりやすいとおもいますが、そのようなすべも知らず、申し訳ありません。この件については、これで終了したいと思います。（sy）さんを含めて、ご協力ありがとうございました。
（ブニセ） 2016/05/04(水) 18:18
話が噛み合ってないなぁ。。。
出席簿と言う事は学校の先生ですか？ でしたら生徒に教えたり、生徒の質問に答える時には、要点を順番に1つづつ整理して会話してないんですか？
まずご自身の色々と言いたい事は申し訳ないですけど分からないので、此方から順番に1つづつ分からない事を聞いています。 此方が言ってる質問の部分のみ順番に答えて頂けないですか。
当てずっぽうですけど、取り敢えず、AL19〜FU19までの式は、こう言う事だと思います。
=SUM(INDIRECT("R4C:R18C",0))
FU19までコピー
で行けると思います。
マクロでやるなら、コードの一番最終、End Sub のすぐ上に
Range("X4:AD25").Formula = "=AL4+AS4+AZ4+BG4+BN4+BU4+CB4+CI4+CP4+CW4+DD4+DK4+DR4+DY4+EF4+EM4+ET4+FA4+FH4+FO4"
Range("AL19:FU19").Formula = "=SUM(AL4:AL18)"
を記述して下さい。 これで挿入とか関係無くなります。
（sy ） 2016/05/04(水) 19:14
ちゃんと動く数式があるなら、
セルを横にずらしたあとで、もういちど数式を入力したらどうですか？
もちろんマクロで。
※あまり良いレイアウトだと思いません。
余裕があるときに変更検討されるようおすすめします。
（ 佳 ） 2016/05/06(金) 07:08
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.