エクセルとVBAを上手に使う②ワークシート関数を使いこなす

参照：
[エクセルVBAを上手に使うための心構え](http://qiita.com/tobigitsune/items/162c2c6ef818eba6f0b6)
[エクセルとVBAを上手に使う①他のファイルからの貼付](http://qiita.com/tobigitsune/items/4ef98a1fb9f1c03c297a)

馬鹿でかい、そして読みにくいマクロを書いてしまうことの一因として、そもそも「ワークシート関数が使いこなせていない」というのがある。
私の持論としては、マクロはコピペと最低限の加工、および最終物の生成さえ行えば事足りると思っていて、その他のあらゆる作業はワークシート上で完結させるほうが結局いろいろな効率が良い（上記記事のとおり）。
そこで、ついマクロに頼ってしまいがちな部分をいかにワークシート関数で処理するかという点について、いくつかの方法を以下にまとめた。

#IFのネストを作らない
次のような式が最も見苦しい。

```
=IF(条件1,IF(条件2,IF(条件3,結果3,結果4),結果2),結果1)
```

読みにくいし書きにくい。保守も辛い。
というわけで、具体的な回避法をまとめた。

##AND,ORを使う

```
=IF(AND(条件1,条件2,条件3),結果1,結果2)
=IF(OR(条件1,条件2,条件3),結果1,結果2)
=IF(AND(条件1,条件2,OR(条件3,条件4)),結果1,結果2)
```

##1重のIFを繋ぐ

```
=IF(条件1,文字列1,"")&IF(条件2,文字列2,"")&IF(条件3,文字列3,"")
=IF(条件1,数値1,0)+IF(条件2,数値2,0)+IF(条件3,数値3,0)
```

##論理値に数値を掛ける
※論理値を四則演算に巻き込んだ場合、TRUE=1、FALSE=0と解釈される

```
=(条件1)*(条件2)*(条件3)*数値1
=(条件1)*数値1+(条件2)*数値2+(条件3)*数値3
```

##配列数式
※数式を入力後Ctrl+Shift+Enterで配列数式となり、自動で外側に{}がつく

```
{=SUM({条件1,条件2,条件3}*{数値1,数値2,数値3})}
```

##LOOKUP

```
=LOOKUP(A1,{0,5,10,15,20},{結果1,結果2,結果3,結果4,結果5})
```

##CHOOSEとMATCHの合わせ技

```
=CHOOSE(MATCH(A1,{"あ","い","う"},0),結果1,結果2,結果3)
```

##CHOOSEと二進法
※冗談に近いが、稀に役立つ機会もある。

```
=CHOOSE(1+(条件1)*1+(条件2)*2+(条件3)*4,結果1,結果2,...,結果8)
```

#VLOOKUPを避ける
ExcelといえばVLOOKUPという感じだが、この関数はどうも読みにくい。

```
=VLOOKUP(A1,$C$1:$T$100,18,FALSE)
```

原因は第3引数の18という数値だろう。上の式で18が本当にT列を指すのか、それとも間のどこかなのか、数秒で判断することは難しい。
そもそも広い範囲をガバッと参照して、その間を無視して列数で指定するというのが意味不明だ。そこでできるだけこれを使わない方法をまとめた。

##SUMIF
※求めたいものが数値である、かつ、条件にあてはまるセルが1つしかないと決まっている時、以下の式で上記の式と同じものになる

```
=SUMIF($C$1:$C$100,A1,$T$1:$T$100)
```

列全体を参照したいなら以下のほうがよい

```
=SUMIF(C:C,A1,T:T)
```

##配列数式
※{}内を入力後Ctrl+Shift+Enter
※使用できる条件は上のSUMIFと同じ

```
{=SUM(($C$1:$C$100=A1)*$T$1:$T$100)}
```

2007以降のExcelなら以下の書き方も可能

```
{=SUM((C:C=A1)*T:T)}
```

##MATCHとINDEXの合わせ技

```
=INDEX($T$1:$T$100,MATCH(A1,$C$1:$C$100,0))
=INDEX(T:T,MATCH(A1,C:C,0))
```

トリッキーだが、INDEXの代わりにINDIRECTも使える

```
=INDIRECT("T"&MATCH(A1,C:C,0))
```

#同じ式を二度書かない
次のような式も見苦しい。

```
=IF(式1>0,式1,0)
```

これは式1が非常に長い場合、関数欄では非常に読みにくい。
それ以上に困るのが保守の時で、二箇所を修正しなければならなくなる。

もっとも良いのは、単純に、式1の部分を別のセルに書き、それを参照する方法。
補助セルという概念である。
しかし、無駄にセルを増やしたくないという場合にそなえ、色々な方法を考えた。
同じ式を二度書きたくなるような状況に分けてまとめる。

##式が正ならその値、負なら0としたい

```
=MAX(式1,0)
=TEXT(式1,"0;!0")*1
```

"0;!0"とは、正の場合はその数値、負の場合は文字列"0"を出力する書式を表す。

##式が50以上ならその値、50未満なら0としたい

```
=TEXT(式1,"[>=50]0;!0")*1
```

##式が正ならその値、負ならその絶対値にしたい

```
=ABS(式1)
=TEXT(式1,"0;0")*1
```

##式が正ならその値+「円」、負なら空白にしたい

```
=TEXT(式1,"0円;")
```

※このように値で分岐させる表示はほぼなんでもTEXT関数でできる。

```
=TEXT(式1,"0円の利益です;0円の損失です")
```

##式が正常ならその値、エラーなら空白にしたい
※2007以降のExcel限定

```
=IFERROR(式1,"")
```

#日付計算
エクセルにおいて最も面倒かつ重要なのが日付に関わる計算。
本来はアドインでEDATE、DDATE、EOMONTH等の関数を使えるようにするのが最適解だが、何故かそれをしたくない人向け。
なお、どうしても同じ日付を何度も参照することになるので、途中計算は逐一補助セルを作っていくべきである。
以下、実際の計算についてまとめた。
##形式の変換
A1セルに入力されているyyyymmdd形式をシリアル値へ変換

```
=TEXT(A1,"0000!/00!/00")*1
```

A1セルに入力されているシリアル値をyyyymmdd形式へ変換

```
=TEXT(A1,"yyyymmdd")*1
```

##月始、月末
A1セルにシリアル値で入力されている日付と同じ月の月始

```
=DATE(YEAR(A1),MONTH(A1),1)
```

A1セルにシリアル値で入力されている日付と同じ月の月末

```
=DATE(YEAR(A1),MONTH(A1)+1,0)
```

##一ヶ月進める
A1セルにシリアル値で入力されている日付の翌月同日
ただし、31日等で月末を越える場合は月末とする

```
=MIN(DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)),DATE(YEAR(A1),MONTH(A1)+2,0))
```

##年齢計算
誕生日、現在の日付がyyyymmdd形式で用意されていれば、

````
=INT((現在の日付-誕生日)/10000)
````

したがって、それらがシリアル値の形式ならば、

```
=INT((TEXT(現在の日付,"yyyymmdd")-TEXT(誕生日,"yyyymmdd"))/10000)
```