2020/7/30 追記
『Udemy』向けExcelVBA講座を作成しましたので、
ぜひご活用いただければと思います。
関連講座
デスクワーカーなら誰もが使うであろう最強ソフト『エクセル(excel)』。
『エクセル(excel)』には計算を助けてくれる便利関数がたくさんありますが、
その中でも個人的によく使う関数を集めてみることにしました。
『幾何学(きかがく)』 や『統計学(統計学)』 関連でなければ、
これらの関数でほぼほぼカバーできるんじゃないかなと思います。
エクセルでよく使う関数 合計・数
まずは計算。
『エクセル(excel)』といえばとにかく計算を楽に、ですね。
こんな表を元にあれこれやってみます。
エクセルで合計値を出したい sum
王道です。
引数(ひきすう)に合計したい範囲を選びます。
エクセルで平均値を出したい average
こちらもよく使います。
引数(ひきすう)に平均を出したい範囲を選びます。
エクセル四捨五入したい round
消費税計算などで端数が出た時に四捨五入か、繰り上げか、繰り下げかで使います。
第一引数(だいいちひきすう)で数値セルを選びつつ、
第二引数(だいにひきすう)で数値を選んで、処理する桁数を指定します。
| 数値 | 処理対象の位 |
|---|---|
| -2 | 百の位 |
| -1 | 十の位 |
| 0 | 一の位 |
| 1 | 小数点第一位 |
| 2 | 小数点第二位 |
roundは3タイプあってその時々で使い分けます。
使い方はどれも一緒。
- round・・四捨五入
- roundup・・繰り上げ
- rounddown・・繰り下げ
エクセルで個数を数えたい counta
こちらも王道。
引数に個数を数えたい範囲を選びます。
- count・・数値が入ったセルの個数
- counta・・空ではないセルの個数
エクセルで条件をつけたい if
プログラミングでもおなじみの『if文(イフブン)』。
『if文』使うとかなり便利で、『if文』の中に『if文』を書けたりします。
条件を組み合わせる時は、ANDやORを使ったりもします。
=IF(G2>60000,"○","×")
引数は3つで、
- 条件と、
- 真の時の内容と(条件にあう時)
- 偽の時の内容(条件に合わない時)
になります。
2つのデータを比較して、
同じなら ◯、 違うなら× とつけたり、
5000円以上なら◯、という時に使います。
エクセルで条件つきで合計したい sumif
『sum』と『if』の組み合わせ関数で、
商品名毎にまとめた金額を計算する時によく使います。
引数は3つとります。
- 条件と数値を含む範囲
- 条件
- 合計したい範囲
今回はボールの合計を出しています。
エクセルで条件つきで数えたい countif
こちらは『count』と『if』の組み合わせ。
商品名毎にまとめた個数や金額を計算する時によく使います。
引数は2つ。
- 個数を数えたい範囲
- 数えたい文言や数値
エクセルでフィルターかけて合計したい subtotal
フィルターかけて、表示されている商品だけ合計したい時に使います。
『sum』だとフィルターで非表示なものも全部合計されちゃうので。
引数は2つ。
- 集計したい種類
- 範囲
集計したい種類は以下のようにいくつか種類がありますが、
もっぱら109の『sum』ばっかり使ってます。
| 数値 | 集計方法 |
|---|---|
| 101 | average |
| 102 | count |
| 103 | counta |
| 104 | max |
| 105 | min |
| 106 | product (かけ算) |
| 107 | stdev.s 不偏標準偏差 |
| 108 | stdev.p 標本標準偏差 |
| 109 | sum |
| 110 | var.s 不偏分散 |
| 111 | var.p 標本分散 |
エクセルでよく使う関数 日付関連
続いて日付関連の計算。
曜日を表示したり年月を表示したり、あると便利なあれこれです。
エクセルで日付から曜日を表示したい text
何曜日が一番売上多いんだろうといったときに使います。
『text関数』を使うことで表示できます。
引数は2つ。
- 曜日を出したい日付
- “aaa”
“aaa”とうつことで曜日がとれます。
エクセルで年月で表示したい text
年月でまとめると、毎月の売上をグラフにしたりする時に便利です。
『ピポットテーブル』と組み合わせて、何年何月の売り上げなどを
グラフにする時に使います。
こちらも『text』関数で出せます。
引数は2つ。
- 年月を出したい日付
- “yyyymm”
『ピポットテーブル』の関連記事
エクセルで日付と日付の差分を出したい day360組み合わせ
=IF(C2>=D2,0,(DAY360(C2.D2)))
予約日と売上日など、2つの日付の間隔を調べる時に使います。
後で紹介する『vlookup関数』と組み合わせて、
1週間以内だったらAグループ、
1週間〜2週間以内ならBグループなど、
グルーピングに使ったりします。
『DAY360関数』は1年を360として扱う関数で、
微妙に誤差があるようで、
正確な日付の差を出したい場合は工夫が必要です。
エクセルで何週目かを表示したい weeksum組み合わせ
=WEEKNUM(D2)-WEEKNUM(DATE(YEAR(D2),MONTH(D2),1))+1&"週目"
曜日だけで分類すると、
祝日や連休が入った時に普段より売上が増えたりするので、
それを分けたい時に使います。
年月、曜日、何週目の3つの情報が出せれば、
『ピポットテーブル』と組み合わせて、
何年何月、何週目の何曜日ということで、
カレンダー通りの日付毎の売り上げ合計がだせるようになります。
エクセルでグループ分けしたい vlookup
Aグループ、Bグループなどグループ分けをしたい時につかいます。
サンプルとして、
1～9ならAグループ、10～12ならBグループ、という対応表を作成しています。
引数は4つ必要です。
- グループ分けしたい値
- 検索範囲 (別シートでつくった対応表の範囲)
- 対応表のどの列にある値を取り出すか(サンプルの場合 A,Bグループで分けたいので2列目)
- 検索の型・・trueかfalse。1か0。0だとぴったり合わなければエラー
第4引数は0にしておけばいいかなと思います。
おまけ エクセルで文字と文字をつなげたい &
関数ではないんですが、
名字と名前が別のセルでつなげたいときが時々あります。
&でつなげばOKです。
間にスペースを入れたい時は、 &でつなげて書きます。
エクセルでよく使う関数まとめ
実際に仕事で使う時は、
- これらの関数を単独で使う
- これらの関数で元データをつくった後に『ピボットテーブル 』で集計する
- これらの関数を組み込んだVBAマクロをつくる
の3パターンが多いかなと思います。
大量の計算なら『SQL』なり『Python』の出番かなと思いますが、
2万件くらいまでなら『エクセル』だけでどうにかなるかなという印象です。
『ピポットテーブル』の関連記事
『エクセルVBA・分析・統計』関係ではこんな記事も読まれています。
1. 【ExcelVBA】初心者向け講座をリリースしました。2. 【ExcelVBA(マクロ)】(エクセル)よく使う書き方をまとめてみた【随時更新】
3. 【Excel(エクセル)】よく使う関数をまとめてみた(組み合わせもあり)
4. 【ピボットテーブル】の使い方をわかりやすくまとめてみた【初心者向け】
5. 【Excel(エクセル)】確率・統計でよく使う関数をまとめてみた【随時更新】
6. 【分散】【標準偏差】とは わかりやすくまとめてみた【ExcelとPython】
7. 【データドリブンマーケティング】の本を頼りにデータ分析力を高めていくべし【統計学】
8. 【正規分布】とは わかりやすくまとめてみた【ExcelとPython】
9. 【確率】当たりがでる確率を計算する方法【二項分布】【Excel/Python】
10. エクセルマクロで勤務表の自動チェックを依頼され2時間でつくった話
11. 【エクセルvba(マクロ)】でできる事を増やし使える人になったら女の子に「すごぃ！」って言われた時の話
アオキのツイッターアカウント。
この記事へのコメントはありません。