関数
上へ エクセル実践塾2016 条件式の考え方 条件付き書式 関数 エクセル実践塾2013 エクセル実践塾2010 質問の投函 塾長の部屋

 

特集「関数」

 

特に要望の多い「関数」 の使用例を、よく利用される関数ごとにまとめました。実際に関数を使用する際の参考にしてください。

 ABS | AND | AVERAGE | AVERAGEIF | CELL |  CHOOSE |  CODE | COLUMN | COUNT | COUNTIF | COUNTIFS  | DATE | DATEDIF | EDATE | EOMONTH | EXACT |  FIND | FREQUENCY | HLOOKUP | HYPERLINK | IF  | IFERROR | INDEX | INDIRECT | ISBLANK | ISERROR  | ISEVEN | ISNUMBER | ISODD | LARGE | LEFT | LEN |  MATCH | MAX | MID | MIN | MOD |  MONTH |  MROUND |  NETWORKDAYS  | NOT | OR |  PERCENTRANK |  PHONETIC | PRODUCT | RANK | ROUNDDOWN | ROW |  SMALL |  STDEV | SUBTOTAL | SUM | SUMIF | SUMIFS  | SUMPRODUCT | TEXT |  TIME | TODAY | VLOOKUP | WEEKDAY 

 

本記事に対する質問や要望などは、気軽にどうぞ。

個別の回答は行いませんが、適時この 特集「関数」に反映させます。

 

 

 

ABS関数

指定した数値から符号(+−)を除いた、絶対値を返す関数です。

ABS関数の書式は、次のようになります。

ABS(数値)

「数値」には、絶対値を求める実数を指定します。

使用例

特定の日を含む、前後3日間を強調する
比較する日付との差を絶対値で求めて、それがシリアル値3以内となるものを、条件付き書式の条件式とします

前後1時間で重複するスケジュールをチェックする
すべての日付+時刻と減算を行い、その差が1時間以内になるものを数えます

A列の同一データ間で、B列で前後30日間に該当する日付を調べる
シリアル値で使われていない桁で、A列のデータを識別し、2つのデータをまとめたもので前後30日を判定します

 

 

 

AND関数

複数の比較演算式の結果を、総合して判断することができる関数です。すべての結果が「TRUE」の場合にだけ、「TRUE」と判断されます。1つでも「FALSE」が含まれていれば、「FALSE」となる、厳しい論理関数です。

AND関数の書式は次のようになります。

AND(論理式1, [論理式2], ...)

引数の「論理式」には、その結果がTRUE(真)もしくはFALSE(偽)となる条件式を指定します。

AND関数の引数「論理式」は、255個まで指定できます。

使用例

空白のセルにルールを適用しない
“何もない”ことを表す "" と比較する式の追加で、空白セルを除外することができます

一つの条件付き書式に、AND条件を設定する
AND関数を使えば、指定した条件すべてに合致する場合にのみ、書式が適用される条件式を作れます

0の入力されているセルを強調する
空白セルは0扱いなので、それを除くための条件が必要です

 

 

 

AVERAGE関数

セル範囲に含まれる値の平均値を求めることができます。

AVERAGE関数の書式は次のようになります。

AVERAGE(セル範囲1, [セル範囲2],...)

引数にはセル範囲を指定するのが一般的です。セル範囲に含まれる文字列や空白セルは無視されます。

条件に合致する値のみの平均を求めたい場合は、AVERAGEIFやAVERAGEIFS(条件が複数の場合)を使います。

使用例

10行10列のセル範囲の平均値を求めたい」
AVERAGE関数の引数には、矩形のセル範囲を指定することもできます

 

 

 

AVERAGEIF関数

指定したセル範囲の中から、指定した条件に合致するセルのみを対象にして、その平均値 (算術平均) を得られます。

AVERAGEIF関数は、次のような書式で使います。

AVERAGEIF(範囲,条件,平均対象範囲)

範囲:「条件」と照らし合わせるセル範囲を指定します。

条件:条件式を、">32"や"Windows"などのように、半角の二重引用符 (") で囲んで指定します。

平均対象範囲:「条件」に合致した場合に、平均するセル範囲を指定します。「範囲」と同じセル範囲を平均する場合は、省略することができます。

AVERAGEIF関数は、旧バージョンのExcelには用意されていませんが、SUMIFとCOUNTIFの関数の組み合わせで、AVERAGEIF関数を実現できます。

使用例

男女別の平均身長を求めたいA
AVERAGEIF関数を使えば、条件に合致するデータだけの平均値を求められます

正もしくは負の数値だけを平均したい
AVERAGEIF関数を使えば、正の数値だけの平均や、負の数値だけの平均を求めることは、難しくありません

 

デル株式会社

 

CELL関数

指定セルの書式などの情報を返します。

CELL関数は、次のような書式で使います。

CELL(検査の種類,調べたいセル)

検査の種類:調べたい情報を、次のようなキーワードで指定します。
たとえば、「"type"」を指定すると、セルに含まれるデータのタイプに対応する文字列定数が返されます。セルが空白の場合には「 "b"」が、文字列の場合は「 "l"」が、その他の値の場合は「"v"」が返されます。

調べたいセル:調べたいセルの番地を指定します。

使用例

 

デル株式会社

デル株式会社

 

CHOOSE関数

1から順に並ぶ整数に応じて、任意の値を割り当てることができます。

CHOOSE関数は、次のような書式で使います。

CHOOSE(インデックス, 値 1, [値 2], ...)

インデックス: 1 〜 254 の整数が表示されるセル番地を指定します。

値 1:インデックスが1のときに表示する値を指定します。

値 2:インデックスが2のときに表示する値を指定します。以降、想定されるインデックスに応じて、必要な数の値をカンマで区切りながら指定します。

使用例

日付データを使って第○四半期と表示させたい
CHOOSE関数を使えば、年度初めの月に合わせて、四半期の表示を行えます

5段階評価を点数に置き換えたい
対象が1から順に並ぶ整数なら、CHOOSE関数を使えます

 

 

 

CODE関数

文字列の先頭文字のASCII/JISコードを返す関数です。

CODE関数は、次のような書式で使います。

CODE(文字列)

「文字列」には、それが入力されているセルを指定できます。

表示されるのは、対象となる文字列の先頭1文字目の文字コードです。

使用例

ふりがなの付加されているセルのふりがなだけを、PHONETIC関数で表示したい
ふりがなの文字種を確認することで、ふりがななのか、文字列そのものなのかを判別することは可能です

 

 

 

COLUMN関数

引数として指定したセルの列番号を知ることができる関数です。アルファベットではなく、数値で列番号を取得します。

COLUMN関数の書式は、次のようになります。

COLUMN(セル番地)

引数のセル番地を省略すると、COLUMN関数が入力されているセルの行番号が返されます。

使用例

表内の各列を交互に2色で塗りつぶす
あらかじめ表全体を塗りつぶしておけば、条件付き書式でISEVEN関数による偶数列の塗りつぶしを設定するだけで済みます

市松模様(チェック柄)に表を修飾する
行番号と列番号を足したもので、条件式(論理式)としての判定を行います

対戦表で不要な同一対戦枠を黒く塗りつぶす
行番号と列番号を比較して、同一である場合のみ黒く塗りつぶされるようにします

 

DMM.com DVD通販、レンタルなどの総合サイト

 

COUNT関数

数値を含むセルの個数がわかります。

COUNT関数は、次のような書式で使います。

COUNT(セル範囲)

セル範囲に含まれる空白セルや論理値、文字列、エラー値は個数に含まれません。

使用例

月別シートのデータの個数を集計したい
COUNT関数を使うと、複数シートのセル範囲に含まれる、数値データの個数を知ることができます

 

COUNTIF関数

セル範囲の中から、単一の検索条件に一致するセルの個数を返します。

COUNTIF関数は、次のような書式で使います。

COUNTIF(範囲, 検索条件)

範囲:対象となるセル範囲を指定します。あらかじめ適当な名前を付けておけば、その名前で指定することもできます。

検索条件:文字列や数値、またはそれらが入力されているセル番地を指定します。条件式を設定することもできます。文字列や数値、条件式は、半角の二重引用符 (") で囲んで指定します。

使用例

リストから選択した地方の人を強調したい
地方名を選択できるセルを作っておき、それを使った条件付き書式にします

関東在住の人を強調したい
関東一都六県のリストと照合することによって、条件付き書式で強調できます

祝日名や振替休日を、日付の隣に表示したい
祝日名はVLOOKUP関数で、振替休日はCOUNTIF関数で照合します

祝日と振替休日の日付セルを塗りつぶしたい
祝日と振替休日の日付一覧を用意すれば、それとの照合で条件付き書式による塗りつぶしを行えます

年齢層ごとの来店数を求めたい
「年齢層」を検索条件とする、COUNTIF関数式で求められます

重複値を無視して、伝票番号の個数が知りたい
COUNTIF関数を使った配列数式で求めることができます

配列数式を使わないで、ユニークな伝票番号の個数を求めたい
COUNTIF関数で重複数をあらかじめ求めておけば、通常のCOUNTIF関数式でユニーク値の個数を求めることができます

都道府県ごとの人数を知りたい
COUNTIF関数を使います

指定データの個数を調べたい
COUNTIF関数を使って調べることができます

指定値未満の値の個数を調べたい
COUNTIF関数を使って調べることができます

OR条件で指定データの個数を調べたい
COUNTIF関数を組み合わせます

複数の検索データに合致するセル数を調べたい
配列数式を使えば、COUNTIF関数で調べられます

5件以上の予約が入っている日を強調したい
COUNTIF関数を使えば、データの重複個数がわかります

祝日や振替休日の日付の色を変えたい
祝日や振替休日のリストと照らし合わせる条件付き書式で、日付の色を変えられます

祝祭日を黄色で塗りつぶす
祝祭日の年月日を別表として用意し、COUNTIF関数で日付との照合を行い、それを塗りつぶしの条件とします

休業日を赤色で塗りつぶす
休業日の年月日を別表として用意し、COUNTIF関数で日付との照合を行い、それを塗りつぶしの条件とします

別シートの一覧に含まれる商品名が入力されたときに、そのセルを任意の色で塗りつぶして強調したい
COUNTIF関数を使った条件式で、条件付き書式を設定します

特定のデータが入力されているセルの数を知りたい
COUNTIF関数を使えば、指定データの入力されているセルの数がわかります

データの入力されていないセルの数を知りたい
COUNTIF関数で、「検索条件」として「""」を指定すれば、データの入力されていないセルの数がわかります

特定の値以上のセルの個数を常に確認したい
COUNTIF関数を使えば、条件に合致するセルの個数を表示できます

特定の範囲にあるセルの個数を常に確認したい
COUNTIF関数式を組み合わせる方法があります

月〜金曜日の売上の平均を求めたい
WEEKDAY関数による結果を条件として、SUMIF関数での集計結果を、COUNTIF関数で調べた個数で割れば、曜日を限定して平均を求めることができます

男女別で回答数を求めたい
COUNTIF関数を使えば、セル範囲に含まれるデータの数を調べることができます

評価点が8以上の回答者数を求めたい
COUNTIF関数を使えば、指定した条件に合致するセルの数を調べることができます

30代と40代の回答者の数を求めたい
COUNTIF関数式を2つ使えば、2つの条件のいずれかに合致するセルの数を調べることができます(OR条件)

4以上8未満の評価点の入っているセルの個数を求めたい
COUNTIF関数式2つを「-」で結ぶことで、2つの条件の両方に合致するセルの数を調べることができます(AND条件)

 

 

 

COUNTIFS関数

複数のセル範囲で、異なる条件を指定し、そのすべての条件に一致したものの個数を求めることができます。

Excel 2007で初めて搭載された関数なので、それ以前のExcelでは利用できません。

COUNTIFS関数は、次のような書式で使います。

COUNTIFS(条件範囲 1, 検索条件 1, [条件範囲 2, 検索条件 2]…)

条件範囲:任意のセル範囲を指定できます。それぞれの「条件範囲」は隣接している必要はなく、離れた場所にあっても、列数と行数が同じなら指定できます。

検索条件:対象となる「条件範囲」での検索条件を、セル参照や文字列、数値、数式などで指定します。文字列や数式を指定する場合には、半角の二重引用符 (") で囲む必要があります。

「条件範囲」と「検索条件」は対で指定します。最大127組を指定できます。求められる個数は、各組の条件すべてに合致したものです。

使用例

年齢層ごとの来店数を求めたい
COUNTIFS関数を使えば、複数の条件に合致するデータの個数を求められます

指定範囲内の値の個数を調べたい
COUNTIFS関数を使って調べることができます

各列の値がすべて指定値以上の行数を求めたい
Excel 2007で新たに用意されたCOUNTIFS関数を使えば、複数の条件に合致する個数を求めることができます

 

 

 

DATE関数

指定した日付に対するシリアル値が得られます。

DATE関数は、次のような書式で使います。

DATE(年,月,日)

「年」には、基本的に4桁の西暦年を指定します。

「月」は1〜12で指定します。

「日」は、基本的に1〜31で指定します。その年月に存在しない日にちを指定した場合には、余分な日数が加算され、翌月の日付が指定されたとみなされます。たとえば、「DATE(2012,2,30)」だと、2012年3月1日が指定されたことになります。

使用例

分けて入力していた月と日を、日付データに直したい
DATE関数を使うと、日付データに直せます

指定年月日の曜日を表示したい
曜日は、年月日のデータから求めて表示できます

 

 

DATEDIF関数

2つの日付間の日数や月数、年数を求めることのできる関数です。

かつての代表的な表計算ソフトとの互換性を保つために残されている関数なので、他の関数のように候補の一覧として表示されることはありませんが、使用には問題ありません。

DATEDIF関数の書式は、次のようになります。

DATEDIF(開始日,終了日,単位)

開始日:期間の開始日を指定します。

終了日:期間の終了日を指定します。

単位:求める値の単位を、次のように指定します。

単位 求められる値
"Y" 期間内の満年数
"M" 期間内の満月数
"D" 期間内の満日数

※終了日には、開始日より後の日付を指定します。逆の場合には、エラーとなります。

使用例

今日から終了日までの残り月数を求めたい
DATEDIF関数を使えば、期間内の満月数を求めることができます

DATEDIF関数式のエラーを解消したい
IFERROR関数を使えば、エラー値の代わりに、任意の文字列を表示させられます

 

EDATE関数

EDATE関数を使うと、指定した月数だけ前または後の日付を得られます。

EDATE関数の書式は、次のようになります。

EDATE(開始日, 月)

開始日:基点となる日付、もしくはそれが入力してあるセルを指定します。

月:「開始日」からの月数を指定します。正数を指定すると「開始日」より後の日付を返し、負数を指定すると「開始日」より前の日付となります。

使用例

期日まで一か月を切ったら強調したい
TODAY関数を使って、今日と期日一か月前の日付を比べる条件付き書式にします

期日を過ぎたら、その行全体を強調したい
TODAY関数を使って、今日と期日の日付を比べる条件付き書式にします

 

EOMONTH関数

EOMONTH関数を使うと、指定した月数だけ前または後の月の最終日を調べることができます。 満期日や支払日などの計算に便利です。

EOMONTH関数の書式は、次のようになります。

EOMONTH(開始日, 月)

開始日:基点となる日付、もしくはそれが入力してあるセルを指定します。

月:「開始日」からの月数を指定します。正数を指定すると「開始日」より後の日付を返し、負数を指定すると「開始日」より前の日付となります。

使用例

 

EXACT関数

指定したセルの文字列を比較して、まったく同じである場合はTRUEを、そうでない場合はFALSEを返す関数です。文字列の照合に使います。

EXACT関数の書式は、次のようになります。

EXACT(文字列 1, 文字列 2)

「文字列 1」と「文字列 2」に、照合するセルや文字列を指定します。

使用例

 

 

 

FIND関数

FIND関数を使うと、指定された文字列を他の文字列から検索できます。得られる値は、指定文字列が最初に現れる位置です。

FIND関数の書式は次のようになります。

FIND(検索文字列, 対象, [開始位置])

検索文字列:検索する文字列を指定します。

対象:検索対象となる文字列を指定します。

開始位置:省略可能です。検索を開始する位置を指定します。対象の先頭文字から検索を開始するときは 1 を指定します。開始位置を省略すると、1 を指定したと見なされます。

※大文字と小文字を区別しないで検索する場合や、ワイルドカード文字を使用する場合は、SEARCH関数を使用します。

使用例

木曜日午後のスケジュール日付を強調したい
「午後」などの文字列が含まれるか否かはFIND関数で調べることができます

“仙台市”の含まれる住所セルと同じ行にある、氏名セルを強調したい
FIND関数を使って、条件付き書式を設定します

関東地方の一都六県に住む人を強調する
あらかじめ一都六県のセルに名前を付けておき、ISNUMBER関数+FIND関数で各セルの検索を行います

 

 

FREQUENCY関数

FREQUENCY関数を使うと、あらかじめ指定しておいた区分値に応じたデータ個数を求めることができます。
配列数式として設定する必要があるので、FREQUENCY関数式の確定は、Ctrl+Shift+Enterで行います。

このFREQUENCY関数の書式は、次のようになります。

FREQUENCY(データ配列, 区間配列)

データ配列:対象となるデータの入力されたセル範囲を指定します。

区間配列: 区分値を入力したセル範囲を指定します。

使用例

階級ごとの度数を数式で求めたい
FREQUENCY関数を使えば、配列数式で簡単に度数を求められます

 

HLOOKUP関数

HLOOKUPは、指定範囲の先頭行を検索し、同じ列にある指定行のデータを抽出する関数です。左端列を検索して、同じ行にある指定列のデータを抽出する場合には、VLOOKUP関数を使います。

標準では、検索対象となる先頭行のデータは昇順に並べられていなければなりません。

昇順に並んでいない先頭行を検索対象にする場合は、引数「検索の型」には「FALSE」を指定する必要があります。

HLOOKUP関数の書式は次のようになります。

HLOOKUP(検索値,範囲,行番号,検索の型)

検索値:検索する値を指定します。

範囲:検索と抽出の対象となるセル範囲を指定します。セル範囲の名前や、テーブル名(Excel 2010/2007の場合)を指定することもできます。

行番号:抽出するデータのある行を、「範囲」の上端から数えた行数で指定します。行番号に 1 を指定した場合は、セル範囲の上端行の値が 抽出対象となります。

検索の型:省略した場合は「TRUE」となり、「検索値」が見つからなくても、「検索値」未満でもっとも大きいものが該当値とみなされます。「FALSE」を指定した場合は、検索値と完全に一致する値だけが検索され、見つからない場合はエラー値「#N/A」が返されます。

使用例

型番を入力すると、小売価格が表示されるようにしたい
検索するべき型番が横(行)方向に並んでいる場合にはHLOOKUP関数を使います

 

 

HYPERLINK関数

HYPERLINK関数式は、次のような書式で設定します。

HYPERLINK(リンク先,別名)

「リンク先」には、メールアドレスやURLなどを指定します。クリックするだけでメール送信できるようにしたい場合には、メールアドレスの頭に「mailto:」という文字列を付加します。

「別名」には、セルに表示される文字列などを指定します。

※ハイパーリンクが設定されたセルにマウスポインタを合わせて、しばらく左ボタンを押したままにすると、マウスポインタが十字形に代わり、リンク先に移動することなく、そのセルを選択できます。HYPERLINK関数式をコピーする場合には、この方法でセルを選択します。 

使用例

ハイパーリンクの設定をまとめて行いたい
HYPERLINK関数を使えば、数式でハイパーリンクの設定を行えます

入力済みの住所に該当する、地図を表示したい
HYPERLINK関数を使って、Web地図サービスへのリンクボタンを作る方法があります

名前にメールアドレスをハイパーリンクして、クリックするだけでメール送信できるようにしたい
HYPERLINK関数を使えば、ハイパーリンク化をまとめて行えます

 

デル株式会社

 

IF関数

IF関数式の書式は次のようになります。

IF(論理式,真の場合,偽の場合)

「論理式」として指定した条件が正しいときに、「真の場合」の値を、誤っているときに「偽の場合」の値を表示します。

IF関数式では、「真の場合」や「偽の場合」として、さらにIF関数式を用いることが可能です。このような入れ子を、Excel 2010/2007では64個まで、Excel 2003/2002では7個まで行えるようになっています。

使用例

月曜日の日付の隣に『定休日』と表示したい
WEEKDAY関数を使ったIF関数式で、月曜日の日付だけに「定休日」と表示させられます

一月の第二月曜日である『成人の日』の日付を求めたい
WEEKDAY関数を利用して求めることができます

振替休日を求めたい
祝日の曜日を調べれば、振替休日の有無を求められます

対象セルが空白セルもしくは0の場合に、計算結果を表示しない
対象セル同士を乗算し、その結果が0となる場合に計算結果を表示しないIF関数式にします

すべての月で目標を達成した者だけにメッセージを表示する
PRODUCTとINDEXの関数を組み合わせることで、シンプルなAND条件式にできます

名字と住所の重複を調べて、名前変換ミスの疑いをメッセージ表示で注意する
便宜上、LEN関数で名前の頭2文字を抜き出し、「&」で郵便番号とつないだもので、各行と全体を比較します

マイナス値をゼロに置換したいB
IF関数を使えば、隣接するセルにそれを実現できます

ふりがなの付加されているセルのふりがなだけを、PHONETIC関数で表示したい
ふりがなの文字種を確認することで、ふりがななのか、文字列そのものなのかを判別することは可能です

『○』を8とする計算式の結果を表示させたい
IF関数式を使えば、「○」を8と置き換えて計算できます

IF関数式で置換する値を、簡単に変更したい
置き換える値を特定のセルに入力するようにします

同じ数字が2つずつ並ぶ、変則的な連番を入力する
偶数行で始まるか、奇数行で始まるか、に応じて、ISEVENとISODDの関数を使い分けます

別のシートに入力してあるデータを、隔行で表示しなおす
INDIRECT関数を使って、偶数行もしくは奇数行にのみデータが表示されるようにします

 

デル株式会社

 

 

 

 

IFERROR関数

エラー値の表示を回避したいときに、IFERROR関数が便利です。

IFERROR関数の書式は、次のようになります。

IFERROR(数式, エラーの場合の値)

数式:エラーが表示される可能性のある数式を指定します。

エラーの場合の値:「数式」がエラー値となったときに表示する値などを指定します。

IFERROR関数で対処できるエラーは、#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?、#NULL! です。

使用例

文字列を含む時刻計算でエラー表示させたくない
IFERROR関数を使うと、エラー値を任意の文字列に置き換えることができます

DATEDIF関数式のエラーを解消したい
IFERROR関数を使えば、エラー値の代わりに、任意の文字列を表示させられます

例外の市を含む 都道府県から、送料を求めたい
IFERRORを使って、2つのVLOOKUP関数で段階的に送料を検索します

数式の結果としてのエラーを表示したくない」
IFERROR関数を使えば、数式がエラーになるときの表示を任意に決められます

商品コードが空欄のとき、 商品名や単価の欄にエラー値を表示させたくない
IFERROR関数を使えば、エラー値を表示させないようにできます

2つの型番リストを参照して、小売価格を調べたい
IFERROR関数を使って、1つめの参照でエラーとなったときに、2つめのリストを参照するようにします

エラー値 #N/A を表示したくない
IFERROR関数を使えば、#N/Aなどの数式エラーをトラップできます
 

デル株式会社

 

INDEX関数

配列の要素の値を格納することができる関数です。

INDEX関数の書式は次のようになります。

INDEX(配列, 行番号, [列番号])

行番号または列番号を0に設定すると、全体の値の配列が返されます。

※INDEX関数に格納されている配列の値は、配列数式にすることで確認することができます。

使用例

すべての月で目標を達成した者だけを強調表示する
PRODUCTとINDEXの関数を組み合わせることで、シンプルなAND条件式にできます

すべての月で目標を達成した者だけにメッセージを表示する
PRODUCTとINDEXの関数を組み合わせることで、シンプルなAND条件式にできます

 

 

 

INDIRECT関数

指定したセル番地の値を表示することができる関数です。

間接的にセルを指定できるので、指定したセル番地のセルが移動したり並べ替えられたりしても、変わらずそのセル番地の値を表示することができます。

INDIRECT関数の書式は、次のようになります。

INDIRECT(セル番地を表わす文字列, [参照形式])

「セル番地を表わす文字列」には、値を表示したいセル番地を指定します。

R1C1形式でセル番地を指定したいときには、「参照形式」として「FALSE」を指定します。

使用例

逆順の並べ替えを自動的に行いたい
INDIRECT関数を使って、逆順の並べ替えを行う方法もあります

別のシートに入力してあるデータを、隔行で表示しなおす
INDIRECT関数を使って、偶数行もしくは奇数行にのみデータが表示されるようにします

 

 

ISBLANK関数

指定したセルが空白セルの場合にTRUE を返します。

ISBLANK関数の書式は次のようになります。

ISBLANK(セル)

空白セルかどうかを調べたいセルの番地を指定します。

IF関数式の結果などによる見た目の空白セルは、FALSEとなります。数式も入力されていない真の空白セルだけがTRUEとなります。

使用例

値が空欄でない項目を強調する
ISBLANK関数を使えば、セルが空欄であるか否かを調べることができます

空欄の含まれる行を強調する
複数のセルを同じ条件で評価する場合には、SUMPRODUCT関数が便利です

 

 

ISERROR関数

VLOOKUP関数式では、引数「検索値」として指定したセルが空の場合に、エラー値 #N/A が表示されます。これを回避したいときに、ISERROR関数を用います。

ISERROR関数の書式は次のようになります。

ISERROR(テストの対象)

テストの対象:エラーが表示される可能性のある数式を指定します。

「テストの対象」がエラー値 (#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?、#NULL!) となるとき、TRUEとなります。それ以外のときにはFALSEとなります。

使用例

エラー値 #N/A を表示したくない
IFERROR関数を使えば、#N/Aなどの数式エラーをトラップできます

 

【送料無料】はじめてのExcel 2007(基本編)

【送料無料】はじめてのExcel 2007(基本編)

価格:1,260円(税込、送料別)

 

ISEVEN関数

指定した数式の結果などが偶数になるときに、TRUEを返す関数です。

ISEVEN関数の書式は、次のようになります。

ISEVEN(テストの対象)

「テストの対象」には、数式やセル番地を指定します。

使用例

表内の各行を交互に2色で塗りつぶす
あらかじめ表全体を塗りつぶしておけば、条件付き書式でISEVEN関数による偶数行の塗りつぶしを設定するだけで済みます

同じ数字が2つずつ並ぶ、変則的な連番を入力する
偶数行で始まるか、奇数行で始まるか、に応じて、ISEVENとISODDの関数を使い分けます

別のシートに入力してあるデータを、隔行で表示しなおす
INDIRECT関数を使って、偶数行もしくは奇数行にのみデータが表示されるようにします

表内の各列を交互に2色で塗りつぶす
あらかじめ表全体を塗りつぶしておけば、条件付き書式でISEVEN関数による偶数列の塗りつぶしを設定するだけで済みます

市松模様(チェック柄)に表を修飾する
行番号と列番号を足したもので、条件式(論理式)としての判定を行います

 

 

ISNUMBER関数

指定した数式の結果などが数値になるときに、TRUEを返す関数です。数値以外のエラーなどは、FALSEと判断されます。

ISNUMBER関数の書式は、次のようになります。

ISNUMBER(テストの対象)

「テストの対象」には、数式やセル番地を指定します。

使用例

関東地方の一都六県に住む人を強調する
あらかじめ一都六県のセルに名前を付けておき、ISNUMBER関数+FIND関数で各セルの検索を行います

 

 

 

ISODD関数

指定した数式の結果などが奇数になるときに、TRUEを返す関数です。

ISODD関数の書式は、次のようになります。

ISODD(テストの対象)

「テストの対象」には、数式やセル番地を指定します。

使用例

同じ数字が2つずつ並ぶ、変則的な連番を入力する
偶数行で始まるか、奇数行で始まるか、に応じて、ISEVENとISODDの関数を使い分けます

別のシートに入力してあるデータを、隔行で表示しなおす
INDIRECT関数を使って、偶数行もしくは奇数行にのみデータが表示されるようにします

 

 

LARGE関数

LARGEは、セル範囲から○番目に大きなデータを返す関数です。LARGE関数を使えば、2番目や3番目などの値を調べることができます。

LARGE関数の書式は次のようになります。

LARGE(範囲,順位)

範囲:セル範囲を指定します。

順位:大きい方から数えた順位を数値で指定します。

使用例

上位10人の得点を強調したい
LARGE関数による条件付き書式を設定すれば、「上位10項目」の値を強調できます

 

 

 

LEFT関数

指定した文字列の先頭から、指定文字数の文字列を返す関数です。

LEFT関数の書式は、次のようになります。

LEFT(文字列, [文字数])

「文字列」には、取り出す文字を含むセルを指定します。

「文字数」には、取り出す文字数を指定します。

使用例

住所データから都道府県名を抜き出したい
LEFT関数を使えば、住所の先頭に入力されている都道府県名を抜き出せます

名字と住所の重複を調べて、名前変換ミスの疑いのある人を強調する
便宜上、LEN関数で名前の頭2文字を抜き出し、「&」で郵便番号とつないだもので、各行と全体を比較します

名字と住所の重複を調べて、名前変換ミスの疑いをメッセージ表示で注意する
便宜上、LEN関数で名前の頭2文字を抜き出し、「&」で郵便番号とつないだもので、各行と全体を比較します

 

 

 

LEN関数

指定したセルに入力されている文字列の文字数を返します。

LEN関数の書式は次のようになります。

LEN(セル)

文字数を調べたい、セル番地を指定します。

スペースも文字として数えられます。

指定セルが空白セルの場合には、0が返されます。

使用例

データの文字数を求めたい
LEN関数を使うと、指定したセルのデータの文字数がわかります

存在しない桁の数字を表示したくない
対象となる数値の桁数をあらかじめ確認するようにすれば、存在しない桁の数字を非表示にできます

最長の文字列データを求めたい
LEN関数で求めた文字数から、オートフィルタで最大値のものを絞り込めば、最長の文字列を持つ行だけが表示されます

0の入力されているセルを強調する
空白セルは0扱いなので、それを除くための条件が必要です

 

DHCオンラインショップ

 

MATCH関数

MATCHは、指定範囲内で検索した項目の相対的な位置を返す関数です。

MATCH関数は、次のような書式で使います。

MATCH(検査値, 検査範囲, [照合の種類])

「検査値」には、照合する値を指定します。

「検査範囲」には、検索の対象となるセル範囲を指定します。

「照合の種類」を省略すると、検査値以下の最大の値が検索され、その相対位置が返されます。「-1」を指定した場合は、その逆に検査値以上の最小の値が検索されます。「0」にした場合は、検査値に一致する値のみが検索されることになります。

使用例

最大値の左側に記されている番号を知りたい
連番になっているなら、MATCH関数だけで該当番号がわかります

プランに応じた規定料金を求めたい
MATCH関数を利用すれば、プラン別の料金をVLOOKUP関数で求めることができます

A列の同一データ間で、B列で前後30日間に該当する日付を調べる
シリアル値で使われていない桁で、A列のデータを識別し、2つのデータをまとめたもので前後30日を判定します

 

 

 

MAX関数

MAXは、引数リストに含まれる数値の中から、最大のものを返す関数です。

このMAX関数は、次のような書式で使います。

MAX(セル範囲)

 

使用例

最大値を知りたい
MAX関数を使えば、指定セル範囲の中から最大値を取り出して表示できます

早出の時間を求めたい
マイナスの無効時刻とならないように、MAX関数を利用します

月別で回答数の最大値を確認したい
MAX関数を使えば、指定したセルの中から最大値を求めることができます

 

 

MID関数

MIDは、指定した位置から指定した文字数の文字を返す関数です。

このMID関数は、次のような書式で使います。

MID(文字列, 開始位置, 文字数)

「文字列」には、セルを指定できます。

「開始位置」には、取り出す先頭文字の位置を数値で指定します。 文字列の先頭文字の位置が 1 になります。

「文字数」には、取り出す文字数を指定します。「文字列」以上の文字数を指定すると、「開始位置」以降のすべての文字が返されます。

使用例

 

MIN関数

MINは、引数リストに含まれる数値の中から、最小のものを返す関数です。

このMIN関数は、次のような書式で使います。

MIN(セル範囲や数式)

 

使用例

いくつかの計算結果の中から最小値を得たい
MIN関数を使えば、計算結果などから最小値を選ぶことができます

行ごとに最小値を強調する
まず先頭行で最小値の強調表示を設定し、それを適用範囲で広げる方法が効率的です

 

 

 

MOD関数

数値を割ったときの余りを返す関数です。

このMOD関数は、次のような書式で使います。

MOD(数値, 除数)

「数値」には、割り算の分子となる数値、もしくはその数値を表すセル番地を指定します。

「除数」には、割り算の分母となる数値を指定します。

使用例

数値をひと桁ずつ個別のセルに表示したい
ROUNDDOWNとMOD関数を組み合わせて使えば、任意の桁の数字を表示できます

偶数行と奇数行を値で区別したい
行番号を2で割って、その余りをMOD関数で調べれば、偶数行と奇数行を区別できます

整数部の数値を位別に表示したい
MOD関数とROWNDDOWN関数を組み合わせることで、目的の位の数字だけを取り出して表示できます

割り算の結果を、余りと一緒に表示させたい
ROUNDDOWN関数とMOD関数を使えば、除算の結果を、整数と余りに分けられます

数値をひと桁ずつ個別のセルに表示したい
ROUNDDOWNとMOD関数を組み合わせて使えば、任意の桁の数字を表示できます

条件付き書式を使って、奇数行のセルだけを塗りつぶす
ROW関数で得た行番号を、MOD関数で除算すれば、その余りで奇数行と偶数行を判別できます

表内の各行を3色で順に塗りつぶす
MOD関数を使って3で除算した余りが、条件付き書式での判定条件となります

5000との差が500で割り切れる値を強調したい
MOD関数による条件式を使い、500で除算した余りが0となるセルを、条件付き書式で強調します

 

 

 

MONTH関数

日付データに含まれる月の数値を、 1〜12の範囲の整数で返す関数です。

このMONTH関数は、次のような書式で使います。

MONTH(日付データ)

「日付データ」には、日付データの入力されているセルを指定します。

使用例

仕入伝票と売上伝票を使って、月ごとで在庫管理したい@
それぞれの伝票に「月」列を追加すれば、それを条件にしたSUMIFS関数式で入出庫数を求めることができます

日付データを使って○月と表示させたい
MONTH関数を使う方法があります

 

MROUND関数

対象となる数値を、指定した値の倍数になるように丸めます。

日時を表すシリアル値も対象にできます。

このMROUND関数は、次のような書式で使います。

MROUND(数値,倍数)

「数値」には、丸める対象の数値を指定します。日時の入力されたセルも指定できます。

「倍数」には、丸めて求める倍数の基準値を指定します。日付や時刻の場合には、""で囲んで指定します。

使用例

月の合計時間を30分単位で丸めたい
MROUND関数を使えば、指定した時間で丸めることができます

 

NETWORKDAYS関数

NETWORKDAYS関数を使うと、引数として指定した開始日から終了日までの期間に含まれる稼動日、すなわち平日の日数を求められます。祝祭日の日付データを用意しておけば、それも引数として指定することで、特定期間内の祝祭日ものぞいた稼動日数を求めることができます。

このNETWORKDAYS関数は、次のような書式で使用します。

NETWORKDAYS(開始日,終了日,祭日)

「開始日」で日付を直接指定する場合には、DATE 関数を使って、「DATE(2010,3,1)」のように記述します。「終了日」も同様です。

「祭日」には、祝祭日などの休日の日付データの入力されたセル範囲を指定します。

使用例

平日の日数を計算式で求めたい
NETWORKDAYS関数を使えば、土日や祭日をのぞく、平日の日数を求めることができます

 

富士通パソコンFMVの直販サイト富士通 WEB MART

 

NOT関数

引数がTRUEのときにFALSEを、FALSEのときにTRUEを返します。

NOT関数の書式は、次のようになります。

NOT(論理式)

「論理式」として指定できるのは、論理式に限りません。結果が数値となる数式であれば、論理式の代わりに指定することができます。その場合のNOT関数の評価は、0であればTRUE、それ以外の数値の場合にはFALSEとなります。

使用例

値が空欄でない項目を強調する
ISBLANK関数を使えば、セルが空欄であるか否かを調べることができます

 

 

 

OR関数

複数の比較演算式の結果を、総合して判断することができる関数です。1つでも「TRUE」が含まれていれば、「TRUE」と判断されます。「FALSE」と判断されるのは、すべての結果が「FALSE」の場合に限られます。AND関数よりも条件の緩やかな論理関数といえます。

OR関数の書式は次のようになります。

OR(論理式1, [論理式2], ...)

引数の「論理式」には、その結果がTRUE(真)もしくはFALSE(偽)となる条件式を指定します。

OR関数の引数「論理式」は、255個まで指定できます。

使用例

月曜日と木曜日に該当するセルを強調したい
条件が複数あり、そのいずれかに該当するセルを強調したい場合は、条件付き書式の条件式にOR関数を使います

同じ値が連続するセル範囲を強調したい
上のセルとの比較、そして下のセルとの比較を行い、いずれかの条件式がTRUEの場合、同じ値が連続するセル範囲の1つだと判断できます

 

DMM.com DVD通販、レンタルなどの総合サイト

 

PERCENTRANK関数

セル範囲の中で指定セル値がどのあたりに位置するのかを、パーセンテージで示す関数です。

PERCENTRANK関数の書式は次のようになります。

PERCENTRANK(セル範囲,指定セル,[有効桁数])

「セル範囲」には、「指定セル」の含まれるセル範囲を指定します。

「有効桁数」を省略すると、小数点以下第三位までのパーセンテージが求められます。

使用例

先月の営業成績が上位30%以内に含まれる人を強調する
PERCENTRANK関数を使って、70%以上に該当する人を判定します

過去半年間の営業成績が上位30%以内に含まれる人を強調する
別シートで半年分の営業成績を合計し、条件付き書式でPERCENTRANK関数による判定を行います

 

 

PHONETIC関数

セルに入力されている文字列から、付加されているふりがなを抽出することのできる関数です。

ふりがなが付加されている文字列の場合には、その文字列そのものが抽出されてしまうので、使い方には注意が必要です。

PHONETIC関数の書式は次のようになります。

PHONETIC(セル番地)

指定セルに入力されている文字列のふりがなが表示されます。

ふりがなの付加されている文字列の場合には、 文字列そのものが返されます。

使用例

ふりがなの付加されているセルのふりがなだけを、PHONETIC関数で表示したい
ふりがなの文字種を確認することで、ふりがななのか、文字列そのものなのかを判別することは可能です

ふりがなを他のセルに表示したい
PHONETIC関数を使うと、データに付加されているふりがなを別のセルに表示できます

 

DMM.com DVD通販、レンタルなどの総合サイト

DMM.com DVD通販、レンタルなどの総合サイト

 

PRODUCT関数

PRODUCTは、引数として指定したセルの値すべての積を計算する関数です。多数のセルの積を計算するときに便利です。

PRODUCT関数の書式は次のようになります。

PRODUCT(セル範囲1, [セル範囲2], ...)

引数のセル範囲は、255個まで指定できます。

ただし、空白セルや論理値、文字列は無視されます。

使用例

すべての月で目標を達成した者だけを強調表示する
PRODUCTとINDEXの関数を組み合わせることで、シンプルなAND条件式にできます

すべての月で目標を達成した者だけにメッセージを表示する
PRODUCTとINDEXの関数を組み合わせることで、シンプルなAND条件式にできます

 

 

RANK関数

RANK関数を使うと、指定範囲内で指定数値が何番目に位置するのかがわかります。

このRANK関数は、次のような書式で記述します。

RANK(数値,範囲,順序)

数値:調べる対象の数値を指定します

範囲:対象数値の含まれるセル範囲を指定します

順序:「0」を指定するか省略すると、降順での順位を調べます。「0」以外の数値を指定すると、昇順での順位となります。

使用例

合計点での順位を求めたい
RANK関数を使うと、全体における順位を求めることができます

並べ替えないで、勝ち数による順位を知りたい
RANK関数を使えば、並べ替えることなく順位がわかります

直近2カ月の営業成績のいずれかが上位5位以内の人を強調する
RANK関数での判定結果を加算することで、1度でも上位5位以内に入った人がわかります

過去半年で上位5位以内が3ヵ月以上ある人を強調する
別シートで各月の判定を行い、条件付き書式でそれらを加算します

直近2カ月の営業成績がいずれも下位5位以内の人を強調する
RANK関数での判定結果を乗算することで、両方で下位5位以内だった人がわかります

過去半年で下位5位以内が3ヵ月以上ある人を強調する
別シートで各月の判定を行い、条件付き書式でそれらを加算します

 

DMM.com DVD通販、レンタルなどの総合サイト

 

ROUNDDOWN関数

指定された桁数で数値を切り捨てることができる関数です。

ROUNDDOWN関数の書式は、次のようになります。

ROUNDDOWN(セル番地,桁数)

指定した「セル番地」に表示されている数値が処理の対象となります。

「桁数」を0にすると、対象となる数値は整数に切り捨てられます。

使用例

数値をひと桁ずつ個別のセルに表示したい
ROUNDDOWNとMOD関数を組み合わせて使えば、任意の桁の数字を表示できます

整数部の数値を位別に表示したい
MOD関数とROWNDDOWN関数を組み合わせることで、目的の位の数字だけを取り出して表示できます

割り算の結果を、余りと一緒に表示させたい
ROUNDDOWN関数とMOD関数を使えば、除算の結果を、整数と余りに分けられます

数値をひと桁ずつ個別のセルに表示したい
ROUNDDOWNとMOD関数を組み合わせて使えば、任意の桁の数字を表示できます

 

 

 

ROW関数

引数として指定したセルの行番号を知ることができる関数です。

ROW 関数の書式は、次のようになります。

ROW(セル番地)

引数のセル番地を省略すると、ROW関数が入力されているセルの行番号が返されます。

使用例

行番号を列に表示したい
ROW関数を使えば、対象とするセルの行番号を表示できます

条件付き書式を使って、奇数行のセルだけを塗りつぶす
ROW関数で得た行番号を、MOD関数で除算すれば、その余りで奇数行と偶数行を判別できます

表内の各行を交互に2色で塗りつぶす
あらかじめ表全体を塗りつぶしておけば、条件付き書式でISEVEN関数による偶数行の塗りつぶしを設定するだけで済みます

表内の各行を3色で順に塗りつぶす
MOD関数を使って3で除算した余りが、条件付き書式での判定条件となります

同じ数字が2つずつ並ぶ、変則的な連番を入力する
偶数行で始まるか、奇数行で始まるか、に応じて、ISEVENとISODDの関数を使い分けます

別のシートに入力してあるデータを、隔行で表示しなおす
INDIRECT関数を使って、偶数行もしくは奇数行にのみデータが表示されるようにします

市松模様(チェック柄)に表を修飾する
行番号と列番号を足したもので、条件式(論理式)としての判定を行います

対戦表で不要な同一対戦枠を黒く塗りつぶす
行番号と列番号を比較して、同一である場合のみ黒く塗りつぶされるようにします

 

DMM.com DVD通販、レンタルなどの総合サイト

 

SMALL関数

SMALLは、セル範囲から○番目に小さなデータを返す関数です。SMALL関数を使えば、2番目や3番目などの値を調べることができます。

SMALL関数の書式は次のようになります。

SMALL(範囲,順位)

範囲:セル範囲を指定します。

順位:小さい方から数えた順位を数値で指定します。

使用例

条件付き書式を使って、上位または下位に入る値を強調する
Excel 2013/2010/2007では「上位/下位ルール」を使うことで、Excel 2003/2002ではSMALLやLARGEなどの関数式を使うことで、セル範囲から上位や下位に該当する値を強調できます

 

 

 

STDEV関数

標準偏差を求めるときに用いる関数です。引数を母集団の標本であるとみなして、母集団の標準偏差を求めることができます。標準偏差とは、統計的な対象となる値が、その平均値からどれだけ広い範囲に分布しているかを計量したものです。標準偏差からは、偏差値などをも求めることが可能です。

STDEV関数の書式は次のようになります。

STDEV(母集団のセル範囲)

引数には、母集団のセル範囲を指定します。

使用例

偏差値を求めたい
偏差値は、z得点を10倍にして、50を足すことで、数値として扱いやすくしたものです

 

DMM.com DVD通販、レンタルなどの総合サイト

DMM.com DVD通販、レンタルなどの総合サイト

 

SUBTOTAL関数

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関数を使えば、非表示のデータを無視して集計できます

 

 

 

SUM関数

SUMは、引数として指定したセルにある数値を合計する関数です。

SUM関数の書式は次のようになります。

SUM(セル範囲)

SUM関数では、数値だけが計算の対象となります。空白セルや論理値、文字列はすべて無視されます。

使用例

累計を求めたい
SUM関数式でも累計が可能です

月別シートのデータを合計したい
SUM関数は、複数シートのセル範囲を合計することができます

毎日増えていく日別シートを集計したい
同じフォーマットで入力されている値なら、SUM関数による串刺し集計が可能です

毎日の売上を順に累計したい
SUM関数式で、始点を先頭セルに絶対指定します

合計を求めるSUM関数式の結果に、「時間」を付けて表示したい
ユーザー定義の表示形式をセルに割り当てれば、「時間」付きで表示されるようになります

構成比の累計を求めたい
範囲の先頭セルを絶対指定にすれば、SUM関数式で求めることができます

加算(足し算)の結果としてのエラーを表示したくない
演算子「+」の代わりにSUM関数を使えば、IFERROR関数などによる空白セルが含まれていてもエラーとなりません

過去半年で上位5位以内が3ヵ月以上ある人を強調する
別シートで各月の判定を行い、条件付き書式でそれらを加算します

過去半年で下位5位以内が3ヵ月以上ある人を強調する
別シートで各月の判定を行い、条件付き書式でそれらを加算します

複数シートのセル値を串刺しで合計したい
合計するセルが、異なるシートでも、同じ番地にあるならSUM関数で串刺し合計できます

 

 

 

SUMIF関数

SUMIFは、指定した条件を満たす値を合計する関数です。

このSUMIF関数は、次のような書式で使います。

SUMIF(範囲, 検索条件, [合計範囲])

「範囲」には、条件によって評価するセル範囲を指定します。空白と文字列は無視されます。

「検索条件」には、数値や式、文字列などを指定します。検索条件をセルに入力しておくこともでき、その場合にはそのセル番地を指定します。

「合計範囲」 には、合計する値の入力されているセル範囲を指定します。「検索条件」の対象となる「範囲」の値を合計する場合には省略できます。

使用例

担当者ごとの売上合計を数式で求めたい
SUMIF関数で求められます

担当者ごとの売上金額を合計したい
担当者の名前がわかっていれば、SUMIF関数で担当者ごとの合計を求められます

数式で 担当者別の売上を求めたい
担当者の一覧を用意できるなら、SUMIF関数で担当者別の売上を求められます

追加データの予定されている表から、条件に合致するデータの合計を求めたい
テーブル化してあれば、データの追加が予定されている表からでも、SUMIF関数で動的に合計を求めることができます

曜日毎に授業実施時数の合計を求めたい
SUMIF関数を使えば、曜日を条件として、該当する数値だけの合計を行えます

月〜金曜日の売上だけを合計したい
WEEKDAY関数による結果を条件とすれば、SUMIF関数で曜日を限定して売上を合計できます

識別数字が“7”の製品の個数を合計したい
SUMIF関数を使えば、条件に該当する行の値だけを合計できます

 

 

 

SUMIFS関数

SUMIFSは、複数の条件を満たす値を合計する関数です。

SUMIFS関数は、次のような書式で使います。

SUMIFS(合計対象範囲,条件範囲1,条件1,[条件範囲2,条件2...])

「合計対象範囲」には、合計する値があるセル範囲をを指定します。空の値やテキスト値は無視されます。

「条件範囲1」には、条件による評価の対象となる最初の範囲を指定します。「条件1」には、その「条件範囲1」に適用する条件を指定します。

「条件範囲2,条件 2, ...」は、ほかにも条件がある場合に指定します。最大 127 組の範囲/条件のペアを指定できます。

SUMIFS関数では、こうして指定した条件をすべて満たすものだけが合計の対象となります。

このSUMIFS関数を使って、日付と項目の2つを条件にすれば、各項目の日付ごとの集計表を作ることができます。

※SUMIFS関数は、2003以前のExcelでは利用できません。

※SUMIFS関数の引数は、SUMIF関数と指定順序が異なります。SUMIFS関数では合計の対象となるセル範囲を第1引数として指定します。

使用例

仕入伝票と売上伝票を使って、月ごとで在庫管理したい@
それぞれの伝票に「月」列を追加すれば、それを条件にしたSUMIFS関数式で入出庫数を求めることができます

同じシートに項目ごとの集計結果を表示したい
日付と項目の2つを条件とする、SUMIFS関数を使います

 

SUMPRODUCT関数

SUMPRODUCTは、引数として指定した配列の対応する要素間の積をまず計算し、さらにその和を求める関数です。

SUMPRODUCT関数の書式は次のようになります。

SUMPRODUCT(配列1, [配列2], [配列3], ...)

SUMPRODUCT関数は、SUM関数の配列数式の代わりに使用することができます。

使用例

目標達成月数で色分けする
あとから設定した条件付き書式のほうが上に表示されて優先適用されるので、値の大から小、もしくは小から大へと順に設定していきます

目標達成月数の多い人にメッセージを表示する
SUMPRODUCT関数で論理値を加算する条件式にすれば、一定の月数以上の目標達成者にだけメッセージを表示することができます

特定の日を含む、前後3日間を強調する
比較する日付との差を絶対値で求めて、それがシリアル値3以内となるものを、条件付き書式の条件式とします

前後1時間で重複するスケジュールをチェックする
すべての日付+時刻と減算を行い、その差が1時間以内になるものを数えます

関東地方の一都六県に住む人を強調する
あらかじめ一都六県のセルに名前を付けておき、ISNUMBER関数+FIND関数で各セルの検索を行います

名字と住所の重複を調べて、名前変換ミスの疑いのある人を強調する
便宜上、LEN関数で名前の頭2文字を抜き出し、「&」で郵便番号とつないだもので、各行と全体を比較します

名字と住所の重複を調べて、名前変換ミスの疑いをメッセージ表示で注意する
便宜上、LEN関数で名前の頭2文字を抜き出し、「&」で郵便番号とつないだもので、各行と全体を比較します

空欄の含まれる行を強調する
複数のセルを同じ条件で評価する場合には、SUMPRODUCT関数が便利です

A列の同一データ間で、B列で前後30日間に該当する日付を調べる
シリアル値で使われていない桁で、A列のデータを識別し、2つのデータをまとめたもので前後30日を判定します

 

 

 

TIME関数

指定した時刻や時間を、シリアル値(小数)に変換します。

TIME関数の書式は、次のようになります。

TIME(時, 分, 秒)

「時」「分」「秒」は、それぞれ省略することも可能です。その場合には、0と指定したことになります。

使用例

時刻表示の時間から、8時間を引きたい
TIME関数を使えば、任意の時分秒を表わすことができます

 

 

 

TEXT関数

セルの表示形式で用いられる書式文字列を使って、必要なデータを表示できる関数です。

このTEXT関数の書式は、次のようになります。

TEXT(値, 表示形式)

値:元データの入力されているセル番地を指定します。

表示形式:セルの表示形式で用いる書式文字列を引用符(")で囲んで指定します。

使用例

毎日の売上を曜日で集計したい
元表に「曜日」列を追加すれば、ピボットテーブルでそれを使って集計できます

 

 

 

TODAY関数

今日の日付のシリアル値を表わすことができます。

TODAY関数の書式は、次のようになります。

TODAY()

 

使用例

期日まで一か月を切ったら強調したい
TODAY関数を使って、今日と期日一か月前の日付を比べる条件付き書式にします

期日を過ぎたら、その行全体を強調したい
TODAY関数を使って、今日と期日の日付を比べる条件付き書式にします

最新の在庫数を把握したい
TODAY関数を使うと、本日の日付を条件にして、最新の在庫数を得ることができます

 

 

 

VLOOKUP関数

VLOOKUPは、指定範囲の左端列を検索し、同じ行にある指定列のデータを抽出する関数です。先頭行を検索して、同じ列にある指定行のデータを抽出する場合には、HLOOKUP関数を使います。

標準では、検索対象となる左端列のデータは昇順に並べられていなければなりません。

昇順に並んでいない左端列を検索対象にする場合は、引数「検索の型」には「FALSE」を指定する必要があります。

VLOOKUP関数の書式は次のようになります。

VLOOKUP(検索値,範囲,列番号,検索の型)

検索値:検索する値を指定します。

範囲:検索と抽出の対象となるセル範囲を指定します。セル範囲の名前や、テーブル名を指定することもできます。

列番号:抽出するデータのある列を、「範囲」の左端から数えた列数で指定します。列番号に 1 を指定した場合は、セル範囲の左端列の値が抽出対象となります。

検索の型:省略した場合は「TRUE」となり、「検索値」が見つからなくても、「検索値」未満でもっとも大きいものが該当値とみなされます。「FALSE」を指定した場合は、検索値と完全に一致する値だけが検索され、見つからない場合はエラー値「#N/A」が返されます。

使用例

住所データから抜き出した都道府県名を完全なものにしたい
VLOOKUP関数を使って、都道府県名リストと照合します

祝日名や振替休日を、日付の隣に表示したい
祝日名はVLOOKUP関数で、振替休日はCOUNTIF関数で照合します

受注時に在庫数を確認したい
VLOOKUP関数で在庫表から参照できます

祝祭日を自動的に表示させたい
祝祭日のリストを用意しておけば、日付に該当するものを、VLOOKUP関数で表引きできます

料金表から規定料金を導きたい
VLOOKUP関数を使えば、料金表から表引きできます

テーブル化した表を使って表引きしたい
あらかじめ適当なテーブル名をつけておけば、その名前で表引きを行えます

配分で5段階評価したい
配分表を用意すれば、VLOOKUP関数で評価値を求められます

都道府県名から送料を求めたい
VLOOKUP関数を使って、都道府県名に該当する送料を検索します

例外の市を含む 都道府県から、送料を求めたい
IFERRORを使って、2つのVLOOKUP関数で段階的に送料を検索します

入力済みの住所を、都道府県名と、それ以降の住所に分けたい
郵政事業株式会社の郵便番号データで都道府県名を表引きすることで、それ以降の住所と分けて表示することが可能です

郵便番号から、都道府県名と、それ以降の住所を、別々に入力したい
郵政事業株式会社のWebで公開されている郵便番号データを使えば、郵便番号から都道府県名などを表引きできます

商品名や単価を、商品一覧から表引きしたい
表引きする表にあらかじめ適当な名前を付けておけば、VLOOKUP関数で簡単に表引きできます

あとから見てもわかりやすいVLOOKUP関数式にしたい
「範囲」として指定するセル範囲に、あらかじめ適当な名前を付けておけば、VLOOKUP関数式でもそれを使えるのでわかりやすくなります

商品番号を入力すると、自動的に商品名が表示されるようにしたい
VLOOKUP関数式を設定すれば、商品リストからの表引きを行えます

商品番号を入力すると、自動的に商品名以外の情報も表示されるようにしたい
商品名などを表示するセルが、表引きの対象リストと同じ並びになっていれば、商品名のVLOOKUP関数式をコピーして使えます

一覧表のデータを、申請書の必要な箇所にコピーしたい
あらかじめ必要なVLOOKUP関数式を設定しておけば、データの一つを入力するだけで、関連する項目を簡単に埋められます

 

 

 

WEEKDAY関数

日付を表すシリアル値から、その日付に対応する曜日を返す関数です。既定では、戻り値は 1 (日曜) から 7 (土曜) までの範囲の整数で、それぞれの曜日が表されます。

Excel では、「2010/8/17」ような形式で、日付として入力したデータは、1900 年 1 月 1 日を基点とするシリアル値で管理されます。「2010/8/17」ような形式は表示形式の一つであり、必要に応じて日にちや曜日など、さまざまな見た目で表すことができます。

WEEKDAY関数の書式は次のようになります。

WEEKDAY(シリアル値,種類)

シリアル値:日付データ(シリアル値)の入力されているセルを指定します。

種類:1〜3のいずれかの数値を指定することで、曜日を表す整数を変えることができます。
1 または省略した場合は、 1 (日曜) 〜 7 (土曜) の範囲の整数となります。
2を指定した場合は、 1 (月曜) 〜 7 (日曜) の範囲の整数となり、平日(1〜5)と土日(6,7)の区別が容易となります。
3を指定した場合は、0 (月曜) 〜 6 (日曜) の範囲の整数となります。

使用例

土曜日を青色で、日曜日を赤色で表示したい
WEEKDAY関数を使った条件付き書式で行えます

日曜日の日付を赤色で表示したい
年/月/日の形で日付を入力していれば、WEEKDAY関数を使った条件付き書式で、曜日を色分けできます

月曜日の日付の隣に『定休日』と表示したい
WEEKDAY関数を使ったIF関数式で、月曜日の日付だけに「定休日」と表示させられます

土日を色分けしたい
WEEKDAY関数を使った条件付き書式で、色分けが可能です

木曜日の日付を強調したい
ユーザー定義で曜日を表示している場合には、WEEKDAY関数で木曜日かどうかを調べます

一月の第二月曜日である『成人の日』の日付を求めたい
WEEKDAY関数を利用して求めることができます

日曜日を赤色で、土曜日を青色で表示する
日付の入力されているセルをWEEKDAY関数で参照し、その値を書式適用の条件にします

スケジュール表の土日を塗りつぶしたい
日付や曜日がシリアル値(日時データ)で入力されていれば、WEEKDAY関数を使った条件付き書式で、土日だけを強調できます

WEEKDAY関数を使ってみたい
WEEKDAY関数を使うと、指定した日付の曜日を1〜7の数値で表せます

日曜日の列だけを塗りつぶしたい
WEEKDAY関数による条件式を使えば、日曜日のセル範囲だけを塗りつぶせます

日付の曜日を数値で表したい
WEEKDAY関数を使うと、各曜日を数値で表すことができます

日付から曜日データを取得したい
WEEKDAY関数を使えば、日付データから、曜日を表す数値データを取得できます

曜日でデータをまとめたい
WEEKDAY関数の結果を並べ変えれば、曜日ごとに集計することもできます

ピボットテーブルを使って、曜日でデータを分析したい
WEEKDAY関数の結果をピボットテーブルでページフィルタにすれば、曜日でデータを分析できます

 

 

 

 

 

 

 

質問や要望などはこちらから

本記事に対する質問や要望などは、気軽にお寄せください。個別の回答は行いませんが、適時この特集「関数」に反映させていきます。

Powered by NINJA TOOLS