ゼロはじ　「第26回　Excel(エクセル)の数式で計算間違いなし(2)」)

　前回は、数式の入力方法と合計、平均、同じ文字が入っているセルの個数のカウントの関数、また、数式のコピーについて学びました。関数や数式は変わっても、基本的には、同様の方法で計算式は入力できます。そこで今回は、数式の組み合わせなど、応用について学んで行きます。

　図26-1を見てください。前回から残っている計算は最下行の日当支払額、K列の個人別の支払合計とその横L列の出席率、N列、O列の活動項目実施回数です。これらは、合計やカウント数の関数だけでは計算することはできません。いくつかの関数を組み合わせて、理屈のあった式にしていくことが必要です。ここからは、かなり算数というか数学的なセンスが必要となってきます。さて、それでは一つずつ紐解いていきます。

9.5.合計とIF文の組み合わせ

　先ずは、活動日毎の日当支払額ですが、これは多面活動の経理としては必要ないかもしれませんが、数式の学習のためにここに設けてみました。日当支払額は〇のついた個数に実施時間と時間単価(ここでは1000円/時間)を積算したものです。ですから、D26に入る最も簡単な式は、

　=D25×D6×1000

となります。勿論これでも良いのですが、D25の値を使わずにデータからいきなり計算したい場合は、IF関数とSUM関数を使います。

　IF関数は一般的には次の式です。

　=IF(論理式,真の場合の値,偽の場合の値)

　論理式とは、簡単に言うと条件のことを言います、基本は「=」「>」「<」が使われます。例えばD13が”〇”の時は1,”〇”以外の時は2をセルに代入せよと言う場合は、

　=IF(D13=”○”,1,2)となります。

　また、偽の値に何も入力しない時は、

　=IF(D13=”○”,1)

としても問題ありません。偽の場合は数値が代入されません。

　このIF関数と合計を組み合わせると、図26-2のように、もしセルの値が”〇”だった時は、そこのセルの値にD6(実施時間)の値を入れて、D13～D22までの間で、それを合計したものに単価1000円/時間をかけなさいという式を作ることができます。

=SUM(IF(D13:D22=”〇”,D6)*1000

　この式を具体的に説明すると、D13、14、16、17、19、20、21のセルは”〇”が入っているで、D6の「4」が代入され、その値を合計するのでSUMが関数としてセットされ、合計値は28時間となり、さらにそれに1000円/時間が積算されることになります。

　次に、E26～J26はD26のコピーですので、前回の9.2で習った方法を使います。

　同様の方法で、K13～K22の人別の日当合計額を算出してみましょう。IF関数はD13=”〇”に対してD6の値が入力されます。その式をD13～J13まで合計すればよいのですから、それを表す式は次の通りです。

　=SUM(IF(D13:J13=”〇”,D6:J6))*1000

　図26-3をご覧ください。山本一太郎さんの場合、11月11日4時間、11月15日4時間、12月14日4時間、1月25日3.25時間、2月4日3.25時間の合計18.5時間で単価1000円/時間ですので、図26-3のセルK13には18,500円が計算されています。

　後はコピーするだけと考え、図26-4のようにコピーをしますと、ちょっとおかしなことに気づきます。K14～K22までの計算値はどうも違っているようです。

　ちなみに、図26-5において、セルK15の数式を見てみると、「=SUM(IF(D15:J15=”〇”,D8:J8))*1000」となっています。D15～J15は”〇”をカウントする対象として正解ですが、実施時間がD6～J6でなければならないところ、D8～J8に変わっていますよね。ここが違っているのです。これは、単純に、K13をK14～K22にコピーしたため、”〇”を探す行は対応できましたが、実施時間はD6～J6に固定しなければならないところ、こちらまで一行ずつ下にスライドしてしまったということになります。

　そこで、行を6行目に固定する必要があります。それが、次のように行「６」の前に「$」を入れた式です。

　=SUM(IF(D13:J13=”〇”,D$6:J$6))*1000

　図26-6のように、正確に計算が行われました。 　ここでは事例は扱いませんが、列を固定したい場合は列の前に「$」を記述します。よって、列固定なら「$D6」と記述すれば、コピーで列が右に移動してもD列は移動しません。また、全固定(セル固定)なら「$D$6」と記述すれば、コピーで列行が移動しても、D6の値となります。

9.6.割合の式

　作表においては、全体の何パーセントの割合かとか頻度を計算することが多いです。そこで、今回目標としている多面事務の表においても、個人別に全活動中どれくらいの頻度で出席したかをセルL13～L22で計算してみましょう。

　割合を計算する関数を使う方法もありますが、割合とは全体の値で割ったものに100をかければ良いだけなので、数式で対応してみます。D13～J13までの範囲のセルの〇の個数を数えて、全活動回数7で割って100をかけるということになり、数式は次の通りです。

　=COUNTIF(D13:J13,”〇”)/7*100

　計算結果は図26-7の左下図です。L13の値は「71.43857143」となり、山本一太郎さんは約70%の活動に参加したということになります。

　ここで一つ気になるのは、活動日数が増えれば「7」の値は使えなくなるということです。列の数を数えるくらい人力でも良いのですが、どうしてもここも数式でやりたいと言う方は、列を数えるCOLUMNSという関数がありますので、それを使うと以下の式となります。

　=COUNTIF(D13:J13,”〇”)/COLUMNS(D$12:J$12)*100

　だんだんと複雑になりますが、まぁ、数式は大方なんとでもなるということを理解してください。

　さて、図26-7を再度診てもらいたいのですが、もう一つ気になることがあります。「71.43857143」は表示としてはとても見にくいと思いませんか。「%」も付いていませんので単位が分かりにくいですし、数値も「71%」とか「71.4%」で十分ではないかと思いますよね。

　そんな場合は、図26-8の左図のように、式に100をかけないで、次の式にして、

　=COUNTIF(D13:J13,”〇”)/7

　第23回で学んだ書式の設定を思い出しながら、中図のように、表示形式を変えたいセルを選択して、右クリックで、「セルの書式設定」を選択して、右図のように表示されたメニューから「形式」、「パーセンテージ」を選択すると、図26-9のように「%」を含んだ値が表示されます。

9.7.応用

　最後に、これまでに学んだ方法を応用して、図26-1の右端のO7～O16に、活動項目番号が入力された回数を表示してみましょう。

　特定の数値が入ったセルの数を数えるのですから、COUNTIFを使います。そして、範囲は固定されるので、$D$7～$J$12からということになり、さらに、探す数値はN7～N16となり、式は次のようになります。

　=COUNTIF($D$7:$J$12,N7)

これをO8～O16にコピーすれば、図26-10となります。

　非農家、農家の参加延べ人数や日当全額とその平均もこれまでの応用ですべて式を入れると図26-11が完成です。

　数式については、まだまだやることはたくさんありますが、ゼロはじ初心者としては、ここまでできれば十分でしょう。後は、徐々に使える関数を増やしていくだけです。また、関数がよく分からない時は、GoogleChromeやMicrosoftEdgeなどのネット検索で、例えば「Excelで列の数の数える関数」とか「Excelで合計する式」とか、適当に聞いてみるだけで、たくさんの方がホームページなどで説明をしてくださっています。こういうのを利用してください。「Excelの使い方」等の書籍も使えますが、お金もかかりますし、結構難しいですので、先ずはネット検索で、目的のことだけを調べるのがお勧めです。

　以上で、Excelの数式の扱いについては終了します。

※二十七番札所、書寫山圓教寺は兵庫県姫路市のお寺です。西国三十三か所で最大規模の寺院で、「西の比叡山」と呼ばれるほど寺格は高いです。中世には、比叡山、大山とともに天台宗の三大道場と称された巨刹です。「書写寺」というのは、写経が盛んだったということかなと思ったが、書写山が釈迦如来による霊鷲山の一握の土で作られたと伝えられており、山が霊鷲山を「書き写した」ように似ているからだとも言われています。NHK「鎌倉殿の13人」ではあまりフューチャーされていないが、武蔵坊弁慶は、書写山で修行したことがあるとも言われています。古い時代のことだから、本当のところは定かでありませんが。