今回は関数を紹介します。
普段使っている関数を列挙しましたが、
組み合わせて応用すれば大抵のことはできると思います。
例えばMod関数なんかは余りを出すだけですが、
ナンバリングされた列をN個ずつ分類したいときなんかに意外に便利です。
N=3
| No | Mod(3) | (No-Mod(3))/3 |
| 0 | 0 | 0 |
| 1 | 1 | 0 |
| 2 | 2 | 0 |
| 3 | 0 | 1 |
| 4 | 1 | 1 |
| 5 | 2 | 1 |
| 6 | 0 | 2 |
| … | … | … |
また、SumproductやVlookupなどは、量が多いと非常に重くなるので注意が必要です。
集計
基本ですが、条件付きを覚えるとピボットテーブルもいらなくなります。
ローデータ貼り付けるだけになります。
| SUM | 数値を合計する |
| SUMIF | 条件を指定して数値を合計する |
| SUMIFS | 複数の条件を指定して数値を合計する |
| SUMPRODUCT | 配列要素の積を合計する |
| ROUND | 指定した桁数で四捨五入する |
| ROUNDDOWN | 指定した桁数で切り捨てる |
| ROUNDUP | 指定した桁数で切り上げる |
| MOD | 余りを求める |
日付時刻
レポート（主にデイリー）を作る時は必須じゃないかと思います。
うまく組み合わせれば月末曜日の取得やうるう年の処理も可能です。
| TODAY／NOW | 現在の日付、または現在の日付と時刻を求める |
| DATE | 年、月、日から日付を求める |
| YEAR | 日付から「年」を取り出す |
| MONTH | 日付から「月」を取り出す |
| DAY | 日付から「日」を取り出す |
| WEEKDAY | 日付から曜日を取り出す |
論理
分岐やロジックなので、復数の関数と一緒に使うことが多いでしょうか。
見た目を良くするためIferrorはよく使います。
| IF | 条件によって利用する式を変える |
| IFS | 複数の条件を順に調べた結果に応じて異なる値を返す |
| AND | すべての条件が満たされているかを調べる |
| OR | いずれかの条件が満たされているかを調べる |
| IFERROR／IFNA | エラーの場合に返す値を指定する |
検索/行列
集計や確認作業をする人で、Vlookを知らない人はいないんじゃないかってくらい使います。
縦ができるなら横もできるだろ、と思いますが、意外にHlookの知名度は低いです。
キーで引っ掛ける系ならIndex-Matchの方がスマートな気もします。
| VLOOKUP | 範囲を縦方向に検索する |
| HLOOKUP | 範囲を横方向に検索する |
| LOOKUP | 1行または1列の範囲を検索する |
| INDEX | 行と列で指定した位置の値を求める |
| MATCH | 検査値の相対位置を求める |
| COLUMN | セルの列番号を求める |
| ROW | セルの行番号を求める |
| INDIRECT | 参照文字列をもとにセルを間接参照する |
文字列操作
文字列の切り抜きや置換でよく使います。
Trim関数はリスティング広告やってる人くらいしか使わない気もします。
LenとLenbの違いですが、Lenは全角、Lenbが半角、程度の認識です。
| LEN／LENB | 文字列の文字数またはバイト数を求める |
| LEFT／LEFTB | 左端から何文字かまたは何バイトかを取り出す |
| RIGHT／RIGHTB | 右端から何文字かまたは何バイトかを取り出す |
| FIND／FINDB | 文字列の位置またはバイト位置を調べる |
| SUBSTITUTE | 検索した文字列を置き換える |
| CONCATENATE | 文字列を連結する |
| TRIM | 余計な空白文字を削除する |
| CODE／UNICODE | 文字コードを調べる |
| CHAR／UNICHAR | 文字コードに対応する文字を返す |
統計
平均なんかは実用的には集計に入れてもいいと思いますが、一応統計関数ということで。
AIを入れなくてもトレンド分析程度ならエクセルで十分なんですよね。
外れ値をAIが勝手に除外してくれるようになってくれたらいいのですが。
| COUNT／COUNTA | 数値や日付、時刻またはデータの個数を求める |
| COUNTIF | 条件に一致するデータの個数を求める |
| COUNTIFS | 複数の条件に一致するデータの個数を求める |
| AVERAGE／AVERAGEA | 数値またはデータの平均値を求める |
| AVERAGEIF | 条件を指定して数値の平均を求める |
| AVERAGEIFS | 複数の条件を指定して数値の平均を求める |
| MAX／MAXA | 数値またはデータの最大値を求める |
| MIN／MINA | 数値またはデータの最小値を求める |
| MEDIAN | 数値の中央値を求める |
| MODE.SNGL／MODE | 数値の最頻値を求める |
| STDEV.P／STDEVP | 数値をもとに標準偏差を求める |
| FORECAST | 回帰直線を使って予測する |
| LINEST | 重回帰分析により係数や定数項を求める |
| CORREL／PEARSON | 相関係数を求める |
| POISSON.DIST／POISSON | ポアソン分布の確率や累積確率を求める |
以上です。
