エクセルを四則計算と合計だけで済ましているのはとても
勿 体ないことです。
どかたのエクセル ということで、「これは 使える！」
という便利関数を紹介します。
前回「VLOOKUP関数」の紹介で触れましたが、IF関数は
VLOOKUP関数と組み合わせて使えば、スマートな計算表
を作ることができます。
前回の紹介でVLOOKUP関数が出来上がっているので、そ
れを今回はIF関数の中に組み込ませていきます。
前回、例として上の表を作りました。
左側の表が計算書です。
右側の表はデータです。
この土工データ表を利用して土工数量計算表を作成するというもの
です。
いやらしいのは、
IF関数と組み合わせると、関数式が長くなります。
長い式は、初心者から見ると何が何だか解りません。
私も洩れなく、たちまちの拒絶反応でした。
例えば
=IF(A5=””,””,VLOOKUP($A5,$M$3:$R$79,6,FALSE))
何てのは一見して訳わからんでしょ。
これでもそんなに長くない方ですよ。
最初のうちはしょうがないです。
慣れるまで見るだけでいいのです。
何度も見ているうち、こいつなんだろなと言う気持ちが起きてきます。
それでも深入りせず、見ては離れて睡眠を挟んでまた見たりすると
徐々に慣れてきます。
人間でも同じで、初対面で気に入らない人とは口も利かないし、目
も合わせません。でも何度か会ってるうちに打ち解けてくるんです。
時間をかけて何度も会うことが大事なのです。
これは脳のしくみだからしょうがないです。
さて、VLOOKUP関数を入れただけでは、生データを全部入れるま
でこんな風になってしまってカッコ悪いということでした。
(画像クリックで拡大)
この表で、生データを記入するのは「NO」と「延長」だけです。
その生データが入ってない状態のときは関係するセルが空欄になる
ようにした方がカッコいいです。
上の（表-1）では「NO」欄に“1”だけ入ってる状態で、関連する
「単掘」と「単戻」の欄がVLOOKUP関数でデータ表から引張
って来て数値に置き換わってる状態です。
“0.00”となっている部分は、お判りでしょうが、ただの掛け算式で
「延長」が空欄のため“0”と認識されてるためです。
「掘削土量」は、「延長」×「単掘」ですから、0×2.39＝0ですから“0.00”と表記されるわけです。
こんなふうに式で埋まってます。
(画像クリックで拡大）
（表-2）を一見すれば、作成するに大変な作業が要るように見えま
すがこれは一行一行に手で入力してるわけではありません。
エクセルのオートフィル機能で自動変換入力出来ますから、最初の
行さえ作ってしまえばあとは楽なもんです。
（表-1）のようにならないように、関数式が入っても生データが入
力されるまで空欄でいるように。
「IF」関数を使って次の（表-3）のようにします。
「NO」の’1’を記入して関連する数値が表示されている状態。
これに「延長」の数値を入れると残りの数値が計算される。
では、「IF」関数を見ていきましょう。
Ｂ列の一行目、B4セルには
=IF(A4=””,””,VLOOKUP(A4,$L$4:$P$10,2,FALSE))
の関数式が入ってます。
IF関数を入れる前は、
=VLOOKUP(A4,L4:P10,2,FALSE)と言う式でした。
IF関数を入れる前にこの部分を＄マークで調整します。
＄マークはオートフィルを使うときこの値が変わらないようにセル
や範囲を固定するためのマークです。
検索する範囲は変わってはいけませんから、これを使ってオートフ
ィルかけても検索範囲が変わらないようにするための作業になりま
す。
＄マークを付けるときは数式バーでマークしたいセル番号をドラッ
グで選択してファンクションキーのF4を押すことで出来ます。
もちろん、手で入力も出来ますが、F4キーの方がはるかに楽です。
オートフィルは、セルを選択したとき、右下に小さな 「+」のマー
クが付くので、それをドラッグで上下左右に移動すると、関数式に
対応しているセルの位置関係を変えずに行列番号だけを変化させコ
ピーしてくれます。
連番を付けたい時や同じ文字をコピーしたい時などにもよく使うあ
れです。
この部分の説明が判りにくかったら、一度簡単な数式を入れてオート
フィルを試してみればよく分かるはずです。
関数式のオートフィルでは、あるセルや範囲の位置を固定させて使
いたい時がよく出てきます。その時使うのが＄マークなのです。
例えばこの土工計算表表の場合 L4からP10 （L4:P10 ）の範囲を固
定してオーフィルしていますが、数式バーの関数式に表示された
L4:P10 をドラッグしてファンクションのＦ4を押して、 $L$4:$P$10
としています。
この意味は行も列も固定するという意味で、つまりは L4からP10 の
範囲（ L4:P10 ）固定されています。
例えばA列だけ固定させたい場合は $A となり、4行目だけ固定させ
たい場合は$4 となります。 $A$4になればA4セルが固定されます。
つまり、$A4，A$4，$A$4 の組み合わせが出来るわけです。
=VLOOKUP(A4,L4:P10,2,FALSE)
この式の意味は、A4と全く同じ（FALSE ）値を L4からP10の範囲 から見つけて、そこから数えて2つ目の列を探してくれ。
探したらその値をこのセルに入れてくれと言う意味です。
これをそのまま下にオートフィルすると、A4はA5, A6, A7…と変わり、
同様にL4 は L5，L6， L7，…と変わり、P10も P11，P12，P13，…と変わってコピーされていきます。
A列の行数値は変わらなければいけませんが、検索範囲の場所は変
わっては困るわけですからその関数式は次のように、
=VLOOKUP(A4,$L$4:$P$10,2,FALSE)
にしてからでなければ、オートフィルとしては使えません。
そこを捉えたうえで、ここから「IF」関数を入れていきます。
まず、「名称」からです。
B4にカーソルをあてると、数式バーは次のようになります。
=VLOOKUP(A4,$L$4:$P$10,2,FALSE)
この数式で = の後にカーソルをあてて、次のように入力します。
IF(A4=””,””,
式全体ではこうなります。
=IF(A4=””,””, VLOOKUP(A4,$L$4:$P$10,2,FALSE))
ここでは、関数の入力ウィンドウを使いません。
既存の関数式が入ってる状態での関数入力ウィンドウは使いにくい
からです。
この程度の入力ならば手で入力した方が早いし楽です。
経験のために関数入力ウィンドウを開いてやってみて下さい。
関数式ではなんでもそうですが、右向きの（ と左向きの ）この
カッコの数は同じでなくてはいけません。間違えるとお節介さんが
出てきて注意されます。これ、しつこいよ～！
ここに出てきた「””」の意味は、ブランクつまり空白という意味で
す。
=IF(A4=””,””,…)は もしA4が空欄だったら空欄で、そうでなかっ
たら … の部分を解いて答を入れてくれと言うことになります。
ここまで出来たら、A9までオートフィルしてみてください。
同様にして D4、E4 も見ていきます。
D4=IF(A4=””,””,VLOOKUP(A4,$L$4:$P$15,4,FALSE))
E4=IF(A4=””,””,VLOOKUP(A4,$L$4:$P$15,5,FALSE))
「掘削土量」、「埋戻土量」、「残土量」の数式は普通の四則計算
ですが「IF」を使ってつぎのようになります。
F4=IF(OR(A4=””,C4=””),””,C4*D4)
G4=IF(OR(A4=””,C4=””),””,C4*E4)
H4=IF(OR(A4=””,C4=””),””,F4-G4)
上式に出ているOR関数 は、 英語のORそのまんまで、 または と
言う意味の論理関数で、 OR(A4=””,C4=””) の部分が OR関数です。
意味はA4またはC4が空欄だったら空欄にして、そうでなかったらC4*D4を計算して という意味です。
これを、必要なところまでオートフィルします。
（このとき「OR関数」がないときはどうなるかやってみてください。）
これで完成です。
この土工数量計算書に「NO」と「延長」に必要な数値を入れたり
消したりして試してみてください。
うまくいったら、独自の土工数量計算書を作って使ってみてくだ
さい。
その時はデータ表と計算書は別々のシートに作るべきです。
式にシート名が付く分 長くなりますが やり方は全く同じです。
注意が必要なのは、せっかく作った計算表は、ちょっとした手違いで関数式を消してしまったり、キーボードにものが当たったりして関数式が崩れて駄目になる可能性があるということです。それを防ぐために入力セル以外はロックをかけて使うことをお勧めします。
私の場合はVBAを使っているので心配ないのですが、関数式だけでやってた頃はそんな事故がよくありました。
今は初心者であっても、いずれはマクロ、VBAと進化していくと思いますが、関数式で暫くやっていくのであればロックのかけ方はぜひ覚えて下さい。
（エクセル先生（ヘルプ）が説明してくれます。）
了
