[[20110119110820]]　『現金出納簿作成』（OH!）　ページの最後に飛ぶ

『現金出納簿作成』（OH!）
関数、マクロ、ＶＢＡなんでも結構ですのでご指導お願いします。

 【Sheet1】
 〈収入日計表〉
 　　　　　Ａ　　　　　Ｂ　　　Ｃ　　 Ｄ　　　 Ｅ　　　　　Ｆ　　　 Ｇ
 １　　　　　　　　   　 　○○支店                     　　　　○○支店
 　┌──────────────┐　　┌──────────────┐
 ２│　収入日計表　１月　４日分　│　　│　収入日計表　１月　４日分　│
 　├─────┬─────┬──┤　　├─────┬─────┬──┤
 ３│　種　目　│　金　額　│枚数│　　│　種　目　│　金　額　│枚数│
 　├─────┼─────┼──┤　　├─────┼─────┼──┤
 ４│　使用料　│ 10,000円 │ ２ │　　│　占用料　│ 12,000円 │ ２ │
 　├─────┼─────┼──┤　　├─────┼─────┼──┤
 ５│　山田太郎│  5,000円 │ １ │　　│ 明石義男 │　6,000円 │ １ │
 　├─────┼─────┼──┤　　├─────┼─────┼──┤
 ６│　鈴木一郎│  5,000円 │ １ │　　│ 佐藤　武 │　6,000円 │ １ │
 ～～～～～～～～～～～～～～～～～～~～～～～～～～～～～～～～～～～～
 14│　 合 計　│ 10,000円 │ ２ │　　│　 合 計　│ 12,000円 │ ２ │
 　└─────┴─────┴──┘　　└─────┴─────┴──┘
 15　　　　　　　   　 　○○支店                     　　　　○○支店
 　┌──────────────┐　　┌──────────────┐
 16│　収入日計表　１月　４日分　│　　│　収入日計表　１月　４日分　│
 　├─────┬─────┬──┤　　├─────┬─────┬──┤
 17│　種　目　│　金　額　│枚数│　　│　種　目　│　金　額　│枚数│
 　├─────┼─────┼──┤　　├─────┼─────┼──┤
 18│　負担金　│ 10,000円 │ ２ │　　│　施設料　│ 12,000円 │ ２ │
 　├─────┼─────┼──┤　　├─────┼─────┼──┤
 19│ 斉藤　始 │  5,000円 │ １ │　　│ 坂本竜馬 │　6,000円 │ １ │
 　├─────┼─────┼──┤　　├─────┼─────┼──┤
 20│ 沖田宗司 │  5,000円 │ １ │　　│ 西郷隆盛 │　6,000円 │ １ │
 ～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
 28│　 合 計　│ 10,000円 │ ２ │　　│　 合 計　│ 12,000円 │ ２ │
 　└─────┴─────┴──┘　　└─────┴─────┴──┘
 このような様式が項目ごとに、様式内に納まらなければ同じ項目の様式が毎日追加される。
 このデータを「シート２」で日付順で整理したい。

 【Sheet2】
 〈現金出納帳〉
 　　　　Ａ　　　　Ｂ　　　　 Ｃ　　　Ｄ　　　 Ｅ　　　  　Ｆ　　　　　Ｇ
 　┌────┬─────────────┬─────┬─────┬─────┐
 １│ 　　 　│　　　種　　　　　目　　　│　　　　　│　　　　　│　　　　　│
 　│ 日　付 ├─────┬────┬──┤ 収入金額 │ 支出金額 │ 差引残高 │
 ２│　　　　│ 　種別　 │氏　　名│件数│　　　　　│　　　　　│　　　　　│
 　├────┼─────┼────┼──┼─────┼─────┼─────┤
 ３│ 1月 4日│　使用料　│山田太郎│ ２ │　10,000円│　　　　　│　　　　　│
 　├────┼─────┼────┼──┼─────┼─────┼─────┤
 ４│　　　　│　占用料　│明石義男│ ２ │　12,000円│　　　　　│　　　　　│
 　├────┼─────┼────┼──┼─────┼─────┼─────┤
 ５│　　　　│　負担金　│斉藤　始│ ２ │　10,000円│　　　　　│　　　　　│
 　├────┼─────┼────┼──┼─────┼─────┼─────┤
 ６│　　　　│　施設料　│坂本竜馬│ ２ │　12,000円│　　　　　│　　　　　│
 　├────┼─────┼────┼──┼─────┼─────┼─────┤
 ７│ 1月 4日(赤)　　　　│　日計　│ ８ │　44,000円│　　　　　│　44,000円│
 　├────┼─────┼────┼──┼─────┼─────┼─────┤
 ８│ 1月 5日│　占用料　│山田花子│１０│　30,000円│　　　　　│　　　　　│
 ～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
 　│ 1月14日(赤)　　　　│　日計　│ ８ │　50,000円│　　　　　│ 270,000円│
 　┝━━━━┷━━━━━┷━━━━┿━━┿━━━━━┿━━━━━┿━━━━━┥
 　│ 1月4日～1月14日分を本店へ納入│ 150│　　　　　│ 270,000円│ 　　　0円│
 　└───────────────┴──┴─────┴─────┴─────┘
 収入日計表は毎日入力します。現金出納帳は、２週間に１回印刷します。
 出納簿に入力する時は収入日計表の項目別の代表者名で入力します。
 オートフィルタ＆コピーとかではなく、入力情報が現金出納帳に自動的に反映される方法を教えて下さい。

昨日からまったくコメントありませんが、やっぱり難しいんですかね？(OH!)

 難しいというよりも、めんどい。
「このような様式」というのはOH!さん独自の様式ですよね。
そこからまた独自の様式に「自動的に反映される方法」というのは
「指導」とか「教える」というレベルじゃなくて、
そういう仕組みの「開発」になっちゃいますから。
　
「元となるデータ」を比較的標準的な「リスト形式」としておいて、
そこから「収入日計表」「現金出納帳」に反映させる、というものなら
関数でも出来るかもしれません。
(みやほりん)(-_∂)b

 それでは、Sheet1を入力用の様式に変更し(Sheet1と同じ様式)
 その一覧から指定日を日計表に、指定期間を出納簿に反映させる事は可能でしょうか？
 最悪、一覧から指定範囲を出納簿に反映するだけでもかまいません。
 反映の際は上の図のように日別の小計、項目の最初の人を代表名て表示し合計をさせたいです。
 似たような質問の[[20060410102752]]http://www.excel.studio-kazu.jp/kw/20060410102752.html
 ではVBAで出来ているようですがVBAの内容が分からないので
 様式にあわせた変更が出来ませんでした。

まず、Sheet1の形式が標準的なリスト形式とはいえないので
せめて
　

  A   　 B   　 C    　D    E   　 F   
1日付　支店名　項目　氏名　件数　金額

のスタイルにして、あとはvlookとsumifの組み合わせ、もしくはsumproductか配列関数でいけると思います。

その際出納帳の項目はMAX設定しておき、印刷の際にゼロ項目を削除なり非表示なりするとか。

（ゆ）

 関数で、とも考えていましたが、「日計」がちょっとややこしかった。
　
 Sub test20110120()
     Dim myR As Long
     For myR = 3 To 100
         If myR Mod 2 <> 0 Then
             Worksheets("現金出納帳").Cells(myR, 1).Value = Worksheets("収入日計表").Cells((myR - 3) * 7 + 2, 1).Value
             Worksheets("現金出納帳").Cells(myR, 2).Value = Worksheets("収入日計表").Cells((myR - 3) * 7 + 4, 1).Value
             Worksheets("現金出納帳").Cells(myR, 3).Value = Worksheets("収入日計表").Cells((myR - 3) * 7 + 5, 1).Value
             Worksheets("現金出納帳").Cells(myR, 4).Value = Worksheets("収入日計表").Cells((myR - 3) * 7 + 4, 3).Value
             Worksheets("現金出納帳").Cells(myR, 5).Value = Worksheets("収入日計表").Cells((myR - 3) * 7 + 4, 2).Value
         Else
             Worksheets("現金出納帳").Cells(myR, 1).Value = Worksheets("収入日計表").Cells((myR - 4) * 7 + 2, 5).Value
             Worksheets("現金出納帳").Cells(myR, 2).Value = Worksheets("収入日計表").Cells((myR - 4) * 7 + 4, 5).Value
             Worksheets("現金出納帳").Cells(myR, 3).Value = Worksheets("収入日計表").Cells((myR - 4) * 7 + 5, 5).Value
             Worksheets("現金出納帳").Cells(myR, 4).Value = Worksheets("収入日計表").Cells((myR - 4) * 7 + 4, 7).Value
             Worksheets("現金出納帳").Cells(myR, 5).Value = Worksheets("収入日計表").Cells((myR - 4) * 7 + 4, 6).Value
         End If
     Next myR
 End Sub
　
準備データを簡単に作る、ということであれば、上記を実行すると、
編集はかなり楽になるかもしれません。
「収入日計表　１月　４日分」などの部分がシリアル値入力で表示形式にて表示
していると仮定しています。
(みやほりん)(-_∂)b

みやほりんさん

 ややこしいコードを開発して頂きありがとうございます。
 やはり日計は難しいんですね。
 『帳簿の集計』 （オレンジ） http://www.excel.studio-kazu.jp/kw/20060403190250.html
 にある
 Sub test()
    Dim myR As Range
    Dim r As Range
    Dim myDic As Object
    Dim myKey As Variant

    Application.ScreenUpdating = False
    With Sheets("Sheet2")
        .Cells.ClearContents
    End With

    Set myDic = CreateObject("Scripting.Dictionary")
    With Sheets("Sheet1")
        .AutoFilterMode = False
        Set myR = .Range("C2", .Range("C65536").End(xlUp))
        For Each r In myR
         myDic(r.Value) = myDic(r.Value) + r.Offset(, 2).Value
        Next

        For Each myKey In myDic.Keys
          .Range("A1").AutoFilter 3, myKey
          .AutoFilter.Range.Copy
          With Sheets("Sheet2")
             If IsEmpty(.Range("A1")) Then
                .Range("A65536").End(xlUp).PasteSpecial
             Else
                .Range("A65536").End(xlUp).Offset(2).PasteSpecial
             End If
             With .Range("A65536").End(xlUp)
                  .Offset(1).Resize(, 2).Value = Array("合", "計")
                  .Offset(1, 4).Value = myDic(myKey)
                  .Offset(1).EntireRow.Font.Bold = True
             End With
          End With
        Next
        .AutoFilterMode = False
        With Application
             .CutCopyMode = False
             .ScreenUpdating = True
        End With
    End With
    Set myR = Nothing
    Set myDic = Nothing
 End Sub
 を改良して出来ないかと思いましたが、何しろVBAに関してまったくの素人ですので
 どうしようもありませんでした。
 この中身が分かる方、応用可能でしょうか？

 どの状態のデータからどうなれば良いのかよく分かりません。

 みやほりんさんのコードで、準備データを作って
 それを集計して行く事にすれば良いのでしょうか？

 動かしてみられましたか？
 どうでしたか？

 また
 ＞何しろVBAに関してまったくの素人ですので
 ＞どうしようもありませんでした。
 と書いて居られますが、ご希望が叶えられるVBAがこれから投稿されたとして
 何か変更が在った時に、ご自身で修正出来るのでしょうか？
 （これは、実際にその時になってみないと分からない部分が有りますので
 　その努力をする気があるかどうかの確認と言う事になりますが。）

 VBAでその様に集計をするものは作れるでしょう。
 そして、今回のご希望に合わせて作るので要求が変わらない限り使えると思います。
 ただ、表のフォーマット等は割と簡単に変わってしまうと思います。
 その時に
 ＞何しろVBAに関してまったくの素人ですので
 ＞どうしようもありませんでした。
 と言う状態になって使えなくなるのなら、他の方法を考えた方が良いと思います。

 一覧に成っていたら、ピボットテーブルで有る程度集計は出来ます。
 その集計結果から、実際の様式に合わせて変更して行くのなら
 手作業でもそう手数は多くならないと思いますし
 集計せずに済むなら、VBAもより簡単に成ると思います。

 その辺りの(OH!)さんの決意がどの程度のものなのかよく分からないので
 レスも付きにくいのではないかと思います。

 (HANA)

 私には　OH!さんの処理したい内容と[[20060403190250]]　『帳簿の集計』 （オレンジ）とは
まったく別物に見えます。
共通しているのは「あるレイアウトから別のレイアウトに転記する」という発想の部分だけで、
あとはレイアウトも、転記の仕方もまったく別物です。
ウィルスに対する抗体みたいなもので、ちょっとでも型が違うと効きません。
インフルエンザワクチンだったら何を使ってもいい、というわけではないですよね。
　
「転記先の開始行が１行だけ違う」程度だったら迷わず「応用」を考えますが、
今回は「まったく違う処理」なので、「別のマクロを作る」という選択になります。
　
で、OH!さんの目的によって方針が変わります。
単に日計などの数値を確認するのが目的であれば、エクセルの関数や集計機能を活用しやすい
データ構造で記録をとっていくのもひとつです。この場合は、御提示のレイアウトは捨てます。
御提示のレイアウトでどうしても運用する必要があり、なおかつ自動化したいのであれば、
このレイアウトに合わせたマクロを一から作る必要があるかと。
　
複雑な処理を提示して「～する方法はありませんか」と相談される方が多いのですが、
複雑な処理ほどエクセルの機能を組み合わせてブックを「作りこむ」必要があります。
したがって、「～する方法」は「作りこむだけのスキルを身につける」事ですね。
　
これでは答えにはなっていませんが。
　
さて、test20110120()を実行すると、現金出納帳に収入日計表の大まかな部分が転記されます。
作業としてはかなり軽減されるはずですので、２週間に１度のことなら、あとは手作業でも
かなり時間短縮になるはずです。
あとは、ゆっくりVBAのスキルをつけていって、このマクロに肉付けしていったり、
自分なりの工夫を加えたりしていただければ、と思います。
　
収入日計表を記入していくだけで現金出納帳が出来上がってしまえば確かに楽ですけれども、
私の回答スタンスは「相談者が楽になること」ではなくて、「相談者のスキルアップのお手伝い」
なので、「丸投げ相談者にマクロをぽんっ！」はやりたくないのです。
　
「出し惜しみ」となじる相談者もいらっしゃいましたけど。
(みやほりん)(-_∂)b

 ＞私の回答スタンスは「相談者が楽になること」ではなくて、「相談者のスキルアップのお手伝い」
 ＞なので、「丸投げ相談者にマクロをぽんっ！」はやりたくないのです。

 賛成。ここは学校ですから。

 他人の書き込みを消さないでください。
2011/01/24　09:00 消された書き込み復活
(みやほりん)(-_∂)b

 あらら、（OH!）さんが削除されたのかな？

 私は、過去ログを探して来られただけでも　評価出来ると思っていますが。
 （今後がどうか　ってのが気になっているだけで。）

 評価と言えば、最初のご説明の図は　素晴らしいと思いました。
 作るのは、かなり　大変だったんじゃないですか？

 私は、自分が報われる
 　　これからマクロを勉強されて、変更が在った時に
 　　ご自身でコードを変更して使用し続けてもらえる
 　　（或いは、頑張る気が現在ある）
 と思えば、作ることは問題と思いません。

 基本的に目指したい所は、みやほりんさん同様
 ＞「相談者のスキルアップのお手伝い」
 ですが、コードを見て動きを確認しながらスキルアップ
 ってのは、ありだと思います。

 で、（OH!）さんに関しては最初のご説明の図を作って居られるので
 マクロだってその気になれば出来るんじゃないかと思っています。

 今分からないと仰っているのは、「マクロは分からない」
 と、ご自身でリミットを掛けて居られる為の様に感じます。
 その状態では、いつまでたっても分かるようには成らないと思います。

 なので、比較的簡単そうな　みやほりんさんが書いて下さったマクロに関して
 ＞動かしてみられましたか？
 ＞どうでしたか？
 と言う質問を書いてみました。

 test20110120はどうでしたか？
 どこがどうなって実行結果になるのか
 朧気ながらでも分かりますか？
 　For ～ Next　とか、If ～ Else ～ End If　とか。
 　Mod　とか。。。

 「このコードのココが分からない」とピンポイントで書いておけば
 みやほりんさんが丁寧に教えてくれる
 或いは参考に成るページを教えてくれる　と思いますよ。
 （はっ！！これって丸投げ発言！？）

 集計はその次のステップと言う事で良いのではないかと、私は思いますが。

 ×　ご指導お願いします。
 ○　マクロを作って下さい。 
 と言う事ではなかったと思っています。

 (HANA)

「シートタブ右クリックからモジュールに...」なんて説明しなきゃならないような人に

配列やらディクショナリなんかを平気で書く人も

どうなのかな～？

って思いますけど。（jun53）

 うーん、ここって相談者の問題解決の場であり、
回答者の実践修練場でもあったりするので、
どうかなぁ～？とも言い切れないんですけれども・・・。
私も覚えたてのステートメントを利用したマクロを作るのが
楽しかった時期がありますから。
　
みやほりんとしては、test20110120()を実行してもらって、
「これに日計の行を挿入していくにはどうしたらいいか」っていう方向に
進んでもらえば、良いと思っていたんですけれども。
(みやほりん)(-_∂)b

そうですか、わかりました。

私はそのようには考えませんが、いろんなやり方が有っていいのでしょう。

ただ、一日中張り付いて更新しまくったりは出来ませんけど、

これからも思ったことは書きますね^^ （jun53）

コメント返信：

[ 一覧(最新更新順) ]

[[20110119110820]] 『現金出納簿作成』（OH!） ページの最後に飛ぶ

[[20110119110820]]　『現金出納簿作成』（OH!）　ページの最後に飛ぶ