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