わえなび ワード&エクセル問題集

ワードとエクセルの練習問題の動画を「Youtube」で無料公開しています

【Excel数式】計算が苦手な人のための「累計」「残高」「繰越」

預金通帳で残高を見ることはありますが、小学校の算数の授業で「残高」とか「累計」とか「繰り越し」といった言葉を聞いたことがありません。社会人が当たり前のように使う計算なのに、残念ながら義務教育では習いません。
そこで、今回は、累積的な計算(積み重ねる計算)が苦手な人のために、その基本的な考え方と累計や残高の計算方法を解説します。 
 

f:id:waenavi:20180908100652j:plain

 

目次

問題文のあとに簡単な操作方法を解説していますが、静止画では、わかりにくいと思いますので、最後に動画を載せています。ぜひご覧ください。

0.オートフィルによる計算(復習)

オートフィルによって同じ計算を繰り返す練習はこちらの記事をご覧ください。

 

1.ループの基本練習

問題
A列に1から10までの数値を入力した。これを利用して次の計算をしなさい。
(1)1からnまでの和
(2)1からnまでの積
(3)1からnまでをカンマで連結

f:id:waenavi:20180908052024j:plain

 

解説

同じ計算を何回も繰り返すことを、一般にループ(LOOP)といいます。ループによって累積計算(積み重ねる計算)をするのはExcelが得意とする計算の一つです。
 
1を参照します。(セルの参照についてはファンダメンタルExcel Program5-3の動画参照)
f:id:waenavi:20180908052926j:plain
 
ここで上のセルと左のセルを足します(=B2+A3)。3になります。これは1と2を足したものです(1+2=3)。

f:id:waenavi:20180908053412j:plain

 
オートフィルをします。
f:id:waenavi:20180908052932j:plain
 
下向きにオートフィルをすると相対参照によって足し算の対象となるセルも下に下がります(=B3+A4)。計算結果である3に、さらに左の3を加えて6になります。
f:id:waenavi:20180908052934j:plain
 
さらに、オートフィルをします。
f:id:waenavi:20180908052936j:plain
 
オートフィルをすると足し算の対象となるセルも下に下がります(=B4+A5)。計算結果である6に、さらに左の4を加えて10になります。
f:id:waenavi:20180908052938j:plain
 
これを繰り返すと1つずつ順番に足し算をしていく計算になります。例えば、「8」の隣の値が「36」です。これは1から8までの和が36であることを表しています。
f:id:waenavi:20180908052941j:plain
 
上のセルは今までの計算結果です。これに左のセルを加えます。このように上から順番に足していく計算のことを「累計(るいけい)」といいます。足し算を繰り返していることを理解しましょう。
f:id:waenavi:20180908052944j:plain
 
同じように掛け算をします。1を参照します。1と2をかけます。2になります。
f:id:waenavi:20180908052946j:plain
 
オートフィルをします。1からnまでの積になります。例えば、「8」の隣が「40320」です。これは1から8までの積が40320であることを表しています。このように1から順に掛け算をしていく計算のことを「階乗(かいじょう)」といいます。掛け算を繰り返していることを理解しましょう。
f:id:waenavi:20180908052949j:plain
 
今度はカンマで連結します。1を参照します。上のセル(D2)とカンマ(",")と左のセル(A3)を連結します。(文字列連結についてはファンダメンタルExcel Program 5-2 の動画参照)

f:id:waenavi:20180908060831j:plain

 
オートフィルをします。1からnまでがカンマで連結されます。例えば、「8」の隣が「1,2,3,4,5,6,7,8」となります。カンマで連結したデータを「CSV」といいます。カンマによる連結を繰り返していることを理解しましょう。

f:id:waenavi:20180908052954j:plain

 

2.縦方向と横方向の累計 

問題
A列と1行目に数を入力した。

f:id:waenavi:20180908053345j:plain

 

B列と2行目にそれぞれ累計を求めなさい。

f:id:waenavi:20180908053348j:plain

 
解説
累計を求めるときはまず最初の数をそのまま累計の列に参照します。

f:id:waenavi:20180908062622j:plain

 
次に計算結果である上のセルと、左のセルを足します。オートフィルをします。
f:id:waenavi:20180908062624j:plain
 
これで完成です。
f:id:waenavi:20180908062627j:plain
 
横向きの場合も同じです。まず、最初の数をそのまま累計の行に参照します。
f:id:waenavi:20180908062630j:plain
 
次に計算結果である左のセルと、上のセルを足します。
f:id:waenavi:20180908062703j:plain
 
オートフィルをします。これで完成です。

f:id:waenavi:20180908062715j:plain

 
ところで、セルB3に上のセルと左のセルを足す計算式を入力します。

f:id:waenavi:20180908063229j:plain

 
日本語は足し算できないので、当然、エラーになります。
f:id:waenavi:20180908063232j:plain
 
そこで、日本語を消します。エラーがなくなりました。空白のセルは、足し算ではゼロとみなされますから、正しく計算ができます。
f:id:waenavi:20180908063339j:plain
 
この場合に限り、最初のセルに、上のセルと左のセルを足す計算式を入力して、オートフィルをすれば終わりです。
f:id:waenavi:20180908063234j:plain
 
最初の計算式の前のセルに何らかの文字があるときは、最初のセルはそのまま参照します。そして、2つ目のセルから足し算をします。空白の場合は、足し算してもゼロとみなされますから、はじめから足し算の式を入力してかまいません。
f:id:waenavi:20180908063525j:plain
 

3.逆方向の累計と反転の累計

問題
8月17日をイベント本番の日として、それに向けて練習する日を「1」として入力した。練習する日は全部で21日ある。これを用いて、本番までに練習できる残り日数を計算しなさい。また、本番までの休みの日数(練習しない日)を累計によって計算しなさい。

f:id:waenavi:20180908063757j:plain

 
解説
本番当日は「0」と入力します。

f:id:waenavi:20180908065058j:plain

 
本番の前日は、下のセルと左のセルを足します。1となります。
f:id:waenavi:20180908065101j:plain
 
この計算を上向きに繰り返します。上向きにオートフィルをします。この計算の繰り返しによって下から順に練習日である「1」が加算されるため、結果として残り日数が求められます。(空白はゼロと扱われるので加算されない)
f:id:waenavi:20180908065103j:plain
 
7月21日は練習日なので、7月21日を含めて練習日が残り21日であるという意味になります。
f:id:waenavi:20180908065105j:plain
 
次に休みの日を計算します。休みの日を累計するためには、練習日が0で、休みの日を1にする必要があります(0と1を逆にする必要がある)。

f:id:waenavi:20180908070012j:plain

 
ここで、1と0を反転させる計算式を使います。1から引き算をすることによって1と0が反転します。
f:id:waenavi:20180908070015j:plain
 
このように1から引き算をすると(=1-B2)、練習日が0、休みの日が1になります。
この性質を利用して、累計を求めます。
f:id:waenavi:20180908070017j:plain
 
本番の日は0とします。

f:id:waenavi:20180908074246j:plain

 
下のセルに、練習日を1から引いたものを加算します(=D30+1-B29)。1から引くと練習日が0、休みの日は1が加算されます。カッコはあってもなくてもよいです。
f:id:waenavi:20180908074248j:plain
 
これを、上向きにオートフィルをします。7月20日の時点で、7月20日を含めて7日の休みがあります。

f:id:waenavi:20180908074326j:plain

 

4.繰り越しと残高

問題
次の図で、昨年からの繰り越しを含めた残高を求めなさい。

f:id:waenavi:20180908075216j:plain

 
解説
お年玉の場合、使わなければ増える一方です。お金の累計のことを「残高(ざんだか)」といいます。「残」という漢字を使っているので引き算のイメージがありますが、お金が増える場合は足し算です。
 
累計を求めるときには、最初はそのまま参照します。次に上のセルと左のセルを足します。そしてオートフィルをします。

f:id:waenavi:20180908080052j:plain

 
最初の数をそのまま参照します。昨年末で財布の中に残っているお金5,500円はそのまま残高になります。このように余ったお金を参照して残高にすることを「繰り越し(くりこし)」といいます。

f:id:waenavi:20180908080334j:plain

 
残高(上のセルC4)と次の金額(左のセルB5)を足します。
f:id:waenavi:20180908080336j:plain
 
オートフィルをします。これで完成です。
f:id:waenavi:20180908080339j:plain
 

5.差引残高

問題
次の図で残高を求めなさい。

f:id:waenavi:20180908081138j:plain

 
解説
今度は収入と支出がある例です。収入は足し算で支出は引き算です。計算式が足し算になったり引き算になったりするため、このままではオートフィルができません。
f:id:waenavi:20180908081044j:plain
 
金額を見ただけでは収入なのか支出なのかが分かりにくいので、収入はプラス、支出はマイナスで入力します。これで、累計を計算すれば残高になります。

f:id:waenavi:20180908081422j:plain

 
はじめに繰越金があれば、収入であり、残高にも入ります。(繰越がなければ0と入力します。)ちなみに、摘要とはお金の使い方を記録したメモ書きのことです(漢字注意)。

f:id:waenavi:20180908081603j:plain

 
つぎにこの残高にお小遣いを足します。

f:id:waenavi:20180908081934j:plain

 
これをオートフィルします。これで完成です。収入があれば残高が増え、支出があれば残高が減ります。このように、収入をプラス、支出をマイナスとして、収入から支出を引いた残高のことを「差引残高(さしひきざんだか)」といいます。商取引で残高と言えば通常、差引残高のことをいいます。
f:id:waenavi:20180908082007j:plain
 
 

6.プラスとマイナスの分離

問題
次の表の問題点を指摘しなさい。

f:id:waenavi:20180908082220j:plain

 
また、次のように作り変えて残高を求めなさい。ただし、科目は「収入」または「支出」と入力しなさい。

f:id:waenavi:20180908082254j:plain

解説
この方法の場合、簡単に残高が計算できますが、収入と支出を分けて、合計を計算するのが難しくなります。また、いっぱんに、帳簿で金額をマイナスで入力するのは正しくありません。

f:id:waenavi:20180908082440j:plain

 
今度は収入と支出の列を分けます。日付と摘要は変わりません。このとき、収入も支出もプラスの金額にします。ちなみに「科目」は集計するときの分類です。

f:id:waenavi:20180908090102j:plain

 
差引残高を求めます。まずは繰越金を参照します。
f:id:waenavi:20180908085920j:plain
 
次に上のセルに収入を足して、支出を引きます。
 f:id:waenavi:20180908085922j:plain
 
累計を求めるときに、プラスにする数とマイナスにする数の列が分かれている場合は、上のセルに、加算と減算の両方をします。空欄であったとしても、オートフィルをすることを考えれば両方必要です。
 f:id:waenavi:20180908085925j:plain
 
オートフィルをします。これで収入があったときは加算され、支出があった時は減算されます。
 f:id:waenavi:20180908085927j:plain
 
この形であれば、この期間内の収入と支出の合計を求めることもできます。
f:id:waenavi:20180908085930j:plain
 

7.繰り越しの正しい処理の仕方

問題
期末残高23,666円を次のページ(次の期間)に繰り越しなさい。
f:id:waenavi:20180908090602j:plain
 
解説
期間を区切ったときの最終的な残高(上の図では2月末)のことを期末残高ということがあります。これを次のページ(次の期間の最初=3月1日のこと)へ繰り越す場合には、繰り越す前に、差引残高を参照した計算式を支出に入力します。※残高がマイナスの場合は借入残高になるので説明省略。

f:id:waenavi:20180908091102j:plain

 

そして、残高をゼロにするのが一般的です。つまり、いったん財布から全部のお金を取り出して支出にしてしまうということです。

f:id:waenavi:20180908091153j:plain

 
前期からの繰越726円は収入で、次期へ繰り越す23,666円は支出です。これで繰り越しを含めて合計すると、収入と支出の合計金額は同じになります。このようにして計算ミスを防ぐことができます。

f:id:waenavi:20180908092433j:plain

 

ちなみに、財布から取り出した23,666円は、次の期間の最初で財布に戻します(収入となります)。

f:id:waenavi:20180908093318j:plain

 

※補足

 

8.残高の応用問題(定額法)

問題
100万円を10回に分けて支払うことになった。100万円を均等の額に分けて支払う場合の残高を求めなさい。ただし金利・手数料は考慮しなくてもよい。
 
解説
100万円を10回に分ける場合、1回分の割合は1/10です。

f:id:waenavi:20180908093744j:plain

 
最初の残高は100万円です。この10%を計算します。両方とも絶対参照です。

f:id:waenavi:20180908094304j:plain

 
支払額は10万円ずつです。残高を求めます。累計の場合には足し算になりますが、支払うと残高は減るので引き算になります。上のセルから支払額を引きます。
f:id:waenavi:20180908094030j:plain
 
オートフィルをします。10回目で残高はゼロになります。

f:id:waenavi:20180908094143j:plain 

 

9.残高の応用問題(定率法)

問題
100万円を10回に分けて支払うことになった。最初の6回は残高の20%を支払うが、残り4回(7回目~10回目)は、6回目と同じ金額を支払う場合の残高を求めなさい。ただし金利・手数料は考慮しなくてもよい。
 
解説
同じように最初の残高は100万円です。残高の20%を計算します。20%だけ絶対参照です。

f:id:waenavi:20180908094854j:plain

 
残高は引き算です。
f:id:waenavi:20180908094856j:plain
 
オートフィルをします。6回目までこれを繰り返します。 
6回目はその前の残高が327,680円なので、その20%にあたる65,536円を支払い、残りが262,144円となります。
f:id:waenavi:20180908094859j:plain
 
これ以降、残り4回は65,536円を支払うので、上のセルを参照します。
f:id:waenavi:20180908094901j:plain
 
残高の計算式は変わりませんので、そのままオートフィルをします。10回目で残高はちょうどゼロになります。
f:id:waenavi:20180908094903j:plain
 

10.補足

*補足意見1*
累計についてはSUMを使って求める方法もあります(SUMによる累計はファンダメンタルExcel Program 6-9 の動画参照)。確かに、SUMはエラーが出にくい等のメリットがあります。しかし、累計の求め方が分からない初心者は「累計はループである」という基本知識を学校で習ったことがないので、そのような人にSUMを使う方法だけを教えるのは極めて不親切です。ループはアルゴリズムの基本です。

*補足意見2*
収入と支出の金額を1列で入力して、科目(収入/支出)によって複数列に振り分ける方法がありますが説明を省略しました。詳しくはExcel新演習 Program 3-9-4の動画参照。

*補足意見3*
計算式の基本についてはファンダメンタルExcel 5 計算式の入力(全15回)をご覧ください。

 

解説は以上です。 


11.動画版はこちら(無料)

この記事は、わえなび実力養成講座「Excel新演習2」Program 2-5、2-6 の2本のYoutube動画を書き起こしたものです。

ちなみに、繰り返しの計算の動画は全6回のシリーズです。すべて無料公開しています。ぜひご覧ください。

 


Youtube わえなび公式チャンネル
http://www.youtube.com/c/waenavijp

チャンネル登録(無料)をすると最新の動画をチェックできます。
初心者向けの動画が大量にあります。

 

 

 

 

 

Copyright(C)2018 waenavi, All rights reserved. [www.waenavi.com 定礎 平成30年8月]