(1)特定列は最下行まで空白の行がない。
(2)データ集計列には空白の行があるかもしれない。
上記の場合にデータが入力された列の最下行の1行下に集計式を入れるマクロを作りたいということはよくあると思います。
シンプルなVBA記述でマクロを作る方法です。
Contents
最下行に移動して集計(sum)をすること自体は簡単。
集計結果を表示したいセルを選択して、「=sum(」と入力してから、
集計したい範囲の列の最上行を選択して
shiftキーを押しながら
集計したい範囲の列の最下行を選択して、「)」と入力すれば
集計結果が表示されます。
同じ行の範囲で集計したい列がある場合は、
集計結果が表示されている(計算式が入力されている)セルを選択し、
ctrlキーとcキーを同時に押してコピーし
集計結果を表示したい列の同じ行のセルを選択して、
ctrlキーとvキーを同時に押してペースト(貼りつけます)。
複数の列に集計結果を表示したい場合は、
shiftキーを押しながら矢印キー(←か→)で複数のセルを選択してから
ctrlキーとvキーを同時に押してペースト(貼りつけます)。
集計結果を表示した行の合計を同じ行の右隣の列に表示したい場合は、
集計結果を表示したいセルを選択して、「=sum(」と入力してから、
集計したい範囲の行の最左行を選択して
shiftキーを押しながら
集計したい範囲の行の最右行を選択して「)」と入力すれば
集計結果が表示されます。
ここまでの作業、sum関数を使用した集計作業は日常的に何度も行なっているのではないでしょうか。
この作業の中で範囲指定などをマウスで行なっている方はキーボードだけで操作してみましょう。
何も難しいことはない、簡単な作業ではありますが、単純な作業をカチャカチャ・ガチャガチャとキーを叩きすぎるのはせわしないです。
この集計作業をマクロで自動実行できれば、ほんの少しですがせわしない作業から解放されます。
最下行まで空白行がない列(見出し列など)の最下行の値を取得する
今回の例ではA列(商品コード)の列は空白のセルはないということにしています。
- A列は空白セルがありませんが、行数(商品アイテム数)はその都度変わります。
- B列〜F列の5列は売上のない商品のセルはセルには何も入力されていません。
1行目は見出しですので、売上数のデータが入力されているのは2行目からです。
データが入力されている最終行は不明です。
あるセルを選択して、ctrlキーと矢印(↓)キーを同時に押せば、
- データが入力されているセルであれば、データが入力されている最下行セルへと移動します。
- データが入力されていないセルであれば、下の行の中でデータが入力されている最上行セルへと移動します。(データが入力されているセルが下の行の中になければsheetの最下行セルへと移動します。
ctrlキーと矢印(↓)キーを同時に押してセルの移動をすることをVBAでは「End(xlDown)」で表現します。
データが入力されているA列1行目から連続してデータが入力されているA列の最下行のセルは以下の表現です。
「Range(“a1”).End(xlDown).Row」
B列〜F列の5列は売上のない商品のセルはセルには何も入力されていませんので、
商品の売上がなかった空白のセルより下にデータが入力されていた場合は、
2行目〜「End(xlDown)では正しい集計ができません。
そこで、データが入力された最下行まで空白セルがないA列の最下行の行数を取得してB列〜F列の5列の売上を集計すれば正しく集計することにします。
Excel データが入力された最下行を取得して集計するVBA マクロ
(1)A列は最下行まで空白の行がない。
(2)データ集計列(B列〜F列の5列)には空白の行があるかもしれない。
上記の場合にデータが入力された列(B列〜F列の5列)の最下行の1行下に集計式を入れるマクロを作ります。
そして集計行が入った右隣のセルに集計結果の集計(総合計)式を入れてみました。
———————————————————————————-
Sub EndLine()
Dim EndLine As Long
EndLine = Range(“a1”).End(xlDown).Row + 1 ‘EndLine = A列1行目から最下行+1行目の行数
With Range(“b” & EndLine)
.Formula = “=SUM(b2:b” & (EndLine – 1) & ” )” ’SUM計算式を
.AutoFill Destination:=.Resize(1, 5) ’AutoFillの到着地1を1から5へ変更
End With
Range(“g” & EndLine) = “=SUM(b” & EndLine & “:f” & EndLine & “)”
ActiveWorkbook.Save
End Sub
———————————————————————————-
(1) altキーとF11キーを同時に押して、VBEを起動します。
いつも使うという場合でしたら、VBA Project (PERSONAL.XLSB)の「標準モジュール」を右クリックして表示されるメニューから「挿入(N)」→「標準モジュール(M)」を選びます。
(2) Sub〜End Sub までをコピーしてペースト(貼りつけ)します。
(3) ctrl キーと s キーを同時に押して保存します。
(4) Excelに戻り、alt キーと F8 キーを同時に押して、マクロを呼び出します。
(5) 実行するマクロを選択して、alt キーと R キーを同時に押してマクロを実行します。
SUM関数による計算式が正しく入力されて、計算結果が表示されています。
【編集後記】
同じ結果を得るための方法は1つではありません。
Excel VBA マクロ実行の方法も同じく1つではありません。
データの最下行を取得するための考え方もさまざまです。
たとえば、sheetの最下行のセルを選択し、そこからctrlキーと矢印(↑)キーを押せば、データの最下行を取得できます。
しかし、集計したいデータの最下行のさらに下の方に集計するべきではない別のデータがあるかもしれません。
VBAでマクロを記述していくのに、どの考え方・表現が簡単でシンプルなのか、ご自身の好みも含めて、いろいろと試してみてはいかがでしょうか。
忙しいからせわしなくカチャカチャ・バチャバチャとキーを打ちまくるしかない、とならないように、
少しでも立ち止まって意味を考えて、それはどういう意味だろう(今回の例では、このデータの特長での最下行とはなんだろう)?と考えてみると、無駄な作業を見直すヒントが見つかるかもしれません。
今日の1日1新:タニタ食堂監修のデザート カスタードプリン
小倉健二(労働者のための社労士・労働者側の社労士)Office新宿(東京都)
最新記事 by 小倉健二(労働者のための社労士・労働者側の社労士)Office新宿(東京都) (全て見る)
- 【産休・育休】厚生年金・健康保険の保険料免除について知ろう - 2023年3月29日
- 2023年4月1日【出産育児一時金】8万円増額1児50万円支給 - 2023年3月16日
- 【60歳で老齢年金繰上げ受給を検討中】繰上受給したあとで障害年金請求できるか? - 2023年3月14日