エクセル・VBA

2010年4月 8日 (木)

目指せ職場のプチヒーロー!~SUMPRODUCT関数編2~

さて、前回お話をしたSUMPRODUCT関数。

この真価が問われるのは、複数検索です。

たとえば、

東京支店

鉛筆 100円 30本

ノート 150円 20冊

消しゴム50円 30個

札幌支店

鉛筆 100円 20本

ノート 150円 25冊

消しゴム50円 5個

とした時、「札幌支店」の「鉛筆」の購入額を出すことが可能なのです。

まずは、「札幌支店」の購入額をSUMPRODUCT関数で出してみましょう。

イメージとしてはこんな感じ。

20100408001

箱が3列6段あります。

札幌支店の合計額を知りたいときは、一番左側の列が、「札幌支店」

かどうかを知る必要がありますよね。

{"東京支店","東京支店","東京支店","札幌支店","札幌支店","札幌支店"}="札幌支店"

こうすると、答えは

{False,False,False,True,True,True}となります。

これで、終わりかというとそうではなく、

これで掛け算をすると、かけることができないこともあるので、

さらにそれぞれに1をかけます。

つまり、

False*1=0,True*1=1となります。

そして、それぞれを1でかけるということは、かっこで閉じることができますよね。

5×100+3×100+8×100は

(5+3+8)×100と同じですので、

({"東京支店","東京支店","東京支店","札幌支店","札幌支店","札幌支店"}="札幌支店")*1

とします。

もちろん前回の通り、{}かっこ内は範囲を指定しても大丈夫。

さて、後は同じように

=SUMPRODUCT(({"東京支店","東京支店","東京支店","札幌支店","札幌支店","札幌支店"}="札幌支店")*1,{100,150,50,100,150,50},{30,20,30,20,25,5})

と、足すことによって、札幌支店の合計額を表示することができます。

さらに、鉛筆を出したい場合は、一列を追加するイメージで

=SUMPRODUCT(({"東京支店","東京支店","東京支店","札幌支店","札幌支店","札幌支店"}="札幌支店")*1,({"鉛筆","ノート","消しゴム","鉛筆","ノート","消しゴム"}="鉛筆")*1,{100,150,50,100,150,50},{30,20,30,20,25,5})

と書き加えると複数検索も可能です。

試してみてくださいね。

| | コメント (0) | トラックバック (0)

2010年4月 2日 (金)

目指せ職場のプチヒーロー!~SUMPRODUCT関数編1~

どうも、実はエクセルを少したしなんでいるみもっちです。

この間、「エクセルの『SUMPRODUCT関数』が難しいんだよね」

と質問をうけました。

確かに、SUMPRODUCT関数は難しいのですが、

複数検索が可能になったりと、大変便利なのです。

この関数を知っているとエクセルを使う職場で

プチヒーローになれること間違いなしです。

まず、SUMPRODUCTはどんな関数かを調べましょう。

エクセルの数式バーの「fx」(関数の挿入)ボタンをクリックします。

そして関数の検索に「SUMPRODUCT」を入力して、

「検索開始」ボタンをクリックすると説明が表示されます。

「範囲または配列の対応する要素の積を合計した結果を返します。」

・・・うーん、わかったようなわからないような。

まず、積は掛け算の答えなので、

掛け算の答えの合計が出てくるということなのですが。

とくに配列は難しいと思います。

たとえば、

次の売り上げ額を調べたいとします。

鉛筆 100円 30本

ノート 150円 20冊

消しゴム50円 30個

だった場合、計算の仕方は

100*30+150*20+50*30となりますよね。

つまり100×30(鉛筆代金)と150×20(ノート代金)と50×30(消しゴム代金)

の合計値を指しているんです。

イメージとしてはこんな感じ

20100402002

箱をたんすのように積み上げているのをイメージしていただくと

わかりやすいと思います。

その場合、出来上がったたんすが「配列」となります。

これは1列3段のたんすが出来上がっていますが、

もちろん2列に増やしたり、さらに奥行きに増やすことも可能です。

また左右の個数が同じでなければいけないので、

右が3つの配列で左が2つの配列を作ることはできません。

範囲を指定するときや配列を作るときには注意をしてください。

さて、関数は次の通りになります。

=SUMPRODUCT({100,150,50},{30,20,30})

この{}かっこ内が配列という意味です。

もちろん

=SUMPRODUCT(B2:B4,C2:C4)

と、セルの範囲を指定することも可能です。

| | コメント (0) | トラックバック (0)