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




前回の記事

で、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 シートでも同じです








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




Introducing array formulas in Excel



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

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









■関連情報  





PAGE TOP