Web 用 Excelを初めて使用する場合は、列または行に数値を入力するグリッドだけではありません。 はい、 Web 用 Excel を使用して、列または数値の行の合計を見つけることができますが、住宅ローンの支払いを計算したり、数学やエンジニアリングの問題を解決したり、プラグインする変数番号に基づいて最適なシナリオを見つけたりすることもできます。
Web 用 Excel では、セル内の数式を使用してこれを行います。 数式は、ワークシート内のデータに対して計算またはその他のアクションを実行します。 数式は常に等号 (=) で始まり、その後に数値、算術演算子 (正符号や負符号など)、関数が続き、数式の累乗を実際に拡張できます。
たとえば、次の数式は 2 と 3 を乗算し、その結果に 5 を加算して、11 という解を求めます。
=2*3+5
この次の数式では、PMT 関数を使って、住宅ローンの返済額 (¥107,364) を計算します。これは、¥20,000,000 のローンに対し、5% の金利 (5% を 12 か月で割った値、つまり 1 か月分の利率) で 30 年間 (360 か月) を基にしています。
=PMT(0.05/12,360,20000000)
ここに、ワークシートに入力できる数式について、その他の例をいくつか示します。
-
=A1+A2+A3 セル A1、A2、A3 の値を加算します。
-
=SQRT(A1) SQRT 関数を使って A1 の値の平方根を返します。
-
=TODAY() 現在の日付を返します。
-
=UPPER("hello") UPPER ワークシート関数を使って、"hello" という文字列を "HELLO" に変換します。
-
=IF(A1>0) セル A1 に格納されている値が 0 よりも大きいかどうかを調べます。
数式の要素
数式には、関数、セル参照、演算子、定数のいずれか、またはすべてを含めることもできます。
1. 関数: PI() 関数は円周率 (3.142...) の値を返します。
2. セル参照: A2 はセル A2 の値を返します。
3. 定数: 数式に直接入力される数値や文字列の値 (2 など) です。
4. 演算子: ^ (キャレット) 演算子はべき算を実行し、* (アスタリスク) 演算子は乗算を実行します。
数式に定数を使用する
定数とは、計算されない値のことで、常に同じ値のままです。 たとえば、日付 "2008/10/9"、数値 "210"、文字列 "四半期の収益" はすべて定数です。 式および式の結果の値は定数ではありません。 数式に、セル参照ではなく定数を使う場合 (たとえば、=30+70+110)、ユーザーが数式を変更しない限り、結果が変わることはありません。
数式に計算演算子を使用する
演算子は、数式の要素に対して実行する計算の種類を指定します。 計算が処理される順序は既定で決められていますが (一般的な数学の規則に従って計算されます)、かっこを使用してこの既定の順序を変更することができます。
演算子の種類
演算子には、算術演算子、比較演算子、文字列演算子、参照演算子の 4 種類があります。
算術演算子
次に挙げる算術演算子は、加算、減算、乗算、除算などの基本的な計算を実行し、数値を組み合わせて、計算結果として数値を返します。
算術演算子 |
意味 |
使用例 |
+ (正符号) |
加算 |
3+3 |
- (負符号) |
減算 |
3 ~ 1 |
* (アスタリスク) |
乗算 |
3*3 |
/ (スラッシュ) |
除算 |
3/3 |
% (パーセント記号) |
パーセンテージ |
20% |
^ (キャレット) |
累乗 |
3^2 |
比較演算子
2 つの値を比較する場合は、次の比較演算子を使います。 これらの演算子を使って 2 つの値を比較すると、計算結果として論理値 TRUE または FALSE を返します。
比較演算子 |
意味 |
使用例 |
= (等号) |
等しい |
A1=B1 |
> (大なり記号) |
より大きい |
A1>B1 |
< (小なり記号) |
より小さい |
A1<B1 |
>= (より大か等しい記号) |
以上 |
A1>=B1 |
<= (より小か等しい記号) |
以下 |
A1<=B1 |
<> (不等号) |
等しくない |
A1<>B1 |
文字列演算子
文字列演算子のアンパサンド (&) を使って、1 つ以上の文字列を連結 (結合) して、1 つの文字列を作成します。
文字列演算子 |
意味 |
使用例 |
& (アンパサンド) |
2 つの文字列を結合、または連結して、1 つの連続する文字列の値を作成する |
"北"&"風" の結果は "北風" |
参照演算子
次に挙げる参照演算子は、計算するセル範囲を結合します。
参照演算子 |
意味 |
使用例 |
: (コロン) |
セル範囲の参照演算子。指定した 2 つのセル参照と、その間に含まれるすべてのセルによって構成される 1 つの参照を作成する |
B5:B15 |
, (カンマ) |
複数の範囲を表す参照演算子。複数の参照を 1 つの参照に結合する |
SUM(B5:B15,D5:D15) |
(スペース) |
共通部分を示す参照演算子。2 つの参照に共通する 1 つのセル参照を作成する |
B7:D7 C6:C8 |
Web 用 Excel が数式で操作を実行する順序
計算が実行される順序が数式の戻り値に影響することがあるため、演算の実行順序の決定方法、および必要な結果を得るための実行順序の変更方法を理解しておくことは重要です。
計算の順序
数式では、特定の順序で値が計算されます。 数式は、常に等号 (=) で開始します。 Web 用 Excel は、等号に続く文字を数式として解釈します。 等号の後に、定数やセル参照など、計算対象の要素 (オペランド) が続きます。 これらの要素を演算子で区切ります。 Web 用 Excel は、数式内の各演算子の特定の順序に従って、左から右に数式を計算します。
演算子の優先順位
1 つの数式で複数の演算子を組み合わせる場合、 Web 用 Excel は次の表に示す順序で操作を実行します。 数式に同じ優先順位の演算子が含まれている場合 (たとえば、数式に乗算演算子と除算演算子の両方が含まれている場合) Web 用 Excel 、演算子が左から右に評価されます。
演算子 |
説明 |
: (コロン) (スペース 1 つ) , (カンマ) |
参照演算子 |
– |
負の値 (-1 など) |
% |
パーセンテージ |
^ |
累乗 |
* および / |
乗算または除算 |
+ または - |
加算または減算 |
& |
文字列の結合または連結 |
= |
比較 |
かっこの使用
計算順序を変更するには、最初に計算を実行する必要のある数式の要素をかっこで囲みます。 たとえば、次の数式では加算前に乗算 Web 用 Excel 実行されるため、11 が生成されます。 つまり、この数式ではまず 2 と 3 が乗算され、その結果に 5 が加算されます。
=5+2*3
これに対し、かっこを使用して構文を変更する場合、 Web 用 Excel は 5 と 2 を加算し、結果に 3 を乗算して 21 を生成します。
=(5+2)*3
次の例では、数式の最初の部分を囲むかっこは、最初に B4+ 25 を計算し、その結果をセル D5、E5、F5 の値の合計で除算する Web 用 Excel 。
=(B4+25)/SUM(D5:F5)
数式に関数およびネストした関数を使用する
関数とはあらかじめ定義された数式のことで、引数と呼ばれる特定の値を使い、特定の順序 (構造) に従って計算を行います。 関数を使うと、単純な計算だけでなく複雑な計算も行うことができます。
関数の書式
関数の書式を説明するため、ROUND 関数を使ってセル A10 の数値を四捨五入する例を以下に示します。
1. 構造体。 関数の構造は、等号 (=) で始まり、その後に関数名、開始かっこ、コンマで区切られた関数の引数、および終了かっこが続きます。
2. 関数名。 利用できる関数のリストを表示するには、セルをクリックし、Shift キーを押しながら F3 キーを押します。
3. 引数。 引数には、数値、文字列、論理値 (TRUE、FALSE など)、配列、エラー値 (#N/A など) またはセル参照を使用できます。 有効な値を返す引数を指定する必要があります。 定数、数式、または他の関数を引数として渡すこともできます。
4. 引数のヒント。 関数を入力すると同時に、書式と引数に関するヒントが表示されます。 たとえば、「=ROUND(」と入力すると、ヒントが表示されます。 組み込み関数の場合にのみ、ヒントが表示されます。
関数を入力する
関数を含む数式を作成するときは、[関数の挿入] ダイアログ ボックスを使ってワークシート関数を入力することができます。 数式に関数を入力すると、関数の名前、各引数、関数と引数の説明、その時点での関数の計算結果、およびその時点での数式全体の計算結果が [関数の挿入] ダイアログ ボックスに表示されます。
数式オートコンプリートを使うと、数式の作成や編集を簡単に行って、入力ミスや構文エラーを最小限にできます。 = (等号) と開始文字または表示トリガーを入力すると、 Web 用 Excel 、有効な関数、引数、および文字またはトリガーに一致する名前の動的ドロップダウン リストがセルの下に表示されます。 このドロップダウン リストから任意のアイテムを数式に挿入できます。
関数をネストする
ある関数を、別の関数の引数として使用することもできます。 たとえば、次の数式は、ネストした AVERAGE 関数を使って、その結果を値 50 と比較します。
1. AVERAGE 関数と SUM 関数が IF 関数内にネストされています。
有効な戻り値 ネストした関数を引数として使用する場合は、ネストした関数が返す値の型と引数の型が同一にする必要があります。 たとえば、引数が TRUE または FALSE の値を返す場合は、TRUE または FALSE の値を返す関数をネストすることができます。 関数が表示されない場合は、#VALUEが表示 Web 用 Excel ! というエラー値が表示されます。
ネスト レベルの制限 数式には、7 レベルまでのネストした関数を含めることができます。 ある関数 (これを関数 B とします) を別の関数 (これを関数 A とします) の引数として指定する場合、関数 B は、第 2 レベルの関数として機能します。 たとえば、AVERAGE 関数と SUM 関数は IF 関数の引数として指定されている場合、第 2 レベルの関数となります。 さらに、ネストした AVERAGE 関数にネストする関数があった場合、その関数は第 3 レベルの関数になります。
数式にセル参照を使用する
参照は、ワークシート上のセルまたはセル範囲を識別し、数式で使用する値またはデータを検索する場所を Web 用 Excel に指示します。 参照を使うと、ワークシート上の異なる場所に格納されている複数のデータを 1 つの数式内で使用したり、ある 1 つのセルの値を複数の数式で使用したりすることができます。 また、同じブックにある他のシートのセルや、他のブックにあるセルの参照を行うこともできます。 他のブックにあるセルの参照は、リンクまたは外部参照と呼びます。
A1 参照形式
<c0>既定の参照形式</c0>既定では、A1 参照形式が使用されます。 既定では、 Web 用 Excel は A1 参照スタイルを使用します。これは、文字を含む列 (A ~ XFD、合計 16,384 列) を参照し、数値 (1 ~ 1,048,576) の行を参照します。 これらの文字と数字は行見出しおよび列見出しと呼ばれます。 セルを参照するには、列の文字に続けて行の数字を入力します。 たとえば、B2 は行 B と列 2 が交差する位置にあるセルを参照します。
参照セル (またはセル範囲) |
入力する文字列 |
列 A、行 10 のセル |
A10 |
列 A、行 10 ~ 行 20 のセル範囲 |
A10:A20 |
行 15、列 B ~ 列 E のセル範囲 |
B15:E15 |
行 5 のすべてのセル |
5:5 |
行 5 ~ 10 のすべてのセル |
5:10 |
列 H のすべてのセル |
H:H |
列 H ~ J のすべてのセル |
H:J |
列 A ~ E、行 10 ~ 20 のセル範囲 |
A10:E20 |
他のワークシートを参照する 次の例では、AVERAGE ワークシート関数を使って、同じブック内の "マーケティング" という名前のワークシートのセル範囲 B1:B10 の平均値が計算されます。
1. "マーケティング" という名前のワークシートを参照する
2. B1 から B10 までのセル範囲を参照する
3. ワークシート参照をセル範囲参照から切り離す
絶対参照、相対参照、複合参照の違い
相対参照 数式の中の相対セル参照 (たとえば A1) は、数式が入力されているセルと参照されるセルの相対位置に基づいています。 数式を含むセルの位置を変更した場合、参照も変更されます。 数式を他の行や列にコピーまたはオートフィルによりコピーした場合、参照も自動的に調整されます。 既定では、新しく作成した数式には相対参照が使われます。 たとえば、セル B2 にある相対参照をセル B3 にコピーまたはオートフィルによりコピーすると、=A1 は自動的に =A2 に変更されます。
絶対参照 $A$1 などの絶対セル参照を数式で使用すると、必ず特定の位置にあるセルが参照されます。 数式を含むセルの位置を変更しても、絶対参照で参照するセルは変更されません。 その数式を他の行や列にコピーまたはオートフィルによりコピーしても、絶対参照は変わりません。 既定では、新しく作成した数式には相対参照が使用されるため、絶対参照を使用するには切り替えが必要になる場合があります。 たとえば、セル B2 にある絶対参照をセル B3 にコピーまたはオートフィルによりコピーした場合、どちらの参照も =$A$1 になります。
複合参照 複合参照では、絶対列参照と相対行参照、または絶対行参照と相対列参照を組み合わせて使用します。 絶対列参照は、$A1 や $B1 のように指定します。 また、絶対行参照は、A$1 や B$1 のように指定します。 数式を含むセルの位置を変更した場合、相対参照は変更されますが、絶対参照はそのままです。 数式を他の行や列にコピーまたはオートフィルによりコピーした場合も、相対参照は自動的に調整されますが、絶対参照はそのままです。 たとえば、複合参照をセル A2 から B3 にコピーまたはオートフィルによりコピーすると、数式の =A$1 は =B$1 に変更されます。
3-D 参照スタイル
複数のワークシートを簡単に参照する ブック内の複数のワークシートの同一セルまたは同一セル範囲のデータを分析する場合は、3-D 参照を使います。 3-D 参照では、ワークシート名の範囲の後にセル参照またはセル範囲の参照を指定します。 Web 用 Excel では、参照の開始名と終了名の間に格納されているすべてのワークシートが使用されます。 たとえば、3-D 参照を使用して「=SUM(Sheet2:Sheet13!B5)」という数式を入力すると、Sheet 2 から Sheet 13 までのすべてのワークシートのセル B5 の値が合計されます。
-
3-D 参照を使うと、他のシートのセルを参照したり、名前を定義したり、次のような関数を使用して数式を作成したりすることができます。使用できる関数には、SUM、AVERAGE、AVERAGEA、COUNT、COUNTA、MAX、MAXA、MIN、MINA、PRODUCT、STDEV.P、STDEV.S、STDEVA、STDEVPA、VAR.P、VAR.S、VARA および VARPA があります。
-
3-D 参照は、配列数式では使用できません。
-
3-D 参照は、共通部分の演算子 (スペース 1 つ) の引数として使用したり、共通部分を使用する数式内で使用したりすることはできません。
ワークシートを移動、コピー、挿入、または削除した場合 次の例は、3-D 参照に含まれるワークシートの移動、コピー、挿入、削除を行ったときの結果を示しています。 この例では、ワークシート 2 ~ 6 (Sheet2 ~ Sheet6) のセル A2 ~ A5 を合計する数式 =SUM(Sheet2:Sheet6!A2:A5) を使います。
-
挿入またはコピー シート 2 とシート 6 (この例のエンドポイント) の間にシートを挿入またはコピーする場合、計算で追加されたシートからセル A2 ~ A5 のすべての値が Web 用 Excel に含まれます。
-
削除 シート 2 とシート 6 の間でシートを削除すると、その値が計算から削除 Web 用 Excel 。
-
移動 シートをシート 2 とシート 6 の間から参照元のシート範囲外の場所に移動すると、その値が計算から削除 Web 用 Excel 。
-
エンドポイントを移動 シート 2 またはシート 6 を同じブック内の別の場所に移動すると、シート間の新しい範囲に合わせて計算が調整 Web 用 Excel 。
-
エンドポイントを削除 Sheet2 または Sheet6 を削除すると、シート間の範囲に合わせて計算が調整 Web 用 Excel 。
R1C1 参照スタイル
ワークシートの行と列の両方に番号を指定する参照形式を使うこともできます。 R1C1 参照形式は、マクロを使って行と列の位置を計算する場合に便利です。 R1C1 スタイルでは、 Web 用 Excel はセルの位置を示し、その後に "R" が続き、行番号が "C" の後に列番号が続きます。
参照 |
意味 |
R[-2]C |
同じ列で 2 行上のセルを表す相対参照 |
R[2]C[2] |
2 行下で 2 列右のセルを表す相対参照 |
R2C2 |
行 2 の列 2 にあるセルを表す絶対参照 |
R[-1] |
アクティブ セルの 1 つ上の行全体を表す相対参照 |
R |
アクティブ セルのある行を表す絶対参照 |
マクロを記録すると、 Web 用 Excel は R1C1 参照スタイルを使用して一部のコマンドを記録します。 たとえば、[ AutoSum ] ボタンをクリックしてセル範囲を追加する数式を挿入するなど、コマンドを記録する場合 Web 用 Excel 、A1 スタイルではなく R1C1 スタイルを使用して数式を記録します。
数式に名前を使用する
定義された名前を作成して、セル、セルの範囲、数式、定数、または Web 用 Excel テーブルを表すことができます。 名前は、見ただけでは内容を把握するのが難しいセル参照、定数、数式、テーブルなどの用途を簡単に理解できるようにするための、わかりやすい簡潔な表現です。 以下は、名前の一般的な例を示しており、名前を数式で使用することによって数式の用途が明確かつ理解しやすくなっていることがわかります。
種類 |
名前の代わりに範囲を使用する例 |
名前を使用する例 |
参照 |
=SUM(A16:A20) |
=SUM(売上) |
定数 |
=PRODUCT(A12,9.5%) |
=PRODUCT(価格,KCTaxRate) |
数式 |
=TEXT(VLOOKUP(MAX(A16,A20),A16:B20,2,FALSE),"yyyy/m/dd") |
=TEXT(VLOOKUP(MAX(売上),売上情報,2,FALSE),"yyyy/m/dd") |
表 |
A22:B25 |
=PRODUCT(価格,Table1[@Tax Rate]) |
名前の種類
作成および使用できる名前にはいくつかの種類があります。
定義された名前 セル、セル範囲、数式、または定数値を表す名前です。 定義された名前は独自に作成できます。 また、 Web 用 Excel は、印刷領域を設定する場合など、定義された名前を作成することがあります。
テーブル名 Web 用 Excel テーブルの名前。レコード (行) とフィールド (列) に格納されている特定のサブジェクトに関するデータのコレクションです。 Web 用 Excel では、 Web 用 Excel テーブルを挿入するたびに、"Table1"、"Table2" などの既定の Web 用 Excel テーブル名が作成されますが、これらの名前を変更してわかりやすい名前にすることができます。
名前の作成と入力
選択から名前を作成するを使用して 名前を作成します。 ワークシートの選択セルから、既存の行および列のラベルに基づいて名前を作成できます。
注: 既定では、名前には絶対参照が使われます。
次のような方法で名前を入力します。
-
入力 数式に対する引数などとして、名前を直接入力します。
-
数式オートコンプリートの使用 有効な名前が自動的に一覧表示される [数式オートコンプリート] ボックスを使います。
数式に配列数式および配列定数を使用する
Web 用 Excel 配列数式の作成はサポートされていません。 Excel デスクトップ アプリケーションで作成した配列数式の結果は表示できますが、それらを編集または再計算することはできません。 Excel デスクトップ アプリケーションをお持ちの場合は、[Excel で開く] をクリックして配列を操作してください。
次の配列の例では、株価と株数の配列の集計値を計算していますが、各セルの行を使用した銘柄ごとの値の計算や表示は行っていません。
配列数式として数式 ={SUM(B2:D2*B3:D3)} を入力すると、各銘柄の株数と株価を乗算した後、その計算結果が合算されます。
複数の結果を計算する ワークシート関数には、値の配列を返すものや、引数として値の配列が必要なものがあります。 配列数式を使って複数の結果を計算するには、配列引数と同じ数の行および列を持つセル範囲に配列を入力する必要があります。
たとえば、3 か月の月 (列 A) ごとの売上高 (列 B) を入力して TREND 関数を使うと、売上高の直線方式での値を返すことができます。 式のすべての結果を表示するには、列 C の 3 つのセル (C1:C3) に配列数式を入力します。
配列数式として数式「=TREND(B1:B3,A1:A3)」を入力すると、3 つの売上高と 3 つの月から 3 つの別々の結果 (22196、17079、11962) が返されます。
配列定数を使用する
通常の数式には、値を含むセル参照か、または値そのものを表す定数を入力することができます。 同様に配列数式では、配列への参照か、またはセル内に含まれる値の配列を表す配列定数を入力することができます。 配列数式では、配列数式以外の数式と同様に、定数を処理しますが、特定の形式に従って配列定数を入力する必要があります。
配列定数には、数値、文字列、論理値 (TRUE、FALSE など)、またはエラー値 (#N/A など) を格納できます。 異なる種類の値を同じ配列定数に格納できます (たとえば、{1,3,4;TRUE,FALSE,TRUE})。 配列定数の数値として、整数、小数、または指数を格納できます。 文字列は、"Tuesday" のように二重引用符で囲む必要があります。
配列定数では、セル参照、長さが異なる行または列、数式、特殊文字である $ (ドル記号)、かっこ、% (パーセント記号) を使用することはできません。
配列定数を指定する場合は、次の点にご注意ください。
-
配列定数は、中かっこ ({ } ) で囲みます。
-
異なる列の値はカンマ (,) で区切ります。 たとえば、値 10、20、30、40 を表すには、「{10,20,30,40}」と入力します。 この配列定数は、1 × 4 配列と呼ばれ、1 行 × 4 列のセル範囲を参照するのと同じ働きをします。
-
異なる行の値はセミコロン (;) で区切ります。 たとえば、ある行の値 10、20、30、40 とそのすぐ下の行の値 50、60、70、80 を表すには、2 × 4 配列の配列定数「{10,20,30,40;50,60,70,80}」を入力します。