エクセルを使えるようになろう2

藤井です。
今回は関数を紹介します。

普段使っている関数を列挙しましたが、
組み合わせて応用すれば大抵のことはできると思います。
例えば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 ポアソン分布の確率や累積確率を求める

以上です。

この記事を書いた人

藤井雅浩
藤井雅浩コンサルティング事業部 ウェブコンサルタント
今までの集大成 プロフィールを見る
  • このエントリーをはてなブックマークに追加
  • follow us in feedly

何でもお気軽にお問い合せください。

お問い合せはこちら