事務管理に役立つExcelの関数

〇 関数とは

• 関数とは、引数（ひきすう＝パラメーター）と呼ばれる数値や文字列等を与えると、前もって定義された特定の計算処理を行い、結果となる値を返す数式のことです。
• 関数の構文は【 =’関数名’(引数,引数,…) 】で、関数ごとにこの引数のルール（種類、順序、必要な個数など）が定義されており、それを守らないとエラーとなります。引数の種類には、数値、文字列及び論理値(TRUEまたはFALSE)がありますが、結果的に定義された引数の種類になるのであれば、引数は数値や文字列そのものではなく、セル参照でも、また多くの場合、関数などの数式でも構いません。なお、構文で、引数が角括弧「［ ］」で囲われていれば、その引数は省略することも可能という意味です。
• 連続していないセルを引数とする場合、引数が1つの関数ではカンマ「, 」を使って参照できる場合があり ます【 =SUM(A2:A8,C2:C8) 】。また、引数が複数の関数では丸括弧「()」で括って参照できる場合があります【 =RANK(D5,(D3:D8,D14:D16),0) 】。

〇 合計関数（SUM、SUMIFS、SUBTOTAL）

• SUM関数の構文は【 =SUM(対象範囲) 】で、対象範囲を合計します。対象範囲内に（数値ではない）文字列の入ったセルがあっても除外して計算してくれます。使用頻度の高い関数なので、［ホーム］タブの［∑オートSUM］や、（クイックアクセスツールバーに［Σ］を登録している場合は）クイックアクセスツールバーの［Σ］をクリックするのが便利です。
• SUMIFS関数の構文は【 =SUMIFS(対象範囲, 条件範囲1, 条件1, ［条件範囲2, 条件2］, ...) 】で、条件範囲と条件の組合せを複数設定でき（1つでもよい）、複数の条件に全て一致するデータの対象範囲を合計します。従って、各条件はANDの関係（条件1かつ条件2かつ…）となります。
• 例えば、完成工事の過去10年間の一覧表があり、列項目が工事名(A)、土木建築区分(B)、決算年度(C)、売上高(D)、総利益(E)の場合、建築工事の2017年度から2019年度の3年間の売上高は=SUMIFS(D:D, $B:$B,"建築",$C:$C,">=2017",$C:$C,"<=2019")となり、同じく総利益は=SUMIFS(E:E, $B:$B,"建築",$C:$C,">=2017",$C:$C,"<=2019")となります。（注：計算式でB及びCに$を付けて「絶対参照」にするのは、計算式を他の列にコピーするときB及びCは連動して変化しないようにするためです。）
  
• SUBTOTAL関数の構文は【 =SUBTOTAL(集計方法, 対象範囲) 】で、集計方法に応じた対象範囲の集計を行います。一般には、一覧表の途中に小計行を入れる場合に使用する関数で、集計方法を 9 にすると対象範囲を合計します。SUM関数と異なるのは、一覧表の途中途中にSUBTOTAL関数で計算された小計があっても、SUBTOTAL関数で小計行を含んで全体の合計を出しても小計行の数値は除外されるという便利な特性があります。
• SUBTOTAL関数で更に便利なのは、［データ］タブの［フィルター］で指定した条件に外れた数値を除外してくれる点です。具体的には、一覧表の列項目の上にSUBTOTAL関数を使った合計欄を作っておき、当該一覧表をフィルターで条件指定した場合に、条件に合致した場合の数値だけが合計欄に集計され、結果的にはSUMIFS関数を使用するより簡易に条件付合計が得られます。
• 例えば、上記SUMIFS関数の表の例で、1行目に売上高と総利益の合計欄を設けた場合の計算式は、売上高は=SUBTOTAL(9, D$3:D$9999)となり、同じく総利益は=SUBTOTAL(9, E$3:E$9999)となります。（注：9999はデータベースの行数より十分大きければいくらでも構いません。=SUBTOTAL(9, D:D)とできないのは、データと同じ列に合計欄を置いているため「循環参照」エラーを起こすからです。）
  

〇 カウント関数・順位関数（COUNT、COUNTA、COUNTBLANK、COUNTIFS、RANK）

• COUNT関数の構文は【 =COUNT(対象範囲) 】で、対象範囲にある数値の入ったセルの個数を求めます。
• COUNTA関数の構文は【 =COUNTA(対象範囲) 】で、対象範囲にある空白以外のセルの個数を求めます。COUNTBLANK関数の構文は【 =COUNTBLANK(対象範囲) 】で、対象範囲にある空白セルの個数を求めます。
• COUNTBLANK関数の使用法としては、例えば出席者一覧表の出欠欄に、連絡に応じ出席、欠席と入力していく場合に、未連絡者の数（何も入力のないセルの数）をカウントできます。
• COUNTIFS関数の構文は【 =COUNTIFS (条件範囲1, 条件1, ［条件範囲2, 条件2］,…)) 】で、条件範囲と条件の組合せを複数設定でき（1つでもよい）、複数の条件に全て一致するデータの個数を求める関数です。従って、各条件はANDの関係（条件1かつ条件2かつ…）となります。
• 例えば、上記のSUMIFS関数の例で、建築工事の2017年度から2019年度の3年間の工事件数は=COUNTIFS($B:$B,"建築",$C:$C,">=2017",$C:$C,"<=2019")となります。
• RANK関数の構文は【 =RANK(順位を知りたいセル,順位を知りたいセルを含む対象範囲,降順か昇順かの区別) 】で、「降順か昇順かの区別」は降順（大きい順）なら 0 、昇順（小さい順）なら 1とします。対象範囲は、関数を縦方向にコピーしてもずれないよう行は絶対参照とするとよいでしょう。
• 例えば、上記のSUMIFS関数の例で、セルE3の総利益額の順位は【 =RANK(E3,E$3:E$9999,0) 】となります。

〇 平均関数・最大値関数・最小値関数（AVERAGE、AVERAGEIFS、MAX、MAXIFS、MIN、MINIFS）

• AVERAGE関数の構文は【 =AVERAGE(対象範囲) 】で、対象範囲にある数値の入ったセルの平均値を求めます。対象範囲内に（数値ではない）文字列の入ったセルや空白セルがあっても除外して計算してくれますが、0 (ゼロ) を含むセルは計算の対象となります。
• AVERAGEIFS関数の構文は【 =AVERAGEIFS(対象範囲, 条件範囲1, 条件1, ［条件範囲2, 条件2］, ...) 】で、条件範囲と条件の組合せを複数設定でき（1つでもよい）、複数の条件に全て一致するデータの対象範囲の平均値を求める関数です。従って、各条件はANDの関係（条件1かつ条件2かつ…）となります。
• 例えば、上記のSUMIFS関数の例で、建築工事の2017年度から2019年度の3年間の工事の平均請負金額は=AVERAGEIFS(D:D,$B:$B,"建築",$C:$C,">=2017",$C:$C,"<=2019")となります。
• MAX関数の構文は【 =MAX(対象範囲) 】で、対象範囲にある数値の入ったセルの最大値を求めます。対象範囲内に（数値ではない）文字列の入ったセルや空白セルがあっても除外して計算してくれます。MIN関数は同様に最小値を求めます。
• MAXIFS関数の構文は【 =MAXIFS(対象範囲, 条件範囲1, 条件1, ［条件範囲2, 条件2］, ...) 】で、条件範囲と条件の組合せを複数設定でき（1つでもよい）、複数の条件に全て一致するデータの対象範囲の最大値を求める関数です。従って、各条件はANDの関係（条件1かつ条件2かつ…）となります。MINIFS関数は同様に条件付の最小値を求めます。なお、MAXIFS関数とMINIFS関数は、共に「Excel2019」から利用できるようになった新しい関数ですので、旧いバージョンのExcelを使っている人と共有は難しいと思われます。
• 例えば、上記のSUMIFS関数の例で、建築工事の2017年度から2019年度の3年間の工事で最大請負金額は=MAXIFS(D:D,$B:$B,"建築",$C:$C,">=2017",$C:$C,"<=2019")となります。

〇 概数関数（ROUND、 ROUNDUP、ROUNDDOWN、MROUND、CEILING、FLOOR）

• ROUND関数の構文は【 =ROUND(数値, 桁位置) 】で、数値を指定された桁位置で四捨五入します。桁位置の指定は必須で、0の場合を小数点の位置とし、正の数（1、2、…）の場合はその数に応じ小数点より下の桁位置で、負の数（-1、-2、…）の場合はその数に応じ小数点より上の桁位置で四捨五入します。例えば、金額の四捨五入で1,000円単位なら、桁位置は -3 になります。
• ROUNDUP関数の構文は【 =ROUNDUP(数値, 桁位置) 】で、数値を指定された桁位置で切り上げます。
• ROUNDDOWN関数の構文は【 =ROUNDDOWN(数値, 桁位置) 】で、数値を指定された桁位置で切り捨てます。
• MROUND関数の構文は【 =MROUND(数値, 倍数) 】で、数値を指定された倍数で四捨五入します。例えば、社員の新本給を、旧本給×定昇倍率で計算する場合、1,000円単位で四捨五入では少し大雑把、100円単位で四捨五入では細か過ぎると感じれば、500円単位で四捨五入することもありえますよね。この場合、倍数を500にすれば良いわけです。
• CEILING関数の構文は【 =CEILING(数値, 倍数) 】で、同様に指定された倍数で切り上げます。★CEILING関数は時間の概数化にも便利で、例えば1ヶ月間の時間外の総時間数を15分単位で切り上げたいときは、セルA1に 28:26 という時間があった場合、【 =CEILING(A1, "0:15") 】とした場合の計算結果は 28:30 となります（なお、この場合のセルの書式の表示形式は [h]:mm です⇒日付関数の項を参照）。
• FLOOR関数の構文は【 =FLOOR(数値, 倍数) 】で、同様に指定された倍数で切り捨てます。

〇 論理関数（IF、IFS、AND、OR）

• プログラミング制御において最も重要な「条件付分岐命令」（条件に応じてデータ処理方法を変える）をExcelで実現するのがIF関数です。
• IF関数の構文は【 =IF(条件式 , 成立する場合(TRUE)の数式 , 成立しない場合(FALSE)の数式) 】で、条件式が成立するかしないかで異なる数式を実行します。条件を複数にすることができるようにするため、［成立する場合の数式］や［成立しない場合の数式］を更にIF関数を使った数式とすることができ、このように、もし(IF)～が成立した（しなかった）場合に、更にもし(IF)～が成立した（しなかった）場合に…と、IF関数の中に更にIF関数を繰り返すことを、ネスト（入れ子）構造と呼びます。1つの計算式でIF関数のネストは以前は7つまででしたが、最近は64まで可能で事実上気にする必要はありません。
• 例えば、上記SUMIFS関数の表の例で、G列に土木で売上高3000以上、建築で売上高5000以上の工事を「重要工事」と表示する式は、【 =IF(B3="土木",IF(D3>=3000,"重要工事",""),IF(B3="建築",IF(D3>=5000,"重要工事",""),"")) 】となります。
  
• IFS関数の構文は【 =IFS（条件式1,成立する場合の計算式1,条件式2,成立する場合の計算式2,…,TRUE,成立しない場合の計算式） 】で、［条件式1］が成立するときは［成立する場合の計算式1］を実行し、［条件式1］が成立しないときで［条件式2］が成立するときは［成立する場合の計算式2］を実行し、［条件式2］が成立しないときで…と続き、全ての条件式が成立しないときは［成立しない場合の計算式］を実行します。IF関数のように IF を繰り返す必要がなく計算式がシンプルですが、条件が一度成立すると実行されてしまうので、（条件式1が成立し、更に条件式2が成立した場合だけ実行するといった）複合条件を扱えるわけではないようです。
• IFS関数は「Excel2016」から利用できるようになりましたが、個人的には制限のあるIFS関数の使用は避け、より万能のIF関数に絞って使用する方が混乱せずに良いかと考えます。
• AND関数とOR関数は、共に主としてIF関数の条件式において使用し、2つ以上の条件式を1つの条件式にまとめます。AND関数の構文は【 =AND（条件式1,条件式2,…） 】で、条件式1,条件式2,…の全てが成立したときを表す条件式となります。OR関数の構文は【 =OR（条件式1,条件式2,…） 】で、条件式1,条件式2,…ののいずれかが成立したときを表す条件式となります。AND関数とOR関数は相互にその引数にも使え、2つを組み合わせることで複雑な条件式を構成できます。
• 例えば、上記IF関数の例の計算式は【 =IF(OR(AND(B3="土木",D3>=3000),AND(B3="建築",D3>=5000)),"重要工事","") 】としても結果は同じで、IF関数をネスト構造にしなくても、ANDとORの組合せで記述できています。

〇 参照関数（VLOOKUP）

• Excelでデータベース（「工事一覧表」など）を作成するとき、例えば得意先名「株式会社青木商事」をその都度手入力すると、「青木商事」とか「㈱青木商事」といった入力も可能であるため、後にデータベースで得意先名を「株式会社青木商事」で検索しても、株式会社青木商事から発注された全ての工事が検索されてこない可能性があります。そこで、工事一覧表とは別シートにあらかじめマスターファイルである「得意先一覧表」（得意先コード、得意先名、得意先名カナヨミ）を作っておき、工事一覧表に入力する際は得意先コード（検索値）を入力すると、得意先一覧表から得意先名が正確に参照されるようにします。この際に、工事一覧表のシートで使用する関数がVLOOKUP関数です。
• VLOOKUP関数の構文は【 =VLOOKUP(検索値, 検索する範囲, 検索する範囲で参照するデータは何列目か, FALSE(完全一致)またはTRUE(近似一致)) 】で、マスターファイルの検索であれば完全一致でなければ意味がないので最後の引数は必ずFALSEです（なお、実務でTRUEを使うことも稀ながらあります⇒ckjsk813_Excel（データベース化）の「決算期」を求める計算式の例を参照）。
• 例えば、次のような工事一覧表と得意先マスターがある場合、工事一覧表に得意先コードを入力すると、得意先名が得意先マスターから参照されて表示されるには、工事一覧表の得意先名欄C2の計算式は【 =VLOOKUP(B2,得意先マスター!A:C,2,FALSE) 】となります。（得意先マスターのあるシート名を「得意先マスター」とした場合。2 は得意先名称がマスターの検索範囲（A列～C列）の2列目（B列）にあるという意味です。）
  

〇 日付関数（DATE、YEAR、MONTH、DAY、DATEVALUE、DATEDIF、EDATE、NOW）

• Excelでは日付や時刻はシリアル値(1900年の元旦を1とし1日に1ずつ増える、2020年元旦は43831)で管理していますが、日付のシリアル値の入ったセルの書式を、［セルの書式設定］の［表示形式］で［日付］形式とすることで、実際には数値であるシリアル値のまま、見た目は文字列のような日付（ 2020年10月1日 や 2020/10/1 など）として表示することができます。実際にはシリアル値という数値であるため、日付同士の引き算も可能で、その結果は日付間の日数（両日付の日も含めると引き算の結果に1を加える）となります。
• なお、［日付］形式について、あらかじめ用意されているものでは桁が揃うものがないので、実務的には［表示形式］の［ユーザー定義］で yyyy/mm/dd 形式に設定する方が良いでしょう。（yyyy/mm/dd 形式では、2020/1/1を2020/01/01と表示するので、例えば工事一覧表に契約日や工期の欄がある場合、どのデータも常に桁数が10桁に揃って見易くなります）。
• 日付関数は、シリアル値である日付に対し、様々な計算や加工を可能とするものです。
• DATE関数の構文は【 =DATE(年,月,日) 】で、年、月、日の各数値から当該年月日が表す日付のシリアル値を求めます。例えば、年(2020)、月(10)、日(1)が別々のセルのデータとして存在するとき、この関数の引数(年,月,日) としてそれぞれセル参照することで当該日付のシリアル値が求まり、結果として1つの日付(2020/10/01)として表示できます。
• YEAR関数の構文は【 =YEAR(日付(シリアル値)) 】、MONTH関数の構文は【 =MONTH(日付(シリアル値)) 】、DAY関数の構文は【 =DAY(日付(シリアル値)) 】で、こちらはDATE関数とは逆に1つの日付(シリアル値)から、年、月、日を別々のデータとして取り出すことができます。
• DATEVALUE関数の構文は【 =DATEVALUE(日付文字列) 】で、日付形式の文字列を、日付関数を使った計算ができるよう、シリアル値に変換します。何らかのシステムからＣＳＶ出力した場合などで日付データが文字列形式となっていることがあり、日付を加工する必要があれば、先に数値形式（シリアル値）に変換します。変換できる日付形式の文字列の種類は、［セルの書式設定］にある［日付］形式の種類とほぼ同じです。
• DATEDIF関数の構文は【 =DATEDIF(開始日,終了日,単位) 】で、2 つの日付の間の、年数（引数の「単位」が"Y"のとき）、月数（同じく"M"）、日数（同じく"D"）を計算する、非常に便利な関数です。なお、計算結果は切り捨てになります。
• 例えば、セルK3に工期の開始日が、セルL3に工期の終了日があるとき、工期の日数は【 =DATEDIF(K3,L3,"D")+1 】となります（この計算は、【 =L3-K3+1 】としても同じです）。また、例えば、社員名簿があり、生年月日がセルD3に、入社年月日がセルF3にあれば、2020/4/1現在の年齢は【 =DATEDIF(D3,"2020/4/1","Y") 】で、経験年数は【 =DATEDIF(F3,"2020/4/1","Y") 】で求めることができます。なお、このような場合、シートのどこかに計算の基準となる日付を入力する欄を設け、このセルに名前ボックスで例えば「基準日」といった名前を付けておくと、上記の計算式は【 =DATEDIF(D3,基準日,"Y") 】となり、計算の基準となる日付を変えることになっても数式を直す必要がなくなり便利です。
• EDATE関数の構文は【 =EDATE（日付, 月数） 】で、日付から月数が経過した日を求めます。月数がマイナスの時は月数を遡った日となります。
• 例えば、セルK3に工期の開始日が、セルL3に工期の終了日があるとき、工期を単なる日数ではなく何年何月何日と表現したい場合の計算式は少々長いですが【 =DATEDIF(K3,L3,"Y")&"年"&MOD(DATEDIF(K3,L3,"M"),12)&"月"&(L3-EDATE(K3,DATEDIF(K3,L3,"M"))+1)&"日" 】となります。
• NOW関数の構文は【 =NOW() 】で、珍しく引数のない関数で、現在の日付・時刻を表すシリアル値を返します。シートの印刷範囲の一定の位置にこの関数を置くようにしておくと、印刷したものが何日の何時のバージョンのものか分かって便利です。日付だけで良い場合は、TODAY関数【 =TODAY() 】でも良いですが、NOW関数でも［セルの書式設定］で yyyy/mm/dd などと書式設定しておけば良いことです。（［ページレイアウト］の［印刷タイトル］の［ヘッダー/フッター］設定で、ヘッダーかフッターに［日付の挿入］や［時刻の挿入］をしても同様です。）
• 日付関数ではないですが、日付や時刻は、［セルの書式設定］の［表示形式］の［ユーザー定義］で表示方法を使いやすく設定できます。
• 日付は、年月日をスラッシュ（/）で区切る場合、標準の書式では yyyy/m/d がありますが、この場合、月や日付が1桁と2桁の場合で長さが異なり見づらくなります。事務処理的には月や日付が1桁の場合に前ゼロを付けて桁数を揃えた方がはるかに見易いので、［ユーザー定義］で書式を yyyy/mm/dd とする習慣をつけた方が良いでしょう（ex. 2021年5月3日 は 2021/05/03 と表示されます）。ちなみに年を下2桁表示にする場合は yy/mm/dd とします（ex. 21/05/03 と表示されます）。年を和暦表示にする場合は rr/mm/dd （もしくは gggee/mm/dd ）とします（ex. 令和03/05/03 と表示されます。 r/mm/dd なら 令和3/05/03 と表示）。
• 年月日に対応する曜日を表示したいときは、［セルの書式設定］の［表示形式］の［ユーザー定義］で aaa とします。例えば、セルA3に日付があれば、セルB3の計算式を【 =A3 】とし書式を aaa とするだけで、日付欄（A列）の横（B列）に曜日欄（ex. 月 ）を表示できます。ちなみに書式を aaaa とすると（ex. 月曜日 ）の表示になります。また、1マスで日付も曜日も表示したいときは、先の例でセルA3の書式を yyyy/mm/dd aaa とすれば（ex. 2021年5月3日 は 2021/05/03 月 ）のように表示できます。
• 時刻の表示形式は h:mm ですが、これは 1900/1/1 0:00 に起算した24時間表示の時刻で表示されます。1ヶ月の勤務時間の計算などで、24時間を超える時間を時分で表示したい場合がありますが、その場合は時間表示形式にする必要があり、［セルの書式設定］の［表示形式］の［ユーザー定義］で [h]:mm とします（ex. 155時間15分 は 155:15 と表示されます）。ちなみに秒まで表示したい場合は [h]:mm:ss です。なお、時間もセル参照による加減が可能で（セルA1に8:10、セルB1に24:15となっている場合、セルC1の計算式が【 =A1+A2 】であれば、セルC1の値は 32:25 となります）、セル参照ではなく直接時間を計算式に入れる場合は、""で囲みます（【 ="10:00"-"3:30" 】の計算結果は 6:30　となります）が、結果が負になる場合はエラー表示（#####）となるので留意が必要です（※）。
• ※時刻形式の表示で負の場合にエラーとなることに対する逃げ道としては、時刻の計算そのものは小数点のある数値形式の表示で行い、計算結果を文字形式で表示する方法があります。次の表で、A列の時刻からB列の時刻を引いた差をC列に時刻として表示したい場合、計算自体はE列で小数点表示で行い、その結果をC列に文字形式で戻します。この場合のセルE1の計算式は【 =A1-B1 】で、セルC1の計算式は【 =IF(E1>=0,TEXT(E1,"[h]:mm"),TEXT(ABS(E1),"-[h]:mm")) 】です。なお、セルE1の数値が 0.4 となるのは日単位だからであり、時間単位にするなら 24 を掛ければ 9.5 となります。
  

〇 文字列操作関数（FIND、LEN、MID、LEFT、RIGHT、VALUE、TEXT、SUBSTITUTE）

• 文字列操作関数は、文字列を加工して別の文字列を作成したり、数値を文字列にあるいは文字列を数値に変換したりするときに使用する関数です。
• FIND関数の構文は【 =FIND(検索文字列,対象文字列) 】で、検索する文字列が対象文字列の何番目に入っているのかを返します。2バイト文字も1 つと数えます（2バイト文字を2と数えるにはFINDB関数を使用します）。
• LEN関数の構文は【 =LEN(対象文字列) 】で、対象文字列の文字数を返します。2バイト文字も1 つと数えます（2バイト文字を2と数えるにはLENB関数を使用します）。
• MID関数の構文は【 =MID(対象文字列,開始位置,開始位置からの抽出文字数) 】、LEFT関数の構文は【 =LEFT(対象文字列,左端からの抽出文字数) 】、RIGHT関数の構文は【 =RIGHT(対象文字列,右端からの抽出文字数) 】で、共に文字列の一部を抽出するために使います。MID関数は、対象文字列の開始位置から指定した文字数の文字を抽出します。左端から指定した文字数の文字を抽出する場合は、MID関数で開始位置を 1 とするか、LEFT関数を使用します。右端から指定した文字数の文字を抽出する場合は、RIGHT関数を使います。2バイト文字も1つと数えます（2バイト文字を2と数えるにはそれぞれMIDB関数、LEFTB関数、RIGHTB関数を使用します）。
• 例えば、セルA1にあるメールアドレスをユーザー名とドメイン名に分割するには、@マークの前後で区分けして、ユーザー名=LEFT(A1,FIND("@",A1)-1)、ドメイン名=MID(A1,FIND("@",A1)+1,LEN(A1)-FIND("@",A1))となります。
• VALUE関数の構文は【 =VALUE(文字列) 】で、数値を表す文字列を数値に変換します。何らかのシステムからＣＳＶ出力した場合などで数値が文字列形式となっていることがあり、文字列では計算する場合に不便なので、数値に変換することは少なくありません。
• TEXT関数の構文は【 =TEXT(数値,表示形式) 】で、数値を指定した表示形式の文字列に変換します。セルA1に 12000 という数値が入っていた場合、=TEXT(A1,"#,###")とすれば、12,000という形式の文字列で返されます。
• SUBSTITUTE関数の構文は【 =SUBSTITUTE(対象文字列, 指定の文字列, 置換後の文字列) 】で、対象文字列内の指定の文字列を他の文字列に置換します。［置換後の文字列］を空白文字列にすれば［指定の文字列］の除去をすることもできます。
• 例えばセルA1に1バイトの空白文字を含む文字列があり、1バイトの空白文字だけを取り除きたいときは【 =SUBSTITUTE(A1," ","") 】で除去できます。

〇 エラー処理関数（ISERROR、IFERROR）

• Excelの主なエラー値は7種類あり、エラー値が表示されたら数式等に何らかの原因があるので対処する必要があります。しかし、エラー内容によっては結果には影響は与えないもののエラー値が表示されたままになってしまう場合もあり、エラー処理関数を使うなどしてエラー値が表示されないようにすると見栄えが良くなります。
• 例えば、上記のSUMIFS関数の例で、セルF3の総利益率の計算式は【 =E3/D3 】ですが、データが入っていない行で計算式だけ入れておくと、［#DIV/0!］のエラー値が表示されます。これを避けるため、割り算を含む計算式では分母が0の場合を計算しないよう、普段から【 =IF(D3=0,"",E3/D3) 】のような計算式にしておくと良いでしょう。
• ISERROR関数の構文は【 =ISERROR(値) 】で、値がエラーの場合、TRUE を返します。値には、セル参照や数式などが入ります。
• 例えば、上記のVLOOKUP関数の例で、工事一覧表に入力した得意先コードが得意先マスターにない場合は［#N/A!］のエラー値が表示されます。これを避け、得意先マスターにない場合は空欄となるよう、【 =IF(ISERROR(VLOOKUP(B2,得意先マスター!$A:$C,2,FALSE))=TRUE,"",VLOOKUP(B2,得意先マスター!$A:$C,2,FALSE)) 】のような計算式にしておくと良いでしょう。
• IFERROR関数の構文は【 =IFERROR(値1,値2)】で、値1がエラーでない場合は値1を返し、値1がエラーの場合は値2を返します。値1や値2には、文字列のほかセル参照や数式も指定できます。
• 例えば、上記のIF関数とISERROR関数を組み合わせた計算式は、【 =IFERROR(VLOOKUP(B2,得意先マスター!$A:$C,2,FALSE),"") 】とIF関数を使わず簡単に記述できます。

〇 乱数関数（RAND、RANDBETWEEN）

• 実務でもパスワードを求められる機会は多いと思いますが、最近はパスワード解析ソフトも出回り、文字数を12～15文字位まで増やしたり、文字の種類を大文字・小文字・数字・記号と組み合わせないと、短時間で容易に解析されてしまうようです。複雑なパスワードを作成するには、フリーのパスワード生成アプリを使う手もありますが、パスワードだけに、エクセルの乱数関数を使って自分で作るのも良いかと思います。
• RAND関数の構文は【 =RAND() 】で、珍しく引数の無い関数です。ワークシートが再計算される都度、0～1未満の範囲で小数点以下の乱数を生成します。
• 例えば、次は、任意の文字の組合せと、任意の文字数でパスワードを作成する表です。黄色いセルは入力欄です。
  
• セルB2～B5は文字の種類で、それぞれアルファベットの大文字、同小文字、数字、記号があらかじめ入力されていますが、任意に文字を増減しても問題ありません（Oは0と間違いやすいので省くなど）。
• セルA2～A5は、どの文字をパスワードに使うかのパラメータで、1を入れるとその種類の文字を使います。その結果、パスワードに使われる文字候補がB6で、その計算式は、【 =IF($A$2=1,$B$2,"")&IF($A$3=1,$B$3,"")&IF($A$4=1,$B$4,"")&IF($A$5=1,$B$5,"") 】です（ここでは全ての文字種を使っていますので文字候補の総数は26+26+10+10=72となっています）。
• セルA9は、パスワードの文字数で、1～99の範囲で任意に設定します（ここでは20を指定）。
• セルD1は、パスワードの1文字目の値ですが、その計算式は、【 =MID($B$6,INT(RAND()*LEN($B$6))+1,1) 】です。乱数関数（RAND）で0～0.999999…の乱数を発生させ、それに使用する文字数（ここでは72）を乗じ、切捨関数（INT）で小数点以下を切り捨て、そこに1を足すことで、1～72の乱数を作り出し、文字列取出し関数（MID）を使ってB6の文字候補（72文字）の中からその乱数の位置の文字1文字を求めています。
• セルD2以降は、パスワードの2文字目以降の文字となり、D1の計算式を縦にセルD99までコピーします（それ以上コピーしても問題ありません）。全く同じ計算式ですが、乱数関数により表示される文字は異なり、なおかつ規則性もありません。
• セルB9は、求めるパスワードですが、その計算式は、【 =MID(CONCAT($D:$D),1,$A$9) 】です。セルD1～D99の文字を文字列結合関数（CONCAT）で結合し、文字列取出し関数（MID）で先頭からA9で指定した文字数（ここでは20）のパスワードとしています。なお、［F9］キーを押すと再計算し、乱数が変わりますので、パスワードも変化します。気に入ったパスワードが生成されるまで押し続けるのも良いでしょう。
• RANDBETWEEN関数の構文は【 =RANDBETWEEN(最小値, 最大値) 】で、ワークシートが再計算される都度、最小値～最大値の範囲で整数の乱数が生成されます。
• 上の表で、セルD1～D99の計算式を、【 =MID($B$6,RANDBETWEEN(1,LEN($B$6)),1) 】としても結構です。乱数関数（RANDBETWEEN）で1～使用する文字数（ここでは72）の整数の乱数を作り出し、文字列取出し関数（MID）を使ってB6の文字候補（72文字）の中からその乱数の位置の文字1文字を求めています。RAND関数を使うよりは分かりやすい計算式ですね。
• 乱数関数の他の使い方として、一定の集団を規則性なしに振り分ける際に使えます。
• 例えば、次は、研修時に社員を任意に班分けしたい場合に使う表と考えて下さい。黄色いセルは入力欄です。
  
• セルB1は、1つの班の人数で、1～社員の総数（ここでは12）の範囲で任意に設定します。
• セルA4～A15には社員の名前Ａ～Ｌの12名が入力されています。
• セルC4～C15は乱数で、その計算式は全て【 =RAND() 】です。（RANDBETWEEN関数は、整数しか生成せず重複する可能性があるので、ここでは使えません。）
• セルB4は、社員Ａの班番号（ここでは1～3）ですが、その計算式は、【 =ROUNDUP(RANK($C4,$C:$C,0)/$B$1,0) 】です。セルC4の乱数がC4～C15の乱数の中で何番目に大きいかランク付けし（ここでは大きい順に1～12）、求まったランクを班人数（ここでは4）で割ったものを概数関数（ROUNDUP）で整数に切り上げて班番号を求めています（ex.ランク1～4が1班になる）。セルB4の計算式は、縦にセルB15までオートフィルでコピーします。