Excel操作のコツ（5）

ひろです。Excel操作のコツも終盤に入ってきましたね。

～ここから過去の記事～

さて、今回はいったん休憩として、便利なので覚えておくと有用な数式を紹介しておきます。

とは言っても、数式だけを紹介しておけば、詳細な使い方は他サイトや書籍にお任せできるかと思うので（笑）、「こんな時にこういう数式があるよ」と紹介するに留めておきたいと思います。

各種統計データを算出しよう！
sum：合計値を出します
sumproduct：積の和を出します
sumif/sumifs：条件に見合う時だけ合計します
median：中央値を出します
average：平均値を出します
max：最大値を出します
min：最小値を出します
aggregate：様々な統計データを、様々な設定で出します

もう「頻出」としか言いようがないような数式群です。

ただ、sumで合計値を算出すると、たとえば「A事業の内訳製品について、後から行を追加」等をした場合に、下図のようにsum結果が変わってしまうようになります。なので、こういう場面では面倒でも「+」で1つずつ加算していく方法がお勧めです。

「特定の位置」（日付等）を指定した上で、そこからX行／X列ずれた位置にあるセルへリンクしたい！
vlookup：vertical（縦）に特定の数値・文字を検索した後、X列ずれた位置にあるセルへリンクします
hlookup：horizontal（横）に特定の数値。」文字を検索した後、X行ずれた位置にあるセルへリンクします
offset、match、及びその組み合わせ：行・列ともに特定の数値・文字を検索し、その位置にあるセルへリンクします（vlookup/hlookupと違い、固定された「X」列/行ずれるわけではないので、行/列が増えたとしてもずれない）（vlookup/hlookupにも、row/column関数を活用することで行/列が増加しても自動的に「X」もずれるように設定もできるが、応用編となる）

たとえば日付データがずらーっと並んでいるところで、特定の日付の数値をとってきたい時等に使用しますね。

事業計画でケースを複数作成したい！
choose：1,2,3...の番号に基づいて、使用する数値を変更する

この使い方は、追って少々詳しめに紹介しようと思います。

条件で分岐させたい！
if：条件で分岐させます
iferror：エラーが出る場合に特定の処理を行うようにします
n：内容がtrueであれば1を返し、falseであれば0を返します

まあ、頻出の数式かと思います。
n関数は一風変わった方法になりますが、条件に応じて「特定の値または0」という場合に、すっきりとした数式にすることが可能です。
if関数だと「if(条件, 特定の値, 0)」ですが、n関数だと「n(条件)×特定の値」で終わります。

条件分岐で、複数の条件を設定したい！
and：複数の条件すべて同時に満たすかをチェックします
or：複数の条件のどれか1つでも満たすかをチェックします

使い方次第で、if(...if(...if(...と、何度もifを使うよりもぐっと簡潔になる場合があります。

リンク元を特定の書式で表示させたい！
text：特定の書式で表示します

リンク元の数値を異なる書式で表示したい場合に使います。
たとえば「=“as of ”&today()」とだけ入力すると、表示は「as of 42776」となってしまいます。「42776」は日付データを数値で表示したもので、これでは意味が全くわかりません。
そこで、「=“as of ”&text(today(), “yyyy/mm/dd”)」としておくと、綺麗に「as of 2017/02/10」と表示されるようになります。
このように、文字を組み合わせて1つのセルに表示させたいような場合には、text関数を活用することになります。

なお、「as of 」にある「“”」は文字としての入力であること、「&」は前後の内容を繋げて表示すること、をそれぞれ意味しています。

複利のリターンを計算しよう！
rate：複利のリターンを計算できます
irr：同上
xirr：同上ですが、日付のタイミングも選択できるので、より厳密な計算もできるようになります

お勧めはxirrですね。非常に便利です。

「0」ではなくエラー（存在しない）として扱いたい！
na：エラー値である「#N/A」として取り扱います

na関数は、「na()」としか入力しません。「括弧内に何等の入力をしない前提」という、excelでは珍しい関数です。
ひろはiferrorと一緒に使うことが多いです。na()とすれば、「0」とは違う扱いになるので、下図のようにグラフにした場合に違いが出てきます。

この場合は、単純に初年度の成長率データを削除して、何も入力しなければ右側と同じグラフになりますが、たとえば「途中の年度だけデータがない」等、いちいち目で見てやっていられないような場合も多々あるかと思います。
そんな時は、データがない場合は「na()」となるようif等で条件設定をしておくと、自動で該当箇所を抜かしたグラフを作成することが可能になります。

1年後/半年後/3か月後/1か月後の、月末の日付を確実にとってきたい！
eomonth：指定の月分だけ後/前の月の、月末を指定します

私の場合は、12か月後にすることで、企業の決算期を簡単に入力していく場合に頻繁に使用します。

数式を活用して文字を置換したい！
substitute：文字を置換します

たとえば私の場合は、各種データベースからexcelに落とした時に、「●●株式会社」とあるところの「株式会社」を空欄に置換することで、会社名をすっきりとさせて表示させたりしています。

セルの特定の位置にある数値をとってきたい！
left：左から文字等を取得します
right：右から文字等を取得します
mid：中間にある文字等を取得します

たとえば何らかのデータベースからとったものを、一律に自らの好きな形で処理したいような時に使っていますかね。

…ふう。こんな所ですかね。

数式も、多分「意外と使っているけど、ほぼ意識せずに使っているから、改めて言われてもすぐには出てこない」数式が多い気がします。
何か思い出したら追記しますが、やはり全くあてにならない言葉なので（笑）、「きっと他にも便利な数式はたくさんあるんだろう」という目で見てもらえればと思います。
さて次回は今度こそ、応用編をちょっとやっていきます。

～ここまで過去の記事～

ではではまた。