合計金額が記載されるセルの場所が毎回変わるExcelデータで合計金額を取得するにはFindとOffsetを使うとだいたい上手くいく。

2021年8月8日2021年8月10日

Excelのデータから会計ソフトに仕訳データを入力する際にExcelデータに記載された合計金額だけが必要なケースがあります。

合計金額をマクロを使って取得しにいくときに毎回Excelの決められたセルに合計金額が記載されていればいいのですが、そうではないときもあります。

そんな時にはマクロのFindとOffsetを使うとだいたい上手くいきます。

合計金額が記載されるセルが毎回変わるExcelデータの具体的イメージ
合計金額が記載されるセルが毎回変わるExcelデータで合計金額を取得する手順①Findで目印となるセルを見つける。
合計金額が記載されるセルが毎回変わるExcelデータで合計金額を取得する手順②Offsetを使って目印となるセルから移動する。
1. 具体例：合計金額が記載されたセルの内容をメッセージボックスに表示させる
2. 具体例：別シートに記載された合計金額のセルの内容をメッセージボックスに表示させる
まとめ

合計金額が記載されるセルが毎回変わるExcelデータの具体的イメージ

今回の記事でイメージしているExcelデータを準備しました。

こんな感じのやつです↓

１日の売上データがExcelにずらーっと記載されていて、最後に1日の合計金額が記載されているデータです。この場合はC１３セルに合計金額が記載されています。

日によって売上の件数が変化するので、合計金額が記載されているセルが同じ場所になることがありません。

次の日はこんな感じになったりします。

このケースの場合にはC17セルに合計金額が記載されています。

このように日によって合計金額が記載されたセルの場所が異なる場合にマクロを使って合計金額だけを取得する方法を考えます。

合計金額が記載されるセルが毎回変わるExcelデータで合計金額を取得する手順①Findで目印となるセルを見つける。

今回のExcelデータの合計金額を取得するためには2段階のステップを踏むことにします。

一つ目のステップとして

合計金額が記載されたセルの周辺に目印となるセル（規則的なセル）がないかを探します。

今回の場合には合計金額が記載されたセルの左側には必ず【合計】と記載されたセルがあるという規則性があるのでこれを目印にします。

なのでこの【合計】と記載されたセルをマクロで取得します。

セルを検索するにはFindを使うと検索することができます。
コードとしてはこんな感じです↓

Sub total_find()
 
'■B列から【合計】と記載されたセルを選択するマクロ
 
Dim target As Range
 
Set target = Range("b:b").Find(what:="合計")
 
target.Select
 
End Sub

変数「target」を宣言をしておき、その「target」に対してFindを使ってB列から【合計】と記載されたセルを検索させます。

これで【合計】と記載されたセルを選択できました。

ただし、取得したいのは【合計】と記載されたセルではなくてその一つ右側のセルに記載された合計金額です。

この合計金額は次のステップでOffsetを使って取得していきます。

合計金額が記載されるセルが毎回変わるExcelデータで合計金額を取得する手順②Offsetを使って目印となるセルから移動する。

目印となるセルが取得できたら後はOffsetを使って選択するセルをずらし（移動し）ていきます。

今は【合計】と記載されたセルを選択している状態なのでこれを

Offset(0,1)

とすることで右側にセル１つ分移動させます。

この部分を上で紹介したマクロに組み込むとこんな感じになります。11行目の記載が変更になっている点を確認していただきたいです。

Sub total_find()
 
'■B列から【合計】と記載されたセルを探し、その右にあるセルを選択するマクロ
 
Dim target As Range
 
Set target = Range("b:b").Find(what:="合計")
 
target.Offset(0, 1).Select
 
End Sub

これで合計金額が記載されたセルが取得できました。

ここまでくればあとは自分のやりたい処理をどんどん追加していけばよいです。

具体例：合計金額が記載されたセルの内容をメッセージボックスに表示させる

例えばメッセージボックスに合計金額を表示させたい場合にはこんな感じでマクロを書き替えるとよいです。

Sub total_find()
 
'■B列から【合計】と記載されたセルを探し、その右にあるセルの記載内容をメッセージボックスに表示する
 
Dim target As Range
 
Set target = Range("b:b").Find(what:="合計")
 
MsgBox target.Offset(0, 1).Value
 
End Sub

実行するとこうなります↓

具体例：別シートに記載された合計金額のセルの内容をメッセージボックスに表示させる

シート１に16日の売上が記載されていて、シート２に17日の売上が記載されている場合に各シートの合計金額をメッセージボックスに表示させる場合にはこんな感じでマクロを書き換えるとよいです。

同時に【合計】と記載されたセルが見つからなかった場合のエラー処理も追加しています。

Sub total_find()
 
'■各シートのB列から【合計】と記載されたセルを探しその右セルに
'■記載された合計金額をメッセージボックスに表示するマクロ
 
Dim target As Range
Dim i As Long
 
For i = 1 To 2
 
    Set target = Sheets(i).Range("b:b").Find(what:="合計")
     
    If target Is Nothing Then
     
        Exit Sub
         
    Else
     
        Sheets(i).Activate
        MsgBox target.Offset(0, 1).Value
     
    End If
 
Next i
 
End Sub

変数宣言の箇所で変数「i」を追加し、それからFor～Nextステートメントを使っています。

今回はシートが２個しかない前提なので2回だけ繰り返しの処理をしていますが、

シートの数が何個になるかわからない場合にはsheets.countを使って、シートの個数を取得して、そのシートの回数だけ繰り返しの処理をする

とよいと思います。

実行するとこんな感じになると思います↓

まとめ

Excelデータの都合上、合計額が記載されるセルが変動する場合に、合計金額を取得する方法をExcelマクロでやる場合に自分がよくやるやり方を記事にしました。

あくまで一つの案であって、目的は合計金額を取得することなので記事のやり方以外のどんなやりかたでも問題ありません。

考え方で参考になることがあれば使っていただければと思います。

今回の場合にはマクロをつかわずに済むようにそもそものExcelデータの作り方を見直す方法も有効です（むしろこれを最優先すべきだと考えます。）し、Excel関数等でデータを加工して、ピボットテーブルで合計金額を取得したりする方法もあると思います。

【本日の近況報告】

ここ1週間くらい目が充血したりする症状が発生しています。朝起きると目ヤニがでるなんてこともありまして本日眼科を受診したところ、花粉症による症状の可能性があると診断されました。

最近はブタクサなどの花粉がでているとのこと。

花粉症とは長い付き合いですがスギ・ヒノキだけかと思ってました。

今回ブタクサにも反応するようになってきたようで残念極まりないです。

映画「海街diary」を鑑賞。うまく表現できないですがいい映画だと思います。気に入りました。

【本日の１曲】

fast cars/everyday i make the another mistake