[Numbers] 簡単な関数をうまく使って一歩進んだ表を作ろう！

こんな表を作ってみます

Mac の Numbers は多機能な表計算ソフトですが、多くの方は簡単な四則演算や入力したデータのグラフ化程度でしか使っていないのではないでしょうか？
Numbers にも Excel と同じようにたくさんの数学や統計用の関数が用意されています。これを使うと1ランク上の表計算が可能です。

今回は家計簿を例に、次の機能を持った表の作り方を解説します。

• カテゴリごとに金額を集計する
• チェックが付いた項目を集計する
• 結果を円グラフにしてみる

最終的にはこんな表ができあがります。

基本の表を作る

まずは、買い物の金額を入力するための表を作成しますが基本的な表の作成はある程度知っている前提で書きます。↑の完成イメージを元に同じような表を作ってもらうか、完成後のファイルをこちらに用意したのでダウンロードしてください。

カテゴリの準備

架空の家計簿なのでいい加減ですが、適当に次の10カテゴリを用意しました。
　家賃、光熱費、食費、保険、通信費、衣服、日用品、趣味、交際費、貯金、その他

これらのカテゴリを、ドロップダウンリストで選べるようにしましょう。

ドロップダウンリストにしたいセルを選んで、右側のフォーマットペインから「セル」を選び、データフォーマットを「ポップアップメニュー」にします。「+」ボタンを押すとポップアップメニューにリスト項目を追加していくことができます。

※フォーマットペインが現れない場合には、ツールバー右端の「フォーマット」(ブラシみたいなアイコン)をクリックすると表示されます。

チェックボックスの準備

同様にチェックボックスを配置したいセルをクリックして、セルのデータフォーマットを「チェックボックス」にします。

すると、セルの中にチェックボックスが現れてチェックを付けられるようになります。

集計する

では、今回の本題であるデータの集計をやってみましょう。

カテゴリごとに集計する

まず最初にカテゴリごとの金額の集計です。食費がいくら、交際費がいくら、というようにカテゴリごとに自動的に集計したい例はよくあるかと思います。

これを実現するには「カテゴリ」セルでドロップダウンリストから選ばれたカテゴリが特定のカテゴリ(例えば「食費」)かどうかを比較して、一致したら金額欄の値を加算する、、、という処理をすればよさそうです。

では、それをどのように書いたらよいでしょうか？

Numbers には “SUMIF” という関数が用意されています。SUM(合計) + IF(もしも)が合体した関数で、「1つ目に指定したセルの値がもしも○○であれば、2つ目に指定したセルの数値を加算しなさい」という意味になります。
文章だけだと難しいので図でも説明します。

例えば食費の合計を出したい場合を例にします。食費の合計を出したいセルをダブルクリックすると関数を入れる横長の小窓が現れます。この小窓の先頭にある関数名を “SUMIF” にします。
SUMIF は全部で3つの引数をとります。

SUMIF [テスト値], [条件], [合計範囲]

テスト値は条件を調べるセルの範囲で、この図では青い四角で囲われているセル群です。条件は、この青いセルの文字列が “食費” になっているかどうかを見るので “食費” を文字列で指定します。最後の合計範囲は、条件が合致した場合にどこの数値を加算するかを指定します。金額を加算したいのでオレンジ色で囲ったセル群を指定します。

平たく書くと、「カテゴリが食費なら、同行の金額欄から金額をとってきて加算してね」となります。

※図を間違えてしまいました。図の関数一覧では “SUMIFS” を選んでいますが、実際には “SUMIF” を選びます。

カード支払いを集計する

さらに一歩進んで、「カード払い」欄にチェックボックスが付いているものをカテゴリごとに合計してみます。勘の良い方であれば、条件が2つになったことに気づかれるでしょう。そうです、「カテゴリが一致する」ことと「チェックボックスが付いている」ことが条件です。
これも図を見ながらの方が分かりやすいですね。

Numbers には “SUMIFS” という関数が用意されています。SUM(合計) + IFS(複数のもしも)が合体した関数ですが、SUMIF とは書き方の順番が異なります。
SUMIF は条件の下図だけ引数をとります。

SUMIFS [合計範囲], [テスト値1], [条件1], [テスト値2], [条件2], [テスト値n], [条件n]
SUMIF とは違って最初の引数が合計範囲です。以降、テスト値と条件のペアが調べたい数だけ並びます。

カテゴリごとの集計は先ほどと同じですが、新たに「カード払い」が TRUE であればという条件が追加されています。チェックボックスはチェックがついていると TRUE(真), ついていないと FALSE(偽) という2値をとります。

この図の例ではカテゴリが食費になっていて、かつ、カード払いのチェックボックスが付いている場合に、金額を加算してねという意味になります。

円グラフをつける

カテゴリごとの集計を行うと、円グラフにしてみたくなるのが人情。かっこよく円グラフを描画して完成にしましょう。

図のように各カテゴリの金額セルを複数選択して、グラフメニューの「円グラフ」を選びます。これだけでかっこいい円グラフを描画してくれます。
ただ、カテゴリが多いとちょっと見にくいですね。。。

完成した表はこちらからダウンロードできます。