Worksheet function

ワークシート関数Worksheet function

関数の習得は、自転車運転の習得に似ています。徒歩10分かかっていた道程が、自転車では2分ですむ、といった感覚に近いでしょう。慣れるまで多少時間を要しますが、慣れれば作業のスピードは飛躍的に増します。関数を敬遠している方は、これを機に覚えてみてはいかがでしょうか。

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

ワークシート関数詳細

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


ワークシート関数の構成

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

引数

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

引数の型

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

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

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

論理値:TRUE、FALSE

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

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

配列:中カッコ「{}」で括り、データはコンマ「,」で区切る。行の区切りはセミコロン「;」を使用。
 例1:=SUM({2,3,4;3,6,9})  この場合は(2+3)+(3+6)+(4+9)になる。
 例2:=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)になる。


演算子

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

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

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


ワークシート関数の挿入

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


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


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

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

セル参照の形式

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

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

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

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



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



数式の貼り付け

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

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



数式の表示

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

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

「ツール」→「ワークシート分析」→「ワークシート分析モード」(Excel2003)

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



最近使用した関数の表示

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

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

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

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



引数に関数を指定

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

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

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

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



エラー値

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

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

 対処方法:IS関数

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

 対処方法:名前の修正

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

 対処方法:ISNA関数

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

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

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

 対処方法:数式の見直し

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

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

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

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

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

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


ワイルドカード

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

?任意の1文字と一致する文字列を検索

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

*」(アスタリスク)任意の1文字又は複数文字と一致する文字列を検索

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

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

使用例:「~?」(「?」を探す)


配列数式

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

配列とは?

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

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

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

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

配列なし


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

配列数式を利用


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

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

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

配列数式の挿入

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

配列数式のパターン

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

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

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

その他の配列数式例

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

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

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

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

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

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

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

ページトップへ戻る
Copyright(C) 2009- 坂江保 All Rights Reserved.