| エクセル関数講座です。 |
| |
| 今回は、よく使う関数をあつかいます。 |
| といっても、それほど多くありません。１０もマスターすれば、そこそこのシステムが作れます。 |
| |
| |
| よく使う関数 |
| |
| ＡＶＥＲＡＧＥ関数……平均を求める
|
| |
| ＭＩＮ関数 ……最小を求める |
| |
| ＭＡＸ関数 ……最大を求める |
| |
| ＡＢＳ関数 ……絶対値を求める
|
| |
| ＣＯＵＮＴ関数 ……数を数える
|
| |
| ＣＯＵＮＴＩＦ関数……特定の条件を満たすセルの数を数える |
| |
| ＳＵＭ関数 ……総和を求める
|
| |
| ＳＵＭＩＦ関数 ……特定の条件を満たす値の総和を求める
|
| |
| |
| これらは、名前を読んだだけで、どんな関数なのか分かると思います |
| |
| セルＦ１０１〜Ｆ１０３の平均を求めたいなと思えば、 =AVERAGE(F101:F103)
とすればいいわけです。 |
| 絶対値がほしいなら、 =ABS(値) とします。 |
| |
| エクセル関数は無理に覚えようとしなくても、使っているうちに自然と覚えていきます。 |
| どんなシステムをこしらえるか、頭をひねっているうちに身につきます。 |
| |
| その関数のなかでも、とりわけ重要なのが、OFFSET関数です。 |
| |
| 平均線を導きだすときに、お世話になる関数です。 |
| |
 |
| |
| このＯＦＦＳＥＴ関数は、結果としてセルの位置を返さなければなりません。 |
| ＯＦＦＳＥＴ（？）＝"あ" |
| というような、ＯＦＦＳＥＴ関数の結果、だされたものが、「あ」という文字になるといったことは、ありません。 |
| |
| ＡＢＳ（ＯＦＦＳＥＴ（？）） |
| のように、「ＯＦＦＳＥＴ関数によって算出されたセルに入っている値を絶対値化せよ」という処理なら使えます。 |
| （この場合、ＯＦＦＳＥＴ関数によって参照されるセルには、数値が入っていなければなりません。文字を絶対 |
| 値であらわすことはできないからです） |
| |
| 次の例は、ＡＶＥＲＡＧＥ関数を使って、３日移動平均を求めたものです。 |
| |
 |
| |
| Ｆ列に平均値を出すようにしました。 |
| Ｆ４のセルに入っている関数は、Ｅ２〜Ｅ４の範囲を平均化しています。つまり、３日移動平均です。 |
| |
| |
| では、ＯＦＦＳＥＴ関数を使って、同じ結果をだすようにしてみましょう。 |
| |
| =AVERAGE(OFFSET(E4,-2,0):E4) |
| |
| この関数をＦ４へ入れてください。 |
| 関数が分かりにくいようなら、簡単な関数に分解して考えます。 |
| |
| 上記の関数は、=AVERAGE(■:E4) のかたちになっています。平均を算出するべき範囲をしていしなければ |
| なりません。 |
| 範囲は、■〜Ｅ４です。 |
| |
| OFFSET関数を使って、この■を求めるには、現在のセルの位置から、いくつさかのぼればいいでしょうか？ |
| ３日平均を出したければ、２日前にさかのぼることになります。したがって、上の行へふたつ分。 |
| 数値で表すと−２です。列は移動させなくてよいですから、０が入ります。 |
| かくして、■には OFFSET(E4,-2,0) が入ると分かりました。 |
| |
| =AVERAGE(■:E4) |
| ↑ |
| OFFSET(E4,-2,0) |
| |
| つまり、 |
| =AVERAGE(OFFSET(E4,-2,0):E4) |
| |
| が求めるべき関数式となります。 |
| |
 |
| |
| ここで何故、OFFSET関数を使うか解説します。 |
| 確かに、AVERAGE関数を使えば、平均値は求められます。ところが、３日平均、４日平均……と異なる期間 |
| の平均値を求めようとすると、すべての関数を書き換えなければなりません。 |
| データの数が多いと面倒になります。 |
| |
| もっと手軽に、１箇所だけ「３」と入力するだけで、３日平均がでるようにしたいものです。 |
| |
| ふたつの関数式を比べてみましょう。 |
|
| =AVERAGE(E2:E4) |
| =AVERAGE(OFFSET(E4,-2,0):E4) |
|
| OFFSET関数の−２の部分を変更できれば、３日平均だろうが４日平均だろうが、どんな平均線にも対応 |
| できそうです。なんとか、ワンタッチで変更できる方法は、ないものでしょうか？ |
| その一方で、OFFSET関数の使われていない方を、手軽にどんな平均線にも対応できるようにするのは、 |
| 無理なようです（思いつきません）。 |
| |
| |
| この作業をワンタッチでやってしまう方法がエクセルにはあります。 |
| 絶対参照を使えばいいのです。 |
| |
| 関数式をひとつのセルに入力し、以降のセルにも適用したとき（セルの右下へマウスカーソルを合わせて、 |
| マウスカーソルの形が＋になっているときに、マウスを左クリックしたままドラッグします）、自動的に合った |
| 関数に変わっていますよね？ |
| |
| この自動的に変わってくれる参照の方式を相対参照といいます。 |
| ３日移動平均でいうなら、平均値は現在のセル（終値）から、２日前にさかのぼったところを範囲の起点と |
| 考えています。この範囲の起点となるセルは、関数式の数だけあります。 |
| そして、起点となるセルは、それぞれの関数式で異なります。 |
| |
| ワンタッチの作業でやってしまうには、特定のひとつのセルに値を入力すれば、すべてのセルに反映され、 |
| 平均値を求められるようにすればいいことになります。 |
| この、ただひとつの決まったセルを参照する方式を絶対参照といいます。 |
| |
| では、Ｆ１を絶対参照のセルにしてみましょう。 |
| 絶対参照は、Ｆ＄１というように、＄を加えた書き方をします。 |
| |
| |
| ３日平均を求めるので、入力する値は「３」になります。ところが、３日平均線を実現した関数式は、 |
| =AVERAGE(OFFSET(E4,-2,0):E4) なのです。 |
| |
| 「３」を入力したら、−２になるようにせねばなりません。なんらかの便宜的な処理が必要です。 |
|
| =AVERAGE(OFFSET(E4,■,0):E4) |
| ↑ |
| 1-F$1 という式は、どうでしょうか？ |
| |
| ３をＦ１に入れると、−２になります。 ４をＦ１に入れると、−３になります。 |
| ４を入れて−３になるというのは、「４日平均を求めるための起点は、３日さかのぼったところから」という |
| 条件も満たすということです。 |
| |
| =AVERAGE(OFFSET(E4,1-F$1,0):E4) |
| |
| Ｆ＄１の値 計算結果 意味 |
| ３ −２ ２日前にさかのぼる |
| ４ −３ ３日前にさかのぼる |
| ５ −４ ４日前にさかのぼる |
| |
| どうやら、この式で対処できそうです。 |
| それでは関数式を修正してみましょう。Ｆ１に３を入れ、Ｆ４へは =AVERAGE(OFFSET(E4,1-F$1,0):E4) を |
| 書き込みます。 |
| |
 |
| |
| 以降のセルにも関数式を適用させます。３日平均が出ていますね。 |
| ４日平均も試してみましょう。Ｆ１に４を入れます。 |
| |
 |
| |
| エクセル関数式を触ることなく、平均値が算出されました。 |
| このように１箇所入力するだけで、すべの平均値が求められるようにするために、ＯＦＦＳＥＴ関数を使い |
| ました。 |
| |
| ※Ｆ４の値だけが、３日平均のままなのは、起点となるＥ１に値が入っていないからです。 |
| こういった本筋と関係ない「はした」の部分は、便宜的な処理を追加して、対処します。 |
| たとえば、この場合、「範囲の起点となる部分が数値でないなら、空白を表示する」といった条件を |
| 追加するのです。 |
| |
| 「はした」にあたる部分は、システムを作っていると頻繁に出てきますので、個別に対応しましょう。 |
| たとえば、勝ちトレード０のときに、勝率を求めると #DIV/0! というエラーが表示されます。 |
| ０での除算はエラーになってしまうのです。電卓を叩いて０で割ると、隅のほうにＥと表示されますが、 |
| 同じことです（ＥはＥｒｒｏｒのＥです）。 |
| |
| |
| 関数式を学び終えたあとは、条件分岐式です。 |
| 「もし〜なら、〜する。さもなくば、〜する」という判定式です。 |
| |
| 条件分岐式は、エクセル関数講座 その４ で解説します。 |
| |
| |
| |
| |
| |
|
| |
|
| |
|
| TOPへ戻る |
|
