5本目 「セルの計算と表示形式の指定」を学ぶ
お題
作成したファイルはこちらから
基礎知識
繰り返しの処理 For Next
→指定した回数処理をくりかえしたい場合に使用します。
場合分けの処理 if 文
→任意の条件式の真偽(true or false)に応じて異なる処理を行う際に使用します。
NumberFormatLocal
ここが今回の課題で割と難しいところかと思います。
これは表示形式を変えるメソッドです。エクセルではセルに入っている値自体は同じでも
それをいろんな見せ方(形式)で示すことができます。今回のお題では円マークとカンマ区切りをつけました。ついでにいくつか例をあげておきます。
- Range.NumberFormutLocal="@" '文字列で表示する
- Range.NumberFormutLocal="#,###" 'カンマ区切り
- Range.NumberFormutLocal="0.00" ' 小数点2以下まで表示
- Range.NumberFormutLocal="yyyy/mm/dd" '日付表示
- Range.NumberFormutLocal="aaa" '曜日表示
- Range.NumberFormutLocal="ggge" '明治大正昭和平成令和 ●● (例:昭和60)
とりあえずこれくらい覚えてあとはその都度覚えましょ。
回答
まずは答えを示します。D列にきちんと計算結果、円マーク、カンマ区切りがついています。では、頭から一つずつ追ってみていきましょう。
最初に使いたい変数の宣言です。Option Explicitに怒られないようにね! データ型 Long の変数としてiを定義しています。
Longは前回勉強しました1とか2とかの「整数 」です。整数なので、例えば小数点のついた数値や「あいうえお」のような文字列はここに入りられません。
これは3行目、4行目、5行目、・・と処理を行なう際の、●行目の●に入る変数です。なので整数で定義しました。i 行目、ということにしておいて、このiをまず最初に3にしてみて、次は4にしてみて、次は5にして・・と処理していきます。
するとおそらく次に、そのiをどこまで増やしていいの?という問題にぶつかります。12行目までで良い処理を、1000行目までされてはたまりません、日が暮れまちゃいます。
答えはもちろん表の最後の行まで、です。
ではその「表の最終行」をどうやって取得するか、となり、ここが今回の一番の山場、学びPOINT!です。
頭の中だけで考えるとマクロはなにかと小難しいので、色分けして少し分解しちゃいましょう!
要は最終行は下図のとおり
「見出しより上の空白行(行数特定済みとする) + 見出し以下の表の行数(行数は未特定とする)」
です。
つまり1+11=12行です。ただしここでよっしゃと安直に For i=3 to 12 ~~
と書いてしまうと、12行までの表にしか対応できません。これではだれも使ってくれません。
そこで増減する表に対応するため、表の行数をマクロでどう表現するか(取得するか)、という考えが必要になります。でも、理屈は簡単で、あくまで先ほどと同じ、
「見出しより上の空白行 + 見出し以下の表の行数」
です。
Dim i As Long
'三行目から(表自体の行数+表より上の空白行数)行目まで。For とNext で囲まれた処理を繰返す つまり回数は最終行数次第で可変的!嬉しい^^
For i = 3 To Range("B2").CurrentRegion.Rows.Count + 1
’単価=空白 または 数量=空白 なら
If Cells(i, 2) = "" Or Cells(i, 3) = "" Then
'金額には何も入れない
Cells(i, 4) = ""
’それ以外、つまり単価か数量のどちらかでも、なにか数値が入っている なら
Else
’積算したものをD列に代入する
Cells(i, 4) = Cells(i, 2) * Cells(i, 3)
End If
Next ’」ここまでを繰り返します
’-------------------------------------
’↓D列を計算式で埋めたら以下の処理に移行します
’D列の表示形式に円マークを付け&カンマで区切る
’(※セルの数値は不変、見え方変えてみるだけです)
Range("D:D").NumberFormatLocal = "¥#,##0"
学んだこと総括
今回はだいぶボリューム感じましたよ(うっぷ)。相変わらず表がでてきたらCurrentRegion様様って感じがします、今のところ。あと私自身、表示形式の理解が浅かったです。マクロで覚える前に、先にエクセルのユーザー設定で触って感覚を掴んでおかねばと痛感し次第です。
ざっっくりまとめると・・