雑・Excel入門試論 - 脱VLOOKUPの思考 08 - セル - 計算式・関数

Last updated at 2022-12-27Posted at 2022-12-07

計算式の基本

数式

セル内または、セル範囲の値を計算する式。
ex. =A1+A2+A3+A4

関数

Excelにすでに用意されている定義済みの計算式です。

関数は、引数またはパラメータと呼ばれる指定された値に基づいて、特定の計算を特定の順序で実行する。
ex. =SUM(A1:A4)

関数には戻り値があります。=1+2 の戻り値は 3 です。

セル参照をする場合、=A1 の戻り値は、A1に入っているデータと書式に依存します。空白だった場合、日付はシリアル値０の1900年1月0日、それ以外は０が表示され、表示形式はそれぞれの書式で表示されます。下の図のB列はそれぞれの書式設定が行われており、C列は標準のままです。文字列にも０が返されることに注意してください。

セルの参照

　計算したい数値を数式に入力することもできますが、代わりにセル参照機能を使うケースが多くなります。なぜなら、セルの値を変更すると、自動的に再計算されるからです。

　単に数式に値を入力したのでは、後で値が変わると、数式を作り直さなければなりません。しかし、セル参照すれば、値が変わっても数式は変わらず、自動的に再計算されます。

　また、数値が並んだ表になっている場合、計算式をコピーすると自動的に参照先を変更してくれ、同じ計算をそれぞれの行に対して行うことができます。

注意事項

BODMAS (Brackets, Division, Multiplication, Addition, Subtraction)のルール
- 計算が行われる順序です。
- カッコで囲まれた式が最初に評価されます。
- 算術演算子は、除算が最初に評価され、次に乗算、そして加算が評価され、減算は最後に評価されます。
計算式は通常数値データを扱います。「データの入力規則」を利用すると、セルに入るべきデータの種類を指定できます。
数式で参照するセルのアドレスが正しいかどうかを確認するには、キーボードのF2キーを押します。数式で使用されているセルがハイライトされます。

IF関数

IF関数は、複雑な計算に使用できますが、IF式のネストは悪夢です。計算式を読みにくくし、全体の動作を遅くします。

=IF(C4>5,40*C4,20*C4)

その他の動作を遅くする関数として、SUMIF、COUNTIF、VLOOKUP、INDIRECT、OFFSETなどがあります。特にテーブルが大きい場合、関数の使い方に注意しなければなりません。

LAMBDA（ラムダ）でユーザー定義関数

セルにLAMBDAを設定する

　Microsoft365では、ユーザーが関数を定義するためにLAMBDA関数が使用できます。
　小数点以下を銀行丸めする式を書くと、以下のようになります。

LAMBDAなし

=IF(
    MOD(ABS(A2),1)=0.5,
    EVEN(ABS(A2)-0.5)/SIGN(A2),
    ROUND(A2,0)
)

　これをLAMBDAを使って書くと

LAMBDAあり

=LAMBDA(
    n,
    IF(
        MOD( ABS(n), 1 ) = 0.5,
        EVEN( ABS(n) - 0.5 ) / SIGN(n),
        ROUND( n, 0 )
    )
 )(A2)

　このように、参照先を1か所にできます。これを小数点以下の桁数指定の関数に拡張すると、以下のようになります。

LAMBDA桁数付き

=LAMBDA(n,digit,
    IF(
        MOD( ABS(n) * 10 ^ digit, 1 ) = 0.5,
        EVEN( ABS(n) * 10 ^ digit - 0.5 ) / 10 ^ digit * SIGN(n),
        ROUND( n, digit )
    )
)( A2, 1 )

名前の管理にLAMBDAを追加する

LAMBDA 関数にわかりやすい名前を付け、説明を指定し、ブック内の任意のセルから再利用できるようにします。


名前	LAMBDA関数につける名前
スコープ	関数の使える範囲。ワークブックまたはシート
コメント	関数の説明
参照範囲	ラムダ式を入力します。最期のパラメータを入れるカッコは入力しません

参照範囲に入れるLAMBDA式は、最期のパラメータを入れるカッコを付けません。
=LAMBDA(x,x*10)(A1)
ではなく
=LAMBDA(x,x*10)
です。

これで、自分で作成した関数を名前で呼び出すことができます。

エラー値

エラー	ERROR.TYPE	内容
#NULL!	1	指定した複数のセルやセル範囲に共通部分がない
#DIV/0!	2	0または空のセルによる除算
#VALUE!	3	入力した数式に問題があるか、参照先のセルに問題があります
#REF!	4	数式が無効なセルを参照している
#NAME?	5	数式名にが正しくない
#NUM!	6	数式または関数に無効な数値が含まれている
#N/A	7	数式で参照の対象が見つからない (Not Applicable)
#GETTING_DATA	8	CUBEVALUE 関数を使用して評価する場合、すべてのデータを取得する前に、一時的にセル内に "#GETTING_DATA..." というメッセージが表示されます。
#SPILL!	9	スピルする配列数式がワークシートの範囲を超えている
#CONNECT!	10
#BLOCKED!	11	必要なリソースにアクセスできない
#UNKNOWN!	12
#FIELD!	13	データ型に問題がある
#CALC!	14	計算式が空の配列
#EXTERNAL!	19
#####	-	列の幅がセルのすべての内容を表示するのに十分でない

コンテンツ

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up