Excel基礎1：Excel関数の紹介
概要
Excelで使用する関数を紹介します。今回はマクロ機能は使用しないため拡張子は".xlsm"でなくても使用できます。
１．基礎操作
１－１．文字列の結合
文字、数値の結合は"&"で処理できます。
２．計算
たまに使うけど忘れる計算式を記載します。（詳細は追って）
２－１．線形関数（y=ax + b）：SLOPE, INTERCEPT
線形関数での切片と傾きを取得する関数は下記の通りです。
[In]
=SLOPE(y値の範囲, x値の範囲)
=INTERCEPT(y値の範囲, x値の範囲)
[Out]
下図
参考で近似曲線の追加より切片・傾きの値が正しいことを確認しました。
２－２．掛け算
ここでは下記3つの関数を紹介します。
●PRODUCT：指定したセルの値をすべて掛け算した値を返す。
●SUMPRODUCT：線形代数のアダマール積を返す。
●SUMXMY2：各値の差分の2乗の合計（最小二乗法）
２－２－１．PRODUCT関数
PRODUCT関数は選択した範囲の値をすべて掛け算したものになります。記載方法は下記の通りです。
２－２－２．SUMPRODUCT関数
SUMPRODUCT関数は選択した複数範囲にある数値を配列順にかけ合わせえた値の合計となり、アダマール積と同じです。
$$
S = [1, 2, 3]\bigodot[10, 20, 30] = 1×10 + 2×20 + 3×30 =140
$$
サンプルコードは3つの配列で計算した値を出力しました。
２－２－３．SUMXMY2
SUMXMY2関数は配列ごとの値の差分を2乗した値です。主に最小二乗法での誤差計算などに使用できます。
$$
Q = [1, 2, 3]・[5, 7, 9] = (1-5)^2 + (2-7)^2 + (3-9)^2 = 77
$$
２－３．単位換算(Metric->Imperial)：CONVERT
CONVERT関数は数値の単位換算を計算します。下記などに使用できます。
アメリカの案件でImperial単位をMetric/SI単位に変換
圧力のような単位がたくさんある物性値の確認
使用方法は下記の通りであり、出力値は換算値[変換後単位/変換前単位]をかけた結果と同じになります。
[Cell]
CONVERT(<数値>,<変換前単位>,<変換後単位>)
【単位の参考例】
３．文字列の操作・作成
３－１．数値による文字列作成・変換：CHAR()、CODE()
数値から決められた文字列を出力するのがCHAR()、文字をCHAR()の数値に戻すのがCODE()関数です。私は主に①文字列結合時の改行、②アルファベットの連番作成 に使用します。
３－２．文字列抽出：LEFT, MID, RIGHT
文字列から指定の文字を抽出します。
【文字列抽出関数】
LEFT(参照セル, 左からの文字数) '左から文字列抽出
RIGHT(参照セル, 右からの文字数) '右から文字列抽出
MID(参照セル, 左からの開始位置, 取得したい文字数) '引数2から引数3だけも文字列を抽出
３－３．値の型式変換：TEXT()
「セルの書式設定」を設定して文字列で表示する場合に使用します。
【表示形式の参考例】※セルの書式設定->ユーザー定義->種類からコピペ
●標準書式：G/標準
●3桁ごとにカンマがつく2桁の数値：#,##0.00
●頭に0がつく3桁数値：000 （下図参考例）
●文字列：@
●年月日の日付：yyyy"年"m"月"d"日";@
４．セルの参照
４－１．文字列でセルを参照：INDIRECT()
値参照をする時にセルではなく文字列を使用できます。具体的には下記のようなシーンで使用しています。
●連続値を横に引っ張りたいのに参照元が縦になっているためオートフィルが期待する動きをしない時
●Excelの最新シートを同じ名前で更新する時（通常のセル参照だとシートが消えると#REF!となるため）
参考として前者の処理は下記の通りです（Sheet1は概要参照）。
４－２．セルアドレスを取得
セルの行取得：ROW()、列取得：COLUMN()となります。
[In]
ROW() & COLUMN()
ROW(Cell) & COLUMN(Cell)
[Out]
数式を入れたセルの行番号、列番号
入力セルの行番号、列番号
５．ベクトル・行列計算
Excelでベクトルや行列の計算が可能です。参考としてPythonではNumpyやSympyなどのライブラリを使用すると簡単に計算ができます。
５－１．転置：TRANSPOSE
転置行列を作成する場合はTRANSPOSE関数を使用します。
[CELL]
=TRANSPOSE(<行列の指定>)注意点として「選択したN×M行列とは逆形状の範囲(M×N)を選択したうえで［Ctrl］＋［Shift］＋［Enter］を押す」ことで結果が出力されます。
５－２．内積：MMULT
内積の計算はMMULT関数を使用します。
[CELL]
=MMULT(<行列1の指定>, <行列2の指定>)注意点として「内積の計算で出力される行列形状の範囲を選択したうえで［Ctrl］＋［Shift］＋［Enter］を押す」ことで結果が出力されます。
５－３．逆行列：MINVERSE
指定した行列の逆行列を作成する場合はMINVERSE関数を使用します。
[CELL]
=MINVERSE(<行列の指定>)注意点として「内積の計算で出力される行列形状の範囲を選択したうえで［Ctrl］＋［Shift］＋［Enter］を押す」ことで結果が出力されます。また逆行列が存在しない行列もあるので注意が必要です。
参考までにMMULT関数で内積を計算した結果、出力値が単位行列Iになることを確認しました。
５－４．行列式：MDETERM
指定した行列の逆行列を作成する場合はMDETERM関数を使用します。
[CELL]
=MDETERM(<行列の指定>)
別添 Functionプロシージャ
関数とは入力値があり戻り値があるものです（下図参照）。ExcelではVBAで記載する処理をプロシージャと呼び、主に３種類あります。
【プロシージャ一覧】
●Subプロシージャ：戻り値を持たない処理であり「マクロダイアログ」から呼び出して実行可能です。
●Private Subプロシージャ：戻り値を持たない処理であり「マクロダイアログ」から呼び出しできません（裏側だけで処理させる用）。
●Functionプロシージャ：戻り値を持つ処理であり通常のExcel内でも使用が可能です。
Functionプロシージャを使用すれば自作関数も作成可能です。
参考資料・参考記事
あとがき
全部は書ききれないのでぽつぽつ修正していきたい。
VLOOKUPのエラー集、ポイント
SWITCH関数（IF文との比較）
SUMPRODUCT:２つの指定した歯ににある数値の積和を計算
SUMXMY2:２つの指定した範囲にある数値の差の兵法和を計算
RAND：０以上１以下の乱数を生成
この記事が気に入ったらサポートをしてみませんか？