この記事では、Excel動画をまとめてみました。
「予算作成の担当になったけど、どんな関数を活用すればよいか分からない」
「毎週行っている集計作業をどうにか効率化したい」
「今のやり方がベストじゃなさそう。でも、どうすれば良いか」
こんな疑問や課題がある方々へ向けて、イメージとしてはExcel中級者向けて、まとめ記事を書きました。
このブログを辞書として、ブックマークしてくれたら泣いて喜びます。
以前、TwitterでExcel活用動画を流していたのですが、その動画のうち、特に活用していただけそうな21動画を厳選してお届けします。これを読んでいただいた方々には、このブログをある意味「辞書」として活用いただきたいと考えています。ブックマークいただければ泣いて喜びます笑。
その動画集の内容は以下の通りです。
動画を見て覚えなくて良い
まず初めに、この動画を必死になって一つ一つ見て覚える必要はありません。なぜなら、ボクの経験上、ただ眺めても覚えられないからです笑。それよりも「そういえば、なんかこんなのあったなぁーー」とか「確かどこかの誰かがブログで書いてた気がするなぁ」くらいの感じて、頭の片隅に置いておく程度で十分です。そして、必要となった時に、このブログを見返して、活用できそうな動画を見るという感じで良いがします。
実は、昔、Twitterでこのような動画を配信し続けていたんですよね。
これはこれで誰かの役に立つことができたかなと思う一方で、Twitterの特性上リアルタイム性が高く、情報がストックされにくいものです。そのことを踏まえて、今回は情報ストックとなるよう、ブログ形式でまとめましたので、ご参考ください！
ただ、、、一応申し上げると、「基本的な関数の使い方」や「覚えておくべき必須の方法」などを全て網羅したわけではなく、私がTwitterで流してきた動画の一部を抜粋したものを紹介しています。ついては、網羅的ではありませんので、ご容赦ください！笑。
動画集
では、ここから具体的にその動画集を見ていきたいと思います。ぜひご覧ください！
集計
まずは、集計編です！
Month + Sumif
いきなり変化球の紹介です笑。
１）年月日からMonth関数で月を取り出す（青セル）
２）月別にSumif関数で集計する（緑セル）
３）これで日別PV数をもとに月別PV数を算出できる（緑セル）
&で結ぶ
見やすさ重視のためか、ご丁寧にも月次決算PLの科目列がバラバラ…って時はありますよね？
この科目列がバラバラだと、集計するときに使いづらかったりしますよね？ そんなときはこうやって処理すると良いです。
１）セルを参照し & でつなぐ
２）すると文字列が結合し一列にまとまる
３）その一列をSumifとかの検索Keyとして使える！
Large関数で上位から並べる
マスターデータを並べ替えずに、トップ5の販売本数を出したい時はこれ！
1) Large関数を使用
2) 順位を1~5まで書く
3) それを参照する
Small関数で下位から並べる
上位ではなく、下位5部門の販売本数を並べる時にはこっち！
1) Small関数を使用
2) 順位を1~5まで書く
3) それを参照する
＜動画を挿入＞
参照
次に参照編です！
Index＆Match 基礎
Index関数はこれ見たら分かる！ Index関数とは何ぞやを、分かりやすく動画にしてみました。
１）範囲を指定
２）その範囲内の行数を指定 (=青)
３）その範囲内の列数を指定 (=緑)
→ 行と列が交差する数字を返す (=黄)
それぞれ色分けして示したから、動画をぜひ参照してください。これ使いこなせると結構強くなります！
Index&Match 応用編
↑で紹介したIndex&Matchの応用編がこれ！
1) IndexでPL範囲を選択
2) 参照させたい行・列数をMatchで指定
3) 他のセルに数式コピー これで完成。
Index&Matchを覚えると守備範囲が大きく広がります！
VlookupとLeftを組み合わせる
多くの人が知ってるvlookup関数。その検索値にLeft関数を活用する応用編をご紹介。
１）文字列から指定した文字数を取り出すLeft
２）vlookupの検索値にLeftを活用
３）この組み合わせでvlookupの活用の幅広がる！
地味にこういうのも実務で使えたりするんですよねぇ。
Choose関数で参照
続いて、CHOOSE関数。これは、もう超便利なので「覚えなくて良い」とブログの冒頭で言い放ってしまったものの、これだけでも覚えてほしいくらい笑。
「この部門は前年同月実績、あの部門は前月実績、そしてこっちの部門は標準売上をベースに今期予算作るぜ」。ってときに簡単にシナリオ選択できるのが、Choose関数。私が作成した予算ファイルでも、ガッツリ使用しています。ほんと便利なのです！
Find&Mid
顧客IDの一部 (動画▼真ん中にある数字列) を取り出したいなら、こんなのどうでしょうか？
１）FIND関数で数字直前の – の位置を探す
２）その位置を元にMID関数で取り出す
文字列を探す・取り出す関数使えると超パワーアップ！慣れたら、二つの組み合わせ一発入力にトライしたいところです！
日付
続いて日付編！この「日付」や次に紹介する「テキスト」を自由自在に使えるようになると、相当Excelマスターになれると個人的には思っています笑。
Year, Month, Day関数
日付処理に活用できる関数はこの4つ！
１）Year、Month、Day関数で年月日それぞれ取り出し可能
２）Date関数でそれらを日付に戻せる！
この関数を使うことで、前述したMonth関数とSumif関数の組み合わせが活用できますね。他にもYear関数を使って年別集計するなども可能になります。
Datedifで勤続年数を算出する
これは人事部や経営企画の人に使えるかもしれません。
従業員の勤続年数を知りたいときは、この方法が超シンプルです。外部向けの資料作成に必要だった時、このように活用して計算しました。
１）Today関数で今日の日付を入れる
２）Datedif関数で入社日から今日までの年数カウント。年数カウントは “y “ と打ち込む
Datedif関数の他の活用法
Datedif関数の他の活用法としては、オープン初月の営業日数の算出です。少し説明しますと、例えば、5/15にある店がオープンすると、その月は、丸一か月営業ができないため、予算としては、日割り計算することが一般的です。そこで、その日割り計算するための営業日数の算出は店舗ごとに行うと非常に手間なので、関数を用いて一発で出すことができます。
具体的には次の通りです。
１）Datedif関数で日数カウント&算出
２）1日足りないから、＋１する
３）月商予算を日割換算て初月予算完成
Workday関数で請求日を自動設定する
受注日から◯営業日後に請求日を設定したい！って時は、これ。といってもあんまり使えるシーンはないかもしれないですが、Workday関数というものがある、と覚えておいていただけると嬉しいです笑。
１）Workday関数を活用
２）受注日、◯営業日を選択
３）除外する祝祭日リストを選択
◯営業日をポチポチ変えれば、容易に請求日の変更が可能に。祝祭日リストもググれば出てくるのです。
テキスト
日付に加えて、ワンランク上がるのが、テキストに関する関数。
実際に使う場面は限られているかもしれませんが、いざ必要な時に大いに役立ちます。それだけで作業時間を大幅に減らせるので、ぜひ頭の片隅にそっと置いといていただけると良いと思います。
Upper、Lower、Proper関数
この関数を活用できる場面は、地味に頭を悩ませる「固有名詞の表記揺れ」。
英字ならこんなふうに対応できるんです。これで表記揺れを一発で解消できます。
１）Upper : 全部大文字に！
２）Lower : 全部小文字に！
３）Proper : 頭文字だけ大文字に！
Substitute、Trim関数
Substitute関数とTrim関数の組み合わせで、こんなバラバラな文字列も整います。
１) Substituteで全角スペースを半角に
２) Trimで文字列前後のスペース削除
３) これらの組み合わせが緑色列
上記の例では全角スペースを半角に入れ替えるのにSubstitute関数を使用しましたが、何かの文字を別の文字に入れ替えたいときに使えますので、ぜひお試しください。
Jis、Trim関数も表記揺れに使える
「ピボットテーブルを使いたいのにデータにエラーがあるっぽい…。これじゃあマスターデータちゃうやん！」と嘆く前に、こんな風に表記揺れを修正できます。
１）半角→全角の修正はJIS関数(青色)
２）無駄なスペース削除はTRIM関数(ピンク)
３）関数を活用し表記揺れ修正すれば、ピボットテーブルが活用できる
Concatenate関数でテキスト結合
個人的に結構な衝撃を受けたのが、このConcatenate関数。
１）Concatenate関数で文字列としてセル結合
２）データテーブルの情報をまとめたい時に活用
※” “や & の使い方はググってください。
会議資料とかの備考に記載する時とかに使えそうです。
モデリング
続いて、財務モデルを構築する際に活用できる関数やTipsをお伝えしたいと思います。
黒、緑、青の使い分け
誰かとExcelファイルを共有するときは、ぜひやってほしいことがあります。それがこれです。というか、今日はこれだけでも覚えてほしい！（何も覚えなくて良い、と言ったのは誰や）
１）黒字：計算式
２）緑字：他セル参照
３）青字：数字ベタ打ち
誰か他部署の人やチームメンバーにに数字の記入を依頼するときは、その入力セルを青字&黄ハイライトにして、入力箇所が明確になるようにするんです。特にシートがいくつもあるファイルを使っていたり、行・列数がとても多いシートを使用している場合は、依頼を受けた人はどこに入れるのかが分かりやすくなりますし、モデルの構造を理解するのにとても役立ちます。
この使い分けはどうも投資銀行業界の常識らしく、教えてもらったボクは秒速でパクりました笑
Index＆Match 応用編
月毎に適用するPLシナリオを自由に変えたい時。
１）各シナリオを用意
２）IndexとMacthの合わせ技
３）シナリオ名の記入だけで適宜反映
実践では改装予算を作るときに使いました。改装前のPL、改装期間中のPL、改装後のPL.、それぞれ使うPLモデルを変えたいときに、こんな風に活用してみてください。
エラーチェック
Excelで集計したり財務モデルを構築したり、っていうけど、結局は全て手作業なんですよね。全部人が手を動かして、数式作って、数字を入れて完成させてます。
だから、大切なのが、エラーチェックです。その計算は正しいのか、ミスはないのか。その確認を入れることは、間違いなく必須です。「これだけの手作業をしているんだから、間違って当然だよねぇ～」って口が裂けても言えません笑。
ということで、すぐに使えそうなエラーチェックの方法を二つお伝えしたいと思います。
Sumifで数値の一致を確認する
PL作ったけど、計算ミスが心配… って時はこれ！
１）異なる二つの方法で営業利益合計を算出
２）それぞれの計算結果が一致するか確認
３）不一致ならエラー
あくまで一つの確認方法ですが絶対活用できるはずなので、ぜひご覧ください。これに何度助けられたことか笑。
True・Falseを使う
もう一つのやり方がこれ！この数式なら超絶簡単です。予算値の反映確認とかで最近使用しました。
１）比較したいセル同士を” = “で結ぶだけ
２）値が等しければTRUE、等しくなければFALSE
恥ずかしながらこれ、 に入ってから知りました…。でも超絶活用できますので、冒頭で「覚えなくて良い」と言いましたが、覚えてください笑。
財務モデルはこれらの応用
この記事を読んでくれている方の中には、これから初めて財務モデルを作るっていう方もいるかもしれません。財務モデルって聞くと、「げ、なんか難しそう」「そんなのできるかな、やったことないし…」となるかもしれません。私もまさにそうでした。Excelのトレーニングも受けたことないし、実務を通じて覚えていくしかありません。
ただ、一つお伝えできるとすれば、「財務モデルで使う関数は意外にも多くない」ということです。以前こちらのツイートでも書いたのですが、ある案件の財務モデルで使用している関数をリストアップしてみると、たったこれだけ。
少なくはないかもしれないですが、多くはないと思いませんか？
何が言いたいかというと、これらの関数だけで覚えれば十分ということなんです。そして、これらのほとんどをこの記事で紹介しています。なので、あきらめずに一つずつできるようになれば問題ありません！
最後に
とても長い長い記事となりましたが、「情報のストック」として、この記事にまとめようと思い、21選も書かせていただきました。
これから予算作成タスクに立ち向かう方、経営企画チームへ異動することになり、Excel仕事が増えそうな方、日常的に集計業務があり効率化したい方、もっと効率化させる方法はないかと考えている方。そんな方々のお役に立てたのであれば幸いです。
もしよかったら、ぜひこの記事をブックマークいただいて、21選の参考動画を見たいときに見れるようにしておいていただければと思います。
また、他にも↓のような記事を書いたりしてますので、よろしければぜひお目をお通しくださいね。
それではまた！ではでは～！！
コメント