Worksheet Function

ワークシート関数編

 「データや文字の入力は得意だけれど、関数はちょっと・・・」という方は少なくないと思います。確かに関数はある程度覚えるまで、面倒であったり頭を悩ませる事もあります。しかし、一度その特性を理解すると作業効率が驚くほどあがります。

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

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


関数に関する基礎知識

INDEX

ワークシート関数の構成
演算子
ワークシート関数の挿入
セル参照の形式
数式の貼り付け
数式の表示
最近使用した関数の表示
引数に関数を指定
エラー値
ワイルドカード
配列数式  


ワークシート関数の構成

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

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

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

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

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

 論理値:TRUE、FALSE

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

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

 配列:中カッコ「{}」で括り、データはコンマ「,」で区切る。行の区切りはセミコロン「;」を使用。
  例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関数等は引数が文字列だとその引数は計算されません。

◆配列数式の挿入

 数式を入力し「{}」を付けたしても配列数式にはなりません。配列数式にするためには、特殊な操作が必要になります。
 数式=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)} でも可。

Worksheet Function

Excel Tips for Teachers

Copyright (C) 2009- 坂江 保 All Rights Reserved.