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

ワークシート関数
Worksheet function

ワークシート関数Worksheet function   2009.02.06   更新:2023/12/23

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

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

書籍紹介本を執筆しました

2024年1月20日に販売!
「教師のExcel ~校務(個人業務+チーム業務)カイゼンのためのデジタルリテラシー~」
基本の学びなおしから校務をサポートするシステム作りまで(技術評論社)

教員みんなができる「チーム業務」のポイントを丁寧にわかりやすく解説しています。また「個人業務」を更に効率化するための学び直しと応用テクニックを解説しています。
この書籍の活用してもらうことで、先生方が児童・生徒に向き合う時間を、更に確保できることを願っています!
■ 詳細(技術評論社)   ■ 内容紹介:note   ■ 購入:amazon

押さえたい関数15選

  • SUM 合計を求める
  • AVERAGE 平均する
  • ROUND 小数点を操作する
  • COUNT 数値セルを数える
  • COUNTIF 条件一致セルを数える
  • LARGE / SMALL 指定順位の値を求める
  • LEN 文字数を求める
  • MID 文字列を指定場所から抽出する
  • FIND 文字列の検索する(1)
  • IF 条件によって分岐する
  • IS セルの状態を調べる
  • LOOKUP 配列から値を検索する
  • VLOOKUP 指定した列の値を返す
  • INDEX 範囲から値を抽出する
  • MATCH 検査値の位置を返す

その他便利な関数

  • PRODUCT 積を求める
  • SUMIF 条件が合えば合計する
  • RANK 順位を求める
  • COUNTA 空白セルを数える
  • FREQUENCY 頻度分布を行う
  • MAX / MIN 最大・最少値を求める
  • ASC / JIS 半角 / 全角に変換する
  • CONCATENATE 文字列を連結する
  • LEFT 文字列を左端を基準に抽出する
  • RIGHT 文字列を右端を基準に抽出する
  • SEARCH 文字列の検索する(2)
  • TRIM 余分なスペースを削除する
  • SUBSTITUTE 文字列を置換する
  • PHONETIC フリガナ情報を取得する
  • HLOOKUP 指定した行の値を返す

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


ワークシート関数の構成

一般的なワークシート関数は「=」と「関数名」、そして「引数※」を「()」で括った構成になっています。引数が複数ある場合は「,(コンマ)」で区切ります。
=関数名(引数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.