富裕層・投資家の「今と先」を伝えるキュレーションサイト

【エクセル関数】面倒な金種計算を関数で簡略化する

問題:「支払金額」欄の金額を支払う際に、必要な各金種の最小枚数を求めよ。 難易度:☆☆☆☆★ 実用度:☆☆☆★★ 目標ステップ数:8 解答: 今回の問題で取り上げた金種計算は、指定した桁数で対象となる数値を切り捨てる「TRUNC(トランク)」関数と、割り算の結果(商)の余りの部分を求める「MOD(モッド)」関数の2つを組み合わせて行っている。1万円の枚数と、5000円以下の枚数を求める数式が異なるが、順番に追っていくことで、TRUNC関数とMOD関数の役割が理解できるはずだ。さっそく標準解答の手順を見ていこう。 ●STEP1 数式を入力したいセル(ここではE3)を選択し、「数式バー」に「=TRU」と入力。表示される関数の候補から「TRUNC」をクリック。 ●STEP2 計算対象となる支払金額が収められているセル(ここではA3)をクリックして選択。半角の「/(スラッシュ)」を入力し、さらに割り算の分母にあたる金種(1万円)が収められているセル(ここではD3)をクリックしたら、Enterキーを押す。 ●STEP3 選択したセルに計算結果が表示される。 ●STEP4 続けて、5000円以下の金種計算を行う。数式を入力したいセル(ここではE4)を選択し、「数式バー」に「=TRU」と入力。表示される関数の候補から「TRUNC」をクリック。TRUNC関数が入力されるので、さらに半角で「MO」と入力し表示される関数の候補から「MOD」をクリック。 ●STEP5 MOD関数が入力される。計算対象となる支払金額が収められているセル(ここではA3)をクリックして選択。「F4」キーを押してセル範囲が「$A$3」となるようにする。 ●STEP6 続けて半角の「,(カンマ)」を入力し、求めたい金種が収められているセルの1行上となる金種(1万円。ここではD3)のセルを選択する。半角の「)」を入力しMOD関数を閉じたら、続けて求めたい金種が収められているセル(ここではD4)を選択。半角の「)」を入力しTRUNC関数を閉じたら、Enterキーを押す。数式全体は「=TRUNC(MOD($A$3,D3)/D4)」となっている。 ●STEP7 選択したセルに計算結果が表示される。 ●STEP8 選択範囲の右下にポインタをあわせダブルクリックすると、オートフィル機能が働き表組の空欄が埋まる。 【今回のまとめ】 今回の問題で、最初に用いたTRUNC関数は「=TRUNC(数値,桁数)」という基本構文になる。たとえば「=TRUNC(123.45,1)」とした場合は、数値「123.45」の小数点以下「1」桁までを求めることになるので、計算結果は「123.4」となる。また、「=TRUNC(123.45,-1)」というように、桁数にマイナスを指定した場合は小数点より前の整数部分の桁で切り上げされるので計算結果は「120」となる。 ちなみに今回の問題では、小数点以下の数値を切り捨てているので、桁数には「0」を指定することになるのだが、TRUNC関数では桁数を「0」にする場合には「=TRUNC(数値)」と、桁数の指定を省略することができる。 最初に説明したように、今回の金種計算では、1万円の枚数と、5000円以下の枚数を求める数式が異なっている。1万円の枚数を求める計算では、TRUNC関数のみを用いている。 この場合は、求めたい金種の数値(セルD3/1000)で支払金額の数値(セルA3/87562)を割った結果の、小数点以下を切り捨てる(桁数は0だが省略されている)ので、計算結果は「8」となる。「8.7562」の小数点以下が切り捨てられているわけだ。 5000円以下の金種計算はTRUNC関数に、割り算の結果(商)の余りの部分を求めるMOD関数を組み合わせている。MOD関数は「=MOD(数値、除数)」という基本構文になる。 まず、MOD関数の働きを確認しておこう。 例として作成した上記画面のセルE7には、「数式バー」に表示されているように、「=MOD($A$3,D6)」という数式が収められている。この場合は、セルA3(オートフィルを使っても常に同じセルが指定されるように$A$3という「絶対参照」の形にしている)の数値を金種のセル(D6/100)で割った商の余りを求める、という計算になるので、計算結果は「62」(87562÷100=875、余り62)となる。 この計算結果を、今回の問題で取り上げたTRUNC関数にあてはめてみると、下記画面のように「=TRUNC(62/D7)」という数式になる。 これは、セルD7の数値(100)で「62」を割った結果の小数点以下を切り捨てる、という計算を行っているので、答えは「0」となるわけだ。 100円以外の金種の欄も、同様に分解しながら考えてみれば、各関数の役割や数式の構造が理解できるはず。このように複数の関数を組み合わせた数式が使いこなせるようになれば、関数のスキルは中級以上といえる。パズルを解くような感覚で、じっくりと学んでいこう。