Help us understand the problem. What is going on with this article?

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

More than 3 years have passed since last update.

参照:
エクセル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)
tobigitsune
ひよっこ趣味入門プログラマーです。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away