トレビのHP

バージョンExcel２０００はこちら
旧2007作成のお部屋はこちらに異動しました。

ようこそお越しくださいまして有難うございます。
こちらEXCEL2007での会計簿作成でございます。
この度以前の作成マニュアルを加筆修正致しました。
何分色々な面で素人で解かり難い点多々有りますが
下手の横好きとでも言いましょうか現在私は
商売をリタイヤしている身ですが長年培ってきた
経験と現役時には成し得なかった事項の数々をを
少しずつクリアして行けたら幸いに思っております
未熟者ですがどうぞ、、お付き合いくだiさい

追伸　最終行に完成ファイル置いています。

勘定科目設定 出納簿 月日入力 月算シート データーべース関数設定 決算シート
ステップ１　　ワークシートの準備
EXCEL２００７を開いて シート見出しを５つに増やす。 左の図参照、矢印のとこをクリックしたら増えていく 右クリックで名前の変更
バージョンが違いますが↑の補強です。
シート名を年号、科目、出納、月算、決算、にする
ステップ２、年号シートから表紙を作る
年号シートタブをクリックン年号シートを開く 矢印の所をクリックして全セル選択
ここでは、A列を右クリック列の幅をクリック 列幅ダイアログで５に設定 これは後でこの小さなセルをデータ−ベース関数で 使えるように準備しておきます
会計の開始月を入力、後で入力規則の設定の為の準備 A1セルに１、A２セルに４、を入力 １月から始めるか４月から始めるかの意味 F７セルに年度と入力 D6セルに会計年号を入力ここでは２０１6と入れている Ｃ１６セルに会計期間と入力 D１７、セルに月から D１８、セルに月までと入力
ここでA1A2に入力した１と４を入力規則を設定するのに使います ↑ピンクの画像参照 C１７セルをクリック ツールバーのデータータブをクリック データーの入力規則をクリック入力規則ダイアログを出す 入力規則ダイアログボックスを開いて 設定タブをクリック 入力値の種類ボックス下向▼をクリック リストを選びます↑図参照 元の値ボックスにカーソルを置いて Ａ１：Ａ２を選択すると自動でセルが固定 絶対参照になる、OKをクリック Ｃ１７セルをクリックすると 下向▼が付き１と４の数字が入っていると成功 C18セルに　IF関数設定 次はC１８セルにIF関数を入れる。 もしも、C17セルが１の場合は１２を４の場合は３を表示する式 =IF(C17=1,12,3) ↑の図参照、関数パレットから指示に従って行けば簡単です 関数パレットの出し方、 数式バーに＝を入れると左横の名前ボックスが関数ボックスに成って 最近使った関数が上に１０個出ています。 ＩＦを選びます。 ここにＩＦが出ていない場合は 一番下のその他の関数をクリック 全て収納してある関数ボックスから選びます。 ↑のピンクの画像参照数式バーに＝を入れたところ 数式バーのFXをクリックしてもよい、 、
↑これは年号シート 列幅５で表紙のタイトル等は、 ワードアートで可愛くもお洒落にも自由自在 表紙の大きさは適当ここでは B3:Q20位の大きさで、セルの書式設定で罫線等々使ってる。 ワードアートは、 ツールバーの挿入タブをクリック、 ワードアートを選択、好きなネーミングを起用
ステップ３、勘定科目の設定
科目シートを開く このシートに取引勘定科目を設定いたします A1セルにシートのタイトル勘定科目と入力y 適当な大きさでセルの結合見やすくなる。 Ｂ２にタイトル勘定科目と入力、Ｃ２に番号と入力 ご自身のご商売に合った様に考えて下さい。 ここでは 収入の部には７行取っています 収入に繋がる相手科目が多ければその数分多くとってください 支出の部も同様に増減して下さい。 私の場合掛け仕入れ等、支出に繋がる 相手科目を全て支出に入れるようにしました そうする事で決算に直反映し 手抜きズル出来ますから(笑) 何項目でも自由 ここでは、Ｂ３：Ｃ２８を範囲選択して 見やすいように罫線で囲み 収入101〜収入107 一行空けて 支出１〜支出25としています。
僭越ながら↑の勘定科目画像は私の 商売に合ったような科目名で設定したところです参考に・・・ 項目が多すぎるとリストＢＯＸに入りきらないかもその場合修正してください。
ステップ４、情報発信基地出納簿
出納シートを開き、↑図のようなフォームを作るセル番地は同様に B１にタイトル出納簿と入力Ｂ１〜H１までセルを結合して、中央揃え、 今はＥ３の(記帳中）は無視して置いてください。 一日平均3行使うとして年間で１０００行位、 （これはお好みで結構です） Ｂ４：H１００４を選択して セルの書式設定ダイアログを表示 罫線タブやパターンタブで見易く ※　セル番地は、↑の図と同様に・・・・・
↑の画像赤丸のところ　　M１セルに記録中と入力 M２セルに終了と入力 Ｅ３セルをクリック ツールバーのデータータブ　━　入力規則で データー入力規則ダイアログを出します 設定タブをアクティブにして 入力値の種類ボックスの下向▼をクリック リストを選びます。 元の値ボックスにカーソルを置いて M１〜M２を選択OK Ｅ３セルに下向▼矢印が付いて リストボックスが出来ていれば成功！！ 出納簿のデーターを入力する時は、 記帳中を選び 入力が終わると、終了を選ぶ、 科目名が自動で入力出来るように設定、 先ほど入力規則を設定したＥ３に循環参照させる関数、 Ｅ３セルがM２セルと同じ（真の場合と言う）であればM２を表示させ この場合等号記号の＝を使います 違う場合（偽の場合と言う）は科目シートのＢ３を表示 K１から勘定科目を入れる数分ＩＦ関数を入れます K１をクリック　↓この数式を入れます =IF($E$3=M$2,M$2,科目!B3) ↑関数パレット、もしも、E3セルが終了であれば、真の場合M2を表示して 記帳中であれば、偽の場合 科目シートの勘定科目を表示させる式 K1から勘定科目の数分コピー、科目シートの勘定科目が表示すれば成功！！ 収入と支出の境目を空白して置くと良い、↑の出納簿の図参照 Ｅ３のセルで記帳中を選ぶとK列に 科目シートの勘定科目名が表示され Ｅ３のセルで終了を選ぶと K列に終了と表示されれば成功！！ ↑図と、↓図参照
月、日、がクリックで入力出来るように設定
データー入力する時の月列に当月を先頭に来させる設定 I１セルにＭＯＮＴＨ関数を入れる I１をクリック　↓この数式を入れる =MONTH(TODAY()) ＭＯＮＴＨ関数パレットで シリアル値の入力バーにＴＯＤＡＹ関数を入れる 関数に関数を組み合わせる事を関数のネストと言う ＭＯＮＴＨ関数パレットのシリアル値にカーソルが有る事を確認して 関数ボックスからＴＯＤＡＹ関数を選択、 I１セルに当月が表示されたら成功です。 続いてI ２セルに式を入れる ＩＦ関数を使って上のセルの値に1つ加える式 しかし1年は12ヶ月ですので先ず 真の場合、上の値が１２（12月）だと等号記号＝で１（1月）にする 偽の場合、上のセルの値に＋１とする↓図参照
I ２セルにI１セルの次の月が表示されたら成功！！ I２セルをクリックしてI １２迄コピー 12ヶ月が表示されたら成功！↑の図、出納簿を参照 このリストを月入力に使う 次に、Ｊ列に日列の設定する 同様に当日を先頭に来させる為に ＤＡＹ関数とＴＯＤＡＹ関数をネスト、 Ｊ１をクリック、↓図参照、 上のセルの値に　＋１　する数式を31迄入力 Ｊ１セルが３１（３１日）であれば 真の場合意１（１日）を表示 偽の場合は上のセルの値にプラス１する事 Ｊ列のリストを日付入力に利用する J J１に本日の日が表示されていれば成功です。 J２セルに月の場合と同様にＩＦ関数を使って 上のセルの値に　＋１　する数式を31迄入力 Ｊ１セルが３１（３１日）であれば 真の場合意１（１日）を表示 偽の場合は上のセルの値にプラス１する事 Ｊ列のリストを日付入力に利用する Ｊ２セルが、Ｊ１セルの値に１が＋されていたら成功！！ Ｊ３１セルまでフィルハンドルコピー これで月と日と科目のリストが完成
入力規則を設定 前回入力規則と同様に入力値の種類はリスト 月の列、Ｂ７セルからＢ列の最後のセルまでと 日の列Ｃ７セル〜最後のセルまでと 科目名の列Ｅ７セル〜最後までをフィルハンドルコピー 月列日列科目名列其々にリストボックスが付いて簡単入力ＯＫ 注※　上のＩＦ関数パレットは、Ｊ２をクリックした日にちが２９日だった為に値は３０に成っている、
番号列に式を入れる。IF関数とVLOOKUP関数のネスト
科目が選択されたら、準備シートの勘定科目、右列の番号を VLOOKUP関数で検索させて出納シートの番号列に表示させる式 ここでは、Ｄ７，セルをクリック下の式を入れる =IF(E7="","",VLOOKUP(出納!E7,科目!$B$3:$C$35,2,FALSE)) もしも、Ｅ７セルが真の場合空白なら空白を表示させ 違う時、偽の場合いは、VLOOKUP関数 で、Ｅ７の勘定科目を、準備シートの勘定科目に割り当てられた番号を、検索させる式 関数パレットはこのように成る↓
科目名のリストボックスから科目を選び番号列に 科目シートの２列目に割り当てられた数字が検索されてたら 成功！！列最後のセルまでフィルハンドルコピー Ａ６セルにタイトル金額と入力 Ａ７セルに収入＋支出の式を入れるここでは、=F7+G7最終セルまでフィルハンドルコピー これってつまりは収入欄と支出欄に金額が入っていればそれを表示してるだけ、 この金額データー及び月及び番号を月計シートから検索させるデーター いよいよこのシート最終設定 残高列Ｈ７セルに=H6+F7-G7を入れて最終セルまでフィルハンドルコピー 確認作業しながら、パグがないか調べる ※補足収入列と支出列に条件付き書式、収支間違いを防ぐ設定 収入列のＦ７をクリック↓の画像 ↑ツールバーのホームタブをクリック 条件付き書式をクリック、プルダウンメニューの 新しいルールをクリック ↓新しい書式ルールダイアログでルールの種類数式を使用して、、、 を選択ルールの内容を、 Ｄ７セルの数字が100より小さいと赤文字で警告する設定 =D7<=100　とする。　書式をクリックしてフォントの色を赤に 支出列のＧ７には=D7>=100とする 同様にＤ７セルの数字が100より大きいと赤文字で警告する設定をする 最終行までフィルハンドルコピー ↓図参照 ↑は、収入と支出欄を間違って入力した例 出納簿の最初へ　　このページのＴＯＰへ
ステップ5　月算シートの作成
月算シートを開いて、以下のようなフォームを作る 罫線の色等はご自身のセンスで、見易いように、 画像の7行目塗りつぶしてるところが合計行です。 収入と支出の境目を空白にした部分です。 ご自身のご商売に合った任意の数分設定して下さい．
科目列の設定
月算シートのＡ４セルに シートの勘定科目を参照させる式=科目!B3　の数式を入れます Ａ４をクリック数式入力バーに＝を入れて シート見出し科目をクリック 科目シートのＢ３をクリックエンターキーで確定 月計シートのＡ４に　収入１　　が表示されたら成功！ Ａ36セルまでフィルハンドルでコピー 空白に成っているＡ11には合計と入力 次に ☆Ｃ３　セルから横へＮ３　セルまで１〜１２月を 表示させる設定の準備 会計期間が1月〜12月の場合と ４月〜翌年の3月の場合を Ｅｘｃｅｌに判定させたい為の設定 年号シートをクリック 年号シートの　Ａ２３　セルに　 会計期間の月を参照させる式 =$C$17 Ａ２３セルをクリック数式入力バーに＝を入れて Ｃ１７をクリック　Ｆ４キーを押して絶対参照、 Ｉ ２３　セルまで横にフィルハンドルでコピー Ｄ２３より＋１、＋２と言う風に付け加える 例えばＢ２３セルは　=$C$17+1 Ｄ２３セルには　=$C$17+2 Ｉ ２３セル　=$C$17+8まで付け加える 次の、Ｊ２３セル〜Ｋ２３セルＬ２３セルには ＩＦ関数を設定、 会計期間が1月〜12月か、 会計期間が４月〜翌年の3月か、 先ずＪ２３セルには、=IF($C$17=1,10,1) Ｋ２３セルには、=IF($C$17=1,11,2) Ｌ２３セルには、=IF($C$17=1,12,3) 以上3つのセルの数式、↓図参照 入力出来ましたら確認作業する C17セルを１にした時、A23セルからL２３セルが１〜１２に C17セルを４にした時４〜３に成ってたら成功！！ 以上で会計期間の月を参照する作業が終わり！ 続いて月算シートのＣ３〜〜Ｎ３迄に 先ほど設定した式を参照させる式を入力、 =年号!A23〜=年号!L23 月算シートのＣ３をクリック年号シート見出しをクリック Ａ２３をクリックでＯＫ、Ｎ３迄横にコピー A4セル〜A29セルとC3セル〜〜Ｎ１２セルが ↓図のように成れば成功！！ 月の文字は後から設定したもの 見やすくする為、C3セル〜〜Ｎ１２セルに月の文字を設定 C3セルをクリックセルの書式設定ダイアログの表示タブで ↓図のようにするユーザー定義を選択 #,##0を探して"月"ダブルクォーテイションでくくる 一回設定しておけばまた使える
ステップ6　データーベース関数の設定
出納シートからデーター入力すると その月の収入及び支出の合計を検索して 月算シートに反映させる為 色々な方法は有るが ここでは、データーベース関数を使う データーベースの元となるクリテリアの入力 年号シートの空きを利用する 年号シートを表示、空きのセルを利用して、以下のような表を作る 月は列、横に、１月〜１２月まで　 その時月の列には前に設定した会計期間の 月を参照させる為のセルを参照させる↓図 セル番地が違いますが1月は赤丸印↓のセルを参照ている 又2月の列は赤丸の横の２を参照させる 画像が解り難いですが悪しからず 番号は行、縦に勘定科目の数分 ここは収入分が１01，10２、10３、104、105、106、107、 支出分が１〜27まで設定していますが余らせても影響無いと思います
上の画像はデーターベース関数で使う Ｃｒｉｔｅｒｉａ（クリテリア＝検索の条件）。 Ａ3１セルに月と入力Ａ3２セルに１月を参照させる式=$A$23を入力 Ｂ31セルに番号と入力、Ｂ3２セルに収入1の番号101を入力 Ａ31：Ｂ32（月と１と番号と１01とでワンセット） 縦に収入の数と支出の数分、横に１月から１２月分、こしらえる 例えば準備シートで収入が7つ支出が２7だったら3２行分作る 次に月算シートの科目収入１と１月が交わるセルに式を入れる↓
ここでは、データーベース関数、　ＤＳＵＭを使ってる　 この関数の意味は、 データーベースの指定されたフィールド列を検索し 条件を満たすレコードの合計を返す 式　　ＤＳＵＭ（Ｄａｔａｂａｓｅ.フィールド.Criteria) 　　　　　　　　範囲　　　　指定列　　　条件 =DSUM(出納!$A$5:$D$1006,1,年号!A31:B32) ここでは↑の式を入れている 範囲は出納シートの金額列から番号列迄、 フィールドは出納シートの１列目 収入＋支出の式を入れた列　　結局は片方が空欄で 金額を入力した額が出てる、 その列の月と科目がクリテリアに合っているのを 探して合計している。 ↑の画像は私の勝手で試し打ちしてるので少し見にくいですが・・・ １月の収入１のセルに式が入れば 次からが、細かい作業の開始！！
年号シートのクリテリアのセルを相対参照させる為に フィルハンドルは、一つづつしかずれない所を、 ２つズレにしていかねばならない 例えば初めのセルが　=DSUM(出納!$A$5:$D$1006,1,年号!A31:B32)で　 その下のセルは　　　　　　=DSUM(出納!$A$5:$D$1006,1,年号!A33:B34)にする 先ず1月の、縦にフィルハンドルコピーして後数字を直していく 次は上の画像で2行目にAB CD EF　と入れてそれを目印にアルファベットを直していく 先ず1月の列の数字が直せたら1月列を横に12月までフィルハンドルコピーして、 そして2月列だったらＣＤに直して行くと少し楽かな？あくまでも私感ですが？？ これも慣れれば結構楽しいもの、内職してるみたいで・・・（笑） 全セル詰める、合計行、列、、残高行に式を入れると完成！！ 楽しい確認作業（笑） このページのＴＯＰへ
ステップ7　決算シートを作る
決算シートを開いて 以下のようなフォームを作る
科目列の収入１〜支出２5までに式を入れる （画像は私感で科目シートで設定しているものです。） （相手勘定科目はご自身のご商売に合うように科目シートで設定してください。） 科目シートの科目を参照する式　C5セルに　=科目!B３を入れる 勘定科目列にフィルハンドルコピー合計欄は空白セルにして置く 例えは↑の画像でしたらC5:C6とフィルハンドルコピー1行空けて C8をクリック科目シートのB5をクリック　 =科目!B5を入れて収入合計までフィルハンドルコピー 確認作業例えば科目シートの 収入欄に売上と入れてみる。 決算シート、出納シートのリストＢＯＸ、月算シート、 に表示されたら成功！！ 決算額列には月計シートの合計欄を参照させる ここでは、=月計!O4としている 収入、支出、末行迄コピー 収入、支出、の合計行に式を入れる　 合計はＳＵＭ関数
収入合計　　=SUM(D5:D6,D8:D12) 支出合計　　=SUM(D14:D18,D21:D40) 決算欄に式を入る 収入合計ー支出合計 =D13-D41 月計シートの差引残高ここでは、（O31セル） 額が一緒であれば成功！これが決算額 最終仕上げ出納シート E3セルリストＢＯＸで記帳中、＆終了を選んだ時の表示設定 条件付き書式の設定で終了の時は、決算シートに決算額が表示されて 記帳中の時は空白（見えなく）にする 先ず決算シートのJ 5セルに=出納!$E$3の式を入れる 決算表のデーター部分を選択（何処でもよい） 条件付き書式をクリック新しいルールをクリック 数式を使用して、・・・を選択次の数式を満たす場合、、、、の下の 入力窓にカーソルを置きJ 5をクリック ＝を入れて”記帳中”とする その後書式ボタンを押してセルの書式設定ダイアログでフォントを白に設定する。 ↓図のように 出納シートにデーターを入れて記帳中、終了と試してください。
以上で一応完成と致します。 この後の作業では 入力に必要無いデーターは折りたたんで隠したり excelのｵﾌﾟｼｮﾝ詳細設定、等で 0値は表示させないとか シートの保護を掛けるとか 効率の良い方法を 色々楽しみながらやって下さい では、最後までお付き合いお疲れ様でした そして有難うございました。 文章不足お見苦しく至らぬ点、多々、お許しください そしてパグ等、お気づきでしたら お知らせください トレビ ＨＰ、ＴＯＰ http://torebi.sakura.ne.jp/index.htm ＭＡＩＬ xva93677@meg.winknet.ne.jp ＢＢＳ http://torebi.sakura.ne.jp/cgi-bin/tore/joyful.cgi?bbs=0 完成ファイル こちらからロードしていただけます。