特集「関数」
行番号と列番号を指定して、数式で使えるセル番地に変えることができます。
ADDRESS関数の書式は、次のようになります。
ADDRESS(行番号, 列番号, [参照の型], [a1], [シート名])
「列番号」は、アルファベットではなく、数値で指定します。
「参照の型」は省略でき、その場合には絶対参照のセル番地になります。
「参照の型」を2にすると、行のみの絶対参照のセル番地になります。
3にすると、列のみの絶対参照になります。
4にすると、相対参照のセル番地になります。
「a1」は省略でき、その場合にはセル番地はA1スタイルになります。
FALSEを指定すると、R1C1スタイルになります。
「シート名」を指定すると、得られるセル番地には、そのシート名が付加されます。
使用例
結合セルを含む表からの集計結果を、
別表に反映させたい
MATCH関数で対象を検索し、
ADDRESS関数とINDIRECT関数の組み合わせで、
SUM関数による集計を行う方法があります
時間帯ごとの予約状況を参照しながら、
新たな予約を追加していきたい
該当する時間帯から最大値を取得することで、
予約の詰まった時間帯を条件付き書式で警告できます
|
複数の比較演算式の結果を、総合して判断することができる関数です。すべての結果が「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(検査の種類,調べたいセル)
検査の種類：調べたい情報を、次のようなキーワードで指定します。
|
検査の種類 |
戻り値 |
|
"address" |
対象範囲の左上隅にあるセルの参照を表す文字列。 |
|
"col" |
対象範囲の左上隅にあるセルの列番号。 |
|
"contents" |
対象範囲の左上隅にあるセルの値。 |
|
"filename" |
対象範囲を含むファイルのフル パス名 (文字列)。 対象範囲を含むワークシートが保存されていなかった場合は、空白文字列 ("")。 |
|
"protect" |
セルがロックされていない場合は 0、ロックされている場合は 1。 |
|
"row" |
対象範囲の左上隅にあるセルの行番号。 |
調べたいセル：調べたいセルの番地を指定します。
使用例
日付にしたブック名から、
日付を作って表示させたい
CELL関数で得たフルパス文字列から、
必要な年と月を取り出して、
DATE関数で日付にすることができます
タブ名をセルに表示したい
CELL関数を使えば、
タブ名を含む文字列を取得できます
日付をシートタブ名にしたい
シートタブを日にちにして、
それを日付として表示することはできます
|
|
指定した基準値の倍数で、切り上げられた数値を返します。
このCEILING関数は、次のような書式で使います。
CEILING(数値, 基準値)
数値：丸めの対象となる数値を指定します。
基準値：倍数の基準となる数値を指定します。
使用例
記録した時刻を、15分単位で丸めたい
CEILING関数を使うと、15分を基準値にして、その倍数で時刻を丸められます
|
引数として指定したセルの列番号を知ることができる関数です。アルファベットではなく、数値で列番号を取得します。
COLUMN関数の書式は、次のようになります。
COLUMN(セル番地)
引数のセル番地を省略すると、COLUMN関数が入力されているセルの行番号が返されます。
使用例
テーブル化されている表から表引きしたい
F5キーでテーブルおよび列の指定を簡単に行えます
表内の各列を交互に2色で塗りつぶす
あらかじめ表全体を塗りつぶしておけば、条件付き書式でISEVEN関数による偶数列の塗りつぶしを設定するだけで済みます
市松模様（チェック柄）に表を修飾する
行番号と列番号を足したもので、条件式（論理式）としての判定を行います
対戦表で不要な同一対戦枠を黒く塗りつぶす
行番号と列番号を比較して、同一である場合のみ黒く塗りつぶされるようにします
|
 |
数値を含むセルの個数がわかります。
COUNT関数は、次のような書式で使います。
COUNT(セル範囲)
セル範囲に含まれる空白セルや論理値、文字列、エラー値は個数に含まれません。
使用例
出勤日数を求めたい
COUNT関数を使うと、
数値の個数を求めることができます
条件に合致する値の個数を求めたい
COUNTやCOUNTIF、COUNTIFS関数を使い分けます
選択された回答の個数と比率を求めたい
COUNTIF関数で個数を求め、
それをCOUNT関数で求めた全体数で割ります
正答率を求めたい
COUNT関数なら、空セルを除いた、数値セルだけを数えられます
月別シートのデータの個数を集計したい
COUNT関数を使うと、複数シートのセル範囲に含まれる、数値データの個数を知ることができます
指定した範囲内の空白セルの数を調べられます。
空白セルとは、データが入力されていないセルのことを指します。
この関数を使うことで、特定の範囲内にどれだけの空白セルがあるかを簡単に知ることができます。
COUNTBLANK関数は、次のような書式で使います。
COUNTBLANK(range)
引数「range」に、空白セルの数をカウントする範囲を指定します。
使用例
空欄の含まれる行を強調したい
COUNTBLANKを使った条件付き書式にします
|
複数のセル範囲で、異なる条件を指定し、
そのすべての条件に一致したものの個数を求めることができます。
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関数を使えば、複数の条件に合致する個数を求めることができます
|
|
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関数を使うと、
その月の末日の日付がわかるので、
それを使って翌月の日付は非表示にします
翌月の日にちを非表示にしたい
EOMONTH関数で末日を求め、
その日付を過ぎた日のみを条件付き書式で非表示にします
万年カレンダーにしたい
EOMONTHとWEEKDAYの2つの関数を使って、
その月の1日の曜日を求め、
万年カレンダーとして働くようにします
|
指定したセルの文字列を比較して、まったく同じである場合はTRUEを、そうでない場合はFALSEを返す関数です。文字列の照合に使います。
EXACT関数の書式は、次のようになります。
EXACT(文字列 1, 文字列 2)
「文字列 1」と「文字列 2」に、照合するセルや文字列を指定します。
使用例
|
FREQUENCY関数を使うと、あらかじめ指定しておいた区分値に応じたデータ個数を求めることができます。
配列数式として設定する必要があるので、FREQUENCY関数式の確定は、Ctrl＋Shift＋Enterで行います。
このFREQUENCY関数の書式は、次のようになります。
FREQUENCY(データ配列, 区間配列)
データ配列：対象となるデータの入力されたセル範囲を指定します。
区間配列： 区分値を入力したセル範囲を指定します。
使用例
階級ごとの度数を数式で求めたい
FREQUENCY関数を使えば、配列数式で簡単に度数を求められます
|
HLOOKUPは、指定範囲の先頭行を検索し、同じ列にある指定行のデータを抽出する関数です。
左端列を検索して、同じ行にある指定列のデータを抽出する場合には、VLOOKUP関数を使います。
標準では、検索対象となる先頭行のデータは昇順に並べられていなければなりません。
昇順に並んでいない先頭行を検索対象にする場合は、引数「検索の型」には「FALSE」を指定する必要があります。
HLOOKUP関数の書式は次のようになります。
HLOOKUP(検索値,範囲,行番号,検索の型)
検索値：検索する値を指定します。
範囲：検索と抽出の対象となるセル範囲を指定します。セル範囲の名前や、テーブル名（Excel 2010/2007の場合）を指定することもできます。
行番号：抽出するデータのある行を、「範囲」の上端から数えた行数で指定します。
行番号に 1 を指定した場合は、セル範囲の上端行の値が抽出対象となります。
検索の型：省略した場合は「TRUE」となり、「検索値」が見つからなくても、「検索値」未満でもっとも大きいものが該当値とみなされます。
「FALSE」を指定した場合は、検索値と完全に一致する値だけが検索され、見つからない場合はエラー値「#N/A」が返されます。
使用例
選択肢に応じて、表示する項目を変えたい
HLOOKUP関数を使って表引きする方法があります
型番を入力すると、小売価格が表示されるようにしたい
検索するべき型番が横（行）方向に並んでいる場合にはHLOOKUP関数を使います
|
|
指定したセルが空白セルの場合に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となります。
使用例
複数回答のアンケートを集計したい
FIND関数を使った配列数式で集計することができます
エラー値 #N/A を表示したくない
IFERROR関数を使えば、#N/Aなどの数式エラーをトラップできます
|
|
指定した数式の結果などが数値になるときに、TRUEを返す関数です。数値以外のエラーなどは、FALSEと判断されます。
ISNUMBER関数の書式は、次のようになります。
ISNUMBER(テストの対象)
「テストの対象」には、数式やセル番地を指定します。
使用例
関東地方の一都六県に住む人を強調する
あらかじめ一都六県のセルに名前を付けておき、ISNUMBER関数＋FIND関数で各セルの検索を行います |
|
指定したセルに入力されている文字列の文字数を返します。
LEN関数の書式は次のようになります。
LEN(セル)
文字数を調べたい、セル番地を指定します。
スペースも文字として数えられます。
指定セルが空白セルの場合には、0が返されます。
使用例
都道府県と、それ以降の住所を分けたい
都道府県の文字数を調べて、それ以降の住所を取り出します
データの文字数を求めたい
LEN関数を使うと、指定したセルのデータの文字数がわかります
存在しない桁の数字を表示したくない
対象となる数値の桁数をあらかじめ確認するようにすれば、存在しない桁の数字を非表示にできます
最長の文字列データを求めたい
LEN関数で求めた文字数から、オートフィルタで最大値のものを絞り込めば、最長の文字列を持つ行だけが表示されます
0の入力されているセルを強調する
空白セルは0扱いなので、それを除くための条件が必要です
|
  |
MIDは、指定した位置から指定した文字数の文字を返す関数です。
このMID関数は、次のような書式で使います。
MID(文字列, 開始位置, 文字数)
「文字列」には、セルを指定できます。
「開始位置」には、取り出す先頭文字の位置を数値で指定します。 文字列の先頭文字の位置が 1 になります。
「文字数」には、取り出す文字数を指定します。「文字列」以上の文字数を指定すると、「開始位置」以降のすべての文字が返されます。
使用例
姓名を区切る空白を使って、
姓と名に分けて表示したい
FIND関数で調べた空白の位置を使って、
LEFT関数やMID関数で該当文字列を取り出せます
ぁ ぃ ぅ ぇ ぉ などの拗音を含む文字と、
そうでない文字を各セルに分けて表示したい
次の文字が拗音か否かを確認しながら、
文字列の分割を行う方法があります
日付データに含まれる月の数値を、 1～12の範囲の整数で返す関数です。
このMONTH関数は、次のような書式で使います。
MONTH(日付データ)
「日付データ」には、日付データの入力されているセルを指定します。
使用例
仕入伝票と売上伝票を使って、月ごとで在庫管理したい①
それぞれの伝票に「月」列を追加すれば、それを条件にしたSUMIFS関数式で入出庫数を求めることができます
日付データを使って○月と表示させたい
MONTH関数を使う方法があります
対象となる数値を、指定した値の倍数になるように丸めます。
日時を表すシリアル値も対象にできます。
このMROUND関数は、次のような書式で使います。
MROUND(数値,倍数）
「数値」には、丸める対象の数値を指定します。日時の入力されたセルも指定できます。
「倍数」には、丸めて求める倍数の基準値を指定します。日付や時刻の場合には、""で囲んで指定します。
使用例
月の合計時間を30分単位で丸めたい
MROUND関数を使えば、指定した時間で丸めることができます
|
NETWORKDAYS関数を使うと、引数として指定した開始日から終了日までの期間に含まれる稼動日、すなわち平日の日数を求められます。祝祭日の日付データを用意しておけば、それも引数として指定することで、特定期間内の祝祭日ものぞいた稼動日数を求めることができます。
このNETWORKDAYS関数は、次のような書式で使用します。
NETWORKDAYS(開始日,終了日,祭日)
「開始日」で日付を直接指定する場合には、
DATE関数を使って、「DATE(2010,3,1)」のように記述します。
「終了日」も同様です。
"20130/3/1"のような形式で指定することもできます。
「祭日」には、祝祭日などの休日の日付データの入力されたセル範囲を指定します。
使用例
週休二日制での所定の出勤日数や出勤時間を求めたい
NETWORKDAYS関数を使えば、
土日や祝日を除いた、
指定期間内の所定の出勤日数や出勤時間を求められます
平日の日数を計算式で求めたい
NETWORKDAYS関数を使えば、土日や祭日をのぞく、平日の日数を求めることができます
|
  |
NETWORKDAYS関数と同様に、引数として指定した開始日から終了日までの期間に含まれる稼動日を数えることができます。
週末の曜日と日数を示すパラメータがあるので、土日ではない、任意の曜日を定休として指定することもできます。
このNETWORKDAYS.INTL関数は、次のような書式で使用します。
NETWORKDAYS.INTL(開始日, 終了日, [週末], [休日])
「開始日」で日付を直接指定する場合には、
DATE関数を使って、「DATE(2010,3,1)」のように記述します。
「終了日」も同様です。
"20130/3/1"のような形式で指定することもできます。
「週末」には、任意の定休の曜日を、週末を示す週末番号、または文字列で指定できます。
文字列で指定する場合は、月曜日から順に、稼働日を0、定休日を1で表します。
たとえば、火曜日と水曜日を定休にしたいときには、"0110000"のように指定します。
「休日」には、祝日などの休日の日付データの入力されたセル範囲を指定します。
使用例
指定期間の曜日ごとの数を知りたい
NETWORKDAYS.INTL関数の引数「週末」を、
0（稼働日）と1（非稼働日）の文字列で指定することで、
特定の曜日の数を調べることができます
日祝定休での、所定の出勤時間を求めたい
NETWORKDAYS.INTL関数を使えば、
特定の曜日の稼働日数を求られます
日祝定休での、所定の出勤日数を求めたい
特定の曜日や祝日を定休と勤務制では、
NETWORKDAYS.INTL関数を使って、
指定期間内の所定の出勤日数を求めます
|
引数がTRUEのときにFALSEを、FALSEのときにTRUEを返します。
NOT関数の書式は、次のようになります。
NOT(論理式)
「論理式」として指定できるのは、論理式に限りません。結果が数値となる数式であれば、論理式の代わりに指定することができます。その場合のNOT関数の評価は、0であればTRUE、それ以外の数値の場合にはFALSEとなります。
使用例
複数回答のアンケートを集計したい
FIND関数を使った配列数式で集計することができます
値が空欄でない項目を強調する
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関数による判定を行います
|
|
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関数を組み合わせて使えば、任意の桁の数字を表示できます
|
|
SEARCH関数は、対象となる文字列から、指定文字（列）を検索し、その文字列が見つかった場合にその位置を数値で返します。
SEARCH関数の書式は、次のようになります。
SEARCH(検索文字列,対象,[開始位置])
検索文字列：必ず指定します。 検索する文字列を指定します。
対象：必ず指定します。 対象に、”検索文字列”が含まれていない場合にはエラーとなります。
開始位置：省略可能です。 検索を開始する位置を指定します。
※SEARCH関数は、大文字と小文字を区別しません。 大文字と小文字を区別する検索を行う場合は、FIND関数を使います。
※検索文字列 には、半角の疑問符(?) または半角のアスタリスク (*) をワイルドカード文字として使用できます。 ワイルドカード文字の疑問符は任意の 1
文字を表し、アスタリスクは 1 文字以上の任意の文字列を表します。
ワイルドカード文字ではなく、通常の文字として疑問符やアスタリスクを検索する場合は、その文字の前に半角のチルダ (~) を付けます。
※検索文字列 が見つからないときは、エラー値 #VALUE! が返されます。
※開始位置 を省略すると、1 を指定したと見なされます。
※開始位置 に 0 (ゼロ) 以下の整数を指定した場合、または開始位置が対象の文字数よりも大きい場合は、エラー値 #VALUE! が返されます。
使用例
|
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を足すことで、数値として扱いやすくしたものです
|
特定の文字列を、指定した文字列に置き換えることができます。
このSUBSTITUTE関数は、次のような書式で使います。
SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])
文字列：検索文字列や置換文字列を含む文字列全体を指定します。セル参照や数式での指定も可能です。
検索文字列：置き換える対象の文字列を指定します。
置換文字列：検索文字列と置き換える文字列を指定します。空の文字列を指定して、検索文字列を文字列全体から取り除くこともできます。
置換対象：省略可能です。何番目の検索文字列を置換するのかを指定できます。指定しない場合は、検索文字列中のすべての文字列が置換文字列に置き換えられます。
|
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関数で回数を集計します
赤い数値を除いて、平均を求めたい
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ヶ月と顧客という複数の条件があるので、
SUMIFS関数を使います
期間内の入庫と出庫の個数を集計したい
期間と品名の複数の条件で集計することになるので、
SUMIFS関数を使います
直近3回の来店日の売上金額も知りたい
直近3回の来店日がわかっていれば、
顧客番号との2つの条件を使ったSUMIFS関数で求めることができます
数式を使って、各月の売上を集計したい
SUMIFS関数を使って、
月と品名の2つの条件で集計します
合計列の値の個数や総計を知りたい
COUNTIFSやSUMIFSの関数を使えば、列名を条件として、
その列の値だけを計算対象にすることができます
仕入伝票と売上伝票を使って、月ごとで在庫管理したい①
それぞれの伝票に「月」列を追加すれば、
それを条件にしたSUMIFS関数式で入出庫数を求めることができます
同じシートに項目ごとの集計結果を表示したい
日付と項目の2つを条件とする、SUMIFS関数を使います
正の平方根を返します。
このSQRT関数の書式は、次のようになります。
SQRT(数値)
数値：必ず指定します。 平方根を求める数値を指定します。
※数値に負の数を指定すると、エラー値 #NUM! が返されます。
使用例
加重値の標準偏差を求めたい
SQRT関数を使います
セルの表示形式で用いられる書式文字列を使って、必要なデータを表示できる関数です。
このTEXT関数の書式は、次のようになります。
TEXT(値, 表示形式)
値：元データの入力されているセル番地を指定します。
表示形式：セルの表示形式で用いる書式文字列を引用符（"）で囲んで指定します。
使用例
毎日の売上を曜日で集計したい
元表に「曜日」列を追加すれば、ピボットテーブルでそれを使って集計できます
|
WORKDAY.INTL関数は、指定した開始日から指定した営業日数だけ進んだ日付を計算します。
週末や休日を考慮して、次の営業日を算出することができます。特に、カスタマイズ可能な週末を指定できる点が特徴です。
WORKDAY.INTL関数の書式は次のようになります。
WORKDAY.INTL(start_date, days, [weekend], [holidays])
start_date： 開始日を指定します。この日付から計算が始まります。
days： 営業日数を指定します。正の値であれば未来の日付を、負の値であれば過去の日付を返します。
weekend： 週末の日を指定できます。
たとえば、"0000011"なら土曜日と日曜日が週末、"0000001"なら日曜日のみ週末、"0000010"なら土曜日のみ週末、"1111110"なら月曜日のみ営業日、のように、7桁のバイナリコード（1が週末、0が営業日）で指定します。
holidays： 祝日のセル範囲を指定できます。指定した日が営業日から除外されるようになります。
使用例
平日の日付をまとめて入力したい
WORKDAY.INTL関数が便利です
|
本記事に対する質問や要望などは、気軽にお寄せください。個別の回答は行いませんが、適時この特集「関数」に反映させていきます。
 
| 
