区分欄は収入支出移動の 3 つの何れかを選び集計項目を識別するのに使います特定の言葉しか入力しないのでドロップダウンリストから入力するようにしますセル E7 をクリックしておきデータタブにあるデータの入力規則を開きます右に示すような画面が開かれますから入力値の種類にリス

Size: px

Start display at page:

Download "区分欄は収入支出移動の 3 つの何れかを選び集計項目を識別するのに使います特定の言葉しか入力しないのでドロップダウンリストから入力するようにしますセル E7 をクリックしておきデータタブにあるデータの入力規則を開きます右に示すような画面が開かれますから入力値の種類にリス"

つねときたかはし
5 years ago
Views:

1 まえがき自治会会計簿の事例紹介ここで紹介する自治会会計簿は筆者が自治会の会計担当者になったときに作成したものをベースにシンプルでしかも使い易く改良したものです金銭出入りのデータを集計するにはいろいろな方法がありますがこの会計簿の特徴は入力と同時に自動集計されるようにしてあることですこの資料は Excel 2013 で説明しています Excel 2007 などでも使えますがメニュー名などは適宜読み替えてくださいこの自治会会計簿を他の自治会でも使えるように改造するときの注意事項を最後にまとめていますが改造によって如何なる問題や損害が発生しても責任を負いかねますので自己責任で改造してご使用くださるようお願いします自治会の会計担当者は予算書に基づく予算管理を行い年度末には決算書を作成し予算との対比を報告しなければなりませんそこで予め集計を行うときの集計項目を適切に決めておき会計簿に記入するたびにこれらの集計項目で自動集計される仕組みを考えてみましょうこのような仕組みを作れば予算の執行状況が一目で分かり決算書も簡単に作ることができるようになるでしょうまた補助金の申請も会計担当者の重要な仕事ですこれには決められた様式で前年度の決算書と当年度の予算書を提出しなければなりませんが多くの場合自治会の会計簿とは会計費目が異なるためそれらの整合をとるのに苦労しますここに紹介する自治会会計簿はドロップダウンリストから集計項目を入力でき記入と同時に補助金申請用の集計表と自治会用の集計表が作られますまた現金と預金の残高も記入と同時に確認できるようにしてありますこの自治会会計簿には会計簿設定集計表補助金申請用集計表自治会用集計表という 5 つのシートがあります会計簿シートについてタイトルは番号月度月日区分集計項目摘要伝票取扱者現金収入現金支出預金収入預金支出現金残高預金残高という 14 個に分かれています番号欄は中央寄せに設定してから 1, 2, 3 と入力したのちドラッグして 300 までの数字を入れておきます月日欄は中央寄せに設定してからセルのプロパティを日付に設定し 4 月 20 日なら 4/20 で入力できるようにしておきます月度欄も中央寄せに設定します月日の入力で自動的に入力されるように C6 には =IF(D6="", "", MONTH(D6)) という式を C7 には =IF(D7="", "", MONTH(D7)) と書いておきます IF 関数の書式は =IF( 論理式, 真の場合, 偽の場合 ) です最初の論理式とは若し ~ ならばと読み替えれば分かり易いでしょう MONTH 関数の書式は =MONTH( シリアル値 ) ですエクセルでは日付を扱うのに内部的には 1900/1/1 を 1 としてこの日からの経過日数 ( シリアル値 ) を使って処理しています次の区分欄と集計項目欄は集計を行うときの項目を入力する箇所です最初の行は前年からの繰越金を記入する箇所なので E6 には " 収入 " と入力し F6 には次の設定シートで定義した繰越金に該当する言葉が入力されるようにしておきます 1 / 8

区分欄は収入支出移動の 3 つの何れかを選び集計項目を識別するのに使います特定の言葉しか入力しないのでドロップダウンリストから入力するようにしますセル E7 をクリックしておきデータタブにあるデータの入力規則を開きます右に示すような画面が開かれますから入力値の種類にリストを選び元の値に収入, 支出, 移動と入力しておきますこれでセル E7

2 区分欄は収入支出移動の 3 つの何れかを選び集計項目を識別するのに使います特定の言葉しか入力しないのでドロップダウンリストから入力するようにしますセル E7 をクリックしておきデータタブにあるデータの入力規則を開きます右に示すような画面が開かれますから入力値の種類にリストを選び元の値に収入, 支出, 移動と入力しておきますこれでセル E7 はドロップダウンリストから " 収入 " " 支出 " " 移動 " の何れかを選ぶことができるようになりますセル E7 も中央寄せにしておきましょう集計項目欄は次の設定シートで定義した収入支出移動の 3 つのリストを利用しますセル F7 をクリックしておき前述と同様にしてデータの入力規則を開きます右に示すような画面が開かれますから入力値の種類にリストを選び元の値に =INDIRECT(E7) と書いておきます INDIRECT 関数は領域名に変換する関数ですすなわちセル E7 が " 収入 " であればドロップダウンリストの内容は収入と名前が付けられたリストになります次の摘要伝票取扱者は会計簿の覚書の項目です摘要欄には集計項目に関する補足メモを自由に書いてください伝票欄には収入伝票と支出伝票の番号を N-1, N-2, S-1, S-2, などと収入と支出を区別するための記号を付けて記入しますタイトル名を領収書と変更して領収書の番号を書くようにしても構いません取扱者欄には現金を誰に渡したか誰から受け取ったかなどを記入しておきます摘要伝票取扱者の 3 つの欄は集計には関係しないのでタイトル名を変更しても問題はなくまた列を挿入して新しいメモ欄を作っても構いません実際に金額を記入するのは現金収入現金支出預金収入預金支出の 4 つの項目の何れかです現金収入預金収入の最初の行は前年度からの繰越金を記入する箇所なのでセル J6, L6 にはコメントを付けておきます 2 / 8

セル J7 には区分が " 支出 " の場合には入力されるのを防ぐ必要がありますこのために入力規則を使いますセル J7 をクリックしておいてデータの入力規則を開きます右に示すような画面が開かれますから入力値の種類にユーザ設定を選びます数式の右にあるをクリックすると右下図のような細長い画面に切り替わりますから =OR(E7=" 収入 ", F7=" 預金引き出し ",

3 セル J7 には区分が " 支出 " の場合には入力されるのを防ぐ必要がありますこのために入力規則を使いますセル J7 をクリックしておいてデータの入力規則を開きます右に示すような画面が開かれますから入力値の種類にユーザ設定を選びます数式の右にあるをクリックすると右下図のような細長い画面に切り替わりますから =OR(E7=" 収入 ", F7=" 預金引き出し ", F7=" 仮払金戻り ") と記入しますまた右にあるをクリックと元の画面に戻りますすなわちこの数式の条件を満たすときのみ入力が許され区分 E7 が " 支出 " の時はエラーを表示しますエラーの場合の表示はエラーメッセージタブを開き区分または集計項目で入力が制限されていますとしておきます上述のような処理をセル K7, L7, M7 にも施しておきます入力を許容する数式にはそれぞれ右図のように記入しておきます預金の預け入れの場合は現金支出と預金収入に同じ金額を記入してください引き出しの場合は現金収入と預金支出に同じ金額を記入します仮払金を支出したときには清算時に仮払金戻りの収入処理をしてから実際に必要とした金額を該当する集計項目の支出として処理するようにします仮払金と実際に必要とした金額の差額を仮払金戻りとして処理するのは間違いです残金の計算をしているのが現金残高と預金残高の 2 つの欄です現金残高 N7 の式を考えてみましょう普通に考えれば =N6+J7-K7 とすればよさそうですがこれだけでは入力のない行も残高が計算されてしまいますこれを防ぐため =IF(F7="", "", N6+J7-K7) としますすなわちセル F7 の入力が済んでいることを条件に表示するようにしますただしこれだけでは十分ではありません後で述べるソートや並べ替えを行うとエラーが発生してしまうからですこのために使っているのが IS 関数です IS 関数と呼ばれるものには ISTEXT ISNUMBER などがありますが指定した対象の種類をチェックし TRUE または FALSE を返します ISNUMBER 関数を使いセル N7 は =IF(ISNUMBER(N6), IF(F7="", "", N6+J7-K7), "") とし一行上に残高があることを確かめて表示するようにしています同様に最初の N6 は =IF(ISTEXT(N5), IF(J6="", "", J6), "") とし上にタイトルが書いてあることを確かめて表示しています同様な操作を預金残高に関するセル O6, O7 についても行っておきます 3 / 8

金額に関する現金収入現金支出預金収入預金支出現金残高と預金残高の 6 つの欄の 6 行目 7 行目を選んでおいてプロパティ画面を開き右図のように通貨に設定し記号をなしにしておきましょうまた月日現金収入現金支出預金収入預金支出の 5 つの欄の 6 行目 7 行目にはデータ入力規則の日本語入力タブを開いて

と書き込みますこれでこの領域が集計項目と名付けられたことになります現金収入現金支出預金収入預金支出の書き込み領域 J6:J305, K6:K305, L6:L305, M6:M305 についてもそれぞれ現金収入現金支出預金収入預金支出と名付けます会計簿シートで記入できるのは 300 行にしてありますが

4 金額に関する現金収入現金支出預金収入預金支出現金残高と預金残高の 6 つの欄の 6 行目 7 行目を選んでおいてプロパティ画面を開き右図のように通貨に設定し記号をなしにしておきましょうまた月日現金収入現金支出預金収入預金支出の 5 つの欄の 6 行目 7 行目にはデータ入力規則の日本語入力タブを開いて日本語入力をオフ ( 英語モード ) に設定しておきましょうこれらが済んだらセル C7 から O7 までの 13 のセルを選んでおき下方の番号 300 までドラッグして全体を完成させます合計について会計簿シートで集計に必要となる領域を定義しておきましょう集計項目欄の書き込み領域 F6:F305 を選んで名前ボックスに集計項目と書き込みますこれでこの領域が集計項目と名付けられたことになります現金収入現金支出預金収入預金支出の書き込み領域 J6:J305, K6:K305, L6:L305, M6:M305 についてもそれぞれ現金収入現金支出預金収入預金支出と名付けます会計簿シートで記入できるのは 300 行にしてありますが行数を増やすには最終行を下方にドラッグしてくださいただし上で定義した領域の大きさは元のままですから数式タブにある名前の管理を開いて集計項目現金収入現金支出預金収入預金支出の領域を修正する必要があります右に名前の管理を開いた画面を示します集計項目の欄を見ると = 会計簿!$F$6:$F$305 と定義されていますセルを F6 4 / 8

5 と参照する方法を相対参照ドル記号を付けて $F$6 と参照する方法を絶対参照と呼びます相対参照ではセルをコピーすれば参照先が変化しますが絶対参照ではコピーしても参照先に変化はありませんこの画面から分かるように名前を付けられた領域は絶対参照で指定されているわけです会計簿シートの行数を増やした場合は図の赤丸で囲んだ部分を変更します会計簿シートに合計欄を作っておきましょうセル J3, K3, L3, M3 にそれぞれ現金収入現金支出預金収入預金支出の合計を求める式を書きます合計を求めるには SUM 関数を使いますセル J3 に =SUM() と書きカーソルを () の中において J6 をクリックし J305 までドラッグしますセル J3 は =SUM(J6:J305) となりますが直ぐ =SUM( 現金収入 ) と書き換わりますすなわち領域名で合計が求められます同様にして K3, L3, M3 に =SUM( 現金支出 ) =SUM( 預金収入 ) =SUM( 預金支出 ) という式を入れておきますセル N3, O3 には残高を求める式 =J3-K3, =L3-M3 を入れておきます設定シートについて自治会の決算報告に使う会計費目と補助金申請に使う会計費目とは通常異なっていますここに示したのはあくまでサンプルですが補助金申請用集計表シートと自治会集計表シートで使われている会計費目は大分異なっています例えば補助金申請での防犯灯維持管理費という項目はこの例の自治会ではこれまで電灯料と修繕費に分けて会計処理を行ってきたという経緯がありますまた補助金申請では会館借上料会館光熱水費会館修繕費などを事務費に計上していますがこの自治会では一括して別項目として計上していますこれらの違いを吸収するために設定シートで集計項目を決めています設定シートの左側には補助金申請用の会計費目を並べていますこれと対比する形で真ん中の集計項目の列に自治会の会計費目を割り付けてみます補助金申請の防犯灯維持管理費は 2 行に分けられ防犯灯電灯料と防犯灯修繕費としていますこのようにして左側の各会計費目に対応する言葉を真ん中の集計項目の列に入力しすべて埋まるようにしてください色の付いた領域を収入支出移動の 3 つのリストとして定義しています会計簿シートで集計項目を入力するときは区分で選んだ内容に従ってドロップダウンリストの内容が収入支出移動の何れかに切り替わります移動のリストに使っている言葉は会計簿シートで金額欄の入力制限に使っているため変更しないでください集計表シートについて集計表シートでは会計簿シートでの集計項目ごとの現金収入現金支出預金収入預金支出の集計を計算します先ずこれらの項目に収入計支出計を加えて 7 列の表を作ります集計項目欄の最初のセル B6 には = と書いておき設定シートを開いてセル F5 をクリックして Enter を押します集計表シートのセル B6 には = 設定!F5 と入り会費と表示されますこのセル B6 をクリックして下方にドラッグすれば他の項目名も自動的に入りますこのようにして項目名の入った表を完成させます集計の計算に使うのは SUMIF 関数です SUMIF 関数の書式は =SUMIF( 範囲, 検索条件, 合計範囲 ) です最初の範囲の中から 2 番目の検索条件に一致するものを探し出し 3 番目の合計範囲にある夫々の数値を合計した値を返しますこの関数を使って会計簿シートの集計項目欄の中から例えば " 会費 " と書いてあるものだけを探し出し現金収入欄にある数値を合計するには次のようにします 5 / 8

6 範囲 : 集計項目欄にあるデータ範囲 ( 集計項目と名付けられた領域) 検索条件 : " 会費 " 合計範囲 : 現金収入欄にあるデータ範囲 ( 現金収入と名付けられた領域) すなわち =SUMIF( 集計項目, " 会費 ", 現金収入 ) という式になります従ってセル C6 には =SUMIF( 集計項目, B6, 現金収入 ) という式を入れればよいことになりますこのセル C6 をクリックして下方にドラッグすれば現金収入の列が完成します同様にして現金支出預金収入預金支出収入計支出計の表を完成させます最後に現預金移動の表を作り収入合計支出合計を加えて現在の残高を計算しておきますここに示す残高は会計簿シート上部の残高と同じ値でなければなりません時折チェックに使ってください補助金申請用集計表自治会用集計表シートについて補助金申請用集計表自治会用集計表シートには集計表シートで計算した収入合計および支出合計の数字を写します補助金申請用集計表自治会用集計表シートのそれぞれにはタイトルとして項目予算現在該当集計項目の 4 つを作り収入の部と支出の部の項目名を決められた様式によって書き込みます該当集計項目というのは集計表シートで使った集計項目です現在欄に集計表シートの計算結果を写します補助金申請用集計表シートの会費のセル F5 にはこのセルに = を入れておいて集計表シートを開き収入計欄の会費のセル G6 をクリックし Enter を押しますすると補助金申請用集計表シートの会費のセル F5 には = 集計表!G6 という式が入ります集計表シートの 2 つの行をまとめるには + 記号で結びますこのようにしてそれぞれの表を完成させます予算欄はこの会計簿を使い始めるときに書き込んでくださいシートの保護についてセルのプロパティを開き保護タブをクリックするとロックと表示しないの 2 つの設定項目があります通常はロックにチェックが入っています校閲タブにあるシートの保護をクリックすると小さな条件設定の画面が開かれますがそのまま OK ボタンを押すとロックにチェックの入っているセルはロックされ書き込みが禁止されます会計簿シートの薄いピンクの色が付いた箇所は入力を行う箇所ですからセルのロックを外しておきます通常は誤操作防止のためシートを保護した状態で使ってくださいシート保護の解除をクリックするとロックされていたセルの編集が可能になりますなおセルを表示しないに設定しておくとシートの保護にした場合数式は非表示になるようになっていますフィルター機能を活用しようこの機能を使うには最初にシートの保護を解除しておきます会計簿シートのタイトル行にある任意のセルをクリックしておきホームタブにある並べ替えとフィルターからまたはデータタブからフィルターをクリックすると全てのタイトル項目にドロップダウンリストを開くボタンが付きこのボタンをクリックしてリストの中で指定したデータを抽出することができます例えば集計項目のドロップダウンリストを開くと ( すべて選択 ) にチェックが入っていますがこれを外し会費のみにチェックを入れて OK を押せば会費のデータのみが抽出されますまた月度のドロップダウンリストを開き例えば 6 月のデータのみを抽出することができますこれをコピーして新しいシートに張り付け少し加工すれば 6 月度の収支明細表などとして使えます同様にして 4 月 ~9 月を指定してデータを抽出し上期収支明細表なども作れます 6 / 8

7 抽出された状態を元に戻すには再度フィルターメニューをクリックします並べ替えを行うときの注意預金の出入りは銀行に出かけた日に記入するのが普通でしょうから会計簿シートの月日は日付順になっているとは限りませんこのため日付順に並べ替えてみたくなりますまた区分や集計項目で並べ替えてみたいこともあるでしょう並べ替えを行うには最初にシートの保護を解除しておきます並べ替えを行いたい列のどこかのセルをクリックしておいてホームタブにある並べ替えとフィルターからまたはデータタブから昇順または降順をクリックするとその列の並べ替えを行うことができます並べ替えをおこなうと前年度繰越金が 1 行目にあるとは限らなくなり現金残高預金残高が正しく計算されず意味を持たなくなりますこのため ISNUMBER 関数を使い 1 行上の行が数字かどうかを調べていますこれにより前年度繰越金が最初の行から別の行に移ったときは残高の表示が消えるようになっています並べ替えを行ったら元に戻す操作を忘れないでください番号の列を昇順に並べ替えれば元に戻ります別シートにコピーするときの注意集計表シートなどをコピーして別シートに貼り付けるときそのシートの中に書かれている数式をコピーしたいのかそれとも表示されている数値をコピーしたいのかによって張り付け方が異なることに注意してくださいコピー操作をした後で別シートを開き張り付けたいセルを指定してホームタブにある貼り付けの下のをクリックすると貼り付け値の貼り付けなどに分類されたいろいろなオプションのボタンが表示されそれらの上にマウスを移すとどのように張り付けられるかを表示してくれます数式も含めてそっくりコピーするには貼り付け元の列幅を保持を使えばいいでしょう数式ではなく表示されている数値をコピーするには値の貼り付け値と数値の書式を選ぶといいでしょうこの自治会会計簿を改造するときの注意 1. 設定シートには補助金申請用会計費目に対応する形で集計項目欄が設けてありますこの集計項目に自治会の会計費目を割り付けてみて集計項目の変更削除追加などを行ってください 2. 設定シートの集計項目名を変更すれば会計簿シートの集計項目のドロップダウンリストが変わりますまた集計表シートの集計項目名が自動的に変更されます会計簿シートの記入済みデータは自動的には書き換わりませんから使い始めてから集計項目名を変更するときは変更に関係する集計項目は入力し直すなどの処理が必要になります 3. 不要な集計項目を削除するには先ず集計表シートを見てその項目に対して集計値が 0 であることを確認してください未使用の項目であることを確認したら設定シートで該当する行を削除します集計表シートを見ると #REF! というエラー表示が出ていますからその行を削除します 4. 新しい集計項目を追加するには設定シートで追加したい場所に新しい行を挿入して項目名を記入します次に集計表シートで該当する場所に新しい行を挿入し数式の書いてある上の行のすべ 7 / 8

8 てのセルを下方にドラッグしてコピーします上下の行のセルで使われている数式を調べ式の中で使われている行番号が連続していることを確認してください 5. 補助金申請用集計表シートは設定シートで集計項目の削除や追加を行った場合該当項目の現在欄のセルに書いてある数式を修正する必要があります 6. 自治会用集計表シートはここに示した例を参考にして自分の自治会の様式で全面的に書き直してください以上 2016/01/20 8 / 8

次の病院薬局欄は氏名欄に入力された値によって入力すべき値が変わります太郎の行く病院と花子の行く病院が必ずしも同じではないからですこのような違いを設定シートで定義しておきましょう太郎の行く病院のリストを太郎花子の行く病院のリストを花子として 2 つのリストが定義されていますこ

次の病院薬局欄は氏名欄に入力された値によって入力すべき値が変わります太郎の行く病院と花子の行く病院が必ずしも同じではないからですこのような違いを設定シートで定義しておきましょう太郎の行く病院のリストを太郎花子の行く病院のリストを花子として 2 つのリストが定義されていますこ医療費の入力と集計まえがき医療費は一年間の合計を計算し 10 万円を超えていれば税務申告に際して医療費控除を受けることができますそこで医療費を記入するたびに自動集計される仕組みを考えてみましょうここで紹介する医療費の入力と集計は税務申告で必要となる医療費のデータを作成するのに使うものです特徴はドロップダウンリストから簡便に入力ができ入力と同時に自動集計されるようにしてあることです