1
VLOOKUP 関数
VLOOKUP 関数は、最もポピュラーで使用頻度の高い検索/行列関数です。
構文は次のとおりです。
=VLOOKUP(検索値,範囲,列番号,[検索の型])
「範囲」の左端の列が「検索値」と合致する行の「列番号」のセルの値を返します。
=VLOOKUP(C1,F1:I12,3)
下図では、左端(F 列)が「5」である行の「3」列目のセルの値を返しています。
VLOOKUP 関数は検索の型で異なる動作をします。
検索の型が、省略されている場合(上図)や「TRUE」または「1」の場合は、左端の列が昇順で並べ替えられていることが 必要です。この場合、検索値が左列の最大値を超えている場合は、最大値の行の値を返します。
左端の列が昇順で並べ替えられていない場合は、正しい値を返さないことがあります。
2
検索の型が「FALSE」又は「0」の場合は、左端を昇順に並べ替えておく必要はありません。
が、該当する値がない場合は、エラーになります。
旅行命令書など、名前から職名や住所を VLOOKUP 関数で取得する場合は、必ず、4 番目の引数を「FALSE」又は
「0」にします。
=VLOOKUP($L$13,職員データ,2,FALSE)
学校事務では、
=VLOOKUP(検索値,範囲,列番号,FALSE)
=VLOOKUP(検索値,範囲,列番号,0) と使うものだと考えておいても、良いかもしれません。
3
HLOOKUP 関数
VLOOKUP 関数が、範囲の左端の列を検索するのに対し、HLOOKUP 関数は、範囲の上端の行を検索します。
「範囲」の上端の行が「検索値」に合致する列の「行番号」のセルの値を返します。
HLOOKUP(検索値,範囲,行番号,[検索の型])
=HLOOKUP(C1,F1:Q3,3)
下図では、上端(1 行)が「8」である列の「3」行目のセルの値を返しています。
「検索の型」は、VLOOKUP 関数と同様です。
HLOOKUP 関数が使われる機会は、それほどありません。というのも、表はデーターベース的(左端に、排他的な ID を持 たせる)に作ったほうが、利用価値が高いからです。
非常勤勤務状況報告書の例
下図左の表で時間数を管理しているため、HLOOKUP 関数で該当月の時数を取得します。
=HLOOKUP(A20,勤務データ!$B$1:$C$13,IF(MONTH(報告書!$A$10)<4,MONTH(報告書!$A$10)+
9,MONTH(報告書!$A$10)-2),0)
※ A10 には、作成月の初日の日付を入れています。
4
CHOOSE 関数
CHOOSE 関数は、値のリストからインデックス番目の値を取り出します。
=CHOOSE(インデックス,値 1,[値 2],...)
インデックスの小数部分は無視されます。また、インデックスが 1 以下の場合や値の個数を超えると、エラー値(#VALU E!)になります。
値は、最大 254 個の数値、セル範囲、名前、数式、関数、または文字列が指定できます。
セル範囲が引数の場合、単一のセルならセルの値を返します。
複数セルのセル範囲の場合は、エラー(#VALUE!、引数の種類が正しくない)になります。が、SUM 関数などの引数に すれば、正しく機能します。
1 子、2 子、・・・で会費が異なる場合など、一見、IF 関数で処理したくなりますが、「1 子、2 子、・・・」をコード化すれば、
CHOOSE 関数で簡単に取り出すことができます。
=CHOOSE(B2,300,150,0)
1 子は 300 円、2 子は 150 円、3 子以降は 0 円である場合、それぞれを 1,2,3 とコード化するだけの話です。
5
MATCH 関数
MATCH 関数は、検査範囲内で検査値を検索し、その相対的な位置を数値で返します。
=MATCH(検査値,検査範囲,[照合の型]) 検査値
1 または省略 検査値以下の最大の値が検索されます。このとき検査範囲のデータは、昇順に並べ替えておく必要があり ます。
0 検査値に一致する値のみが検索の対象となります。このとき検査範囲を並べ替えておく必要はありませ ん。
-1 検査値以上の最小の値が検索されます。このとき検査範囲のデータは、降順に並べ替えておく必要があり ます。
=MATCH(D2,$B$2:$B$14,0)
下図では、「宮崎県教育研修センター」が、検査範囲の 5 番目であることを返しています。
また、照合の型が「0」の場合、一致するものがない場合 MATCH 関数は、エラーを返します。
次の例は、IF 関数で MATCH 関数がエラーであるかどうかを判断し、一致するものがあった場合のみ「該当」を表示しま す。
=IF(ISERROR(MATCH(D2,$I$1:$U$1,0)),"","該当") これは、年齢から「指定年齢」であるかどうかを判断する式です。
年齢は、DATEDIF 関数を使用しています。
=DATEDIF(C2,DATE(2016,4,1),"y")
6
また、MATCH 関数は、INDEX 関数の引数とし、動的に値を返す場合にも使用されます。
次の例では、会場名の上のセルに、市町村名を返します。
=INDEX($B$2:$C$14,MATCH(D2,$B$2:$B$14,0),2)
ただし、これは VLOOKUP 関数を使用する方が簡単です。
=VLOOKUP($D$2,$B$2:$C$14,2,0)
VLOOKUP 関数は 2002 以降の関数であり、上で紹介した INDEX 関数の使用例は、Excel97 や 2000 では必須 でした。
従って、2010 や 2013 といったバージョンの Excel が多く使われている現状では、INDEX 関数と MATCH 関数の組 み合わせを使うことはないでしょう。
7
INDIRECT 関数
INDIRECT 関数は、参照文字列で示されるセルやセル範囲の参照を返します。
=INDIRECT(参照文字列,[参照形式])
参照文字列は、セルの参照を表す文字列や定義された名前です。
参照形式は、省略可です。A1 形式でセルを参照している限り必要のない引数です。
引数は、セルのアドレスを表す文字列なので、必ず「”」で挟みます。
=INDIRECT("A5")
上の例は、次の式と同じです。
=A5
これでは、わざわざ INDIRECT 関数を使う必要はありません。
実際は、動的に参照を変更する場合に、INDIRECT 関数は使用されます。
もっとも有名なのが、入力規則のリストを絞り込む場合です。
市町村名で会場のリストを絞り込みます。
まず市町村ごとに名前を定義します。
日向市 ・・・・・ A2:A10 の範囲 延岡市 ・・・・・ B2:B10 の範囲 門川町 ・・・・・ C2:C10 の範囲 宮崎市 ・・・・・ D2:D9 の範囲
F2 に元の値を=$A$1:$D$1 として、入力規則のリストを設定します。
8 G2 に元の値を次の数式として、入力規則のリストを設定します。
=INDIRECT($F$2)
リストで市町村名を切り替えると、同じ定義された名前を持つ範囲に参照が切り替わります。
「宮崎市」で絞り込んだ場合、INDIRECT 関数は次と同じ動作をします。
9
OFFSET 関数
OFFSET 関数は、セルまたはセル範囲から指定された行数と列数だけシフトした位置にあるセル範囲の参照を返します。
=OFFSET(基準,行数,列数,[高さ],[幅])
高さと幅は省略できます。そこで、基準と行数、列数のみを指定してみます。
=OFFSET(A1,5,3)
下図では、この式は「13」を返します。=D6と同じです。
A1 から下に「+5」、右に「+3」のセルということです。
下に「+3」、右に「+1」のセル、B4 は次の式で返します。
=OFFSET(A1,3,1)
これに、高さ「2」と幅「2」を指定します。
=OFFSET(A1,3,1,2,2)
複数セルの参照となるため、エラーとなります。そこで、SUM 関数の引数にします。
=SUM(OFFSET(A1,3,1,2,2))
B4 から、高さ「2」、幅「2」のセル範囲、B4:C5の合計が返されます。
OFFSET 関数は、あまり使う機会のない関数の一つですが、増減するセル範囲の自動取得は必須のテクニックです。
10
OFFSET 関数の定番 1 入力規則
リストの元の値を単に、セル範囲にしておくと、データが増えた場合は、範囲を変更しなければなりません。
=$A$1:$A$8
OFFSET 関数を使用すると、変動するセル範囲を取得できるため、データの増加に自動的に対応できます。
=OFFSET(基準,行数,列数,[高さ],[幅])
データの増加は、[高さ]を COUNTA 関数で取得します。行数と列数は、「,,」で省略できます。
=OFFSET(A1,,,COUNTA(A:A),1)
ここで、A1 や 1 行目を削除すると、OFFSET 関数がエラーになり、リストが機能しません。
これには INDIRECT 関数で A1 を参照することで、対応できます。
=OFFSET(INDIRECT("A1"),,,COUNTA(A:A),1) A1 を削除しても、エラーになりません。
また、数式で値を返しているセル範囲では、COUNTA 関数のみでは、高さを取得できません。
=IFERROR(INDIRECT("A" & SMALL(C:C,ROW())),"")
このような場合は、数式が設定されている行数から COUNTBLANK 関数で空白行数を差し引くことで、高さを得ることが できます。
=OFFSET(E1,,,20-COUNTBLANK(E1:E20),1)
11
OFFSET 関数の定番 2 名前の定義
OFFSET 関数で、可変する名前の定義範囲を自動で取得します。
=OFFSET(Sheet1!$A$2,,,COUNT(Sheet1!$A:$A),5)
ここでは、A 列の数値をカウントするため、[高さ]は COUNT 関数で取得できます。
次の例は、VLOOKUP 関数の引数に、「職員データ」と定義された名前を使用しています。
=VLOOKUP($A$2,職員データ,COLUMN())
データを追加します。
正しく反映します。
ただし、OFFSET 関数を使用した名前は、名前ボックスには表示されません。
12
COLUMN・ROW 関数
COLUMN 関数は、単一のセルを参照している場合はそのセルの列番号を、セル範囲を参照している場合は一番左の列 番号を、引数が省略されている場合は関数が入力されているセルの列番号を返します。
=COLUMN([範囲])
下図では、A2 以降同じ数式が入っています。A1 の日付を変えると、自動でそれに続く日付を得ることができます。
=$A$1+COLUMN()-1
このように、COLUMN 関数を連続で増加する変数として使うことができます。
ROW 関数は、単一のセルを参照している場合はそのセルの行番号を、セル範囲を参照している場合は一番上の行番号 を、引数が省略されている場合は関数が入力されているセルの行番号を返します。
=ROW([範囲])
あまり使うことのない関数ですが、同じ数式をコピーする場合など、増加値をこれらの関数で置き換えることができます。
=VLOOKUP($A$2,職員データ,COLUMN())
ROW 関数の定番
条件付き書式で 1 行おきに塗りつぶします。「=1」は奇数行を、「=0」は偶数行を塗りつぶします。
=MOD(ROW(),2)=1 5 行おきに塗りつぶします。
=MOD(ROW(),5)=1