上記の表は、左の表の中に同じ商品が、いくつあるのかを数えたものである。
セルH4に、「=COUNTIF($C$4:$E$10,$G4)」と入力すると「2」と表示された。
COUNTIF関数は、指定した条件を、範囲の中にいくつあるのかを数える関数である。



上記は、3ケ月の売上回数を上位順に抽出したものである。
セルH13に、「INDEX($G$4:$G$10,MATCH(LARGE($H$4:$H$10,G13),$H$4:$H$10,0))」と
入力すると「ボールペン」と表示された。
これは、G4からG10までの商品名に対して、H4からH10に売上回数を表示したものを基に、
G13の売上1位を、H4からH10までの数値の大きい順に抽出した、一番目に大きい数値「6」の
行のG7の「ボールペン」を表示したものである。それをH15までコピーすると3位まで表示される。

上記の図は、右の表にある氏名が、左の表にあるのかを調べて、あると「継続」、無いと「新規」と
表示するものである。
セルM4に、「=IF(ISERROR(VLOOKUP(I4,$C$4:$C$9,1,FALSE)),"●新規","継続")」と入力すると、
「継続」と表示される。それをM11までコピーする。
VLOOKUP関数の引数は、「=VLOOKUP(検索値,検索範囲,データを取り出す列,エラーにするか
近似値を表示するか)」であり、最後の引数の「エラーにするか近似値を表示するか」の部分を、
「TRUE」又は「省略」すると、近似値が取得される。「FALSE」又は「0」を指定すると、同じ値が
ない場合には「エラー」を表示する。ここでは「FALSE」と書いてあるので、エラーが出力される。
そのエラーをISERROR関数が取得し、「エラー」なら「●新規」と表示し、エラーでなかったら
「継続」と表示している。
ISERROR関数の引数は、「=ISERROR(処理内容)」と指定する。

MATCH関数は、指定したデータが範囲の中で、何番目に位置するのかを求める関数である。



セルC3に、「=MATCH(C2,C6:C10,0)」と入力すると、「3」と表示された。
これは、C2の[検索値]「赤」がC6から数えて、下に3番目に存在することを示している。
B列の数値とは関係がない。
MATCH関数の引数は、「=MATCH(検索値,範囲,検索方法)」と指定する。範囲は、列でも
行でも構わない。但し、検索できる範囲は、1列か1行のみである。



C14セルに、「MATCH(C13,C17:G17,0)」と入力すると、「3」と表示された。
C17から数えて、右に3列目に「赤」があるからである。16行目の数値が関係ない事が
分かる。指定範囲の何番目にあるのかを示す関数である。
この表を見て、色名を入力すると、色番号を表示できないのかと、色々試みたが、
MATCH関数だけでは、表示することが出来ない。



上図は、セルC20に、「INDEX(C23:G23,MATCH(C19,C22:G22,0))」と入力すると、
「1」と赤色の色番号が表示された。
INDEX関数の引数は、「=INDEX(範囲,行位置,列位置)」と指定するので、範囲に番号抽出の
[行位置]の「C23:G23」を指定し、[列位置]にMATCH関数で「赤」に対する列位置「3」を
求めて「色番号」「1」を抽出することができた。



上記の表は、各学期の成績順1位から3位までの、氏名を抽出したものである。
セルI４に、「=INDEX($B$4:$B$13,MATCH(LARGE(C$4:C$13,$H4),C$4:C$13,0),1)」と
入力して、L6までのすべてのセルにコピーすると、I4からL6までに、学期別の1位から
3位までの氏名が表示された。
INDEX関数の引数は、「=INDEX(範囲,行位置,列位置)」で指定したデータを取り出す。
MATCH関数の引数は、「=MATCH(検索値,範囲,検索方法)」と指定する。
LARGE関数の引数は、「=LARGE(範囲,順位の数値)」と指定する。
だから、I4セルの式は、「LARGE(H4と同じ1位の値を、C4:C13の範囲から見つけて),
MATCH(その値と同じ値がある),INDEX(行を,B4:B13で探して、その氏名を表示する)」と
言うことになる。
尚、最下位を求めるには、「=INDEX(B4:B13,MATCH(MIN(C4:C13),C4:C13,0),1)」となる。
MIN関数の引数は、最下位と決まっているので、「=MIN(範囲)」と[範囲]だけ指定する。



上記の表は、上位合格率を入力すると、合格点を参照して、合格点に達している人の点数を
表示し、合格人数と合格者を抽出するものである。



G4セルに、「=IF(F4>=PERCENTILE($F$4:$F$13,$I$5),F4," ")」と入力すると、
上位45％以上の合格点に入ると合格判定列に表示される。
G4セルの式をG13までコピーすると、合格点の行だけが合格判定列に表示される。
PERCENTILE関数の引数は、「=PARCENTILE(範囲,百分率した値)」と指定する。
ちなみに、I7セルに、「=PARCENTILE(F4:F13,I5)」と入力してみる。
「=PERCENTILE(F4からF13の範囲,0.55)」となり、「256.45」と表示される。
これは、範囲内の値で45％以内に合格するには、合計点が256.45か、それ以上で
なければならない。「256.45」以下なら空白となる。



K4セルに、「=IF($G$14<ROW( )−3," ",INDEX($B$4:$B$13,MATCH(LARGE($G$4:$G$13,
　　　　　　　ROW( )−3),$G$4:$G$13,0),1))」と入力すると、「北川」と表示された。
K列には、合格判定得点の1位から順に、G14で示した合格人数が得点順に下方に表示される。
ここで、「LARGE(G4:G13,ROW( )−13)」の「ROW( )−3」の意味を見てみよう。



上記のL4セルに、「=ROW( )−3」と入力すると、「1」と表示されている。
これは、「ROW( )」と入力すると、現在のセルの行位置が表示される。現在の行位置は
「4」で、そこから「−3」すると「1」となる。これは、順位の1から順に数値を入れる
ためのもので、下の方へ数値を書かなくても、LARGE関数に順位の数値を入れるための
ものである。INDEX関数で、合格判定に得点が表示された行の、1位から順にG14の値分の
合格者の名前をB列から抽出して表示する。
では、「IF(G14<ROW( )−3," ")」の意味は何か。



上図がその理由である。前述の式の、IF関数の部分を削除した場合に、K列の枠を合格者の
人数変化に対応するために余分に空欄を作って、K4セルの式をコピーするとエラーが出る。
なぜなら、仮にL9セルに、「ROW( )−3」と入力してみると「6」になり、6位まで求める
様になる。だが、G14セルの合格人数は「5」だから、6位はないのでエラーになる。
エラーにならないようにするのが、「IF(G14<ROW( )−3," ")」である。
K列のセルで、G14の値以上の順位を求める式が入っても、G14の値の方が小さいとセルに
空白が入る。

VLOOKUP関数は、別に表から対応するデータを取り出す関数でもある。


上の見積シートに、下のシートのデータを取り出すものである。
見積書のコード番号を入力すれば、品名と単価が抽出されて、C列とD列に記入される。


見積書のC4セルに、「=VLOOKUP(B4,単価!$B$4:$D$9,2,0)」を入力すると、「BB」と
取得されたのが分かる。
VLOOKUP関数の引数は、「=VLOOKUP(検索値,検索を探す表,列番号,検索方法)」と指定する。
「列番号」には、取り出す列を指定するが、検索値を探す列を「1」(ここではNo列)から順に
右に、「2」列目、「3」列目となる。「検索方法」は、「FALSE」又は「0」を指定すると、
完全に一致するデータが検出される。又、「TRUE」を指定すると、一致するデータがない場合、
検索値より小さい近似値を検索する。C4セルは、C6セルまでコピーすることができる。
D4セルの関数は、「=VLOOKUP(B4,単価!$B$4:$D$9,3,0)」の様に、検索値を探す列が「3」
になる。D4セルをD6セルまでコピーできる。



但し、この式では「コード番号」の入力がない場合、エラーが出て見苦しい。



そこで、「IF(B5="","",VLOOKUP(B5,単価!$B$4:$D$9,2,0))」の様にIF関数を
追加すれば、未入力セルが空白にできる。
尚、「=IF(B5="","",VLOOKUP(B5,単価!$B$4:$D$9,2,0))」の引数の「2」は、
列位置を示しているので、C列のセルにしかコピーできないので、式を変更してすべての
列にコピーできるようにするためには、どの様にしたらよいのか。



上図のC4セルの式は、VLOOKUP関数の引数「2」をCOLUMN関数にしている。
「=IF($B4="","",VLOOKUP($B4,単価!$B$4:$D$9,COLUMN(B1),0))」とCOLUMN関数を
使用すると、すべての列とセルに式をコピーできる。
COLUMN関数は、引数にセルを指定すると、そのセル列番号が求められる。
「COLUMN(B1)」を指定すると、「2」が求められる。B1が相対参照なのでコピーすると、
その列の番号になる。「COLUMN(B1)」の「B1」は、B列なら行は何行目でもかまわない。



これは、C2セルに入力した「W004」と言う検索値に基づいて、コードNoを下の表から
検索して、C3セルに「品名」を表示するものである。
セルC3に、「=VLOOKUP(C2,B5:C10,2)」と入力すると、「パンプス」と表示された。
これは、C2の「W004」を検索値として、B5からC10の範囲で、B列を検索列として、
検索値と同じ値の行を特定し、その2列目の値を表示している。
だから、「VLOOKUP(C2,B5:D10,3)」と範囲と列の値を変更すれば、「13,000」と
表示される。



VLOOKUP関数の引数は、「VLOOKUP(検索値,範囲,表示する列,検索方法)」となる。
検索方法は、「FALSE」または「０」を指定すると、検索値がない場合は、「エラー」を
表示する。「TRUE」又は「１」又は、省略すると、検索値がない場合は、検索値より小さい
近似値を検索値を検索値として検索し表示する。



セルC3に、「W003」と入力してあるのに、下の表に無いので「W002」のC7の値が
表示されている。



だが、引数の「検索方法」を省略して、「=VLOOKUP(C2,B5:D11,2)」とC3セルに
入力するとC2セルに「W005」と入力していても、C3には「ハイヒール」と表示されている。
全然違う値である。これは、「検索方法」を省略するときには、検索列のB列はデータが
昇順に並んでいなければならない、と言う決まりがある為である。


検索方法に、「FALSE」又は「0」を指定すると、上図の様に正常に検索される。
但し、該当データがない場合は、エラーが表示される。



上図は、C2セルに入力した値を基に、下の表から、加算マイルを抽出し、C3セルに表示する
ものである。検索値と同じものが、検索範囲にない場合の処理を「TRUE」で指定している。
「TRUE」を指定すると、検索値と同じ値がない場合、検索値より小さい近似値を検索して
表示する。この場合「支払金額」の「12,500」が基準額列に一致しないので、それより小さい
金額「10,000」の行が抽出されて、その右側の列の「120」が表示される。
尚、基準値のデータB列は昇順に並んでいる必要がある。



上図の式の、VLOOKUPの部分だけを見てみると、
「=VLOOKUP($B3 & $C3,$I$3:$K$8,2,FALSE)」となっている。このように検索値を、複数セルの
文字列をつないだものにすることも可能である。
VLOOKUP関数の引数「検索値」にB列とC列をつないだものにする場合、それぞれの番地の間に「＆」
を置く必要がある。
D3セルに、「=IF(OR($B3="",$C3=""),"",VLOOKUP($B3 & $C3,$I$3:$K$8,2,FALSE))」と入力
されている。「=IF(OR($B3="",$C3=""),""」の部分は、B列からC列に値が入力されていない場合に、
品名や単価を空白にするためのものである。
又、D3セルの式をE3にコピーできるように、「＄B3＆$C3」と絶対参照にしている。
但し、D3式をE3にコピーして、「2,FALSE」の部分の値「2」を「3」に変更してからE6までコピーする
必要がある。なぜなら、「品名」列は２列目、「単価」列は3列目であるから。



C列かB列が空白なら、「品名」と「単価」は空白になっている。


上図は、別表からキーワードの入力で、その文字列を含むデータを取り出すものである。
E3セルに、「=VLOOKUP("*" & D3 & "*",$I$3:$J$6,1,FALSE)」と入力すると、「�叶ﾎ田電機」と
表示されている。
これは、D3セルの「石田」の前にある文字列すべて、プラスD3セルの「石田」プラス「石田」の
後にある文字列すべてを、別表の1列目から探し、それを表示すると言うことになる。
すなわち「�梶v+「石田」+「電機」と言うことになる。



上図はF3セルに、「=VLOOKUP("*" & D3 & "*",$I$3:$J$6,2,FALSE)」と入力されている。
別表の2列目から、「西口」を抽出している。
絶対参照にしているので、下方のセルにコピーしても数式は変わらないで正常である。



上図は、Eドライブにある「商品単価表1.xlsm」と言うファイルの、「単価表」と言うシートの
データを、VLOOKUP関数で取り出したものである。



上記がEドライブに保存されている、「商品単価表1.xlsm」ファイルである。
[見積書]ファイルのC3セルに、
「=VLOOKUP(B3,'E:\[商品単価表1.xlsm]単価表'!$B$3:$C$9,2,FALSE)」と
入力されていて、「300」と表示されている。「'E:\[商品単価表1.xlsm]」の部分が
別のファイルが保存されている、ドライブとファイル名を指定している。
「商品単価表'!」この部分がシート名を指定している。「'」を忘れてはいけない。
これで、開いていない別のドライブにある、別のファイルからでも、データを取り出す
ことができる。



上記の左の表は、商品単価の順位を示したものである。右の表は、その中から単価の高い順に
品名を取り出したものである。
セルB5に、「=RANK(D5,$D$5:$D$12)」と入力すると、「5」と順位が表示される。
RANK関数の引数は、「=RANK(数値,参照範囲,順序)」となり、順序には高い方から数える(降順)
には「0」、低い方から数える(昇順)には「1」を指定する。省略すると降順になる。
セルB5を下方にコピーすると、12行までの商品の単価順位が表示される。



上記の右の表は、1位から3位までを抽出するものである。
セルG5に、「=VLOOKUP(F5,$B$5:$D$12,2,FALSE)」と入力すると、「E」と表示された。
一番単価の高い商品は、「E」と表示されたのである。
これは、F５セルの「1」をB5からD12の範囲検索して、その結果一致した行の2列目の「E]を
表示したのである。


H５セルには、F5を検索して、一致した行の2列目の金額を表示している。

INDIRECT関数は、引数に指定したい文字列を、指定セルの値によって指定する関数である。


上図の様に、転記、8月、9月とシートが3枚ある場合を見てみよう。


C4セルに、「=INDIRECT($B$4&"!C4:G15")」と入力すると、「AA」と表示される。
これは、B4セルの「8月」と言う文字列のシート名の、C4からG15までを参照すると言う
ものである。C4セルをG15までにコピーすると、すべてのセルに値が表示される。



コピーしたG15セルの関数は、「=INDIRECT($B$4&"!C4:G15")」とC4セルと変わりがない。
INDIRECT関数の引数には、文字列を指定する必要があるので、シート名に続く「!」と「" "」
でくくり、間を「&」演算子でつなぐ。これは、文字列を入力したセルを指定するものである。
INDIRECT関数は、引数に指定した、文字列の入力されたセルでも、範囲でも参照する。
B4セルは、この内容を書き換えるだけで、そのセルの内容が参照される。だから、シート名と
同じ文字列が入力されることが前提となる。又、転記するシートは、B3からG15までの表範囲
でなくてはならない。



転記シートで、I4セルに同じ関数を入力しても、範囲外だからエラーになる。

OFFSET関数は、基準セルから行数と列数を移動した、セルのデータを取り出す。


B2セルに、「OFFSET(B5,2,4)」と入力すると、「400」と表示される。
これは、B5セルから下へ2行目の、右に4番目の列の、セルを参照すると言うことになる。
引数は、「=OFFSET(基準のセル、移動行数、移動列数)」と指定する。
尚、移動行数と移動列数の値は、正の値を指定すると、下方向、右方向に移動する。
負の値を指定すると、上方向、左方向に移動する。



又、表の範囲を取得することもできる。
セルC3に「=SUM(OFFSET(H4,1,0,B3,1))」と入力すると、「3400」と表示される。
これは、OFFSET関数で、H4セルの1行下から、B3の値「４」で４行目までを範囲とする。
すなわち、H5からH8までを選択することになる。SUM関数でその範囲が合計される。
OFFSET関数は、一つのセルを参照するだけでなく、範囲を参照する事ができる。
その場合の引数は、「=OFFSET(基準のセル、移動行数、移動列数、行の大きさ、列の大きさ)」
のように、第4引数の「行の大きさ」、第5引数の「列の大きさ」を追加すると範囲を参照できる。

CELL関数は、セルの様々の情報を表示する。


セルC2に、「=CELL(　」と入力すると、検査の種類が表示されるので、「filename」を
選択して、キーボードの[Tab]キーを押す。


セルC2に、このファイルのパスと名前、シート名、セル番地が表示される。



セルC２に、「=CELL("format",B1)」と入力すると、B1セルの書式設定が表示される。
C2セルには、日付データが入力されているので、日付の「yyyy/m/d」の形式が設定されて
いる事を示す。







セルC２に、「=CELL("width",B1)」と入力すると、セルの列幅が表示される。但し、列幅を
後で変更しても、値は自動的に変わらない。再計算するには、「F９」キーを押す。

CHOOSE関数は、引数に指定したリストから、その何番目かのデータを表示することができる。


セルE4に、「=CHOOSE(B4,"東京","横浜","静岡","名古屋","大阪")」と入力し、
B4セルに「1」と入力すると、引数の1番目の「東京」が選択表示される。
尚、上の図の場合、「=CHOOSE(B5,"東京","横浜","静岡","名古屋","大阪")」と入力すれば、
「2」が選択されるので、「横浜」と表示される。


尚、引数には上図の様に、セル番地を指定することもできる。又、番号には整数を指定する。
値は、256個を指定することができる。



又、引数には範囲を指定することもできる。上図の様に、G列とH列の合計を引数で選択する
事ができる。セルH10の、「=SUM(CHOOSE(2,G4:G8,H4:H18))」の場合、引数が「2」なので
「H4:H18」の範囲が合計される。セルH10の式は引数が、「1」なら、「G4:G8」の範囲が合計
される。

LOW関数は、指定したセルの行番号が求められる。

セルB4に、「=ROW(B4)」と入力すると、そのワークシートの行番号「4」が表示される。
B4セルの式を下方にコピーすると、連番になりどれかの行が削除されても欠番にはならない。

　
関数の実用事例と引数の解説
　表引きに使用する関数の解説

実際に使っているシステムの内容で解説
　　EXCEL VBA 講座