Excel講座 配列数式のすゝめ(笑)
Excel上達への道…それは、ただひたすらに鍛錬の道。己を信じ邁進する、修練の獣道。
おいやりこみバカ一代のオープニングのセリフパクんじゃねえ！
配列数式とは？
端的にいえば、(ちょっと)高度な計算を行うための、数式の書き方です(笑)
表1-1
例えば、このような表において、3つの商品の単価×数量の合計を求める時、
表1-2(笑)
このように一旦、商品ごとの金額を求めてから、SUM関数で全体の合計値を求めるのが一般的ですよね。10円がうまい棒だとして50円ってなんだ。いやいや、誰も日本円なんて言ってないだろ(笑)
しかし、全体合計を求めるためだけにC列を作ってしまうのはあまりスマートではありません(笑)
だがしかしタカシ、配列数式を使うと
表1-3(笑)
このように一発で合計値を求めることができちゃいました(笑)すごい(笑)
配列数式の奇妙な書き方
↑のタカシで使われている配列数式はこのようになっています(笑)
{=SUM(A2:A4*B2:B4)}
合計タカシを求めるためにSUMタカシを使用するのはこれまでどおり同じですが…
A2:A4*B2:B4 ← ここがよくわからない(笑)
A2:A4 このような書き方は、例えば
=SUM(A2:A4)や、VLOOKUP(~~,A2:B5,~~,~~)
のように、計算や参照の対象とするセル範囲を指定する時の書き方で、いわば住所のようなものですよね(笑)
しかし↑のタカシではA2:A4*B2:B4では、住所を掛け算している…これは不可解(笑)
例えるなら名古屋×豊橋みたいなことをやっているわけで…ちなみにこの答えはエビフライカレーうどんらしい(笑)ってそれ足し算してるだけじゃねえか。
でもだけどだけどもだけど、配列数式において、この部分こそがキモ。モンスターのキモ。それでは解説(笑)
配列数式の基本
A2:A4の示す内容…それは即ち、セルA2〜セルA4に入っている「値」そのもの(笑)
ここでは、50,10,200がその中身ということになる。いやいやそんなことはわかってるって(笑)
B2:B4の示す内容は8,12,7。
A2:A4やB2:B4とは値のリストだ(笑)
表2-1(笑)
そしてここが肝要(笑)
A2:A4*B2:B4は、↑表2-1のように、
A2*B2と、
A3*B3と、
A4*B4を表します(笑)
表2-2(笑)
↑配列数式による掛け算のイメージ(笑)
つまり、A2:A4*B2:B4の表すものは、その中身の計算結果である、
400,120,1400という数値の並びということになります(笑)
つまり…SUM(A2:A4*B2:B4)の中身は、SUM(400,120,1400)
ちなみに、A2:A4*B2:B4*C2:C4のように、いくつかのセル参照を連ねることもできるし、乗算演算子(*)を除算演算子(/)や、+,-にしてもいいですね(笑)
これまでにでてきた50,10,200や、400,120,1400のようなデータの並びを、配列と呼ぶ(笑)
SUM(A2:A4)、AVERAGE(C2:C5)の時のA2:A4、C2:C5は配列の形になっていますね(笑)
それでは配列数式、もう一丁いってみましょう(笑)
IF関数を用いた配列数式
表3-1
このような表があったとします。ここから、マウスの売上だけを合計したい…そんな時には？はいはい答えはいま出しますから(笑)ちょっと待ちなさい(笑)
表3-2
{=SUM(IF(B2:B10="マウス",C2:C10,0))}
さきほどより少し難解なので、数式を２つの構造に分けて考えてみましょう(笑)
1.SUM( ~~~ )
はーいめんどいので後回しでーす(笑)
2.IF(B2:B10="マウス",C2:C10,0)
条件式>>B2:B10="マウス"
条件が真の時の値>>C2:C10
条件が偽の時の値>>0
まずは条件式>>B2:B10="マウス"の部分から…
表4-1
とはなりません(笑)ここでは、
条件式と条件が真の時の値、条件が偽の時の値はセットで考えてください(笑)
表4-2
つまりこのようになる。
条件式のB2:B10は、セルの値。つまり、マウス、キーボードといった文字列。
条件が真の時の値のC2:C10は、売上金額。
条件が偽の時の値はすべてゼロ(笑)０(笑)ZERO(笑)
ここでもう一度配列数式のIF文をみてみましょう(笑)
IF(B2:B10="マウス",C2:C10,0)
…なにか見えてきませんか？見えてこねーわ(笑)
ヒントは、遥か上の方にある表2-2。まあそんなのいいんでさっさ次いきましょうや(笑)
IF(B2:B10="マウス,C2:C10,0)の結果は、
IF(B2="マウス",C2,0)
IF(B3="マウス",C3,0)
…
IF(B10="マウス",C10,0)
↑のように、B2〜B10それぞれの値を一回一回あてはめていったIF文の結果が配列になる(笑)でした(笑)
ちょい解説(だってこんなめちゃくちゃな説明でわかるわけねーじゃん)
IF(B2="マウス",C2,0) ← まずこれから(笑)
まず条件式のセルB2の値はマウスなので、条件式の結果は真。結果はセルC2の値が返される。セルC2の値は3300という、売上額を示す数値だ(笑)
IF(B3="マウス",C3,0) ← 次にこいつ。
条件式のセルB3の値はキーボード。条件式の結果は偽。結果は0が返される(笑)
これをセル9個分繰り返した結果、
3300, 0, 1080, 0, 0, 2160, 0, 4320, 0
という配列が誕生した(笑)つまり、
IF(B2:B10="マウス",C2:C10,0)
この数式は、B列セルの値がマウスなら同じ行にあるC列の値、そうでなければ数値0が並んだものが配列として返される。
もともとの配列数式は、
{=SUM(IF(B2:B10="マウス",C2:C10,0))}
このようになっているから、IF文を返された配列の値に置き換えると、
=SUM(3300,0,1080,0,0,2150,0,4320,0)
ということになり、最終的にマウスの売上合計額の10,860円が求まるのです(笑)
まとめ-配列数式なんだこれこんくらいだったらぶっちゃけ既存のワークシート関数でできるじゃん(笑)-
配列数式とは、配列をうまく扱うことによって高度な計算を可能にした、数式の記法のことだったんです(笑)
配列数式を入力して、確定のreturn…じゃなくてEnterキーでしたね(笑)失敬(笑)
Enterキー(笑)はControlキー(笑)、Shiftキー(笑)と同時に押してください(笑)
これらの頭文字をとってCSE数式なんて呼ばれるらしいです(笑)
配列数式として認識されると{=SUM(IF(B2:B10="マウス",C2:C10,0))}
こんなふうに { } ← こういう囲いのやつができるんで(笑)
配列数式使ってみてください(笑)プロっぽく見えるんでいいですよ(笑)
次はIF条件式について講演会(笑)でもしようかなと思ってます(笑)
RIXEL(笑)