特集「関数」
|
複数の比較演算式の結果を、総合して判断することができる関数です。すべての結果が「TRUE」の場合にだけ、「TRUE」と判断されます。1つでも「FALSE」が含まれていれば、「FALSE」となる、厳しい論理関数です。
AND関数の書式は次のようになります。
AND(論理式1, [論理式2], ...)
引数の「論理式」には、その結果がTRUE（真）もしくはFALSE（偽）となる条件式を指定します。
AND関数の引数「論理式」は、255個まで指定できます。
使用例
出勤していない日の計算は行わないようにしたい
IF関数式への条件の追加は、AND関数などを使って行います
空白のセルにルールを適用しない
“何もない”ことを表す "" と比較する式の追加で、空白セルを除外することができます
一つの条件付き書式に、AND条件を設定する
AND関数を使えば、指定した条件すべてに合致する場合にのみ、書式が適用される条件式を作れます
0の入力されているセルを強調する
空白セルは0扱いなので、それを除くための条件が必要です
|
|
|
セル範囲に含まれる値の平均値を求めることができます。
AVERAGE関数の書式は次のようになります。
AVERAGE(セル範囲1, [セル範囲2],...)
引数にはセル範囲を指定するのが一般的です。セル範囲に含まれる文字列や空白セルは無視されます。
条件に合致する値のみの平均を求めたい場合は、
AVERAGEIFやAVERAGEIFS（条件が複数の場合）を使います。
使用例
グループごとの平均も求めたい
全体の平均はAVERAGE関数を、
グループごとの平均はAVERAGEIF関数を使って求めることができます
各テストの平均点を求めたい
AVERAGE関数を使います
10行10列のセル範囲の平均値を求めたい」
AVERAGE関数の引数には、矩形のセル範囲を指定することもできます
|
|
|
指定したセル範囲の中から、指定した条件に合致するセルのみを対象にして、その平均値 (算術平均) を得られます。
AVERAGEIF関数は、次のような書式で使います。
AVERAGEIF(範囲,条件,平均対象範囲)
範囲：「条件」と照らし合わせるセル範囲を指定します。
条件：条件式を、">32"や"Windows"などのように、
半角の二重引用符 (") で囲んで指定します。
平均対象範囲：「条件」に合致した場合に、平均するセル範囲を指定します。「範囲」と同じセル範囲を平均する場合は、省略することができます。
AVERAGEIF関数は、旧バージョンのExcelには用意されていませんが、SUMIFとCOUNTIFの関数の組み合わせで、AVERAGEIF関数を実現できます。
使用例
偏差値を求めたい
偏差値は
10×（個人の得点―平均点）÷（標準偏差）＋50
という計算式で求められます
グループごとの平均も求めたい
全体の平均はAVERAGE関数を、
グループごとの平均はAVERAGEIF関数を使って求めることができます
男女別の平均身長を求めたい②
AVERAGEIF関数を使えば、条件に合致するデータだけの平均値を求められます
正もしくは負の数値だけを平均したい
AVERAGEIF関数を使えば、正の数値だけの平均や、負の数値だけの平均を求めることは、難しくありません
|
|
|
指定セルの書式などの情報を返します。
CELL関数は、次のような書式で使います。
CELL(検査の種類,調べたいセル)
検査の種類：調べたい情報を、次のようなキーワードで指定します。
たとえば、「"type"」を指定すると、セルに含まれるデータのタイプに対応する文字列定数が返されます。セルが空白の場合には「
"b"」が、文字列の場合は「 "l"」が、その他の値の場合は「"v"」が返されます。
調べたいセル：調べたいセルの番地を指定します。
使用例
日付をシートタブ名にしたい
シートタブを日にちにして、
それを日付として表示することはできます
|
|
|
1から順に並ぶ整数に応じて、任意の値を割り当てることができます。
CHOOSE関数は、次のような書式で使います。
CHOOSE（インデックス, 値 1, [値 2], ...)
インデックス： 1 ～ 254 の整数が表示されるセル番地を指定します。
値 1：インデックスが1のときに表示する値を指定します。
値 2：インデックスが2のときに表示する値を指定します。以降、想定されるインデックスに応じて、必要な数の値をカンマで区切りながら指定します。
使用例
対戦結果を○△●で表したい
対戦結果を論理計算し、
その値を使ったCHOOSE関数式にします
3段階評価をABC評価に変えたい
CHOOSE関数を使えば、整数値を順に別の値に置き換えることができます
日付データを使って第○四半期と表示させたい
CHOOSE関数を使えば、年度初めの月に合わせて、四半期の表示を行えます
5段階評価を点数に置き換えたい
対象が１から順に並ぶ整数なら、CHOOSE関数を使えます
|
|
|
引数として指定したセルの列番号を知ることができる関数です。アルファベットではなく、数値で列番号を取得します。
COLUMN関数の書式は、次のようになります。
COLUMN(セル番地)
引数のセル番地を省略すると、COLUMN関数が入力されているセルの行番号が返されます。
使用例
テーブル化されている表から表引きしたい
F5キーでテーブルおよび列の指定を簡単に行えます
表内の各列を交互に2色で塗りつぶす
あらかじめ表全体を塗りつぶしておけば、条件付き書式でISEVEN関数による偶数列の塗りつぶしを設定するだけで済みます
市松模様（チェック柄）に表を修飾する
行番号と列番号を足したもので、条件式（論理式）としての判定を行います
対戦表で不要な同一対戦枠を黒く塗りつぶす
行番号と列番号を比較して、同一である場合のみ黒く塗りつぶされるようにします
|
 |
数値を含むセルの個数がわかります。
COUNT関数は、次のような書式で使います。
COUNT(セル範囲)
セル範囲に含まれる空白セルや論理値、文字列、エラー値は個数に含まれません。
使用例
出勤日数を求めたい
COUNT関数を使うと、
数値の個数を求めることができます
条件に合致する値の個数を求めたい
COUNTやCOUNTIF、COUNTIFS関数を使い分けます
選択された回答の個数と比率を求めたい
COUNTIF関数で個数を求め、
それをCOUNT関数で求めた全体数で割ります
正答率を求めたい
COUNT関数なら、空セルを除いた、数値セルだけを数えられます
月別シートのデータの個数を集計したい
COUNT関数を使うと、複数シートのセル範囲に含まれる、数値データの個数を知ることができます
|
複数のセル範囲で、異なる条件を指定し、
そのすべての条件に一致したものの個数を求めることができます。
COUNTIFS関数は、次のような書式で使います。
COUNTIFS(条件範囲 1, 検索条件 1, [条件範囲 2, 検索条件 2]…)
条件範囲：任意のセル範囲を指定できます。
それぞれの「条件範囲」は隣接している必要はなく、
離れた場所にあっても、列数と行数が同じなら指定できます。
検索条件：対象となる「条件範囲」での検索条件を、セル参照や文字列、数値、数式などで指定します。
文字列や数式を指定する場合には、半角の二重引用符("") で囲む必要があります。
「条件範囲」と「検索条件」は対で指定します。最大127組を指定できます。
求められる個数は、各組の条件すべてに合致したものです。
Excel 2007で初めて搭載された関数なので、それ以前のExcelでは利用できません。
使用例
過去3か月の
顧客ごとの来店回数を求めたい
COUNTIFS関数を使って、
日付も条件に加えます
条件に合致する値の個数を求めたい
COUNTやCOUNTIF、COUNTIFS関数を使い分けます
クロス集計したい
COUNTIFS関数を使って、
複数の条件に合致する値の個数を求めます
直近3カ月の来店回数を知りたい
COUNTIFS関数を使って、
顧客番号と指定日以降の2つの条件で集計します
合計列の値の個数や総計を知りたい
COUNTIFSやSUMIFSの関数を使えば、
列名を条件として、
その列の値だけを計算対象にすることができます
年齢層ごとの来店数を求めたい
COUNTIFS関数を使えば、複数の条件に合致するデータの個数を求められます
指定範囲内の値の個数を調べたい
COUNTIFS関数を使って調べることができます
各列の値がすべて指定値以上の行数を求めたい
Excel 2007で新たに用意されたCOUNTIFS関数を使えば、複数の条件に合致する個数を求めることができます
|
|
|
指定した日付に対するシリアル値が得られます。
DATE関数は、次のような書式で使います。
DATE(年,月,日)
「年」には、基本的に4桁の西暦年を指定します。
「月」は1～12で指定します。
「日」は、基本的に1～31で指定します。その年月に存在しない日にちを指定した場合には、余分な日数が加算され、翌月の日付が指定されたとみなされます。たとえば、「DATE(2012,2,30)」だと、2012年3月1日が指定されたことになります。
使用例
分けて入力していた月と日を、日付データに直したい
DATE関数を使うと、日付データに直せます
指定年月日の曜日を表示したい
曜日は、年月日のデータから求めて表示できます
|
|
2つの日付間の日数や月数、年数を求めることのできる関数です。
かつての代表的な表計算ソフトとの互換性を保つために残されている関数なので、他の関数のように候補の一覧として表示されることはありませんが、使用には問題ありません。
DATEDIF関数の書式は、次のようになります。
DATEDIF(開始日,終了日,単位)
開始日：期間の開始日を指定します。
終了日：期間の終了日を指定します。
単位：求める値の単位を、次のように指定します。
| 単位 |
求められる値 |
| "Y" |
期間内の満年数 |
| "M" |
期間内の満月数 |
| "D" |
期間内の満日数 |
※終了日には、開始日より後の日付を指定します。逆の場合には、エラーとなります。
使用例
誕生日から、
年齢と直近の誕生日を求めたい
DATEDIF関数で年齢を、
EDATE関数で直近の誕生日を求めることができます
今日から終了日までの残り月数を求めたい
DATEDIF関数を使えば、期間内の満月数を求めることができます
DATEDIF関数式のエラーを解消したい
IFERROR関数を使えば、エラー値の代わりに、任意の文字列を表示させられます
日付データから日にちを数値で取り出せます。
DAY関数の書式は、次のようになります。
DAY(シリアル値)
シリアル値：日付データ、もしくは日付データの入力されているセルを指定します。
使用例
今日の日にちを強調したい
TODAYとDAYの２つの関数を組み合わせれば、
今日の日にちを求められます
EDATE関数を使うと、指定した月数だけ前または後の日付を得られます。
EDATE関数の書式は、次のようになります。
EDATE(開始日, 月)
開始日：基点となる日付、もしくはそれが入力してあるセルを指定します。
月：「開始日」からの月数を指定します。正数を指定すると「開始日」より後の日付を返し、負数を指定すると「開始日」より前の日付となります。
使用例
誕生日から、
年齢と直近の誕生日を求めたい
DATEDIF関数で年齢を、
EDATE関数で直近の誕生日を求めることができます
期日まで一か月を切ったら強調したい
TODAY関数を使って、今日と期日一か月前の日付を比べる条件付き書式にします
期日を過ぎたら、その行全体を強調したい
TODAY関数を使って、今日と期日の日付を比べる条件付き書式にします
EOMONTH関数を使うと、指定した月数だけ前または後の月の最終日を調べることができます。
満期日や支払日などの計算に便利です。
EOMONTH関数の書式は、次のようになります。
EOMONTH(開始日, 月)
開始日：基点となる日付、もしくはそれが入力してあるセルを指定します。
月：「開始日」からの月数を指定します。正数を指定すると「開始日」より後の日付を返し、負数を指定すると「開始日」より前の日付となります。
使用例
翌月の日にちを非表示にしたい
EOMONTH関数で末日を求め、
その日付を過ぎた日のみを条件付き書式で非表示にします
万年カレンダーにしたい
EOMONTHとWEEKDAYの2つの関数を使って、
その月の1日の曜日を求め、
万年カレンダーとして働くようにします
|
指定したセルの文字列を比較して、まったく同じである場合はTRUEを、そうでない場合はFALSEを返す関数です。文字列の照合に使います。
EXACT関数の書式は、次のようになります。
EXACT(文字列 1, 文字列 2)
「文字列 1」と「文字列 2」に、照合するセルや文字列を指定します。
使用例
|
|
|
FIND関数を使うと、指定された文字列を他の文字列から検索できます。得られる値は、指定文字列が最初に現れる位置です。
FIND関数の書式は次のようになります。
FIND(検索文字列, 対象, [開始位置])
検索文字列：検索する文字列を指定します。
対象：検索対象となる文字列を指定します。
開始位置：省略可能です。検索を開始する位置を指定します。対象の先頭文字から検索を開始するときは 1 を指定します。開始位置を省略すると、1
を指定したと見なされます。
※大文字と小文字を区別しないで検索する場合や、ワイルドカード文字を使用する場合は、SEARCH関数を使用します。
使用例
木曜日午後のスケジュール日付を強調したい
「午後」などの文字列が含まれるか否かはFIND関数で調べることができます
“仙台市”の含まれる住所セルと同じ行にある、氏名セルを強調したい
FIND関数を使って、条件付き書式を設定します
関東地方の一都六県に住む人を強調する
あらかじめ一都六県のセルに名前を付けておき、ISNUMBER関数＋FIND関数で各セルの検索を行います
|
|
FREQUENCY関数を使うと、あらかじめ指定しておいた区分値に応じたデータ個数を求めることができます。
配列数式として設定する必要があるので、FREQUENCY関数式の確定は、Ctrl＋Shift＋Enterで行います。
このFREQUENCY関数の書式は、次のようになります。
FREQUENCY(データ配列, 区間配列)
データ配列：対象となるデータの入力されたセル範囲を指定します。
区間配列： 区分値を入力したセル範囲を指定します。
使用例
階級ごとの度数を数式で求めたい
FREQUENCY関数を使えば、配列数式で簡単に度数を求められます
|
HLOOKUPは、指定範囲の先頭行を検索し、同じ列にある指定行のデータを抽出する関数です。
左端列を検索して、同じ行にある指定列のデータを抽出する場合には、VLOOKUP関数を使います。
標準では、検索対象となる先頭行のデータは昇順に並べられていなければなりません。
昇順に並んでいない先頭行を検索対象にする場合は、引数「検索の型」には「FALSE」を指定する必要があります。
HLOOKUP関数の書式は次のようになります。
HLOOKUP(検索値,範囲,行番号,検索の型)
検索値：検索する値を指定します。
範囲：検索と抽出の対象となるセル範囲を指定します。セル範囲の名前や、テーブル名（Excel 2010/2007の場合）を指定することもできます。
行番号：抽出するデータのある行を、「範囲」の上端から数えた行数で指定します。
行番号に 1 を指定した場合は、セル範囲の上端行の値が抽出対象となります。
検索の型：省略した場合は「TRUE」となり、「検索値」が見つからなくても、「検索値」未満でもっとも大きいものが該当値とみなされます。
「FALSE」を指定した場合は、検索値と完全に一致する値だけが検索され、見つからない場合はエラー値「#N/A」が返されます。
使用例
選択肢に応じて、表示する項目を変えたい
HLOOKUP関数を使って表引きする方法があります
型番を入力すると、小売価格が表示されるようにしたい
検索するべき型番が横（行）方向に並んでいる場合にはHLOOKUP関数を使います
|
|
|
HYPERLINK関数式は、次のような書式で設定します。
HYPERLINK(リンク先,別名)
「リンク先」には、メールアドレスやURLなどを指定します。
クリックするだけでメール送信できるようにしたい場合には、メールアドレスの頭に「mailto:」という文字列を付加します。
「別名」には、セルに表示される文字列などを指定します。
※ハイパーリンクが設定されたセルにマウスポインタを合わせて、しばらく左ボタンを押したままにすると、
マウスポインタが十字形に代わり、リンク先に移動することなく、そのセルを選択できます。
HYPERLINK関数式をコピーする場合には、この方法でセルを選択します。
使用例
名前をクリックして、新規メール作成したい
HYPERLINK関数を使えば、名前にメールアドレスをハイパーリンク設定できます
ハイパーリンクの設定をまとめて行いたい
HYPERLINK関数を使えば、数式でハイパーリンクの設定を行えます
入力済みの住所に該当する、地図を表示したい
HYPERLINK関数を使って、Web地図サービスへのリンクボタンを作る方法があります
名前にメールアドレスをハイパーリンクして、クリックするだけでメール送信できるようにしたい
HYPERLINK関数を使えば、ハイパーリンク化をまとめて行えます
|
  |
|
配列の要素の値を格納することができる関数です。
INDEX関数の書式は次のようになります。
INDEX(配列, 行番号, [列番号])
行番号または列番号を0に設定すると、全体の値の配列が返されます。
※INDEX関数に格納されている配列の値は、配列数式にすることで確認することができます。
使用例
すべての月で目標を達成した者だけを強調表示する
PRODUCTとINDEXの関数を組み合わせることで、シンプルなAND条件式にできます
すべての月で目標を達成した者だけにメッセージを表示する
PRODUCTとINDEXの関数を組み合わせることで、シンプルなAND条件式にできます
|
|
|
指定したセル番地の値を表示することができる関数です。
間接的にセルを指定できるので、指定したセル番地のセルが移動したり並べ替えられたりしても、変わらずそのセル番地の値を表示することができます。
INDIRECT関数の書式は、次のようになります。
INDIRECT(セル番地を表わす文字列, [参照形式])
「セル番地を表わす文字列」には、値を表示したいセル番地を指定します。
R1C1形式でセル番地を指定したいときには、「参照形式」として「FALSE」を指定します。
使用例
日別の勤務表から、
個人別の勤務表を作りたい
シートタブ名が日にちしてあれば、
それを使ったVLOOKUP参照が可能です
直前の選択内容に応じた選択肢を表示したい」
INDIRECT関数で、直前の選択肢を『名前』として使います
逆順の並べ替えを自動的に行いたい
INDIRECT関数を使って、逆順の並べ替えを行う方法もあります
別のシートに入力してあるデータを、隔行で表示しなおす
INDIRECT関数を使って、偶数行もしくは奇数行にのみデータが表示されるようにします
|
|
指定したセルが空白セルの場合にTRUE を返します。
ISBLANK関数の書式は次のようになります。
ISBLANK(セル)
空白セルかどうかを調べたいセルの番地を指定します。
IF関数式の結果などによる見た目の空白セルは、FALSEとなります。数式も入力されていない真の空白セルだけがTRUEとなります。
使用例
値が空欄でない項目を強調する
ISBLANK関数を使えば、セルが空欄であるか否かを調べることができます
空欄の含まれる行を強調する
複数のセルを同じ条件で評価する場合には、SUMPRODUCT関数が便利です
|
|
VLOOKUP関数式では、引数「検索値」として指定したセルが空の場合に、エラー値 #N/A
が表示されます。これを回避したいときに、ISERROR関数を用います。
ISERROR関数の書式は次のようになります。
ISERROR(テストの対象)
テストの対象：エラーが表示される可能性のある数式を指定します。
「テストの対象」がエラー値 (#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?、#NULL!)
となるとき、TRUEとなります。それ以外のときにはFALSEとなります。
使用例
エラー値 #N/A を表示したくない
IFERROR関数を使えば、#N/Aなどの数式エラーをトラップできます
|
|
|
指定した数式の結果などが数値になるときに、TRUEを返す関数です。数値以外のエラーなどは、FALSEと判断されます。
ISNUMBER関数の書式は、次のようになります。
ISNUMBER(テストの対象)
「テストの対象」には、数式やセル番地を指定します。
使用例
関東地方の一都六県に住む人を強調する
あらかじめ一都六県のセルに名前を付けておき、ISNUMBER関数＋FIND関数で各セルの検索を行います |
|
|
LARGEは、セル範囲から○番目に大きなデータを返す関数です。LARGE関数を使えば、2番目や3番目などの値を調べることができます。
LARGE関数の書式は次のようになります。
LARGE(範囲,順位)
範囲：セル範囲を指定します。
順位：大きい方から数えた順位を数値で指定します。
使用例
直近3回の来店日を知りたい
LARGE関数を使った配列数式で求められます
上位10人の得点を強調したい
LARGE関数による条件付き書式を設定すれば、「上位10項目」の値を強調できます
|
|
|
指定したセルに入力されている文字列の文字数を返します。
LEN関数の書式は次のようになります。
LEN(セル)
文字数を調べたい、セル番地を指定します。
スペースも文字として数えられます。
指定セルが空白セルの場合には、0が返されます。
使用例
都道府県と、それ以降の住所を分けたい
都道府県の文字数を調べて、それ以降の住所を取り出します
データの文字数を求めたい
LEN関数を使うと、指定したセルのデータの文字数がわかります
存在しない桁の数字を表示したくない
対象となる数値の桁数をあらかじめ確認するようにすれば、存在しない桁の数字を非表示にできます
最長の文字列データを求めたい
LEN関数で求めた文字数から、オートフィルタで最大値のものを絞り込めば、最長の文字列を持つ行だけが表示されます
0の入力されているセルを強調する
空白セルは0扱いなので、それを除くための条件が必要です
|
  |
|
MATCHは、指定範囲内で検索した項目の相対的な位置を返す関数です。
MATCH関数は、次のような書式で使います。
MATCH(検査値, 検査範囲, [照合の種類])
「検査値」には、照合する値を指定します。
「検査範囲」には、検索の対象となるセル範囲を指定します。
「照合の種類」を省略すると、検査値以下の最大の値が検索され、その相対位置が返されます。「-1」を指定した場合は、その逆に検査値以上の最小の値が検索されます。「0」にした場合は、検査値に一致する値のみが検索されることになります。
使用例
最大値の左側に記されている番号を知りたい
連番になっているなら、MATCH関数だけで該当番号がわかります
プランに応じた規定料金を求めたい
MATCH関数を利用すれば、プラン別の料金をVLOOKUP関数で求めることができます
A列の同一データ間で、B列で前後30日間に該当する日付を調べる
シリアル値で使われていない桁で、A列のデータを識別し、2つのデータをまとめたもので前後30日を判定します
|
|
MIDは、指定した位置から指定した文字数の文字を返す関数です。
このMID関数は、次のような書式で使います。
MID(文字列, 開始位置, 文字数)
「文字列」には、セルを指定できます。
「開始位置」には、取り出す先頭文字の位置を数値で指定します。 文字列の先頭文字の位置が 1 になります。
「文字数」には、取り出す文字数を指定します。「文字列」以上の文字数を指定すると、「開始位置」以降のすべての文字が返されます。
使用例
日付データに含まれる月の数値を、 1～12の範囲の整数で返す関数です。
このMONTH関数は、次のような書式で使います。
MONTH(日付データ)
「日付データ」には、日付データの入力されているセルを指定します。
使用例
仕入伝票と売上伝票を使って、月ごとで在庫管理したい①
それぞれの伝票に「月」列を追加すれば、それを条件にしたSUMIFS関数式で入出庫数を求めることができます
日付データを使って○月と表示させたい
MONTH関数を使う方法があります
対象となる数値を、指定した値の倍数になるように丸めます。
日時を表すシリアル値も対象にできます。
このMROUND関数は、次のような書式で使います。
MROUND(数値,倍数）
「数値」には、丸める対象の数値を指定します。日時の入力されたセルも指定できます。
「倍数」には、丸めて求める倍数の基準値を指定します。日付や時刻の場合には、""で囲んで指定します。
使用例
月の合計時間を30分単位で丸めたい
MROUND関数を使えば、指定した時間で丸めることができます
|
NETWORKDAYS関数を使うと、引数として指定した開始日から終了日までの期間に含まれる稼動日、すなわち平日の日数を求められます。祝祭日の日付データを用意しておけば、それも引数として指定することで、特定期間内の祝祭日ものぞいた稼動日数を求めることができます。
このNETWORKDAYS関数は、次のような書式で使用します。
NETWORKDAYS(開始日,終了日,祭日)
「開始日」で日付を直接指定する場合には、DATE 関数を使って、「DATE(2010,3,1)」のように記述します。「終了日」も同様です。
「祭日」には、祝祭日などの休日の日付データの入力されたセル範囲を指定します。
使用例
平日の日数を計算式で求めたい
NETWORKDAYS関数を使えば、土日や祭日をのぞく、平日の日数を求めることができます
|
  |
|
引数がTRUEのときにFALSEを、FALSEのときにTRUEを返します。
NOT関数の書式は、次のようになります。
NOT(論理式)
「論理式」として指定できるのは、論理式に限りません。結果が数値となる数式であれば、論理式の代わりに指定することができます。その場合のNOT関数の評価は、0であればTRUE、それ以外の数値の場合にはFALSEとなります。
使用例
値が空欄でない項目を強調する
ISBLANK関数を使えば、セルが空欄であるか否かを調べることができます
|
|
|
基準のセルまたはセル範囲から、
指定した行数と列数だけシフトした位置にあるセル値を参照できます。
OFFSET関数の書式は、次のようになります。
OFFSET(基準, 行数, 列数, [高さ], [幅])
「基準」として、基点となるセル（セル範囲）を指定します。
「行数」には、上下方向へシフトする距離を行数単位で指定します。
正の数で下方向へ、負の数で上方向へシフトします。
「列数」には、左右方向へシフトする距離を列数単位で指定します。
正の数で右方向へ、負の数で左方向へシフトします。
[高さ]と [幅]は、セル範囲を参照する場合に指定します（省略可）。
使用例
対戦結果を転記したい
OFFSET関数を使えば、
指定したセルの値を転記できます
|
|
|
複数の比較演算式の結果を、総合して判断することができる関数です。1つでも「TRUE」が含まれていれば、「TRUE」と判断されます。「FALSE」と判断されるのは、すべての結果が「FALSE」の場合に限られます。AND関数よりも条件の緩やかな論理関数といえます。
OR関数の書式は次のようになります。
OR(論理式1, [論理式2], ...)
引数の「論理式」には、その結果がTRUE（真）もしくはFALSE（偽）となる条件式を指定します。
OR関数の引数「論理式」は、255個まで指定できます。
使用例
月曜日と木曜日に該当するセルを強調したい
条件が複数あり、そのいずれかに該当するセルを強調したい場合は、条件付き書式の条件式にOR関数を使います
同じ値が連続するセル範囲を強調したい
上のセルとの比較、そして下のセルとの比較を行い、いずれかの条件式がTRUEの場合、同じ値が連続するセル範囲の1つだと判断できます
|
 |
|
セル範囲の中で指定セル値がどのあたりに位置するのかを、パーセンテージで示す関数です。
PERCENTRANK関数の書式は次のようになります。
PERCENTRANK(セル範囲,指定セル,[有効桁数])
「セル範囲」には、「指定セル」の含まれるセル範囲を指定します。
「有効桁数」を省略すると、小数点以下第三位までのパーセンテージが求められます。
使用例
先月の営業成績が上位30%以内に含まれる人を強調する
PERCENTRANK関数を使って、70%以上に該当する人を判定します
過去半年間の営業成績が上位30%以内に含まれる人を強調する
別シートで半年分の営業成績を合計し、条件付き書式でPERCENTRANK関数による判定を行います
|
|
セルに入力されている文字列から、付加されているふりがなを抽出することのできる関数です。
ふりがなが付加されている文字列の場合には、その文字列そのものが抽出されてしまうので、使い方には注意が必要です。
PHONETIC関数の書式は次のようになります。
PHONETIC(セル番地)
指定セルに入力されている文字列のふりがなが表示されます。
ふりがなの付加されている文字列の場合には、 文字列そのものが返されます。
使用例
名前のフリガナを入力したい」
PHONETIC関数を使います
ふりがなの付加されているセルのふりがなだけを、PHONETIC関数で表示したい
ふりがなの文字種を確認することで、ふりがななのか、文字列そのものなのかを判別することは可能です
ふりがなを他のセルに表示したい
PHONETIC関数を使うと、データに付加されているふりがなを別のセルに表示できます
|
 |
|
PRODUCTは、引数として指定したセルの値すべての積を計算する関数です。多数のセルの積を計算するときに便利です。
PRODUCT関数の書式は次のようになります。
PRODUCT(セル範囲1, [セル範囲2], ...)
引数のセル範囲は、255個まで指定できます。
ただし、空白セルや論理値、文字列は無視されます。
使用例
すべての月で目標を達成した者だけを強調表示する
PRODUCTとINDEXの関数を組み合わせることで、シンプルなAND条件式にできます
すべての月で目標を達成した者だけにメッセージを表示する
PRODUCTとINDEXの関数を組み合わせることで、シンプルなAND条件式にできます |
|
RAND関数を使うと、0以上で1より小さい実数の乱数を作ることができます。
このRAND関数には、引数はなく、次のような書式で記述します。
RAND()
使用例
0～9の数値を重複しないように並べたい
0～9に乱数を割り当て、
その乱数の大きな順に0～9を配置していく方法があります
|
|
|
RANDBETWEEN関数を使うと、指定された範囲内の整数の乱数を作ることができます。
このRANDBETWEEN関数は、次のような書式で記述します。
RANDBETWEEN(最小値,最大値)
最小値：作りたい乱数の最小値を整数で指定します
最大値：作りたい乱数の最大値を整数で指定します
使用例
0～9の数値を重複しないように並べたい
0～9に乱数を割り当て、
その乱数の大きな順に0～9を配置していく方法があります
|
|
|
指定された桁数で数値を切り捨てることができる関数です。
ROUNDDOWN関数の書式は、次のようになります。
ROUNDDOWN(セル番地,桁数)
指定した「セル番地」に表示されている数値が処理の対象となります。
「桁数」を0にすると、対象となる数値は整数に切り捨てられます。
使用例
消費税8％を計算したい
8％は「8%」を乗算することで求められますが、
小数点以下は自動的に四捨五入されるので、
切り捨てたいときにはROUNDDOWN関数を使う必要があります
数値をひと桁ずつ個別のセルに表示したい
ROUNDDOWNとMOD関数を組み合わせて使えば、任意の桁の数字を表示できます
整数部の数値を位別に表示したい
MOD関数とROWNDDOWN関数を組み合わせることで、目的の位の数字だけを取り出して表示できます
割り算の結果を、余りと一緒に表示させたい
ROUNDDOWN関数とMOD関数を使えば、除算の結果を、整数と余りに分けられます
数値をひと桁ずつ個別のセルに表示したい
ROUNDDOWNとMOD関数を組み合わせて使えば、任意の桁の数字を表示できます
|
|
|
SMALLは、セル範囲から○番目に小さなデータを返す関数です。SMALL関数を使えば、2番目や3番目などの値を調べることができます。
SMALL関数の書式は次のようになります。
SMALL(範囲,順位)
範囲：セル範囲を指定します。
順位：小さい方から数えた順位を数値で指定します。
使用例
条件付き書式を使って、上位または下位に入る値を強調する
Excel 2013/2010/2007では「上位/下位ルール」を使うことで、Excel
2003/2002ではSMALLやLARGEなどの関数式を使うことで、セル範囲から上位や下位に該当する値を強調できます |
|
|
標準偏差を求めるときに用いる関数です。引数を母集団の標本であるとみなして、母集団の標準偏差を求めることができます。標準偏差とは、統計的な対象となる値が、その平均値からどれだけ広い範囲に分布しているかを計量したものです。標準偏差からは、偏差値などをも求めることが可能です。
STDEVおよびSTDEV.P関数の書式は次のようになります。
STDEV(母集団のセル範囲)
STDEV.P(母集団のセル範囲)
引数には、母集団のセル範囲を指定します。
使用例
偏差値を求めたい
偏差値は
10×（個人の得点―平均点）÷（標準偏差）＋50
という計算式で求められます
標準偏差や偏差値を求めたい
標準偏差をSTDEV.P関数で求めてから、
それを使って偏差値を計算します
偏差値を求めたい
偏差値は、ｚ得点を10倍にして、50を足すことで、数値として扱いやすくしたものです
|
 |
|
SUBTOTAL関数を使うと、オートフィルタで絞り込まれている状態のデータの集計を行えます。
このSUBTOTAL関数は、次のような書式で使います。
SUBTOTAL(集計方法,範囲 1,範囲 2,...)
「集計方法」として指定する番号によって、SUBTOTAL関数の集計方法が変わります。
旧バージョンのExcelでは1 ～ 11しか指定できませんが、2007では101 ～ 111も指定することができます。101 ～
111の場合には、非表示の値を無視しての集計が可能です。
| 集計方法 |
番号 |
番号 (非表示の値を無視する場合) |
| AVERAGE |
1 |
101 |
| COUNT |
2 |
102 |
| COUNTA |
3 |
103 |
| MAX |
4 |
104 |
| MIN |
5 |
105 |
| PRODUCT |
6 |
106 |
| STDEV |
7 |
107 |
| STDEVP |
8 |
108 |
| SUM |
9 |
109 |
| VAR |
10 |
110 |
| VARP |
11 |
111 |
使用例
赤い数値を除いて、平均を求めたい
SUBTOTAL関数を使えば、絞り込まれている状態のセルだけを集計できます
非表示のデータを無視して集計したい
SUBTOTAL関数を使えば、非表示のデータを無視して集計できます
|
|
SUMIFSは、複数の条件を満たす値を合計する関数です。
SUMIFS関数は、次のような書式で使います。
SUMIFS(合計対象範囲,条件範囲1,条件1,[条件範囲2,条件2...])
「合計対象範囲」には、合計する値があるセル範囲を指定します。空の値やテキスト値は無視されます。
「条件範囲1」には、条件による評価の対象となる最初の範囲を指定します。
「条件1」には、その「条件範囲1」に適用する条件を指定します。
「条件範囲2,条件 2, ...」は、ほかにも条件がある場合に指定します。
最大 127 組の範囲/条件のペアを指定できます。
SUMIFS関数では、こうして指定した条件をすべて満たすものだけが合計の対象となります。
このSUMIFS関数を使って、日付と項目の2つを条件にすれば、
各項目の日付ごとの集計表を作ることができます。
※SUMIFS関数は、2003以前のExcelでは利用できません。
※SUMIFS関数の引数は、SUMIF関数と指定順序が異なります。
SUMIFS関数では合計の対象となるセル範囲を第1引数として指定します。
使用例
期間内の入庫と出庫の個数を集計したい
期間と品名の複数の条件で集計することになるので、
SUMIFS関数を使います
直近3回の来店日の売上金額も知りたい
直近3回の来店日がわかっていれば、
顧客番号との2つの条件を使ったSUMIFS関数で求めることができます
数式を使って、各月の売上を集計したい
SUMIFS関数を使って、
月と品名の2つの条件で集計します
合計列の値の個数や総計を知りたい
COUNTIFSやSUMIFSの関数を使えば、列名を条件として、
その列の値だけを計算対象にすることができます
仕入伝票と売上伝票を使って、月ごとで在庫管理したい①
それぞれの伝票に「月」列を追加すれば、
それを条件にしたSUMIFS関数式で入出庫数を求めることができます
同じシートに項目ごとの集計結果を表示したい
日付と項目の2つを条件とする、SUMIFS関数を使います
|
指定した時刻や時間を、シリアル値（小数）に変換します。
TIME関数の書式は、次のようになります。
TIME(時, 分, 秒)
「時」「分」「秒」は、それぞれ省略することも可能です。その場合には、0と指定したことになります。
使用例
シフト時刻より遅れた出社時刻を強調したい
TIME関数で時刻化したシフトを、
出社時刻と比較して、
条件付き書式で強調する方法があります
時刻表示の時間から、8時間を引きたい
TIME関数を使えば、任意の時分秒を表わすことができます
|
|
セルの表示形式で用いられる書式文字列を使って、必要なデータを表示できる関数です。
このTEXT関数の書式は、次のようになります。
TEXT(値, 表示形式)
値：元データの入力されているセル番地を指定します。
表示形式：セルの表示形式で用いる書式文字列を引用符（"）で囲んで指定します。
使用例
毎日の売上を曜日で集計したい
元表に「曜日」列を追加すれば、ピボットテーブルでそれを使って集計できます
本記事に対する質問や要望などは、気軽にお寄せください。個別の回答は行いませんが、適時この特集「関数」に反映させていきます。
 
|
