プログラミングをやるなら知っておきたいExcelの数式のしくみ

2018年10月13日

URLをコピーしました！

プログラミングをいつかやりたい、今年やりたいという方もいらっしゃるでしょう。
使い慣れたExcelでも、意識しておくと、プログラミングをやるときに役立ちます。

1 プログラミングの敷居の高さ
2 Excelの数式のしくみ
3 プログラミングで自責・正確さ・しくみを学ぶメリット

プログラミングの敷居の高さ

プログラミングとは、こういったものをいいます。

Excelマクロ

Rangeは、セルの意味ですので、「セルA3」に「セルA1とセルA2を足したもの」を入れるというプログラムです。

これを実行すると、セルA3にセルA1（100）＋セルA2（200）の300が入ります。

普通にExcelでやるなら、

=A1+A2

ですみます。

Excelでやったほうが楽ですし、何もプログラミングする必要はありません。

さきほどのプログラムだと、

Sub test()

Range(“a3”).Value = Range(“a1”).Value + Range(“a2”).Value

End Sub

を

Sub test()

renge(“a3”).Value = Range(“a1”).Value + Range(“a2”).Value

End Sub

※rengeと間違えている

や

Sub test()

Range(“a5”).Value = Range(“a1”).Value + Range(“a2”).Value

End Sub

※a3とすべきところをa5にしている

や

Sub test()

Range(“a3”).Value = Range(“a1).Value + Range(“a2”).Value

End Sub
※ダブルコーテーションが抜けている

とやると、正しく動きません。

こういった敷居と比べると、Excelだけのほうが楽です。

ただ、プログラミング＝マクロでしかできないこともあります。
・複数のファイルから売上を集計する
・複数のシートから売上を集計する
・売上データから請求書をつくってPDFで保存する（＋メールで送る）

などといったことです。

プログラミングができたほうができることはあるのですが、やはり基本はExcel。

そのExcelの数式をちょっと意識しておくとプログラミングをやるときに役立ちます。

Excelの数式のしくみ

EXCELの数式には次のようなしくみがあります。

=ではじまる

数式は、＝ではじまります。
=A1+A2
=SUM(A1,A2)
=Vlookup(A1,C:D,2,false)

たとえば、A1＋A2と入れても、計算はしてくれず、A1＋A2という文字が入ります。

たかだか＝だけですが、Excelにとっては重要なのです。
これを意識しないでExcelを使っていると、

・合っているはずなのに
・何もしてないのに壊れた
・Excelが間違えた
となります。

何かしら人間が間違っていることが99%です。

プログラミングだと、
Range(“a3”).Value = Range(“a1”).Value + Range(“a2”).Value

と書き、
Excelだと、
=A1+A2

どちらも、〇に△を入れるという意味ですが、プログラミングは、〇=△で両方指示します。
これは、Excelの外で動かすからです。
Excelだと、そのセルに入力するので、〇はわかりきっており、省略できます。
そのため、＝△だけを書くわけです。

大文字、小文字は関係ない

Excelの数式では、大文字・小文字は関係ありません。
通常、小文字になっているでしょうから、

=a1+a2

と入力してかまわないのです。

正しく計算してくれて、＝A1+A2に変えてくれます。

マクロの場合は、
range(“a3”).value = range(“a1”).value + range(“a2”).value

と打てば、
Range(“a3”).Value = Range(“a1”).Value + Range(“a2”).Value

と、RangeやValueは変わりますが、a1、a2はそのままです。

このように、何を自動修正してくれて何をしてくれないかの見極めは、プログラミングに限らず、Excel、IT全般に欠かせません。

全角半角の違いはNG

大文字、小文字はいいのですが、全角半角は別のものと認識します。

むりやりこうやって入れても、＝A1+A2にしてくれるのですが、このケースはあまりないでしょう。

IFで判定するとき（スタバだったら、1、そうじゃなかった2と表示）は、関係あります。

ｽﾀﾊﾞ（半角）は、スタバとみなしていません。

PS4とＰＳ４も別物です。

こういったことを踏まえてデータ入力をしないと、「あれ？おかしいな、Excel」となってしまいます。
おかしいのは、入力した側です。
その他、
・（株）と㈱
・打ち合わせと打合せ
・ケーキとケ-キ

も違うものです。

ましてや、「打ち合わせ」と「ミーティング」なんて全く違います。

そりゃそうだろと思われるかもしれませんが、Excel上のデータはそうなっていることが多いのです。
AIなら、同じものとして判定してくれるでしょうが、それがヒトのためになるのかどうかというところでしょうね。

スペースも文字のうち

次はこちら。
「スタバとスタバ、あっとるやないかーー」と思われるかもしれませんが、Excelからすると違うものです。

下のほうは、スタバのあとにスペース（空白）が入っています。

これがあれば、違うものなのです。

もしこれをあいまいに判定していたらそのほうが困ることが多いでしょう。
全角スペースはもちろん、半角スペースも注意しなければいけません。

スペースを無視して判定することもできますが、それがヒトのためかどうか。

＆でつなげられる

セルとセルの値をつなげるときには、＆が使えます。
（複数だとCONCATENATE関数が便利です）

こういったつなげることはプログラミングでよく使うので意識しておきましょう。
スペースを入れたいときは、このように” “を挟みます。

文字はダブルコーテーションで囲む

さきほどの” “は、スペースが文字なので、ダブルコーテーションにしています。
この「文字をダブルコーテーションで囲む」は、プログラム上、非常に大事です。

ダブルコーテーションがないと、このような警告が出ます。

姓に様をつけるときも、そのままだと、エラーになりますので、

ダブルコーテーションを付けるようにしましょう。

文字はダブルコーテーションで囲むというと、あれ？と思われるかもしれません。
普通に入力するときはダブルコーテーションで囲んでないからです。
Excelの場合、入力したものが文字か数値かをある程度判断してくれます。

プログラミングだと、
Range(“d1″).Value = “尾崎”

と書かなければいけません。

なお、セルを示すd1も文字ですので、”d1″とします。

かっこは大事

関数で、最初のかっこがないとエラーになります。

かっこというと、補助的な役割のように思えますが、そうではなく重要なのです。

（法律でもそうです）

ただ、Excelの場合、最後のかっこは省略できます。

関数名は正確に

関数名を間違えると、正しく計算されません。
Excelでは、数式バーやリボンから選んで関数を使うことができますが、直接打つことをおすすめしています。

そのほうが、速いですし、プログラミングにもやくだつからです。

ある程度打てば、予測で出てくるので、Tabを押せば入力できます。

離れた場所を指定

離れた場所を指定するときは、マウスでクリックすれば楽です。

これも、直接入力してみましょう。

そうすると、離れた場所を指定するには、「,」（カンマ）を使うことが身につきます。

連続した場所を指定

連続した場所を指定するには、「：」（コロン）です。

これがわかっていると、最重要関数であるVLOOKUP関数の理解もすすみます。

この場合は、「長浜」を「D5：E7」の範囲のD列から探して、一致すれば、「D5:E7」の2列目にある「熊」を表示するという意味です。

違う種類のものを同じ列に入れない

数値と文字はExcelでは違うものを考えます。
数値を足していて、途中に文字、例えば「中止」といったものが入ると、混乱するわけです。
このようなエラーが出ます。
（見出し、1行目はOKです）

行の指定

行を指定するには、「：」を使います。

SUMではあまり使わないのですが検索する系の関数で使うことが多いです。

列の指定

列の指定も「：」です。

シートの指定

別のシートの値を数式に使う場合があります。

そのときは、シート名に！をつけてシートを表現するルールです。

このとき、
・シート名が正しいこと
・スペースが入っていないこと
・！があること
・全角半角の違いがないこと
に気を付けましょう。

これは数式にスペースが入っているケース。

これは、シート名が「Ｓｈｅｅｔ２」と全角のケース。

一応、シート名に合わせて全角にしてくれます。
これは、Excel、直さんでいいのに・・・。

プログラミングではエラーが出ます。
というよりも、全角でプログラミングするほうが大変です。
日頃から、英数字は半角（文章も）としておきましょう。

大文字小文字の違いは問題ありません。

が、間違いを防ぐためにも、きちんと指定するクセはつけておいたほうがいいです。
または、シート名は半角でそろえるかでしょうね。

このシート指定の数式を手入力してみると、「シート名が全角（日本語）ってめんどくさい」という体験ができます。
半角モードと全角モードを数式の途中で切り替えなければいけないので、手間がかかるからで、これは、プログラミングするときに大事なことです。

ブック（ファイル）の指定

別のファイルを指定することもあります。

test1.xlsxというファイルを指定するのは、[test1.xlsx]です。
Excelはファイル、シート、セルという構成ですので、この場合だと、そのすべてを指定します。

本来は、すべて指定するのですが、省略して =A1 とすると、今選択しているファイル、今選択しているシートとみなしてくれるのです。

プログラミングだとこう書きます。

Sub test2()

Range(“a1”).Value = Workbooks(“test1.xlsx”).Worksheets(“Sheet1”).Range(“a1”).Value

End Sub

なんとなくイメージできるかと。
もちろん、ファイル名を正しくというルールは変わりません。

プログラミングで自責・正確さ・しくみを学ぶメリット

プログラミングをやるメリットは、効率化できることだけではないと思っています。

自責

Excelを使うよりも、プログラミングのほうが自責、つまり自分の責任ということが身につきます。
予期せぬこともありますが、なにかしらヒトがやらかしていることがほとんどです。
何かあったら、「自分が」何をしたかと考えるクセは大事でしょう。
（人がガンガン辞めるブラック企業の社長にはすべからくプログラミングやらせたいのですが、やらないでしょうね）

正確さ

プログラミングをやると正確さが要求されます。
Excelだとマウスでクリックすればいいところを正確に指定しなければいけません。
効率化と逆行するようですが、これがいいのです。
PCやITはそもそも正確に動くもの。
イレギュラーなのはヒトの操作やヒトがつくったルールです。
効率化のネックになるのは、しょうもないルールだったりします。