Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Home Premium : Excel 2010)
セルの結合時の繰り返し処理
投稿日時: 17/12/22 01:34:05
投稿者: ダイハード

みなさんこんにちは
質問させて下さい。
下記の表があります
 
M4〜M6 N4〜N6
M7〜M10 N7〜N10
M11〜M12 N11〜N12
はセルが結合されています。
 

	L	M	N
3	計画	在庫	不足
4	12	115	-87
5	16		
6	0		
7	12	100	0
8	16		
9	12		
10	60		
11	16	44	-12
12	16		
13	30	26	4

Range("L4") には”計画”
Range("M3")には”在庫”
Range("N4")には”不足”の文字が入力されており、
Range("L4:L〇〇")には各数字が入力されています。
〇〇はM列、N列都度違いますが同じです
M列、L列にはセルが結合された部分があります。
結合されている行数は都度違いますがM列:N列は同じです。
この時Range("N4")=SUM("L4:L6")-M4を計算しRange("N7")に移動そして
       Range("N7")=SUM("L7:L10")-M7を計算しRange("N11")に移動そして
       Range("N11")=SUM("L11:L12")-M11を計算しRange("N13")に移動そして
       Range("N13")=SUM("L13:L13")-M13という計算式順次組入れるマクロを組みたいのですが
計算式とセルの移動は(最初のActiveCellはN4です)
        Dim buf As Long
         buf = ActiveCell.MergeArea.Rows.Count
         ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:R[" & buf - 1 & "]C[-2])-RC[-1]"
        'セル移動
         ActiveCell.Offset(0, 1).Activate
         ActiveCell.Offset(buf, 0).Activate
         ActiveCell.Offset(0, -1).Activate
としましたが〇〇行まで自動計算したいのですがその方法が解らず投稿しました。
どなたかアドバイスをお願いします。
欲をいえばM列の結合状態をN列にも反映させ計算式を入れたいです。
 
 

回答
投稿日時: 17/12/22 02:19:46
投稿者: もこな2

最近R1C1形式の投稿をちらほら見かけるけどトレンドなんだろうか・・・・それはさておき確認です。
セルの移動は必須ですか?もとよりセルの移動ってなんですか?
セルの値や計算式を設定するためにSelectやActiveにする必要はありませんよ。
 
ちょっとご質問が読み込めてませんが、ざっとみると、セル結合された箇所をみつけて、そこにSUM関数を使った数式を入れたいってことのように思えますがその通りでしょうか?
 
自動計算っていうのもわかりません。
値を入れればいいのか、数式をいれればいいのか・・・

回答
投稿日時: 17/12/22 02:49:51
投稿者: もこな2

こういうことかな。。。
  セル結合されている行数は不定だけど、列範囲はM列〜N列に固定。
  セル結合されていたら、結合範囲の1行上のN列にを設定
   は、結合範囲の1行上の行〜結合範囲の最終行までのL列の合計から、
   結合範囲の1行上の行のM列の値を減算したもの
  これを結合セルがみつかるたびに繰り返した後に、
  全体の最終行のN列に同じ行のL列からM列を引く数式を設定する。
 
計算しっていうのを値
計算式順次組入れるってのを数式
が欲しいんだと判断しました。
 
セル移動?はどう考えても要らないと思いますが、必要であればどういうために必要なのか追加情報をください。
 

引用:
欲をいえばM列の結合状態をN列にも反映させ計算式を入れたいです。
これもちょっと意味がわからないです。
M列とN列は別々に結合されていて、それを結合したいんですか?
計算式(数式)をどこに入れたいんですか?

回答
投稿日時: 17/12/22 02:55:18
投稿者: もこな2

もこな2 さんの引用:
引用:
欲をいえばM列の結合状態をN列にも反映させ計算式を入れたいです。
これもちょっと意味がわからないです。
M列とN列は別々に結合されていて、それを結合したいんですか?
計算式(数式)をどこに入れたいんですか?
ごめんなさい。最初に書いてありましたね。
引用:
M4〜M6 N4〜N6
M7〜M10 N7〜N10
M11〜M12 N11〜N12
はセルが結合されています。
数式の方はよくわからなかったので追加情報をお待ちします。

回答
投稿日時: 17/12/22 06:11:02
投稿者: simple

横から失礼します。
 
> M列の結合状態をN列にも反映させ計算式を入れたいです。
ということなら、以下ではどうでしょう。

Sub test()
    Dim k As Long
    Dim r As Range
    Dim m_area As Range

    For k = 4 To Cells(Rows.Count, "L").End(xlUp).Row
        Set r = Cells(k, "M")
        If r.Value <> "" Then
            Set m_area = r.MergeArea
            m_area.UnMerge  ' (*)
            r.Offset(0, 1).Formula = _
                "=SUM(" & m_area.Offset(0, -1).Address & ") - " & r.Address
            m_area.Offset(0, 1).Merge
            m_area.Merge  ' (*)
        End If
    Next
End Sub
(*)結合セルを対象としたoffsetは、
   カーソル移動を前提にしてしまい適切な領域を返さないので、
   いったん結合を解いて処理を行い、最後に復元するのがよいかと思います。
 
もっとしゃれた対応方法があるかもしれません。
(一行ごとのチェックを避けることは出来ると思いますが)
 
なお、一般論として、計算領域では結合セルを使わない方が困難は少ないと思います。

回答
投稿日時: 17/12/22 10:34:23
投稿者: もこな2

セルが結合されているかどうかを判定する(MergeCells/MergeAreaプロパティ)
https://www.moug.net/tech/exvba/0050106.html
セルを結合/解除する(Merge/UnMergeメソッド)
https://www.moug.net/tech/exvba/0050105.html
 
とりあえず、セルの結合関係は↑が参考になりそうです。
 
ご質問の処理については、こんな処理方法を考えてみました。
<処理開始>
1.表の開始行、最終行を取得
2.表の開始行から最終行−1のM列を上から順にみていって
3.MergeCellsプロパティで結合状態の真偽を判定
4.結合が真なら、以下を実行
5.  結合範囲の開始行、結合範囲の終了行、結合範囲の1こ上の行、を取得
6.  結合範囲の1こ上の行 と 結合範囲の終了行 をつかって
    1こ上の行の計算してN列に値を設定
7.  結合範囲の開始行、結合範囲の終了行を使ってM列、N列の結合を
    一旦解除して、直後にM開始行〜N終了行をセル結合
8.結合範囲の終了行の次の行から結合状態の真偽判定を再開
9.最終行−1まで判定がおわったら
10.最終列のN行に数式を設定
<処理終了>
 
この掲示板はコードの作成依頼を禁止していますので、とりあえず確認&自力作成のヒントまで。

回答
投稿日時: 17/12/22 12:27:27
投稿者: 細雪

ご質問を拝読していて「?」と思う表現等はいくつかありましたが・・
R1C1?とか移動?とかはもこな2さんがツッコんでくださってる内容に同です。
試しにご自身で書かれたコードをご提示いただければ、
僭越ながら分かる範囲でアドバイスできると思いますので、是非どうぞ。
 
 
 
そんな中、結果だけ出れば良いのかな、ってことで、
Do〜Loop While でやっつける体で考えてみました。
慣れれば10分ほどで書ける内容なのですが・・ちょっと事情があって流れの紹介だけで留めます。
 
 
流れは、
・N列を一度削除(L列を基準に範囲決めて)して上詰め
 ※余計な「結合」があるとうまく動かないと思うので、念のため。
・開始位置をM4セルにセット(Range型変数を使うとラクチンです)。
・繰り返し開始(Do を使います)。
  ・フォーカスを移動したい? 基本的には不要と思われ。
    ・結合している行数を確認(.MergeArea.Rows.Count で取れますね)。
    ・1列右(M列)について(.Offset を使うのが楽かな)。
      ・Sum関数式を入力(メインイベント?)
     .Offset と .Resize を組み合わせて範囲を確認します。
     そこに .Address を使って「範囲を文字列で」取れば、Sum関数に与えられますね。(※)
    ・M列をN列と同じ行数で結合(ってことですよね)。
     これも、.Offset と .Resize を組み合わせて、.Merge してやります。
  ・対象を(M列の)次のセルに移動
・以上、対象が空白ではない間は繰り返し
って感じですか。ちょっと端折りすぎてる感もありますがご容赦ください。
 
(※)のところだけは少し厄介なので・・例えば
  Range("B1").Offset(0, -1).Resize(5, 1).Address
        .0行下の−1列右(つまり1列左)=A1セル
               .そこから5行1列の範囲
                     .のセル番地=A1:A5
などとしてやると、絶対参照で「$A$1:$A$5」が返ってきます。
コレを .Formula = "=SUM(" & 〜〜〜 に渡してやれば良い感じです。
 
 
 
ココから先は全く別な・・ある意味では愚痴と言われそうですが・・・
こういうときって、きっとA:K列のどこかに「M列の結合行数の根拠」がありますよね?
いや、無かったらなんで結合してるの?ってなりますし。
例えば、商品番号とかロットNo.とか、きっとあるはずです。
データベースの世界では「キー項目」と言います。
で、計算・演算された「結果」はキー項目にはなり得ないものです。
そんな不明瞭な「結合された行数」とかを苦労して拾うより、その「根拠」から計算する方が楽なのに。
 
例えばSUMIF(S)関数とかSUMPRODUCT関数とかを使えば楽でしょうし
   ※A列にキー項目があるなら
    N4セルに:=IF(COUNTIF($A$4:A4,A4)=1,M4-SUMIF(A:A,A4,L:L),"")  (以下、フィル)
エクセルの看板機能の一つであるピボットテーブル・集計などを使う手段もありますね。
そうすりゃぁ、VBAなんて使わなくても解決できるのに。と、いつも思います。
 
 
 
以下コッソリ。
なんでもかんでも結合しない方が良いですよ。
結合すればするほど、あとの編集が面倒になりますから。
罫線は条件付き書式で引けば良いです。
印刷してやればセルの仕切り線は見えなくなるのですから。
結合なんて、基本的には要らないんです。
・・・と思う、オールドファッションな私です。

回答
投稿日時: 17/12/22 16:06:10
投稿者: 細雪

あ、そうそう。
絶対参照「$」つきにしたくないなら
  .Address(RowAbsolute:=False, ColumnAbsolute:=False)
で相対参照に出来ますよ。
なお、True にすると絶対参照ですが、この場合は省略可能です。
  Range("A1").Adress(ColumnAbsolute:=False)
と書けば「列は相対参照、行は(省略したので)絶対参照」というわけで「A$1」と文字列で返ります。
 
時々便利に使えます(笑)。

回答
投稿日時: 17/12/22 19:12:04
投稿者: WinArrow
投稿者のウェブサイトに移動

結合セルをOffsetで、移動させるより、L列セルを基準にOFSETで移動する方法を紹介
 
Sub test()
Dim L As Long, M As Long, N As Long, ii As Long
     
    With ActiveSheet
        L = .Columns("L").Column
        M = L + 1
        N = M + 1
        ii = 4
        Do Until .Cells(ii, L).Value = ""
            If .Cells(ii, L).Offset(, 1).MergeCells Then
                If .Cells(ii, L).Offset(, 1).MergeArea.Cells(1).Row = ii Then
                    .Cells(ii, N).Formula = "=SUM(" & .Cells(ii, L).Resize(.Cells(ii, M).MergeArea.Cells.Count).Address(0, 0) & ")-" & .Cells(ii, M).Value
                    .Cells(ii, N).Value = .Cells(ii, N).Value
                    ii = ii + .Cells(ii, N).MergeArea.Cells.Count
                End If
            Else
                .Cells(ii, N).Value = .Cells(ii, L).Value - .Cells(ii, M).Value
                ii = ii + 1
            End If
        Loop
    End With
 
End Sub

回答
投稿日時: 17/12/23 03:28:55
投稿者: baoo

自分だったら多分大して考えずに下記のようにしてしまいますね。
データが多いとか考慮しなければならないことがあるならその時考えます。

Sub Test()
    
    Dim lngRow As Long      '各行
    Dim lngResult As Long   '入力行
    Dim lngSum As Long      'L列の合計

    lngResult = 4
    lngRow = 4
    Do
        lngSum = lngSum + Cells(lngRow, 12).Value
        
        '次の行のM列にデータが有ったら入力してリセット
        If Cells(lngRow + 1, 13).Value <> "" Then
            Cells(lngResult, 14).Value = lngSum - Cells(lngResult, 13).Value
            lngResult = lngRow + 1
            lngSum = 0
        End If
        
        '次の行のL列にデータが無くなったら抜ける
        If Cells(lngRow + 1, 12).Value = "" Then
            Cells(lngRow, 14).Value = Cells(lngRow, 12).Value - Cells(lngRow, 13).Value
            Exit Do
        End If
        lngRow = lngRow + 1
    Loop
    
End Sub

回答
投稿日時: 17/12/23 21:35:59
投稿者: baoo

ふと自分のコードは間違ってることに気づきました。
下記に修正します。

Sub Test()
    
    Dim lngRow As Long
    Dim lngResult As Long
    Dim lngSum As Long
    
    lngResult = 4
    lngRow = 4
    Do
        lngSum = lngSum + Cells(lngRow, 12).Value
        If Cells(lngRow + 1, 13).Value <> "" Or Cells(lngRow + 1, 12).Value = "" Then
            Cells(lngResult, 14).Value = lngSum - Cells(lngResult, 13).Value
            lngResult = lngRow + 1
            lngSum = 0
        End If
        If Cells(lngRow + 1, 12).Value = "" Then
            Exit Do
        End If
        lngRow = lngRow + 1
    Loop
    
End Sub

回答
投稿日時: 17/12/24 11:23:11
投稿者: LMK

ええっと。。。
 
始めから数式を入れておけばいいのでは?
表計算ソフトってそう使うものですよね。
 
あるいは「在庫」と「不足」を別表にするとか。
何らかののキーはあるのでしょうから、「在庫」を指定すれば「不足」は同じ式で計算できますよね。

投稿日時: 17/12/25 00:51:13
投稿者: ダイハード

みなさんこんにちは
アドバイスいろいろありがとうございます。
細雪さんの指摘の通りM列とN列の結合には根拠があります。
その根拠を確かにSUMIF関数を使用すれば簡潔かもしれませんが
現状その根拠は頭の中にありまだコード化されていません。
今は最初にM列とN列を人力で結合し、M列に在庫数を手入力し
N列に不足数の計算式をひとつひとつ入力していました。
ひとつの表の行数が多いもので200〜300行もありその入力時間は間違いの手直しを考慮すると20分〜30分かかり毎週更新しなければいけないので結構きつい作業となります・・・・

引用:
セルの移動は(最初のActiveCellはN4です)

はN4のセルに SUM(L4:L6)-M4 を入力するとN7に移動する事です。
引用:
欲をいえばM列の結合状態をN列にも反映させ計算式を入れたいです。

は今はM列とN列を人力で結合させていますが少しでも楽をしたいので、
N列の結合、N列への計算式はVBAへと言う意味です。
みなさんのサンプルを理解しながら試しているところです。
最終結果は同じですがWinArrowさんbaooさんは計算結果でsimpleさんは計算式が入力
またWinArrowさんbaooさんはN列が結合されている事が条件でsimpleさんはN列が結合されていてもいなくても結果結合される。そこまで確認しました。コードの理解はこれからです。
 
もう一つ欲を言うとひとつのSheetに今のような表が4〜5あればどうなるのでしょう?
行数は表によっていろいろです。表と表の間には3行の空白行がある場合です。
 

回答
投稿日時: 17/12/25 02:24:39
投稿者: baoo

ダイハード さんの引用:
はN4のセルに SUM(L4:L6)-M4 を入力するとN7に移動する事です。
それは分かりますがもこな2さんがおっしゃるように本案件は移動することなく目的の動作が達成出来ます。
 
ダイハード さんの引用:
またWinArrowさんbaooさんはN列が結合されている事が条件で
別に結合されている必要は無い筈です。
結合する処理が入っていないのはL列は結合されていないのに
ダイハード さんの引用:
M列、L列にはセルが結合された部分があります。
とあったのでN列のことだろうと推測し、結合されている前提で組んだだけです。
個人的に、そしてここで回答されている方々なら殆どの方がセル結合の運用なら、
そして自分の仕事としてプログラムを組んでいるなら(自分の仕様なら)、
M、N列のセル結合処理もプログラムに追加するでしょう。

回答
投稿日時: 17/12/25 02:58:05
投稿者: baoo

ダイハード さんの引用:
もう一つ欲を言うとひとつのSheetに今のような表が4〜5あればどうなるのでしょう?
行数は表によっていろいろです。表と表の間には3行の空白行がある場合です。

表と表の区別ができればアドレスを変えて殆ど同じコードを実行するだけでしょう。
例えば私のプログラムならlngRowを引数にして
lngResult=4 -> lngResult=lngRow
lngRow=4 -> 削る
そして表の最終行を戻り値として返すようにすれば
Cells(戻り値, 12).End(xlDown).Rowを引数に次をよび出せば良い筈です。

回答
投稿日時: 17/12/25 07:29:40
投稿者: simple

引用:
細雪さんの指摘の通りM列とN列の結合には根拠があります。
その根拠を確かにSUMIF関数を使用すれば簡潔かもしれませんが
現状その根拠は頭の中にありまだコード化されていません。
 今は最初にM列とN列を人力で結合し、M列に在庫数を手入力し
N列に不足数の計算式をひとつひとつ入力していました。
ひとつの表の行数が多いもので200〜300行もありその入力時間は間違いの手直しを考慮すると20分〜30分かかり毎週更新しなければいけないので結構きつい作業となります・・・・
ここを是非効率化して欲しいものですね。結合が不可避とも思えません。
M列を結合する際の判断基準があるはずなんで。

回答
投稿日時: 17/12/26 00:18:12
投稿者: もこな2

ダイハード さんの引用:
細雪さんの指摘の通りM列とN列の結合には根拠があります。
ダイハード さんの引用:
今はM列とN列を人力で結合させていますが少しでも楽をしたいので、N列の結合、N列への計算式はVBAへと言う意味です。
みなさん指摘されてますけど、どうしても結合が必要っていう場合であっても、どうせなら、結合と数式の設定の両方をマクロでやったほうがいいでしょう。
もちろん現物を見てないのでわかりませんけど、なんとなく一連の流れで、結合も数式設定もできてしまうように思えます。
 
とりあえず、
http://vbae.odyssey-com.co.jp/column/no7_2.html
こちらのように、やりたいことの一連の流れ(結合と数式入力)を言葉で書いて整理してみてはみてはどうでしょうか(疑似コードと言ったりするようです)
 
実コードじゃなくても、疑似コード付きの質問であれば、丸投げだって怒る回答者いないと思うんですが・・・・(私見ですけど)

投稿日時: 17/12/28 23:09:07
投稿者: ダイハード

みなさんありがとうございました。
 

引用:
結合セルを対象としたoffsetは、
   カーソル移動を前提にしてしまい適切な領域を返さないので、
   いったん結合を解いて処理を行い、最後に復元するのがよいかと思います。

今後、セル結合時は一旦解除し計算することにします。
頭がすっきりしました。