トップ　>　ワークシート関数

ここでは、学習者にとって役に立つ関数をピックアップし、説明や使用例、そして応用方法を紹介していきます。
※Excel2003/2007のヘルプを基に解説しています

ワークシート関数について

押さえたい関数15選

その他便利な関数

ワークシート関数について

ワークシート関数の構成

一般的なワークシート関数は「=」と「関数名」、そして「引数※」を「()」で括った構成になっています。引数が複数ある場合は「,（コンマ）」で区切ります。
=関数名(引数1,引数2,...)
※NOW()やTODAY()など引数を持たない関数もあります

引数

引数（ひきすう）は、関数を実行するために渡す値です。本来、引数の定義はもっと複雑ですが、ワークシート関数に於いてはこの位の理解で大丈夫です。

引数の型

引数には型があり、関数によって異なります。型は主に数値や文字列です。セル範囲参照を設定する場合もあります。
関数は、型にあった引数を設定しないとエラーになります。現時点で、型を全て理解する事は難しいかもしれません。しかし、関数を使用していると何となく分かってきます。触れてみて慣れましょう。

【型の種類】
●数値：「0」「623.45」「-51」など数値そのもの。

●文字列："ETT"、"校務分掌"など。数式内で文字列を使用する際はダブルクォーテーション「"」で括る。

●論理値：TRUE、FALSE

●エラー値：#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!、#VALUE!、####の８種類。

●セル参照：セル[A1]やセル範囲[A1]～[B10]など。セル範囲を参照の場合[A1:B10]のようにコロン「:」で区切る。

●配列：中カッコ「{}」で括り、データはコンマ「,」で区切る。行の区切りはセミコロン「;」を使用。
　例１：=SUM({2,3,4;3,6,9})　　この場合は(2+3)+(3+6)+(4+9)になる。
　例２：=SUM({2,3,4}*{3;6;9})　　※「*」は「×」の意
　　　　この場合は(2*3)+(2*6)+(2*9)+(3*3)+(3*6)+(3*9)+(4*3)+(4*6)+(4*9)になる。

演算子

数式・関数では演算子を使用します。演算子とは「＋」や「×」等の記号の事です。演算子には優先順位があり、順位の高い方から計算されます。
優先順位は「＋」と「×」だと「×」の方を先に計算する、といった約束事で、皆さんご存じの通りです。カッコ「()」がつく場合は、カッコで括られた方が優先されます。

優先順位は、関数を組み合わせる場合や、引数に演算子を使用する際、重要になります。その機能と優先順位を覚えることが、関数のエラーを減らすことに繋がります。

演算子は大きく分けて３つあります。加減乗除などの算術演算子、文字列を繋げる文字列演算子、そして、左右の論理式を比較する比較演算子です。それぞれの優先順位は次の通りです。

ワークシート関数の挿入

ワークシート関数を使用するには、セルに直接か、数式バーに「=SUM(A1:A3)」のような形で入力します。その他に「関数の挿入」ダイアログボックスが用意されています。ダイアログボックスは関数の挿入をサポートする機能です。慣れない間は、ダイアログボックスを使用すると良いでしょう。
「関数の挿入」ダイアログボックスの表示には、下図の赤丸部、又は赤矩形部をクリックします。

関数の挿入ダイアログボックス

使用する関数を選択し「OK」を押すと、次に「関数の引数」ダイアログボックスが表示されます。
そのダイアログボックスを用い引数の値を設定します。

例：ダイアログボックスを用いSUM関数を挿入

セル参照の形式

引数にセルの参照を設定する場合には、参照形式に注意が必要です。

参照形式とは、あるセルの数式をコピーし、他のセルへ貼り付けする際、その数式内のセルの参照元を、貼り付け先にあわせ移動させるか、又は固定するかを指定するものです。

参照形式は３つあります。

絶対参照：セルの参照元を固定します。数式をどこへ貼り付けてもセルの参照元は変わりません。
相対参照：貼り付け先のセルにあわせて、セルの参照元も移動します。
複合参照：「行」又は「列」の参照を固定し、もう一方は貼り付け先セルにあわせ移動します。



セルを参照した場合、相対参照が基本になります。絶対参照や「列」「行」何れかを固定する場合は、$（ドル記号）をつけます。又、数式バーをクリックし、アルファベットと数字の間にカーソルを移動させ[F4]を押すと、「相対参照」→「絶対参照」→「行を固定」→「列を固定」の順に参照が変化します。

数式の貼り付け

罫線や書式を除いた「数式のみ」を他のセルに貼り付ける際は、次の手順を踏みましょう。

１．基になるセルをコピー
２．数式を貼り付けたいセル上で右クリックをし「ショートカットメニュー」を表示
３．「形式を選択して貼り付け」を選択
４．ダイアログボックスが現れるので、その中の「数式」を選んで、「OK」をクリック

数式の表示

数式を確認したい場合は、数式の入力されているセルを選択します。すると、数式バーに数式が表示されます。数式が入力セルが少ない場合は、この方法で充分ですが、これだと複数のセルの数式を一度に確認は出来ません。

複数のセルの数式を一度に確認するには、「ワークシート分析モード」を使用します。この機能は、セルに数式を表示させます。

「ツール」→「ワークシート分析」→「ワークシート分析モード」（Excel2003）

※Excel2007では「数式」タブ→「数式の表示」

最近使用した関数の表示

最近使用した関数は１０個まで記録されます。その記録を利用すると、より簡単に関数を挿入できます。
記録された関数を表示するには２つの方法があります。

【１】「関数の挿入」ダイアログボックスの「関数の分類(C)」で「最近使用した関数」を選択する。

【２】セルに「=」を挿入すると「名前ボックス」が「関数ボックス」に変わる。その「▼」をクリックする。

Hint!数式を入力中に入力をキャンセルする場合は[ESC]キーを押します

引数に関数を指定

関数の引数には、数値や文字列、セルやセル範囲参照の他に関数も指定できます。引数に関数を指定することをネストといい、引数に指定した関数の結果が引数となり計算されます。ネストを用いることで、単一の関数では不可能な、複雑な計算が可能になります。

例：=IF(SUM(A1:A10)>=50,"○","×")
セル[A1]～[A10]の合計が50以上だったら「○」を表示、それ以外なら「×」を表示

ダイアログボックスを用いネストする場合

１．「関数の挿入」ダイアログボックスで最初の関数を選択。
２．「関数の引数」ダイアログボックス表示後、数式バー隣の「関数ボックス」の「▼」をクリック。
３．ネストする次の関数を選択する。

エラー値

関数の名前を間違えた場合や、引数の型を誤った場合などにはエラーが発生します。次にエラーの種類と原因、対処方法を紹介します。エラーの種類と意味を知ると、修正時間が大幅に軽減します。

#DIV/0!「数値÷0」という割り算が行われたときに発生

　対処方法：IS関数

#NAME?関数名や名前を定義したセル・セル範囲名を間違えた場合に発生

　対処方法：名前の修正

#N/A検索関数で該当するデータが見つからなかった場合に発生

　対処方法：ISNA関数

#REF!行・列を削除した結果、参照元が無くなったセルに発生

　対処方法：セル番地を確認・修正

#NUM!計算結果がExcelの処理できる数値範囲を超えた場合に発生

　対処方法：数式の見直し

#NULL!存在しないセルを参照した時に発生

　対処方法：セル番地を確認・修正

#VALUE!引数の型を間違えた時に発生

　対処方法：引数の型確認。セル参照の場合は参照元の確認

####セル幅が狭く計算結果を表示出来ない時に発生

　対処方法：セル幅を広げる。フォントの縮小

ワイルドカード

ワイルドカードとはデータ検索の際、結果に幅を持たせるために使用する記号です。関数ではSEARCH関数等で使用できます。尚、使用する記号(?/*/~)は全て半角になります。

「?」任意の１文字と一致する文字列を検索

使用例：「神?川」→「神奈川」「神田川」　　　「越?」→「越前」「越中」「越後」「越谷」

「*」（アスタリスク）任意の１文字又は複数文字と一致する文字列を検索

使用例：「*袋」→「池袋」「沼袋」「堪忍袋」

「~」（チルダ）「?」「*」を検索する際に使用

使用例：「~?」（「?」を探す）

配列数式

ワークシート関数には配列数式というテクニックがあります。少し難しくなりますので、ワークシート関数にある程度慣れた段階で取り組む事をお奨めします。

配列とは？

複数のデータを一つの集合データとして扱うものです。

配列数式を利用するメリットは？

作業用セルを必要とする数式の場合でも、配列を取り入れる事により数式を一つのセルにまとめられます。また、ひとつの同じ数式で複数のセルに結果を返せます。

下図は「配列を利用しないケース」と「配列を利用するケース」の例です。

配列なし

セル[F3]～[F9]を作業セルとして使用し、その結果をセル[F11]で合計する２ステップを経て最終結果を表示しています。

配列数式を利用

作業セルを使用せず、セル[F11]の数式のみでダイレクトに結果を表示しています。

見た目では分かり辛い配列数式{=SUM(IF(D3:D9="１級",E3:E9,""))}ですが、内部では
=SUM(IF(D3="１級",E3),IF(D4="１級",E4),IF(D5="１級",E5)...)※
のように計算されていると考えて下さい。

※IF関数の第３引数を省略すると、論理式がFALSEだった場合にFALSE（文字列）が返ります。SUM関数やAVERAGE関数等は引数が文字列だとその引数は計算されません。

配列数式の挿入

数式を入力し「{}」を付けたしても配列数式にはなりません。通常の数式を配列数式にするためには、数式挿入時に[Shift]+[Ctrl]を押しながら[Enter]を押します。
例えば、数式=SUM(IF(D3:D9="１級",E3:E9,""))を配列数式にする場合、セル（または数式バー）に=SUM(IF(D3:D9="１級",E3:E9,""))と入力した状態で（数式バー入力待機状態で）[Shift]+[Ctrl]を押しながら[Enter]を押すといった操作になります。

配列数式のパターン

上図ではSUM関数を使用した配列数式{=SUM(IF(D3:D9="１級",E3:E9,""))}を例に挙げましが、配列数式にはパターンがあります。その他の関数もそのパターンに当てはめる事で応用できます（配列数式に対応しない関数もあります）。

=関数(IF(データ範囲を含む条件,対応データ範囲,""))

このパターンが一般的です。但し、関数によって引数の数が違ったり、状況によってはIF関数の第２引数（TRUE時）・第３引数（FALSE時）の値を変える必要があります。

その他の配列数式例

次の例は、先の図を基に、平均点や最高点を算出する例です。

{=AVERAGE(IF(C3:C9="１級",D3:D9,""))}
１級者の平均点を算出 。{=AVERAGE(IF(C3:C9="１級",D3:D9))} でも可。

{=MAX(IF(C3:C9="１級",D3:D9,""))}
１級者の中で最高点を算出 。{=MAX(IF(C3:C9="１級",D3:D9))} でも可。
対関数：MIN

{=LARGE(IF(D3:D9="１級",E3:E9,""),3)}
１級者の中で三番目の点数を算出。 {=LARGE(IF(D3:D9="１級",E3:E9),3)} でも可。
対関数：SMALL

{=COUNT(IF(D3:D9="１級",E3:E9,""))}
１級者の人数をカウント。 {=COUNT(IF(D3:D9="１級",E3:E9))} でも可。
COUNTは引数リストに含まれる数値の個数を返す関数です。この数式の意味は「IF関数で
１級者の点数（数値）を返し、COUNT関数で点数（数値）の個数をカウントする」になります。

似た機能をもつ関数にCOUNTIFがあります。COUNTIF関数で表すと=COUNTIF(D3:D9,"１級")になります。

{=MATCH(120,IF(D3:D9="１級",E3:E9,""),0)}
１級者で120点の者は上から何番目か算出。
{=MATCH(120,IF(D3:D9="１級",E3:E9),0)} でも可。