今回は「関数PRODUCT」の活用方法を紹介していこう。関数PRODUCTは、指定したセル範囲にある数値をすべて「掛け算」してくれる関数だ。普通に考えると、あまり使い道はなさそうに見えるが、実は幅広いシーンで活用できる関数だ。「見積書」や「請求書」で小計を算出する場合にも役立つので、この機会に使い方を覚えておくとよい。→連載「作り方で変わる!Excelグラフ実践テク」はこちら。
セル範囲を掛け算する関数PRODUCT
前回は、関数SUMを使うときの注意点などを紹介した。今回取り上げるPRODUCTはSUM関数に似ている。まずは、関数PRODUCTの概要から紹介していこう。
合計を算出する関数SUMは、指定したセル範囲をすべて「足し算」してくれる関数。一方、関数PRODUCTは、指定したセル範囲をすべて「掛け算」してくれる関数となる。
その記述方法は関数SUMと同じで、カッコ内(引数)にセル範囲を指定するだけ。これで、そのセル範囲内にある数値をすべて「掛け算」した結果を得ることができる。例えば、「=PRODUCT(C4:C6)」と入力すると、C4、C5、C6のセルにある数値をすべて「掛け算」した値を計算結果として表示してくれる。
具体的な例を紹介しておこう。たとえアバ、形状が4種類、色が5種類、サイズが3種類のラインナップを備えた商品があるとしよう。この場合、それぞれの組み合わせのパターン数は(4種類)×(5色)×(3サイズ)となる。これを関数PRODUCTで計算したのが以下の図だ。
「Enter」キーを押して計算を実行すると、その答えは「60」通りと表示された。
このように、いくつもの数値を「掛け算」したいときに活用できる関数がPRODUCTとなる。とはいえ、「このような計算を行う機会は滅多にないのでは?」と感じる方も多いだろう。仮にあったとしても、「電卓で計算した方が手っ取り早い」と考えるかもしれない。確かに、その通りである。
では、どのような場面で関数PRODUCTが活用できるのか? その答えは、「普通に掛け算するとき」だ。単なる「掛け算」であれば数式でも十分に実現できるが、関数PRODUCTを使った方が柔軟性は高くなる。その理由を具体的に解説していこう。
関数PRODUCTの活用例
ここでは、ある施設(コワーキングスペース)の利用料を計算する場合を例に、関数PRODUCTの活用方法を紹介していこう。
この施設では部屋(ブース)ごとに「1時間あたりの利用料」(単価/h)が定められている。よって、利用料は(単価/h)×(利用時間)で計算するのが基本となる。ただし、会員特典(利用料10%OFF)や割引クーポン(利用料20%OFF)なども用意されているため、必ずしも(単価/h)×(利用時間)の計算だけで済むとは限らない。
そこで、「係数」という項目を追加して「割引後の料金」を計算している。「割引なし」の場合は100%、「10%OFF」の場合は90%、「20%OFF」の場合は80%、という具合に「基本料金」に「係数」を掛け算することで「割引後の料金」を算出している。
このような場合、以下の図のように「数式」を入力して「料金」を計算するのが一般的ではないだろうか?
続いて、この数式をオートフィルでコピーすると、他のデータ(行)についても「料金」を算出できる。
ここまでの話は、特に問題のない処理手順といえる。ただし、「このExcelを他の人も利用する」となると、問題が生じる可能性がある。
例えば、「割引特典なし」のため、係数に「なし」と入力した場合を考えてみよう(下図のE8セル)。この場合、料金の計算結果はエラーになってしまう。また、係数を「空白」のまま放置するケースもあるだろう(下図のE9セル)。この場合、料金の計算結果は0円になってしまう。
このように「掛け算の数式」で計算すると、Excelに不慣れな方(データ入力のルールを知らない方)が作業したときに不具合が生じてしまう恐れがある。
同様の計算を「関数PRODUCT」で実行した例も紹介しておこう。この場合は、以下の図のように記述して関数を入力すればよい。
続いて、この関数をオートフィルでコピーすると、他のデータ(行)についても「料金」を算出できる。
上図を見ると分かるように、関数PRODUCTで計算した場合は、係数が「文字」や「空白」であっても「正しい料金」を算出できる。この理由は関数SUMと同じで、関数PRODUCTも「文字」や「空白」を無視する仕様になっているためだ(詳しくは前回を参照)。よって、掛け算するセル範囲に「文字」や「空白」が含まれていても、特に問題なく計算を実行することが可能となる。
このように「数値」以外のデータが含まれる場合は、「数式」と「関数」で計算結果が異なるケースがある。その理由を理解するには、数式が「文字データや空白データをどのように処理しているか?」を理解しておく必要がある。
◆数式における「数値」以外のデータの処理方法
「文字」・・・文字は計算できないのでエラーになる
「空白」・・・0(ゼロ)とみなして計算する
数式が参照しているセルに「文字」が入力されていた場合は、文字の計算はできないため、その計算結果はエラーになる。
数式が参照しているセルに「空白」が含まれていた場合は、そのデータは0(ゼロ)として処理される。「足し算」あれば0(ゼロ)として処理されても問題ないケースが多いといえるが、「掛け算」の場合は大問題となる。というのも、0を含む掛け算は、その計算結果が必ず0(ゼロ)になってしまうからだ。
一方、関数PRODUCTは「文字」や「空白」を無視する仕様になっているため、「数値」以外のデータが含まれていても問題は生じない。このような観点からみると、「数式」よりも「関数」のほうが使い勝手がよいと考えられるだろう。
見積書や請求書などにも応用できる関数PRODUCT
先ほどの例のように少し特殊なケースだけでなく、「見積書」や「請求書」でよく使用する(単価)×(数量)の計算にも関数PRODUCTが活用できる。こちらも具体的な例を示していこう。
(単価)×(数量)の計算を行うときは、以下の図のように「掛け算の数式」を利用するのが一般的かもしれない。
この数式をオートフィルでコピーすると、他のデータ(行)についても「小計」を算出できる。
ただし、この場合は、配送料や設置料のように「数量」をカウントしない項目にも必ず「1」の数値データを入力しておく必要がある。
こららのセルを「空白」のまま放置してしまうと、「小計」は0円と計算されてしまう。また、何らかの「文字」を入力した場合は、「小計」の計算結果はエラーになってしまう。さらには、それを参照する「合計」もエラーになる。
この理由は、先ほど示した例と同じ。数式では、「空白」を0とみなす、「文字」は計算できない、という仕様になっているからだ。
一方、関数PRODUCTで「掛け算」を計算すると、このような問題は生じなくなる。念のため、関数PRODUCTの記述方法を以下の図に示しておこう。
この関数をオートフィルでコピーすると、以下の図のような結果になる。数量が「空白」や「文字」であっても、正しい「小計」が表示されるのを確認できるだろう。
関数PRODUCTを使うと、数量の項目を「空白」のまま放置しても構わないし、「文字」を入力してもエラーが発生しなくなる。つまり、それだけ「柔軟性の高い掛け算」を実現できるわけだ。
関数PRODUCTは「一般的によく知られている関数」ではないが、使い方によってはとても便利な関数になる。その活用ポイントは、計算における「文字」や「空白」の処理方法をよく理解しておくこと。厳密性が求められる「数式」よりも、「関数」のほうが柔軟に対応できるケースは少なくない。
このように、数式の「掛け算」よりも「関数PRODUCT」のほうが便利に活用できるケースは多々ある。Excelで作成する「見積書」や「請求書」などの使い勝手を向上させたい方は、この機会にぜひ、関数PRODUCTの活用も視野に入れてみるとよいだろう。