データの集計に配列数式をZoho シートで活用しよう！

すべて | October 13, 2011 | 1 min read
Reading Time: 1 minutes



前回の記事

で、Zoho シートに新しい機能「配列数式」が追加されたことをお知らせしました。この配列数式、とっても便利なんですが、パッと見だとちょっとその便利さが分かりづらいんです…


そこで、この記事で解説していきたいと思います。具体的には、次のような値を求める操作を行ってみます。

  1. 売上の合計

  2. 収益の合計

  3. 商品ごとの売上の合計


  4. 上位3位までの金額の合計


さっそく例を見ていきましょう。次のようなシートがあるとします。




















1) 売上の合計を一気に算出する


売上の合計を出そうとすると、まず一番上の行の「販売単価」と「数量」をかけて、一番上の行の売上を出し、それをドラッグして各行の売上を算出し、それを合計することが多いでしょう。






しかし、配列数式を利用すると、この操作を一気に行えます



手順は次のとおりです。
  1. 売上合計の値を入力するセル（E13）に「=SUM(C2:C11*D2:D11)」と入力
  2. 「Shift+Ctrl+Enter」キーを入力



ここで利用している「=SUM(C2:C11*D2:D11)」という

数式では、C列の「販売単価」（C2:C11の値）とD列の「数量」（D2:D11）を、各行ごとにかけ合わせた値を合計しています。つまり、次の内容の計算を一気に行っているのです。

C2*D2+C3*D3+C4*D4+……+C11*D11

また、最後の操作の、「Shift+Ctrl+Enter」キーがポイントです。配列数式を利用する場合は、このキーの組み合わせを利用します

いかがでしょう？こちらのやり方の方がスマートではないでしょうか？




2) 収益の合計

上記の例に「仕入単価」を加えた例も見てみましょう。ここで、収益を算出してみます。






通常の場合、「収益」という列を追加して、各行ごとに収益を算出し、それを合計するでしょう。ここで、配列数式を使うと…
  1. 「収益合計」の値を入力するセルに収益を算出する数式を入力（=SUM((C2:C11-D2:D11)*E2:E11))）
  2. 「Shift+Ctrl+Enter」キーを入力

わざわざ計算用に「収益」の列を作成しなくても、直接収益の合計を算出できます


3)特定の項目の値を合計する

今度は、特定の商品の販売額の合計を求めるとします。この場合は、次のような数式で算出できます。


{=SUM(IF(B2:B11=”チーズ饅頭”;C2:C11*D2:D11;””))}


この数式では、IF関数を用いて、「商品」のセルに「チーズ饅頭」と記載されているかどうかを判断し、「チーズ饅頭」と記載されている場合は販売数と販売単価をかけた値を出し、当てはまらない場合は空の値を出すことで、チーズ饅頭のみの合計を算出しています。






なお、「チーズ饅頭」の部分を「冷汁パック」など別の商品名に変えればそれぞれの商品の合計を算出できます。


4) 上位3位までの売上の合計を算出する


最後に、ちょっと応用的な使い方を見てみましょう。行ごとの売上の中で上位3つのみの合計を算出します。

これは、次のような数式で算出できます。





=SUM(LARGE(C2:C11*E2:E11;{1;2;3}))



この数式では、C列の「販売単価」とE列の「数量」をかけた値（売上）の値の中から、LARGE()関数を用いて上位3位までの値を抽出し、SUM




()




関数で合計を算出しています。同じような操作で平均を算出することも可能です（AVERAGE()関数を利用）。



最初はちょっと慣れるのが大変かもしれませんが、一度覚えてしまえば活用範囲は非常に幅広いです。ぜひご活用ください！






なお、配列数式については、下記のサイトが分かりやすいです。


エクセルと銘打っていますが、ほとんどの仕組みはZoho シートでも同じです


英語でも問題ない方は下記のサイトが分かりやすいです。

便利な「配列数式」機能、ぜひご活用ください！

なお、今回の例で利用したシートはこちらです↓興味のある方は、「クリックして編集」して動作を確認してみてください！









■関連情報  





Akiyoshi Matsumoto

Comments are closed.